New "Handle Nested 'Order By's" Logic Leads To Hibernate QuerySyntaxExceptions Complaining About Seemingly Invalid Paths

[mwftapi]

Hey!

As we were updating from Spring Boot 2.6.6 to Spring Boot 2.6.7 in our Project, which subsequently leads to an update of Spring Data JPA from 2.6.3 to 2.6.4, our integration tests suddenly failed with the following exceptions:

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'group.id' [select distinct u from FooBar u where [REDACTED] and (not exists (from FooBarGroup group where group in :excludedGroups and group in elements(u.groups))) order by group.id desc]
 	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
 	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
 	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
 	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:757)
 	at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
 	at jdk.internal.reflect.GeneratedMethodAccessor390.invoke(Unknown Source)
 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
 	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311)
 	at jdk.proxy3/jdk.proxy3.$Proxy245.createQuery(Unknown Source)
 	at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.createJpaQuery(AbstractStringBasedJpaQuery.java:159)
 	at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:89)
 	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:227)
 	at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128)
 	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
 	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
 	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
 	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
 	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
 	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
 	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
 	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
 	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
 	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
 	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
 	[REDACTED]
 Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'group.id' [select distinct u from FooBar u where [REDACTED] and (not exists (from FooBarGroup group where group in :excludedGroups and group in elements(u.groups))) order by group.id desc]
 	at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
 	at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
 	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:282)
 	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
 	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
 	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
 	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
 	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
 	at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636)
 	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748)
 	... 218 more

After checking the changes introduced by updating from Hibernate 5.6.7 to 5.6.8 and further investigating what the now in Spring Boot 2.6.7 Spring Data BOM 2021.1.4 had in store, we stumpled upon a specific commit that was shipped with Spring Data JPA 2.6.4: c93aa25

Here logic was touched regarding nested order by handling. In our case this is not even a nested order by clause but still led to a regression which i could locally verify by shadowing the QueryUtils class with its 2.6.3 version which made the regression disappear.

This happens on @Query string queries in our project, where we also provide Pageables. For example:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface FooBarRepository extends JpaRepository<FooBar, Long> {
@Query("select distinct u from FooBar u where [REDACTED] and (not exists (from FooBarGroup group where group in :excludedGroups and group in elements(u.groups)))")
  List<FooBar> findAllByExcludedFooBarGroups(
    @Param("excludedGroups") Set<FooBarGroup> excludedGroups, Pageable pageable);
}

If need be i can see if i can create a mock project that reproduces this issue but unfortunately i have no time right now to investigate further.

This regression blocks us from updating to Spring Data JPA 2.6.3 to 2.6.4 and subsequently to keep track with the latest Spring Boot versions. We would appreciate if you can have a look at this issue, thank you very much!

If more information is required, please do not hestitate to contact me!

[pbialonc]

I've faced similar issue, in order to reproduce:

• use pageable with sort
• use inner nested query
• AbstractStringBasedJpaQuery will perform QueryUtils.applySorting
• QueryUtils.applySorting will add sort with inner query entity alias outside of inner select

Code (redacted):
@Query("SELECT i FROM Item i FETCH ALL PROPERTIES " + "WHERE i.id IN (" + "SELECT max(i2.id) FROM Item i2 " + "WHERE i2.field.id = :fieldId " + "GROUP BY i2.field.id, i2.version)")

Invalid query is being generated:
Wrapped by: org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'i2.version' [SELECT i FROM com.test.Item i FETCH ALL PROPERTIES WHERE i.id IN (SELECT max(i2.id) FROM com.test.Item i2 WHERE i2.field.id = :fieldId GROUP BY i2.field.id, i2.version) order by i2.version desc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'i2.version' [SELECT i FROM com.test.Item i FETCH ALL PROPERTIES WHERE i.id IN (SELECT max(i2.id) FROM com.test.Item i2 WHERE i2.field.id = :fieldId GROUP BY i2.field.id, i2.version) order by i2.version desc]

[jmax01]

Duplicate of #2496

[gregturn]

Backported to 2.7.x and 2.6.x.