JOOQ multiset throwing syntax exception

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?

Solution

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.