access.sql

derby database source code.good for you.

set isolation to RR;
run resource 'createTestProcedures.subsql';

-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

autocommit off;

--------------------------------------------------------------------------
-- test qualifier skip code on fields with length having the 8th bit set in low
-- order length byte.
--------------------------------------------------------------------------

drop table a;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '32768');
create table a
    (
        i1      int,
        col00   varchar(384),
        col01   varchar(390),
        i2      int
    );
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);

insert into a values (1, PADSTRING('10',384), PADSTRING('100',390), 1000);
insert into a values (2, PADSTRING('20',384), PADSTRING('200',390), 2000);
insert into a values (3, PADSTRING('30',384), PADSTRING('300',390), 3000);

select i1, i2 from a where i2 = 3000;

drop table a;

commit;

-- test case for track 2590
--    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.


create table foo (a int, b varchar(900), c int);
insert into foo values (1, PADSTRING('1',900), 1); 
insert into foo values (2, PADSTRING('2',900), 1); 
insert into foo values (3, PADSTRING('3',900), 1); 
insert into foo values (4, PADSTRING('4',900), 1); 
insert into foo values (5, PADSTRING('5',900), 1); 
insert into foo values (6, PADSTRING('6',900), 1); 
insert into foo values (7, PADSTRING('7',900), 1); 
insert into foo values (8, PADSTRING('8',900), 1); 
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
create index foox on foo (a, b);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);

commit;

delete from foo where foo.a <> 2;

-- Test full cursor for update scan over all the rows in the heap, 
-- with default group fetch.  Group fetch should be disabled.
--------------------------------------------------------------------------------
-- force index until optimizer fixes problem where it does not pick index.
get cursor scan_cursor as
    'select a, b, c from foo for update of c';

next scan_cursor;

-- these inserts would cause a lock wait timeout before the bug fix.
insert into foo values (1, PADSTRING('11',900), 1);
insert into foo values (1, PADSTRING('12',900), 1);
insert into foo values (1, PADSTRING('13',900), 1);
insert into foo values (1, PADSTRING('14',900), 1);
insert into foo values (1, PADSTRING('15',900), 1);

commit;

drop table foo;
commit;



-- test case for track 735
--    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.
create table foo (a int);
insert into foo values (1024);
insert into foo (select foo.a - 1   from foo); 
insert into foo (select foo.a - 2   from foo); 
insert into foo (select foo.a - 4   from foo); 
insert into foo (select foo.a - 8   from foo); 
insert into foo (select foo.a - 16  from foo); 
insert into foo (select foo.a - 32  from foo); 
insert into foo (select foo.a - 64  from foo); 
insert into foo (select foo.a - 128 from foo); 
insert into foo (select foo.a - 256 from foo); 
insert into foo (select foo.a - 512 from foo); 

-- this create index use to fail.
create index a on foo (a);

-- Check the consistency of the indexes
VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'FOO');

-- a delete of the whole table also exercises the index well.
delete from foo;

drop table foo;

-- ----------------------------------------------------------------------------
-- stress the conglomerate directory.  abort of an alter table will clear
-- the cache.
-- ----------------------------------------------------------------------------
autocommit off;
create table a (a int);
commit;
alter table a add column c1 int;
rollback;
select * from a;
drop table a;
commit;

-- ----------------------------------------------------------------------------
-- test case for partial row runtime statistics.
-- ----------------------------------------------------------------------------

create table foo (a int, b int, c int, d int, e int);
insert into foo values (1, 2, 3, 4, 5);
insert into foo values (10, 20, 30, 40, 50);


call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 2500;

-- all the columns
select * from foo;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- just last column - should be 5 and 50
select e from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 5,3,1 and 50,30,10
select e, c, a from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier in list - should be 5,3,1 and 50,30,10
select e, c, a from foo where foo.e = 5;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier not in list 
--   - should be 5,3,1 and 50,30,10
select e, c, a from foo where foo.b = 20;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 1,2 and 10,20
select a, b from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- now check index scans - force the index just to 
-- make sure it does an index scan.
create index foo_cover on foo (e, d, c, b, a);

-- all the columns
select * from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- just last column - should be 5 and 50
select e from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 5,3,1 and 50,30,10
select e, c, a from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier in list - should be 5,3,1 and 50, 30, 10
select e, c, a from foo where foo.e = 5;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier not in list - should be 5,3,1 
-- and 50, 30, 10
select e, c, a from foo where foo.b = 20;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 1,2 and 10, 20
select a, b from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- check deleted row feature
insert into foo values (100, 2, 3, 4, 5);
insert into foo values (1000, 2, 3, 4, 5);
delete from foo where foo.a = 100;
delete from foo where foo.a = 1000;

-- now check with deleted rows involved.

-- all the columns
select * from foo;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- just last column - should be 5 and 50
select e from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 5,3,1 and 50,30,10
select e, c, a from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier in list - should be 5,3,1 and 50,30,10
select e, c, a from foo where foo.e = 5;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier not in list 
--   - should be 5,3,1 and 50,30,10
select e, c, a from foo where foo.b = 20;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 1,2 and 10,20
select a, b from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- now check index scans - force the index just to 
-- make sure it does an index scan.
create index foo_cover on foo (e, d, c, b, a);

-- all the columns
select * from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- just last column - should be 5 and 50
select e from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 5,3,1 and 50,30,10
select e, c, a from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier in list - should be 5,3,1 and 50, 30, 10
select e, c, a from foo where foo.e = 5;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns, with qualifier not in list - should be 5,3,1 
-- and 50, 30, 10
select e, c, a from foo where foo.b = 20;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- as subset of columns - should be 1,2 and 10, 20
select a, b from foo;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ----------------------------------------------------------------------------
-- test case for costing - make sure optimizer picks obvious covered query.
-- ----------------------------------------------------------------------------
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 2000;

create table base_table (a int, b varchar(1000));

insert into base_table values (1,  PADSTRING('1',1000));
insert into base_table values (2,  PADSTRING('2',1000));
insert into base_table values (3,  PADSTRING('3',1000));
insert into base_table values (4,  PADSTRING('4',1000));
insert into base_table values (5,  PADSTRING('5',1000));
insert into base_table values (6,  PADSTRING('6',1000));
insert into base_table values (7,  PADSTRING('7',1000));
insert into base_table values (8,  PADSTRING('8',1000));
insert into base_table values (9,  PADSTRING('9',1000));
insert into base_table values (10, PADSTRING('10',1000));

create index cover_idx on base_table(a);

-- make sure covered index is chosen
select a from base_table;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ----------------------------------------------------------------------------
-- test for key too big error message.
-- ----------------------------------------------------------------------------
create table d (id int not null, t_bigvarchar varchar(400), unique (id));
create index t_bigvarchar_ind on d ( t_bigvarchar);
alter table d alter t_bigvarchar set data type varchar(4096);
insert into d (id, t_bigvarchar) values (1, 

'1111111123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

890123456789012345678901234567890123456789012345678901234567890123456');


-- ----------------------------------------------------------------------------
-- test space for update
-- ----------------------------------------------------------------------------

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1');
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', '0');
create table testing (a varchar(100));
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', NULL);

insert into testing values ('a');
insert into testing (select testing.a from testing); 
insert into testing (select testing.a from testing); 
insert into testing (select testing.a from testing); 
insert into testing (select testing.a from testing); 
insert into testing (select testing.a from testing); 
insert into testing (select testing.a from testing); 
insert into testing (select testing.a from testing); 
update testing set a = 'abcd' where a = 'a';
create index zz on testing (a);

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1');
create table t1 (a varchar(100));
insert into t1 values ('a');
insert into t1 (select t1.a from t1); 
insert into t1 (select t1.a from t1); 
insert into t1 (select t1.a from t1); 
insert into t1 (select t1.a from t1); 
insert into t1 (select t1.a from t1); 
insert into t1 (select t1.a from t1); 
insert into t1 (select t1.a from t1); 
update t1 set a = 'abcd' where a = 'a';
create index zz1 on t1 (a);

-- ----------------------------------------------------------------------------
-- test load with long columns with index creation
-- ----------------------------------------------------------------------------
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create table long1 (a varchar(32000), b int, c int);
insert into long1 values ('this is a long row which will get even longer and longer to force a stream', 1, 2);
insert into long1 values ('this is another long row which will get even longer and longer to force a stream', 2, 3);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);

update long1 set a = a||a||a||a||a||a;
update long1 set a = a||a||a||a||a||a;
update long1 set a = a||a;

select LENGTH(a) from long1;

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create table long2 (a varchar(16384), b int, c int);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '16384');
create index long2i1 on long2 (a);
create index long2i2 on long2 (a,b);
create index long2i3 on long2 (a,b,c);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);

insert into long2 select * from long1;
select LENGTH(a) from long2;

-- DefectId 1346 
insert into long2 select * from long1;
select LENGTH(a) from long2;