db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1231296 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: CollationTest.java GroupByTest.java
Date Fri, 13 Jan 2012 21:12:55 GMT
Author: mamta
Date: Fri Jan 13 21:12:55 2012
New Revision: 1231296

URL: http://svn.apache.org/viewvc?rev=1231296&view=rev
Log:
DERBY-4631 Wrong join column returned by right outer join with NATURAL or USING and territory-based
collation

Adding comments to some of the tests, stating that Derby is allowing incorrect behavior because
of DERBY-4631. Once the jira is fixed, these tests should start failing and would need to
be changed to show the correct behavior.


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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=1231296&r1=1231295&r2=1231296&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Fri Jan 13 21:12:55 2012
@@ -522,12 +522,20 @@ public void testUsingClauseAndNaturalJoi
     	  // the value resulting from coalescing the left table's column value 
     	  // with the right table's column value but Derby instead always picks
     	  // up right table's column value
+    	  //
+    	  //Following query is returning INCORRECT data and once DERBY-4631 is
+    	  // fixed, we should get the expected results as 
+    	  // new String[][] {{"A","A"},{"b","b"}});
           checkLangBasedQuery(s, "SELECT x, " +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
         		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
           		new String[][] {{"A","A"},{"B","b"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+    	  //
+    	  //Following query is returning INCORRECT data and once DERBY-4631 is
+    	  // fixed, we should get the expected results as 
+    	  // new String[][] {{"A","A"},{"b","b"}});
           checkLangBasedQuery(s, "SELECT x, " +
             		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
               		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
@@ -544,12 +552,18 @@ public void testUsingClauseAndNaturalJoi
           // will also be null and hence it is ok for Derby to always pick up  
           // left table's column value for join columns in case of LEFT OUTER
           // JOIN.
+          //
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, " +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
         		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
           		new String[][] {{"b","b"},{"c","c"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+          //
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, " +
             		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
               		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
@@ -560,12 +574,18 @@ public void testUsingClauseAndNaturalJoi
     	  // For the given data, RIGHT OUTER JOIN will not find any 
     	  // matching rows in the left table and hence it will be ok for
           // Derby to pick up join column's value from right table's column.
+    	  //
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
           checkLangBasedQuery(s, "SELECT x, " +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
         		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
           		new String[][] {{"A","A"},{"B","B"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+    	  //
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
           checkLangBasedQuery(s, "SELECT x, " +
             		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
               		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
@@ -576,12 +596,18 @@ public void testUsingClauseAndNaturalJoi
           // rows in the left table match the rows in the right table and hence
           // it will be ok for Derby to pick up join column's value from left
           // table's column
+          //
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, " +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
         		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
           		new String[][] {{"b","b"},{"c","c"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+          //
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, " +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
           		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
@@ -599,6 +625,9 @@ public void testUsingClauseAndNaturalJoi
       s.executeUpdate("INSERT INTO derby4631_t2 VALUES ('b','Y'),('c','x')");
       
       if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
+    	  //Following query is returning INCORRECT data and once DERBY-4631 is
+    	  // fixed, we should get the expected results as 
+    	  // new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
           checkLangBasedQuery(s, "SELECT x, y," +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
           		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -606,6 +635,10 @@ public void testUsingClauseAndNaturalJoi
           		new String[][] {{"A","z","A","z"},{"B","y","b","Y"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+          //
+    	  //Following query is returning INCORRECT data and once DERBY-4631 is
+    	  // fixed, we should get the expected results as 
+    	  // new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
           checkLangBasedQuery(s, "SELECT x, y," +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
           		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -613,6 +646,8 @@ public void testUsingClauseAndNaturalJoi
           		"USING(x,y)",
           		new String[][] {{"A","z","A","z"},{"B","y","b","Y"}});
 
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, y," +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
           		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -620,6 +655,9 @@ public void testUsingClauseAndNaturalJoi
           		new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+          //
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, y," +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
           		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -627,6 +665,8 @@ public void testUsingClauseAndNaturalJoi
           		"USING(x,y)",
           		new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
       } else {
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
           checkLangBasedQuery(s, "SELECT x, y," +
             		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
             		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -634,6 +674,9 @@ public void testUsingClauseAndNaturalJoi
               		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+          //
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
           checkLangBasedQuery(s, "SELECT x, y," +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
           		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -641,6 +684,8 @@ public void testUsingClauseAndNaturalJoi
           		"USING(x,y)",
         		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
 
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, y," +
             		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
             		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +
@@ -648,6 +693,9 @@ public void testUsingClauseAndNaturalJoi
               		new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
           //Do the same test as above, but this time using the USING clause
           // rather the NATURAL join
+          //
+          //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+          // and hence following is returning the correct results.
           checkLangBasedQuery(s, "SELECT x, y," +
           		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
           		"coalesce(derby4631_t2.y, derby4631_t1.y) cy " +

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=1231296&r1=1231295&r2=1231296&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Fri Jan 13 21:12:55 2012
@@ -1482,6 +1482,9 @@ public class GroupByTest extends BaseJDB
 		// should be qualified with left or right table name. Just a note
 		// that ON clause is not allowed on CROSS and NATURAL JOINS.
 		//
+        //The join queries with ON clause are not impacted by DERBY-4631 and 
+        // hence following tests are showing the correct behavior.
+		//
 		//Try INNER JOIN with ON clause.
         assertStatementError("42X03", s,
                 "select i from t1_D3880 " +
@@ -1519,26 +1522,47 @@ public class GroupByTest extends BaseJDB
 		// on CROSS and NATURAL JOINS.
 		//
 		//Try INNER JOIN with USING clause.
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"inner join t2_D3880 USING(i) group by t1_D3880.i");
 		expRs = new String[][] {{"1"},{"2"}};
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query does not allow t2_D3880.i in group by clause
+		// because join column i the select query gets associated with
+		// left table in case of INNER JOIN.
         assertStatementError("42Y36", s,
         		"select i from t1_D3880 " +
 				"inner join t2_D3880 USING(i) group by t2_D3880.i");
 
         //Test the GROUP BY problem with LEFT OUTER JOIN and USING clause.
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"LEFT OUTER JOIN t2_D3880 USING(i) GROUP BY t1_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query does not allow t2_D3880.i in group by clause
+		// because join column i the select query gets associated with
+		// left table in case of LEFT OUTER JOIN.
         assertStatementError("42Y36", s,
         		"select i from t1_D3880 " +
 				"LEFT OUTER JOIN t2_D3880 USING(i) GROUP BY t2_D3880.i");
 
         //Test the GROUP BY problem with RIGHT OUTER JOIN and USING clause.
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"RIGHT OUTER JOIN t2_D3880 USING(i) GROUP BY t2_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query does not allow t1_D3880.i in group by clause
+		// because join column i the select query gets associated with
+		// right table in case of RIGHT OUTER JOIN.
         assertStatementError("42Y36", s,
         		"select i from t1_D3880 " +
 				"RIGHT OUTER JOIN t2_D3880 USING(i) GROUP BY t1_D3880.i");
@@ -1566,14 +1590,26 @@ public class GroupByTest extends BaseJDB
 		// NATURAL JOIN.
 		//
 		//Try the GROUP BY problem with NATURAL INNER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"NATURAL inner join t2_D3880 group by t1_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
 		//Test the GROUP BY problem with NATURAL LEFT OUTER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"NATURAL LEFT OUTER JOIN t2_D3880 GROUP BY t1_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
 		//Test the GROUP BY problem with NATURAL RIGHT OUTER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"NATURAL RIGHT OUTER JOIN t2_D3880 GROUP BY t2_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
@@ -1589,6 +1625,10 @@ public class GroupByTest extends BaseJDB
 				"NATURAL LEFT OUTER JOIN t2_D3880 GROUP BY t1_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
 		//NATURAL RIGHT OUTER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select t2_D3880.i from t1_D3880 " +
 				"NATURAL RIGHT OUTER JOIN t2_D3880 GROUP BY t2_D3880.i");
 		JDBC.assertFullResultSet(rs,expRs);
@@ -1596,29 +1636,53 @@ public class GroupByTest extends BaseJDB
 		//Similar query for HAVING clause. HAVING clause should not be able
 		// to use a column which is not part of the SELECT column list.
 		// Doing this testing with USING clause
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"inner join t2_D3880 USING(i) group by t1_D3880.i " +
 				"HAVING t1_D3880.i > 1");
 		expRs = new String[][] {{"2"}};
 		JDBC.assertFullResultSet(rs,expRs);
 		// Doing the same test as above with NATURAL JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"NATURAL inner join  t2_D3880 group by t1_D3880.i " +
 				"HAVING t1_D3880.i > 1");
 		expRs = new String[][] {{"2"}};
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"LEFT OUTER join t2_D3880 USING(i) group by t1_D3880.i " +
 				"HAVING t1_D3880.i > 1");
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"NATURAL LEFT OUTER join t2_D3880 group by t1_D3880.i " +
 				"HAVING t1_D3880.i > 1");
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"RIGHT OUTER join t2_D3880 USING(i) group by t2_D3880.i " +
 				"HAVING t2_D3880.i > 1");
 		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
 		rs = s.executeQuery("select i from t1_D3880 " +
 				"NATURAL RIGHT OUTER join t2_D3880 group by t2_D3880.i " +
 				"HAVING t2_D3880.i > 1");



Mime
View raw message