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
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 |
[1, 2, 3, 4] and available
[1, 2], the
UNION will remove duplicates.
What do you think will happen when we attach this ad-hoc converter only to the second
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
- Furthermore, jOOQ doesn’t know which
UNIONsubquery 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
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.
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
UNIONsubquery. Ideally, you’ll just apply it to all the
UNIONsubqueries 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
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!