`ParameterBinder` creation fails during count query derivation of declared queries using parameters only in `ORDER BY`

[pavelety]

After migrating from 3.3.8 to 3.4.0 (issue stays in 3.4.1, 3.4.2) facing in runtime the IllegalStateException: No position associated for code like this:

@Query("""
	select p
	from Product p
	order by CASE WHEN p.width > :width THEN 0 ELSE 1 END
""")
Page<Product> findWithCustomOrder(int width, Pageable pageable);

Stacktrace for java.lang.IllegalStateException: No position associated

at org.springframework.data.jpa.repository.query.ParameterBinding$BindingIdentifier.getPosition(ParameterBinding.java:420) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.QueryParameterSetterFactory$BasicQueryParameterSetterFactory.create(QueryParameterSetterFactory.java:246) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createQueryParameterSetter(ParameterBinderFactory.java:146) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:135) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createQueryAwareBinder(ParameterBinderFactory.java:102) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.createBinder(AbstractStringBasedJpaQuery.java:141) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.lambda$1(AbstractStringBasedJpaQuery.java:98) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.util.Lazy.getNullable(Lazy.java:135) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.data.util.Lazy.get(Lazy.java:113) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateCountQuery(AbstractStringBasedJpaQuery.java:157) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createCountQuery(AbstractJpaQuery.java:269) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.count(JpaQueryExecution.java:211) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.lambda$0(JpaQueryExecution.java:206) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:71) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:205) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:93) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:152) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:140) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:170) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69) ~[spring-data-commons-3.4.2.jar:3.4.2]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380) ~[spring-tx-6.2.2.jar:6.2.2]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.2.2.jar:6.2.2]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138) ~[spring-tx-6.2.2.jar:6.2.2]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:136) ~[spring-data-jpa-3.4.2.jar:3.4.2]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223) ~[spring-aop-6.2.2.jar:6.2.2]
at jdk.proxy2/jdk.proxy2.$Proxy183.findWithCustomOrder(Unknown Source) ~[na:na]

Hibernate logs for 3.3.8:

select p1_0... from Product p1_0 order by case when p1_0.width>? then 0 else 1 end fetch first ? rows only
select count(p1_0.code) from Product p1_0

Hibernate logs for 3.4.2:

select p1_0... from Product p1_0 order by case when p1_0.width>? then 0 else 1 end fetch first ? rows only

@Param annotation and numeric access by ?1 don't help.

Workaround:
Figured out that it happens if parameters are only in order by part and came to a workaround (add always true condition into where part with query param).

@Query("""
	select p
	from Product p where :width=:width
	order by CASE WHEN p.width > :width THEN 0 ELSE 1 END
""")
Page<Product> findWithCustomOrder(int width, Pageable pageable);

Removing Pageable and Page is also fixing the issue.
If you need I can prepare a simple project, but issue is easily reproducible:
-query param is only in order by
-use Pageable and Page
-Spring Boot 3.4.0+

[mp911de]

Thanks for reporting the problem. It is indeed an issue on our side introduced by a combination of changes (#3293, ) by copying parameters from the original query into the count query and remembering whether we use named parameters in the original query. 3.3.x is not affected because the named parameter check in StringQuery returns a cached (but wrong) value. We assumed that parameters have either a name or a position and because we've checked against the query, we didn't consider the case that count queries (we truncate the ORDER BY clause) might not use any parameters and so we've used a code path that wasn't intended for parameter lookup.

For the time being, you can specify a count-query.