/*
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 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);
st.executeUpdate("drop table long1");
st.executeUpdate("drop table long2");
}
// 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");
st.executeUpdate("drop table a");
}
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
{
setAutoCommit(false);
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);
rollback();
st.close();
}
}