db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1294275 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Date Mon, 27 Feb 2012 19:05:42 GMT
Author: mamta
Date: Mon Feb 27 19:05:41 2012
New Revision: 1294275

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

Testing with more than 2 joins columns


Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.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=1294275&r1=1294274&r2=1294275&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
Mon Feb 27 19:05:41 2012
@@ -710,6 +710,144 @@ public void testUsingClauseAndNaturalJoi
    	  s.executeUpdate("DROP TABLE derby4631_t1");
       s.executeUpdate("DROP TABLE derby4631_t2");
       
+      //Now do the testing with 3 join columns
+      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5), "+
+      	      "y varchar(2), z int, a int)");
+      s.executeUpdate("INSERT INTO derby4631_t1 VALUES " +
+      	      "('A','z',1,11),('B','y',2,22)");
+      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5), "+
+      	      "y varchar(2), z int)");
+      s.executeUpdate("INSERT INTO derby4631_t2 VALUES  " +
+      	      "('b','Y',2),('c','Y',2)");
+      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
+    	  //For case-insensitive-territory based db, there will be a match for
+    	  // one of the rows in derby4631_t2 with derby4631_t1 and that is why 
+    	  // column a from derby4631_t1 will be non-null for that row for the 
+    	  // LEFT OUTER JOIN query
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+          		new String[][] {{"b","b","Y","Y","2","2","22"},
+        		  {"c","c","Y","Y","2","2",null}});
+          //test with USING clause
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(x,y,z)",
+          		new String[][] {{"b","b","Y","Y","2","2","22"},
+          		  {"c","c","Y","Y","2","2",null}});
+          //Test LEFT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"b","Y","Y","2","2","22"},
+        		  {"c","Y","Y","2","2","22"}});
+          //Test RIGHT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"A","z","z","1","1","11"},
+        		  {"b","y","Y","2","2","22"},
+          		  {"c","y","Y","2","2","22"}});
+          //Test NATURAL RIGHT OUTER JOIN
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+          		new String[][] {{"A","A","z","z","1","1","11"},
+        		  {"B","b","y","Y","2","2","22"}});
+        //test with USING clause
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+        		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+        		"USING(x,y,z)",
+          		new String[][] {{"A","A","z","z","1","1","11"},
+        		  {"B","b","y","Y","2","2","22"}});
+      } else {
+    	  //For non-territory based db, there will be no match for both the
+    	  // rows in derby4631_t2 with derby4631_t1 and that is why column
+    	  // a from derby4631_t1 will be null for the LEFT OUTER JOIN
+    	  // query
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+          		new String[][] {{"b","b","Y","Y","2","2",null},
+        		  {"c","c","Y","Y","2","2",null}});
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(x,y,z)",
+          		new String[][] {{"b","b","Y","Y","2","2",null},
+          		  {"c","c","Y","Y","2","2",null}});
+          //Test LEFT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"b","Y","Y","2","2",null},
+        		  {"c","Y","Y","2","2",null}});
+
+          //Test NATURAL RIGHT OUTER JOIN
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+          		new String[][] {{"A","A","z","z","1","1","11"},
+        		  {"B","B","y","y","2","2","22"}});
+        //test with USING clause
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+        		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+        		"USING(x,y,z)",
+        		new String[][] {{"A","A","z","z","1","1","11"},
+        		  {"B","B","y","y","2","2","22"}});
+          //Test RIGHT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"A","z","z","1","1","11"},
+        		  {"B","y","y","2","2","22"}});
+      }
+      s.executeUpdate("DROP TABLE derby4631_t1");
+      s.executeUpdate("DROP TABLE derby4631_t2");
+      
       //Now do the testing with 2 join columns
       s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5), y varchar(2))");
       s.executeUpdate("INSERT INTO derby4631_t1 VALUES ('A','z'),('B','y')");



Mime
View raw message