core/sql/regress/seabase/TEST024

-- tests for sequence numbers

--
-- @@@ START COPYRIGHT @@@
--
-- 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.
--
-- @@@ END COPYRIGHT @@@

set schema seqsch;
obey TEST024(clean_up);

log LOG024 clear;
create schema seqsch;
set schema seqsch;
obey TEST024(setup);
obey TEST024(select_queries);
obey TEST024(insert_queries);
obey TEST024(error_cases);
obey TEST024(clean_up);
log;
exit;

?section clean_up
drop sequence test024seq0;
drop sequence test024seq1;
drop sequence test024seq2;
drop sequence test024seq3;
drop sequence test024seq4;
drop sequence test024seq5;
drop sequence test024seq6;
drop sequence test024seq7;
drop sequence test024seq8;
drop sequence test024seq9;
drop sequence error_seq;

drop table test024t1;
drop table test024t2;

drop schema seqsch cascade;

?section setup

initialize trafodion, create metadata views;

create sequence test024seq0;

-- return error
create sequence test024seq0;

create sequence test024seq1;
create sequence test024seq2 start with 10 minvalue 1 maxvalue 15 increment by 3 cache 2;
create sequence test024seq3 maxvalue 3 cycle;
create sequence test024seq4 maxvalue 3 no cache;
create sequence test024seq5 minvalue 2 maxvalue 5 cache 4;
create sequence test024seq6 maxvalue 8 cache 3 increment by 2;
create sequence test024seq7;

showddl sequence test024seq0;
showddl sequence test024seq1;
showddl sequence test024seq2;
showddl sequence test024seq3;
showddl sequence test024seq4;
showddl sequence test024seq5;
showddl sequence test024seq6;

alter sequence test024seq2 minvalue 5;
showddl sequence test024seq2;

drop sequence test024seq0;
showddl sequence test024seq0;

set schema temp;
get sequences in schema seqsch;
set schema seqsch;

create table test024t1 (a int not null primary key, b int not null);
create table test024t2 (z int not null primary key, a int not null, b int not null);

?section select_queries
-- next 3 stmts will return 1, 2, 2
select seqnum(test024seq1) from (values(1)) x(a);
select seqnum(test024seq1, next) from (values(1)) x(a);
select seqnum(test024seq1, current) from (values(1)) x(a);

-- next 3 stmts will return 10, 13, error
select seqnum(test024seq2) from (values(1)) x(a);
select seqnum(test024seq2) from (values(1)) x(a);
select seqnum(test024seq2) from (values(1)) x(a);

-- Next 4 stmts will return 1,2,3,1
select seqnum(test024seq3) from (values(1)) x(a);
select seqnum(test024seq3) from (values(1)) x(a);
select seqnum(test024seq3) from (values(1)) x(a);
select seqnum(test024seq3) from (values(1)) x(a);
showddl sequence test024seq3;

-- Next 3 stmts will return 1,2,3
select seqnum(test024seq4) from (values(1)) x(a);
select seqnum(test024seq4) from (values(1)) x(a);
select seqnum(test024seq4) from (values(1)) x(a);
showddl sequence test024seq4;

-- Next 5 stmts will return 2,3,4,5,error
select seqnum(test024seq5) from (values(1)) x(a);
select seqnum(test024seq5) from (values(1)) x(a);
select seqnum(test024seq5) from (values(1)) x(a);
select seqnum(test024seq5) from (values(1)) x(a);
select seqnum(test024seq5) from (values(1)) x(a);

-- Next 4 stmts will return 1,3,5,7,error
select seqnum(test024seq6) from (values(1)) x(a);
select seqnum(test024seq6) from (values(1)) x(a);
select seqnum(test024seq6) from (values(1)) x(a);
select seqnum(test024seq6) from (values(1)) x(a);
select seqnum(test024seq6) from (values(1)) x(a);

select left(trim(schema_name) || '.' || trim(seq_name), 40), num_calls from 
  "_MD_".sequences_view where schema_name = 'SEQSCH';

-- where preds
insert into test024t1 values (1,1), (2,2);

-- return 0 rows
select * from test024t1 where a < seqnum(test024seq7);

-- return 1
select * from test024t1 where a < seqnum(test024seq7);

-- return 1,2
select * from test024t1 where a < seqnum(test024seq7);


?section insert_queries
delete from test024t1;
insert into test024t1 values (seqnum(test024seq7), 10);
insert into test024t1 values (seqnum(test024seq7), 10);
insert into test024t1 values (seqnum(test024seq7), 10);

-- error, dup insert
insert into test024t1 values (seqnum(test024seq7, current), 10);

-- return 4,5,6
select * from test024t1;

-- inserts 7,8,9
insert into test024t2 select seqnum(test024seq7), a, b from test024t1;

-- return 7,8,9
select * from test024t2;

-- seq num generation is independent of enclosing explicit or implicit transaction
drop sequence test024seq8;
create sequence test024seq8;
begin work;
showddl sequence test024seq8;
select seqnum(test024seq8) from (values (1)) x(a);
showddl sequence test024seq8;
rollback work;
showddl sequence test024seq8;

drop sequence test024seq8;
create sequence test024seq8;
showddl sequence test024seq8;
delete from test024t1;
insert into test024t1 values (1,1);
insert into test024t1 values (seqnum(test024seq8), 10);
showddl sequence test024seq8;

-- alter sequence
drop sequence test024seq8;
create sequence test024seq8 cache 5;
prepare s from select seqnum(test024seq8) from (values (1)) x(a);
showddl  sequence test024seq8;
execute s;
execute s;
execute s;
alter sequence test024seq8 maxvalue 2 cycle;
showddl  sequence test024seq8;
execute s;
execute s;
execute s;
execute s;
execute s;
create sequence test024seq9 no cache;
prepare s from select seqnum(test024seq9) from (values (1)) x(a);
execute s;
execute s;
alter sequence test024seq9 reset;
execute s;

-- select from metadata sequences view
invoke trafodion."_MD_".sequences_view;
select catalog_name, schema_name, seq_name, start_value, increment, max_value, min_value, cycle_option, cache_option, cache_size, next_value from trafodion."_MD_".sequences_view where schema_name = 'SEQSCH';

?section error_cases
create sequence error_seq maxvalue -1;
create sequence error_seq minvalue -1;
create sequence error_seq maxvalue 0;
create sequence error_seq minvalue 0;
create sequence error_seq minvalue 10 maxvalue 5;
create sequence error_seq increment by  0;  
create sequence error_seq increment by  -1; 
create sequence error_seq minvalue 11 maxvalue 23 increment by 14;
create sequence error_seq minvalue 5 maxvalue 10 start with 3;
create sequence error_seq minvalue 5 maxvalue 10 start with 20;  
create sequence error_seq cache 1;
create sequence error_seq minvalue 1 maxvalue 3 cache 10;
alter sequence error_seq start with 50;
create sequence error_seq;
create sequence error_seq reset;
alter sequence error_seq start with 50;