autoincrement.sql

derby database source code.good for you.

prepare autoprepare as 'insert into testme (text) values ?';
execute autoprepare using 'values (''one'')';
execute autoprepare using 'values (''two'')';
execute autoprepare using 'values (''three'')';

select * from testme;

-- give exact query and make sure that the statment cache doesn't
-- mess up things.
insert into testme (text) values ('four');
insert into testme (text) values ('four');
select * from testme;
drop table testme;
commit;

-- go back to our commiting ways.
autocommit on;

-- negative tests from autoincrementNegative.sql
-- negative bind tests.
-- invalid types 
create table ni (x int, y char(1) generated always as identity);
create table ni (x int, y decimal(5,2) generated always as identity);
create table ni (x int, y float generated always as identity (start with 1, increment by 1));
create table ni (s int, y varchar(10) generated always as identity);

-- 0 increment 
-- pass in DB2 UDB
-- fail in DB2 CS
create table ni (x int, y int generated always as identity (increment by 0));
create table ni (x int, y int generated always as identity (start with 0, increment by 0));

create table ni (x int, y smallint generated always as identity (increment by 0));
create table ni (x int, y smallint generated always as identity (start with 0, increment by 0));

create table ni (x int, y int generated always as identity (increment by 0);
create table ni (x int, y int generated always as identity (start with 0, increment by 0));

create table ni (x int, y bigint generated always as identity (increment by 0));
create table ni (x int, y bigint generated always as identity (start with 0, increment by 0));

-- out of range start 
-- actually the first few are valid
create table ni (x int, y int generated always as identity (start with 127, increment by -1));
drop table ni;
create table ni (x int, y int generated always as identity (start with -128));
drop table ni;

--  now go through this exercise for all types!
create table ni (x int, y smallint generated always as identity (start with 32768));
create table ni (x int, y smallint generated always as identity (start with -32769));

create table ni (x int, y int generated always as identity (start with  2147483648));
create table ni (x int, y int generated always as identity (start with  -2147483649));

create table ni (x int, y bigint  
				 generated always as identity (start with  9223372036854775808));
create table ni (x int, y bigint 
				 generated always as identity (start with  -9223372036854775809));

-- attempt to update or insert into autoincrement columns.
create table ai (x smallint generated always as identity, y int);
insert into ai (y) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
select * from ai;
delete from ai where y=8 OR y=4;
insert into ai (y) values (11),(13),(14),(15),(17),(18),(19);
select * from ai;

-- valid updates.
update ai set y=-y;
select * from ai order by x;
update ai set y=-y;
select * from ai order by x;

update ai set y=4 where y=3;
select * from ai order by x;
update ai set y=4 where x=3;
select * from ai order by x;

-- error, error!
update ai set x=4 where y=3;
insert into ai values (1,2);

-- overflow.
drop table ai;

create table ai (x int, y int generated always as identity (increment by 200000000));
insert into ai (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);

-- should have been rolled back.
select * from ai;

-- but the entry in syscolumns has been updated! still can't do inserts.
insert into ai (x) values (1);

-- more overflow.
drop table ai;
create table ai (x int, y smallint generated always as identity (start with  -32760, increment by -1));
insert into ai (x) values (1),(2),(3),(4),(5),(6),(7),(8);
insert into ai (x) values (9),(10);
select * from ai;

-- try overflow with int and bigint.
drop table ai;
create table ai (x int, y int generated always as identity (start with  2147483646));
insert into ai (x) values (1);
insert into ai (x) values (2);
select * from ai;
insert into ai (x) select x from ai;

drop table ai;
-- for bigint we don't go to the end-- stop one value before....
create table ai (x int, y bigint generated always as identity 
				   (start with     9223372036854775805));

insert into ai (x) values (1),(2);
insert into ai (x) values (3);

select * from ai;

-- clean up
drop table ai;

--- alter table...
create table base (x int);
insert into base values (1),(2),(3),(4),(5),(6);
select * from base;
-- should fail because alter table add generated column is not supported
alter table base add column y smallint generated always as identity (start with  10);
alter table base add column y int generated always as identity (start with  10);
alter table base add column y bigint generated always as identity (start with  10);
-- make sure alter table failures above rolled themselves back 
select * from base;
drop table base;

-- testing non-reserved keywords: generated, start, always
-- should be successful
create table always (a int);
create table start (a int);
create table generated (a int);

drop table always;
drop table start;
drop table generated;

-- IDENTITY_VAL_LOCAL function, same as DB2, beetle 5354
drop table t1;

create table t1(c1 int generated always as identity, c2 int);

-- start
insert into t1(c2) values (8);
values IDENTITY_VAL_LOCAL();

select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1;

insert into t1(c2) values (IDENTITY_VAL_LOCAL());
select * from t1;
values IDENTITY_VAL_LOCAL();
select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1;

insert into t1(c2) values (8), (9);
-- multi-values insert, return value of the function should not change, same as DB2
values IDENTITY_VAL_LOCAL();
select * from t1;
insert into t1(c2) select c1 from t1;
-- insert with sub-select, return value should not change
values IDENTITY_VAL_LOCAL();
select * from t1;
delete from t1;
values IDENTITY_VAL_LOCAL();
insert into t1(c2) select c1 from t1;
values IDENTITY_VAL_LOCAL();

-- end of practice, back to start...
insert into t1(c2) values (8);
values IDENTITY_VAL_LOCAL();

drop table t1;

-- test cases for beetle 5404: inserting multiple rows of defaults into autoincrement column.
create table t1(c1 int generated always as identity);

-- this is okay
insert into t1 values (default);
select * from t1;

-- should fail
insert into t1 values (1), (1);
select * from t1;

-- this returns the right error
insert into t1 values (1), (default);
insert into t1 values (default), (1);
insert into t1 values (default), (default), (default), (2);
insert into t1 values (default), (default), (2);
insert into t1 values (default), (default), (2), (default);

-- this returns NPE
insert into t1 values (default), (default);
select * from t1;

insert into t1 values (default), (default), (default);
select * from t1;

insert into t1 values (default), (default), (default), (default);
select * from t1;

create table t2 (a int, b int generated always as identity);
insert into t2 values (1, default), (2, default);
select * from t2;

insert into t2 values (1, default), (2, 2);
insert into t2 values (1, default), (2, default), (2, 2);
insert into t2 values (1, 2), (2, default), (2, default);

create table t3(c1 int generated always as identity (increment by 3));

-- succeeded
insert into t3 values (default);

select * from t3;
insert into t3 values (default);
select * from t3;

-- should fail
insert into t3 values (1), (1);
select * from t3;

-- this returns the right error
insert into t3 values (1), (default);
insert into t3 values (default), (1);
insert into t3 values (default), (default), (default), (2);
insert into t3 values (default), (default), (2);
insert into t3 values (default), (default), (2), (default);
insert into t3 select * from t1;
insert into t3 select * from table (values (1)) as q(a);
insert into t3 select * from table (values (default)) as q(a);

-- this returns NPE
insert into t3 values (default), (default);
select * from t3;

insert into t3 values (default), (default), (default);
select * from t3;

insert into t3 values (default), (default), (default), (default);
select * from t3;

drop table t1;
drop table t2;
drop table t3;


-- Defaults/always
-- without increment option
create table t1(i int, t1_autogen int generated always as identity);
create table t2(i int, t2_autogen int generated by default as identity);

insert into t1(i) values(1);
insert into t1(i) values(1);
select * from t1;

insert into t2(i) values(1);
insert into t2(i) values(1);
select * from t2;

drop table t1;
drop table t2;

create table t1(i int, t1_autogen int generated always as identity);
create table t2(i int, t2_autogen int generated by default as identity);

insert into t1(i,t1_autogen) values(2,1);
insert into t1(i,t1_autogen) values(2,2);
insert into t1(i) values(2);
insert into t1(i) values(2);
select * from t1;

insert into t2(i,t2_autogen) values(2,1);
insert into t2(i,t2_autogen) values(2,2);
insert into t2(i) values(2);
insert into t2(i) values(2);
select * from t2;

drop table t1;
drop table t2;


--with increment by 

create table t1(i int, t1_autogen int generated always as identity(increment by 10));
create table t2(i int, t2_autogen int generated by default as identity(increment by 10));

insert into t1(i) values(1);
insert into t1(i) values(1);
select * from t1;

insert into t2(i) values(1);
insert into t2(i) values(1);
select * from t2;

drop table t1;
drop table t2;

create table t1(i int, t1_autogen int generated always as identity(increment by 10));
create table t2(i int, t2_autogen int generated by default as identity(increment by 10));

insert into t1(i,t1_autogen) values(2,1);
insert into t1(i,t1_autogen) values(2,2);
insert into t1(i) values(2);
insert into t1(i) values(2);
select * from t1;

insert into t2(i,t2_autogen) values(2,1);
insert into t2(i,t2_autogen) values(2,2);
insert into t2(i) values(2);
insert into t2(i) values(2);
select * from t2;

drop table t1;
drop table t2;


--with start with, increment by 

create table t1(i int, t1_autogen int generated always as identity(start with 100, increment by 20));
create table t2(i int, t2_autogen int generated by default as identity(start with 100, increment by 20));

insert into t1(i) values(1);
insert into t1(i) values(1);
select * from t1;

insert into t2(i) values(1);
insert into t2(i) values(1);
select * from t2;

drop table t1;
drop table t2;

create table t1(i int, t1_autogen int generated always as identity(start with 100, increment by 20));
create table t2(i int, t2_autogen int generated by default as identity(start with 100, increment by 20));

insert into t1(i,t1_autogen) values(2,1);
insert into t1(i,t1_autogen) values(2,2);
insert into t1(i) values(2);
insert into t1(i) values(2);
select * from t1;

insert into t2(i,t2_autogen) values(2,1);
insert into t2(i,t2_autogen) values(2,2);
insert into t2(i) values(2);
insert into t2(i) values(2);
select * from t2;

drop table t1;
drop table t2;


--with unique constraint

create table t3(i int,t3_autogen int generated by default as identity(start with 0, increment by 1) unique);

insert into t3(i,t3_autogen) values(1,0);
insert into t3(i,t3_autogen) values(2,1);

insert into t3(i) values(3);
insert into t3(i) values(4);
insert into t3(i) values(5);

select i,t3_autogen from t3;

drop table t3;

--with unique index

create table t4(i int,t4_autogen int generated by default as identity(start with 0, increment by 1));
create unique index idx_t4_autogen on t4(t4_autogen);

insert into t4(i,t4_autogen) values(1,0);
insert into t4(i,t4_autogen) values(2,1);

insert into t4(i) values(3);
insert into t4(i) values(4);
insert into t4(i) values(5);

select i,t4_autogen from t4;

drop index idx_t4_autogen;
drop table t4;

-- test IDENTITY_VAL_LOCAL function with 2 different connections
-- connection one
connect 'wombat' as conn1;
create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
-- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn1
values IDENTITY_VAL_LOCAL();
commit;
-- connection two
connect 'wombat' as conn2;
-- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn2
values IDENTITY_VAL_LOCAL();
insert into t2 (c22) values (1);
-- IDENTITY_VAL_LOCAL() will return 201 because there was single row insert into table t2 with identity column on this connection conn2
values IDENTITY_VAL_LOCAL();
set connection conn1;
-- IDENTITY_VAL_LOCAL() will continue to return NULL because no single row insert into table with identity column yet on this connection conn1
values IDENTITY_VAL_LOCAL();
insert into t1 (c12) values (1);
-- IDENTITY_VAL_LOCAL() will return 101 because there was single row insert into table t1 with identity column on this connection conn1
values IDENTITY_VAL_LOCAL();
set connection conn2;
-- IDENTITY_VAL_LOCAL() on conn2 not impacted by single row insert into table with identity column on conn1
values IDENTITY_VAL_LOCAL();
-- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL()
commit;
values IDENTITY_VAL_LOCAL();
-- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL()
values IDENTITY_VAL_LOCAL();
drop table t1;
drop table t2;

-- A table with identity column has an insert trigger which inserts into another table 
-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the 
-- statement table and not for the table that got modified by the trigger
create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1);
values IDENTITY_VAL_LOCAL();
insert into t1 (c12) values (1);
-- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1. 
-- It will not return 201 which got generated for t2 as a result of the trigger fire.
values IDENTITY_VAL_LOCAL();
select * from t1;
select * from t2;
drop table t1;
drop table t2;