db2compatibility.sql

derby database source code.good for you.

values cast('a' as blob(1M));

-- 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));
insert into likeable values ('foo%bar3', 'fooZ%bar3', 'Z');
select match_me from likeable where match_me like pattern escape esc;
select match_me from likeable where match_me like pattern escape 'Z';
drop table likeable;

-- beetle 5298 
-- disable Field Access
VALUES java.lang.Integer::MAX_VALUE;
VALUES (1)->noSuchField;


-- beetle 5299
-- disable Method Invocations 
VALUES (1)->toString();
VALUES 1.->toString();
VALUES 1..getClass()->toString();
create table m5299 (i int, s varchar(10));
insert into m5299 values(1, 'hello');
select i.hashCode(), s.indexOf('ll') from m5299;
select s.indexOf('ll') from m5299;
drop table m5299;

-- beetle 5307
-- scale of the resulting data type for division 
values(11.0/1111.33);
values (11111111111111111111111111111.10/1.11);
values (11111111111111111111111111111.10/1.1);

-- beetle 5346
-- positive test
-- NULLs sort low in Cloudscape, but sort high in DB2 
create table testOrderBy(c1 int);
insert into testOrderBy values (1);
insert into testOrderBy values (2);
insert into testOrderBy values (null);
select * from testOrderBy order by c1;
drop table testOrderBy;

create table likeable (match_me varchar(10), pattern varchar(10), esc varchar(1), e varchar(1));
insert into likeable values ('foo%bar3', 'fooZ%bar3', 'Z', 'Z');

select match_me from likeable where match_me like 'fooZ%bar3' escape 'Z';
select match_me from likeable where 'foo%bar3' like 'fooZ%bar3' escape 'Z';
select match_me from likeable where 'foo%bar3' like 'foo%';


-- SQLSTATE=42824
select match_me from likeable where match_me like pattern escape esc;
select match_me from likeable where match_me like pattern escape e;
select match_me from likeable where match_me like pattern escape 'Z';
select match_me from likeable where match_me like pattern;
select match_me from likeable where match_me like e;

-- SQLSTATE=22019
select match_me from likeable where match_me like 'fooZ%bar3' escape esc;
select match_me from likeable where match_me like 'fooZ%bar3' escape e;

-- SQLSTATE=42884
select match_me from likeable where match_me like 'fooZ%bar3' escape 1;
select match_me from likeable where match_me like 'fooZ%bar3' escape 1;
select match_me from likeable where 'foo%bar3' like 1;
select match_me from likeable where 1 like 1;
select match_me from likeable where match_me like 1;
-- beetle 5845
select match_me from likeable where match_me like CURRENT_DATE;
create table likes (dt date, tm time, ts timestamp);
insert into likes values (current_date, current_time, current_timestamp);
insert into likes values ('2004-03-03', current_time, current_timestamp);
select * from likes where dt like '2004-03-0_';
select * from likes where tm like '_8:%:1%';
select * from likes where ts like '2004-04-09 08:5%';

drop table likeable;
drop table likes;

-- READ ONLY not allowed in "FOR" clause of a select.
create table roTable (i int);
insert into roTable values (8);
select * from roTable for update;
select * from roTable for update of i;
select * from roTable for fetch only;
select * from roTable for read only;
drop table roTable;


-- No support for Java types in CAST statements;

values CAST (NULL AS CLASS java.lang.Integer);
values CAST (NULL AS CLASS com.acme.SomeClass);
values CAST (NULL AS CLASS java.sql.Date);

values CAST (NULL AS java.lang.Integer);
values CAST (NULL AS com.acme.SomeClass);
values CAST (NULL AS java.sql.Date);

values CAST (? AS CLASS java.lang.Integer);
values CAST (? AS CLASS com.acme.SomeClass);
values CAST (? AS CLASS java.sql.Date);

values CAST (? AS java.lang.Integer);
values CAST (? AS com.acme.SomeClass);
values CAST (? AS java.sql.Date);

-- No support for BIT_LENGTH, OCTET_LENGTH, TRIP and SUBSTRING in DB2 compatibility mode

values BIT_LENGTH(X'55');
values OCTET_LENGTH('asdfasdfasdf');
values TRIM('x' FROM 'xasdf x');
values SUBSTRING('12345' FROM 3 FOR 2);

-- Tests for explicit nulls. Not allowed in DB2, defect 5589 
-- Should fail.
create table t1 ( i int null);

-- Should pass.
create table t1 (i int);
insert into t1 values null;

-- Alter table add explict null column should also fail.
alter table t1 add column j int null;

-- Should pass
alter table t1 add column j int;
insert into t1 values (null, null);

drop table t1;

-- Beetle 5538: Match DB2 trigger restrictions.

-- Part I) SQL-Procedure-Statement restrictions:

