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. -- -- tests for drop table -- autocommit off; ij> -- -- test simple table - all should work -- create table t1 ( a int); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 (a int); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 (a int); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> -- -- test table with unique constraint - all should work -- create table t1 (a int not null unique); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 (a int not null unique); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 (a int not null unique); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> -- -- test table with primary constraint - all should work -- create table t1 ( a int not null primary key); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 ( a int not null primary key); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 ( a int not null primary key); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> -- -- test table with check constraint - all should work -- create table t1 ( a int check(a > 0)); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 ( a int check(a > 0)); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 ( a int check(a > 0)); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> -- -- test table with index - all should work -- create table t1 ( a int); 0 rows inserted/updated/deleted ij> create index t1index on t1(a); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 (a int); 0 rows inserted/updated/deleted ij> create index t1index on t1(a); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1 (a int); 0 rows inserted/updated/deleted ij> create index t1index on t1(a); 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> -- -- test table with foreign key references; -- create table t1(a int not null primary key); 0 rows inserted/updated/deleted ij> create table t2(a int constraint reft1a references t1(a)); 0 rows inserted/updated/deleted ij> -- this should fail with a dependent constraint error drop table t1; ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'xxxxGENERATED-IDxxxx' because CONSTRAINT 'REFT1A' is dependent on that object. ij> -- this should fail with a dependent constraint error drop table t1; ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'xxxxGENERATED-IDxxxx' because CONSTRAINT 'REFT1A' is dependent on that object. ij> -- dropping dependent constraint alter table t2 drop constraint reft1a; 0 rows inserted/updated/deleted ij> -- this should work since dependent constraint was dropped drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> -- the following should work since no referential constraint is left insert into t2 values(1); 1 row inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> -- -- test table with view -- create table t1(a int, b int); 0 rows inserted/updated/deleted ij> create table t2(c int, d int); 0 rows inserted/updated/deleted ij> create view vt1a as select a from t1; 0 rows inserted/updated/deleted ij> create view vt1b as select b from t1; 0 rows inserted/updated/deleted ij> create view vt1t2 as select * from t1, t2; 0 rows inserted/updated/deleted ij> create view vvt1a as select * from vt1a; 0 rows inserted/updated/deleted ij> create view vvvt1a as select * from vvt1a; 0 rows inserted/updated/deleted ij> -- this should fail with view being a dependent object drop table t1; ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VT1A' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VT1B' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VT1T2' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VVT1A' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VVVT1A' is dependent on that object. ij> -- this should fail with view being a dependent object drop table t1; ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VT1A' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VT1B' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VT1T2' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VVT1A' is dependent on that object. ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' because VIEW 'VVVT1A' is dependent on that object. ij> -- dropping dependent views drop view vvvt1a; 0 rows inserted/updated/deleted ij> drop view vvt1a; 0 rows inserted/updated/deleted ij> drop view vt1t2; 0 rows inserted/updated/deleted ij> drop view vt1b; 0 rows inserted/updated/deleted ij> drop view vt1a; 0 rows inserted/updated/deleted ij> -- this should work after dependent views were dropped drop table t1; 0 rows inserted/updated/deleted ij> -- this shouldn't find the view select * from vt1a; ERROR 42X05: Table/View 'VT1A' does not exist. ij> select * from vt1b; ERROR 42X05: Table/View 'VT1B' does not exist. ij> select * from vt1t2; ERROR 42X05: Table/View 'VT1T2' does not exist. ij> select * from vvt1a; ERROR 42X05: Table/View 'VVT1A' does not exist. ij> select * from vvvt1a; ERROR 42X05: Table/View 'VVVT1A' does not exist. ij> drop table t2; 0 rows inserted/updated/deleted ij> -- -- test table with prepared statement -- create table t1(a int); 0 rows inserted/updated/deleted ij> prepare t1stmt as 'select * from t1'; ij> -- this should work, statement will be invalidated and will fail when recompiled drop table t1; 0 rows inserted/updated/deleted ij> execute t1stmt; ERROR 42X05: Table/View 'T1' does not exist. ij> remove t1stmt; ij> create table t1(a int); 0 rows inserted/updated/deleted ij> prepare t1stmt as 'select * from t1'; ij> -- this should work, statement will be invalidated and will fail when recompiled drop table t1; 0 rows inserted/updated/deleted ij> execute t1stmt; ERROR 42X05: Table/View 'T1' does not exist. ij> remove t1stmt; ij> create table t1(a int); 0 rows inserted/updated/deleted ij> prepare t1stmt as 'select * from t1'; ij> -- this should work, statement will be invalidated and will fail when recompiled drop table t1; 0 rows inserted/updated/deleted ij> execute t1stmt; ERROR 42X05: Table/View 'T1' does not exist. ij> remove t1stmt; ij> -- -- test table with triggers -- create table t1(a int); 0 rows inserted/updated/deleted ij> create table t2(a int); 0 rows inserted/updated/deleted ij> create trigger t1trig after insert on t1 for each row insert into t2 values(1); 0 rows inserted/updated/deleted ij> -- this should work - trigger should be deleted drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1(a int); 0 rows inserted/updated/deleted ij> create trigger t1trig after insert on t1 for each row insert into t2 values(1); 0 rows inserted/updated/deleted ij> -- this should work - trigger should be deleted drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> create table t1(a int); 0 rows inserted/updated/deleted ij> create trigger t1trig after insert on t1 for each row insert into t2 values(1); 0 rows inserted/updated/deleted ij> -- this should work - trigger should be deleted drop table t1; 0 rows inserted/updated/deleted ij> -- t1 shouldn't be found select * from t1; ERROR 42X05: Table/View 'T1' does not exist. ij> drop table t2; 0 rows inserted/updated/deleted ij> -- -- test table within the body of a trigger on another table -- create table t1(a int); 0 rows inserted/updated/deleted ij> create table t2(a int); 0 rows inserted/updated/deleted ij> create trigger t2trig after insert on t2 for each row insert into t1 values(1); 0 rows inserted/updated/deleted ij> -- this should work drop table t1; 0 rows inserted/updated/deleted ij> -- the following should get an error when trying to recompile the trigger action insert into t2 values(1); ERROR 42X05: Table/View 'T1' does not exist. ij> drop table t2; 0 rows inserted/updated/deleted ij> create table t1(a int); 0 rows inserted/updated/deleted ij> create table t2(a int); 0 rows inserted/updated/deleted ij> create trigger t2trig after insert on t2 for each row insert into t1 values(1); 0 rows inserted/updated/deleted ij> -- this should work drop table t1; 0 rows inserted/updated/deleted ij> -- the following should get an error when trying to recompile the trigger action insert into t2 values(1); ERROR 42X05: Table/View 'T1' does not exist. ij> drop table t2; 0 rows inserted/updated/deleted ij> create table t1(a int); 0 rows inserted/updated/deleted ij> create table t2(a int); 0 rows inserted/updated/deleted ij> create trigger t2trig after insert on t2 for each row insert into t1 values(1); 0 rows inserted/updated/deleted ij> -- this should work drop table t1; 0 rows inserted/updated/deleted ij> -- the following should get an error when trying to recompile the trigger action insert into t2 values(1); ERROR 42X05: Table/View 'T1' does not exist. ij> drop table t2; 0 rows inserted/updated/deleted ij> -- -- test drop view -- create table t1(a int); 0 rows inserted/updated/deleted ij> create view vt1 as select * from t1; 0 rows inserted/updated/deleted ij> create view vvt1 as select * from vt1; 0 rows inserted/updated/deleted ij> -- these should fail drop view vt1; ERROR X0Y23: Operation 'DROP VIEW' cannot be performed on object 'VT1' because VIEW 'VVT1' is dependent on that object. ij> drop view vt1 restrict; ERROR 42X01: Syntax error: Encountered "restrict" at line 1, column 15. ij> drop view vt1 cascade; ERROR 42X01: Syntax error: Encountered "cascade" at line 1, column 15. ij> -- -- make sure that indexes are dropped for drop table -- create table t2(a int not null primary key); 0 rows inserted/updated/deleted ij> create table reft2(a int constraint ref1 references t2); 0 rows inserted/updated/deleted ij> -- count should be 2 select count(*) from sys.sysconglomerates c, sys.systables t where t.tableid = c.tableid and t.tablename = 'REFT2'; 1 ----------- 2 ij> -- drop dependent referential constraint alter table reft2 drop constraint ref1; 0 rows inserted/updated/deleted ij> -- should work since dependent constraint was previously dropped drop table t2; 0 rows inserted/updated/deleted ij> -- count should be 1 select count(*) from sys.sysconglomerates c, sys.systables t where t.tableid = c.tableid and t.tablename = 'REFT2'; 1 ----------- 1 ij> -- unsuccessful drop table should not affect open cursor -- beetle 4393 rollback; ij> create table T1 (i int, c varchar(255), d varchar(255)); 0 rows inserted/updated/deleted ij> insert into T1(i) values(1); 1 row inserted/updated/deleted ij> insert into T1(i) values(2); 1 row inserted/updated/deleted ij> get cursor X1 as 'select i from t1 for update of c'; ij> prepare U as 'update t1 set c = CHAR(i) where current of X1'; ij> next X1; I ----------- 1 ij> drop table T1; ERROR X0X95: Operation 'DROP TABLE' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object. ij> execute U; 1 row inserted/updated/deleted ij> select * from T1; I |C |D ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 |1 |NULL 2 |NULL |NULL ij> -- pretend all of the above didn't happen autocommit on; ij>