db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1245143 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Date Thu, 16 Feb 2012 19:55:20 GMT
Author: mamta
Date: Thu Feb 16 19:55:20 2012
New Revision: 1245143

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

Adding test for left and right outer joins with VALUES caluse supplying the data instead of
base tables.


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=1245143&r1=1245142&r2=1245143&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
Thu Feb 16 19:55:20 2012
@@ -705,7 +705,106 @@ public void testUsingClauseAndNaturalJoi
       }
       s.executeUpdate("DROP TABLE derby4631_t1");
       s.executeUpdate("DROP TABLE derby4631_t2");
+      
+      //Test for DERBY-4631 using VALUES clause
+      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(v2.x, v1.x) cx, " +
+          		"coalesce(v2.y, v1.y) cy " +
+        		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+          		"NATURAL RIGHT OUTER JOIN "+
+        		"(values ('b','Y'),('c','x')) v1(x,y)",
+          		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
+          //
+    	  //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(v2.x, v1.x) cx, " +
+              		"coalesce(v2.y, v1.y) cy " +
+            		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+              		"RIGHT OUTER JOIN "+
+            		"(values ('b','Y'),('c','x')) v1(x,y)" +
+            		"USING(x,y)",
+            		new String[][] {{"b","Y","B","y"},{"c","x","c","x"}});
+
+          //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(v2.x, v1.x) cx, " +
+              		"coalesce(v2.y, v1.y) cy " +
+            		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+              		"NATURAL LEFT OUTER JOIN "+
+            		"(values ('b','Y'),('c','x')) v1(x,y)",
+              		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
+          //
+          //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(v2.x, v1.x) cx, " +
+            		"coalesce(v2.y, v1.y) cy " +
+              		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+            		"LEFT OUTER JOIN "+
+              		"(values ('b','Y'),('c','x')) v1(x,y)" +
+              		"USING(x,y)",
+            		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+      } 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(v2.x, v1.x) cx, " +
+            		"coalesce(v2.y, v1.y) cy " +
+              		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+            		"NATURAL RIGHT OUTER JOIN "+
+              		"(values ('b','Y'),('c','x')) v1(x,y)",
+              		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
+          //
+    	  //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(v2.x, v1.x) cx, " +
+            		"coalesce(v2.y, v1.y) cy " +
+              		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+            		"RIGHT OUTER JOIN "+
+              		"(values ('b','Y'),('c','x')) v1(x,y)" +
+              		"USING(x,y)",
+          		new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+
+          //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(v2.x, v1.x) cx, " +
+            		"coalesce(v2.y, v1.y) cy " +
+              		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+            		"NATURAL LEFT OUTER JOIN "+
+              		"(values ('b','Y'),('c','x')) v1(x,y)",
+            		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
+          //
+          //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(v2.x, v1.x) cx, " +
+          		"coalesce(v2.y, v1.y) cy " +
+        		"FROM (values('A','z'),('B','y')) v2(x,y) " +
+          		"LEFT OUTER JOIN "+
+        		"(values ('b','Y'),('c','x')) v1(x,y)" +
+          		"USING(x,y)",
+        		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+    	  
       }
+}
 
   /**
    * Test order by with English collation



Mime
View raw message