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/