db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r810860 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Thu, 03 Sep 2009 10:13:18 GMT
Author: kahatlen
Date: Thu Sep  3 10:13:17 2009
New Revision: 810860

URL: http://svn.apache.org/viewvc?rev=810860&view=rev
Log:
DERBY-4284: All Columns become Nullable when Using left join

Only the columns from the right side of a left outer join, or
from the left side of a right outer join, should be changed to
nullable. This is done correctly in JoinNode.buildRCL(), but in
addition SelectNode.bindResultColumns() makes all result columns
nullable if the from list contains an outer join.

This patch fixes the bug by removing the code in SelectNode that
makes all columns nullable. This also made it possible to remove
a method in FromList that became unused after the fix. Also, some
test canons needed to be updated because IJ may change the
formatting/spacing slightly depending on whether the result
columns are nullable or not.

The fix also exposed another manifestation of DERBY-2916, where
some nodes saw the wrong nullability because they looked at the
columns before JoinNode.buildRCL() had done its job. This was
addressed by adding a workaround to JoinNode.getMatchingColumn().

Also, changes that had been made to wisconsin.out as part of
DERBY-2916 earlier in order to make the Wisconsin test run
cleanly despite the bug, could now be reverted because of the
workaround.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml162.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=810860&r1=810859&r2=810860&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java Thu Sep
 3 10:13:17 2009
@@ -385,27 +385,6 @@
 	}
 
 	/**
-	 * Returns true if any Outer joins present. Used to set Nullability
-	 *
-	 * @return	True if has any outer joins. False otherwise.
-	 */
-	public boolean hasOuterJoins()
-				throws StandardException
-	{
-		FromTable	fromTable;
-
-		int size = size();
-		for (int index = 0; index < size; index++)
-		{
-			fromTable = (FromTable) elementAt(index);
-			if (fromTable instanceof HalfOuterJoinNode)
-				return true;
-		}
-
-		return false;
-	}
-
-	/**
 	 * Expand a "*" into the appropriate ResultColumnList. If the "*"
 	 * is unqualified it will expand into a list of all columns in all
 	 * of the base tables in the from list at the current nesting level;

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=810860&r1=810859&r2=810860&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 Thu Sep
 3 10:13:17 2009
@@ -572,6 +572,17 @@
 				throw StandardException.newException(SQLState.LANG_AMBIGUOUS_COLUMN_NAME, 
 						 columnReference.getSQLColumnName());
 			}
+
+            // All columns on the logical right side of a "half" outer join
+            // can contain nulls. The correct nullability is set by
+            // bindResultColumns()/buildRCL(). However, if bindResultColumns()
+            // has not been called yet, the caller of this method will see
+            // the wrong nullability. This problem is logged as DERBY-2916.
+            // Until that's fixed, set the nullability here too.
+            if (this instanceof HalfOuterJoinNode) {
+                rightRC.setNullability(true);
+            }
+
 			resultColumn = rightRC;
 		}
 

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=810860&r1=810859&r2=810860&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 Thu
Sep  3 10:13:17 2009
@@ -641,10 +641,6 @@
 		{
 			throw StandardException.newException(SQLState.LANG_TOO_MANY_ELEMENTS);
 		}
-
-		/* Fix nullability in case of any outer joins in the fromList */
-		if (fromList.hasOuterJoins())
-			resultColumns.setNullability(true);
 	}
 
 	/**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out?rev=810860&r1=810859&r2=810860&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out
Thu Sep  3 10:13:17 2009
@@ -162,13 +162,13 @@
     FROM SEVEN_TYPES right outer JOIN HU.STAFF
        ON GRADE IN (10, 11, 13) AND EMPNUM = T_CHAR
    ORDER BY EMPNAME, T_INT;
-T_INT      |T_CHAR    |EMPNAME             |EMP&|GRADE
-------------------------------------------------------
-NULL       |NULL      |Alice               |E1  |12   
-2          |E2        |Betty               |E2  |10   
-NULL       |NULL      |Carmen              |E3  |13   
-NULL       |NULL      |Don                 |E4  |12   
-NULL       |NULL      |Ed                  |E5  |13   
+T_INT      |T_CHAR    |EMPNAME             |EM&|GRADE
+-----------------------------------------------------
+NULL       |NULL      |Alice               |E1 |12   
+2          |E2        |Betty               |E2 |10   
+NULL       |NULL      |Carmen              |E3 |13   
+NULL       |NULL      |Don                 |E4 |12   
+NULL       |NULL      |Ed                  |E5 |13   
 ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ?
 -- PASS:0844    NULL NULL Alice  E1 12  ?
 -- PASS:0844       2 E2   Betty  E2 10  ?

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml162.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml162.out?rev=810860&r1=810859&r2=810860&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml162.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml162.out
Thu Sep  3 10:13:17 2009
@@ -45,21 +45,21 @@
 
    SELECT * FROM HU.STAFF LEFT OUTER JOIN HU.WORKS
       ON (HU.STAFF.EMPNUM=HU.WORKS.EMPNUM);
-EMP&|EMPNAME             |GRADE|CITY           |EMP&|PNUM|HOURS 
-----------------------------------------------------------------
-E1  |Alice               |12   |Deale          |E1  |P1  |40    
-E1  |Alice               |12   |Deale          |E1  |P2  |20    
-E1  |Alice               |12   |Deale          |E1  |P3  |80    
-E1  |Alice               |12   |Deale          |E1  |P4  |20    
-E1  |Alice               |12   |Deale          |E1  |P5  |12    
-E1  |Alice               |12   |Deale          |E1  |P6  |12    
-E2  |Betty               |10   |Vienna         |E2  |P1  |40    
-E2  |Betty               |10   |Vienna         |E2  |P2  |80    
-E3  |Carmen              |13   |Vienna         |E3  |P2  |20    
-E4  |Don                 |12   |Deale          |E4  |P2  |20    
-E4  |Don                 |12   |Deale          |E4  |P4  |40    
-E4  |Don                 |12   |Deale          |E4  |P5  |80    
-E5  |Ed                  |13   |Akron          |NULL|NULL|NULL  
+EM&|EMPNAME             |GRADE|CITY           |EMP&|PNUM|HOURS 
+---------------------------------------------------------------
+E1 |Alice               |12   |Deale          |E1  |P1  |40    
+E1 |Alice               |12   |Deale          |E1  |P2  |20    
+E1 |Alice               |12   |Deale          |E1  |P3  |80    
+E1 |Alice               |12   |Deale          |E1  |P4  |20    
+E1 |Alice               |12   |Deale          |E1  |P5  |12    
+E1 |Alice               |12   |Deale          |E1  |P6  |12    
+E2 |Betty               |10   |Vienna         |E2  |P1  |40    
+E2 |Betty               |10   |Vienna         |E2  |P2  |80    
+E3 |Carmen              |13   |Vienna         |E3  |P2  |20    
+E4 |Don                 |12   |Deale          |E4  |P2  |20    
+E4 |Don                 |12   |Deale          |E4  |P4  |40    
+E4 |Don                 |12   |Deale          |E4  |P5  |80    
+E5 |Ed                  |13   |Akron          |NULL|NULL|NULL  
 ij> -- PASS:0863 If 13 rows are returned?
 
    COMMIT WORK;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out?rev=810860&r1=810859&r2=810860&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
Thu Sep  3 10:13:17 2009
@@ -136,13 +136,13 @@
 NULL       
 NULL       
 ij> select a.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left
outer join (values ('e', 'f')) c on 1=1;
-1   |2   
----------
-a   |b   
+1|2
+---
+a|b
 ij> select b.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left
outer join (values ('e', 'f')) c on 1=1;
-1   |2   
----------
-c   |d   
+1|2
+---
+c|d
 ij> select c.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left
outer join (values ('e', 'f')) c on 1=1;
 1   |2   
 ---------

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out?rev=810860&r1=810859&r2=810860&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
Thu Sep  3 10:13:17 2009
@@ -29214,11 +29214,9 @@
 					start position:
 						>= on first 1 column(s).
 						Ordered null semantics on the following columns: 
-						0 
 					stop position:
 						> on first 1 column(s).
 						Ordered null semantics on the following columns: 
-						0 
 					qualifiers:
 						None
 	Right result set:

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=810860&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Thu Sep  3 10:13:17 2009
@@ -0,0 +1,124 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.JoinTest
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test cases for JOINs.
+ */
+public class JoinTest extends BaseJDBCTestCase {
+    public JoinTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        return TestConfiguration.defaultSuite(JoinTest.class);
+    }
+
+    /**
+     * Test that the columns returned by a left or right outer join have the
+     * correct nullability. In a left outer join, the columns from the left
+     * side of the join should have their original nullability, and all the
+     * columns from the right side of the join should be nullable. In a right
+     * outer join, all the columns from the left side should be nullable,
+     * and the columns from the right side should preserve their original
+     * nullability. DERBY-4284.
+     */
+    public void testNullabilityInLeftOrRightOuterJoin() throws SQLException {
+        // Turn auto-commit off so that tearDown() can roll back all test data
+        setAutoCommit(false);
+
+        Statement s = createStatement();
+        s.execute("create table t (c1 int not null, c2 int not null, c3 int)");
+
+        // Nullability should be unchanged for columns from the left side
+        // (first three columns) and nullable for the ones from the right side).
+        ResultSet rs = s.executeQuery(
+                "select * from t t1 left outer join t t2 on 1=1");
+        JDBC.assertNullability(rs,
+                new boolean[]{false, false, true, true, true, true});
+        JDBC.assertEmpty(rs);
+
+        // Nullability should be unchanged for columns from the right side of
+        // the right outer join, and nullable for the ones from the left side.
+        rs = s.executeQuery(
+                "select * from t t1 right outer join t t2 on 1=1");
+        JDBC.assertNullability(rs,
+                new boolean[]{true, true, true, false, false, true});
+        JDBC.assertEmpty(rs);
+
+        // CASTs had some problems where they set the nullability too early
+        // to get it correctly from the underlying join. Test it here.
+        rs = s.executeQuery(
+                "select cast(t1.c1 as int), cast(t2.c2 as int) from " +
+                "t t1 left outer join t t2 on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{false, true});
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+                "select cast(t1.c1 as int), cast(t2.c2 as int) from " +
+                "t t1 right outer join t t2 on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{true, false});
+        JDBC.assertEmpty(rs);
+
+        // Nested outer joins
+        rs = s.executeQuery(
+                "select t1.c1, t2.c1, t3.c1 from " +
+                "t t1 left join (t t2 left join t t3 on 1=1) on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{false, true, true});
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+                "select t1.c1, t2.c1, t3.c1 from " +
+                "t t1 right join (t t2 right join t t3 on 1=1) on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{true, true, false});
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+                "select t1.c1, t2.c1, t3.c1, t4.c1 from " +
+                "(t t1 left join t t2 on 1=1) left join " +
+                "(t t3 left join t t4 on 1=1) on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{false, true, true, true});
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+                "select t1.c1, t2.c1, t3.c1, t4.c1 from " +
+                "(t t1 left join t t2 on 1=1) right join " +
+                "(t t3 left join t t4 on 1=1) on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{true, true, false, true});
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+                "select t1.c1, t2.c1, t3.c1, t4.c1 from " +
+                "(t t1 right join t t2 on 1=1) left join " +
+                "(t t3 left join t t4 on 1=1) on 1=1");
+        JDBC.assertNullability(rs, new boolean[]{true, false, true, true});
+        JDBC.assertEmpty(rs);
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=810860&r1=810859&r2=810860&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Thu Sep  3 10:13:17 2009
@@ -73,6 +73,7 @@
         suite.addTest(GroupByExpressionTest.suite());
         suite.addTest(InbetweenTest.suite());
         suite.addTest(InsertTest.suite());
+        suite.addTest(JoinTest.suite());
 		suite.addTest(LangScripts.suite());
         suite.addTest(MathTrigFunctionsTest.suite());
         suite.addTest(PrepareExecuteDDL.suite());



Mime
View raw message