db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d...@apache.org
Subject svn commit: r601021 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Date Tue, 04 Dec 2007 18:04:30 GMT
Author: dyre
Date: Tue Dec  4 10:04:30 2007
New Revision: 601021

URL: http://svn.apache.org/viewvc?rev=601021&view=rev
Log:
DERBY-2998: Add support for ROW_NUMBER() window function 
Starting point for a JUnit test for the ROW_NUMBER() functionality. Not run
as part of lang._Suite yet.
Patch file: d2998-test.diff
Patch contributed by Thomas Nielsen

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
  (with props)

Added: 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=601021&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Tue Dec  4 10:04:30 2007
@@ -0,0 +1,123 @@
+/**
+ *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OLAPTest
+ *  
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * OLAP functionallity test.
+ * 
+ * Please refer to DERBY-581 for more details.
+ */ 
+public class OLAPTest extends BaseJDBCTestCase {
+
+	public OLAPTest(String name) {
+		super(name);    
+	}
+
+	/**
+	 * Main test body
+	 * 
+	 * @throws SQLException
+	 */
+	public void testBasicOperations() 
+		throws SQLException 
+	{   
+		Connection conn = getConnection();
+		Statement s = conn.createStatement();
+
+		s.executeUpdate("create table t1 (a int, b int)");
+		s.executeUpdate("create table t2 (x int)");
+		s.executeUpdate("create table t3 (y int)");      
+
+		s.executeUpdate("insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5)");
+		s.executeUpdate("insert into t2 values (1),(2),(3),(4),(5)");
+		s.executeUpdate("insert into t3 values (4),(5),(6),(7),(8)");
+		/*
+		 * Positive testing of Statements
+		 */
+		ResultSet rs = s.executeQuery("select row_number(), t1.* from t1");
+		String [][] expectedRows = {{"1","1","1"},{"2","2","2"},{"3","3","3"},{"4","4","4"},{"5","5","5"}};
+		JDBC.assertFullResultSet(rs,expectedRows);
+
+		rs = s.executeQuery("select row_number(), t1.* from t1 where a > 3");
+		expectedRows = new String [][] {{"1","4","4"},{"2","5","5"}};
+		JDBC.assertFullResultSet(rs,expectedRows);
+
+		rs = s.executeQuery("select row_number(), a from t1 where b > 3");
+		expectedRows = new String [] [] {{"1","4"},{"2","5"}};
+		JDBC.assertFullResultSet(rs,expectedRows);
+
+		rs = s.executeQuery("select row_number() as r, a from t1 where b > 3");
+		expectedRows = new String [] [] {{"1","4"},{"2","5"}};
+		JDBC.assertFullResultSet(rs,expectedRows);
+
+		rs = s.executeQuery("select * from (select row_number() as r, t1.* from t1) as tr where
r < 3");
+		expectedRows = new String [] [] {{"1","1","1"},{"2","2","2"}};
+		JDBC.assertFullResultSet(rs,expectedRows);        
+
+		rs = s.executeQuery("select * from (select row_number() as r, t1.* from t1) as tr where
r > 3");
+		expectedRows = new String [] [] {{"4","4","4"},{"5","5","5"}};
+		JDBC.assertFullResultSet(rs,expectedRows);        
+
+		rs = s.executeQuery("select * from (select row_number() over () as r, t1.* from t1) as
tr where r < 3");
+		expectedRows = new String [] [] {{"1","1","1"},{"2","2","2"}};
+		JDBC.assertFullResultSet(rs,expectedRows);        
+
+		// Pushing predicates (... where r ... ) too far cause this join to fail 
+		rs = s.executeQuery("select row_number(),x from t2,t3 where x=y");
+		expectedRows = new String [] [] {{"1","4"},{"2","5"}};
+		JDBC.assertFullResultSet(rs,expectedRows);
+
+		/*
+		 * Negative testing of Statements
+		 */
+
+		// Illegal where clause, r not a named column of t1.        
+		assertStatementError("42X04",s,"select row_number() as r, a from t1 where r < 3");
+
+		// Illegal use of asterix with another column identifier.        
+		assertStatementError("42X01",s,"select row_number() as r, * from t1 where t1.a > 2");
+
+		/*
+		 * Clean up the tables used.
+		 */
+		s.executeUpdate("drop table t1");        
+		s.executeUpdate("drop table t2");
+		s.executeUpdate("drop table t3");
+	}
+
+	public static Test suite() {
+		Test suite = TestConfiguration.defaultSuite(OLAPTest.class);
+		return DatabasePropertyTestSetup.setLockTimeouts(suite,3,3);
+	}
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message