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

NativeQuery pageable sort with existing inner ORDER BY

    Details

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

      Description

      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

          Activity

            People

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

              Dates

              • Created:
                Updated: