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. -- -- -- this test is for basic delete functionality -- -- create the table create table t1 (c1 int); 0 rows inserted/updated/deleted ij> create table t2 (c1 int); 0 rows inserted/updated/deleted ij> -- negative tests -- table name required for positioned delete and for searched delete delete; ERROR 42X01: Syntax error: Encountered "" at line 4, column 6. ij> -- populate the table insert into t1 values (1); 1 row inserted/updated/deleted ij> insert into t2 select * from t1; 1 row inserted/updated/deleted ij> -- delete all the rows (only 1) select * from t1; C1 ----------- 1 ij> delete from t1; 1 row inserted/updated/deleted ij> select * from t1; C1 ----------- ij> -- repopulate the table insert into t1 values(2); 1 row inserted/updated/deleted ij> insert into t1 values(3); 1 row inserted/updated/deleted ij> -- delete all the rows (multiple rows) select * from t1; C1 ----------- 2 3 ij> delete from t1; 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> -- test atomicity of multi row deletes create table atom_test (c1 smallint); 0 rows inserted/updated/deleted ij> insert into atom_test values 1, 30000,0, 2; 4 rows inserted/updated/deleted ij> -- overflow delete from atom_test where c1 + c1 > 0; ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> select * from atom_test; C1 ------ 1 30000 0 2 ij> -- divide by 0 delete from atom_test where c1 / c1 = 1; ERROR 22012: Attempt to divide by zero. ij> select * from atom_test; C1 ------ 1 30000 0 2 ij> -- target table in source, should be done in deferred mode -- repopulate the tables insert into t1 values(1); 1 row inserted/updated/deleted ij> insert into t1 values(2); 1 row inserted/updated/deleted ij> insert into t2 select * from t1; 2 rows inserted/updated/deleted ij> autocommit off; ij> select * from t1; C1 ----------- 1 2 ij> delete from t1 where c1 <= (select t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 = 2); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> rollback; ij> delete from t1 where c1 >= (select (select c1 from t1 where c1 = 1) from t2 where c1 = 2); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> rollback; ij> delete from t1 where c1 >= (select (select c1 from t1 a where c1 = 1) from t2 where c1 = 2); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> rollback; ij> -- delete 0 rows - degenerate case for deferred delete delete from t1 where c1 = (select 1 from t2 where 1 = (select c1 from t1 where c1 = 2) ); 0 rows inserted/updated/deleted WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from t1; C1 ----------- 1 2 ij> rollback; ij> -- delete 1 row delete from t1 where c1 = (select c1 from t1 where c1 = 2) and c1 = 2; 1 row inserted/updated/deleted ij> select * from t1; C1 ----------- 1 ij> rollback; ij> delete from t1 where c1 <= (select c1 from (select c1 from t1) a where c1 = 2); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> rollback; ij> delete from t1 where c1 <= (select c1 from t2 where c1 = 37 union select c1 from t1 where c1 = 2); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> rollback; ij> delete from t1 where c1 <= (select c1 from t2 where c1 = 37 union select c1 from (select c1 from t1) a where c1 = 2); 2 rows inserted/updated/deleted ij> select * from t1; C1 ----------- ij> rollback; ij> autocommit on; ij> -- drop the table drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table atom_test; 0 rows inserted/updated/deleted ij> -- -- here we test extra state lying around in the -- deleteResultSet on a prepared statement that -- is executed multiple times. if we don't -- get a nasty error then we are ok -- create table x (x int, y int); 0 rows inserted/updated/deleted ij> create index ix on x(x); 0 rows inserted/updated/deleted ij> insert into x values (1,1),(2,2),(3,3); 3 rows inserted/updated/deleted ij> autocommit off; ij> prepare p as 'delete from x where x = ? and y = ?'; ij> execute p using 'values (1,1)'; 1 row inserted/updated/deleted ij> execute p using 'values (2,2)'; 1 row inserted/updated/deleted ij> commit; ij> -- clean up autocommit on; ij> drop table x; 0 rows inserted/updated/deleted ij> -------------------------------------------- -- -- Test delete piece of the fix for bug171. -- -------------------------------------------- create table bug171_employee( empl_id int, bonus int ); 0 rows inserted/updated/deleted ij> create table bug171_bonuses( empl_id int, bonus int ); 0 rows inserted/updated/deleted ij> insert into bug171_employee( empl_id, bonus ) values ( 1, 0 ), ( 2, 0 ), ( 3, 0 ); 3 rows inserted/updated/deleted ij> insert into bug171_bonuses( empl_id, bonus ) values ( 1, 100 ), ( 1, 100 ), ( 1, 100 ), ( 2, 200 ), ( 2, 200 ), ( 2, 200 ), ( 3, 300 ), ( 3, 300 ), ( 3, 300 ); 9 rows inserted/updated/deleted ij> select * from bug171_employee; EMPL_ID |BONUS ----------------------- 1 |0 2 |0 3 |0 ij> select * from bug171_bonuses; EMPL_ID |BONUS ----------------------- 1 |100 1 |100 1 |100 2 |200 2 |200 2 |200 3 |300 3 |300 3 |300 ij> -- -- The problem query. could not use correlation names in delete. -- delete from bug171_employee e where e.empl_id > 2 and e.bonus < ( select sum( b.bonus ) from bug171_bonuses b where b.empl_id = e.empl_id ); 1 row inserted/updated/deleted ij> select * from bug171_employee; EMPL_ID |BONUS ----------------------- 1 |0 2 |0 ij> -- positioned delete with correlation names autocommit off; ij> get cursor bug171_c2 as 'select * from bug171_employee where empl_id = 2 for update'; ij> next bug171_c2; EMPL_ID |BONUS ----------------------- 2 |0 ij> delete from bug171_employee e where current of bug171_c2; 1 row inserted/updated/deleted ij> close bug171_c2; ij> select * from bug171_employee; EMPL_ID |BONUS ----------------------- 1 |0 ij> autocommit on; ij> -- -- Cleanup -- drop table bug171_employee; 0 rows inserted/updated/deleted ij> drop table bug171_bonuses; 0 rows inserted/updated/deleted ij>