After update to spring boot 2.6.3 from 2.5.X hibernate does not work in some query syntax

Question

someone could help me to solve this bug?

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;

and error entity:


@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

Can you put hibernate in debug mode an see what query is realy being performed? It appears to be an error in SQL server not in your code. Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','. — pringi, Feb 17 '22 at 15:47
select count(prechecken0_.SUBJECT_ID) as col_0_0_ from PRECHECK_ENQUIRED_SUBJECTS prechecken0_ where prechecken0_.REQUEST_ID=? and (exists (select (errors1_.ERROR_ID, errors1_.REQUEST_ID) from STAGE_ERRORS errors1_ where prechecken0_.REQUEST_ID=errors1_.REQUEST_ID and prechecken0_.ROW_ID=errors1_.ROW_ID and prechecken0_.SOURCE=errors1_.SOURCE)) @pringi — Davide Boldrin, Feb 17 '22 at 16:46
If you can, rollback for the working version and incrementally increase spring boot version for each one that exists (or something to latest 2.4, first 2.5, latest 2.5, first 2.6 and latest 2.6) to see where the error is introduced. Also do you have the latest sqlserver driver? — pringi, Feb 17 '22 at 17:03
@DavideBoldrin I'm facing the same problem. Were you able to resolve it? — Michał Kaciuba, Apr 22 '22 at 09:54
@MichałKaciuba Hi, i have posted now my workaournd, it work done for me, but it's a workaround. — Davide Boldrin, Apr 26 '22 at 07:53

score 1 · Answer 1 · answered Apr 26 '22 at 07:52

I have found the following workaournd, and it work done for me:

    @Query("select count(s.subjectId) from PreCheckEnquiredSubjectEntity s where s.key.requestId = :requestId and size(s.errors) <> 0")
long countSubjectEnquiredWithBusinessError(@Param("requestId") String requestId);

After update to spring boot 2.6.3 from 2.5.X hibernate does not work in some query syntax

1 Answers1