I got a runtime-error when using the row() Syntax of jooq, and im not sure where the problem is. When the row() part is removed from the query, it executes correctly. Following code-example produces the error:
record ProductItem(Long productId, BigDecimal price, UserItem creator) {
}
record UserItem(Integer userId, String email) {
}
return dsl()
.select(
PRODUCT.PRODUCTID,
PRODUCT.PRICE,
row(
PRODUCT.fk_product_creatorId().USERID,
PRODUCT.fk_product_creatorId().EMAIL
).mapping(UserItem::new))
.fetch(Records.mapping(ProductItem::new));
When executing the above Jooq-Query it crashes with following runtime-exception:
2024-06-10 10:58:30,580 DEBUG [org.joo.too.LoggerListener] (executor-thread-1) Executing query :
select
testshop2.product.productId,
testshop2.product.price,
alias_2438556.userId as nested.userId,
alias_2438556.email as nested.email
2024-06-10 10:58:30,581 WARN [org.mar.jdb.mes.ser.ErrorPacket] (executor-thread-1) Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.userId,
alias_2438556.email as nested.email' at line 4
2024-06-10 10:58:30,581 DEBUG [org.joo.too.LoggerListener] (executor-thread-1) Exception : org.jooq.exception.DataAccessException: SQL [select
testshop2.product.productId,
testshop2.product.price,
alias_2438556.userId as nested.userId,
alias_2438556.email as nested.email]; (conn=129) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.userId,
alias_2438556.email as nested.email' at line 4
at org.jooq_3.19.9.MARIADB.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:3607)
at org.jooq.impl.Tools.translate(Tools.java:3595)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:827)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:362)
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301)
at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:322)
at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:3256)
at org.jooq.impl.ResultQueryTrait.collect(ResultQueryTrait.java:360)
at org.jooq.impl.ResultQueryTrait.fetch(ResultQueryTrait.java:1465)
at org.fk.product.repository.ProductRepository.query2(ProductRepository.java:77)
at org.fk.product.manager.ProductManager.query(ProductManager.java:64)
at org.fk.product.manager.ProductManager_ClientProxy.query(Unknown Source)
at org.fk.product.controller.ProductExamplesControllerV1.query(ProductExamplesControllerV1.java:43)
at org.fk.product.controller.ProductExamplesControllerV1$quarkusrestinvoker$query_8cfa215d81ea3f2aa9f868d5bca87ea66e97652c.invoke(Unknown Source)
at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)
at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:599)
at org.jboss.threads.EnhancedQueueExecutor$Task.doRunWith(EnhancedQueueExecutor.java:2516)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2495)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1521)
at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:11)
at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:11)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLSyntaxErrorException: (conn=129) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.userId,
alias_2438556.email as nested.email' at line 4
at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:289)
at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:378)
at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:172)
at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:915)
at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:854)
at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:773)
at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:697)
at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:93)
at org.mariadb.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:276)
at io.agroal.pool.wrapper.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:301)
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4975)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:236)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
... 21 more
I tries to reproduce the SQL that is created by jooq and it looks something like this. It seems that especially the v8.userId seems to produce the error. When the v8-parts are removed the SQL also executes correctly without error.
select
testshop2.product.productId as v0,
testshop2.product.price as v2,
alias_2438556.userId as v8.userId,
alias_2438556.email as v8.email
from (
testshop2.product
left outer join testshop.user as alias_2438556
on testshop2.product.creatorId = alias_2438556.userId
);
The used versions are:
Please give an info if more details are needed.
Also see reference for Row-Value-Expression:
The identifiers that are generated for nested attributes are things like "nested.email", using jOOQ's default "dot notation" for such nested objects. This is a historic feature also of the DefaultRecordMapper, which supports this notation to automatically map nested data reflectively.
The assumption here is that identifiers are quoted (which is always the default in jOOQ). You probably set your Settings.renderQuotedNames to something else, which is why you're getting an invalid nested.email identifier. If you do that, you can still change the Settings.namePathSeparator to something else, e.g. "__", such that unquoted nested identifiers like nested__email will still work with the DefaultRecordMapper as well as produce valid SQL.
But if you don't have a good reason to remove quoting of identifiers, then I'd recommend keeping that turned on.