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'); 0 rows inserted/updated/deleted 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'); 0 rows inserted/updated/deleted 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'); 0 rows inserted/updated/deleted ij> get cursor test1 as 'select * from foo order by a'; ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); 0 rows inserted/updated/deleted 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'); 0 rows inserted/updated/deleted 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'); 0 rows inserted/updated/deleted 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>