Spring Data JPA Extension: Sort not working with @Query annotated method

[snowdrop-bot]

Unexpected behaviour when using @Query in CrudRepository in combination with Sort from Pageable. Pagination works nicely, but sorting is not being included in underlying SQL. When using derived query methods (methods of repository interfaces that follow the Spring Data conventions) it works, and ORDER BY statement is included in SQL. However, if I create custom method annotated with @Query and having Pageable as an input parameter, then ORDER BY is omitted.

In this example, expected behaviour would be a list of movies, sorted by ID in descending order.

Example entity:

@Entity
public class Movie {
    @Id
    protected Long id;
    private String title;

    public Long getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }
}

Example repository:

public interface MovieRepository extends CrudRepository<Movie, Long> {
    @Query(value = "select m from Movie m", countQuery = "select count(m) from Movie m")
    Page<Movie> customFind(Pageable pageable);
}

Example resource:

@Path("/movie")
public class MovieResource {
    @Inject
    MovieRepository repository;

    @GET
    @Path("/")
    @Produces(MediaType.APPLICATION_JSON)
    public Response customFind() {
        return Response.ok(repository.customFind(PageRequest.of(0, 10, Sort.Direction.DESC, "id")).getContent()).build();
    }
}

Dependency added:

<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-spring-data-jpa</artifactId>
</dependency>

Example configuration:

quarkus.datasource.db-kind=h2
quarkus.datasource.jdbc.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1
quarkus.hibernate-orm.dialect=org.hibernate.dialect.H2Dialect
quarkus.hibernate-orm.database.generation=drop-and-create
quarkus.hibernate-orm.log.sql=true

Generated SQL in this case is as follows:

Hibernate:
    select
        movie0_.id as id1_0_,
        movie0_.title as title2_0_
    from
        Movie movie0_ limit ?
Hibernate:
    select
        count(movie0_.id) as col_0_0_
    from
        Movie movie0_

If I changed it using Spring derived queries, for example:

public interface MovieRepository extends CrudRepository<Movie, Long> {
    Page<Movie> findAll(Pageable pageable);
}

it would result in following:

Hibernate:
    select
        movie0_.id as id1_0_,
        movie0_.title as title2_0_
    from
        Movie movie0_
    order by
        movie0_.id DESC limit ?
Hibernate:
    select
        count(*) as col_0_0_
    from
        Movie movie0_

In this case, statement ORDER BY is included, which is expected behaviour.

Sample project can be downloaded from here. To test this behaviour, use following command to run:

./mvnw quarkus:dev

And open http://localhost:8080/movie.

---

https://github.com/quarkusio/quarkus/issues/18240

---

$upstream:18240$