db2compatibility.out

derby database source code.good for you.

0 rows inserted/updated/deleted
ij> CREATE TABLE testdropconst6 (col1 CHAR(7) CONSTRAINT ck1 CHECK (col1 is null), col2 int CONSTRAINT ck2 CHECK (col2 is null));
0 rows inserted/updated/deleted
ij> -- dropping more than one unique-constraint 
alter table testdropconst1 drop primary key drop constraint uk1;
ERROR 42X01: Syntax error: Encountered "drop" at line 2, column 45.
ij> alter table testdropconst2 drop primary key drop constraint uk2 drop constraint uk3;
ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 45.
ij> -- dropping more than one foreign key constraint
alter table testdropconst5 drop constraint fk1 drop constraint fk2;
ERROR 42X01: Syntax error: Encountered "drop" at line 2, column 48.
ij> -- dropping more than one check constraint
alter table testdropconst6 drop constraint ck1 drop constraint ck2;
ERROR 42X01: Syntax error: Encountered "drop" at line 2, column 48.
ij> --clean up
drop table testdropconst1;
0 rows inserted/updated/deleted
ij> drop table testdropconst2;
0 rows inserted/updated/deleted
ij> drop table testdropconst3;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'xxxxGENERATED-IDxxxx' because CONSTRAINT 'FK1' is dependent on that object.
ij> drop table testdropconst4;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'xxxxGENERATED-IDxxxx' because CONSTRAINT 'FK2' is dependent on that object.
ij> drop table testdropconst5;
0 rows inserted/updated/deleted
ij> drop table testdropconst6;
0 rows inserted/updated/deleted
ij> -- (4) altering more than one column
-- beetle 5268
-- syntax that will be supported in db2 compat mode (beetle 5267)
-- db2 compat mode should support 
create table testmodify (col1 varchar(30), col2 varchar(30));
0 rows inserted/updated/deleted
ij> alter table testmodify alter col1 set data type varchar(60) alter col2 set data type varchar(60);
ERROR 42X01: Syntax error: Encountered "alter" at line 1, column 61.
ij> -- clean up
drop table testmodify;
0 rows inserted/updated/deleted
ij> -- number of values assigned in an INSERT statement should be the same as the number of specified or implied columns 
-- beetle 5269
create table t1(a int, b int, c char(10));
0 rows inserted/updated/deleted
ij> -- this statement should throw an error in db2 compat mode, but it does not
insert into t1 values(1);
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> -- beetle 5281
-- These statements are successful in DB2 UDB v8, but not in Cloudscape
-- Cloudscape does not support cast-functions such as blob, timestamp, time, and date
-- DB2 does support cast functions such as these below:
create table t1 (ch blob(10));
0 rows inserted/updated/deleted
ij> insert into t1 values (blob('hmm'));
ERROR 42Y03: 'BLOB' is not recognized as a function or procedure.
ij> create table t2 (ch timestamp);
0 rows inserted/updated/deleted
ij> insert into t2 values (timestamp(current timestamp));
1 row inserted/updated/deleted
ij> create table t3 (ch time);
0 rows inserted/updated/deleted
ij> insert into t3 values (time(current time));
1 row inserted/updated/deleted
ij> create table t4 (ch date);
0 rows inserted/updated/deleted
ij> insert into t4 values (date(current date));
1 row inserted/updated/deleted
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> drop table t4;
0 rows inserted/updated/deleted
ij> -- test operands
-- beetle 5282
-- <,> =, !=, <=, >= operands are not supported in db2 but supported in cloudscape
CREATE TABLE testoperatorclob (colone clob(1K));
0 rows inserted/updated/deleted
ij> INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K)));
1 row inserted/updated/deleted
ij> select * from testoperatorclob;
COLONE                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------
50                                                                                                                              
ij> -- these select statements should raise an error but are successful in cloudscape
select * from testoperatorclob where colone > 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone < 70;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone = 50;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone != 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone <= 70;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone >= 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorclob where colone <> 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> drop table testoperatorclob;
0 rows inserted/updated/deleted
ij> -- beetle 5282
CREATE TABLE testoperatorblob (colone clob(1K));
0 rows inserted/updated/deleted
ij> INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K)));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> select * from testoperatorblob;
COLONE                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------
ij> -- these select statements should raise an error but are successful in cloudscape
select * from testoperatorblob where colone > 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone < 999999;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone = 00350030;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone != 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone <= 999999;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone >= 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> select * from testoperatorblob where colone <> 10;
ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported.
ij> drop table testoperatorblob;
0 rows inserted/updated/deleted
ij> -- beetle 5283
-- casting using "X" for hex constant, "B" literal is not allowed in DB2
-- db2 raises ERROR 56098, cloudscape should raise error msg?a
values cast(B'1' as char(100));
ERROR 42X01: Syntax error: Encountered "\'1\'" at line 4, column 14.
ij> values cast(B'1' as clob(1M));
ERROR 42X01: Syntax error: Encountered "\'1\'" at line 1, column 14.
ij> values cast(B'1' as blob(1M));
ERROR 42X01: Syntax error: Encountered "\'1\'" at line 1, column 14.
ij> values cast(X'11' as char(100));
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.
ij> values cast(X'11' as clob(1M));
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CLOB'.
ij> values cast(X'11' as blob(1M));
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
11                                                                                                                              
ij> -- beetle 5284
-- minor difference in outputs when casting to blob in Cloudscape and DB2. 
values cast('   ' as blob(1M));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> values cast('a' as blob(1M));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> -- beetle 5294
-- diable column names in the characterExpression and escape clause of a LIKE predicate
create table likeable (match_me varchar(10), pattern varchar(10), esc varchar(1));
0 rows inserted/updated/deleted
ij> insert into likeable values ('foo%bar3', 'fooZ%bar3', 'Z');
1 row inserted/updated/deleted
ij> select match_me from likeable where match_me like pattern escape esc;
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> select match_me from likeable where match_me like pattern escape 'Z';
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> drop table likeable;
0 rows inserted/updated/deleted
ij> -- beetle 5298 
-- disable Field Access
VALUES java.lang.Integer::MAX_VALUE;
ERROR 42X01: Syntax error: java.lang.Integer::MAX_VALUE.
ij> VALUES (1)->noSuchField;
ERROR 42X01: Syntax error: Encountered "->" at line 1, column 11.
ij> -- beetle 5299
-- disable Method Invocations 
VALUES (1)->toString();
ERROR 42X01: Syntax error: java.lang.Integer.toString.
ij> VALUES 1.->toString();
ERROR 42X01: Syntax error: java.math.BigDecimal.toString.
ij> VALUES 1..getClass()->toString();
ERROR 42X01: Syntax error: Encountered "1." at line 1, column 8.
ij> create table m5299 (i int, s varchar(10));
0 rows inserted/updated/deleted
ij> insert into m5299 values(1, 'hello');
1 row inserted/updated/deleted
ij> select i.hashCode(), s.indexOf('ll') from m5299;
ERROR 42Y07: Schema 'I' does not exist
ij> select s.indexOf('ll') from m5299;
ERROR 42Y07: Schema 'S' does not exist
ij> drop table m5299;
0 rows inserted/updated/deleted
ij> -- beetle 5307
-- scale of the resulting data type for division 
values(11.0/1111.33);
1                                 
----------------------------------
0.009898050084133425715134118     
ij> values (11111111111111111111111111111.10/1.11);
1                               
--------------------------------
10010010010010010010010010010   
ij> values (11111111111111111111111111111.10/1.1);
1                                 
----------------------------------
10101010101010101010101010101.0   
ij> -- beetle 5346
-- positive test
-- NULLs sort low in Cloudscape, but sort high in DB2 
create table testOrderBy(c1 int);
0 rows inserted/updated/deleted
ij> insert into testOrderBy values (1);
1 row inserted/updated/deleted
ij> insert into testOrderBy values (2);
1 row inserted/updated/deleted
ij> insert into testOrderBy values (null);
1 row inserted/updated/deleted
ij> select * from testOrderBy order by c1;
C1         
-----------
1          
2          
NULL       
ij> drop table testOrderBy;
0 rows inserted/updated/deleted
ij> create table likeable (match_me varchar(10), pattern varchar(10), esc varchar(1), e varchar(1));
0 rows inserted/updated/deleted
ij> insert into likeable values ('foo%bar3', 'fooZ%bar3', 'Z', 'Z');
1 row inserted/updated/deleted
ij> select match_me from likeable where match_me like 'fooZ%bar3' escape 'Z';
MATCH_ME  
----------
foo%bar3  
ij> select match_me from likeable where 'foo%bar3' like 'fooZ%bar3' escape 'Z';
MATCH_ME  
----------
foo%bar3  
ij> select match_me from likeable where 'foo%bar3' like 'foo%';
MATCH_ME  
----------
foo%bar3  
ij> -- SQLSTATE=42824
select match_me from likeable where match_me like pattern escape esc;
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> select match_me from likeable where match_me like pattern escape e;
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> select match_me from likeable where match_me like pattern escape 'Z';
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> select match_me from likeable where match_me like pattern;
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> select match_me from likeable where match_me like e;
ERROR 42824: An operand of LIKE is not a string, or the first operand is not a column.
ij> -- SQLSTATE=22019
select match_me from likeable where match_me like 'fooZ%bar3' escape esc;
ERROR 22019: Invalid escape sequence, 'ESC'. The escape string must be exactly one character. It cannot be a null or more than one character.
ij> select match_me from likeable where match_me like 'fooZ%bar3' escape e;
ERROR 22019: Invalid escape sequence, 'E'. The escape string must be exactly one character. It cannot be a null or more than one character.
ij> -- SQLSTATE=42884
select match_me from likeable where match_me like 'fooZ%bar3' escape 1;
ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found.
ij> select match_me from likeable where match_me like 'f