java/testing/org/apache/derbyTesting/functionTests/master/delete.out

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 "<EOF>" at line 4, 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> -- 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> --
-- Test case for DERBY-4585
--
create table d4585_t1 (id int primary key, a int);
0 rows inserted/updated/deleted
ij> create table d4585_t2 (id int primary key, b int,
                       constraint fk_t2 foreign key (b) references d4585_t1);
0 rows inserted/updated/deleted
ij> create table d4585_t3 (id int primary key, c int);
0 rows inserted/updated/deleted
ij> create table d4585_t4 (d int references d4585_t2);
0 rows inserted/updated/deleted
ij> insert into d4585_t1 values (16,51),(30,12),(39,24),(48,1),(53,46),(61,9);
6 rows inserted/updated/deleted
ij> insert into d4585_t2 values
    (2,16),(3,61),(4,16),(6,30),(7,16),(10,48),(13,30),(15,48),(17,61),
    (18,30),(21,48),(22,53),(23,61),(25,48),(26,30),(27,48),(29,16),(31,39),
    (33,30),(35,61),(37,30),(40,53),(42,53),(45,16),(49,30),(54,53),(57,53),
    (58,61),(60,30),(63,61),(64,30);
31 rows inserted/updated/deleted
ij> insert into d4585_t3 values
    (1,50),(5,50),(8,50),(9,50),(11,36),(12,50),(14,50),(19,50),(20,50),
    (24,36),(28,50),(32,50),(34,50),(38,50),(41,50),(43,50),(46,36),(47,36),
    (51,36),(52,50),(55,36),(56,44),(59,36),(62,36);
24 rows inserted/updated/deleted
ij> insert into d4585_t4 values (7), (33), (57);
3 rows inserted/updated/deleted
ij> -- The query below resulted in a NullPointerException if a certain query plan
-- was chosen. Use an optimizer override to force that plan.
delete from d4585_t4 where d in
  (select id from d4585_t2 --derby-properties constraint=fk_t2
    where b in (select t1.id
                       from d4585_t1 t1, d4585_t3 t3
                       where t1.a=t3.id and t3.c=36));
2 rows inserted/updated/deleted
ij> -- Verify that the correct rows were deleted.
select * from d4585_t4;
D          
-----------
33         
ij> -- Clean up
drop table d4585_t4;
0 rows inserted/updated/deleted
ij> drop table d4585_t3;
0 rows inserted/updated/deleted
ij> drop table d4585_t2;
0 rows inserted/updated/deleted
ij> drop table d4585_t1;
0 rows inserted/updated/deleted
ij>