count query with expressions requires the same number of expressions as select query for parameter binding to succeed

[vimil-saju]

the count query uses parameter binding of the query so for the count query to be generated correctly it has to use the same expressions as the original query in the same order.

for example, the below query annotation

@Query(
        value ="SELECT * From table1   LEFT JOIN table_2 
                  ON table_2.value = :#{#value2}
                   WHERE table1.value=:#{#value1}"
        countQuery =
               "SELECT COUNT(*) From table1 
                   WHERE table1.value=:#{#value1}"
        nativeQuery = true
    )
Page<ReportDetail> search(
          @Param("value1") String value1,
          @Param("value2") String value2
          Pageable pageable
    );

does not work because countQuery does not use same number of parameters as the select query

however this one works

@Query(
        value ="SELECT * From table1   LEFT JOIN table_2 
                  ON table_2.value = :#{#value2}
                   WHERE table1.value=:#{#value1}"
        countQuery =
               "SELECT COUNT(*) From table1  LEFT JOIN table_2 
                  ON table_2.value = :#{#value2}
                   WHERE table1.value=:#{#value1}"
        nativeQuery = true
    )
Page<ReportDetail> search(
          @Param("value1") String value1,
          @Param("value2") String value2
          Pageable pageable
    );

I think countQuery should not share parameterBindings of original select query

[gregturn]

I can't reproduce this. I wrote a test case...

// GH-2475
@Query(value = "SELECT * FROM SD_User u WHERE u.firstname = :#{#firstname} and u.lastname = :#{#lastname}",
		countQuery = "SELECT COUNT(*) FROM SD_User u WHERE u.firstname=:#{#firstname}", nativeQuery = true)
Page<User> search(String firstname, String lastname, Pageable pageable);

@Test // GH-2475
void countQueriesShouldOnlyBindParametersFoundInTheQuery() {

	flushTestUsers();

	Page<User> pageOfUsers = repository.search("Dave", "Matthews", PageRequest.of(0, 1));

	assertThat(pageOfUsers.getContent()).containsExactlyInAnyOrder(thirdUser);
}

It passes green. This clearly has two parameters fed to the primary query and one in the count query. It also have nativeQuery switched on.

Additionally, I dug in with the debugger, and it seems to have the proper bindings for each query. See attached screen shots.

Thus, I'm not convinced that this issue exists. Perhaps it existed before, but was side effect of some other issue recently resolved. Or if this isn't an issue on main but instead an older version of Spring Data JPA.

[vimil-saju]

Hi @gregturn could you test by making the count query have 3 parameters or using lastname parameter in the count query instead of first name parameter

[gregturn]

Okay, something blew up with this:

@Query(value = "SELECT * FROM SD_User u WHERE u.firstname = :#{#firstname}",
		countQuery = "SELECT COUNT(*) FROM SD_User u WHERE u.firstname = :#{#firstname} and u.lastname = :#{#lastname}", nativeQuery = true)
Page<User> search(String firstname, String lastname, Pageable pageable);

and

@Test // GH-2475
void countQueriesShouldOnlyBindParametersFoundInTheQuery() {

	flushTestUsers();

	Page<User> pageOfUsers = repository.search("Dave", "Matthews", PageRequest.of(0, 1));

	assertThat(pageOfUsers.getContent()).containsExactlyInAnyOrder(thirdUser);
}

with a stack trace like this:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userDao' defined in class path resource [application-context.xml]: Could not create query for public abstract org.springframework.data.domain.Page org.springframework.data.jpa.repository.sample.UserRepository.search(java.lang.String,java.lang.String,org.springframework.data.domain.Pageable); Reason: Using named parameters for method public abstract org.springframework.data.domain.Page org.springframework.data.jpa.repository.sample.UserRepository.search(java.lang.String,java.lang.String,org.springframework.data.domain.Pageable) but parameter 'Optional[lastname]' not found in annotated query 'SELECT * FROM SD_User u WHERE u.firstname = :#{#firstname}'
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1771)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:599)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:521)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:967)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:938)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:608)
	at org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:221)
	at org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:110)
	at org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.loadContext(AbstractDelegatingSmartContextLoader.java:212)
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:225)
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:152)
	... 72 more
Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract org.springframework.data.domain.Page org.springframework.data.jpa.repository.sample.UserRepository.search(java.lang.String,java.lang.String,org.springframework.data.domain.Pageable); Reason: Using named parameters for method public abstract org.springframework.data.domain.Page org.springframework.data.jpa.repository.sample.UserRepository.search(java.lang.String,java.lang.String,org.springframework.data.domain.Pageable) but parameter 'Optional[lastname]' not found in annotated query 'SELECT * FROM SD_User u WHERE u.firstname = :#{#firstname}'
	at org.springframework.data.repository.query.QueryCreationException.create(QueryCreationException.java:101)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:115)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.mapMethodsToQuery(QueryExecutorMethodInterceptor.java:99)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$new$0(QueryExecutorMethodInterceptor.java:88)
	at java.base/java.util.Optional.map(Optional.java:260)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:88)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:357)
	at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:279)
	at org.springframework.data.util.Lazy.getNullable(Lazy.java:245)
	at org.springframework.data.util.Lazy.get(Lazy.java:114)
	at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:285)
	at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:132)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1817)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1767)
	... 86 more
Caused by: java.lang.IllegalStateException: Using named parameters for method public abstract org.springframework.data.domain.Page org.springframework.data.jpa.repository.sample.UserRepository.search(java.lang.String,java.lang.String,org.springframework.data.domain.Pageable) but parameter 'Optional[lastname]' not found in annotated query 'SELECT * FROM SD_User u WHERE u.firstname = :#{#firstname}'
	at org.springframework.data.jpa.repository.query.JpaQueryMethod.assertParameterNamesInAnnotatedQuery(JpaQueryMethod.java:179)
	at org.springframework.data.jpa.repository.query.JpaQueryMethod.<init>(JpaQueryMethod.java:146)
	at org.springframework.data.jpa.repository.query.DefaultJpaQueryMethodFactory.build(DefaultJpaQueryMethodFactory.java:44)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:94)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:111)
	... 98 more

[mp911de]

Seems related: #2140

[gregturn]

#2140 involves a JPQL grammar error to handle != in queries.

This ticket is the side effect of reusing bindings between the query and the countQuery of an @Query and having one query's bindings cause the other's to get altered. In fact, the reproducer is based on a native query, which doesn't even touch our grammar.

I'm actually more interested in https://github.com/spring-projects/spring-data-jpa/commits/issue/3041, because this directly interacts with the same bits.

[gregturn]

This looks like a duplicate of #3140, where @mp911de fixed handling of ParameterBindings to ensure unique bindings between usage. Please verify.

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.