Re: [hibernate-dev] 6.0 - HQL literals

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