| ij> -- |
| -- 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)); |
| 0 rows inserted/updated/deleted |
| ij> INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA); |
| 1 row inserted/updated/deleted |
| ij> SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA; |
| 1 |
| ----------- |
| 1 |
| ij> SET SCHEMA DILBERT; |
| ERROR 42Y07: Schema 'DILBERT' does not exist |
| ij> connect 'wombat;user=dilbert'; |
| ij(CONNECTION1)> INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA; |
| 1 |
| ----------- |
| 2 |
| ij(CONNECTION1)> VALUES CURRENT SCHEMA; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| DILBERT |
| ij(CONNECTION1)> disconnect; |
| ij> SET CONNECTION CONNECTION0; |
| ij> -- still should not be created |
| SET SCHEMA DILBERT; |
| ERROR 42Y07: Schema 'DILBERT' does not exist |
| ij> connect 'wombat;user=dilbert'; |
| ij(CONNECTION1)> INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA; |
| 1 |
| ----------- |
| 3 |
| ij(CONNECTION1)> VALUES CURRENT SCHEMA; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| DILBERT |
| ij(CONNECTION1)> CREATE TABLE SCOTT(i int); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> insert into SCOTT VALUES(4); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> disconnect; |
| ij> SET CONNECTION CONNECTION0; |
| ij> SELECT * FROM DILBERT.SCOTT; |
| I |
| ----------- |
| 4 |
| ij> DROP TABLE DILBERT.SCOTT; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE DST.DEF_SCHEMA_TEST; |
| 0 rows inserted/updated/deleted |
| ij> DROP SCHEMA DST RESTRICT; |
| 0 rows inserted/updated/deleted |
| ij> DROP SCHEMA DILBERT RESTRICT; |
| 0 rows inserted/updated/deleted |
| ij> -- Simple Cloudscape specific features. |
| -- CLASS ALIAS; |
| create class alias MyMath for java.lang.Math; |
| ERROR 42X01: Syntax error: Encountered "class" at line 5, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop class alias MyMath; |
| ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create class alias for java.lang.Math; |
| ERROR 42X01: Syntax error: Encountered "class" at line 1, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop class alias Math; |
| ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- METHOD ALIAS; |
| create method alias myabs for java.lang.Math.abs; |
| ERROR 42X01: Syntax error: Encountered "method" at line 3, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop method alias myabs; |
| ERROR 42X01: Syntax error: Encountered "method" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- 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; |
| ERROR 42X01: Syntax error: Encountered "statement" at line 3, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- alter, drop and execute statements are still supported for existing stored prepared statements for customers |
| alter statement recompile all; |
| ERROR 42X01: Syntax error: Encountered "statement" at line 2, column 7. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- following will give error because there is no stored prepared statement s1 in the database |
| drop statement s1; |
| ERROR 42X01: Syntax error: Encountered "statement" at line 2, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| DROP TABLE t1; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist. |
| ij> DROP TABLE t2; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist. |
| ij> DROP CLASS ALIAS ExternalInsert; |
| ERROR 42X01: Syntax error: Encountered "CLASS" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> DROP STATEMENT insert1; |
| ERROR 42X01: Syntax error: Encountered "STATEMENT" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- Primary key constraint, DB2 requires NOT null on the columns. |
| create table customer (id int primary key, name char(100)); |
| 0 rows inserted/updated/deleted |
| ij> drop table customer; |
| 0 rows inserted/updated/deleted |
| ij> create table customer (id int NOT NULL, id2 int, name char(100), primary key (id, id2)); |
| 0 rows inserted/updated/deleted |
| ij> drop table customer; |
| 0 rows inserted/updated/deleted |
| ij> -- drop schema requires restrict |
| create schema fred; |
| 0 rows inserted/updated/deleted |
| ij> drop schema fred; |
| ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 16. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop schema fred restrict; |
| 0 rows inserted/updated/deleted |
| ij> -- create schema not supported for schemas that start with SYS |
| create schema SYS; |
| ERROR 42939: An object cannot be created with the schema name 'SYS'. |
| ij> create schema SYSDJD; |
| ERROR 42939: An object cannot be created with the schema name 'SYSDJD'. |
| ij> create schema "SYSNO"; |
| ERROR 42939: An object cannot be created with the schema name 'SYSNO'. |
| ij> create schema "sys"; |
| 0 rows inserted/updated/deleted |
| ij> create schema "sysok"; |
| 0 rows inserted/updated/deleted |
| ij> drop schema "sys" restrict; |
| 0 rows inserted/updated/deleted |
| ij> drop schema "sysok" restrict; |
| 0 rows inserted/updated/deleted |
| ij> -- data types not supported |
| create table NOTYPE(i int, b BOOLEAN); |
| 0 rows inserted/updated/deleted |
| ij> create table NOTYPE(i int, b TINYINT); |
| ERROR 42X94: TYPE 'TINYINT' does not exist. |
| ij> create table NOTYPE(i int, b java.lang.String); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 39. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table NOTYPE(i int, b com.acme.Address); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 38. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table NOTYPE(i int, b org.apache.derby.vti.VTIEnvironment); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 40. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- VTI in the DELETE statement |
| -- beetle 5234 |
| CREATE TABLE testCS (col1 int, col2 char(30), col3 int); |
| 0 rows inserted/updated/deleted |
| ij> INSERT INTO testCS VALUES (100, 'asdf', 732); |
| 1 row inserted/updated/deleted |
| ij> 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- 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); |
| ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.serializabletypes.ExternalTable. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- VTI in the SELECT statement |
| -- beetle 5234 |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from new com.acme.myVTI() as T; |
| ERROR 42X01: Syntax error: com.acme.myVTI. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from new org.apache.derbyTesting.not.myVTI() as T; |
| ERROR 42X01: Syntax error: org.apache.derbyTesting.not.myVTI. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from syscs_diag.lock_table; |
| XID |TYPE |MODE|TABLENAME |LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ij> -- VTI in CREATE TRIGGER statement |
| -- beetle 5234 |
| CREATE TABLE tb1(a int); |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| DROP TABLE tb1; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE testCS; |
| 0 rows inserted/updated/deleted |
| ij> -- beetle 5177 |
| create table maps2 (country_ISO_code char(2)); |
| 0 rows inserted/updated/deleted |
| ij> -- 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); |
| ERROR 42X01: Syntax error: Encountered "btree" at line 2, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create unique btree index map_idx2 on maps2(country_ISO_code); |
| ERROR 42X01: Syntax error: Encountered "btree" at line 1, column 15. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop table maps2; |
| 0 rows inserted/updated/deleted |
| ij> -- SET LOCKING clause in DB2 mode |
| -- beetle 5208 |
| create table maps1 (country_ISO_code char(2)) set locking = table; |
| ERROR 42X01: Syntax error: Encountered "set" at line 3, column 47. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table maps2 (country_ISO_code char(2)) set locking = row; |
| ERROR 42X01: Syntax error: Encountered "set" at line 1, column 47. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop table maps1; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MAPS1' because it does not exist. |
| ij> drop table maps2; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MAPS2' because it does not exist. |
| ij> -- ALTER TABLE statement |
| -- beetle 5201 |
| -- Locking syntax |
| -- negative tests |
| create table tb1 (country_ISO_code char(2)); |
| 0 rows inserted/updated/deleted |
| ij> alter table tb1 set locking = table; |
| ERROR 42X01: Syntax error: Encountered "set" at line 1, column 17. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> alter table tb1 set locking = row; |
| ERROR 42X01: Syntax error: Encountered "set" at line 1, column 17. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- Locking syntax |
| -- positive tests |
| -- beetle 5201 |
| create table tb2 (country_ISO_code char(2)); |
| 0 rows inserted/updated/deleted |
| ij> alter table tb2 locksize table; |
| 0 rows inserted/updated/deleted |
| ij> alter table tb2 locksize row; |
| 0 rows inserted/updated/deleted |
| ij> -- clean up |
| drop table tb1; |
| 0 rows inserted/updated/deleted |
| ij> drop table tb2; |
| 0 rows inserted/updated/deleted |
| ij> -- VTI in the DELETE statement |
| -- beetle 5234 |
| CREATE TABLE testCS (col1 int, col2 char(30), col3 int); |
| 0 rows inserted/updated/deleted |
| ij> INSERT INTO testCS VALUES (100, 'asdf', 732); |
| 1 row inserted/updated/deleted |
| ij> 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- 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); |
| ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- VTI in the SELECT statement |
| -- beetle 5234 |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- VTI in CREATE TRIGGER statement |
| -- beetle 5234 |
| CREATE TABLE tb1(a int); |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- alter table tt drop column b; This is now supported by Derby |
| alter table tt rename to ttnew; |
| ERROR 42X01: Syntax error: Encountered "rename" at line 2, column 16. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> alter table tt rename c to d; |
| ERROR 42X01: Syntax error: Encountered "rename" at line 1, column 16. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> rename column tt.c to tt.d; |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 25. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> ALTER TABLE TT DROP CONSTRAINT ABC2 RESTRICT; |
| ERROR 42X01: Syntax error: Encountered "RESTRICT" at line 1, column 37. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- CASCADE/RESTRICT on DROP TABLE |
| -- beetle 5206 |
| DROP TABLE TT CASCADE; |
| ERROR 42X01: Syntax error: Encountered "CASCADE" at line 3, column 15. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> DROP TABLE TT RESTRICT; |
| ERROR 42X01: Syntax error: Encountered "RESTRICT" at line 1, column 15. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> DROP AGGREGATE STDEV; |
| ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> CREATE AGGREGATE MAXBUTONE FOR org.apache.derbyTesting.functionTests.util.aggregates.MaxButOneDef; |
| ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 1, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> DROP AGGREGATE MAXBUTONE; |
| ERROR 42X01: Syntax error: Encountered "AGGREGATE" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop class alias Hashtable; |
| ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop method alias hashtable; |
| ERROR 42X01: Syntax error: Encountered "method" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- RENAME COLUMN |
| -- beetle 5222 |
| create table TT(col1 int, col2 int); |
| 0 rows inserted/updated/deleted |
| ij> rename column TT.col2 to newcolumn2; |
| 0 rows inserted/updated/deleted |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SET TRIGGERS FOR tb1 DISABLED; |
| ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SET TRIGGERS testtrig2 ENABLED; |
| ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SET TRIGGERS testtrig2 DISABLED; |
| ERROR 42X01: Syntax error: Encountered "TRIGGERS" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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); |
| 0 rows inserted/updated/deleted |
| ij> create table mm (x org.apache.derbyTesting.functionTests.util.ManyMethods); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 30. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table sc (x org.apache.derbyTesting.functionTests.util.SubClass); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 30. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select i from t1 where i instanceof java.lang.Integer; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select i from t1 where i instanceof java.lang.Number; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select i from t1 where i instanceof java.lang.Object; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select s from t1 where s instanceof java.lang.Integer; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select b from t1 where b instanceof java.lang.Boolean; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 26. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select c10 from t1 where c10 instanceof java.lang.String; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 30. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select vc30 from t1 where vc30 instanceof java.lang.String; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 32. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select x instanceof org.apache.derbyTesting.functionTests.util.SubClass from mm; |
| ERROR 42X01: Syntax error: Encountered "org" at line 1, column 21. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select x instanceof org.apache.derbyTesting.functionTests.util.SubSubClass from mm; |
| ERROR 42X01: Syntax error: Encountered "org" at line 1, column 21. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select (i + i) instanceof java.lang.Integer from t1; |
| ERROR 42X01: Syntax error: Encountered "java" at line 1, column 27. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select (i instanceof java.lang.Integer) = true from t1; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 1, column 11. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testtype2(col1 bit varying(10)); |
| ERROR 42X01: Syntax error: Encountered "bit" at line 1, column 29. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> --- boolean datatype already disabled |
| create table testtype3(col1 boolean); |
| 0 rows inserted/updated/deleted |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testtype6(col1 LONG BIT VARYING); |
| ERROR 42X01: Syntax error: Encountered "BIT" at line 1, column 34. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testtype7(col1 LONG BINARY); |
| ERROR 42X01: Syntax error: Encountered "BINARY" at line 1, column 34. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 42X94: TYPE 'TINYINT' does not exist. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testtype13 (a Tour); |
| ERROR 42X94: TYPE 'TOUR' does not exist. |
| 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; |
| 0 rows inserted/updated/deleted |
| 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; |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 "DISABLED" at line 1, column 20. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| & |
| - |
| E |
| E |
| ij> SET CONSTRAINTS fk ENABLED; |
| ERROR 42X01: Syntax error: Encountered "ENABLED" at line 1, column 20. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| & |
| - |
| E |
| E |
| ij> SET CONSTRAINTS ALL DISABLED; |
| ERROR 42X01: Syntax error: Encountered "DISABLED" at line 1, column 21. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SELECT STATE FROM SYS.SYSCONSTRAINTS; |
| & |
| - |
| E |
| E |
| ij> SET CONSTRAINTS FOR testsetconst1 ENABLED; |
| ERROR 42X01: Syntax error: Encountered "FOR" at line 1, column 17. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table deftest2 (i int default 3+4); |
| ERROR 42X01: Syntax error: Encountered "+" at line 1, column 39. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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> -- 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; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE testconst2; |
| 0 rows inserted/updated/deleted |
| ij> -- CREATE TRIGGERS |
| -- beetle 5253 |
| 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> -- 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; |
| 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> 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); |
| 0 rows inserted/updated/deleted |
| ij> -- clean up |
| DROP TRIGGER testtrig1; |
| 0 rows inserted/updated/deleted |
| ij> DROP TRIGGER testtrig2; |
| 0 rows inserted/updated/deleted |
| ij> DROP TRIGGER testtrig3; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE tb1; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE tb2; |
| 0 rows inserted/updated/deleted |
| ij> -- 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); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values 1; |
| 1 row inserted/updated/deleted |
| ij> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
| ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
| ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
| ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- statements should pass in db2 compat mode |
| -- beetle 5260 |
| autocommit off; |
| ij> create table t1(c1 int not null constraint asdf primary key); |
| 0 rows inserted/updated/deleted |
| ij> commit; |
| ij> insert into t1 values 1; |
| 1 row inserted/updated/deleted |
| ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation serializable; |
| 0 rows inserted/updated/deleted |
| ij> -- rollback should find nothing to undo |
| rollback; |
| ij> select * from t1; |
| C1 |
| ----------- |
| 1 |
| ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| NULL |
| ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation read committed; |
| 0 rows inserted/updated/deleted |
| ij> -- rollback should find nothing to undo |
| rollback; |
| ij> select * from t1; |
| C1 |
| ----------- |
| 1 |
| ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| NULL |
| ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation repeatable read; |
| 0 rows inserted/updated/deleted |
| ij> -- rollback should find nothing to undo |
| rollback; |
| ij> select * from t1; |
| C1 |
| ----------- |
| 1 |
| ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| NULL |
| ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level |
| set isolation read uncommitted; |
| 0 rows inserted/updated/deleted |
| ij> -- rollback should find nothing to undo |
| rollback; |
| ij> select * from t1; |
| C1 |
| ----------- |
| 1 |
| ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| NULL |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- SET ISOLATION statement |
| -- beetle 5260 |
| -- set isolation statement that are supported in db2 |
| create table t1(c1 int not null constraint asdf primary key); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values 1; |
| 1 row inserted/updated/deleted |
| ij> set isolation serializable; |
| 0 rows inserted/updated/deleted |
| ij> set isolation read committed; |
| 0 rows inserted/updated/deleted |
| ij> set isolation repeatable read; |
| 0 rows inserted/updated/deleted |
| ij> set isolation read uncommitted; |
| 0 rows inserted/updated/deleted |
| ij> -- clean up |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- SELECT statement testing |
| -- beetle 5255 |
| CREATE TABLE t1(col1 int, col2 int); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE t2(col1 int, col2 int); |
| 0 rows inserted/updated/deleted |
| ij> INSERT INTO t1 VALUES(3,4); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO t2 VALUES(3,4); |
| 1 row inserted/updated/deleted |
| ij> -- (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; |
| COL1 |COL2 |COL1 |COL2 |
| ----------------------------------------------- |
| 3 |4 |3 |4 |
| ij> SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE false; |
| COL1 |COL2 |COL1 |COL2 |
| ----------------------------------------------- |
| ij> -- (5) TRUE and FALSE constants are no longer disabled in WHERE clause of DELETE statement |
| DELETE FROM t1 where true; |
| 1 row inserted/updated/deleted |
| ij> DELETE FROM t1 where false; |
| 0 rows inserted/updated/deleted |
| ij> -- (5) TRUE and FALSE constants are no longer disabled in WHERE clause of DELETE statement |
| UPDATE t2 SET col1 = NULL WHERE true; |
| 1 row inserted/updated/deleted |
| ij> UPDATE t2 SET col1 = NULL WHERE false; |
| 0 rows inserted/updated/deleted |
| ij> -- (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; |
| ERROR 42X01: Syntax error: Encountered "AT" at line 3, column 18. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SELECT * FROM t1 AT ISOLATION READ COMMITTED; |
| ERROR 42X01: Syntax error: Encountered "AT" at line 1, column 18. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SELECT * FROM t1 AT ISOLATION SERIALIZABLE; |
| ERROR 42X01: Syntax error: Encountered "AT" at line 1, column 18. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> SELECT * FROM t1 AT ISOLATION REPEATABLE READ; |
| ERROR 42X01: Syntax error: Encountered "AT" at line 1, column 18. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| DROP TABLE t1; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE t2; |
| 0 rows inserted/updated/deleted |
| ij> -- 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))); |
| ERROR 42X01: Syntax error: Encountered "cast" at line 2, column 45. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testsessionuser(col1 BLOB(3K) default cast(session_user as blob(3k))); |
| ERROR 42X01: Syntax error: Encountered "cast" at line 1, column 52. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testcurrentuser(col1 BLOB(3K) default cast(current_user as blob(3k))); |
| ERROR 42X01: Syntax error: Encountered "cast" at line 1, column 52. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> create table testschema(col1 BLOB(3K) default cast(current schema as blob(3k))); |
| ERROR 42X01: Syntax error: Encountered "cast" at line 1, column 47. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- 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); |
| 0 rows inserted/updated/deleted |
| ij> -- increasing the length of the varchar column |
| alter table testmodify alter col1 set data type varchar(60); |
| 0 rows inserted/updated/deleted |
| ij> -- specifying the interval between consecutive values of col2, the identity column |
| alter table testmodify alter col2 set increment by 2; |
| 0 rows inserted/updated/deleted |
| ij> -- clean up |
| drop table testmodify; |
| 0 rows inserted/updated/deleted |
| ij> -- (1) adding more than one column |
| -- beetle 5268 |
| -- db2 compat mode should support the following statements |
| create table testaddcol (col1 int); |
| 0 rows inserted/updated/deleted |
| ij> alter table testaddcol add column col2 int add col3 int; |
| ERROR 42X01: Syntax error: Encountered "add" at line 1, column 44. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop table testaddcol; |
| 0 rows inserted/updated/deleted |
| ij> -- (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); |
| 0 rows inserted/updated/deleted |
| ij> create table testaddconst2 (col1 int not null primary key, col2 int not null unique); |
| 0 rows inserted/updated/deleted |
| ij> create table testaddconst3 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table testaddconst4 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int); |
| 0 rows inserted/updated/deleted |
| ij> -- adding more than one unique-constraint |
| alter table testaddconst3 add primary key (col1) add unique (col2); |
| ERROR 42X01: Syntax error: Encountered "add" at line 2, column 50. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> alter table testaddconst3 add unique (col3) add unique (col4); |
| ERROR 42X01: Syntax error: Encountered "add" at line 1, column 45. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- adding more than one referential-constraint |
| alter table testaddconst3 add foreign key (col1) references testaddconst1(col1) add foreign key (col2) references testaddconst2(col2); |
| ERROR 42X01: Syntax error: Encountered "add" at line 2, column 81. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- adding more than one check-constraint |
| alter table testaddconst3 add check (col5 is null) add check (col6 is null); |
| ERROR 42X01: Syntax error: Encountered "add" at line 2, column 52. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- 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); |
| ERROR 42X01: Syntax error: Encountered "add" at line 2, column 49. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| drop table testaddconst1; |
| 0 rows inserted/updated/deleted |
| ij> drop table testaddconst2; |
| 0 rows inserted/updated/deleted |
| ij> drop table testaddconst3; |
| 0 rows inserted/updated/deleted |
| ij> drop table testaddconst4; |
| 0 rows inserted/updated/deleted |
| ij> -- (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)); |
| 0 rows inserted/updated/deleted |
| ij> 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)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE testdropconst3 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk4 UNIQUE , PRIMARY KEY(col1)); |
| 0 rows inserted/updated/deleted |
| ij> CREATE TABLE testdropconst4 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk5 UNIQUE , PRIMARY KEY(col1)); |
| 0 rows inserted/updated/deleted |
| ij> 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)); |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> alter table testdropconst2 drop primary key drop constraint uk2 drop constraint uk3; |
| ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 45. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorclob where colone < 70; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorclob where colone = 50; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorclob where colone != 10; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorclob where colone <= 70; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorclob where colone >= 10; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorclob where colone <> 10; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| 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 (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorblob where colone < 999999; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorblob where colone = 00350030; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorblob where colone != 10; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorblob where colone <= 999999; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorblob where colone >= 10; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> select * from testoperatorblob where colone <> 10; |
| ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| 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. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values cast(B'1' as clob(1M)); |
| ERROR 42X01: Syntax error: Encountered "\'1\'" at line 1, column 14. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values cast(B'1' as blob(1M)); |
| ERROR 42X01: Syntax error: Encountered "\'1\'" at line 1, column 14. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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> -- 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; |
| ERROR 42X01: Syntax error: java.lang.Integer::MAX_VALUE. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> VALUES (1)->noSuchField; |
| ERROR 42X01: Syntax error: Encountered "->" at line 1, column 11. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- beetle 5299 |
| -- disable Method Invocations |
| VALUES (1)->toString(); |
| ERROR 42X01: Syntax error: java.lang.Integer.toString. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> VALUES 1.->toString(); |
| ERROR 42X01: Syntax error: java.math.BigDecimal.toString. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> VALUES 1..getClass()->toString(); |
| ERROR 42X01: Syntax error: Encountered "1." at line 1, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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> -- 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; |
| 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 '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 'foo%bar3' like 1; |
| ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. |
| ij> select match_me from likeable where 1 like 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 1; |
| ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. |
| ij> -- beetle 5845 |
| select match_me from likeable where match_me like CURRENT_DATE; |
| ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. |
| ij> create table likes (dt date, tm time, ts timestamp); |
| 0 rows inserted/updated/deleted |
| ij> insert into likes values (current_date, current_time, current_timestamp); |
| 1 row inserted/updated/deleted |
| ij> insert into likes values ('2004-03-03', current_time, current_timestamp); |
| 1 row inserted/updated/deleted |
| ij> select * from likes where dt like '2004-03-0_'; |
| ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. |
| ij> select * from likes where tm like '_8:%:1%'; |
| ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. |
| ij> select * from likes where ts like '2004-04-09 08:5%'; |
| ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. |
| ij> drop table likeable; |
| 0 rows inserted/updated/deleted |
| ij> drop table likes; |
| 0 rows inserted/updated/deleted |
| ij> -- READ ONLY not allowed in "FOR" clause of a select. |
| create table roTable (i int); |
| 0 rows inserted/updated/deleted |
| ij> insert into roTable values (8); |
| 1 row inserted/updated/deleted |
| ij> select * from roTable for update; |
| I |
| ----------- |
| 8 |
| ij> select * from roTable for update of i; |
| I |
| ----------- |
| 8 |
| ij> select * from roTable for fetch only; |
| I |
| ----------- |
| 8 |
| ij> select * from roTable for read only; |
| I |
| ----------- |
| 8 |
| ij> drop table roTable; |
| 0 rows inserted/updated/deleted |
| ij> -- No support for Java types in CAST statements; |
| values CAST (NULL AS CLASS java.lang.Integer); |
| ERROR 42X01: Syntax error: Encountered "java" at line 3, column 28. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (NULL AS CLASS com.acme.SomeClass); |
| ERROR 42X01: Syntax error: Encountered "com" at line 1, column 28. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (NULL AS CLASS java.sql.Date); |
| ERROR 42X01: Syntax error: Encountered "java" at line 1, column 28. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (NULL AS java.lang.Integer); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 31. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (NULL AS com.acme.SomeClass); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 30. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (NULL AS java.sql.Date); |
| ERROR 42X01: Syntax error: Encountered "sql" at line 1, column 27. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (? AS CLASS java.lang.Integer); |
| ERROR 42X01: Syntax error: Encountered "java" at line 1, column 25. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (? AS CLASS com.acme.SomeClass); |
| ERROR 42X01: Syntax error: Encountered "com" at line 1, column 25. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (? AS CLASS java.sql.Date); |
| ERROR 42X01: Syntax error: Encountered "java" at line 1, column 25. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (? AS java.lang.Integer); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 28. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (? AS com.acme.SomeClass); |
| ERROR 42X01: Syntax error: Encountered "." at line 1, column 27. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> values CAST (? AS java.sql.Date); |
| ERROR 42X01: Syntax error: Encountered "sql" at line 1, column 24. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- No support for BIT_LENGTH, OCTET_LENGTH, TRIP and SUBSTRING in DB2 compatibility mode |
| values BIT_LENGTH(X'55'); |
| ERROR 42Y03: 'BIT_LENGTH' is not recognized as a function or procedure. |
| ij> values OCTET_LENGTH('asdfasdfasdf'); |
| ERROR 42Y03: 'OCTET_LENGTH' is not recognized as a function or procedure. |
| ij> values TRIM('x' FROM 'xasdf x'); |
| 1 |
| ------- |
| asdf |
| ij> values SUBSTRING('12345' FROM 3 FOR 2); |
| ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. |
| ij> -- Tests for explicit nulls. Not allowed in DB2, defect 5589 |
| -- Should fail. |
| create table t1 ( i int null); |
| ERROR 42X01: Syntax error: Encountered "null" at line 3, column 25. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- Should pass. |
| create table t1 (i int); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values null; |
| 1 row inserted/updated/deleted |
| ij> -- Alter table add explict null column should also fail. |
| alter table t1 add column j int null; |
| ERROR 42X01: Syntax error: Encountered "null" at line 2, column 33. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- Should pass |
| alter table t1 add column j int; |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values (null, null); |
| 1 row inserted/updated/deleted |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- 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); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 (i int); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig1a NO CASCADE before insert on t1 for each row mode db2sql insert into t2 values(1); |
| ERROR 42Z9D: 'INSERT' statements are not allowed in 'BEFORE' triggers. |
| ij> create trigger trig1b NO CASCADE before insert on t1 for each row mode db2sql update t2 set i=1 where i=2; |
| ERROR 42Z9D: 'UPDATE' statements are not allowed in 'BEFORE' triggers. |
| ij> create trigger trig1c NO CASCADE before insert on t1 for each row mode db2sql delete from t2 where i=8; |
| ERROR 42Z9D: 'DELETE' statements are not allowed in 'BEFORE' triggers. |
| ij> -- 2) AFTER triggers |
| create trigger trig2a after insert on t1 for each row mode db2sql insert into t2 values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig2b after insert on t1 for each row mode db2sql update t2 set i=1 where i=2; |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig2c after insert on t1 for each row mode db2sql delete from t2 where i=8; |
| 0 rows inserted/updated/deleted |
| ij> -- 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); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3b after update on t1 referencing old as ooga for each row mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3c after update on t1 referencing new as ooga for each row mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3d after delete on t1 referencing old as ooga for each row mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3e after insert on t1 referencing new_table as ooga for each statement mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3f after update on t1 referencing old_table as ooga for each statement mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3g after update on t1 referencing new_table as ooga for each statement mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig3h after delete on t1 referencing old_table as ooga for each statement mode db2sql values(1); |
| 0 rows inserted/updated/deleted |
| ij> -- Next 4 should fail. |
| create trigger trig3i after insert on t1 referencing old as ooga for each row mode db2sql values(1); |
| ERROR 42Y92: INSERT triggers may only reference new transition variables/tables. |
| ij> create trigger trig3j after delete on t1 referencing new as ooga for each row mode db2sql values(1); |
| ERROR 42Y92: DELETE triggers may only reference old transition variables/tables. |
| ij> create trigger trig3k after insert on t1 referencing old_table as ooga for each statement mode db2sql values(1); |
| ERROR 42Y92: INSERT triggers may only reference new transition variables/tables. |
| ij> create trigger trig3m after delete on t1 referencing new_table as ooga for each statement mode db2sql values(1); |
| ERROR 42Y92: DELETE triggers may only reference old transition variables/tables. |
| ij> -- 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); |
| ERROR 42Y92: BEFORE triggers may only reference row transition variables/tables. |
| ij> create trigger trig4b no cascade before update on t1 referencing new_table as ooga for each statement mode db2sql values(1); |
| ERROR 42Y92: BEFORE triggers may only reference row transition variables/tables. |
| ij> -- 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); |
| ERROR 42Y92: STATEMENT triggers may only reference table transition variables/tables. |
| ij> create trigger trig5b after update on t1 referencing new as ooga for each statement mode db2sql values(1); |
| ERROR 42Y92: STATEMENT triggers may only reference table transition variables/tables. |
| ij> -- cleanup for 5538: |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> -- Beetle 5637: Require FOR EACH clause in DB2 mode. Optional in Cloudscape mode. |
| create table t1(i int); |
| 0 rows inserted/updated/deleted |
| ij> -- 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); |
| 0 rows inserted/updated/deleted |
| ij> create trigger trig2 after insert on t1 for each statement mode db2sql values (8); |
| 0 rows inserted/updated/deleted |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- match SUBSTR builtin function out of range handling (5570). |
| create table x1 (c char(10)); |
| 0 rows inserted/updated/deleted |
| ij> insert into x1 values ('foo'); |
| 1 row inserted/updated/deleted |
| ij> -- DB2: Raises ERROR 22011: out of range, Cloudscape doesn't |
| select substr('foo', -2,1) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: Raises ERROR 22011: out of range, Cloudscape return NULL |
| select substr('foo', 1,-1) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> select substr('foo', 2,-1) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> select substr('foo', 3,-2) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> select substr('foo', -2,-3) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: ERROR 22011 out of range, Cloudscape returns empty string |
| select substr('foo', 5) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> select substr('foo', 6,3) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: Raises ERROR 22011: out of range, Cloudscape returns 'f' |
| select substr('foo', 0,1) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: ERROR 22011 out of range, Cloudscape return 'foo' |
| select substr('foo', 1,4) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: ERROR 22011 out of range, Cloudscape return 'foo' |
| select substr('foo', -5) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: ERROR 22011 out of range |
| select substr('foo', -6,3) from x1; |
| 1 |
| ---- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- DB2: Returns an empty value, Cloudscape returns NULL |
| select substr('foo', 1,0) from x1; |
| 1 |
| --------------- |
| ij> select substr('foo', 2,0) from x1; |
| 1 |
| --------------- |
| ij> select substr('foo', 3,0) from x1; |
| 1 |
| --------------- |
| ij> -- DB2: Raises ERROR 22011: out of range, Cloudscape returns NULL |
| select substr('foo', 4,0) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> select substr('foo', 5,0) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> select substr('foo', 6,0) from x1; |
| 1 |
| --------------- |
| ERROR 22011: The second or third argument of the SUBSTR function is out of range. |
| ij> -- Beetle 5630: A column check constraint can only refer to that column in DB2 |
| create table t1(c1 int, c2 int check (c1 > 5)); |
| ERROR 42621: A check constraint or generated column that is defined with 'C2' is invalid. |
| ij> -- 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)); |
| ERROR 42621: A check constraint or generated column that is defined with 'C2' is invalid. |
| ij> -- Same test with alter table |
| create table t1(c1 int); |
| 0 rows inserted/updated/deleted |
| ij> alter table t1 add column c2 int constraint ck2 check(c2 > c1); |
| ERROR 42621: A check constraint or generated column that is defined with 'C2' is invalid. |
| ij> -- These should pass, uses table constraints |
| create table t2(c1 int, c2 int, check (c1 > 5)); |
| 0 rows inserted/updated/deleted |
| ij> create table t3(i int, j int, check (j > 5)); |
| 0 rows inserted/updated/deleted |
| ij> alter table t1 add column c2 int; |
| 0 rows inserted/updated/deleted |
| ij> alter table t1 add constraint t1con check(c2 > c1); |
| 0 rows 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> -- Beetle 5638: DB2 requires matching target and result columns for insert |
| create table t1 ( i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 ( i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values (1, 1); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (2, 2); |
| 1 row inserted/updated/deleted |
| ij> -- negative tests, mismatch of columns |
| insert into t1 select i from t2; |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> insert into t1(i) select * from t2; |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> insert into t1(i, j) select j from t2; |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> insert into t1 select * from t2 union select i from t2; |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> insert into t1 select j from t2 union select j from t2; |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> insert into t1(i) select * from t2 union all select * from t2; |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> insert into t1(i, j) select i, j from t2 union all i from t2; |
| ERROR 42X01: Syntax error: Encountered "i" at line 1, column 52. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- positive cases |
| insert into t1 select * from t2; |
| 1 row inserted/updated/deleted |
| ij> select * from t1; |
| I |J |
| ----------------------- |
| 1 |1 |
| 2 |2 |
| ij> insert into t1(i,j) select * from t2 union select i, j from t2; |
| 1 row inserted/updated/deleted |
| ij> insert into t1(i) select i from t2 union all select j from t2; |
| 2 rows inserted/updated/deleted |
| ij> select * from t1; |
| I |J |
| ----------------------- |
| 1 |1 |
| 2 |2 |
| 2 |2 |
| 2 |NULL |
| 2 |NULL |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> -- Beetle 5667: DB2 requires non-nullable columns to have a default in ALTER TABLE |
| create table t1( i int); |
| 0 rows inserted/updated/deleted |
| ij> -- Negative cases |
| alter table t1 add column j int not null; |
| ERROR 42601: In an ALTER TABLE statement, the column 'J' has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL. |
| ij> alter table t1 add column j int not null default null; |
| ERROR 42601: In an ALTER TABLE statement, the column 'J' has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL. |
| ij> -- positive cases |
| alter table t1 add column j int; |
| 0 rows inserted/updated/deleted |
| ij> alter table t1 add column k int not null default 5; |
| 0 rows inserted/updated/deleted |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- 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); |
| 0 rows inserted/updated/deleted |
| ij> select * from t1 where ((1=1) IS TRUE); |
| ERROR 42X01: Syntax error: Encountered "TRUE" at line 1, column 34. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from t1 where ((1=1) IS NOT TRUE); |
| ERROR 42X01: Syntax error: Encountered "TRUE" at line 1, column 38. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from t1 where ((1=0) IS FALSE); |
| ERROR 42X01: Syntax error: Encountered "FALSE" at line 1, column 34. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from t1 where ((1=0) IS NOT FALSE); |
| ERROR 42X01: Syntax error: Encountered "FALSE" at line 1, column 38. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * from t1 where (null IS UNKNOWN); |
| ERROR 42X01: Syntax error: Encountered "null" at line 1, column 25. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> -- Beetle 5635, 5645 and 5633: Generated column name issues |
| create table t1(i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> create table t2(c1 int, c2 int); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values (1, 1); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (2, 2); |
| 1 row inserted/updated/deleted |
| ij> -- 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; |
| 1 |2 |
| ----------------------- |
| 1 |1 |
| 2 |2 |
| ij> select i as c1, j as c2 from t1 |
| union all |
| select c1, c2 from t2 |
| order by 1; |
| C1 |C2 |
| ----------------------- |
| 1 |1 |
| 2 |2 |
| ij> -- Prevent Cloudscape from using generated column names for ordering |
| select i+1 from t1 order by "SQLCol1"; |
| ERROR 42X04: Column 'SQLCol1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SQLCol1' is not a column in the target table. |
| ij> select i+1 from t1 order by SQLCol1; |
| ERROR 42X04: Column 'SQLCOL1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SQLCOL1' is not a column in the target table. |
| ij> values (1,2,3),(4,5,6),(7,8,9) order by "SQLCol1"; |
| ERROR 42X78: Column 'SQLCol1' is not in the result of the query expression. |
| ij> -- Column names for a CREATE VIEW should be specified when result table has unnamed columns. |
| create view v1 as values 1; |
| ERROR 42908: The CREATE VIEW statement does not include a column list. |
| ij> create view v1 as select i+1 from t1; |
| ERROR 42908: The CREATE VIEW statement does not include a column list. |
| ij> create view v1 as select i+1 as i from t1; |
| 0 rows inserted/updated/deleted |
| ij> create view v2(c) as select i+1 from t1; |
| 0 rows inserted/updated/deleted |
| ij> drop view v1; |
| 0 rows inserted/updated/deleted |
| ij> drop view v2; |
| 0 rows inserted/updated/deleted |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> -- 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)); |
| 0 rows inserted/updated/deleted |
| ij> alter table tb1 compress; |
| ERROR 42X01: Syntax error: COMPRESS. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> alter table tb1 compress sequential; |
| ERROR 42X01: Syntax error: COMPRESS. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- clean up |
| drop table tb1; |
| 0 rows inserted/updated/deleted |
| ij> -- Beetle 5717: Disable adding primary or unique constraints on non-nullable columns |
| -- negative tests |
| create table t1 (c1 int, c2 int); |
| 0 rows inserted/updated/deleted |
| ij> alter table t1 add constraint pk1 primary key (c1); |
| ERROR 42831: 'C1' cannot be a column of a primary key because it can contain null values. |
| ij> alter table t1 add constraint uc1 unique (c2); |
| 0 rows inserted/updated/deleted |
| ij> -- positive tests |
| create table t2 (c1 int not null, c2 char(10) not null); |
| 0 rows inserted/updated/deleted |
| ij> alter table t2 add constraint pk2 primary key (c1); |
| 0 rows inserted/updated/deleted |
| ij> alter table t2 add constraint uc2 unique (c2); |
| 0 rows inserted/updated/deleted |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> -- 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; |
| ERROR 42X01: Syntax error: Encountered "statistics" at line 3, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- 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; |
| ERROR 42X01: Syntax error: Encountered "runtimestatistics" at line 3, column 5. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- following runtime statistics related sql will fail in db2 mode but will run fine in Cloudscape mode |
| create table t1 (c1 int, c2 int); |
| 0 rows inserted/updated/deleted |
| ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); |
| 0 rows inserted/updated/deleted |
| ij> select * from t1; |
| C1 |C2 |
| ----------------------- |
| ij> values runtimestatistics()->getScanStatisticsText(); |
| ERROR 42Y03: 'RUNTIMESTATISTICS' is not recognized as a function or procedure. |
| ij> values runtimestatistics()->toString(); |
| ERROR 42Y03: 'RUNTIMESTATISTICS' is not recognized as a function or procedure. |
| ij> -- following runtime statistics related sql is not supported anymore and will not run in any mode |
| UPDATE STATISTICS FOR TABLE T1; |
| ERROR 42X01: Syntax error: Encountered "FOR" at line 2, column 19. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> DROP STATISTICS FOR TABLE T1; |
| ERROR 42X01: Syntax error: Encountered "STATISTICS" at line 1, column 6. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> |
