[hibernate-issues] [JIRA] (HHH-16020) Hibernate doesn't set the correct bind parameter for the offset when using NativeQuery.setFirstResult() on Postgresql

Johannes Edmeier ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5d9b11b... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNTI3NzBmMjdm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16020?atlOrigin=eyJpIjoiNTI3Nz... ) HHH-16020 ( https://hibernate.atlassian.net/browse/HHH-16020?atlOrigin=eyJpIjoiNTI3Nz... ) Hibernate doesn't set the correct bind parameter for the offset when using NativeQuery.setFirstResult() on Postgresql ( https://hibernate.atlassian.net/browse/HHH-16020?atlOrigin=eyJpIjoiNTI3Nz... ) Issue Type: Bug Affects Versions: 6.1.6 Assignee: Unassigned Attachments: demo.zip Components: hibernate-core Created: 11/Jan/2023 01:12 AM Priority: Critical Reporter: Johannes Edmeier ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5d9b11b... ) When trying to read from an offset > 0 like this: (See attached demo.zip for a full example to reproduce) em.createNativeQuery( "select id, name from person where name like :name" ) .setParameter( "name" , "A%" ) .setFirstResult(1) .getResultStream() .forEach( System.out::println); on postgresql 13 the following exception is thrown: Caused by: org.hibernate.exception.DataException: JDBC exception executing SQL [select id, name from person where name like ? offset ? rows] at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:101) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:253) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:33) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:49) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:187) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:559) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:350) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQueryScroll(JdbcSelectExecutorStandardImpl.java:191) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.scroll(JdbcSelectExecutorStandardImpl.java:114) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performScroll(NativeSelectQueryPlanImpl.java:149) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.query.sql.internal.NativeQueryImpl.doScroll(NativeQueryImpl.java:816) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.query.spi.AbstractSelectionQuery.scroll(AbstractSelectionQuery.java:433) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.query.spi.AbstractSelectionQuery.stream(AbstractSelectionQuery.java:441) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.query.Query.getResultStream(Query.java:139) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:401) ~[spring-orm-6.0.3.jar:6.0.3] at jdk.proxy3/jdk.proxy3.$Proxy110.getResultStream(Unknown Source) ~[na:na] at com.example.demo.DemoApplication.lambda$runner$0(DemoApplication.java:26) ~[main/:na] at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:758) ~[spring-boot-3.0.1.jar:3.0.1] ... 98 common frames omitted Caused by: org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2. at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:70) ~[postgresql-42.5.1.jar:42.5.1] at org.postgresql.core.v3.SimpleParameterList.setBinaryParameter(SimpleParameterList.java:137) ~[postgresql-42.5.1.jar:42.5.1] at org.postgresql.jdbc.PgPreparedStatement.bindBytes(PgPreparedStatement.java:1086) ~[postgresql-42.5.1.jar:42.5.1] at org.postgresql.jdbc.PgPreparedStatement.setInt(PgPreparedStatement.java:322) ~[postgresql-42.5.1.jar:42.5.1] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setInt(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na] at org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParameters(AbstractLimitHandler.java:198) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtEndOfQuery(AbstractLimitHandler.java:155) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:196) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final] ... 119 common frames omitted ( https://hibernate.atlassian.net/browse/HHH-16020#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16020#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:b01baad )