Re: [hibernate-dev] 6.0 - HQL literals
> > 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. > It seems like the proper syntax for that would actually be "2020-01-01 10:10:10+02:00 America/Chicago", but in my testing DateTimeFormatter#parseBest did not handle that form either ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] 6.0 - HQL literals
On Tue, Jan 7, 2020 at 10:05 AM Yoann Rodiere wrote:
> On Tue, 7 Jan 2020 at 14:45, Steve Ebersole 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
Re: [hibernate-dev] 6.0 - HQL literals
On Tue, 7 Jan 2020 at 14:45, Steve Ebersole 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.
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] 6.0 - HQL literals
Thanks for the feedback.
I added some comments in-line.
On Tue, Jan 7, 2020 at 1:59 AM Yoann Rodiere 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.
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
