search

spring-projects / spring-boot

Spring Boot
https://spring.io/projects/spring-boot
Apache License 2.0
74.82k stars 40.6k forks source link

After update to spring boot 2.6.3 from 2.5.8 hibernate does not work in some query syntax #29899

Closed Sbolder closed 2 years ago

Sbolder commented 2 years ago

i have update spring boot from 2.5.x to 2.6.3 and now the query like: @Query("select count(s.subjectId) from PreCheckEnquiredSubjectEntity s where s.key.requestId = :requestId and s.errors is not empty") long countSubjectEnquiredWithBusinessError(@Param("requestId") String requestId);

return me an error:

[2022-02-17 15:02:10.382] [,] [main] WARN  {org.hibernate.engine.jdbc.spi.SqlExceptionHelper} - SQL Error: 102, SQLState: S0001
[2022-02-17 15:02:10.382] [,] [main] ERROR {org.hibernate.engine.jdbc.spi.SqlExceptionHelper} - Incorrect syntax near ','.
[2022-02-17 15:02:10.393] [,] [main] ERROR {org.springframework.batch.core.step.AbstractStep} - Encountered an error executing step StageUdfStorageStep in job BANK1-CatchSubmission
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy186.findSubjectIdsForRequestId(Unknown Source)
    at com.crif.ctch.app.batch.submission.readers.CustomStageUdfStorageReader.beforeStep(CustomStageUdfStorageReader.java:45)
    at org.springframework.batch.core.listener.CompositeStepExecutionListener.beforeStep(CompositeStepExecutionListener.java:79)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:204)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:152)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:68)
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:68)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
    at org.springframework.batch.core.job.flow.support.state.FlowState.handle(FlowState.java:56)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:137)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:320)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:149)
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:140)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:128)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy208.run(Unknown Source)
    at com.crif.ctch.app.batch.submission.runners.JobRunner.doRun(JobRunner.java:224)
    at com.crif.ctch.app.batch.submission.runners.JobRunner.run(JobRunner.java:197)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:760)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:750)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:309)
    at com.crif.ctch.app.batch.submission.App.main(App.java:31)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2322)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
    at org.hibernate.loader.Loader.doQuery(Loader.java:956)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
    at org.hibernate.loader.Loader.doList(Loader.java:2868)
    at org.hibernate.loader.Loader.doList(Loader.java:2850)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
    at org.hibernate.loader.Loader.list(Loader.java:2677)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1454)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
    at org.hibernate.query.Query.getResultList(Query.java:165)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:406)
    at com.sun.proxy.$Proxy238.getResultList(Unknown Source)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 40 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:456)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
    ... 78 common frames omitted

in the update hibernate version was changed from 5.4.x to 5.6.3.

To complete information for debug attach the entity declaration:

@Entity
@Table(name = "PRECHECK_ENQUIRED_SUBJECTS")
public class PreCheckEnquiredSubjectEntity implements Traceable {

    @EmbeddedId
    private EnquiredSubjectRawKey key;

    @Column(name = "SUBJECT_ID", nullable = true)
    private String subjectId;

    @Column(name = "SUBJECT_INDEX", nullable = false)
    private Long subjectIndex;

    @OneToMany(mappedBy = "preCheckEnquiredSubject", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<StageErrorEntity> errors;
@Entity
@Table(name = "STAGE_ERRORS")
public class StageErrorEntity extends StageError {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns(value = {
            @JoinColumn(name = "REQUEST_ID", referencedColumnName = "REQUEST_ID", nullable = false, insertable = false, updatable = false),
            @JoinColumn(name = "ROW_ID", referencedColumnName = "ROW_ID", nullable = false, insertable = false, updatable = false),
            @JoinColumn(name = "SOURCE", referencedColumnName = "SOURCE", nullable = false, insertable = false, updatable = false)
    })
    private PreCheckEnquiredSubjectEntity preCheckEnquiredSubject;

    public PreCheckEnquiredSubjectEntity getPreCheckEnquiredSubject() {
        return this.preCheckEnquiredSubject;
    }

    public void setPreCheckEnquiredSubject(PreCheckEnquiredSubjectEntity preCheckEnquiredSubject) {
        this.preCheckEnquiredSubject = preCheckEnquiredSubject;
    }

}

someone could help me to solve ? in previous spring boot version the query work do good.

thank you

bclozel commented 2 years ago

Thanks for getting in touch, but it feels like this is a question that would be better suited to StackOverflow. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements. Feel free to update this issue with a link to the re-posted question (so that other people can find it) or add some more details if you feel this is a genuine bug.

Sbolder commented 2 years ago

@bclozel I think this is a bug. the same code, in version 2.5.8 work done, and after update the query failed. So i have checked the syntax of query, and is the same, but with version 2.6.3 of spring boot return me a syntax error.

in Projects release note, i have not found any mention of this type.

No problem for me, i have solved with rollback spring-boot version to 2.5.9, and now all work, with change nothing in my code. So i think the bug are present in version 2.6.3, i have moved the answer in : https://stackoverflow.com/questions/71159804/after-update-to-spring-boot-2-6-3-from-2-5-x-hibernate-does-not-work-in-some-que

have a good day.

Thank you. Davide

wilkinsona commented 2 years ago

@Sbolder As you have already observed, Spring Boot 2.6.x upgrades to Hibernate 5.6.x. The change in behaviour that you are seeing is almost certainly due to a change in Hibernate and that's out of Spring Boot's control. Another possible cause could be the version of the SQL Server JDBC driver. Spring Boot 2.6.x upgrades to 9.4.x (from 9.2.x in Boot 2.5).