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