db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r524940 - 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 Mon, 02 Apr 2007 22:00:29 GMT
Author: abrown
Date: Mon Apr  2 15:00:28 2007
New Revision: 524940

URL: http://svn.apache.org/viewvc?view=rev&rev=524940
Log:
DERBY-1852: Fix "modification of access paths" code in TableOperatorNode
so that the final query tree accurately reflects (and generates) the
necessary modified nodes.  Patch also adds corresponding test cases
to lang/union.sql and updates master files accordingly.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?view=diff&rev=524940&r1=524939&r2=524940
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java Mon Apr  2 15:00:28 2007
@@ -171,13 +171,62 @@
 		ResultSetNode topNode = (ResultSetNode)modifyAccessPath(outerTables);
 
 		/* Now see if there are any left over predicates; if so, then we
-		 * have to generate a ProjectRestrictNode.  Note: we walk the
-		 * entire chain of UnionNodes (if there is a chain) and see if
-		 * any UnionNode at any level has un-pushed predicates; if so, then
-		 * we use a PRN to enforce the predicate at this, the top-most
-		 * UnionNode.
+		 * have to generate a ProjectRestrictNode.  Note: we want to check
+		 * all SetOpNodes that exist in the subtree rooted at this SetOpNode.
+		 * Since we just modified access paths on this node, it's possible
+		 * that the SetOperatorNode chain (if there was one) is now "broken"
+		 * as a result of the insertion of new nodes.  For example, prior
+		 * to modification of access paths we may have a chain such as:
+		 *
+		 *                          UnionNode (0)
+		 *                          /       \
+		 *                 UnionNode (1)    SelectNode (2)
+		 *                 /        \ 
+		 *      SelectNode (3)     SelectNode (4)
+		 *
+		 * Now if UnionNode(1) did not specify "ALL" then as part of the
+		 * above call to modifyAccessPaths() we will have inserted a
+		 * DistinctNode above it, thus giving:
+		 *
+		 *                          UnionNode (0)
+		 *                          /       \
+		 *                 DistinctNode (5)  SelectNode (2)
+		 *                      |
+		 *                 UnionNode (1)
+		 *                 /        \ 
+		 *      SelectNode (3)     SelectNode (4)
+		 *
+		 * So our chain of UnionNode's has now been "broken" by an intervening
+		 * DistinctNode.  For this reason we can't just walk the chain of
+		 * SetOperatorNodes looking for unpushed predicates (because the
+		 * chain might be broken and then we could miss some nodes). Instead,
+		 * we have to get a collection of all relevant nodes that exist beneath
+		 * this SetOpNode and call hasUnPushedPredicates() on each one.  For
+		 * now we only consider UnionNodes to be "relevant" because those are
+		 * the only ones that might actually have unpushed predicates.
+		 * 
+		 * If we find any UnionNodes that *do* have unpushed predicates then
+		 * we have to use a PRN to enforce the predicate at the level of
+		 * this, the top-most, SetOperatorNode.
 		 */
-		if (hasUnPushedPredicates())
+
+		// Find all UnionNodes in the subtree.
+		CollectNodesVisitor cnv = new CollectNodesVisitor(UnionNode.class);
+		this.accept(cnv);
+		java.util.Vector unions = cnv.getList();
+
+		// Now see if any of them have unpushed predicates.
+		boolean genPRN = false;
+		for (int i = unions.size() - 1; i >= 0; i--)
+		{
+			if (((UnionNode)unions.get(i)).hasUnPushedPredicates())
+			{
+				genPRN = true;
+				break;
+			}
+		}
+
+		if (genPRN)
 		{
 			// When we generate the project restrict node, we pass in the
 			// "pushedPredicates" list because that has the predicates in
@@ -451,34 +500,20 @@
 	 * children both satisfy the criteria for pushing a predicate
 	 * (namely, they reference base tables) but the children's
 	 * children do not (see modifyAccessPaths() above for an example
-	 * of how that can happen).  So this method will walk the chain
-	 * of nodes beneath this one and determine if any SetOperatorNode
-	 * at any level has predicates that were not successfully pushed
-	 * to both of its children (note: this currently only applies
-	 * to UnionNodes).
+	 * of how that can happen).  So this method determines whether
+	 * or not this particular SetOperatorNode has predicates which
+	 * were *not* successfully pushed to both of its children (note:
+	 * this currently only applies to UnionNodes).
 	 *
