search

spring-projects / spring-data-jpa

Simplifies the development of creating a JPA-based data access layer.
https://spring.io/projects/spring-data-jpa/
Apache License 2.0
2.98k stars 1.41k forks source link

BadJpqlGrammarException with the latest release when using query hints inside the query #3385

Open vamsigangarapu opened 7 months ago

vamsigangarapu commented 7 months ago

We are facing an issue with the latest version of Spring Boot 3.2.2 upgrade.

My SQL query is:

@Query(value = """
        SELECT c.key, c.code, c.description \
        FROM #{#entityName} c WITH (FORCESEEK) WHERE EXISTS (SELECT TOP(1) 1 FROM testData td \
        WHERE td.key=c.key) AND c.key BETWEEN :minKey AND :maxKey\
        """, nativeQuery = true)
List<T> getAllKeys(@Param("minKey") Long minKey, @Param("maxKey") Long maxKey);

The issue we have: Line 1:92 mismatched input 'WITH' expecting {<EOF>, ',', CROSS, EXCEPT, FULL, GROUP, INNER, INTERSECT, JOIN, LEFT, ORDER, OUTER, RIGHT, UNION, WHERE}; Bad JPQL grammar [SELECT c.key, c.code, c.description FROM TEST c WITH (FORCESEEK) WHERE EXISTS (SELECT TOP(1) 1 FROM testData td WHERE td.key=c.key) AND c.key BETWEEN :minKey AND :maxKey]

The query was working fine before the upgrade in Spring Boot 2.6.14

Thanks for any advice to fix the issue

quaff commented 7 months ago

It's weird BadJpqlGrammarException is thrown since nativeQuery indicate the statement is SQL not JPQL, is it a known issue? @christophstrobl

mp911de commented 7 months ago

From the exception, I assume JSQLParser is throwing the exception.

vamsigangarapu commented 7 months ago

Adding stacktrace.

java.util.concurrent.CompletionException: org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:92 mismatched input 'WITH' expecting {<EOF>, ',', CROSS, EXCEPT, FULL, GROUP, INNER, INTERSECT, JOIN, LEFT, ORDER, OUTER, RIGHT, UNION, WHERE}; Bad JPQL grammar [SELECT c.key, c.code, c.description FROM TEST c WITH (FORCESEEK) WHERE EXISTS (SELECT TOP(1) 1 FROM testData td WHERE td.key=c.key) AND c.key BETWEEN :minKey AND :maxKey]
    at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:315)
    at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:320)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1770)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.exec(CompletableFuture.java:1760)
    at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
    at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182)
    at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655)
    at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622)
    at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:92 mismatched input 'WITH' expecting {<EOF>, ',', CROSS, EXCEPT, FULL, GROUP, INNER, INTERSECT, JOIN, LEFT, ORDER, OUTER, RIGHT, UNION, WHERE}; Bad JPQL grammar [SELECT c.key, c.code, c.description FROM TEST c WITH (FORCESEEK) WHERE EXISTS (SELECT TOP(1) 1 FROM testData td WHERE td.key=c.key) AND c.key BETWEEN :minKey AND :maxKey]
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
    at jdk.proxy3/jdk.proxy3.$Proxy209.getAllKeys(Unknown Source)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
    at com.demo.batch.project.config.database.datasource.aop.RepositoryInterceptor.proceed(RepositoryInterceptor.java:31)
    at jdk.internal.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:637)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:627)
    at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:71)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
    at com.demo.batch.project.config.database.datasource.aop.RouteInterceptorUtils.handleOperation(RouteInterceptorUtils.java:22)
    at com.demo.batch.project.config.database.datasource.aop.TargetDataSourceInterceptor.proceed(TargetDataSourceInterceptor.java:17)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:637)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:627)
    at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:71)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
    at jdk.proxy3/jdk.proxy3.$Proxy209.getAllKeys(Unknown Source)
    at com.demo.batch.project.preprocessing.getDataFromTestCodeFromDb(TestDataPreProcessor.java:125)
    at com.demo.batch.project.preprocessing.TestDataPreProcessor.lambda$3(TestDataPreProcessor.java:86)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
    ... 6 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:92 mismatched input 'WITH' expecting {<EOF>, ',', CROSS, EXCEPT, FULL, GROUP, INNER, INTERSECT, JOIN, LEFT, ORDER, OUTER, RIGHT, UNION, WHERE}; Bad JPQL grammar [SELECT c.key, c.code, c.description FROM TEST c WITH (FORCESEEK) WHERE EXISTS (SELECT TOP(1) 1 FROM testData td WHERE td.key=c.key) AND c.key BETWEEN :minKey AND :maxKey]
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport.renderSortedQuery(JpaQueryParserSupport.java:56)
    at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.applySorting(JpaQueryEnhancer.java:103)
    at org.springframework.data.jpa.repository.query.JpaQueryEnhancer.applySorting(JpaQueryEnhancer.java:115)
    at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:96)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:239)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:149)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 47 common frames omitted
Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:92 mismatched input 'WITH' expecting {<EOF>, ',', CROSS, EXCEPT, FULL, GROUP, INNER, INTERSECT, JOIN, LEFT, ORDER, OUTER, RIGHT, UNION, WHERE}; Bad JPQL grammar [SELECT c.key, c.code, c.description FROM TEST c WITH (FORCESEEK) WHERE EXISTS (SELECT TOP(1) 1 FROM testData td WHERE td.key=c.key) AND c.key BETWEEN :minKey AND :maxKey]
    at org.springframework.data.jpa.repository.query.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:327)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139)
    at org.springframework.data.jpa.repository.query.HqlParser.start(HqlParser.java:265)
    at org.springframework.data.jpa.repository.query.HqlQueryParser.parseQuery(HqlQueryParser.java:53)
    at org.springframework.data.jpa.repository.query.HqlQueryParser.parse(HqlQueryParser.java:63)
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport$ParseState.lambda$0(JpaQueryParserSupport.java:182)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:135)
    at org.springframework.data.util.Lazy.get(Lazy.java:113)
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport$ParseState.getContext(JpaQueryParserSupport.java:194)
    at org.springframework.data.jpa.repository.query.JpaQueryParserSupport.renderSortedQuery(JpaQueryParserSupport.java:54)
    ... 67 common frames omitted
christophstrobl commented 7 months ago

So things in this thread do not really line up. nativeQuery = true delegates the parsing to JSqlParser if present, which does not understand FORCESEEK and will error with ParseException: Encountered unexpected token: "FORCESEEK" because it only allows INDEX & NOLOCK. Parsing the same input as non native with the HqlParser (as indicated in the provided stack trace) causes the parser to fail with BadJpqlGrammarException on the (FORCESEEK) token. @vamsigangarapu it would help if you could spend some time to provide a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem, so we can understand why the hql parser is used for a native query.

vamsigangarapu commented 7 months ago

Here is a sample project to reproduce the error

demo.zip