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.

[snicoll]

I've created SPR-15255 to discuss what we could do on the framework's side of things.

[jhoeller]

Most of the supported databases have several dialects. We're setting the latest dialect variants there in Spring Framework 5.0 already, but 4.3.x hasn't been updated due to its Hibernate 3.6-5.2 support constraints. SQL Server 2000 is really outdated though, we need to update that one.

I wasn't aware that Hibernate is doing smart autodetection for specific SQL Server versions in general. Why is it picking 2008 specifically when there's 2012 as well? Also, why wasn't Hibernate's own detection able to figure out the cases that your #7708 enhancement meant to cover?

[snicoll]

All I got from @olivergierke is that "It doesn't do for all databases.". I'd argue that if Hibernate does some smart detection for certain dialects, we shouldn't set it ourselves. I don't know if that's documented.

[snicoll]

@fcjxxl just to be sure, could you please also try with spring.jpa.database=default (this is going to let Hibernate detects the driver as before). I am afraid we'll have to document this even if we've fixed the framework side of things to use the 2008 dialect by default now.

[snicoll]

I've updated the doc and the release notes. Also 1.5.2 will auto-configure SQLServer2008Dialectsince Spring Framework 4.3.7 has just changed that.

[fcjxxl]

@snicoll Hi, I tried with spring.jpa.database=default , the console outputs Using dialect: org.hibernate.dialect.SQLServer2008Dialect and the application worked fine, but when I followed the release notes and used spring.jpa.database=none , the application failed to start:

2017-02-15 09:56:10.754 ERROR 849 --- [  restartedMain] o.s.b.b.PropertiesConfigurationFactory   : Properties configuration failed validation
2017-02-15 09:56:10.754 ERROR 849 --- [  restartedMain] o.s.b.b.PropertiesConfigurationFactory   : Field error in object 'spring.jpa' on field 'database': rejected value [none]; codes [typeMismatch.spring.jpa.database,typeMismatch.database,typeMismatch.org.springframework.orm.jpa.vendor.Database,typeMismatch]; arguments [org.springframework.context.support.DefaultMessageSourceResolvable: codes [spring.jpa.database,database]; arguments []; default message [database]]; default message [Failed to convert property value of type 'java.lang.String' to required type 'org.springframework.orm.jpa.vendor.Database' for property 'database'; nested exception is org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.String] to type [org.springframework.orm.jpa.vendor.Database] for value 'none'; nested exception is java.lang.IllegalArgumentException: No enum constant org.springframework.orm.jpa.vendor.Database.none]
2017-02-15 09:56:10.756  WARN 849 --- [  restartedMain] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'multipleDSConfig': Unsatisfied dependency expressed through field 'jpaProperties'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'spring.jpa-org.springframework.boot.autoconfigure.orm.jpa.JpaProperties': Could not bind properties to JpaProperties (prefix=spring.jpa, ignoreInvalidFields=false, ignoreUnknownFields=true, ignoreNestedProperties=false); nested exception is org.springframework.validation.BindException: org.springframework.boot.bind.RelaxedDataBinder$RelaxedBeanPropertyBindingResult: 1 errors
Field error in object 'spring.jpa' on field 'database': rejected value [none]; codes [typeMismatch.spring.jpa.database,typeMismatch.database,typeMismatch.org.springframework.orm.jpa.vendor.Database,typeMismatch]; arguments [org.springframework.context.support.DefaultMessageSourceResolvable: codes [spring.jpa.database,database]; arguments []; default message [database]]; default message [Failed to convert property value of type 'java.lang.String' to required type 'org.springframework.orm.jpa.vendor.Database' for property 'database'; nested exception is org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.String] to type [org.springframework.orm.jpa.vendor.Database] for value 'none'; nested exception is java.lang.IllegalArgumentException: No enum constant org.springframework.orm.jpa.vendor.Database.none]
2017-02-15 09:56:10.757  WARN 849 --- [  restartedMain] o.s.boot.SpringApplication               : Error handling failed (Error creating bean with name 'delegatingApplicationListener' defined in class path resource [org/springframework/security/config/annotation/web/configuration/WebSecurityConfiguration.class]: BeanPostProcessor before instantiation of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration': Initialization of bean failed; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'org.springframework.context.annotation.ConfigurationClassPostProcessor.importRegistry' available)
2017-02-15 09:56:10.761 ERROR 849 --- [  restartedMain] o.s.b.d.LoggingFailureAnalysisReporter   : 

***************************
APPLICATION FAILED TO START
***************************

Description:

Binding to target org.springframework.boot.autoconfigure.orm.jpa.JpaProperties@4d93dc18 failed:

    Property: spring.jpa.database
    Value: none
    Reason: Failed to convert property value of type 'java.lang.String' to required type 'org.springframework.orm.jpa.vendor.Database' for property 'database'; nested exception is org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.String] to type [org.springframework.orm.jpa.vendor.Database] for value 'none'; nested exception is java.lang.IllegalArgumentException: No enum constant org.springframework.orm.jpa.vendor.Database.none


Action:

Update your application's configuration

[snicoll]

Thanks for testing! The reason why it doesn't work is because I am an idiot. I meant to write default and I wrote none for some weird reason. I've updated the release notes so all should be good now.