db2compatibility.out

derby database source code.good for you.

0 rows inserted/updated/deleted
ij> CREATE TRIGGER testtrig1 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL INSERT INTO NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (1000);
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TRIGGER testtrig2 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TRIGGER testtrig3 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL SELECT * FROM testCS, NEW org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI.
ij> -- clean up
DROP TABLE tb1;
0 rows inserted/updated/deleted
ij> DROP TABLE testCS;
0 rows inserted/updated/deleted
ij> -- RENAME/DROP COLUMN
-- ALTER RENAME TABLE/COLUMN
-- beetle 5205
create table table tt (a int, b int, c int);
ERROR 42X01: Syntax error: Encountered "table" at line 4, column 14.
ij> alter table tt drop column b;
ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 16.
ij> alter table tt rename to ttnew;
ERROR 42X01: Syntax error: Encountered "rename" at line 1, column 16.
ij> alter table tt rename c to d;
ERROR 42X01: Syntax error: Encountered "rename" at line 1, column 16.
ij> rename column tt.c to tt.d;
ERROR 42X01: Syntax error: Encountered "column" at line 1, column 8.
ij> drop table tt;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TT' because it does not exist.
ij> -- CASCADE/RESTRICT on DROP CONSTRAINT
-- beetle 5204
ALTER TABLE TT DROP CONSTRAINT ABC CASCADE;
ERROR 42X01: Syntax error: Encountered "CASCADE" at line 3, column 36.
ij> ALTER TABLE TT DROP CONSTRAINT ABC2 RESTRICT;
ERROR 42X01: Syntax error: Encountered "RESTRICT" at line 1, column 37.
ij> -- CASCADE/RESTRICT on DROP TABLE
-- beetle 5206
DROP TABLE TT CASCADE;
ERROR 42X01: Syntax error: Encountered "CASCADE" at line 3, column 15.
ij> DROP TABLE TT RESTRICT;
ERROR 42X01: Syntax error: Encountered "RESTRICT" at line 1, column 15.
ij> -- beetle 5216
-- there should only be one autoincrement column per table 
CREATE TABLE T1 (C1 INT GENERATED ALWAYS AS IDENTITY 
(START WITH 1, INCREMENT BY 1));
0 rows inserted/updated/deleted
ij> -- this statement should raise an error because it has more than one auto increment column in a table
CREATE TABLE T2 (C1 INT GENERATED ALWAYS AS IDENTITY 
(START WITH 1, INCREMENT BY 1), C2 INT GENERATED ALWAYS AS 
IDENTITY (START WITH 1, INCREMENT BY 1));
ERROR 428C1: Only one identity column is allowed in a table.
ij> -- clean up
DROP TABLE t1;
0 rows inserted/updated/deleted
ij> DROP TABLE t2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist.
ij> -- limit to 16 columns in an index key
-- beetle 5181
-- this create index statement should be successful in db2 compat mode because ix2 specifies 16 columns
create table testindex1 (a int,b int,c int,d int ,e int ,f int,g int,h int,i int,j int,k int,l int,m int,n int,o int,p int);
0 rows inserted/updated/deleted
ij> create unique index ix1 on testindex1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p);
0 rows inserted/updated/deleted
ij> -- this create index statement should fail in db2 compat mode because ix2 specifies more than 16 columns
create table testindex2 (a int,b int,c int,d int ,e int ,f int,g int,h int,i int,j int,k int,l int,m int,n int,o int,p int,q int);
0 rows inserted/updated/deleted
ij> create unique index ix2 on testindex2(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q);
ERROR 54008: The CREATE INDEX statement specifies too many columns (16 is the maximum).
ij> --clean up
drop table testindex1;
0 rows inserted/updated/deleted
ij> drop table testindex2;
0 rows inserted/updated/deleted
ij> -- insert into a lob column using explicit cast
-- positive test
-- beetle 5221
CREATE TABLE testblob(col1 BLOB(1M));
0 rows inserted/updated/deleted
ij> INSERT INTO testblob (col1) VALUES cast(X'11' as blob(1M));
1 row inserted/updated/deleted
ij> CREATE TABLE testclob(col1 CLOB(1M));
0 rows inserted/updated/deleted
ij> INSERT INTO testclob (col1) VALUES cast('asdf' as clob(1M));
1 row inserted/updated/deleted
ij> -- ALTER INDEX
-- beetle 5222
CREATE TABLE TT (A INT);
0 rows inserted/updated/deleted
ij> CREATE INDEX TTIDX ON TT(A);
0 rows inserted/updated/deleted
ij> ALTER INDEX TTIDX RENAME TTIDXNEW;
ERROR 42X01: Syntax error: Encountered "INDEX" at line 1, column 7.
ij> -- clean up
drop table tt;
0 rows inserted/updated/deleted
ij> -- CREATE and DROP AGGREGATE
-- beetle 5222
CREATE AGGREGATE STDEV FOR org.apache.derbyTesting.functionTests.util.aggregates.StandardDeviation;
ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 3, column 8.
ij> DROP AGGREGATE STDEV;
ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 1, column 6.
ij> CREATE AGGREGATE MAXBUTONE FOR org.apache.derbyTesting.functionTests.util.aggregates.MaxButOneDef;
ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 1, column 8.
ij> DROP AGGREGATE MAXBUTONE;
ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 1, column 6.
ij> -- CREATE and DROP CLASS ALIAS
-- beetle 5222
create class alias for java.util.Hashtable;
ERROR 42X01: Syntax error: Encountered "class" at line 3, column 8.
ij> drop class alias Hashtable;
ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6.
ij> -- CREATE and DROP METHOD ALIAS
-- beetle 5222
create method alias hashtable for java.lang.Math.sin;
ERROR 42X01: Syntax error: Encountered "method" at line 3, column 8.
ij> drop method alias hashtable;
ERROR 42X01: Syntax error: Encountered "method" at line 1, column 6.
ij> -- RENAME COLUMN
-- beetle 5222
create table TT(col1 int, col2 int);
0 rows inserted/updated/deleted
ij> rename column TT.col2 to newcolumn2;
ERROR 42X01: Syntax error: Encountered "column" at line 1, column 8.
ij> drop table TT;
0 rows inserted/updated/deleted
ij> -- SET TRIGGERS
-- beetle 5222
CREATE TABLE tb1 (col1 int, col2 int, col3 int, constraint chk1 check (col1 > 0));
0 rows inserted/updated/deleted
ij> CREATE TABLE tb2 (col1 char(30), c2 int, c3 int);
0 rows inserted/updated/deleted
ij> CREATE TRIGGER testtrig2 AFTER UPDATE on tb1
REFERENCING OLD as oldtable FOR EACH ROW MODE DB2SQL INSERT INTO tb2 VALUES ('tb', oldtable.col1, oldtable.col2);
0 rows inserted/updated/deleted
ij> SET TRIGGERS FOR tb1 ENABLED;
ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5.
ij> SET TRIGGERS FOR tb1 DISABLED;
ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5.
ij> SET TRIGGERS testtrig2 ENABLED;
ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5.
ij> SET TRIGGERS testtrig2 DISABLED;
ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5.
ij> -- clean up
DROP TRIGGER testtrig1;
ERROR 42X94: TRIGGER 'TESTTRIG1' does not exist.
ij> DROP TRIGGER testtrig2;
0 rows inserted/updated/deleted
ij> DROP TRIGGER testtrig3;
ERROR 42X94: TRIGGER 'TESTTRIG3' does not exist.
ij> DROP TABLE tb1;
0 rows inserted/updated/deleted
ij> DROP TABLE tb2;
0 rows inserted/updated/deleted
ij> -- INSTANCEOF in where clause of select, delete, update,
-- beetle 5224
create table t1 (i int, s smallint, c10 char(10), vc30 varchar(30), b boolean);
ERROR 42X01: Syntax error: BOOLEAN.
ij> create table mm (x org.apache.derbyTesting.functionTests.util.ManyMethods);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 20.
ij> create table sc (x org.apache.derbyTesting.functionTests.util.SubClass);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 20.
ij> select i from t1 where i instanceof java.lang.Integer;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26.
ij> select i from t1 where i instanceof java.lang.Number;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26.
ij> select i from t1 where i instanceof java.lang.Object;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26.
ij> select s from t1 where s instanceof java.lang.Integer;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26.
ij> select b from t1 where b instanceof java.lang.Boolean;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26.
ij> select c10 from t1 where c10 instanceof java.lang.String;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 30.
ij> select vc30 from t1 where vc30 instanceof java.lang.String;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 32.
ij> -- following are negative test cases because boolean values disallowed in select clause
select x instanceof org.apache.derbyTesting.functionTests.util.ManyMethods from mm;
ERROR 42X01: Syntax error: Encountered "org" at line 2, column 21.
ij> select x instanceof org.apache.derbyTesting.functionTests.util.SubClass from mm;
ERROR 42X01: Syntax error: Encountered "org" at line 1, column 21.
ij> select x instanceof org.apache.derbyTesting.functionTests.util.SubSubClass from mm;
ERROR 42X01: Syntax error: Encountered "org" at line 1, column 21.
ij> select (i + i) instanceof java.lang.Integer from t1;
ERROR 42X01: Syntax error: Encountered "java" at line 1, column 27.
ij> select (i instanceof java.lang.Integer) = true from t1;
ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 11.
ij> DELETE FROM t1 where i INSTANCEOF 
org.apache.derbyTesting.functionTests.util.serializabletypes.City;
ERROR 42X01: Syntax error: Encountered "INSTANCEOF" at line 1, column 24.
ij> UPDATE t1 SET s = NULL WHERE i INSTANCEOF 
org.apache.derbyTesting.functionTests.util.serializabletypes.City;
ERROR 42X01: Syntax error: Encountered "INSTANCEOF" at line 1, column 32.
ij> -- clean up
drop table t1;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist.
ij> drop table mm;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MM' because it does not exist.
ij> drop table sc;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'SC' because it does not exist.
ij> -- datatypes
-- beetle 5233
create table testtype1(col1 bit);
ERROR 42X01: Syntax error: Encountered "bit" at line 3, column 29.
ij> create table testtype2(col1 bit varying(10));
ERROR 42X01: Syntax error: Encountered "bit" at line 1, column 29.
ij> -- boolean datatype already disabled
create table testtype3(col1 boolean);
ERROR 42X01: Syntax error: BOOLEAN.
ij> create table testtype4(col1 LONG NVARCHAR);
ERROR 0A000: Feature not implemented: LONG NVARCHAR.
ij> create table testtype5(col1 LONG VARBINARY);
ERROR 42X01: Syntax error: Encountered "VARBINARY" at line 1, column 34.
ij> create table testtype6(col1 LONG BIT VARYING);
ERROR 42X01: Syntax error: Encountered "BIT" at line 1, column 34.
ij> create table testtype7(col1 LONG BINARY);
ERROR 42X01: Syntax error: Encountered "BINARY" at line 1, column 34.
ij> create table testtype8(col1 NCHAR);
ERROR 0A000: Feature not implemented: NATIONAL CHAR.
ij> create table testtype9(col1 NVARCHAR(10));
ERROR 0A000: Feature not implemented: NATIONAL CHAR VARYING.
ij> -- tinyint datatype already disabled
create table testtype10(col1 TINYINT);
ERROR 42X01: Syntax error: Encountered "" at line 2, column 30.
ij> create table testtype11 (a national character large object (1000));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- beetle5426
-- disable nclob
create table beetle5426 (a nclob (1M));
ERROR 0A000: Feature not implemented: NCLOB.
ij> create table testtype12 (a national char(100));
ERROR 0A000: Feature not implemented: NATIONAL CHAR.
ij> CREATE CLASS ALIAS FOR org.apache.derbyTesting.functionTests.util.serializabletypes.Tour;
ERROR 42X01: Syntax error: Encountered "CLASS" at line 1, column 8.
ij> create table testtype13 (a Tour);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 28.
ij> -- clean up
drop table testtype1;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE1' because it does not exist.
ij> drop table testtype2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE2' because it does not exist.
ij> drop table testtype3;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE3' because it does not exist.
ij> drop table testtype4;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE4' because it does not exist.
ij> drop table testtype5;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE5' because it does not exist.
ij> drop table testtype6;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE6' because it does not exist.
ij> drop table testtype7;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE7' because it does not exist.
ij> drop table testtype8;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE8' because it does not exist.
ij> drop table testtype9;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTTYPE9' because it does not exist.
ij> drop table testtype10;