You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2010/01/12 22:03:40 UTC
Re: svn commit: r897938 [2/2] - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/services/context/ engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/harness/ testing/org/apache/derbyTesting/functionTest
Hi Myrna,
When you get a chance, will you look at following javadoc failures?
[javadoc] C:\nightlies\main\src\opensource\java\testing\org\apache\derbyTesting\junit\RuntimeStatisticsParser.java:220:
warning - @param argument "indexName" is not a parameter name.
[javadoc] C:\nightlies\main\src\opensource\java\testing\org\apache\derbyTesting\junit\RuntimeStatisticsParser.java:415:
warning - @ is an unknown tag.
[javadoc] C:\nightlies\main\src\opensource\java\testing\org\apache\derbyTesting\junit\RuntimeStatisticsParser.java:433:
warning - @ is an unknown tag.
[javadoc] 3 warnings
thanks,
Mamta
On Mon, Jan 11, 2010 at 8:54 AM, <my...@apache.org> wrote:
> Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AccessTest.java
> URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AccessTest.java?rev=897938&view=auto
> ==============================================================================
> --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AccessTest.java (added)
> +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AccessTest.java Mon Jan 11 16:54:01 2010
> @@ -0,0 +1,1857 @@
> +/*
> + Derby - Class org.apache.derbyTesting.functionTests.tests.store.AccessTest
> +
> + 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.store;
> +
> +import java.sql.CallableStatement;
> +import java.sql.Connection;
> +import java.sql.PreparedStatement;
> +import java.sql.ResultSet;
> +import java.sql.SQLException;
> +import java.sql.Statement;
> +import java.util.Properties;
> +
> +import junit.framework.Test;
> +
> +import org.apache.derbyTesting.functionTests.util.Formatters;
> +import org.apache.derbyTesting.junit.BaseJDBCTestCase;
> +import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
> +import org.apache.derbyTesting.junit.JDBC;
> +import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
> +import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
> +import org.apache.derbyTesting.junit.TestConfiguration;
> +
> +
> +public final class AccessTest extends BaseJDBCTestCase {
> +
> + /**
> + * Public constructor required for running test as standalone JUnit.
> + */
> + public AccessTest(String name)
> + {
> + super(name);
> + }
> +
> + public void tearDown() throws Exception {
> + Statement st = createStatement();
> + super.tearDown();
> + try {
> + st.executeUpdate("DROP FUNCTION PADSTRING");
> + } catch (SQLException e) {
> + // never mind.
> + }
> + }
> +
> + public static Test suite() {
> + Properties sysProps = new Properties();
> + sysProps.put("derby.optimizer.optimizeJoinOrder", "false");
> + sysProps.put("derby.optimizer.ruleBasedOptimization", "true");
> + sysProps.put("derby.optimizer.noTimeout", "true");
> +
> + Test suite = TestConfiguration.embeddedSuite(AccessTest.class);
> + return new CleanDatabaseTestSetup(new SystemPropertyTestSetup(suite, sysProps, true)) {
> + /**
> + * Creates the table used in the test cases.
> + *
> + */
> + protected void decorateSQL(Statement s) throws SQLException {
> + Connection conn = s.getConnection();
> + conn.setAutoCommit(false);
> +
> + s.execute("CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), "
> + + "LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME " +
> + "'org.apache.derbyTesting.functionTests.util.Formatters" +
> + ".padString' LANGUAGE JAVA PARAMETER STYLE JAVA");
> + }
> + };
> + }
> +
> + //---------------------------------------------------------
> + // test qualifier skip code on fields with length
> + // having the 8th bit set in low order length byte.
> + // --------------------------------------------------------
> + public void testQualifierSkipLOLB() throws Exception
> + {
> +
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> +
> + String [][] expRS;
> + String [] expColNames;
> +
> + setAutoCommit(false);
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '32768')");
> + cSt.execute();
> + st.executeUpdate("create table a ( " +
> + "i1 int, col00 varchar(384), col01 varchar(390), i2 int )");
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + // insert rows
> + st.executeUpdate(
> + "insert into a values (1, PADSTRING('10',384), "
> + + "PADSTRING('100',390), 1000)");
> + st.executeUpdate(
> + "insert into a values (2, PADSTRING('20',384), "
> + + "PADSTRING('200',390), 2000)");
> + st.executeUpdate(
> + "insert into a values (3, PADSTRING('30',384), "
> + + "PADSTRING('300',390), 3000)");
> +
> + rs = st.executeQuery("select i1, i2 from a where i2 = 3000");
> +
> + expColNames = new String [] {"I1", "I2"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][] {{"3", "3000"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + st.executeUpdate("drop table a");
> + commit();
> + }
> +
> + // test case for a fixed bug where the problem was that the btree split
> + // would self deadlock while trying to reclaim rows during the split.
> + // Fixed by just giving up if btree can't get the locks during the
> + // reclaim try.
> + public void testCSBug2590() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate("create table foo (a int, b varchar(900), c int)");
> +
> + // insert
> + st.executeUpdate("insert into foo values (1, PADSTRING('1',900), 1)");
> + st.executeUpdate("insert into foo values (2, PADSTRING('2',900), 1)");
> + st.executeUpdate("insert into foo values (3, PADSTRING('3',900), 1)");
> + st.executeUpdate("insert into foo values (4, PADSTRING('4',900), 1)");
> + st.executeUpdate("insert into foo values (5, PADSTRING('5',900), 1)");
> + st.executeUpdate("insert into foo values (6, PADSTRING('6',900), 1)");
> + st.executeUpdate("insert into foo values (7, PADSTRING('7',900), 1)");
> + st.executeUpdate("insert into foo values (8, PADSTRING('8',900), 1)");
> +
> + CallableStatement cSt;
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '4096')");
> + cSt.execute();
> + st.executeUpdate("create index foox on foo (a, b)");
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + commit();
> +
> + assertUpdateCount(st, 7, "delete from foo where foo.a <> 2");
> +
> + ResultSet rs = null;
> + String [][] expRS;
> +
> + // Test full cursor for update scan over all the rows in the heap,
> + // with default group fetch. Group fetch should be disabled.
> +
> + rs = st.executeQuery("select a, b, c from foo for update of c");
> + expRS = new String [][] {{"2","2","1"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // Do the same, but use a PreparedStatement.
> + PreparedStatement ps_scan_cursor = prepareStatement(
> + "select a, b, c from foo for update of c");
> + ResultSet scan_cursor = ps_scan_cursor.executeQuery();
> + expRS = new String [][] {{"2","2","1"}};
> + JDBC.assertFullResultSet(scan_cursor, expRS, true);
> +
> + // these inserts would cause a lock wait timeout before
> + // the bug fix.
> + st.executeUpdate("insert into foo values (1, PADSTRING('11',900), 1)");
> + st.executeUpdate("insert into foo values (1, PADSTRING('12',900), 1)");
> + st.executeUpdate("insert into foo values (1, PADSTRING('13',900), 1)");
> + st.executeUpdate("insert into foo values (1, PADSTRING('14',900), 1)");
> + st.executeUpdate("insert into foo values (1, PADSTRING('15',900), 1)");
> +
> + commit();
> + st.executeUpdate("drop table foo");
> + commit();
> + }
> +
> + // test case a fixed bug where the problem was that when
> + // the level of btree grew, raw store would incorrectly
> + // report that there was not enough space to move all the
> + // rows from the root page to a newly allocated leaf page,
> + // so the create index operation would fail with a
> + // message saying that a row was too big. create and
> + // load a table with values from 1024 down to 1, the
> + // reverse order is important to reproduce the bug.
> + public void testCSBug735() throws Exception
> + {
> +
> + ResultSet rs = null;
> + Statement st = createStatement();
> +
> + String [][] expRS;
> + String [] expColNames;
> +
> + st.executeUpdate("create table foo (a int)");
> + st.executeUpdate("insert into foo values (1024)");
> + st.executeUpdate("insert into foo (select foo.a - 1 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 2 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 4 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 8 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 16 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 32 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 64 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 128 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 256 from foo)");
> + st.executeUpdate("insert into foo (select foo.a - 512 from foo)");
> +
> + // this create index used to fail.
> + assertEquals(0, st.executeUpdate("create index a on foo (a)"));
> +
> + // Check the consistency of the indexes
> + rs = st.executeQuery(
> + "VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'FOO')");
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][] {{"1"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // a delete of the whole table also exercises the index well.
> + assertUpdateCount(st, 1024, "delete from foo");
> +
> + st.executeUpdate("drop table foo");
> + }
> +
> + // ---------------------------------------------------------
> + // stress the conglomerate directory.
> + // abort of an alter table will clear the cache.
> + // ---------------------------------------------------------
> + public void test_conglomDirectory() throws Exception
> + {
> + ResultSet rs = null;
> + Statement st = createStatement();
> +
> + String [] expColNames;
> + setAutoCommit(false);
> +
> + st.executeUpdate("create table a (a int)");
> + commit();
> + st.executeUpdate("alter table a add column c1 int");
> +
> + rollback();
> +
> + rs = st.executeQuery("select * from a");
> +
> + expColNames = new String [] {"A"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + st.executeUpdate("drop table a");
> + commit();
> + }
> +
> + // ---------------------------------------------------------
> + // ----- test case for partial row runtime statistics. -----
> + // ---------------------------------------------------------
> + /* This test checks correctness of simple runtime statistics.
> + It first exercises queries when there's no index present, then with
> + an index present; expecting to see a difference between Table Scan
> + and Index Scan. Also of interest is that the qualifiers look right;
> + whether it is using scan start/stop (this is a way to do qualifiers
> + using index).
> + Then it does the same 2 actions after some rows have been
> + deleted, to exercise the 'deleted rows visited' section in the
> + runtime statistics.
> + The queries which are cycled through are:
> + query1: all columns & rows: "select * from foo"
> + query2 - just last column: "select e from foo"
> + query3: as subset of columns: "select e, c, a from foo"
> + query4: as subset of columns, with qualifier in list:
> + "select e, c, a from foo where foo.e = 5"
> + query5: as subset of columns, with qualifier not in list:
> + "select e, c, a from foo where foo.b = 20"
> + query6: as subset of columns: "select a, b from foo"
> + */
> + public void testPartialRowRTStats() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate("set ISOLATION to RR");
> + setAutoCommit(false);
> +
> + st.executeUpdate(
> + "create table foo (a int, b int, c int, d int, e int)");
> + st.executeUpdate("insert into foo values (1, 2, 3, 4, 5)");
> + st.executeUpdate("insert into foo values (10, 20, 30, 40, 50)");
> +
> + // switch on runtime statistics
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + runQueriesNormal(st);
> +
> + // now check index scans - force the index just to make sure it
> + // does an index scan.
> + st.executeUpdate("create index foo_cover on foo (e, d, c, b, a)");
> + runQueriesWithIndex(st);
> + // drop the index...
> + st.executeUpdate("drop index foo_cover");
> + st.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','FOO',null)");
> +
> + // check deleted row feature
> + st.executeUpdate("insert into foo values (100, 2, 3, 4, 5)");
> + st.executeUpdate("insert into foo values (1000, 2, 3, 4, 5)");
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 100");
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 1000");
> + runQueriesWithDeletedRows(st);
> +
> + // now check index scans again
> + // recreate the index to make sure it does an index scan.
> + st.execute("create index foo_cover on foo (e, d, c, b, a)");
> + // of course, we'll have to update statistics now before it looks good
> + st.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','FOO',null)");
> + // and then we have to re-delete the rows because update statistics would've
> + // reset the info about deleted rows.
> + st.executeUpdate("insert into foo values (100, 2, 3, 4, 5)");
> + st.executeUpdate("insert into foo values (1000, 2, 3, 4, 5)");
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 100");
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 1000");
> + runQueriesWithIndexDeletedRows(st);
> +
> + st.executeUpdate("drop table foo");
> + }
> +
> + /* method used in testPartialRowRTStats and testCostingCoveredQuery */
> + private void assertStatsOK(Statement st, String expectedScan,
> + String expTableInIndexScan, String expIndexInIndexScan,
> + String expBits, String expNumCols, String expDelRowsV,
> + String expPages, String expRowsQ, String expRowsV,
> + String expScanType, String expStartPosition, String expStopPosition,
> + String expQualifier, String expQualifierInfo)
> + throws SQLException {
> +
> + ResultSet rs = null;
> + rs = st.executeQuery(
> + "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
> + rs.next();
> + if(usingEmbedded()){
> + RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
> + rs.close();
> + if (expectedScan.equals("Table"))
> + assertTrue(rtsp.usedTableScan());
> + else if (expectedScan.equals("Index"))
> + {
> + assertTrue(rtsp.usedIndexScan());
> + assertTrue(rtsp.usedSpecificIndexForIndexScan(
> + expTableInIndexScan, expIndexInIndexScan));
> + }
> + else if (expectedScan.equals("Constraint"))
> + {
> + assertTrue(rtsp.usedIndexScan());
> + assertTrue(rtsp.usedConstraintForIndexScan(
> + expTableInIndexScan));
> + }
> + assertTrue(rtsp.findString("Bit set of columns fetched="+expBits, 1));
> + assertTrue(rtsp.findString("Number of columns fetched="+expNumCols, 1));
> + if (expDelRowsV!=null)
> + assertTrue(rtsp.findString("Number of deleted rows visited="+expDelRowsV, 1));
> + assertTrue(rtsp.findString("Number of pages visited="+expPages, 1));
> + assertTrue(rtsp.findString("Number of rows qualified="+expRowsQ, 1));
> + assertTrue(rtsp.findString("Number of rows visited="+expRowsV, 1));
> + assertTrue(rtsp.findString("Scan type="+expScanType, 1));
> + assertTrue(rtsp.getStartPosition()[1].indexOf(expStartPosition)>1);
> + assertTrue(rtsp.getStopPosition()[1].indexOf(expStopPosition)>1);
> +
> + if (expQualifier.equals("None"))
> + assertTrue(rtsp.hasNoQualifiers());
> + else if (expQualifier.equals("Equals"))
> + assertTrue(rtsp.hasEqualsQualifier());
> + if (expQualifierInfo !=null)
> + assertTrue(rtsp.findString(expQualifierInfo, 1));
> + }
> + }
> +
> + private void runQueriesNormal(Statement st) throws SQLException {
> + doQuery1(st);
> + assertStatsOK(st,
> + "Table", null, null, "All", "5", null, "1", "2", "2",
> + "heap","null","null","None", null);
> +
> + doQuery2(st);
> + assertStatsOK(st,
> + "Table", null, null, "{4}", "1", null, "1", "2", "2",
> + "heap","null","null","None", null);
> +
> + doQuery3(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 2, 4}", "3", null, "1", "2", "2",
> + "heap","null","null","None", null);
> +
> + doQuery4(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 2, 4}", "3", null, "1", "1", "2",
> + "heap","null","null","Equals","Column[0][0] Id: 4");
> +
> + doQuery5(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 1, 2, 4}", "4", null, "1", "1", "2",
> + "heap","null","null","Equals","Column[0][0] Id: 1");
> +
> + doQuery6(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 1}", "2", null, "1", "2", "2",
> + "heap","null","null","None",null);
> + }
> +
> + private void runQueriesWithIndex(Statement st) throws SQLException {
> + doQuery1(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 1, 2, 3, 4}", "5", "0", "1", "2", "2", "btree",
> + "None","None","None", null);
> +
> + doQuery2(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0}", "1", "0", "1", "2", "2", "btree",
> + "None","None","None", null);
> +
> + doQuery3(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 2, 4}", "3", "0", "1", "2", "2", "btree",
> + "None","None","None", null);
> +
> + doQuery4(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 2, 4}", "3", "0", "1", "1", "2",
> + "btree",">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + doQuery5(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 2, 3, 4}", "4", "0", "1", "1", "2", "btree",
> + "None","None","Equals", "Column[0][0] Id: 3");
> +
> + doQuery6(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{3, 4}", "2", "0", "1", "2", "2", "btree",
> + "None","None","None", null);
> + }
> +
> + private void runQueriesWithDeletedRows(Statement st) throws SQLException {
> + doQuery1(st);
> + assertStatsOK(st,
> + "Table", null, null, "All", "5", null, "1", "2", "4", "heap",
> + "null","null","None", null);
> +
> + doQuery2(st);
> + assertStatsOK(st,
> + "Table", null, null, "{4}", "1", null, "1", "2", "4", "heap",
> + "null","null","None", null);
> +
> + doQuery3(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 2, 4}", "3", null, "1", "2", "4", "heap",
> + "null","null","None", null);
> +
> + doQuery4(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 2, 4}", "3", null, "1", "1", "4", "heap",
> + "null","null","Equals","Column[0][0] Id: 4");
> +
> + doQuery5(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 1, 2, 4}", "4", null, "1", "1", "4", "heap",
> + "null","null","Equals","Column[0][0] Id: 1");
> +
> + doQuery6(st);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 1}", "2", null, "1", "2", "4", "heap",
> + "null","null","None",null);
> + }
> +
> + private void runQueriesWithIndexDeletedRows(Statement st) throws SQLException {
> + doQuery1(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 1, 2, 3, 4}", "5", "2", "1", "2", "4", "btree",
> + "None","None","None", null);
> +
> + doQuery2(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0}", "1", "2", "1", "2", "4", "btree",
> + "None","None","None", null);
> +
> + doQuery3(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 2, 4}", "3", "2", "1", "2", "4", "btree",
> + "None","None","None", null);
> +
> + doQuery4(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 2, 4}", "3", "2", "1", "1", "4", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + doQuery5(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{0, 2, 3, 4}", "4", "2", "1", "1", "4", "btree",
> + "None","None","Equals", "Column[0][0] Id: 3");
> +
> + doQuery6(st);
> + assertStatsOK(st,
> + "Index", "FOO", "FOO_COVER",
> + "{3, 4}", "2", "2", "1", "2", "4", "btree",
> + "None","None","None", null);
> + }
> +
> + private void doQuery1(Statement st) throws SQLException {
> + // all columns and rows
> + doQuery(st, "select * from foo",
> + new String [] {"A", "B", "C", "D", "E"},
> + new String [][] {
> + {"1", "2", "3", "4", "5"},
> + {"10", "20", "30", "40", "50"}});
> + }
> +
> + private void doQuery2(Statement st) throws SQLException {
> + // just last column - should be 5 and 50
> + doQuery(st, "select e from foo",
> + new String[] {"E"},
> + new String[][] {{"5"},{"50"}});
> + }
> +
> + private void doQuery3(Statement st) throws SQLException {
> + // as subset of columns - should be 5,3,1 and 50,30,10
> + doQuery(st, "select e, c, a from foo",
> + new String [] {"E", "C", "A"},
> + new String [][] {
> + {"5", "3", "1"},
> + {"50", "30", "10"}});
> + }
> +
> + private void doQuery4(Statement st) throws SQLException {
> + // as subset of columns, with qualifier in list - should be 5,3,1
> + doQuery(st, "select e, c, a from foo where foo.e = 5",
> + new String [] {"E", "C", "A"},
> + new String [][]{{"5", "3", "1"}});
> + }
> +
> + private void doQuery5(Statement st) throws SQLException {
> + // as subset of columns, with qualifier not in list; should be 50,30,10
> + doQuery(st, "select e, c, a from foo where foo.b = 20",
> + new String [] {"E", "C", "A"},
> + new String [][] {{"50", "30", "10"}});
> + }
> +
> + private void doQuery6(Statement st) throws SQLException {
> + // as subset of columns
> + doQuery(st, "select a, b from foo",
> + new String [] {"A", "B"},
> + new String [][] {{"1", "2"},{"10", "20"}});
> + }
> +
> + private void doQuery(Statement st,
> + String query, String [] expColNames, String[][] expRS)
> + throws SQLException {
> + ResultSet rs = null;
> + rs = st.executeQuery(query);
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertFullResultSet(rs, expRS, true);
> + }
> +
> + // ----------------------------------------------------
> + // -- test case for costing -
> + // make sure optimizer picks obvious covered query.
> + // ----------------------------------------------------
> + public void testCostingCoveredQuery() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate("set ISOLATION to RR");
> + setAutoCommit(false);
> +
> + st.executeUpdate("create table base_table (a int, b varchar(1000))");
> + st.executeUpdate(
> + "insert into base_table values (1, PADSTRING('1',1000))");
> + st.executeUpdate(
> + "insert into base_table values (2, PADSTRING('2',1000))");
> + st.executeUpdate(
> + "insert into base_table values (3, PADSTRING('3',1000))");
> + st.executeUpdate(
> + "insert into base_table values (4, PADSTRING('4',1000))");
> + st.executeUpdate(
> + "insert into base_table values (5, PADSTRING('5',1000))");
> + st.executeUpdate(
> + "insert into base_table values (6, PADSTRING('6',1000))");
> + st.executeUpdate(
> + "insert into base_table values (7, PADSTRING('7',1000))");
> + st.executeUpdate(
> + "insert into base_table values (8, PADSTRING('8',1000))");
> + st.executeUpdate(
> + "insert into base_table values (9, PADSTRING('9',1000))");
> + st.executeUpdate(
> + "insert into base_table values (10, PADSTRING('10',1000))");
> + st.executeUpdate("create index cover_idx on base_table(a)");
> +
> + // switch on runtime statistics
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + // make sure covered index is chosen
> + doQuery(st, "select a from base_table",
> + new String [] {"A"},
> + new String [][] {{"1"},{"2"},{"3"},{"4"},{"5"},
> + {"6"},{"7"},{"8"},{"9"},{"10"}});
> + assertStatsOK(st,
> + "Index", "BASE_TABLE", "COVER_IDX",
> + "{0}", "1", "0", "1", "10", "10", "btree",
> + "None","None","None", null);
> + }
> +
> + // ----------------------------------------------------
> + // -- test for key too big error message. --
> + // ----------------------------------------------------
> + public void testKeyTooBigError() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate ("create table d (id int not null, " +
> + "t_bigvarchar varchar(400), unique (id))");
> + st.executeUpdate("create index t_bigvarchar_ind on d ( t_bigvarchar)");
> + st.executeUpdate(
> + "alter table d alter t_bigvarchar set data type varchar(4096)");
> +
> + String bigString="1111111";
> + for (int i=0 ; i<314 ; i++)
> + bigString=bigString+"1234567890";
> + bigString=bigString+"123456";
> + assertStatementError("XSCB6", st,
> + "insert into d (id, t_bigvarchar) values (1, '" + bigString + "')");
> + }
> +
> + // ---------------------------------------------------------
> + // test space for update
> + // ---------------------------------------------------------
> + public void testSpaceForUpdate() throws Exception
> + {
> + CallableStatement cSt;
> + Statement st = createStatement();
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.minimumRecordSize', '1')");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageReservedSpace', '0')");
> + cSt.execute();
> +
> + st.executeUpdate("create table testing (a varchar(100))");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.minimumRecordSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.minimumRecordSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageReservedSpace', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("insert into testing values ('a')");
> + for (int i=0 ; i<7 ; i++)
> + st.executeUpdate(
> + "insert into testing (select testing.a from testing)");
> +
> + assertUpdateCount(st, 128,
> + "update testing set a = 'abcd' where a = 'a'");
> +
> + st.executeUpdate("create index zz on testing (a)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.minimumRecordSize', '1')");
> + cSt.execute();
> +
> + st.executeUpdate("create table t1 (a varchar(100))");
> +
> + st.executeUpdate("insert into t1 values ('a')");
> + for (int i=0 ; i<7 ; i++)
> + st.executeUpdate("insert into t1 (select t1.a from t1)");
> +
> + assertUpdateCount(st, 128,
> + " update t1 set a = 'abcd' where a = 'a'");
> +
> + st.executeUpdate("create index zz1 on t1 (a)");
> + }
> +
> +
> + // ---------------------------------------------------------
> + // test load with long columns with index creation
> + // ---------------------------------------------------------
> + public void testLoadLongColumnsCreateIndex() throws Exception
> + {
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> + st.executeUpdate("set ISOLATION to RR");
> + setAutoCommit(false);
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + st.executeUpdate(
> + "create table long1 (a varchar(32000), b int, c int)");
> +
> + st.executeUpdate("insert into long1 values (" +
> + "'this is a long row which will get even longer and longer " +
> + "to force a stream', 1, 2)");
> + st.executeUpdate("insert into long1 values (" +
> + "'this is another long row which will get even longer " +
> + "and longer to force a stream', 2, 3)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + assertUpdateCount(st, 2, "update long1 set a = a||a||a||a||a||a");
> + assertUpdateCount(st, 2, "update long1 set a = a||a||a||a||a||a");
> + assertUpdateCount(st, 2, "update long1 set a = a||a");
> +
> + rs = st.executeQuery("select LENGTH(a) from long1");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"5328"},{"5760"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + st.executeUpdate(
> + "create table long2 (a varchar(16384), b int, c int)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '16384')");
> + cSt.execute();
> +
> + st.executeUpdate("create index long2i1 on long2 (a)");
> + st.executeUpdate("create index long2i2 on long2 (a,b)");
> + st.executeUpdate("create index long2i3 on long2 (a,b,c)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("insert into long2 select * from long1");
> +
> + rs = st.executeQuery("select LENGTH(a) from long2");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"5328"},{"5760"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + rs = st.executeQuery("select LENGTH(a) from long2 " +
> + "/*derby_properties index=long2i2*/");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"5328"},{"5760"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // DefectId CS 1346
> +
> + st.executeUpdate("insert into long2 select * from long1");
> +
> + rs = st.executeQuery("select LENGTH(a) from long2");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"5328"},{"5328"},{"5760"},{"5760"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + rs = st.executeQuery("select LENGTH(a) from long2 " +
> + "/*derby_properties index=long2i2*/");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"5328"},{"5328"},{"5760"},{"5760"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + assertUpdateCount(st, 4, "delete from long2");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + st.executeUpdate("create index long2small on long2 (a, c)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + // this small index should cause the insert to fail
> +
> + assertStatementError("XSCB6", st,
> + "insert into long2 select * from long1");
> +
> + // DefectId CS 1346 the small index should cause this insert
> + // to also fail
> +
> + assertStatementError("XSCB6", st,
> + "insert into long2 select * from long1");
> +
> + rs = st.executeQuery("select LENGTH(a) from long2");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + // test case for track 1346
> +
> + st.executeUpdate("drop table long1");
> + st.executeUpdate("drop table long2");
> + }
> +
> + public void testCS1346() throws Exception
> + {
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> + st.executeUpdate("set ISOLATION to RR");
> + setAutoCommit(false);
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> + st.executeUpdate(
> + "create table long1 (a varchar(32000), b int, c int)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("insert into long1 values ('this is a long row " +
> + "which will get even longer', 1, 2)");
> + st.executeUpdate("insert into long1 values ('a second row that will " +
> + "also grow very long', 2, 3)");
> +
> + assertUpdateCount(st, 2, "update long1 set a = a||a||a||a||a||a");
> + assertUpdateCount(st, 2, "update long1 set a = a||a||a||a||a||a");
> + assertUpdateCount(st, 2, "update long1 set a = a||a");
> +
> + rs = st.executeQuery("select LENGTH(a) as x from long1 order by x");
> +
> + expColNames = new String [] {"X"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"3024"},{"3240"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + st.executeUpdate(
> + "create table long2 (a varchar(30000), b int, c int)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '16384')");
> + cSt.execute();
> +
> + st.executeUpdate("create index long2i1 on long2 (a)");
> + st.executeUpdate("create index long2i2 on long2 (b, a)");
> + st.executeUpdate("create index long2i3 on long2 (b, a, c)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("insert into long2 select * from long1");
> + st.executeUpdate("insert into long2 select * from long1");
> +
> + rs = st.executeQuery("select LENGTH(a) as x from long2 order by x");
> +
> + expColNames = new String [] {"X"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"3024"},{"3024"},{"3240"},{"3240"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + st.executeUpdate("drop table long1");
> + st.executeUpdate("drop table long2");
> +
> + }
> +
> + public void testCS1346b() throws Exception
> + {
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + st.executeUpdate(
> + "create table long1 (a varchar(32000), b int, c int)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("insert into long1 values ('this is a long row " +
> + "which will get even longer', 1, 2)");
> + st.executeUpdate("insert into long1 values ('a second row that will "
> + + "also grow very long', 2, 3)");
> +
> + assertUpdateCount(st, 2, "update long1 set a = a||a||a||a||a||a");
> + assertUpdateCount(st, 2, "update long1 set a = a||a||a||a||a||a");
> + assertUpdateCount(st, 2, "update long1 set a = a||a");
> +
> + rs = st.executeQuery("select LENGTH(a) as x from long1 order by x");
> +
> + expColNames = new String [] {"X"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"3024"},{"3240"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '1024')");
> + cSt.execute();
> +
> + st.executeUpdate(
> + "create table long2 (a varchar(32000), b int, c int)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '16384')");
> + cSt.execute();
> +
> + st.executeUpdate("create index long2i1 on long2 (a)");
> + st.executeUpdate("create index long2i2 on long2 (b, a)");
> + st.executeUpdate("create index long2i3 on long2 (b, a, c)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + // insert into the second table multiple times
> + for (int i=0 ; i<10 ; i++)
> + st.executeUpdate("insert into long2 select * from long1");
> +
> + rs = st.executeQuery("select LENGTH(a) as x from long2 order by x");
> +
> + expColNames = new String [] {"X"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [20][1];
> + //10 rows should have '3024' and 10 '3240 as length
> + for (int i=0 ; i<10 ; i++)
> + expRS[i][0]="3024";
> + for (int i=10 ; i<20 ; i++)
> + expRS[i][0]="3240";
> + JDBC.assertFullResultSet(rs, expRS, true);
> + rs = st.executeQuery("select count(*) from long2");
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"20"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> + }
> +
> + // regression test case for a Cloudscape era bug, 1552
> + // Make sure that a full scan which needs columns not in index
> + // does not use the index.
> + // Before the fix, access costing would make the optimizer
> + // pick the index because it incorrectly costed rows spanning pages.
> + public void testCS1552() throws Exception
> + {
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize','4096')");
> + cSt.execute();
> +
> + st.executeUpdate("create table a " +
> + "(a int, b varchar(4000), c varchar(4000), d varchar(4000))");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("create index a_idx on a (a)");
> +
> + st.executeUpdate("insert into a values (5, PADSTRING('a',4000), " +
> + "PADSTRING('a',4000), PADSTRING('a',4000))");
> + st.executeUpdate("insert into a values (4, PADSTRING('a',4000), " +
> + "PADSTRING('a',4000), PADSTRING('a',4000))");
> + st.executeUpdate("insert into a values (3, PADSTRING('a',4000), " +
> + "PADSTRING('a',4000), PADSTRING('a',4000))");
> + st.executeUpdate("insert into a values (2, PADSTRING('a',4000), " +
> + "PADSTRING('a',4000), PADSTRING('a',4000))");
> + st.executeUpdate("insert into a values (1, PADSTRING('a',4000), " +
> + "PADSTRING('a',4000), PADSTRING('a',4000))");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> + cSt.execute();
> +
> + rs = st.executeQuery("select a, d from a");
> +
> + expColNames = new String [] {"A", "D"};
> + JDBC.assertColumnNames(rs, expColNames);
> + String paddeda = Formatters.padString("a", 4000);
> + expRS = new String[][] {
> + {"5", paddeda},
> + {"4", paddeda},
> + {"3", paddeda},
> + {"2", paddeda},
> + {"1", paddeda}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> + assertStatsOK(st,
> + "Table", null, null, "{0, 3}", "2", null, "6", "5", "5",
> + "heap","null","null","None", null);
> +
> + st.execute("drop table a");
> + commit();
> + }
> +
> + // test case for track 2241"};
> + // The problem was that when the level of btree grew,
> + // sometimes a long row would be chosen as the branch
> + // delimiter, and the branch code did not throw the
> + // correct error noSpaceForKey error.
> + public void testCS2241() throws Exception
> + {
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.minimumRecordSize', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("create table b2241 (a int, b varchar(32000))");
> + st.executeUpdate("insert into b2241 values (1024, " +
> + "'01234567890123456789012345678901234567890123456789')");
> + String inshalf1 = "insert into b2241 (select b2241.a + ";
> + String inshalf2 = ", b from b2241)";
> + st.executeUpdate(inshalf1 + "1" + inshalf2);
> + st.executeUpdate(inshalf1 + "2" + inshalf2);
> + st.executeUpdate(inshalf1 + "4" + inshalf2);
> + st.executeUpdate(inshalf1 + "8" + inshalf2);
> + st.executeUpdate(inshalf1 + "16" + inshalf2);
> + st.executeUpdate(inshalf1 + "32" + inshalf2);
> + st.executeUpdate(inshalf1 + "64" + inshalf2);
> + for (int i=0 ; i<5 ; i++)
> + assertUpdateCount(st, 128, "update b2241 set b = b||b");
> + rs = st.executeQuery("select LENGTH(b) from b2241 where a = 1025");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"1600"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> + st.executeUpdate(
> + " insert into b2241 (select 1, "
> + + "b||b||b||b||b||b||b||b from b2241 where a = 1024)");
> + st.executeUpdate(
> + " insert into b2241 (select 8000, "
> + + "b||b||b||b||b||b||b||b from b2241 where a = 1024)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '4096')");
> + cSt.execute();
> +
> + // this create index use to fail with an assert - should
> + // fail with key too big error.
> + assertStatementError("XSCB6", st, "create index a on b2241 (b, a)");
> + // make sure table still accessable, by doing the same statement
> + assertStatementError("XSCB6", st, "create index a on b2241 (b, a)");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> +
> + // delete 2 big records and then index should work.
> + assertUpdateCount(st, 1, "delete from b2241 where a = 1");
> + assertUpdateCount(st, 1, "delete from b2241 where a = 8000");
> +
> + st.executeUpdate("create index a on b2241 (b, a)");
> +
> + // Check the consistency of the indexes
> + rs = st.executeQuery("VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'B2241')");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"1"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + st.executeUpdate("drop table b2241");
> + }
> +
> + // o insert bunch of rows with sequential keys.
> + // o create an index (non unique or unique)
> + // o delete every other one - will make normat post commit not fire.
> + // o commit
> + // o now reinsert rows into the "holes" which before the fix
> + // would cause splits, but now will force reclaim space and
> + // reuse existing space in btree.
> + private void reclaimTest(String createIndex, String expectedError)
> + throws SQLException {
> + CallableStatement cSt;
> + setAutoCommit(false);
> + Statement st = createStatement();
> +
> + // set page size to default.
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '4096')");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.minimumRecordSize', '1')");
> + cSt.execute();
> + cSt.close();
> + commit();
> +
> + // create and load a table with values from 1024 down to 1,
> + st.executeUpdate("create table foo (a int, b char(200), c int)");
> + st.executeUpdate("insert into foo values (1024, 'even', 0)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 1, 'odd' , 1 from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 2, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 4, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 8, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 16, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 32, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 64, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 128, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 256, foo.b, foo.c from foo)");
> + st.executeUpdate("insert into foo " +
> + "(select foo.a - 512, foo.b, foo.c from foo)");
> +
> + // insert into the "holes", but different keys (even2 instead of even)
> + st.executeUpdate("create table foo2 (a int, b char(200), c int)");
> + st.executeUpdate("insert into foo2 (select * from foo)");
> +
> + assertUpdateCount(st, 512, "delete from foo2 where foo2.c = 1");
> +
> + // create "packed" index.
> + st.executeUpdate(createIndex);
> +
> + // delete ever other row
> + assertUpdateCount(st, 512, "delete from foo where foo.c = 0");
> +
> + // turn all the deletes into "committed deletes"
> + commit();
> + st.executeUpdate("insert into foo " +
> + "(select foo2.a, 'even2', foo2.c from foo2)");
> + commit();
> +
> + // insert dups
> + if (expectedError != null)
> + assertStatementError("23505", st, "insert into foo " +
> + "(select foo2.a, 'even2', foo2.c from foo2)");
> + else
> + assertUpdateCount(st, 512, "insert into foo " +
> + "(select foo2.a, 'even2', foo2.c from foo2)");
> + commit();
> +
> + // a delete of the whole table also exercises the btree well.
> + if (expectedError != null)
> + {
> + assertUpdateCount(st, 1024, "delete from foo");
> + assertUpdateCount(st, 512, "delete from foo2");
> + }
> + else
> + {
> + assertUpdateCount(st, 1536, "delete from foo");
> + assertUpdateCount(st, 512, "delete from foo2");
> + }
> + commit();
> +
> + st.executeUpdate("drop table foo");
> + st.executeUpdate("drop table foo2");
> + commit();
> + }
> +
> + // test case for reclaiming deleted rows during split.
> + // actual work is done in method reclaimTest()
> + // exercise test case with non-unique index
> + public void testReclaimDeletedRowsDuringSplit() throws Exception
> + {
> + reclaimTest("create index a on foo (a, b)", null);
> + }
> +
> + // as testReclaimDeletedRowsDuringSplit, but with unique index,
> + // so when attempting to create the duplicat rows, we should get an
> + // error.
> + // actual work is done in method reclaimTest()
> + public void testReclaimDeletedRowsUniqueIndex() throws Exception
> + {
> + reclaimTest("create unique index a on foo (a, b)", "23505");
> + }
> +
> + // same foo used in the next 3 test fixtures
> + private void setupForReclaim2(Statement st) throws SQLException {
> + st.executeUpdate("create table foo (a int, b varchar(1100), c int)");
> + st.executeUpdate("create index a on foo (a, b)");
> + st.executeUpdate("insert into foo values (1, PADSTRING('a',1100), 1)");
> + st.executeUpdate("insert into foo values (2, PADSTRING('a',1100), 1)");
> + st.executeUpdate("insert into foo values (3, PADSTRING('a',1100), 1)");
> + }
> +
> + private void reclaimDeletedRows2(boolean toCommit) throws SQLException {
> + Statement st = createStatement();
> +
> + setupForReclaim2(st);
> + commit();
> +
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 1");
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 2");
> +
> + if (toCommit)
> + commit();
> + st.executeUpdate("insert into foo values " +
> + "(-1, PADSTRING('ab',1100), 1)");
> + st.executeUpdate("insert into foo values " +
> + "(-2, PADSTRING('ab',1100), 1)");
> + rollback();
> +
> + st.executeUpdate("drop table foo");
> + commit();
> + }
> +
> + // another simple test of reclaim deleted row code paths.
> + // this test should not reclaim rows as deletes are not committed.
> + public void testUncommittedDeletesNotReclaimed() throws Exception
> + {
> + reclaimDeletedRows2(false);
> + }
> +
> + // another simple test of reclaim deleted row code paths.
> + // this test should reclaim rows as deletes are committed.
> + public void testCommittedDeletesReclaim() throws Exception
> + {
> + reclaimDeletedRows2(true);
> + }
> +
> + // this test will not reclaim rows because the parent xact
> + // has table level lock.
> + public void testAllUncommittedReclaim() throws Exception
> + {
> + Statement st = createStatement();
> +
> + setupForReclaim2(st);
> + assertUpdateCount(st, 1, "delete from foo where foo.a = 1");
> +
> + st.executeUpdate("insert into foo values (0, PADSTRING('a',1100), 1)");
> + st.executeUpdate("insert into foo values (1, PADSTRING('a',1100), 1)");
> + rollback();
> +
> + st.executeUpdate("drop table foo");
> + }
> +
> + // regression test case for Cloudscape fixed bug track 2778
> + // Make sure that an update which causes a row to go from a non long row
> + // to a long row can be aborted correctly.
> + // Prior to this fix the columns moving off the page would be corrupted.
> + // create a base table that contains 2 rows, 19 columns,
> + // that leaves just 1 byte free on the page.
> + // freeSpace: 1, spareSpace: 10, PageSize: 2048
> + public void testCS2778() throws Exception
> + {
> + ResultSet rs = null;
> + CallableStatement cSt;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', '2048')");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageReservedSpace', '10')");
> + cSt.execute();
> +
> + st.executeUpdate("create table t2778 (" +
> + "col00 char(2), col01 char(1), col02 char(99), col03 char(11), " +
> + "col04 char(7), col05 char(11), col06 char(6), col07 char(6), " +
> + "col08 char(2), col09 char(6), col10 varchar(1000), " +
> + "col11 char(2), col12 char(1), col13 char(7), col14 char(24), " +
> + "col15 char(1), col16 char(166), col17 char(207), col18 char(2))");
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageSize', NULL)");
> + cSt.execute();
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageReservedSpace', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("create unique index a_idx on t2778 (col00)");
> + commit();
> +
> + st.executeUpdate("insert into t2778 values ( '0_', '0', '0_col02', " +
> + "'0_col03', '0_col04', '0_col05', '0_06', '0_07', '0_', '0_09', " +
> + "'0_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllll012340_col10lllllllllll', '0_', '0', '0_col13', " +
> + "'0_col14', '0', '0_col16', '0_col17', '0_' )");
> +
> + st.executeUpdate("insert into t2778 values ( '1_', '1', '1_col02', " +
> + "'1_col03', '1_col04', '1_col05', '1_06', '1_07', '1_', '1_09', " +
> + "'1_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllll012340_col10llllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxx" +
> + "xxxxxxxxxxxxxxxx', '1_', '1', '1_col13', '1_col14', '1', " +
> + "'1_col16', '1_col17', '1_' )");
> + commit();
> +
> + rs = st.executeQuery(" select col16, col17, col18 from t2778");
> + expColNames = new String [] {"COL16", "COL17", "COL18"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{
> + {"0_col16", "0_col17", "0_"},
> + {"1_col16", "1_col17", "1_"}
> + };
> + JDBC.assertFullResultSet(rs, expRS, true);
> + commit();
> +
> + assertUpdateCount(st, 1, "update t2778 " +
> + "/*derby-properties index=a_idx*/ set col10 = " +
> + "'0_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllll012340_col10lllllllllllxxxxxx' " +
> + "where col00 = '0_'");
> + rollback();
> +
> + // prior to the fix col17 and col18 would come back null.
> + rs = st.executeQuery("select " +
> + "col01, col02, col03, col04, col05, col06, " +
> + "col07, col08, col09, col10, col11, col12, col13, " +
> + "col14, col15, col16, col17, col18 from t2778");
> +
> + expColNames = new String [] {"COL01", "COL02", "COL03", "COL04",
> + "COL05", "COL06", "COL07", "COL08", "COL09", "COL10", "COL11",
> + "COL12", "COL13", "COL14", "COL15", "COL16", "COL17", "COL18"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"0", "0_col02", "0_col03", "0_col04",
> + "0_col05", "0_06", "0_07", "0_", "0_09",
> + "0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "llllllllll012340_col10lllllllllllxxxxxx",
> + "0_", "0", "0_col13", "0_col14", "0", "0_col16", "0_col17", "0_"},
> + {"1", "1_col02", "1_col03", "1_col04",
> + "1_col05", "1_06", "1_07", "1_", "1_09",
> + "1_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "llllllllll012340_col10lllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll" +
> + "llllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxx" +
> + "xxxxxxxxxxxxxxx", "1_", "1", "1_col13", "1_col14", "1",
> + "1_col16", "1_col17", "1_"}
> + };
> + JDBC.assertFullResultSet(rs, expRS, true);
> + commit();
> +
> + st.executeUpdate("drop table t2778");
> + commit();
> + }
> +
> + // test case for Cloudscape track 3149, improving max on btree optimization
> + public void testCS3149() throws Exception
> + {
> + ResultSet rs = null;
> + Statement st = createStatement();
> + String [][] expRS;
> + String [] expColNames;
> +
> + setAutoCommit(false);
> + st.executeUpdate("create table foo (a int, b varchar(500), c int)");
> +
> + String insertPart1 = "insert into foo values (";
> + String insertPart2 = ", PADSTRING('";
> + String insertPart3 = "',500), 1)";
> + for (int i=1 ; i<10 ; i++)
> + {
> + String s = String.valueOf(i);
> + st.executeUpdate(insertPart1 + s + insertPart2 + s + insertPart3);
> + }
> + for (int i=11 ; i<19 ; i++)
> + {
> + String s = String.valueOf(i);
> + st.executeUpdate(insertPart1 + s + insertPart2 + s + insertPart3);
> + }
> + st.executeUpdate("create index foox on foo (b)");
> + commit();
> +
> + // normal max optimization, last row in index is not deleted.
> + rs = st.executeQuery("select max(b) from foo");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"9"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // new max optimization, last row in index is deleted but
> + // others on page aren't.
> + assertUpdateCount(st, 1, "delete from foo where a = 9");
> +
> + rs = st.executeQuery("select max(b) from foo");
> +
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"8"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // new max optimization, last row in index is deleted but
> + // others on page aren't.
> + assertUpdateCount(st, 1, "delete from foo where a = 8");
> +
> + rs = st.executeQuery("select max(b) from foo");
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"7"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // max optimization does not work - fail over to scan, all
> + // rows on last page are deleted.
> + assertUpdateCount(st, 13, "delete from foo where a > 2");
> +
> + rs = st.executeQuery("select max(b) from foo");
> + expColNames = new String [] {"1"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"2"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> + commit();
> +
> + st.executeUpdate("drop table foo");
> + commit();
> + }
> +
> + //---------------------------------------------------------
> + // regression test for Cloudscape bugs 3368, 3370
> + // the bugs arose for the edge case where pageReservedSpace = 100
> + // before bug 3368 was fixed, a short row insert caused 2 pages
> + // to be allocated per short row insert.
> + public void testCS3368_3370() throws Exception
> + {
> + CallableStatement cSt;
> + Statement st = createStatement();
> +
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageReservedSpace', '100')");
> + cSt.execute();
> + st.executeUpdate("create table a (a int)");
> + cSt = prepareCall("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
> + "'derby.storage.pageReservedSpace', NULL)");
> + cSt.execute();
> +
> + st.executeUpdate("insert into a values (1)");
> +
> + checkSpaceTable(st, "2");
> +
> + st.executeUpdate("insert into a values (2)");
> +
> + checkSpaceTable(st, "3");
> +
> + st.executeUpdate("insert into a values (1)");
> +
> + checkSpaceTable(st, "4");
> +
> + st.executeUpdate("insert into a values (2)");
> +
> + checkSpaceTable(st, "5");
> + }
> +
> + private void checkSpaceTable(Statement st, String expValue)
> + throws SQLException {
> + ResultSet rs = null;
> + String [][] expRS;
> +
> + rs = st.executeQuery("select numallocatedpages from TABLE" +
> + "(SYSCS_DIAG.SPACE_TABLE('APP', 'A')) a");
> + expRS = new String [][]{{expValue}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> + }
> +
> + //---------------------------------------------------------
> + // regression test for old Cloudscape bug track 4595,
> + // following are 2 test cases that get cycled with 3 different indexes:
> + // 1. unique index
> + // 2. primary key
> + // 3. non unique index
> + // The 2 test cases are:
> + // a. do delete, update and select without any rows (and check statistics)
> + // then insert a row, and do update and delete
> + // b. do delete, and update after inserting a row (and check stats)
> + // then do the same selects as in test case a
> + private void doTestCaseCS4595A (Statement st, String indexOrConstraint)
> + throws SQLException {
> + ResultSet rs = null;
> + String [] expColNames;
> +
> + String indexName;
> + if (indexOrConstraint.equals("Index"))
> + indexName="FOOX";
> + else
> + indexName=null;
> +
> + st.executeUpdate("set ISOLATION to RR");
> +
> + // delete against table with 0 rows.
> + assertUpdateCount(st, 0, "delete from foo where a = 1");
> +
> + // make sure index used in unique key update even if table has zero rows.
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0, 1}", "2", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // update against table with 0 rows.
> +
> + assertUpdateCount(st, 0, "update foo set b = 1 where a = 2");
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "All", "2", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // select * against table with 0 rows.
> + rs = st.executeQuery("select * from foo where a = 2");
> +
> + JDBC.assertEmpty(rs);
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "All", "2", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // select against table with 0 rows
> + rs = st.executeQuery("select a from foo where a = 2");
> +
> + expColNames = new String [] {"A"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0}", "1", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // select against table with 0 rows.
> + // second time should give slightly different statistics; different
> + // set of rows fetched.
> + rs = st.executeQuery("select a from foo where a = 2");
> +
> + expColNames = new String [] {"A"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0}", "1", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // now insert one row and make sure still same plan.
> + // Previous to 4595, 0 row plan was a table scan and it would not
> + // change when 1 row was inserted.
> + st.execute("insert into foo values (1, 1)");
> +
> + // update against table with 1 row.
> + assertUpdateCount(st, 1, "update foo set b = 2 where a = 1");
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "All", "2", "0", "1", "1", "1", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // delete against table with 1 row.
> + st.execute("delete from foo where a = 1");
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0, 1}", "2", "0", "1", "1", "1", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + st.execute("drop table foo");
> + commit();
> + }
> +
> + private void doTestCaseCS4595B(Statement st, String indexOrConstraint) throws SQLException {
> + ResultSet rs = null;
> + String [] expColNames;
> +
> + String indexName;
> + if (indexOrConstraint.equals("Index"))
> + indexName="FOOX";
> + else
> + indexName=null;
> +
> + commit();
> +
> + // update against table with 1 row.
> + assertUpdateCount(st, 1, "update foo set b = 2 where a = 1");
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "All", "2", "0", "1", "1", "1", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // delete against table with 1 row.
> + st.execute("delete from foo where a = 1");
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0, 1}", "2", "0", "1", "1", "1", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + rs = st.executeQuery("select * from foo where a = 2");
> +
> + JDBC.assertEmpty(rs);
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "All", "2", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // select against table with 0 rows
> + rs = st.executeQuery("select a from foo where a = 2");
> +
> + expColNames = new String [] {"A"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0}", "1", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + // select against table with 0 rows.
> + // second time should give slightly different statistics; different
> + // set of rows fetched.
> + rs = st.executeQuery("select a from foo where a = 2");
> +
> + expColNames = new String [] {"A"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + assertStatsOK(st,
> + indexOrConstraint, "FOO", indexName,
> + "{0}", "1", "0", "1", "0", "0", "btree",
> + ">= on first 1 column(s).","> on first 1 column(s).","None", null);
> +
> + st.execute("drop table foo");
> + }
> +
> + public void testCS4595A_UniqueIndex() throws Exception
> + {
> + Statement st = createStatement();
> +
> + st.executeUpdate("set ISOLATION to RR");
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + st.executeUpdate("create table foo (a int, b int)");
> + st.executeUpdate("create unique index foox on foo (a)");
> +
> + doTestCaseCS4595A(st, "Index");
> + }
> +
> + // try delete/update statement compiled against table with 1 row.
> + public void testCS4595B_UniqueIndex() throws Exception
> + {
> + Statement st = createStatement();
> +
> + st.executeUpdate("set ISOLATION to RR");
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + st.executeUpdate("create table foo (a int, b int)");
> + // this time, insert a row before creating an index
> + st.executeUpdate("insert into foo values (1, 1)");
> + st.executeUpdate("create unique index foox on foo (a)");
> +
> + doTestCaseCS4595B(st, "Index");
> + }
> +
> + // repeat set of testCS459_a against table with primary key,
> + // vs. unique index
> + // there should be no difference in plan shape.
> + // try delete/update statement compiled against table with 0 rows
> + public void testCS4595A_PrimaryKey() throws Exception
> + {
> + Statement st = createStatement();
> +
> + st.executeUpdate("set ISOLATION to RR");
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + st.executeUpdate(
> + "create table foo (a int not null primary key, b int)");
> +
> + doTestCaseCS4595A(st, "Constraint");
> + }
> +
> + // try delete/update statement compiled against table with 1 row.
> + // With primary key.
> + public void testCS4595B_PrimaryKey() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate("set ISOLATION to RR");
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + st.executeUpdate(
> + "create table foo (a int not null primary key, b int)");
> + st.executeUpdate("insert into foo values (1, 1)");
> +
> + doTestCaseCS4595B(st, "Constraint");
> + }
> +
> + // repeat set of 4595 tests against table with non-unique index
> + // with no statistics.
> + // there should be no difference in plan shape.
> + // try delete/update statement compiled against table with 0 rows
> + public void testCaseCS4595A_NonUniqueIndex() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate("set ISOLATION to RR");
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + st.executeUpdate("create table foo (a int, b int)");
> + st.executeUpdate("create index foox on foo (a)");
> +
> + doTestCaseCS4595A(st, "Index");
> + }
> +
> + // try delete/update statement compiled against table with 1 row.
> + public void testCaseCS4595B_NonUniqueIndex() throws Exception
> + {
> + Statement st = createStatement();
> + st.executeUpdate("set ISOLATION to RR");
> + st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> +
> + st.executeUpdate("create table foo (a int, b int)");
> + st.executeUpdate("create index foox on foo (a)");
> + st.executeUpdate("insert into foo values (1, 1)");
> +
> + doTestCaseCS4595B(st, "Index");
> + }
> +
> + // ----------------------------------------------------
> + // simple regression test for qualifier work.
> + // ----------------------------------------------------
> + public void testQualifiers() throws Exception
> + {
> + ResultSet rs = null;
> + Statement st = createStatement();
> +
> + String [][] expRS;
> + String [] expColNames;
> +
> + st.executeUpdate("create table foo (a int, b int, c int)");
> + st.executeUpdate("insert into foo values (1, 10, 100)");
> + st.executeUpdate("insert into foo values (2, 20, 200)");
> + st.executeUpdate("insert into foo values (3, 30, 300)");
> +
> + // should return no rows
> + rs = st.executeQuery("select a, b, c from foo where a = 1 and b = 20");
> +
> + expColNames = new String [] {"A", "B", "C"};
> + JDBC.assertColumnNames(rs, expColNames);
> + JDBC.assertDrainResults(rs, 0);
> +
> + // should return one row
> + rs = st.executeQuery("select a, b, c from foo where a = 3 and b = 30");
> +
> + expColNames = new String [] {"A", "B", "C"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"3", "30", "300"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + rs = st.executeQuery("select a, b, c from foo where a = 3 or c = 40");
> +
> + expColNames = new String [] {"A", "B", "C"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"3", "30", "300"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + // should return 2 rows
> + rs = st.executeQuery("select a, b, c from foo where a = 1 or b = 20");
> +
> + expColNames = new String [] {"A", "B", "C"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"1", "10", "100"}, {"2", "20", "200"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + rs = st.executeQuery("select a, b, c from foo where a = 1 or a = 3");
> +
> + expColNames = new String [] {"A", "B", "C"};
> + JDBC.assertColumnNames(rs, expColNames);
> + expRS = new String [][]{{"1", "10", "100"}, {"3", "30", "300"}};
> + JDBC.assertFullResultSet(rs, expRS, true);
> +
> + st.executeUpdate("DROP FUNCTION PADSTRING");
> +
> + rollback();
> + st.close();
> + }
> +}
> \ No newline at end of file
>
> Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AccessTest.java
> ------------------------------------------------------------------------------
> svn:eol-style = native
>
> Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java
> URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java?rev=897938&r1=897937&r2=897938&view=diff
> ==============================================================================
> --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java (original)
> +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/_Suite.java Mon Jan 11 16:54:01 2010
> @@ -65,6 +65,7 @@
> suite.addTest(ClobReclamationTest.suite());
> suite.addTest(IndexSplitDeadlockTest.suite());
> suite.addTest(HoldCursorJDBC30Test.suite());
> + suite.addTest(AccessTest.suite());
>
> /* Tests that only run in sane builds */
> if (SanityManager.DEBUG) {
>
> Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
> URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java?rev=897938&r1=897937&r2=897938&view=diff
> ==============================================================================
> --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java (original)
> +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java Mon Jan 11 16:54:01 2010
> @@ -35,6 +35,8 @@
> private String statistics = "";
> private boolean scrollInsensitive = false;
> private final HashSet qualifiers;
> + private String [] startPosition = {"None"};
> + private String [] stopPosition = {"None"};
>
> /**
> * Create a RuntimeStatistics object to parse the text and extract
> @@ -75,6 +77,9 @@
> scrollInsensitive = true;
>
> qualifiers = findQualifiers();
> +
> + startPosition = getStartPosition();
> + stopPosition = getStopPosition();
> }
>
>
> @@ -206,9 +211,17 @@
> tableName + " ")!= -1);
> }
>
> + /**
> + * @param tableName
> + * @param indexName
> + * @return true if passed indexName was used for Index Scan ResultSet
> + * for the passed tableName
> + */
> + public boolean usedConstraintForIndexScan(String tableName){
> + return (statistics.indexOf("Index Scan ResultSet for " +
> + tableName + " using constraint")!= -1);
> + }
>
> -
> -
> /**
> * Return whether or not an index scan result set was used in the query.
> */
> @@ -281,8 +294,36 @@
> public boolean hasLessThanQualifier() {
> return qualifiers.contains(new Qualifier("<", false));
> }
> -
>
> + /**
> + * Return whether or not the query used an equals scan qualifier.
> + */
> + public boolean hasEqualsQualifier() {
> + return qualifiers.contains(new Qualifier("=", false));
> + }
> +
> + /**
> + * Return whether there are no qualifiers (i.e. qualifiers: None)
> + */
> + public boolean hasNoQualifiers() {
> + int startPos = statistics.indexOf("qualifiers:\n");
> + if (startPos >= 0) {
> + // start search after "qualifiers:\n"
> + String searchString = statistics.substring(startPos + 12);
> + if (searchString.indexOf("None")>1)
> + return true;
> + else
> + {
> + System.out.println("statistics.substring: " + searchString);
> + return false;
> + }
> + }
> + else {
> + throw new AssertionError(
> + "Expected to find \"qualifiers: None\", " +
> + "but didn't even find 'qualifiers'");
> + }
> + }
>
> /**
> * Return whether or not the query plan includes a line of the form
> @@ -365,5 +406,41 @@
> public String toString() {
> return statistics;
> }
> +
> + /**
> + * Find the start position ; sometimes using a scan start / stop is
> + * a way of doing qualifiers using an index
> + * @ return the String array following start position:
> + */
> + public String [] getStartPosition() {
> + int startStartIndex = statistics.indexOf("start position:");
> + int endStartIndex = statistics.indexOf("stop position:");
> + if (startStartIndex >= 0 && endStartIndex >= 0)
> + {
> + String positionLines = statistics.substring(startStartIndex, endStartIndex);
> + String [] startPositionLines = positionLines.split("\n");
> + return startPositionLines;}
> + else
> + return null;
> +
> + }
> +
> + /**
> + * Find the stop position ; sometimes using a scan start / stop is
> + * a way of doing qualifiers using an index
> + * @ return the String array following start position:
> + */
> + public String [] getStopPosition() {
> + int startStopIndex = statistics.indexOf("stop position:");
> + int endStopIndex = statistics.indexOf("qualifiers:");
> + if (startStopIndex >= 0 && endStopIndex >= 0)
> + {
> + String positionLines = statistics.substring(startStopIndex, endStopIndex);
> + String [] startPositionLines = positionLines.split("\n");
> + return startPositionLines;}
> + else
> + return null;
> + }
> +
> }
>
>
>
>