identifier.out

derby database source code.good for you.

ij> --
-- this test is for identifiers and delimited idenifiers
-- identifiers get converted to upper case
-- delimited identifiers have their surrounding double quotes removed and
-- any pair of adjacent double quotes is converted to a single double quote
-- max identifier length is 128
--
-- trailing blank not trimmed
create table t1("       " int);
0 rows inserted/updated/deleted
ij> -- duplicate identifiers
create table t1 (c1 int, C1 int);
ERROR 42X12: Column name 'C1' appears more than once in the CREATE TABLE statement.  
ij> -- duplicate identifier/delimited identifier
create table t1 (c1 int, "C1" int);
ERROR 42X12: Column name 'C1' appears more than once in the CREATE TABLE statement.  
ij> -- duplicate delimited identifier/identifier
create table t1 ("C1" int, C1 int);
ERROR 42X12: Column name 'C1' appears more than once in the CREATE TABLE statement.  
ij> -- duplicate delimited identifiers
create table t1 ("C1" int, "C1" int);
ERROR 42X12: Column name 'C1' appears more than once in the CREATE TABLE statement.  
ij> -- verify preservation of spaces
create table success1 (c1 int, " C1" int, "  C1  " int);
0 rows inserted/updated/deleted
ij> -- verify correct handling of case
create table success2 ("c1" int, "C1" int);
0 rows inserted/updated/deleted
ij> create table success3 (c1 int, "c1" int);
0 rows inserted/updated/deleted
ij> -- verify correct handling of double quotes
create table success4 ("C1""" int, "C1""""" int);
0 rows inserted/updated/deleted
ij> -- verify correct handling in an insert
insert into success1 (c1, " C1", "  C1  ") 
values (1, 2, 3);
1 row inserted/updated/deleted
ij> insert into success1 (C1, " C1", "  C1  ") 
values (6, 7, 8);
1 row inserted/updated/deleted
ij> -- negative testing for an insert
-- "c1 " is not in success1
insert into success1 (c1, "c1 ", " C1", " C1 ", "  C1  ") 
values (11, 12, 13, 14, 15);
ERROR 42X14: 'c1 ' is not a column in table or VTI 'APP.SUCCESS1'.
ij> -- C1 appears twice in the column list - C1 and "C1"
insert into success1 (C1, "C1", " C1", " C1 ", "  C1  ") 
values (16, 17, 18, 19, 20);
ERROR 42X13: Column name 'C1' appears more than once times in the column list of an INSERT statement. 
ij> -- verify correct handling in a select
select C1, " C1", " C1", "  C1  " from success1;
C1         | C1        | C1        |  C1       
-----------------------------------------------
1          |2          |2          |3          
6          |7          |7          |8          
ij> -- following should fail for "C1 "
select c1, "C1 ", " C1", " C1 ", "  C1  " from success1;
ERROR 42X04: Column 'C1 ' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C1 ' is not a column in the target table.
ij> -- negative testing for an insert
-- "c1 " should not match
select c1, "c1 ", " C1", " C1 ", "  C1  "  from success1;
ERROR 42X04: Column 'c1 ' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'c1 ' is not a column in the target table.
ij> -- negative test for max identifier width
-- NOTE: no negative test for max identifier length of function, savepoint and cursor
-- tables needed for index, trigger and view test
create table idtest1 (i integer, j integer);
0 rows inserted/updated/deleted
ij> create table idtest2 (i integer, j integer);
0 rows inserted/updated/deleted
ij> -- table
create table
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast6
(c1 int);
ERROR 42622: The name 'ASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASDFASLAST6' is too long. The maximum length is '128'.
ij> create table
"asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast7"
(c1 int);
ERROR 42622: The name 'asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast7' is too long. The maximum length is '128'.
ij> -- column
create table fail1 (ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccx integer);
ERROR 42622: The name 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCX' is too long. The maximum length is '128'.
ij> create table fail2 ("ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccx" integer);
ERROR 42622: The name 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccx' is too long. The maximum length is '128'.
ij> -- view
create view vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvx as select * from idtest1;
ERROR 42622: The name 'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVX' is too long. The maximum length is '128'.
ij> create view "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvy" as select * from idtest1;
ERROR 42622: The name 'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvy' is too long. The maximum length is '128'.
ij> -- trigger
create trigger ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
ERROR 42622: The name 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTX' is too long. The maximum length is '128'.
ij> create trigger "ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx" after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
ERROR 42622: The name 'ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx' is too long. The maximum length is '128'.
ij> -- schema
create schema ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssx;
ERROR 42622: The name 'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSX' is too long. The maximum length is '128'.
ij> create schema "ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssx";
ERROR 42622: The name 'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssx' is too long. The maximum length is '128'.
ij> -- index
CREATE INDEX iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiix 
ON idtest1 (i);
ERROR 42622: The name 'IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIX' is too long. The maximum length is '128'.
ij> CREATE INDEX "iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiix" 
ON idtest1 (j);
ERROR 42622: The name 'iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiix' is too long. The maximum length is '128'.
ij> -- constraint
create table fail3 (i integer, constraint ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccx check (i > 0));
ERROR 42622: The name 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCX' is too long. The maximum length is '128'.
ij> create table fail4 (i integer, constraint "ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccx" check (i > 0));
ERROR 42622: The name 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccx' is too long. The maximum length is '128'.
ij> --- procedure
create procedure ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppx(in i integer) external name 'a.b.c.d' language java parameter style java;
ERROR 42622: The name 'PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPX' is too long. The maximum length is '128'.
ij> create procedure "ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppx"(in i integer) external name 'a.b.c.d' language java parameter style java;
ERROR 42622: The name 'ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppx' is too long. The maximum length is '128'.
ij> -- positive test for max identifier width
-- NOTE: no positive test for max identifier length of function, savepoint and cursor
-- table
create table
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast
(c1 int);
0 rows inserted/updated/deleted
ij> insert into 
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast
values (1);
1 row inserted/updated/deleted
ij> select * from 
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast;
C1         
-----------
1          
ij> create table
"delimitedsdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast"
(c1 int);
0 rows inserted/updated/deleted
ij> insert into 
"delimitedsdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast"
values (2);
1 row inserted/updated/deleted
ij> select * from 
"delimitedsdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfaslast";
C1         
-----------
2          
ij> -- column
create table longid1 (cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc integer);
0 rows inserted/updated/deleted
ij> create table longid2 ("cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc" integer);
0 rows inserted/updated/deleted
ij> -- view
create view vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv as select * from idtest1;
0 rows inserted/updated/deleted
ij> create view "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvw" as select * from idtest1;
0 rows inserted/updated/deleted
ij> -- trigger
create trigger tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
0 rows inserted/updated/deleted
ij> create trigger "tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt" after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
0 rows inserted/updated/deleted
ij> -- schema
create schema ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss;
0 rows inserted/updated/deleted
ij> create schema "ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss";
0 rows inserted/updated/deleted
ij> -- index
CREATE INDEX iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 
ON idtest1 (i);
0 rows inserted/updated/deleted
ij> CREATE INDEX "iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii" 
ON idtest1 (j);
0 rows inserted/updated/deleted
ij> -- constraint
create table longid3 (i integer, constraint cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc check (i > 0));
0 rows inserted/updated/deleted
ij> create table longid4 (i integer, constraint "cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc" check (i > 0));
0 rows inserted/updated/deleted
ij> --- procedure
create procedure pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp(in i integer) external name 'a.b.c.d' language java parameter style java;
0 rows inserted/updated/deleted
ij> create procedure "pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp"(in i integer) external name 'a.b.c.d' language java parameter style java;
0 rows inserted/updated/deleted
ij> -- drop the tables etc.
drop view vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv;
0 rows inserted/updated/deleted
ij> drop view "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvw";
0 rows inserted/updated/deleted
ij> drop trigger tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt;
0 rows inserted/updated/deleted
ij> drop trigger "tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt";
0 rows inserted/updated/deleted
ij> drop schema ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss restrict;
0 rows inserted/updated/deleted
ij> drop schema "ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss" restrict;
0 rows inserted/updated/deleted
ij> drop index iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii;
0 rows inserted/updated/deleted
ij> drop index "iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii";
0 rows inserted/updated/deleted
ij> drop table success1;
0 rows inserted/updated/deleted
ij> drop table success2;
0 rows inserted/updated/deleted
ij> drop table success3;
0 rows inserted/updated/deleted
ij> drop table success4;
0 rows inserted/updated/deleted
ij> drop table idtest1;
0 rows inserted/updated/deleted
ij> drop table idtest2;
0 rows inserted/updated/deleted
ij> drop table longid1;
0 rows inserted/updated/deleted
ij> drop table longid2;