[hibernate-dev] 6.0 - HQL literals

[Yoann Rodiere]

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.

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.


As far as timezones, a datetime with no timezone is interpreted as a
> LocalDateTime.  However I did have an open question there still regarding
> *which* local - the local VM's timezone?  Or the "JDBC timezone" (which we
> know via our `hibernate.jdbc.time_zone` setting)?
>

Unless I misunderstood, if you use the appropriate org.hibernate.type.Type,
the selection of a timezone is already handled by the implementation, which
will indeed use the "hibernate.jdbc.time_zone" setting if necessary, or the
local VM's timezone otherwise.


  The literals can also contain zone id or offset.  I choose to not except
> the form with 'T'.  E.g., all of these are valid:
>
>    - ...
>
> 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.


* 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.



> I don't understand the "broad range of Java types ..." part.  What do you
> mean?  Do you have an example?
>

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.