Number of Params in complex Native Query is wrong [DATAJPA-1417]

[spring-projects-issues]

Thomas Laegler opened DATAJPA-1417 and commented

Dear Spring Data JPA Team,

I have a quite complex native Query (PostgreSQL with PostGIS) with 3 Parameters (tripId, lat, lng). But it looks like Spring is only detecting 2 Parameters in the Query.

The repository looks like this:

@SqlResultSetMapping(name = "StopDistanceMapping", entities = {@EntityResult(entityClass = StopDistance.class, fields = {@FieldResult(name = "stopId", column = "stop_id"), @FieldResult(name = "code", column = "stop_code"), @FieldResult(name = "lat", column = "stop_lat"), @FieldResult(name = "lng", column = "stop_lon"), @FieldResult(name = "sequence", column = "stop_sequence"), @FieldResult(name = "distance", column = "distance")})})
@NamedNativeQuery(name = "StopDistanceQuery", query = "WITH line AS (select sg.geom as line from shapes_geom sg, trips t WHERE t.trip_id = tripId AND t.shape_id = sg.shape_id) " + ", stops AS (select s.stop_code AS code, s.stop_id AS stopid, s.stop_lat AS lat, s.stop_lon AS lon, st.stop_sequence AS seq, ST_ClosestPoint(line.line, geom) AS stop FROM stops s, stop_times st, line WHERE st.trip_id = :tripId AND s.stop_id = st.stop_id), " + " pos AS (SELECT ST_ClosestPoint(line, pt) AS pt FROM (SELECT ST_SetSRID(ST_Point( :lng, :lat ), 4326) AS pt, sg.geom AS line FROM shapes_geom sg, trips t WHERE t.trip_id = :tripId AND t.shape_id = sg.shape_id) AS foo) " + " SELECT stops.code AS stop_code, stops.stopid AS stop_id, stops.lat AS stop_lat, stops.lon AS stop_lon, stops.seq AS stop_sequence, ST_Length(ST_LineSubstring(line.line, least(ST_LineLocatePoint(line, pos.pt), ST_LineLocatePoint(line, stops.stop)), greatest(ST_LineLocatePoint(line, pos.pt), ST_LineLocatePoint(line, stops.stop)))::geography) " + " AS distance FROM line, pos, stops ORDER BY distance ASC LIMIT 2", resultSetMapping = "StopDistanceMapping")
@Repository
public interface GtfsFlatFeedJpaRepository extends JpaRepository<FlatFeed, FlatFeedCompositeId> {
@Query(name = "StopDistanceQuery")
List<StopDistance> findNextStopByTripIdAndPosition(@Param("tripId") String tripId, @Param("lat") double lat, @Param("lng") double lng);
}

But unfortunately Spring Data JPA is not recognising the Parameter "tripId" in the NamedNativeQuery and I get the following Exception on Spring Start-up:

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.2018-09-06 09:11:10.320 ERROR 30147 --- [           main] o.s.boot.SpringApplication               : Application run failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'gtfsFlatFeedService': Unsatisfied dependency expressed through field 'flatFeedRepo'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'gtfsFlatFeedJpaRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List nz.govt.nzta.mobilityos.scheduler.gtfs.GtfsFlatFeedJpaRepository.findNextStopByTripIdAndPosition(java.lang.String,double,double)! At least 3 parameter(s) provided but only 2 parameter(s) present in query. at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:586) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:91) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:372) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1341) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:572) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:495) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:759) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869) ~[spring-context-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140) ~[spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:762) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:398) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:330) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1258) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1246) [spring-boot-2.0.4.RELEASE.jar:2.0.4.RELEASE] at nz.govt.nzta.mobilityos.scheduler.Application.main(Application.java:14) [classes/:na]Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'gtfsFlatFeedJpaRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List nz.govt.nzta.mobilityos.scheduler.gtfs.GtfsFlatFeedJpaRepository.findNextStopByTripIdAndPosition(java.lang.String,double,double)! At least 3 parameter(s) provided but only 2 parameter(s) present in query. at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1699) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:573) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:495) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1135) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1062) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:583) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] ... 19 common frames omittedCaused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract java.util.List nz.govt.nzta.mobilityos.scheduler.gtfs.GtfsFlatFeedJpaRepository.findNextStopByTripIdAndPosition(java.lang.String,double,double)! At least 3 parameter(s) provided but only 2 parameter(s) present in query. at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:82) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:103) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:208) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:553) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:546) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[na:1.8.0_181] at java.util.Iterator.forEachRemaining(Iterator.java:116) ~[na:1.8.0_181] at java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1049) ~[na:1.8.0_181] at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471) ~[na:1.8.0_181] at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:1.8.0_181] at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499) ~[na:1.8.0_181] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:548) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:538) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at java.util.Optional.map(Optional.java:215) ~[na:1.8.0_181] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:538) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:317) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$3(RepositoryFactoryBeanSupport.java:286) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.util.Lazy.getNullable(Lazy.java:141) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.util.Lazy.get(Lazy.java:63) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:289) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:102) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1758) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1695) ~[spring-beans-5.0.8.RELEASE.jar:5.0.8.RELEASE] ... 29 common frames omittedCaused by: java.lang.IllegalArgumentException: At least 3 parameter(s) provided but only 2 parameter(s) present in query. at org.springframework.util.Assert.isTrue(Assert.java:134) ~[spring-core-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.data.jpa.repository.query.QueryParameterSetterFactory$CriteriaQueryParameterSetterFactory.create(QueryParameterSetterFactory.java:289) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.ParameterBinderFactory.lambda$createQueryParameterSetter$1(ParameterBinderFactory.java:139) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[na:1.8.0_181] at java.util.Spliterators$ArraySpliterator.tryAdvance(Spliterators.java:958) ~[na:1.8.0_181] at java.util.stream.ReferencePipeline.forEachWithCancel(ReferencePipeline.java:126) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:498) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:485) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471) ~[na:1.8.0_181] at java.util.stream.FindOps$FindOp.evaluateSequential(FindOps.java:152) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:1.8.0_181] at java.util.stream.ReferencePipeline.findFirst(ReferencePipeline.java:464) ~[na:1.8.0_181] at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createQueryParameterSetter(ParameterBinderFactory.java:141) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.ParameterBinderFactory.lambda$createSetters$0(ParameterBinderFactory.java:131) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[na:1.8.0_181] at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1382) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471) ~[na:1.8.0_181] at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708) ~[na:1.8.0_181] at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:1.8.0_181] at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499) ~[na:1.8.0_181] at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:132) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:124) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createCriteriaBinder(ParameterBinderFactory.java:75) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.getBinder(PartTreeJpaQuery.java:248) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.<init>(PartTreeJpaQuery.java:147) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$CountQueryPreparer.<init>(PartTreeJpaQuery.java:268) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:77) ~[spring-data-jpa-2.0.9.RELEASE.jar:2.0.9.RELEASE] ... 55 common frames omitted

I also tried it with indexed parameters (?1, ?2, ?3) with the same result.
I needed, I can also provide the SQL/PostGIS create scripts.

Update 1

I also tried a plain JPA approach with a similar result. the Parameter tripId is not recognised inside a PostGIS Statement. So it looks like JPA is the root cause of this issue and not Spring.

TypedQuery<StopDistance> query =
          entityManager.createNamedQuery("StopDistanceQuery", StopDistance.class);
      query.setParameter("tripId", tripId);
      query.setParameter("lat", lat);
      query.setParameter("lng", lng);
      List<StopDistance> stopDistance = query.getResultList();

2018-09-06 10:04:02.898 ERROR 5729 --- [askExecutor-710] n.g.n.m.s.w.WebsocketAsyncHandler        : Cannot deserialize JSON Websocket Message of FeedEntity

java.lang.IllegalArgumentException: Unknown parameter name : tripId
	at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:208) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:486) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
	at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:586) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
	at sun.reflect.GeneratedMethodAccessor226.invoke(Unknown Source) ~[na:na]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_181]
	at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:380) ~[spring-orm-5.0.8.RELEASE.jar:5.0.8.RELEASE]
	at com.sun.proxy.$Proxy239.setParameter(Unknown Source) ~[na:na]
	at nz.govt.nzta.mobilityos.scheduler.websocket.WebsocketAsyncHandler.detectStop(WebsocketAsyncHandler.java:105) ~[classes/:na]
	at nz.govt.nzta.mobilityos.scheduler.websocket.WebsocketAsyncHandler.processWebsocketMessage(WebsocketAsyncHandler.java:82) ~[classes/:na]
	at nz.govt.nzta.mobilityos.scheduler.websocket.WebsocketAsyncHandler$$FastClassBySpringCGLIB$$115b77ab.invoke(<generated>) [classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [spring-core-5.0.8.RELEASE.jar:5.0.8.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) [spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
	at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115) [spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_181]
	at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_181]

Update 2

---

Affects: 2.0.9 (Kay SR9)