In the interest of questioning everything, just to make sure we are all on the same page, Hibernate's support for native SQL queries currently recognizes named parameters, positional parameters as well as JDBC-style parameters. JPA only defines support for "JDBC-style parameters" as valid for native SQL queries: {quote} It is assumed that for native queries the parameters themselves use the SQL syntax (i.e., “?”, rather than “?1”). {quote} Furthermore Hibernate does not support a native query using both positional parameters and JDBC-style parameters in the same query because it causes a non-determinism wrt the positions. I assume we want to continue to support that full complement of parameter types, with the positional/JDBC-style caveat. Further I assume we will hook up the use of any non-JDBC-style parameters in with the "strict JPA compliance" checking and throw an error when indicated. Anyone have objections to any of that? _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Actually JPA defines it as "Only positional parameter binding and positional access to result items may be portably used for native queries". I believe "portably" means the providers are only required to support positional, but not forbidden from supporting other. 2016-09-21 3:59 GMT+03:00 Steve Ebersole <steve(a)hibernate.org&gt;: > In the interest of questioning everything, just to make sure we are all on > the same page, Hibernate's support for native SQL queries currently > recognizes named parameters, positional parameters as well as JDBC-style > parameters. > > JPA only defines support for "JDBC-style parameters" as valid for native > SQL queries: > {quote} > It is assumed that for native queries the parameters themselves use the > SQL > syntax (i.e., “?”, rather than “?1”). > {quote} > > Furthermore Hibernate does not support a native query using both > positional > parameters and JDBC-style parameters in the same query because it causes a > non-determinism wrt the positions. > > I assume we want to continue to support that full complement of parameter > types, with the positional/JDBC-style caveat. > > Further I assume we will hook up the use of any non-JDBC-style parameters > in with the "strict JPA compliance" checking and throw an error when > indicated. > > Anyone have objections to any of that? > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev

One additional thing we might consider is possibly unifying this 0- and 1- based mismatch wrt JDBC-style parameters. In Hibernate's APIs these JDBC-style parameters were 0-based. I have already dropped support (it has been deprecated for a long time anyway) for using JDBC-style params in HQL. So this is now only a concern for native queries. Interestingly JPA has the following to say (3.10.13 Positional Parameters): <quote> Only positional parameter binding ... may be portably used for native queries... When binding the values of positional parameters, the numbering starts as “1”. It is assumed that for native queries the parameters themselves use the SQL syntax (i.e., “?”, rather than “?1”). </quote> So for portable JPA, the native query would use JDBC/SQL syntax for the parameter marker, but use the JPA convention for parameter index (1-based) rather than the JDBC/SQL convention (0-based). As bassackwards as we may think JPA is here, it is a spec and we have to support it: we have to support 1-based binding of these JDBC-style params. The question is whether we should deviate from Hibernate's legacy decision to align with JDBC/SQL (0-based) for binding of these JDBC-style params. Perhaps a setting that controls how this should work - specifically limited to JDBC-style param markers in native queries. By default we'd honor the 1-based approach whenever bootstrapping was done via JPA and 0-based when native bootstrapping is used. I guess what I am wondering is whether we want to have a setting for the native bootstrapping piece that controls whether to use 0-based or 1-based? I do not think we should allow the setting (if we add one) to force 0-based usage with JPA bootstrapping; this would only control how it works in native bootstrapping. Thoughts? On Wed, Sep 21, 2016 at 1:40 PM Steve Ebersole <steve(a)hibernate.org&gt; wrote: > I never said anything about dropping support for named and positional > parameters in native queries. > > I simply mentioned leveraging the new "JPA strict compliance" stuff I am > adding to 6.0. The idea is to allow you to enable that and get feedback > when you use non-portable things. > > > On Tue, Sep 20, 2016 at 11:45 PM Jordan Gigov <coladict(a)gmail.com&gt; wrote: > >> Actually JPA defines it as "Only positional parameter binding and >> positional access to result items may be portably used for native queries". >> I believe "portably" means the providers are only required to support >> positional, but not forbidden from supporting other. >> >> 2016-09-21 3:59 GMT+03:00 Steve Ebersole <steve(a)hibernate.org&gt;: >> >>> In the interest of questioning everything, just to make sure we are all >>> on >>> the same page, Hibernate's support for native SQL queries currently >>> recognizes named parameters, positional parameters as well as JDBC-style >>> parameters. >>> >>> JPA only defines support for "JDBC-style parameters" as valid for native >>> SQL queries: >>> {quote} >>> It is assumed that for native queries the parameters themselves use the >>> SQL >>> syntax (i.e., “?”, rather than “?1”). >>> {quote} >>> >>> Furthermore Hibernate does not support a native query using both >>> positional >>> parameters and JDBC-style parameters in the same query because it >>> causes a >>> non-determinism wrt the positions. >>> >>> I assume we want to continue to support that full complement of >>> parameter >>> types, with the positional/JDBC-style caveat. >>> >>> Further I assume we will hook up the use of any non-JDBC-style >>> parameters >>> in with the "strict JPA compliance" checking and throw an error when >>> indicated. >>> >>> Anyone have objections to any of that? >>> >> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >>

Well therein lies a minor rub in having split SQM out from ORM. Specifically if the "compliance violation" occurs in the HQL/JPQL/Criteria query the exception is a type from SQM (it has no deps on ORM). Here though it is not reasonable to throw an SQM exception type. On Wed, Sep 21, 2016 at 2:28 PM Jordan Gigov <coladict(a)gmail.com&gt; wrote: > That sounds reasonable. So long as there is a property to control it from > persistence.xml, more people will be happy with it. > > It would be very important to quote whatever error people will get in the > docs, so they can find the solution easier. > > 2016-09-21 22:03 GMT+03:00 Steve Ebersole <steve(a)hibernate.org&gt;: > >> One additional thing we might consider is possibly unifying this 0- and >> 1- based mismatch wrt JDBC-style parameters. >> >> In Hibernate's APIs these JDBC-style parameters were 0-based. I have >> already dropped support (it has been deprecated for a long time anyway) for >> using JDBC-style params in HQL. So this is now only a concern for native >> queries. >> >> Interestingly JPA has the following to say (3.10.13 Positional >> Parameters): >> <quote> >> Only positional parameter binding ... may be portably used for native >> queries... When binding the values of positional parameters, the numbering >> starts as “1”. It is assumed that for native queries the parameters >> themselves use the SQL syntax (i.e., “?”, rather than “?1”). >> </quote> >> >> So for portable JPA, the native query would use JDBC/SQL syntax for the >> parameter marker, but use the JPA convention for parameter index (1-based) >> rather than the JDBC/SQL convention (0-based). >> >> As bassackwards as we may think JPA is here, it is a spec and we have to >> support it: we have to support 1-based binding of these JDBC-style params. >> The question is whether we should deviate from Hibernate's legacy decision >> to align with JDBC/SQL (0-based) for binding of these JDBC-style params. >> Perhaps a setting that controls how this should work - specifically limited >> to JDBC-style param markers in native queries. By default we'd honor the >> 1-based approach whenever bootstrapping was done via JPA and 0-based when >> native bootstrapping is used. I guess what I am wondering is whether we >> want to have a setting for the native bootstrapping piece that controls >> whether to use 0-based or 1-based? I do not think we should allow the >> setting (if we add one) to force 0-based usage with JPA bootstrapping; this >> would only control how it works in native bootstrapping. >> >> Thoughts? >> >> On Wed, Sep 21, 2016 at 1:40 PM Steve Ebersole <steve(a)hibernate.org&gt; >> wrote: >> >>> I never said anything about dropping support for named and positional >>> parameters in native queries. >>> >>> I simply mentioned leveraging the new "JPA strict compliance" stuff I >>> am adding to 6.0. The idea is to allow you to enable that and get feedback >>> when you use non-portable things. >>> >>> >>> On Tue, Sep 20, 2016 at 11:45 PM Jordan Gigov <coladict(a)gmail.com&gt; >>> wrote: >>> >>>> Actually JPA defines it as "Only positional parameter binding and >>>> positional access to result items may be portably used for native queries". >>>> I believe "portably" means the providers are only required to support >>>> positional, but not forbidden from supporting other. >>>> >>>> 2016-09-21 3:59 GMT+03:00 Steve Ebersole <steve(a)hibernate.org&gt;: >>>> >>>>> In the interest of questioning everything, just to make sure we are >>>>> all on >>>>> the same page, Hibernate's support for native SQL queries currently >>>>> recognizes named parameters, positional parameters as well as >>>>> JDBC-style >>>>> parameters. >>>>> >>>>> JPA only defines support for "JDBC-style parameters" as valid for >>>>> native >>>>> SQL queries: >>>>> {quote} >>>>> It is assumed that for native queries the parameters themselves use >>>>> the SQL >>>>> syntax (i.e., “?”, rather than “?1”). >>>>> {quote} >>>>> >>>>> Furthermore Hibernate does not support a native query using both >>>>> positional >>>>> parameters and JDBC-style parameters in the same query because it >>>>> causes a >>>>> non-determinism wrt the positions. >>>>> >>>>> I assume we want to continue to support that full complement of >>>>> parameter >>>>> types, with the positional/JDBC-style caveat. >>>>> >>>>> Further I assume we will hook up the use of any non-JDBC-style >>>>> parameters >>>>> in with the "strict JPA compliance" checking and throw an error when >>>>> indicated. >>>>> >>>>> Anyone have objections to any of that? >>>>> >>>> _______________________________________________ >>>>> hibernate-dev mailing list >>>>> hibernate-dev(a)lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> >>>> >

I did end up adding support for controlling this via a setting[1]. Now, when recognizing ordinal parameters (as distinct from positional parameters, i.e. ?1) we define the "base" as: if ( factory.getSessionFactoryOptions().isJpaBootstrap() ) { ordinalParameterBase = 1; } else { final Integer configuredBase = factory.getSessionFactoryOptions().getNonJpaNativeQueryOrdinalPar ameterBase(); ordinalParameterBase = configuredBase == null ? 0 : configuredBase; } ordinalParameterImplicitPosition = ordinalParameterBase; That `factory.getSessionFactoryOptions().getNonJpaNativeQueryOrdinalPar ameterBase()` call is driven by this setting. However back to the question of whether we want to go ahead and unify this base across the board (regardless of bootstrap method) to be 1 *by default*. The difference is who needs to provide this setting... is it legacy apps upgrading to 6.0 that happen to use ordinal parameters? or, is it apps that want the unified base? Essentially should it be: ordinalParameterBase = configuredBase == null ? 0 : configuredBase; or: ordinalParameterBase = configuredBase == null ? 1 : configuredBase; Personally I think it should be the latter. It is pretty normal for legacy app upgrading to have to set some "use legacy behavior" flags. What do y'all think? [1] https://gist.github.com/sebersole/b13c11a9f7d55b84be6b4371e0259bc5 On Wed, Sep 21, 2016 at 2:03 PM Steve Ebersole <steve(a)hibernate.org&gt; wrote: > One additional thing we might consider is possibly unifying this 0- and 1- > based mismatch wrt JDBC-style parameters. > > In Hibernate's APIs these JDBC-style parameters were 0-based. I have > already dropped support (it has been deprecated for a long time anyway) for > using JDBC-style params in HQL. So this is now only a concern for native > queries. > > Interestingly JPA has the following to say (3.10.13 Positional Parameters): > <quote> > Only positional parameter binding ... may be portably used for native > queries... When binding the values of positional parameters, the numbering > starts as “1”. It is assumed that for native queries the parameters > themselves use the SQL syntax (i.e., “?”, rather than “?1”). > </quote> > > So for portable JPA, the native query would use JDBC/SQL syntax for the > parameter marker, but use the JPA convention for parameter index (1-based) > rather than the JDBC/SQL convention (0-based). > > As bassackwards as we may think JPA is here, it is a spec and we have to > support it: we have to support 1-based binding of these JDBC-style params. > The question is whether we should deviate from Hibernate's legacy decision > to align with JDBC/SQL (0-based) for binding of these JDBC-style params. > Perhaps a setting that controls how this should work - specifically limited > to JDBC-style param markers in native queries. By default we'd honor the > 1-based approach whenever bootstrapping was done via JPA and 0-based when > native bootstrapping is used. I guess what I am wondering is whether we > want to have a setting for the native bootstrapping piece that controls > whether to use 0-based or 1-based? I do not think we should allow the > setting (if we add one) to force 0-based usage with JPA bootstrapping; this > would only control how it works in native bootstrapping. > > Thoughts? > > On Wed, Sep 21, 2016 at 1:40 PM Steve Ebersole <steve(a)hibernate.org&gt; > wrote: > >> I never said anything about dropping support for named and positional >> parameters in native queries. >> >> I simply mentioned leveraging the new "JPA strict compliance" stuff I am >> adding to 6.0. The idea is to allow you to enable that and get feedback >> when you use non-portable things. >> >> >> On Tue, Sep 20, 2016 at 11:45 PM Jordan Gigov <coladict(a)gmail.com&gt; wrote: >> >>> Actually JPA defines it as "Only positional parameter binding and >>> positional access to result items may be portably used for native queries". >>> I believe "portably" means the providers are only required to support >>> positional, but not forbidden from supporting other. >>> >>> 2016-09-21 3:59 GMT+03:00 Steve Ebersole <steve(a)hibernate.org&gt;: >>> >>>> In the interest of questioning everything, just to make sure we are all >>>> on >>>> the same page, Hibernate's support for native SQL queries currently >>>> recognizes named parameters, positional parameters as well as JDBC-style >>>> parameters. >>>> >>>> JPA only defines support for "JDBC-style parameters" as valid for native >>>> SQL queries: >>>> {quote} >>>> It is assumed that for native queries the parameters themselves use the >>>> SQL >>>> syntax (i.e., “?”, rather than “?1”). >>>> {quote} >>>> >>>> Furthermore Hibernate does not support a native query using both >>>> positional >>>> parameters and JDBC-style parameters in the same query because it >>>> causes a >>>> non-determinism wrt the positions. >>>> >>>> I assume we want to continue to support that full complement of >>>> parameter >>>> types, with the positional/JDBC-style caveat. >>>> >>>> Further I assume we will hook up the use of any non-JDBC-style >>>> parameters >>>> in with the "strict JPA compliance" checking and throw an error when >>>> indicated. >>>> >>>> Anyone have objections to any of that? >>>> >>> _______________________________________________ >>>> hibernate-dev mailing list >>>> hibernate-dev(a)lists.jboss.org >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>> >>> _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

In the interest of questioning everything, just to make sure we are all on the same page, Hibernate's support for native SQL queries currently recognizes named parameters, positional parameters as well as JDBC-style parameters. JPA only defines support for "JDBC-style parameters" as valid for native SQL queries: {quote} It is assumed that for native queries the parameters themselves use the SQL syntax (i.e., “?”, rather than “?1”). {quote} Furthermore Hibernate does not support a native query using both positional parameters and JDBC-style parameters in the same query because it causes a non-determinism wrt the positions. I assume we want to continue to support that full complement of parameter types, with the positional/JDBC-style caveat. Further I assume we will hook up the use of any non-JDBC-style parameters in with the "strict JPA compliance" checking and throw an error when indicated. Anyone have objections to any of that? _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

In the interest of questioning everything, just to make sure we are all on the same page, Hibernate's support for native SQL queries currently recognizes named parameters, positional parameters as well as JDBC-style parameters. JPA only defines support for "JDBC-style parameters" as valid for native SQL queries: {quote} It is assumed that for native queries the parameters themselves use the SQL syntax (i.e., “?”, rather than “?1”). {quote} Furthermore Hibernate does not support a native query using both positional parameters and JDBC-style parameters in the same query because it causes a non-determinism wrt the positions. I assume we want to continue to support that full complement of parameter types, with the positional/JDBC-style caveat. Further I assume we will hook up the use of any non-JDBC-style parameters in with the "strict JPA compliance" checking and throw an error when indicated. Anyone have objections to any of that? _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev