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

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(a)hibernate.org <mailto:steve@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(a)gmail.com <mailto:christian.beikov@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(a)hibernate.org <mailto:steve@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(a)gmail.com <mailto:mihalcea.vlad@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(a)hibernate.org <mailto:andrea@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(a)hibernate.org <mailto:steve@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(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org> >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> >>>> _______________________________________________ >>>> hibernate-dev mailing list >>>> hibernate-dev(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org> >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> >>> > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org> > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org <mailto:hibernate-dev@lists.jboss.org> https://lists.jboss.org/mailman/listinfo/hibernate-dev