Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 8AA469672 for ; Mon, 27 Feb 2012 19:06:36 +0000 (UTC) Received: (qmail 69903 invoked by uid 500); 27 Feb 2012 19:06:36 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 69863 invoked by uid 500); 27 Feb 2012 19:06:36 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 69856 invoked by uid 99); 27 Feb 2012 19:06:36 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 27 Feb 2012 19:06:36 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 27 Feb 2012 19:06:33 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 1715523888D2; Mon, 27 Feb 2012 19:05:42 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: mamta@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120227190542.1715523888D2@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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')");