db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r998163 - in /db/derby/code/branches/10.6: ./ java/engine/org/apache/derby/impl/sql/compile/JoinNode.java java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
Date Fri, 17 Sep 2010 14:56:48 GMT
Author: dag
Date: Fri Sep 17 14:56:48 2010
New Revision: 998163

URL: http://svn.apache.org/viewvc?rev=998163&view=rev
Log:
DERBY-4712 Complex nested joins problems

Backport to 10.6 branch as

svn merge -c 997325 https://svn.apache.org/repos/asf/db/derby/code/trunk

Patch DERBY-4712b, which removes one of the sources for NPE seen by the reporter.
The other is covered by DERBY-4798.

A corner case: the patch makes an inner join which decides it is not
flattenable, propagate this fact down to any nested outer join nodes
containing nested inner joins, the latter inner joins will otherwise
think they are flattenable (a priori value for inner joins).

Adds new test cases.


Modified:
    db/derby/code/branches/10.6/   (props changed)
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java

Propchange: db/derby/code/branches/10.6/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Fri Sep 17 14:56:48 2010
@@ -1,2 +1,2 @@
-/db/derby/code/trunk:938547,938796,938959,939231,940462,940469,941627,942031,942286,942476,942480,942587,944152,946794,948045,948069,951346,951366,952138,952237,952581,954344,954421,954544,954748,955001,955540,955634,956075,956234,956445,956569,956659,957260,958163,958522,958555,958618,958939,959550,962716,963206,963705,964115,965647,967304,980684,986689,986834,987539,989099,990292
+/db/derby/code/trunk:938547,938796,938959,939231,940462,940469,941627,942031,942286,942476,942480,942587,944152,946794,948045,948069,951346,951366,952138,952237,952581,954344,954421,954544,954748,955001,955540,955634,956075,956234,956445,956569,956659,957260,958163,958522,958555,958618,958939,959550,962716,963206,963705,964115,965647,967304,980684,986689,986834,987539,989099,990292,997325
 /db/derby/docs/trunk:954344

Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=998163&r1=998162&r2=998163&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
(original)
+++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
Fri Sep 17 14:56:48 2010
@@ -1512,6 +1512,13 @@ public class JoinNode extends TableOpera
 		/* Can't flatten if no predicates in where clause. */
 		if (predicateTree == null)
 		{
+            // DERBY-4712. Make sure any nested outer joins know we are non
+            // flattenable, too, since they inform their left and right sides
+            // which, is they are inner joins, a priori think they are
+            // flattenable. If left/right result sets are not outer joins,
+            // these next two calls are no-ops.
+            ((FromTable) leftResultSet).transformOuterJoins(null, numTables);
+            ((FromTable) rightResultSet).transformOuterJoins(null, numTables);
 			return this;
 		}
 

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java?rev=998163&r1=998162&r2=998163&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
(original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
Fri Sep 17 14:56:48 2010
@@ -3133,4 +3133,250 @@ public final class OuterJoinTest extends
 
         JDBC.assertFullResultSet(rs, expRS);
     }
