Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 14224 invoked from network); 11 Jan 2010 16:54:27 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 11 Jan 2010 16:54:27 -0000 Received: (qmail 61688 invoked by uid 500); 11 Jan 2010 16:54:27 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 61635 invoked by uid 500); 11 Jan 2010 16:54:26 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 61626 invoked by uid 99); 11 Jan 2010 16:54:26 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 11 Jan 2010 16:54:26 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 11 Jan 2010 16:54:23 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 345B123889F7; Mon, 11 Jan 2010 16:54:03 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: 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/functionTests/mast... Date: Mon, 11 Jan 2010 16:54:02 -0000 To: derby-commits@db.apache.org From: myrnavl@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20100111165403.345B123889F7@eris.apache.org> 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; + } + }