You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/01/07 02:06:44 UTC
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
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;