You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2006/04/26 03:23:58 UTC
svn commit: r397043 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bandaram
Date: Tue Apr 25 18:23:29 2006
New Revision: 397043
URL: http://svn.apache.org/viewcvs?rev=397043&view=rev
Log:
DERBY-805: Follow up patch to address remaining issues following Phase IV work.
1) Added logic to skip predicate pushdown when either of the predicate's column references does not point to a base table. This can happen if, for example, the column reference points to a literal or an aggregate expression. Further work is required for such situations in order to correctly "remap" the column reference to its source (or at least, to figure out what exactly it means to remap a ColumnReference that doesn't point to a base table, and then to implement the appropriate changes)--so in the meantime, I've just decided to skip pushing the predicate for now.
2) Added logic to correctly set the column number of a "scoped" reference based on whether or not the reference points to a base table. Existing comments in the relevant sections of code describe why we need to set the column numbers for references pointing to base tables, but the code itself didn't actually check for the base table condition--it set the column number for all scoped references, which wasn't always correct.
3) In cases where a ColumnReference's source ResultColumn's expression is not another ColumnReference, made it so that the scope operation will return a clone of ColumnReference (instead of the ColumnReference itself) since that ColumnReference will be pushed to two result sets.
4) Added corresponding test cases to the lang/predicatePushdown.sql test and updated the master file accordingly.
I ran derbyall on Red Hat Linux with ibm142 and saw no new failures.
Submitted by Army Brown.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryRelationalOperatorNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryRelationalOperatorNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryRelationalOperatorNode.java?rev=397043&r1=397042&r2=397043&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryRelationalOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryRelationalOperatorNode.java Tue Apr 25 18:23:29 2006
@@ -1280,6 +1280,18 @@
* position (namely, the position w.r.t the ProjectRestrictNode above
* the FromBaseTable) needs to be. So that's the column number we
* use.
+ *
+ * As a final note, we have to be sure we only set the column
+ * reference's column number if the reference points to a base table.
+ * If the reference points to some other ResultSetNode--esp. another
+ * subquery node--then it (the reference) already holds the correct
+ * number with respect to that ResultSetNode and we don't change
+ * it. The reason is that the reference could end up getting pushed
+ * down further to that ResultSetNode, in which case we'll do another
+ * scoping operation and, in order for that to be successful, the
+ * reference to be scoped has to know what the target column number
+ * is w.r.t to that ResultSetNode (i.e. it'll be playing the role of
+ * "cr" as described here).
*/
if (rc.getExpression() instanceof ColumnReference)
{
@@ -1291,7 +1303,8 @@
// correctly. That remapping is done in the pushOptPredicate()
// method of ProjectRestrictNode.
ColumnReference cRef = (ColumnReference)rc.getExpression();
- cRef.setColumnNumber(cr.getColumnNumber());
+ if (cRef.pointsToBaseTable())
+ cRef.setColumnNumber(cr.getColumnNumber());
return cRef;
}
@@ -1301,10 +1314,10 @@
*
* select 1, 1 from t1
*
- * In this case we just return the column reference as it is
+ * In this case we just return a clone of the column reference
* because it's scoped as far as we can take it.
*/
- return cr;
+ return (ValueNode)cr.getClone();
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java?rev=397043&r1=397042&r2=397043&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java Tue Apr 25 18:23:29 2006
@@ -979,4 +979,52 @@
}
return dtd;
} // end of getTypeServices
+
+ /**
+ * Determine whether or not this ColumnReference's source comes
+ * from a FromBaseTable (as opposed to some other ResultSetNode).
+ * We figure this out by walking the ResultColumn/VirtualColumnNode
+ * chain until we get to last VirtualColumnNode in the chain
+ * (if there is one), and then seeing what that VCN's source
+ * result set is. If there are no VCNs then we check to see
+ * if the source is pointing to a BaseColumnNode.
+ *
+ * This is useful when scoping predicates for pushing; we
+ * need to know if the predicate's column references are pointing
+ * directly to base tables so that we can set the scoped references'
+ * column numbers correctly.
+ */
+ protected boolean pointsToBaseTable() throws StandardException
+ {
+ ResultColumn rc = getSource();
+
+ if (rc == null) {
+ // this can happen if column reference is pointing to a column
+ // that is not from a base table. For example, if we have a
+ // VALUES clause like
+ //
+ // (values (1, 2), (3, 4)) V1 (i, j)
+ //
+ // and then a column reference to VI.i, the column reference
+ // won't have a source.
+ return false;
+ }
+
+ // Walk the VirtualColumnNode->ResultColumn chain.
+ VirtualColumnNode vcn = null;
+ ValueNode rcExpr = rc.getExpression();
+ while (rcExpr instanceof VirtualColumnNode) {
+ vcn = (VirtualColumnNode)rcExpr;
+ rc = vcn.getSourceColumn();
+ rcExpr = rc.getExpression();
+ }
+
+ // If we've reached the bottom of the chain then see if
+ // the VCN is pointing to a FromBaseTable.
+ if (vcn != null)
+ return (vcn.getSourceResultSet() instanceof FromBaseTable);
+
+ // Else check our source's expression.
+ return (rc.getExpression() instanceof BaseColumnNode);
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java?rev=397043&r1=397042&r2=397043&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java Tue Apr 25 18:23:29 2006
@@ -852,8 +852,10 @@
* Determine whether or not this predicate is eligible for
* push-down into subqueries. Right now the only predicates
* we consider to be eligible are those which 1) are Binary
- * Relational operator nodes, and 2) have a column reference
- * on BOTH sides.
+ * Relational operator nodes, 2) have a column reference
+ * on BOTH sides, and 3) have column references such that
+ * each column reference has a reference to a base table
+ * somewhere beneath it.
*
* @return Whether or not this predicate is eligible to be
* pushed into subqueries.
@@ -872,8 +874,35 @@
BinaryRelationalOperatorNode opNode =
(BinaryRelationalOperatorNode)getAndNode().getLeftOperand();
- return ((opNode.getLeftOperand() instanceof ColumnReference) &&
- (opNode.getRightOperand() instanceof ColumnReference));
+ // If either side is not a column reference, we don't push.
+ if (!((opNode.getLeftOperand() instanceof ColumnReference) &&
+ (opNode.getRightOperand() instanceof ColumnReference)))
+ {
+ return false;
+ }
+
+ // Make sure both column references ultimately point to base
+ // tables. If, for example, either column reference points to a
+ // a literal or an aggregate, then we do not push the predicate.
+ // This is because pushing involves remapping the references--
+ // but if the reference doesn't have a base table beneath it,
+ // the notion of "remapping" it doesn't (seem to) apply. RESOLVE:
+ // it might be okay to make the "remap" operation a no-op for
+ // such column references, but it's not clear whether that's
+ // always a safe option; further investigation required.
+
+ JBitSet tNums = new JBitSet(getReferencedSet().size());
+ BaseTableNumbersVisitor btnVis = new BaseTableNumbersVisitor(tNums);
+ opNode.getLeftOperand().accept(btnVis);
+ if (tNums.getFirstSetBit() == -1)
+ return false;
+
+ tNums.clearAll();
+ opNode.getRightOperand().accept(btnVis);
+ if (tNums.getFirstSetBit() == -1)
+ return false;
+
+ return true;
}
/**
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out?rev=397043&r1=397042&r2=397043&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out Tue Apr 25 18:23:29 2006
@@ -1300,6 +1300,211 @@
null stop position:
null qualifiers:
None
+ij> -- Next set of queries tests pushdown of predicates whose
+-- column references do not reference base tables--ex. they
+-- reference literals, aggregates, or subqueries. We don't
+-- check the query plans here, we're just checking to make
+-- sure pushdown doesn't cause problems during compilation/
+-- execution. In the case of regressions, errors that might
+-- show up here include compile-time NPEs, execution-time
+-- NPEs, errors saying no predicate was found for a hash join,
+-- and/or type comparison errors caused by incorrect column
+-- numbers for scoped predicates.
+create table tc (c1 char, c2 char, c3 char, c int);
+0 rows inserted/updated/deleted
+ij> create view vz (z1, z2, z3, z4) as
+ select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from
+ (select c1, c, c2, c3 from tc) xx1
+ union select 'i','j','j',i from t2;
+0 rows inserted/updated/deleted
+ij> create view vz2 (z1, z2, z3, z4) as
+ select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from
+ (select c1, c, c2, c3 from tc) xx1;
+0 rows inserted/updated/deleted
+ij> -- Both sides of predicate reference aggregates.
+select x1.c1 from
+ (select count(*) from t1 union select count(*) from t2) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+ij> -- Both sides of predicate reference aggregates, and
+-- predicate is pushed through to non-flattenable nested
+-- subquery.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+ij> -- Both sides of predicate reference aggregates, and
+-- predicate is pushed through to non-flattenable nested
+-- subquery that is in turn part of a nested union.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1 union select distinct j from t2) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+ij> -- Left side of predicate references base column, right side
+-- references aggregate; predicate is pushed through to non-
+-- flattenable nested subquery.
+select x1.c1 from
+ (select xx1.c from
+ (select distinct c, c1 from tc) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+ij> -- Left side of predicate references base column, right side
+-- references aggregate; predicate is pushed through to non-
+-- flattenable nested subquery.
+select x1.c1 from
+ (select xx1.c from
+ (select c, c1 from tc) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+ij> -- Left side of predicate references base column, right side
+-- side references aggregate; predicate is pushed through to
+-- a subquery in a nested union that has literals in its result
+-- column.
+select x1.z1 from
+ (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from
+ (select c1, c2, c3, c from tc) xx1
+ union select 'i','j',j,'i' from t2
+ ) x1 (z1, z2, z3, z4),
+ (select count(*) from t3 union select count (*) from t4) x2 (c2)
+where x1.z3 = x2.c2;
+Z1
+----
+ij> -- Both sides of predicate reference base columns; predicate
+-- predicate is pushed through to a subquery in a nested union
+-- that has literals in its result column.
+select x1.z1 from
+ (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from
+ (select c1, c2, c3, c from tc) xx1
+ union select 'i','j',j,'i' from t2
+ ) x1 (z1, z2, z3, z4),
+ (select a from t3 union select count (*) from t4) x2 (c2)
+where x1.z3 = x2.c2;
+Z1
+----
+i
+i
+i
+ij> -- Same as previous query, but with aggregate/base column
+-- in x2 switched.
+select x1.z1 from
+ (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from
+ (select c1, c2, c3, c from tc) xx1
+ union select 'i','j',j,'i' from t2
+ ) x1 (z1, z2, z3, z4),
+ (select count(*) from t3 union select a from t4) x2 (c2)
+where x1.z3 = x2.c2;
+Z1
+----
+ij> -- Left side references aggregate, right side references base
+-- column; predicate is pushed to non-flattenable subquery
+-- that is part of a nested union for which one child references
+-- a base column and the other references an aggregate.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select a from t3 union select a from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+ij> -- Same as previous query, but both children of inner-most
+-- union reference base columns.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select i from t2
+ ) x1 (c1),
+ (select a from t3 union select a from t4) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+1
+2
+3
+4
+ij> -- Left side references aggregate, right side references base
+-- column; predicate is pushed to non-flattenable subquery
+-- that is part of a nested union for which one child references
+-- a base column and the other references an aggregate.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select i from t2 union select i from t1) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+5
+ij> -- Same as previous query, but one child of x2 references
+-- a literal.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select 1 from t2 union select i from t1) x2 (c2)
+where x1.c1 = x2.c2;
+C1
+-----------
+5
+ij> -- Left side of predicate references a base column that is
+-- deeply nested inside a subquery, a union, and a view,
+-- the latter of which itself has a union between two
+-- nested subqueries (whew). And finally, the position of
+-- the base column w.r.t the outer query (x1) is different
+-- than it is with respect to inner view (vz).
+select x1.z4 from
+ (select z1, z4, z3 from vz
+ union select '1', 4, '3' from t1
+ ) x1 (z1, z4, z3),
+ (select distinct j from t2 union select j from t1) x2 (c2)
+where x1.z4 = x2.c2;
+Z4
+-----------
+4
+2
+4
+ij> -- Same as previous query but with a different nested
+-- view (vz2) that is missing the nested union found
+-- in vz.
+select x1.z4 from
+ (select z1, z4, z3 from vz2
+ union select '1', 4, '3' from t1
+ ) x1 (z1, z4, z3),
+ (select distinct j from t2 union select j from t1) x2 (c2)
+where x1.z4 = x2.c2;
+Z4
+-----------
+4
+ij> -- Cleanup from this set of tests.
+drop view vz;
+0 rows inserted/updated/deleted
+ij> drop view vz2;
+0 rows inserted/updated/deleted
+ij> drop table tc;
+0 rows inserted/updated/deleted
ij> -- Now bump up the size of tables T3 and T4 to the point where
-- use of indexes will cause optimizer to choose nested loop join
-- (and push predicates) instead of hash join. The following
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql?rev=397043&r1=397042&r2=397043&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql Tue Apr 25 18:23:29 2006
@@ -80,6 +80,183 @@
where x1.i = x2.a;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+-- Next set of queries tests pushdown of predicates whose
+-- column references do not reference base tables--ex. they
+-- reference literals, aggregates, or subqueries. We don't
+-- check the query plans here, we're just checking to make
+-- sure pushdown doesn't cause problems during compilation/
+-- execution. In the case of regressions, errors that might
+-- show up here include compile-time NPEs, execution-time
+-- NPEs, errors saying no predicate was found for a hash join,
+-- and/or type comparison errors caused by incorrect column
+-- numbers for scoped predicates.
+
+create table tc (c1 char, c2 char, c3 char, c int);
+
+create view vz (z1, z2, z3, z4) as
+ select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from
+ (select c1, c, c2, c3 from tc) xx1
+ union select 'i','j','j',i from t2;
+
+create view vz2 (z1, z2, z3, z4) as
+ select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from
+ (select c1, c, c2, c3 from tc) xx1;
+
+-- Both sides of predicate reference aggregates.
+select x1.c1 from
+ (select count(*) from t1 union select count(*) from t2) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Both sides of predicate reference aggregates, and
+-- predicate is pushed through to non-flattenable nested
+-- subquery.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Both sides of predicate reference aggregates, and
+-- predicate is pushed through to non-flattenable nested
+-- subquery that is in turn part of a nested union.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1 union select distinct j from t2) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Left side of predicate references base column, right side
+-- references aggregate; predicate is pushed through to non-
+-- flattenable nested subquery.
+select x1.c1 from
+ (select xx1.c from
+ (select distinct c, c1 from tc) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Left side of predicate references base column, right side
+-- references aggregate; predicate is pushed through to non-
+-- flattenable nested subquery.
+select x1.c1 from
+ (select xx1.c from
+ (select c, c1 from tc) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select count(*) from t3 union select count(*) from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Left side of predicate references base column, right side
+-- side references aggregate; predicate is pushed through to
+-- a subquery in a nested union that has literals in its result
+-- column.
+select x1.z1 from
+ (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from
+ (select c1, c2, c3, c from tc) xx1
+ union select 'i','j',j,'i' from t2
+ ) x1 (z1, z2, z3, z4),
+ (select count(*) from t3 union select count (*) from t4) x2 (c2)
+where x1.z3 = x2.c2;
+
+-- Both sides of predicate reference base columns; predicate
+-- predicate is pushed through to a subquery in a nested union
+-- that has literals in its result column.
+select x1.z1 from
+ (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from
+ (select c1, c2, c3, c from tc) xx1
+ union select 'i','j',j,'i' from t2
+ ) x1 (z1, z2, z3, z4),
+ (select a from t3 union select count (*) from t4) x2 (c2)
+where x1.z3 = x2.c2;
+
+-- Same as previous query, but with aggregate/base column
+-- in x2 switched.
+select x1.z1 from
+ (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from
+ (select c1, c2, c3, c from tc) xx1
+ union select 'i','j',j,'i' from t2
+ ) x1 (z1, z2, z3, z4),
+ (select count(*) from t3 union select a from t4) x2 (c2)
+where x1.z3 = x2.c2;
+
+-- Left side references aggregate, right side references base
+-- column; predicate is pushed to non-flattenable subquery
+-- that is part of a nested union for which one child references
+-- a base column and the other references an aggregate.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select a from t3 union select a from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Same as previous query, but both children of inner-most
+-- union reference base columns.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select i from t2
+ ) x1 (c1),
+ (select a from t3 union select a from t4) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Left side references aggregate, right side references base
+-- column; predicate is pushed to non-flattenable subquery
+-- that is part of a nested union for which one child references
+-- a base column and the other references an aggregate.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select i from t2 union select i from t1) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Same as previous query, but one child of x2 references
+-- a literal.
+select x1.c1 from
+ (select count(*) from
+ (select distinct j from t1) xx1
+ union select count(*) from t2
+ ) x1 (c1),
+ (select 1 from t2 union select i from t1) x2 (c2)
+where x1.c1 = x2.c2;
+
+-- Left side of predicate references a base column that is
+-- deeply nested inside a subquery, a union, and a view,
+-- the latter of which itself has a union between two
+-- nested subqueries (whew). And finally, the position of
+-- the base column w.r.t the outer query (x1) is different
+-- than it is with respect to inner view (vz).
+select x1.z4 from
+ (select z1, z4, z3 from vz
+ union select '1', 4, '3' from t1
+ ) x1 (z1, z4, z3),
+ (select distinct j from t2 union select j from t1) x2 (c2)
+where x1.z4 = x2.c2;
+
+-- Same as previous query but with a different nested
+-- view (vz2) that is missing the nested union found
+-- in vz.
+select x1.z4 from
+ (select z1, z4, z3 from vz2
+ union select '1', 4, '3' from t1
+ ) x1 (z1, z4, z3),
+ (select distinct j from t2 union select j from t1) x2 (c2)
+where x1.z4 = x2.c2;
+
+-- Cleanup from this set of tests.
+drop view vz;
+drop view vz2;
+drop table tc;
+
-- Now bump up the size of tables T3 and T4 to the point where
-- use of indexes will cause optimizer to choose nested loop join
-- (and push predicates) instead of hash join. The following