java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryFlatteningTest.java - derby - Git at Google

 /*
  * 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);
     }
 }
	/*
	* 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);
	}
	}