Examples of org.apache.derbyTesting.junit.RuntimeStatisticsParser$Qualifier

org.apache.derbyTesting.junit.RuntimeStatisticsParser
Class which represents a qualifier used in a scan.

  public void testDistinctElimination() throws SQLException {
    Statement s = createStatement();
    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
    
    assertRowCount(1, s.executeQuery("select distinct c2 from one"));
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedDistinctScan());
    
    // Derby251 Distinct should not get eliminated for following query
    // because there is no equality condition on unique column of table
    // in the outside query
    assertRowCount(2, s.executeQuery("select distinct q1.\"NO1\",  q1.\"NAME\",  q1.\"AUDITOR_NO\",  q1.\"REPORTTO_NO\",  q1.\"DISCRIM_DEPT\",  q1.\"SOFTWAREASSET\" from IDEPT q1, IDEPT q2" +
        " where ( q2.\"DISCRIM_DEPT\" = 'HardwareDept') and ( q1.\"DISCRIM_DEPT\" = 'SoftwareDept') and ( q1.\"NO1\" <> ALL ( " +
        "select q3.\"NO1\" from IDEPT q3 where ( ( q3.\"DISCRIM_DEPT\" = 'Dept') or ( q3.\"DISCRIM_DEPT\" = 'HardwareDept')  or  " +
        "( q3.\"DISCRIM_DEPT\" = 'SoftwareDept') ) and ( q3.\"REPORTTO_NO\" =  q2.\"NO1\") ) ) "));
    
    // Another test case of Derby251 where the exists table column is embedded in an expression.
    assertRowCount(2, s.executeQuery("select  distinct  q1.\"NO1\" from IDEPT q1, IDEPT q2 where ( q2.\"DISCRIM_DEPT\" = 'HardwareDept')  and " +
        "( q1.\"DISCRIM_DEPT\" = 'SoftwareDept') and ( q1.\"NO1\" <> ALL (select  q3.\"NO1\" from IDEPT q3 where  ( ABS(q3.\"REPORTTO_NO\") =  q2.\"NO1\")))"));


    //result ordering is not guaranteed, but order by clause will change how
    // distinct is executed.  So test by retrieving data into a temp table and
    // return results ordered after making sure the query was executed as expected
    s.execute("create table temp_result (c2 int, c3 int)");
    s.execute("insert into temp_result select distinct c2, c3 from two");
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedDistinctScan());
    
    // Try same query, but with an order by at the end.  This will use the sort for
    // the "order by" to do the distinct and not do a "DISTINCT SCAN".
    assertRowCount(3, s.executeQuery("select distinct c2, c3 from two order by c2, c3"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    
    // more than one table in the select list
    // Following runtime statistics output should have Eliminate duplicates = true
    assertRowCount(3, s.executeQuery("select distinct a.c1, b.c1 from one a, two b where a.c1 = b.c1 and b.c2 =1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());  
    
    // cross product join
    assertRowCount(8, s.executeQuery("select distinct a.c1 from one a, two b"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());  
    
    // no single table will yield at most 1 row
    assertRowCount(9, s.executeQuery("select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());
    assertRowCount(9, s.executeQuery("select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and a.c2 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());
    
    // both keys from unique index in where clause but joined to different tables
    assertRowCount(1, s.executeQuery("select distinct a.c1 from one a, two b, three c where a.c1 = b.c1 and c.c1 = b.c3 and a.c1 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());
    
    // join between two tables using one columns of unique key
    assertRowCount(3, s.executeQuery("select distinct a.c1 from two a, four b where a.c1 = b.c1 and b.c3 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());
    
    // join between two tables with no join predicate
    assertRowCount(9, s.executeQuery("select distinct a.c1, a.c3 from two a, one b"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());


    // join between three tables with two tables joined uniquely
    assertRowCount(1, s.executeQuery("select distinct a.c1 from one a, two b, three c where a.c1 = c.c1 and a.c1 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.eliminatedDuplicates());
    
    // queries that should eliminate the distinct
    // Following runtime statistics output should NOT have Eliminate duplicates = true
    // single table queries
    // unique columns in select list
    assertRowCount(8, s.executeQuery("select distinct c1 from one"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());
    
    assertRowCount(8, s.executeQuery("select distinct c1, c2 + c3 from one"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    assertRowCount(9, s.executeQuery("select distinct c3, c1 from two"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    // query returns single row
    assertRowCount(1, s.executeQuery("select distinct c2 from one where c1 = 3"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());
    
    assertRowCount(1, s.executeQuery("select distinct c3 from one where c1 = 3"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    // super-set in select list
    assertRowCount(8, s.executeQuery("select distinct c2, c5, c1 from one"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    assertRowCount(9, s.executeQuery("select distinct c2, c3, c1 from two"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());
    
    // multi-table queries


    // 1 to 1 join, select list is superset
    assertRowCount(8, s.executeQuery("select distinct a.c1 from one a, one b where a.c1 = b.c1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    assertRowCount(8, s.executeQuery("select distinct a.c1, 3 from one a, one b where a.c1 = b.c1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    assertRowCount(9, s.executeQuery("select distinct a.c1, a.c3, a.c2 from two a, one b where a.c1 = b.c1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    assertRowCount(9, s.executeQuery("select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and b.c3 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());


    //join between two tables using both columns of unique key
    assertRowCount(3, s.executeQuery("select distinct a.c1 from two a, four b where a.c1 = b.c1 and a.c3 = b.c3 and b.c3 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());
  
    s.execute("drop table temp_result");
    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
    s.close();
  }

View Full Code Here

      s.execute("insert into t2 values (1, '1', '1'), (1, '2', '2'), (2, '1', '1'), (2, '2', '2'), (null, 'null', 'null')");
    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
      
    // first column of an index
    assertRowCount(4, s.executeQuery("select distinct c1 from t1 where 1=1"));
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());


    // equality predicate on preceding key columns
    assertRowCount(1, s.executeQuery("select distinct c2 from t1 where c1 = 1 and c3 = '1'"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());
    
    // equality predicate on all key columns, non unique
    assertRowCount(1, s.executeQuery("select distinct c3 from t1 where c1 = 1 and c2 = '1'"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());
    
    // equality predicate on all key columns, non unique
    assertRowCount(1, s.executeQuery("select distinct c3 from t2 where c1 = 1 and c2 = '1'"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertFalse(rtsp.eliminatedDuplicates());
    
    // different orderings
    assertRowCount(6, s.executeQuery("select distinct c2, c1 from t1 where 1=1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());


    assertRowCount(2, s.executeQuery("select distinct c2 from t1 where c1 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());


    assertRowCount(1, s.executeQuery("select distinct c2, c1 from t1 where c3 = '1'"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());


    assertRowCount(1, s.executeQuery("select distinct c2 from t1 where c3 = '1' and c1 = 1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());


    // ordered, but no where clause - uses distinct scan
    // the following approach is used because the ordering of the results from
    // the distinct is not guaranteed (it varies depending on the JVM hash 
    // implementation), but adding an order by to the query may
    // change how we execute the distinct and we want to test the code path without
    // the order by.  By adding the temp table, we can maintain a single master
    // file for all JVM's.
    
    s.execute("create table temp_result (result_column int)");
    s.execute("insert into temp_result (select distinct c1 from t1)");
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedDistinctScan());
    assertRowCount(4, s.executeQuery("select * from temp_result order by result_column"));
    
    // test distinct with an order by
    assertRowCount(4, s.executeQuery("select distinct c1 from t1 order by c1"));
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.usedDistinctScan());
    assertTrue(rtsp.eliminatedDuplicates());    
    
      s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
      s.execute("drop table temp_result");
    s.execute("drop table t1");
    s.execute("drop table t2");

View Full Code Here

        int expRowCount) throws SQLException
    {
        if (!rStat.next())
            return false;


        RuntimeStatisticsParser rsp =
            new RuntimeStatisticsParser(rStat.getString(1));


        return (rsp.usedIndexRowToBaseRow() && rsp.usedIndexScan()
            && (rsp.rowsQualifiedEquals(expRowCount)));
    }

View Full Code Here

        String sql1 = 
          "SELECT t1.id, t1.name FROM test2 t2 INNER JOIN test1 t1 "+
          "ON t2.rel_id = t1.id WHERE t2.entity_id = 1 ORDER BY t1.id ASC";
        Statement s;
        ResultSet rs;
        RuntimeStatisticsParser rtsp;
        String [][] result;


        s = createStatement();
        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        result = new String[][] {
                {"101", "Pupy"},{"102", "Tom"}, {"103", "Jerry"}};
        JDBC.assertFullResultSet(rs, result);
    }

View Full Code Here

     */
    public void testAdditionalOrderByCases() throws SQLException {
        String sql1;
        Statement s;
        ResultSet rs;
        RuntimeStatisticsParser rtsp;
        String [][] result;


        s = createStatement();
        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        
        sql1 = "select a.col1, b.col2, c.col2 from a, b, c where c.col1=3 " +
        "order by a.col1, c.col1";
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        result = new String[][] {
                {"1", "2", "3"},{"1", "2", "3"}, {"1", "2", "3"},   
                {"1", "2", "3"},{"1", "2", "3"}, {"1", "2", "3"},   
                {"1", "2", "3"},{"1", "2", "3"}};
        JDBC.assertFullResultSet(rs, result);
        
        sql1 = "select a.col1, b.col2, c.col2 from a, b, c where a.col1=1 "+
        "and b.col1 = 2 and c.col1=3 order by a.col1, b.col1, c.col1";
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        JDBC.assertFullResultSet(rs, result);


        sql1 = "select c.col1, b.col1, a.col1 from a, b, c where a.col1=1 "+
        "and b.col1 = 2 and c.col1=3 order by c.col1, b.col1, a.col1";
        result = new String[][] {
                {"3", "2", "1"},{"3", "2", "1"}, {"3", "2", "1"},   
                {"3", "2", "1"},{"3", "2", "1"}, {"3", "2", "1"},   
                {"3", "2", "1"},{"3", "2", "1"}};
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        JDBC.assertFullResultSet(rs, result);


        sql1 = "select c.col1, b.col1, a.col1 from a, b, c where a.col1=1 "+
        "and b.col1 = 2 and c.col1=3 order by c.col2, b.col2, a.col2";
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        JDBC.assertFullResultSet(rs, result);
}

View Full Code Here

        "AND table1.id=m1.id AND m1.name='PostComponentId' "+
        "AND m1.value='21857' ORDER BY m0.value";


        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        ResultSet rs = s.executeQuery(sql1);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedTableScan("TABLE2"));
    assertTrue(rtsp.whatSortingRequired());


        rs = s.executeQuery(sql1);
        String[][] result = {
                {"4294967297", "000001", "21857"}, 
                {"2147483653", "000002", "21857"},

View Full Code Here

        "m1.name='PostComponentId' AND m1.value='21857' " +
        "ORDER BY m0.value";


        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        ResultSet rs = s.executeQuery(sql1);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("TABLE2","KEY3"));
    assertTrue(rtsp.usedTableScan("TABLE1"));
    assertTrue(rtsp.whatSortingRequired());
  
        rs = s.executeQuery(sql1);
        String[][] result = {
                {"4294967297", "000001", "21857"}, 
                {"2147483653", "000002", "21857"},

View Full Code Here

        "AND table1.id=m1.id AND m1.name='PostComponentId' AND "+ 
        " m1.value='21857' ORDER BY m0.value";


    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        ResultSet rs = s.executeQuery(sql1);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(
        s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("TABLE2","KEY3"));
    assertTrue(rtsp.usedTableScan("TABLE1"));
    assertTrue(rtsp.whatSortingRequired());


    rs = s.executeQuery(sql1);
        String[][] result = {
                {"4294967297", "000001", "21857"}, 
                {"2147483653", "000002", "21857"},

View Full Code Here

    //statistics available for T2I2 to show that it is a better index
    ps = prepareStatement("SELECT * FROM t2 WHERE c21=? AND c22=?");
      ps.setInt(1, 0);
        ps.setString(2, "Tuple 4");
        JDBC.assertDrainResults(ps.executeQuery());
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));


    //Running the update statistics below will create statistics for T2I2
    s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T2','T2I2')");
        rs = s.executeQuery("SELECT * FROM SYS.SYSSTATISTICS");
        JDBC.assertDrainResults(rs, 1);
    
        //Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
        //looking at it's plan will show that this time it picked up more
        //efficient index which is T2I2. 
        JDBC.assertDrainResults(ps.executeQuery());
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I2"));
        //cleanup
        s.executeUpdate("DROP TABLE t2");
        //End of test case for better index selection after statistics
        //availability
    }

View Full Code Here

    // simple query SELECT MAX(D1) FROM T1:
    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        JDBC.assertFullResultSet(
                s.executeQuery("SELECT MAX(D1) FROM D3904_T1"),
            new String[][] {  {"2008-10-02"} } );
    RuntimeStatisticsParser rtsp =
      SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedLastKeyIndexScan());
    assertFalse(rtsp.usedIndexRowToBaseRow());


    // A form of the Beetle 4423 query:
        JDBC.assertFullResultSet(
                s.executeQuery("SELECT MAX(D1) " +
          "FROM d3904_T1, D3904_T2 WHERE d3904_T1.D1='2008-10-02'"),

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.derbyTesting.junit.RuntimeStatisticsParser$Qualifier

org.apache.derbyTesting.functionTests.tests.jdbcapi.SetTransactionIsolationTest

org.apache.derbyTesting.functionTests.tests.lang.DistinctTest

org.apache.derbyTesting.functionTests.tests.lang.DynamicLikeOptimizationTest

org.apache.derbyTesting.functionTests.tests.lang.ForUpdateTest

org.apache.derbyTesting.functionTests.tests.lang.GroupByTest

org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest

org.apache.derbyTesting.functionTests.tests.lang.InListMultiProbeTest

org.apache.derbyTesting.functionTests.tests.lang.JoinTest

org.apache.derbyTesting.functionTests.tests.lang.OptimizerOverridesTest

org.apache.derbyTesting.functionTests.tests.lang.OrderByAndOffsetFetchInSubqueries

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.