[hibernate-dev] Continue "parameter list" support?
Christian Beikov
christian.beikov at gmail.com
Fri Sep 9 17:45:03 EDT 2016
Hmm I think I understand what you mean now. How would you do parameter
expansion in SQM? Or will the result after a parameter expansion not be
cached?
Am 09.09.2016 um 23:35 schrieb Steve Ebersole:
> 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 <mailto: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 <mailto: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 <mailto: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 <mailto: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 <mailto: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 <http://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 <http://p.name> in (?,
> ?, ?)
> >>>>> select ... from PERSON p where p.name <http://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
> <mailto:hibernate-dev at lists.jboss.org>
> >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>>>>
> >>>> _______________________________________________
> >>>> hibernate-dev mailing list
> >>>> hibernate-dev at lists.jboss.org
> <mailto:hibernate-dev at lists.jboss.org>
> >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>>>
> >>>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev at lists.jboss.org <mailto:hibernate-dev at lists.jboss.org>
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org <mailto:hibernate-dev at lists.jboss.org>
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
More information about the hibernate-dev
mailing list