mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
75d354a23a
Gain MySQL compatibility by allowing table aliases in a single table statement. This now supports the syntax of: DELETE [delete_opts] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] .... The delete.test is from MySQL commit 1a72b69778a9791be44525501960b08856833b8d / Change-Id: Iac3a2b5ed993f65b7f91acdfd60013c2344db5c0. Co-Author: Gleb Shchepa <gleb.shchepa@oracle.com> (for delete.test) Reviewed by Igor Babaev (igor@mariadb.com)
407 lines
11 KiB
Text
407 lines
11 KiB
Text
create table t1(c1 integer not null,c2 integer not null, key (c1))
|
|
ENGINE=InnoDB STATS_PERSISTENT=1;
|
|
create view v1 as select * from t1 where c1 in (0,1);
|
|
insert t1 select 0,seq from seq_1_to_500;
|
|
insert t1 select 1,seq from seq_1_to_100;
|
|
insert t1 select 2,seq from seq_1_to_50;
|
|
insert t1 select 3,seq from seq_1_to_20;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
#
|
|
# Delete with limit (quick select - range acces)
|
|
#
|
|
start transaction;
|
|
delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
|
|
affected rows: 1
|
|
delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
|
|
affected rows: 0
|
|
select count(*) from v1 where c1=0;
|
|
count(*)
|
|
499
|
|
rollback;
|
|
#
|
|
# Delete
|
|
#
|
|
start transaction;
|
|
delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ;
|
|
affected rows: 500
|
|
rollback;
|
|
#
|
|
# Delete with exists
|
|
#
|
|
start transaction;
|
|
select count(*) from v1 where c1=2;
|
|
count(*)
|
|
0
|
|
delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
|
|
affected rows: 50
|
|
delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
|
|
affected rows: 0
|
|
select count(*) from v1 where c1=2;
|
|
count(*)
|
|
0
|
|
rollback;
|
|
#
|
|
# Delete through a view with limit (range access)
|
|
#
|
|
start transaction;
|
|
explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 range c1 c1 4 NULL 600 Using index condition; Using where
|
|
2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 167 Using index
|
|
delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
|
|
affected rows: 1
|
|
delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
|
|
affected rows: 0
|
|
select count(*) from v1 where c1=0;
|
|
count(*)
|
|
499
|
|
rollback;
|
|
#
|
|
# Delete through a view (ALL access)
|
|
#
|
|
start transaction;
|
|
explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 range c1 c1 4 NULL # Using index condition; Using where
|
|
2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 # Using index
|
|
delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
|
|
affected rows: 500
|
|
select count(*) from v1 where c1=0;
|
|
count(*)
|
|
0
|
|
rollback;
|
|
#
|
|
# Delete failed due to trigger
|
|
#
|
|
create trigger trg after delete on t1 for each row
|
|
begin
|
|
declare c int;
|
|
begin
|
|
if old.c1 = 1 then
|
|
select count(*) into c from t1 where c1!=old.c1;
|
|
SIGNAL SQLSTATE '45000' set table_name=c;
|
|
end if;
|
|
end;
|
|
end;
|
|
/
|
|
start transaction;
|
|
delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10;
|
|
ERROR 45000: Unhandled user-defined exception condition
|
|
rollback;
|
|
start transaction;
|
|
delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100;
|
|
ERROR 45000: Unhandled user-defined exception condition
|
|
select c1,count(*) from t1 group by c1;
|
|
c1 count(*)
|
|
0 500
|
|
1 100
|
|
2 50
|
|
3 20
|
|
rollback;
|
|
drop trigger trg;
|
|
#
|
|
# Delete through a view with returning
|
|
#
|
|
start transaction;
|
|
delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2;
|
|
c1 c2
|
|
0 1
|
|
0 2
|
|
0 3
|
|
0 4
|
|
0 5
|
|
0 6
|
|
0 7
|
|
0 8
|
|
0 9
|
|
0 10
|
|
rollback;
|
|
start transaction;
|
|
delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 desc limit 10 returning c1,c2;
|
|
c1 c2
|
|
0 491
|
|
0 492
|
|
0 493
|
|
0 494
|
|
0 495
|
|
0 496
|
|
0 497
|
|
0 498
|
|
0 499
|
|
0 500
|
|
rollback;
|
|
drop view v1;
|
|
drop table t1;
|
|
#
|
|
# Delete from table with more than 150000 rows
|
|
#
|
|
create table t1(c1 integer not null,c2 integer not null, key (c1));
|
|
insert t1 select 0,seq from seq_1_to_128000;
|
|
insert t1 select 1,seq from seq_1_to_25600;
|
|
select count(*) from t1;
|
|
count(*)
|
|
153600
|
|
# with a lot of memory for sort_buffer_size
|
|
set session sort_buffer_size = 1024000;
|
|
delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
|
|
affected rows: 128000
|
|
# with little memory for sort_buffer_size
|
|
insert t1 select 0,seq from seq_1_to_128000;
|
|
set session sort_buffer_size = 1024;
|
|
delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
|
|
affected rows: 128000
|
|
drop table t1;
|
|
#
|
|
# MDEV-17954: multi-table DELETE with the same source and target
|
|
#
|
|
create table t1 (c1 int, c2 int, c3 int);
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
#
|
|
# Single-table DELETE with the same source and target
|
|
# handled as multi-table DELETE
|
|
#
|
|
explain delete from t1
|
|
where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1)
|
|
delete from t1
|
|
where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3);
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 3 3
|
|
2 3 6
|
|
2 4 7
|
|
2 5 8
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
prepare stmt from "delete from t1
|
|
where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3)";
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 3 3
|
|
2 3 6
|
|
2 4 7
|
|
2 5 8
|
|
delete from t1;
|
|
insert into t1 values
|
|
(2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
2 3 6
|
|
2 4 7
|
|
2 5 8
|
|
deallocate prepare stmt;
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
#
|
|
# Multi-table DELETE with the same source and target
|
|
#
|
|
create table t2 (c1 int, c2 int, c3 int);
|
|
insert into t2 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8);
|
|
explain delete from t1 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 7
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where
|
|
delete from t1 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 4 7
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
prepare stmt from "delete from t1 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1";
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 4 7
|
|
delete from t1;
|
|
insert into t1 values
|
|
(2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
2 4 7
|
|
deallocate prepare stmt;
|
|
explain delete from t1 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 system NULL NULL NULL NULL 1
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 7 Using where
|
|
delete from t1 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
2 4 7
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
prepare stmt from "delete from t1 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1";
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 4 7
|
|
delete from t1;
|
|
insert into t1 values
|
|
(2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
2 4 7
|
|
deallocate prepare stmt;
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
explain delete from t1,t2 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 7 Using where
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where
|
|
delete from t1,t2 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 4 7
|
|
select * from t2;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
delete from t2;
|
|
insert into t2 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8);
|
|
prepare stmt from "delete from t1,t2 using t1,t2
|
|
where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1";
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 4 7
|
|
select * from t2;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
delete from t1;
|
|
insert into t1 values
|
|
(1,2,2), (1,3,3), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
|
|
delete from t2;
|
|
insert into t2 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5);
|
|
execute stmt;
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 2 2
|
|
1 3 3
|
|
2 3 6
|
|
2 4 7
|
|
2 5 8
|
|
select * from t2;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 1 4
|
|
deallocate prepare stmt;
|
|
drop table t1,t2;
|
|
# End of 11.1
|
|
#
|
|
# MDEV-33988 DELETE (single table) to support table aliases
|
|
#
|
|
create table t1 (c1 int, c2 int, c3 int);
|
|
insert into t1 values
|
|
(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,7), (3,5,8);
|
|
create table t2 (id int auto_increment primary key, a int, key(a));
|
|
insert into t2(a) values (3), (5), (-1);
|
|
# 1. The alias in delete coincides with the table name in IN subquery.
|
|
explain extended
|
|
delete from t1 t2 where t2.c1 in (select a from t2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 8 100.00 Using where
|
|
1 PRIMARY t2 ref a a 5 test.t2.c1 1 100.00 Using index; FirstMatch(t2)
|
|
Warnings:
|
|
Note 1003 delete from `test`.`t1` `t2` using (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t2`.`c1`
|
|
delete from t1 t2 where t2.c1 in (select a from t2);
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
2 1 4
|
|
2 2 5
|
|
2 3 6
|
|
2 5 7
|
|
# 2. The alias in delete is different from the alias in IN subquery
|
|
explain extended
|
|
delete from t1 t_x where t_x.c2 IN (select a from t2 as t_y);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t_x ALL NULL NULL NULL NULL 7 100.00 Using where
|
|
1 PRIMARY t_y ref a a 5 test.t_x.c2 1 100.00 Using index; FirstMatch(t_x)
|
|
Warnings:
|
|
Note 1003 delete from `test`.`t1` `t_x` using (`test`.`t2` `t_y`) where `test`.`t_y`.`a` = `test`.`t_x`.`c2`
|
|
delete from t1 t_x where t_x.c2 IN (select a from t2 as t_y);
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
2 1 4
|
|
2 2 5
|
|
# 3. The alias in delete is the same as the alias in IN subquery.
|
|
explain extended
|
|
delete from t1 as t_x where t_x.c3 IN (select a from t2 t_x);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t_x ALL NULL NULL NULL NULL 4 100.00 Using where
|
|
1 PRIMARY t_x ref a a 5 test.t_x.c3 1 100.00 Using index; FirstMatch(t_x)
|
|
Warnings:
|
|
Note 1003 delete from `test`.`t1` `t_x` using (`test`.`t2` `t_x`) where `test`.`t_x`.`a` = `test`.`t_x`.`c3`
|
|
delete from t1 as t_x where t_x.c3 IN (select a from t2 t_x);
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
2 1 4
|
|
# 4. The table in delete is the alias in IN subquery
|
|
explain extended
|
|
delete from t1 t2 where t2.c1 IN (select a -1 from t2 as t1);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY t1 index NULL a 5 NULL 3 33.33 Using where; Using index; FirstMatch(t2)
|
|
Warnings:
|
|
Note 1003 delete from `test`.`t1` `t2` using (`test`.`t2` `t1`) where `test`.`t2`.`c1` = `test`.`t1`.`a` - 1
|
|
delete from t1 t2 where t2.c1 IN (select a -1 from t2 as t1);
|
|
select * from t1;
|
|
c1 c2 c3
|
|
1 1 1
|
|
1 2 2
|
|
drop table t1, t2;
|
|
# End of 11.6
|