[hibernate-dev] Continue "parameter list" support?

[Steve Ebersole]

BTW, JPA really requires that we support accepting multi-valued bindings
for parameters through #setParameter.  Yet another reason to do away with
#setParameterList.


On Fri, Sep 9, 2016 at 3:26 PM Steve Ebersole <steve at hibernate.org> wrote:

> WRT the "collection_valued_input_parameter" bit, that is limited in the
> spec to IN clauses.  And more specifically in fact the spec specifically
> limits this in *exactly* the way I suggested :)
>
> <quote>
> in_expression ::=
>         {state_valued_path_expression | type_discriminator} [ NOT ] IN
>             { ( in_item { , in_item}* ) | ( subquery ) |
> collection_valued_input_parameter }
>
> in_item ::= literal | single_valued_input_parameter
> </quote>
>
> (see "4.6.9 In Expressions" or "4.14 BNF")
>
> In other words, the "values" of an IN expression may be *one of* the
> following:
>
>    1. an explicit list of "in-items", which in turn are specifically
>    single values
>    2. a subquery
>    3. a *single* multivalued param (one placeholder to rule them all)
>
>
> As I said in my original email:
>
> <quote>
>  we could simply assume that a IN predicate with a single parameter
> placeholder is going to be a multivalued parameter
> </quote>
>
> That's what JPA supports for multi-valued parameters.  Hibernate has long
> supported a broader definition of "collection_valued_input_parameter".  I
> am simply suggesting that we align with the limitation JPA already has in
> place.
>
> Really, the only thing I am really asking about is the overloaded forms of
> Query#setParameterList.  We have to support multi-valued parameters *in
> this very limited* case.  Sure.  The problem with our existing
> broader/open-ended multi-valued param support is that we do not know that a
> parameter is multi-valued *from the query* itself.  Specifically we have
> to wait and see if #setParameter or #setParameterList is called, on any of
> the parameters.
>
> Again, the "win" is that we could then know *up front* that a query is not
> cacheable (precompile-able), whereas today we have to wait until just
> before the execution (so that #setParameter and #setParameterList have all
> been called).
>
>
> @Vlad It depends what "query cannot be precompiled" *means* which is of
> course open to interpretation.  Heck what compilation of a query means at
> all is outside the scope of the spec.  Any "compilation" of the query that
> resolves to SQL of course has to wait.  But "compilation" to a semantic
> form (aka, SQM) does not need to wait.  In fact the SQM is the exact place
> you'd look to know whether the query (plan) is cacheable.
>
>
>
> On Fri, Sep 9, 2016 at 8:20 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
> wrote:
>
>> Hi,
>>
>> I don't think we should deprecate such a feature.
>>
>> First, the JPA specs says the follows:
>>
>> "All input parameters must be single-valued, except in IN expressions
>> (see section 4.6.9), which support
>> the use of collection-valued input parameters."
>>
>> So, we kinda need to support it one way or another.
>>
>> Also, the JPA specs says that:
>>
>> "Note that use of a collection-valued input parameter will mean that a
>> static query cannot be precompiled."
>>
>> So, it's expected to have such a behavior.
>>
>> I don't think that multi-load support can replace paremeterList since the
>> former cannot use any property from a given entity.
>> Also, the IN predicate with parameter list applies to DTO projections or
>> native queries, so it's useful to have it.
>>
>> Vlad
>>
>> On Fri, Sep 9, 2016 at 4:03 PM, andrea boriero <andrea at hibernate.org>
>> wrote:
>>
>>> I am also not able to figure out another use case than the IN predicate
>>> so
>>> I am for always considering IN predicates as multi-valued.
>>>
>>> On 9 September 2016 at 14:20, Steve Ebersole <steve at hibernate.org>
>>> wrote:
>>>
>>> > To be clear, this is the feature that lets you define a query like:
>>> >
>>> > select ... from Person p where p.name in (:names)
>>> >
>>> > And then bind varied multiple values into that single parameter holder:
>>> >
>>> > query.setParameterList( "names", new String[] { "Larry", "Curly",
>>> "Moe" }
>>> > );
>>> > query.setParameterList( "names", new String[] { "John", "Jane" } );
>>> >
>>> > Which magically transforms to the following (rough) SQL:
>>> >
>>> > select ... from PERSON p where p.name in (?, ?, ?)
>>> > select ... from PERSON p where p.name in (?, ?)
>>> >
>>> > Effectively parameter lists allow expansion of the HQL statement - they
>>> > literally are handled by altering the HQL on the fly as we prepare to
>>> > execute the query.  What that means is that we can really not cache
>>> these
>>> > queries, at least not until the parameters are bound (which kind of
>>> defeats
>>> > the purpose).
>>> >
>>> > I'd like to discuss dropping support for parameter lists.  There are
>>> quite
>>> > a few reasons I would like to drop this support:
>>> >
>>> >    1. This is the main culprit that leads to the ever-resurrecting
>>> >    discussion about DB limits on IN clauses.  The one valid use case I
>>> saw
>>> > for
>>> >    that lead me to add multi-load support in 5.1.
>>> >    2. In terms of a QueryPlan cache, this support means we can never
>>> >    effectively cache the plans for these queries because the SQL is
>>> > different
>>> >    every time we execute the query.  The problem though is that we do
>>> not
>>> > know
>>> >    this until well after the point that we'd resolve the QueryPlan.
>>> >     chicken-egg.
>>> >    3. This is more an internal detail, but handling the parameter
>>> bindings
>>> >    for these differently gets quite complicated.
>>> >    4. An additional internal detail is that re-writing the HQL on the
>>> fly
>>> >    is problematic.  And some of that leaks to the user in terms of
>>> result
>>> >    caching and stats (which HQL do we use?).
>>> >
>>> > I get that this can be a useful feature for apps that dynamically build
>>> > HQL, although really for dynamic query building I think a criteria
>>> approach
>>> > is more appropriate.  It is not so much supporting this feature that
>>> bugs
>>> > me, it's how we expose it.  So an alternative to dropping this support
>>> > would be to support it in a different way.  The main issue is that I
>>> would
>>> > like to *syntactically* understanding that a parameter placeholder
>>> will be
>>> > used for multi-valued parameter from the query itself.  This is a
>>> > beyond-JPA feature, so we definitely have some leeway here to define
>>> this
>>> > however we want.
>>> >
>>> > I am open to suggestions as to the best syntax to declare that.
>>> >
>>> > An alternative would be to make some assumptions.  Specifically, the
>>> only
>>> > time I can think this is used is inside an IN predicate.  Am I missing
>>> > others?  If that is the case, we could simply assume that a IN
>>> predicate
>>> > with a single parameter placeholder is going to be a multivalued
>>> > parameter.  That assumption holds valid even if just a single value is
>>> > bound.  The main win there is that we can get rid of the
>>> > Query#setParameterList
>>> > variants.  setParameterList was only ever needed so that we could
>>> > understand that the parameter is multivalued - here we'd assume that
>>> from
>>> > its context as the IN predicate value.
>>> >
>>> > Continuing to support parameters-lists in any form does not really
>>> > address point
>>> > (1) above; but that's ok - the user really could , But each of the
>>> > alternatives does help with the other problems currently stemming from
>>> > parameter-list support.
>>> > _______________________________________________
>>> > hibernate-dev mailing list
>>> > hibernate-dev at lists.jboss.org
>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>> >
>>> _______________________________________________
>>> hibernate-dev mailing list
>>> hibernate-dev at lists.jboss.org
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>
>>