After migrate 1.4.3 release to 1.5.1 release, sql server throw an exception when paging

[fcjxxl]

I use @PageableDefault in controller for pagination, like this:
@PageableDefault(value = 20, sort = { "id" }, direction = Direction.DESC) Pageable pageable
in 1.4.3 it runs without problems, when migrate to 1.5.1, it throws an exception as follows:

_com.microsoft.sqlserver.jdbc.SQLServerException: '@P0' 附近有语法错误。(Incorrect syntax near '@P0')
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159) ~[sqljdbc-4.2.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:284) ~[sqljdbc-4.2.jar:na]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2117) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:50) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:589) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:409) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_121]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:504) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:489) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at com.sun.proxy.$Proxy106.findAll(Unknown Source) ~[na:na]_

Then I compared the two SQL statements in 1.4.3 & 1.5.1, find out the difference:
Hibernate: select TOP(?) patient0_.pat_id as pat_id1_15_...... ----1.4.3
Hibernate: select TOP ? patient0_.pat_id as pat_id1_15_....... ----1.5.1

Is the lack of "()" cause the exception?

[martin-g]

Hi @fcjxxl !
This looks to me like Hibernate related, not Spring Boot.
Check whether the Hibernate version is different with 1.4.3 and 1.5.1 and set ${hibernate.version} in your pom.xml to the working one. If this helps then please report the problem at Hibernate's issue tracker!

[fcjxxl]

@martin-g I am sorry to tell you that the Hibernate version in 1.4.3 and 1.5.1 is exactly the same: 5.0.11.Final

[snicoll]

Several things here

Do you get "Unable to determine jdbc url from datasource" - We've added something to auto-detect the database but it's broken for sql server (fixed in 1.5.2-SNAPSHOT) already.

Could you please try with 1.5.2.BUILD-SNAPSHOT

A sample that reproduces the problem would be ideal. Also, please double check you're using the same Hibernate version.

[fcjxxl]

Hi @snicoll , I tried with 1.5.2.BUILD-SNAPSHOT, but still met the same problem.
I am sure that the Hibernate version is 5.0.11.Final.
I am trying to make a sample now.

[fcjxxl]

sql-server-paging-demo.zip
@martin-g @snicoll Here is the sample, just run the JUnit Test "pagingTest" in ApplicationTests.java and you will see the exception.
Change 1.5.2.BUILD-SNAPSHOT to 1.4.3.RELEASE in pom.xml and run the test then there will be no exceptions at all.

PS: I add the sql server dependency manually by install:install-file -Dfile=sqljdbc42.jar -Dpackaging=jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc -Dversion=4.2, you just remove the sql server dependency in pom.xml and add the sqljdbc42.jar to classpath.

[odrotbohm]

After quick inspection it turns out that for some still to be determined reason, Boot 1.5 bootstraps Hibernate with SQLServerDialect, while 1.4 runs on SQLServer2008Dialect. I guess it's worth playing with the database platform settings to make sure Hibernate is running on the dialect that actually suits your SQL Server instance.

[fcjxxl]

@olivergierke Thank you for your advice. I add spring.jpa.database-platform=SQLServer2008 in application.properties, and there is no such exception now.

[snicoll]

@olivergierke thanks for looking into it. @fcjxxl thanks for confirming. I'll investigate why the dialect detection has changed.

[odrotbohm]

What I am suspecting is that Hibernate chooses a different default depending on whether we don't hand it anything at all VS. when we sort of detect the database from the JDBC URL and thus hand SQL_SERVER down to the infrastructure.

[snicoll]

OK so that's a side effect of #7708 - In 1.4, the Database is DEFAULT which lets Hibernate figure out what to do. In 1.5, we auto-detect it properly (the Database is SQL_SERVER, the bug I mentioned above was in another code path). Because the Database is set, HibernateJpaVendorAdapter#determineDatabaseDialectClass sets the dialect to SQLServerDialect.

On paper, it looks all good. Except there are several dialects for SQL Server... Not sure what to do here.