jOOQ doesn't render a manual CAST of same type

[Thorsten Schöning]

Hi all,

I had the following code which wasn't rendered like I expected. The
added CAST was missing fromt he rendered SQL, but important in my
case. Reason is that Postgres has a result type of "double precision",
which really needs to be casted to an integer in my case.

> DSL.field
> (
> "extract(epoch FROM now() - {0})",
> SQLDataType.INTEGER,
> CLTS.COL_PACKET_WHEN
> ).div(3600).div(24).cast(SQLDataType.INTEGER)

This resulted in the following SQL:

> ((extract(epoch FROM now() - "clts"."packet_when") / 3600) / 24)

The field was of type "Integer" and the results of "div" were so as
well. Might that have been the problem, that jOOQ decided the cast
wasn't actually necessary and didn't render it?

My current implementation looks like the following and renders the
cast:

> Field<Integer> secsCalc = DSL.field
> (
> "extract(epoch FROM now() - {0})",
> SQLDataType.INTEGER,
> CLTS.COL_PACKET_WHEN
> );

> Field<Integer> daysCalc = secsCalc.div(3600).cast(SQLDataType.DECIMAL)
> .div( 24).cast(SQLDataType.INTEGER);

The important difference is that I cast to something which is
compatible with the floating-point runtime type manually. Though, I
would have expected this isn't necessary, because I don't need to care
when simply forcefully casting to INTEGER.

What I recognized as well: SQLDataType.DOUBLE doesn't work, because
that renders to "double" in Postgres instead of "double precision" and
the former doesn't exist in my Postgres 11.

Is that as expected as well?

Thanks!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Lukas Eder]

Hi Thorsten,

Old versions of jOOQ used to prevent avoid some casts, but that's no longer the case:

https://github.com/jOOQ/jOOQ/issues/8234

I'm assuming you're not using jOOQ 3.11 or older. I'm trying to reproduce this:

Field<?> x = DSL.field

(

  "extract(epoch FROM now() - {0})",

   SQLDataType.INTEGER,

   DSL.field("test")

).div(3600).div(24).cast(SQLDataType.INTEGER);

System.out.println(DSL.using(POSTGRES).render(x));

I'm getting, in both 3.14.0 and 3.13.5:

cast(((extract(epoch FROM now() - test) / ?) / ?) as int)

So this seems to work as expected. What am I missing?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/747120238.20201010112925%40am-soft.de.

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Montag, 12. Oktober 2020 um 08:58 schrieben Sie:

> I'm assuming you're not using jOOQ 3.11 or older.

But I am, 3.11.12, should have mentioned that of course. :-/ So thanks
for testing and letting me know about the root cause and fix. I've ran
into an unrelated problem when upgrading some months ago and need to
fix that first at some point.

> Field<Integer> daysCalc = secsCalc.div(3600).cast(SQLDataType.DECIMAL)
> .div( 24).cast(SQLDataType.INTEGER);

vs.

> Field<Integer> daysCalc = secsCalc.div(3600).cast(SQLDataType.DOUBLE)
> .div( 24).cast(SQLDataType.INTEGER);

What I recognized as well: SQLDataType.DOUBLE doesn't work, because
that renders to "double" in Postgres instead of "double precision" and
the former doesn't exist in my Postgres 11.

Might this be realted to the old jOOQ as well?

[Lukas Eder]

A quick test on jOOQ 3.14.0-SNAPSHOT:

System.out.println(DSL.using(SQLDialect.POSTGRES).renderInlined(

    DSL.field("test", SQLDataType.INTEGER).div(3600).cast(SQLDataType.DOUBLE)

                           .div(24).cast(SQLDataType.INTEGER)

));

Yields:

cast((cast((test / 3600) as double precision) / 24) as int

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/138316955.20201013195915%40am-soft.de.