db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d...@apache.org
Subject svn commit: r633067 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Date Mon, 03 Mar 2008 12:27:02 GMT
Author: dyre
Date: Mon Mar  3 04:27:01 2008
New Revision: 633067

URL: http://svn.apache.org/viewvc?rev=633067&view=rev
Log:
DERBY-2998: Add support for ROW_NUMBER() window function
Add new queries to the OLAPTest.
Patch contributed by Thomas Nielsen
Patch file: d2998-followup-issue4.diff

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java?rev=633067&r1=633066&r2=633067&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Mon Mar  3 04:27:01 2008
@@ -230,7 +230,67 @@
 							"where x1.r1 = 2 * x2.r2 and x2.c4 = t1.a");
 		expectedRows = new String[][]{{"100", "20", "200"}, {"200", "40", "400"}};
 		JDBC.assertFullResultSet(rs, expectedRows);
-			
+
+		/* Group by and having */
+		rs = s.executeQuery("select r from (select a, row_number() over() as r, b from t1) x group
by r");
+		expectedRows = new String[][]{{"1"}, {"2"}, {"3"}, {"4"}, {"5"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select * from (select a, row_number() over() as r, b from t1) x group
by a, b, r");
+		expectedRows = new String[][]{{"10", "1", "100"}, 
+										{"20", "2", "200"},
+										{"30", "3", "300"},
+										{"40", "4", "400"},
+										{"50", "5", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select * from (select a, row_number() over() as r, b from t1) x group
by b, r, a");
+		expectedRows = new String[][]{{"10", "1", "100"}, 
+										{"20", "2", "200"},
+										{"30", "3", "300"},
+										{"40", "4", "400"},
+										{"50", "5", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select * from "+
+							"(select a, row_number() over() as r, b from t1) x "+
+							"group by b, r, a "+
+							"having r > 2");
+		expectedRows = new String[][]{{"30", "3", "300"},
+										{"40", "4", "400"}, 
+										{"50", "5", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select * from "+
+							"(select a, row_number() over() as r, b from t1) x "+
+							"group by b, r, a "+
+							"having r > 2 and a >=30 "+
+							"order by a desc");
+		expectedRows = new String[][]{{"50", "5", "500"},
+										{"40", "4", "400"}, 
+										{"30", "3", "300"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		 
+		rs = s.executeQuery("select * from "+
+							"(select a, row_number() over() as r, b from t1) x "+
+							"group by b, r, a "+
+							"having r > 2 and a >=30 "+
+							"order by r desc");
+		expectedRows = new String[][]{{"50", "5", "500"},
+										{"40", "4", "400"}, 
+										{"30", "3", "300"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select * from "+
+							"(select a, row_number() over() as r, b from t1) x "+
+							"group by b, r, a "+
+							"having r > 2 and a >=30 "+
+							"order by a asc, r desc");
+		expectedRows = new String[][]{{"30", "3", "300"},
+										{"40", "4", "400"}, 
+										{"50", "5", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
 		/*
 		 * Negative testing of Statements
 		 */



Mime
View raw message