NativeQuery pageable sort with existing inner ORDER BY [DATAJPA-1406]

[spring-projects-issues]

Nicolas Dywicki opened DATAJPA-1406 and commented

Hello guys,

Since we upgrade our Spring boot projet to 2.0.3 (before 1.5.9) and upgraded the Spring Data dependency 2.0.8-RELEASE, some native pageable queries doesn't work anymore.

First I've removed the hint "-- #pageable" and keep only the Pageable parameter (like describe in https://jira.spring.io/browse/DATAJPA-928) and its work like a charm for most of native queries.

But i notice for native queries use an ORDER BY in inner join Spring data adds the final query with ", mycolumn" instead of "ORDER BY mycolumn". See QueryUtils

I missed somethings ?

Please find a sample Maven projet in attachment base on the Spring Getting Started JPA

I added two functions in the repository:

 => JIRA checker blocks SQL queries i had some wrong sql selec(t) order ...

@Query(
  value = "SELECT * FROM Customer",
  countQuery = "SELECT count(*) FROM Customer",
  nativeQuery = true)
Page<Customer> findAllCustomerWithPagination(Pageable pageable);

@Query(
  value = "SELEC c1.* FROM (SELEC c2.* FROM Customer AS c2 ORDER BY c2.id DESC) AS c1",
  countQuery = "SELECT count...",
  nativeQuery = true)
Page<Customer> findAllCustomerWithOrderByAndPagination(Pageable pageable);

 
+Result:+

findAllCustomerWithPagination => ok

SELECT * FROM Customer order by id asc limit ?

 findAllCustomerWithOrderByAndPagination => failed

SELEC c1.* FROM (SELEC c2.* FROM Customer AS c2 ORDER BY c2.id DESC) AS c1, c2.id asc limit ?

 Note also that the alias of order by c2.id is taken from the inner order 

 

The final working query should be :

SELEC c1.* FROM (SELEC c2.* FROM Customer AS c2 ORDER BY c2.id DESC) AS c1 ORDER BY c1.id ASC limit ?

 

Thanks.

 

---

Attachments:

• sample-native-query-order-by.zip (3.52 kB)

Issue Links:

• DATAJPA-987 Query Syntax error in Spring JPA pagination

• DATAJPA-1613 StringQuery incorrect alias definition for complex native queries

Referenced from: pull request #291

[spring-projects-issues]

Jens Schauder commented

I suggest merging this into 2.2.0 since it makes some substantial changes to the parsing which I'm not too eager to merge into 2.1. so late before the GA release

[spring-projects-issues]

Mark Paluch commented

Let's discuss this topic on a general level. Getting us into SQL parsing is a significant feature with a lot of variety. Postponing this ticket for now until we have a clearer picture of potential approaches

[spring-projects-issues]

Stere commented

As a workaround you can add "ORDER BY '1' " at the end of your query, it will not break the sorting

[gregturn]

This situation appears too complicated for QueryUtils to handle, and we aren't likely to approve a change there that would meet your needs. Basically, native queries are too complex to manage. We have parser, but for JPQL and HQL. That's because SQL itself is managed by multiple standards.

Your best best moving forward may be to implement a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository.