db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d...@apache.org
Subject svn commit: r618586 - 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 Tue, 05 Feb 2008 09:29:33 GMT
Author: dyre
Date: Tue Feb  5 01:29:32 2008
New Revision: 618586

URL: http://svn.apache.org/viewvc?rev=618586&view=rev
Log:
DERBY-3301: Incorrect result from query with nested EXIST
Prevent the optimizer from flattening subqueries that 
need to be evaluated to get correct results.

Patch contributed by Thomas Nielsen
Patch files: derby-3301-8.diff, derby-3301-test-master-2.diff,
derby-3301-test-3.diff


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.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/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=618586&r1=618585&r2=618586&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 Tue
Feb  5 01:29:32 2008
@@ -112,6 +112,8 @@
 
 	private boolean orderByAndDistinctMerged;
 
+	boolean originalWhereClauseHadSubqueries;
+	
 	/* Copy of fromList prior to generating join tree */
 	private FromList preJoinFL;
 
@@ -138,6 +140,16 @@
 		this.groupByList = (GroupByList) groupByList;
 		this.havingClause = (ValueNode)havingClause;
 		bindTargetListOnly = false;
+		
+		this.originalWhereClauseHadSubqueries = false;
+		if (this.whereClause != null){
+			CollectNodesVisitor cnv = 
+				new CollectNodesVisitor(SubqueryNode.class, SubqueryNode.class);
+			this.whereClause.accept(cnv);
+			if (!cnv.getList().isEmpty()){
+				this.originalWhereClauseHadSubqueries = true;
+			}
+		}
 	}
 
 	/**
@@ -456,7 +468,7 @@
 			whereClause = whereClause.bindExpression(fromListParam, 
 										whereSubquerys,
 										whereAggregates);
-
+			
 			/* RESOLVE - Temporarily disable aggregates in the HAVING clause.
 			** (We may remove them in the parser anyway.)
 			** RESOLVE - Disable aggregates in the WHERE clause.  Someday
@@ -868,6 +880,13 @@
 		 */
 		if (whereClause != null)
 		{
+			// DERBY-3301
+			// Mark subqueries that are part of the where clause as such so
+			// that we can avoid flattening later, particularly for nested 
+			// WHERE EXISTS subqueries.
+			if (whereSubquerys != null){
+				whereSubquerys.markWhereSubqueries();
+			}
 			whereClause.preprocess(numTables,
 								   fromList, whereSubquerys,
 								   wherePredicates);

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java Tue
Feb  5 01:29:32 2008
@@ -255,5 +255,20 @@
 	        subqueryNode.setHavingSubquery(true);
 	    }
 	}
+
+	/**
+	 * Mark all of the subqueries in this list as being part of a where clause
+	 * so we can avoid flattening later if needed.
+	 */
+	public void markWhereSubqueries() {
+		int size = size();
+		for (int index = 0; index < size; index++)
+		{
+			SubqueryNode    subqueryNode;
+
+			subqueryNode = (SubqueryNode) elementAt(index);
+			subqueryNode.setWhereSubquery(true);
+		}
+	}
 }
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Tue
Feb  5 01:29:32 2008
@@ -44,6 +44,7 @@
 
 import java.lang.reflect.Modifier;
 
+import java.util.Iterator;
 import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
 import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
 import org.apache.derby.impl.sql.execute.OnceResultSet;
@@ -110,6 +111,9 @@
 	/* Whether or not this subquery began life as a distinct expression subquery */
 	boolean			distinctExpression;
 
+	/* Whether or not this subquery began life as a subquery in a where clause */
+	boolean			whereSubquery;
+	
 	/* Since we do not have separate subquery operator nodes, the
 	 * type of the subquery is stored in the subqueryType field.  Most subquery
 	 * types take a left operand (except for expression and exists).  We could
@@ -614,9 +618,14 @@
          *  o It is not a subquery in a having clause (DERBY-3257)
 		 *  o It is an expression subquery on the right side
 		 *	  of a BinaryComparisonOperatorNode.
+		 *  o Either a) it does not appear within a WHERE clause, or 
+		 *           b) it appears within a WHERE clause but does not itself 
+		 *              contain a WHERE clause with other subqueries in it. 
+		 *          (DERBY-3301)
 		 */
 		flattenable = (resultSet instanceof RowResultSetNode) &&
 					  underTopAndNode && !havingSubquery &&
+					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  parentComparisonOperator instanceof BinaryComparisonOperatorNode;
 		if (flattenable)
 		{
@@ -677,11 +686,16 @@
 		 *
 		 *	OR,
 		 *  o The subquery is NOT EXISTS, NOT IN, ALL (beetle 5173).
+		 *  o Either a) it does not appear within a WHERE clause, or 
+		 *           b) it appears within a WHERE clause but does not itself 
+		 *              contain a WHERE clause with other subqueries in it. 
+		 *          (DERBY-3301)
 		 */
 		boolean flattenableNotExists = (isNOT_EXISTS() || canAllBeFlattened());
 
 		flattenable = (resultSet instanceof SelectNode) &&
 					  underTopAndNode && !havingSubquery &&
+					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
                        parentComparisonOperator != null);
 
@@ -2310,4 +2324,67 @@
     public void setHavingSubquery(boolean havingSubquery) {
         this.havingSubquery = havingSubquery;
     }
+	
+
+	/**
+	 * Is this subquery part of a whereclause?
+	 *
+	 * @return true if it is part of a where clause, otherwise false
+	 */
+	public boolean isWhereSubquery() {
+		return whereSubquery;
+	}
+
+	/**
+	 * Mark this subquery as being part of a where clause.
+	 * @param whereSubquery
+	 */
+	public void setWhereSubquery(boolean whereSubquery) {
+		this.whereSubquery = whereSubquery;
+	}
+
+	/**
+	 * Check whether this is a WHERE EXISTS | ANY | IN subquery with a subquery
+	 * in its own WHERE clause. Used in flattening decision making.
+	 * 
+	 * DERBY-3301 reported wrong results from a nested WHERE EXISTS, but 
+	 * according to the derby optimizer docs this applies to a broader range of 
+	 * WHERE clauses in a WHERE EXISTS subquery. No WHERE EXISTS subquery with 
+	 * anohter subquery in it own WHERE clause can be flattened. 
+	 * 
+	 * @return true if this subquery is a WHERE EXISTS | ANY | IN subquery with 
+	 *              a subquery in its own WHERE clause
+	 */
+	public boolean isWhereExistsAnyInWithWhereSubquery() 
+			throws StandardException
+	{
+		if ( isWhereSubquery() && (isEXISTS() || isANY() || isIN()) ) {
+			if (resultSet instanceof SelectNode){
+				SelectNode sn = (SelectNode) resultSet;
+				/* 
+				 * Flattening happens in lower QueryTree nodes first and then 
+				 * removes nodes from the whereSubquerys list or whereClause. 
+				 * Hence we check the original WHERE clause for subqueries in 
+				 * SelectNode.init(), and simply check here.
+				 */ 
+				if (sn.originalWhereClauseHadSubqueries){
+					/*
+					 * This is a WHERE EXISTS | ANY |IN subquery with a subquery
+					 * in its own WHERE clause (or now in whereSubquerys).
+					 */ 
+					return true;
+				}	
+			}
+			/* 
+			 * This is a WHERE EXISTS | ANY | IN subquery, but does not contain 
+			 * a subquery in its WHERE subquerylist or clause
+			 */
+			return false;
+		} else {
+			/* 
+			 * This isn't a WHERE EXISTS | ANY | IN subquery 
+			 */
+			return false;
+		}
+	}
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
Tue Feb  5 01:29:32 2008
@@ -1207,123 +1207,129 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (6):
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-restriction = false
-projection = true
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	restriction time (milliseconds) = 0
-	projection time (milliseconds) = 0
-Source result set:
-	Nested Loop Exists Join ResultSet:
-	Number of opens = 1
-	Rows seen from the left = 1
-	Rows seen from the right = 1
-	Rows filtered = 0
-	Rows returned = 1
+Attached subqueries:
+	Begin Subquery Number 0
+	Any ResultSet  (Attached to 2):
+	Number of opens = 2
+	Rows seen = 2
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-	Left result set:
-		Nested Loop Join ResultSet:
-		Number of opens = 1
-		Rows seen from the left = 1
-		Rows seen from the right = 1
+	Source result set:
+		Project-Restrict ResultSet (6):
+		Number of opens = 2
+		Rows seen = 1
 		Rows filtered = 0
-		Rows returned = 1
+		restriction = false
+		projection = true
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		Left result set:
-			Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using
share table locking chosen by the optimizer
-			Number of opens = 1
-			Rows seen = 1
-			Rows filtered = 0
-			Fetch Size = 16
-				constructor time (milliseconds) = 0
-				open time (milliseconds) = 0
-				next time (milliseconds) = 0
-				close time (milliseconds) = 0
-				next time in milliseconds/row = 0
-			scan information: 
-				Bit set of columns fetched={0, 1}
-				Number of columns fetched=2
-				Number of deleted rows visited=0
-				Number of pages visited=1
-				Number of rows qualified=1
-				Number of rows visited=2
-				Scan type=btree
-				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
-				qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-		Right result set:
-			Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking
chosen by the optimizer
-			Number of opens = 1
-			Rows seen = 1
+			restriction time (milliseconds) = 0
+			projection time (milliseconds) = 0
+		Source result set:
+			Nested Loop Exists Join ResultSet:
+			Number of opens = 2
+			Rows seen from the left = 1
+			Rows seen from the right = 1
 			Rows filtered = 0
-			Fetch Size = 16
+			Rows returned = 1
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-				next time in milliseconds/row = 0
-			scan information: 
-				Bit set of columns fetched=All
-				Number of columns fetched=3
-				Number of pages visited=1
-				Number of rows qualified=1
-				Number of rows visited=2
-				Scan type=heap
-				start position: 
-null				stop position: 
-null				qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-	Right result set:
-		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using
share row locking chosen by the optimizer
-		Number of opens = 1
-		Rows seen = 1
-		Rows filtered = 0
-		Fetch Size = 1
-			constructor time (milliseconds) = 0
-			open time (milliseconds) = 0
-			next time (milliseconds) = 0
-			close time (milliseconds) = 0
-			next time in milliseconds/row = 0
-		scan information: 
-			Bit set of columns fetched={0}
-			Number of columns fetched=1
-			Number of deleted rows visited=0
-			Number of pages visited=1
-			Number of rows qualified=1
-			Number of rows visited=1
-			Scan type=btree
-			Tree height=1
-			start position: 
+			Left result set:
+				Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using
share row locking chosen by the optimizer
+				Number of opens = 2
+				Rows seen = 1
+				Rows filtered = 0
+				Fetch Size = 1
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					next time in milliseconds/row = 0
+				scan information: 
+					Bit set of columns fetched={0, 1}
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=2
+					Number of rows qualified=1
+					Number of rows visited=1
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 2 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 2 column(s).
+	Ordered null semantics on the following columns: 
+					qualifiers:
+None
+			Right result set:
+				Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using
share row locking chosen by the optimizer
+				Number of opens = 1
+				Rows seen = 1
+				Rows filtered = 0
+				Fetch Size = 1
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					next time in milliseconds/row = 0
+				scan information: 
+					Bit set of columns fetched={0}
+					Number of columns fetched=1
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=1
+					Number of rows visited=1
+					Scan type=btree
+					Tree height=1
+					start position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
-			stop position: 
+					stop position: 
 	> on first 1 column(s).
 	Ordered null semantics on the following columns: 
-			qualifiers:
+					qualifiers:
+None
+	End Subquery Number 0
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 2
+Rows filtered = 1
+restriction = true
+projection = false
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking
chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 2
+	Rows filtered = 0
+	Fetch Size = 16
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+		next time in milliseconds/row = 0
+	scan information: 
+		Bit set of columns fetched=All
+		Number of columns fetched=3
+		Number of pages visited=1
+		Number of rows qualified=2
+		Number of rows visited=2
+		Scan type=heap
+		start position: 
+null		stop position: 
+null		qualifiers:
 None
 ij> -- only flatten bottom
 select * from outer1 o where exists
