svn commit: r582439 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/

[bp...@apache.org]

Author: bpendleton
Date: Fri Oct  5 19:56:53 2007
New Revision: 582439

URL: http://svn.apache.org/viewvc?rev=582439&view=rev
Log:
DERBY-3033: NullPointerExceptions with flattening of NOT EXISTS subqueries

Merged from the trunk by svn merge -r 581942:581943

Added:
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
      - copied unchanged from r581943, db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
Removed:
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSetOpsTest.java
Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/FromList.java
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=582439&r1=582438&r2=582439&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/FromList.java Fri Oct  5 19:56:53 2007
@@ -1550,6 +1550,42 @@
 	}
 
 	/**
+	 * determine whether this table is NOT EXISTS.
+	 *
+	 * This routine searches for the indicated table number in the fromlist
+	 * and returns TRUE if the table is present in the from list and is 
+	 * marked NOT EXISTS, false otherwise.
+	 *
+	 * A table may be present in the from list for NOT EXISTS if it is used
+	 * as a correlated NOT EXISTS subquery. In such a situation, when the
+	 * subquery is flattened, it is important that we remember that this is
+	 * a NOT EXISTS subquery, because the join semantics are different 
+	 * (we're looking for rows that do NOT match, rather than rows
+	 * that do). And since the join semantics are different, we cannot
+	 * include this table into a transitive closure of equijoins
+	 * (See DERBY-3033 for a situation where this occurs).
+	 *
+	 * @param tableNumber	which table to check
+	 * @return true if this table is in the from list as NOT EXISTS
+	 */
+	boolean tableNumberIsNotExists(int tableNumber)
+		throws StandardException
+	{
+		int size = size();
+		for (int index = 0; index < size; index++)
+		{
+			ProjectRestrictNode prn = (ProjectRestrictNode) elementAt(index);
+			if (! (prn.getChildResult() instanceof FromTable))
+			{
+				continue;
+			}
+			FromTable ft = (FromTable) prn.getChildResult();
+			if (ft.getTableNumber() == tableNumber)
+				return ft.isNotExists();
+		}
+		return false;
+	}
+	/**
 	 * Get the lock mode for the target of an update statement
 	 * (a delete or update).  The update mode will always be row for
 	 * CurrentOfNodes.  It will be table if there is no where clause.

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=582439&r1=582438&r2=582439&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java Fri Oct  5 19:56:53 2007
@@ -1818,6 +1818,14 @@
      * between outer and inner.  If so, then we simply assign it to the same 
      * equivalence class.  If not, then we add the new equijoin clause.
 	 *
+	 * Note that an equijoin predicate between two tables CANNOT be
+	 * used for transitive closure, if either of the tables is in the
+	 * fromlist for NOT EXISTS. In that case, the join predicate
+	 * actually specifies that the rows from the indicated table must
+	 * NOT exist, and therefore those non-existent rows cannot be
+	 * transitively joined to the other matching tables. See DERBY-3033
+	 * for a description of a situation in which this actually arises.
+	 *
 	 * @param numTables	The number of tables in the query
 	 * @param fromList	The FromList in question.
 	 * @param cc		The CompilerContext to use
@@ -1867,7 +1875,9 @@
 				ColumnReference leftCR = (ColumnReference) left;
 				ColumnReference rightCR = (ColumnReference) right;
 				if (leftCR.getSourceLevel() == rightCR.getSourceLevel() &&
-					leftCR.getTableNumber() != rightCR.getTableNumber())
+					leftCR.getTableNumber() != rightCR.getTableNumber() &&
+					!fromList.tableNumberIsNotExists(leftCR.getTableNumber()) &&
+					!fromList.tableNumberIsNotExists(rightCR.getTableNumber()))
 				{
 					// Add the equijoin clause to each of the lists
 					joinClauses[leftCR.getTableNumber()].addElement(predicate);

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=582439&r1=582438&r2=582439&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Fri Oct  5 19:56:53 2007
@@ -63,7 +63,7 @@
         suite.addTest(CreateTableFromQueryTest.suite());
         suite.addTest(DatabaseClassLoadingTest.suite());
         suite.addTest(DynamicLikeOptimizationTest.suite());
-        suite.addTest(ExistsWithSetOpsTest.suite());
+        suite.addTest(ExistsWithSubqueriesTest.suite());
         suite.addTest(GrantRevokeTest.suite());
         suite.addTest(GroupByExpressionTest.suite());
 		suite.addTest(LangScripts.suite());