Solely the primary UNION subquery’s Converters are utilized to the outcome.

jOOQ 3.15 launched the idea of an ad-hoc converter, a converter that’s utilized “ad-hoc” to a single question. It makes use of the identical underlying mechanisms as any bizarre Converter that’s hooked up to generated code to be used in each question.

An instance of such an ad-hoc converter is that this:

// With out the converter, assuming BOOK.ID is of kind Area<Integer>
End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .fetch();

// With the converter
End result<Record1<Lengthy>> outcome =
ctx.choose(BOOK.ID.convertFrom(i -> i.longValue()))
   .from(BOOK)
   .fetch();

Whereas there are different methods to transform knowledge varieties, e.g. by utilizing CAST() or COERCE() expressions, this method attaches a Converter to the sphere, which is known as proper after studying the Integer worth from the JDBC ResultSet so as to flip it right into a Lengthy. This conversion is completed on the consumer aspect. The RDBMS that executes the question just isn’t conscious of it.

That’s an necessary element! The RDBMS just isn’t conscious of it!

Caveat: Utilizing UNION

An fascinating problem (#14693) was raised lately on the problem tracker relating to using such ad-hoc converters in a UNION. For instance, let’s assume this question is being run:

End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .union(
    choose(AUTHOR.ID)
   .from(AUTHOR))
   .fetch();

This would possibly produce one thing like:

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

Assuming obtainable BOOK.ID are [1, 2, 3, 4] and obtainable AUTHOR.ID are [1, 2], the UNION will take away duplicates.

What do you assume will occur once we connect this ad-hoc converter solely to the second UNION subquery?

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

Its purpose appears to be to get the detrimental worth of every AUTHOR.ID, whereas holding the BOOK.ID intact. However bear in mind:

• The conversion occurs within the consumer, not the server, so the RDBMS isn’t conscious of it
• This implies it has no impact on the UNION operator
• Moreover, jOOQ doesn’t know which UNION subquery contributes which row, so it couldn’t presumably determine whether or not to use the converter or not!

And that’s successfully what occurs. The outcome remains to be:

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

And the lambda i -> -i isn’t known as! This isn’t simply true for ad-hoc converters, it’s additionally true for some other Converter (or Binding) that you simply connect to those projected columns. jOOQ will solely ever think about the row kind of the primary UNION subquery when fetching outcomes from a JDBC (or R2DBC) ResultSet. You solely have to ensure that each row varieties are appropriate for the Java compiler to kind examine your question.

Answer

There are actually solely 2 options to such a state of affairs:

• If you happen to’re positive your conversion ought to occur in your consumer code (versus the server), then it is best to apply it at the very least to the primary UNION subquery. Ideally, you’ll simply apply it to all of the UNION subqueries for consistency causes, together with in case you extract a subquery for it to be reused.
• Probably, it is best to have moved the conversion to the server aspect, within the first place

Within the latter case, this question would possibly make extra sense, if the intention was to create detrimental AUTHOR.ID values:

End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .union(
    choose(AUTHOR.ID.neg())
   .from(AUTHOR))
   .fetch();

It will now produce the next SQL question:

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

And a outcome set like this:

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

Maintain this in thoughts when utilizing ad-hoc converters together with MULTISET, specifically!