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

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 abnormal 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 sort Discipline
Outcome> consequence =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .fetch();

// With the converter
Outcome> consequence =
ctx.choose(BOOK.ID.convertFrom(i -> i.longValue()))
   .from(BOOK)
   .fetch();

Whereas there are different methods to transform knowledge varieties, e.g. through the use of CAST() or COERCE() expressions, this strategy attaches a Converter to the sector, which known as proper after studying the Integer worth from the JDBC ResultSet in an effort to flip it right into a Lengthy. This conversion is finished on the shopper aspect. The RDBMS that executes the question shouldn’t be conscious of it.

That’s an vital element! The RDBMS shouldn’t be conscious of it!

Caveat: Utilizing UNION

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

Outcome> consequence =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .union(
    choose(AUTHOR.ID)
   .from(AUTHOR))
   .fetch();

This may 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 suppose will occur after we connect this ad-hoc converter solely to the second UNION subquery?

Outcome> consequence =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .union(
    choose(AUTHOR.ID.convertFrom(i -> -i))
   .from(AUTHOR))
   .fetch();

Its objective appears to be to get the detrimental worth of every AUTHOR.ID, whereas preserving the BOOK.ID intact. However keep in mind:

• The conversion occurs within the shopper, 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 probably determine whether or not to use the converter or not!

And that’s successfully what occurs. The consequence continues to be:

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

And the lambda i -> -i is rarely referred to as! This isn’t simply true for ad-hoc converters, it’s additionally true for every other Converter (or Binding) that you just connect to those projected columns. jOOQ will solely ever contemplate the row sort 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 sort examine your question.

Answer

There are actually solely 2 options to such a scenario:

• If you happen to’re certain your conversion ought to occur in your shopper code (versus the server), then it is best to apply it not less than 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.
• Presumably, it is best to have moved the conversion to the server aspect, within the first place

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

Outcome> consequence =
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 consequence set like this:

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

Hold this in thoughts when utilizing ad-hoc converters together with MULTISET, particularly!