SubQuery & PageRequest : wrong alias used for sort

[MarneusCalgarXP]

bug occurs in version : spring-data-jpa 2.6.4 (was ok in 2.6.3 and prior versions)

sample project to reproduce:

Model :

@Entity
public class Foo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

}

Repository :

public interface FooRepository extends JpaRepository<Foo, Long> {

    @Query("SELECT f" +
            " FROM com.example.demo.model.Foo f " +
            "WHERE f.id = ( " +
            "    SELECT MIN(g.id)" +
            "    FROM com.example.demo.model.Foo g " +
            " ) ")
    Page<Foo> findCustomFoo(Pageable pageable);

}

call :

Page<Foo> page = fooRepository.findCustomFoo(
	PageRequest.of(0, 10, Sort.Direction.ASC, "id")
);

HQL generated in versions prior to 2.6.3 (included)

SELECT f FROM com.example.demo.model.Foo f WHERE f.id = ( SELECT MIN(g.id) FROM com.example.demo.model.Foo g ) 
order by f.id asc

HQL generated in version 2.6.4

SELECT f FROM com.example.demo.model.Foo f WHERE f.id = ( SELECT MIN(g.id) FROM com.example.demo.model.Foo g ) 
order by g.id asc

==> the alias used in order by points to the subquery table, which is not available at global level

which leads to a QuerySyntaxException :

org.hibernate.hql.internal.ast.QuerySyntaxException: 
Invalid path: 'g.id' 
[SELECT f FROM com.example.demo.model.Foo f WHERE f.id = (     SELECT MIN(g.id)    FROM com.example.demo.model.Foo g  )  order by g.id asc]

[DiegoKrupitza]

I think this should be fixed with PR #2516 by @darinmanica.

[darinmanica]

I agree, @DiegoKrupitza. PR #2516 should resolve this issue, making this issue a duplicate of #2500 and the second half of #2260. @MarneusCalgarXP feel free to pull the PR and test with your examples. I was able to replace the 2.6.4 builds with the PR builds in my application, and everything is working again.

[MarneusCalgarXP]

@darinmanica I copied the QueryUtils.java file in my project from PR #2516 , everything is OK, problem solved.

thank you ;-)

[gregturn]

Duplicates #2260.

[alexantonica]

Still happening in 2.7.1. I've just upgraded from 2.6.3 and this started failing.
Stackoverflow post

Edit: found a workaround. I've read in the posts you posted above that it might be related with the multi lined query.
So instead of

     @Query("""
      SELECT x FROM X x
      WHERE x.fieldId IN (:fieldIds)
          AND ((:statusList) IS NULL OR t.status IN (:statusList))
          AND (:term IS NULL OR LOWER(x.name) LIKE :term OR LOWER(x.code) LIKE :term)
          AND (:excludeId is NULL OR NOT EXISTS (
              FROM Y y
              WHERE y.id.someId = x.id
                AND y.id.someOtherId = :excludeId
              )
          )
       """)

i've changed to

     @Query("""
      SELECT x FROM X x
      WHERE x.fieldId IN (:fieldIds)
          AND ((:statusList) IS NULL OR t.status IN (:statusList))
          AND (:term IS NULL OR LOWER(x.name) LIKE :term OR LOWER(x.code) LIKE :term)
          AND (:excludeId is NULL OR NOT EXISTS 
          ( FROM Y y WHERE y.id.someId = x.id AND 
           y.id.someOtherId = :excludeId)
          )
       """)

and it worked. 👍