[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 
> <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>> wrote:
>
>     Am 08.01.2020 um 13:50 schrieb Steve Ebersole:
>     > On Wed, Jan 8, 2020 at 6:09 AM Christian Beikov
>     > <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>
>     <mailto:christian.beikov at gmail.com
>     <mailto:christian.beikov at 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.