From derby-commits-return-10315-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Fri May 30 20:52:19 2008 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 32421 invoked from network); 30 May 2008 20:52:18 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 30 May 2008 20:52:18 -0000 Received: (qmail 77920 invoked by uid 500); 30 May 2008 20:52:20 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 77900 invoked by uid 500); 30 May 2008 20:52:20 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 77891 invoked by uid 99); 30 May 2008 20:52:20 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 May 2008 13:52:20 -0700 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 May 2008 20:51:32 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 849522388A0F; Fri, 30 May 2008 13:51:52 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r661847 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/master/ functionTests/suites/ functionTests/tests/lang/ junit/ Date: Fri, 30 May 2008 20:51:52 -0000 To: derby-commits@db.apache.org From: kmarsden@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080530205152.849522388A0F@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kmarsden Date: Fri May 30 13:51:51 2008 New Revision: 661847 URL: http://svn.apache.org/viewvc?rev=661847&view=rev Log: DERBY-3686 convert predicatePushdown.sql to JUnit Removed: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown_derby.properties Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=661847&r1=661846&r2=661847&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Fri May 30 13:51:51 2008 @@ -37,7 +37,6 @@ lang/outparams.java lang/paramij.sql lang/partdml.sql -lang/predicatePushdown.sql lang/predicatesIntoViews.sql lang/refActions.sql lang/refActions2.sql Modified: 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=661847&r1=661846&r2=661847&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PredicatePushdownTest.java Fri May 30 13:51:51 2008 @@ -6,6 +6,7 @@ import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLWarning; +import java.util.Properties; import junit.framework.Test; import junit.framework.TestSuite; @@ -13,6 +14,9 @@ import org.apache.derbyTesting.junit.BaseJDBCTestCase; import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; import org.apache.derbyTesting.junit.JDBC; +import org.apache.derbyTesting.junit.RuntimeStatisticsParser; +import org.apache.derbyTesting.junit.SQLUtilities; +import org.apache.derbyTesting.junit.SystemPropertyTestSetup; import org.apache.derbyTesting.junit.TestConfiguration; /* @@ -45,17 +49,18 @@ } public static Test suite() { + Properties systemProperties = new Properties(); + systemProperties.setProperty("derby.optimizer.noTimeout","true"); TestSuite suite = new TestSuite("predicatePushdown Test"); - suite.addTest(new CleanDatabaseTestSetup(TestConfiguration - .embeddedSuite(PredicatePushdownTest.class))); + suite.addTest(new SystemPropertyTestSetup(new CleanDatabaseTestSetup(TestConfiguration + .embeddedSuite(PredicatePushdownTest.class)),systemProperties)); return suite; } public void test_predicatePushdown() throws Exception { ResultSet rs = null; ResultSetMetaData rsmd; - SQLWarning sqlWarn = null; - + PreparedStatement pSt; CallableStatement cSt; Statement st = createStatement(); @@ -153,19 +158,23 @@ // 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. + // cases. + st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); + + + 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() + 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"); @@ -175,6 +184,9 @@ 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. @@ -194,8 +206,10 @@ { "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()); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() UNION ALLs. rs = st .executeQuery("select * from (select * from t1 union all select * " @@ -214,6 +228,9 @@ { "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 " @@ -230,8 +247,10 @@ { "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()); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() rs = st .executeQuery("select * from (select * from t1 union all select * " @@ -250,6 +269,9 @@ { "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 @@ -1112,8 +1134,10 @@ expRS = new String[][] { { "1", "2", "2", "2" }, { "2", "4", "4", "4" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() + 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"); @@ -1123,7 +1147,10 @@ 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 @@ -1138,9 +1165,11 @@ expRS = new String[][] { { "404" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() - + + 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" }; @@ -1149,7 +1178,11 @@ 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. @@ -1163,8 +1196,12 @@ 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")); + - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() Make + // Make // sure predicates are pushed even if the subquery is // explicit (as opposed to a view). Should see index scans // on T3 and T4. @@ -1184,6 +1221,9 @@ { "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 @@ -1203,8 +1243,11 @@ { "4", "8", "4", "4" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() UNION + 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. @@ -1226,7 +1269,10 @@ JDBC.assertFullResultSet(rs, expRS, true); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() + 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 * " @@ -1244,8 +1290,11 @@ { "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")); - // 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. @@ -1256,7 +1305,12 @@ JDBC.assertColumnNames(rs, expColNames); JDBC.assertEmpty(rs); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + 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). @@ -1276,8 +1330,10 @@ { "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")); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() rs = st .executeQuery("select * from (select * from t1 union select * from " @@ -1294,8 +1350,9 @@ { "4", "8", "4", "4" }, { "4", "8", "4", "16" }}; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + 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 @@ -1308,12 +1365,15 @@ 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()); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() If we + // 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_ @@ -1336,7 +1396,10 @@ JDBC.assertFullResultSet(rs, expRS, true); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + 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 @@ -1354,8 +1417,10 @@ { "3", "6" }, { "4", "-8" }, { "4", "8" }, { "5", "10" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() In this + 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. @@ -1372,7 +1437,12 @@ JDBC.assertFullResultSet(rs, expRS, true); - //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() + 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. @@ -1388,9 +1458,12 @@ 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")); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() Can't - // push predicates into VALUES clauses. Predicate should + + // 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 " @@ -1410,8 +1483,12 @@ { "4", "8", "4", "4" }, { "4", "8", "4", "16" }}; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() + + 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. @@ -1431,7 +1508,9 @@ JDBC.assertFullResultSet(rs, expRS, true); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() Can't + 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 @@ -1460,8 +1539,9 @@ { "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(); + 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 @@ -1485,7 +1565,10 @@ JDBC.assertFullResultSet(rs, expRS, true); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() When we + 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 @@ -1542,8 +1625,15 @@ 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")); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() Here we + // Here we // should see index scans for both instances of T3 and for // both instances of T4. @@ -1558,8 +1648,10 @@ 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")); - //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() // Predicates pushed from outer queries shouldn't // interfere with inner predicates for subqueries. Mostly @@ -1592,8 +1684,9 @@ { "3", "6", "6", "24" }, { "5", "10", "10", "40" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() Outer + 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 @@ -1613,8 +1706,9 @@ { "4", "-8", "4", "16" }, { "4", "8", "4", "16" }}; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + 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 @@ -1632,8 +1726,9 @@ expRS = new String[][] { { "1", "2", "2", "4" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() + 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 @@ -1652,8 +1747,11 @@ 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")); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + // Multiple subqueries but NO UNIONs. All predicates are // used for joins at their current level (no pushing). @@ -1664,8 +1762,13 @@ expColNames = new String[] { "I", "P" }; JDBC.assertColumnNames(rs, expColNames); JDBC.assertDrainResults(rs, 0); - - // anything. + 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 " @@ -1679,9 +1782,12 @@ { "8", "4" } }; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() - + 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 " @@ -1695,8 +1801,12 @@ { "4", "2" }, { "6", "3" }, { "6", "3" }, { "8", "4" }, { "8", "4" }}; JDBC.assertFullResultSet(rs, expRS, true); - - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + 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 @@ -1714,8 +1824,12 @@ 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")); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() Same as + // Same as // above but without the inner predicate (so no hash on T6). rs = st @@ -1730,7 +1844,9 @@ JDBC.assertEmpty(rs); - //values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + 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). @@ -1745,7 +1861,10 @@ JDBC.assertColumnNames(rs, expColNames); JDBC.assertDrainResults(rs, 0); - // values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() + 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 Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=661847&r1=661846&r2=661847&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Fri May 30 13:51:51 2008 @@ -133,6 +133,7 @@ suite.addTest(ViewsTest.suite()); suite.addTest(DeadlockModeTest.suite()); suite.addTest(AnsiSignaturesTest.suite()); + suite.addTest(PredicatePushdownTest.suite()); // Add the XML tests, which exist as a separate suite // so that users can "run all XML tests" easily. Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java?rev=661847&r1=661846&r2=661847&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java Fri May 30 13:51:51 2008 @@ -74,6 +74,7 @@ qualifiers = findQualifiers(); } + /** * Class which represents a qualifier used in a scan. @@ -160,6 +161,26 @@ public boolean usedTableScan() { return tableScan; } + + /** + * @param tableName + * @return true if a Table Scan ResultSet was used for tableName + */ + public boolean usedTableScan(String tableName){ + return (statistics.indexOf("Table Scan ResultSet for " + + tableName)!= -1); + } + /** + * @param tableName + * @return true if an Index Scan ResultSet was used for tableName + */ + public boolean usedIndexScan(String tableName){ + return (statistics.indexOf("Index Scan ResultSet for " + + tableName)!= -1); + } + + + /** * Return whether or not an index scan result set was used in the query. @@ -177,6 +198,30 @@ } /** + * @param tableName + * @return true if Index Row to Base Row ResultSet was used for tableName + */ + public boolean usedIndexRowToBaseRow(String tableName) { + + return (statistics.indexOf("Index Row to Base Row ResultSet for " + + tableName)!= -1); + + } + + + /** + * @param tableName + * @return true if Used Distinct Scan ResultSet for tablenName + */ + public boolean usedDistinctScan(String tableName) { + return (statistics.indexOf("Distinct Scan ResultSet for " + + tableName)!= -1); + + } + + + + /** * Return whether or not the query involved a sort that eliminated * duplicates */ @@ -202,6 +247,8 @@ public boolean hasLessThanQualifier() { return qualifiers.contains(new Qualifier("<", false)); } + + /** * Return whether or not the query plan includes a line of the form @@ -219,4 +266,38 @@ return (statistics.indexOf("Number of rows qualified=" + qualRows + "\n") != -1); } + + /** + * @return true if a hash join was used + */ + public boolean usedHashJoin() + { + return (statistics.indexOf("Hash Join ResultSet") != -1); + } + + /** + * Search the RuntimeStatistics for a string. It must occur + * at least instances times. + * @param stringToFind + * @param instances + * @return true if stringToFind is found instances times. + */ + public boolean findString(String stringToFind, int instances) + { + int foundCount=0; + int currentOffset=0; + String stat = statistics; + for (int i = 0; i < instances; i++) { + currentOffset = stat.indexOf(stringToFind); + if (currentOffset != -1) { + foundCount++; + stat = stat.substring(currentOffset + stringToFind.length()); + } else { + break; + } + } + return (foundCount >=instances); + + } } +