-- 1) BEFORE triggers: can't have CALL, INSERT, UPDATE, or DELETE as action; when beetle 5253 is resolved, thsese should be changed to "no cascade before", instead of just "before".
create table t1 (i int, j int);
create table t2 (i int);
create trigger trig1a NO CASCADE before insert on t1 for each row mode db2sql insert into t2 values(1);
create trigger trig1b NO CASCADE before insert on t1 for each row mode db2sql update t2 set i=1 where i=2;
create trigger trig1c NO CASCADE before insert on t1 for each row mode db2sql delete from t2 where i=8;
create trigger trig1d NO CASCADE before insert on t1 for each row mode db2sql call procOne();

-- 2) AFTER triggers: can't have CALL as action, but others should still work.
create trigger trig2 after insert on t1 for each row mode db2sql call procOne();
create trigger trig2a after insert on t1 for each row mode db2sql insert into t2 values(1);
create trigger trig2b after insert on t1 for each row mode db2sql update t2 set i=1 where i=2;
create trigger trig2c after insert on t1 for each row mode db2sql delete from t2 where i=8;

-- Part II) Verify applicable restrictions on the "REFERENCES" clause (should be the same as in DB2).

-- 3) NEW, NEW_TABLE only valid with insert and update triggers; OLD, OLD_TABLE
--  only valid with delete and update triggers.

-- Next 8 should succeed.
create trigger trig3a after insert on t1 referencing new as ooga for each row mode db2sql values(1);
create trigger trig3b after update on t1 referencing old as ooga for each row mode db2sql values(1);
create trigger trig3c after update on t1 referencing new as ooga for each row mode db2sql values(1);
create trigger trig3d after delete on t1 referencing old as ooga for each row mode db2sql values(1);
create trigger trig3e after insert on t1 referencing new_table as ooga for each statement mode db2sql values(1);
create trigger trig3f after update on t1 referencing old_table as ooga for each statement mode db2sql values(1);
create trigger trig3g after update on t1 referencing new_table as ooga for each statement mode db2sql values(1);
create trigger trig3h after delete on t1 referencing old_table as ooga for each statement mode db2sql values(1);

-- Next 4 should fail.
create trigger trig3i after insert on t1 referencing old as ooga for each row mode db2sql values(1);
create trigger trig3j after delete on t1 referencing new as ooga for each row mode db2sql values(1);
create trigger trig3k after insert on t1 referencing old_table as ooga for each statement mode db2sql values(1);
create trigger trig3m after delete on t1 referencing new_table as ooga for each statement mode db2sql values(1);

-- 4) NEW_TABLE, OLD_TABLE not valid with BEFORE triggers (these will throw syntax errors until beetle 5253 is resolved).
create trigger trig4a no cascade before update on t1 referencing old_table as ooga for each statement mode db2sql values(1);
create trigger trig4b no cascade before update on t1 referencing new_table as ooga for each statement mode db2sql values(1);

-- 5) OLD, NEW not valid with FOR EACH STATEMENT.
create trigger trig5a after update on t1 referencing old as ooga for each statement mode db2sql values(1);
create trigger trig5b after update on t1 referencing new as ooga for each statement mode db2sql values(1);

-- cleanup for 5538:
drop table t1;
drop table t2;

-- Beetle 5637: Require FOR EACH clause in DB2 mode. Optional in Cloudscape mode. 

create table t1(i int);

-- Should fail
create trigger trig1 after insert on t1 mode db2sql values (8);

-- Should pass
create trigger trig1 after insert on t1 for each row mode db2sql values (8);
create trigger trig2 after insert on t1 for each statement mode db2sql values (8);

drop table t1;

-- match SUBSTR builtin function out of range handling (5570).

create table x1 (c char(10));
insert into x1 values ('foo');

-- DB2: Raises ERROR 22011: out of range, Cloudscape doesn't
select substr('foo', -2,1) from x1;

-- DB2: Raises ERROR 22011: out of range, Cloudscape return NULL
select substr('foo', 1,-1) from x1;
select substr('foo', 2,-1) from x1;
select substr('foo', 3,-2) from x1;
select substr('foo', -2,-3) from x1;

-- DB2: ERROR 22011 out of range, Cloudscape returns empty string
select substr('foo', 5) from x1;
select substr('foo', 6,3) from x1;

-- DB2: Raises ERROR 22011: out of range, Cloudscape returns 'f'
select substr('foo', 0,1) from x1;

-- DB2: ERROR 22011 out of range, Cloudscape return 'foo'
select substr('foo', 1,4) from x1;

-- DB2: ERROR 22011 out of range, Cloudscape return 'foo'
select substr('foo', -5) from x1;

-- DB2: ERROR 22011 out of range
select substr('foo', -6,3) from x1;

-- DB2: Returns an empty value, Cloudscape returns NULL
select substr('foo', 1,0) from x1;
select substr('foo', 2,0) from x1;
select substr('foo', 3,0) from x1;

-- DB2: Raises ERROR 22011: out of range, Cloudscape returns NULL
select substr('foo', 4,0) from x1;
select substr('foo', 5,0) from x1;
select substr('foo', 6,0) from x1;

-- Beetle 5630: A column check constraint can only refer to that column in DB2

create table t1(c1 int, c2 int check (c1 > 5));
-- check constraint ck1 in the column-definition of c2 can not refer to column c1
create table t1(c1 int, c2 int constraint ck1 check(c1 > c2));

-- Same test with alter table
create table t1(c1 int);
alter table t1 add column c2 int constraint ck2 check(c2 > c1);

-- These should pass, uses table constraints
create table t2(c1 int, c2 int, check (c1 > 5));
create table t3(i int, j int, check (j > 5));
alter table t1 add column c2 int;
alter table t1 add constraint t1con check(c2 > c1);

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

-- Beetle 5638: DB2 requires matching target and result columns for insert

create table t1 ( i int, j int);
create table t2 ( i int, j int);

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

-- negative tests, mismatch of columns
insert into t1 select i from t2;
insert into t1(i) select * from t2;
insert into t1(i, j) select j from t2;

insert into t1 select * from t2 union select i from t2;
insert into t1 select j from t2 union select j from t2;
insert into t1(i) select * from t2 union all select * from t2;
insert into t1(i, j) select i, j from t2 union all i from t2;

-- positive cases
insert into t1 select * from t2;
select * from t1;
insert into t1(i,j) select * from t2 union select i, j from t2;
insert into t1(i) select i from t2 union all select j from t2;
select * from t1;

drop table t1;
drop table t2;

-- Beetle 5667: DB2 requires non-nullable columns to have a default in ALTER TABLE

create table t1( i int);

-- Negative cases
alter table t1 add column j int not null;
alter table t1 add column j int not null default null;

-- positive cases
alter table t1 add column j int;
alter table t1 add column k int not null default 5;

drop table t1;

-- IS [NOT] TRUE/FALSE/UNKNOWN not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error
-- 
create table t1( i int);
select * from t1 where ((1=1) IS TRUE);
select * from t1 where ((1=1) IS NOT TRUE);
select * from t1 where ((1=0) IS FALSE);
select * from t1 where ((1=0) IS NOT FALSE);
select * from t1 where (null IS UNKNOWN);
drop table t1;

-- Beetle 5635, 5645 and 5633: Generated column name issues

create table t1(i int, j int);
create table t2(c1 int, c2 int);

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

-- Cloudscape should generate column names when both sides of union don't match
 
select i,j from t1
union all
select c1,c2 from t2
order by 1;

select i as c1, j as c2 from t1
union all
select c1, c2 from t2
order by 1;

-- Prevent Cloudscape from using generated column names for ordering

select i+1 from t1 order by "SQLCol1";
select i+1 from t1 order by SQLCol1;

values (1,2,3),(4,5,6),(7,8,9) order by "SQLCol1";

-- Column names for a CREATE VIEW should be specified when result table has unnamed columns. 

create view v1 as values 1;
create view v1 as select i+1 from t1;
create view v1 as select i+1 as i from t1;
create view v2(c) as select i+1 from t1;

drop view v1;
drop view v2;
drop table t1;
drop table t2;


-- ALTER TABLE COMPRESS statement is cloudscape specific, disable in db2 mode
-- beetle 5553
--   TODO - not working yet

-- negative tests
create table tb1 (country_ISO_code char(2));
alter table tb1 compress;
alter table tb1 compress sequential;

-- clean up
drop table tb1;

-- Beetle 5717: Disable adding primary or unique constraints on non-nullable columns
 
-- negative tests
create table t1 (c1 int, c2 int);
alter table t1 add constraint pk1 primary key (c1);
alter table t1 add constraint uc1 unique (c2);

-- positive tests
create table t2 (c1 int not null, c2 char(10) not null);
alter table t2 add constraint pk2 primary key (c1);
alter table t2 add constraint uc2 unique (c2);

drop table t1;
drop table t2;


-- SET STATISTICS TIMING ON stmt is cloudscape specific, disabled in db2 mode
-- Once we have rewritten our functions to not use following sql, SET STATISTICS TIMING can be completely removed from the parser.
set statistics timing on;

-- SET RUNTIMESTATISTICS ON stmt is cloudscape specific, disabled in db2 mode
-- Once we have rewritten our functions to not use following sql, SET RUNTIMESTATISTICS can be completely removed from the parser.
set runtimestatistics on;

-- following runtime statistics related sql will fail in db2 mode but will run fine in Cloudscape mode
create table t1 (c1 int, c2 int);
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
select * from t1;
values runtimestatistics()->getScanStatisticsText();
values runtimestatistics()->toString();
-- following runtime statistics related sql is not supported anymore and will not run in any mode
UPDATE STATISTICS FOR TABLE T1;
DROP STATISTICS FOR TABLE T1;
drop table t1;