Hey, commented inline.
On Tue, May 24, 2016 at 3:04 PM, Lukas Eder <[email protected]> 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 <[email protected]>: > >> 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? > > >> 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? > 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 > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- Dmitry Gusev AnjLab Team http://anjlab.com -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
