db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject 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/
Date Thu, 09 May 2013 17:48:44 GMT
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



Mime
View raw message