`QueryEnhancer.hasConstructorExpression()` returns `false` for `some_function(…) IS TRUE`

[troyzhxu]

When I upgraded SpringBoot from v2.7.18 to 3.3.4, Jpa encountered an error :

• RoleTmplRepository

public interface RoleTmplRepository extends CrudRepository<RoleTmpl, Integer> {

    @Query("""
    select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl
    where find_in_set(:appId, appIds) is true and id not in (:idList) and deleted = false
    """)
    List<RolePart> findRoleInfoListByIdNotInAndAppId(List<Integer> idList, int appId);

}

• bp.web.user.model.RolePart

package bp.web.user.model;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class RolePart {

    private int id;
    private String name;
    private String rkey;

}

• A ConverterNotFoundException throwed when invoking the findRoleInfoListByIdNotInAndAppId method

17:57:32.040 ERROR Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [bp.web.user.model.RolePart]] with root cause
org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [bp.web.user.model.RolePart]
	at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:294)
	at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:185)
	at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:165)
	at org.springframework.data.repository.query.ResultProcessor$ProjectingConverter.convert(ResultProcessor.java:305)
	at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.lambda$and$0(ResultProcessor.java:233)
	at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:240)
	at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:160)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:140)
	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:169)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:148)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:379)
	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:138)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:136)
	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:223)
	at jdk.proxy2/jdk.proxy2.$Proxy227.findRoleInfoListByIdNotInAndAppId(Unknown Source)
	at bp.web.saas.service.SaasRoleService.getUnownedRoleTmplList(SaasRoleService.java:32)
	at bp.web.saas.SaasController.appInstall(SaasController.java:152)
	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:355)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:174)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)
	at bp.web.saas.SaasController$$SpringCGLIB$$0.appInstall(<generated>)
	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.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:113)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:384)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
	at java.base/java.lang.Thread.run(Thread.java:833)

[mp911de]

I'm not able to reproduce the issue. Care to provide a reproducer?

[troyzhxu]

OK, This repository has reproduced the issue:
https://github.com/troyzhxu/sb3-data-jpa-issue-demo
@mp911de

[troyzhxu]

I found this:

public interface RoleTmplRepository extends CrudRepository<RoleTmpl, Integer> {

    /**
     * invoking this method, a ConverterNotFoundException will be thrown.
     */
    @Query("""
    select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl
    where find_in_set(:appId, appIds) is true and id not in (:idList) and deleted = false
    """)
    List<RolePart> findRoleInfoListByIdNotInAndAppId(List<Integer> idList, int appId);

    /**
     * not using 'new bp.web.user.model.RolePart(id, name, rkey)' in sql, it works.
     */
    @Query("""
    from RoleTmpl
    where find_in_set(:appId, appIds) is true and id not in (:idList) and deleted = false
    """)
    List<RoleTmpl> findAllByIdNotInAndAppId(List<Integer> idList, int appId);

    /**
     * not using find_in_set in sql, it works too.
     */
    @Query("""
    select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl
    where id not in (:idList) and deleted = false
    """)
    List<RolePart> findRoleInfoListByIdNotIn(List<Integer> idList);

}

[troyzhxu]

I found through debugging that the root cause of this issue is that HqlQueryParser thinks my HQL syntax is incorrect:

Line 2:34 mismatched input 'is' expecting {',', ')', '+', '-', '/', '||', '[', '.', '*', BY, DAY, EPOCH, HOUR, MINUTE, MONTH, NANOSECOND, QUARTER, SECOND, WEEK, YEAR}; Bad JPQL grammar [select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl
where find_in_set(:appId, appIds) is true and id not in (:idList) and deleted = false
]

Is there really a syntax error in the HQL I wrote, and how should I correct it?

select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl
where find_in_set(:appId, appIds) is true and id not in (:idList) and deleted = false

[troyzhxu]

I try to replace is true with = true, and it will throw SemanticException :

org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.lang.Object' with right expression of type 'java.lang.Boolean'

I try to replace is true with = 1, and it will throw SemanticException :

org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.lang.Object' with right expression of type 'java.lang.Integer'

I try to remove is true, and it will throw SemanticException too:

org.hibernate.query.SemanticException: Non-boolean expression used in predicate context: find_in_set(:appId,appIds) [select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl

[mp911de]

Thanks for further analysis, we need to fix:

Line 2:34 mismatched input 'is' expecting {',', ')', '+', '-', '/', '||', '[', '.', '*', BY, DAY, EPOCH, HOUR, MINUTE, MONTH, NANOSECOND, QUARTER, SECOND, WEEK, YEAR}; Bad JPQL grammar [select new bp.web.user.model.RolePart(id, name, rkey) from RoleTmpl
where find_in_set(:appId, appIds) is true and id not in (:idList) and deleted = false
]