Nesting MULTISET/ROW/MULTISET/ROW with ad-hoc converters and JSON emulation results in ClassCastException

Question

When performing a select which includes a subquery in multiselect, which itself performs a subquery in a multiset, I would expect the result to have the expected type.

Instead, the nested result appears to be left as an ArrayList and not serialized properly to the target type.

Steps to reproduce the problem

record Tuple2(String t1, String t2) {}

var result = db.select(
    multiset(
        select(
            row(
                multiset(
                    select(
                        row("Hello", "World").mapping(Tuple2::new)
                    ).from(dual())
                ).convertFrom(r -> r.map(Record1::component1))
            ).mapping(r -> r)
        ).from(dual())
    ).convertFrom(r -> r.map(Record1::component1))
).from(dual())
.fetchOne(Record1::component1);

log.debug("The result: {}", result);
log.debug("The tuple: {}", result.get(0).get(0));
log.debug("The type of the result: {}", result.get(0).get(0).getClass());

The expected type of result is List<List<Tuple2>> but instead it appears to be ArrayList<ArrayList<ArrayList>> (I'm guessing the raw representation of the row type).

The output of this code is:

The result: [[[[Hello, World]]]]
The tuple: [[Hello, World]]
An unhandled error occurred
java.lang.ClassCastException: class java.util.ArrayList cannot be cast to class com.mycompany.mypackage.MyClass$1Tuple2 (java.util.ArrayList is in module java.base of loader 'bootstrap'; com.mycompany.mypackage.MyClass$1Tuple2 is in unnamed module of loader 'app')

jOOQ Version

3.17.8

Database product and version

MySQL 8.0.28

Java Version

openjdk version "19.0.1" 2022-10-18

OS Version

No response

JDBC driver name and version (include name if unofficial driver)

mysql:mysql-connector-java:8.0.31

Answer 1

This is a bug that was fixed in jOOQ 3.18.0, 3.17.9, and 3.16.15: https://github.com/jOOQ/jOOQ/issues/14657

The bug only affected JSON emulations of the MULTISET operator, not XML emulations, though that doesn't help as a workaround for MySQL, which doesn't support SQL/XML.

As noted in the bug discussion, this particular problem can be worked around by omitting one of the row() operators:

var result = db.select(
    multiset(
        select(
            // row( Unnecessary in this case
                multiset(
                    select(
                        row("Hello", "World").mapping(Tuple2::new)
                    ).from(dual())
                ).convertFrom(r -> r.map(Record1::component1))
            // ).mapping(r -> r)
        ).from(dual())
    ).convertFrom(r -> r.map(Record1::component1))
).from(dual())
.fetchOne(Record1::component1);

I realise that this is just a simplified reproducer, the real query being more complex, though perhaps this helps work around the problem also in the real query, until the fix is available.