How to use jOOQ’s Converters with UNION Operations

jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied “ad-hoc” to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query.

An example of such an ad-hoc converter is this:

// Without the converter, assuming BOOK.ID is of type Field<Integer>
Result<Record1<Integer>> result =
ctx.select(BOOK.ID)
   .from(BOOK)
   .fetch();

// With the converter
Result<Record1<Long>> result =
ctx.select(BOOK.ID.convertFrom(i -> i.longValue()))
   .from(BOOK)
   .fetch();

While there are other ways to convert data types, e.g. by using CAST() or COERCE() expressions, this approach attaches a Converter to the field, which is called right after reading the Integer value from the JDBC ResultSet in order to turn it into a Long. This conversion is done on the client side. The RDBMS that executes the query is not aware of it.

That’s an important detail! The RDBMS is not aware of it!

Caveat: Using UNION

An interesting issue (#14693) was raised recently on the issue tracker regarding the use of such ad-hoc converters in a UNION. For example, let’s assume this query is being run:

Result<Record1<Integer>> result =
ctx.select(BOOK.ID)
   .from(BOOK)
   .union(
    select(AUTHOR.ID)
   .from(AUTHOR))
   .fetch();

This might produce something like:

|id |
|---|
|1  |
|2  |
|3  |
|4  |

Assuming available BOOK.ID are [1, 2, 3, 4] and available AUTHOR.ID are [1, 2], the UNION will remove duplicates.

What do you think will happen when we attach this ad-hoc converter only to the second UNION subquery?

Result<Record1<Integer>> result =
ctx.select(BOOK.ID)
   .from(BOOK)
   .union(
    select(AUTHOR.ID.convertFrom(i -> -i))
   .from(AUTHOR))
   .fetch();

Its goal seems to be to get the negative value of each AUTHOR.ID, while keeping the BOOK.ID intact. But remember:

• The conversion happens in the client, not the server, so the RDBMS isn’t aware of it
• This means it has no effect on the UNION operator
• Furthermore, jOOQ doesn’t know which UNION subquery contributes which row, so it couldn’t possibly decide whether to apply the converter or not!

And that’s effectively what happens. The result is still:

|id |
|---|
|1  |
|2  |
|3  |
|4  |

And the lambda i -> -i is never called! This isn’t just true for ad-hoc converters, it’s also true for any other Converter (or Binding) that you attach to these projected columns. jOOQ will only ever consider the row type of the first UNION subquery when fetching results from a JDBC (or R2DBC) ResultSet. You only have to guarantee that both row types are compatible for the Java compiler to type check your query.

Solution

There are really only 2 solutions to such a situation:

• If you’re sure your conversion should happen in your client code (as opposed to the server), then you should apply it at least to the first UNION subquery. Ideally, you’ll just apply it to all the UNION subqueries for consistency reasons, including in case you extract a subquery for it to be reused.
• Possibly, you should have moved the conversion to the server side, in the first place

In the latter case, this query might make more sense, if the intention was to create negative AUTHOR.ID values:

Result<Record1<Integer>> result =
ctx.select(BOOK.ID)
   .from(BOOK)
   .union(
    select(AUTHOR.ID.neg())
   .from(AUTHOR))
   .fetch();

This will now produce the following SQL query:

SELECT book.id
FROM book
UNION
SELECT -author.id
FROM author

And a result set like this:

|id |
|---|
|-2 |
|-1 |
|1  |
|2  |
|3  |
|4  |

Keep this in mind when using ad-hoc converters along with MULTISET, in particular!