@@ -1516,121 +1522,127 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (6):
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-restriction = false
-projection = true
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	restriction time (milliseconds) = 0
-	projection time (milliseconds) = 0
-Source result set:
-	Nested Loop Exists Join ResultSet:
-	Number of opens = 1
-	Rows seen from the left = 1
-	Rows seen from the right = 1
-	Rows filtered = 0
-	Rows returned = 1
+Attached subqueries:
+	Begin Subquery Number 0
+	Any ResultSet  (Attached to 2):
+	Number of opens = 2
+	Rows seen = 2
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-	Left result set:
-		Nested Loop Join ResultSet:
-		Number of opens = 1
-		Rows seen from the left = 1
-		Rows seen from the right = 1
+	Source result set:
+		Project-Restrict ResultSet (6):
+		Number of opens = 2
+		Rows seen = 1
 		Rows filtered = 0
-		Rows returned = 1
+		restriction = false
+		projection = true
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		Left result set:
-			Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using
share table locking chosen by the optimizer
-			Number of opens = 1
-			Rows seen = 1
-			Rows filtered = 0
-			Fetch Size = 16
-				constructor time (milliseconds) = 0
-				open time (milliseconds) = 0
-				next time (milliseconds) = 0
-				close time (milliseconds) = 0
-				next time in milliseconds/row = 0
-			scan information: 
-				Bit set of columns fetched={0, 1}
-				Number of columns fetched=2
-				Number of deleted rows visited=0
-				Number of pages visited=1
-				Number of rows qualified=1
-				Number of rows visited=2
-				Scan type=btree
-				Tree height=1
-				start position: 
-	None
-				stop position: 
-	None
-				qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-		Right result set:
-			Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking
chosen by the optimizer
-			Number of opens = 1
-			Rows seen = 1
+			restriction time (milliseconds) = 0
+			projection time (milliseconds) = 0
+		Source result set:
+			Nested Loop Exists Join ResultSet:
+			Number of opens = 2
+			Rows seen from the left = 1
+			Rows seen from the right = 1
 			Rows filtered = 0
-			Fetch Size = 16
+			Rows returned = 1
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-				next time in milliseconds/row = 0
-			scan information: 
-				Bit set of columns fetched=All
-				Number of columns fetched=3
-				Number of pages visited=1
-				Number of rows qualified=1
-				Number of rows visited=2
-				Scan type=heap
-				start position: 
-null				stop position: 
-null				qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
-	Right result set:
-		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using
share table locking chosen by the optimizer
-		Number of opens = 1
-		Rows seen = 1
-		Rows filtered = 0
-		Fetch Size = 1
-			constructor time (milliseconds) = 0
-			open time (milliseconds) = 0
-			next time (milliseconds) = 0
-			close time (milliseconds) = 0
-			next time in milliseconds/row = 0
-		scan information: 
-			Bit set of columns fetched={}
-			Number of columns fetched=0
-			Number of deleted rows visited=0
-			Number of pages visited=1
-			Number of rows qualified=1
-			Number of rows visited=1
-			Scan type=btree
-			Tree height=1
-			start position: 
+			Left result set:
+				Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using
share row locking chosen by the optimizer
+				Number of opens = 2
+				Rows seen = 1
+				Rows filtered = 0
+				Fetch Size = 1
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					next time in milliseconds/row = 0
+				scan information: 
+					Bit set of columns fetched={0, 1}
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=2
+					Number of rows qualified=1
+					Number of rows visited=1
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 2 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 2 column(s).
+	Ordered null semantics on the following columns: 
+					qualifiers:
+None
+			Right result set:
+				Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using
share table locking chosen by the optimizer
+				Number of opens = 1
+				Rows seen = 1
+				Rows filtered = 0
+				Fetch Size = 1
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					next time in milliseconds/row = 0
+				scan information: 
+					Bit set of columns fetched={}
+					Number of columns fetched=0
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=1
+					Number of rows visited=1
+					Scan type=btree
+					Tree height=1
+					start position: 
 	None
-			stop position: 
+					stop position: 
 	None
