Closed
Description
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