ij> -- ----- 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. ----- ----- ----- TEST CASES SPECIFIC TO STORE IMPLEMENTATION OF HOLD CURSOR (external sort): ----- overview: ----- TEST 0: test hold cursor with external sort (order by). ----- TEST 1: basic heap scan tests (multiple rows) ----- TEST 2: basic btree scan tests (zero rows/update nonkey field) ----- TEST 3: basic btree scan tests (multiple rows/update nonkey field) ----- TEST 4: basic btree scan tests (zero rows/read only/no group fetch) ----- TEST 5: basic btree scan tests (multiple rows/read only/no group fetch) ----- TEST 6: basic tests for cursors with order by ----- TEST 7: test of hold cursor code in DistinctScalarAggregateResultSet.java ----- TEST 8: test of hold cursor code in GroupedAggregateResultSet.java ----- TEST 9: test scan positioned on a row which has been purged. ----- TEST 10: test scan positioned on a page which has been purged ----- ----- ----- ----- TEST 0: test hold cursor with external sort (order by). ----- Cutover to external sort has been set to 4 rows by the test property ----- file so with 10 rows we get a 1 level external sort. This tests that ----- temp files will be held open across the commit if the cursor is held ----- open. ----- run resource 'createTestProcedures.subsql'; ij> -- ----- 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. ----- 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; 0 rows inserted/updated/deleted ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> autocommit off; ij> create table foo (a int, data varchar(2000)); 0 rows inserted/updated/deleted ij> insert into foo values (10,PADSTRING('10',2000)), (9,PADSTRING('9',2000)), (8,PADSTRING('8',2000)), (7,PADSTRING('7',2000)), (6,PADSTRING('6',2000)), (5,PADSTRING('5',2000)), (4,PADSTRING('4',2000)), (3,PADSTRING('3',2000)), (2,PADSTRING('2',2000)), (1,PADSTRING('1',2000)); 10 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '1'); Statement executed. ij> get with hold cursor test1 as 'select * from foo order by a'; ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); Statement executed. ij> next test1; A |DATA ----- 1 |1 & ij> commit; ij> next test1; A |DATA ----- 2 |2 & ij> next test1; A |DATA ----- 3 |3 & ij> next test1; A |DATA ----- 4 |4 & ij> next test1; A |DATA ----- 5 |5 & ij> commit; ij> next test1; A |DATA ----- 6 |6 & ij> next test1; A |DATA ----- 7 |7 & ij> next test1; A |DATA ----- 8 |8 & ij> next test1; A |DATA ----- 9 |9 & ij> commit; ij> next test1; A |DATA ----- 10 |10 & ij> next test1; No current row ij> commit; ij> close test1; ij> -- exercise the non-held cursor path also. call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '1'); Statement executed. ij> get cursor test1 as 'select * from foo order by a'; ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); Statement executed. ij> next test1; A |DATA ----- 1 |1 & ij> next test1; A |DATA ----- 2 |2 & ij> next test1; A |DATA ----- 3 |3 & ij> next test1; A |DATA ----- 4 |4 & ij> next test1; A |DATA ----- 5 |5 & ij> next test1; A |DATA ----- 6 |6 & ij> next test1; A |DATA ----- 7 |7 & ij> next test1; A |DATA ----- 8 |8 & ij> next test1; A |DATA ----- 9 |9 & ij> next test1; A |DATA ----- 10 |10 & ij> next test1; No current row ij> close test1; ij> commit; ij> ------------------------------------------------------------------------------ ----- TEST 1: test hold cursor with multi-level external sort (order by). ----- Cutover to external sort has been set to 4 rows by the test property ----- file so with 10 rows we get a 1 level external sort. This tests that ----- temp files will be held open across the commit if the cursor is held ----- open. ----- insert into foo select a + 100, data from foo; 10 rows inserted/updated/deleted ij> insert into foo select a + 10, data from foo; 20 rows inserted/updated/deleted ij> insert into foo select a + 200, data from foo; 40 rows inserted/updated/deleted ij> insert into foo select a + 200, data from foo; 80 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '1'); Statement executed. ij> get with hold cursor test1 as 'select * from foo order by a'; ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); Statement executed. ij> next test1; A |DATA ----- 1 |1 & ij> commit; ij> next test1; A |DATA ----- 2 |2 & ij> next test1; A |DATA ----- 3 |3 & ij> next test1; A |DATA ----- 4 |4 & ij> next test1; A |DATA ----- 5 |5 & ij> commit; ij> next test1; A |DATA ----- 6 |6 & ij> next test1; A |DATA ----- 7 |7 & ij> next test1; A |DATA ----- 8 |8 & ij> next test1; A |DATA ----- 9 |9 & ij> commit; ij> next test1; A |DATA ----- 10 |10 & ij> next test1; A |DATA ----- 11 |1 & ij> commit; ij> next test1; A |DATA ----- 12 |2 & ij> next test1; A |DATA ----- 13 |3 & ij> next test1; A |DATA ----- 14 |4 & ij> next test1; A |DATA ----- 15 |5 & ij> commit; ij> next test1; A |DATA ----- 16 |6 & ij> next test1; A |DATA ----- 17 |7 & ij> next test1; A |DATA ----- 18 |8 & ij> next test1; A |DATA ----- 19 |9 & ij> commit; ij> next test1; A |DATA ----- 20 |10 & ij> next test1; A |DATA ----- 101 |1 & ij> commit; ij> next test1; A |DATA ----- 102 |2 & ij> next test1; A |DATA ----- 103 |3 & ij> next test1; A |DATA ----- 104 |4 & ij> next test1; A |DATA ----- 105 |5 & ij> commit; ij> next test1; A |DATA ----- 106 |6 & ij> next test1; A |DATA ----- 107 |7 & ij> next test1; A |DATA ----- 108 |8 & ij> next test1; A |DATA ----- 109 |9 & ij> commit; ij> next test1; A |DATA ----- 110 |10 & ij> next test1; A |DATA ----- 111 |1 & ij> commit; ij> next test1; A |DATA ----- 112 |2 & ij> next test1; A |DATA ----- 113 |3 & ij> next test1; A |DATA ----- 114 |4 & ij> next test1; A |DATA ----- 115 |5 & ij> commit; ij> next test1; A |DATA ----- 116 |6 & ij> next test1; A |DATA ----- 117 |7 & ij> next test1; A |DATA ----- 118 |8 & ij> next test1; A |DATA ----- 119 |9 & ij> commit; ij> next test1; A |DATA ----- 120 |10 & ij> next test1; A |DATA ----- 201 |1 & ij> commit; ij> close test1; ij> -- clean up drop function PADSTRING; 0 rows inserted/updated/deleted ij> drop procedure WAIT_FOR_POST_COMMIT; 0 rows inserted/updated/deleted ij> drop table foo; 0 rows inserted/updated/deleted ij> commit; ij>