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

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

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 form with 'T'. E.g., all of these are valid: - ... Be aware that the form zone id + offset may also make sense, when users

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

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.

> 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&gt; 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

> 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

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)

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?

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`

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.

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?

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.

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

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&gt; 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 > >

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? Always a tough call, In my experience guaranteed to annoy at least half of

On Mon, 13 Jan 2020 at 14:30, Dave Cramer <davecramer(a)gmail.com&gt; 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&gt; 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&gt; > 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 > >

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