db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r997325 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/JoinNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
Date Wed, 15 Sep 2010 13:27:55 GMT
Author: dag
Date: Wed Sep 15 13:27:55 2010
New Revision: 997325

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

Patch DERBY-4712b, which removes one of the source 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/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=997325&r1=997324&r2=997325&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Wed Sep
15 13:27:55 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java?rev=997325&r1=997324&r2=997325&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
Wed Sep 15 13:27:55 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