You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by my...@apache.org on 2014/01/22 04:52:43 UTC
svn commit: r1560247 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang:
SelectivityTest.java selectivity.sql selectivity_derby.properties
Author: myrnavl
Date: Wed Jan 22 03:52:42 2014
New Revision: 1560247
URL: http://svn.apache.org/r1560247
Log:
DERBY-3955; test lang/selectivity.sql can be revived
committing patch 3 - which adds the remaining test cases from selectivity.sql to SelectivityTest.java
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/selectivity.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/selectivity_derby.properties
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java?rev=1560247&r1=1560246&r2=1560247&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SelectivityTest.java Wed Jan 22 03:52:42 2014
@@ -25,12 +25,14 @@ 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 junit.framework.TestSuite;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.JDBC;
public class SelectivityTest extends BaseJDBCTestCase {
@@ -39,24 +41,22 @@ public class SelectivityTest extends Bas
super(name);
}
- public void testSingleColumnSelectivity() throws SQLException {
- // choose whatever plan you want but the row estimate should be.
- //(n * n) * 0.5
- Connection conn = getConnection();
- Statement s = createStatement();
- 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);
-
-
-
- }
-
public static Test suite() {
- return new CleanDatabaseTestSetup(new TestSuite(SelectivityTest.class,
- "SelectivityTest")) {
- protected void decorateSQL(Statement s) throws SQLException {
+
+ 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 TestSuite(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)");
@@ -89,8 +89,7 @@ public class SelectivityTest extends Bas
s.executeUpdate("create index test_id on test(id)");
s.executeUpdate("insert into test select * from template");
- s
- .executeUpdate("create view showstats as "
+ s.executeUpdate("create view showstats as "
+ "select cast (conglomeratename as varchar(20)) indexname, "
+ "cast (statistics as varchar(40)) stats, "
+ "creationtimestamp createtime, "
@@ -111,7 +110,7 @@ public class SelectivityTest extends Bas
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");
+ .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"},
@@ -119,10 +118,1121 @@ public class SelectivityTest extends Bas
{"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"}}
- );
+ {"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");
+ JDBC.assertFullResultSet(statsrs, new String[][] {
+ {"T1_HUNDRED","numunique= 100 numrows= 4000","1"},
+ {"T1_TWENTY_HUNDRED","numunique= 2000 numrows= 4000","2"},
+ {"T1_TWENTY_HUNDRED","numunique= 20 numrows= 4000","1"},
+ {"T1_TWO_TWENTY","numunique= 40 numrows= 4000","2"},
+ {"T1_TWO_TWENTY","numunique= 2 numrows= 4000","1"}});
+ statsrs = s.executeQuery(
+ "select indexname, stats, ncols from showstats " +
+ "where indexname like 'T2%' order by indexname");
+ JDBC.assertFullResultSet(statsrs, new String[][] {
+ {"T2_HUNDRED","numunique= 100 numrows= 4000","1"},
+ {"T2_TWENTY_HUNDRED","numunique= 2000 numrows= 4000","2"},
+ {"T2_TWENTY_HUNDRED","numunique= 20 numrows= 4000","1"},
+ {"T2_TWO_TWENTY","numunique= 40 numrows= 4000","2"},
+ {"T2_TWO_TWENTY","numunique= 2 numrows= 4000","1"}});
+ statsrs = s.executeQuery(
+ "select indexname, stats, ncols from showstats " +
+ "where indexname like 'T3%' order by indexname");
+ JDBC.assertFullResultSet(statsrs, new String[][] {
+ {"T3_HUNDRED","numunique= 100 numrows= 4000","1"},
+ {"T3_TWENTY_HUNDRED","numunique= 2000 numrows= 4000","2"},
+ {"T3_TWENTY_HUNDRED","numunique= 20 numrows= 4000","1"},
+ {"T3_TWO_TWENTY","numunique= 40 numrows= 4000","2"},
+ {"T3_TWO_TWENTY","numunique= 2 numrows= 4000","1"}});
+ 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+ }
+
+ 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_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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ // 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=hash, " +
+ "index=template_twenty \n" +
+ "where test.twenty = template.twenty " +
+ "and test.two = template.two").close();
+ checkEstimatedRowCount(conn,401000.625);
+
+ 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);
+
+ // 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_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);
+
+ // 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);
+ }
+
+ 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);
+
+ // 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);
+
+ // 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);
+
+ 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);
+
+ 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);
+
+ 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);
+
+ // 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);
+
+ // 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);
+
+ 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);
+
+ 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);
+
+ 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);
+
+ // 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 errir 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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ 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);
+
+ 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);
+
+ 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);
+
+ 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);
+
+ // 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);
+ }
+
+ 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);
+
+ // 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);
+
+ // 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);
+ }
+
+ 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_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);
+
+ // 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);
+
+ // 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);
+
+ // 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);
+
+ 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);
+
+ 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);
+ }
+
+ // 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);
+ }
+
+ 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");
+ 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");
+ 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();
+ }
}