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

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 Area<Integer>
End result<Record1<Integer>> end result =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .fetch();

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

Whereas there are different methods to transform information varieties, e.g. through the use of 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 to be able to flip it right into a Lengthy. This conversion is finished on the consumer facet. The RDBMS that executes the question is just not conscious of it.

That’s an vital element! The RDBMS is just not conscious of it!

Caveat: Utilizing UNION

An attention-grabbing problem (#14693) was raised not too long ago on the difficulty tracker concerning using such ad-hoc converters in a UNION. For instance, let’s assume this question is being run:

End result<Record1<Integer>> end result =
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 accessible BOOK.ID are [1, 2, 3, 4] and accessible AUTHOR.ID are [1, 2], the UNION will take away duplicates.

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

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

Its objective appears to be to get the damaging worth of every AUTHOR.ID, whereas protecting 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 probably determine whether or not to use the converter or not!

And that’s successfully what occurs. The end result continues 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 every other Converter (or Binding) that you simply connect to those projected columns. jOOQ will solely ever think about 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 suitable for the Java compiler to sort examine your question.

Resolution

There are actually solely 2 options to such a scenario:

• Should you’re certain your conversion ought to occur in your consumer code (versus the server), then you need to apply it at the 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.
• Presumably, you need to have moved the conversion to the server facet, within the first place

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

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

It will now produce the next SQL question:

SELECT ebook.id
FROM ebook
UNION
SELECT -author.id
FROM creator

And a end result set like this:

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

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