You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2010/09/15 15:27:55 UTC
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
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);
+ }
}