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

Christian Beikov christian.beikov at gmail.com
Sat Sep 10 09:57:59 EDT 2016


Will there be a way in SQM to specify the argument types of a function? 
If so, then you could introduce the concept of var-args and then a 
parameter will only be a multi-valued parameter if the function argument 
it is used for is a var-arg parameter.
If not, then you probably need a custom syntax. Maybe something like 
":param...", ":...param", ":*param" or ":param*"?

Am 10.09.2016 um 15:30 schrieb Steve Ebersole:
> 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 <mailto: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>
>     > <mailto: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>
>     <mailto: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>
>     <mailto: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> <mailto: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>
>     <mailto: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>
>     <mailto: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> <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> <http://p.name> in
>     >         (?, ?, ?)
>     >         >>>>> select ... from PERSON p where p.name
>     <http://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>
>     >         <mailto: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>
>     >         <mailto: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>
>     >         <mailto: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>
>     >         <mailto: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