| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| |
| -- With DB2 current schema is equal to the user name on login. |
| CREATE TABLE DST.DEF_SCHEMA_TEST(NAME_USER VARCHAR(128), NAME_SCHEMA VARCHAR(128)); |
| INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA); |
| SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA; |
| SET SCHEMA DILBERT; |
| |
| connect 'wombat;user=dilbert'; |
| INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA); |
| SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA; |
| VALUES CURRENT SCHEMA; |
| disconnect; |
| |
| SET CONNECTION CONNECTION0; |
| |
| -- still should not be created |
| SET SCHEMA DILBERT; |
| |
| connect 'wombat;user=dilbert'; |
| INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA); |
| SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA; |
| VALUES CURRENT SCHEMA; |
| CREATE TABLE SCOTT(i int); |
| insert into SCOTT VALUES(4); |
| disconnect; |
| |
| SET CONNECTION CONNECTION0; |
| |
| SELECT * FROM DILBERT.SCOTT; |
| DROP TABLE DILBERT.SCOTT; |
| DROP TABLE DST.DEF_SCHEMA_TEST; |
| DROP SCHEMA DST RESTRICT; |
| DROP SCHEMA DILBERT RESTRICT; |
| |
| |
| -- Simple Cloudscape specific features. |
| |
| -- CLASS ALIAS; |
| |
| create class alias MyMath for java.lang.Math; |
| drop class alias MyMath; |
| create class alias for java.lang.Math; |
| drop class alias Math; |
| |
| -- METHOD ALIAS; |
| |
| create method alias myabs for java.lang.Math.abs; |
| drop method alias myabs; |
| |
| -- STORED PREPARED STATEMENTS |
| -- create statement no more supported both in db2 and cloudscpae mode. -ve test for that |
| create statement s1 as values 1,2; |
| -- alter, drop and execute statements are still supported for existing stored prepared statements for customers |
| alter statement recompile all; |
| -- following will give error because there is no stored prepared statement s1 in the database |
| drop statement s1; |
| |
| -- clean up |
| DROP TABLE t1; |
| DROP TABLE t2; |
| DROP CLASS ALIAS ExternalInsert; |
| DROP STATEMENT insert1; |
| |
| -- Primary key constraint, DB2 requires NOT null on the columns. |
| |
| create table customer (id int primary key, name char(100)); |
| drop table customer; |
| |
| create table customer (id int NOT NULL, id2 int, name char(100), primary key (id, id2)); |
| drop table customer; |
| |
| -- drop schema requires restrict |
| create schema fred; |
| drop schema fred; |
| drop schema fred restrict; |
| |
| -- create schema not supported for schemas that start with SYS |
| |
| create schema SYS; |
| create schema SYSDJD; |
| create schema "SYSNO"; |
| create schema "sys"; |
| create schema "sysok"; |
| drop schema "sys" restrict; |
| drop schema "sysok" restrict; |
| |
| -- data types not supported |
| create table NOTYPE(i int, b BOOLEAN); |
| create table NOTYPE(i int, b TINYINT); |
| create table NOTYPE(i int, b java.lang.String); |
| create table NOTYPE(i int, b com.acme.Address); |
| create table NOTYPE(i int, b org.apache.derby.vti.VTIEnvironment); |
| |
| -- VTI in the DELETE statement |
| -- beetle 5234 |
| CREATE TABLE testCS (col1 int, col2 char(30), col3 int); |
| INSERT INTO testCS VALUES (100, 'asdf', 732); |
| DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732; |
| |
| -- VTI in the INSERT statement |
| -- beetle 5234 |
| INSERT INTO NEW org.apache.derbyTesting.functionTests.util.serializabletypes.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (100, 'asdf', 732); |
| |
| -- VTI in the SELECT statement |
| -- beetle 5234 |
| select * from testCS, new org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a; |
| select * from new com.acme.myVTI() as T; |
| select * from new org.apache.derbyTesting.not.myVTI() as T; |
| select * from syscs_diag.lock_table; |
| |
| -- VTI in CREATE TRIGGER statement |
| -- beetle 5234 |
| CREATE TABLE tb1(a int); |
| 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); |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- clean up |
| DROP TABLE tb1; |
| DROP TABLE testCS; |
| |
| -- beetle 5177 |
| create table maps2 (country_ISO_code char(2)); |
| -- BTREE not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error in DB2 mode |
| create btree index map_idx2 on maps2(country_ISO_code); |
| create unique btree index map_idx2 on maps2(country_ISO_code); |
| drop table maps2; |
| |
| -- SET LOCKING clause in DB2 mode |
| -- beetle 5208 |
| create table maps1 (country_ISO_code char(2)) set locking = table; |
| create table maps2 (country_ISO_code char(2)) set locking = row; |
| drop table maps1; |
| drop table maps2; |
| |
| -- ALTER TABLE statement |
| -- beetle 5201 |
| |
| -- Locking syntax |
| -- negative tests |
| create table tb1 (country_ISO_code char(2)); |
| alter table tb1 set locking = table; |
| alter table tb1 set locking = row; |
| |
| -- Locking syntax |
| -- positive tests |
| -- beetle 5201 |
| create table tb2 (country_ISO_code char(2)); |
| alter table tb2 locksize table; |
| alter table tb2 locksize row; |
| |
| -- clean up |
| drop table tb1; |
| drop table tb2; |
| |
| -- VTI in the DELETE statement |
| -- beetle 5234 |
| CREATE TABLE testCS (col1 int, col2 char(30), col3 int); |
| INSERT INTO testCS VALUES (100, 'asdf', 732); |
| DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732; |
| |
| -- VTI in the INSERT statement |
| -- beetle 5234 |
| INSERT INTO NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (100, 'asdf', 732); |
| |
| -- VTI in the SELECT statement |
| -- beetle 5234 |
| select * from testCS, new org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a; |
| |
| -- VTI in CREATE TRIGGER statement |
| -- beetle 5234 |
| CREATE TABLE tb1(a int); |
| 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); |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- clean up |
| DROP TABLE tb1; |
| DROP TABLE testCS; |
| |
| -- RENAME/DROP COLUMN |
| -- ALTER RENAME TABLE/COLUMN |
| -- beetle 5205 |
| create table table tt (a int, b int, c int); |
| -- alter table tt drop column b; This is now supported by Derby |
| alter table tt rename to ttnew; |
| alter table tt rename c to d; |
| rename column tt.c to tt.d; |
| drop table tt; |
| |
| -- CASCADE/RESTRICT on DROP CONSTRAINT |
| -- beetle 5204 |
| ALTER TABLE TT DROP CONSTRAINT ABC CASCADE; |
| ALTER TABLE TT DROP CONSTRAINT ABC2 RESTRICT; |
| |
| -- CASCADE/RESTRICT on DROP TABLE |
| -- beetle 5206 |
| DROP TABLE TT CASCADE; |
| DROP TABLE TT RESTRICT; |
| |
| -- 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)); |
| -- 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)); |
| -- clean up |
| DROP TABLE t1; |
| DROP TABLE t2; |
| |
| -- 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); |
| create unique index ix1 on testindex1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p); |
| |
| -- 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); |
| create unique index ix2 on testindex2(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q); |
| --clean up |
| drop table testindex1; |
| drop table testindex2; |
| |
| -- insert into a lob column using explicit cast |
| -- positive test |
| -- beetle 5221 |
| CREATE TABLE testblob(col1 BLOB(1M)); |
| INSERT INTO testblob (col1) VALUES cast(X'11' as blob(1M)); |
| CREATE TABLE testclob(col1 CLOB(1M)); |
| INSERT INTO testclob (col1) VALUES cast('asdf' as clob(1M)); |
| |
| -- ALTER INDEX |
| -- beetle 5222 |
| CREATE TABLE TT (A INT); |
| CREATE INDEX TTIDX ON TT(A); |
| ALTER INDEX TTIDX RENAME TTIDXNEW; |
| -- clean up |
| drop table tt; |
| |
| -- CREATE and DROP AGGREGATE |
| -- beetle 5222 |
| CREATE AGGREGATE STDEV FOR org.apache.derbyTesting.functionTests.util.aggregates.StandardDeviation; |
| DROP AGGREGATE STDEV; |
| CREATE AGGREGATE MAXBUTONE FOR org.apache.derbyTesting.functionTests.util.aggregates.MaxButOneDef; |
| DROP AGGREGATE MAXBUTONE; |
| |
| -- CREATE and DROP CLASS ALIAS |
| -- beetle 5222 |
| create class alias for java.util.Hashtable; |
| drop class alias Hashtable; |
| |
| -- CREATE and DROP METHOD ALIAS |
| -- beetle 5222 |
| create method alias hashtable for java.lang.Math.sin; |
| drop method alias hashtable; |
| |
| -- RENAME COLUMN |
| -- beetle 5222 |
| create table TT(col1 int, col2 int); |
| rename column TT.col2 to newcolumn2; |
| drop table TT; |
| |
| -- SET TRIGGERS |
| -- beetle 5222 |
| CREATE TABLE tb1 (col1 int, col2 int, col3 int, constraint chk1 check (col1 > 0)); |
| CREATE TABLE tb2 (col1 char(30), c2 int, c3 int); |
| 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); |
| SET TRIGGERS FOR tb1 ENABLED; |
| SET TRIGGERS FOR tb1 DISABLED; |
| SET TRIGGERS testtrig2 ENABLED; |
| SET TRIGGERS testtrig2 DISABLED; |
| -- clean up |
| DROP TRIGGER testtrig1; |
| DROP TRIGGER testtrig2; |
| DROP TRIGGER testtrig3; |
| DROP TABLE tb1; |
| DROP TABLE tb2; |
| |
| -- 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); |
| create table mm (x org.apache.derbyTesting.functionTests.util.ManyMethods); |
| create table sc (x org.apache.derbyTesting.functionTests.util.SubClass); |
| select i from t1 where i instanceof java.lang.Integer; |
| select i from t1 where i instanceof java.lang.Number; |
| select i from t1 where i instanceof java.lang.Object; |
| select s from t1 where s instanceof java.lang.Integer; |
| select b from t1 where b instanceof java.lang.Boolean; |
| select c10 from t1 where c10 instanceof java.lang.String; |
| select vc30 from t1 where vc30 instanceof java.lang.String; |
| -- following are negative test cases because boolean values disallowed in select clause |
| select x instanceof org.apache.derbyTesting.functionTests.util.ManyMethods from mm; |
| select x instanceof org.apache.derbyTesting.functionTests.util.SubClass from mm; |
| select x instanceof org.apache.derbyTesting.functionTests.util.SubSubClass from mm; |
| select (i + i) instanceof java.lang.Integer from t1; |
| select (i instanceof java.lang.Integer) = true from t1; |
| DELETE FROM t1 where i INSTANCEOF |
| org.apache.derbyTesting.functionTests.util.serializabletypes.City; |
| UPDATE t1 SET s = NULL WHERE i INSTANCEOF |
| org.apache.derbyTesting.functionTests.util.serializabletypes.City; |
| -- clean up |
| drop table t1; |
| drop table mm; |
| drop table sc; |
| |
| -- datatypes |
| -- beetle 5233 |
| create table testtype1(col1 bit); |
| create table testtype2(col1 bit varying(10)); |
| --- boolean datatype already disabled |
| create table testtype3(col1 boolean); |
| create table testtype4(col1 LONG NVARCHAR); |
| create table testtype5(col1 LONG VARBINARY); |
| create table testtype6(col1 LONG BIT VARYING); |
| create table testtype7(col1 LONG BINARY); |
| create table testtype8(col1 NCHAR); |
| create table testtype9(col1 NVARCHAR(10)); |
| -- tinyint datatype already disabled |
| create table testtype10(col1 TINYINT); |
| create table testtype11 (a national character large object (1000)); |
| -- beetle5426 |
| -- disable nclob |
| create table beetle5426 (a nclob (1M)); |
| create table testtype12 (a national char(100)); |
| CREATE CLASS ALIAS FOR org.apache.derbyTesting.functionTests.util.serializabletypes.Tour; |
| create table testtype13 (a Tour); |
| -- clean up |
| drop table testtype1; |
| drop table testtype2; |
| drop table testtype3; |
| drop table testtype4; |
| drop table testtype5; |
| drop table testtype6; |
| drop table testtype7; |
| drop table testtype8; |
| drop table testtype9; |
| drop table testtype10; |
| drop table testtype11; |
| drop table beetle5426; |
| drop table testtype12; |
| drop class alias Tours; |
| drop table testtype13; |
| |
| -- 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)); |
| -- following will pass because char length <= 254 |
| create table test1(col1 char(254), col2 char(23)); |
| -- try truncation error with the 2 chars |
| -- the trailing blanks will not give error |
| insert into test1 values('a','abcdefghijklmnopqrstuvw '); |
| -- the trailing non-blank characters will give error |
| insert into test1 values('a','abcdefghijklmnopqrstuvwxyz'); |
| insert into test1 values('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','a'); |
| drop table test1; |
| -- following will fail because varchar length > 32672 |
| create table test1(col1 varchar(32673)); |
| -- following will pass because varchar length <= 32672 |
| create table test1(col1 varchar(32672), col2 varchar(1234)); |
| drop table test1; |
| |
| -- SET CONSTRAINTS statement |
| -- beetle 5251 |
| CREATE TABLE testsetconst1 (col1 CHAR(7) NOT NULL, PRIMARY KEY(col1)); |
| CREATE TABLE testsetconst2 (col1 char(7) NOT NULL, CONSTRAINT fk FOREIGN KEY(col1) REFERENCES testsetconst1(col1)); |
| SET CONSTRAINTS fk DISABLED; |
| SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| SET CONSTRAINTS fk ENABLED; |
| SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| SET CONSTRAINTS ALL DISABLED; |
| SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| SET CONSTRAINTS FOR testsetconst1 ENABLED; |
| SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| |
| -- clean up |
| DROP TABLE testsetconst1; |
| DROP TABLE testsetconst2; |
| |
| -- CALL statement |
| -- beetle 5252 |
| call org.apache.derby.iapi.db.Factory::getDatabaseOfConnection().dropAllJDBCMetaDataSPSes(); |
| |
| -- 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); |
| create table deftest2 (vc varchar(30) default 'howdy'); |
| create table deftest21 (vc clob(10) default 'okie'); |
| create table deftest3 (d date default current date); |
| create table deftest31 (d date default '2004-02-08'); |
| create table deftest5 (vc char(130) default current schema); |
| create table deftest4 (c char(130) default user); |
| create table deftest6 (d decimal(5,2) default null); |
| create table deftest7 (d decimal(5,2) default 123.450); |
| create table deftest8 (f float default 1.234); |
| -- make sure they actually work @ insertion. |
| insert into deftest1 values (default); |
| insert into deftest2 values (default); |
| insert into deftest21 values (default); |
| insert into deftest3 values (default); |
| insert into deftest31 values (default); |
| insert into deftest4 values (default); |
| insert into deftest5 values (default); |
| insert into deftest6 values (default); |
| insert into deftest7 values (default); |
| insert into deftest8 values (default); |
| -- cleanup. |
| drop table deftest1; |
| drop table deftest2; |
| drop table deftest21; |
| drop table deftest3; |
| drop table deftest31; |
| drop table deftest4; |
| drop table deftest5; |
| drop table deftest6; |
| drop table deftest7; |
| drop table deftest8; |
| -- 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)); |
| create table deftest2 (i int default 3+4); |
| -- floating point assignment to non-float column. |
| create table deftest3 (i int default 1.234); |
| -- decimal value with too much precision. |
| create table deftest4 (d decimal(5,2) default 1.2234); |
| -- 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)); |
| create table t1 (i int default someFunc('hi')); |
| -- Type mismatches should fail with 42894 (NOT with 42821), to match DB2. |
| create table t1 (i int default 'hi'); |
| |
| -- Beetle 5281: <cast-function> for a default. |
| -- Date-time functions (DATE, TIME, and TIMESTAMP) |
| create table t1a (d date default date(current date)); |
| create table t1b (d date default date('1978-03-22')); |
| create table t2a (t time default time(current time)); |
| create table t2b (t time default time('08:28:08')); |
| create table t3a (ch timestamp default timestamp(current timestamp)); |
| create table t3b (ts timestamp default timestamp('2004-04-27 08:59:02.91')); |
| -- BLOB function (not yet supported). |
| create table t4 (b blob default blob('nope')); |
| -- cleanup. |
| drop table t1a; |
| drop table t1b; |
| drop table t2a; |
| drop table t2b; |
| drop table t3a; |
| drop table t3b; |
| |
| -- 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)); |
| 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)); |
| -- DROP FOREIGN KEY syntax should be supported in DB2 compat mode |
| insert into testconst1( col1, col2) values( 'a', 2); |
| insert into testconst1( col1, col2) values( 'a', 2); |
| insert into testconst1( col1, col2) values( 'b', 0); |
| |
| insert into testconst2( col1, col2, col3) values( 'a', 'a', 1); |
| insert into testconst2( col1, col2, col3) values( 'z', 'b', 1); |
| insert into testconst2( col1, col2, col3) values( 'a', 'a', 1); |
| |
| -- beetle 5204 |
| ALTER TABLE testconst1 DROP FOREIGN KEY cc; |
| ALTER TABLE testconst2 DROP UNIQUE fk; |
| ALTER TABLE testconst2 DROP CHECK fk; |
| |
| ALTER TABLE testconst2 DROP FOREIGN KEY fk; |
| -- DROP PRIMARY KEY syntax should be supported in DB2 compat mode |
| -- beetle 5204 |
| ALTER TABLE testconst1 DROP PRIMARY KEY; |
| -- DROP UNIQUE KEY syntax should be supported in DB2 compat mode |
| -- beetle 5204 |
| ALTER TABLE testconst2 DROP UNIQUE uk; |
| -- DROP CHECK condition syntax should be supported in DB2 compat mode |
| -- beetle 5204 |
| ALTER TABLE testconst1 DROP CHECK cc; |
| |
| insert into testconst1( col1, col2) values( 'a', 2); |
| insert into testconst1( col1, col2) values( 'b', 0); |
| |
| insert into testconst2( col1, col2, col3) values( 'z', 'b', 1); |
| insert into testconst2( col1, col2, col3) values( 'a', 'a', 1); |
| |
| ALTER TABLE testconst2 DROP FOREIGN KEY noSuchConstraint; |
| ALTER TABLE testconst2 DROP CHECK noSuchConstraint; |
| ALTER TABLE testconst2 DROP UNIQUE noSuchConstraint; |
| ALTER TABLE testconst1 DROP PRIMARY KEY; |
| |
| -- clean up |
| DROP TABLE testconst1; |
| DROP TABLE testconst2; |
| |
| -- CREATE TRIGGERS |
| -- beetle 5253 |
| CREATE TABLE tb1 (col1 int, col2 int, col3 int, constraint chk1 check (col1 > 0)); |
| CREATE TABLE tb2 (col1 char(30), c2 int, c3 int); |
| -- change syntax of before to "NO CASCADE BEFORE" |
| CREATE TRIGGER testtrig1 NO CASCADE BEFORE UPDATE OF col1,col2 on tb1 FOR EACH ROW MODE DB2SQL VALUES 1; |
| 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); |
| CREATE TRIGGER testtrig3 AFTER UPDATE on tb1 |
| REFERENCING OLD as oldtable FOR EACH ROW MODE DB2SQL INSERT INTO tb2 VALUES ('tb', oldtable.col1, oldtable.col2); |
| -- clean up |
| DROP TRIGGER testtrig1; |
| DROP TRIGGER testtrig2; |
| DROP TRIGGER testtrig3; |
| DROP TABLE tb1; |
| DROP TABLE tb2; |
| |
| -- SET TRANSACTION ISOLATION LEVEL |
| -- beetle 5254 |
| -- these SET TRANSACTION ISOLATION statements fail in db2 compat mode because it has cloudscape specific syntax |
| create table t1(c1 int not null constraint asdf primary key); |
| insert into t1 values 1; |
| SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
| SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
| SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
| -- clean up |
| drop table t1; |
| |
| -- statements should pass in db2 compat mode |
| -- beetle 5260 |
| autocommit off; |
| create table t1(c1 int not null constraint asdf primary key); |
| commit; |
| insert into t1 values 1; |
| -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation serializable; |
| -- rollback should find nothing to undo |
| rollback; |
| select * from t1; |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation read committed; |
| -- rollback should find nothing to undo |
| rollback; |
| select * from t1; |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation repeatable read; |
| -- rollback should find nothing to undo |
| rollback; |
| select * from t1; |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation read uncommitted; |
| -- rollback should find nothing to undo |
| rollback; |
| select * from t1; |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| drop table t1; |
| |
| -- SET ISOLATION statement |
| -- beetle 5260 |
| -- set isolation statement that are supported in db2 |
| create table t1(c1 int not null constraint asdf primary key); |
| insert into t1 values 1; |
| set isolation serializable; |
| set isolation read committed; |
| set isolation repeatable read; |
| set isolation read uncommitted; |
| -- clean up |
| drop table t1; |
| |
| -- SELECT statement testing |
| -- beetle 5255 |
| |
| CREATE TABLE t1(col1 int, col2 int); |
| CREATE TABLE t2(col1 int, col2 int); |
| INSERT INTO t1 VALUES(3,4); |
| INSERT INTO t2 VALUES(3,4); |
| |
| -- (5) TRUE and FALSE constants are no longer disabled in WHERE clause of SELECT statement |
| SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE true; |
| SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE false; |
| |
| -- (5) TRUE and FALSE constants are no longer disabled in WHERE clause of DELETE statement |
| DELETE FROM t1 where true; |
| DELETE FROM t1 where false; |
| |
| -- (5) TRUE and FALSE constants are no longer disabled in WHERE clause of DELETE statement |
| UPDATE t2 SET col1 = NULL WHERE true; |
| UPDATE t2 SET col1 = NULL WHERE false; |
| |
| -- (6) AT ISOLATION clause should be disabled in SELECT statement |
| -- AT ISOLATION not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error |
| SELECT * FROM t1 AT ISOLATION READ UNCOMMITTED; |
| SELECT * FROM t1 AT ISOLATION READ COMMITTED; |
| SELECT * FROM t1 AT ISOLATION SERIALIZABLE; |
| SELECT * FROM t1 AT ISOLATION REPEATABLE READ; |
| |
| -- clean up |
| DROP TABLE t1; |
| DROP TABLE t2; |
| |
| -- DEFAULT CAST 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 testuser(col1 BLOB(3K) default cast(user as blob(3k))); |
| create table testsessionuser(col1 BLOB(3K) default cast(session_user as blob(3k))); |
| create table testcurrentuser(col1 BLOB(3K) default cast(current_user as blob(3k))); |
| create table testschema(col1 BLOB(3K) default cast(current schema as blob(3k))); |
| |
| -- alter table syntax that should be supported in db2 compat mode |
| -- beetle 5267 |
| create table testmodify (col1 varchar(30), col2 int generated always as identity); |
| -- increasing the length of the varchar column |
| alter table testmodify alter col1 set data type varchar(60); |
| -- specifying the interval between consecutive values of col2, the identity column |
| alter table testmodify alter col2 set increment by 2; |
| -- clean up |
| drop table testmodify; |
| |
| -- (1) adding more than one column |
| -- beetle 5268 |
| -- db2 compat mode should support the following statements |
| create table testaddcol (col1 int); |
| alter table testaddcol add column col2 int add col3 int; |
| drop table testaddcol; |
| |
| -- (2) adding more than one unique, referential, or check constraint |
| -- beetle 5268 |
| -- db2 compat mode should support the following statements |
| create table testaddconst1 (col1 int not null primary key, col2 int not null unique); |
| create table testaddconst2 (col1 int not null primary key, col2 int not null unique); |
| create table testaddconst3 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int); |
| create table testaddconst4 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int); |
| -- adding more than one unique-constraint |
| alter table testaddconst3 add primary key (col1) add unique (col2); |
| alter table testaddconst3 add unique (col3) add unique (col4); |
| -- adding more than one referential-constraint |
| alter table testaddconst3 add foreign key (col1) references testaddconst1(col1) add foreign key (col2) references testaddconst2(col2); |
| -- adding more than one check-constraint |
| alter table testaddconst3 add check (col5 is null) add check (col6 is null); |
| -- adding a primary, unique, foreign key, and check-constraint |
| alter table testaddconst4 add primary key(col1) add unique(col2) add foreign key (col1) references testaddconst1(col1) add check (col2 is null); |
| -- clean up |
| drop table testaddconst1; |
| drop table testaddconst2; |
| drop table testaddconst3; |
| drop table testaddconst4; |
| |
| -- (3) adding more than one unique, referential, or check constraints |
| -- beetle 5268 |
| -- syntax that will be supported in db2 compat mode (beetle 5204) |
| CREATE TABLE testdropconst1 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk1 UNIQUE , PRIMARY KEY(col1)); |
| CREATE TABLE testdropconst2 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk2 UNIQUE, col3 CHAR(5) not null CONSTRAINT uk3 UNIQUE, PRIMARY KEY(col1)); |
| CREATE TABLE testdropconst3 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk4 UNIQUE , PRIMARY KEY(col1)); |
| CREATE TABLE testdropconst4 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk5 UNIQUE , PRIMARY KEY(col1)); |
| CREATE TABLE testdropconst5 (col1 CHAR(7) NOT NULL, col2 int, col3 CHAR(5) not null, CONSTRAINT fk1 FOREIGN KEY (col1) REFERENCES testdropconst3(col1), CONSTRAINT fk2 FOREIGN KEY (col1) REFERENCES testdropconst4(col1)); |
| CREATE TABLE testdropconst6 (col1 CHAR(7) CONSTRAINT ck1 CHECK (col1 is null), col2 int CONSTRAINT ck2 CHECK (col2 is null)); |
| -- dropping more than one unique-constraint |
| alter table testdropconst1 drop primary key drop constraint uk1; |
| alter table testdropconst2 drop primary key drop constraint uk2 drop constraint uk3; |
| -- dropping more than one foreign key constraint |
| alter table testdropconst5 drop constraint fk1 drop constraint fk2; |
| -- dropping more than one check constraint |
| alter table testdropconst6 drop constraint ck1 drop constraint ck2; |
| --clean up |
| drop table testdropconst1; |
| drop table testdropconst2; |
| drop table testdropconst3; |
| drop table testdropconst4; |
| drop table testdropconst5; |
| drop table testdropconst6; |
| |
| -- (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)); |
| alter table testmodify alter col1 set data type varchar(60) alter col2 set data type varchar(60); |
| -- clean up |
| drop table testmodify; |
| |
| -- 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)); |
| -- this statement should throw an error in db2 compat mode, but it does not |
| insert into t1 values(1); |
| |
| -- clean up |
| drop table t1; |
| |
| -- 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)); |
| insert into t1 values (blob('hmm')); |
| create table t2 (ch timestamp); |
| insert into t2 values (timestamp(current timestamp)); |
| create table t3 (ch time); |
| insert into t3 values (time(current time)); |
| create table t4 (ch date); |
| insert into t4 values (date(current date)); |
| drop table t1; |
| drop table t2; |
| drop table t3; |
| drop table t4; |
| |
| -- test operands |
| -- beetle 5282 |
| -- <,> =, !=, <=, >= operands are not supported in db2 but supported in cloudscape |
| CREATE TABLE testoperatorclob (colone clob(1K)); |
| INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K))); |
| select * from testoperatorclob; |
| -- these select statements should raise an error but are successful in cloudscape |
| select * from testoperatorclob where colone > 10; |
| select * from testoperatorclob where colone < 70; |
| select * from testoperatorclob where colone = 50; |
| select * from testoperatorclob where colone != 10; |
| select * from testoperatorclob where colone <= 70; |
| select * from testoperatorclob where colone >= 10; |
| select * from testoperatorclob where colone <> 10; |
| drop table testoperatorclob; |
| |
| -- beetle 5282 |
| CREATE TABLE testoperatorblob (colone clob(1K)); |
| INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K))); |
| select * from testoperatorblob; |
| -- these select statements should raise an error but are successful in cloudscape |
| select * from testoperatorblob where colone > 10; |
| select * from testoperatorblob where colone < 999999; |
| select * from testoperatorblob where colone = 00350030; |
| select * from testoperatorblob where colone != 10; |
| select * from testoperatorblob where colone <= 999999; |
| select * from testoperatorblob where colone >= 10; |
| select * from testoperatorblob where colone <> 10; |
| drop table testoperatorblob; |
| |
| -- 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)); |
| values cast(B'1' as clob(1M)); |
| values cast(B'1' as blob(1M)); |
| values cast(X'11' as char(100)); |
| values cast(X'11' as clob(1M)); |
| values cast(X'11' as blob(1M)); |
| |
| -- beetle 5284 |
| -- minor difference in outputs when casting to blob in Cloudscape and DB2. |
| values cast(' ' as blob(1M)); |
| values cast('a' as blob(1M)); |
| |
| -- Test removed: DERBY-2147 |
| -- 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%'; |
| |
| |
| -- Test removed: DERBY-2147 |
| -- 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; |
| |
| -- Test removed: DERBY-2147 |
| -- 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 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; |
| |
| -- 2) AFTER triggers |
| 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); |
| |
| -- DERBY-1953: The following statement will fail in DB2 LUW since it currently does |
| -- not allow the FOR EACH part to be optional but DB2 iSeries allows both FOR EACH and |
| -- MODE part to be optional. So this test is commented out for reference since it is |
| -- not relevant after DERBY-1953 is applied (allow FOR EACH and MODE part to be optional). |
| -- 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; |
