JOOQ: ad-hoc converters ignored in union query with multiple converters

Question

It seems when running a union query with multiple converters, the second ad-hoc converter is ignored.

For example:

record A(UInteger id, String name) implements Something { };
record B(UInteger id, String name) implements Something { };

// ... 
List<Something> record = db.select(
  row(TABLE_A.ID,TABLE_A.NAME).mapping(A::new)
)
.from(TABLE_A)
.unionAll(
   select(
       row(TABLE_A.ID,TABLE_A.NAME).mapping(B::new)
   ).from(TABLE_B)
).fetch(Record1::component1);

All results end up as A and the second ad-hoc converter gets ignored.

Answer 1

Why this works as "expected":

That's a very interesting case, and looking only at the API usage, it at first seems like a bug where the rows from the second UNION ALL subquery should be mapped to B, but are instead mapped to A.

But jOOQ cannot possibly discriminate between rows produced from the first subquery and the second one. Only the database server might know about the origin of each row. Think of how jOOQ works behind the scenes:

• The query is being generated the way you wrote it (ad-hoc converters don't play a role in this, being completely client side mapping implementations)
• The JDBC driver returns rows via ResultSet
• jOOQ maps those rows to your custom data type A, which is the type of the query for all UNION ALL subqueries (just like the names of the columns are derived only from the first UNION ALL subquery)

In your particular case, you tell the compiler to infer List<Something> as the result type via your explicit declaration. That's why you can still compile your mapping(B::new) from the second subquery, but what you're really getting is a List<A>, because jOOQ will only use the first UNION ALL subquery's row type for the actual results, given that it cannot discriminate between the two.

Since this issue came up on the issue tracker a few times, there's now a section in the manual explaining this, as well as a blog post:

• https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/set-operations/set-operation-rowtype/
• https://blog.jooq.org/how-to-use-jooqs-converters-with-union-operations/

Note, this isn't limited to ad-hoc converters. It works this way for any type of converter or binding.

A workaround using an explicit discriminator

You can always implement a discriminator aware converter yourself:

Function3<String, Integer, String, Something> converter = 
    (discriminator, id, name) -> {
        switch (discriminator) {
            case "A": return new A(id, name);
            case "B": return new B(id, name);
            default: throw new IllegalArgumentException();
        }
    };

List<Something> records = db.select(
  row(inline("A"), TABLE_A.ID, TABLE_A.NAME).mapping(converter)
)
.from(TABLE_A)
.unionAll(
   select( 
       row(inline("B"), TABLE_A.ID, TABLE_A.NAME).mapping(converter)
   ).from(TABLE_B)
).fetch(Record1::component1);

This is a bit more verbose, but now it does exactly what you're expecting. Note that the second UNION ALL subquery still needs a reference to the converter in order to compile.

Mapping only globally

Maybe even easier in this particular case, map things globally instead of per UNION ALL subquery (the actual query might be more complicated, of course):

List<Something> records = db.select(inline("A"), TABLE_A.ID, TABLE_A.NAME)
.from(TABLE_A)
.unionAll(
   select(inline("B"), TABLE_A.ID,TABLE_A.NAME)
  .from(TABLE_B)
).fetch((discriminator, id, name) -> {
    switch (discriminator) {
        case "A": return new A(id, name);
        case "B": return new B(id, name);
        default: throw new IllegalArgumentException();
    }
});

Now, it is a bit less confusing, as you don't have to map the two subqueries twice anymore, only the global result, once.