db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r765930 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/lang/SubqueryFlatteningTest.java junit/RuntimeStatisticsParser.java
Date Fri, 17 Apr 2009 10:17:30 GMT
Author: kahatlen
Date: Fri Apr 17 10:17:30 2009
New Revision: 765930

URL: http://svn.apache.org/viewvc?rev=765930&view=rev
Log:
DERBY-4001: Sequence comparison with "ALL" does not yield correct results

Added a regression test that exposes the bug. The test is not enabled
since the fix has not been checked in yet.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java?rev=765930&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java
Fri Apr 17 10:17:30 2009
@@ -0,0 +1,266 @@
+/*
+ * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SubqueryFlatteningTest
+ *
+ * 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.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * This test verifies that flattening of subqueries works correctly.
+ *
+ * The test cases in <tt>subqueryFlattening.sql</tt> could be moved to this
+ * class when they are converted to JUnit.
+ */
+public class SubqueryFlatteningTest extends BaseJDBCTestCase {
+
+    public SubqueryFlatteningTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        // We're testing the SQL layer, so we run the test in embedded
+        // mode only.
+        return TestConfiguration.embeddedSuite(SubqueryFlatteningTest.class);
+    }
+
+    /**
+     * Set up the test environment. Turn off auto-commit so that all the test
+     * data can easily be removed by the rollback performed in
+     * {@code BaseJDBCTestCase.tearDown()}.
+     */
+    protected void setUp() throws SQLException {
+        setAutoCommit(false);
+    }
+
+    /**
+     * Enable collection of runtime statistics in the current connection.
+     * @param s the statement to use for enabling runtime statistics
+     */
+    private void enableRuntimeStatistics(Statement s) throws SQLException {
+        s.execute("call syscs_util.syscs_set_runtimestatistics(1)");
+    }
+
+    /**
+     * Check that a query returns the expected rows and whether or not it was
+     * flattened to an exists join (or not exists join). An error is raised if
+     * wrong results are returned or if the query plan is not the expected one.
+     *
+     * @param s the statement on which the query is executed
+     * @param sql the query text
+     * @param rows the expected result
+     * @param flattenable whether or not we expect the query to be flattened
+     * to a (not) exists join
+     * @throws SQLException if a database error occurs
+     * @throws junit.framework.AssertionFailedError if the wrong results are
+     * returned from the query, or if the query plan is not as expected
+     */
+    private void checkExistsJoin(Statement s, String sql, String[][] rows,
+                                 boolean flattenable)
+            throws SQLException
+    {
+        JDBC.assertFullResultSet(s.executeQuery(sql), rows);
+        RuntimeStatisticsParser parser =
+                SQLUtilities.getRuntimeStatisticsParser(s);
+        assertEquals("unexpected plan", flattenable, parser.usedExistsJoin());
+    }
+
+    /**
+     * DERBY-4001: Test that certain NOT EXISTS/NOT IN/ALL subqueries are
+     * flattened, and that their predicates are not pulled out. Their
+     * predicates are known to be always false, so when they are (correctly)
+     * applied on the subquery, they will cause all the rows from the outer
+     * query to be returned. If the predicates are (incorrectly) pulled out to
+     * the outer query, the query won't return any rows at all. DERBY-4001.
+     */
+    public void testNotExistsFlattenablePredicatesNotPulled()
+            throws SQLException
+    {
+        Statement s = createStatement();
+        // X must be NOT NULL, otherwise X NOT IN and X < ALL won't be
+        // rewritten to NOT EXISTS
+        s.execute("create table t (x int not null)");
+        s.execute("insert into t values 1,2,3");
+
+        enableRuntimeStatistics(s);
+
+        String[][] allRows = {{"1"}, {"2"}, {"3"}};
+
+        checkExistsJoin(
+                s,
+                "select * from t where not exists (select x from t where 1<>1)",
+                allRows, true);
+
+        checkExistsJoin(
+                s,
+                "select * from t where x not in (select x from t where 1<>1)",
+                allRows, true);
+
+        checkExistsJoin(
+                s,
+                "select * from t where x < all (select x from t where 1<>1)",
+                allRows, true);
+    }
+
+    /**
+     * DERBY-4001: Test that some ALL subqueries that used to be flattened to
+     * a not exists join and return incorrect results, are not flattened.
+     * These queries should not be flattened because the generated NOT EXISTS
+     * JOIN condition or some of the subquery's predicates could be pushed
+     * down into the left side of the join, which is not allowed in a not
+     * exists join because the predicates have a completely different effect
+     * if they're used on one side of the join than if they're used on the
+     * other side of the join.
+     */
+    public void testAllNotFlattenableToNotExists() throws SQLException {
+        Statement s = createStatement();
+        // X must be NOT NULL, otherwise rewriting ALL to NOT EXISTS won't even
+        // be attempted
+        s.execute("create table t (x int not null)");
+        s.execute("insert into t values 1,2,3");
+
+        enableRuntimeStatistics(s);
+
+        String[][] allRows = {{"1"}, {"2"}, {"3"}};
+
+        // Join condition is X >= 100, which should make the right side of
+        // the not exists join empty and return all rows from the left side.
+        // If (incorrectly) pushed down on the left side, no rows will be
+        // returned.
+        checkExistsJoin(
+                s, "select * from t where x < all (select 100 from t)",
+                allRows, false);
+
+        // Join condition is 1 >= 100, which should make the right side of
+        // the not exists join empty and return all rows from the left side.
+        // If (incorrectly) pushed down on the left side, no rows will be
+        // returned.
+        checkExistsJoin(
+                s, "select * from t where 1 < all (select 2 from t)",
+                allRows, false);
+
+        // Join condition is X <> 1, which will remove the only interesting
+        // row from the left side if (incorrectly) pushed down there.
+        checkExistsJoin(
+                s, "select * from t where x = all (select 1 from t)",
+                new String[][]{{"1"}}, false);
+
+        // Join condition is T1.X >= T2.X which cannot be pushed down on the
+        // left side. The predicate in the subquery (T1.X > 100) can be pushed
+        // down on the left side and filter out rows that should not be
+        // filtered out, so check that this query is not flattened.
+        checkExistsJoin(
+                s, "select * from t t1 where x < all " +
+                "(select x from t t2 where t1.x > 100)",
+                allRows, false);
+
+        // Same as above, but with an extra, unproblematic predicate added
+        // to the subquery.
+        checkExistsJoin(
+                s, "select * from t t1 where x < all " +
+                "(select x from t t2 where t1.x > 100 and t2.x > 100)",
+                allRows, false);
+
+        // Same as above, but since the problematic predicate is ORed with
+        // an unproblematic one, it is not possible to push it down on the
+        // left side (only ANDed predicates can be split and pushed down
+        // separately), so in this case we expect the query to be flattened.
+        // (This query worked correctly also before DERBY-4001 was fixed.)
+        checkExistsJoin(
+                s, "select * from t t1 where x < all " +
+                "(select x from t t2 where t1.x > 100 or t2.x > 100)",
+                allRows, true);
+    }
+
+    /**
+     * DERBY-4001: Test that some NOT IN subqueries that used to be flattened
+     * to a not exists join and return incorrect results, are not flattened.
+     * These queries should not be flattened because the generated NOT EXISTS
+     * JOIN condition or some of the subquery's predicates could be pushed
+     * down into the left side of the join, which is not allowed in a not
+     * exists join because the predicates have a completely different effect
+     * if they're used on one side of the join than if they're used on the
+     * other side of the join.
+     */
+    public void testNotInNotFlattenableToNotExists() throws SQLException {
+        Statement s = createStatement();
+        // X must be NOT NULL, otherwise rewriting NOT IN to NOT EXISTS won't
+        // even be attempted
+        s.execute("create table t (x int not null)");
+        s.execute("insert into t values 1,2,3");
+
+        enableRuntimeStatistics(s);
+
+        String[][] allRows = {{"1"}, {"2"}, {"3"}};
+
+        // Join condition is X = 100, which should make the right side of
+        // the not exists join empty and return all rows from the left side.
+        // If (incorrectly) pushed down on the left side, no rows will be
+        // returned.
+        checkExistsJoin(
+                s, "select * from t where x not in (select 100 from t)",
+                allRows, false);
+
+        // Join condition is 1 = 100, which should make the right side of
+        // the not exists join empty and return all rows from the left side.
+        // If (incorrectly) pushed down on the left side, no rows will be
+        // returned.
+        checkExistsJoin(
+                s, "select * from t where 1 not in (select 100 from t)",
+                allRows, false);
+
+        // Join condition is X = 2, which will remove the interesting rows
+        // from the left side if (incorrectly) pushed down there.
+        checkExistsJoin(
+                s, "select * from t where x not in (select 2 from t)",
+                new String[][]{{"1"}, {"3"}}, false);
+
+        // Join condition is T1.X = T2.X which cannot be pushed down on the
+        // left side. The predicate in the subquery (T1.X > 100) can be pushed
+        // down on the left side and filter out rows that should not be
+        // filtered out, so check that this query is not flattened.
+        checkExistsJoin(
+                s, "select * from t t1 where x not in " +
+                "(select x from t t2 where t1.x > 100)",
+                allRows, false);
+
+        // Same as above, but with an extra, unproblematic predicate added
+        // to the subquery.
+        checkExistsJoin(
+                s, "select * from t t1 where x not in " +
+                "(select x from t t2 where t1.x > 100 and t2.x > 100)",
+                allRows, false);
+
+        // Same as above, but since the problematic predicate is ORed with
+        // an unproblematic one, it is not possible to push it down on the
+        // left side (only ANDed predicates can be split and pushed down
+        // separately), so in this case we expect the query to be flattened.
+        // (This query worked correctly also before DERBY-4001 was fixed.)
+        checkExistsJoin(
+                s, "select * from t t1 where x not in " +
+                "(select x from t t2 where t1.x > 100 or t2.x > 100)",
+                allRows, true);
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

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=765930&r1=765929&r2=765930&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 Apr 17 10:17:30 2009
@@ -298,6 +298,15 @@
     }
 
     /**
+     * Check if an exists join (or a not exists join) was used.
+     *
+     * @return {@code true} if the query used a (not) exists join
+     */
+    public boolean usedExistsJoin() {
+        return statistics.indexOf("Exists Join ResultSet") != -1;
+    }
+
+    /**
      * Search the RuntimeStatistics for a string.  It must occur
      * at least instances times.
      * @param stringToFind



Mime
View raw message