Description of Pageable.Sort problem

Introduction

I have a controller where @SortDefault is used.

@SortDefault(sort="updated", direction = Sort.Direction.DESC) Pageable pageable

There are 2 separate entities without connection. There is a query in DocumentRepository (JpaRepository) in which both entities are used.

@Query("select d from Document d where " +
            "(:name is null or d.name = :name) and " +
            "(:machineExternalId is null or exists (select machine.id from Machine machine where machine.externalId = :machineExternalId))"
    )
Page<DocumentEntity> findAllBy(String name, String machineExternalId, Pageable pageable);

In real application this query is much more complicated, I simplify this query for example application.

Problem report

Before Spring Boot 2.6.7 and 2.5.13 this query generated this SQL statement.

select documenten0_.id as id1_0_, documenten0_.name as name2_0_, documenten0_.updated as updated3_0_ from document documenten0_ where (? is null or documenten0_.name=?) and (? is null or exists (select machineent1_.id from machine machineent1_ where machineent1_.external_id=?)) order by documenten0_.updated desc limit ?

As you can see the sql statement ends with order by documenten0_.updated. This is expected behaviour. After upgrading to Spring Boot 2.6.7 or 2.5.13 QuerySyntaxException is thrown instead.

org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'machine.updated' 
[select d from org.example.entity.DocumentEntity d where 
(:name is null or d.name = :name) and 
(:machineExternalId is null or exists (select machine.id from org.example.entity.MachineEntity machine where machine.externalId = :machineExternalId)) 
order by machine.updated desc]

As you can see the problem is that the query ends with order by machine.updated. This is not OK. Incorrectly machine entity is used.

Example project

I attach maven project (as simple as possible) in which the problem can be debug.

pageable-sort-problem.zip

You can see in pom.xml tested spring boot versions and the results of my tests.

        <!--        <version>2.6.7</version> PROBLEM -->
        <!--        <version>2.6.6</version> OK -->
        <!--        <version>2.6.5</version> OK -->
        <!--        <version>2.6.1</version> OK -->
        <!--        <version>2.5.13</version> PROBLEM -->
        <!--        <version>2.5.12</version> OK -->
        <!--        <version>2.4.10</version> OK -->

Comment From: scottfrederick

@fuxao This appears to be a Spring Data JPA problem, possibly related to https://github.com/spring-projects/spring-data-jpa/issues/2260. It's difficult to verify with your sample because it does not include a failing test that reproduces the problem.

Spring Boot 2.6.7 upgraded to Spring Data BOM 2021.1.4, which includes Spring Data JPA 2.6.4. You can configure your build to use Boot 2.6.7 while downgrading Spring Data BOM to 2021.1.3 (and therefore Spring Data JPA 2.6.3) by setting a property in pom.xml like the one below.

    <properties>
        ...
        <spring-data-bom.version>2021.1.3</spring-data-bom.version>
    </properties>

Can you try this and see if it works around your problem?

Comment From: fuxao

@scottfrederick You are right. Everything is OK with the properties setting <spring-data-bom.version>2021.1.3</spring-data-bom.version>. This workaround solves the problem.

After reading https://github.com/spring-projects/spring-data-jpa/issues/2260 I think that it describes the same problem.

I also updated the example project and now there is one failing test that reproduces the problem.

pageable-sort-problem.zip

By default the properties setting for spring-data-bom is in pom.xml as comment . In this case the result is NOT OK.

[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0

If you uncomment this properties setting, everything is OK.

[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

Comment From: scottfrederick

Thanks very much for following up @fuxao. I'll close this issue as a duplicate of the Spring Data JPA issue. It looks like you should see the fix in the next round of Spring Data releases and the Spring Boot releases that follow.

Comment From: wilkinsona

@xuekvm You can learn how to do it with Gradle in the plugin's documentation. If you have any further questions, please follow up on Stack Overflow or Gitter. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements.