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

Steve Ebersole steve at hibernate.org
Fri Sep 9 17:35:50 EDT 2016


Not any particular reason, HHH-10502 or otherwise.  Its more a general
question as I am integrating SQM and the new query translators upstream.

Again, I think y'all are missing the point.  The concern is not whether a
query is cacheable or not.  The concern is *when* I know whether it is
cacheable.

Consider some psuedo-code.  First, the ideal case:

SessionImpl#createQuery(...) {
    ...

    // interpret the query into SQM
    SqmStatementSelect sqm = SemanticQueryInterpreter.interpret(...);

    QueryPlan queryPlan;
    if ( isCacheable( sqm ) ) {
        queryPlan = queryPlanCache.get( ... );
        if ( queryPlan == null ) {
            queryPlan = new QueryPlan( ... );
            queryPlanCache.put( ..., queryPlan );
        }
    }
    else {
        queryPlan = new QueryPlan( ... );
    }

    return new QueryImpl( sqm, queryPlan, this );
}

versus:

SessionImpl#createQuery(...) {
    ...

    // interpret the query into SQM
    SqmStatementSelect sqm = SemanticQueryInterpreter.interpret(...);

    // now we have to delay creation of the QueryPlan until later
    // because we do not know if the query is cacheable, because
    // we do not know yet whether it contains any multi-valued
    // parameters.

    return new QueryImpl( sqm, this );
}



On Fri, Sep 9, 2016 at 4:17 PM Christian Beikov <christian.beikov at gmail.com>
wrote:

> Ah ok, I thought you wanted to drop parameter lists in general ^^
> I suppose you are discussing this because of
> https://hibernate.atlassian.net/browse/HHH-10502?
>
> I personally don't have a use case for a general parameter list
> expansion like the reporter of that issue and I also don't think that
> people are directly writing that kind of stuff into a query, but a
> wrapper does. All in all, I think people will be able to workaround if
> you remove the parameter list expansion for other places. Is this such a
> problem to keep around that feature?
>
> Would be interesting to see how other people make use of that feature.
> If everyone uses such collection valued parameters at least for an IN
> predicate and maybe additionally for something else like that FIELD
> function, you would have to wait for the parameter list anyway.
>
> Query caching can generally be handled if you introduce a configuration
> parameter for a fixed expansion size, but that would probably be even
> more work because then you would have to exectue the query multiple times.
>
> I just don't think that removing this feature will bring any benefits.
>
> Regards,
> Christian
>
> Am 09.09.2016 um 22:30 schrieb 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
> >>>>
> >>>
> > _______________________________________________
> > 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