Skip to content

DerbyPagingQueryProvider remaining pages query is not correct #1253

Closed
@spring-projects-issues

Description

@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

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions