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

on

|

views

and

comments

[ad_1]

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 kind Discipline<Integer>
Outcome<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
   .from(BOOK)
   .fetch();

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

Whereas there are different methods to transform knowledge sorts, e.g. by utilizing CAST() or COERCE() expressions, this method attaches a Converter to the sector, 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 finished on the shopper facet. The RDBMS that executes the question isn’t conscious of it.

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

Caveat: Utilizing UNION

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

Outcome<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 accessible BOOK.ID are [1, 2, 3, 4] and accessible 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?

Outcome<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 unfavorable worth of every AUTHOR.ID, whereas maintaining 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 presumably determine whether or not to use the converter or not!

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

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

And the lambda i -> -i is rarely 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 contemplate 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 sorts are appropriate for the Java compiler to kind verify your question.

Resolution

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 shopper code (versus the server), then you need to apply it no 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.
  • Probably, 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 unfavorable AUTHOR.ID values:

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

This can now produce the next SQL question:

SELECT guide.id
FROM guide
UNION
SELECT -author.id
FROM writer

And a outcome set like this:

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

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

[ad_2]

Supply hyperlink

Share this
Tags

Must-read

Google Presents 3 Suggestions For Checking Technical web optimization Points

Google printed a video providing three ideas for utilizing search console to establish technical points that may be inflicting indexing or rating issues. Three...

A easy snapshot reveals how computational pictures can shock and alarm us

Whereas Tessa Coates was making an attempt on wedding ceremony clothes final month, she posted a seemingly easy snapshot of herself on Instagram...

Recent articles

More like this

LEAVE A REPLY

Please enter your comment!
Please enter your name here