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. -- -- Adding new testcases for DB2 syntax "GENERATED ALWAYS AS IDENTITY" -- We don't enhance "ALTER TABLE MODIFY COLUMN" yet: DB2 uses "ALTER TABLE ALTER COLUMN..." -- try generated values with all types. -- use query on lock table that only looks at locks held by user transactions, -- to avoid picking up locks by background threads. create view lock_table as select cast(username as char(8)) as username, cast(t.type as char(8)) as trantype, cast(l.type as char(8)) as type, cast(lockcount as char(3)) as cnt, mode, cast(tablename as char(12)) as tabname, state, status from syscs_diag.lock_table l right outer join syscs_diag.transaction_table t on l.xid = t.xid where t.type='UserTransaction' and l.lockcount is not null; 0 rows inserted/updated/deleted ij> create table ai_zero (i int, a_zero int generated always as identity); 0 rows inserted/updated/deleted ij> create table ai_one (i int, a_one smallint generated always as identity); 0 rows inserted/updated/deleted ij> create table ai_two (i int, a_two int generated always as identity); 0 rows inserted/updated/deleted ij> create table ai_three (i int, a_three int generated always as identity); 0 rows inserted/updated/deleted ij> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME in ('A_ZERO', 'A_ONE', 'A_TWO', 'A_THREE'); COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- A_ZERO |1 |1 |1 A_ONE |1 |1 |1 A_TWO |1 |1 |1 A_THREE |1 |1 |1 ij> drop table ai_zero; 0 rows inserted/updated/deleted ij> drop table ai_one; 0 rows inserted/updated/deleted ij> drop table ai_two; 0 rows inserted/updated/deleted ij> drop table ai_three; 0 rows inserted/updated/deleted ij> -- try a generated column spec with initial and start values. create table ai (i int, autoinc int generated always as identity (start with 100)); 0 rows inserted/updated/deleted ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'AUTOINC'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- 100 |100 |1 ij> drop table ai; 0 rows inserted/updated/deleted ij> create table ai (i int, autoinc int generated always as identity (increment by 100)); 0 rows inserted/updated/deleted ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'AUTOINC'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- 1 |1 |100 ij> drop table ai; 0 rows inserted/updated/deleted ij> create table ai (i int, autoinc int generated always as identity (start with 101, increment by 100)); 0 rows inserted/updated/deleted ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'AUTOINC'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- 101 |101 |100 ij> drop table ai; 0 rows inserted/updated/deleted ij> -- try -ive numbers. create table ai1 (i int, a1 int generated always as identity (start with 0, increment by -1)); 0 rows inserted/updated/deleted ij> create table ai2 (i int, a2 int generated always as identity (start with +0, increment by -1)); 0 rows inserted/updated/deleted ij> create table ai3 (i int, a3 int generated always as identity (start with -1, increment by -1)); 0 rows inserted/updated/deleted ij> create table ai4 (i int, a4 int generated always as identity (start with -11, increment by +100)); 0 rows inserted/updated/deleted ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'A1'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- 0 |0 |-1 ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'A2'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- 0 |0 |-1 ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'A3'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- -1 |-1 |-1 ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'A4'; AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -------------------------------------------------------------- -11 |-11 |100 ij> drop table ai1; 0 rows inserted/updated/deleted ij> drop table ai2; 0 rows inserted/updated/deleted ij> drop table ai3; 0 rows inserted/updated/deleted ij> drop table ai4; 0 rows inserted/updated/deleted ij> -- **** simple increment tests. -- should return null as no single insert has been executed values IDENTITY_VAL_LOCAL(); 1 ------------------------------- NULL ij> create table ai_short (i int, ais smallint generated always as identity (start with 0, increment by 2)); 0 rows inserted/updated/deleted ij> insert into ai_short (i) values (0); 1 row inserted/updated/deleted ij> insert into ai_short (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_short (i) values (2); 1 row inserted/updated/deleted ij> insert into ai_short (i) values (3); 1 row inserted/updated/deleted ij> select * from ai_short; I |AIS ------------------ 0 |0 1 |2 2 |4 3 |6 ij> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'AIS'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- AIS |8 |0 |2 ij> drop table ai_short; 0 rows inserted/updated/deleted ij> -- table with more than one generated column spec should fail create table ai_multiple (i int, a0 int generated always as identity (start with -1, increment by -1), a1 smallint generated always as identity, a2 int generated always as identity (start with 0), a3 bigint generated always as identity (start with -100, increment by 10)); ERROR 428C1: Only one identity column is allowed in a table. ij> -- table with one generated column spec should succeed create table ai_single1 (i int, a0 int generated always as identity (start with -1, increment by -1)); 0 rows inserted/updated/deleted ij> create table ai_single2 (i int, a1 smallint generated always as identity); 0 rows inserted/updated/deleted ij> create table ai_single3 (i int, a2 int generated always as identity (start with 0)); 0 rows inserted/updated/deleted ij> create table ai_single4 (i int, a3 bigint generated always as identity (start with -100, increment by 10)); 0 rows inserted/updated/deleted ij> insert into ai_single1 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (2); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (3); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (4); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (5); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (6); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (7); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (8); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (9); 1 row inserted/updated/deleted ij> insert into ai_single1 (i) values (10); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (2); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (3); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (4); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (5); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (6); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (7); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (8); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (9); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (10); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (2); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (3); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (4); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (5); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (6); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (7); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (8); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (9); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (10); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (2); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (3); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (4); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (5); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (6); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (7); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (8); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (9); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (10); 1 row inserted/updated/deleted ij> select a.i, a0, a1, a2, a3 from ai_single1 a join ai_single2 b on a.i = b.i join ai_single3 c on a.i = c.i join ai_single4 d on a.i = d.i; I |A0 |A1 |A2 |A3 --------------------------------------------------------------- 1 |-1 |1 |0 |-100 2 |-2 |2 |1 |-90 3 |-3 |3 |2 |-80 4 |-4 |4 |3 |-70 5 |-5 |5 |4 |-60 6 |-6 |6 |5 |-50 7 |-7 |7 |6 |-40 8 |-8 |8 |7 |-30 9 |-9 |9 |8 |-20 10 |-10 |10 |9 |-10 ij> delete from ai_single1; 10 rows inserted/updated/deleted ij> delete from ai_single2; 10 rows inserted/updated/deleted ij> delete from ai_single3; 10 rows inserted/updated/deleted ij> delete from ai_single4; 10 rows inserted/updated/deleted ij> insert into ai_single1 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single2 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single3 (i) values (1); 1 row inserted/updated/deleted ij> insert into ai_single4 (i) values (1); 1 row inserted/updated/deleted ij> select a.i, a0, a1, a2, a3 from ai_single1 a join ai_single2 b on a.i = b.i join ai_single3 c on a.i = c.i join ai_single4 d on a.i = d.i; I |A0 |A1 |A2 |A3 --------------------------------------------------------------- 1 |-11 |11 |10 |0 ij> -- clean up drop table ai_single1; 0 rows inserted/updated/deleted ij> drop table ai_single2; 0 rows inserted/updated/deleted ij> drop table ai_single3; 0 rows inserted/updated/deleted ij> drop table ai_single4; 0 rows inserted/updated/deleted ij> -- **** connection info tests {basic ones} create table ai_test (x int generated always as identity (start with 2, increment by 2), y int); 0 rows inserted/updated/deleted ij> insert into ai_test (y) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 10 rows inserted/updated/deleted ij> -- should see 0. values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 0 ij> insert into ai_test (y) select y+10 from ai_test; 10 rows inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 0 ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 0 ij> -- try some more connection info tests create table ai_single1 (c char(100), a_odd int generated always as identity (start with 1, increment by 2)); 0 rows inserted/updated/deleted ij> create table ai_single2 (c char(100), a_even int generated always as identity (start with 0, increment by 2)); 0 rows inserted/updated/deleted ij> create table ai_single3 (c char(100), a_sum bigint generated always as identity (start with 1, increment by 2)); 0 rows inserted/updated/deleted ij> insert into ai_single1 (c) values ('a'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 ij> insert into ai_single2 (c) values ('a'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 0 ij> insert into ai_single3 (c) values ('a'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 ij> insert into ai_single1 (c) values ('b'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 3 ij> insert into ai_single2 (c) values ('b'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> insert into ai_single3 (c) values ('b'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 3 ij> drop table ai_single1; 0 rows inserted/updated/deleted ij> drop table ai_single2; 0 rows inserted/updated/deleted ij> drop table ai_single3; 0 rows inserted/updated/deleted ij> drop table ai_test; 0 rows inserted/updated/deleted ij> -- nested, nested, nested stuff. -- t1 --> trigger --> insert into t2 -- insert row into t1. -- I can get lastAutoincrementValue for t1 but not t2. create table t1 (c1 int generated always as identity, name char(32)); 0 rows inserted/updated/deleted ij> create table t2 (c2 int generated always as identity, name char(32)); 0 rows inserted/updated/deleted ij> create trigger insert_trigger after insert on t1 for each row insert into t2 (name) values ('Bob Finocchio'); 0 rows inserted/updated/deleted ij> insert into t1 (name) values ('Phil White'); 1 row inserted/updated/deleted ij> select * from t1; C1 |NAME -------------------------------------------- 1 |Phil White ij> select * from t2; C2 |NAME -------------------------------------------- 1 |Bob Finocchio ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 ij> insert into t2 (name) values ('Jean-Yves Dexemier'); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> -- insert into multiple tables in different schema names with same tablename,column names -- make sure -- lastAutoincrementValue shouldn't get confused..... drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> -- APP.TAB1.A1 ==> -1,-2,-3 -- APP.TAB1.A2 ==> 1,2,3 -- APP.TAB2.A1 ==> 0,-2,-4 -- APP.TAB3.A2 ==> 0,2,4 create table tab1 (i int, a1 int generated always as identity (start with -1, increment by -1)); 0 rows inserted/updated/deleted ij> create table tab2 (i int, a2 smallint generated always as identity (start with 1, increment by +1)); 0 rows inserted/updated/deleted ij> create table tab3 (i int, a1 int generated always as identity (start with 0, increment by -2)); 0 rows inserted/updated/deleted ij> create table tab4 (i int, a2 bigint generated always as identity (start with 0, increment by 2)); 0 rows inserted/updated/deleted ij> create schema BPP; 0 rows inserted/updated/deleted ij> set schema BPP; 0 rows inserted/updated/deleted ij> -- BPP.TAB1.A1 ==> 100,101,102 -- BPP.TAB2.A2 ==> 100,99,98 -- BPP.TAB3.A1 ==> 100,102,104 -- BPP.TAB4.A2 ==> 100,98,96 create table tab1 (i int, a1 int generated always as identity (start with 100, increment by 1)); 0 rows inserted/updated/deleted ij> create table tab2 (i int, a2 bigint generated always as identity (start with 100, increment by -1)); 0 rows inserted/updated/deleted ij> create table tab3 (i int, a1 int generated always as identity (start with 100, increment by 2)); 0 rows inserted/updated/deleted ij> create table tab4 (i int, a2 smallint generated always as identity (start with 100, increment by -2)); 0 rows inserted/updated/deleted ij> insert into APP.tab1 (i) values (1); 1 row inserted/updated/deleted ij> insert into APP.tab2 (i) values (1); 1 row inserted/updated/deleted ij> insert into APP.tab3 (i) values (1); 1 row inserted/updated/deleted ij> insert into APP.tab4 (i) values (1); 1 row inserted/updated/deleted ij> insert into tab1 (i) values (1); 1 row inserted/updated/deleted ij> insert into tab1 (i) values (2); 1 row inserted/updated/deleted ij> insert into tab2 (i) values (1); 1 row inserted/updated/deleted ij> insert into tab2 (i) values (2); 1 row inserted/updated/deleted ij> insert into tab3 (i) values (1); 1 row inserted/updated/deleted ij> insert into tab3 (i) values (2); 1 row inserted/updated/deleted ij> insert into tab4 (i) values (1); 1 row inserted/updated/deleted ij> insert into tab4 (i) values (2); 1 row inserted/updated/deleted ij> select a.i, a1, a2 from app.tab1 a join app.tab2 b on a.i = b.i; I |A1 |A2 ------------------------------ 1 |-1 |1 ij> select a.i, a1, a2 from app.tab3 a join app.tab4 b on a.i = b.i; I |A1 |A2 -------------------------------------------- 1 |0 |0 ij> select a.i, a1, a2 from tab1 a join tab2 b on a.i = b.i; I |A1 |A2 -------------------------------------------- 1 |100 |100 2 |101 |99 ij> select a1, a2, a.i from tab3 a join tab4 b on a.i = b.i; A1 |A2 |I ------------------------------ 100 |100 |1 102 |98 |2 ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 98 ij> set schema app; 0 rows inserted/updated/deleted ij> drop table bpp.tab1; 0 rows inserted/updated/deleted ij> drop table bpp.tab2; 0 rows inserted/updated/deleted ij> drop table bpp.tab3; 0 rows inserted/updated/deleted ij> drop table bpp.tab4; 0 rows inserted/updated/deleted ij> drop schema bpp restrict; 0 rows inserted/updated/deleted ij> drop table tab1; 0 rows inserted/updated/deleted ij> drop table tab2; 0 rows inserted/updated/deleted ij> drop table tab3; 0 rows inserted/updated/deleted ij> drop table tab4; 0 rows inserted/updated/deleted ij> -- trigger, -- insert into t2 -- ==> fires trigger which inserts into t1. -- create table tab1 (s1 int generated always as identity, lvl int); 0 rows inserted/updated/deleted ij> create table tab3 (c1 int); 0 rows inserted/updated/deleted ij> create trigger tab1_after1 after insert on tab3 referencing new as newrow for each row insert into tab1 (lvl) values 1,2,3; 0 rows inserted/updated/deleted ij> insert into tab3 values null; 1 row inserted/updated/deleted ij> select * from tab1; S1 |LVL ----------------------- 1 |1 2 |2 3 |3 ij> select b.tablename, a.autoincrementvalue, a.autoincrementstart, a.autoincrementinc from sys.syscolumns a, sys.systables b where a.referenceid=b.tableid and a.columnname ='S1' and b.tablename = 'TAB1'; TABLENAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TAB1 |4 |1 |1 ij> create table tab2 (lvl int, s1 bigint generated always as identity); 0 rows inserted/updated/deleted ij> create trigger tab1_after2 after insert on tab3 referencing new as newrow for each row insert into tab2 (lvl) values 1,2,3; 0 rows inserted/updated/deleted ij> insert into tab3 values null; 1 row inserted/updated/deleted ij> select * from tab2; LVL |S1 -------------------------------- 1 |1 2 |2 3 |3 ij> select b.tablename, a.autoincrementvalue, a.autoincrementstart, a.autoincrementinc from sys.syscolumns a, sys.systables b where a.referenceid=b.tableid and a.columnname ='S1' and b.tablename = 'TAB2'; TABLENAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TAB2 |4 |1 |1 ij> -- clean up drop trigger tab1_after1; 0 rows inserted/updated/deleted ij> drop trigger tab1_after2; 0 rows inserted/updated/deleted ij> drop table tab1; 0 rows inserted/updated/deleted ij> drop table tab2; 0 rows inserted/updated/deleted ij> drop table tab3; 0 rows inserted/updated/deleted ij> -- some more variations of lastAutoincrementValue.... -- make sure we don't lose values from previous inserts. create table t1 (x int, s1 int generated always as identity); 0 rows inserted/updated/deleted ij> create table t2 (x smallint, s2 int generated always as identity (start with 0)); 0 rows inserted/updated/deleted ij> insert into t1 (x) values (1); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 ij> insert into t1 (x) values (2); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> insert into t2 (x) values (1); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 0 ij> -- alter table tests. drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> create table t1 (s1 int generated always as identity); 0 rows inserted/updated/deleted ij> alter table t1 add column x int; 0 rows inserted/updated/deleted ij> insert into t1 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> create table t2 (s2 int generated always as identity (start with 2)); 0 rows inserted/updated/deleted ij> alter table t2 add column x int; 0 rows inserted/updated/deleted ij> insert into t2 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> create table t3 (s0 int generated always as identity (start with 0)); 0 rows inserted/updated/deleted ij> alter table t3 add column x int; 0 rows inserted/updated/deleted ij> insert into t3 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x; X |S1 |S2 |S0 ----------------------------------------------- 1 |1 |2 |0 2 |2 |3 |1 3 |3 |4 |2 4 |4 |5 |3 5 |5 |6 |4 ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 0 ij> -- test some more generated column specs create table trigtest (s1 smallint generated always as identity, lvl int); 0 rows inserted/updated/deleted ij> insert into trigtest (lvl) values (0); 1 row inserted/updated/deleted ij> insert into trigtest (lvl) values (1),(2); 2 rows inserted/updated/deleted ij> insert into trigtest (lvl) values (3),(4); 2 rows inserted/updated/deleted ij> insert into trigtest (lvl) values (5),(6); 2 rows inserted/updated/deleted ij> insert into trigtest (lvl) values (7),(8); 2 rows inserted/updated/deleted ij> select * from trigtest; S1 |LVL ------------------ 1 |0 2 |1 3 |2 4 |3 5 |4 6 |5 7 |6 8 |7 9 |8 ij> drop table trigtest; 0 rows inserted/updated/deleted ij> select count(*) from t1; 1 ----------- 5 ij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x; X |S1 |S2 |S0 ----------------------------------------------- 1 |1 |2 |0 2 |2 |3 |1 3 |3 |4 |2 4 |4 |5 |3 5 |5 |6 |4 ij> delete from t1; 5 rows inserted/updated/deleted ij> delete from t2; 5 rows inserted/updated/deleted ij> delete from t3; 5 rows inserted/updated/deleted ij> insert into t1 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> insert into t2 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> insert into t3 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> -- should have started from after the values in t1 due to alter. select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x; X |S1 |S2 |S0 ----------------------------------------------- 1 |6 |7 |5 2 |7 |8 |6 3 |8 |9 |7 4 |9 |10 |8 5 |10 |11 |9 ij> insert into t1 (x) values (6); 1 row inserted/updated/deleted ij> insert into t2 (x) values (6); 1 row inserted/updated/deleted ij> insert into t3 (x) values (6); 1 row inserted/updated/deleted ij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x; X |S1 |S2 |S0 ----------------------------------------------- 1 |6 |7 |5 2 |7 |8 |6 3 |8 |9 |7 4 |9 |10 |8 5 |10 |11 |9 6 |11 |12 |10 ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 10 ij> delete from t1; 6 rows inserted/updated/deleted ij> delete from t2; 6 rows inserted/updated/deleted ij> delete from t3; 6 rows inserted/updated/deleted ij> insert into t1 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> insert into t2 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> insert into t3 (x) values (1),(2),(3),(4),(5); 5 rows inserted/updated/deleted ij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x; X |S1 |S2 |S0 ----------------------------------------------- 1 |12 |13 |11 2 |13 |14 |12 3 |14 |15 |13 4 |15 |16 |14 5 |16 |17 |15 ij> insert into t1 (x) values (6); 1 row inserted/updated/deleted ij> insert into t2 (x) values (6); 1 row inserted/updated/deleted ij> insert into t3 (x) values (6); 1 row inserted/updated/deleted ij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x; X |S1 |S2 |S0 ----------------------------------------------- 1 |12 |13 |11 2 |13 |14 |12 3 |14 |15 |13 4 |15 |16 |14 5 |16 |17 |15 6 |17 |18 |16 ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 16 ij> -- make sure we're doing nested xactions to update ai values. drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> create table t1 (x int, yyy int generated always as identity (start with 0)); 0 rows inserted/updated/deleted ij> autocommit off; ij> insert into t1 (x) values (1); 1 row inserted/updated/deleted ij> insert into t1 (x) values (2); 1 row inserted/updated/deleted ij> select * from t1; X |YYY ----------------------- 1 |0 2 |1 ij> -- should see only locks on t1, no locks on system catalogs. select * from lock_table order by tabname, type desc, mode, cnt; USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS ---------------------------------------------------------------- APP |UserTran|TABLE |2 |IX |T1 |GRANT|ACTIVE APP |UserTran|ROW |1 |X |T1 |GRANT|ACTIVE APP |UserTran|ROW |1 |X |T1 |GRANT|ACTIVE ij> delete from t1; 2 rows inserted/updated/deleted ij> commit; ij> -- locks should be gone now. select * from lock_table order by tabname, type desc, mode, cnt; USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS ---------------------------------------------------------------- ij> set isolation serializable; 0 rows inserted/updated/deleted ij> -- this will get a share lock on syscolumns select columnname, autoincrementvalue from sys.syscolumns where columnname = 'YYY'; COLUMNNAME |AUTOINCREMENTVALUE ----------------------------------------------------------------------------------------------------------------------------------------------------- YYY |2 ij> select * from lock_table order by tabname, type desc, mode, cnt; USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS ---------------------------------------------------------------- APP |UserTran|TABLE |1 |S |SYSCOLUMNS |GRANT|ACTIVE ij> insert into t1 (x) values (3); 1 row inserted/updated/deleted ij> select * from lock_table order by tabname, type desc, mode, cnt; USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS ---------------------------------------------------------------- APP |UserTran|TABLE |1 |IX |SYSCOLUMNS |GRANT|ACTIVE APP |UserTran|TABLE |1 |S |SYSCOLUMNS |GRANT|ACTIVE APP |UserTran|ROW |2 |X |SYSCOLUMNS |GRANT|ACTIVE APP |UserTran|TABLE |1 |IX |T1 |GRANT|ACTIVE APP |UserTran|ROW |1 |X |T1 |GRANT|ACTIVE ij> commit; ij> -- try using default keyword with ai. drop table t1; 0 rows inserted/updated/deleted ij> create table t1 (x char(2) default 'yy', y bigint generated always as identity); 0 rows inserted/updated/deleted ij> insert into t1 (x, y) values ('aa', default); 1 row inserted/updated/deleted ij> insert into t1 values ('bb', default); 1 row inserted/updated/deleted ij> insert into t1 (x) values default; 1 row inserted/updated/deleted ij> insert into t1 (x) values null; 1 row inserted/updated/deleted ij> -- switch the order of the columns insert into t1 (y, x) values (default, 'cc'); 1 row inserted/updated/deleted ij> select * from t1; X |Y ------------------------- aa |1 bb |2 yy |3 NULL|4 cc |5 ij> -- bug 3450. autocommit off; ij> create table testme (text varchar(10), autonum int generated always as identity); 0 rows inserted/updated/deleted ij> commit; ij> prepare autoprepare as 'insert into testme (text) values ?'; ij> execute autoprepare using 'values (''one'')'; 1 row inserted/updated/deleted ij> execute autoprepare using 'values (''two'')'; 1 row inserted/updated/deleted ij> execute autoprepare using 'values (''three'')'; 1 row inserted/updated/deleted ij> select * from testme; TEXT |AUTONUM ---------------------- one |1 two |2 three |3 ij> -- give exact query and make sure that the statment cache doesn't -- mess up things. insert into testme (text) values ('four'); 1 row inserted/updated/deleted ij> insert into testme (text) values ('four'); 1 row inserted/updated/deleted ij> select * from testme; TEXT |AUTONUM ---------------------- one |1 two |2 three |3 four |4 four |5 ij> drop table testme; 0 rows inserted/updated/deleted ij> commit; ij> -- go back to our commiting ways. autocommit on; ij> -- negative tests from autoincrementNegative.sql -- negative bind tests. -- invalid types create table ni (x int, y char(1) generated always as identity); ERROR 42Z22: Invalid type specified for identity column 'Y'. The only valid types for identity columns are BIGINT, INT and SMALLINT. ij> create table ni (x int, y decimal(5,2) generated always as identity); ERROR 42Z22: Invalid type specified for identity column 'Y'. The only valid types for identity columns are BIGINT, INT and SMALLINT. ij> create table ni (x int, y float generated always as identity (start with 1, increment by 1)); ERROR 42Z22: Invalid type specified for identity column 'Y'. The only valid types for identity columns are BIGINT, INT and SMALLINT. ij> create table ni (s int, y varchar(10) generated always as identity); ERROR 42Z22: Invalid type specified for identity column 'Y'. The only valid types for identity columns are BIGINT, INT and SMALLINT. ij> -- 0 increment -- pass in DB2 UDB -- fail in DB2 CS create table ni (x int, y int generated always as identity (increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> create table ni (x int, y int generated always as identity (start with 0, increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> create table ni (x int, y smallint generated always as identity (increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> create table ni (x int, y smallint generated always as identity (start with 0, increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> create table ni (x int, y int generated always as identity (increment by 0); ERROR 42X01: Syntax error: Encountered "" at line 1, column 75. ij> create table ni (x int, y int generated always as identity (start with 0, increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> create table ni (x int, y bigint generated always as identity (increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> create table ni (x int, y bigint generated always as identity (start with 0, increment by 0)); ERROR 42Z21: Invalid increment specified for identity for column 'Y'. Increment cannot be zero. ij> -- 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)); 0 rows inserted/updated/deleted ij> drop table ni; 0 rows inserted/updated/deleted ij> create table ni (x int, y int generated always as identity (start with -128)); 0 rows inserted/updated/deleted ij> drop table ni; 0 rows inserted/updated/deleted ij> -- now go through this exercise for all types! create table ni (x int, y smallint generated always as identity (start with 32768)); ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> create table ni (x int, y smallint generated always as identity (start with -32769)); ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> create table ni (x int, y int generated always as identity (start with 2147483648)); ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> create table ni (x int, y int generated always as identity (start with -2147483649)); ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> create table ni (x int, y bigint generated always as identity (start with 9223372036854775808)); ERROR 42X49: Value '9223372036854775808' is not a valid integer literal. ij> create table ni (x int, y bigint generated always as identity (start with -9223372036854775809)); ERROR 42X49: Value '9223372036854775809' is not a valid integer literal. ij> -- attempt to update or insert into autoincrement columns. create table ai (x smallint generated always as identity, y int); 0 rows inserted/updated/deleted ij> insert into ai (y) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 11 rows inserted/updated/deleted ij> select * from ai; X |Y ------------------ 1 |0 2 |1 3 |2 4 |3 5 |4 6 |5 7 |6 8 |7 9 |8 10 |9 11 |10 ij> delete from ai where y=8 OR y=4; 2 rows inserted/updated/deleted ij> insert into ai (y) values (11),(13),(14),(15),(17),(18),(19); 7 rows inserted/updated/deleted ij> select * from ai; X |Y ------------------ 1 |0 2 |1 3 |2 4 |3 6 |5 7 |6 8 |7 10 |9 11 |10 12 |11 13 |13 14 |14 15 |15 16 |17 17 |18 18 |19 ij> -- valid updates. update ai set y=-y; 16 rows inserted/updated/deleted ij> select * from ai order by x; X |Y ------------------ 1 |0 2 |-1 3 |-2 4 |-3 6 |-5 7 |-6 8 |-7 10 |-9 11 |-10 12 |-11 13 |-13 14 |-14 15 |-15 16 |-17 17 |-18 18 |-19 ij> update ai set y=-y; 16 rows inserted/updated/deleted ij> select * from ai order by x; X |Y ------------------ 1 |0 2 |1 3 |2 4 |3 6 |5 7 |6 8 |7 10 |9 11 |10 12 |11 13 |13 14 |14 15 |15 16 |17 17 |18 18 |19 ij> update ai set y=4 where y=3; 1 row inserted/updated/deleted ij> select * from ai order by x; X |Y ------------------ 1 |0 2 |1 3 |2 4 |4 6 |5 7 |6 8 |7 10 |9 11 |10 12 |11 13 |13 14 |14 15 |15 16 |17 17 |18 18 |19 ij> update ai set y=4 where x=3; 1 row inserted/updated/deleted ij> select * from ai order by x; X |Y ------------------ 1 |0 2 |1 3 |4 4 |4 6 |5 7 |6 8 |7 10 |9 11 |10 12 |11 13 |13 14 |14 15 |15 16 |17 17 |18 18 |19 ij> -- error, error! update ai set x=4 where y=3; ERROR 42Z23: Attempt to modify an identity column 'X'. ij> insert into ai values (1,2); ERROR 42Z23: Attempt to modify an identity column 'X'. ij> -- overflow. drop table ai; 0 rows inserted/updated/deleted ij> create table ai (x int, y int generated always as identity (increment by 200000000)); 0 rows inserted/updated/deleted ij> insert into ai (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19); ERROR 42Z24: Overflow occurred in identity value for column 'Y' in table 'AI'. ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> -- should have been rolled back. select * from ai; X |Y ----------------------- ij> -- but the entry in syscolumns has been updated! still can't do inserts. insert into ai (x) values (1); ERROR 42Z24: Overflow occurred in identity value for column 'Y' in table 'AI'. ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> -- more overflow. drop table ai; 0 rows inserted/updated/deleted ij> create table ai (x int, y smallint generated always as identity (start with -32760, increment by -1)); 0 rows inserted/updated/deleted ij> insert into ai (x) values (1),(2),(3),(4),(5),(6),(7),(8); 8 rows inserted/updated/deleted ij> insert into ai (x) values (9),(10); ERROR 42Z24: Overflow occurred in identity value for column 'Y' in table 'AI'. ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> select * from ai; X |Y ------------------ 1 |-32760 2 |-32761 3 |-32762 4 |-32763 5 |-32764 6 |-32765 7 |-32766 8 |-32767 ij> -- try overflow with int and bigint. drop table ai; 0 rows inserted/updated/deleted ij> create table ai (x int, y int generated always as identity (start with 2147483646)); 0 rows inserted/updated/deleted ij> insert into ai (x) values (1); 1 row inserted/updated/deleted ij> insert into ai (x) values (2); 1 row inserted/updated/deleted ij> select * from ai; X |Y ----------------------- 1 |2147483646 2 |2147483647 ij> insert into ai (x) select x from ai; ERROR 42Z24: Overflow occurred in identity value for column 'Y' in table 'AI'. ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> drop table ai; 0 rows inserted/updated/deleted ij> -- 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)); 0 rows inserted/updated/deleted ij> insert into ai (x) values (1),(2); 2 rows inserted/updated/deleted ij> insert into ai (x) values (3); ERROR 42Z24: Overflow occurred in identity value for column 'Y' in table 'AI'. ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> select * from ai; X |Y -------------------------------- 1 |9223372036854775805 2 |9223372036854775806 ij> -- clean up drop table ai; 0 rows inserted/updated/deleted ij> --- alter table... create table base (x int); 0 rows inserted/updated/deleted ij> insert into base values (1),(2),(3),(4),(5),(6); 6 rows inserted/updated/deleted ij> select * from base; X ----------- 1 2 3 4 5 6 ij> -- 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); ERROR 42601: ALTER TABLE statement cannot add an IDENTITY column to a table. ij> alter table base add column y int generated always as identity (start with 10); ERROR 42601: ALTER TABLE statement cannot add an IDENTITY column to a table. ij> alter table base add column y bigint generated always as identity (start with 10); ERROR 42601: ALTER TABLE statement cannot add an IDENTITY column to a table. ij> -- make sure alter table failures above rolled themselves back select * from base; X ----------- 1 2 3 4 5 6 ij> drop table base; 0 rows inserted/updated/deleted ij> -- testing non-reserved keywords: generated, start, always -- should be successful create table always (a int); 0 rows inserted/updated/deleted ij> create table start (a int); 0 rows inserted/updated/deleted ij> create table generated (a int); 0 rows inserted/updated/deleted ij> drop table always; 0 rows inserted/updated/deleted ij> drop table start; 0 rows inserted/updated/deleted ij> drop table generated; 0 rows inserted/updated/deleted ij> -- IDENTITY_VAL_LOCAL function, same as DB2, beetle 5354 drop table t1; 0 rows inserted/updated/deleted ij> create table t1(c1 int generated always as identity, c2 int); 0 rows inserted/updated/deleted ij> -- start insert into t1(c2) values (8); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 ij> select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1; 1 |2 ----------------------------------------------------------------- 2 |0 ij> insert into t1(c2) values (IDENTITY_VAL_LOCAL()); 1 row inserted/updated/deleted ij> select * from t1; C1 |C2 ----------------------- 1 |8 2 |1 ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1; 1 |2 ----------------------------------------------------------------- 3 |1 3 |1 ij> insert into t1(c2) values (8), (9); 2 rows inserted/updated/deleted ij> -- multi-values insert, return value of the function should not change, same as DB2 values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> select * from t1; C1 |C2 ----------------------- 1 |8 2 |1 3 |8 4 |9 ij> insert into t1(c2) select c1 from t1; 4 rows inserted/updated/deleted ij> -- insert with sub-select, return value should not change values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> select * from t1; C1 |C2 ----------------------- 1 |8 2 |1 3 |8 4 |9 5 |1 6 |2 7 |3 8 |4 ij> delete from t1; 8 rows inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> insert into t1(c2) select c1 from t1; 0 rows inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 2 ij> -- end of practice, back to start... insert into t1(c2) values (8); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 9 ij> drop table t1; 0 rows inserted/updated/deleted ij> -- test cases for beetle 5404: inserting multiple rows of defaults into autoincrement column. create table t1(c1 int generated always as identity); 0 rows inserted/updated/deleted ij> -- this is okay insert into t1 values (default); 1 row inserted/updated/deleted ij> select * from t1; C1 ----------- 1 ij> -- should fail insert into t1 values (1), (1); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> select * from t1; C1 ----------- 1 ij> -- this returns the right error insert into t1 values (1), (default); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t1 values (default), (1); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t1 values (default), (default), (default), (2); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t1 values (default), (default), (2); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t1 values (default), (default), (2), (default); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> -- this returns NPE insert into t1 values (default), (default); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- 1 2 3 ij> insert into t1 values (default), (default), (default); 3 rows inserted/updated/deleted ij> select * from t1; C1 ----------- 1 2 3 4 5 6 ij> insert into t1 values (default), (default), (default), (default); 4 rows inserted/updated/deleted ij> select * from t1; C1 ----------- 1 2 3 4 5 6 7 8 9 10 ij> create table t2 (a int, b int generated always as identity); 0 rows inserted/updated/deleted ij> insert into t2 values (1, default), (2, default); 2 rows inserted/updated/deleted ij> select * from t2; A |B ----------------------- 1 |1 2 |2 ij> insert into t2 values (1, default), (2, 2); ERROR 42Z23: Attempt to modify an identity column 'B'. ij> insert into t2 values (1, default), (2, default), (2, 2); ERROR 42Z23: Attempt to modify an identity column 'B'. ij> insert into t2 values (1, 2), (2, default), (2, default); ERROR 42Z23: Attempt to modify an identity column 'B'. ij> create table t3(c1 int generated always as identity (increment by 3)); 0 rows inserted/updated/deleted ij> -- succeeded insert into t3 values (default); 1 row inserted/updated/deleted ij> select * from t3; C1 ----------- 1 ij> insert into t3 values (default); 1 row inserted/updated/deleted ij> select * from t3; C1 ----------- 1 4 ij> -- should fail insert into t3 values (1), (1); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> select * from t3; C1 ----------- 1 4 ij> -- this returns the right error insert into t3 values (1), (default); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 values (default), (1); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 values (default), (default), (default), (2); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 values (default), (default), (2); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 values (default), (default), (2), (default); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 select * from t1; ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 select * from table (values (1)) as q(a); ERROR 42Z23: Attempt to modify an identity column 'C1'. ij> insert into t3 select * from table (values (default)) as q(a); ERROR 42Y85: The DEFAULT keyword is only allowed in a VALUES clause when the VALUES clause appears within an INSERT statement. ij> -- this returns NPE insert into t3 values (default), (default); 2 rows inserted/updated/deleted ij> select * from t3; C1 ----------- 1 4 7 10 ij> insert into t3 values (default), (default), (default); 3 rows inserted/updated/deleted ij> select * from t3; C1 ----------- 1 4 7 10 13 16 19 ij> insert into t3 values (default), (default), (default), (default); 4 rows inserted/updated/deleted ij> select * from t3; C1 ----------- 1 4 7 10 13 16 19 22 25 28 31 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> -- Defaults/always -- without increment option create table t1(i int, t1_autogen int generated always as identity); 0 rows inserted/updated/deleted ij> create table t2(i int, t2_autogen int generated by default as identity); 0 rows inserted/updated/deleted ij> insert into t1(i) values(1); 1 row inserted/updated/deleted ij> insert into t1(i) values(1); 1 row inserted/updated/deleted ij> select * from t1; I |T1_AUTOGEN ----------------------- 1 |1 1 |2 ij> insert into t2(i) values(1); 1 row inserted/updated/deleted ij> insert into t2(i) values(1); 1 row inserted/updated/deleted ij> select * from t2; I |T2_AUTOGEN ----------------------- 1 |1 1 |2 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> create table t1(i int, t1_autogen int generated always as identity); 0 rows inserted/updated/deleted ij> create table t2(i int, t2_autogen int generated by default as identity); 0 rows inserted/updated/deleted ij> insert into t1(i,t1_autogen) values(2,1); ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. ij> insert into t1(i,t1_autogen) values(2,2); ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. ij> insert into t1(i) values(2); 1 row inserted/updated/deleted ij> insert into t1(i) values(2); 1 row inserted/updated/deleted ij> select * from t1; I |T1_AUTOGEN ----------------------- 2 |1 2 |2 ij> insert into t2(i,t2_autogen) values(2,1); 1 row inserted/updated/deleted ij> insert into t2(i,t2_autogen) values(2,2); 1 row inserted/updated/deleted ij> insert into t2(i) values(2); 1 row inserted/updated/deleted ij> insert into t2(i) values(2); 1 row inserted/updated/deleted ij> select * from t2; I |T2_AUTOGEN ----------------------- 2 |1 2 |2 2 |1 2 |2 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> --with increment by create table t1(i int, t1_autogen int generated always as identity(increment by 10)); 0 rows inserted/updated/deleted ij> create table t2(i int, t2_autogen int generated by default as identity(increment by 10)); 0 rows inserted/updated/deleted ij> insert into t1(i) values(1); 1 row inserted/updated/deleted ij> insert into t1(i) values(1); 1 row inserted/updated/deleted ij> select * from t1; I |T1_AUTOGEN ----------------------- 1 |1 1 |11 ij> insert into t2(i) values(1); 1 row inserted/updated/deleted ij> insert into t2(i) values(1); 1 row inserted/updated/deleted ij> select * from t2; I |T2_AUTOGEN ----------------------- 1 |1 1 |11 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> create table t1(i int, t1_autogen int generated always as identity(increment by 10)); 0 rows inserted/updated/deleted ij> create table t2(i int, t2_autogen int generated by default as identity(increment by 10)); 0 rows inserted/updated/deleted ij> insert into t1(i,t1_autogen) values(2,1); ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. ij> insert into t1(i,t1_autogen) values(2,2); ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. ij> insert into t1(i) values(2); 1 row inserted/updated/deleted ij> insert into t1(i) values(2); 1 row inserted/updated/deleted ij> select * from t1; I |T1_AUTOGEN ----------------------- 2 |1 2 |11 ij> insert into t2(i,t2_autogen) values(2,1); 1 row inserted/updated/deleted ij> insert into t2(i,t2_autogen) values(2,2); 1 row inserted/updated/deleted ij> insert into t2(i) values(2); 1 row inserted/updated/deleted ij> insert into t2(i) values(2); 1 row inserted/updated/deleted ij> select * from t2; I |T2_AUTOGEN ----------------------- 2 |1 2 |2 2 |1 2 |11 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> --with start with, increment by create table t1(i int, t1_autogen int generated always as identity(start with 100, increment by 20)); 0 rows inserted/updated/deleted ij> create table t2(i int, t2_autogen int generated by default as identity(start with 100, increment by 20)); 0 rows inserted/updated/deleted ij> insert into t1(i) values(1); 1 row inserted/updated/deleted ij> insert into t1(i) values(1); 1 row inserted/updated/deleted ij> select * from t1; I |T1_AUTOGEN ----------------------- 1 |100 1 |120 ij> insert into t2(i) values(1); 1 row inserted/updated/deleted ij> insert into t2(i) values(1); 1 row inserted/updated/deleted ij> select * from t2; I |T2_AUTOGEN ----------------------- 1 |100 1 |120 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> create table t1(i int, t1_autogen int generated always as identity(start with 100, increment by 20)); 0 rows inserted/updated/deleted ij> create table t2(i int, t2_autogen int generated by default as identity(start with 100, increment by 20)); 0 rows inserted/updated/deleted ij> insert into t1(i,t1_autogen) values(2,1); ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. ij> insert into t1(i,t1_autogen) values(2,2); ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. ij> insert into t1(i) values(2); 1 row inserted/updated/deleted ij> insert into t1(i) values(2); 1 row inserted/updated/deleted ij> select * from t1; I |T1_AUTOGEN ----------------------- 2 |100 2 |120 ij> insert into t2(i,t2_autogen) values(2,1); 1 row inserted/updated/deleted ij> insert into t2(i,t2_autogen) values(2,2); 1 row inserted/updated/deleted ij> insert into t2(i) values(2); 1 row inserted/updated/deleted ij> insert into t2(i) values(2); 1 row inserted/updated/deleted ij> select * from t2; I |T2_AUTOGEN ----------------------- 2 |1 2 |2 2 |100 2 |120 ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> --with unique constraint create table t3(i int,t3_autogen int generated by default as identity(start with 0, increment by 1) unique); 0 rows inserted/updated/deleted ij> insert into t3(i,t3_autogen) values(1,0); 1 row inserted/updated/deleted ij> insert into t3(i,t3_autogen) values(2,1); 1 row inserted/updated/deleted ij> insert into t3(i) values(3); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'T3'. ij> insert into t3(i) values(4); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'T3'. ij> insert into t3(i) values(5); 1 row inserted/updated/deleted ij> select i,t3_autogen from t3; I |T3_AUTOGEN ----------------------- 1 |0 2 |1 5 |2 ij> drop table t3; 0 rows inserted/updated/deleted ij> --with unique index create table t4(i int,t4_autogen int generated by default as identity(start with 0, increment by 1)); 0 rows inserted/updated/deleted ij> create unique index idx_t4_autogen on t4(t4_autogen); 0 rows inserted/updated/deleted ij> insert into t4(i,t4_autogen) values(1,0); 1 row inserted/updated/deleted ij> insert into t4(i,t4_autogen) values(2,1); 1 row inserted/updated/deleted ij> insert into t4(i) values(3); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'IDX_T4_AUTOGEN' defined on 'T4'. ij> insert into t4(i) values(4); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'IDX_T4_AUTOGEN' defined on 'T4'. ij> insert into t4(i) values(5); 1 row inserted/updated/deleted ij> select i,t4_autogen from t4; I |T4_AUTOGEN ----------------------- 1 |0 2 |1 5 |2 ij> drop index idx_t4_autogen; 0 rows inserted/updated/deleted ij> drop table t4; 0 rows inserted/updated/deleted ij> -- test IDENTITY_VAL_LOCAL function with 2 different connections -- connection one connect 'wombat' as conn1; ij(CONN1)> create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int); 0 rows inserted/updated/deleted ij(CONN1)> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int); 0 rows inserted/updated/deleted ij(CONN1)> -- 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(); 1 ------------------------------- NULL ij(CONN1)> commit; ij(CONN1)> -- connection two connect 'wombat' as conn2; ij(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(); 1 ------------------------------- NULL ij(CONN2)> insert into t2 (c22) values (1); 1 row inserted/updated/deleted ij(CONN2)> -- 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(); 1 ------------------------------- 201 ij(CONN2)> set connection conn1; ij(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(); 1 ------------------------------- NULL ij(CONN1)> insert into t1 (c12) values (1); 1 row inserted/updated/deleted ij(CONN1)> -- 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(); 1 ------------------------------- 101 ij(CONN1)> set connection conn2; ij(CONN2)> -- IDENTITY_VAL_LOCAL() on conn2 not impacted by single row insert into table with identity column on conn1 values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 201 ij(CONN2)> -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL() commit; ij(CONN2)> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 201 ij(CONN2)> -- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL() values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 201 ij(CONN2)> drop table t1; 0 rows inserted/updated/deleted ij(CONN2)> drop table t2; 0 rows inserted/updated/deleted ij(CONN2)> -- 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); 0 rows inserted/updated/deleted ij(CONN2)> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int); 0 rows inserted/updated/deleted ij(CONN2)> create trigger t1tr1 after insert on t1 for each row insert into t2 (c22) values (1); 0 rows inserted/updated/deleted ij(CONN2)> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 201 ij(CONN2)> insert into t1 (c12) values (1); 1 row inserted/updated/deleted ij(CONN2)> -- 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(); 1 ------------------------------- 101 ij(CONN2)> select * from t1; C11 |C12 ----------------------- 101 |1 ij(CONN2)> select * from t2; C21 |C22 ----------------------- 201 |1 ij(CONN2)> drop table t1; 0 rows inserted/updated/deleted ij(CONN2)> drop table t2; 0 rows inserted/updated/deleted ij(CONN2)> -- Test RESTART WITH syntax of ALTER TABLE for autoincrment columns create table t1(c11 int generated by default as identity(start with 2, increment by 2), c12 int); 0 rows inserted/updated/deleted ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |2 |2 |2 ij(CONN2)> insert into t1 values(2,2); 1 row inserted/updated/deleted ij(CONN2)> select * from t1; C11 |C12 ----------------------- 2 |2 ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |2 |2 |2 ij(CONN2)> insert into t1(c12) values(9999); 1 row inserted/updated/deleted ij(CONN2)> select * from t1; C11 |C12 ----------------------- 2 |2 2 |9999 ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |4 |2 |2 ij(CONN2)> -- try RESTART WITH on a non-autoincrement column. It should fail alter table t1 alter column c12 RESTART WITH 2; ERROR 42837: ALTER TABLE '"APP"."T1"' specified attributes for column 'C12' that are not compatible with the existing column. ij(CONN2)> -- try RESTART WITH with a non-integer column alter table t1 alter column c11 RESTART WITH 2.20; ERROR 42X49: Value '2.20' is not a valid integer literal. ij(CONN2)> alter table t1 alter column c11 RESTART WITH 2; 0 rows inserted/updated/deleted ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |2 |2 |2 ij(CONN2)> autocommit off; ij(CONN2)> drop table t1; 0 rows inserted/updated/deleted ij(CONN2)> create table t1(c11 int generated by default as identity (start with 1, increment by 1), c12 int); 0 rows inserted/updated/deleted ij(CONN2)> --following puts locks on system table SYSCOLUMNS's row for t1.c11 --Later when a user tries to have the system generate a value for the --t1.c11, system can't generate that value in a transaction of it's own --and hence it reverts to the user transaction to generate the next value. --This use of user transaction to generate a value can be problematic if --user statement to generate the next value runs into statement rollback. --This statement rollback will cause the next value generation to rollback --too and system will not be able to consume the generated value. --In a case like this, user can use ALTER TABLE....RESTART WITH to change the --start value of the autoincrement column as shown below. create unique index t1i1 on t1(c11); 0 rows inserted/updated/deleted ij(CONN2)> insert into t1 values(1,1); 1 row inserted/updated/deleted ij(CONN2)> select * from t1; C11 |C12 ----------------------- 1 |1 ij(CONN2)> -- you will notice that the next value for generated column is 1 at this point select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |1 |1 |1 ij(CONN2)> insert into t1(c12) values(3); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'T1I1' defined on 'T1'. ij(CONN2)> -- the insert above fails as expected because there is already a *1* in the table. --But the generated value doesn't get consumed and following select will still show --next value for generated column as 1. If autocommit was set to on, you would see -- the next generated value at this point to be 2. select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |1 |1 |1 ij(CONN2)> select * from t1; C11 |C12 ----------------------- 1 |1 ij(CONN2)> --the following insert will keep failing because it is going to use 1 as the generated --value for c11 again and that will cause unique key violation insert into t1(c12) values(3); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'T1I1' defined on 'T1'. ij(CONN2)> select * from t1; C11 |C12 ----------------------- 1 |1 ij(CONN2)> --User can change the RESTART WITH for autoincrement column to say 2 at this point, --and then the insert above will not fail alter table t1 alter column c11 restart with 2; 0 rows inserted/updated/deleted ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'C11'; COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C11 |2 |2 |1 ij(CONN2)> insert into t1(c12) values(3); 1 row inserted/updated/deleted ij(CONN2)> select * from t1; C11 |C12 ----------------------- 1 |1 2 |3 ij(CONN2)> -- Since RESTART is not a reserved keyword, we should be able to create a table with name RESTART create table restart (c11 int); 0 rows inserted/updated/deleted ij(CONN2)> select * from restart; C11 ----------- ij(CONN2)> create table newTable (restart int); 0 rows inserted/updated/deleted ij(CONN2)> select * from newTable; RESTART ----------- ij(CONN2)> create table newTable2 (c11 int); 0 rows inserted/updated/deleted ij(CONN2)> alter table newTable2 add column RESTART int; 0 rows inserted/updated/deleted ij(CONN2)> select * from newTable2; C11 |RESTART ----------------------- ij(CONN2)> -- Verify that if we change the START WITH value for a GENERATED_BY_DEFAULT -- column, the column is still GENERATED_BY_DEFAULT and its INCREMENT BY -- value is preserved CREATE TABLE DERBY_1495 ( id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL ,col2 INT NOT NULL); 0 rows inserted/updated/deleted ij(CONN2)> SELECT col.columndefault, col.columndefaultid, col.autoincrementvalue, col.autoincrementstart, col.autoincrementinc FROM sys.syscolumns col INNER JOIN sys.systables tab ON col.referenceId = tab.tableid WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID'; COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ------------------------------------------------------------------------------------------------------------------- GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1 |1 |1 ij(CONN2)> -- Insert using an explicit value on the ID-field INSERT INTO DERBY_1495(ID, COL2) VALUES(2, 2); 1 row inserted/updated/deleted ij(CONN2)> -- Reset the identity field ALTER TABLE DERBY_1495 ALTER COLUMN id RESTART WITH 3; 0 rows inserted/updated/deleted ij(CONN2)> SELECT col.columndefault, col.columndefaultid, col.autoincrementvalue, col.autoincrementstart, col.autoincrementinc FROM sys.syscolumns col INNER JOIN sys.systables tab ON col.referenceId = tab.tableid WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID'; COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ------------------------------------------------------------------------------------------------------------------- GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|3 |3 |1 ij(CONN2)> INSERT INTO DERBY_1495(ID, COL2) VALUES(4, 4); 1 row inserted/updated/deleted ij(CONN2)> INSERT INTO DERBY_1495(COL2) VALUES(4); 1 row inserted/updated/deleted ij(CONN2)> -- Similarly, verify that if we change the INCREMENT BY value for a -- GENERATED_BY_DEFAULT column, the column remains GENERATED_BY_DEFAULT -- and its START WITH value is preserved. create table derby_1645 ( TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, StringValue VARCHAR(20) not null, constraint PK_derby_1645 primary key (TableId)); 0 rows inserted/updated/deleted ij(CONN2)> SELECT col.columndefault, col.columndefaultid, col.autoincrementvalue, col.autoincrementstart, col.autoincrementinc FROM sys.syscolumns col INNER JOIN sys.systables tab ON col.referenceId = tab.tableid WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID'; COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ------------------------------------------------------------------------------------------------------------------- GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1 |1 |1 ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (1, 'test1'); 1 row inserted/updated/deleted ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (2, 'test2'); 1 row inserted/updated/deleted ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (3, 'test3'); 1 row inserted/updated/deleted ij(CONN2)> ALTER TABLE derby_1645 ALTER TableId SET INCREMENT BY 50; 0 rows inserted/updated/deleted ij(CONN2)> SELECT col.columndefault, col.columndefaultid, col.autoincrementvalue, col.autoincrementstart, col.autoincrementinc FROM sys.syscolumns col INNER JOIN sys.systables tab ON col.referenceId = tab.tableid WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID'; COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ------------------------------------------------------------------------------------------------------------------- GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|53 |1 |50 ij(CONN2)> INSERT INTO derby_1645 (StringValue) VALUES ('test53'); 1 row inserted/updated/deleted ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (-999, 'test3'); 1 row inserted/updated/deleted ij(CONN2)> -- Test cases related to DERBY-1644, which involve: -- a) multi-row VALUES clauses -- b) GENERATED BY DEFAULT autoincrement fields -- c) insert statements which mention only a subset of the table's columns -- First we have the actual case from the bug report. Then we have a number -- of other similar cases, to try to cover the code area in question create table D1644 (c1 int, c2 int generated by default as identity); 0 rows inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values default, 10; 2 rows inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values (11); 1 row inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values default; 1 row inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values (default); 1 row inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values 12, 13, 14; 3 rows inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values 15, 16, default; 3 rows inserted/updated/deleted ij(CONN2)> insert into D1644 values (17, 18); 1 row inserted/updated/deleted ij(CONN2)> insert into D1644 values (19, default); 1 row inserted/updated/deleted ij(CONN2)> insert into D1644 values (20, default), (21, 22), (23, 24), (25, default); 4 rows inserted/updated/deleted ij(CONN2)> insert into D1644 (c2, c1) values (default, 26); 1 row inserted/updated/deleted ij(CONN2)> insert into D1644 (c2, c1) values (27, 28), (default, 29), (30, 31); 3 rows inserted/updated/deleted ij(CONN2)> insert into D1644 (c2) values default, default, default, default; 4 rows inserted/updated/deleted ij(CONN2)> insert into D1644 (c2, c1) values (default, 128),(default, 129),(default, 131); 3 rows inserted/updated/deleted ij(CONN2)> select * from D1644; C1 |C2 ----------------------- NULL |1 NULL |10 NULL |11 NULL |2 NULL |3 NULL |12 NULL |13 NULL |14 NULL |15 NULL |16 NULL |4 17 |18 19 |5 20 |6 21 |22 23 |24 25 |7 26 |8 28 |27 29 |9 31 |30 NULL |10 NULL |11 NULL |12 NULL |13 128 |14 129 |15 131 |16 ij(CONN2)> create table D1644_A (c1 int, c2 int generated by default as identity, c3 int); 0 rows inserted/updated/deleted ij(CONN2)> insert into D1644_A (c3, c1, c2) values (1, 2, default); 1 row inserted/updated/deleted ij(CONN2)> insert into D1644_A (c3, c1, c2) values (3,4,5), (6,7,default); 2 rows inserted/updated/deleted ij(CONN2)> insert into D1644_A (c3, c2) values (8, default), (9, 10); 2 rows inserted/updated/deleted ij(CONN2)> select * from D1644_A; C1 |C2 |C3 ----------------------------------- 2 |1 |1 4 |5 |3 7 |2 |6 NULL |3 |8 NULL |10 |9 ij(CONN2)> create table D1644_B (c1 int generated by default as identity); 0 rows inserted/updated/deleted ij(CONN2)> insert into D1644_B (c1) values default, 10; 2 rows inserted/updated/deleted ij(CONN2)> insert into D1644_B values default, 10; 2 rows inserted/updated/deleted ij(CONN2)> select * from D1644_B; C1 ----------- 1 10 2 10 ij(CONN2)> -- Derby-2902: can't use LONG.MIN_VALUE as the start value for -- an identity column. These tests verify that values less than MIN_VALUE -- or greater than MAX_VALUE are rejected, but MIN_VALUE and MAX_VALUE -- themeselves are accepted. create table t2902_a (c1 bigint generated always as identity (start with -9223372036854775807)); 0 rows inserted/updated/deleted ij(CONN2)> create table t2902_b (c1 bigint generated always as identity (start with +9223372036854775807)); 0 rows inserted/updated/deleted ij(CONN2)> create table t2902_c (c1 bigint generated always as identity (start with -9223372036854775808)); 0 rows inserted/updated/deleted ij(CONN2)> create table t2902_d (c1 bigint generated always as identity (start with 9223372036854775808)); ERROR 42X49: Value '9223372036854775808' is not a valid integer literal. ij(CONN2)> create table t2902_e (c1 bigint generated always as identity (start with -9223372036854775809)); ERROR 42X49: Value '9223372036854775809' is not a valid integer literal. ij(CONN2)> drop table t2902_a; 0 rows inserted/updated/deleted ij(CONN2)> drop table t2902_b; 0 rows inserted/updated/deleted ij(CONN2)> drop table t2902_c; 0 rows inserted/updated/deleted ij(CONN2)>