[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 
> <christian.beikov at gmail.com <mailto:christian.beikov at 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 at gmail.com <mailto:christian.beikov at 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/
>