[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