JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "2", "2" }, { "2", "4", "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
RuntimeStatisticsParser p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan", p.usedTableScan());
assertTrue("Expected hash join", p.usedHashJoin());
rs = st.executeQuery("select * from V2, V1 where V1.j = V2.b");
expColNames = new String[] { "A", "B", "I", "J" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2", "1", "2" }, { "4", "4", "2", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan", p.usedTableScan());
assertTrue("Expected hash join", p.usedHashJoin());
// Nested unions.
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2 union select * from t1 union select * from t2 ) "
+ "x1, (select * from t3 union select * from t4 union "
+ "select * from t4 ) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan", p.usedTableScan());
assertTrue("Expected hash join", p.usedHashJoin());
rs = st
.executeQuery("select * from (select * from t1 union all select * "
+ "from t2) x1, (select * from t3 union select * from "
+ "t4) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "8", "4", "4" },
{ "4", "8", "4", "16" }, { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "-8", "4", "4" },
{ "4", "-8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan", p.usedTableScan());
assertTrue("Expected hash join", p.usedHashJoin());
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2) x1, (select * from t3 union all select * from "
+ "t4) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan", p.usedTableScan());
assertTrue("Expected hash join", p.usedHashJoin());
rs = st
.executeQuery("select * from (select * from t1 union all select * "
+ "from t2) x1, (select * from t3 union all select * "
+ "from t4) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "8", "4", "4" },
{ "4", "8", "4", "16" }, { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "-8", "4", "4" },
{ "4", "-8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan", p.usedTableScan());
assertTrue("Expected hash join", p.usedHashJoin());
// 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.
st.executeUpdate("create table tc (c1 char, c2 char, c3 char, c int)");
st.executeUpdate(" 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");
st.executeUpdate(" 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");
st.executeUpdate(" create view vz3 (z1, z2, z3, z4) as select "
+ "distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from "
+ "(select c1, c, c2, 28 from tc) xx1 union select "
+ "'i','j','j',i from t2");
st.executeUpdate(" create view vz4 (z1, z2, z3, z4) as select "
+ "distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from "
+ "(select c1, c, c2, 28 from tc) xx1 union select "
+ "'i','j','j',i from t2 union select c1, c2, c3, c from tc");
// For DERBY-1866. The problem for DERBY-1866 was that,
// when pushing predicates to subqueries beneath UNIONs,
// the predicates were always being pushed to the *first*
// table in the FROM list, regardless of whether or not
// that was actually the correct table. For the test query
// that uses this view (see below) the predicate is
// supposed to be pushed to TC, so in order to repro the
// DERBY-1866 failure we want to make sure that TC is *not*
// the first table in the FROM list. Thus we use the
// optimizer override to fix the join order so that TC is
// the second table.
st.executeUpdate("create view vz5a (z1, z2, z3, z4) as select "
+ "distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from "
+ "(select c1, c2, c3, c from "
+ "--DERBY-PROPERTIES joinOrder=FIXED \n"
+ "t2, tc where tc.c = t2.i) xx1 union "
+ "select 'i','j','j',i from t2");
// Same as above but target FromTable in subquery is
// itself another subquery.
st.executeUpdate("create view vz5b (z1, z2, z3, z4) as select "
+ "distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from "
+ "(select c1, c2, c3, c from --DERBY-PROPERTIES "
+ "joinOrder=FIXED \n t2, (select distinct * from tc) tc "
+ "where tc.c = t2.i) xx1 union select 'i','j','j',i from t2");
// Same as above but target FromTable in subquery is
// another union node between two subqueries.
st.executeUpdate("create view vz5c (z1, z2, z3, z4) as select "
+ "distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from "
+ "(select c1, c2, c3, c from --DERBY-PROPERTIES "
+ "joinOrder=FIXED \n t2, (select * from tc union select "
+ "* from tc) tc where tc.c = t2.i) xx1 union select "
+ "'i','j','j',i from t2");
// Same as above but target FromTable in subquery is
// another full query with unions and subqueries.
st.executeUpdate("create view vz5d (z1, z2, z3, z4) as select "
+ "distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from "
+ "(select c1, c2, c3, c from --DERBY-PROPERTIES "
+ "joinOrder=FIXED \n t2, (select * from tc union select "
+ "z1 c1, z2 c2, z3 c3, z4 c from vz5b) tc where tc.c "
+ "= t2.i) xx1 union select 'i','j','j',i from t2");
// Both sides of predicate reference aggregates.
rs = st
.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// Both sides of predicate reference aggregates, and
// predicate is pushed through to non-flattenable nested
// subquery.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// 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.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// Left side of predicate references base column, right
// side references aggregate; predicate is pushed through
// to non- flattenable nested subquery.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// Left side of predicate references base column, right
// side references aggregate; predicate is pushed through
// to non- flattenable nested subquery.
rs = st
.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// 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.
rs = st
.executeQuery("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");
expColNames = new String[] { "Z1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// 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.
rs = st
.executeQuery("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");
expColNames = new String[] { "Z1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "i" }, { "i" }, { "i" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous query, but with aggregate/base column
// in x2 switched.
rs = st
.executeQuery("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");
expColNames = new String[] { "Z1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// 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.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// Same as previous query, but both children of inner-most
// union reference base columns.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1" }, { "2" }, { "3" }, { "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
// 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.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous query, but one child of x2 references
// a literal.
rs = st.executeQuery("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");
expColNames = new String[] { "C1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
// 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).
rs = st
.executeQuery("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");
expColNames = new String[] { "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "4" }, { "2" }, { "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as above but with an expression ("i+1") instead of
// a numeric literal.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select z1, z4, z3 from vz "
+ "union select '1', i+1, '3' from t1 ) x1 (z1, z4, "
+ "z3), (select distinct j from t2 union select j from "
+ "t1) x2 (c2) where x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2" }, { "4", "4" }, { "6", "6" },
{ "2", "2" }, { "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous query but with a different nested view
// (vz2) that is missing the nested union found in vz.
rs = st
.executeQuery("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");
expColNames = new String[] { "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous query but with a different nested view
// (vz4) that has double-nested unions in it. This is a
// test case for DERBY-1777.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select z1, z4, z3 from "
+ "vz4 union select '1', i+1, '3' from t1 ) x1 (z1, "
+ "z4, z3), (select distinct j from t2 union select j "
+ "from t1) x2 (c2) where x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2" }, { "4", "4" }, { "6", "6" },
{ "2", "2" }, { "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Push outer where predicate down into a UNION having a a
// Select child with more than one table in its FROM list.
// The predicate should be pushed to the correct table in
// the Select's FROM list. Prior to the fix for DERBY-1866
// the predicate was always being pushed to the *first*
// table, regardless of whether or not that was actually
// the correct table. Thus the predicate "t1.i = vz5.z4"
// was getting pushed to table T2 even though it doesn't
// apply there. The result was an ASSERT failure in sane
// mode and an IndexOutOfBounds exception in insane mode.
// NOTE: Use of NESTEDLOOP join strategy ensures the
// predicate will be pushed (otherwise optimizer might
// choose to do a hash join and we wouldn't be testing what
// we want to test).
rs = st
.executeQuery("select t1.i, vz5a.* from t1 left outer join vz5a "
+ "--DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n on t1.i = vz5a.z4");
expColNames = new String[] { "I", "Z1", "Z2", "Z3", "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "i", "j", "j", "1" },
{ "2", "i", "j", "j", "2" }, { "3", "i", "j", "j", "3" },
{ "4", "i", "j", "j", "4" }, { "5", "i", "j", "j", "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same query as above, but without the optimizer
// override. In this case there was another error where
// optimizer state involving the "joinOrder" override (see
// the definition of vz5a) was not properly reset, which
// could lead to an infinite loop. This problem was fixed
// as part of DERBY-1866, as well.
rs = st
.executeQuery("select t1.i, vz5a.* from t1 left outer join vz5a on "
+ "t1.i = vz5a.z4");
expColNames = new String[] { "I", "Z1", "Z2", "Z3", "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "i", "j", "j", "1" },
{ "2", "i", "j", "j", "2" }, { "3", "i", "j", "j", "3" },
{ "4", "i", "j", "j", "4" }, { "5", "i", "j", "j", "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
// More tests for DERBY-1866 using more complicated views.
rs = st
.executeQuery("select t1.i, vz5b.* from t1 left outer join vz5b "
+ "--DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n on t1.i = vz5b.z4");
expColNames = new String[] { "I", "Z1", "Z2", "Z3", "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "i", "j", "j", "1" },
{ "2", "i", "j", "j", "2" }, { "3", "i", "j", "j", "3" },
{ "4", "i", "j", "j", "4" }, { "5", "i", "j", "j", "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st
.executeQuery(" select t1.i, vz5c.* from t1 left outer join vz5c "
+ "--DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n on t1.i = vz5c.z4");
expColNames = new String[] { "I", "Z1", "Z2", "Z3", "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "i", "j", "j", "1" },
{ "2", "i", "j", "j", "2" }, { "3", "i", "j", "j", "3" },
{ "4", "i", "j", "j", "4" }, { "5", "i", "j", "j", "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st
.executeQuery(" select t1.i, vz5d.* from t1 left outer join vz5d "
+ "--DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n on t1.i = vz5d.z4");
expColNames = new String[] { "I", "Z1", "Z2", "Z3", "Z4" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "i", "j", "bokibob", "1" },
{ "1", "i", "j", "j", "1" }, { "2", "i", "j", "bokibob", "2" },
{ "2", "i", "j", "j", "2" }, { "3", "i", "j", "bokibob", "3" },
{ "3", "i", "j", "j", "3" }, { "4", "i", "j", "bokibob", "4" },
{ "4", "i", "j", "j", "4" }, { "5", "i", "j", "bokibob", "5" },
{ "5", "i", "j", "j", "5" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Queries with Select->Union->Select chains having
// differently- ordered result column lists with some
// non-column reference expressions. In all of these
// queries we specify LEFT join and force NESTEDLOOP in
// order to coerce the optimizer to push predicates to a
// specific subquery. We do this to ensure that we test
// predicate pushdown during compilation AND during
// execution. It's the execution-time testing that is
// particular important for verifying DERBY-1633
// functionality. Push predicate to union whose left child
// has a Select within a Select, both of which have the
// same result column ordering.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select z1, z4, z3 from vz "
+ "union select '1', i+1, '3' from t1 ) x1 (z1, z4, "
+ "z3) left join (select distinct i,j from (select "
+ "distinct i,j from t2) x3 union select i, j from t1 "
+ ") x2 (c1, c2) --DERBY-PROPERTIES "
+ "joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2" }, { "3", null }, { "4", "4" },
{ "5", null }, { "6", "6" }, { "1", null }, { "2", "2" },
{ "3", null }, { "4", "4" }, { "5", null } };
JDBC.assertFullResultSet(rs, expRS, true);
// Push predicate to union whose left child has a Select
// within a Select, where the result column lists for the
// two Selects are different ("i,j" vs "j,i").
rs = st
.executeQuery("select x1.z4, x2.c2 from (select z1, z4, z3 from vz "
+ "union select '1', i+1, '3' from t1 ) x1 (z1, z4, "
+ "z3) left join (select distinct i,j from (select "
+ "distinct j,i from t2) x3 union select i, j from t1 "
+ ") x2 (c1, c2) --DERBY-PROPERTIES "
+ "joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2" }, { "3", null }, { "4", "4" },
{ "5", null }, { "6", "6" }, { "1", null }, { "2", "2" },
{ "3", null }, { "4", "4" }, { "5", null } };
JDBC.assertFullResultSet(rs, expRS, true);
// Push predicate to union whose left child is itself a
// nested subquery (through use of the view "vz") and whose
// right child has an expression in its result column list.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select distinct i,j from "
+ "(select distinct j,i from t2) x3 union select i, j "
+ "from t1) x2 (c1, c2) left join (select z1, z4, z3 "
+ "from vz union select '1', i+1, '3' from t1 ) x1 "
+ "(z1, z4, z3) --DERBY-PROPERTIES "
+ "joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2" }, { "2", "2" }, { null, "-4" },
{ "4", "4" }, { "4", "4" }, { "6", "6" }, { null, "-8" },
{ null, "8" }, { null, "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous but with a different expression.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select distinct i,j from "
+ "(select distinct j,i from t2) x3 union select i, j "
+ "from t1) x2 (c1, c2) left join (select z1, z4, z3 "
+ "from vz union select '1', sin(i), '3' from t1 ) x1 "
+ "(z1, z4, z3) --DERBY-PROPERTIES "
+ "joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2.0", "2" }, { null, "-4" },
{ "4.0", "4" }, { null, "6" }, { null, "-8" }, { null, "8" },
{ null, "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous but expression replaced with a regular
// column reference.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select distinct i,j from "
+ "(select distinct j,i from t2) x3 union select i, j "
+ "from t1) x2 (c1, c2) left join (select z1, z4, z3 "
+ "from vz union select '1', i, '3' from t1 ) x1 (z1, "
+ "z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n"
+ "on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2" }, { "2", "2" }, { null, "-4" },
{ "4", "4" }, { "4", "4" }, { null, "6" }, { null, "-8" },
{ null, "8" }, { null, "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Same as previous but with a different expression and a
// different subquery (this time using view "vz3").
rs = st
.executeQuery("select x1.z4, x2.c2 from (select distinct i,j from "
+ "(select distinct j,i from t2) x3 union select i, j "
+ "from t1) x2 (c1, c2) left join (select z1, z4, z3 "
+ "from vz3 union select '1', sin(i), '3' from t1 ) x1 "
+ "(z1, z4, z3) --DERBY-PROPERTIES "
+ "joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2.0", "2" }, { null, "-4" },
{ "4.0", "4" }, { null, "6" }, { null, "-8" }, { null, "8" },
{ null, "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Push predicate to chain of unions whose left-most child
// is itself a nested subquery (through use of the view
// "vz") and in which the other unions have expressions in
// their result column lists.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select distinct i,j from "
+ "(select distinct j,i from t2) x3 union select i, j "
+ "from t1) x2 (c1, c2) left join (select z1, z4, z3 "
+ "from vz union select '1', sin(i), '3' from t1 union "
+ "select '1', 14, '3' from t1 ) x1 (z1, z4, z3) "
+ "--DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2.0", "2" }, { null, "-4" },
{ "4.0", "4" }, { null, "6" }, { null, "-8" }, { null, "8" },
{ null, "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Push predicate to chain of unions whose right-most
// child is itself a nested subquery (through use of the
// view "vz") and in which the other unions have
// expressions in their result column lists.
rs = st
.executeQuery("select x1.z4, x2.c2 from (select distinct i,j from "
+ "(select distinct j,i from t2) x3 union select i, j "
+ "from t1) x2 (c1, c2) left join (select '1', sin(i), "
+ "'3' from t1 union select '1', 14, '3' from t1 union "
+ "select z1, z4, z3 from vz ) x1 (z1, z4, z3) "
+ "--DERBY-PROPERTIES joinStrategy=NESTEDLOOP \n on x1.z4 = x2.c2");
expColNames = new String[] { "Z4", "C2" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2.0", "2" }, { null, "-4" },
{ "4.0", "4" }, { null, "6" }, { null, "-8" }, { null, "8" },
{ null, "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Cleanup from this set of tests.
st.executeUpdate("drop view vz");
st.executeUpdate(" drop view vz2");
st.executeUpdate(" drop view vz3");
st.executeUpdate(" drop view vz4");
st.executeUpdate(" drop view vz5a");
st.executeUpdate(" drop view vz5d");
st.executeUpdate(" drop view vz5b");
st.executeUpdate(" drop view vz5c");
st.executeUpdate(" 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 insertions put roughly 50,000 rows
// into T3 and into T4. These numbers are somewhat
// arbitrary, but please note that reducing the number of
// rows in these two tables could cause the optimizer to
// choose to skip pushing and instead use a hash join for
// some of the test queries. That's not 'wrong' per se,
// but it's not what we want to test here...
getConnection().setAutoCommit(false);
st.executeUpdate(" insert into t3 (a) values 21, 22, 23, 24, 25, 26, "
+ "27, 28, 29, 30");
st.executeUpdate(" insert into t3 (a) values 31, 32, 33, 34, 35, 36, "
+ "37, 38, 39, 40");
st.executeUpdate(" insert into t3 (a) values 41, 42, 43, 44, 45, 46, "
+ "47, 48, 49, 50");
st.executeUpdate(" insert into t3 (a) values 51, 52, 53, 54, 55, 56, "
+ "57, 58, 59, 60");
st.executeUpdate(" insert into t3 (a) values 61, 62, 63, 64, 65, 66, "
+ "67, 68, 69, 70");
st.executeUpdate(" insert into t3 (a) values 71, 72, 73, 74, 75, 76, "
+ "77, 78, 79, 80");
st.executeUpdate(" insert into t3 (a) values 81, 82, 83, 84, 85, 86, "
+ "87, 88, 89, 90");
st.executeUpdate(" insert into t3 (a) values 91, 92, 93, 94, 95, 96, "
+ "97, 98, 99, 100");
assertUpdateCount(st, 80, " update t3 set b = 2 * a where a > 20");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 20)");
st
.executeUpdate(" insert into t4 (a, b) (select a,b from t3 where a > 20)");
st
.executeUpdate(" insert into t3 (a, b) (select a,b from t4 where a > 60)");
commit();
getConnection().setAutoCommit(true);
// See exactly how many rows we inserted, for sanity.
rs = st.executeQuery("select count(*) from t3");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "54579" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(" select count(*) from t4");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "48812" } };
JDBC.assertFullResultSet(rs, expRS, true);
// At this point we create the indexes. Note that we
// intentionally create the indexes AFTER loading the data,
// in order ensure that the index statistics are correct.
// We need the stats to be correct in order for the
// optimizer to choose the correct plan (i.e. to push the
// join predicates where possible).
st
.executeUpdate("CREATE INDEX \"APP\".\"T3_IX1\" ON \"APP\".\"T3\" (\"A\")");
st
.executeUpdate(" CREATE INDEX \"APP\".\"T3_IX2\" ON \"APP\".\"T3\" (\"B\")");
st
.executeUpdate(" CREATE INDEX \"APP\".\"T4_IX1\" ON \"APP\".\"T4\" (\"A\")");
st
.executeUpdate(" CREATE INDEX \"APP\".\"T4_IX2\" ON \"APP\".\"T4\" (\"B\")");
// Create the rest of objects used in this test.
st
.executeUpdate("CREATE TABLE \"APP\".\"T5\" (\"I\" INTEGER, \"J\" INTEGER)");
st.executeUpdate(" insert into t5 values (5, 10)");
st
.executeUpdate(" CREATE TABLE \"APP\".\"T6\" (\"P\" INTEGER, \"Q\" INTEGER)");
st.executeUpdate(" insert into t5 values (2, 4), (4, 8)");
st.executeUpdate(" CREATE TABLE \"APP\".\"XX1\" (\"II\" INTEGER NOT "
+ "NULL, \"JJ\" CHAR(10), \"MM\" INTEGER, \"OO\" "
+ "DOUBLE, \"KK\" BIGINT)");
st.executeUpdate(" CREATE TABLE \"APP\".\"YY1\" (\"II\" INTEGER NOT "
+ "NULL, \"JJ\" CHAR(10), \"AA\" INTEGER, \"OO\" "
+ "DOUBLE, \"KK\" BIGINT)");
st.executeUpdate(" ALTER TABLE \"APP\".\"YY1\" ADD CONSTRAINT "
+ "\"PK_YY1\" PRIMARY KEY (\"II\")");
st.executeUpdate(" ALTER TABLE \"APP\".\"XX1\" ADD CONSTRAINT "
+ "\"PK_XX1\" PRIMARY KEY (\"II\")");
st.executeUpdate(" create view xxunion as select all ii, jj, kk, mm "
+ "from xx1 union all select ii, jj, kk, mm from xx1 "
+ "union all select ii, jj, kk, mm from xx1 union all "
+ "select ii, jj, kk, mm from xx1 union all select ii, "
+ "jj, kk, mm from xx1 union all select ii, jj, kk, mm "
+ "from xx1 union all select ii, jj, kk, mm from xx1 "
+ "union all select ii, jj, kk, mm from xx1 union all "
+ "select ii, jj, kk, mm from xx1 union all select ii, "
+ "jj, kk, mm from xx1 union all select ii, jj, kk, mm "
+ "from xx1 union all select ii, jj, kk, mm from xx1 "
+ "union all select ii, jj, kk, mm from xx1 union all "
+ "select ii, jj, kk, mm from xx1 union all select ii, "
+ "jj, kk, mm from xx1 union all select ii, jj, kk, mm "
+ "from xx1 union all select ii, jj, kk, mm from xx1 "
+ "union all select ii, jj, kk, mm from xx1 union all "
+ "select ii, jj, kk, mm from xx1 union all select ii, "
+ "jj, kk, mm from xx1 union all select ii, jj, kk, mm "
+ "from xx1 union all select ii, jj, kk, mm from xx1 "
+ "union all select ii, jj, kk, mm from xx1 union all "
+ "select ii, jj, kk, mm from xx1 union all select ii, "
+ "jj, kk, mm from xx1");
st.executeUpdate(" create view yyunion as select all ii, jj, kk, aa "
+ "from yy1 union all select ii, jj, kk, aa from yy1 "
+ "union all select ii, jj, kk, aa from yy1 union all "
+ "select ii, jj, kk, aa from yy1 union all select ii, "
+ "jj, kk, aa from yy1 union all select ii, jj, kk, aa "
+ "from yy1 union all select ii, jj, kk, aa from yy1 "
+ "union all select ii, jj, kk, aa from yy1 union all "
+ "select ii, jj, kk, aa from yy1 union all select ii, "
+ "jj, kk, aa from yy1 union all select ii, jj, kk, aa "
+ "from yy1 union all select ii, jj, kk, aa from yy1 "
+ "union all select ii, jj, kk, aa from yy1 union all "
+ "select ii, jj, kk, aa from yy1 union all select ii, "
+ "jj, kk, aa from yy1 union all select ii, jj, kk, aa "
+ "from yy1 union all select ii, jj, kk, aa from yy1 "
+ "union all select ii, jj, kk, aa from yy1 union all "
+ "select ii, jj, kk, aa from yy1 union all select ii, "
+ "jj, kk, aa from yy1 union all select ii, jj, kk, aa "
+ "from yy1 union all select ii, jj, kk, aa from yy1 "
+ "union all select ii, jj, kk, aa from yy1 union all "
+ "select ii, jj, kk, aa from yy1 union all select ii, "
+ "jj, kk, aa from yy1");
// Run compression on the test tables to try to get a
// consistent set of row count stats for the tables
// (DERBY-1902, DERBY-3479).
cSt = prepareCall("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 1)");
assertUpdateCount(cSt, 0);
cSt = prepareCall(" call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T2', 1)");
assertUpdateCount(cSt, 0);
cSt = prepareCall(" call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T3', 1)");
assertUpdateCount(cSt, 0);
cSt = prepareCall(" call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T4', 1)");
assertUpdateCount(cSt, 0);
cSt = prepareCall(" call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T5', 1)");
assertUpdateCount(cSt, 0);
cSt = prepareCall(" call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T6', 1)");
assertUpdateCount(cSt, 0);
// And finally, run more extensive tests using the larger
// tables that have indexes. In these tests the optimizer
// should consider pushing predicates where possible. We
// can tell if a predicate has been "pushed" by looking at
// the query plan information for the tables in question:
// if the table has an index on a column that is used as
// part of the pushed predicate, then the optimizer will
// (for these tests) do an Index scan instead of a Table
// scan. If the table does not have such an index then the
// predicate will show up as a "qualifier" for a Table
// scan. In all of these tests T3 and T4 have appropriate
// indexes, so if we push a predicate to either of those
// tables we should see index scans. Neither T1 nor T2 has
// indexes, so if we push a predicate to either of those
// tables we should see a qualifier in the table scan
// information. Predicate push-down should occur for next
// two queries. Thus we we should see Index scans for T3
// and T4--and this should be the case regardless of the
// order of the FROM list.
rs = st.executeQuery("select * from V1, V2 where V1.j = V2.b");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "2", "2" }, { "2", "4", "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
rs = st.executeQuery("select * from V2, V1 where V1.j = V2.b");
expColNames = new String[] { "A", "B", "I", "J" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "2", "1", "2" }, { "4", "4", "2", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Changes for DERBY-805 don't affect non-join predicates
// (ex. "IN" or one- sided predicates), but make sure
// things still behave--i.e. these queries should still
// compile and execute without error. We don't expect to
// see any predicates pushed to T3 nor T4.
rs = st.executeQuery("select count(*) from V1, V2 where V1.i in (2,4)");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "404" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan on T3", p.usedTableScan("T3"));
assertTrue("Expected table scan on T4", p.usedTableScan("T4"));
rs = st.executeQuery("select count(*) from V1, V2 where V1.j > 0");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "505" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan on T3", p.usedTableScan("T3"));
assertTrue("Expected table scan on T4", p.usedTableScan("T4"));
// Combination of join predicate and non-join predicate:
// the join predicate should be pushed to V2 (T3 and T4),
// the non-join predicate should operate as usual.
rs = st
.executeQuery("select * from V1, V2 where V1.j = V2.b and V1.i in (2,4)");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "4", "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Make
// sure predicates are pushed even if the subquery is
// explicit (as opposed to a view). Should see index scans
// on T3 and T4.
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2) x1, (select * from t3 union select * from t4) "
+ "x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// In this case optimizer will consider pushing predicate
// to X1 but will choose not to because it's cheaper to
// push the predicate to T3. So should see regular table
// scans on T1 and T2.
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2) x1, t3 where x1.i = t3.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "4", "-8", "4", "4" },
{ "4", "8", "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected Table Scan ResultSet for T1", p.usedTableScan("T1"));
assertTrue("Expected Table Scan ResultSet for T2", p.usedTableScan("T2"));
// UNION
// ALL should behave just like normal UNION. I.e.
// predicates should still be pushed to T3 and T4.
rs = st
.executeQuery("select * from (select * from t1 union all select * "
+ "from t2) x1, (select * from t3 union select * from "
+ "t4) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "8", "4", "4" },
{ "4", "8", "4", "16" }, { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "-8", "4", "4" },
{ "4", "-8", "4", "16" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
rs = st
.executeQuery("select * from (select * from t1 union all select * "
+ "from t2) x1, (select * from t3 union all select * "
+ "from t4) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "8", "4", "4" },
{ "4", "8", "4", "16" }, { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "12" }, { "4", "-8", "4", "4" },
{ "4", "-8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Predicate with both sides referencing same UNION isn't a
// join predicate, so no pushing should happen. So should
// see regular table scans on all tables.
rs = st.executeQuery("select * from v1, v2 where V1.i = V1.j");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertEmpty(rs);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected Table Scan ResultSet for T1", p.usedTableScan("T1"));
assertTrue("Expected Table Scan ResultSet for T2", p.usedTableScan("T2"));
assertTrue("Expected Table Scan ResultSet for T3", p.usedTableScan("T3"));
assertTrue("Expected Table Scan ResultSet for T4", p.usedTableScan("T4"));
// Pushing predicates should still work even if user
// specifies explicit column names. In these two queries
// we push to X2 (T3 and T4).
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2) x1 (c, d), (select * from t3 union select * "
+ "from t4) x2 (e, f) where x1.c = x2.e");
expColNames = new String[] { "C", "D", "E", "F" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2) x1 (a, b), (select * from t3 union select * "
+ "from t4) x2 (i, j) where x1.a = x2.i");
expColNames = new String[] { "A", "B", "I", "J" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// In this query the optimizer will consider pushing, but
// will find that it's cheaper to do a hash join and thus
// will _not_ push. So we see hash join with table scan on T3.
rs = st
.executeQuery("select count(*) from (select * from t1 union select "
+ "* from t3) x1 (c, d), (select * from t2 union "
+ "select * from t4) x2 (e, f) where x1.c = x2.e");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "103" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected Table Scan ResultSet for T3", p.usedTableScan("T3"));
assertTrue("Expected Hash Join",p.usedHashJoin());
// If we
// have nested unions, the predicate should get pushed all
// the way down to the base table(s) for every level of
// nesting. Should see index scans for T3 and for _both_
// instances of T4.
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2 union select * from t1 union select * from t2 ) "
+ "x1, (select * from t3 union select * from t4 union "
+ "select * from t4 ) x2 where x1.i = x2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Nested unions with non-join predicates should work as
// usual (no change with DERBY-805). So should see scalar
// qualifiers on scans for all instances of T1 and T2.
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2 union select * from t1 union select * from t2 ) "
+ "x1 where x1.i > 0");
expColNames = new String[] { "I", "J" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2" }, { "2", "-4" }, { "2", "4" },
{ "3", "6" }, { "4", "-8" }, { "4", "8" }, { "5", "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
// Expect to see scalar qualifiers with <= operator for four scans.
p.findString("Operator: <=", 4);
// In this
// case there are no qualifiers, but the restriction is
// enforced at the ProjectRestrictNode level. That hasn't
// changed with DERBY-805.
rs = st
.executeQuery("select count(*) from (select * from t1 union select "
+ "* from t2 union select * from t3 union select * "
+ "from t4 ) x1 (i, b) where x1.i > 0");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "108" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected Table Scan ResultSet for T1", p.usedTableScan("T1"));
assertTrue("Expected Table Scan ResultSet for T2", p.usedTableScan("T2"));
assertTrue("Expected Table Scan ResultSet for T3", p.usedTableScan("T3"));
assertTrue("Expected Table Scan ResultSet for T4", p.usedTableScan("T4"));
// Predicate pushdown should work with explicit use of
// "inner join" just like it does for implicit join. So
// should see index scans on T3 and T4.
rs = st
.executeQuery("select * from (select * from t1 union select * from "
+ "t2) x1 inner join (select * from t3 union select * "
+ "from t4) x2 on x1.j = x2.b");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "2", "2" }, { "2", "4", "4", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Can't push predicates into VALUES clauses. Predicate should
// end up at V2 (T3 and T4).
rs = st.executeQuery("select * from ( select i,j from t2 union values "
+ "(1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) "
+ "x0 (i,j), v2 where x0.i = v2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "1", "1", "1" },
{ "1", "2", "1", "1" }, { "2", "-4", "2", "2" },
{ "2", "2", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "3", "3", "3" }, { "3", "3", "3", "12" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "16" },
{ "4", "4", "4", "4" }, { "4", "4", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "16" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Can't push predicates into VALUES clauses. Optimizer
// might consider pushing but shouldn't do it; in the end
// we'll do a hash join between X1 and T2.
rs = st
.executeQuery("select * from t2, (select * from t1 union values "
+ "(3,3), (4,4), (5,5), (6,6)) X1 (a,b) where X1.a = t2.i");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "2" },
{ "2", "-4", "2", "4" }, { "3", "6", "3", "3" },
{ "3", "6", "3", "6" }, { "4", "-8", "4", "4" },
{ "4", "-8", "4", "8" }, { "5", "10", "5", "5" },
{ "5", "10", "5", "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected Hash Join", p.usedHashJoin());
// Can't
// push predicates into VALUES clause. We'll try to push
// it to X1, but it will only make it to T4; it won't make
// it to T3 because the "other side" of the union with T3
// is a VALUES clause. So we'll see an index scan on T4
// and table scan on T3--but the predicate should still be
// applied to T3 at a higher level (through a
// ProjectRestrictNode), so we shouldn't get any extra rows.
rs = st.executeQuery("select * from (select i,j from t2 union values "
+ "(1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) "
+ "x0 (i,j), (select a, b from t3 union values (4, 5), "
+ "(5, 6), (6, 7) union select a, b from t4 ) x1 (a,b) "
+ "where x0.i = x1.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "1", "1", "1" },
{ "1", "2", "1", "1" }, { "2", "-4", "2", "2" },
{ "2", "2", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "3", "3", "3" }, { "3", "3", "3", "12" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "-8", "4", "5" },
{ "4", "-8", "4", "16" }, { "4", "4", "4", "4" },
{ "4", "4", "4", "5" }, { "4", "4", "4", "16" },
{ "4", "8", "4", "4" }, { "4", "8", "4", "5" },
{ "4", "8", "4", "16" }, { "5", "10", "5", "6" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan on T3", p.usedTableScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Make sure optimizer is still considering predicates for
// other, non-UNION nodes. Here we should use the
// predicate to do a hash join between X0 and T5 (i.e. we
// will not push it down to X0 because a) there are VALUES
// clauses to which we can't push, and b) it's cheaper to
// do the hash join).
rs = st
.executeQuery("select * from t5, (values (2,2), (4,4) union values "
+ "(1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) "
+ "x0 (i,j) where x0.i = t5.i");
expColNames = new String[] { "I", "J", "I", "J" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "4", "2", "2" },
{ "2", "4", "2", "4" }, { "4", "8", "4", "4" },
{ "4", "8", "4", "8" }, { "5", "10", "5", "10" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected hash join", p.usedHashJoin());
// When we
// have very deeply nested union queries, make sure
// predicate push- down logic still works (esp. the scoping
// logic). These queries won't return any results, but the
// predicate should get pushed to EVERY instance of the
// base table all the way down. We're just checking to
// make sure these compile and execute without error. The
// query plan for these two queries alone would be several
// thousand lines so we don't print them out. We have
// other (smaller) tests to check that predicates are
// correctly pushed through nested unions.
rs = st
.executeQuery("select distinct xx0.kk, xx0.ii, xx0.jj from xxunion "
+ "xx0, yyunion yy0 where xx0.mm = yy0.ii");
expColNames = new String[] { "KK", "II", "JJ" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertEmpty(rs);
rs = st.executeQuery("values (1)");
rs.next();
rsmd = rs.getMetaData();
pSt = prepareStatement("select distinct "
+ "xx0.kk, xx0.ii, xx0.jj from " + "xxunion xx0, "
+ "yyunion yy0 " + "where xx0.mm = yy0.ii and yy0.aa in (?) "
+ "for fetch only");
for (int i = 1; i <= rsmd.getColumnCount(); i++)
pSt.setObject(i, rs.getObject(i));
rs = pSt.executeQuery();
expColNames = new String[] { "KK", "II", "JJ" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// Predicate push-down should only affect the UNIONs
// referenced; other UNIONs shouldn't interfere or be
// affected. Should see table scans for T1 and T2 then an
// index scan for the first instance of T3 and a table scan
// for second instance of T3; likewise for two instances of T4.
rs = st
.executeQuery("select count(*) from (select * from t1 union select "
+ "* from t2) x1, (select * from t3 union select * "
+ "from t4) x2, (select * from t4 union select * from "
+ "t3) x3 where x1.i = x3.a");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "909" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan on T1", p.usedTableScan("T1"));
assertTrue("Expected table scan on T2", p.usedTableScan("T2"));
assertTrue("Expected table scan on T3", p.usedTableScan("T3"));
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected table scan on T4", p.usedTableScan("T4"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Here we
// should see index scans for both instances of T3 and for
// both instances of T4.
rs = st
.executeQuery("select count(*) from (select * from t1 union select "
+ "* from t2) x1, (select * from t3 union select * "
+ "from t4) x2, (select * from t4 union select * from "
+ "t3) x3 where x1.i = x3.a and x3.b = x2.b");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "9" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Predicates pushed from outer queries shouldn't
// interfere with inner predicates for subqueries. Mostly
// checking for correct results here.
rs = st
.executeQuery("select * from (select i, b j from t1, t4 where i = "
+ "j union select * from t2) x1, t3 where x1.j = t3.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "2", "2" },
{ "3", "6", "6", "24" }, { "5", "10", "10", "40" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Inner predicate should be handled as normal, outer
// predicate should either get pushed to V2 (T3 and T4) or
// else used for a hash join between x1 and v2.
rs = st
.executeQuery("select * from (select i, b j from t1, t4 where i = "
+ "j union select * from t2) x1, v2 where x1.j = v2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "2", "2" },
{ "3", "6", "6", "24" }, { "5", "10", "10", "40" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected hash join", p.usedHashJoin());
// Outer
// predicate should either get pushed to V2 (T3 and T4) or
// else used for a hash join; similarly, inner predicate
// should either get pushed to T3 or else used for hash
// join between T1 and T3.
rs = st
.executeQuery("select * from (select i, j from t1, t3 where i = a "
+ "union select * from t2) x1, v2 where x1.i = v2.a");
expColNames = new String[] { "I", "J", "A", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "1", "1" },
{ "2", "-4", "2", "2" }, { "2", "4", "2", "2" },
{ "3", "6", "3", "3" }, { "3", "6", "3", "12" },
{ "4", "-8", "4", "4" }, { "4", "8", "4", "4" },
{ "4", "-8", "4", "16" }, { "4", "8", "4", "16" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected hash join", p.usedHashJoin());
// Inner predicates treated as restrictions, outer
// predicate either pushed to X2 (T2 and T1) or used for
// hash join between X2 and X1.
rs = st
.executeQuery("select * from (select i, b j from t1, t4 where i = "
+ "j union select * from t2) x1, (select i, b j from "
+ "t2, t3 where i = j union select * from t1) x2 where "
+ "x1.j = x2.i");
expColNames = new String[] { "I", "J", "I", "J" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "2", "2", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected hash join", p.usedHashJoin());
// Following queries deal with nested subqueries, which
// deserve extra testing because "best paths" for outer
// queries might not agree with "best paths" for inner
// queries, so we need to make sure the correct paths
// (based on predicates that are or are not pushed) are
// ultimately generated. Predicate should get pushed to V2
// (T3 and T4).
rs = st
.executeQuery("select count(*) from (select i,a,j,b from V1, V2 "
+ "where V1.j = V2.b ) X3");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Multiple subqueries but NO UNIONs. All predicates are
// used for joins at their current level (no pushing).
rs = st.executeQuery("select t2.i,p from (select distinct i,p from "
+ "(select distinct i,a from t1, t3 where t1.j = t3.b) "
+ "X1, t6 where X1.a = t6.p) X2, t2 where t2.i = X2.i");
expColNames = new String[] { "I", "P" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected hash join", p.usedHashJoin());
assertTrue("Expected table scan on T1", p.usedTableScan("T1"));
assertTrue("Expected index row to base row for T3", p.usedIndexRowToBaseRow("T3"));
// Multiple, non-flattenable subqueries, but NO UNIONs. Shouldn't push
// anything.
rs = st
.executeQuery("select x1.j, x2.b from (select distinct i,j from "
+ "t1) x1, (select distinct a,b from t3) x2 where x1.i "
+ "= x2.a order by x1.j, x2.b");
expColNames = new String[] { "J", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "1" }, { "4", "2" }, { "6", "3" },
{ "8", "4" } };
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected distinct scan on T1", p.usedDistinctScan("T1"));
assertTrue("Expected distinct scan T3", p.usedDistinctScan("T3"));
rs = st
.executeQuery("select x1.j, x2.b from (select distinct i,j from "
+ "t1) x1, (select distinct a,b from t3) x2, (select "
+ "distinct i,j from t2) x3, (select distinct a,b from "
+ "t4) x4 where x1.i = x2.a and x3.i = x4.a order by x1.j, x2.b");
expColNames = new String[] { "J", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "2", "1" }, { "2", "1" }, { "4", "2" },
{ "4", "2" }, { "6", "3" }, { "6", "3" }, { "8", "4" },
{ "8", "4" }};
JDBC.assertFullResultSet(rs, expRS, true);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected distinct scan on T1", p.usedDistinctScan("T1"));
assertTrue("Expected distinct scan T2", p.usedDistinctScan("T2"));
assertTrue("Expected distinct scan on T3", p.usedDistinctScan("T3"));
assertTrue("Expected distinct scan T4", p.usedDistinctScan("T4"));
// Multiple subqueries that are UNIONs. Outer-most
// predicate X0.b = X2.j can be pushed to union X0 but NOT
// to subquery X2. Inner predicate T6.p = X1.i is eligible
// for being pushed into union X1. In this case outer
// predicate is pushed to X0 (so we'll see index scans on
// T3 and T4) but inner predicate is used for a hash join
// between X1 and T6.
rs = st
.executeQuery("select X0.a, X2.i from (select a,b from t4 union "
+ "select a,b from t3) X0, (select i,j from (select "
+ "i,j from t1 union select i,j from t2) X1, T6 where "
+ "T6.p = X1.i) X2 where X0.b = X2.j ");
expColNames = new String[] { "A", "I" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected hash join", p.usedHashJoin());
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Same as
// above but without the inner predicate (so no hash on T6).
rs = st
.executeQuery("select X0.a, X2.i from (select a,b from t4 union "
+ "select a,b from t3) X0, (select i,j from (select "
+ "i,j from t1 union select i,j from t2) X1, T6 ) X2 "
+ "where X0.b = X2.j ");
expColNames = new String[] { "A", "I" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertEmpty(rs);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected index scan on T3", p.usedIndexScan("T3"));
assertTrue("Expected index scan on T4", p.usedIndexScan("T4"));
// Same as above, but without the outer predicate. Should
// see table scan on T3 and T4 (because nothing is pushed).
rs = st
.executeQuery("select X0.a, X2.i from (select a,b from t4 union "
+ "select a,b from t3) X0, (select i,j from (select "
+ "i,j from t1 union select i,j from t2) X1, T6 where "
+ "T6.p = X1.i) X2 ");
expColNames = new String[] { "A", "I" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
p = SQLUtilities.getRuntimeStatisticsParser(st);
assertTrue("Expected table scan on T3", p.usedTableScan("T3"));
assertTrue("Expected table scan on T4", p.usedTableScan("T4"));
// Additional tests with VALUES clauses. Mostly just
// checking to make sure these queries compile and execute,
// and to ensure that all predicates are enforced even if
// they can't be pushed all the way down into a UNION. So