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:
- jooq 3.19.9
- mariadb 10.10.7
- quarkus 3.9.5
Please give an info if more details are needed.
Also see reference for Row-Value-Expression:
- https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/nested-records/