1. It includes the fix for order by syntax checking posted by Satheesh.
2. It fixes the execution of an order by clause in the statement. Previously an order by clause was ignored.
3. The intersect/except is still implemented by sorting its two inputs, but optimizer is given a chance to perform sort avoidance on the inputs.
4. If there is an order by clause on the output of the intersect/except then that ordering is used for the inputs, avoiding a separate sort on the intersect/except output.
5. Test cases were added to cover the above code.
6. The copyright notices for the new files were changed to 2004.
7. The SetOpProjectRestrictResultSet class was renamed to SetOpResultSet, which is more appropriate.
8. The IntersectNode and ExceptNode classes were removed and subsumed in the IntersectOrExceptNode class.
Some of the concerns about optimization were not entirely addressed.
There is still just the one execution strategy: sort the two inputs and scan them together. I did not implement other strategies and an optimizer that picks the best of them. I think that my implementation strategy performs decently in all cases, and is the best in many cases. I don't think that it is wise to write a lot of code to optimize an operation that is probably only used infrequently. (Cloudscape customers have gotten along without it for all these years).
While this update allows the optimizer to avoid sorting the intersect/except inputs it does not try to pick an ordering that is more likely to avoid a sort. For instance, suppose one of the inputs is a single table select that selects the columns of a unique key. Then you only have to order that input on the key columns. Depending on the where clause the optimizer might decide to use the unique key index to traverse the table in sort order, avoiding a separate sort. The other input to the intersect/except may be a different story. Those same columns may not specify a unique key in the other input, in which case that input must be ordered on more columns.
Unfortunately the Derby compiler architecture does not make it easy for the IntersectOrExceptNode class to determine a column ordering that is likely to avoid a sort on its inputs. Its inputs are represented as ResultSetNodes. It is not easy to determine a ResultSetNode represents a single table select, and if so whether the selected columns contain a unique key. One would like to try avoiding a sort on the larger input, and then try a column ordering that might avoid a sort on the smaller input if the optimizer cannot avoid sorting the larger input. Unfortunately this does not work: the architecture requires that the order by clause be pushed down by the start of optimization.
It is easy to see if the output of the intersect/except must be ordered and use this ordering to order the inputs, avoiding a separate sort on the output of the intersect/except. I did this in the attached patch update.
Jack
Jack Klebanoff wrote:
Attached is a patch that implements the SQL INTERSECT and EXCEPT operators. The INTERSECT operator constructs the intersection of two tables. The EXCEPT operator finds all rows in one table but not in the other. The syntax is (roughly):
<query expression> INTERSECT [ALL] <query expression> <query expression> EXCEPT [ALL] <query expression>
By default these operators remove duplicates, which can occur if there are duplicates in the inputs. If ALL is specified then duplicates are not returned. If t1 has m copies of row R and t2 has n copies then t1 INTERSECT ALL t2 returns min(m,n) copies of R, and t1 EXCEPT ALL t2 returns max( 0, m-n) copies of R.
The EXCEPT operator has the same precedence as UNION. INTERSECT has higher precedence.
This follows the SQL-92 spec. (At least it follows my understanding of the spec. Spec lawyers are invited to comment).
The implementation uses sorting. The two input tables are sorted and then scanned together. The appropriate rows from the left input are output.
The compiler binds INTERSECT and EXCEPT like UNION. Therefore a new class, org.apache.derby.impl.sql.compile.SetOperatorNode, was carved out of UnionNode. It mainly contains bind methods. Classes UnionNode and IntersectOrExceptNode extend SetOperatorNode. Classes IntersectNode and ExceptNode extend IntersectOrExceptNode. IntersectOrExceptNode does most of the optimization and code generation work. It puts OrderBy nodes in front of its inputs.
The generated code creates a SetOpProjectRestrictResultSet that reads its sorted inputs to produce the required output table.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(working copy)
@@ -512,6 +512,9 @@
case C_NodeTypes.UNION_NODE:
return C_NodeNames.UNION_NODE_NAME;
+ case C_NodeTypes.INTERSECT_OR_EXCEPT_NODE:
+ return C_NodeNames.INTERSECT_OR_EXCEPT_NODE_NAME;
+
case C_NodeTypes.CREATE_TRIGGER_NODE:
return C_NodeNames.CREATE_TRIGGER_NODE_NAME;
Index: java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
(working copy)
@@ -258,6 +258,8 @@
static final String UNION_NODE_NAME =
"org.apache.derby.impl.sql.compile.UnionNode";
+ static final String INTERSECT_OR_EXCEPT_NODE_NAME =
"org.apache.derby.impl.sql.compile.IntersectOrExceptNode";
+
static final String UNTYPED_NULL_CONSTANT_NODE_NAME =
"org.apache.derby.impl.sql.compile.UntypedNullConstantNode";
static final String UPDATE_NODE_NAME =
"org.apache.derby.impl.sql.compile.UpdateNode";
Index: java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
(working copy)
@@ -20,15 +20,12 @@
package org.apache.derby.impl.sql.compile;
-import org.apache.derby.iapi.services.context.ContextManager;
-
import org.apache.derby.iapi.services.compiler.MethodBuilder;
import org.apache.derby.iapi.services.sanity.SanityManager;
import org.apache.derby.iapi.error.StandardException;
-import org.apache.derby.iapi.sql.compile.CompilerContext;
import org.apache.derby.iapi.sql.compile.Optimizable;
import org.apache.derby.iapi.sql.compile.OptimizablePredicate;
import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
@@ -37,33 +34,16 @@
import org.apache.derby.iapi.sql.compile.RowOrdering;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
-import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
-import org.apache.derby.iapi.sql.dictionary.DefaultDescriptor;
-import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
-import org.apache.derby.iapi.types.DataTypeDescriptor;
-
import org.apache.derby.iapi.reference.SQLState;
import org.apache.derby.iapi.reference.ClassName;
-import org.apache.derby.iapi.sql.Activation;
-import org.apache.derby.iapi.types.DataTypeDescriptor;
-import org.apache.derby.iapi.sql.ResultSet;
-import org.apache.derby.iapi.sql.Row;
-
-import org.apache.derby.iapi.types.TypeId;
-
import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
import org.apache.derby.iapi.util.JBitSet;
import org.apache.derby.iapi.services.classfile.VMOpcode;
-import org.apache.derby.catalog.types.DefaultInfoImpl;
-
-import java.util.Properties;
-
/**
* A UnionNode represents a UNION in a DML statement. It contains a boolean
* telling whether the union operation should eliminate duplicate rows.
@@ -71,155 +51,12 @@
* @author Jeff Lichtman
*/
-public class UnionNode extends TableOperatorNode
+public class UnionNode extends SetOperatorNode
{
- /**
- ** Tells whether to eliminate duplicate rows. all == TRUE means do
- ** not eliminate duplicates, all == FALSE means eliminate duplicates.
- */
- boolean all;
-
- /* Is this a UNION ALL generated for a table constructor. */
- boolean tableConstructor;
-
- /* True if this is the top node of a table constructor */
- boolean topTableConstructor;
-
- /* Only optimize a UNION once */
+ /* Only optimize it once */
/* Only call addNewNodes() once */
private boolean addNewNodesCalled;
- private OrderByList orderByList;
-
- /**
- * Initializer for a UnionNode.
- *
- * @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 a UNION
ALL.
- * @param tableConstructor Whether or not this is from a table
constructor.
- * @param tableProperties Properties list associated with the
table
- *
- * @exception StandardException Thrown on error
- */
-
- public void init(
- Object leftResult,
- Object rightResult,
- Object all,
- Object tableConstructor,
- Object tableProperties)
- throws StandardException
- {
- super.init(leftResult, rightResult, tableProperties);
-
- this.all = ((Boolean) all).booleanValue();
-
- /* Is this a UNION ALL for a table constructor? */
- this.tableConstructor = ((Boolean)
tableConstructor).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();
- }
-
- /**
- * Mark this as the top node of a table constructor.
- */
- public void markTopTableConstructor()
- {
- topTableConstructor = true;
- }
-
- /**
- * Tell whether this is a UNION for a table constructor.
- */
- boolean tableConstructor()
- {
- return tableConstructor;
- }
-
- /**
- * Check for (and reject) ? parameters directly under the ResultColumns.
- * This is done for SELECT statements. Don't reject parameters that
- * are in a table constructor - these are allowed, as long as the
- * table constructor is in an INSERT statement or each column of the
- * table constructor has at least one non-? column. The latter case
- * is checked below, in bindExpressions().
- *
- * @return Nothing
- *
- * @exception StandardException Thrown if a ? parameter found
- *
directly under a ResultColumn
- */
- public void rejectParameters() throws StandardException
- {
- if ( ! tableConstructor())
- super.rejectParameters();
- }
-
- /**
- * Set the type of column in the result column lists of each
- * source of this union tree to the type in the given result column list
- * (which represents the result columns for an insert).
- * This is only for table constructors that appear in insert statements.
- *
- * @param typeColumns The ResultColumnList containing the desired
result
- * types.
- *
- * @exception StandardException Thrown on error
- */
- void setTableConstructorTypes(ResultColumnList typeColumns)
- throws StandardException
- {
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(resultColumns.size() <=
typeColumns.size(),
- "More columns in ResultColumnList than in base
table.");
- }
-
- ResultSetNode rsn;
-
- /*
- ** Should only set types of ? parameters to types of result
columns
- ** if it's a table constructor.
- */
- if (tableConstructor())
- {
- /* By looping through the union nodes, we avoid
recursion */
- for (rsn = this; rsn instanceof UnionNode; )
- {
- UnionNode union = (UnionNode) rsn;
-
- /*
- ** Assume that table constructors are left-deep
trees of UnionNodes
- ** with RowResultSet nodes on the right.
- */
- if (SanityManager.DEBUG)
- SanityManager.ASSERT(
- union.rightResultSet instanceof
RowResultSetNode,
- "A " +
union.rightResultSet.getClass().getName() +
- " is on the right of a union in
a table constructor");
-
- ((RowResultSetNode)
union.rightResultSet).setTableConstructorTypes(
-
typeColumns);
-
- rsn = union.leftResultSet;
- }
-
- /* The last node on the left should be a result set
node */
- if (SanityManager.DEBUG)
- SanityManager.ASSERT(rsn instanceof
RowResultSetNode,
- "A " + rsn.getClass().getName() +
- " is at the left end of a table
constructor");
-
- ((RowResultSetNode)
rsn).setTableConstructorTypes(typeColumns);
- }
- }
-
/*
* Optimizable interface
*/
@@ -411,654 +248,6 @@
return treeTop;
}
- /**
- * 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" +
- "tableConstructor: " + tableConstructor + "\n" +
- "orderByList: " +
- (orderByList != null ? orderByList.toString() :
"null") + "\n" +
- super.toString();
- }
- else
- {
- return "";
- }
- }
-
- /**
- * Bind the expressions under this TableOperatorNode. This means
- * binding the sub-expressions, as well as figuring out what the
- * return type is for each expression.
- *
- * @return Nothing
- *
- * @exception StandardException Thrown on error
- */
-
- public void bindExpressions(FromList fromListParam)
- throws StandardException
- {
- super.bindExpressions(fromListParam);
-
- /*
- ** Each ? parameter in a table constructor that is not in an
insert
- ** statement takes its type from the first non-? in its column
- ** of the table constructor. It's an error to have a column
that
- ** has all ?s. Do this only for the top of the table
constructor
- ** list - we don't want to do this for every level of union node
- ** in the table constructor. Also, don't do this for an INSERT
-
- ** the types of the ? parameters come from the columns being
inserted
- ** into in that case.
- */
- if (topTableConstructor && ( ! insertSource) )
- {
- /*
- ** Step through all the rows in the table constructor to
- ** get the type of the first non-? in each column.
- */
- DataTypeDescriptor[] types =
- new
DataTypeDescriptor[leftResultSet.getResultColumns().size()];
-
- ResultSetNode rsn;
- int numTypes = 0;
-
- /* By looping through the union nodes, we avoid
recursion */
- for (rsn = this; rsn instanceof UnionNode; )
- {
- UnionNode union = (UnionNode) rsn;
-
- /*
- ** Assume that table constructors are left-deep
trees of
- ** UnionNodes with RowResultSet nodes on the
right.
- */
- if (SanityManager.DEBUG)
- SanityManager.ASSERT(
- union.rightResultSet instanceof
RowResultSetNode,
- "A " +
union.rightResultSet.getClass().getName() +
- " is on the right side of a union in a
table constructor");
-
- RowResultSetNode rrsn =
-
(RowResultSetNode) union.rightResultSet;
-
- numTypes += getParamColumnTypes(types, rrsn);
-
- rsn = union.leftResultSet;
- }
-
- /* The last node on the left should be a result set
node */
- if (SanityManager.DEBUG)
- SanityManager.ASSERT(rsn instanceof
RowResultSetNode);
-
- numTypes += getParamColumnTypes(types,
(RowResultSetNode) rsn);
-
- /* Are there any columns that are all ? parameters? */
- if (numTypes < types.length)
- {
- throw
StandardException.newException(SQLState.LANG_TABLE_CONSTRUCTOR_ALL_PARAM_COLUMN);
- }
-
- /*
- ** Loop through the nodes again. This time, look for
parameter
- ** nodes, and give them the type from the type array we
just
- ** constructed.
- */
- for (rsn = this; rsn instanceof UnionNode; )
- {
- UnionNode union = (UnionNode) rsn;
- RowResultSetNode rrsn =
-
(RowResultSetNode) union.rightResultSet;
-
- setParamColumnTypes(types, rrsn);
-
- rsn = union.leftResultSet;
- }
-
- setParamColumnTypes(types, (RowResultSetNode) rsn);
- }
- }
-
- /**
- * Bind the result columns of this ResultSetNode when there is no
- * base table to bind them to. This is useful for SELECT statements,
- * where the result columns get their types from the expressions that
- * live under them.
- *
- * @param fromListParam FromList to use/append to.
- *
- * @return Nothing
- *
- * @exception StandardException Thrown on error
- */
- public void bindResultColumns(FromList fromListParam)
- throws StandardException
- {
- super.bindResultColumns(fromListParam);
-
- /* Now we build our RCL */
- buildRCL();
- }
-
- /**
- * Bind the result columns for this ResultSetNode to a base table.
- * This is useful for INSERT and UPDATE statements, where the
- * result columns get their types from the table being updated or
- * inserted into.
- * If a result column list is specified, then the verification that the
- * result column list does not contain any duplicates will be done when
- * binding them by name.
- *
- * @param targetTableDescriptor The TableDescriptor for the table being
- * updated or inserted into
- * @param targetColumnList For INSERT statements, the user
- * does not have to supply column
- * names (for example, "insert
into t
- * values (1,2,3)". When this
- * parameter is null, it means that
- * the user did not supply column
- * names, and so the binding should
- * be done based on order. When it
- * is not null, it means do the
binding
- * by name, not position.
- * @param statement Calling DMLStatementNode
(Insert or Update)
- * @param fromListParam FromList to use/append to.
- *
- * @return Nothing
- *
- * @exception StandardException Thrown on error
- */
-
- public void bindResultColumns(TableDescriptor targetTableDescriptor,
- FromVTI targetVTI,
- ResultColumnList targetColumnList,
- DMLStatementNode statement,
- FromList fromListParam)
- throws StandardException
- {
- super.bindResultColumns(targetTableDescriptor,
- targetVTI,
-
targetColumnList, statement,
- fromListParam);
-
- /* Now we build our RCL */
- buildRCL();
- }
-
- /**
- * Build the RCL for this node. We propagate the RCL up from the
- * left child to form this node's RCL.
- *
- * @return Nothing
- *
- * @exception StandardException Thrown on error
- */
-
- private void buildRCL() throws StandardException
- {
- /* Verify that both sides of the union have the same # of
columns in their
- * RCL.
- */
- if (leftResultSet.getResultColumns().size() !=
- rightResultSet.getResultColumns().size())
- {
- throw
StandardException.newException(SQLState.LANG_UNION_UNMATCHED_COLUMNS);
- }
-
- /* We need to recreate resultColumns for this node, since there
- * may have been 1 or more *'s in the left's SELECT list.
- */
- resultColumns =
leftResultSet.getResultColumns().copyListAndObjects();
-
- /* Create new expressions with the dominant types after
verifying
- * union compatibility between left and right sides.
- */
-
resultColumns.setUnionResultExpression(rightResultSet.getResultColumns(),
tableNumber, level);
- }
-
- /**
- * Bind the result columns of a table constructor to the types in the
- * given ResultColumnList. Use when inserting from a table constructor,
- * and there are nulls in the values clauses.
- *
- * @param rcl The ResultColumnList with the types to bind to
- *
- * @exception StandardException Thrown on error.
- */
- public void bindUntypedNullsToResultColumns(ResultColumnList rcl)
- throws StandardException
- {
- /*
- ** If the RCL from the parent is null, then
- ** the types are coming from the union itself.
- ** So we have to cross check the two child
- ** rcls.
- */
- if (rcl == null)
- {
- ResultColumnList lrcl =
rightResultSet.getResultColumns();
- ResultColumnList rrcl =
leftResultSet.getResultColumns();
-
- leftResultSet.bindUntypedNullsToResultColumns(rrcl);
- rightResultSet.bindUntypedNullsToResultColumns(lrcl);
- }
- else
- {
- leftResultSet.bindUntypedNullsToResultColumns(rcl);
- rightResultSet.bindUntypedNullsToResultColumns(rcl);
- }
- }
-
- /**
- * Get the parameter types from the given RowResultSetNode into the
- * given array of types. If an array position is already filled in,
- * don't clobber it.
- *
- * @param types The array of types to fill in
- * @param rrsn The RowResultSetNode from which to take the param types
- *
- * @return The number of new types found in the RowResultSetNode
- */
- int getParamColumnTypes(DataTypeDescriptor[] types, RowResultSetNode
rrsn)
- {
- int numTypes = 0;
-
- /* Look for columns where we have not found a non-? yet. */
- for (int i = 0; i < 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 generated ProjectRestrictNode atop the original
FromTable.
- *
- * @exception StandardException Thrown on error
- */
-
- public ResultSetNode preprocess(int numTables,
-
GroupByList gbl,
-
FromList fromList)
- throws
StandardException
- {
- ResultSetNode newTop = this;
-
- /* RESOLVE - what does numTables and referencedTableMap mean
here? */
- leftResultSet = leftResultSet.preprocess(numTables, gbl,
fromList);
- rightResultSet = rightResultSet.preprocess(numTables, gbl,
fromList);
-
- /* Build the referenced table map (left || right) */
- referencedTableMap = (JBitSet)
leftResultSet.getReferencedTableMap().clone();
- referencedTableMap.or((JBitSet)
rightResultSet.getReferencedTableMap());
-
- /* If this is a UNION without an all and we have
- * an order by then we can consider eliminating the sort for the
- * order by. All of the columns in the order by list must
- * be ascending in order to do this. There are 2 cases:
- * o The order by list is an in order prefix of the
columns
- * in the select list. In this case the output of
the
- * sort from the distinct will be in the right
order
- * so we simply eliminate the order by list.
- * o The order by list is a subset of the columns in
the
- * the select list. In this case we need to
reorder the
- * columns in the select list so that the ordering
columns
- * are an in order prefix of the select list and
put a PRN
- * above the select so that the shape of the
result set
- * is as expected.
- */
- if ((! all) && 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;
- }
-
/**
* Generate the code for this UnionNode.
*
@@ -1134,4 +323,9 @@
mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null,
"getUnionResultSet", ClassName.NoPutResultSet, 7);
}
+
+ String getOperatorName()
+ {
+ return "UNION";
+ }
}
Index: java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
(revision 0)
+++ java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
(revision 0)
@@ -0,0 +1,845 @@
+/*
+
+ 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;
+
+ /* Is this a UNION ALL generated for a table constructor. */
+ boolean tableConstructor;
+
+ /* True if this is the top node of a table constructor */
+ boolean topTableConstructor;
+
+ 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 tableConstructor Whether or not this is from a table
constructor.
+ * @param tableProperties Properties list associated with the
table
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ public void init(
+ Object leftResult,
+ Object rightResult,
+ Object all,
+ Object tableConstructor,
+ Object tableProperties)
+ throws StandardException
+ {
+ super.init(leftResult, rightResult, tableProperties);
+
+ this.all = ((Boolean) all).booleanValue();
+
+ /* Is this an ALL for a table constructor? */
+ this.tableConstructor = ((Boolean)
tableConstructor).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();
+ }
+
+ /**
+ * Mark this as the top node of a table constructor.
+ */
+ public void markTopTableConstructor()
+ {
+ topTableConstructor = true;
+ }
+
+ /**
+ * Tell whether this is a UNION for a table constructor.
+ */
+ boolean tableConstructor()
+ {
+ return tableConstructor;
+ }
+
+ /**
+ * Check for (and reject) ? parameters directly under the ResultColumns.
+ * This is done for SELECT statements. Don't reject parameters that
+ * are in a table constructor - these are allowed, as long as the
+ * table constructor is in an INSERT statement or each column of the
+ * table constructor has at least one non-? column. The latter case
+ * is checked below, in bindExpressions().
+ *
+ * @return Nothing
+ *
+ * @exception StandardException Thrown if a ? parameter found
+ *
directly under a ResultColumn
+ */
+ public void rejectParameters() throws StandardException
+ {
+ if ( ! tableConstructor())
+ super.rejectParameters();
+ }
+
+ /**
+ * Set the type of column in the result column lists of each
+ * source of this union tree to the type in the given result column list
+ * (which represents the result columns for an insert).
+ * This is only for table constructors that appear in insert statements.
+ *
+ * @param typeColumns The ResultColumnList containing the desired
result
+ * types.
+ *
+ * @exception StandardException Thrown on error
+ */
+ void setTableConstructorTypes(ResultColumnList typeColumns)
+ throws StandardException
+ {
+ if (SanityManager.DEBUG)
+ {
+ SanityManager.ASSERT(resultColumns.size() <=
typeColumns.size(),
+ "More columns in ResultColumnList than in base
table.");
+ }
+
+ ResultSetNode rsn;
+
+ /*
+ ** Should only set types of ? parameters to types of result
columns
+ ** if it's a table constructor.
+ */
+ if (tableConstructor())
+ {
+ /* By looping through the union nodes, we avoid
recursion */
+ for (rsn = this; rsn instanceof UnionNode; )
+ {
+ UnionNode union = (UnionNode) rsn;
+
+ /*
+ ** Assume that table constructors are left-deep
trees of UnionNodes
+ ** with RowResultSet nodes on the right.
+ */
+ if (SanityManager.DEBUG)
+ SanityManager.ASSERT(
+ union.rightResultSet instanceof
RowResultSetNode,
+ "A " +
union.rightResultSet.getClass().getName() +
+ " is on the right of a union in
a table constructor");
+
+ ((RowResultSetNode)
union.rightResultSet).setTableConstructorTypes(
+
typeColumns);
+
+ rsn = union.leftResultSet;
+ }
+
+ /* The last node on the left should be a result set
node */
+ if (SanityManager.DEBUG)
+ SanityManager.ASSERT(rsn instanceof
RowResultSetNode,
+ "A " + rsn.getClass().getName() +
+ " is at the left end of a table
constructor");
+
+ ((RowResultSetNode)
rsn).setTableConstructorTypes(typeColumns);
+ }
+ }
+
+ /**
+ * 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" +
+ "tableConstructor: " + tableConstructor + "\n" +
+ "orderByList: " +
+ (orderByList != null ? orderByList.toString() :
"null") + "\n" +
+ super.toString();
+ }
+ else
+ {
+ return "";
+ }
+ }
+
+ /**
+ * Bind the expressions under this TableOperatorNode. This means
+ * binding the sub-expressions, as well as figuring out what the
+ * return type is for each expression.
+ *
+ * @return Nothing
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ public void bindExpressions(FromList fromListParam)
+ throws StandardException
+ {
+ super.bindExpressions(fromListParam);
+
+ /*
+ ** Each ? parameter in a table constructor that is not in an
insert
+ ** statement takes its type from the first non-? in its column
+ ** of the table constructor. It's an error to have a column
that
+ ** has all ?s. Do this only for the top of the table
constructor
+ ** list - we don't want to do this for every level of union node
+ ** in the table constructor. Also, don't do this for an INSERT
-
+ ** the types of the ? parameters come from the columns being
inserted
+ ** into in that case.
+ */
+ if (topTableConstructor && ( ! insertSource) )
+ {
+ /*
+ ** Step through all the rows in the table constructor to
+ ** get the type of the first non-? in each column.
+ */
+ DataTypeDescriptor[] types =
+ new
DataTypeDescriptor[leftResultSet.getResultColumns().size()];
+
+ ResultSetNode rsn;
+ int numTypes = 0;
+
+ /* By looping through the union nodes, we avoid
recursion */
+ for (rsn = this; rsn instanceof SetOperatorNode; )
+ {
+ SetOperatorNode setOperator =
(SetOperatorNode) rsn;
+
+ /*
+ ** Assume that table constructors are left-deep
trees of
+ ** SetOperatorNodes with RowResultSet nodes on
the right.
+ */
+ if (SanityManager.DEBUG)
+ SanityManager.ASSERT(
+ setOperator.rightResultSet instanceof
RowResultSetNode,
+ "A " +
setOperator.rightResultSet.getClass().getName() +
+ " is on the right side of a
setOperator in a table constructor");
+
+ RowResultSetNode rrsn =
+
(RowResultSetNode) setOperator.rightResultSet;
+
+ numTypes += getParamColumnTypes(types, rrsn);
+
+ rsn = setOperator.leftResultSet;
+ }
+
+ /* The last node on the left should be a result set
node */
+ if (SanityManager.DEBUG)
+ SanityManager.ASSERT(rsn instanceof
RowResultSetNode);
+
+ numTypes += getParamColumnTypes(types,
(RowResultSetNode) rsn);
+
+ /* Are there any columns that are all ? parameters? */
+ if (numTypes < types.length)
+ {
+ throw
StandardException.newException(SQLState.LANG_TABLE_CONSTRUCTOR_ALL_PARAM_COLUMN);
+ }
+
+ /*
+ ** Loop through the nodes again. This time, look for
parameter
+ ** nodes, and give them the type from the type array we
just
+ ** constructed.
+ */
+ for (rsn = this; rsn instanceof SetOperatorNode; )
+ {
+ SetOperatorNode setOperator = (SetOperatorNode)
rsn;
+ RowResultSetNode rrsn = (RowResultSetNode)
setOperator.rightResultSet;
+
+ setParamColumnTypes(types, rrsn);
+
+ rsn = setOperator.leftResultSet;
+ }
+
+ setParamColumnTypes(types, (RowResultSetNode) rsn);
+ }
+ }
+
+ /**
+ * Bind the result columns of this ResultSetNode when there is no
+ * base table to bind them to. This is useful for SELECT statements,
+ * where the result columns get their types from the expressions that
+ * live under them.
+ *
+ * @param fromListParam FromList to use/append to.
+ *
+ * @return Nothing
+ *
+ * @exception StandardException Thrown on error
+ */
+ public void bindResultColumns(FromList fromListParam)
+ throws StandardException
+ {
+ super.bindResultColumns(fromListParam);
+
+ /* Now we build our RCL */
+ buildRCL();
+ }
+
+ /**
+ * Bind the result columns for this ResultSetNode to a base table.
+ * This is useful for INSERT and UPDATE statements, where the
+ * result columns get their types from the table being updated or
+ * inserted into.
+ * If a result column list is specified, then the verification that the
+ * result column list does not contain any duplicates will be done when
+ * binding them by name.
+ *
+ * @param targetTableDescriptor The TableDescriptor for the table being
+ * updated or inserted into
+ * @param targetColumnList For INSERT statements, the user
+ * does not have to supply column
+ * names (for example, "insert
into t
+ * values (1,2,3)". When this
+ * parameter is null, it means that
+ * the user did not supply column
+ * names, and so the binding should
+ * be done based on order. When it
+ * is not null, it means do the
binding
+ * by name, not position.
+ * @param statement Calling DMLStatementNode
(Insert or Update)
+ * @param fromListParam FromList to use/append to.
+ *
+ * @return Nothing
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ public void bindResultColumns(TableDescriptor targetTableDescriptor,
+ FromVTI targetVTI,
+ ResultColumnList targetColumnList,
+ DMLStatementNode statement,
+ FromList fromListParam)
+ throws StandardException
+ {
+ super.bindResultColumns(targetTableDescriptor,
+ targetVTI,
+
targetColumnList, statement,
+ fromListParam);
+
+ /* Now we build our RCL */
+ buildRCL();
+ }
+
+ /**
+ * Build the RCL for this node. We propagate the RCL up from the
+ * left child to form this node's RCL.
+ *
+ * @return Nothing
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ private void buildRCL() throws StandardException
+ {
+ /* Verify that both sides of the union have the same # of
columns in their
+ * RCL.
+ */
+ if (leftResultSet.getResultColumns().size() !=
+ rightResultSet.getResultColumns().size())
+ {
+ throw
StandardException.newException(SQLState.LANG_UNION_UNMATCHED_COLUMNS,
+ 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 generated ProjectRestrictNode atop the original
FromTable.
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ public ResultSetNode preprocess(int numTables,
+
GroupByList gbl,
+
FromList fromList)
+ throws
StandardException
+ {
+ ResultSetNode newTop = this;
+
+ /* RESOLVE - what does numTables and referencedTableMap mean
here? */
+ leftResultSet = leftResultSet.preprocess(numTables, gbl,
fromList);
+ rightResultSet = rightResultSet.preprocess(numTables, gbl,
fromList);
+
+ /* Build the referenced table map (left || right) */
+ referencedTableMap = (JBitSet)
leftResultSet.getReferencedTableMap().clone();
+ referencedTableMap.or((JBitSet)
rightResultSet.getReferencedTableMap());
+
+ /* If this is a UNION without an all and we have
+ * an order by then we can consider eliminating the sort for the
+ * order by. All of the columns in the order by list must
+ * be ascending in order to do this. There are 2 cases:
+ * o The order by list is an in order prefix of the
columns
+ * in the select list. In this case the output of
the
+ * sort from the distinct will be in the right
order
+ * so we simply eliminate the order by list.
+ * o The order by list is a subset of the columns in
the
+ * the select list. In this case we need to
reorder the
+ * columns in the select list so that the ordering
columns
+ * are an in order prefix of the select list and
put a PRN
+ * above the select so that the shape of the
result set
+ * is as expected.
+ */
+ if ((! all) && 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();
+}
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision
111907)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy)
@@ -85,6 +85,7 @@
import org.apache.derby.impl.sql.compile.TransactionStatementNode;
import org.apache.derby.impl.sql.compile.TriggerReferencingStruct;
import org.apache.derby.impl.sql.compile.UnionNode;
+import org.apache.derby.impl.sql.compile.IntersectOrExceptNode;
import org.apache.derby.impl.sql.compile.UntypedNullConstantNode;
import org.apache.derby.impl.sql.compile.UpdateNode;
import org.apache.derby.impl.sql.compile.UserTypeConstantNode;
@@ -175,6 +176,15 @@
// Define for UTF8 max
private static final int MAX_UTF8_LENGTH = 65535;
+ // Constants for set operator types
+ private static final int NO_SET_OP = 0;
+ private static final int UNION_OP = 1;
+ private static final int UNION_ALL_OP = 2;
+ private static final int EXCEPT_OP = 3;
+ private static final int EXCEPT_ALL_OP = 4;
+ private static final int INTERSECT_OP = 5;
+ private static final int INTERSECT_ALL_OP = 6;
+
private StringSlicer stringSlicer;
private Object[] paramDefaults;
private String
statementSQLText;
@@ -2683,7 +2693,7 @@
OrderByList orderCols = null;
}
{
- queryExpression = queryExpression(null, Boolean.FALSE)
+ queryExpression = queryExpression(null, NO_SET_OP)
[ orderCols = orderByClause() ]
[ <FOR> forUpdateState = forUpdateClause(updateColumns) ]
[ isolationLevel = atIsolationLevel() ]
@@ -4129,24 +4139,37 @@
/*
* <A NAME="queryExpression">queryExpression</A>
+ *
+ * We have to be carefull to get the associativity correct. According to the
SQL spec
+ * <non-join query expression> ::=
+ * <non-join query term>
+ * | <query expression body> UNION [ ALL ] <query term>
+ * | <query expression body> EXCEPT [ ALL ] <query term>
+ * Meaning that
+ * t1 UNION ALL t2 UNION t3
+ * is equivalent to
+ * (t1 UNION ALL t2) UNION t3
+ * However recursive descent parsers want recursion to be on the right, so
this kind of associativity is unnatural
+ * for our parser. The queryExpression method must know whether it is being
called as the right hand side of a
+ * set operator to produce a query tree with the correct associativity.
*/
ResultSetNode
-queryExpression(ResultSetNode leftSide, Boolean unionAll) throws
StandardException :
+queryExpression(ResultSetNode leftSide, int operatorType) throws
StandardException :
{
ResultSetNode term;
}
{
- term = nonJoinQueryTerm(leftSide, unionAll) [ term = union(term) ]
+ term = nonJoinQueryTerm(leftSide, operatorType) [ term =
unionOrExcept(term) ]
{
return term;
}
}
/*
- * <A NAME="union">union</A>
+ * <A NAME="unionOrExcept">unionOrExcept</A>
*/
ResultSetNode
-union(ResultSetNode term) throws StandardException :
+unionOrExcept(ResultSetNode term) throws StandardException :
{
ResultSetNode expression;
Token tok = null;
@@ -4154,42 +4177,133 @@
{
<UNION> [ tok = <ALL> ] expression =
queryExpression(term,
- (tok != null) ?
Boolean.TRUE : Boolean.FALSE)
+ (tok != null) ?
UNION_ALL_OP : UNION_OP)
{
return expression;
}
+|
+ <EXCEPT> [ tok = <ALL> ] expression =
+ queryExpression(term,
+ (tok != null) ?
EXCEPT_ALL_OP : EXCEPT_OP)
+ {
+ return expression;
+ }
}
/*
* <A NAME="nonJoinQueryTerm">nonJoinQueryTerm</A>
+ *
+ * Be careful with the associativity of INTERSECT. According to the SQL spec
+ * t1 INTERSECT t2 INTERSECT ALL t3
+ * is equivalent to
+ * (t1 INTERSECT t2) INTERSECT ALL t3
+ * which is not the same as
+ * t1 INTERSECT (t2 INTERSECT ALL t3)
+ * See the comment on queryExpression.
*/
ResultSetNode
-nonJoinQueryTerm(ResultSetNode leftSide, Boolean unionAll) throws
StandardException :
+nonJoinQueryTerm(ResultSetNode leftSide, int operatorType) throws
StandardException :
{
ResultSetNode term;
}
{
- /*
- ** Omitted "intersect".
- */
- term = nonJoinQueryPrimary()
+ term = nonJoinQueryPrimary() [ term = intersect( term) ]
{
- if (leftSide != null)
- {
- return (ResultSetNode) nodeFactory.getNode(
-
C_NodeTypes.UNION_NODE,
-
leftSide,
- term,
-
unionAll,
-
Boolean.FALSE,
- null,
-
getContextManager());
- }
- else
- {
- return term;
- }
+ switch( operatorType)
+ {
+ case NO_SET_OP:
+ return term;
+
+ case UNION_OP:
+ return (ResultSetNode) nodeFactory.getNode(
+ C_NodeTypes.UNION_NODE,
+ leftSide,
+ term,
+ Boolean.FALSE,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+ case UNION_ALL_OP:
+ return (ResultSetNode) nodeFactory.getNode(
+ C_NodeTypes.UNION_NODE,
+ leftSide,
+ term,
+ Boolean.TRUE,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+ case EXCEPT_OP:
+ return (ResultSetNode) nodeFactory.getNode(
+ C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+ ReuseFactory.getInteger( IntersectOrExceptNode.EXCEPT_OP),
+ leftSide,
+ term,
+ Boolean.FALSE,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+ case EXCEPT_ALL_OP:
+ return (ResultSetNode) nodeFactory.getNode(
+ C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+ ReuseFactory.getInteger( IntersectOrExceptNode.EXCEPT_OP),
+ leftSide,
+ term,
+ Boolean.TRUE,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+ case INTERSECT_OP:
+ return (ResultSetNode) nodeFactory.getNode(
+ C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+ ReuseFactory.getInteger( IntersectOrExceptNode.INTERSECT_OP),
+ leftSide,
+ term,
+ Boolean.FALSE,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+ case INTERSECT_ALL_OP:
+ return (ResultSetNode) nodeFactory.getNode(
+ C_NodeTypes.INTERSECT_OR_EXCEPT_NODE,
+ ReuseFactory.getInteger( IntersectOrExceptNode.INTERSECT_OP),
+ leftSide,
+ term,
+ Boolean.TRUE,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+
+ default:
+ if (SanityManager.DEBUG)
+ {
+ SanityManager.THROWASSERT( "Invalid set operator type: " +
operatorType);
+ }
+ return null;
+ }
+ }
+}
+
+/*
+ * <A NAME="intersect">intersect</A>
+ */
+ResultSetNode
+intersect(ResultSetNode term) throws StandardException :
+{
+ ResultSetNode expression;
+ Token tok = null;
+}
+{
+ <INTERSECT> [ tok = <ALL> ] expression =
+ nonJoinQueryTerm(term, (tok != null) ?
INTERSECT_ALL_OP : INTERSECT_OP)
+ {
+ return expression;
}
}
@@ -4207,7 +4321,7 @@
return primary;
}
|
- <LEFT_PAREN> primary = queryExpression(null, Boolean.FALSE)
<RIGHT_PAREN>
+ <LEFT_PAREN> primary = queryExpression(null, NO_SET_OP) <RIGHT_PAREN>
{
return primary;
}
@@ -6750,7 +6864,7 @@
<LEFT_PAREN> columnList = insertColumnList() <RIGHT_PAREN>
]
[ targetProperties = propertyList() ]
- queryExpression = queryExpression(null, Boolean.FALSE)
+ queryExpression = queryExpression(null, NO_SET_OP)
{
return (QueryTreeNode) nodeFactory.getNode(
C_NodeTypes.INSERT_NODE,
@@ -6997,7 +7111,7 @@
SubqueryNode subqueryNode;
}
{
- queryExpression = queryExpression(null, Boolean.FALSE)
+ queryExpression = queryExpression(null, NO_SET_OP)
{
subqueryNode = (SubqueryNode) nodeFactory.getNode(
C_NodeTypes.SUBQUERY_NODE,
@@ -8778,7 +8892,7 @@
{
<VIEW> tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
[ <LEFT_PAREN> resultColumns = viewColumnList() <RIGHT_PAREN> ]
- <AS> queryExpression = queryExpression(null, Boolean.FALSE)
+ <AS> queryExpression = queryExpression(null, NO_SET_OP)
{
checkOptionType = ViewDescriptor.NO_CHECK_OPTION;
endToken = getToken(0);
@@ -9662,7 +9776,7 @@
<EXECUTE> <STATEMENT> stmtName =
qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
[ LOOKAHEAD( { getToken(1).kind == USING } )
usingToken = <USING> usingClause =
-
queryExpression(null, Boolean.FALSE) ]
+
queryExpression(null, NO_SET_OP) ]
{
endToken = getToken(0);
Index: java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
(revision 0)
+++ java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
(revision 0)
@@ -0,0 +1,397 @@
+/*
+
+ 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)
+ * setOpProjectRestrict( 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 setOpProjectRestrict 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 tableConstructor Whether or not this is from a table
constructor.
+ * @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 tableConstructor,
+ Object tableProperties)
+ throws StandardException
+ {
+ super.init( leftResult, rightResult, all, tableConstructor,
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 generated ProjectRestrictNode atop the original
FromTable.
+ *
+ * @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()));
+ // RESOLVE: We should add in the cost of the generated
project/restrict, but this is not high.
+
+ return costEstimate;
+ } // End of estimateCost
+
+ /**
+ * @see Optimizable#modifyAccessPath
+ *
+ * @exception StandardException Thrown on error
+ */
+ public Optimizable modifyAccessPath(JBitSet outerTables) throws
StandardException
+ {
+ Optimizable retOptimizable;
+ retOptimizable = super.modifyAccessPath(outerTables);
+
+ /* We only want call addNewNodes() once */
+ if (addNewNodesCalled)
+ {
+ return retOptimizable;
+ }
+ return (Optimizable) addNewNodes();
+ }
+
+ /**
+ * @see ResultSetNode#modifyAccessPaths
+ *
+ * @exception StandardException Thrown on error
+ */
+ public ResultSetNode modifyAccessPaths() throws StandardException
+ {
+ ResultSetNode retRSN;
+ retRSN = super.modifyAccessPaths();
+
+ /* We only want call addNewNodes() once */
+ if (addNewNodesCalled)
+ {
+ return retRSN;
+ }
+ return addNewNodes();
+ }
+
+ /**
+ * Add any new ResultSetNodes that are necessary to the tree.
+ * We wait until after optimization to do this in order to
+ * make it easier on the optimizer.
+ *
+ * @return (Potentially new) head of the ResultSetNode tree.
+ *
+ * @exception StandardException Thrown on error
+ */
+ private ResultSetNode addNewNodes()
+ throws StandardException
+ {
+ /* Only call addNewNodes() once */
+ if (addNewNodesCalled)
+ {
+ return this;
+ }
+
+ addNewNodesCalled = true;
+
+ if( orderByList == null)
+ return this;
+ // Generate an order by node on top of the intersect/except
+ return (ResultSetNode) getNodeFactory().getNode(
C_NodeTypes.ORDER_BY_NODE,
+ this,
+ orderByList,
+ tableProperties,
+ getContextManager());
+ } // end of addNewNodes
+
+ /**
+ * Generate the code.
+ *
+ * @exception StandardException Thrown on error
+ */
+ public void generate( ActivationClassBuilder acb,
+ MethodBuilder mb)
+ throws StandardException
+ {
+
+ /* Get the next ResultSet #, so that we can number this
ResultSetNode, its
+ * ResultColumnList and ResultSet.
+ */
+ assignResultSetNumber();
+
+ // build up the tree.
+
+ /* Generate the SetOpResultSet. Arguments:
+ * 1) expression for left child ResultSet
+ * 2) expression for right child ResultSet
+ * 3) activation
+ * 4) resultSetNumber
+ * 5) estimated row count
+ * 6) estimated cost
+ * 7) opType
+ * 8) all
+ * 9) close method
+ * 10) intermediateOrderByColumns saved object index
+ * 11) intermediateOrderByDirection saved object index
+ */
+
+ acb.pushGetResultSetFactoryExpression(mb); // instance for
getUnionResultSet
+
+ getLeftResultSet().generate( acb, mb);
+ getRightResultSet().generate( acb, mb);
+
+ acb.pushThisAsActivation(mb);
+ mb.push(resultSetNumber);
+ mb.push( costEstimate.getEstimatedRowCount());
+ mb.push( costEstimate.getEstimatedCost());
+ mb.push( getOpType());
+ mb.push( all);
+ closeMethodArgument(acb, mb);
+ mb.push( getCompilerContext().addSavedObject(
intermediateOrderByColumns));
+ mb.push( getCompilerContext().addSavedObject(
intermediateOrderByDirection));
+
+ mb.callMethod(VMOpcode.INVOKEINTERFACE,
+ (String) null,
+ "getSetOpResultSet",
+ ClassName.NoPutResultSet, 11);
+ } // end of generate
+
+ String getOperatorName()
+ {
+ switch( opType)
+ {
+ case INTERSECT_OP:
+ return "INTERSECT";
+
+ case EXCEPT_OP:
+ return "EXCEPT";
+ }
+ if( SanityManager.DEBUG)
+ SanityManager.THROWASSERT( "Invalid intersectOrExcept opType: " +
opType);
+ return "?";
+ }
+
+ double getRowCountEstimate( double leftRowCount, double rightRowCount)
+ {
+ switch( opType)
+ {
+ case INTERSECT_OP:
+ // The result has at most min( leftRowCount, rightRowCount).
Estimate the actual row count at
+ // half that.
+ return Math.min( leftRowCount, rightRowCount)/2;
+
+ case EXCEPT_OP:
+ // The result has at most leftRowCount rows and at least min( 0,
leftRowCount - rightRowCount) rows.
+ // Use the mean of those two as the estimate.
+ return (leftRowCount + Math.min( 0, leftRowCount -
rightRowCount))/2;
+ }
+ if( SanityManager.DEBUG)
+ SanityManager.THROWASSERT( "Invalid intersectOrExcept opType: " +
opType);
+ return 1.0;
+ } // end of getRowCountEstimate
+
+ double getSingleScanRowCountEstimate( double leftSingleScanRowCount,
double rightSingleScanRowCount)
+ {
+ return getRowCountEstimate( leftSingleScanRowCount,
rightSingleScanRowCount);
+ }
+}
Index: java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
(working copy)
@@ -376,8 +376,8 @@
** Parameters not allowed in select list of either side of
union,
** except when the union is for a table constructor.
*/
- if ( ! (this instanceof UnionNode) ||
- ! ((UnionNode) this).tableConstructor())
+ if ( ! (this instanceof SetOperatorNode) ||
+ ! ((SetOperatorNode) this).tableConstructor())
{
leftResultSet.rejectParameters();
rightResultSet.rejectParameters();
@@ -419,11 +419,11 @@
throws StandardException
{
/*
- ** Parameters not allowed in select list of either side of
union,
- ** except when the union is for a table constructor.
+ ** Parameters not allowed in select list of either side of a
set operator,
+ ** except when the set operator is for a table constructor.
*/
- if ( ! (this instanceof UnionNode) ||
- ! ((UnionNode) this).tableConstructor())
+ if ( ! (this instanceof SetOperatorNode) ||
+ ! ((SetOperatorNode) this).tableConstructor())
{
leftResultSet.rejectParameters();
rightResultSet.rejectParameters();
Index: java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(working copy)
@@ -172,7 +172,7 @@
ResultColumnList targetCols = target.getResultColumns();
//bug 5716 - for db2 compatibility - no qualified names allowed
in order by clause when union/union all operator is used
- if (target instanceof UnionNode && correlationName != null)
+ if (target instanceof SetOperatorNode && correlationName !=
null)
{
String fullName = (schemaName != null) ?
(schemaName + "." + correlationName + "." +
columnName) :
@@ -207,7 +207,7 @@
* because of the gyrations we go to with building the
RCLs
* for a UnionNode.
*/
- if (target instanceof UnionNode)
+ if (target instanceof SetOperatorNode)
{
sourceTableNumber = ((FromTable)
target).getTableNumber();
}
Index: java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(working copy)
@@ -2025,7 +2025,7 @@
}
/**
- * Set up the result expressions for a UNION:
+ * Set up the result expressions for a UNION, INTERSECT, or EXCEPT:
* o Verify union type compatiblity
* o Get dominant type for result (type + max length + nullability)
* o Create a new ColumnReference with dominant type and name of from
this
@@ -2038,14 +2038,16 @@
* @param otherRCL RCL from other side of the UNION.
* @param tableNumber The tableNumber for the UNION.
* @param level The nesting level for the UNION.
+ * @param operatorName "UNION", "INTERSECT", or "EXCEPT"
*
* @return Nothing.
*
* @exception StandardException Thrown on error
*/
public void setUnionResultExpression(ResultColumnList otherRCL,
- int tableNumber,
- int level)
+ int tableNumber,
+ int level,
+ String operatorName)
throws StandardException
{
TableName dummyTN;
@@ -2116,8 +2118,9 @@
!otherExpr.getTypeCompiler().storable(thisTypeId, cf))
{
throw
StandardException.newException(SQLState.LANG_NOT_UNION_COMPATIBLE,
-
thisTypeId.getSQLTypeName(),
-
otherTypeId.getSQLTypeName() );
+
thisTypeId.getSQLTypeName(),
+
otherTypeId.getSQLTypeName(),
+ operatorName);
}
DataTypeDescriptor resultType =
thisExpr.getTypeServices().getDominantType(
Index: java/engine/org/apache/derby/impl/sql/build.xml
===================================================================
--- java/engine/org/apache/derby/impl/sql/build.xml (revision 111907)
+++ java/engine/org/apache/derby/impl/sql/build.xml (working copy)
@@ -16,6 +16,8 @@
<property file="${properties.dir}/extrapath.properties"/>
<property file="${properties.dir}/compilepath.properties"/>
+ <property name="cur.dir" value="impl/sql"/>
+
<!-- Targets -->
<target name="parser">
<ant antfile="${src.dir}/build.xml" target="genParser">
@@ -42,9 +44,9 @@
<classpath>
<pathelement path="${compile.classpath}"/>
</classpath>
- <include name="${derby.dir}/impl/sql/**"/>
+ <include name="${derby.dir}/${cur.dir}/**"/>
</javac>
- <copy file="catalog/metadata_net.properties"
tofile="${out.dir}/org/apache/derby/impl/sql/catalog/metadata_net.properties"/>
+ <copy
file="${derby.engine.src.dir}/${derby.dir}/${cur.dir}/catalog/metadata_net.properties"
tofile="${out.dir}/org/apache/derby/impl/sql/catalog/metadata_net.properties"/>
</target>
</project>
Index:
java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
(revision 111907)
+++ java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
(working copy)
@@ -1053,6 +1053,32 @@
closeCleanup);
}
+ public NoPutResultSet getSetOpResultSet( NoPutResultSet leftSource,
+ NoPutResultSet rightSource,
+ Activation activation,
+ int resultSetNumber,
+ long optimizerEstimatedRowCount,
+ double optimizerEstimatedCost,
+ int opType,
+ boolean all,
+ GeneratedMethod closeCleanup,
+ int
intermediateOrderByColumnsSavedObject,
+ int
intermediateOrderByDirectionSavedObject)
+ throws StandardException
+ {
+ return new SetOpResultSet( leftSource,
+ rightSource,
+ activation,
+ resultSetNumber,
+ optimizerEstimatedRowCount,
+ optimizerEstimatedCost,
+ opType,
+ all,
+ closeCleanup,
+ intermediateOrderByColumnsSavedObject,
+ intermediateOrderByDirectionSavedObject);
+ }
+
/**
* A last index key sresult set returns the last row from
* the index in question. It is used as an ajunct to max().
Index: java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java
(revision 0)
+++ java/engine/org/apache/derby/impl/sql/execute/SetOpResultSet.java
(revision 0)
@@ -0,0 +1,288 @@
+/*
+
+ 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++)
+ {
+ if( i >= intermediateOrderByColumns.length)
+ return 0;
+ 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];
+ }
+ } // 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();
+ }
+}
Index: java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
(revision 111907)
+++ java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
(working copy)
@@ -185,7 +185,8 @@
static final int SUBSTRING_OPERATOR_NODE = 154;
// UNUSED static final int BOOLEAN_NODE = 155;
static final int DROP_ALIAS_NODE = 156;
- // 157 - 185 available
+ static final int INTERSECT_OR_EXCEPT_NODE = 157;
+ // 158 - 185 available
static final int MODIFY_COLUMN_TYPE_NODE = 186;
static final int MODIFY_COLUMN_CONSTRAINT_NODE = 187;
static final int ABSOLUTE_OPERATOR_NODE = 188;
Index: java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
(revision 111907)
+++ java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
(working copy)
@@ -1428,6 +1428,45 @@
throws StandardException;
+ /**
+ * The SetOpResultSet is used to implement an INTERSECT or EXCEPT
operation.
+ * It selects rows from two ordered input result sets.
+ *
+ * @param leftSource The result set that implements the left input
+ * @param rightSource The result set that implements the right input
+ * @param activation the activation for this result set
+ * @param resultSetNumber
+ * @param optimizerEstimatedRowCount
+ * @param optimizerEstimatedCost
+ * @param opType IntersectOrExceptNode.INTERSECT_OP or EXCEPT_OP
+ * @param all true if the operation is an INTERSECT ALL or an EXCEPT ALL,
+ * false if the operation is an INTERSECT DISCTINCT or an
EXCEPT DISCTINCT
+ * @param closeCleanup a method to be called by close
+ * @param intermediateOrderByColumnsSavedObject The saved object index for
the array of order by columns for the
+ * ordering of the left and right sources. That is, both the left
and right sources have an order by
+ * clause of the form ORDER BY
intermediateOrderByColumns[0],intermediateOrderByColumns[1],...
+ * @param intermediateOrderByDirectionSavedObject The saved object index
for the array of source
+ * order by directions. That is, the ordering of the i'th order by
column in the input is ascending
+ * if intermediateOrderByDirection[i] is 1, descending if
intermediateOrderByDirection[i] is -1.
+ *
+ * @return A ResultSet from which the caller can get the INTERSECT
or EXCEPT
+ *
+ * @exception StandardException Thrown on failure
+ */
+ NoPutResultSet getSetOpResultSet( NoPutResultSet leftSource,
+ NoPutResultSet rightSource,
+ Activation activation,
+ int resultSetNumber,
+ long optimizerEstimatedRowCount,
+ double optimizerEstimatedCost,
+ int opType,
+ boolean all,
+ GeneratedMethod closeCleanup,
+ int
intermediateOrderByColumnsSavedObject,
+ int
intermediateOrderByDirectionSavedObject)
+ throws StandardException;
+
+
//
// Misc operations
//
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties (revision
111907)
+++ java/engine/org/apache/derby/loc/messages_en.properties (working copy)
@@ -469,10 +469,10 @@
42X55=Table name ''{1}'' should be the same as ''{0}''.
42X56=The number of columns in the view column list does not match the number
of columns in the underlying query expression in the view definition for
''{0}''.
42X57=The getColumnCount() for external virtual table ''{0}'' returned an
invalid value ''{1}''. Valid values are >= 1.
-42X58=The number of columns on the left and right sides of the UNION must be
the same.
+42X58=The number of columns on the left and right sides of the {0} must be the
same.
42X59=The number of columns in each VALUES constructor must be the same.
42X60=Invalid value ''{0}'' for insertMode property specified for table
''{1}''.
-42X61=Types ''{0}'' and ''{1}'' are not UNION compatible.
+42X61=Types ''{0}'' and ''{1}'' are not {2} compatible.
42X62=''{0}'' is not allowed in the ''{1}'' schema.
42X63=The USING clause did not return any results, no parameters can be set.
42X64=Invalid value ''{0}'' specified for useStatistics property in the
Properties list. TRUE or FALSE are the only valid values.
@@ -919,7 +919,7 @@
X0X61.S=The values for column ''{4}'' in index ''{0}'' and table ''{1}.{2}''
do not match for row location {3}. The value in the index is ''{5}'', while
the value in the base table is ''{6}''. The full index key, including the row
location, is ''{7}''. The suggested corrective action is to recreate the index.
X0X62.S=Inconsistency found between table ''{0}'' and index ''{1}''. Error
when trying to retrieve row location ''{2}'' from the table. The full index
key, including the row location, is ''{3}''. The suggested corrective action is
to recreate the index.
X0X63.S=Got IOException ''{0}''.
-X0X67.S=Columns of type ''{0}'' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that
type.
+X0X67.S=Columns of type ''{0}'' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not
supported for that type.
X0X81.S={0} ''{1}'' does not exist.
X0X85.S=Index ''{0}'' was not created because ''{1}'' is not a valid index
type.
X0X86.S=0 is an invalid parameter value for ResultSet.absolute(int row).
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
(revision 0)
@@ -0,0 +1,141 @@
+create table t1( id integer not null primary key, i1 integer, i2 integer, c10
char(10), c30 char(30), tm time);
+create table t2( id integer not null primary key, i1 integer, i2 integer, vc20
varchar(20), d double, dt date);
+insert into t1(id,i1,i2,c10,c30) values
+ (1,1,1,'a','123456789012345678901234567890'),
+ (2,1,2,'a','bb'),
+ (3,1,3,'b','bb'),
+ (4,1,3,'zz','5'),
+ (5,null,null,null,'1.0'),
+ (6,null,null,null,'a');
+insert into t2(id,i1,i2,vc20,d) values
+ (1,1,1,'a',1.0),
+ (2,1,2,'a',1.1),
+ (5,null,null,'12345678901234567890',3),
+ (100,1,3,'zz',3),
+ (101,1,2,'bb',null),
+ (102,5,5,'',null),
+ (103,1,3,' a',null),
+ (104,1,3,'null',7.4);
+
+-- no duplicates
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by id
DESC,i1,i2;
+select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3;
+
+-- Only specify order by on some columns
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by i2, id DESC;
+select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 3 DESC,
1;
+
+-- duplicates
+select i1,i2 from t1 intersect select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2;
+
+-- right side is empty
+select i1,i2 from t1 intersect select i1,i2 from t2 where id = -1;
+select i1,i2 from t1 intersect all select i1,i2 from t2 where id = -1;
+
+-- left side is empty
+select i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2;
+
+-- check precedence
+select i1,i2 from t1 intersect all select i1,i2 from t2 intersect
values(5,5),(1,3) order by 1,2;
+(select i1,i2 from t1 intersect all select i1,i2 from t2) intersect
values(5,5),(1,3) order by 1,2;
+
+values(-1,-1,-1) union select id,i1,i2 from t1 intersect select id,i1,i2 from
t2 order by 1,2,3;
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 union
values(-1,-1,-1) order by 1,2,3;
+
+-- check conversions
+select c10 from t1 intersect select vc20 from t2 order by 1;
+select c30 from t1 intersect select vc20 from t2;
+select c30 from t1 intersect all select vc20 from t2;
+
+-- check insert intersect into table and intersect without order by
+create table r( i1 integer, i2 integer);
+insert into r select i1,i2 from t1 intersect select i1,i2 from t2;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+insert into r select i1,i2 from t1 intersect all select i1,i2 from t2;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+-- test LOB
+create table t3( i1 integer, cl clob(64), bl blob(1M));
+insert into t3 values
+ (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+create table t4( i1 integer, cl clob(64), bl blob(1M));
+insert into t4 values
+ (1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
+
+select cl from t3 intersect select cl from t4 order by 1;
+
+select bl from t3 intersect select bl from t4 order by 1;
+
+-- invalid conversion
+select tm from t1 intersect select dt from t2;
+select c30 from t1 intersect select d from t2;
+
+-- different number of columns
+select i1 from t1 intersect select i1,i2 from t2;
+
+-- ? in select list of intersect
+select ? from t1 intersect select i1 from t2;
+select i1 from t1 intersect select ? from t2;
+
+-- except tests
+select id,i1,i2 from t1 except select id,i1,i2 from t2 order by id,i1,i2;
+select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3;
+select id,i1,i2 from t2 except select id,i1,i2 from t1 order by 1,2,3;
+select id,i1,i2 from t2 except all select id,i1,i2 from t1 order by 1,2,3;
+
+select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;
+select i1,i2 from t2 except select i1,i2 from t1 order by 1,2;
+select i1,i2 from t2 except all select i1,i2 from t1 order by 1,2;
+
+-- right side is empty
+select i1,i2 from t1 except select i1,i2 from t2 where id = -1 order by 1,2;
+select i1,i2 from t1 except all select i1,i2 from t2 where id = -1 order by
1,2;
+
+-- left side is empty
+select i1,i2 from t1 where id = -1 except select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 where id = -1 except all select i1,i2 from t2 order by
1,2;
+
+-- Check precedence. Union and except have the same precedence. Intersect has
higher precedence.
+select i1,i2 from t1 except select i1,i2 from t2 intersect values(-1,-1) order
by 1,2;
+select i1,i2 from t1 except (select i1,i2 from t2 intersect values(-1,-1))
order by 1,2;
+select i1,i2 from t2 except select i1,i2 from t1 union values(5,5) order by
1,2;
+(select i1,i2 from t2 except select i1,i2 from t1) union values(5,5) order by
1,2;
+select i1,i2 from t2 except all select i1,i2 from t1 except select i1,i2 from
t1 where id = 3 order by 1,2;
+(select i1,i2 from t2 except all select i1,i2 from t1) except select i1,i2
from t1 where id = 3 order by 1,2;
+
+-- check conversions
+select c10 from t1 except select vc20 from t2 order by 1;
+select c30 from t1 except select vc20 from t2 order by 1;
+select c30 from t1 except all select vc20 from t2;
+
+-- check insert except into table and except without order by
+insert into r select i1,i2 from t2 except select i1,i2 from t1;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+insert into r select i1,i2 from t2 except all select i1,i2 from t1;
+select i1,i2 from r order by 1,2;
+delete from r;
+
+-- test LOB
+select cl from t3 except select cl from t4 order by 1;
+select bl from t3 except select bl from t4 order by 1;
+
+-- invalid conversion
+select tm from t1 except select dt from t2;
+select c30 from t1 except select d from t2;
+
+-- different number of columns
+select i1 from t1 except select i1,i2 from t2;
+
+-- ? in select list of except
+select ? from t1 except select i1 from t2;
+
+-- Invalid order by
+select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1;
+select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
(revision 111907)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
(working copy)
@@ -95,6 +95,7 @@
innerjoin.sql
insert.sql
insert_sed.properties
+intersect.sql
isolationLevels.sql
joinDeadlock.sql
joinDeadlock.sql1
Index: java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
(revision 111907)
+++ java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
(working copy)
@@ -45,7 +45,7 @@
create table unmapped(c1 long varchar);
0 rows inserted/updated/deleted
ij> select c1, max(1) from unmapped group by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
Index: java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
(revision 111907)
+++ java/testing/org/apache/derbyTesting/functionTests/master/LOB.out
(working copy)
@@ -266,13 +266,13 @@
0 rows inserted/updated/deleted
ij> -- create index (not allowed)
create index ia on a(a);
-ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that
type.
+ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not
supported for that type.
ij> create index ib on b(a);
-ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that
type.
+ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not
supported for that type.
ij> create index ic on c(a);
ERROR 42Y55: 'CREATE INDEX' cannot be performed on 'C' because it does not
exist.
ij> create index id on d(a);
-ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that
type.
+ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY,
GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons are not
supported for that type.
ij> -- cleanup
drop table a;
0 rows inserted/updated/deleted
@@ -535,7 +535,7 @@
1 row inserted/updated/deleted
ij> -- UNION
select * from testPredicate1 union select * from testPredicate2;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> -- IN predicate
select c1 from testPredicate1 where c1 IN (select c1 from testPredicate2);
ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not
supported.
@@ -544,10 +544,10 @@
ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not
supported.
ij> -- ORDER BY clause
select * from testPredicate1 order by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> -- GROUP BY clause
select substr(c1,1,2) from testPredicate1 group by c1;
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> -- JOIN
select * from testPredicate1 t1, testPredicate2 t2 where t1.c1=t2.c1;
ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not
supported.
@@ -555,15 +555,15 @@
ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not
supported.
ij> -- PRIMARY KEY
create table testConst1(c1 long varchar not null primary key);
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> -- UNIQUE KEY constraints
CREATE TABLE testconst2 (col1 long varchar not null, CONSTRAINT uk UNIQUE
(col1));
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> -- FOREIGN KEY constraints
create table testConst3 (c1 char(10) not null, primary key (c1));
0 rows inserted/updated/deleted
ij> create table testConst4 (c1 long varchar not null, constraint fk foreign
key (c1) references testConst3 (c1));
-ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported
for that type.
+ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX,
ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT, because comparisons
are not supported for that type.
ij> drop table testConst3;
0 rows inserted/updated/deleted
ij> -- MAX aggregate function
Index: java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
(revision 0)
@@ -0,0 +1,331 @@
+ij> 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>
Index:
java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(revision 111907)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(working copy)
@@ -67,6 +67,7 @@
lang/infostreams.sql
lang/innerjoin.sql
lang/insert.sql
+lang/intersect.sql
lang/isolationLevels.sql
lang/joinDeadlock.sql
lang/joins.sql
