/*
 * Class org.apache.derbyTesting.functionTests.tests.lang.SelectivityTest
 *
 * 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.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
import org.apache.derbyTesting.junit.SQLUtilities;

public class SelectivityTest extends BaseJDBCTestCase {

    public SelectivityTest(String name) {
        super(name);
    }
    
    public static Test suite() {
        
        Properties props = new Properties();
        // first disable the automatic statistics gathering so we get
        // clean statistics
        // then switch the statement cache size to 0, so that doesn't
        // interfere and previous tests' left-overs are gone.
        props.setProperty("derby.storage.indexStats.auto", "false");
        props.setProperty("derby.language.statementCacheSize", "0");
        // set the props, and boot the db
        Test test = new DatabasePropertyTestSetup(
            new BaseTestSuite(SelectivityTest.class), props, true);
        
        return new CleanDatabaseTestSetup(test) {
            protected void decorateSQL(Statement s) throws SQLException
            {        
                s.executeUpdate("create table two (x int)");
                s.executeUpdate("insert into two values (1),(2)");
                s.executeUpdate("create table ten (x int)");
                s
                        .executeUpdate("insert into ten values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)");
                s.executeUpdate("create table twenty (x int)");
                s
                        .executeUpdate("insert into twenty values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)");
                s
                        .executeUpdate("create table hundred (x int generated always as identity, dc int)");
                s
                        .executeUpdate("insert into hundred (dc) select t1.x from ten t1, ten t2");
                s
                        .executeUpdate("create table template (id int not null generated always as identity, two int, twenty int, hundred int)");
                // 4000 rows
                s
                        .executeUpdate("insert into template (two, twenty, hundred) select two.x, twenty.x, hundred.x from two, twenty, hundred");
                s.executeUpdate("create index template_two on template(two)");
                s
                        .executeUpdate("create index template_twenty on template(twenty)");
                // 20 distinct values
                s
                        .executeUpdate("create index template_22 on template(twenty,two)");
                s
                        .executeUpdate("create unique index template_id on template(id)");
                s
                        .executeUpdate("create index template_102 on template(hundred,two)");
                s
                        .executeUpdate("create table test (id int, two int, twenty int, hundred int)");
                s.executeUpdate("create index test_id on test(id)");
                s.executeUpdate("insert into test select * from template");

                s.executeUpdate("create view showstats as "
                                + "select cast (conglomeratename as varchar(20)) indexname, "
                                + "cast (statistics as varchar(40)) stats, "
                                + "creationtimestamp createtime, "
                                + "colcount ncols "
                                + "from sys.sysstatistics, sys.sysconglomerates "
                                + "where conglomerateid = referenceid");
                ResultSet statsrs = s
                        .executeQuery("select indexname, stats, ncols from showstats order by indexname, stats, createtime, ncols");
                JDBC.assertFullResultSet(statsrs, new String[][] {
                        {"TEMPLATE_102","numunique= 100 numrows= 4000","1"},
                        {"TEMPLATE_102","numunique= 200 numrows= 4000","2"},
                        {"TEMPLATE_22","numunique= 20 numrows= 4000","1"},
                        {"TEMPLATE_22","numunique= 40 numrows= 4000","2"},
                        {"TEMPLATE_TWENTY","numunique= 20 numrows= 4000","1"},
                        {"TEMPLATE_TWO","numunique= 2 numrows= 4000","1"}});               
                s
                        .executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEMPLATE',null)");
                s
                        .executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST',null)");
                statsrs = s
                        .executeQuery("select indexname, stats, ncols from showstats order by indexname, stats, createtime, ncols");
                JDBC.assertFullResultSet(statsrs, new String[][] {
                        {"TEMPLATE_102","numunique= 100 numrows= 4000","1"},
                        {"TEMPLATE_102","numunique= 200 numrows= 4000","2"},
                        {"TEMPLATE_22","numunique= 20 numrows= 4000","1"},
                        {"TEMPLATE_22","numunique= 40 numrows= 4000","2"},
                        {"TEMPLATE_TWENTY","numunique= 20 numrows= 4000","1"},
                        {"TEMPLATE_TWO","numunique= 2 numrows= 4000","1"},
                        {"TEST_ID","numunique= 4000 numrows= 4000","1"},
                        });
                
                s.executeUpdate("create table t1 " +
                		"(id int generated always as identity, " +
                		"two int, twenty int, hundred varchar(3))");
                s.executeUpdate("insert into t1 (hundred, twenty, two) " +
                		"select CAST(CHAR(hundred.x) AS VARCHAR(3)), " +
                		"twenty.x, two.x from hundred, twenty, two");
                s.executeUpdate("create table t2 " +
                		"(id int generated always as identity, " +
                		"two int, twenty int, hundred varchar(3))");
                s.executeUpdate("insert into t2 (hundred, twenty, two) " +
                		"select CAST(CHAR(hundred.x) AS VARCHAR(3)) , " +
                		"twenty.x, two.x from hundred, twenty, two");
                s.executeUpdate("create table t3 " +
                		"(id int generated always as identity, " +
                		"two int, twenty int, hundred varchar(3))");
                s.executeUpdate("insert into t3 (hundred, twenty, two) " +
                		"select CAST(CHAR(hundred.x) AS VARCHAR(3)), " +
                		"twenty.x, two.x from hundred, twenty, two");
                s.executeUpdate("create index t1_hundred on t1(hundred)");
                s.executeUpdate("create index t1_two_twenty on t1(two,twenty)");
                s.executeUpdate("create index " +
                		"t1_twenty_hundred on t1(twenty, hundred)");
                s.executeUpdate("create index t2_hundred on t2(hundred)");
                s.executeUpdate("create index t2_two_twenty on t2(two,twenty)");
                s.executeUpdate("create index t2_twenty_hundred on t2(twenty, hundred)");
                s.executeUpdate("create index t3_hundred on t3(hundred)");
                s.executeUpdate("create index t3_two_twenty on t3(two,twenty)");
                s.executeUpdate("create index t3_twenty_hundred on t3(twenty, hundred)");
                s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                        "('APP','T1',null)");
                s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                        "('APP','T2',null)");
                s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                        "('APP','T3',null)");

                statsrs = s.executeQuery(
                        "select indexname, stats, ncols from showstats " +
                        "where indexname like 'T1%' " +
                        "order by indexname, stats");
                JDBC.assertFullResultSet(statsrs, new String[][] {
                        {"T1_HUNDRED","numunique= 100 numrows= 4000","1"},
                        {"T1_TWENTY_HUNDRED","numunique= 20 numrows= 4000","1"},
                        {"T1_TWENTY_HUNDRED","numunique= 2000 numrows= 4000","2"},
                        {"T1_TWO_TWENTY","numunique= 2 numrows= 4000","1"},
                        {"T1_TWO_TWENTY","numunique= 40 numrows= 4000","2"}});
                statsrs = s.executeQuery(
                        "select indexname, stats, ncols from showstats " +
                        "where indexname like 'T2%' order by indexname, stats");
                JDBC.assertFullResultSet(statsrs, new String[][] {
                        {"T2_HUNDRED","numunique= 100 numrows= 4000","1"},
                        {"T2_TWENTY_HUNDRED","numunique= 20 numrows= 4000","1"},
                        {"T2_TWENTY_HUNDRED","numunique= 2000 numrows= 4000","2"},
                        {"T2_TWO_TWENTY","numunique= 2 numrows= 4000","1"},
                        {"T2_TWO_TWENTY","numunique= 40 numrows= 4000","2"}});
                statsrs = s.executeQuery(
                        "select indexname, stats, ncols from showstats " +
                        "where indexname like 'T3%' order by indexname, stats");
                JDBC.assertFullResultSet(statsrs, new String[][] {
                        {"T3_HUNDRED","numunique= 100 numrows= 4000","1"},
                        {"T3_TWENTY_HUNDRED","numunique= 20 numrows= 4000","1"},
                        {"T3_TWENTY_HUNDRED","numunique= 2000 numrows= 4000","2"},
                        {"T3_TWO_TWENTY","numunique= 2 numrows= 4000","1"},
                        {"T3_TWO_TWENTY","numunique= 40 numrows= 4000","2"}});
                
                s.executeUpdate("create table scratch_table" +
                        "(id int, two int, twenty int, hundred int)");
                s.executeUpdate("insert into scratch_table select " +
                        "id, two, twenty, CAST(CHAR(hundred) AS INTEGER) " +
                        "from t1");
                s.executeUpdate("create index st_all on scratch_table" +
                        "(two, twenty, hundred)");
                s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                        "('APP','SCRATCH_TABLE',null)");
                
                s.executeUpdate("create table complex" +
                        "(id int generated always as identity, " +
                        "two int, twenty int, hundred int, a int, b int)");
                s.executeUpdate(
                        "insert into complex (two, twenty, hundred, a, b) " +
                        "select two.x, twenty.x, hundred.x, two.x, twenty.x " +
                        "from two, twenty, hundred");
                s.executeUpdate("create index complexind on complex" +
                        "(two, twenty, hundred, a, b)");
                s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                        "('APP','COMPLEX',null)");

            }
        };
    }
    
    public void testSingleColumnSelectivity() throws SQLException {
        // choose whatever plan you want but the row estimate should be.
        //(n * n) * 0.5
        // join on two, template inner, all rows.
        Connection conn = getConnection();
        Statement s = createStatement();
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEMPLATE',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEST',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        s.executeQuery("select template.id from --DERBY-PROPERTIES joinOrder=fixed\n" 
                + "test, template where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        
        // choose hash join. Selectivity should be the same
        // join on two. template inner, hash join
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash \n" +
                "where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        RuntimeStatisticsParser rtsp = 
              SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // choose NL join, no index. Selectivity should be the same
        // join on two. template inner, NL, no index, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertFalse(rtsp.usedHashJoin());
        
        // choose NL join, index template_two. Selectivity should be the same
        // join on two. template inner, NL, index=two, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_two \n" +
                "where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
        
        // do joins on 20
        // first NL
        // join on twenty. template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty").close();
        // Rowcount should be same as testSingleColumnSelectivityHash
        checkEstimatedRowCount(conn,802001.25);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        // join on 20 but use index 20_2
        // cost as well as selectivity should be divided using selectivity
        // cost should same as template_twenty, or just a shade more...
        // join on twenty. template inner, NL, index=template_22, all rows
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_22 \n" +
                "where test.twenty = template.twenty").close();
        checkEstimatedRowCount(conn,802001.25);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_22"));
        
        // join on twenty but no index
        // note: the original test had this comment:
        // 'rc should be divided using selectivity. cost should be way different'
        // however, it seems the ec is identical.
        // join on twenty, template inner, NL, index=null, all rows
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.twenty = template.twenty").close();
        checkEstimatedRowCount(conn,802001.25);
        
        // still single column, try stuff on 100 but with extra qualification
        // on outer table.
        // row count is 100 * 4000 * 0.01 = 4000
        // join on hundred. 
        // template inner, NL, index=template_102, 100 rows from outer
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_102 \n" +
                "where test.hundred = template.hundred and test.id <= 100").close();
        // note: original cloudscape result was expecting 3884.85 here.
        checkEstimatedRowCount(conn,3924.9);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEST", "TEST_ID"));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_102"));
        
        // join on hundred. 
        // template inner, NL, index=null, 100 rows from outer
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.hundred = template.hundred and test.id <= 100").close();
        checkEstimatedRowCount(conn,3924.9);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEST", "TEST_ID"));
        
        // join on hundred. 
        // template inner, hash, index=null, 100 rows from outer.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash, " +
                "index=null \n" +
                "where test.hundred = template.hundred and test.id <= 100").close();
        checkEstimatedRowCount(conn,3924.9);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEST", "TEST_ID"));
    }
    
    public void testMultiPredicate() throws SQLException {
        // multi predicate tests.
        // first do a oin involving twenty and two
        // forde use of a simngle column index to do the join
        // the row count should involve statistics from both 10 and 2 though...
        
        // row count should 4K * 4K * 1/40 = 400,000
        // cost doesn't show up in output but should depend on the index
        // being used (verify by hand before checking in.)
        setAutoCommit(false);
        Connection conn = getConnection();
        Statement s = createStatement();
        
        // join on twenty/two. template inner, hash, index=null, all rows.
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEST',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEMPLATE',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // join on twenty/two. template inner, NL, index=template_two, all rows
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_two \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
        
        // join on twenty/two. 
        // template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        // join on twenty/two. template inner, NL, index=template_22, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_22 \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_22"));
        
        // multi predicate tests continued
        // drop index twenty, two -- use above predicates
        // should be smart enough to figure out the selectivity by
        // combining twenty and two.
        s.executeUpdate("drop index template_22");
        
        // join on twenty/two. index twenty_two dropped. 
        // template inner, hash, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // join on twenty/two. index twenty_two dropped. 
        // template inner, NL, index=template_two, all rows.'
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_two \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
        
        // join on twenty/two. index twenty_two dropped. 
        // template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        s.executeUpdate("drop index template_two");
        
        // we only have index template_twenty
        // for the second predicate we should use 0.1 instead of 0.5
        // thus reducing earlier row count by a factor of 5
        // 80,000 instead of 400,000
        
        // join on twenty/two. index twenty_two and two dropped. 
        // template inner, NL, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,80200.12500000001);
        
        // join on twenty/two. index twenty_two and two dropped. 
        // template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,80200.12500000001);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        // now drop index template_twenty
        // selectivity should become 0.1 * 0.1 = 0.01
        // 16 * 10^6 * .01 = 160,000
        
        s.executeUpdate("drop index template_twenty");
        
        // join on twenty/two. all indexes dropped.
        // template inner, NL, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,160400.25000000003);
        rollback();
    } 
    
    public void testTwoWayJoins() throws SQLException {
        setAutoCommit(false);
        Connection conn = getConnection();
        Statement s = createStatement();
        
        // throw in aditional predicates
        // see that the optimizer does the right thing
        
        // index on template_102. join on hundred, constant predicate on two. 
        // should be able to use statistics for hundred_two to com up with
        // row estimate.
        
        // selectivity should be 0.01 * 0.5 = 0.005
        // row count is 16*10^6 * 0.005 = 8*10^4.
        
        // join on hundred. constant pred on two. NL, index=null, all rows.
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEMPLATE',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEST',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.two").close();
        checkEstimatedRowCount(conn,80200.125);
        
        // just retry above query with different access paths
        // row count shouldn't change!
        // join on hundred. constant pred on two. 
        // NL, index=template_102, all rows.
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_102 \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.two").close();
        checkEstimatedRowCount(conn,80200.125);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_102"));
        
        // hundred and twenty
        // we can use statistics for 100,2 to get selectivity for 100 and
        // twenty and twenty to get selectivity for 20
        // selectivity should 0.01 * 0.05 = 0.0005 -> 80,000
        // join on hundred. constant pred on twenty. 
        // NL, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.twenty").close();
        checkEstimatedRowCount(conn,8020.0125);
        
        // 'join on hundred. constant pred on twenty. 
        // NL, index=template_102 all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_102 \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.twenty").close();
        checkEstimatedRowCount(conn,8020.0125);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_102"));
    }
    
    public void testThreeWayJoins() throws SQLException {
        setAutoCommit(false);
        Connection conn = getConnection();
        Statement s = createStatement();
        
        // t1 x t2 yields 8000 rows.
        // x t3 yields 8*4 * 10^6 /2 = 16*10^6
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','T1',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','T2',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','T3',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t1.twenty = t2.twenty and " +
                "t2.two = t3.two").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 2, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        
        // t1 x t2 -> 16 * 10^4.
        // x t3    -> 32 * 10^7
        // additional pred -> 32 * 10^5
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two and " +
                "t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        // variations on above query; try different join strategies
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 --DERBY-PROPERTIES joinStrategy=hash \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 --DERBY-PROPERTIES joinStrategy=nestedLoop \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2 --DERBY-PROPERTIES joinStrategy=hash \n, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2 --DERBY-PROPERTIES joinStrategy=hash \n, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        // duplicate predicates; this time t1.hundred=?
        // will show up twice when t1 is optimized at the end
        // selectivity should be same as above
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // variations on above query; try different join strategies
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2, t1 --DERBY-PROPERTIES joinStrategy=hash \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2, t1 --DERBY-PROPERTIES joinStrategy=nestedLoop \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3 --DERBY-PROPERTIES joinStrategy=nestedLoop \n, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2 --DERBY-PROPERTIES joinStrategy=hash \n, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // some more variations on the above theme
        // some constant predicates thrown in.
        // remember hundred is a char column
        // -- for some reason if you give the constant 
        // as a numeric argument it doesn't recognize that 
        // as a constant start/stop value for the index 
        // The error is that the types must be comparable.
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred " +
                "and t1.hundred='1'").close();
        checkEstimatedRowCount(conn,30458.025);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T2", "T2_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        
        // we have t1.100=t2.100 and t1.100=t3.100, so 
        // t2.100=t3.100 is redundant. 
        // row count shouldn't factor in the redundant predicate.
        // row count should be 3200000.0
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred " +
                "and t2.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // slightly different join predicates-- use composite stats.
        // t1 x t2            --> 16 * 10.4.
        //         x t3       --> 16 * 10.4 * 4000 * 1/40 = 16*10.6
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // same as above but muck around with join order.
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t1, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t3, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t1, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        
        // and just for fun, what would we have gotten without statistics.
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES useStatistics=false, joinOrder=fixed \n" + 
                "t3, t1, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,6.4240300125000015E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
    }
    
    public void testScratch() throws SQLException {
        // make sure we do a good job of stats on 1/3
        setAutoCommit(false);
        Connection conn = getConnection();
        Statement s = createStatement();
        
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','T1',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','SCRATCH_TABLE',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        
        // Note: The original test did the first query *after* the following:
        // since the statistics (rowEstimates) are not precise, force a 
        // checkpoint to force out all the row counts to the container header,
        // and for good measure do a count which will update the row counts 
        // exactly.
        // s.executeUpdate("CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()");
        // But if that's executed, the estimatedRowCount becomes: 2582648.45
        // Without the checkpoint, the following select counts are unnecessary.
        // assertTableRowCount("T1", 4000);
        // assertTableRowCount("SCRATCH_TABLE", 4000);
        
        // preds are on columns 1 and 3
        // should use default stats for 100 (0.1) and 0.5 for two
        
        // 16*10.6 * 5*10.-2 = 80*10.4
        
        s.executeQuery("select s.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, scratch_table s " +
                "where t1.two = s.two " +
                "and s.hundred = CAST(CHAR(t1.hundred) AS INTEGER)").close();
        checkEstimatedRowCount(conn,802001.25);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // preds are on column 2.
        // 0.1 -> 16*10.5
        s.executeQuery("select s.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, scratch_table s " +
                "where t1.twenty = s.twenty").close();
        checkEstimatedRowCount(conn,1604002.5);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_TWO_TWENTY"));
        assertTrue(rtsp.usedHashJoin());
        
        // preds are on column 2,3.
        // 0.01 -> 16*10.4
        s.executeQuery("select s.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, scratch_table s " +
                "where t1.twenty = s.twenty " +
                "and s.hundred = CAST(CHAR(t1.hundred) AS INTEGER)").close();
        checkEstimatedRowCount(conn,160400.2500000);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_TWENTY_HUNDRED"));
        assertTrue(rtsp.usedHashJoin());
    }
    
    public void testStatMatcher() throws SQLException {
        // test of statistics matcher algorithm; make sure that we choose the
        // best statistics (the weight stuff in predicatelist)
        
        setAutoCommit(false);
        Connection conn = getConnection();
        Statement s = createStatement();
        
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','T1',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','T2',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        
        // 2,20,100
        s.executeQuery("select t1.id from t1, t2 " +
                "where t1.two = t2.two " +
                "and t1.twenty = t2.twenty " +
                "and t1.hundred = t2.hundred").close();
        checkEstimatedRowCount(conn,4010.00625);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // now muck around with the order of the predicates
        // 2,100,20
        s.executeQuery("select t1.id from t1, t2 " +
                "where t1.two = t2.two " +
                "and t1.hundred = t2.hundred " +
                "and t1.twenty = t2.twenty").close();
        checkEstimatedRowCount(conn,4010.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // 100,20,2
        s.executeQuery("select t1.id from t1, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t1.twenty = t2.twenty " +
                "and t1.two = t2.two").close();
        checkEstimatedRowCount(conn,4010.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // 100,2,20
        s.executeQuery("select t1.id from t1, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t1.two = t2.two " +
                "and t1.twenty = t2.twenty").close();
        checkEstimatedRowCount(conn,4010.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        s.executeQuery("select t1.id from t1, t2 " +
                "where t1.twenty = t2.twenty " +
                "and t1.hundred = t2.hundred " +
                "and t1.two = t2.two").close();
        checkEstimatedRowCount(conn,4010.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        s.executeQuery("select t1.id from t1, t2 " +
                "where t1.twenty = t2.twenty " +
                "and t1.two = t2.two " +
                "and t1.hundred = t2.hundred").close();
        checkEstimatedRowCount(conn,4010.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
    }
    
    // Beetle was the bug system for Cloudscape, the forerunner
    // of Derby. The bug report described a query that was hitting an Error:
    // XJ001: Java exception: '2 >=2: java.lang.ArrayIndexOutOfBoundsException
    // on a specific query; when running the same query with DERBY-PROPERTIES
    // useStatistics=false the same query worked correctly.
    // The fix is in org.apache.derby.impl.sql.compile.PredicateList
    // referencing beetle 4321.
    public void testBeetle4321() throws SQLException {
        // test of statistics matcher algorithm; make sure that we choose the
        // best statistics (the weight stuff in predicatelist)
        
        setAutoCommit(false);
        Connection conn = getConnection();
        Statement s = createStatement();
        
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','COMPLEX',NULL)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TEMPLATE',NULL)");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        
        s.executeQuery("select t10.two from complex t10, template t20 " +
                "where t10.two = 1 " +
                "and t10.hundred = 2 " +
                "and t10.a = 2 " +
                "and t10.b = 2").close();
        checkEstimatedRowCount(conn,7945.920000000);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("COMPLEX", "COMPLEXIND"));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
    }
    
    public void testBasic() throws SQLException {
        // basic test for update statistics; make sure that statistics with
        // correct values are created and dropped and such.
        setAutoCommit(false);
        Statement s = createStatement();
        
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        
        // first on int, multi-column
        s.executeUpdate("create table tbasic1 " +
                "(c1 int generated always as identity, c2 int, c3 int)");
        for (int i=1; i<5 ; i++)
        {
            for (int j=1 ; j<3 ; j++)
            {
                for (int c=0; c<2 ; c++)
                    s.executeUpdate(
                        "insert into tbasic1 values " +
                        "(default, " + i + ", " + j + ")");
            }
        }
        
        // create index should automatically create stats. 
        s.executeUpdate("create index t1_c1c2 on tbasic1 (c1, c2)");
        ResultSet statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'T1_C1C2%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"T1_C1C2","numunique= 16 numrows= 16","1"},
                {"T1_C1C2","numunique= 16 numrows= 16","2"}});
        // index dropped stats should be dropped.
        s.executeUpdate("drop index t1_c1c2");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'T1_C1C2%' order by indexname");
        JDBC.assertEmpty(statsrs);
        
        // second part of the test.
        // check a few extra types.
        s.executeUpdate("create table tbasic2 " +
                "(i int not null, " +
                "vc varchar(32) not null, " +
                "dt date, ch char(20), " +
                "constraint pk primary key (i, vc))");
        s.executeUpdate("create index tbasic2_i on tbasic2(i)");
        s.executeUpdate("create index tbasic2_ch_dt on tbasic2(ch, dt)");
        s.executeUpdate("create index tbasic2_dt_vc on tbasic2(dt, vc)");
        // do normal inserts. 
        s.executeUpdate(
                "insert into tbasic2 values (1, 'one', '2001-01-01', 'one')");
        s.executeUpdate(
                "insert into tbasic2 values (2, 'two', '2001-01-02', 'two')");
        s.executeUpdate(
                "insert into tbasic2 values (3, 'three', '2001-01-03', 'three')");
        s.executeUpdate(
                "insert into tbasic2 values (1, 'two', '2001-01-02', 'one')");
        s.executeUpdate(
                "insert into tbasic2 values (1, 'three', '2001-01-03', 'one')");
        s.executeUpdate(
                "insert into tbasic2 values (2, 'one', '2001-01-01', 'two')");
        
        // figure out the name of the primary key's backing index
        statsrs = s.executeQuery(
                "select conglomeratename from sys.sysconglomerates " +
                "where conglomeratename like 'SQL%'");
        statsrs.next();
        String backIndName = statsrs.getString("conglomeratename");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC2','" + backIndName + "')");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'SQL%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {backIndName,"numunique= 3 numrows= 6","1"},
                {backIndName,"numunique= 6 numrows= 6","2"}});
        
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC2','TBASIC2_I')");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname = 'TBASIC2_I' order by indexname");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC2_I","numunique= 3 numrows= 6","1"}});
        
        // do another insert then just updstat for whole table.
        s.executeUpdate(
                "insert into tbasic2 values(2, 'three', '2001-01-03', 'two')");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC2',null)");
        
        // make sure that stats are correct.
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname = 'TBASIC2_I' order by indexname");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC2_I","numunique= 3 numrows= 7","1"}});
        statsrs = s.executeQuery(
                "select count(*) from (select distinct i from tbasic2) t");
        JDBC.assertFullResultSet(statsrs, new String[][] {{"3"}});
        
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname = 'TBASIC2_CH_DT' order by indexname, stats");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC2_CH_DT","numunique= 3 numrows= 7","1"},
                {"TBASIC2_CH_DT","numunique= 7 numrows= 7","2"}});
        
        statsrs = s.executeQuery(
                "select count(*) from (select distinct ch from tbasic2) t");
        JDBC.assertFullResultSet(statsrs, new String[][] {{"3"}});
        statsrs = s.executeQuery(
                "select count(*) from (select distinct ch, dt from tbasic2) t");
        JDBC.assertFullResultSet(statsrs, new String[][] {{"7"}});
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname = 'TBASIC2_DT_VC' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC2_DT_VC","numunique= 3 numrows= 7","1"},
                {"TBASIC2_DT_VC","numunique= 3 numrows= 7","2"}});
        statsrs = s.executeQuery(
                "select count(*) from (select distinct dt from tbasic2) t");
        JDBC.assertFullResultSet(statsrs, new String[][] {{"3"}});
        statsrs = s.executeQuery(
                "select count(*) from (select distinct dt, vc from tbasic2) t");
        JDBC.assertFullResultSet(statsrs, new String[][] {{"3"}});
        statsrs = s.executeQuery(
                "select stats, ncols from showstats " +
                "where indexname like 'SQL%' order by stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"numunique= 3 numrows= 7","1"},
                {"numunique= 7 numrows= 7","2"}});
        
        // delete everything from t2, do bulkinsert see what happens.
        assertUpdateCount(s, 7, "delete from tbasic2");
        
        // no material impact on stats
        // note; the test didn't actually confirm, here's the expected now
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC2%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC2_CH_DT","numunique= 3 numrows= 7","1"},
                {"TBASIC2_CH_DT","numunique= 7 numrows= 7","2"},
                {"TBASIC2_DT_VC","numunique= 3 numrows= 7","1"},
                {"TBASIC2_DT_VC","numunique= 3 numrows= 7","2"},
                {"TBASIC2_I","numunique= 3 numrows= 7","1"}});
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC2',null)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC2%' " +
                "order by indexname, stats, ncols");
        JDBC.assertEmpty(statsrs);
        
        // Note: the original (Cloudscape) test did a 'bulkinsert'.
        // this is now only possible internally, and is used in
        // the SYSCS_IMPORT_DATA system procedures.
        // Possibly this test can be added onto by moving the
        // value intended to be inserted into a data file, and calling
        // SYSCS_UTIL.SYSCS_IMPORT_DATA using that.
        // The row inserted was:
        //        "values (2, 'one', '2001-01-01', 'two')");
        // subsequently, there was a bulk insert-replace, this is
        // no longer supported. We could update the row.
        // the replacement was of row:
        //        "(2, 'one', '2001-01-01', 'two'), " +
        // by
        //        "(1, 'one', '2001-01-01', 'two')");
        s.executeUpdate("drop table tbasic2");
        
        // various alter table operations to ensure correctness.
        // 1. add and drop constraint.
        s.executeUpdate("create table tbasic3 " +
                "(x int not null generated always as identity," +
                " y int not null, z int)");
        s.executeUpdate(
                "insert into tbasic3 (y,z) values " +
                "(1,1),(1,2),(1,3),(1,null),(2,1),(2,2),(2,3),(2,null)");
        // first alter table to add primary key;
        s.executeUpdate("alter table tbasic3 " +
                "add constraint pk_tbasic3 primary key (x,y)");
        statsrs = s.executeQuery(
                "select conglomeratename from sys.sysconglomerates " +
                "where conglomeratename like 'SQL%'");
        statsrs.next();
        backIndName = statsrs.getString("conglomeratename");
        statsrs = s.executeQuery(
                "select stats, ncols from showstats " +
                "where indexname like '" + backIndName + "' " +
                "order by stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"numunique= 8 numrows= 8","1"},
                {"numunique= 8 numrows= 8","2"}});
        // now drop the constraint
        s.executeUpdate("alter table tbasic3 drop constraint pk_tbasic3");
        statsrs = s.executeQuery(
                "select stats, ncols from showstats " +
                "where indexname like '" + backIndName + "' " +
                "order by stats, ncols");
        JDBC.assertEmpty(statsrs);
        
        // try compress with tons of rows. you can never tell 
        // what a few extra pages can do :)
        for (int i=0; i<9 ; i++)
            s.executeUpdate("insert into tbasic3(y,z) select y,z from tbasic3");
        statsrs = s.executeQuery("select count(*) from tbasic3");
        JDBC.assertFullResultSet(statsrs, new String[][] {{"4096"}});
        s.executeUpdate("create index tbasic3_xy on tbasic3(x,y)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC3_XY%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC3_XY","numunique= 4096 numrows= 4096","1"},
                {"TBASIC3_XY","numunique= 4096 numrows= 4096","2"}});
        s.executeUpdate("delete from tbasic3 where z is null");
        s.executeUpdate("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE" +
                "('APP', 'TBASIC3', 0)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC3_XY%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC3_XY","numunique= 3072 numrows= 3072","1"},
                {"TBASIC3_XY","numunique= 3072 numrows= 3072","2"}});
        s.executeUpdate("drop table tbasic3");
        
        s.executeUpdate("create table tbasic4 " +
                "(x int, y int, z int)");
        s.executeUpdate("insert into tbasic4 values (1,1,1)");
        s.executeUpdate("insert into tbasic4 values (1,2,1)");
        s.executeUpdate("insert into tbasic4 values (1,1,2)");
        
        s.executeUpdate("create index tbasic4_x on tbasic4(x)");
        s.executeUpdate("create index tbasic4_xy on tbasic4(x,y)");
        s.executeUpdate("create index tbasic4_yz on tbasic4(y,z)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC4%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC4_X","numunique= 1 numrows= 3","1"},
                {"TBASIC4_XY","numunique= 1 numrows= 3","1"},
                {"TBASIC4_XY","numunique= 2 numrows= 3","2"},
                {"TBASIC4_YZ","numunique= 2 numrows= 3","1"},
                {"TBASIC4_YZ","numunique= 3 numrows= 3","2"}});

        // if we drop column x, then stats for tbasic4_x should get dropped
        // index tbasic4_xy should get rebuilt to only be on y. so one of the
        // stats should be recreated. and tbasic4_yz shouldn remain in its
        // entirety.
        s.executeUpdate("alter table tbasic4 drop column x");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC4%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC4_XY","numunique= 2 numrows= 3","1"},
                {"TBASIC4_YZ","numunique= 2 numrows= 3","1"},
                {"TBASIC4_YZ","numunique= 3 numrows= 3","2"}});
        s.executeUpdate("drop table tbasic4");
        
        // test re tbasic5 were intended to exercise Cloudscape's
        // stored prepared statements. This is not supported in Derby
        // it also does some drop statistics, but there are already 
        // other tests that do this. So on to tbasic6.
        
        s.executeUpdate("create table tbasic6 " +
                "(i int generated always as identity," +
                " j varchar(10))");
        s.executeUpdate("create index tbasic6_i on tbasic6(i)");
        s.executeUpdate("create index tbasic6_j on tbasic6(j)");
        s.executeUpdate("create index tbasic6_ji on tbasic6(j,i)");
        char[] alphabet = {'a','b','c','d','e','f','g','h','i'};
        for (int i=0; i<alphabet.length-1 ; i++)
            s.executeUpdate("insert into tbasic6 " +
                    "values (default, '" + alphabet[i] + "')");
        for (int i=0; i<alphabet.length ; i++)
            s.executeUpdate("insert into tbasic6 " +
                    "values (default, '" + alphabet[i] + "')");
        for (int i=0; i<alphabet.length-1 ; i++)
            s.executeUpdate("insert into tbasic6 " +
                    "values (default, '" + alphabet[i] + "')");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC6','TBASIC6_J')");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC6',NULL)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC6%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC6_I","numunique= 25 numrows= 25","1"},
                {"TBASIC6_J","numunique= 9 numrows= 25","1"},
                {"TBASIC6_JI","numunique= 25 numrows= 25","2"},
                {"TBASIC6_JI","numunique= 9 numrows= 25","1"}});

        s.executeUpdate("delete from TBASIC6");
        // make the 17th row the same as the 16th;
        // make sure when we switch to the next group fetch
        // we handle the case correctly.
        for (int i=0; i<17 ; i++)
            s.executeUpdate("insert into tbasic6 values (default, 'a')");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','TBASIC6',NULL)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'TBASIC6%' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"TBASIC6_I","numunique= 17 numrows= 17","1"},
                {"TBASIC6_J","numunique= 1 numrows= 17","1"},
                {"TBASIC6_JI","numunique= 1 numrows= 17","1"},
                {"TBASIC6_JI","numunique= 17 numrows= 17","2"}});
        s.executeUpdate("drop table tbasic6");
        
        // table with no rows.
        s.executeUpdate("create table et (x int, y int)");
        s.executeUpdate("create index etx on et(x)");
        s.executeUpdate("create index ety on et(y)");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','ET','ETX')");
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','ET',NULL)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname like 'ET%' " +
                "order by indexname, stats, ncols");
        JDBC.assertEmpty(statsrs);
        s.executeUpdate("drop table et");
        
        // tests for nulls.
        s.executeUpdate("create table null_table (x int, y varchar(2))");
        s.executeUpdate("create index nt_x on null_table(x desc)");
        for (int i=1; i<4 ; i++)
            s.executeUpdate("insert into null_table " +
                    "values (" + i + ", '" + alphabet[i-1] + "')");
        for (int c=0; c<2 ; c++)
        {
            for (int i=1; i<4 ; i++)
                s.executeUpdate("insert into null_table " +
                        "values (null, '" + alphabet[i-1] + "')");
        }
        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" +
                "('APP','NULL_TABLE',NULL)");
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname = 'NT_X' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"NT_X","numunique= 9 numrows= 9","1"}});
        // try composite null keys (1,null) is unique from (1,null)
        // as is (null,1) from (null,1)
        s.executeUpdate("drop index nt_x");
        s.executeUpdate("create index nt_yx on null_table(y,x)");
        // the first key y has 3 unique values.
        // the second key y,x has 9 unique values because of nulls.
        statsrs = s.executeQuery(
                "select indexname, stats, ncols from showstats " +
                "where indexname = 'NT_YX' " +
                "order by indexname, stats, ncols");
        JDBC.assertFullResultSet(statsrs, new String[][] {
                {"NT_YX","numunique= 3 numrows= 9","1"},
                {"NT_YX","numunique= 9 numrows= 9","2"}});
    }
    
    // drop any tables created during testBasic
    protected void tearDown() throws Exception {
        Statement s = createStatement();
        try {
            s.execute("drop table tbasic1");
            s.execute("drop table tbasic2");
            s.execute("drop table tbasic3");
            s.execute("drop table tbasic4");
            s.execute("drop table tbasic6");
            s.execute("drop table et");
            s.execute("drop table null_table");
        } catch (SQLException sqle) {
            // if it doesn't work, never mind, we'll assume the
            // cleanDatabaseSetup will deal with it.
        }
        s.close();
        super.tearDown();
    }
}