Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-1406

NativeQuery pageable sort with existing inner ORDER BY


    • Type: Bug
    • Status: Waiting for Review
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Core


      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 ...

        value = "SELECT * FROM Customer",
        countQuery = "SELECT count(*) FROM Customer",
        nativeQuery = true)
      Page<Customer> findAllCustomerWithPagination(Pageable pageable);
        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);


      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 ?







            • Assignee:
              olivergierke Oliver Drotbohm
              ndywicki Nicolas Dywicki
              Last updater:
              Jens Schauder
            • Votes:
              0 Vote for this issue
              2 Start watching this issue


              • Created: