Re: [hibernate-dev] 6.0 - HQL literals

[Yoann Rodiere]

 >> 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
yo...@hibernate.org


On Mon, 13 Jan 2020 at 17:14, Dave Cramer  wrote:

>
> Dave Cramer
>
>
> On Mon, 13 Jan 2020 at 11:10, Yoann Rodiere  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  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/db228a4ffd8b356a9028363b35b0eb9055ea53f0/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L961-L968
>>>
>>> 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 
>>> 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 
>>> 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@lists.jboss.org
>>> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>> >>
>>> >>
>>> ___
>>> hibernate-dev mailing list
>>> hibernate-dev@lists.jboss.org
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Dave Cramer]

Dave Cramer


On Mon, 13 Jan 2020 at 11:10, Yoann Rodiere  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  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/db228a4ffd8b356a9028363b35b0eb9055ea53f0/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L961-L968
>>
>> 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  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 
>> 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@lists.jboss.org
>> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>> >>
>> >>
>> ___
>> hibernate-dev mailing list
>> hibernate-dev@lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Yoann Rodiere]

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
yo...@hibernate.org


On Mon, 13 Jan 2020 at 14:30, Dave Cramer  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/db228a4ffd8b356a9028363b35b0eb9055ea53f0/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L961-L968
>
> 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  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 
> 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@lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>
> >>
> ___
> hibernate-dev mailing list
> hibernate-dev@lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Dave Cramer]

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/db228a4ffd8b356a9028363b35b0eb9055ea53f0/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L961-L968

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  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  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@lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

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  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@lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

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  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/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38
>
> > 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
> yo...@hibernate.org
>
>
> On Tue, 7 Jan 2020 at 19:50, Steve Ebersole  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@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Dave Cramer]

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@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Christian Beikov]

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 
> mailto:christian.bei...@gmail.com>> wrote:
>
> Am 08.01.2020 um 20:21 schrieb Steve Ebersole:
>> On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov
>> mailto:christian.bei...@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-variable.html
>
> [2]
> 
> https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be4942252
>
> [3]
> 
> https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for-performance/
>
> [4]
> 
> https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-inline-values-in-sql/
>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[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 
wrote:

> Am 08.01.2020 um 20:21 schrieb Steve Ebersole:
>
> On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov <
> christian.bei...@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-variable.html
>
> [2]
> https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be4942252
>
> [3]
> https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for-performance/
>
> [4]
> https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-inline-values-in-sql/
>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Christian Beikov]

Am 08.01.2020 um 20:21 schrieb Steve Ebersole:
> On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov 
> mailto:christian.bei...@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-variable.html

[2] 
https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be4942252

[3] 
https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for-performance/

[4] 
https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-inline-values-in-sql/

___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov 
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.
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Christian Beikov]

Am 08.01.2020 um 14:47 schrieb Steve Ebersole:
> On Wed, Jan 8, 2020 at 7:11 AM Christian Beikov 
> mailto:christian.bei...@gmail.com>> wrote:
>
> Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
> > On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov
> > mailto:christian.bei...@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.
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

On Wed, Jan 8, 2020 at 7:11 AM Christian Beikov 
wrote:

> Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
> > On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov
> > mailto:christian.bei...@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?
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Christian Beikov]

Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
> On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov 
> mailto:christian.bei...@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
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov 
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?
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

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  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
> yo...@hibernate.org
>
>
> On Wed, 8 Jan 2020 at 12:41, Christian Beikov 
> 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  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/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38
> > >
> > > > 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
> > > yo...@hibernate.org
> > >
> > >
> > > On Tue, 7 Jan 2020 at 19:50, Steve Ebersole 
> 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 -
> > 

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

On Wed, Jan 8, 2020 at 4:21 AM Yoann Rodiere  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.

>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Christian Beikov]

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
> yo...@hibernate.org 
>
>
> On Wed, 8 Jan 2020 at 12:41, Christian Beikov 
> mailto:christian.bei...@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 mailto:yo...@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/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38
> >
> > > 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
> 

Re: [hibernate-dev] 6.0 - HQL literals

[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
yo...@hibernate.org


On Wed, 8 Jan 2020 at 12:41, Christian Beikov 
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  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/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38
> >
> > > 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
> > yo...@hibernate.org
> >
> >
> > On Tue, 7 Jan 2020 at 19:50, Steve Ebersole  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@lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> ___
> hibernate-dev mailing list
> hibernate-dev@lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
___
hibernate-dev mailing list

Re: [hibernate-dev] 6.0 - HQL literals

[Christian Beikov]

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  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/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38
>
> > 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
> yo...@hibernate.org
>
>
> On Tue, 7 Jan 2020 at 19:50, Steve Ebersole  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@lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Yoann Rodiere]

> 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/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38

> 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
yo...@hibernate.org


On Tue, 7 Jan 2020 at 19:50, Steve Ebersole  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@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

>
> As far as I know, even Java does not support that.  A true zone-id would
> be something like (for me) "America/Chicago".  If I ask Java to parse
> "2020-01-01 10:10:10 America/Chicago +02:00" it just says no.  For me, CST
> (standard) and CDT (daylight savings) are really synonyms for offset -
> either UTC-05:00 or UTC-06:00 depending on day of the year.
>

It seems like the proper syntax for that would actually be "2020-01-01
10:10:10+02:00 America/Chicago", but in my
testing DateTimeFormatter#parseBest did not handle that form either
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

On Tue, Jan 7, 2020 at 10:05 AM Yoann Rodiere  wrote:

> On Tue, 7 Jan 2020 at 14:45, Steve Ebersole  wrote:
>
>> Sorry, I should have been more clear.  The literals are not "passed
>> through"; it's just a mechanism to be able to recognize the literal
>> syntactically while parsing.  All of those forms I showed actually are
>> handled in the code and interpreted as a Java temporal.  We then do
>> whatever we want to do with it in order to send it to the database (often
>> even as a parameter).
>>
>
> You mean you use the org.hibernate.type.Type instance registered in the
> dialect for this Java type to convert it to the SQL type? Nice, that
> definitely sounds more robust than what I thought.
>

This does nothing with Type.  The way the grammar is defined it literally
understands each piece of the temporal.  So given, e.g., {2020-01-01}, we
know that 2020 is the year, etc.  This is the benefit of defining it
syntactically.

I suppose the org.hibernate.type.Type instance used is inferred from the
> class of the litteral, which should be good enough in most cases. It may
> not work properly when users assigned their own type to a property, for
> example "where foo = {d 2020-01-01}" where "foo" is a LocalDate mapped as a
> Timestamp (see org.hibernate.test.type.LocalDateTest.
> DateAsTimestampRemappingH2Dialect). But that's rather advanced use case,
> and if you provide a way to define custom literal types as you said, users
> will have a way out.
>

This ties in with what I mentioned above.  The literal value is always an
instance of a Java 8 temporal type.  The Type we associate with the node
will be pulled from the TypeConfiguration.


Be aware that the form zone id + offset may also make sense, when users
> want to use a zone id during DST switch with overlap (the same datetime
> happens twice in the same zone, so the offset is needed for disambiguation).
> I suppose the offset alone would be enough, but from what I've seen, the
> resulting ZoneDateTime object is different depending on whether you pass
> zone id + offset or just offset.
>

As far as I know, even Java does not support that.  A true zone-id would be
something like (for me) "America/Chicago".  If I ask Java to parse
"2020-01-01 10:10:10 America/Chicago +02:00" it just says no.  For me, CST
(standard) and CDT (daylight savings) are really synonyms for offset -
either UTC-05:00 or UTC-06:00 depending on day of the year.

As counter-intuitive as it sounds, a ZonedDateTime actually includes an
offset to differentiate the overlap case you mention.



>
>
> * We also support a STRING_LITERAL form of temporal literals as I
>> mentioned originally.  In my experience, using
>> `java.time.format.DateTimeFormatter#parseBest` always returned a
>> ZonedDateTime whether a zone-id or offset was specified.  My understanding
>> is that this varies from Java 8 to Java 9.  So that's something to consider
>> as well.
>>
>


> Not sure I see which literal you're talking about, since the ones you
> mentioned were temporal literals; do you mean something like "where date =
> '2019-01-01'"?
>
If it's new, I'd personally be in favor of not allowing this and sticking
> to a specific syntax for time literals. Seems less error-prone.
>

No I meant the alternative form I mentioned initially.  So I could have
`{ts '2020-0101 10:10:10'}` or `{2020-0101 10:10:10`).  The first form is
parsed from the String via `java.time.format.DateTimeFormatter#parseBest`.
For the other form, we actually process each int value individually and
piece together the correct temporal.


I was talking about the org.hibernate.type.Type implementations for all the
> Java date/time types, be it in java.util or in java.time. As you know, each
> type maps to the database column slightly differently, and each has its
> quirks, because of how JDBC drivers handle date/time types. Quite a few
> JDBC drivers behave very strangely, especially in corner cases (DST switch,
> dates before year 1900, ...). Often, the legacy date/time APIs are to blame
> (though I wouldn't touch the older versions of the MySQL drivers with a ten
> foot pole).
>
> More to the point: I know from experience it is quite hard to get the
> conversion from Java date/time values to an SQL date/time value to work
> properly in all cases (JDBC driver, JVM timezone, database timezone). But
> if you re-use org.hibernate.type.Type to convert literals to SQL types,
> then it should behave the same as when persisting, so queries such as
> "where foo = {2019-01-01}" should work even if the date is converted in a
> convoluted way before it is sent to the database.
> You can find examples in package org.hibernate.test.type: LocalTimeTest,
> LocalDateTest, ...
>
>
>>
>> I might be wrong, but only exhaustive testing of all literals with all
>>> date/time types on all RDBMS will let us know for sure. Let's keep in mind
>>> how many bugs have surfaced from time-related features in the past...
>>>
>>
>> Gavin actually did quite a bit of that in 

Re: [hibernate-dev] 6.0 - HQL literals

[Yoann Rodiere]

On Tue, 7 Jan 2020 at 14:45, Steve Ebersole  wrote:

> Sorry, I should have been more clear.  The literals are not "passed
> through"; it's just a mechanism to be able to recognize the literal
> syntactically while parsing.  All of those forms I showed actually are
> handled in the code and interpreted as a Java temporal.  We then do
> whatever we want to do with it in order to send it to the database (often
> even as a parameter).
>

You mean you use the org.hibernate.type.Type instance registered in the
dialect for this Java type to convert it to the SQL type? Nice, that
definitely sounds more robust than what I thought.

I suppose the org.hibernate.type.Type instance used is inferred from the
class of the litteral, which should be good enough in most cases. It may
not work properly when users assigned their own type to a property, for
example "where foo = {d 2020-01-01}" where "foo" is a LocalDate mapped as a
Timestamp (see
org.hibernate.test.type.LocalDateTest.DateAsTimestampRemappingH2Dialect).
But that's rather advanced use case, and if you provide a way to define
custom literal types as you said, users will have a way out.


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

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


  The literals can also contain zone id or offset.  I choose to not except
> the form with 'T'.  E.g., all of these are valid:
>
>- ...
>
> Be aware that the form zone id + offset may also make sense, when users
want to use a zone id during DST switch with overlap (the same datetime
happens twice in the same zone, so the offset is needed for disambiguation).
I suppose the offset alone would be enough, but from what I've seen, the
resulting ZoneDateTime object is different depending on whether you pass
zone id + offset or just offset.


* We also support a STRING_LITERAL form of temporal literals as I mentioned
> originally.  In my experience, using
> `java.time.format.DateTimeFormatter#parseBest` always returned a
> ZonedDateTime whether a zone-id or offset was specified.  My understanding
> is that this varies from Java 8 to Java 9.  So that's something to consider
> as well.
>

Not sure I see which literal you're talking about, since the ones you
mentioned were temporal literals; do you mean something like "where date =
'2019-01-01'"?
If it's new, I'd personally be in favor of not allowing this and sticking
to a specific syntax for time literals. Seems less error-prone.



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

I was talking about the org.hibernate.type.Type implementations for all the
Java date/time types, be it in java.util or in java.time. As you know, each
type maps to the database column slightly differently, and each has its
quirks, because of how JDBC drivers handle date/time types. Quite a few
JDBC drivers behave very strangely, especially in corner cases (DST switch,
dates before year 1900, ...). Often, the legacy date/time APIs are to blame
(though I wouldn't touch the older versions of the MySQL drivers with a ten
foot pole).

More to the point: I know from experience it is quite hard to get the
conversion from Java date/time values to an SQL date/time value to work
properly in all cases (JDBC driver, JVM timezone, database timezone). But
if you re-use org.hibernate.type.Type to convert literals to SQL types,
then it should behave the same as when persisting, so queries such as
"where foo = {2019-01-01}" should work even if the date is converted in a
convoluted way before it is sent to the database.
You can find examples in package org.hibernate.test.type: LocalTimeTest,
LocalDateTest, ...


>
> I might be wrong, but only exhaustive testing of all literals with all
>> date/time types on all RDBMS will let us know for sure. Let's keep in mind
>> how many bugs have surfaced from time-related features in the past...
>>
>
> Gavin actually did quite a bit of that in the PR he sent us.  He added
> pretty cool support for various temporal-related things such as how to
> handle formatting (to_char, etc), extraction and temporal arithmetic -
> specifically formalizing and normalizing them across databases.
>

Nice. As long as it uses org.hibernate.type.Type implementations when it
comes to converting between Java values and SQL values, it should be safe.
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

Thanks for the feedback.

I added some comments in-line.

On Tue, Jan 7, 2020 at 1:59 AM Yoann Rodiere  wrote:

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

Sorry, I should have been more clear.  The literals are not "passed
through"; it's just a mechanism to be able to recognize the literal
syntactically while parsing.  All of those forms I showed actually are
handled in the code and interpreted as a Java temporal.  We then do
whatever we want to do with it in order to send it to the database (often
even as a parameter).

As far as timezones, a datetime with no timezone is interpreted as a
LocalDateTime.  However I did have an open question there still regarding
*which* local - the local VM's timezone?  Or the "JDBC timezone" (which we
know via our `hibernate.jdbc.time_zone` setting)?  The literals can also
contain zone id or offset.  I choose to not except the form with 'T'.
E.g., all of these are valid:

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

* We also support a STRING_LITERAL form of temporal literals as I mentioned
originally.  In my experience, using
`java.time.format.DateTimeFormatter#parseBest` always returned a
ZonedDateTime whether a zone-id or offset was specified.  My understanding
is that this varies from Java 8 to Java 9.  So that's something to consider
as well.  I mention this because I tried to make interpreting the syntactic
form consistent.  If you are interested in the specifics of how this
happens, see:

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


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


I might be wrong, but only exhaustive testing of all literals with all
> date/time types on all RDBMS will let us know for sure. Let's keep in mind
> how many bugs have surfaced from time-related features in the past...
>

Gavin actually did quite a bit of that in the PR he sent us.  He added
pretty cool support for various temporal-related things such as how to
handle formatting (to_char, etc), extraction and temporal arithmetic -
specifically formalizing and normalizing them across databases.
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

Re: [hibernate-dev] 6.0 - HQL literals

[Yoann Rodiere]

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
yo...@hibernate.org


On Mon, 6 Jan 2020 at 15:57, Steve Ebersole  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/eab6107ec2e7b3a0c06146a9ff51b9964f4b3169/hibernate-core/src/main/antlr/org/hibernate/grammars/hql/HqlParser.g4#L484
> ___
> hibernate-dev mailing list
> hibernate-dev@lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
>
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev

[hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

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/eab6107ec2e7b3a0c06146a9ff51b9964f4b3169/hibernate-core/src/main/antlr/org/hibernate/grammars/hql/HqlParser.g4#L484
___
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev