Named Parameter in countQuery not recognized unless used in value

[henriqueln7]

Hey everyone, I'm encountering an issue when attempting to use a named parameter inside the countQuery that is not utilized in the value clause. This may be related to issue #2475. Currently, I'm unsure whether this is a Hibernate or a Spring Data JPA issue.

Problem

Inability to use a named parameter inside a countQuery if that same parameter is not present in the value.

Demonstration

Below is a snippet demonstrating the issue:

public interface UserRepository extends JpaRepository<User, Long> {

    @Query(
    value = """
    SELECT u.id, u.username, u.name
    FROM User u
    WHERE u.kind = 'ADMIN'
    UNION ALL
    SELECT u.id, u.username, u.name
    FROM User u
    WHERE u.kind = 'REGULAR'
    """, countQuery = """
    SELECT count(1) FROM User u WHERE u.kind IN :#{#kinds.![name()]}
    """, nativeQuery = true)
    // The kinds are used only in countQuery
    Page<List<UserDto>> findAllByKinds(List<UserKind> kinds, Pageable pageable);
}

// UserKind.java
public enum UserKind {
    REGULAR,
    ADMIN,
    SUPER
}

// UserDto.java
public interface UserDto {
    Long getId();
    String getUsername();
    String getName();
}

// UserController.java
@RestController
public class UserController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping("/users")
    public Page<List<UserDto>> getUsers(@RequestParam Optional<Integer> page, @RequestParam List<UserKind> kinds) {
        PageRequest pageable = PageRequest.of(page.orElse(0), 10);
        return userRepository.findAllByKinds(kinds, pageable);
    }

}

When executing the following search: http://localhost:8081/users?kinds=REGULAR,ADMIN, I encounter this exception:

org.hibernate.QueryParameterException: No argument for named parameter ':__$synthetic$__1'
	at org.hibernate.query.internal.QueryParameterBindingsImpl.lambda$validate$0(QueryParameterBindingsImpl.java:142) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596) ~[na:na]
	at java.base/java.util.Collections$UnmodifiableCollection.forEach(Collections.java:1116) ~[na:na]
	at org.hibernate.query.internal.ParameterMetadataImpl.visitParameters(ParameterMetadataImpl.java:191) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.spi.ParameterMetadataImplementor.visitRegistrations(ParameterMetadataImplementor.java:29) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.internal.QueryParameterBindingsImpl.validate(QueryParameterBindingsImpl.java:138) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.spi.AbstractSelectionQuery.beforeQuery(AbstractSelectionQuery.java:455) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.spi.AbstractSelectionQuery.beforeQueryHandlingFetchProfiles(AbstractSelectionQuery.java:446) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:424) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.Query.getResultList(Query.java:120) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.count(JpaQueryExecution.java:210) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.lambda$doExecute$0(JpaQueryExecution.java:205) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.data.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:63) ~[spring-data-commons-3.2.1.jar:3.2.1]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:204) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:149) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:137) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170) ~[spring-data-commons-3.2.1.jar:3.2.1]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158) ~[spring-data-commons-3.2.1.jar:3.2.1]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) ~[spring-data-commons-3.2.1.jar:3.2.1]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.2.1.jar:3.2.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70) ~[spring-data-commons-3.2.1.jar:3.2.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.1.2.jar:6.1.2]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385) ~[spring-tx-6.1.2.jar:6.1.2]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.2.jar:6.1.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.1.2.jar:6.1.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-3.2.1.jar:3.2.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.2.jar:6.1.2]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:249) ~[spring-aop-6.1.2.jar:6.1.2]
	at jdk.proxy2/jdk.proxy2.$Proxy104.findAllByKinds(Unknown Source) ~[na:na]
	at treino.springdatajpabug.user.UserController.getUsers(UserController.java:20) ~[main/:na]

Adding this line to my query resolves the issue:

AND u.kind IN :#{#kinds.![name()]} 
//Actually I just need to use the same parameter anwhere.

[christophstrobl]

Thanks for reporting - we're looking into this.

[christophstrobl]

The problem is that the count query is using the ParameterBinder created for Query.value which does not hold the setter for the parameter. We need to create separate binders for count queries.

[henriqueln7]

Thank you all for the support!

[femosso]

Thank you!

I was following this thread and although I confirm this is fixed in Spring Boot version 3.3.3, I wonder why a silent warning is now thrown in the logs?

2024-09-04 15:14:52.447 INFO 1379820 --- [tp1011254112-52] j.r.q.QueryParameterSetter$ErrorHandling : Silently ignoring
org.hibernate.query.UnknownParameterException: No parameter named ':parameterInCountQuery' in query with named parameters [ __$synthetic$__8, __$synthetic$__7,, __$synthetic$__4, __$synthetic$__3, __$synthetic$__6, __$synthetic$__5, __$synthetic$__2, __$synthetic$__1]
... 126 common frames omitted