svn commit: r989099 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/SelectNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java

[da...@apache.org]

Author: dag
Date: Wed Aug 25 13:29:37 2010
New Revision: 989099

URL: http://svn.apache.org/viewvc?rev=989099&view=rev
Log:
DERBY-4736 ASSERT FAIL when code generating a column reference in a join predicate in presence of other outer join reordering

Follow-up patch derby-4736-followup-a.

In some cases, with this fix, the nullability of columns from the
null-producing (right) side of the LOJ gets set to NOT NULL after
reassociation.

The problem is that the added call to SelectNode#bindResultColumns, in
addition to calling fromList.bindResultColumns, which what we need in
for this issue, also calls super.bindResultColumns, which sets up the
datatypes over again, erroneously (i.e. without taking into
consideration the nature of outer join which can make values stemming
from otherwise NOT NULL columns be null in the final result set).

Replacing the call to SelectNode#bindResultColumns with
fromTable.bindResultColumns avoids this problem.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.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/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=989099&r1=989098&r2=989099&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Wed Aug 25 13:29:37 2010
@@ -1000,7 +1000,7 @@ public class SelectNode extends ResultSe
 																	 getNodeFactory().doJoinOrderOptimization(),
 																	 getContextManager());
 			bindExpressions(afromList);
-            bindResultColumns(afromList);
+            fromList.bindResultColumns(afromList);
 		}
 
 		/* Preprocess the fromList.  For each FromTable, if it is a FromSubquery

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=989099&r1=989098&r2=989099&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 Aug 25 13:29:37 2010
@@ -2545,4 +2545,67 @@ public final class OuterJoinTest extends
 
         rs.close();
     }
-}
+
+
+    /**
+     * Test for a follow-up patch for DERBY-4736: verify that nullability in
+     * result set metadata is correct also for columns for the null-producing
+     * side of the LOJ.
+     */
+    public void testDerby_4736_nullability() throws Exception
+    {
+        setAutoCommit(false);
+
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String [][] expRS;
+        String [] expColNames;
+
+        st.executeUpdate(
+            "CREATE TABLE T (A INT NOT NULL, B DECIMAL(10,3) NOT "
+            + "NULL, C VARCHAR(5) NOT NULL)");
+
+        st.executeUpdate(
+            " INSERT INTO T VALUES (1, 1.0, '1'), (2, 2.0, '2'), "
+            + "(3, 3.0, '3')");
+
+        st.executeUpdate(
+            " CREATE TABLE S (D INT NOT NULL, E DECIMAL(10,3) "
+            + "NOT NULL, F VARCHAR(5) NOT NULL)");
+
+        st.executeUpdate(
+            " INSERT INTO S VALUES (2, 2.0, '2'), (3, 3.0, '3'), "
+            + "(4, 4.0, '4')");
+
+        st.executeUpdate(
+            "create view v1 (fv, ev, dv, cv, bv, av) as (select "
+            + "f, e, d, c, b, a from t left outer join s on b = e)");
+
+        rs = st.executeQuery(
+            " select * from t left outer join (s left outer join "
+            + "v1 on (f = cv)) on (d=a)");
+
+        expColNames = new String [] {"A", "B", "C", "D", "E", "F",
+                                     "FV", "EV", "DV", "CV", "BV", "AV"};
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String [][]
+        {
+            // Before the follow-up patch, the three first NULL column values
+            // below would get NOT NULL metadata before the follow-up patch
+            // (caught by JDBC.assertResultColumnNullable called from
+            // JDBC.assertRowInResultSet if a null value is seen).
+            //
+            {"1", "1.000", "1", null, null, null,
+             "1", "1.000", "1", null, null, null},
+
+            {"2", "2.000", "2", "2", "2.000", "2",
+             "2", "2.000", "2", "2", "2.000", "2"},
+
+            {"3", "3.000", "3", "3", "3.000", "3",
+             "3", "3.000", "3", "3", "3.000", "3"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS);
+    }
+ }