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