+
+
+    /**
+     * Test the queries reported in DERBY-4712 as giving null pointer
+     * exceptions. Should fail with NPE before the fix went in.  For bug
+     * explanation, see the JIRA issue and {@code JoinNode#transformOuterJoins}.
+     */
+    public void testDerby_4712_NPEs() throws Exception
+    {
+        setAutoCommit(false);
+
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String [][] expRS;
+
+        st.executeUpdate("create table t0(x0 int)");
+        st.executeUpdate("create table t1(x1 int)");
+        st.executeUpdate("create table t2(x2 int)");
+        st.executeUpdate("create table t3(x3 int)");
+        st.executeUpdate("create table t4(x4 int)");
+        st.executeUpdate("insert into t4 values(0)");
+        st.executeUpdate("insert into t4 values(1)");
+        st.executeUpdate("insert into t4 values(2)");
+        st.executeUpdate("insert into t4 values(3)");
+        st.executeUpdate("create table t5(x5 int)");
+        st.executeUpdate("insert into t5 values(0)");
+        st.executeUpdate("insert into t5 values(1)");
+        st.executeUpdate("insert into t5 values(2)");
+        st.executeUpdate("insert into t5 values(3)");
+        st.executeUpdate("insert into t5 values(4)");
+        st.executeUpdate("create table t6(x6 int)");
+        st.executeUpdate("insert into t6 values(0)");
+        st.executeUpdate("insert into t6 values(1)");
+        st.executeUpdate("insert into t6 values(2)");
+        st.executeUpdate("insert into t6 values(3)");
+        st.executeUpdate("insert into t6 values(4)");
+        st.executeUpdate("insert into t6 values(5)");
+        st.executeUpdate("create table t7(x7 int)");
+        st.executeUpdate("insert into t7 values(0)");
+        st.executeUpdate("insert into t7 values(1)");
+        st.executeUpdate("insert into t7 values(2)");
+        st.executeUpdate("insert into t7 values(3)");
+        st.executeUpdate("insert into t7 values(4)");
+        st.executeUpdate("insert into t7 values(5)");
+        st.executeUpdate("insert into t7 values(6)");
+        st.executeUpdate("create table t8(x8 int)");
+        st.executeUpdate("insert into t8 values(0)");
+        st.executeUpdate("insert into t8 values(1)");
+        st.executeUpdate("insert into t8 values(2)");
+        st.executeUpdate("insert into t8 values(3)");
+        st.executeUpdate("insert into t8 values(4)");
+        st.executeUpdate("insert into t8 values(5)");
+        st.executeUpdate("insert into t8 values(6)");
+        st.executeUpdate("insert into t8 values(7)");
+        st.executeUpdate("create table t9(x9 int)");
+        st.executeUpdate("insert into t9 values(0)");
+        st.executeUpdate("insert into t9 values(1)");
+        st.executeUpdate("insert into t9 values(2)");
+        st.executeUpdate("insert into t9 values(3)");
+        st.executeUpdate("insert into t9 values(4)");
+        st.executeUpdate("insert into t9 values(5)");
+        st.executeUpdate("insert into t9 values(6)");
+        st.executeUpdate("insert into t9 values(7)");
+        st.executeUpdate("insert into t9 values(8)");
+        st.executeUpdate("insert into t0 values(1)");
+        st.executeUpdate("insert into t1 values(2)");
+        st.executeUpdate("insert into t0 values(3)");
+        st.executeUpdate("insert into t1 values(3)");
+        st.executeUpdate("insert into t2 values(4)");
+        st.executeUpdate("insert into t0 values(5)");
+        st.executeUpdate("insert into t2 values(5)");
+        st.executeUpdate("insert into t1 values(6)");
+        st.executeUpdate("insert into t2 values(6)");
+        st.executeUpdate("insert into t0 values(7)");
+        st.executeUpdate("insert into t1 values(7)");
+        st.executeUpdate("insert into t2 values(7)");
+        st.executeUpdate("insert into t3 values(8)");
+        st.executeUpdate("insert into t0 values(9)");
+        st.executeUpdate("insert into t3 values(9)");
+        st.executeUpdate("insert into t1 values(10)");
+        st.executeUpdate("insert into t3 values(10)");
+        st.executeUpdate("insert into t0 values(11)");
+        st.executeUpdate("insert into t1 values(11)");
+        st.executeUpdate("insert into t3 values(11)");
+        st.executeUpdate("insert into t2 values(12)");
+        st.executeUpdate("insert into t3 values(12)");
+        st.executeUpdate("insert into t0 values(13)");
+        st.executeUpdate("insert into t2 values(13)");
+        st.executeUpdate("insert into t3 values(13)");
+        st.executeUpdate("insert into t1 values(14)");
+        st.executeUpdate("insert into t2 values(14)");
+        st.executeUpdate("insert into t3 values(14)");
+        st.executeUpdate("insert into t0 values(15)");
+        st.executeUpdate("insert into t1 values(15)");
+        st.executeUpdate("insert into t2 values(15)");
+        st.executeUpdate("insert into t3 values(15)");
+
+        rs = st.executeQuery(
+        "SELECT t0.x0,                                                  " +
+        "       t1.x1,                                                  " +
+        "       t2.x2,                                                  " +
+        "       t3.x3,                                                  " +
+        "       t4.x4,                                                  " +
+        "       t5.x5,                                                  " +
+        "       t6.x6,                                                  " +
+        "       t7.x7,                                                  " +
+        "       t8.x8                                                   " +
+        "FROM   (((t0                                                   " +
+        "          INNER JOIN ((t1                                      " +
+        "                       RIGHT OUTER JOIN (t2                    " +
+        "                                         INNER JOIN t3         " +
+        "                                           ON t2.x2 = t3.x3 )  " +
+        "                         ON t1.x1 = t2.x2 )                    " +
+        "                      LEFT OUTER JOIN (t4                      " +
+        "                                       INNER JOIN t5           " +
+        "                                         ON t4.x4 = t5.x5 )    " +
+        "                        ON t1.x1 = t4.x4 )                     " +
+        "            ON t0.x0 = t2.x2 )                                 " +
+        "         LEFT OUTER JOIN (t6                                   " +
+        "                          INNER JOIN t7                        " +
+        "                            ON t6.x6 = t7.x7 )                 " +
+        "           ON t1.x1 = t6.x6 )                                  " +
+        "        INNER JOIN t8                                          " +
+        "          ON t5.x5 = t8.x8 )                                   ");
+
+        JDBC.assertEmpty(rs);
+
+        rs = st.executeQuery(
+        "SELECT t0.x0,                                               " +
+        "       t1.x1,                                               " +
+        "       t2.x2,                                               " +
+        "       t3.x3,                                               " +
+        "       t4.x4,                                               " +
+        "       t5.x5,                                               " +
+        "       t6.x6,                                               " +
+        "       t7.x7                                                " +
+        "FROM   ((t0                                                 " +
+        "         RIGHT OUTER JOIN t1                                " +
+        "           ON t0.x0 = t1.x1 )                               " +
+        "        INNER JOIN (((t2                                    " +
+        "                      INNER JOIN (t3                        " +
+        "                                  LEFT OUTER JOIN t4        " +
+        "                                    ON t3.x3 = t4.x4 )      " +
+        "                        ON t2.x2 = t3.x3 )                  " +
+        "                     RIGHT OUTER JOIN t5                    " +
+        "                       ON t2.x2 = t5.x5 )                   " +
+        "                    LEFT OUTER JOIN (t6                     " +
+        "                                     INNER JOIN t7          " +
+        "                                       ON t6.x6 = t7.x7 )   " +
+        "                      ON t4.x4 = t6.x6 )                    " +
+        "          ON t0.x0 = t5.x5 )                                ");
+
+        expRS = new String [][]
+        {
+            {"3", "3", null, null, null, "3", null, null}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS);
+
+        rs = st.executeQuery(
+        "SELECT t0.x0,                                                " +
+        "       t1.x1,                                                " +
+        "       t2.x2,                                                " +
+        "       t3.x3,                                                " +
+        "       t4.x4,                                                " +
+        "       t5.x5,                                                " +
+        "       t6.x6,                                                " +
+        "       t7.x7                                                 " +
+        "FROM   ((((t0                                                " +
+        "           LEFT OUTER JOIN t1                                " +
+        "             ON t0.x0 = t1.x1 )                              " +
+        "          RIGHT OUTER JOIN t2                                " +
+        "            ON t0.x0 = t2.x2 )                               " +
+        "         RIGHT OUTER JOIN t3                                 " +
+        "           ON t0.x0 = t3.x3 )                                " +
+        "        INNER JOIN ((t4                                      " +
+        "                     INNER JOIN t5                           " +
+        "                       ON t4.x4 = t5.x5 )                    " +
+        "                    RIGHT OUTER JOIN (t6                     " +
+        "                                      RIGHT OUTER JOIN t7    " +
+        "                                        ON t6.x6 = t7.x7 )   " +
+        "                      ON t4.x4 = t6.x6 )                     " +
+        "          ON t1.x1 = t4.x4 )                                 ");
+
+        JDBC.assertEmpty(rs);
+
+        rs = st.executeQuery(
+        "SELECT t0.x0,                                    " +
+        "       t1.x1,                                    " +
+        "       t2.x2,                                    " +
+        "       t3.x3,                                    " +
+        "       t4.x4,                                    " +
+        "       t5.x5                                     " +
+        "FROM   (((t0                                     " +
+        "          INNER JOIN t1                          " +
+        "            ON t0.x0 = t1.x1 )                   " +
+        "         RIGHT OUTER JOIN (t2                    " +
+        "                           RIGHT OUTER JOIN t3   " +
+        "                             ON t2.x2 = t3.x3 )  " +
+        "           ON t0.x0 = t2.x2 )                    " +
+        "        INNER JOIN (t4                           " +
+        "                    LEFT OUTER JOIN t5           " +
+        "                      ON t4.x4 = t5.x5 )         " +
+        "          ON t1.x1 = t4.x4 )                     ");
+
+        JDBC.assertEmpty(rs);
+
+        rs = st.executeQuery(
+        "SELECT t0.x0,                                                    " +
+        "       t1.x1,                                                    " +
+        "       t2.x2,                                                    " +
+        "       t3.x3,                                                    " +
+        "       t4.x4,                                                    " +
+        "       t5.x5,                                                    " +
+        "       t6.x6                                                     " +
+        "FROM   ((t0                                                      " +
+        "         RIGHT OUTER JOIN                                        " +
+        "                  (t1                                            " +
+        "                   RIGHT OUTER JOIN (t2                          " +
+        "                                     LEFT OUTER JOIN             " +
+        "                                           (t3                   " +
+        "                                            LEFT OUTER JOIN t4   " +
+        "                                               ON t3.x3 = t4.x4  " +
+        "                                            )                    " +
+        "                                               ON t2.x2 = t3.x3 )" +
+        "                      ON t1.x1 = t3.x3 )                         " +
+        "           ON t0.x0 = t1.x1 )                                    " +
+        "        LEFT OUTER JOIN (t5                                      " +
+        "                         INNER JOIN t6                           " +
+        "                           ON t5.x5 = t6.x6 )                    " +
+        "          ON t2.x2 = t5.x5 )                                     ");
+
+        expRS = new String [][]
+        {
+            {null, null, "4", null, null, "4", "4"},
+            {null, null, "5", null, null, null, null},
+            {null, null, "6", null, null, null, null},
+            {null, null, "7", null, null, null, null},
+            {null, null, "12", "12", null, null, null},
+            {null, null, "13", "13", null, null, null},
+            {null, "14", "14", "14", null, null, null},
+            {"15", "15", "15", "15", null, null, null},
+        };
+
+        JDBC.assertFullResultSet(rs, expRS);
+    }
 }



Mime
View raw message