-	 * @return True if any UnionNode (or actually, any SetOperatorNode)
-	 *  in the chain of SetOperatorNodes (starting with this one) has
-	 *  unpushed predicates; false otherwise.
+	 * @return True if this SetOperatorNode has unpushed predicates;
+	 *  false otherwise.
 	 */
 	protected boolean hasUnPushedPredicates()
 	{
 		// Check this node.
-		if (((leftOptPredicates != null) && (leftOptPredicates.size() > 0)) ||
-			((rightOptPredicates != null) && (rightOptPredicates.size() > 0)))
-		{
-			return true;
-		}
-
-		// Now check the children.
-		if ((leftResultSet instanceof SetOperatorNode) &&
-			((SetOperatorNode)leftResultSet).hasUnPushedPredicates())
-		{
-			return true;
-		}
-
-		return ((rightResultSet instanceof SetOperatorNode) &&
-			((SetOperatorNode)rightResultSet).hasUnPushedPredicates());
+		return
+			((leftOptPredicates != null) && (leftOptPredicates.size() > 0)) ||
+			((rightOptPredicates != null) && (rightOptPredicates.size() > 0));
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java?view=diff&rev=524940&r1=524939&r2=524940
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java Mon Apr  2 15:00:28 2007
@@ -98,7 +98,27 @@
 		if (leftResultSet instanceof FromTable)
 		{
 			if (leftOptimizer != null)
+			{
+				/* We know leftOptimizer's list of Optimizables consists of
+				 * exactly one Optimizable, and we know that the Optimizable
+				 * is actually leftResultSet (see optimizeSource() of this
+				 * class). That said, the following call to modifyAccessPaths()
+				 * will effectively replace leftResultSet as it exists in
+				 * leftOptimizer's list with a "modified" node that *may* be
+				 * different from the original leftResultSet--for example, it
+				 * could be a new DISTINCT node whose child is the original
+				 * leftResultSet.  So after we've modified the node's access
+				 * path(s) we have to explicitly set this.leftResulSet to
+				 * point to the modified node. Otherwise leftResultSet would
+				 * continue to point to the node as it existed *before* it was
+				 * modified, and that could lead to incorrect behavior for
+				 * certain queries.  DERBY-1852.
+				 */
 				leftOptimizer.modifyAccessPaths();
+				leftResultSet = (ResultSetNode)
+					((OptimizerImpl)leftOptimizer)
+						.optimizableList.getOptimizable(0);
+			}
 			else
 			{
 				leftResultSet = 
@@ -115,7 +135,17 @@
 		if (rightResultSet instanceof FromTable)
 		{
 			if (rightOptimizer != null)
+			{
+				/* For the same reasons outlined above we need to make sure
+				 * we set rightResultSet to point to the *modified* right result
+				 * set node, which sits at position "0" in rightOptimizer's
+				 * list.
+				 */
 				rightOptimizer.modifyAccessPaths();
+				rightResultSet = (ResultSetNode)
+					((OptimizerImpl)rightOptimizer)
+						.optimizableList.getOptimizable(0);
+			}
 			else
 			{
 				rightResultSet = 
@@ -693,7 +723,27 @@
 		if (!leftModifyAccessPathsDone)
 		{
 			if (leftOptimizer != null)
+			{
+				/* We know leftOptimizer's list of Optimizables consists of
+				 * exactly one Optimizable, and we know that the Optimizable
+				 * is actually leftResultSet (see optimizeSource() of this
+				 * class). That said, the following call to modifyAccessPaths()
+				 * will effectively replace leftResultSet as it exists in
+				 * leftOptimizer's list with a "modified" node that *may* be
+				 * different from the original leftResultSet--for example, it
+				 * could be a new DISTINCT node whose child is the original
+				 * leftResultSet.  So after we've modified the node's access
+				 * path(s) we have to explicitly set this.leftResulSet to
+				 * point to the modified node. Otherwise leftResultSet would
+				 * continue to point to the node as it existed *before* it was
+				 * modified, and that could lead to incorrect behavior for
+				 * certain queries.  DERBY-1852.
+				 */
 				leftOptimizer.modifyAccessPaths();
+				leftResultSet = (ResultSetNode)
+					((OptimizerImpl)leftOptimizer)
+						.optimizableList.getOptimizable(0);
+			}
 			else
 			{
 				// If this is a SetOperatorNode then we may have pushed
@@ -714,7 +764,17 @@
 		if (!rightModifyAccessPathsDone)
 		{
 			if (rightOptimizer != null)
+			{
+				/* For the same reasons outlined above we need to make sure
+				 * we set rightResultSet to point to the *modified* right result
+				 * set node, which sits at position "0" in rightOptimizer's
+				 * list.
+				 */
 				rightOptimizer.modifyAccessPaths();
+				rightResultSet = (ResultSetNode)
+					((OptimizerImpl)rightOptimizer)
+						.optimizableList.getOptimizable(0);
+			}
 			else
 			{
 				if (this instanceof SetOperatorNode) {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out?view=diff&rev=524940&r1=524939&r2=524940
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out Mon Apr  2 15:00:28 2007
@@ -517,12 +517,12 @@
 Left result set:
 	Sort ResultSet:
 	Number of opens = 1
-	Rows input = 20
+	Rows input = 12
 	Rows returned = 7
 	Eliminate duplicates = true
 	In sorted order = false
 	Sort information: 
-		Number of rows input=20
+		Number of rows input=12
 		Number of rows output=7
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
@@ -531,57 +531,107 @@
 	Source result set:
 		Union ResultSet:
 		Number of opens = 1
-		Rows seen from the left = 15
+		Rows seen from the left = 7
 		Rows seen from the right = 5
-		Rows returned = 20
+		Rows returned = 12
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 		Left result set:
-			Union ResultSet:
+			Sort ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 10
-			Rows seen from the right = 5
-			Rows returned = 15
+			Rows input = 12
+			Rows returned = 7
+			Eliminate duplicates = true
+			In sorted order = false
+			Sort information: 
+				Number of rows input=12
+				Number of rows output=7
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			Left result set:
+			Source result set:
 				Union ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 5
+				Rows seen from the left = 7
 				Rows seen from the right = 5
-				Rows returned = 10
+				Rows returned = 12
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 				Left result set:
-					Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+					Sort ResultSet:
 					Number of opens = 1
-					Rows seen = 5
-					Rows filtered = 0
-					Fetch Size = 1
+					Rows input = 10
+					Rows returned = 7
+					Eliminate duplicates = true
+					In sorted order = false
+					Sort information: 
+						Number of rows input=10
+						Number of rows output=7
 						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=2
-						Number of pages visited=1
-						Number of rows qualified=5
-						Number of rows visited=5
-						Scan type=heap
-						start position: 
-null						stop position: 
-null						qualifiers:
+					Source result set:
+						Union ResultSet:
+						Number of opens = 1
+						Rows seen from the left = 5
+						Rows seen from the right = 5
+						Rows returned = 10
+							constructor time (milliseconds) = 0
+							open time (milliseconds) = 0
+							next time (milliseconds) = 0
+							close time (milliseconds) = 0
+						Left result set:
+							Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+							Number of opens = 1
+							Rows seen = 5
+							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=All
+								Number of columns fetched=2
+								Number of pages visited=1
+								Number of rows qualified=5
+								Number of rows visited=5
+								Scan type=heap
+								start position: 
+null								stop position: 
+null								qualifiers:
+None
+						Right result set:
+							Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+							Number of opens = 1
+							Rows seen = 5
+							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=All
+								Number of columns fetched=2
+								Number of pages visited=1
+								Number of rows qualified=5
+								Number of rows visited=5
+								Scan type=heap
+								start position: 
+null								stop position: 
+null								qualifiers:
 None
 				Right result set:
-					Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+					Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
 					Number of opens = 1
 					Rows seen = 5
 					Rows filtered = 0
@@ -602,28 +652,6 @@
 null						stop position: 
 null						qualifiers:
 None
-			Right result set:
-				Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 5
-				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=All
-					Number of columns fetched=2
-					Number of pages visited=1
-					Number of rows qualified=5
-					Number of rows visited=5
-					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
-None
 		Right result set:
 			Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
 			Number of opens = 1
@@ -647,7 +675,7 @@
 null				qualifiers:
 None
 Right result set:
-	Hash Table ResultSet (16):
+	Hash Table ResultSet (19):
 	Number of opens = 7
 	Hash table size = 19
 	Hash key is column number 0
@@ -667,12 +695,12 @@
 	Source result set:
 		Sort ResultSet:
 		Number of opens = 1
-		Rows input = 43
+		Rows input = 33
 		Rows returned = 21
 		Eliminate duplicates = true
 		In sorted order = false
 		Sort information: 
-			Number of rows input=43
+			Number of rows input=33
 			Number of rows output=21
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
@@ -681,66 +709,80 @@
 		Source result set:
 			Union ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 31
+			Rows seen from the left = 21
 			Rows seen from the right = 12
-			Rows returned = 43
+			Rows returned = 33
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 			Left result set:
-				Union ResultSet:
+				Sort ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 19
-				Rows seen from the right = 12
-				Rows returned = 31
+				Rows input = 31
+				Rows returned = 21
+				Eliminate duplicates = true
+				In sorted order = false
+				Sort information: 
+					Number of rows input=31
+					Number of rows output=21
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
-				Left result set:
-					Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer
+				Source result set:
+					Union ResultSet:
 					Number of opens = 1
-					Rows seen = 19
-					Rows filtered = 0
-					Fetch Size = 1
+					Rows seen from the left = 19
+					Rows seen from the right = 12
+					Rows returned = 31
 						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=2
-						Number of pages visited=1
-						Number of rows qualified=19
-						Number of rows visited=19
-						Scan type=heap
-						start position: 
-null						stop position: 
-null						qualifiers:
+					Left result set:
+						Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer
+						Number of opens = 1
+						Rows seen = 19
+						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=All
+							Number of columns fetched=2
+							Number of pages visited=1
+							Number of rows qualified=19
+							Number of rows visited=19
+							Scan type=heap
+							start position: 
+null							stop position: 
+null							qualifiers:
 None
-				Right result set:
-					Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer
-					Number of opens = 1
-					Rows seen = 12
-					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=All
-						Number of columns fetched=2
-						Number of pages visited=1
-						Number of rows qualified=12
-						Number of rows visited=12
-						Scan type=heap
-						start position: 
-null						stop position: 
-null						qualifiers:
+					Right result set:
+						Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer
+						Number of opens = 1
+						Rows seen = 12
+						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=All
+							Number of columns fetched=2
+							Number of pages visited=1
+							Number of rows qualified=12
+							Number of rows visited=12
+							Scan type=heap
+							start position: 
+null							stop position: 
+null							qualifiers:
 None
 			Right result set:
 				Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer
@@ -4803,12 +4845,12 @@
 Left result set:
 	Sort ResultSet:
 	Number of opens = 1
-	Rows input = 20
+	Rows input = 12
 	Rows returned = 7
 	Eliminate duplicates = true
 	In sorted order = false
 	Sort information: 
-		Number of rows input=20
+		Number of rows input=12
 		Number of rows output=7
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
@@ -4817,57 +4859,107 @@
 	Source result set:
 		Union ResultSet:
 		Number of opens = 1
-		Rows seen from the left = 15
+		Rows seen from the left = 7
 		Rows seen from the right = 5
-		Rows returned = 20
+		Rows returned = 12
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 		Left result set:
-			Union ResultSet:
+			Sort ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 10
-			Rows seen from the right = 5
-			Rows returned = 15
+			Rows input = 12
+			Rows returned = 7
+			Eliminate duplicates = true
+			In sorted order = false
+			Sort information: 
+				Number of rows input=12
+				Number of rows output=7
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			Left result set:
+			Source result set:
 				Union ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 5
+				Rows seen from the left = 7
 				Rows seen from the right = 5
-				Rows returned = 10
+				Rows returned = 12
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 				Left result set:
-					Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+					Sort ResultSet:
 					Number of opens = 1
-					Rows seen = 5
-					Rows filtered = 0
-					Fetch Size = 1
+					Rows input = 10
+					Rows returned = 7
+					Eliminate duplicates = true
+					In sorted order = false
+					Sort information: 
+						Number of rows input=10
+						Number of rows output=7
 						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=2
-						Number of pages visited=1
-						Number of rows qualified=5
-						Number of rows visited=5
-						Scan type=heap
-						start position: 
-null						stop position: 
-null						qualifiers:
+					Source result set:
+						Union ResultSet:
+						Number of opens = 1
+						Rows seen from the left = 5
+						Rows seen from the right = 5
+						Rows returned = 10
+							constructor time (milliseconds) = 0
+							open time (milliseconds) = 0
+							next time (milliseconds) = 0
+							close time (milliseconds) = 0
+						Left result set:
+							Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+							Number of opens = 1
+							Rows seen = 5
+							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=All
+								Number of columns fetched=2
+								Number of pages visited=1
+								Number of rows qualified=5
+								Number of rows visited=5
+								Scan type=heap
+								start position: 
+null								stop position: 
+null								qualifiers:
+None
+						Right result set:
+							Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+							Number of opens = 1
+							Rows seen = 5
+							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=All
+								Number of columns fetched=2
+								Number of pages visited=1
+								Number of rows qualified=5
+								Number of rows visited=5
+								Scan type=heap
+								start position: 
+null								stop position: 
+null								qualifiers:
 None
 				Right result set:
-					Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+					Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
 					Number of opens = 1
 					Rows seen = 5
 					Rows filtered = 0
@@ -4888,28 +4980,6 @@
 null						stop position: 
 null						qualifiers:
 None
-			Right result set:
-				Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 5
-				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=All
-					Number of columns fetched=2
-					Number of pages visited=1
-					Number of rows qualified=5
-					Number of rows visited=5
-					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
-None
 		Right result set:
 			Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
 			Number of opens = 1
@@ -4957,86 +5027,100 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 		Left result set:
-			Union ResultSet:
+			Sort ResultSet:
 			Number of opens = 7
-			Rows seen from the left = 6
-			Rows seen from the right = 3
+			Rows input = 9
 			Rows returned = 9
+			Eliminate duplicates = true
+			In sorted order = false
+			Sort information: 
+				Number of rows input=0
+				Number of rows output=0
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			Left result set:
-				Index Row to Base Row ResultSet for T3:
+			Source result set:
+				Union ResultSet:
 				Number of opens = 7
-				Rows seen = 6
-				Columns accessed from heap = {1}
+				Rows seen from the left = 6
+				Rows seen from the right = 3
+				Rows returned = 9
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
-					Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer
+				Left result set:
+					Index Row to Base Row ResultSet for T3:
 					Number of opens = 7
 					Rows seen = 6
-					Rows filtered = 0
-					Fetch Size = 1
+					Columns accessed from heap = {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=2
-						Number of deleted rows visited=0
-						Number of pages visited=3
-						Number of rows qualified=0
-						Number of rows visited=1
-						Scan type=btree
-						Tree height=3
-						start position: 
+						Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer
+						Number of opens = 7
+						Rows seen = 6
+						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=All
+							Number of columns fetched=2
+							Number of deleted rows visited=0
+							Number of pages visited=3
+							Number of rows qualified=0
+							Number of rows visited=1
+							Scan type=btree
+							Tree height=3
+							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
-			Right result set:
-				Index Row to Base Row ResultSet for T4:
-				Number of opens = 7
-				Rows seen = 3
-				Columns accessed from heap = {1}
-					constructor time (milliseconds) = 0
-					open time (milliseconds) = 0
-					next time (milliseconds) = 0
-					close time (milliseconds) = 0
-					Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer
+				Right result set:
+					Index Row to Base Row ResultSet for T4:
 					Number of opens = 7
 					Rows seen = 3
-					Rows filtered = 0
-					Fetch Size = 1
+					Columns accessed from heap = {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=2
-						Number of deleted rows visited=0
-						Number of pages visited=3
-						Number of rows qualified=0
-						Number of rows visited=1
-						Scan type=btree
-						Tree height=3
-						start position: 
+						Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer
+						Number of opens = 7
+						Rows seen = 3
+						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=All
+							Number of columns fetched=2
+							Number of deleted rows visited=0
+							Number of pages visited=3
+							Number of rows qualified=0
+							Number of rows visited=1
+							Scan type=btree
+							Tree height=3
+							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
 		Right result set:
 			Index Row to Base Row ResultSet for T4:
@@ -5120,7 +5204,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (9):
+Project-Restrict ResultSet (11):
 Number of opens = 1
 Rows seen = 7
 Rows filtered = 0
@@ -5135,12 +5219,12 @@
 Source result set:
 	Sort ResultSet:
 	Number of opens = 1
-	Rows input = 20
+	Rows input = 12
 	Rows returned = 7
 	Eliminate duplicates = true
 	In sorted order = false
 	Sort information: 
-		Number of rows input=20
+		Number of rows input=12
 		Number of rows output=7
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
@@ -5149,61 +5233,115 @@
 	Source result set:
 		Union ResultSet:
 		Number of opens = 1
-		Rows seen from the left = 15
+		Rows seen from the left = 7
 		Rows seen from the right = 5
-		Rows returned = 20
+		Rows returned = 12
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 		Left result set:
-			Union ResultSet:
+			Sort ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 10
-			Rows seen from the right = 5
-			Rows returned = 15
+			Rows input = 12
+			Rows returned = 7
+			Eliminate duplicates = true
+			In sorted order = false
+			Sort information: 
+				Number of rows input=12
+				Number of rows output=7
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			Left result set:
+			Source result set:
 				Union ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 5
+				Rows seen from the left = 7
 				Rows seen from the right = 5
-				Rows returned = 10
+				Rows returned = 12
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 				Left result set:
-					Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+					Sort ResultSet:
 					Number of opens = 1
-					Rows seen = 5
-					Rows filtered = 0
-					Fetch Size = 1
+					Rows input = 10
+					Rows returned = 7
+					Eliminate duplicates = true
+					In sorted order = false
+					Sort information: 
+						Number of rows input=10
+						Number of rows output=7
 						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=2
-						Number of pages visited=1
-						Number of rows qualified=5
-						Number of rows visited=5
-						Scan type=heap
-						start position: 
-null						stop position: 
-null						qualifiers:
+					Source result set:
+						Union ResultSet:
+						Number of opens = 1
+						Rows seen from the left = 5
+						Rows seen from the right = 5
+						Rows returned = 10
+							constructor time (milliseconds) = 0
+							open time (milliseconds) = 0
+							next time (milliseconds) = 0
+							close time (milliseconds) = 0
+						Left result set:
+							Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+							Number of opens = 1
+							Rows seen = 5
+							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=All
+								Number of columns fetched=2
+								Number of pages visited=1
+								Number of rows qualified=5
+								Number of rows visited=5
+								Scan type=heap
+								start position: 
+null								stop position: 
+null								qualifiers:
+Column[0][0] Id: 0
+Operator: <=
+Ordered nulls: false
+Unknown return value: true
+Negate comparison result: true
+						Right result set:
+							Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+							Number of opens = 1
+							Rows seen = 5
+							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=All
+								Number of columns fetched=2
+								Number of pages visited=1
+								Number of rows qualified=5
+								Number of rows visited=5
+								Scan type=heap
+								start position: 
+null								stop position: 
+null								qualifiers:
 Column[0][0] Id: 0
 Operator: <=
 Ordered nulls: false
 Unknown return value: true
 Negate comparison result: true
 				Right result set:
-					Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+					Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
 					Number of opens = 1
 					Rows seen = 5
 					Rows filtered = 0
@@ -5228,32 +5366,6 @@
 Ordered nulls: false
 Unknown return value: true
 Negate comparison result: true
-			Right result set:
-				Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 5
-				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=All
-					Number of columns fetched=2
-					Number of pages visited=1
-					Number of rows qualified=5
-					Number of rows visited=5
-					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
-Column[0][0] Id: 0
-Operator: <=
-Ordered nulls: false
-Unknown return value: true
-Negate comparison result: true
 		Right result set:
 			Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
 			Number of opens = 1
@@ -5318,7 +5430,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (12):
+Project-Restrict ResultSet (14):
 Number of opens = 1
 Rows seen = 1
 Rows filtered = 0
@@ -5340,7 +5452,7 @@
 		close time (milliseconds) = 0
 	Index Key Optimization = false
 	Source result set:
-		Project-Restrict ResultSet (11):
+		Project-Restrict ResultSet (13):
 		Number of opens = 1
 		Rows seen = 108
 		Rows filtered = 0
@@ -5353,7 +5465,7 @@
 			restriction time (milliseconds) = 0
 			projection time (milliseconds) = 0
 		Source result set:
-			Project-Restrict ResultSet (10):
+			Project-Restrict ResultSet (12):
 			Number of opens = 1
 			Rows seen = 108
 			Rows filtered = 0
@@ -5368,12 +5480,12 @@
 			Source result set:
 				Sort ResultSet:
 				Number of opens = 1
-				Rows input = 103401
+				Rows input = 48918
 				Rows returned = 108
 				Eliminate duplicates = true
 				In sorted order = false
 				Sort information: 
-					Number of rows input=103401
+					Number of rows input=48918
 					Number of rows output=108
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
@@ -5382,59 +5494,109 @@
 				Source result set:
 					Union ResultSet:
 					Number of opens = 1
-					Rows seen from the left = 54589
+					Rows seen from the left = 106
 					Rows seen from the right = 48812
-					Rows returned = 103401
+					Rows returned = 48918
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 					Left result set:
-						Union ResultSet:
+						Sort ResultSet:
 						Number of opens = 1
-						Rows seen from the left = 10
-						Rows seen from the right = 54579
-						Rows returned = 54589
+						Rows input = 54586
+						Rows returned = 106
+						Eliminate duplicates = true
+						In sorted order = false
+						Sort information: 
+							Number of rows input=54586
+							Number of rows output=106
 							constructor time (milliseconds) = 0
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
-						Left result set:
+						Source result set:
 							Union ResultSet:
 							Number of opens = 1
-							Rows seen from the left = 5
-							Rows seen from the right = 5
-							Rows returned = 10
+							Rows seen from the left = 7
+							Rows seen from the right = 54579
+							Rows returned = 54586
 								constructor time (milliseconds) = 0
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
 							Left result set:
-								Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+								Sort ResultSet:
 								Number of opens = 1
-								Rows seen = 5
-								Rows filtered = 0
-								Fetch Size = 1
+								Rows input = 10
+								Rows returned = 7
+								Eliminate duplicates = true
+								In sorted order = false
+								Sort information: 
+									Number of rows input=10
+									Number of rows output=7
 									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=2
-									Number of pages visited=1
-									Number of rows qualified=5
-									Number of rows visited=5
-									Scan type=heap
-									start position: 
-null									stop position: 
-null									qualifiers:
+								Source result set:
+									Union ResultSet:
+									Number of opens = 1
+									Rows seen from the left = 5
+									Rows seen from the right = 5
+									Rows returned = 10
+										constructor time (milliseconds) = 0
+										open time (milliseconds) = 0
+										next time (milliseconds) = 0
+										close time (milliseconds) = 0
+									Left result set:
+										Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
+										Number of opens = 1
+										Rows seen = 5
+										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=All
+											Number of columns fetched=2
+											Number of pages visited=1
+											Number of rows qualified=5
+											Number of rows visited=5
+											Scan type=heap
+											start position: 
+null											stop position: 
+null											qualifiers:
+None
+									Right result set:
+										Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+										Number of opens = 1
+										Rows seen = 5
+										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=All
+											Number of columns fetched=2
+											Number of pages visited=1
+											Number of rows qualified=5
+											Number of rows visited=5
+											Scan type=heap
+											start position: 
+null											stop position: 
+null											qualifiers:
 None
 							Right result set:
-								Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+								Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer
 								Number of opens = 1
-								Rows seen = 5
+								Rows seen = 54579
 								Rows filtered = 0
 								Fetch Size = 1
 									constructor time (milliseconds) = 0
@@ -5445,36 +5607,14 @@
 								scan information: 
 									Bit set of columns fetched=All
 									Number of columns fetched=2
-									Number of pages visited=1
-									Number of rows qualified=5
-									Number of rows visited=5
+									Number of pages visited=434
+									Number of rows qualified=54579
+									Number of rows visited=54579
 									Scan type=heap
 									start position: 
 null									stop position: 
 null									qualifiers:
 None
-						Right result set:
-							Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer
-							Number of opens = 1
-							Rows seen = 54579
-							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=All
-								Number of columns fetched=2
-								Number of pages visited=434
-								Number of rows qualified=54579
-								Number of rows visited=54579
-								Scan type=heap
-								start position: 
-null								stop position: 
-null								qualifiers:
-None
 					Right result set:
 						Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer
 						Number of opens = 1
@@ -5790,53 +5930,57 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 		Left result set:
-			Union ResultSet:
+			Sort ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 5
-			Rows seen from the right = 4
+			Rows input = 9
 			Rows returned = 9
+			Eliminate duplicates = true
+			In sorted order = false
+			Sort information: 
+				Number of rows input=9
+				Number of rows output=9
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			Left result set:
-				Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 5
-				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=All
-					Number of columns fetched=2
-					Number of pages visited=1
-					Number of rows qualified=5
-					Number of rows visited=5
-					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
-None
-			Right result set:
+			Source result set:
 				Union ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 3
-				Rows seen from the right = 1
-				Rows returned = 4
+				Rows seen from the left = 5
+				Rows seen from the right = 4
+				Rows returned = 9
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 				Left result set:
+					Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+					Number of opens = 1
+					Rows seen = 5
+					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=All
+						Number of columns fetched=2
+						Number of pages visited=1
+						Number of rows qualified=5
+						Number of rows visited=5
+						Scan type=heap
+						start position: 
+null						stop position: 
+null						qualifiers:
+None
+				Right result set:
 					Union ResultSet:
 					Number of opens = 1
-					Rows seen from the left = 2
+					Rows seen from the left = 3
 					Rows seen from the right = 1
-					Rows returned = 3
+					Rows returned = 4
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
 						next time (milliseconds) = 0
@@ -5844,21 +5988,39 @@
 					Left result set:
 						Union ResultSet:
 						Number of opens = 1
-						Rows seen from the left = 1
+						Rows seen from the left = 2
 						Rows seen from the right = 1
-						Rows returned = 2
+						Rows returned = 3
 							constructor time (milliseconds) = 0
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 						Left result set:
-							Row ResultSet:
+							Union ResultSet:
 							Number of opens = 1
-							Rows returned = 1
+							Rows seen from the left = 1
+							Rows seen from the right = 1
+							Rows returned = 2
 								constructor time (milliseconds) = 0
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
+							Left result set:
+								Row ResultSet:
+								Number of opens = 1
+								Rows returned = 1
+									constructor time (milliseconds) = 0
+									open time (milliseconds) = 0
+									next time (milliseconds) = 0
+									close time (milliseconds) = 0
+							Right result set:
+								Row ResultSet:
+								Number of opens = 1
+								Rows returned = 1
+									constructor time (milliseconds) = 0
+									open time (milliseconds) = 0
+									next time (milliseconds) = 0
+									close time (milliseconds) = 0
 						Right result set:
 							Row ResultSet:
 							Number of opens = 1
@@ -5875,14 +6037,6 @@
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
-				Right result set:
-					Row ResultSet:
-					Number of opens = 1
-					Rows returned = 1
-						constructor time (milliseconds) = 0
-						open time (milliseconds) = 0
-						next time (milliseconds) = 0
-						close time (milliseconds) = 0
 		Right result set:
 			Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
 			Number of opens = 1
@@ -6305,53 +6459,57 @@
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
 		Left result set:
-			Union ResultSet:
+			Sort ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 5
-			Rows seen from the right = 4
+			Rows input = 9
 			Rows returned = 9
+			Eliminate duplicates = true
+			In sorted order = false
+			Sort information: 
+				Number of rows input=9
+				Number of rows output=9
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-			Left result set:
-				Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 5
-				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=All
-					Number of columns fetched=2
-					Number of pages visited=1
-					Number of rows qualified=5
-					Number of rows visited=5
-					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
-None
-			Right result set:
+			Source result set:
 				Union ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 3
-				Rows seen from the right = 1
-				Rows returned = 4
+				Rows seen from the left = 5
+				Rows seen from the right = 4
+				Rows returned = 9
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
 				Left result set:
+					Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer
+					Number of opens = 1
+					Rows seen = 5
+					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=All
+						Number of columns fetched=2
+						Number of pages visited=1
+						Number of rows qualified=5
+						Number of rows visited=5
+						Scan type=heap
+						start position: 
+null						stop position: 
+null						qualifiers:
+None
+				Right result set:
 					Union ResultSet:
 					Number of opens = 1
-					Rows seen from the left = 2
+					Rows seen from the left = 3
 					Rows seen from the right = 1
-					Rows returned = 3
+					Rows returned = 4
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
 						next time (milliseconds) = 0
@@ -6359,21 +6517,39 @@
 					Left result set:
 						Union ResultSet:
 						Number of opens = 1
-						Rows seen from the left = 1
+						Rows seen from the left = 2
 						Rows seen from the right = 1
-						Rows returned = 2
+						Rows returned = 3
 							constructor time (milliseconds) = 0
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 						Left result set:
-							Row ResultSet:
+							Union ResultSet:
 							Number of opens = 1
-							Rows returned = 1
+							Rows seen from the left = 1
+							Rows seen from the right = 1
+							Rows returned = 2
 								constructor time (milliseconds) = 0
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
+							Left result set:
+								Row ResultSet:
+								Number of opens = 1
+								Rows returned = 1
+									constructor time (milliseconds) = 0
+									open time (milliseconds) = 0
+									next time (milliseconds) = 0
+									close time (milliseconds) = 0
+							Right result set:
+								Row ResultSet:
+								Number of opens = 1
+								Rows returned = 1
+									constructor time (milliseconds) = 0
+									open time (milliseconds) = 0
+									next time (milliseconds) = 0
+									close time (milliseconds) = 0
 						Right result set:
 							Row ResultSet:
 							Number of opens = 1
@@ -6390,14 +6566,6 @@
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
-				Right result set:
-					Row ResultSet:
-					Number of opens = 1
-					Rows returned = 1
-						constructor time (milliseconds) = 0
-						open time (milliseconds) = 0
-						next time (milliseconds) = 0
-						close time (milliseconds) = 0
 		Right result set:
 			Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
 			Number of opens = 1
@@ -6421,7 +6589,7 @@
 null				qualifiers:
 None
 Right result set:
-	Project-Restrict ResultSet (25):
+	Project-Restrict ResultSet (27):
 	Number of opens = 11
 	Rows seen = 1127
 	Rows filtered = 1108
@@ -6436,12 +6604,12 @@
 	Source result set:
 		Sort ResultSet:
 		Number of opens = 11
-		Rows input = 600407
+		Rows input = 1127
 		Rows returned = 1127
 		Eliminate duplicates = true
 		In sorted order = false
 		Sort information: 
-			Number of rows input=54582
+			Number of rows input=102
 			Number of rows output=102
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
@@ -6450,73 +6618,95 @@
 		Source result set:
 			Union ResultSet:
 			Number of opens = 11
-			Rows seen from the left = 600402
+			Rows seen from the left = 1122
 			Rows seen from the right = 5
-			Rows returned = 600407
+			Rows returned = 1127
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 			Left result set:
-				Union ResultSet:
+				Sort ResultSet:
 				Number of opens = 11
-				Rows seen from the left = 600369
-				Rows seen from the right = 33
-				Rows returned = 600402
+				Rows input = 600402
+				Rows returned = 1122
+				Eliminate duplicates = true
+				In sorted order = false
+				Sort information: 
+					Number of rows input=54582
+					Number of rows output=102
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
-				Left result set:
-					Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer
-					Number of opens = 11
-					Rows seen = 600369
-					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=All
-						Number of columns fetched=2
-						Number of pages visited=434
-						Number of rows qualified=54579
-						Number of rows visited=54579
-						Scan type=heap
-						start position: 
-null						stop position: 
-null						qualifiers:
-None
-				Right result set:
+				Source result set:
 					Union ResultSet:
 					Number of opens = 11
-					Rows seen from the left = 22
-					Rows seen from the right = 11
-					Rows returned = 33
+					Rows seen from the left = 600369
+					Rows seen from the right = 33
+					Rows returned = 600402
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 					Left result set:
+						Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer
+						Number of opens = 11
+						Rows seen = 600369
+						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=All
+							Number of columns fetched=2
+							Number of pages visited=434
+							Number of rows qualified=54579
+							Number of rows visited=54579
+							Scan type=heap
+							start position: 
+null							stop position: 
+null							qualifiers:
+None
+					Right result set:
 						Union ResultSet:
 						Number of opens = 11
-						Rows seen from the left = 11
+						Rows seen from the left = 22
 						Rows seen from the right = 11
-						Rows returned = 22
+						Rows returned = 33
 							constructor time (milliseconds) = 0
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
 						Left result set:
-							Row ResultSet:
+							Union ResultSet:
 							Number of opens = 11
-							Rows returned = 11
+							Rows seen from the left = 11
+							Rows seen from the right = 11
+							Rows returned = 22
 								constructor time (milliseconds) = 0
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
+							Left result set:
+								Row ResultSet:
+								Number of opens = 11
+								Rows returned = 11
+									constructor time (milliseconds) = 0
+									open time (milliseconds) = 0
+									next time (milliseconds) = 0
+									close time (milliseconds) = 0
+							Right result set:
+								Row ResultSet:
+								Number of opens = 11
+								Rows returned = 11
+									constructor time (milliseconds) = 0
+									open time (milliseconds) = 0
+									next time (milliseconds) = 0
+									close time (milliseconds) = 0
 						Right result set:
 							Row ResultSet:
 							Number of opens = 11
@@ -6525,14 +6715,6 @@
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
-					Right result set:
-						Row ResultSet:
-						Number of opens = 11
-						Rows returned = 11
-							constructor time (milliseconds) = 0
-							open time (milliseconds) = 0
-							next time (milliseconds) = 0
-							close time (milliseconds) = 0
 			Right result set:
 				Index Row to Base Row ResultSet for T4:
 				Number of opens = 11
@@ -6615,7 +6797,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (17):
+Project-Restrict ResultSet (18):
 Number of opens = 1
 Rows seen = 5
 Rows filtered = 0
@@ -6641,12 +6823,12 @@
 	Left result set:
 		Sort ResultSet:
 		Number of opens = 1
-		Rows input = 11
+		Rows input = 9
 		Rows returned = 9
 		Eliminate duplicates = true
 		In sorted order = false
 		Sort information: 
-			Number of rows input=11
+			Number of rows input=9
 			Number of rows output=9
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
@@ -6655,65 +6837,69 @@
 		Source result set:
 			Union ResultSet:
 			Number of opens = 1
-			Rows seen from the left = 6
+			Rows seen from the left = 4
 			Rows seen from the right = 5
-			Rows returned = 11
+			Rows returned = 9
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
 			Left result set:
-				Union ResultSet:
+				Sort ResultSet:
 				Number of opens = 1
-				Rows seen from the left = 2
-				Rows seen from the right = 4
-				Rows returned = 6
+				Rows input = 6
+				Rows returned = 4
+				Eliminate duplicates = true
+				In sorted order = false
+				Sort information: 
+					Number of rows input=6
+					Number of rows output=4
 					constructor time (milliseconds) = 0
 					open time (milliseconds) = 0
 					next time (milliseconds) = 0
 					close time (milliseconds) = 0
-				Left result set:
+				Source result set:
 					Union ResultSet:
 					Number of opens = 1
-					Rows seen from the left = 1
-					Rows seen from the right = 1
-					Rows returned = 2
+					Rows seen from the left = 2
+					Rows seen from the right = 4
+					Rows returned = 6
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 					Left result set:
-						Row ResultSet:
+						Union ResultSet:
 						Number of opens = 1
-						Rows returned = 1
+						Rows seen from the left = 1
+						Rows seen from the right = 1
+						Rows returned = 2
 							constructor time (milliseconds) = 0
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
 							close time (milliseconds) = 0
+						Left result set:
+							Row ResultSet:
+							Number of opens = 1
+							Rows returned = 1
+								constructor time (milliseconds) = 0
+								open time (milliseconds) = 0
+								next time (milliseconds) = 0
+								close time (milliseconds) = 0
+						Right result set:
+							Row ResultSet:
+							Number of opens = 1
+							Rows returned = 1
+								constructor time (milliseconds) = 0
+								open time (milliseconds) = 0
+								next time (milliseconds) = 0
+								close time (milliseconds) = 0
 					Right result set:
-						Row ResultSet:
-						Number of opens = 1
-						Rows returned = 1
-							constructor time (milliseconds) = 0
-							open time (milliseconds) = 0
-							next time (milliseconds) = 0
-							close time (milliseconds) = 0
-				Right result set:
-					Union ResultSet:
-					Number of opens = 1
-					Rows seen from the left = 3
-					Rows seen from the right = 1
-					Rows returned = 4
-						constructor time (milliseconds) = 0
-						open time (milliseconds) = 0
-						next time (milliseconds) = 0
-						close time (milliseconds) = 0
-					Left result set:
 						Union ResultSet:
 						Number of opens = 1
-						Rows seen from the left = 2
+						Rows seen from the left = 3
 						Rows seen from the right = 1
-						Rows returned = 3
+						Rows returned = 4
 							constructor time (milliseconds) = 0
 							open time (milliseconds) = 0
 							next time (milliseconds) = 0
@@ -6721,21 +6907,39 @@
 						Left result set:
 							Union ResultSet:
 							Number of opens = 1
-							Rows seen from the left = 1
+							Rows seen from the left = 2
 							Rows seen from the right = 1
-							Rows returned = 2
+							Rows returned = 3
 								constructor time (milliseconds) = 0
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
 							Left result set:
-								Row ResultSet:
+								Union ResultSet:
 								Number of opens = 1
-								Rows returned = 1
+								Rows seen from the left = 1
+								Rows seen from the right = 1
+								Rows returned = 2
 									constructor time (milliseconds) = 0
 									open time (milliseconds) = 0
 									next time (milliseconds) = 0
 									close time (milliseconds) = 0
+								Left result set:
+									Row ResultSet:
+									Number of opens = 1
+									Rows returned = 1
+										constructor time (milliseconds) = 0
+										open time (milliseconds) = 0
+										next time (milliseconds) = 0
+										close time (milliseconds) = 0
+								Right result set:
+									Row ResultSet:
+									Number of opens = 1
+									Rows returned = 1
+										constructor time (milliseconds) = 0
+										open time (milliseconds) = 0
+										next time (milliseconds) = 0
+										close time (milliseconds) = 0
 							Right result set:
 								Row ResultSet:
 								Number of opens = 1
@@ -6752,14 +6956,6 @@
 								open time (milliseconds) = 0
 								next time (milliseconds) = 0
 								close time (milliseconds) = 0
-					Right result set:
-						Row ResultSet:
-						Number of opens = 1
-						Rows returned = 1
-							constructor time (milliseconds) = 0
-							open time (milliseconds) = 0
-							next time (milliseconds) = 0
-							close time (milliseconds) = 0
 			Right result set:
 				Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer
 				Number of opens = 1

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out?view=diff&rev=524940&r1=524939&r2=524940
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out Mon Apr  2 15:00:28 2007
@@ -990,4 +990,211 @@
 0 rows inserted/updated/deleted
 ij> drop table a;
 0 rows inserted/updated/deleted
+ij> -- DERBY-1852: Incorrect results when a UNION U1 (with no "ALL") appears
+-- in the FROM list of a SELECT query, AND there are duplicate rows
+-- across the left and/or right result sets of U1, AND U1 is the left or
+-- right child of another set operator.
+
+create table t1 (i int, j int);
+0 rows inserted/updated/deleted
+ij> create table t2 (i int, j int);
+0 rows inserted/updated/deleted
+ij> insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10);
+5 rows inserted/updated/deleted
+ij> insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10);
+5 rows inserted/updated/deleted
+ij> insert into t2 values (3, 6), (4, 8), (3, -6), (4, -8);
+4 rows inserted/updated/deleted
+ij> -- U1 is left child of another UNION; top-level query.
+select * from t1 union select * from t2 union select * from t1;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+2          |4          
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> -- U1 is left child of another UNION; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 union select * from t1) x;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+2          |4          
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> -- Same kind of thing, but in the form of a view (which is a
+-- more likely use-ccase).
+create view uv as
+  select * from t1 union select * from t2 union select * from t1;
+0 rows inserted/updated/deleted
+ij> select * from uv;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+2          |4          
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> drop view uv;
+0 rows inserted/updated/deleted
+ij> -- U1 is left child of a UNION ALL; top-level query.
+select * from t1 union select * from t2 union all select * from t1;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+2          |4          
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+1          |2          
+2          |4          
+3          |6          
+4          |8          
+5          |10         
+ij> -- U1 is left child of a UNION ALL; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 union all select * from t1) x;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+2          |4          
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+1          |2          
+2          |4          
+3          |6          
+4          |8          
+5          |10         
+ij> -- U1 is left child of an EXCEPT; top-level query.
+select * from t1 union select * from t2 except select * from t1;
+I          |J          
+-----------------------
+2          |-4         
+3          |-6         
+4          |-8         
+ij> -- U1 is left child of an EXCEPT; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 except select * from t1) x;
+I          |J          
+-----------------------
+2          |-4         
+3          |-6         
+4          |-8         
+ij> -- U1 is left child of an EXCEPT ALL; top-level query.
+select * from t1 union select * from t2 except all select * from t1;
+I          |J          
+-----------------------
+2          |-4         
+3          |-6         
+4          |-8         
+ij> -- U1 is left child of an EXCEPT ALL; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 except all select * from t1) x;
+I          |J          
+-----------------------
+2          |-4         
+3          |-6         
+4          |-8         
+ij> -- U1 is left child of an INTERSECT; top-level query.
+-- Note: intersect has higher precedence than union so we have to use
+-- quotes to force the UNION to be a child of the intersect.
+(select * from t1 union select * from t2) intersect select * from t2;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> -- U1 is left child of an INTERSECT; subquery in FROM list.
+create view iv as
+  (select * from t1 union select * from t2) intersect select * from t2;
+0 rows inserted/updated/deleted
+ij> select * from iv;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> drop view iv;
+0 rows inserted/updated/deleted
+ij> -- U1 is left child of an INTERSECT ALL; top-level query.
+(select * from t1 union select * from t2) intersect all select * from t2;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> -- U1 is left child of an INTERSECT ALL; subquery in FROM list.
+create view iv as
+  (select * from t1 union select * from t2) intersect all select * from t2;
+0 rows inserted/updated/deleted
+ij> select * from iv;
+I          |J          
+-----------------------
+1          |2          
+2          |-4         
+3          |-6         
+3          |6          
+4          |-8         
+4          |8          
+5          |10         
+ij> drop view iv;
+0 rows inserted/updated/deleted
+ij> -- Just as a sanity check, make sure things work if U1 is a child of
+-- an explicit JoinNode (since JoinNode is an instanceof TableOperatorNode
+-- and TableOperatorNode is where the bug for DERBY-1852 was fixed).
+select * from
+  (select * from t1 union select * from t2) x2 left join t2 on x2.i = t2.i;
+I          |J          |I          |J          
+-----------------------------------------------
+1          |2          |1          |2          
+2          |-4         |2          |-4         
+2          |4          |2          |-4         
+3          |-6         |3          |6          
+3          |-6         |3          |6          
+3          |-6         |3          |-6         
+3          |6          |3          |6          
+3          |6          |3          |6          
+3          |6          |3          |-6         
+4          |-8         |4          |-8         
+4          |-8         |4          |8          
+4          |-8         |4          |-8         
+4          |8          |4          |-8         
+4          |8          |4          |8          
+4          |8          |4          |-8         
+5          |10         |5          |10         
+ij> -- cleanup.
+drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql?view=diff&rev=524940&r1=524939&r2=524940
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql Mon Apr  2 15:00:28 2007
@@ -395,3 +395,79 @@
 drop view v1;
 drop table o;
 drop table a;
+
+-- DERBY-1852: Incorrect results when a UNION U1 (with no "ALL") appears
+-- in the FROM list of a SELECT query, AND there are duplicate rows
+-- across the left and/or right result sets of U1, AND U1 is the left or
+-- right child of another set operator.
+
+create table t1 (i int, j int);
+create table t2 (i int, j int);
+insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10);
+insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10);
+insert into t2 values (3, 6), (4, 8), (3, -6), (4, -8);
+
+-- U1 is left child of another UNION; top-level query.
+select * from t1 union select * from t2 union select * from t1;
+
+-- U1 is left child of another UNION; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 union select * from t1) x;
+
+-- Same kind of thing, but in the form of a view (which is a
+-- more likely use-ccase).
+create view uv as
+  select * from t1 union select * from t2 union select * from t1;
+select * from uv;
+drop view uv;
+
+-- U1 is left child of a UNION ALL; top-level query.
+select * from t1 union select * from t2 union all select * from t1;
+
+-- U1 is left child of a UNION ALL; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 union all select * from t1) x;
+
+-- U1 is left child of an EXCEPT; top-level query.
+select * from t1 union select * from t2 except select * from t1;
+
+-- U1 is left child of an EXCEPT; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 except select * from t1) x;
+
+-- U1 is left child of an EXCEPT ALL; top-level query.
+select * from t1 union select * from t2 except all select * from t1;
+
+-- U1 is left child of an EXCEPT ALL; subquery in FROM list.
+select * from
+  (select * from t1 union select * from t2 except all select * from t1) x;
+
+-- U1 is left child of an INTERSECT; top-level query.
+-- Note: intersect has higher precedence than union so we have to use
+-- quotes to force the UNION to be a child of the intersect.
+(select * from t1 union select * from t2) intersect select * from t2;
+
+-- U1 is left child of an INTERSECT; subquery in FROM list.
+create view iv as
+  (select * from t1 union select * from t2) intersect select * from t2;
+select * from iv;
+drop view iv;
+
+-- U1 is left child of an INTERSECT ALL; top-level query.
+(select * from t1 union select * from t2) intersect all select * from t2;
+
+-- U1 is left child of an INTERSECT ALL; subquery in FROM list.
+create view iv as
+  (select * from t1 union select * from t2) intersect all select * from t2;
+select * from iv;
+drop view iv;
+
+-- Just as a sanity check, make sure things work if U1 is a child of
+-- an explicit JoinNode (since JoinNode is an instanceof TableOperatorNode
+-- and TableOperatorNode is where the bug for DERBY-1852 was fixed).
+select * from
+  (select * from t1 union select * from t2) x2 left join t2 on x2.i = t2.i;
+
+-- cleanup.
+drop table t1;
+drop table t2;



Mime
View raw message