[hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

On Tue, Jan 7, 2020 at 10:05 AM Yoann Rodiere <yoann at hibernate.org> wrote:

> On Tue, 7 Jan 2020 at 14:45, Steve Ebersole <steve at hibernate.org> wrote:
>
>> Sorry, I should have been more clear.  The literals are not "passed
>> through"; it's just a mechanism to be able to recognize the literal
>> syntactically while parsing.  All of those forms I showed actually are
>> handled in the code and interpreted as a Java temporal.  We then do
>> whatever we want to do with it in order to send it to the database (often
>> even as a parameter).
>>
>
> You mean you use the org.hibernate.type.Type instance registered in the
> dialect for this Java type to convert it to the SQL type? Nice, that
> definitely sounds more robust than what I thought.
>

This does nothing with Type.  The way the grammar is defined it literally
understands each piece of the temporal.  So given, e.g., {2020-01-01}, we
know that 2020 is the year, etc.  This is the benefit of defining it
syntactically.

I suppose the org.hibernate.type.Type instance used is inferred from the
> class of the litteral, which should be good enough in most cases. It may
> not work properly when users assigned their own type to a property, for
> example "where foo = {d 2020-01-01}" where "foo" is a LocalDate mapped as a
> Timestamp (see org.hibernate.test.type.LocalDateTest.
> DateAsTimestampRemappingH2Dialect). But that's rather advanced use case,
> and if you provide a way to define custom literal types as you said, users
> will have a way out.
>

This ties in with what I mentioned above.  The literal value is always an
instance of a Java 8 temporal type.  The Type we associate with the node
will be pulled from the TypeConfiguration.


Be aware that the form zone id + offset may also make sense, when users
> want to use a zone id during DST switch with overlap (the same datetime
> happens twice in the same zone, so the offset is needed for disambiguation).
> I suppose the offset alone would be enough, but from what I've seen, the
> resulting ZoneDateTime object is different depending on whether you pass
> zone id + offset or just offset.
>

As far as I know, even Java does not support that.  A true zone-id would be
something like (for me) "America/Chicago".  If I ask Java to parse
"2020-01-01 10:10:10 America/Chicago +02:00" it just says no.  For me, CST
(standard) and CDT (daylight savings) are really synonyms for offset -
either UTC-05:00 or UTC-06:00 depending on day of the year.

As counter-intuitive as it sounds, a ZonedDateTime actually includes an
offset to differentiate the overlap case you mention.



>
>
> * We also support a STRING_LITERAL form of temporal literals as I
>> mentioned originally.  In my experience, using
>> `java.time.format.DateTimeFormatter#parseBest` always returned a
>> ZonedDateTime whether a zone-id or offset was specified.  My understanding
>> is that this varies from Java 8 to Java 9.  So that's something to consider
>> as well.
>>
>


> Not sure I see which literal you're talking about, since the ones you
> mentioned were temporal literals; do you mean something like "where date =
> '2019-01-01'"?
>
If it's new, I'd personally be in favor of not allowing this and sticking
> to a specific syntax for time literals. Seems less error-prone.
>

No I meant the alternative form I mentioned initially.  So I could have
`{ts '2020-0101 10:10:10'}` or `{2020-0101 10:10:10`).  The first form is
parsed from the String via `java.time.format.DateTimeFormatter#parseBest`.
For the other form, we actually process each int value individually and
piece together the correct temporal.


I was talking about the org.hibernate.type.Type implementations for all the
> Java date/time types, be it in java.util or in java.time. As you know, each
> type maps to the database column slightly differently, and each has its
> quirks, because of how JDBC drivers handle date/time types. Quite a few
> JDBC drivers behave very strangely, especially in corner cases (DST switch,
> dates before year 1900, ...). Often, the legacy date/time APIs are to blame
> (though I wouldn't touch the older versions of the MySQL drivers with a ten
> foot pole).
>
> More to the point: I know from experience it is quite hard to get the
> conversion from Java date/time values to an SQL date/time value to work
> properly in all cases (JDBC driver, JVM timezone, database timezone). But
> if you re-use org.hibernate.type.Type to convert literals to SQL types,
> then it should behave the same as when persisting, so queries such as
> "where foo = {2019-01-01}" should work even if the date is converted in a
> convoluted way before it is sent to the database.
> You can find examples in package org.hibernate.test.type: LocalTimeTest,
> LocalDateTest, ...
>
>
>>
>> I might be wrong, but only exhaustive testing of all literals with all
>>> date/time types on all RDBMS will let us know for sure. Let's keep in mind
>>> how many bugs have surfaced from time-related features in the past...
>>>
>>
>> Gavin actually did quite a bit of that in the PR he sent us.  He added
>> pretty cool support for various temporal-related things such as how to
>> handle formatting (to_char, etc), extraction and temporal arithmetic -
>> specifically formalizing and normalizing them across databases.
>>
>
> Nice. As long as it uses org.hibernate.type.Type implementations when it
> comes to converting between Java values and SQL values, it should be safe.
>
>

I think the confusion here is in terms of (1) recognizing a temporal
literal and "parsing it" and (2) applying it to SQL.  Different parts of
the puzzle.

HTH