METAMODEL-35: Improved paged queries on DB2 by carrying ORDER BY clause
to OVER function.
diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java
index ef21fbf..fcb0e27 100644
--- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java
+++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java
@@ -100,7 +100,10 @@
outerQuery.select(new SelectItem(selectItem, subQuerySelectItem));
}
- innerQuery.select(new SelectItem("ROW_NUMBER() OVER()", "metamodel_row_number"));
+ final String rewrittenOrderByClause = rewriteOrderByClause(innerQuery, innerQuery.getOrderByClause());
+ final String rowOver = "ROW_NUMBER() " + "OVER(" + rewrittenOrderByClause + ")";
+ innerQuery.select(new SelectItem(rowOver, "metamodel_row_number"));
+ innerQuery.getOrderByClause().removeItems();
final String baseQueryString = rewriteQuery(outerQuery);
diff --git a/jdbc/src/test/java/org/apache/metamodel/dialects/DB2QueryRewriterTest.java b/jdbc/src/test/java/org/apache/metamodel/dialects/DB2QueryRewriterTest.java
index b71e98a..9108461 100644
--- a/jdbc/src/test/java/org/apache/metamodel/dialects/DB2QueryRewriterTest.java
+++ b/jdbc/src/test/java/org/apache/metamodel/dialects/DB2QueryRewriterTest.java
@@ -68,7 +68,34 @@
"SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER() AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number > 400",
str);
}
+
+ public void testRewriteFirstRowWithoutOrderByClause() throws Exception {
+ Query q = new Query().from(table).select(col).setFirstRow(401);
+ String str = new DB2QueryRewriter(null).rewriteQuery(q);
+ assertEquals(
+ "SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER() AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number > 400",
+ str);
+ }
+
+ public void testRewriteFirstRowWithOrderByClause() throws Exception {
+ Query q = new Query().from(table).select(col).setFirstRow(401);
+ q.orderBy(col);
+ String str = new DB2QueryRewriter(null).rewriteQuery(q);
+
+ assertEquals(
+ "SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER( ORDER BY sch.foo.bar ASC) AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number > 400",
+ str);
+ }
+
+ public void testRewriteFirstRowAndMaxRowsWithoutOrderByClause() throws Exception {
+ Query q = new Query().from(table).select(col).setFirstRow(401).setMaxRows(400);
+ String str = new DB2QueryRewriter(null).rewriteQuery(q);
+ assertEquals(
+ "SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER() AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number BETWEEN 401 AND 800",
+ str);
+ }
+
public void testRewriteFirstRowAndMaxRows() throws Exception {
Query q = new Query().from(table).select(col).setFirstRow(401).setMaxRows(400);
String str = new DB2QueryRewriter(null).rewriteQuery(q);
@@ -76,6 +103,15 @@
"SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER() AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number BETWEEN 401 AND 800",
str);
}
+
+ public void testRewriteFirstRowAndMaxRowsWithOrderByClause() throws Exception {
+ Query q = new Query().from(table).select(col).setFirstRow(401).setMaxRows(400);
+ q.orderBy(col);
+ String str = new DB2QueryRewriter(null).rewriteQuery(q);
+ assertEquals(
+ "SELECT metamodel_subquery.bar FROM (SELECT sch.foo.bar, ROW_NUMBER() OVER( ORDER BY sch.foo.bar ASC) AS metamodel_row_number FROM sch.foo) metamodel_subquery WHERE metamodel_row_number BETWEEN 401 AND 800",
+ str);
+ }
public void testRewriteColumnType() throws Exception {
assertEquals("SMALLINT", new DB2QueryRewriter(null).rewriteColumnType(ColumnType.BOOLEAN));
