/*
* Class org.apache.derbyTesting.functionTests.tests.lang.StalePlansTest
*
* 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.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import junit.framework.Test;
import junit.framework.TestSuite;
import org.apache.derbyTesting.functionTests.util.Formatters;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.SQLUtilities;
/**
* This is the test for stale plan invalidation. The system determines at
* execution whether the tables used by a DML statement have grown or shrunk
* significantly, and if so, causes the statement to be recompiled at the next
* execution.
*/
public class StalePlansTest extends BaseJDBCTestCase {
public StalePlansTest(String name) {
super(name);
}
/**
* Create the test suite. This test is not run in client/server mode since
* it only tests the query plans generated by the embedded driver.
*/
public static Test suite() {
Properties props = new Properties();
props.setProperty("derby.language.stalePlanCheckInterval", "10");
props.setProperty("derby.storage.checkpointInterval", "100000");
Test suite = new DatabasePropertyTestSetup(
new TestSuite(StalePlansTest.class), props, true);
return new CleanDatabaseTestSetup(suite) {
protected void decorateSQL(Statement s) throws SQLException {
// Create and populate a table to be used for flushing the
// cache. Flushing the cache causes all row count changes to be
// written, which is necessary for the results of this test to
// be stable (because otherwise the row count changes would be
// written asynchronously)
s.executeUpdate("create table flusher (c1 varchar(3000))");
PreparedStatement ps = getConnection().prepareStatement(
"insert into flusher values ?");
ps.setString(1, Formatters.padString("a", 3000));
for (int i = 0; i < 64; i++) {
ps.executeUpdate();
}
ps.close();
}
};
}
/**
* Create tables and indexes needed by the test cases. Enable collection of
* run-time statistics.
*/
protected void setUp() throws SQLException {
getConnection().setAutoCommit(false);
Statement stmt = createStatement();
stmt.executeUpdate("create table t (c1 int, c2 int, c3 varchar(255))");
stmt.executeUpdate("create index idx on t (c1)");
stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
stmt.close();
commit();
}
/**
* Drop tables used in the test.
*/
protected void tearDown() throws Exception {
Statement stmt = createStatement();
stmt.executeUpdate("drop table t");
commit();
super.tearDown();
}
/**
* Flush the cache so that row count changes are visible.
*/
private void flushRowCount(Statement stmt) throws SQLException {
JDBC.assertFullResultSet(
stmt.executeQuery("select count(c1) from flusher"),
new String[][] { { "64" } });
}
/**
* Negative test - set stalePlanCheckInterval to a value out of range.
*/
public void testStalePlanCheckIntervalOutOfRange() throws SQLException {
Statement stmt = createStatement();
assertStatementError("XCY00", stmt,
"call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
"'derby.language.stalePlanCheckInterval', '2')");
stmt.close();
}
/**
* Test that the query plan is changed when the size of a small table
* changes.
*
* <p><b>Note:</b> This test is outdated since Derby now tries to use index
* scans whenever possible on small tables (primarily to avoid table locks
* for certain isolation levels, but also because a small table is likely
* to grow).
*/
public void testStalePlansOnSmallTable() throws SQLException {
Statement stmt = createStatement();
PreparedStatement insert =
prepareStatement("insert into t values (?,?,?)");
insert.setInt(1, 1);
insert.setInt(2, 100);
insert.setString(3, Formatters.padString("abc", 255));
insert.executeUpdate();
commit();
// Make sure row count from insert is flushed out
flushRowCount(stmt);
PreparedStatement ps =
prepareStatement("select count(c1 + c2) from t where c1 = 1");
// Expect this to do an index scan
String[][] expected = {{ "1" }};
JDBC.assertFullResultSet(ps.executeQuery(), expected);
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedIndexScan());
// Execute 11 more times, the plan should not change
for (int i = 0; i < 11; i++) {
JDBC.assertFullResultSet(ps.executeQuery(), expected);
}
// Expect index scan
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedIndexScan());
commit();
// Now increase the size of the table
insert.setInt(2, 100);
for (int i = 2; i <= 10; i++) {
insert.setInt(1, i);
insert.executeUpdate();
}
commit();
// Make sure row count from inserts is flushed out
flushRowCount(stmt);
// Execute 11 times, the plan should not change
for (int i = 0; i < 11; i++) {
JDBC.assertFullResultSet(ps.executeQuery(), expected);
}
// Expect this to use index
JDBC.assertFullResultSet(ps.executeQuery(), expected);
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedIndexScan());
commit();
// Now shrink the table back to its original size
stmt.executeUpdate("delete from t where c1 >= 2");
// Execute 11 times, the plan should not change
for (int i = 0; i < 11; i++) {
JDBC.assertFullResultSet(ps.executeQuery(), expected);
}
// Expect this to do an index scan
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedIndexScan());
stmt.close();
ps.close();
insert.close();
}
/**
* Test that the query plan changes when a large table is modified.
*/
public void testStalePlansOnLargeTable() throws SQLException {
Statement stmt = createStatement();
PreparedStatement insert =
prepareStatement("insert into t values (?,?,?)");
insert.setInt(1, 1);
insert.setInt(2, 1);
insert.setString(3, Formatters.padString("abc", 255));
insert.executeUpdate();
PreparedStatement insert2 =
prepareStatement("insert into t select c1+?, c2+?, c3 from t");
for (int i = 1; i <= 512; i *= 2) {
insert2.setInt(1, i);
insert2.setInt(2, i);
insert2.executeUpdate();
}
commit();
// Make sure row count from inserts is flushed out
flushRowCount(stmt);
PreparedStatement ps = prepareStatement(
"select count(c1 + c2) from t where c1 = 1");
// Expect this to use index
String[][] expected = {{ "1" }};
JDBC.assertFullResultSet(ps.executeQuery(), expected);
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedIndexScan());
commit();
// Change the row count a little bit
for (int i = 1025; i <= 1034; i++) {
insert.setInt(1, i);
insert.setInt(2, i);
insert.executeUpdate();
}
commit();
// Change the data so a table scan would make more sense.
// Use a qualifier to convince TableScanResultSet not to
// update the row count in the store (which would make it
// hard for this test to control when recompilation takes
// place).
stmt.executeUpdate("update t set c1 = 1 where c1 > 0");
// Make sure row count from inserts is flushed out
flushRowCount(stmt);
// Execute 11 more times, the plan should not change
for (int i = 0; i < 11; i++) {
JDBC.assertFullResultSet(ps.executeQuery(),
new String[][] { { "1034" } });
}
// Expect this to use table scan, as the above update has basically
// made all the rows in the table be equal to "1", thus using the index
// does not help if all the rows are going to qualify.
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedTableScan());
// Change the row count significantly
stmt.executeUpdate("insert into t select c1,c2,c3 from t where c1<128");
// Make sure row count from inserts is flushed out
flushRowCount(stmt);
// Execute 11 times, the plan should change
for (int i = 0; i < 11; i++) {
JDBC.assertFullResultSet(ps.executeQuery(),
new String[][] { { "2068" } });
}
// Expect this to do table scan
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedTableScan());
// Change the distribution back to where an index makes sense
stmt.executeUpdate("update t set c1 = c2");
// Change the row count significantly
stmt.executeUpdate("insert into t select c1, c2, c3 from t");
// Make sure row count from inserts is flushed out
flushRowCount(stmt);
// Execute 11 times, the plan should change
for (int i = 0; i < 11; i++) {
JDBC.assertFullResultSet(ps.executeQuery(),
new String[][] { { "4" } });
}
// Expect this to do index to baserow
assertTrue(SQLUtilities.
getRuntimeStatisticsParser(stmt).usedIndexRowToBaseRow());
stmt.close();
insert.close();
insert2.close();
ps.close();
}
}