-			qualifiers:
+					qualifiers:
+None
+	End Subquery Number 0
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 2
+Rows filtered = 1
+restriction = true
+projection = false
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking
chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 2
+	Rows filtered = 0
+	Fetch Size = 16
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+		next time in milliseconds/row = 0
+	scan information: 
+		Bit set of columns fetched=All
+		Number of columns fetched=3
+		Number of pages visited=1
+		Number of rows qualified=2
+		Number of rows visited=2
+		Scan type=heap
+		start position: 
+null		stop position: 
+null		qualifiers:
 None
 ij> -- flatten a subquery that has a subquery in its select list
 -- verify that subquery gets copied up to outer block

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java?rev=618586&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
Tue Feb  5 01:29:32 2008
@@ -0,0 +1,208 @@
+/**
+ *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.NestedWhereSubqueryTest
+ *  
+ * 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;
+
+/**
+ * Nested WHERE subquery tests. Tests nested WHERE EXISTS | ANY | IN functionality.
+ *
+ * Please refer to DERBY-3301 for more details.
+ */
+public class NestedWhereSubqueryTest extends BaseJDBCTestCase {
+
+	public NestedWhereSubqueryTest(String name) {
+		super(name);
+	}
+
+	/**
+	 * Main test body
+	 * 
+	 * @throws SQLException
+	 */
+	public void testBasicOperations()
+		throws SQLException {
+		Statement s = createStatement();
+
+		/*
+		 * Create tables needed for DERBY-3301 regression test
+		 */
+		StringBuffer sb = new StringBuffer();
+		sb.append("CREATE TABLE departments ( ");
+		sb.append("ID INTEGER NOT NULL, ");
+		sb.append("NAME VARCHAR(32) NOT NULL, ");
+		sb.append("COMPANYID INTEGER, ");
+		sb.append("CONSTRAINT DEPTS_PK PRIMARY KEY (ID) ");
+		sb.append(")");
+		s.executeUpdate(sb.toString());
+
+		sb = new StringBuffer();
+		sb.append("CREATE TABLE employees ( ");
+		sb.append("EMPID INTEGER NOT NULL, ");
+		sb.append("FIRSTNAME VARCHAR(32) NOT NULL, ");
+		sb.append("DEPARTMENT INTEGER, ");
+		sb.append("CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments, ");
+		sb.append("CONSTRAINT EMPS_PK PRIMARY KEY (EMPID) ");
+		sb.append(")");
+		s.executeUpdate(sb.toString());
+
+		sb = new StringBuffer();
+		sb.append("CREATE TABLE projects ( ");
+		sb.append("PROJID INTEGER NOT NULL, ");
+		sb.append("NAME VARCHAR(32) NOT NULL, ");
+		sb.append("CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) ");
+		sb.append(")");
+		s.executeUpdate(sb.toString());
+
+		sb = new StringBuffer();
+		sb.append("CREATE TABLE project_employees ( ");
+		sb.append("PROJID INTEGER REFERENCES projects NOT NULL, ");
+		sb.append("EMPID INTEGER REFERENCES employees NOT NULL ");
+		sb.append(")");
+		s.executeUpdate(sb.toString());
+
+		/*
+		 * Fill some data into the tables
+		 */
+		s.executeUpdate("INSERT INTO departments VALUES (1, 'Research', 1)");
+		s.executeUpdate("INSERT INTO departments VALUES (2, 'Marketing', 1)");
+
+		s.executeUpdate("INSERT INTO employees VALUES (11, 'Alex', 1)");
+		s.executeUpdate("INSERT INTO employees VALUES (12, 'Bill', 1)");
+		s.executeUpdate("INSERT INTO employees VALUES (13, 'Charles', 1)");
+		s.executeUpdate("INSERT INTO employees VALUES (14, 'David', 2)");
+		s.executeUpdate("INSERT INTO employees VALUES (15, 'Earl', 2)");
+
+		s.executeUpdate("INSERT INTO projects VALUES (101, 'red')");
+		s.executeUpdate("INSERT INTO projects VALUES (102, 'orange')");
+		s.executeUpdate("INSERT INTO projects VALUES (103, 'yellow')");
+
+		s.executeUpdate("INSERT INTO project_employees VALUES (102, 13)");
+		s.executeUpdate("INSERT INTO project_employees VALUES (101, 13)");
+		s.executeUpdate("INSERT INTO project_employees VALUES (102, 12)");
+		s.executeUpdate("INSERT INTO project_employees VALUES (103, 15)");
+		s.executeUpdate("INSERT INTO project_employees VALUES (103, 14)");
+		s.executeUpdate("INSERT INTO project_employees VALUES (101, 12)");
+		s.executeUpdate("INSERT INTO project_employees VALUES (101, 11)");
+
+		/*
+		 * Preliminary data check
+		 */
+		ResultSet rs = s.executeQuery("select * from employees");
+		String[][] expectedRows = {{"11", "Alex", "1"},
+									{"12", "Bill", "1"},
+									{"13", "Charles", "1"},
+									{"14", "David", "2"},
+									{"15", "Earl", "2"}};		
+		JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select * from departments");
+		expectedRows = new String [][] {{"1", "Research", "1"},
+										{"2","Marketing","1"}};		
+		JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select * from projects");
+		expectedRows = new String [][] {{"101","red"},
+										{"102","orange"},
+										{"103","yellow"}};		
+		JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select * from project_employees");
+		expectedRows = new String [][] {{"102","13"},
+										{"101","13"},
+										{"102","12"},
+										{"103","15"},
+										{"103","14"},
+										{"101","12"},
+										{"101","11"}};		
+		JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+		/*
+		 * DERBY-3301: This query should return 7 rows
+		 */
+		sb = new StringBuffer();
+		sb.append("select unbound_e.empid, unbound_p.projid ");
+		sb.append("from departments this, ");
+		sb.append("     employees unbound_e, ");
+		sb.append("     projects unbound_p ");
+		sb.append("where exists ( ");
+		sb.append("  select 1 from employees this_employees_e ");
+		sb.append("  where exists ( ");
+		sb.append("    select 1 from project_employees this_employees_e_projects_p ");
+		sb.append("    where this_employees_e_projects_p.empid = this_employees_e.empid ");
+		sb.append("    and this_employees_e.department = this.id ");
+		sb.append("    and unbound_p.projid = this_employees_e_projects_p.projid ");
+		sb.append("    and unbound_e.empid = this_employees_e.empid) ");
+		sb.append(" )");
+
+		rs = s.executeQuery(sb.toString());
+		expectedRows = new String [][] {{"13", "101"},
+										{"12", "101"},
+										{"11", "101"},
+										{"13", "102"},
+										{"12", "102"},
+										{"15", "103"},
+										{"14", "103"}};
+		JDBC.assertUnorderedResultSet(rs, expectedRows);
+		
+		/* A variation of the above WHERE EXISTS but using IN should return the same rows */
+		sb = new StringBuffer();
+		sb.append("select unbound_e.empid, unbound_p.projid ");
+		sb.append("from departments this, ");
+		sb.append("     employees unbound_e, ");
+		sb.append("     projects unbound_p ");
+		sb.append("where exists ( "); 
+		sb.append(" select 1 from employees this_employees_e ");
+		sb.append("     where this_employees_e.empid in ( ");
+		sb.append("         select this_employees_e_projects_p.empid ");
+		sb.append("           from project_employees this_employees_e_projects_p ");
+		sb.append("         where this_employees_e_projects_p.empid = this_employees_e.empid ");
+		sb.append("         and this_employees_e.department = this.id ");
+		sb.append("         and unbound_p.projid = this_employees_e_projects_p.projid ");
+		sb.append("         and unbound_e.empid = this_employees_e.empid) ");
+		sb.append("     )");
+
+		rs = s.executeQuery(sb.toString());
+		JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+		/*
+		 * Clean up the tables used.
+		 */				
+		s.executeUpdate("drop table project_employees");	
+		s.executeUpdate("drop table projects");
+		s.executeUpdate("drop table employees");
+		s.executeUpdate("drop table departments");			
+		
+		s.close();
+	}
+
+	public static Test suite() {
+		return TestConfiguration.defaultSuite(NestedWhereSubqueryTest.class);
+	}
+}
\ No newline at end of file

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.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=618586&r1=618585&r2=618586&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
Tue Feb  5 01:29:32 2008
@@ -122,6 +122,7 @@
         suite.addTest(Bug4356Test.suite());
         suite.addTest(SynonymTest.suite());
         suite.addTest(CommentTest.suite());
+        suite.addTest(NestedWhereSubqueryTest.suite());
 
         // Add the XML tests, which exist as a separate suite
         // so that users can "run all XML tests" easily.



Mime
View raw message