JOOQ multiset throwing syntax exception-CodePudding

Recently moved to JOOQ 3.15.5 and tried the Multiset feature but it is throwing SQLSyntaxErrorException. Below is the query I wrote:

dslContext.select(
    tableA.asterisk(),
    multiset(
            select(tableB.DELETED, tableB.VALUE)
                .from(tableB)
                .where(tableB.ORDER_ID.eq(tableA.ORDER_ID))
    ).as("bookingAdditions")
).from(tableA)
 .where(tableA.BATCH_ID.greaterThan(batchId))
 .fetchInto(BookingDto.class);

Here is the relations:

|tableA|  1           n  |tableB| 
|      | --------------> |      | 
|      |                 |      | 
--------                  --------
(tableA)                  (tableB)

Here is the query that is being generated by JOOQ:

set @t = @@group_concat_max_len; set @@group_concat_max_len = 4294967295; select `tablea`.*, (select coalesce(json_merge_preserve('[]', concat('[', group_concat(json_array(`v0`, `v1`) separator ','), ']')), json_array()) from (select `tableb`.`deleted` as `v0`, `tableb`.`value` as `v1` from `db_name`.`booking_additions` as `tableb` where `tableb`.`order_id` = `tablea`.`order_id`) as `t`) as `bookingadditions` from `db_name`.`booking` as `tablea` where `tablea`.`batch_id` > 0; set @@group_concat_max_len = @t;

Here are exceptions:

org.jooq.exception.DataAccessException: SQL [set @t = @@group_concat_max_len; set @@group_concat_max_len = 4294967295; select `tablea`.*, (select coalesce(json_merge_preserve('[]', concat('[', group_concat(json_array(`v0`, `v1`) separator ','), ']')), json_array()) from (select `tableb`.`deleted` as `v0`, `tableb`.`value` as `v1` from `db_name`.`booking_additions` as `tableb` where `tableb`.`order_id` = `tablea`.`order_id`) as `t`) as `bookingadditions` from `db_name`.`booking` as `tablea` where `tablea`.`batch_id` > ?; set @@group_concat_max_len = @t;]; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @@group_concat_max_len = 4294967295; select `tablea`.*, (select coalesce(jso' at line 1
    at org.jooq_3.15.5.MYSQL.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2988)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:295)
    at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:316)
    at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:2866)
    at org.jooq.impl.ResultQueryTrait.collect(ResultQueryTrait.java:357)
    at org.jooq.impl.ResultQueryTrait.fetchInto(ResultQueryTrait.java:1423)
    at com.company.BookingDAO.fetchBookings(BookingDAO.java:118)
    at

I am using Mysql: 5.7. What am I doing wrong? Any hint?

CodePudding user response：

Regarding the multiple statements

In addition to what Bill Karwin said, you could also specify the Settings.renderGroupConcatMaxLenSessionVariable to be false and increase your server's @@group_concat_max_len variable for your session or your server yourself.

jOOQ's default here is to assume it's easier to add the JDBC connection flag rather than change the server settings, but both approaches are viable.

Regarding correlated derived tables

This particular emulation requires support for correlated derived tables, which MySQL 5.7 doesn't support, see https://github.com/jOOQ/jOOQ/issues/12045. You're not going to get a correlated MULTISET expression to work on MySQL 5.7, but you could write an almost equivalent MULTISET_AGG expression, like this:

dslContext.select(
    tableA.asterisk(),
    multisetAgg(tableB.DELETED, tableB.VALUE).as("bookingAdditions")
).from(tableA)
 .join(tableB).on(tableB.ORDER_ID.eq(tableA.ORDER_ID))
 .where(tableA.BATCH_ID.greaterThan(batchId))
 // You can group by the primary key, or tableA.fields() if you don't have a PK
 .groupBy(tableA.ID)
 .fetchInto(BookingDto.class);

Unlike MULTISET, MULTISET_AGG produces NULL values instead of empty lists in case you're left joining tableB, as is usual for SQL aggregate functions. You could then use coalesce(multisetAgg(...), multiset(...)) as a workaround.

CodePudding user response：

jOOQ generates three SQL statements separated by semicolons when you use GROUP_CONCAT() in a query. Unfortunately, the default behavior of MySQL is to disallow multiple queries in a single request.

You have to change your JDBC connection options to include allowMultiQueries.