[hibernate-dev] Continue "parameter list" support?
Steve Ebersole
steve at hibernate.org
Sat Sep 10 09:30:13 EDT 2016
Semantic means the "meaning" of something, but also the "intent". Here,
specifically, the intent is "is this param placeholder representing a
single- or multi-valued parameter? So I'll ask you... Given a query:
"from Something order by field( id, :param )"
Does ":param" represent a single- or multi-valued parameter? Because that
is part of the semantic of the query... the ability to answer such
questions...
On Sat, Sep 10, 2016 at 7:34 AM Christian Beikov <christian.beikov at gmail.com>
wrote:
> May I ask how the semantics of a query could change in case of parameter
> expansion in different places than IN predicate?
> Since such collection valued parameters only make sense on some sort of
> varg-args accepting thingy like IN or a SQLFunction, I don't really see
> how this is an issue.
>
> You could have 2 caches e.g.
>
> * SQMCache<HQL, SQMQuery>
> * SQLCache<SQMQuery, Cache<ParameterMetadata, SQLString>>
>
> Can you please elaborate what you think is problematic about something
> like that?
>
> Am 09.09.2016 um 23:39 schrieb Steve Ebersole:
> > Although, thinking about it some more, SQM itself already exposes some
> > of the things QueryPlan does today. So maybe this is not such a big
> > issue.
> >
> > I personally am just not a fan of not understanding the semantic of a
> > query up front. Good translation in part comes down to good design of
> > the language so that as much semantic as possible is available
> > statically (from the Query itself, and from Session/SessionFactory).
> >
> >
> > On Fri, Sep 9, 2016 at 4:35 PM Steve Ebersole <steve at hibernate.org
> > <mailto:steve at hibernate.org>> wrote:
> >
> > 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
> >
>
> _______________________________________________
> 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