11:56 p.m.
Initially when I started working on 6.0 I added support for defining
temporal literals using JDBC's "escape syntax". JDBC already defines a
syntax for declaring temporal literals using this syntax -
- {d '2020-01-01'} for a Date
- {t '10:10:10'} for a Time
- {ts '2020-01-01 10:10:10'} for a Timestamp
I had planned on using this syntax to define generalized support for adding
new types of literals using the "prefix" which is the first identifier
after the open brace. I did not have any concrete plans for specific types
of literals, although I was hoping this would fit with hibernate-spatial
needs.
Since temporal values are so common I added another simplified form:
- {2020-01-01} for a "Date"
- {10:10:10} for a "Time"
- {2020-01-01 10:10:10} for a "Timestamp"
Notice first the absence of quotes for these. The patterns is defined as a
syntactic element of the grammar (thanks to Gavin King for this particular
idea)[1].
As a side note, I first tried to use back-ticks for these simplified
temporal literals rather than braces but that conflicts with the
`QUOTED_IDENTIFIER` lexer rule. `QUOTED_IDENTIFIER` is never used (and
really kind of meaningless in the HQL grammar) so one option would be to
remove that rule and use the back-ticks for these literals like
`2020-01-01` as opposed to {2020-01-01} if folks like that better.
It's also important to note that I actually use the Java 8 temporal types
internally to represent these simplified literals because it is easy to
translate from one type to another starting from these types.
Anyone have objections or suggestions regarding any of this?
[1] -
https://github.com/hibernate/hibernate-orm/blob/eab6107ec2e7b3a0c06146a9f...
4:59 p.m.
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".
The solution would be to support litterals for the *java* types and
converting them to the correct SQL type automatically depending on the
context, but that will be a hassle, if even possible at all.
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...
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Mon, 6 Jan 2020 at 15:57, Steve Ebersole <steve(a)hibernate.org> wrote:
Initially when I started working on 6.0 I added support for defining
temporal literals using JDBC's "escape syntax". JDBC already defines a
syntax for declaring temporal literals using this syntax -
- {d '2020-01-01'} for a Date
- {t '10:10:10'} for a Time
- {ts '2020-01-01 10:10:10'} for a Timestamp
I had planned on using this syntax to define generalized support for adding
new types of literals using the "prefix" which is the first identifier
after the open brace. I did not have any concrete plans for specific types
of literals, although I was hoping this would fit with hibernate-spatial
needs.
Since temporal values are so common I added another simplified form:
- {2020-01-01} for a "Date"
- {10:10:10} for a "Time"
- {2020-01-01 10:10:10} for a "Timestamp"
Notice first the absence of quotes for these. The patterns is defined as a
syntactic element of the grammar (thanks to Gavin King for this particular
idea)[1].
As a side note, I first tried to use back-ticks for these simplified
temporal literals rather than braces but that conflicts with the
`QUOTED_IDENTIFIER` lexer rule. `QUOTED_IDENTIFIER` is never used (and
really kind of meaningless in the HQL grammar) so one option would be to
remove that rule and use the back-ticks for these literals like
`2020-01-01` as opposed to {2020-01-01} if folks like that better.
It's also important to note that I actually use the Java 8 temporal types
internally to represent these simplified literals because it is easy to
translate from one type to another starting from these types.
Anyone have objections or suggestions regarding any of this?
[1] -
https://github.com/hibernate/hibernate-orm/blob/eab6107ec2e7b3a0c06146a9f...
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
10:45 p.m.
Thanks for the feedback.
I added some comments in-line.
On Tue, Jan 7, 2020 at 1:59 AM Yoann Rodiere <yoann(a)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.
1:05 a.m.
On Tue, 7 Jan 2020 at 14:45, Steve Ebersole <steve(a)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.
3:47 a.m.
On Tue, Jan 7, 2020 at 10:05 AM Yoann Rodiere <yoann(a)hibernate.org> wrote:
On Tue, 7 Jan 2020 at 14:45, Steve Ebersole
<steve(a)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
3:50 a.m.
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
7:21 p.m.
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 trust you can build a temporal correctly from a string. I'm more
concerned about passing that information to the JDBC driver through a
parameter, or even directly to the database through an SQL literal. Last
time I checked you had to use java.sql types to pass temporal parameters to
JDBC drivers, so you will have to convert the java.time value to a
java.sql.Timestamp or similar eventually. And *that* is much more tricky
that I, at least, originally thought.
Among other quirks:
- creating a Timestamp from a year/day/etc. assumes the given
year/day/etc. are in the default JVM timezone.
- the JDBC driver will sometimes extract the year/day/etc. and interpret
them as being in the DB timezone, or will sometimes use a DateFormat with a
timezone to convert it to the correct timezone. It depends on the driver
and even the version of the driver.
- java.sql.Timestamp and java.time do not rely on the same calendar:
Julian/Gregorian calendar for one, proleptic Gregorian calendar for the
other.
- java.sql.Timestamp and java.time do not assume the same offsets for
various zone IDs around and before 1900, when time zones were not a
formalized concept.
I've spent days on conversion problems between java.time and java.sql in
ORM over the last few months.
Which is why I think using LocalDateTimeType to convert between the
LocalDateTime literal and the Timestamp would be a good idea. If you want
to rewrite that code for literals, sure that can work, but exhaustive
testing will be needed.
As counter-intuitive as it sounds, a ZonedDateTime actually includes
an
offset to differentiate the overlap case you mention.
Yep. That's why it accepts parsing a ZoneDateTime with both a zone ID and
an offset. Try this:
https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
Note that a bug affects parsing ZoneDateTimes with both offset and zone ID
on JDK8 (fixed in 9): https://bugs.openjdk.java.net/browse/JDK-8066982
We have a helper to work around that in Search:
https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5db...
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.
Yep.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve(a)hibernate.org> wrote:
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
8:37 p.m.
Can't we just render a literal in the DBMS proprietary way as literal again
but with respective TZ information to avoid TZ issues in the JDBC driver?
If we use an instant literal we use the UTC TZ in a SQL literal or function
to render that.
Yoann Rodiere <yoann(a)hibernate.org> schrieb am Mi., 8. Jän. 2020, 11:22:
> 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 trust you can build a temporal correctly from a string. I'm more
concerned about passing that information to the JDBC driver through a
parameter, or even directly to the database through an SQL literal. Last
time I checked you had to use java.sql types to pass temporal parameters to
JDBC drivers, so you will have to convert the java.time value to a
java.sql.Timestamp or similar eventually. And *that* is much more tricky
that I, at least, originally thought.
Among other quirks:
- creating a Timestamp from a year/day/etc. assumes the given
year/day/etc. are in the default JVM timezone.
- the JDBC driver will sometimes extract the year/day/etc. and interpret
them as being in the DB timezone, or will sometimes use a DateFormat
with a
timezone to convert it to the correct timezone. It depends on the driver
and even the version of the driver.
- java.sql.Timestamp and java.time do not rely on the same calendar:
Julian/Gregorian calendar for one, proleptic Gregorian calendar for the
other.
- java.sql.Timestamp and java.time do not assume the same offsets for
various zone IDs around and before 1900, when time zones were not a
formalized concept.
I've spent days on conversion problems between java.time and java.sql in
ORM over the last few months.
Which is why I think using LocalDateTimeType to convert between the
LocalDateTime literal and the Timestamp would be a good idea. If you want
to rewrite that code for literals, sure that can work, but exhaustive
testing will be needed.
> As counter-intuitive as it sounds, a ZonedDateTime actually includes an
offset to differentiate the overlap case you mention.
Yep. That's why it accepts parsing a ZoneDateTime with both a zone ID and
an offset. Try this:
https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
Note that a bug affects parsing ZoneDateTimes with both offset and zone ID
on JDK8 (fixed in 9): https://bugs.openjdk.java.net/browse/JDK-8066982
We have a helper to work around that in Search:
https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5db...
> 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.
Yep.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve(a)hibernate.org> wrote:
> 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(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
8:56 p.m.
Can't we just render a literal in the DBMS proprietary way as
literal
again
but with respective TZ information to avoid TZ issues in the JDBC
driver?
This may be an option. We would need to take into account the default JVM
timezone, or the "jdbc_timezone" setting, to set the correct offset.
However, I believe some JDBC drivers (MariaDB?) have options to convert
between the JVM timezone and the DB timezone, because apparently some DBs
assume the timezone of date/time values to be something else than UTC. Not
sure SQL literals would be interpreted correctly in such scenarios.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Wed, 8 Jan 2020 at 12:41, Christian Beikov <christian.beikov(a)gmail.com>
wrote:
> Can't we just render a literal in the DBMS proprietary way as literal again
but with respective TZ information to avoid TZ issues in the JDBC
driver?
>
> If we use an instant literal we use the UTC TZ in a SQL literal or function
> to render that.
>
> Yoann Rodiere <yoann(a)hibernate.org> schrieb am Mi., 8. Jän. 2020, 11:22:
>
> > > 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 trust you can build a temporal correctly from a string. I'm more
> > concerned about passing that information to the JDBC driver through a
> > parameter, or even directly to the database through an SQL literal. Last
> > time I checked you had to use java.sql types to pass temporal parameters
> to
> > JDBC drivers, so you will have to convert the java.time value to a
> > java.sql.Timestamp or similar eventually. And *that* is much more tricky
> > that I, at least, originally thought.
> >
> > Among other quirks:
> >
> > - creating a Timestamp from a year/day/etc. assumes the given
> > year/day/etc. are in the default JVM timezone.
> > - the JDBC driver will sometimes extract the year/day/etc. and
> interpret
> > them as being in the DB timezone, or will sometimes use a DateFormat
> > with a
> > timezone to convert it to the correct timezone. It depends on the
> driver
> > and even the version of the driver.
> > - java.sql.Timestamp and java.time do not rely on the same calendar:
> > Julian/Gregorian calendar for one, proleptic Gregorian calendar for
> the
> > other.
> > - java.sql.Timestamp and java.time do not assume the same offsets for
> > various zone IDs around and before 1900, when time zones were not a
> > formalized concept.
> >
> > I've spent days on conversion problems between java.time and java.sql in
> > ORM over the last few months.
> >
> > Which is why I think using LocalDateTimeType to convert between the
> > LocalDateTime literal and the Timestamp would be a good idea. If you want
> > to rewrite that code for literals, sure that can work, but exhaustive
> > testing will be needed.
> >
> > > As counter-intuitive as it sounds, a ZonedDateTime actually includes an
> > offset to differentiate the overlap case you mention.
> >
> > Yep. That's why it accepts parsing a ZoneDateTime with both a zone ID and
> > an offset. Try this:
> > https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
> >
> > Note that a bug affects parsing ZoneDateTimes with both offset and zone
> ID
> > on JDK8 (fixed in 9): https://bugs.openjdk.java.net/browse/JDK-8066982
> > We have a helper to work around that in Search:
> >
> >
>
https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5db...
> >
> > > 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.
> >
> > Yep.
> >
> > Yoann Rodière
> > Hibernate Team
> > yoann(a)hibernate.org
> >
> >
> > On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve(a)hibernate.org> wrote:
> >
> > > 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(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
9:08 p.m.
If a user enters a HQL literal, that user wants the literal to be
rendered like that if possible(which should always be possible).
The only thing we have to define is whether the literal is by default in
the JVM TZ, JDBC TZ or UTC. We could offer syntax variants that default
to UTC etc.
Not sure what makes sense, even if I like UTC more, to me it feels like
the default should be using the JDBC TZ(which by default is the JVM TZ)
and offer a dedicated literal syntax for the UTC variant as well as
support for specifying the TZ explicitly.
Am 08.01.2020 um 12:56 schrieb Yoann Rodiere:
> Can't we just render a literal in the DBMS proprietary way
as literal again
> but with respective TZ information to avoid TZ issues in the JDBC driver?
This may be an option. We would need to take into account the default
JVM timezone, or the "jdbc_timezone" setting, to set the correct offset.
However, I believe some JDBC drivers (MariaDB?) have options to
convert between the JVM timezone and the DB timezone, because
apparently some DBs assume the timezone of date/time values to be
something else than UTC. Not sure SQL literals would be interpreted
correctly in such scenarios.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org <mailto:yoann@hibernate.org>
On Wed, 8 Jan 2020 at 12:41, Christian Beikov
<christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
Can't we just render a literal in the DBMS proprietary way as
literal again
but with respective TZ information to avoid TZ issues in the JDBC
driver?
If we use an instant literal we use the UTC TZ in a SQL literal or
function
to render that.
Yoann Rodiere <yoann(a)hibernate.org <mailto:yoann@hibernate.org>>
schrieb am Mi., 8. Jän. 2020, 11:22:
> > 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 trust you can build a temporal correctly from a string. I'm more
> concerned about passing that information to the JDBC driver
through a
> parameter, or even directly to the database through an SQL
literal. Last
> time I checked you had to use java.sql types to pass temporal
parameters to
> JDBC drivers, so you will have to convert the java.time value to a
> java.sql.Timestamp or similar eventually. And *that* is much
more tricky
> that I, at least, originally thought.
>
> Among other quirks:
>
> - creating a Timestamp from a year/day/etc. assumes the given
> year/day/etc. are in the default JVM timezone.
> - the JDBC driver will sometimes extract the year/day/etc.
and interpret
> them as being in the DB timezone, or will sometimes use a
DateFormat
> with a
> timezone to convert it to the correct timezone. It depends on
the driver
> and even the version of the driver.
> - java.sql.Timestamp and java.time do not rely on the same
calendar:
> Julian/Gregorian calendar for one, proleptic Gregorian
calendar for the
> other.
> - java.sql.Timestamp and java.time do not assume the same
offsets for
> various zone IDs around and before 1900, when time zones were
not a
> formalized concept.
>
> I've spent days on conversion problems between java.time and
java.sql in
> ORM over the last few months.
>
> Which is why I think using LocalDateTimeType to convert between the
> LocalDateTime literal and the Timestamp would be a good idea. If
you want
> to rewrite that code for literals, sure that can work, but
exhaustive
> testing will be needed.
>
> > As counter-intuitive as it sounds, a ZonedDateTime actually
includes an
> offset to differentiate the overlap case you mention.
>
> Yep. That's why it accepts parsing a ZoneDateTime with both a
zone ID and
> an offset. Try this:
> https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
>
> Note that a bug affects parsing ZoneDateTimes with both offset
and zone ID
> on JDK8 (fixed in 9):
https://bugs.openjdk.java.net/browse/JDK-8066982
> We have a helper to work around that in Search:
>
>
https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5db...
>
> > 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.
>
> Yep.
>
> Yoann Rodière
> Hibernate Team
> yoann(a)hibernate.org <mailto:yoann@hibernate.org>
>
>
> On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve(a)hibernate.org
<mailto:steve@hibernate.org>> wrote:
>
> > 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(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org>
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/hibernate-dev
9:37 p.m.
On Wed, Jan 8, 2020 at 4:21 AM Yoann Rodiere <yoann(a)hibernate.org> wrote:
> 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 trust you can build a temporal correctly from a string. I'm more
concerned about passing that information to the JDBC driver through a
parameter, or even directly to the database through an SQL literal. Last
time I checked you had to use java.sql types to pass temporal parameters to
JDBC drivers, so you will have to convert the java.time value to a
java.sql.Timestamp or similar eventually. And *that* is much more tricky
that I, at least, originally thought.
Not sure why we keep coming back to how the literal will be used in JDBC.
Again, this topic is about HQL parsing.
Yes, handling temporal values at the JDBC/SQL level can be very tricky.
That's true however whether that temporal value is an HQL literal or an
attribute value.
And its just a completely different topic overall.
9:44 p.m.
Actually Java and JDBC say that all of these Java 8 temporal types are
valid to pass via JDBC. It's done "implicitly" via the various
`#setObject` methods (as opposed to `#setTimestamp` e.g.).
Part of the problem here (6 and before) at the moment is that our
LocalDateTimeType, ZonedDateTimeType, etc types use the JDBC Time, Date and
Timestamp SQL mappings rather than SQL type descriptors that simply use
`#setObject`
On Wed, Jan 8, 2020 at 5:57 AM Yoann Rodiere <yoann(a)hibernate.org> wrote:
> Can't we just render a literal in the DBMS proprietary way
as literal
again
> but with respective TZ information to avoid TZ issues in the JDBC driver?
This may be an option. We would need to take into account the default JVM
timezone, or the "jdbc_timezone" setting, to set the correct offset.
However, I believe some JDBC drivers (MariaDB?) have options to convert
between the JVM timezone and the DB timezone, because apparently some DBs
assume the timezone of date/time values to be something else than UTC. Not
sure SQL literals would be interpreted correctly in such scenarios.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Wed, 8 Jan 2020 at 12:41, Christian Beikov <christian.beikov(a)gmail.com>
wrote:
> Can't we just render a literal in the DBMS proprietary way as literal
again
> but with respective TZ information to avoid TZ issues in the JDBC driver?
>
> If we use an instant literal we use the UTC TZ in a SQL literal or
function
> to render that.
>
> Yoann Rodiere <yoann(a)hibernate.org> schrieb am Mi., 8. Jän. 2020, 11:22:
>
> > > 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 trust you can build a temporal correctly from a string. I'm more
> > concerned about passing that information to the JDBC driver through a
> > parameter, or even directly to the database through an SQL literal.
Last
> > time I checked you had to use java.sql types to pass temporal
parameters
> to
> > JDBC drivers, so you will have to convert the java.time value to a
> > java.sql.Timestamp or similar eventually. And *that* is much more
tricky
> > that I, at least, originally thought.
> >
> > Among other quirks:
> >
> > - creating a Timestamp from a year/day/etc. assumes the given
> > year/day/etc. are in the default JVM timezone.
> > - the JDBC driver will sometimes extract the year/day/etc. and
> interpret
> > them as being in the DB timezone, or will sometimes use a DateFormat
> > with a
> > timezone to convert it to the correct timezone. It depends on the
> driver
> > and even the version of the driver.
> > - java.sql.Timestamp and java.time do not rely on the same calendar:
> > Julian/Gregorian calendar for one, proleptic Gregorian calendar for
> the
> > other.
> > - java.sql.Timestamp and java.time do not assume the same offsets
for
> > various zone IDs around and before 1900, when time zones were not a
> > formalized concept.
> >
> > I've spent days on conversion problems between java.time and java.sql
in
> > ORM over the last few months.
> >
> > Which is why I think using LocalDateTimeType to convert between the
> > LocalDateTime literal and the Timestamp would be a good idea. If you
want
> > to rewrite that code for literals, sure that can work, but exhaustive
> > testing will be needed.
> >
> > > As counter-intuitive as it sounds, a ZonedDateTime actually includes
an
> > offset to differentiate the overlap case you mention.
> >
> > Yep. That's why it accepts parsing a ZoneDateTime with both a zone ID
and
> > an offset. Try this:
> > https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
> >
> > Note that a bug affects parsing ZoneDateTimes with both offset and zone
> ID
> > on JDK8 (fixed in 9): https://bugs.openjdk.java.net/browse/JDK-8066982
> > We have a helper to work around that in Search:
> >
> >
>
https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5db...
> >
> > > 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.
> >
> > Yep.
> >
> > Yoann Rodière
> > Hibernate Team
> > yoann(a)hibernate.org
> >
> >
> > On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve(a)hibernate.org>
wrote:
> >
> > > 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(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
9:50 p.m.
On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov <christian.beikov(a)gmail.com>
wrote:
If a user enters a HQL literal, that user wants the literal to be
rendered like that if possible(which should always be possible).
Like I said earlier, we actually try to render literals (of any type) as
parameters whenever we can (which is almost always possible)
The only thing we have to define is whether the literal is by default in
the JVM TZ, JDBC TZ or UTC. We could offer syntax variants that
default
to UTC etc.
Not sure what makes sense, even if I like UTC more, to me it feels
like
the default should be using the JDBC TZ(which by default is the JVM TZ)
and offer a dedicated literal syntax for the UTC variant as well as
support for specifying the TZ explicitly.
The literal can define the zone if they want control. I think that is
enough. Conceptually that is what "JDBC time zone" is supposed to mean
exactly what we are discussing here, right?
10:09 p.m.
Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov
<christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
If a user enters a HQL literal, that user wants the literal to be
rendered like that if possible(which should always be possible).
Like I said earlier, we actually try to render literals (of any type)
as parameters whenever we can (which is almost always possible)
IMO this should be configurable and I personally would prefer rendering
as SQL literal as I'd argue people would generally only use a literal if
they really want a literal i.e. not append a literal in a dynamic way
like `"FROM Entity e WHERE e.active = " + someBoolean`. Such a query
might be better off with a parameter. Why do you prefer rendering
literals as parameter?
Most DBMS can better optimize a query plan when encountering literals
vs. parameters e.g. choose a partial index or have better estimates. The
trade-off is performance for possibly "worse" statement cache hit rate.
I'd argue people would just use parameters if they want possible
statement sharing or better caching.
The only thing we have to define is whether the literal is by
default in
the JVM TZ, JDBC TZ or UTC. We could offer syntax variants that
default
to UTC etc.
Not sure what makes sense, even if I like UTC more, to me it feels
like
the default should be using the JDBC TZ(which by default is the
JVM TZ)
and offer a dedicated literal syntax for the UTC variant as well as
support for specifying the TZ explicitly.
The literal can define the zone if they want control. I think that is
enough. Conceptually that is what "JDBC time zone" is supposed to mean
exactly what we are discussing here, right?
Right
10:47 p.m.
On Wed, Jan 8, 2020 at 7:11 AM Christian Beikov <christian.beikov(a)gmail.com>
wrote:
Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
> On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov
> <christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
>
> If a user enters a HQL literal, that user wants the literal to be
> rendered like that if possible(which should always be possible).
>
>
> Like I said earlier, we actually try to render literals (of any type)
> as parameters whenever we can (which is almost always possible)
IMO this should be configurable and I personally would prefer rendering
as SQL literal as I'd argue people would generally only use a literal if
they really want a literal i.e. not append a literal in a dynamic way
like `"FROM Entity e WHERE e.active = " + someBoolean`. Such a query
might be better off with a parameter. Why do you prefer rendering
literals as parameter?
It is controllable. Actually this part is not new to 6. We added that
previously. Vlad pulled it back from 6 into 5 back, although unfortunately
put it in a bad package and for some reason chose to name it differently.
So 5.x has `org.hibernate.query.criteria.LiteralHandlingMode`. 6.0
deprecates that in favor of
`org.hibernate.query.QueryLiteralRendering`
I'm not sure I'd classify all usages of literals as you do. I think there
are a fair amount of people who do it simply because its easier and more
concise.
Most DBMS can better optimize a query plan when encountering literals
vs. parameters e.g. choose a partial index or have better estimates.
The
trade-off is performance for possibly "worse" statement cache hit rate.
I'd argue people would just use parameters if they want possible
statement sharing or better caching.
Exactly, although a few additional thoughts:
1. Passing along the literals as-is means possibly having SQL injection
issues. We'd really need to validate the content of the literal value.
Parameters are safe from such injection attacks.
2. The cases where the database optimizer can build a "better" plan
using a literal versus a parameter is generally pretty small. I can think
of partition columns, tenancy columns - things of that nature. You
think its more broad?
12:21 a.m.
Am 08.01.2020 um 14:47 schrieb Steve Ebersole:
On Wed, Jan 8, 2020 at 7:11 AM Christian Beikov
<christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
> On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov
> <christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>
<mailto:christian.beikov@gmail.com
<mailto:christian.beikov@gmail.com>>> wrote:
>
> If a user enters a HQL literal, that user wants the literal
to be
> rendered like that if possible(which should always be possible).
>
>
> Like I said earlier, we actually try to render literals (of any
type)
> as parameters whenever we can (which is almost always possible)
IMO this should be configurable and I personally would prefer
rendering
as SQL literal as I'd argue people would generally only use a
literal if
they really want a literal i.e. not append a literal in a dynamic way
like `"FROM Entity e WHERE e.active = " + someBoolean`. Such a query
might be better off with a parameter. Why do you prefer rendering
literals as parameter?
It is controllable. Actually this part is not new to 6. We added that
previously. Vlad pulled it back from 6 into 5 back, although
unfortunately put it in a bad package and for some reason chose to
name it differently. So 5.x has
`org.hibernate.query.criteria.LiteralHandlingMode`. 6.0 deprecates
that in favor of
`org.hibernate.query.QueryLiteralRendering`
This is about JPA Criteria whereas I was referring to how a literal in
HQL ends up in the SQL. I agree that with JPA Criteria things are
different as the API for parameters in queries is not very neat. A flag
for controlling how literals should be rendered in for JPA Criteria
sounds ok, but ultimately, a literal should be just that, a literal.
I'm not sure I'd classify all usages of literals as you do.
I think
there are a fair amount of people who do it simply because its easier
and more concise.
Most DBMS can better optimize a query plan when encountering literals
vs. parameters e.g. choose a partial index or have better
estimates. The
trade-off is performance for possibly "worse" statement cache hit
rate.
I'd argue people would just use parameters if they want possible
statement sharing or better caching.
Exactly, although a few additional thoughts:
1. Passing along the literals as-is means possibly having SQL
injection issues. We'd really need to validate the content of the
literal value. Parameters are safe from such injection attacks.
I agree that we have to take care and do proper escaping, but since we
have type literals, we only have to be careful about strings. The
escaping is simple to implement as it requires to just prepend a literal
single quote before a single quote. So a value like `abc's bar` would
become the following SQL literal `'abc''s bar'`.
1. The cases where the database optimizer can build a "better" plan
using a literal versus a parameter is generally pretty small. I
can think of partition columns, tenancy columns - things of that
nature. You think its more broad?
If the selectivity estimates for a parameter are low, and in bigger
queries such wrong estimates multiply during row count estimation, this
can lead to pretty bad plans.
4:21 a.m.
On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov <christian.beikov(a)gmail.com>
wrote:
This is about JPA Criteria whereas I was referring to how a literal in
HQL ends up in the SQL. I agree that with JPA Criteria things are
different as the API for parameters in queries is not very neat. A flag
for controlling how literals should be rendered in for JPA Criteria
sounds ok, but ultimately, a literal should be just that, a literal.
Well no - its about how literals defined in ORM queries should be handled
in JDBC. The HQL / criteria distinction is not important here.
We'll agree to disagree that query literals should always be rendered as
SQL literals.
4:28 p.m.
Am 08.01.2020 um 20:21 schrieb Steve Ebersole:
On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov
<christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
This is about JPA Criteria whereas I was referring to how a
literal in
HQL ends up in the SQL. I agree that with JPA Criteria things are
different as the API for parameters in queries is not very neat. A
flag
for controlling how literals should be rendered in for JPA Criteria
sounds ok, but ultimately, a literal should be just that, a literal.
Well no - its about how literals defined in ORM queries should be
handled in JDBC. The HQL / criteria distinction is not important here.
We'll agree to disagree that query literals should always be rendered
as SQL literals.
I got that you are concerned with the security aspect and think that the
DBMS optimizer will not be able to build significantly better plans than
with parameters. Is there any other reason why you prefer rendering
literals as JDBC parameter by default?
Since string literals in JPQL/HQL are defined syntactically like in SQL,
the security part sounds managable to me as other (currently existing)
literals are non-problematic due to their nature i.e. being numeric or
strictly typed such the representation can't interferre with the SQL
they are embedded into.
The performance part is for me actually very critical. I attached a few
links[1][2][3][4] to articles that cover this topic. Essentially, the
difference in execution plans come from wrong row count estimates
because of wrong selectivity. When using parameters, DBMS do a few
tricks to overcome the problems of unknown selectivity due to a missing
value. Oracle is pretty good at that AFAIU, but the behavior is
configurable and we might run in an environment where it isn't configure
how we'd expect it to be.
PostgreSQL seems to use a generic plan(with wrong selectivity estimates)
after the 5th execution and other DBMS probably have similar problems.
In general, using literals is imporant when there are only a few values
or when the row distribution is non-uniform. The problem gets big when a
query has lots of joins that multiply to the wrong estimates, leading to
e.g. full table scans rather than index scans.
[1]
http://davetechnotes.blogspot.com/2009/02/literal-vs-bind-variables-bind-...
[2]
https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be49...
[3]
https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for...
[4]
https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-...
9:06 p.m.
Well, again, this is configurable. If you don't want to use it, don't ;)
On Thu, Jan 9, 2020 at 1:28 AM Christian Beikov <christian.beikov(a)gmail.com>
wrote:
Am 08.01.2020 um 20:21 schrieb Steve Ebersole:
On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov <
christian.beikov(a)gmail.com> wrote:
>
> This is about JPA Criteria whereas I was referring to how a literal in
> HQL ends up in the SQL. I agree that with JPA Criteria things are
> different as the API for parameters in queries is not very neat. A flag
> for controlling how literals should be rendered in for JPA Criteria
> sounds ok, but ultimately, a literal should be just that, a literal.
>
Well no - its about how literals defined in ORM queries should be handled
in JDBC. The HQL / criteria distinction is not important here.
We'll agree to disagree that query literals should always be rendered as
SQL literals.
I got that you are concerned with the security aspect and think that the
DBMS optimizer will not be able to build significantly better plans than
with parameters. Is there any other reason why you prefer rendering
literals as JDBC parameter by default?
Since string literals in JPQL/HQL are defined syntactically like in SQL,
the security part sounds managable to me as other (currently existing)
literals are non-problematic due to their nature i.e. being numeric or
strictly typed such the representation can't interferre with the SQL they
are embedded into.
The performance part is for me actually very critical. I attached a few
links[1][2][3][4] to articles that cover this topic. Essentially, the
difference in execution plans come from wrong row count estimates because
of wrong selectivity. When using parameters, DBMS do a few tricks to
overcome the problems of unknown selectivity due to a missing value. Oracle
is pretty good at that AFAIU, but the behavior is configurable and we might
run in an environment where it isn't configure how we'd expect it to be.
PostgreSQL seems to use a generic plan(with wrong selectivity estimates)
after the 5th execution and other DBMS probably have similar problems. In
general, using literals is imporant when there are only a few values or
when the row distribution is non-uniform. The problem gets big when a query
has lots of joins that multiply to the wrong estimates, leading to e.g.
full table scans rather than index scans.
[1]
http://davetechnotes.blogspot.com/2009/02/literal-vs-bind-variables-bind-...
[2]
https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be49...
[3]
https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for...
[4]
https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-...
11:10 p.m.
Ok, just wanted to make sure I didn't miss anything and understand the
reasoning for your preference :)
Am 09.01.2020 um 13:06 schrieb Steve Ebersole:
Well, again, this is configurable. If you don't want to use it,
don't ;)
On Thu, Jan 9, 2020 at 1:28 AM Christian Beikov
<christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>> wrote:
Am 08.01.2020 um 20:21 schrieb Steve Ebersole:
> On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov
> <christian.beikov(a)gmail.com <mailto:christian.beikov@gmail.com>>
> wrote:
>
>
> This is about JPA Criteria whereas I was referring to how a
> literal in
> HQL ends up in the SQL. I agree that with JPA Criteria things
> are
> different as the API for parameters in queries is not very
> neat. A flag
> for controlling how literals should be rendered in for JPA
> Criteria
> sounds ok, but ultimately, a literal should be just that, a
> literal.
>
>
> Well no - its about how literals defined in ORM queries should be
> handled in JDBC. The HQL / criteria distinction is not important
> here.
>
> We'll agree to disagree that query literals should always be
> rendered as SQL literals.
I got that you are concerned with the security aspect and think
that the DBMS optimizer will not be able to build significantly
better plans than with parameters. Is there any other reason why
you prefer rendering literals as JDBC parameter by default?
Since string literals in JPQL/HQL are defined syntactically like
in SQL, the security part sounds managable to me as other
(currently existing) literals are non-problematic due to their
nature i.e. being numeric or strictly typed such the
representation can't interferre with the SQL they are embedded into.
The performance part is for me actually very critical. I attached
a few links[1][2][3][4] to articles that cover this topic.
Essentially, the difference in execution plans come from wrong row
count estimates because of wrong selectivity. When using
parameters, DBMS do a few tricks to overcome the problems of
unknown selectivity due to a missing value. Oracle is pretty good
at that AFAIU, but the behavior is configurable and we might run
in an environment where it isn't configure how we'd expect it to be.
PostgreSQL seems to use a generic plan(with wrong selectivity
estimates) after the 5th execution and other DBMS probably have
similar problems. In general, using literals is imporant when
there are only a few values or when the row distribution is
non-uniform. The problem gets big when a query has lots of joins
that multiply to the wrong estimates, leading to e.g. full table
scans rather than index scans.
[1]
http://davetechnotes.blogspot.com/2009/02/literal-vs-bind-variables-bind-...
[2]
https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be49...
[3]
https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for...
[4]
https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-...
1:22 a.m.
Hi,
As one of the maintainers of the postgres jdbc driver I am interested in
this discussion.
Postgres only stores date/times in UTC. Everything else is a translation.
The driver uses the client's timezone for all dates/times (for better or
worse) If there is anything I can do to help make things easier, let me
know.
--
Sent from: http://hibernate-development.74578.x6.nabble.com/
1:27 p.m.
Sorry Yoann, it was a crazy week and I missed this reply somehow...
Yes, you did an awesome job with setting up tests. We will obviously make
sure those continue to work as we move forward.
I did mention in the original email (I think anyway, I meant to) that there
seemed to be a problem in parsing with Java 8 in certain scenarios. I
found this via a SO post asking why something was not working the way I
expected. The answer was that there is a bug in Java 8, but that parseBest
seems to work consistently across both versions. We plan to talk about
moving to Java 9 as a base anyway so this may not be an issue either way we
decide to go regarding parse or parseBest.
On Wed, Jan 8, 2020 at 4:21 AM Yoann Rodiere <yoann(a)hibernate.org> wrote:
> 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 trust you can build a temporal correctly from a string. I'm more
concerned about passing that information to the JDBC driver through a
parameter, or even directly to the database through an SQL literal. Last
time I checked you had to use java.sql types to pass temporal parameters to
JDBC drivers, so you will have to convert the java.time value to a
java.sql.Timestamp or similar eventually. And *that* is much more tricky
that I, at least, originally thought.
Among other quirks:
- creating a Timestamp from a year/day/etc. assumes the given
year/day/etc. are in the default JVM timezone.
- the JDBC driver will sometimes extract the year/day/etc. and
interpret them as being in the DB timezone, or will sometimes use a
DateFormat with a timezone to convert it to the correct timezone. It
depends on the driver and even the version of the driver.
- java.sql.Timestamp and java.time do not rely on the same calendar:
Julian/Gregorian calendar for one, proleptic Gregorian calendar for the
other.
- java.sql.Timestamp and java.time do not assume the same offsets for
various zone IDs around and before 1900, when time zones were not a
formalized concept.
I've spent days on conversion problems between java.time and java.sql in
ORM over the last few months.
Which is why I think using LocalDateTimeType to convert between the
LocalDateTime literal and the Timestamp would be a good idea. If you want
to rewrite that code for literals, sure that can work, but exhaustive
testing will be needed.
> As counter-intuitive as it sounds, a ZonedDateTime actually includes an
offset to differentiate the overlap case you mention.
Yep. That's why it accepts parsing a ZoneDateTime with both a zone ID and
an offset. Try this:
https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
Note that a bug affects parsing ZoneDateTimes with both offset and zone ID
on JDK8 (fixed in 9): https://bugs.openjdk.java.net/browse/JDK-8066982
We have a helper to work around that in Search:
https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5db...
> 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.
Yep.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve(a)hibernate.org> wrote:
> 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
>
>
1:35 p.m.
Hi Dave.
Same - I was swamped with stuff at the end of last week.
Yes, from what I was reading postgres is a bit strange in storing temporal
values. Not unique to postgres - many databases do interesting things.
I'm curious how the driver handles binding Java 8 types directly. The JDBC
spec was updated to support these types through the generic `#setObject`
methods (`#getObject` as well?). Does the driver handle this.
Out of curiosity, which jdbc driver are you helping with?
On Thu, Jan 9, 2020 at 10:23 AM Dave Cramer <davecramer(a)gmail.com> wrote:
Hi,
As one of the maintainers of the postgres jdbc driver I am interested in
this discussion.
Postgres only stores date/times in UTC. Everything else is a translation.
The driver uses the client's timezone for all dates/times (for better or
worse) If there is anything I can do to help make things easier, let me
know.
--
Sent from: http://hibernate-development.74578.x6.nabble.com/
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
10:27 p.m.
Hi Steve,
I'm not sure there is a better way to store the data in the database. Doing
any kind of date/time math in anything else but UTC seems fraught with
danger.
See below as to how we handle Java 8 types.
https://github.com/pgjdbc/pgjdbc/blob/db228a4ffd8b356a9028363b35b0eb9055e...
Also tells you which driver I maintain.
As far as my interest in this discussion goes. What is the pgjdbc driver
doing that is not consistent with what hibernate is doing/wants ?
I'd certainly be up for a hibernate compatibility mode.
Thanks,
Dave Cramer
On Sun, 12 Jan 2020 at 23:36, Steve Ebersole <steve(a)hibernate.org> wrote:
Hi Dave.
Same - I was swamped with stuff at the end of last week.
Yes, from what I was reading postgres is a bit strange in storing temporal
values. Not unique to postgres - many databases do interesting things.
I'm curious how the driver handles binding Java 8 types directly. The
JDBC spec was updated to support these types through the generic
`#setObject` methods (`#getObject` as well?). Does the driver handle this.
Out of curiosity, which jdbc driver are you helping with?
On Thu, Jan 9, 2020 at 10:23 AM Dave Cramer <davecramer(a)gmail.com> wrote:
> Hi,
>
> As one of the maintainers of the postgres jdbc driver I am interested in
> this discussion.
> Postgres only stores date/times in UTC. Everything else is a translation.
> The driver uses the client's timezone for all dates/times (for better or
> worse) If there is anything I can do to help make things easier, let me
> know.
>
>
>
> --
> Sent from: http://hibernate-development.74578.x6.nabble.com/
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
>
1:10 a.m.
Hi,
As far as I know there wasn't any specific time-related problem with the
org.postgresql:postrgresql driver. I'm not sure we run tests against
pgjdbc, that might be something to consider.
The problems were mainly with MariaDB/MySQL/Sybase drivers, and we upgraded
our dependencies since then, so they may behave better now.
In any case, most of the problems come from the conversion to javax.sql
types and the rendering of these types by the JDBC drivers. If we can pass
the java.time types to the drivers directly, that would indeed solve lots
of problems. That would mean losing support for older versions of those
drivers when it comes to java.time, but maybe it's not a big deal?
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Mon, 13 Jan 2020 at 14:30, Dave Cramer <davecramer(a)gmail.com> wrote:
Hi Steve,
I'm not sure there is a better way to store the data in the database. Doing
any kind of date/time math in anything else but UTC seems fraught with
danger.
See below as to how we handle Java 8 types.
https://github.com/pgjdbc/pgjdbc/blob/db228a4ffd8b356a9028363b35b0eb9055e...
Also tells you which driver I maintain.
As far as my interest in this discussion goes. What is the pgjdbc driver
doing that is not consistent with what hibernate is doing/wants ?
I'd certainly be up for a hibernate compatibility mode.
Thanks,
Dave Cramer
On Sun, 12 Jan 2020 at 23:36, Steve Ebersole <steve(a)hibernate.org> wrote:
> Hi Dave.
>
> Same - I was swamped with stuff at the end of last week.
>
> Yes, from what I was reading postgres is a bit strange in storing
temporal
> values. Not unique to postgres - many databases do interesting things.
>
> I'm curious how the driver handles binding Java 8 types directly. The
> JDBC spec was updated to support these types through the generic
> `#setObject` methods (`#getObject` as well?). Does the driver handle
this.
>
> Out of curiosity, which jdbc driver are you helping with?
>
>
>
> On Thu, Jan 9, 2020 at 10:23 AM Dave Cramer <davecramer(a)gmail.com>
wrote:
>
>> Hi,
>>
>> As one of the maintainers of the postgres jdbc driver I am interested in
>> this discussion.
>> Postgres only stores date/times in UTC. Everything else is a
translation.
>> The driver uses the client's timezone for all dates/times (for better or
>> worse) If there is anything I can do to help make things easier, let me
>> know.
>>
>>
>>
>> --
>> Sent from: http://hibernate-development.74578.x6.nabble.com/
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev(a)lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
1:14 a.m.
Dave Cramer
On Mon, 13 Jan 2020 at 11:10, Yoann Rodiere <yoann(a)hibernate.org> wrote:
Hi,
As far as I know there wasn't any specific time-related problem with the
org.postgresql:postrgresql driver. I'm not sure we run tests against
pgjdbc, that might be something to consider.
Please do run your tests against that.
One thing I noted was that you were not using the native interval type for
intervals.
The problems were mainly with MariaDB/MySQL/Sybase drivers, and we
upgraded our dependencies since then, so they may behave better now.
In any case, most of the problems come from the conversion to javax.sql
types and the rendering of these types by the JDBC drivers. If we can pass
the java.time types to the drivers directly, that would indeed solve lots
of problems. That would mean losing support for older versions of those
drivers when it comes to java.time, but maybe it's not a big deal?
Always a tough call, In my experience guaranteed to annoy at least half of
the
users.
Dave
On Mon, 13 Jan 2020 at 14:30, Dave Cramer <davecramer(a)gmail.com> wrote:
> Hi Steve,
>
> I'm not sure there is a better way to store the data in the database.
> Doing
> any kind of date/time math in anything else but UTC seems fraught with
> danger.
>
>
> See below as to how we handle Java 8 types.
>
>
https://github.com/pgjdbc/pgjdbc/blob/db228a4ffd8b356a9028363b35b0eb9055e...
>
> Also tells you which driver I maintain.
>
> As far as my interest in this discussion goes. What is the pgjdbc driver
> doing that is not consistent with what hibernate is doing/wants ?
>
> I'd certainly be up for a hibernate compatibility mode.
>
> Thanks,
>
> Dave Cramer
>
>
> On Sun, 12 Jan 2020 at 23:36, Steve Ebersole <steve(a)hibernate.org> wrote:
>
> > Hi Dave.
> >
> > Same - I was swamped with stuff at the end of last week.
> >
> > Yes, from what I was reading postgres is a bit strange in storing
> temporal
> > values. Not unique to postgres - many databases do interesting things.
> >
> > I'm curious how the driver handles binding Java 8 types directly. The
> > JDBC spec was updated to support these types through the generic
> > `#setObject` methods (`#getObject` as well?). Does the driver handle
> this.
> >
> > Out of curiosity, which jdbc driver are you helping with?
> >
> >
> >
> > On Thu, Jan 9, 2020 at 10:23 AM Dave Cramer <davecramer(a)gmail.com>
> wrote:
> >
> >> Hi,
> >>
> >> As one of the maintainers of the postgres jdbc driver I am interested
> in
> >> this discussion.
> >> Postgres only stores date/times in UTC. Everything else is a
> translation.
> >> The driver uses the client's timezone for all dates/times (for better
> or
> >> worse) If there is anything I can do to help make things easier, let me
> >> know.
> >>
> >>
> >>
> >> --
> >> Sent from: http://hibernate-development.74578.x6.nabble.com/
> >> _______________________________________________
> >> hibernate-dev mailing list
> >> hibernate-dev(a)lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>
> >>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
>
1:25 a.m.
> As far as I know there wasn't any specific time-related
problem with
the org.postgresql:postrgresql driver. I'm not sure we run tests
against
pgjdbc, that might be something to consider.
Please do run your tests against that.
My bad, we do run our tests against that. I confused it with pgjdbc-ng. So,
yeah, pgjdbc works just fine.
Yoann Rodière
Hibernate Team
yoann(a)hibernate.org
On Mon, 13 Jan 2020 at 17:14, Dave Cramer <davecramer(a)gmail.com> wrote:
>
> Dave Cramer
>
>
> On Mon, 13 Jan 2020 at 11:10, Yoann Rodiere <yoann(a)hibernate.org> wrote:
>
>> Hi,
>>
>> As far as I know there wasn't any specific time-related problem with the
>> org.postgresql:postrgresql driver. I'm not sure we run tests against
>> pgjdbc, that might be something to consider.
>>
>
Please do run your tests against that.
>
> One thing I noted was that you were not using the native interval type for
> intervals.
>
>
>> The problems were mainly with MariaDB/MySQL/Sybase drivers, and we
>> upgraded our dependencies since then, so they may behave better now.
>>
>> In any case, most of the problems come from the conversion to javax.sql
>> types and the rendering of these types by the JDBC drivers. If we can pass
>> the java.time types to the drivers directly, that would indeed solve lots
>> of problems. That would mean losing support for older versions of those
>> drivers when it comes to java.time, but maybe it's not a big deal?
>>
>> Always a tough call, In my experience guaranteed to annoy at least half
> of the users.
>
> Dave
>
>>
>> On Mon, 13 Jan 2020 at 14:30, Dave Cramer <davecramer(a)gmail.com> wrote:
>>
>>> Hi Steve,
>>>
>>> I'm not sure there is a better way to store the data in the database.
>>> Doing
>>> any kind of date/time math in anything else but UTC seems fraught with
>>> danger.
>>>
>>>
>>> See below as to how we handle Java 8 types.
>>>
>>>
https://github.com/pgjdbc/pgjdbc/blob/db228a4ffd8b356a9028363b35b0eb9055e...
>>>
>>> Also tells you which driver I maintain.
>>>
>>> As far as my interest in this discussion goes. What is the pgjdbc driver
>>> doing that is not consistent with what hibernate is doing/wants ?
>>>
>>> I'd certainly be up for a hibernate compatibility mode.
>>>
>>> Thanks,
>>>
>>> Dave Cramer
>>>
>>>
>>> On Sun, 12 Jan 2020 at 23:36, Steve Ebersole <steve(a)hibernate.org>
>>> wrote:
>>>
>>> > Hi Dave.
>>> >
>>> > Same - I was swamped with stuff at the end of last week.
>>> >
>>> > Yes, from what I was reading postgres is a bit strange in storing
>>> temporal
>>> > values. Not unique to postgres - many databases do interesting things.
>>> >
>>> > I'm curious how the driver handles binding Java 8 types directly.
The
>>> > JDBC spec was updated to support these types through the generic
>>> > `#setObject` methods (`#getObject` as well?). Does the driver handle
>>> this.
>>> >
>>> > Out of curiosity, which jdbc driver are you helping with?
>>> >
>>> >
>>> >
>>> > On Thu, Jan 9, 2020 at 10:23 AM Dave Cramer
<davecramer(a)gmail.com>
>>> wrote:
>>> >
>>> >> Hi,
>>> >>
>>> >> As one of the maintainers of the postgres jdbc driver I am
interested
>>> in
>>> >> this discussion.
>>> >> Postgres only stores date/times in UTC. Everything else is a
>>> translation.
>>> >> The driver uses the client's timezone for all dates/times (for
better
>>> or
>>> >> worse) If there is anything I can do to help make things easier,
let
>>> me
>>> >> know.
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Sent from: http://hibernate-development.74578.x6.nabble.com/
>>> >> _______________________________________________
>>> >> hibernate-dev mailing list
>>> >> hibernate-dev(a)lists.jboss.org
>>> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>> >>
>>> >>
>>> _______________________________________________
>>> hibernate-dev mailing list
>>> hibernate-dev(a)lists.jboss.org
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>>
1576
days inactive
1583
days old
26 comments
4 participants
participants (4)
-
Christian Beikov -
Dave Cramer -
Steve Ebersole -
Yoann Rodiere