db2compatibility.out

derby database source code.good for you.

ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE10' because it does not exist.
ij> drop table testtype11;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE11' because it does not exist.
ij> drop table beetle5426;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'BEETLE5426' because it does not exist.
ij> drop table testtype12;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE12' because it does not exist.
ij> drop class alias Tours;
ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6.
ij> drop table testtype13;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE13' because it does not exist.
ij> -- limit char to 254 and varchar to 32672 columns in db2 mode
-- beetle 5552
-- following will fail because char length > 254
create table test1(col1 char(255));
ERROR 42611: The length, precision, or scale attribute for column, or type mapping 'CHAR(255)' is not valid. 
ij> -- following will pass because char length <= 254
create table test1(col1 char(254), col2 char(23));
0 rows inserted/updated/deleted
ij> -- try truncation error with the 2 chars
-- the trailing blanks will not give error
insert into test1 values('a','abcdefghijklmnopqrstuvw   ');
1 row inserted/updated/deleted
ij> -- the trailing non-blank characters will give error
insert into test1 values('a','abcdefghijklmnopqrstuvwxyz');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'abcdefghijklmnopqrstuvwxyz' to length 23.
ij> insert into test1 values('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','a');
ERROR 22001: A truncation error was encountered trying to shrink CHAR '123456789012345678901234567890123456789012345678901234567890&' to length 254.
ij> drop table test1;
0 rows inserted/updated/deleted
ij> -- following will fail because varchar length > 32672
create table test1(col1 varchar(32673));
ERROR 42611: The length, precision, or scale attribute for column, or type mapping 'VARCHAR(32673)' is not valid. 
ij> -- following will pass because varchar length <= 32672
create table test1(col1 varchar(32672), col2 varchar(1234));
0 rows inserted/updated/deleted
ij> drop table test1;
0 rows inserted/updated/deleted
ij> -- SET CONSTRAINTS statement
-- beetle 5251
CREATE TABLE testsetconst1 (col1 CHAR(7) NOT NULL, PRIMARY KEY(col1));
0 rows inserted/updated/deleted
ij> CREATE TABLE testsetconst2 (col1 char(7) NOT NULL, CONSTRAINT fk FOREIGN KEY(col1) REFERENCES testsetconst1(col1));
0 rows inserted/updated/deleted
ij> SET CONSTRAINTS fk DISABLED;
ERROR 42X01: Syntax error: Encountered "CONSTRAINTS" at line 1, column 5.
ij> SELECT STATE FROM SYS.SYSCONSTRAINTS;
&
-
E
E
ij> SET CONSTRAINTS fk ENABLED;
ERROR 42X01: Syntax error: Encountered "CONSTRAINTS" at line 1, column 5.
ij> SELECT STATE FROM SYS.SYSCONSTRAINTS;
&
-
E
E
ij> SET CONSTRAINTS ALL DISABLED;
ERROR 42X01: Syntax error: Encountered "CONSTRAINTS" at line 1, column 5.
ij> SELECT STATE FROM SYS.SYSCONSTRAINTS;
&
-
E
E
ij> SET CONSTRAINTS FOR testsetconst1 ENABLED;
ERROR 42X01: Syntax error: Encountered "CONSTRAINTS" at line 1, column 5.
ij> SELECT STATE FROM SYS.SYSCONSTRAINTS;
&
-
E
E
ij> -- clean up
DROP TABLE testsetconst1;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'xxxxGENERATED-IDxxxx' because CONSTRAINT 'FK' is dependent on that object.
ij> DROP TABLE testsetconst2;
0 rows inserted/updated/deleted
ij> -- CALL statement
-- beetle 5252
call org.apache.derby.iapi.db.Factory::getDatabaseOfConnection().dropAllJDBCMetaDataSPSes();
ERROR 42X01: Syntax error: org.apache.derby.iapi.db.Factory::getDatabaseOfConnection.
ij> -- Beetle 5203: DB2 restricts what can be used for default clauses, and enforces
-- constraints on the default clause that Cloudscape does not.
-- Following should be okay:
create table deftest1 (i int default 1);
0 rows inserted/updated/deleted
ij> create table deftest2 (vc varchar(30) default 'howdy');
0 rows inserted/updated/deleted
ij> create table deftest21 (vc clob(10) default 'okie');
0 rows inserted/updated/deleted
ij> create table deftest3 (d date default current date);
0 rows inserted/updated/deleted
ij> create table deftest31 (d date default '2004-02-08');
0 rows inserted/updated/deleted
ij> create table deftest5 (vc char(130) default current schema);
0 rows inserted/updated/deleted
ij> create table deftest4 (c char(130) default user);
0 rows inserted/updated/deleted
ij> create table deftest6 (d decimal(5,2) default null);
0 rows inserted/updated/deleted
ij> create table deftest7 (d decimal(5,2) default 123.450);
0 rows inserted/updated/deleted
ij> create table deftest8 (f float default 1.234);
0 rows inserted/updated/deleted
ij> -- make sure they actually work @ insertion.
insert into deftest1 values (default);
1 row inserted/updated/deleted
ij> insert into deftest2 values (default);
1 row inserted/updated/deleted
ij> insert into deftest21 values (default);
1 row inserted/updated/deleted
ij> insert into deftest3 values (default);
1 row inserted/updated/deleted
ij> insert into deftest31 values (default);
1 row inserted/updated/deleted
ij> insert into deftest4 values (default);
1 row inserted/updated/deleted
ij> insert into deftest5 values (default);
1 row inserted/updated/deleted
ij> insert into deftest6 values (default);
1 row inserted/updated/deleted
ij> insert into deftest7 values (default);
1 row inserted/updated/deleted
ij> insert into deftest8 values (default);
1 row inserted/updated/deleted
ij> -- cleanup.
drop table deftest1;
0 rows inserted/updated/deleted
ij> drop table deftest2;
0 rows inserted/updated/deleted
ij> drop table deftest21;
0 rows inserted/updated/deleted
ij> drop table deftest3;
0 rows inserted/updated/deleted
ij> drop table deftest31;
0 rows inserted/updated/deleted
ij> drop table deftest4;
0 rows inserted/updated/deleted
ij> drop table deftest5;
0 rows inserted/updated/deleted
ij> drop table deftest6;
0 rows inserted/updated/deleted
ij> drop table deftest7;
0 rows inserted/updated/deleted
ij> drop table deftest8;
0 rows inserted/updated/deleted
ij> -- Beetle 5203, con't: following should all fail (though they'd pass in Cloudscape mode).
-- expressions:
create table deftest1 (vc varchar(30) default java.lang.Integer::toBinaryString(3));
ERROR 42X01: Syntax error: Encountered "java" at line 3, column 47.
ij> create table deftest2 (i int default 3+4);
ERROR 42X01: Syntax error: Encountered "+" at line 1, column 39.
ij> -- floating point assignment to non-float column.
create table deftest3 (i int default 1.234);
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'I'.
ij> -- decimal value with too much precision.
create table deftest4 (d decimal(5,2) default 1.2234);
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'D'.
ij> -- char constant longer than 254.
create table deftest5 (vc varchar(300) default 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'VC'.
ij> -- function calls (built-in and other) should fail with error 42894 (NOT with 42X01), to match DB2.
create table t1 (i int default abs(0));
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'I'.
ij> create table t1 (i int default someFunc('hi'));
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'I'.
ij> -- Type mismatches should fail with 42894 (NOT with 42821), to match DB2.
create table t1 (i int default 'hi');
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'I'.
ij> -- Beetle 5281: <cast-function> for a default.
-- Date-time functions (DATE, TIME, and TIMESTAMP)
create table t1a (d date default date(current date));
0 rows inserted/updated/deleted
ij> create table t1b (d date default date('1978-03-22'));
0 rows inserted/updated/deleted
ij> create table t2a (t time default time(current time));
0 rows inserted/updated/deleted
ij> create table t2b (t time default time('08:28:08'));
0 rows inserted/updated/deleted
ij> create table t3a (ch timestamp default timestamp(current timestamp));
0 rows inserted/updated/deleted
ij> create table t3b (ts timestamp default timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'));
0 rows inserted/updated/deleted
ij> -- BLOB function (not yet supported).
create table t4 (b blob default blob('nope'));
ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'B'.
ij> -- cleanup.
drop table t1a;
0 rows inserted/updated/deleted
ij> drop table t1b;
0 rows inserted/updated/deleted
ij> drop table t2a;
0 rows inserted/updated/deleted
ij> drop table t2b;
0 rows inserted/updated/deleted
ij> drop table t3a;
0 rows inserted/updated/deleted
ij> drop table t3b;
0 rows inserted/updated/deleted
ij> -- DROP constraint syntax that should be supported in db2 compat mode:
-- beetle 5204
CREATE TABLE testconst1 (col1 CHAR(7) NOT NULL, col2 int CONSTRAINT cc CHECK(col2 > 1), PRIMARY KEY(col1));
0 rows inserted/updated/deleted
ij> CREATE TABLE testconst2 (col1 char(7) NOT NULL, col2 char(7) NOT NULL, col3 int, CONSTRAINT fk FOREIGN KEY(col1) REFERENCES testconst1(col1), CONSTRAINT uk UNIQUE (col2));
0 rows inserted/updated/deleted
ij> -- DROP FOREIGN KEY syntax should be supported in DB2 compat mode
insert into testconst1( col1, col2) values( 'a', 2);
1 row inserted/updated/deleted
ij> insert into testconst1( col1, col2) values( 'a', 2);
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 'TESTCONST1'.
ij> insert into testconst1( col1, col2) values( 'b', 0);
ERROR 23513: The check constraint 'CC' was violated while performing an INSERT or UPDATE on table 'APP.TESTCONST1'.
ij> insert into testconst2( col1, col2, col3) values( 'a', 'a', 1);
1 row inserted/updated/deleted
ij> insert into testconst2( col1, col2, col3) values( 'z', 'b', 1);
ERROR 23503: INSERT on table 'TESTCONST2' caused a violation of foreign key constraint 'FK' for key (z      ).  The statement has been rolled back.
ij> insert into testconst2( col1, col2, col3) values( 'a', 'a', 1);
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 'UK' defined on 'TESTCONST2'.
ij> -- beetle 5204
ALTER TABLE testconst1 DROP FOREIGN KEY cc;
ERROR 42Z9E: Constraint 'CC' is not a FOREIGN KEY constraint.
ij> ALTER TABLE testconst2 DROP UNIQUE fk;
ERROR 42Z9E: Constraint 'FK' is not a UNIQUE constraint.
ij> ALTER TABLE testconst2 DROP CHECK fk;
ERROR 42Z9E: Constraint 'FK' is not a CHECK constraint.
ij> ALTER TABLE testconst2 DROP FOREIGN KEY fk;
0 rows inserted/updated/deleted
ij> -- DROP PRIMARY KEY syntax should be supported in DB2 compat mode
-- beetle 5204
ALTER TABLE testconst1 DROP PRIMARY KEY;
0 rows inserted/updated/deleted
ij> -- DROP UNIQUE KEY syntax should be supported in DB2 compat mode
-- beetle 5204
ALTER TABLE testconst2 DROP UNIQUE uk;
0 rows inserted/updated/deleted
ij> -- DROP CHECK condition syntax should be supported in DB2 compat mode
-- beetle 5204
ALTER TABLE testconst1 DROP CHECK cc;
0 rows inserted/updated/deleted
ij> insert into testconst1( col1, col2) values( 'a', 2);
1 row inserted/updated/deleted
ij> insert into testconst1( col1, col2) values( 'b', 0);
1 row inserted/updated/deleted
ij> insert into testconst2( col1, col2, col3) values( 'z', 'b', 1);
1 row inserted/updated/deleted
ij> insert into testconst2( col1, col2, col3) values( 'a', 'a', 1);
1 row inserted/updated/deleted
ij> ALTER TABLE testconst2 DROP FOREIGN KEY noSuchConstraint;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.NOSUCHCONSTRAINT' on table 'APP.TESTCONST2'. 
ij> ALTER TABLE testconst2 DROP CHECK noSuchConstraint;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.NOSUCHCONSTRAINT' on table 'APP.TESTCONST2'. 
ij> ALTER TABLE testconst2 DROP UNIQUE noSuchConstraint;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.NOSUCHCONSTRAINT' on table 'APP.TESTCONST2'. 
ij> ALTER TABLE testconst1 DROP PRIMARY KEY;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'PRIMARY KEY' on table 'APP.TESTCONST1'. 
ij> -- clean up
DROP TABLE testconst1;