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 theUNION
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!