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 km...@apache.org on 2008/05/28 22:28:16 UTC
svn commit: r661071 [2/3] -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java?rev=661071&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java Wed May 28 13:28:16 2008
@@ -0,0 +1,2636 @@
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Statement;
+import java.sql.CallableStatement;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLWarning;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.PredicatePushdownTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You 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.
+
+ */
+public final class PredicatePushdownTest extends BaseJDBCTestCase {
+
+ /**
+ * Public constructor required for running test as standalone JUnit.
+ */
+ public PredicatePushdownTest(String name) {
+ super(name);
+ }
+
+ public static Test suite() {
+ TestSuite suite = new TestSuite("predicatePushdown Test");
+ suite.addTest(new CleanDatabaseTestSetup(TestConfiguration
+ .embeddedSuite(PredicatePushdownTest.class)));
+ return suite;
+ }
+
+ public void test_predicatePushdown() throws Exception {
+ ResultSet rs = null;
+ ResultSetMetaData rsmd;
+ SQLWarning sqlWarn = null;
+
+ PreparedStatement pSt;
+ CallableStatement cSt;
+ Statement st = createStatement();
+
+ String[][] expRS;
+ String[] expColNames;
+
+ // Licensed to the Apache Software Foundation (ASF)
+ // under one or more contributor license agreements. See
+ // the NOTICE file distributed with this work for
+ // additional information regarding copyright ownership.
+ // The ASF licenses this file to You 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. Test predicate
+ // pushdown into expressions in a FROM list. As of
+ // DERBY-805 this test only looks at pushing predicates
+ // into UNION operators, but this test will likely grow as
+ // additional predicate pushdown functionality is added to
+ // Derby. Note that "noTimeout" is set to true for this
+ // test because we print out a lot of query plans and we
+ // don't want the plans to differ from one machine to
+ // another (which can happen if some machines are faster
+ // than others when noTimeout is false). Create the basic
+ // tables/views for DERBY-805 testing.
+
+ st
+ .executeUpdate("CREATE TABLE \"APP\".\"T1\" (\"I\" INTEGER, \"J\" INTEGER)");
+
+ st.executeUpdate(" insert into t1 values (1, 2), (2, 4), (3, 6), (4, "
+ + "8), (5, 10)");
+
+ st
+ .executeUpdate(" CREATE TABLE \"APP\".\"T2\" (\"I\" INTEGER, \"J\" INTEGER)");
+
+ st.executeUpdate(" insert into t2 values (1, 2), (2, -4), (3, 6), (4, "
+ + "-8), (5, 10)");
+
+ st
+ .executeUpdate(" CREATE TABLE \"APP\".\"T3\" (\"A\" INTEGER, \"B\" INTEGER)");
+
+ st.executeUpdate(" insert into T3 values (1,1), (2,2), (3,3), (4,4), "
+ + "(6, 24), (7, 28), (8, 32), (9, 36), (10, 40)");
+
+ st.executeUpdate(" insert into t3 (a) values 11, 12, 13, 14, 15, 16, "
+ + "17, 18, 19, 20");
+
+ assertUpdateCount(st, 10, " update t3 set b = 2 * a where a > 10");
+
+ st
+ .executeUpdate(" CREATE TABLE \"APP\".\"T4\" (\"A\" INTEGER, \"B\" INTEGER)");
+
+ st.executeUpdate(" insert into t4 values (3, 12), (4, 16)");
+
+ st.executeUpdate(" insert into t4 (a) values 11, 12, 13, 14, 15, 16, "
+ + "17, 18, 19, 20");
+
+ assertUpdateCount(st, 10, " update t4 set b = 2 * a where a > 10");
+
+ st.executeUpdate(" create view V1 as select i, j from T1 union select "
+ + "i,j from T2");
+
+ st.executeUpdate(" create view V2 as select a,b from T3 union select "
+ + "a,b from T4");
+
+ // 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);
+
+ // Now that we have the basic tables and views for the
+ // tests, run some quick queries to make sure that the
+ // optimizer will still consider NOT pushing the predicates
+ // and will instead do a hash join. The optimizer should
+ // choose do this so long as doing so is the best choice,
+ // which usually means that we don't have indexes on the
+ // tables or else we have relatively small tables. Start
+ // by checking the case of small (~20 row) tables. We
+ // should see hash joins and table scans in ALL of these
+ // cases. call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)
+ // maximumdisplaywidth 20000 Basic cases.
+
+ 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ 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);
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() UNION ALLs.
+
+ 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);
+
+ 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ 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);
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ 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);
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ 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);
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+ 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+ // 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);
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+ // 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);
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+
+ 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() 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);
+ //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+ // 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);
+
+ // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+ // 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
+ // we shouldn't get back any extra rows here. NOTE: Row
+ // order is not important in these queries, just so long as
+ // the correct rows are returned.
+
+ cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+ assertUpdateCount(cSt, 0);
+
+ rs = st.executeQuery(" select * from (select * from t1 union select * "
+ + "from t2) x1, (values (2, 4), (3, 6), (4, 8)) x2 (a, "
+ + "b) where x1.i = x2.a");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String[][] { { "2", "-4", "2", "4" },
+ { "2", "4", "2", "4" }, { "3", "6", "3", "6" },
+ { "4", "-8", "4", "8" }, { "4", "8", "4", "8" } };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // ---------------------------------------------
+
+ rs = st.executeQuery("select * from"
+ + "(select * from t1 union (values (1, -1), (2, "
+ + "-2), (5, -5))) x1 (i, j),"
+ + "(values (2, 4), (3, 6), (4, 8)) x2 (a, b)"
+ + "where x1.i = x2.a");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String[][] { { "2", "-2", "2", "4" },
+ { "2", "4", "2", "4" }, { "3", "6", "3", "6" },
+ { "4", "8", "4", "8" }};
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st
+ .executeQuery(" select * from (select * from t1 union all (values "
+ + "(1, -1), (2, -2), (5, -5))) x1 (i, j), (values (2, "
+ + "4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String[][] { { "2", "4", "2", "4" },
+ { "3", "6", "3", "6" }, { "4", "8", "4", "8" },
+ { "2", "-2", "2", "4" } };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st
+ .executeQuery(" select * from (select * from t1 union (values (1, "
+ + "-1), (2, -2), (5, -5))) x1 (i, j), (values (2, 4), "
+ + "(3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a and x2.b = x1.j");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String[][] { { "2", "4", "2", "4" },
+ { "3", "6", "3", "6" }, { "4", "8", "4", "8" } };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st
+ .executeQuery(" select * from (values (2, -4), (3, -6), (4, -8) "
+ + "union values (1, -1), (2, -2), (5, -5) ) x1 (i, j), "
+ + "(values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String[][] { { "2", "-4", "2", "4" },
+ { "2", "-2", "2", "4" }, { "3", "-6", "3", "6" },
+ { "4", "-8", "4", "8" } };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st
+ .executeQuery(" select * from (values (2, -4), (3, -6), (4, -8) "
+ + "union values (1, -1), (2, -2), (5, -5) ) x1 (i, j), "
+ + "(values (2, 4), (3, 6), (4, 8)) x2 (a, b) where "
+ + "x1.i = x2.a and x2.b = x1.j");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+ JDBC.assertDrainResults(rs, 0);
+
+ rs = st
+ .executeQuery(" select * from (values (1, -1), (2, -2), (5, -5) "
+ + "union select * from t1) x1 (i,j), (values (2, 4), "
+ + "(3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a");
+
+ expColNames = new String[] { "I", "J", "A", "B" };
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String[][] { { "2", "-2", "2", "4" },
+ { "2", "4", "2", "4" }, { "3", "6", "3", "6" },
+ { "4", "8", "4", "8" } };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // Clean up DERBY-805 objects.
+
+ st.executeUpdate("drop view v1");
+
+ st.executeUpdate(" drop view v2");
+
+ st.executeUpdate(" drop table t1");
+
+ st.executeUpdate(" drop table t2");
+
+ st.executeUpdate(" drop table t3");
+
+ st.executeUpdate(" drop table t4");
+
+ st.executeUpdate(" drop table t5");
+
+ st.executeUpdate(" drop table t6");
+
+ st.executeUpdate(" drop view xxunion");
+
+ st.executeUpdate(" drop view yyunion");
+
+ st.executeUpdate(" drop table xx1");
+
+ st.executeUpdate(" drop table yy1");
+
+ // DERBY-1633: Nested UNIONs of views with different
+ // column orderings leads to incorrectly scoped predicates.
+ // We have a lot of different tables and views here to try
+ // to cover several different situations. Note that all of
+ // the views use DISTINCT because we don't want the views
+ // to be flattened and Derby doesn't flatten select queries
+ // with DISTINCT in them.
+
+ st.executeUpdate("CREATE TABLE \"APP\".\"T1\" (\"I\" INTEGER, \"D\" "
+ + "DOUBLE, \"C\" CHAR(10))");
+
+ st.executeUpdate(" CREATE TABLE \"APP\".\"T2\" (\"I2\" INTEGER, "
+ + "\"D2\" DOUBLE, \"C2\" CHAR(10))");
+
+ st.executeUpdate(" CREATE TABLE \"APP\".\"T3\" (\"I3\" INTEGER, "
+ + "\"D3\" DOUBLE, \"C3\" CHAR(10))");
+
+ st.executeUpdate(" insert into t1 values (1, -1, '1'), (2, -2, '2')");
+
+ st.executeUpdate(" insert into t2 values (2, -2, '2'), (4, -4, '4'), "
+ + "(8, -8, '8')");
+
+ st.executeUpdate(" insert into t3 values (3, -3, '3'), (6, -6, '6'), "
+ + "(9, -9, '9')");
+
+ st.executeUpdate(" CREATE TABLE \"APP\".\"T4\" (\"C4\" CHAR(10))");
+
+ st.executeUpdate(" insert into t4 values '1', '2', '3', '4', '5', "
+ + "'6', '7', '8', '9'");
+
+ st
+ .executeUpdate(" insert into t4 select rtrim(c4) || rtrim(c4) from t4");
+
+ st.executeUpdate(" CREATE TABLE \"APP\".\"T5\" (\"I5\" INTEGER, "
+ + "\"D5\" DOUBLE, \"C5\" CHAR(10))");
+
+ st.executeUpdate(" CREATE TABLE \"APP\".\"T6\" (\"I6\" INTEGER, "
+ + "\"D6\" DOUBLE, \"C6\" CHAR(10))");
+
+ st.executeUpdate(" insert into t5 values (100, 100.0, '100'), (200, "
+ + "200.0, '200'), (300, 300.0, '300')");
+
+ st.executeUpdate(" insert into t6 values (400, 400.0, '400'), (200, "
+ + "200.0, '200'), (300, 300.0, '300')");
+
+ st.executeUpdate(" create view v_keycol_at_pos_3 as select distinct i "
+ + "col1, d col2, c col3 from t1");
+
+ st.executeUpdate(" create view v1_keycol_at_pos_2 as select distinct "
+ + "i2 col1, c2 col3, d2 col2 from t2");
+
+ st.executeUpdate(" create view v2_keycol_at_pos_2 as select distinct "
+ + "i3 col1, c3 col3, d3 col2 from t3");
+
+ st.executeUpdate(" create view v1_intersect as select distinct i5 "
+ + "col1, c5 col3, d5 col2 from t5");
+
+ st.executeUpdate(" create view v2_intersect as select distinct i6 "
+ + "col1, c6 col3, d6 col2 from t6");
+
+ st.executeUpdate(" create view v1_values as select distinct vals1 "
+ + "col1, vals2 col2, vals3 col3 from (values (321, "
+ + "321.0, '321'), (432, 432.0, '432'), (654, 654.0, "
+ + "'654') ) VT(vals1, vals2, vals3)");
+
+ st.executeUpdate(" create view v_union as select distinct i col1, d "
+ + "col2, c col3 from t1 union select distinct i3 col1, "
+ + "d3 col2, c3 col3 from t3");
+
+ // Chain of UNIONs with left-most child as a view with a
+ // an RCL that is ordered differently than that of the
+ // UNIONs above it. The right child of the top-level node
+ // is a view that is a simple select from a table.
+
+ st
+ .executeUpdate("create view topview as (select distinct 'other:' "
+ + "col0, vpos3.col3, vpos3.col1 from v_keycol_at_pos_3 "
+ + "vpos3 union select distinct 't2stuff:' col0, "
+ + "vpos2_1.col3, vpos2_1.col1 from v1_keycol_at_pos_2 "
+ + "vpos2_1 union select distinct 't3stuff:' col0, "
+ + "vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 vpos2_2 )");
+
+ // Chain of UNIONs with left-most child as a view with a
+ // an RCL that is ordered differently than that of the
+ // UNIONs above it. The right child of the top-level node
+ // is a view that is a select from yet another UNION node.
+
+ st.executeUpdate("create view topview2 as (select distinct 'other:' "
+ + "col0, vpos3.col3, vpos3.col1 from v_keycol_at_pos_3 "
+ + "vpos3 union select distinct 't2stuff:' col0, "
+ + "vpos2_1.col3, vpos2_1.col1 from v1_keycol_at_pos_2 "
+ + "vpos2_1 union select distinct 't3stuff:' col0, "
+ + "vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 "
+ + "vpos2_2 union select distinct 'morestuff:' col0, "
+ + "vu.col3, vu.col1 from v_union vu )");
+
+ // Chain of UNIONs with left-most child as a view with a
[... 660 lines stripped ...]