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

Steve Ebersole steve at hibernate.org
Fri Sep 9 16:26:16 EDT 2016 

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

More information about the hibernate-dev mailing list