db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r124468 - in incubator/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile engine/org/apache/derby/impl/sql/execute testing/org/apache/derbyTesting/functionTests/master testing/org/apache/derbyTesting/functionTests/tests/lang
Date Fri, 07 Jan 2005 01:06:44 GMT
Author: bandaram
Date: Thu Jan  6 17:06:42 2005
New Revision: 124468

URL: http://svn.apache.org/viewcvs?view=rev&rev=124468
Log:
Submit new files that are part of enhancing Derby by adding INTERSECT and EXCEPT.

Submitted by Submitted by Jack Klebanoff (klebanof@Mutagen.Net)

Added:
   incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
   incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
   incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql

Added: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java?view=auto&rev=124468
==============================================================================
--- (empty file)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java	Thu Jan  6 17:06:42 2005
@@ -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
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package	org.apache.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 <key columns>, <other expressions> from T" where there is a unique index on <key columns>
+        // 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 < 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 < 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 < 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 < intermediateOrderByColumns.length; i++)
+        {
+            OrderByColumn orderByColumn = (OrderByColumn)
+              nf.getNode( C_NodeTypes.ORDER_BY_COLUMN,
+                          ReuseFactory.getInteger( intermediateOrderByColumns[i] + 1),
+                          cm);
+            if( intermediateOrderByDirection[i] < 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);
+    }
+}

Added: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?view=auto&rev=124468
==============================================================================
--- (empty file)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java	Thu Jan  6 17:06:42 2005
@@ -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
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package	org.apache.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 < 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 < 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) && orderByList != null && 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 -> missing in current RCL
+	 *								   colmap[i] = j -> targetRCL(i) <-> 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 < 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();
+}

Added: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java?view=auto&rev=124468
==============================================================================
--- (empty file)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java	Thu Jan  6 17:06:42 2005
@@ -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
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.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 <= rightRow
+                while( rightInputRow != null && (compare = compare( leftColumns, rightInputRow.getRowArray())) > 0)
+                    rightInputRow = rightSource.getNextRowCore();
+                
+                if( rightInputRow == null || compare < 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 && 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
+              && compare( leftColumns, rightInputRow.getRowArray()) == 0)
+            ;
+    } // end of advanceRightPastDuplicates
+        
+    private int compare( DataValueDescriptor[] leftCols, DataValueDescriptor[] rightCols)
+        throws StandardException
+    {
+        for( int i = 0; i < 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 < 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();
+    }
+}

Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out?view=auto&rev=124468
==============================================================================
--- (empty file)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out	Thu Jan  6 17:06:42 2005
@@ -0,0 +1,331 @@
+ij> 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> 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> 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> 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> -- 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> 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> -- 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> 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> -- 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> 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> -- right side is empty
+select i1,i2 from t1 intersect select i1,i2 from t2 where id = -1;
+I1         |I2         
+-----------------------
+ij> select i1,i2 from t1 intersect all select i1,i2 from t2 where id = -1;
+I1         |I2         
+-----------------------
+ij> -- left side is empty
+select i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2;
+I1         |I2         
+-----------------------
+ij> -- 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> (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> 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> 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> -- check conversions
+select c10 from t1 intersect select vc20 from t2 order by 1;
+1                   
+--------------------
+a                   
+zz                  
+ij> select c30 from t1 intersect select vc20 from t2;
+1                             
+------------------------------
+a                             
+bb                            
+ij> select c30 from t1 intersect all select vc20 from t2;
+1                             
+------------------------------
+a                             
+bb                            
+ij> -- check insert intersect into table and intersect without order by
+create table r( i1 integer, i2 integer);
+0 rows inserted/updated/deleted
+ij> insert into r select i1,i2 from t1 intersect select i1,i2 from t2;
+4 rows inserted/updated/deleted
+ij> select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
+ij> delete from r;
+4 rows inserted/updated/deleted
+ij> insert into r select i1,i2 from t1 intersect all select i1,i2 from t2;
+5 rows inserted/updated/deleted
+ij> select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+1          |3          
+NULL       |NULL       
+ij> delete from r;
+5 rows inserted/updated/deleted
+ij> -- test LOB
+create table t3( i1 integer, cl clob(64), bl blob(1M));
+0 rows inserted/updated/deleted
+ij> insert into t3 values
+  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+1 row inserted/updated/deleted
+ij> create table t4( i1 integer, cl clob(64), bl blob(1M));
+0 rows inserted/updated/deleted
+ij> insert into t4 values
+  (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+1 row inserted/updated/deleted
+ij> 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> 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> -- invalid conversion
+select tm from t1 intersect select dt from t2;
+ERROR 42X61: Types 'TIME' and 'DATE' are not INTERSECT compatible.
+ij> select c30 from t1 intersect select d from t2;
+ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not INTERSECT compatible.
+ij> -- 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> -- ? 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> select i1 from t1 intersect select ? from t2;
+ERROR 42X34: There is a ? parameter in the select list.  This is not allowed.
+ij> -- 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> 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> 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> 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> select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+ij> select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+NULL       |NULL       
+ij> select i1,i2 from t2 except select i1,i2 from t1 order by 1,2;
+I1         |I2         
+-----------------------
+5          |5          
+ij> 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> -- 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> 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> -- 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> select i1,i2 from t1 where id = -1 except all select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+ij> -- 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> 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> select i1,i2 from t2 except select i1,i2 from t1 union values(5,5) order by 1,2;
+1          |2          
+-----------------------
+5          |5          
+ij> (select i1,i2 from t2 except select i1,i2 from t1) union values(5,5) order by 1,2;
+1          |2          
+-----------------------
+5          |5          
+ij> 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> (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> -- check conversions
+select c10 from t1 except select vc20 from t2 order by 1;
+1                   
+--------------------
+b                   
+NULL                
+ij> select c30 from t1 except select vc20 from t2 order by 1;
+1                             
+------------------------------
+1.0                           
+123456789012345678901234567890
+5                             
+ij> select c30 from t1 except all select vc20 from t2;
+1                             
+------------------------------
+1.0                           
+123456789012345678901234567890
+5                             
+bb                            
+ij> -- 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> select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+5          |5          
+ij> delete from r;
+1 row inserted/updated/deleted
+ij> insert into r select i1,i2 from t2 except all select i1,i2 from t1;
+3 rows inserted/updated/deleted
+ij> select i1,i2 from r order by 1,2;
+I1         |I2         
+-----------------------
+1          |2          
+1          |3          
+5          |5          
+ij> delete from r;
+3 rows inserted/updated/deleted
+ij> -- 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> 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> -- invalid conversion
+select tm from t1 except select dt from t2;
+ERROR 42X61: Types 'TIME' and 'DATE' are not EXCEPT compatible.
+ij> select c30 from t1 except select d from t2;
+ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not EXCEPT compatible.
+ij> -- 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> -- ? 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> -- 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> 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> 

Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql?view=auto&rev=124468
==============================================================================
--- (empty file)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql	Thu Jan  6 17:06:42 2005
@@ -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;

Mime
View raw message