Undocumented change of behavior for DSL.val(java.sql.Timestamp)

77 views
Skip to first unread message

Dmitry Gusev

unread,
May 23, 2016, 3:44:15 PM5/23/16
to jOOQ User Group
Hello,

I've found (undocumented?) change of behavior of DSL.val(java.sql.Timestamp).

Tested on PostgreSQL 9.4/9.5 running in UTC timezone, JVM process running in UTC default timezone too.

Here's the query:

DSLContext create = DSL.using(connection);

create.select(DSL.function(
        "timezone",
        Time.class,
        DSL.val("Asia/Hong_Kong"),
        DSL.val(new java.sql.Timestamp(
                java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
                        .toInstant(ZoneOffset.UTC)
                        .toEpochMilli())))
        .cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
        .execute();

with jOOQ 3.6.2 it generates this (correct result):

Executing query     : select cast(timezone(?, ?) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

+--------+
|cast    |
+--------+
|01:00:00|
+--------+

same query with jOOQ 3.8.1 (wrong result):

Executing query     : select cast(timezone(?, cast(? as timestamp)) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

+--------+
|cast    |
+--------+
|09:00:00|
+--------+

Debug output of both generated SQL queries looks the same, but it doesn't seem as this is the ones being executed, because results are different.

This query is wrong:

select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

This is the right query:

select cast(timezone('Asia/Hong_Kong', '2016-05-23 17:00:00.0') as time without time zone);

or this (gives the same result):

select cast(timezone('Asia/Hong_Kong', cast('2016-05-23 17:00:00.0' as timestamp with time zone)) as time without time zone);

To get the latter query I had to rewrite above jOOQ query like this:

create.select(DSL.function(
        "timezone",
        java.sql.Time.class,
        DSL.val("Asia/Hong_Kong"),
        DSL.val(java.time.OffsetDateTime.ofInstant(
                Instant.ofEpochMilli(
                        java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
                                .toInstant(ZoneOffset.UTC)
                                .toEpochMilli()),
                java.time.ZoneId.of("UTC"))))
        .cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
        .execute();

Executing query     : select cast(timezone(?, cast(? as timestamp with time zone)) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', cast('2016-05-23T17:00Z' as timestamp with time zone)) as time without time zone)

+--------+
|cast    |
+--------+
|01:00:00|
+--------+

Lukas Eder

unread,
May 24, 2016, 8:04:28 AM5/24/16
to jooq...@googlegroups.com
Hello Dmitry,

Thank you very much for your detailed E-Mail. I will comment inline.

2016-05-23 21:44 GMT+02:00 Dmitry Gusev <dmitry...@gmail.com>:
Hello,

I've found (undocumented?) change of behavior of DSL.val(java.sql.Timestamp).

Tested on PostgreSQL 9.4/9.5 running in UTC timezone, JVM process running in UTC default timezone too.

Here's the query:

DSLContext create = DSL.using(connection);

create.select(DSL.function(
        "timezone",
        Time.class,
        DSL.val("Asia/Hong_Kong"),
        DSL.val(new java.sql.Timestamp(
                java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
                        .toInstant(ZoneOffset.UTC)
                        .toEpochMilli())))
        .cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
        .execute();
Just to be sure to get the full picture: Is there any reason for this conversion chain? Why not Timestamp.valueOf(LocalDateTime.of(...)) ?
 
with jOOQ 3.6.2 it generates this (correct result):

Executing query     : select cast(timezone(?, ?) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

+--------+
|cast    |
+--------+
|01:00:00|
+--------+

same query with jOOQ 3.8.1 (wrong result):

Executing query     : select cast(timezone(?, cast(? as timestamp)) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

+--------+
|cast    |
+--------+
|09:00:00|
+--------+

But that cast, which is generated by jOOQ seems to be correct, no? After all, you're binding a java.sql.Timestamp, which corresponds to the SQL TIMESTAMP data type. Temporal casts are sometimes necessary in PostgreSQL in order to correctly disambiguate the types.

If you want to stay in control of your bind variables, you could use an org.jooq.Binding.
 
Debug output of both generated SQL queries looks the same,

Yes, that debug output inlines all bind variables for convenience. As you can see, the two versions *should* really be the same.
 
but it doesn't seem as this is the ones being executed, because results are different.

This query is wrong:

select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

I'd say, it's "correct" because you bound a java.sql.Timestamp variable. But it's obviously "wrong" because that's not what you expected.
 

This is the right query:

select cast(timezone('Asia/Hong_Kong', '2016-05-23 17:00:00.0') as time without time zone);

Hmm, I see. You could pass the Timestamp.toString() value instead of the Timestamp value itself, to achieve this behaviour.
 
or this (gives the same result):

select cast(timezone('Asia/Hong_Kong', cast('2016-05-23 17:00:00.0' as timestamp with time zone)) as time without time zone);
 
Indeed. Unfortunately, formal TIMESTAMP WITH TIME ZONE data type support is still on the roadmap in jOOQ. The JSR-310 support that you're using in the next example...

To get the latter query I had to rewrite above jOOQ query like this:

create.select(DSL.function(
        "timezone",
        java.sql.Time.class,
        DSL.val("Asia/Hong_Kong"),
        DSL.val(java.time.OffsetDateTime.ofInstant(
                Instant.ofEpochMilli(
                        java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
                                .toInstant(ZoneOffset.UTC)
                                .toEpochMilli()),
                java.time.ZoneId.of("UTC"))))
        .cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
        .execute();

Executing query     : select cast(timezone(?, cast(? as timestamp with time zone)) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', cast('2016-05-23T17:00Z' as timestamp with time zone)) as time without time zone)

+--------+
|cast    |
+--------+
|01:00:00|
+--------+
... is not yet complete or fully documented.

Now, the question is why this worked for you before. Perhaps by accident?

Dmitry Gusev

unread,
May 24, 2016, 9:59:25 AM5/24/16
to jooq...@googlegroups.com
Hey,

commented inline.

On Tue, May 24, 2016 at 3:04 PM, Lukas Eder <lukas...@gmail.com> wrote:
Hello Dmitry,

Thank you very much for your detailed E-Mail. I will comment inline.

2016-05-23 21:44 GMT+02:00 Dmitry Gusev <dmitry...@gmail.com>:
Hello,

I've found (undocumented?) change of behavior of DSL.val(java.sql.Timestamp).

Tested on PostgreSQL 9.4/9.5 running in UTC timezone, JVM process running in UTC default timezone too.

Here's the query:

DSLContext create = DSL.using(connection);

create.select(DSL.function(
        "timezone",
        Time.class,
        DSL.val("Asia/Hong_Kong"),
        DSL.val(new java.sql.Timestamp(
                java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
                        .toInstant(ZoneOffset.UTC)
                        .toEpochMilli())))
        .cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
        .execute();
Just to be sure to get the full picture: Is there any reason for this conversion chain? Why not Timestamp.valueOf(LocalDateTime.of(...)) ?
 

No reason, this conversion chain is here just for illustration purposes.

I used initializer with milliseconds because this is what I have in production code but with joda-time, i.e. `DSL.val(new Timestamp(instanceOfJodaDateTime.getMillis()))`.
 
with jOOQ 3.6.2 it generates this (correct result):

Executing query     : select cast(timezone(?, ?) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

+--------+
|cast    |
+--------+
|01:00:00|
+--------+

same query with jOOQ 3.8.1 (wrong result):

Executing query     : select cast(timezone(?, cast(? as timestamp)) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0') as time without time zone)

+--------+
|cast    |
+--------+
|09:00:00|
+--------+

But that cast, which is generated by jOOQ seems to be correct, no? After all, you're binding a java.sql.Timestamp, which corresponds to the SQL TIMESTAMP data type. Temporal casts are sometimes necessary in PostgreSQL in order to correctly disambiguate the types.

If you want to stay in control of your bind variables, you could use an org.jooq.Binding.

The cast seems to be correct, right. If I started implementing my query with jOOQ 3.8.1 this is what I'd expected, and I wouldn't use it, because it doesn't solve my issue.

As for bindings... I don't remember I saw any examples how to use them for adhoc queries like this. Is there any examples?
That's exactly my question :)

It worked because I built the query to do exactly what I needed, it wasn't an accident.
I was doing some TDD experimenting with jOOQ 3.6.2.

Lucky that I created a test for it and that test caught this change in behavior after I upgraded to the latest jOOQ (3.8.1).
Note that I didn't touch any java code at all during the upgrade.

Then I found the overload for OffsetDateTime and it magically worked.

So I was just wondering if the change was intentional or not, because I couldn't find any mentions of that in the documentation.

Maybe there was some change that fixed the bug that I was exploiting. Could not find any mentions of something similar in release notes too.
 

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/oX5QooSuWUo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--
Dmitry Gusev

AnjLab Team
http://anjlab.com

Lukas Eder

unread,
May 24, 2016, 10:12:43 AM5/24/16
to jooq...@googlegroups.com
As for bindings... I don't remember I saw any examples how to use them for adhoc queries like this. Is there any examples?

It's easy. Just use the base data type and create a new DataType from it:

DataType<OffsetDateTime> type = SQLDataType.TIMESTAMP.asConvertedDataType(new MyBinding());

You can now use the above with val():

DSL.val(mydate, type);

It's essentially the same thing as what the code generator does.

Now, the question is why this worked for you before. Perhaps by accident?

That's exactly my question :)

It worked because I built the query to do exactly what I needed, it wasn't an accident.
I was doing some TDD experimenting with jOOQ 3.6.2.

Lucky that I created a test for it and that test caught this change in behavior after I upgraded to the latest jOOQ (3.8.1).
Note that I didn't touch any java code at all during the upgrade.

Then I found the overload for OffsetDateTime and it magically worked.

So I was just wondering if the change was intentional or not, because I couldn't find any mentions of that in the documentation.

No, there was no intention to change this. But perhaps, the PostgreSQL jdbc driver does some magic that no longer applies with an explicit cast. From the past years of discussing the TIMESTAMP WITH TIME ZONE type on this list, I wouldn't be surprised...

Dmitry Gusev

unread,
May 24, 2016, 10:31:34 AM5/24/16
to jooq...@googlegroups.com
Cool, thanks for confirming that, and for a handy code snippet.

And for the great library too! :)

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/oX5QooSuWUo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages