[hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

Thanks for the feedback.

I added some comments in-line.

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

> Hi,
>
> The syntax looks nice.
> I suppose it's future-proof enough, though I can imagine us getting in
> trouble if JDBC starts allowing parameterized or custom formats, which may
> start with a digit or even (in edge cases) look like a date. That seems
> unlikely, so I think it's an acceptable risk.
>
> I'm not entirely sure allowing JDBC literals to be "passed-through" the
> HQL will always be intuitive, even if it's already allowed for integers:
> under some circumstances we map date-only or time-only types to timestamps,
> for example. The database might cast a date-only value to a timestamp
> automatically by setting hours/minutes/etc to zero, but I'm not sure that's
> what *we* do when persisting, considering the various hacks we have around
> timezones; '2019-01-01' might very well be converted to
> '2018-12-31T23:00:00', for all I know. As a result, there might be a broad
> range of Java types where these literals will be seen as "buggy".
>

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

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)?  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:

   - {2020-01-01 10:10:10} - LocalDateTime
   - {2020-01-01 10:10:10 UTC} - ZonedDateTime
   - {2020-01-01 10:10:10 Z} - ZonedDateTime
   - {2020-01-01 10:10:10 +2} - ZonedDateTime
   - {2020-01-01 10:10:10 +02} - ZonedDateTime
   - {2020-01-01 10:10:10 +02:00} - ZonedDateTime
   - {2020-01-01 10:10:10 UTC+02:00} - ZonedDateTime
   - {2020-01-01 10:10:10 CST} - ZonedDateTime
   - {2020-01-01 10:10:10 America/Central} - ZonedDateTime
   - etc

* 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.  I mention this because I tried to make interpreting the syntactic
form consistent.  If you are interested in the specifics of how this
happens, see:

   1. org.hibernate.query.hql.internal.SemanticQueryBuilder#interpretTemporalLiteral
   (handles syntactic forms)
   2. org.hibernate.type.descriptor.DateTimeUtils#DATE_TIME (handles String
   forms)


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


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.