spring-data-jpa
spring-data-jpa copied to clipboard
Published
20 hours ago •
spring-projects
spring-projects
Error binding countQuery parameters on @Query using nativeQuery and pagination
Hi,
Following up from: #2066
I am seeing an error in one of my queries. It seems there are some parameter binding errors while building the countQuery of a paginated native query. The error causes Page.getTotalResults() to return the length of the page instead of the actual totalResults. (#1937). Note that ONLY the countQuery fails (silently). The original query runs just fine.
The code that raises said error is this:
@Query(value = "" +
"SELECT " +
" a.id, " +
" POWER(" +
" POWER(ag.weight - :#{#user.animalAge.weight}, 2) + " +
" POWER(ac.weight - :#{#user.animalCastrated.weight}, 2) + " +
" POWER(aco.weight - :#{#user.animalColor.weight}, 2) + " +
" POWER(age.weight - :#{#user.animalGender.weight}, 2) + " +
" POWER(asi.weight - :#{#user.animalSize.weight}, 2) " +
" ,0.5) AS score " +
"FROM announce a " +
"JOIN animal_age ag ON ag.id = a.animal_age_id " +
"JOIN animal_castrated ac ON ac.id = a.animal_castrated_id " +
"JOIN animal_color aco ON aco.id = a.animal_color_id " +
"JOIN animal_gender age ON age.id = a.animal_gender_id " +
"JOIN animal_size asi ON asi.id = a.animal_size_id " +
"WHERE a.animal_type_id = :#{#user.animalType.id} " +
"AND a.status = :#{#status.name()} " +
"AND a.user_id != :#{#user.id} " +
"ORDER BY score ASC",
nativeQuery = true,
countQuery = "" +
"SELECT COUNT(*) " +
"FROM announce a " +
"JOIN animal_age ag ON ag.id = a.animal_age_id " +
"JOIN animal_castrated ac ON ac.id = a.animal_castrated_id " +
"JOIN animal_color aco ON aco.id = a.animal_color_id " +
"JOIN animal_gender age ON age.id = a.animal_gender_id " +
"JOIN animal_size asi ON asi.id = a.animal_size_id " +
"WHERE a.animal_type_id = :#{#user.animalType.id} " +
"AND a.status = :#{#status.name()} " +
"AND a.user_id != :#{#user.id} ")
Page<AnnounceSuggested> findAllSuggestedByUser(@Param("user") User user, @Param("status") AnnounceStatus status, Pageable pageable);
Note that I have many other natives queries like this, but only this one fails (which is why I did not make a simpler example).
Bellow is the error that is raised (the error is raised multiple times with different $synthetic$ parameters):
2021-01-20 15:19:58.772 INFO 27824 --- [nio-8080-exec-4] j.r.q.QueryParameterSetter$ErrorHandling : Silently ignoring
java.lang.IllegalArgumentException: Could not locate named parameter [__$synthetic$__6], expecting one of [__$synthetic$__3, __$synthetic$__2, __$synthetic$__1]
at org.hibernate.query.internal.ParameterMetadataImpl.getNamedParameterDescriptor(ParameterMetadataImpl.java:229) ~[hibernate-core-5.4.22.Final.jar:5.4.22.Final]
at org.hibernate.query.internal.ParameterMetadataImpl.getQueryParameter(ParameterMetadataImpl.java:198) ~[hibernate-core-5.4.22.Final.jar:5.4.22.Final]
at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:188) ~[hibernate-core-5.4.22.Final.jar:5.4.22.Final]
at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:490) ~[hibernate-core-5.4.22.Final.jar:5.4.22.Final]
at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:607) ~[hibernate-core-5.4.22.Final.jar:5.4.22.Final]
at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:62) ~[hibernate-core-5.4.22.Final.jar:5.4.22.Final]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$BindableQuery.setParameter(QueryParameterSetter.java:327) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$4(QueryParameterSetter.java:118) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$2.execute(QueryParameterSetter.java:151) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:118) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:82) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateCountQuery(AbstractStringBasedJpaQuery.java:122) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createCountQuery(AbstractJpaQuery.java:254) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.count(JpaQueryExecution.java:183) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.lambda$doExecute$0(JpaQueryExecution.java:178) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.repository.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:62) ~[spring-data-commons-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:177) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195) ~[spring-data-commons-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152) ~[spring-data-commons-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130) ~[spring-data-commons-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80) ~[spring-data-commons-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367) ~[spring-tx-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149) ~[spring-data-jpa-2.3.5.RELEASE.jar:2.3.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at com.sun.proxy.$Proxy140.findAllSuggestedByUser(Unknown Source) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at com.sun.proxy.$Proxy66.findAllSuggestedByUser(Unknown Source) ~[na:na]
at br.edu.ifrs.canoas.webapp.service.SuggestionService.findAllByUser(SuggestionService.java:31) ~[classes/:na]
at br.edu.ifrs.canoas.webapp.service.SuggestionService.findFirstFive(SuggestionService.java:27) ~[classes/:na]
at br.edu.ifrs.canoas.webapp.controller.HomeController.home(HomeController.java:32) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:626) ~[tomcat-embed-core-9.0.39.jar:4.0.FR]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.39.jar:4.0.FR]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.springframework.web.servlet.resource.ResourceUrlEncodingFilter.doFilter(ResourceUrlEncodingFilter.java:65) ~[spring-webmvc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:126) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:118) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:158) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.3.5.RELEASE.jar:5.3.5.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.10.RELEASE.jar:5.2.10.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.39.jar:9.0.39]
If you need anything else feel free to ask.
Thank you!
I just noticed that if I remove the where part of the count query and, as a consequence the parameter bindings, it does not raise any errors. So the problem seems to be on those where clause parameters. Problem still happens on 2.3.8 of spring boot
I just realized that if I move the count query to a method of its own, it also works. So, as a workaround, I switched the return type from Page to List and ran the countQuery as another method.
@Query(value = "" +
"SELECT " +
" a.id, " +
" POWER(" +
" POWER(ag.weight - :#{#user.animalAge.weight}, 2) + " +
" POWER(ac.weight - :#{#user.animalCastrated.weight}, 2) + " +
" POWER(aco.weight - :#{#user.animalColor.weight}, 2) + " +
" POWER(age.weight - :#{#user.animalGender.weight}, 2) + " +
" POWER(asi.weight - :#{#user.animalSize.weight}, 2) " +
" ,0.5) AS score " +
"FROM announce a " +
"JOIN animal_age ag ON ag.id = a.animal_age_id " +
"JOIN animal_castrated ac ON ac.id = a.animal_castrated_id " +
"JOIN animal_color aco ON aco.id = a.animal_color_id " +
"JOIN animal_gender age ON age.id = a.animal_gender_id " +
"JOIN animal_size asi ON asi.id = a.animal_size_id " +
"WHERE a.animal_type_id = :#{#user.animalType.id} " +
"AND a.status = :#{#status.name()} " +
"AND a.user_id != :#{#user.id} " +
"ORDER BY score ASC",
nativeQuery = true)
List<AnnounceSuggested> findAllSuggestedByUser(@Param("user") User user, @Param("status") AnnounceStatus status, Pageable pageable);
@Query(value = "" +
"SELECT COUNT(*) " +
"FROM announce a " +
"JOIN animal_age ag ON ag.id = a.animal_age_id " +
"JOIN animal_castrated ac ON ac.id = a.animal_castrated_id " +
"JOIN animal_color aco ON aco.id = a.animal_color_id " +
"JOIN animal_gender age ON age.id = a.animal_gender_id " +
"JOIN animal_size asi ON asi.id = a.animal_size_id " +
"WHERE a.animal_type_id = :#{#user.animalType.id} " +
"AND a.status = :#{#status.name()} " +
"AND a.user_id != :#{#user.id} ",
nativeQuery = true)
Long countSuggestedByUser(@Param("user") User user, @Param("status") AnnounceStatus status);
Same issue. Basicly the issue is due the query param. in the "select" clause isn't appearing in the count query.
Could be related, but I don't think it is a duplicate. In that issue the OP is not using a native query, thus spring generates the count query for him. In this case, I am writing both queries, spring is only binding parameters.
I am writing both queries, spring is only binding parameters. Thanks for pointing that out. I completely missed that part.
Same issue with Spring Boot 2.5.6 with pageable not native queries. News about the fix? I have a number of pageable queries with parameters in order clause, but the issue appears only in some of them and only with certain values of pageSize (maybe it depends on the optimizations applied in the building of the count query). I have to find a workaround for each query when the issue appears; otherwise I have to scan all the queries and find a workaround for all of them!
I just realized that if I move the count query to a method of its own, it also works. So, as a workaround, I switched the return type from Page to List and ran the countQuery as another method.
@Query(value = "" + "SELECT " + " a.id, " + " POWER(" + " POWER(ag.weight - :#{#user.animalAge.weight}, 2) + " + " POWER(ac.weight - :#{#user.animalCastrated.weight}, 2) + " + " POWER(aco.weight - :#{#user.animalColor.weight}, 2) + " + " POWER(age.weight - :#{#user.animalGender.weight}, 2) + " + " POWER(asi.weight - :#{#user.animalSize.weight}, 2) " + " ,0.5) AS score " + "FROM announce a " + "JOIN animal_age ag ON ag.id = a.animal_age_id " + "JOIN animal_castrated ac ON ac.id = a.animal_castrated_id " + "JOIN animal_color aco ON aco.id = a.animal_color_id " + "JOIN animal_gender age ON age.id = a.animal_gender_id " + "JOIN animal_size asi ON asi.id = a.animal_size_id " + "WHERE a.animal_type_id = :#{#user.animalType.id} " + "AND a.status = :#{#status.name()} " + "AND a.user_id != :#{#user.id} " + "ORDER BY score ASC", nativeQuery = true) List<AnnounceSuggested> findAllSuggestedByUser(@Param("user") User user, @Param("status") AnnounceStatus status, Pageable pageable); @Query(value = "" + "SELECT COUNT(*) " + "FROM announce a " + "JOIN animal_age ag ON ag.id = a.animal_age_id " + "JOIN animal_castrated ac ON ac.id = a.animal_castrated_id " + "JOIN animal_color aco ON aco.id = a.animal_color_id " + "JOIN animal_gender age ON age.id = a.animal_gender_id " + "JOIN animal_size asi ON asi.id = a.animal_size_id " + "WHERE a.animal_type_id = :#{#user.animalType.id} " + "AND a.status = :#{#status.name()} " + "AND a.user_id != :#{#user.id} ", nativeQuery = true) Long countSuggestedByUser(@Param("user") User user, @Param("status") AnnounceStatus status);
@Carinaemerim Thanks for this workaround.
To expand on this, you can create a Page using:
public Page<AnnounceSuggested> findSuggestedByUser(User user, AnnounceStatus status, Pageable pageable) {
List<AnnounceSuggested> content = findAllSuggestedByUser(user, status, pageable);
long total = countSuggestedByUser(user, status);
return new PageImpl(content, pageable, total);
}
I am too facing this issue with @NativeQuery and Page type as response. But I am getting "Could not locate named parameter" for order by clause.
I'm facing something similar: I have a query with 3 parameters, but my count query only needs 1. But spring-data-jpa doesn't seem to like that, and throws an exception:
Caused by: java.lang.IllegalArgumentException: Could not locate named parameter [latitude], expecting one of [objectId]
I'll make a separate issue for this
As of spring-boot 2.7.5 I am still seeing this issue using a native query:
2022-11-02 11:25:40.354 INFO 15850 --- [nio-9740-exec-1] j.r.q.QueryParameterSetter$ErrorHandling : Silently ignoring
java.lang.IllegalArgumentException: Could not locate named parameter [__$synthetic$__55], expecting one of [__$synthetic$__29, __$synthetic$__28, __$synthetic$__27, __$synthetic$__26, __$synthetic$__25, __$synthetic$__24, __$synthetic$__23, __$synthetic$__22, __$synthetic$__21, __$synthetic$__20, __$synthetic$__19, __$synthetic$__18, __$synthetic$__17, __$synthetic$__16, __$synthetic$__15, __$synthetic$__14, __$synthetic$__13, __$synthetic$__12, __$synthetic$__11, __$synthetic$__10, __$synthetic$__54, __$synthetic$__53, __$synthetic$__52, __$synthetic$__49, __$synthetic$__48, __$synthetic$__47, __$synthetic$__46, __$synthetic$__45, __$synthetic$__44, __$synthetic$__43, __$synthetic$__42, __$synthetic$__41, __$synthetic$__51, __$synthetic$__50, __$synthetic$__39, __$synthetic$__38, __$synthetic$__37, __$synthetic$__36, __$synthetic$__2, __$synthetic$__35, __$synthetic$__1, __$synthetic$__34, __$synthetic$__33, __$synthetic$__32, __$synthetic$__31, __$synthetic$__30, __$synthetic$__8, __$synthetic$__40, __$synthetic$__7, __$synthetic$__9, __$synthetic$__4, __$synthetic$__3, __$synthetic$__6, __$synthetic$__5]
at org.hibernate.query.internal.ParameterMetadataImpl.getNamedParameterDescriptor(ParameterMetadataImpl.java:229) ~[hibernate-core-5.6.12.Final.jar:5.6.12.Final]
at org.hibernate.query.internal.ParameterMetadataImpl.getQueryParameter(ParameterMetadataImpl.java:198) ~[hibernate-core-5.6.12.Final.jar:5.6.12.Final]
at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:189) ~[hibernate-core-5.6.12.Final.jar:5.6.12.Final]
at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:501) ~[hibernate-core-5.6.12.Final.jar:5.6.12.Final]
at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:650) ~[hibernate-core-5.6.12.Final.jar:5.6.12.Final]
at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:66) ~[hibernate-core-5.6.12.Final.jar:5.6.12.Final]
If I change it to ordinals I get 1 through 54 and it tells me there are no other ordinals beyond 54.
It seems to be an issue with the countQuery itself, but I'm not 100% sure.
