db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [VOTE] [PATCH] Intersect and Except
Date Fri, 07 Jan 2005 01:08:31 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
This patch has been submitted. This enhances Derby by introducing
INTERSECT and EXCEPT operators. Thanks for the great enhancement, Jack.<br>
<br>
Satheesh<br>
<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\iapi\sql\compile\C_NodeTypes.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\iapi\sql\execute\ResultSetFactory.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\impl\sql\build.xml<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\C_NodeNames.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\NodeFactoryImpl.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\OrderByColumn.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\TableOperatorNode.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\UnionNode.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\execute\GenericResultSetFactory.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\loc\messages_en.properties<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\LOB.out<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\groupBy.out<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\copyfiles.ant<br>
Adding&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\IntersectOrExceptNode.java<br>
Adding&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\SetOperatorNode.java<br>
Adding&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\execute\SetOpResultSet.java<br>
Adding&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\intersect.out<br>
Adding&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\intersect.sql<br>
<br>
Jack Klebanoff wrote:<br>
<blockquote cite="mid41DB5376.4070403@Mutagen.Net" type="cite">Satheesh
Bandaram wrote:
  <br>
  <br>
  <blockquote type="cite">I am submitting this patch for a VOTE. It has
been pending for about a week. My vote is "+1", with the following
comments. Since this is a new feature, I think, three +1 votes are
requied. Here is the status of this patch. I am basically waiting for
the final +1 vote....
    <br>
    <br>
&nbsp;&nbsp; 1. It passed build and all tests.
    <br>
&nbsp;&nbsp; 2. Mike and myself have voted +1.
    <br>
&nbsp;&nbsp; 3. Dan provided a suggestion, with some syntax improvement. Any
response from the contributor? I am assuming Dan's vote is a +1. If
not, please speak up.. :-)
    <br>
    <br>
Here are my comments:
    <br>
    <br>
&nbsp;&nbsp; 1. IntersectOrExceptNode still refers to SetOpProjectRestrict.
Should this be SetOpResultSet?
    <br>
&nbsp;&nbsp; 2. Doesn't tableConstructor logic apply only to UnionNode? If so,
should the fields like tableConstructor, topTableConstructor and
methods like setTableConstructorTypes() be moved to UnionNode? Current
code in SetOperatorNode refers to subclass UnionNode a lot, which could
be improved?
    <br>
    <br>
    <br>
  </blockquote>
I have revised my patch to address Satheesh and Dan's comments. The
revision changes IntersectOrExceptNode.java, sqlgrammar.jj,
SetOpResultSet.java, TableOperatorNode.java, UnionNode.java, and
SetOperatorNode.java since my last submission. It passed the derbyall
test suite.
  <br>
  <br>
Jack
  <br>
  <pre wrap="">
<hr size="4" width="90%">
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(working copy)
@@ -512,6 +512,9 @@
 		  case C_NodeTypes.UNION_NODE:
 		  	return C_NodeNames.UNION_NODE_NAME;
 
+		  case C_NodeTypes.INTERSECT_OR_EXCEPT_NODE:
+		  	return C_NodeNames.INTERSECT_OR_EXCEPT_NODE_NAME;
+
 		  case C_NodeTypes.CREATE_TRIGGER_NODE:
 		  	return C_NodeNames.CREATE_TRIGGER_NODE_NAME;
 
Index: java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java	(working copy)
@@ -258,6 +258,8 @@
 
 	static final String UNION_NODE_NAME = "org.apache.derby.impl.sql.compile.UnionNode";
 
+	static final String INTERSECT_OR_EXCEPT_NODE_NAME = "org.apache.derby.impl.sql.compile.IntersectOrExceptNode";
+
 	static final String UNTYPED_NULL_CONSTANT_NODE_NAME = "org.apache.derby.impl.sql.compile.UntypedNullConstantNode";
 
 	static final String UPDATE_NODE_NAME = "org.apache.derby.impl.sql.compile.UpdateNode";
Index: java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/UnionNode.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/UnionNode.java	(working copy)
@@ -20,15 +20,12 @@
 
 package	org.apache.derby.impl.sql.compile;
 
-import org.apache.derby.iapi.services.context.ContextManager;
-
 import org.apache.derby.iapi.services.compiler.MethodBuilder;
 
 import org.apache.derby.iapi.services.sanity.SanityManager;
 
 import org.apache.derby.iapi.error.StandardException;
 
-import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.Optimizable;
 import org.apache.derby.iapi.sql.compile.OptimizablePredicate;
 import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
@@ -37,33 +34,18 @@
 import org.apache.derby.iapi.sql.compile.RowOrdering;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
 
-import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
-import org.apache.derby.iapi.sql.dictionary.DefaultDescriptor;
-import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
 
-import org.apache.derby.iapi.types.DataTypeDescriptor;
-
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.reference.ClassName;
 
-import org.apache.derby.iapi.sql.Activation;
-import org.apache.derby.iapi.types.DataTypeDescriptor;
-import org.apache.derby.iapi.sql.ResultSet;
-import org.apache.derby.iapi.sql.Row;
-
-import org.apache.derby.iapi.types.TypeId;
-
 import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
 
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+
 import org.apache.derby.iapi.util.JBitSet;
 import org.apache.derby.iapi.services.classfile.VMOpcode;
 
-import org.apache.derby.catalog.types.DefaultInfoImpl;
-
-import java.util.Properties;
-
 /**
  * A UnionNode represents a UNION in a DML statement.  It contains a boolean
  * telling whether the union operation should eliminate duplicate rows.
@@ -71,26 +53,19 @@
  * @author Jeff Lichtman
  */
 
-public class UnionNode extends TableOperatorNode
+public class UnionNode extends SetOperatorNode
 {
-	/**
-	** Tells whether to eliminate duplicate rows.  all == TRUE means do
-	** not eliminate duplicates, all == FALSE means eliminate duplicates.
-	*/
-	boolean			all;
+	/* Only optimize it once */
+	/* Only call addNewNodes() once */
+	private boolean addNewNodesCalled;
 
-	/* Is this a UNION ALL generated for a table constructor. */
+	/* Is this a UNION ALL generated for a table constructor -- a VALUES expression with multiple rows. */
 	boolean			tableConstructor;
 
 	/* True if this is the top node of a table constructor */
 	boolean			topTableConstructor;
 
-	/* Only optimize a UNION once */
-	/* Only call addNewNodes() once */
-	private boolean addNewNodesCalled;
 
-	private OrderByList orderByList;
-
 	/**
 	 * Initializer for a UnionNode.
 	 *
@@ -111,21 +86,12 @@
 					Object tableProperties)
 			throws StandardException
 	{
-		super.init(leftResult, rightResult, tableProperties);
+		super.init(leftResult, rightResult, all, tableProperties);
 
-		this.all = ((Boolean) all).booleanValue();
-
 		/* Is this a UNION ALL for a table constructor? */
 		this.tableConstructor = ((Boolean) tableConstructor).booleanValue();
+    } // end of init
 
-		/* resultColumns cannot be null, so we make a copy of the left RCL
-		 * for now.  At bind() time, we need to recopy the list because there
-		 * may have been a "*" in the list.  (We will set the names and
-		 * column types at that time, as expected.)
-		 */
-		resultColumns = leftResultSet.getResultColumns().copyListAndObjects();
-	}
-
 	/**
 	 * Mark this as the top node of a table constructor.
 	 */
@@ -422,11 +388,7 @@
 	{
 		if (SanityManager.DEBUG)
 		{
-			return 	"all: " + all + "\n" +
-			 	"tableConstructor: " + tableConstructor + "\n" +
-				"orderByList: " + 
-				(orderByList != null ? orderByList.toString() : "null") + "\n" +
-				super.toString();
+			return 	"tableConstructor: " + tableConstructor + "\n" + super.toString();
 		}
 		else
 		{
@@ -465,33 +427,33 @@
 			** Step through all the rows in the table constructor to
 			** get the type of the first non-? in each column.
 			*/
-			DataTypeDescriptor[]	types =
+			DataTypeDescriptor[] types =
 				new DataTypeDescriptor[leftResultSet.getResultColumns().size()];
 			
-			ResultSetNode	rsn;
-			int				numTypes = 0;
+			ResultSetNode rsn;
+			int numTypes = 0;
 
 			/* By looping through the union nodes, we avoid recursion */
-			for (rsn = this; rsn instanceof UnionNode; )
+			for (rsn = this; rsn instanceof SetOperatorNode; )
 			{
-				UnionNode		union = (UnionNode) rsn;
+				SetOperatorNode		setOperator = (SetOperatorNode) rsn;
 
 				/*
 				** Assume that table constructors are left-deep trees of
-				** UnionNodes with RowResultSet nodes on the right.
+				** SetOperatorNodes with RowResultSet nodes on the right.
 				*/
 				if (SanityManager.DEBUG)
 					SanityManager.ASSERT(
-					 union.rightResultSet instanceof RowResultSetNode,
-					 "A " + union.rightResultSet.getClass().getName() +
-					 " is on the right side of a union in a table constructor");
+					 setOperator.rightResultSet instanceof RowResultSetNode,
+					 "A " + setOperator.rightResultSet.getClass().getName() +
+					 " is on the right side of a setOperator in a table constructor");
 
 				RowResultSetNode	rrsn =
-										(RowResultSetNode) union.rightResultSet;
+										(RowResultSetNode) setOperator.rightResultSet;
 
 				numTypes += getParamColumnTypes(types, rrsn);
 
-				rsn = union.leftResultSet;
+				rsn = setOperator.leftResultSet;
 			}
 
 			/* The last node on the left should be a result set node */
@@ -511,554 +473,20 @@
 			** nodes, and give them the type from the type array we just
 			** constructed.
 			*/
-			for (rsn = this; rsn instanceof UnionNode; )
+			for (rsn = this; rsn instanceof SetOperatorNode; )
 			{
-				UnionNode		union = (UnionNode) rsn;
-				RowResultSetNode	rrsn =
-										(RowResultSetNode) union.rightResultSet;
+				SetOperatorNode	setOperator = (SetOperatorNode) rsn;
+				RowResultSetNode rrsn = (RowResultSetNode) setOperator.rightResultSet;
 
 				setParamColumnTypes(types, rrsn);
 
-				rsn = union.leftResultSet;
+				rsn = setOperator.leftResultSet;
 			}
 
 			setParamColumnTypes(types, (RowResultSetNode) rsn);
 		}
 	}
 
-	/**
-	 * Bind the result columns of this ResultSetNode when there is no
-	 * base table to bind them to.  This is useful for SELECT statements,
-	 * where the result columns get their types from the expressions that
-	 * live under them.
-	 *
-	 * @param fromListParam		FromList to use/append to.
-	 *
-	 * @return	Nothing
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public void bindResultColumns(FromList fromListParam)
-					throws StandardException
-	{
-		super.bindResultColumns(fromListParam);
-
-		/* Now we build our RCL */
-		buildRCL();
-	}
-
-	/**
-	 * Bind the result columns for this ResultSetNode to a base table.
-	 * This is useful for INSERT and UPDATE statements, where the
-	 * result columns get their types from the table being updated or
-	 * inserted into.
-	 * If a result column list is specified, then the verification that the 
-	 * result column list does not contain any duplicates will be done when
-	 * binding them by name.
-	 *
-	 * @param targetTableDescriptor	The TableDescriptor for the table being
-	 *				updated or inserted into
-	 * @param targetColumnList	For INSERT statements, the user
-	 *					does not have to supply column
-	 *					names (for example, "insert into t
-	 *					values (1,2,3)".  When this
-	 *					parameter is null, it means that
-	 *					the user did not supply column
-	 *					names, and so the binding should
-	 *					be done based on order.  When it
-	 *					is not null, it means do the binding
-	 *					by name, not position.
-	 * @param statement			Calling DMLStatementNode (Insert or Update)
-	 * @param fromListParam		FromList to use/append to.
-	 *
-	 * @return	Nothing
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-
-	public void bindResultColumns(TableDescriptor targetTableDescriptor,
-					FromVTI targetVTI,
-					ResultColumnList targetColumnList,
-					DMLStatementNode statement,
-					FromList fromListParam)
-				throws StandardException
-	{
-		super.bindResultColumns(targetTableDescriptor,
-								targetVTI,
-								targetColumnList, statement,
-								fromListParam);
-
-		/* Now we build our RCL */
-		buildRCL();
-	}
-
-	/**
-	 * Build the RCL for this node.  We propagate the RCL up from the
-	 * left child to form this node's RCL.
-	 *
-	 * @return	Nothing
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-
-	private void buildRCL() throws StandardException
-	{
-		/* Verify that both sides of the union have the same # of columns in their
-		 * RCL.
-		 */
-		if (leftResultSet.getResultColumns().size() !=
-			rightResultSet.getResultColumns().size())
-		{
-			throw StandardException.newException(SQLState.LANG_UNION_UNMATCHED_COLUMNS);
-		}
-
-		/* We need to recreate resultColumns for this node, since there
-		 * may have been 1 or more *'s in the left's SELECT list.
-		 */
-		resultColumns = leftResultSet.getResultColumns().copyListAndObjects();
-
-		/* Create new expressions with the dominant types after verifying
-		 * union compatibility between left and right sides.
-		 */
-		resultColumns.setUnionResultExpression(rightResultSet.getResultColumns(), tableNumber, level);
-	}
-
-	/**
-	 * Bind the result columns of a table constructor to the types in the
-	 * given ResultColumnList.  Use when inserting from a table constructor,
-	 * and there are nulls in the values clauses.
-	 *
-	 * @param rcl	The ResultColumnList with the types to bind to
-	 *
-	 * @exception StandardException		Thrown on error.
-	 */
-	public void bindUntypedNullsToResultColumns(ResultColumnList rcl)
-				throws StandardException
-	{
-		/*
-		** If the RCL from the parent is null, then
-		** the types are coming from the union itself.
-		** So we have to cross check the two child
-		** rcls.
-		*/
-		if (rcl == null)
-		{
-			ResultColumnList lrcl = rightResultSet.getResultColumns();
-			ResultColumnList rrcl = leftResultSet.getResultColumns();
-
-			leftResultSet.bindUntypedNullsToResultColumns(rrcl);
-			rightResultSet.bindUntypedNullsToResultColumns(lrcl);
-		}
-		else	
-		{
-			leftResultSet.bindUntypedNullsToResultColumns(rcl);
-			rightResultSet.bindUntypedNullsToResultColumns(rcl);
-		}			
-	}
-
-	/**
-	 * Get the parameter types from the given RowResultSetNode into the
-	 * given array of types.  If an array position is already filled in,
-	 * don't clobber it.
-	 *
-	 * @param types	The array of types to fill in
-	 * @param rrsn	The RowResultSetNode from which to take the param types
-	 *
-	 * @return	The number of new types found in the RowResultSetNode
-	 */
-	int getParamColumnTypes(DataTypeDescriptor[] types, RowResultSetNode rrsn)
-	{
-		int	numTypes = 0;
-
-		/* Look for columns where we have not found a non-? yet. */
-		for (int i = 0; i &lt; types.length; i++)
-		{
-			if (types[i] == null)
-			{
-				ResultColumn rc =
-					(ResultColumn) rrsn.getResultColumns().elementAt(i);
-				if ( ! (rc.getExpression().isParameterNode()))
-				{
-					types[i] = rc.getExpressionType();
-					numTypes++;
-				}
-			}
-		}
-
-		return numTypes;
-	}
-
-	/**
-	 * Set the type of each ? parameter in the given RowResultSetNode
-	 * according to its ordinal position in the given array of types.
-	 *
-	 * @param types	An array of types containing the proper type for each
-	 *				? parameter, by ordinal position.
-	 * @param rrsn	A RowResultSetNode that could contain ? parameters whose
-	 *				types need to be set.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	void setParamColumnTypes(DataTypeDescriptor[] types, RowResultSetNode rrsn)
-					throws StandardException
-	{
-		/*
-		** Look for ? parameters in the result column list
-		** of each RowResultSetNode
-		*/
-		ResultColumnList rrcl = rrsn.getResultColumns();
-		int rrclSize = rrcl.size();
-		for (int index = 0; index &lt; rrclSize; index++)
-		{
-			ResultColumn	rc = (ResultColumn) rrcl.elementAt(index);
-
-			if (rc.getExpression().isParameterNode())
-			{
-				/*
-				** We found a ? - set its type to the type from the
-				** type array.
-				*/
-				((ParameterNode) rc.getExpression()).setDescriptor(
-											types[index]);
-			}
-		}
-	}
-
-	/**
-	 * Bind the expressions in the target list.  This means binding the
-	 * sub-expressions, as well as figuring out what the return type is
-	 * for each expression.  This is useful for EXISTS subqueries, where we
-	 * need to validate the target list before blowing it away and replacing
-	 * it with a SELECT true.
-	 *
-	 * @return	Nothing
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-
-	public void bindTargetExpressions(FromList fromListParam)
-					throws StandardException
-	{
-		leftResultSet.bindTargetExpressions(fromListParam);
-		rightResultSet.bindTargetExpressions(fromListParam);
-	}
-
-	/**
-	 * Push the order by list down from the cursor node
-	 * into its child result set so that the optimizer
-	 * has all of the information that it needs to 
-	 * consider sort avoidance.
-	 *
-	 * @param orderByList	The order by list
-	 *
-	 * @return Nothing.
-	 */
-	void pushOrderByList(OrderByList orderByList)
-	{
-		this.orderByList = orderByList;
-	}
-
-	/** 
-	 * Put a ProjectRestrictNode on top of each FromTable in the FromList.
-	 * ColumnReferences must continue to point to the same ResultColumn, so
-	 * that ResultColumn must percolate up to the new PRN.  However,
-	 * that ResultColumn will point to a new expression, a VirtualColumnNode, 
-	 * which points to the FromTable and the ResultColumn that is the source for
-	 * the ColumnReference.  
-	 * (The new PRN will have the original of the ResultColumnList and
-	 * the ResultColumns from that list.  The FromTable will get shallow copies
-	 * of the ResultColumnList and its ResultColumns.  ResultColumn.expression
-	 * will remain at the FromTable, with the PRN getting a new 
-	 * VirtualColumnNode for each ResultColumn.expression.)
-	 * We then project out the non-referenced columns.  If there are no referenced
-	 * columns, then the PRN's ResultColumnList will consist of a single ResultColumn
-	 * whose expression is 1.
-	 *
-	 * @param numTables			Number of tables in the DML Statement
-	 * @param gbl				The group by list, if any
-	 * @param fromList			The from list, if any
-	 *
-	 * @return The generated ProjectRestrictNode atop the original FromTable.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-
-	public ResultSetNode preprocess(int numTables,
-									GroupByList gbl,
-									FromList fromList)
-								throws StandardException
-	{
-		ResultSetNode newTop = this;
-
-		/* RESOLVE - what does numTables and referencedTableMap mean here? */
-		leftResultSet = leftResultSet.preprocess(numTables, gbl, fromList);
-		rightResultSet = rightResultSet.preprocess(numTables, gbl, fromList);
-
-		/* Build the referenced table map (left || right) */
-		referencedTableMap = (JBitSet) leftResultSet.getReferencedTableMap().clone();
-		referencedTableMap.or((JBitSet) rightResultSet.getReferencedTableMap());
-
-		/* If this is a UNION without an all and we have
-		 * an order by then we can consider eliminating the sort for the
-		 * order by.  All of the columns in the order by list must
-		 * be ascending in order to do this.  There are 2 cases:
-		 *	o	The order by list is an in order prefix of the columns
-		 *		in the select list.  In this case the output of the
-		 *		sort from the distinct will be in the right order
-		 *		so we simply eliminate the order by list.
-		 *	o	The order by list is a subset of the columns in the
-		 *		the select list.  In this case we need to reorder the
-		 *		columns in the select list so that the ordering columns
-		 *		are an in order prefix of the select list and put a PRN
-		 *		above the select so that the shape of the result set
-		 *		is as expected.
-		 */
-		if ((! all) &amp;&amp; orderByList != null &amp;&amp; orderByList.allAscending())
-		{
-			/* Order by list currently restricted to columns in select
-			 * list, so we will always eliminate the order by here.
-			 */
-			if (orderByList.isInOrderPrefix(resultColumns))
-			{
-				orderByList = null;
-			}
-			/* RESOLVE - We currently only eliminate the order by if it is
-			 * a prefix of the select list.  We do not currently do the 
-			 * elimination if the order by is not a prefix because the code
-			 * doesn't work.  The problem has something to do with the
-			 * fact that we generate additional nodes between the union
-			 * and the PRN (for reordering that we would generate here)
-			 * when modifying the access paths.  VCNs under the PRN can be
-			 * seen as correlated since their source resultset is the Union
-			 * which is no longer the result set directly under them.  This
-			 * causes the wrong code to get generated. (jerry - 11/3/98)
-			 * (bug 59)
-			 */
-		}
-
-		return newTop;
-	}
-	
-	/**
-	 * Ensure that the top of the RSN tree has a PredicateList.
-	 *
-	 * @param numTables			The number of tables in the query.
-	 * @return ResultSetNode	A RSN tree with a node which has a PredicateList on top.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public ResultSetNode ensurePredicateList(int numTables) 
-		throws StandardException
-	{
-		return genProjectRestrict(numTables);
-	}
-
-	/**
-	 * Verify that a SELECT * is valid for this type of subquery.
-	 *
-	 * @param outerFromList	The FromList from the outer query block(s)
-	 * @param subqueryType	The subquery type
-	 *
-	 * @return	None
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public void verifySelectStarSubquery(FromList outerFromList, int subqueryType) 
-					throws StandardException
-	{
-		/* Check both sides - SELECT * is not valid on either side */
-		leftResultSet.verifySelectStarSubquery(outerFromList, subqueryType);
-		rightResultSet.verifySelectStarSubquery(outerFromList, subqueryType);
-	}
-
-	/** 
-	 * Determine whether or not the specified name is an exposed name in
-	 * the current query block.
-	 *
-	 * @param name	The specified name to search for as an exposed name.
-	 * @param schemaName	Schema name, if non-null.
-	 * @param exactMatch	Whether or not we need an exact match on specified schema and table
-	 *						names or match on table id.
-	 *
-	 * @return The FromTable, if any, with the exposed name.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	protected FromTable getFromTableByName(String name, String schemaName, boolean exactMatch)
-		throws StandardException
-	{
-		/* We search both sides for a TableOperatorNode (join nodes)
-		 * but only the left side for a UnionNode.
-		 */
-		return leftResultSet.getFromTableByName(name, schemaName, exactMatch);
-	}
-
-	/**
-	 * Set the result column for the subquery to a boolean true,
-	 * Useful for transformations such as
-	 * changing:
-	 *		where exists (select ... from ...) 
-	 * to:
-	 *		where (select true from ...)
-	 *
-	 * NOTE: No transformation is performed if the ResultColumn.expression is
-	 * already the correct boolean constant.
-	 * 
-	 * @param onlyConvertAlls	Boolean, whether or not to just convert *'s
-	 *
-	 * @return Nothing.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public void setResultToBooleanTrueNode(boolean onlyConvertAlls)
-				throws StandardException
-	{
-		super.setResultToBooleanTrueNode(onlyConvertAlls);
-		leftResultSet.setResultToBooleanTrueNode(onlyConvertAlls);
-		rightResultSet.setResultToBooleanTrueNode(onlyConvertAlls);
-	}
-
-	/**
-	 * This ResultSet is the source for an Insert.  The target RCL
-	 * is in a different order and/or a superset of this RCL.  In most cases
-	 * we will reorder and/or add defaults to the current RCL so that is
-	 * matches the target RCL.  Those RSNs whose generate() method does
-	 * not handle projects will insert a PRN, with a new RCL which matches
-	 * the target RCL, above the current RSN.
-	 * NOTE - The new or enhanced RCL will be fully bound.
-	 *
-	 * @param numTargetColumns	# of columns in target RCL
-	 * @param colMap[]			int array representation of correspondence between
-	 *							RCLs - colmap[i] = -1 -&gt; missing in current RCL
-	 *								   colmap[i] = j -&gt; targetRCL(i) &lt;-&gt; thisRCL(j+1)
-	 * @param dataDictionary	DataDictionary to use
-	 * @param targetTD			TableDescriptor for target if the target is not a VTI, null if a VTI
-     * @param targetVTI         Target description if it is a VTI, null if not a VTI
-	 *
-	 * @return ResultSetNode	The new top of the tree
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public ResultSetNode enhanceRCLForInsert(int numTargetColumns, int[] colMap, 
-											 DataDictionary dataDictionary,
-											 TableDescriptor targetTD,
-                                             FromVTI targetVTI)
-			throws StandardException
-	{
-		// our newResultCols are put into the bound form straight away.
-		ResultColumnList newResultCols =
-								(ResultColumnList) getNodeFactory().getNode(
-												C_NodeTypes.RESULT_COLUMN_LIST,
-												getContextManager());
-		int numResultSetColumns = resultColumns.size();
-
-		/* Create a massaged version of the source RCL.
-		 * (Much simpler to build new list and then assign to source,
-		 * rather than massage the source list in place.)
-		 */
-		for (int index = 0; index &lt; numTargetColumns; index++)
-		{
-			ResultColumn	newResultColumn;
-			ResultColumn	oldResultColumn;
-			ColumnReference newColumnReference;
-
-			if (colMap[index] != -1)
-			{
-				// getResultColumn uses 1-based positioning, so offset the colMap entry appropriately
-				oldResultColumn = resultColumns.getResultColumn(colMap[index]+1);
-
-				newColumnReference = (ColumnReference) getNodeFactory().getNode(
-												C_NodeTypes.COLUMN_REFERENCE,
-												oldResultColumn.getName(),
-												null,
-												getContextManager());
-				/* The ColumnReference points to the source of the value */
-				newColumnReference.setSource(oldResultColumn);
-				// colMap entry is 0-based, columnId is 1-based.
-				newColumnReference.setType(oldResultColumn.getExpressionType());
-
-				// Source of an insert, so nesting levels must be 0
-				newColumnReference.setNestingLevel(0);
-				newColumnReference.setSourceLevel(0);
-
-				// because the insert already copied the target table's
-				// column descriptors into the result, we grab it from there.
-				// alternatively, we could do what the else clause does,
-				// and look it up in the DD again.
-				newResultColumn = (ResultColumn) getNodeFactory().getNode(
-						C_NodeTypes.RESULT_COLUMN,
-						oldResultColumn.getType(),
-						newColumnReference,
-						getContextManager());
-			}
-			else
-			{
-				newResultColumn = genNewRCForInsert(targetTD, targetVTI, index + 1, dataDictionary);
-			}
-
-			newResultCols.addResultColumn(newResultColumn);
-		}
-
-		/* The generated ProjectRestrictNode now has the ResultColumnList
-		 * in the order that the InsertNode expects.
-		 * NOTE: This code here is an exception to several "rules":
-		 *		o  This is the only ProjectRestrictNode that is currently
-		 *		   generated outside of preprocess().
-		 *	    o  The UnionNode is the only node which is not at the
-		 *		   top of the query tree which has ColumnReferences under
-		 *		   its ResultColumnList prior to expression push down.
-		 */
-		return (ResultSetNode) getNodeFactory().getNode(
-									C_NodeTypes.PROJECT_RESTRICT_NODE,
-									this,
-									newResultCols,
-									null,
-									null,
-									null,
-									null,
-									tableProperties,
-									getContextManager());
-	}
-
-	/**
-	 * Evaluate whether or not the subquery in a FromSubquery is flattenable.  
-	 * Currently, a FSqry is flattenable if all of the following are true:
-	 *		o  Subquery is a SelectNode. (ie, not a RowResultSetNode or a UnionNode)
-	 *		o  It contains no top level subqueries.  (RESOLVE - we can relax this)
-	 *		o  It does not contain a group by or having clause
-	 *		o  It does not contain aggregates.
-	 *
-	 * @param fromList	The outer from list
-	 *
-	 * @return boolean	Whether or not the FromSubquery is flattenable.
-	 */
-	public boolean flattenableInFromSubquery(FromList fromList)
-	{
-		/* Unions in FromSubquerys are not flattenable.	 */
-		return false;
-	}
-
-	/**
-	 * Return whether or not to materialize this ResultSet tree.
-	 *
-	 * @return Whether or not to materialize this ResultSet tree.
-	 *			would return valid results.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public boolean performMaterialization(JBitSet outerTables)
-		throws StandardException
-	{
-		// RESOLVE - just say no to materialization right now - should be a cost based decision
-		return false;
-
-		/* Actual materialization, if appropriate, will be placed by our parent PRN.
-		 * This is because PRN might have a join condition to apply.  (Materialization
-		 * can only occur before that.
-		 */
-		//return true;
-	}
-
     /**
 	 * Generate the code for this UnionNode.
 	 *
@@ -1134,4 +562,9 @@
 
 		mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null, "getUnionResultSet", ClassName.NoPutResultSet, 7);
 	}
+
+    String getOperatorName()
+    {
+        return "UNION";
+    }
 }
Index: java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java	(revision 0)
+++ java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java	(revision 0)
@@ -0,0 +1,648 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.SetOperatorNode
+
+   Copyright 2004 The Apache Software Foundation or its licensors, as applicable.
+
+   Licensed 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
+
+      <a class="moz-txt-link-freetext" href="http://www.apache.org/licenses/LICENSE-2.0">http://www.apache.org/licenses/LICENSE-2.0</a>
+
+   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.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
+
+import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+
+import org.apache.derby.iapi.util.JBitSet;
+
+/**
+ * A SetOperatorNode represents a UNION, INTERSECT, or EXCEPT in a DML statement. Binding and optimization
+ * preprocessing is the same for all of these operations, so they share bind methods in this abstract class.
+ *
+ * The class contains a boolean telling whether the operation should eliminate
+ * duplicate rows.
+ *
+ * @author Jeff Lichtman
+ */
+
+public abstract class SetOperatorNode extends TableOperatorNode
+{
+	/**
+	** Tells whether to eliminate duplicate rows.  all == TRUE means do
+	** not eliminate duplicates, all == FALSE means eliminate duplicates.
+	*/
+	boolean			all;
+
+	OrderByList orderByList;
+
+
+	/**
+	 * Initializer for a SetOperatorNode.
+	 *
+	 * @param leftResult		The ResultSetNode on the left side of this union
+	 * @param rightResult		The ResultSetNode on the right side of this union
+	 * @param all				Whether or not this is an ALL.
+	 * @param tableProperties	Properties list associated with the table
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public void init(
+					Object leftResult,
+					Object rightResult,
+					Object all,
+					Object tableProperties)
+			throws StandardException
+	{
+		super.init(leftResult, rightResult, tableProperties);
+
+		this.all = ((Boolean) all).booleanValue();
+
+		/* resultColumns cannot be null, so we make a copy of the left RCL
+		 * for now.  At bind() time, we need to recopy the list because there
+		 * may have been a "*" in the list.  (We will set the names and
+		 * column types at that time, as expected.)
+		 */
+		resultColumns = leftResultSet.getResultColumns().copyListAndObjects();
+	}
+
+	/**
+	 * Convert this object to a String.  See comments in QueryTreeNode.java
+	 * for how this should be done for tree printing.
+	 *
+	 * @return	This object as a String
+	 */
+
+	public String toString()
+	{
+		if (SanityManager.DEBUG)
+		{
+			return 	"all: " + all + "\n" +
+				"orderByList: " + 
+				(orderByList != null ? orderByList.toString() : "null") + "\n" +
+				super.toString();
+		}
+		else
+		{
+			return "";
+		}
+	}
+
+	/**
+	 * Bind the result columns of this ResultSetNode when there is no
+	 * base table to bind them to.  This is useful for SELECT statements,
+	 * where the result columns get their types from the expressions that
+	 * live under them.
+	 *
+	 * @param fromListParam		FromList to use/append to.
+	 *
+	 * @return	Nothing
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void bindResultColumns(FromList fromListParam)
+					throws StandardException
+	{
+		super.bindResultColumns(fromListParam);
+
+		/* Now we build our RCL */
+		buildRCL();
+	}
+
+	/**
+	 * Bind the result columns for this ResultSetNode to a base table.
+	 * This is useful for INSERT and UPDATE statements, where the
+	 * result columns get their types from the table being updated or
+	 * inserted into.
+	 * If a result column list is specified, then the verification that the 
+	 * result column list does not contain any duplicates will be done when
+	 * binding them by name.
+	 *
+	 * @param targetTableDescriptor	The TableDescriptor for the table being
+	 *				updated or inserted into
+	 * @param targetColumnList	For INSERT statements, the user
+	 *					does not have to supply column
+	 *					names (for example, "insert into t
+	 *					values (1,2,3)".  When this
+	 *					parameter is null, it means that
+	 *					the user did not supply column
+	 *					names, and so the binding should
+	 *					be done based on order.  When it
+	 *					is not null, it means do the binding
+	 *					by name, not position.
+	 * @param statement			Calling DMLStatementNode (Insert or Update)
+	 * @param fromListParam		FromList to use/append to.
+	 *
+	 * @return	Nothing
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public void bindResultColumns(TableDescriptor targetTableDescriptor,
+					FromVTI targetVTI,
+					ResultColumnList targetColumnList,
+					DMLStatementNode statement,
+					FromList fromListParam)
+				throws StandardException
+	{
+		super.bindResultColumns(targetTableDescriptor,
+								targetVTI,
+								targetColumnList, statement,
+								fromListParam);
+
+		/* Now we build our RCL */
+		buildRCL();
+	}
+
+	/**
+	 * Build the RCL for this node.  We propagate the RCL up from the
+	 * left child to form this node's RCL.
+	 *
+	 * @return	Nothing
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	private void buildRCL() throws StandardException
+	{
+		/* Verify that both sides of the union have the same # of columns in their
+		 * RCL.
+		 */
+		if (leftResultSet.getResultColumns().size() !=
+			rightResultSet.getResultColumns().size())
+		{
+			throw StandardException.newException(SQLState.LANG_UNION_UNMATCHED_COLUMNS,
+                                                 getOperatorName());
+		}
+
+		/* We need to recreate resultColumns for this node, since there
+		 * may have been 1 or more *'s in the left's SELECT list.
+		 */
+		resultColumns = leftResultSet.getResultColumns().copyListAndObjects();
+
+		/* Create new expressions with the dominant types after verifying
+		 * union compatibility between left and right sides.
+		 */
+		resultColumns.setUnionResultExpression(rightResultSet.getResultColumns(), tableNumber, level, getOperatorName());
+	}
+
+	/**
+	 * Bind the result columns of a table constructor to the types in the
+	 * given ResultColumnList.  Use when inserting from a table constructor,
+	 * and there are nulls in the values clauses.
+	 *
+	 * @param rcl	The ResultColumnList with the types to bind to
+	 *
+	 * @exception StandardException		Thrown on error.
+	 */
+	public void bindUntypedNullsToResultColumns(ResultColumnList rcl)
+				throws StandardException
+	{
+		/*
+		** If the RCL from the parent is null, then
+		** the types are coming from the union itself.
+		** So we have to cross check the two child
+		** rcls.
+		*/
+		if (rcl == null)
+		{
+			ResultColumnList lrcl = rightResultSet.getResultColumns();
+			ResultColumnList rrcl = leftResultSet.getResultColumns();
+
+			leftResultSet.bindUntypedNullsToResultColumns(rrcl);
+			rightResultSet.bindUntypedNullsToResultColumns(lrcl);
+		}
+		else	
+		{
+			leftResultSet.bindUntypedNullsToResultColumns(rcl);
+			rightResultSet.bindUntypedNullsToResultColumns(rcl);
+		}			
+	}
+
+	/**
+	 * Get the parameter types from the given RowResultSetNode into the
+	 * given array of types.  If an array position is already filled in,
+	 * don't clobber it.
+	 *
+	 * @param types	The array of types to fill in
+	 * @param rrsn	The RowResultSetNode from which to take the param types
+	 *
+	 * @return	The number of new types found in the RowResultSetNode
+	 */
+	int getParamColumnTypes(DataTypeDescriptor[] types, RowResultSetNode rrsn)
+	{
+		int	numTypes = 0;
+
+		/* Look for columns where we have not found a non-? yet. */
+		for (int i = 0; i &lt; types.length; i++)
+		{
+			if (types[i] == null)
+			{
+				ResultColumn rc =
+					(ResultColumn) rrsn.getResultColumns().elementAt(i);
+				if ( ! (rc.getExpression().isParameterNode()))
+				{
+					types[i] = rc.getExpressionType();
+					numTypes++;
+				}
+			}
+		}
+
+		return numTypes;
+	}
+
+	/**
+	 * Set the type of each ? parameter in the given RowResultSetNode
+	 * according to its ordinal position in the given array of types.
+	 *
+	 * @param types	An array of types containing the proper type for each
+	 *				? parameter, by ordinal position.
+	 * @param rrsn	A RowResultSetNode that could contain ? parameters whose
+	 *				types need to be set.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	void setParamColumnTypes(DataTypeDescriptor[] types, RowResultSetNode rrsn)
+					throws StandardException
+	{
+		/*
+		** Look for ? parameters in the result column list
+		** of each RowResultSetNode
+		*/
+		ResultColumnList rrcl = rrsn.getResultColumns();
+		int rrclSize = rrcl.size();
+		for (int index = 0; index &lt; rrclSize; index++)
+		{
+			ResultColumn	rc = (ResultColumn) rrcl.elementAt(index);
+
+			if (rc.getExpression().isParameterNode())
+			{
+				/*
+				** We found a ? - set its type to the type from the
+				** type array.
+				*/
+				((ParameterNode) rc.getExpression()).setDescriptor(
+											types[index]);
+			}
+		}
+	}
+
+	/**
+	 * Bind the expressions in the target list.  This means binding the
+	 * sub-expressions, as well as figuring out what the return type is
+	 * for each expression.  This is useful for EXISTS subqueries, where we
+	 * need to validate the target list before blowing it away and replacing
+	 * it with a SELECT true.
+	 *
+	 * @return	Nothing
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public void bindTargetExpressions(FromList fromListParam)
+					throws StandardException
+	{
+		leftResultSet.bindTargetExpressions(fromListParam);
+		rightResultSet.bindTargetExpressions(fromListParam);
+	}
+
+	/**
+	 * Push the order by list down from the cursor node
+	 * into its child result set so that the optimizer
+	 * has all of the information that it needs to 
+	 * consider sort avoidance.
+	 *
+	 * @param orderByList	The order by list
+	 *
+	 * @return Nothing.
+	 */
+	void pushOrderByList(OrderByList orderByList)
+	{
+		this.orderByList = orderByList;
+	}
+
+	/** 
+	 * Put a ProjectRestrictNode on top of each FromTable in the FromList.
+	 * ColumnReferences must continue to point to the same ResultColumn, so
+	 * that ResultColumn must percolate up to the new PRN.  However,
+	 * that ResultColumn will point to a new expression, a VirtualColumnNode, 
+	 * which points to the FromTable and the ResultColumn that is the source for
+	 * the ColumnReference.  
+	 * (The new PRN will have the original of the ResultColumnList and
+	 * the ResultColumns from that list.  The FromTable will get shallow copies
+	 * of the ResultColumnList and its ResultColumns.  ResultColumn.expression
+	 * will remain at the FromTable, with the PRN getting a new 
+	 * VirtualColumnNode for each ResultColumn.expression.)
+	 * We then project out the non-referenced columns.  If there are no referenced
+	 * columns, then the PRN's ResultColumnList will consist of a single ResultColumn
+	 * whose expression is 1.
+	 *
+	 * @param numTables			Number of tables in the DML Statement
+	 * @param gbl				The group by list, if any
+	 * @param fromList			The from list, if any
+	 *
+	 * @return The preprocessed ResultSetNode that can be optimized
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public ResultSetNode preprocess(int numTables,
+									GroupByList gbl,
+									FromList fromList)
+								throws StandardException
+	{
+		ResultSetNode newTop = this;
+
+		/* RESOLVE - what does numTables and referencedTableMap mean here? */
+		leftResultSet = leftResultSet.preprocess(numTables, gbl, fromList);
+		rightResultSet = rightResultSet.preprocess(numTables, gbl, fromList);
+
+		/* Build the referenced table map (left || right) */
+		referencedTableMap = (JBitSet) leftResultSet.getReferencedTableMap().clone();
+		referencedTableMap.or((JBitSet) rightResultSet.getReferencedTableMap());
+
+		/* If this is a UNION without an all and we have
+		 * an order by then we can consider eliminating the sort for the
+		 * order by.  All of the columns in the order by list must
+		 * be ascending in order to do this.  There are 2 cases:
+		 *	o	The order by list is an in order prefix of the columns
+		 *		in the select list.  In this case the output of the
+		 *		sort from the distinct will be in the right order
+		 *		so we simply eliminate the order by list.
+		 *	o	The order by list is a subset of the columns in the
+		 *		the select list.  In this case we need to reorder the
+		 *		columns in the select list so that the ordering columns
+		 *		are an in order prefix of the select list and put a PRN
+		 *		above the select so that the shape of the result set
+		 *		is as expected.
+		 */
+		if ((! all) &amp;&amp; orderByList != null &amp;&amp; orderByList.allAscending())
+		{
+			/* Order by list currently restricted to columns in select
+			 * list, so we will always eliminate the order by here.
+			 */
+			if (orderByList.isInOrderPrefix(resultColumns))
+			{
+				orderByList = null;
+			}
+			/* RESOLVE - We currently only eliminate the order by if it is
+			 * a prefix of the select list.  We do not currently do the 
+			 * elimination if the order by is not a prefix because the code
+			 * doesn't work.  The problem has something to do with the
+			 * fact that we generate additional nodes between the union
+			 * and the PRN (for reordering that we would generate here)
+			 * when modifying the access paths.  VCNs under the PRN can be
+			 * seen as correlated since their source resultset is the Union
+			 * which is no longer the result set directly under them.  This
+			 * causes the wrong code to get generated. (jerry - 11/3/98)
+			 * (bug 59)
+			 */
+		}
+
+		return newTop;
+	}
+	
+	/**
+	 * Ensure that the top of the RSN tree has a PredicateList.
+	 *
+	 * @param numTables			The number of tables in the query.
+	 * @return ResultSetNode	A RSN tree with a node which has a PredicateList on top.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public ResultSetNode ensurePredicateList(int numTables) 
+		throws StandardException
+	{
+		return genProjectRestrict(numTables);
+	}
+
+	/**
+	 * Verify that a SELECT * is valid for this type of subquery.
+	 *
+	 * @param outerFromList	The FromList from the outer query block(s)
+	 * @param subqueryType	The subquery type
+	 *
+	 * @return	None
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void verifySelectStarSubquery(FromList outerFromList, int subqueryType) 
+					throws StandardException
+	{
+		/* Check both sides - SELECT * is not valid on either side */
+		leftResultSet.verifySelectStarSubquery(outerFromList, subqueryType);
+		rightResultSet.verifySelectStarSubquery(outerFromList, subqueryType);
+	}
+
+	/** 
+	 * Determine whether or not the specified name is an exposed name in
+	 * the current query block.
+	 *
+	 * @param name	The specified name to search for as an exposed name.
+	 * @param schemaName	Schema name, if non-null.
+	 * @param exactMatch	Whether or not we need an exact match on specified schema and table
+	 *						names or match on table id.
+	 *
+	 * @return The FromTable, if any, with the exposed name.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	protected FromTable getFromTableByName(String name, String schemaName, boolean exactMatch)
+		throws StandardException
+	{
+		/* We search both sides for a TableOperatorNode (join nodes)
+		 * but only the left side for a UnionNode.
+		 */
+		return leftResultSet.getFromTableByName(name, schemaName, exactMatch);
+	}
+
+	/**
+	 * Set the result column for the subquery to a boolean true,
+	 * Useful for transformations such as
+	 * changing:
+	 *		where exists (select ... from ...) 
+	 * to:
+	 *		where (select true from ...)
+	 *
+	 * NOTE: No transformation is performed if the ResultColumn.expression is
+	 * already the correct boolean constant.
+	 * 
+	 * @param onlyConvertAlls	Boolean, whether or not to just convert *'s
+	 *
+	 * @return Nothing.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void setResultToBooleanTrueNode(boolean onlyConvertAlls)
+				throws StandardException
+	{
+		super.setResultToBooleanTrueNode(onlyConvertAlls);
+		leftResultSet.setResultToBooleanTrueNode(onlyConvertAlls);
+		rightResultSet.setResultToBooleanTrueNode(onlyConvertAlls);
+	}
+
+	/**
+	 * This ResultSet is the source for an Insert.  The target RCL
+	 * is in a different order and/or a superset of this RCL.  In most cases
+	 * we will reorder and/or add defaults to the current RCL so that is
+	 * matches the target RCL.  Those RSNs whose generate() method does
+	 * not handle projects will insert a PRN, with a new RCL which matches
+	 * the target RCL, above the current RSN.
+	 * NOTE - The new or enhanced RCL will be fully bound.
+	 *
+	 * @param numTargetColumns	# of columns in target RCL
+	 * @param colMap[]			int array representation of correspondence between
+	 *							RCLs - colmap[i] = -1 -&gt; missing in current RCL
+	 *								   colmap[i] = j -&gt; targetRCL(i) &lt;-&gt; thisRCL(j+1)
+	 * @param dataDictionary	DataDictionary to use
+	 * @param targetTD			TableDescriptor for target if the target is not a VTI, null if a VTI
+     * @param targetVTI         Target description if it is a VTI, null if not a VTI
+	 *
+	 * @return ResultSetNode	The new top of the tree
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public ResultSetNode enhanceRCLForInsert(int numTargetColumns, int[] colMap, 
+											 DataDictionary dataDictionary,
+											 TableDescriptor targetTD,
+                                             FromVTI targetVTI)
+			throws StandardException
+	{
+		// our newResultCols are put into the bound form straight away.
+		ResultColumnList newResultCols =
+								(ResultColumnList) getNodeFactory().getNode(
+												C_NodeTypes.RESULT_COLUMN_LIST,
+												getContextManager());
+		int numResultSetColumns = resultColumns.size();
+
+		/* Create a massaged version of the source RCL.
+		 * (Much simpler to build new list and then assign to source,
+		 * rather than massage the source list in place.)
+		 */
+		for (int index = 0; index &lt; numTargetColumns; index++)
+		{
+			ResultColumn	newResultColumn;
+			ResultColumn	oldResultColumn;
+			ColumnReference newColumnReference;
+
+			if (colMap[index] != -1)
+			{
+				// getResultColumn uses 1-based positioning, so offset the colMap entry appropriately
+				oldResultColumn = resultColumns.getResultColumn(colMap[index]+1);
+
+				newColumnReference = (ColumnReference) getNodeFactory().getNode(
+												C_NodeTypes.COLUMN_REFERENCE,
+												oldResultColumn.getName(),
+												null,
+												getContextManager());
+				/* The ColumnReference points to the source of the value */
+				newColumnReference.setSource(oldResultColumn);
+				// colMap entry is 0-based, columnId is 1-based.
+				newColumnReference.setType(oldResultColumn.getExpressionType());
+
+				// Source of an insert, so nesting levels must be 0
+				newColumnReference.setNestingLevel(0);
+				newColumnReference.setSourceLevel(0);
+
+				// because the insert already copied the target table's
+				// column descriptors into the result, we grab it from there.
+				// alternatively, we could do what the else clause does,
+				// and look it up in the DD again.
+				newResultColumn = (ResultColumn) getNodeFactory().getNode(
+						C_NodeTypes.RESULT_COLUMN,
+						oldResultColumn.getType(),
+						newColumnReference,
+						getContextManager());
+			}
+			else
+			{
+				newResultColumn = genNewRCForInsert(targetTD, targetVTI, index + 1, dataDictionary);
+			}
+
+			newResultCols.addResultColumn(newResultColumn);
+		}
+
+		/* The generated ProjectRestrictNode now has the ResultColumnList
+		 * in the order that the InsertNode expects.
+		 * NOTE: This code here is an exception to several "rules":
+		 *		o  This is the only ProjectRestrictNode that is currently
+		 *		   generated outside of preprocess().
+		 *	    o  The UnionNode is the only node which is not at the
+		 *		   top of the query tree which has ColumnReferences under
+		 *		   its ResultColumnList prior to expression push down.
+		 */
+		return (ResultSetNode) getNodeFactory().getNode(
+									C_NodeTypes.PROJECT_RESTRICT_NODE,
+									this,
+									newResultCols,
+									null,
+									null,
+									null,
+									null,
+									tableProperties,
+									getContextManager());
+	}
+
+	/**
+	 * Evaluate whether or not the subquery in a FromSubquery is flattenable.  
+	 * Currently, a FSqry is flattenable if all of the following are true:
+	 *		o  Subquery is a SelectNode. (ie, not a RowResultSetNode or a UnionNode)
+	 *		o  It contains no top level subqueries.  (RESOLVE - we can relax this)
+	 *		o  It does not contain a group by or having clause
+	 *		o  It does not contain aggregates.
+	 *
+	 * @param fromList	The outer from list
+	 *
+	 * @return boolean	Whether or not the FromSubquery is flattenable.
+	 */
+	public boolean flattenableInFromSubquery(FromList fromList)
+	{
+		/* Unions in FromSubquerys are not flattenable.	 */
+		return false;
+	}
+
+	/**
+	 * Return whether or not to materialize this ResultSet tree.
+	 *
+	 * @return Whether or not to materialize this ResultSet tree.
+	 *			would return valid results.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public boolean performMaterialization(JBitSet outerTables)
+		throws StandardException
+	{
+		// RESOLVE - just say no to materialization right now - should be a cost based decision
+		return false;
+
+		/* Actual materialization, if appropriate, will be placed by our parent PRN.
+		 * This is because PRN might have a join condition to apply.  (Materialization
+		 * can only occur before that.
+		 */
+		//return true;
+	}
+
+    /**
+     * @return the operator name: "UNION", "INTERSECT", or "EXCEPT"
+     */
+    abstract String getOperatorName();
+}
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(working copy)
@@ -85,6 +85,7 @@
 import org.apache.derby.impl.sql.compile.TransactionStatementNode;
 import org.apache.derby.impl.sql.compile.TriggerReferencingStruct;
 import org.apache.derby.impl.sql.compile.UnionNode;
+import org.apache.derby.impl.sql.compile.IntersectOrExceptNode;
 import org.apache.derby.impl.sql.compile.UntypedNullConstantNode;
 import org.apache.derby.impl.sql.compile.UpdateNode;
 import org.apache.derby.impl.sql.compile.UserTypeConstantNode;
@@ -175,6 +176,15 @@
 	// Define for UTF8 max
 	private static final int	MAX_UTF8_LENGTH = 65535;
 
+    // Constants for set operator types
+    private static final int NO_SET_OP = 0;
+    private static final int UNION_OP = 1;
+    private static final int UNION_ALL_OP = 2;
+    private static final int EXCEPT_OP = 3;
+    private static final int EXCEPT_ALL_OP = 4;
+    private static final int INTERSECT_OP = 5;
+    private static final int INTERSECT_ALL_OP = 6;
+
 	private StringSlicer				stringSlicer;
 	private Object[]					paramDefaults;
 	private String						statementSQLText;
@@ -2683,7 +2693,7 @@
 	OrderByList orderCols = null;
 }
 {
-	queryExpression = queryExpression(null, Boolean.FALSE) 
+	queryExpression = queryExpression(null, NO_SET_OP) 
 		[ orderCols = orderByClause() ]
 		[ &lt;FOR&gt; forUpdateState = forUpdateClause(updateColumns) ]
 		[ isolationLevel = atIsolationLevel() ]
@@ -4129,24 +4139,37 @@
 
 /*
  * &lt;A NAME="queryExpression"&gt;queryExpression&lt;/A&gt;
+ *
+ * We have to be carefull to get the associativity correct. According to the SQL spec
+ *   &lt;non-join query expression&gt; ::=
+ *     &lt;non-join query term&gt;
+ *    | &lt;query expression body&gt; UNION [ ALL ] &lt;query term&gt;
+ *    | &lt;query expression body&gt; EXCEPT [ ALL ] &lt;query term&gt;
+ * Meaning that
+ *   t1 UNION ALL t2 UNION t3
+ * is equivalent to
+ *   (t1 UNION ALL t2) UNION t3
+ * However recursive descent parsers want recursion to be on the right, so this kind of associativity is unnatural
+ * for our parser. The queryExpression method must know whether it is being called as the right hand side of a
+ * set operator to produce a query tree with the correct associativity.
  */
 ResultSetNode
-queryExpression(ResultSetNode leftSide, Boolean unionAll) throws StandardException :
+queryExpression(ResultSetNode leftSide, int operatorType) throws StandardException :
 {
 	ResultSetNode	term;
 }
 {
-	term = nonJoinQueryTerm(leftSide, unionAll) [ term = union(term) ]
+	term = nonJoinQueryTerm(leftSide, operatorType) [ term = unionOrExcept(term) ]
 	{
 		return term;
 	}
 }
 
 /*
- * &lt;A NAME="union"&gt;union&lt;/A&gt;
+ * &lt;A NAME="unionOrExcept"&gt;unionOrExcept&lt;/A&gt;
  */
 ResultSetNode
-union(ResultSetNode term) throws StandardException :
+unionOrExcept(ResultSetNode term) throws StandardException :
 {
 	ResultSetNode	expression;
 	Token 			tok = null;
@@ -4154,42 +4177,129 @@
 {
 	&lt;UNION&gt; [ tok = &lt;ALL&gt; ] expression =
 				queryExpression(term,
-								(tok != null) ? Boolean.TRUE : Boolean.FALSE)
+								(tok != null) ? UNION_ALL_OP : UNION_OP)
 	{
 		return expression;
 	}
+|
+	&lt;EXCEPT&gt; [ tok = &lt;ALL&gt; ] expression =
+				queryExpression(term,
+								(tok != null) ? EXCEPT_ALL_OP : EXCEPT_OP)
+	{
+		return expression;
+	}
 }
 
 
 /*
  * &lt;A NAME="nonJoinQueryTerm"&gt;nonJoinQueryTerm&lt;/A&gt;
+ *
+ * Be careful with the associativity of INTERSECT. According to the SQL spec
+ *   t1 INTERSECT t2 INTERSECT ALL t3
+ * is equivalent to
+ *   (t1 INTERSECT t2) INTERSECT ALL t3
+ * which is not the same as
+ *   t1 INTERSECT (t2 INTERSECT ALL t3)
+ * See the comment on queryExpression.
  */
 ResultSetNode
-nonJoinQueryTerm(ResultSetNode leftSide, Boolean unionAll) throws StandardException :
+nonJoinQueryTerm(ResultSetNode leftSide, int operatorType) throws StandardException :
 {
 	ResultSetNode	term;
 }
 {
-	/*
-	** Omitted "intersect".
-	*/
-	term = nonJoinQueryPrimary()
+	term = nonJoinQueryPrimary() [ term = intersect( term) ]
 	{
-		if (leftSide != null)
-		{
-			return (ResultSetNode) nodeFactory.getNode(
-									C_NodeTypes.UNION_NODE,
-									leftSide,
-									term,
-									unionAll,
-									Boolean.FALSE,
-									null,
-									getContextManager());
-		}
-		else
-		{
-			return term;
-		}
+        switch( operatorType)
+        {
+        case NO_SET_OP:
+            return term;
+
+        case UNION_OP:
+            return (ResultSetNode) nodeFactory.getNode(
+                C_NodeTypes.UNION_NODE,
+                leftSide,
+                term,
+                Boolean.FALSE,
+                Boolean.FALSE,
+                null,
+                getContextManager());
+
+        case UNION_ALL_OP:
+            return (ResultSetNode) nodeFactory.getNode(
+                C_NodeTypes.UNION_NODE,
+                leftSide,
+                term,
+                Boolean.TRUE,
+                Boolean.FALSE,
+                null,
+                getContextManager());
+
+        case EXCEPT_OP:
+            return (ResultSetNode) nodeFactory.getNode(
+                C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+                ReuseFactory.getInteger( IntersectOrExceptNode.EXCEPT_OP),
+                leftSide,
+                term,
+                Boolean.FALSE,
+                null,
+                getContextManager());
+
+        case EXCEPT_ALL_OP:
+            return (ResultSetNode) nodeFactory.getNode(
+                C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+                ReuseFactory.getInteger( IntersectOrExceptNode.EXCEPT_OP),
+                leftSide,
+                term,
+                Boolean.TRUE,
+                null,
+                getContextManager());
+
+        case INTERSECT_OP:
+            return (ResultSetNode) nodeFactory.getNode(
+                C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+                ReuseFactory.getInteger( IntersectOrExceptNode.INTERSECT_OP),
+                leftSide,
+                term,
+                Boolean.FALSE,
+                null,
+                getContextManager());
+
+        case INTERSECT_ALL_OP:
+            return (ResultSetNode) nodeFactory.getNode(
+                C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+                ReuseFactory.getInteger( IntersectOrExceptNode.INTERSECT_OP),
+                leftSide,
+                term,
+                Boolean.TRUE,
+                null,
+                getContextManager());
+
+
+        default:
+            if (SanityManager.DEBUG)
+            {
+                SanityManager.THROWASSERT( "Invalid set operator type: " + operatorType);
+            }
+            return null;
+        }
+    }
+}
+
+/*
+ * &lt;A NAME="intersect"&gt;intersect&lt;/A&gt;
+ */
+ResultSetNode
+intersect(ResultSetNode term) throws StandardException :
+{
+	ResultSetNode	expression;
+	Token 			tok = null;
+}
+{
+	&lt;INTERSECT&gt; [ tok = &lt;ALL&gt; ] expression =
+				nonJoinQueryTerm(term, (tok != null) ? INTERSECT_ALL_OP : INTERSECT_OP)
+	{
+		return expression;
 	}
 }
 
@@ -4207,7 +4317,7 @@
 		return primary;
 	}
 |
-	&lt;LEFT_PAREN&gt; primary = queryExpression(null, Boolean.FALSE) &lt;RIGHT_PAREN&gt;
+	&lt;LEFT_PAREN&gt; primary = queryExpression(null, NO_SET_OP) &lt;RIGHT_PAREN&gt;
 	{
 		return primary;
 	}
@@ -6750,7 +6860,7 @@
 		&lt;LEFT_PAREN&gt; columnList = insertColumnList() &lt;RIGHT_PAREN&gt;
 	]
 	[ targetProperties = propertyList() ]
-	queryExpression = queryExpression(null, Boolean.FALSE)
+	queryExpression = queryExpression(null, NO_SET_OP)
 	{
 		return (QueryTreeNode) nodeFactory.getNode(
 							C_NodeTypes.INSERT_NODE,
@@ -6997,7 +7107,7 @@
 	SubqueryNode	subqueryNode;
 }
 {
-	queryExpression = queryExpression(null, Boolean.FALSE)
+	queryExpression = queryExpression(null, NO_SET_OP)
 	{
 		subqueryNode = (SubqueryNode) nodeFactory.getNode(
 										C_NodeTypes.SUBQUERY_NODE,
@@ -8778,7 +8888,7 @@
 {
 	&lt;VIEW&gt; tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) 
 		[ &lt;LEFT_PAREN&gt; resultColumns = viewColumnList() &lt;RIGHT_PAREN&gt; ]
-		&lt;AS&gt; queryExpression = queryExpression(null, Boolean.FALSE)
+		&lt;AS&gt; queryExpression = queryExpression(null, NO_SET_OP)
 	{
 		checkOptionType = ViewDescriptor.NO_CHECK_OPTION;
 		endToken = getToken(0);
@@ -9662,7 +9772,7 @@
 	&lt;EXECUTE&gt; &lt;STATEMENT&gt; stmtName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) 
 		[ LOOKAHEAD( { getToken(1).kind == USING } )
 		  usingToken = &lt;USING&gt; usingClause =
-		  							queryExpression(null, Boolean.FALSE) ]
+		  							queryExpression(null, NO_SET_OP) ]
 	{
 		endToken = getToken(0);
 
Index: java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java	(revision 0)
+++ java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java	(revision 0)
@@ -0,0 +1,394 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.IntersectNode
+
+   Copyright 2004 The Apache Software Foundation or its licensors, as applicable.
+
+   Licensed 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
+
+      <a class="moz-txt-link-freetext" href="http://www.apache.org/licenses/LICENSE-2.0">http://www.apache.org/licenses/LICENSE-2.0</a>
+
+   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.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.reference.ClassName;
+
+import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.services.context.ContextManager;
+
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.sql.compile.NodeFactory;
+import org.apache.derby.iapi.sql.compile.Optimizable;
+import org.apache.derby.iapi.sql.compile.OptimizablePredicate;
+import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
+import org.apache.derby.iapi.sql.compile.Optimizer;
+import org.apache.derby.iapi.sql.compile.CostEstimate;
+import org.apache.derby.iapi.sql.compile.RowOrdering;
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+
+import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
+
+import org.apache.derby.iapi.reference.SQLState;
+
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+
+import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.iapi.util.ReuseFactory;
+
+import java.sql.Types;
+
+import java.util.BitSet;
+
+/**
+ * A IntersectOrExceptNode represents an INTERSECT or EXCEPT DML statement.
+ *
+ * @author Jack Klebanoff
+ */
+
+public class IntersectOrExceptNode extends SetOperatorNode
+{
+    /* Currently we implement INTERSECT and EXCEPT by rewriting
+     *   t1 (INTERSECT|EXCEPT) [ALL] t2
+     * as (roughly)
+     *   setOpResultSet( opType, all, (select * from t1 order by 1,2,...n), (select * from t2 ORDER BY 1,2,...,n))
+     * where n is the number of columns in t1 (which must be the same as the number of columns in t2),
+     * and opType is INTERSECT, or EXCEPT.
+     *
+     * The setOpResultSet result set simultaneously scans through its two ordered inputs and
+     * performs the intersect or except.
+     *
+     * There are other query plans that may be more efficient, depending on the sizes. One plan is
+     * to make a hash table from one of the input tables and then look up each row of the other input
+     * table in the hash table.  However, we have not yet implemented spilling to disk in the
+     * BackingStoreHashtable class: currently the whole hash table is in RAM. If we were to use it
+     * we would blow up on large input tables.
+     */
+
+    private int opType;
+    public static final int INTERSECT_OP = 1;
+    public static final int EXCEPT_OP = 2;
+
+	/* Only optimize it once */
+	/* Only call addNewNodes() once */
+	private boolean addNewNodesCalled;
+
+    private int[] intermediateOrderByColumns; // The input result sets will be ordered on these columns. 0 indexed
+    private int[] intermediateOrderByDirection; // ascending = 1, descending = -1
+
+	/**
+	 * Initializer for a SetOperatorNode.
+	 *
+	 * @param leftResult		The ResultSetNode on the left side of this union
+	 * @param rightResult		The ResultSetNode on the right side of this union
+	 * @param all				Whether or not this is an ALL.
+	 * @param tableProperties	Properties list associated with the table
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public void init( Object opType,
+                      Object leftResult,
+                      Object rightResult,
+                      Object all,
+                      Object tableProperties)
+        throws StandardException
+	{
+        super.init( leftResult, rightResult, all, tableProperties);
+        this.opType = ((Integer) opType).intValue();
+    }
+
+    private int getOpType()
+    {
+        return opType;
+    }
+    
+    /**
+     * Push order by lists down to the children so that we can implement the intersect/except
+     * by scan of the two sorted inputs.
+	 *
+	 * @param numTables			Number of tables in the DML Statement
+	 * @param gbl				The group by list, if any
+	 * @param fromList			The from list, if any
+	 *
+	 * @return The preprocessed ResultSetNode that can be optimized
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public ResultSetNode preprocess(int numTables,
+									GroupByList gbl,
+									FromList fromList)
+								throws StandardException
+	{
+        // RESOLVE: We are in a quandary as to when and how we should generate order by lists. SelectNode processing
+        // requires order by lists at the start of preprocess. That is why we are doing it here. However we can
+        // pick any column ordering. Depending on the child expressions the optimizer may be able to avoid a
+        // sort if we pick the right column ordering. For instance if one of the child expressions is
+        // "select &lt;key columns&gt;, &lt;other expressions&gt; from T" where there is a unique index on &lt;key columns&gt;
+        // then we can just generate an order by on the key columns and the optimizer should use the unique index
+        // to produce the sorted result set. However the ResultSetNode class does not make it easy to
+        // find the structure of the query expression. Furthermore we most want to avoid a sort on the larger
+        // input, but the size estimate is not available at preprocess time.
+
+        intermediateOrderByColumns = new int[ getResultColumns().size()];
+        intermediateOrderByDirection = new int[ intermediateOrderByColumns.length];
+        /* If there is an order by on the result of the intersect then use that because we know that doing so
+         * will avoid a sort.  If the output of the intersect/except is small relative to its inputs then in some
+         * cases it would be better to sort the inputs on a different sequence of columns, but it is hard to analyze
+         * the input query expressions to see if a sort can be avoided.
+         */
+        if( orderByList != null)
+        {
+            BitSet colsOrdered = new BitSet( intermediateOrderByColumns.length);
+            int orderByListSize = orderByList.size();
+            int intermediateOrderByIdx = 0;
+            for( int i = 0; i &lt; orderByListSize; i++)
+            {
+                if( colsOrdered.get(i))
+                    continue;
+                OrderByColumn orderByColumn = orderByList.getOrderByColumn(i);
+                intermediateOrderByDirection[intermediateOrderByIdx] = orderByColumn.isAscending() ? 1 : -1;
+                int columnIdx = orderByColumn.getResultColumn().getColumnPosition() - 1;
+                intermediateOrderByColumns[intermediateOrderByIdx] = columnIdx;
+                colsOrdered.set( columnIdx);
+                intermediateOrderByIdx++;
+            }
+            for( int i = 0; i &lt; intermediateOrderByColumns.length; i++)
+            {
+                if( ! colsOrdered.get(i))
+                {
+                    intermediateOrderByDirection[intermediateOrderByIdx] = 1;
+                    intermediateOrderByColumns[intermediateOrderByIdx] = i;
+                    intermediateOrderByIdx++;
+                }
+            }
+            orderByList = null; // It will be pushed down.
+        }
+        else // The output of the intersect/except does not have to be ordered
+        {
+            // Pick an intermediate ordering that minimizes the cost.
+            // RESOLVE: how do you do that?
+            for( int i = 0; i &lt; intermediateOrderByColumns.length; i++)
+            {
+                intermediateOrderByDirection[i] = 1;
+                intermediateOrderByColumns[i] = i;
+            }
+        }
+        pushOrderingDown( leftResultSet);
+        pushOrderingDown( rightResultSet);
+
+        return super.preprocess( numTables, gbl, fromList);
+    } // end of preprocess
+
+    private void pushOrderingDown( ResultSetNode rsn)
+        throws StandardException
+    {
+        ContextManager cm = getContextManager();
+        NodeFactory nf = getNodeFactory();
+        OrderByList orderByList = (OrderByList) nf.getNode( C_NodeTypes.ORDER_BY_LIST, cm);
+        for( int i = 0; i &lt; intermediateOrderByColumns.length; i++)
+        {
+            OrderByColumn orderByColumn = (OrderByColumn)
+              nf.getNode( C_NodeTypes.ORDER_BY_COLUMN,
+                          ReuseFactory.getInteger( intermediateOrderByColumns[i] + 1),
+                          cm);
+            if( intermediateOrderByDirection[i] &lt; 0)
+                orderByColumn.setDescending();
+            orderByList.addOrderByColumn( orderByColumn);
+        }
+        orderByList.bindOrderByColumns( rsn);
+        rsn.pushOrderByList( orderByList);
+    } // end of pushOrderingDown
+                                                            
+    /**
+     * @see org.apache.derby.iapi.sql.compile.Optimizable#estimateCost
+     */
+    public CostEstimate estimateCost( OptimizablePredicateList predList,
+                                      ConglomerateDescriptor cd,
+                                      CostEstimate outerCost,
+                                      Optimizer optimizer,
+                                      RowOrdering rowOrdering)
+                          throws StandardException
+    {
+		CostEstimate costEstimate = getCostEstimate(optimizer);
+        CostEstimate leftCostEstimate = leftResultSet.getCostEstimate();
+        CostEstimate rightCostEstimate = rightResultSet.getCostEstimate();
+        // The cost is the sum of the two child costs plus the cost of sorting the union.
+        costEstimate.setCost( leftCostEstimate.getEstimatedCost() + rightCostEstimate.getEstimatedCost(),
+                              getRowCountEstimate( leftCostEstimate.rowCount(),
+                                                   rightCostEstimate.rowCount()),
+                              getSingleScanRowCountEstimate( leftCostEstimate.singleScanRowCount(),
+                                                             rightCostEstimate.singleScanRowCount()));
+
+        return costEstimate;
+    } // End of estimateCost
+
+	/**
+	 * @see Optimizable#modifyAccessPath
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public Optimizable modifyAccessPath(JBitSet outerTables) throws StandardException
+	{
+		Optimizable retOptimizable;
+		retOptimizable = super.modifyAccessPath(outerTables);
+
+		/* We only want call addNewNodes() once */
+		if (addNewNodesCalled)
+		{
+			return retOptimizable;
+		}
+		return (Optimizable) addNewNodes();
+	}
+
+	/**
+	 * @see ResultSetNode#modifyAccessPaths
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public ResultSetNode modifyAccessPaths() throws StandardException
+	{
+		ResultSetNode retRSN;
+		retRSN = super.modifyAccessPaths();
+
+		/* We only want call addNewNodes() once */
+		if (addNewNodesCalled)
+		{
+			return retRSN;
+		}
+		return addNewNodes();
+	}
+
+	/**
+	 * Add any new ResultSetNodes that are necessary to the tree.
+	 * We wait until after optimization to do this in order to
+	 * make it easier on the optimizer.
+	 *
+	 * @return (Potentially new) head of the ResultSetNode tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	private ResultSetNode addNewNodes()
+		throws StandardException
+	{
+		/* Only call addNewNodes() once */
+		if (addNewNodesCalled)
+		{
+			return this;
+		}
+
+		addNewNodesCalled = true;
+
+        if( orderByList == null)
+            return this;
+        // Generate an order by node on top of the intersect/except
+        return (ResultSetNode) getNodeFactory().getNode( C_NodeTypes.ORDER_BY_NODE,
+                                                         this,
+                                                         orderByList,
+                                                         tableProperties,
+                                                         getContextManager());
+    } // end of addNewNodes
+
+    /**
+	 * Generate the code.
+	 *
+	 * @exception StandardException		Thrown on error
+     */
+	public void generate( ActivationClassBuilder acb,
+                          MethodBuilder mb)
+        throws StandardException
+	{
+
+		/* Get the next ResultSet #, so that we can number this ResultSetNode, its
+		 * ResultColumnList and ResultSet.
+		 */
+		assignResultSetNumber();
+
+		// build up the tree.
+
+        /* Generate the SetOpResultSet. Arguments:
+         *  1) expression for left child ResultSet
+         *  2) expression for right child ResultSet
+         *  3) activation
+         *  4) resultSetNumber
+         *  5) estimated row count
+         *  6) estimated cost
+         *  7) opType
+         *  8) all
+         *  9) close method
+         *  10) intermediateOrderByColumns saved object index
+         *  11) intermediateOrderByDirection saved object index
+         */
+
+		acb.pushGetResultSetFactoryExpression(mb); // instance for getUnionResultSet
+
+		getLeftResultSet().generate( acb, mb);
+		getRightResultSet().generate( acb, mb);
+
+		acb.pushThisAsActivation(mb);
+		mb.push(resultSetNumber);
+        mb.push( costEstimate.getEstimatedRowCount());
+        mb.push( costEstimate.getEstimatedCost());
+        mb.push( getOpType());
+        mb.push( all);
+        closeMethodArgument(acb, mb);
+        mb.push( getCompilerContext().addSavedObject( intermediateOrderByColumns));
+        mb.push( getCompilerContext().addSavedObject( intermediateOrderByDirection));
+
+		mb.callMethod(VMOpcode.INVOKEINTERFACE,
+                      (String) null,
+                      "getSetOpResultSet",
+                      ClassName.NoPutResultSet, 11);
+	} // end of generate
+
+    String getOperatorName()
+    {
+        switch( opType)
+        {
+        case INTERSECT_OP:
+            return "INTERSECT";
+
+        case EXCEPT_OP:
+            return "EXCEPT";
+        }
+        if( SanityManager.DEBUG)
+            SanityManager.THROWASSERT( "Invalid intersectOrExcept opType: " + opType);
+        return "?";
+    }
+    
+    double getRowCountEstimate( double leftRowCount, double rightRowCount)
+    {
+        switch( opType)
+        {
+        case INTERSECT_OP:
+            // The result has at most min( leftRowCount, rightRowCount). Estimate the actual row count at
+            // half that.
+            return Math.min( leftRowCount, rightRowCount)/2;
+
+        case EXCEPT_OP:
+            // The result has at most leftRowCount rows and at least min( 0, leftRowCount - rightRowCount) rows.
+            // Use the mean of those two as the estimate.
+            return (leftRowCount + Math.min( 0, leftRowCount - rightRowCount))/2;
+        }
+        if( SanityManager.DEBUG)
+            SanityManager.THROWASSERT( "Invalid intersectOrExcept opType: " + opType);
+        return 1.0;
+    } // end of getRowCountEstimate
+    
+    double getSingleScanRowCountEstimate( double leftSingleScanRowCount, double rightSingleScanRowCount)
+    {
+        return getRowCountEstimate( leftSingleScanRowCount, rightSingleScanRowCount);
+    }
+}
Index: java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java	(working copy)
@@ -419,8 +419,8 @@
 					throws StandardException
 	{
 		/*
-		** Parameters not allowed in select list of either side of union,
-		** except when the union is for a table constructor.
+		** Parameters not allowed in select list of either side of a set operator,
+		** except when the set operator is for a table constructor.
 		*/
 		if ( ! (this instanceof UnionNode) ||
 			 ! ((UnionNode) this).tableConstructor())
Index: java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java	(working copy)
@@ -172,7 +172,7 @@
 		ResultColumnList	targetCols = target.getResultColumns();
 
 		//bug 5716 - for db2 compatibility - no qualified names allowed in order by clause when union/union all operator is used 
-		if (target instanceof UnionNode &amp;&amp; correlationName != null)
+		if (target instanceof SetOperatorNode &amp;&amp; correlationName != null)
 		{
 			String fullName = (schemaName != null) ?
 				(schemaName + "." + correlationName + "." + columnName) :
@@ -207,7 +207,7 @@
 			 * because of the gyrations we go to with building the RCLs
 			 * for a UnionNode.
 			 */
-			if (target instanceof UnionNode)
+			if (target instanceof SetOperatorNode)
 			{
 				sourceTableNumber = ((FromTable) target).getTableNumber();
 			}
Index: java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java	(working copy)
@@ -2025,7 +2025,7 @@
 	}
 
 	/**
-	 * Set up the result expressions for a UNION:
+	 * Set up the result expressions for a UNION, INTERSECT, or EXCEPT:
 	 *	o Verify union type compatiblity
 	 *	o Get dominant type for result (type + max length + nullability)
 	 *  o Create a new ColumnReference with dominant type and name of from this
@@ -2038,14 +2038,16 @@
 	 * @param otherRCL	RCL from other side of the UNION.
 	 * @param tableNumber	The tableNumber for the UNION.
 	 * @param level		The nesting level for the UNION.
+     * @param operatorName "UNION", "INTERSECT", or "EXCEPT"
 	 *
 	 * @return Nothing.
 	 *
 	 * @exception StandardException			Thrown on error
 	 */
 	public void	setUnionResultExpression(ResultColumnList otherRCL,
-						 int tableNumber,
-						 int level)
+                                         int tableNumber,
+                                         int level,
+                                         String operatorName)
 		throws StandardException
 	{
 		TableName		dummyTN;
@@ -2116,8 +2118,9 @@
 				!otherExpr.getTypeCompiler().storable(thisTypeId, cf))
 			{
 				throw StandardException.newException(SQLState.LANG_NOT_UNION_COMPATIBLE, 
-							thisTypeId.getSQLTypeName(),
-							otherTypeId.getSQLTypeName() );
+                                                     thisTypeId.getSQLTypeName(),
+                                                     otherTypeId.getSQLTypeName(),
+                                                     operatorName);
 			}
 
 			DataTypeDescriptor resultType = thisExpr.getTypeServices().getDominantType(
Index: java/engine/org/apache/derby/impl/sql/build.xml
===================================================================
--- java/engine/org/apache/derby/impl/sql/build.xml	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/build.xml	(working copy)
@@ -16,6 +16,8 @@
   &lt;property file="${properties.dir}/extrapath.properties"/&gt;
   &lt;property file="${properties.dir}/compilepath.properties"/&gt;
 
+  &lt;property name="cur.dir" value="impl/sql"/&gt;
+
 &lt;!-- Targets --&gt;
   &lt;target name="parser"&gt;
     &lt;ant antfile="${src.dir}/build.xml" target="genParser"&gt;
@@ -42,9 +44,9 @@
       &lt;classpath&gt;
         &lt;pathelement path="${compile.classpath}"/&gt;
       &lt;/classpath&gt;
-      &lt;include name="${derby.dir}/impl/sql/**"/&gt;
+      &lt;include name="${derby.dir}/${cur.dir}/**"/&gt;
     &lt;/javac&gt;
-    &lt;copy file="catalog/metadata_net.properties" tofile="${out.dir}/org/apache/derby/impl/sql/catalog/metadata_net.properties"/&gt;
+    &lt;copy file="${derby.engine.src.dir}/${derby.dir}/${cur.dir}/catalog/metadata_net.properties" tofile="${out.dir}/org/apache/derby/impl/sql/catalog/metadata_net.properties"/&gt;
   &lt;/target&gt;
 
 &lt;/project&gt;
Index: java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java	(revision 124163)
+++ java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java	(working copy)
@@ -1053,6 +1053,32 @@
 								  closeCleanup);
 	}
 
+    public NoPutResultSet getSetOpResultSet( NoPutResultSet leftSource,
+                                             NoPutResultSet rightSource,
+                                             Activation activation, 
+                                             int resultSetNumber,
+                                             long optimizerEstimatedRowCount,
+                                             double optimizerEstimatedCost,
+                                             int opType,
+                                             boolean all,
+                                             GeneratedMethod closeCleanup,
+                                             int intermediateOrderByColumnsSavedObject,
+                                             int intermediateOrderByDirectionSavedObject)
+        throws StandardException
+    {
+        return new SetOpResultSet( leftSource,
+                                   rightSource,
+                                   activation,
+                                   resultSetNumber,
+                                   optimizerEstimatedRowCount,
+                                   optimizerEstimatedCost,
+                                   opType,
+                                   all,
+                                   closeCleanup,
+                                   intermediateOrderByColumnsSavedObject,
+                                   intermediateOrderByDirectionSavedObject);
+    }
+
 	/**
 	 * A last index key sresult set returns the last row from
 	 * the index in question.  It is used as an ajunct to max().
Index: java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java	(revision 0)
+++ java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java	(revision 0)
@@ -0,0 +1,287 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.SetOpResultSet
+
+   Copyright 2004 The Apache Software Foundation or its licensors, as applicable.
+
+   Licensed 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
+
+      <a class="moz-txt-link-freetext" href="http://www.apache.org/licenses/LICENSE-2.0">http://www.apache.org/licenses/LICENSE-2.0</a>
+
+   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.derby.impl.sql.execute;
+
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.services.loader.GeneratedMethod;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.sql.ResultDescription;
+
+import org.apache.derby.iapi.sql.execute.CursorResultSet;
+import org.apache.derby.iapi.sql.execute.ExecPreparedStatement;
+import org.apache.derby.iapi.sql.execute.ExecRow;
+import org.apache.derby.iapi.sql.execute.NoPutResultSet;
+
+import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.Orderable;
+import org.apache.derby.iapi.types.RowLocation;
+
+import org.apache.derby.impl.sql.compile.IntersectOrExceptNode;
+
+/**
+ * Takes the result set produced by an ordered UNION ALL of two tagged result sets and produces
+ * the INTERSECT or EXCEPT of the two input result sets. This also projects out the tag, the last column
+ * of the input rows.
+ */
+public class SetOpResultSet extends NoPutResultSetImpl
+    implements CursorResultSet
+{
+    private final NoPutResultSet leftSource;
+    private final NoPutResultSet rightSource;
+    private final GeneratedMethod closeCleanup;
+    private final Activation activation;
+    private final int opType;
+    private final boolean all;
+    private final int resultSetNumber;
+    private DataValueDescriptor[] prevCols; /* Used to remove duplicates in the EXCEPT DISTINCT case.
+                                             * It is equal to the previously output columns.
+                                             */
+    private int rightDuplicateCount; // Number of duplicates of the current row from the right input
+    private ExecRow leftInputRow;
+    private ExecRow rightInputRow;
+
+    private final int[] intermediateOrderByColumns;
+    private final int[] intermediateOrderByDirection;
+
+    SetOpResultSet( NoPutResultSet leftSource,
+                    NoPutResultSet rightSource,
+                    Activation activation, 
+                    int resultSetNumber,
+                    long optimizerEstimatedRowCount,
+                    double optimizerEstimatedCost,
+                    int opType,
+                    boolean all,
+                    GeneratedMethod closeCleanup,
+                    int intermediateOrderByColumnsSavedObject,
+                    int intermediateOrderByDirectionSavedObject)
+    {
+		super(activation, resultSetNumber, 
+			  optimizerEstimatedRowCount, optimizerEstimatedCost);
+        this.leftSource = leftSource;
+        this.rightSource = rightSource;
+        this.activation = activation;
+        this.resultSetNumber = resultSetNumber;
+        this.opType = opType;
+        this.all = all;
+        this.closeCleanup = closeCleanup;
+        ExecPreparedStatement eps = activation.getPreparedStatement();
+        intermediateOrderByColumns = (int[]) eps.getSavedObject(intermediateOrderByColumnsSavedObject);
+        intermediateOrderByDirection = (int[]) eps.getSavedObject(intermediateOrderByDirectionSavedObject);
+		constructorTime += getElapsedMillis(beginTime);
+    }
+
+	/**
+     * open the first source.
+ 	 *	@exception StandardException thrown on failure
+     */
+	public void	openCore() throws StandardException 
+	{
+		beginTime = getCurrentTimeMillis();
+		if (SanityManager.DEBUG)
+	    	SanityManager.ASSERT( ! isOpen, "SetOpProjectRestrictResultSet already open");
+
+        isOpen = true;
+        leftSource.openCore();
+        rightSource.openCore();
+        rightInputRow = rightSource.getNextRowCore();
+		numOpens++;
+
+		openTime += getElapsedMillis(beginTime);
+	} // end of openCore
+
+	/**
+     * @return the next row of the intersect or except, null if there is none
+ 	 *	@exception StandardException thrown on failure
+	 */
+	public ExecRow	getNextRowCore() throws StandardException
+    {
+		beginTime = getCurrentTimeMillis();
+	    if ( isOpen )
+        {
+            while( (leftInputRow = leftSource.getNextRowCore()) != null)
+            {
+                DataValueDescriptor[] leftColumns = leftInputRow.getRowArray();
+                if( !all)
+                {
+                    if( isDuplicate( leftColumns))
+                        continue; // Get the next left row
+                    prevCols = leftInputRow.getRowArrayClone();
+                }
+                int compare = 0;
+                // Advance the right until there are no more right rows or leftRow &lt;= rightRow
+                while( rightInputRow != null &amp;&amp; (compare = compare( leftColumns, rightInputRow.getRowArray())) &gt; 0)
+                    rightInputRow = rightSource.getNextRowCore();
+                
+                if( rightInputRow == null || compare &lt; 0)
+                {
+                    // The left row is not in the right source.
+                    if( opType == IntersectOrExceptNode.EXCEPT_OP)
+                        // Output this row
+                        break;
+                }
+                else
+                {
+                    // The left and right rows are the same
+                    if( SanityManager.DEBUG)
+                        SanityManager.ASSERT( rightInputRow != null &amp;&amp; compare == 0,
+                                              "Insert/Except execution has gotten confused.");
+                    if( all)
+                        // Just advance the right input by one row.
+                        rightInputRow = rightSource.getNextRowCore();
+                    // If !all then we will skip past duplicates on the left at the top of this loop,
+                    // which will then force us to skip past any right duplicates.
+                    if( opType == IntersectOrExceptNode.INTERSECT_OP)
+                        break; // output this row
+
+                    // opType == IntersectOrExceptNode.EXCEPT_OP
+                    // This row should not be ouput
+                }
+            }
+        }
+        currentRow = leftInputRow;
+        setCurrentRow( currentRow);
+        nextTime += getElapsedMillis(beginTime);
+        return currentRow;
+    } // end of getNextRowCore
+
+    private void advanceRightPastDuplicates( DataValueDescriptor[] leftColumns)
+        throws StandardException
+    {
+        while((rightInputRow = rightSource.getNextRowCore()) != null
+              &amp;&amp; compare( leftColumns, rightInputRow.getRowArray()) == 0)
+            ;
+    } // end of advanceRightPastDuplicates
+        
+    private int compare( DataValueDescriptor[] leftCols, DataValueDescriptor[] rightCols)
+        throws StandardException
+    {
+        for( int i = 0; i &lt; intermediateOrderByColumns.length; i++)
+        {
+            int colIdx = intermediateOrderByColumns[i];
+            if( leftCols[colIdx].compare( Orderable.ORDER_OP_LESSTHAN,
+                                          rightCols[colIdx],
+                                          true, // nulls sort high
+                                          false))
+                return -1 * intermediateOrderByDirection[i];
+            if( ! leftCols[colIdx].compare( Orderable.ORDER_OP_EQUALS,
+                                            rightCols[colIdx],
+                                            true, // nulls sort high
+                                            false))
+                return intermediateOrderByDirection[i];
+        }
+        return 0;
+    } // end of compare
+    
+    private boolean isDuplicate( DataValueDescriptor[] curColumns)
+        throws StandardException
+    {
+        if( prevCols == null)
+            return false;
+        /* Note that intermediateOrderByColumns.length can be less than prevCols.length if we know that a
+         * subset of the columns is a unique key. In that case we only need to look at the unique key.
+         */
+        for( int i = 0; i &lt; intermediateOrderByColumns.length; i++)
+        {
+            int colIdx = intermediateOrderByColumns[i];
+            if( ! curColumns[colIdx].compare( Orderable.ORDER_OP_EQUALS, prevCols[colIdx], true, false))
+                return false;
+        }
+        return true;
+    }
+
+	public ExecRow getCurrentRow()
+    {
+        return currentRow;
+    }
+    
+	/**
+	 * If the result set has been opened,
+	 * close the currently open source.
+	 *
+	 * @exception StandardException thrown on error
+	 */
+	public void	close() throws StandardException
+	{
+		beginTime = getCurrentTimeMillis();
+		if ( isOpen )
+        {
+			if (closeCleanup != null)
+				closeCleanup.invoke(activation); // let activation tidy up
+	    	clearCurrentRow();
+			currentRow = null;
+            prevCols = null;
+            leftSource.close();
+            rightSource.close();
+            super.close();
+        }
+		else
+			if (SanityManager.DEBUG)
+				SanityManager.DEBUG("CloseRepeatInfo","Close of UnionResultSet repeated");
+
+		closeTime += getElapsedMillis(beginTime);
+	} // end of close
+
+	public void	finish() throws StandardException
+	{
+		leftSource.finish();
+		rightSource.finish();
+		finishAndRTS();
+	}
+
+	/**
+	 * Return the total amount of time spent in this ResultSet
+	 *
+	 * @param type	CURRENT_RESULTSET_ONLY - time spent only in this ResultSet
+	 *				ENTIRE_RESULTSET_TREE  - time spent in this ResultSet and below.
+	 *
+	 * @return long		The total amount of time spent (in milliseconds).
+	 */
+	public long getTimeSpent(int type)
+	{
+		long totTime = constructorTime + openTime + nextTime + closeTime;
+
+		if (type == NoPutResultSet.CURRENT_RESULTSET_ONLY)
+		{
+			return	totTime - leftSource.getTimeSpent(ENTIRE_RESULTSET_TREE)
+              - rightSource.getTimeSpent(ENTIRE_RESULTSET_TREE);
+		}
+		else
+		{
+			return totTime;
+		}
+	} // end of getTimeSpent
+
+	/**
+     * @see CursorResultSet
+	 *
+     * @return the row location of the current cursor row.
+     * @exception StandardException thrown on failure
+	 */
+	public RowLocation getRowLocation() throws StandardException
+    {
+        // RESOLVE: What is the row location of an INTERSECT supposed to be: the location from the
+        // left side, the right side, or null?
+        return ((CursorResultSet)leftSource).getRowLocation();
+    }
+}
Index: java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(revision 124163)
+++ java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(working copy)
@@ -185,7 +185,8 @@
 	static final int SUBSTRING_OPERATOR_NODE = 154;
 	// UNUSED static final int BOOLEAN_NODE = 155;
 	static final int DROP_ALIAS_NODE = 156;
-	// 157 - 185 available
+    static final int INTERSECT_OR_EXCEPT_NODE = 157;
+	// 158 - 185 available
 	static final int MODIFY_COLUMN_TYPE_NODE = 186;
 	static final int MODIFY_COLUMN_CONSTRAINT_NODE = 187;
     static final int ABSOLUTE_OPERATOR_NODE = 188;
Index: java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java	(revision 124163)
+++ java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java	(working copy)
@@ -1428,6 +1428,45 @@
 					throws StandardException;
 
 
+    /**
+     * The SetOpResultSet is used to implement an INTERSECT or EXCEPT operation.
+     * It selects rows from two ordered input result sets.
+     *
+     * @param leftSource The result set that implements the left input
+     * @param rightSource The result set that implements the right input
+     * @param activation the activation for this result set
+     * @param resultSetNumber
+     * @param optimizerEstimatedRowCount
+     * @param optimizerEstimatedCost
+     * @param opType IntersectOrExceptNode.INTERSECT_OP or EXCEPT_OP
+     * @param all true if the operation is an INTERSECT ALL or an EXCEPT ALL,
+     *            false if the operation is an INTERSECT DISCTINCT or an EXCEPT DISCTINCT
+     * @param closeCleanup a method to be called by close
+     * @param intermediateOrderByColumnsSavedObject The saved object index for the array of order by columns for the
+     *        ordering of the left and right sources. That is, both the left and right sources have an order by
+     *        clause of the form ORDER BY intermediateOrderByColumns[0],intermediateOrderByColumns[1],...
+     * @param intermediateOrderByDirectionSavedObject The saved object index for the array of source
+     *        order by directions. That is, the ordering of the i'th order by column in the input is ascending
+     *        if intermediateOrderByDirection[i] is 1, descending if intermediateOrderByDirection[i] is -1.
+	 *
+	 * @return	A ResultSet from which the caller can get the INTERSECT or EXCEPT
+	 *
+	 * @exception StandardException		Thrown on failure
+	 */
+    NoPutResultSet getSetOpResultSet( NoPutResultSet leftSource,
+                                      NoPutResultSet rightSource,
+                                      Activation activation, 
+                                      int resultSetNumber,
+                                      long optimizerEstimatedRowCount,
+                                      double optimizerEstimatedCost,
+                                      int opType,
+                                      boolean all,
+                                      GeneratedMethod closeCleanup,
+                                      int intermediateOrderByColumnsSavedObject,
+                                      int intermediateOrderByDirectionSavedObject)
+        throws StandardException;
+                                                     
+                                                     
 	//
 	// Misc operations
 	//
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties	(revision 124163)
+++ java/engine/org/apache/derby/loc/messages_en.properties	(working copy)
@@ -469,10 +469,10 @@
 42X55=Table name ''{1}'' should be the same as ''{0}''.
 42X56=The number of columns in the view column list does not match the number of columns in the underlying query expression in the view definition for ''{0}''.
 42X57=The getColumnCount() for external virtual table ''{0}'' returned an invalid value ''{1}''.  Valid values are &gt;= 1.
-42X58=The number of columns on the left and right sides of the UNION must be the same.
+42X58=The number of columns on the left and right sides of the {0} must be the same.
 42X59=The number of columns in each VALUES constructor must be the same.
 42X60=Invalid value ''{0}'' for insertMode property specified for table ''{1}''.
-42X61=Types ''{0}'' and ''{1}'' are not UNION compatible.
+42X61=Types ''{0}'' and ''{1}'' are not {2} compatible.
 42X62=''{0}'' is not allowed in the ''{1}'' schema.
 42X63=The USING clause did not return any results, no parameters can be set.
 42X64=Invalid value ''{0}'' specified for useStatistics property in the Properties list. TRUE or FALSE are the only valid values.
@@ -919,7 +919,7 @@
 X0X61.S=The values for column ''{4}'' in index ''{0}'' and table ''{1}.{2}'' do not match for row location {3}.  The value in the index is ''{5}'', while the value in the base table is ''{6}''.  The full index key, including the row location, is ''{7}''.  The suggested corrective action is to recreate the index.
 X0X62.S=Inconsistency found between table ''{0}'' and index ''{1}''.  Error when trying to retrieve row location ''{2}'' from the table.  The full index key, including the row location, is ''{3}''. The suggested corrective action is to recreate the index.
 X0X63.S=Got IOException ''{0}''.
-X0X67.S=Columns of type ''{0}'' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+X0X67.S=Columns of type ''{0}'' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 X0X81.S={0} ''{1}'' does not exist.
 X0X85.S=Index ''{0}'' was not created because ''{1}'' is not a valid index type.
 X0X86.S=0 is an invalid parameter value for ResultSet.absolute(int row).
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql	(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql	(revision 0)
@@ -0,0 +1,141 @@
+create table t1( id integer not null primary key, i1 integer, i2 integer, c10 char(10), c30 char(30), tm time);
+create table t2( id integer not null primary key, i1 integer, i2 integer, vc20 varchar(20), d double, dt date);
+insert into t1(id,i1,i2,c10,c30) values
+  (1,1,1,'a','123456789012345678901234567890'),
+  (2,1,2,'a','bb'),
+  (3,1,3,'b','bb'),
+  (4,1,3,'zz','5'),
+  (5,null,null,null,'1.0'),
+  (6,null,null,null,'a');
+insert into t2(id,i1,i2,vc20,d) values
+  (1,1,1,'a',1.0),
+  (2,1,2,'a',1.1),
+  (5,null,null,'12345678901234567890',3),
+  (100,1,3,'zz',3),
+  (101,1,2,'bb',null),
+  (102,5,5,'',null),
+  (103,1,3,' a',null),
+  (104,1,3,'null',7.4);
+
+-- no duplicates
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by id DESC,i1,i2;
+select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3;
+
+-- Only specify order by on some columns
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by i2, id DESC;
+select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 3 DESC, 1;
+
+-- duplicates
+select i1,i2 from t1 intersect select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2;
+
+-- right side is empty
+select i1,i2 from t1 intersect select i1,i2 from t2 where id = -1;
+select i1,i2 from t1 intersect all select i1,i2 from t2 where id = -1;
+
+-- left side is empty
+select i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2;
+
+-- check precedence
+select i1,i2 from t1 intersect all select i1,i2 from t2 intersect values(5,5),(1,3) order by 1,2;
+(select i1,i2 from t1 intersect all select i1,i2 from t2) intersect values(5,5),(1,3) order by 1,2;
+
+values(-1,-1,-1) union select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by 1,2,3;
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 union values(-1,-1,-1) order by 1,2,3;
+
+-- check conversions
+select c10 from t1 intersect select vc20 from t2 order by 1;
+select c30 from t1 intersect select vc20 from t2;
+select c30 from t1 intersect all select vc20 from t2;
+
+-- check insert intersect into table and intersect without order by
+create table r( i1 integer, i2 integer);
+insert into r select i1,i2 from t1 intersect select i1,i2 from t2;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+insert into r select i1,i2 from t1 intersect all select i1,i2 from t2;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+-- test LOB
+create table t3( i1 integer, cl clob(64), bl blob(1M));
+insert into t3 values
+  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+create table t4( i1 integer, cl clob(64), bl blob(1M));
+insert into t4 values
+  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+
+select cl from t3 intersect select cl from t4 order by 1;
+
+select bl from t3 intersect select bl from t4 order by 1;
+
+-- invalid conversion
+select tm from t1 intersect select dt from t2;
+select c30 from t1 intersect select d from t2;
+
+-- different number of columns
+select i1 from t1 intersect select i1,i2 from t2;
+
+-- ? in select list of intersect
+select ? from t1 intersect select i1 from t2;
+select i1 from t1 intersect select ? from t2;
+
+-- except tests
+select id,i1,i2 from t1 except select id,i1,i2 from t2 order by id,i1,i2;
+select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3;
+select id,i1,i2 from t2 except select id,i1,i2 from t1 order by 1,2,3;
+select id,i1,i2 from t2 except all select id,i1,i2 from t1 order by 1,2,3;
+
+select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;
+select i1,i2 from t2 except select i1,i2 from t1 order by 1,2;
+select i1,i2 from t2 except all select i1,i2 from t1 order by 1,2;
+
+-- right side is empty
+select i1,i2 from t1 except select i1,i2 from t2 where id = -1 order by 1,2;
+select i1,i2 from t1 except all select i1,i2 from t2 where id = -1  order by 1,2;
+
+-- left side is empty
+select i1,i2 from t1 where id = -1 except select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 where id = -1 except all select i1,i2 from t2 order by 1,2;
+
+-- Check precedence. Union and except have the same precedence. Intersect has higher precedence.
+select i1,i2 from t1 except select i1,i2 from t2 intersect values(-1,-1) order by 1,2;
+select i1,i2 from t1 except (select i1,i2 from t2 intersect values(-1,-1)) order by 1,2;
+select i1,i2 from t2 except select i1,i2 from t1 union values(5,5) order by 1,2;
+(select i1,i2 from t2 except select i1,i2 from t1) union values(5,5) order by 1,2;
+select i1,i2 from t2 except all select i1,i2 from t1 except select i1,i2 from t1 where id = 3 order by 1,2;
+(select i1,i2 from t2 except all select i1,i2 from t1) except select i1,i2 from t1 where id = 3 order by 1,2;
+
+-- check conversions
+select c10 from t1 except select vc20 from t2 order by 1;
+select c30 from t1 except select vc20 from t2 order by 1;
+select c30 from t1 except all select vc20 from t2;
+
+-- check insert except into table and except without order by
+insert into r select i1,i2 from t2 except select i1,i2 from t1;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+insert into r select i1,i2 from t2 except all select i1,i2 from t1;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+-- test LOB
+select cl from t3 except select cl from t4 order by 1;
+select bl from t3 except select bl from t4 order by 1;
+
+-- invalid conversion
+select tm from t1 except select dt from t2;
+select c30 from t1 except select d from t2;
+
+-- different number of columns
+select i1 from t1 except select i1,i2 from t2;
+
+-- ? in select list of except
+select ? from t1 except select i1 from t2;
+
+-- Invalid order by
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1;
+select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant	(revision 124163)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant	(working copy)
@@ -95,6 +95,7 @@
 innerjoin.sql
 insert.sql
 insert_sed.properties
+intersect.sql
 isolationLevels.sql
 joinDeadlock.sql
 joinDeadlock.sql1
Index: java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out	(revision 124163)
+++ java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out	(working copy)
@@ -45,7 +45,7 @@
 create table unmapped(c1 long varchar);
 0 rows inserted/updated/deleted
 ij&gt; select c1, max(1) from unmapped group by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- clean up
 drop table t1;
 0 rows inserted/updated/deleted
Index: java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/LOB.out	(revision 124163)
+++ java/testing/org/apache/derbyTesting/functionTests/master/LOB.out	(working copy)
@@ -266,13 +266,13 @@
 0 rows inserted/updated/deleted
 ij&gt; -- create index (not allowed)
 create index ia on a(a);
-ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; create index ib on b(a);
-ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; create index ic on c(a);
 ERROR 42Y55: 'CREATE INDEX' cannot be performed on 'C' because it does not exist.
 ij&gt; create index id on d(a);
-ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- cleanup
 drop table a;
 0 rows inserted/updated/deleted
@@ -535,7 +535,7 @@
 1 row inserted/updated/deleted
 ij&gt; -- UNION
 select * from testPredicate1 union select * from testPredicate2;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- IN predicate
 select c1 from testPredicate1 where c1 IN (select c1 from testPredicate2);
 ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
@@ -544,10 +544,10 @@
 ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
 ij&gt; -- ORDER BY clause
 select * from testPredicate1 order by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- GROUP BY clause
 select substr(c1,1,2) from testPredicate1 group by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- JOIN
 select * from testPredicate1 t1, testPredicate2 t2 where t1.c1=t2.c1;
 ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
@@ -555,15 +555,15 @@
 ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported.
 ij&gt; -- PRIMARY KEY
 create table testConst1(c1 long varchar not null primary key);
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- UNIQUE KEY constraints
 CREATE TABLE testconst2 (col1 long varchar not null, CONSTRAINT uk UNIQUE (col1));
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; -- FOREIGN KEY constraints
 create table testConst3 (c1 char(10) not null, primary key (c1));
 0 rows inserted/updated/deleted
 ij&gt; create table testConst4 (c1 long varchar not null, constraint fk foreign key (c1) references testConst3 (c1));
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
 ij&gt; drop table testConst3;
 0 rows inserted/updated/deleted
 ij&gt; -- MAX aggregate function
Index: java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/intersect.out	(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/master/intersect.out	(revision 0)
@@ -0,0 +1,331 @@
+ij&gt; create table t1( id integer not null primary key, i1 integer, i2 integer, c10 char(10), c30 char(30), tm time);
+0 rows inserted/updated/deleted
+ij&gt; create table t2( id integer not null primary key, i1 integer, i2 integer, vc20 varchar(20), d double, dt date);
+0 rows inserted/updated/deleted
+ij&gt; insert into t1(id,i1,i2,c10,c30) values
+  (1,1,1,'a','123456789012345678901234567890'),
+  (2,1,2,'a','bb'),
+  (3,1,3,'b','bb'),
+  (4,1,3,'zz','5'),
+  (5,null,null,null,'1.0'),
+  (6,null,null,null,'a');
+6 rows inserted/updated/deleted
+ij&gt; insert into t2(id,i1,i2,vc20,d) values
+  (1,1,1,'a',1.0),
+  (2,1,2,'a',1.1),
+  (5,null,null,'12345678901234567890',3),
+  (100,1,3,'zz',3),
+  (101,1,2,'bb',null),
+  (102,5,5,'',null),
+  (103,1,3,' a',null),
+  (104,1,3,'null',7.4);
+8 rows inserted/updated/deleted
+ij&gt; -- no duplicates
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by id DESC,i1,i2;
+ID         |I1         |I2         
+-----------------------------------
+5          |NULL       |NULL       
+2          |1          |2          
+1          |1          |1          
+ij&gt; select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+1          |1          |1          
+2          |1          |2          
+5          |NULL       |NULL       
+ij&gt; -- Only specify order by on some columns
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by i2, id DESC;
+ID         |I1         |I2         
+-----------------------------------
+1          |1          |1          
+2          |1          |2          
+5          |NULL       |NULL       
+ij&gt; select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 3 DESC, 1;
+ID         |I1         |I2         
+-----------------------------------
+5          |NULL       |NULL       
+2          |1          |2          
+1          |1          |1          
+ij&gt; -- duplicates
+select i1,i2 from t1 intersect select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
+ij&gt; select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+1          |3          
+NULL       |NULL       
+ij&gt; -- right side is empty
+select i1,i2 from t1 intersect select i1,i2 from t2 where id = -1;
+I1         |I2         
+-----------------------
+ij&gt; select i1,i2 from t1 intersect all select i1,i2 from t2 where id = -1;
+I1         |I2         
+-----------------------
+ij&gt; -- left side is empty
+select i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2;
+I1         |I2         
+-----------------------
+ij&gt; -- check precedence
+select i1,i2 from t1 intersect all select i1,i2 from t2 intersect values(5,5),(1,3) order by 1,2;
+1          |2          
+-----------------------
+1          |3          
+ij&gt; (select i1,i2 from t1 intersect all select i1,i2 from t2) intersect values(5,5),(1,3) order by 1,2;
+1          |2          
+-----------------------
+1          |3          
+ij&gt; values(-1,-1,-1) union select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by 1,2,3;
+1          |2          |3          
+-----------------------------------
+-1         |-1         |-1         
+1          |1          |1          
+2          |1          |2          
+5          |NULL       |NULL       
+ij&gt; select id,i1,i2 from t1 intersect select id,i1,i2 from t2 union values(-1,-1,-1) order by 1,2,3;
+1          |2          |3          
+-----------------------------------
+-1         |-1         |-1         
+1          |1          |1          
+2          |1          |2          
+5          |NULL       |NULL       
+ij&gt; -- check conversions
+select c10 from t1 intersect select vc20 from t2 order by 1;
+1                   
+--------------------
+a                   
+zz                  
+ij&gt; select c30 from t1 intersect select vc20 from t2;
+1                             
+------------------------------
+a                             
+bb                            
+ij&gt; select c30 from t1 intersect all select vc20 from t2;
+1                             
+------------------------------
+a                             
+bb                            
+ij&gt; -- check insert intersect into table and intersect without order by
+create table r( i1 integer, i2 integer);
+0 rows inserted/updated/deleted
+ij&gt; insert into r select i1,i2 from t1 intersect select i1,i2 from t2;
+4 rows inserted/updated/deleted
+ij&gt; select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
+ij&gt; delete from r;
+4 rows inserted/updated/deleted
+ij&gt; insert into r select i1,i2 from t1 intersect all select i1,i2 from t2;
+5 rows inserted/updated/deleted
+ij&gt; select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+1          |3          
+NULL       |NULL       
+ij&gt; delete from r;
+5 rows inserted/updated/deleted
+ij&gt; -- test LOB
+create table t3( i1 integer, cl clob(64), bl blob(1M));
+0 rows inserted/updated/deleted
+ij&gt; insert into t3 values
+  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+1 row inserted/updated/deleted
+ij&gt; create table t4( i1 integer, cl clob(64), bl blob(1M));
+0 rows inserted/updated/deleted
+ij&gt; insert into t4 values
+  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+1 row inserted/updated/deleted
+ij&gt; select cl from t3 intersect select cl from t4 order by 1;
+ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
+ij&gt; select bl from t3 intersect select bl from t4 order by 1;
+ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
+ij&gt; -- invalid conversion
+select tm from t1 intersect select dt from t2;
+ERROR 42X61: Types 'TIME' and 'DATE' are not INTERSECT compatible.
+ij&gt; select c30 from t1 intersect select d from t2;
+ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not INTERSECT compatible.
+ij&gt; -- different number of columns
+select i1 from t1 intersect select i1,i2 from t2;
+ERROR 42X58: The number of columns on the left and right sides of the INTERSECT must be the same.
+ij&gt; -- ? in select list of intersect
+select ? from t1 intersect select i1 from t2;
+ERROR 42X34: There is a ? parameter in the select list.  This is not allowed.
+ij&gt; select i1 from t1 intersect select ? from t2;
+ERROR 42X34: There is a ? parameter in the select list.  This is not allowed.
+ij&gt; -- except tests
+select id,i1,i2 from t1 except select id,i1,i2 from t2 order by id,i1,i2;
+ID         |I1         |I2         
+-----------------------------------
+3          |1          |3          
+4          |1          |3          
+6          |NULL       |NULL       
+ij&gt; select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3;
+ID         |I1         |I2         
+-----------------------------------
+6          |NULL       |NULL       
+4          |1          |3          
+3          |1          |3          
+ij&gt; select id,i1,i2 from t2 except select id,i1,i2 from t1 order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+100        |1          |3          
+101        |1          |2          
+102        |5          |5          
+103        |1          |3          
+104        |1          |3          
+ij&gt; select id,i1,i2 from t2 except all select id,i1,i2 from t1 order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+100        |1          |3          
+101        |1          |2          
+102        |5          |5          
+103        |1          |3          
+104        |1          |3          
+ij&gt; select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+ij&gt; select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+NULL       |NULL       
+ij&gt; select i1,i2 from t2 except select i1,i2 from t1 order by 1,2;
+I1         |I2         
+-----------------------
+5          |5          
+ij&gt; select i1,i2 from t2 except all select i1,i2 from t1 order by 1,2;
+I1         |I2         
+-----------------------
+1          |2          
+1          |3          
+5          |5          
+ij&gt; -- right side is empty
+select i1,i2 from t1 except select i1,i2 from t2 where id = -1 order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
+ij&gt; select i1,i2 from t1 except all select i1,i2 from t2 where id = -1  order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+1          |3          
+NULL       |NULL       
+NULL       |NULL       
+ij&gt; -- left side is empty
+select i1,i2 from t1 where id = -1 except select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+ij&gt; select i1,i2 from t1 where id = -1 except all select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+ij&gt; -- Check precedence. Union and except have the same precedence. Intersect has higher precedence.
+select i1,i2 from t1 except select i1,i2 from t2 intersect values(-1,-1) order by 1,2;
+1          |2          
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
+ij&gt; select i1,i2 from t1 except (select i1,i2 from t2 intersect values(-1,-1)) order by 1,2;
+1          |2          
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
+ij&gt; select i1,i2 from t2 except select i1,i2 from t1 union values(5,5) order by 1,2;
+1          |2          
+-----------------------
+5          |5          
+ij&gt; (select i1,i2 from t2 except select i1,i2 from t1) union values(5,5) order by 1,2;
+1          |2          
+-----------------------
+5          |5          
+ij&gt; select i1,i2 from t2 except all select i1,i2 from t1 except select i1,i2 from t1 where id = 3 order by 1,2;
+I1         |I2         
+-----------------------
+1          |2          
+5          |5          
+ij&gt; (select i1,i2 from t2 except all select i1,i2 from t1) except select i1,i2 from t1 where id = 3 order by 1,2;
+I1         |I2         
+-----------------------
+1          |2          
+5          |5          
+ij&gt; -- check conversions
+select c10 from t1 except select vc20 from t2 order by 1;
+1                   
+--------------------
+b                   
+NULL                
+ij&gt; select c30 from t1 except select vc20 from t2 order by 1;
+1                             
+------------------------------
+1.0                           
+123456789012345678901234567890
+5                             
+ij&gt; select c30 from t1 except all select vc20 from t2;
+1                             
+------------------------------
+1.0                           
+123456789012345678901234567890
+5                             
+bb                            
+ij&gt; -- check insert except into table and except without order by
+insert into r select i1,i2 from t2 except select i1,i2 from t1;
+1 row inserted/updated/deleted
+ij&gt; select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+5          |5          
+ij&gt; delete from r;
+1 row inserted/updated/deleted
+ij&gt; insert into r select i1,i2 from t2 except all select i1,i2 from t1;
+3 rows inserted/updated/deleted
+ij&gt; select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+1          |2          
+1          |3          
+5          |5          
+ij&gt; delete from r;
+3 rows inserted/updated/deleted
+ij&gt; -- test LOB
+select cl from t3 except select cl from t4 order by 1;
+ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
+ij&gt; select bl from t3 except select bl from t4 order by 1;
+ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not supported for that type.
+ij&gt; -- invalid conversion
+select tm from t1 except select dt from t2;
+ERROR 42X61: Types 'TIME' and 'DATE' are not EXCEPT compatible.
+ij&gt; select c30 from t1 except select d from t2;
+ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not EXCEPT compatible.
+ij&gt; -- different number of columns
+select i1 from t1 except select i1,i2 from t2;
+ERROR 42X58: The number of columns on the left and right sides of the EXCEPT must be the same.
+ij&gt; -- ? in select list of except
+select ? from t1 except select i1 from t2;
+ERROR 42X34: There is a ? parameter in the select list.  This is not allowed.
+ij&gt; -- Invalid order by
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1;
+ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause.
+ij&gt; select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
+ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause.
+ij&gt; 
Index: java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(revision 124163)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(working copy)
@@ -67,6 +67,7 @@
 lang/infostreams.sql
 lang/innerjoin.sql
 lang/insert.sql
+lang/intersect.sql
 lang/isolationLevels.sql
 lang/joinDeadlock.sql
 lang/joins.sql
  </pre>
</blockquote>
</body>
</html>

Mime
View raw message