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

Steve Ebersole steve at hibernate.org
Fri Sep 9 18:11:56 EDT 2016


The parameter expansion would happen on the SQM as we translate it to the
"SQL tree" (mutli-step translation).

What we need to know up front (ideally) is:

   1.  "parameter metadata"
   2. "result metadata"

We can glean all that from SQM.  Today that lives on the QueryPlan
(HQLQueryPlan).  The other thing that we keep on QueryPlan is the SQL(s).
That last part would need to change.  So I'd expect to perform "parameter
expansion" over multi-valued params during this SQM -> SQL-tree
conversion.  This is also part of the work that makes sense to cache
(QueryPlanCache) if possible.

What happens once we have the SQM is still being designed/developed,
especially in terms of the role QueryPlan and QueryTranslator used to
play.  Today, even though the current Antlr-based QueryTranslator does a
multi-step translation it does so in a way that expects all steps to happen
at once.  I am looking to improve that design.  I'd like to build the SQM
(like a parse-tree++) up front and leverage that semantic info within the
Query impl.  The other steps/phases can (and should) happen later.

The other concern is that today we have no real validation of when
a collection_valued_input_parameter is allowed versus expecting
a single_valued_input_parameter.  Take HHH-10502 as an example, since you
mentioned it ;)  If we are not going to support multi-valued parameters
there, it sure would be nice to say that in user-friendly terms, right?  A
nice `new QueryException( "Attempt to bind multi-valued parameter when
single-valued binding expected [:ids]" )` or somesuch.  Of course, assuming
we limit this to the JPA support (+1 from me btw).  In fact we don't even
have to wait until they bind the parameter value; in that statement we
know/assume that we have a multi-valued parameter from its use in the IN
expression, but that same parameter is used in a context where a
multi-valued parameter is not allowed - we'd know (asap!) that this is a
bad query.



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

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