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 ma...@apache.org on 2013/05/09 19:48:44 UTC
svn commit: r1480730 - in /db/derby/code/branches/10.8: ./
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/master/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Thu May 9 17:48:44 2013
New Revision: 1480730
URL: http://svn.apache.org/r1480730
Log:
DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows)
Backporting to 10.8
Modified:
db/derby/code/branches/10.8/ (props changed)
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
Merged /db/derby/code/branches/10.9:r1480320
Merged /db/derby/code/trunk:r1445030,1446048,1450363,1451683,1463378,1465830,1466097,1479607
Merged /db/derby/code/branches/10.10:r1480153
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=1480730&r1=1480729&r2=1480730&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Thu May 9 17:48:44 2013
@@ -1294,13 +1294,32 @@ public class FromBaseTable extends FromT
if (statCompositeSelectivity == -1.0d)
statCompositeSelectivity = 1.0d;
}
-
- if (seenFirstColumn && statisticsForConglomerate &&
- (startStopPredCount > 0))
- {
- statStartStopSelectivity =
- tableDescriptor.selectivityForConglomerate(cd, startStopPredCount);
- }
+
+ if (seenFirstColumn && (startStopPredCount > 0))
+ {
+ if (statisticsForConglomerate) {
+ statStartStopSelectivity =
+ tableDescriptor.selectivityForConglomerate(cd,
+ startStopPredCount);
+ } else if (cd.isIndex()) {
+ //DERBY-3790 (Investigate if request for update
+ // statistics can be skipped for certain kind of
+ // indexes, one instance may be unique indexes based
+ // on one column.) But as found in DERBY-6045 (in list
+ // multi-probe by primary key not chosen on tables with
+ // >256 rows), even though we do not keep the
+ // statistics for single-column unique indexes, we
+ // should improve the selectivity of such an index
+ // when the index is being considered by the optimizer.
+ IndexRowGenerator irg = cd.getIndexDescriptor();
+ if (irg.isUnique()
+ && irg.numberOfOrderedColumns() == 1
+ && startStopPredCount == 1) {
+ statStartStopSelectivity =
+ (double)(1/(double)baseRowCount());
+ }
+ }
+ }
/*
** Factor the non-base-table predicates into the extra
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out?rev=1480730&r1=1480729&r2=1480730&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out Thu May 9 17:48:44 2013
@@ -8853,7 +8853,8 @@ union all
ij> -- #BEGIN;
select * from db2test.dept where dno in (select vdno from
db2test.vempunion)
- and dno in ('K55', 'K52');
+ and dno in ('K55', 'K52')
+ order by dno;
C0 |DNO|DNAME |DMGRNAME
-------------------------------------
2 |K52|OFC |ROBIN
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java?rev=1480730&r1=1480729&r2=1480730&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java Thu May 9 17:48:44 2013
@@ -45,6 +45,7 @@ import junit.framework.TestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.SQLUtilities;
import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
@@ -80,6 +81,21 @@ public class InListMultiProbeTest extend
private final static String COLUMN_NAMES =
"KIND, ITEM_UUID, ITEM_TYPE, BEFORE, AFTER, FOREIGN_KEY_UUID, ID";
+ private final static String DERBY_6045_DATA_TABLE = "VARIABLE_TERM";
+ private final static String CREATE_DERBY_6045_DATA_TABLE =
+ "CREATE TABLE " + DERBY_6045_DATA_TABLE + " (" +
+ "term_id INTEGER NOT NULL, " +
+ "var_name VARCHAR(1024) NOT NULL, " +
+ "var_type SMALLINT NOT NULL " +
+ ")";
+
+ private final static String DERBY_6045_DATA_TABLE2 = "MT_GAF_TOP_LEVEL_TERM_COUNTS";
+ private final static String CREATE_DERBY_6045_DATA_TABLE2 =
+ "CREATE TABLE " + DERBY_6045_DATA_TABLE2 +
+ "(mt BIGINT NOT NULL, term BIGINT NOT NULL, "+
+ "term_index INTEGER NOT NULL, " +
+ "usage_count BIGINT NOT NULL )";
+
private final static String CREATE_DATA_TABLE =
"CREATE TABLE " + DATA_TABLE + " (" +
"ID BIGINT NOT NULL ," +
@@ -181,9 +197,7 @@ public class InListMultiProbeTest extend
{
// Create the test table.
s.executeUpdate(CREATE_DATA_TABLE);
-
// Insert test data.
-
final int BATCH_SIZE = 1000;
int numDataRows = NUM_ROWS;
Random random = new Random(1);
@@ -227,6 +241,89 @@ public class InListMultiProbeTest extend
"insert into d3603_c (c_id, d_id, t_o, t_i) values (21, 1, 1, 1)",
};
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for DELETE statement
+ // on a table with appropriate indexes. This happens with or without
+ // update statistics and with or without parameterized statement.
+ public void testDerby6045DeleteTest()
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE2);
+ // Create the test table, primary key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE2);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE2 +
+ " ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk" +
+ " PRIMARY KEY (mt, term, term_index)");
+ s.executeUpdate("CREATE INDEX " +
+ "kb_mt_gaf_top_level_term_counts_mt_index "+
+ "ON " + DERBY_6045_DATA_TABLE2+"(mt)");
+ s.executeUpdate("CREATE INDEX " +
+ "kb_mt_gaf_top_level_term_counts_term_index "+
+ "ON " + DERBY_6045_DATA_TABLE2+"(term)");
+ //insert requested number of rows in the table
+ PreparedStatement ps = s.getConnection().prepareStatement(
+ "insert into " + DERBY_6045_DATA_TABLE2 +
+ " VALUES (?, ?, ?, ?)");
+ int numberOfRows = 10000;
+ for (int i=1; i<=numberOfRows; i++) {
+ ps.setInt(1, i);
+ ps.setInt(2, i);
+ ps.setInt(3, i);
+ ps.setInt(4, i);
+ ps.executeUpdate();
+ }
+ //do not run update statisitcs and do not use PreparedStatement
+ deleteRows(false, false);
+ //do not run update statisitcs but do use PreparedStatement
+ deleteRows(false, true);
+ //run update statisitcs but do not use PreparedStatement
+ deleteRows(true, false);
+ //run update statisitcs and use PreparedStatement
+ deleteRows(true, true);
+
+ dropTable(DERBY_6045_DATA_TABLE2);
+ ps.close();
+ s.close();
+ }
+
+ void deleteRows(boolean runUpdateStatistics,
+ boolean useParameterMarkers)
+ throws SQLException
+ {
+ Statement s;
+ PreparedStatement ps;
+ RuntimeStatisticsParser rtsp;
+
+ s = createStatement();
+ if (runUpdateStatistics) {
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'MT_GAF_TOP_LEVEL_TERM_COUNTS', null)");
+ }
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = ?) ");
+ ps.setInt(1,1);
+ ps.execute();
+ } else {
+ s.execute("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = 2) ");
+ }
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = ?) OR (mt = ?)");
+ ps.setInt(1,3);
+ ps.setInt(2,4);
+ ps.execute();
+ } else {
+ s.execute("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)");
+ }
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+ }
+
/**
* Executes three different types of queries ("strategies") repeatedly
* with an increasing number of values in the IN list. Underneath we
@@ -894,6 +991,335 @@ public class InListMultiProbeTest extend
st.close();
}
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // after running the update statistics. This test DOES NOT use
+ // parameters in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithUpdateStatistics()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to run update statisitcs after
+ // inserting data in the table. 3rd param says do not use
+ // parameter in the SELECT sql to identify the rows in the
+ // where clause
+ helperDerby6045(10, true, false);
+ helperDerby6045(24, true, false);
+ helperDerby6045(10000, true, false);
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // even though update statistics was not run. This test DOES NOT use
+ // parameters in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithoutUpdateStatistics()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to DO Not run update statisitcs after
+ // inserting data in the table. 3rd param says do not use
+ // parameter in the SELECT sql to identify the rows in the
+ // WHERE clause
+ helperDerby6045(10, false, false);
+ helperDerby6045(24, false, false);
+ helperDerby6045(10000, false, false);
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // after running the update statistics. This test USES parameters
+ // in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithUpdateStatisticsAndParams()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below.
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to run update statisitcs after
+ // inserting data in the table. 3rd param says to use parameters
+ // in the SELECT sql to identify the rows in the where clause
+ helperDerby6045(10, true, true);
+ helperDerby6045(24, true, true);
+ helperDerby6045(10000, true, true);
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // even though no update statistics were run. This test USES parameters
+ // in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithoutUpdateStatisticsAndWithParams()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below.
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to DO Not run update statisitcs after
+ // inserting data in the table. 3rd param says to use parameters
+ // in the SELECT sql to identify the rows in the where clause
+ helperDerby6045(10, false, true);
+ helperDerby6045(24, false, true);
+ helperDerby6045(10000, false, true);
+ }
+
+ // Following method will create a brand new table with primary key,
+ // insert passed number of rows, run update statistics if the
+ // passed parameter to method requests for one and then run
+ // three queries and check that they all use index scan
+ // @param numberOfRows number of rows to be inserted into a brand new table
+ // @param updateStatistics if True, run update statistics after inserting
+ // data into the table
+ // @param useParameterMarkers if True, use parameter in the SELECT sql
+ // to identify the rows in the WHERE clause
+ public void helperDerby6045(int numberOfRows,
+ boolean updateStatistics,
+ boolean useParameterMarkers)
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE);
+ // Create the test table, primary key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE +
+ " ADD CONSTRAINT kb_variable_term_term_id_pk" +
+ " PRIMARY KEY (term_id)");
+
+ //insert requested number of rows in the table
+ PreparedStatement ps = s.getConnection().prepareStatement(
+ "insert into " + DERBY_6045_DATA_TABLE +
+ " VALUES (?, '?var0', 1)");
+ for (int i=1; i<=numberOfRows; i++) {
+ ps.setInt(1, i);
+ ps.executeUpdate();
+ }
+
+ if (updateStatistics) {
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+ }
+
+ runThreeQueries(0, useParameterMarkers);
+
+ dropTable(DERBY_6045_DATA_TABLE);
+ ps.close();
+ s.close();
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we should continue using index scan
+ // even after adding extra rows to the table.
+ public void testDerby6045()
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE);
+ // Create the test table, primary key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE +
+ " ADD CONSTRAINT kb_variable_term_term_id_pk" +
+ " PRIMARY KEY (term_id)");
+
+ //insert 10 rows
+ PreparedStatement ps = s.getConnection().prepareStatement(
+ "insert into " + DERBY_6045_DATA_TABLE +
+ " VALUES (?, '?var0', 1)");
+ for (int i=1; i<=10; i++) {
+ ps.setInt(1, i);
+ ps.executeUpdate();
+ }
+ runThreeQueries(0, false);
+
+ //Add 14 more rows
+ for (int i=11; i<=25; i++) {
+ ps.setInt(1, i);
+ ps.executeUpdate();
+ }
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+ //Need to execute the query with an extra white space so that the
+ // queries will get recompiled. If the following queries looked
+ // exactly like the earlier queries in this fixture, we would
+ // end up using the earloer query plan rather than creating a
+ // new query plan which is going recognize the additional rows.
+ runThreeQueries(1, false);
+
+ //Add 10K more rows
+ for (int i=26; i<=10000; i++) {
+ ps.setInt(1, i);
+ ps.executeUpdate();
+ }
+ //Again, need to execute the query with another extra white space so
+ // it looks different from the queries run earlier and hence they will
+ // get compiled rather than existing query plan getting picked up from
+ // statement cache.
+ runThreeQueries(2, false);
+ s.close();
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Test following case
+ // Insert 10K rows to a table with primary key on a column and
+ // unique index on 2 other columns in the table. A SELECT * from
+ // the table with WHERE clause using primary key with OR ends up
+ // doing table scan rather than index scan.
+ // If the unique key is removed from the table, the same query
+ // will start doing index scan.
+ public void testDerby6045InsertAllRowsAdditionalUniqueIndex()
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE);
+ // Create the test table, primary key, unique key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE +
+ " ADD CONSTRAINT kb_variable_term_term_id_pk" +
+ " PRIMARY KEY (term_id)");
+
+ //create additional unique key. Creation of this unique key is making
+ // the select queries with IN and OR clause on the primary key to use
+ // table scan
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE +
+ " ADD CONSTRAINT kb_variable_term_variable_name_unique " +
+ " UNIQUE (var_name, var_type)");
+
+ //insert 10K rows
+ for (int i=1; i<=10000; i++) {
+ s.executeUpdate("insert into " + DERBY_6045_DATA_TABLE +
+ " VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4) + ")");
+ }
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+ runThreeQueries(0, false);
+ s.close();
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Test following case
+ // 1)If we insert 10K rows to an empty table with primary key on column
+ // being used in the where clause, we use index scan for the queries
+ // being tested
+ // Insert 10K rows to a table with primary key. A SELECT * from
+ // the table with WHERE clause using primary key with OR uses
+ // index scan.
+ public void testDerby6045InsertAllRows()
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE);
+ // Create the test table, primary key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE +
+ " ADD CONSTRAINT kb_variable_term_term_id_pk" +
+ " PRIMARY KEY (term_id)");
+
+ //insert 10K rows
+ for (int i=1; i<=10000; i++) {
+ s.executeUpdate("insert into " + DERBY_6045_DATA_TABLE +
+ " VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4) + ")");
+ }
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+ runThreeQueries(0, false);
+ s.close();
+ }
+
+ //Make sure that we are using index scan for the following queries.
+ // Also, add extra white spaces in the query so old queries from
+ // statement cache don't get picked up. This will cause the query
+ // plans to be created based on the current number of rows in the table
+ // @param numOfWhiteSpace Number of white spaces that will be put in
+ // SELECT queries below
+ private void runThreeQueries(int numOfWhiteSpace,
+ boolean useParameterMarkers)
+ throws SQLException
+ {
+ RuntimeStatisticsParser rtsp;
+ Statement s = createStatement();
+ PreparedStatement ps;
+
+ String whiteSpace = "";
+ for (int i=1; i<=numOfWhiteSpace; i++)
+ {
+ whiteSpace = whiteSpace + " ";
+ }
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE TERM_ID = ?");
+ ps.setInt(1, 11);
+ JDBC.assertDrainResults(ps.executeQuery());
+ } else {
+ s.executeQuery("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE TERM_ID = 11");
+ }
+
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID = ?) OR " +
+ "(TERM_ID = ?) OR (TERM_ID = ?)");
+ ps.setInt(1, 11);
+ ps.setInt(2, 21);
+ ps.setInt(3, 31);
+ JDBC.assertDrainResults(ps.executeQuery());
+ } else {
+ s.executeQuery("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID = 11) OR " +
+ "(TERM_ID =21) OR (TERM_ID = 31)");
+ }
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID IN (?, ?, ?))");
+ ps.setInt(1, 11);
+ ps.setInt(2, 21);
+ ps.setInt(3, 31);
+ JDBC.assertDrainResults(ps.executeQuery());
+ } else {
+ s.executeQuery("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID IN (11, 21, 31))");
+ }
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+ s.close();
+ }
+
public void testDerby3603()
throws SQLException
{
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql?rev=1480730&r1=1480729&r2=1480730&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql Thu May 9 17:48:44 2013
@@ -3143,7 +3143,8 @@ union all
-- #BEGIN;
select * from db2test.dept where dno in (select vdno from
db2test.vempunion)
- and dno in ('K55', 'K52');
+ and dno in ('K55', 'K52')
+ order by dno;
-- #END;
delete from db2test.dept where dno in (select vdno from