--
--   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.
--
-- Very basic single user testing of read locks on cursors on heap tables.  
-- This ".subsql" test is
-- meant to be run from another test such that it gets run under multiple
-- isolation levels.  This is important as they behave
-- differently, depending on isolation levels.
--
-- assume's caller has already done: run 'LockTableQuery.subsql'; to get 
-- easy access to the lock VTI.

-- TEST 0: heap cursor scan, group fetch = 1
-- TEST 1: heap cursor scan, group fetch = 2
-- TEST 2: heap cursor scan, group fetch = 1, stop scan in middle of set
-- TEST 3: heap cursor scan, group fetch = 4, stop scan in middle of set
-- TEST 4: heap cursor scan, group fetch = 1 (scan table with some deleted rows)
-- TEST 5: heap cursor scan, group fetch = 2 (scan with committed deleted rows)
-- TEST 6: heap cursor scan, group fetch = 1 (scan with "<" qualifier)
-- TEST 7: heap cursor scan, group fetch = 2 (scan with "<" qualifier)
-- TEST 8: heap cursor scan, group fetch = 1 (scan with equals qualifier)
-- TEST 9: heap cursor scan, group fetch = 2 (scan with equals qualifier)
-- TEST 10: heap cursor scan, group fetch = 1 (equals qualifier, no rows return)

autocommit off;

--------------------------------------------------------------------------------
-- Assumes that calling routine has set up the following simple dataset, 
-- a heap, no indexes with following initial values:
--     create table (a int, b int, c somesortofchar);
-- 1, 10, 'one'
-- 2, 20, 'two'
-- 3, 30, 'three'
-- 4, 40, 'four'
-- 5, 50, 'five'
-- 6, 60, 'six'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
select * from a;
commit;

--------------------------------------------------------------------------------
-- TEST 0: heap cursor scan, group fetch = 1
-- Test full cursor scan which does no updates.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row.
--    READ COMMITTED   - TABLE IS, will hold single S lock on current row.
--    READ UNCOMMITTED - TABLE IS, no row locks.
--------------------------------------------------------------------------------
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
    'select a, b, c from a';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- TEST 1: heap cursor scan, group fetch = 2
-- Test full cursor scan which does no updates.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row.
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--------------------------------------------------------------------------------
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the query 'select a, b, c from a' to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- TEST 2: heap cursor scan, group fetch = 1, stop scan in middle of set
-- Test full cursor scan which does no updates.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row.
--    READ COMMITTED   - TABLE IS, will hold single S lock on current row.
--    READ UNCOMMITTED - TABLE IS, no row locks.
--------------------------------------------------------------------------------
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the query 'select a, b, c from a' to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- TEST 3: heap cursor scan, group fetch = 4, stop scan in middle of set
-- Test full cursor scan which does no updates.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row.
--    READ COMMITTED   - TABLE IS, will hold single S lock on current row.
--    READ UNCOMMITTED - TABLE IS, no row locks.
--------------------------------------------------------------------------------
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','4');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the query 'select a, b, c from a' to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- TEST 4: heap cursor scan, group fetch = 1 (scan table with some deleted rows)
-- Test full cursor scan on a data set with some deleted rows (the "even" ones).
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- After the delete the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
delete from a where a = 2 or a = 4 or a = 6;
commit;

-- The above delete and commit can allow post commit processing to run before
-- the subsequent select is run, most likely if this run is on a table where
-- there is one row per page.  To get reproducible results from the subsequent
-- lock calls, wait for post commit to finish before running the select.  
-- Without this wait "extra" locks sometimes might show up - these are locks
-- on the committed deleted rows which have not been cleaned yet depending
-- on timing.
CALL WAIT_FOR_POST_COMMIT();

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the query 'select a, b, c from a' to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- TEST 5: heap cursor scan, group fetch = 2 (scan with committed deleted rows)
-- Test full cursor scan on a data set with some deleted rows (the "even" ones).
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                        (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the query 'select a, b, c from a' to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- TEST 6: heap cursor scan, group fetch = 1 (scan with "<" qualifier)
-- Test "less than" qualified cursor scan on a data set.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                        (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
    'select a, b, c from a where a < 3';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

--------------------------------------------------------------------------------
-- TEST 7: heap cursor scan, group fetch = 2 (scan with "<" qualifier)
-- Test "less than" qualified cursor scan on a data set.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                        (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the statement to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a where a < 3';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

--------------------------------------------------------------------------------
-- TEST 8: heap cursor scan, group fetch = 1 (scan with equals qualifier)
-- Test "equals" qualified cursor scan on a data set.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                        (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
    'select a, b, c from a where a = 5';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

--------------------------------------------------------------------------------
-- TEST 9: heap cursor scan, group fetch = 2 (scan with equals qualifier)
-- Test "equals" qualified cursor scan on a data set.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                        (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');
get cursor scan_cursor as
    'select a, b, c from a where a = 7';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

--------------------------------------------------------------------------------
-- TEST 10: heap cursor scan, group fetch = 1 (equals qualifier, no rows return)
-- Test "equals" qualified cursor scan on a data set, no rows returned.
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                        (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');

-- RESOLVE: missing row locks
-- WORKAROUND: creating an index and dropping it 
-- to force the statement to be recompiled
create index ix1 on a(a);
drop index ix1;
commit;

get cursor scan_cursor as
    'select a, b, c from a where a = 7';
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16');

select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;

drop table a;