[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 
> <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/