Java – why does spring nativequery with paging throw an sqlgrammarexception?

I tried to use spring 4.2 Run the following query in 4:

@Query(value="SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN,i.* FROM Item i) AS g WHERE RN between ?#{ #pageable.offset} and ?#{#pageable.offset + #pageable.pageSize}",countQuery="SELECT count(i.ID) FROM Item i",nativeQuery = true)
Page<Item> getItems(Pageable pageable);

This code follows the example shown here: https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/java/org/springframework/data/jpa/repository/sample/UserRepository.java#L539

This causes sqlgrammarexception:

Caused by: org.hibernate.exception.sqlGrammarException: Could not extract ResultSet
    at org.hibernate.exception.internal.sqlStateConversionDelegate.convert(sqlStateConversionDelegate.java:123) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.exception.internal.StandardsqlExceptionConverter.convert(StandardsqlExceptionConverter.java:49) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.spi.sqlExceptionHelper.convert(sqlExceptionHelper.java:126) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.spi.sqlExceptionHelper.convert(sqlExceptionHelper.java:112) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:910) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2554) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2540) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2365) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.sqlQueryImpl.list(sqlQueryImpl.java:141) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:191) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:100) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:91) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:462) ~[spring-data-commons-1.11.4.RELEASE.jar:?]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440) ~[spring-data-commons-1.11.4.RELEASE.jar:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) ~[spring-data-commons-1.11.4.RELEASE.jar:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]

This is by:

com.microsoft.sqlserver.jdbc.sqlServerException: Incorrect Syntax near ','.
    at com.microsoft.sqlserver.jdbc.sqlServerException.makeFromDatabaseError(sqlServerException.java:216) ~[sqljdbc4.jar:?]
    at com.microsoft.sqlserver.jdbc.sqlServerStatement.getNextResult(sqlServerStatement.java:1515) ~[sqljdbc4.jar:?]...

I have tried to run the same native query without paging, and it will run without any errors The query is as follows:

@Query(value="SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN,i.* FROM Item i) AS g WHERE RN between 0 and 50",nativeQuery = true)
    List<Item> getItems();

Is this the limitation of spring data or what I missed here?

Solution

I think this is a mistake in the spring data If the query contains order by, i.itemcategory ASC. Is appended to the spring data In another case, the spring data adds order by i.itemcategory ASC As a workaround, you can add order by 1 = 1 to the query

@Query(value="SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN,i.* FROM Item i) AS g WHERE RN between ?#{ #pageable.offset} and ?#{#pageable.offset + #pageable.pageSize},ORDER BY 1=1",nativeQuery = true)
Page<Item> getItems(Pageable pageable);