DerbyPagingQueryProvider remaining pages query is not correct

[spring-projects-issues]

Jimmy Praet opened BATCH-2348 and commented

DerbyPagingQueryProvider queryProvider = new DerbyPagingQueryProvider();
queryProvider.setFromClause("TEST.TABLE");
queryProvider.setSelectClause("X, Y, Z");
queryProvider.setWhereClause("X = ?");
Map<String, Order> sortKeys = new HashMap<String, Order>();
sortKeys.put("Y", Order.ASCENDING);
sortKeys.put("Z", Order.ASCENDING);
queryProvider.setSortKeys(sortKeys);
System.out.println(queryProvider.generateRemainingPagesQuery(10));

results in the following query:

SELECT * FROM 
 ( SELECT TMP_ORDERED.*, ROW_NUMBER() OVER () AS ROW_NUMBER FROM 
   (SELECT X, Y, Z FROM TEST.TABLE WHERE X = ? ) 
    AS TMP_ORDERED
 ) 
 AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER <= 10 
 AND ((Y > ?) OR (Y = ? AND Z > ?)) ORDER BY Y ASC, Z ASC

I believe the sort key selection and order by should be applied to the inner subquery instead of the outer query:

SELECT * FROM 
 ( SELECT TMP_ORDERED.*, ROW_NUMBER() OVER () AS ROW_NUMBER FROM 
   (SELECT X, Y, Z FROM TEST.TABLE WHERE X = ? 
    AND ((Y > ?) OR (Y = ? AND Z > ?)) ORDER BY Y ASC, Z ASC) 
	AS TMP_ORDERED
 ) 
 AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER <= 10 

---

Affects: 2.2.7, 3.0.3

[spring-projects-issues]

Jimmy Praet commented

As a workaround we are using the Db2PagingQueryProvider, it works on Derby too.

[fmbenhassine]

Resolved with #4713