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

[vamsigangarapu]

trafficstars

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]

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

[mp911de]

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

[vamsigangarapu]

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]

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]

Here is a sample project to reproduce the error

demo.zip