diff options
Diffstat (limited to 'mysql-test/main/delete_use_source_engines.result')
| -rw-r--r-- | mysql-test/main/delete_use_source_engines.result | 8870 |
1 files changed, 8870 insertions, 0 deletions
diff --git a/mysql-test/main/delete_use_source_engines.result b/mysql-test/main/delete_use_source_engines.result new file mode 100644 index 00000000..b1ad9c00 --- /dev/null +++ b/mysql-test/main/delete_use_source_engines.result @@ -0,0 +1,8870 @@ +set @save_default_engine=@@default_storage_engine; +####################################### +# # +# Engine InnoDB # +# # +####################################### +set global innodb_stats_persistent=1; +set default_storage_engine=InnoDB; +create table t1 (c1 integer, c2 integer, c3 integer); +insert into t1(c1,c2,c3) +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); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create view v1 as select * from t1 where c2=2; +####################################### +# Test without any index # +####################################### +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 12.00 3.12 5.56 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 12.50 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 24.50 3.12 1.02 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with an index # +####################################### +create index t1_c2 on t1 (c2,c1); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 11.67 3.12 5.71 Using where; Using index; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 12.50 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 0.25 18.75 100.00 Using index; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a index_subquery t1_c2 t1_c2 5 func 5 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 1.00 100.00 100.00 Using index +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with a primary key # +####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 12.33 3.12 5.41 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 1.00 100.00 100.00 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 1.00 3.12 100.00 Using where; End temporary +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 1 +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +drop view v1; +drop table t1; +# +# Test on dynamic columns (blob) +# +create table assets ( +item_name varchar(32) primary key, -- A common attribute for all items +dynamic_cols blob -- Dynamic columns will be stored here +); +INSERT INTO assets +VALUES ('MariaDB T-shirt', +COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets +VALUES ('Thinkpad Laptop', +COLUMN_CREATE('color', 'black', 'price', 500)); +INSERT INTO assets +VALUES ('Fridge', +COLUMN_CREATE('color', 'white', 'warranty', '5 years')); +INSERT INTO assets +VALUES ('Microwave', +COLUMN_CREATE('warranty', '3 years')); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge white +MariaDB T-shirt blue +Microwave NULL +Thinkpad Laptop black +UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') +WHERE item_name='Fridge'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +Microwave NULL +Thinkpad Laptop black +DELETE FROM assets +WHERE item_name in +(select b.item_name from assets b +where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +Microwave NULL +DELETE FROM assets WHERE item_name='Microwave'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +drop table assets ; +# +# Test on fulltext columns +# +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); +INSERT INTO ft2(copy) VALUES +('MySQL vs MariaDB database'), +('Oracle vs MariaDB database'), +('PostgreSQL vs MariaDB database'), +('MariaDB overview'), +('Foreign keys'), +('Primary keys'), +('Indexes'), +('Transactions'), +('Triggers'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +MySQL vs MariaDB database +Oracle vs MariaDB database +PostgreSQL vs MariaDB database +DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +drop table ft2; +####################################### +# # +# Engine Aria # +# # +####################################### +set default_storage_engine=Aria; +create table t1 (c1 integer, c2 integer, c3 integer); +insert into t1(c1,c2,c3) +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); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create view v1 as select * from t1 where c2=2; +####################################### +# Test without any index # +####################################### +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 12.67 3.12 5.26 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 25.00 100.00 12.00 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 22.67 3.12 1.47 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 +2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 5.00 25.00 20.00 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with an index # +####################################### +create index t1_c2 on t1 (c2,c1); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 11.67 3.12 5.71 Using where; Using index; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 27.00 100.00 14.81 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 0.25 18.75 100.00 Using index; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using where; FirstMatch(t1) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a index_subquery t1_c2 t1_c2 5 func 5 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 1.00 100.00 100.00 Using index +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 +1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 +1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL # Using index condition +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 # Using index; FirstMatch(t1) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a index t1_c2 t1_c2 10 NULL # Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # +analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY a index t1_c2 t1_c2 10 NULL # 16.00 20.00 25.00 Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # 5.00 4.76 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using index; FirstMatch(t1) +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where +3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # Using where; Using index +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # 1.00 100.00 100.00 Using where +3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # 1.00 100.00 100.00 Using where; Using index +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with a primary key # +####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 12.00 3.12 5.56 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 4.00 100.00 100.00 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 1.00 3.12 25.00 Using where; End temporary +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 1 +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 +2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # +analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 6.00 25.00 16.67 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # 1.00 100.00 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +drop view v1; +drop table t1; +# +# Test on dynamic columns (blob) +# +create table assets ( +item_name varchar(32) primary key, -- A common attribute for all items +dynamic_cols blob -- Dynamic columns will be stored here +); +INSERT INTO assets +VALUES ('MariaDB T-shirt', +COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets +VALUES ('Thinkpad Laptop', +COLUMN_CREATE('color', 'black', 'price', 500)); +INSERT INTO assets +VALUES ('Fridge', +COLUMN_CREATE('color', 'white', 'warranty', '5 years')); +INSERT INTO assets +VALUES ('Microwave', +COLUMN_CREATE('warranty', '3 years')); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge white +MariaDB T-shirt blue +Microwave NULL +Thinkpad Laptop black +UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') +WHERE item_name='Fridge'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +Microwave NULL +Thinkpad Laptop black +DELETE FROM assets +WHERE item_name in +(select b.item_name from assets b +where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +Microwave NULL +DELETE FROM assets WHERE item_name='Microwave'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +drop table assets ; +# +# Test on fulltext columns +# +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); +INSERT INTO ft2(copy) VALUES +('MySQL vs MariaDB database'), +('Oracle vs MariaDB database'), +('PostgreSQL vs MariaDB database'), +('MariaDB overview'), +('Foreign keys'), +('Primary keys'), +('Indexes'), +('Transactions'), +('Triggers'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +MySQL vs MariaDB database +Oracle vs MariaDB database +PostgreSQL vs MariaDB database +DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +drop table ft2; +####################################### +# # +# Engine MyISAM # +# # +####################################### +set default_storage_engine=MyISAM; +create table t1 (c1 integer, c2 integer, c3 integer); +insert into t1(c1,c2,c3) +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); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create view v1 as select * from t1 where c2=2; +####################################### +# Test without any index # +####################################### +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 12.67 3.12 5.26 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 25.00 100.00 12.00 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 22.67 3.12 1.47 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 +2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 5.00 25.00 20.00 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with an index # +####################################### +create index t1_c2 on t1 (c2,c1); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a index NULL t1_c2 10 NULL 32 11.67 3.12 5.71 Using where; Using index; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 29.00 100.00 13.79 Using where +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 0.25 18.75 100.00 Using index; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a index_subquery t1_c2 t1_c2 5 func 5 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 1.00 100.00 100.00 Using index +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 +1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 +1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL # Using where; Using filesort +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 # Using index; FirstMatch(t1) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a index t1_c2 t1_c2 10 NULL # Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # +analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY a index t1_c2 t1_c2 10 NULL # 16.00 20.00 25.00 Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # 5.00 5.00 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using index; FirstMatch(t1) +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where +3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # Using where; Using index +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where +analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # 1.00 100.00 100.00 Using where +3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # 1.00 100.00 100.00 Using where; Using index +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with a primary key # +####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 13.33 3.12 5.00 Using where; FirstMatch(t1) +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 4.00 100.00 100.00 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 1.00 3.12 25.00 Using where; End temporary +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 1 +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +# Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED a ALL NULL NULL NULL NULL # +analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 +2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # +analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 3.00 25.00 33.33 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # 1.00 100.00 100.00 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +drop view v1; +drop table t1; +# +# Test on dynamic columns (blob) +# +create table assets ( +item_name varchar(32) primary key, -- A common attribute for all items +dynamic_cols blob -- Dynamic columns will be stored here +); +INSERT INTO assets +VALUES ('MariaDB T-shirt', +COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets +VALUES ('Thinkpad Laptop', +COLUMN_CREATE('color', 'black', 'price', 500)); +INSERT INTO assets +VALUES ('Fridge', +COLUMN_CREATE('color', 'white', 'warranty', '5 years')); +INSERT INTO assets +VALUES ('Microwave', +COLUMN_CREATE('warranty', '3 years')); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge white +MariaDB T-shirt blue +Microwave NULL +Thinkpad Laptop black +UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') +WHERE item_name='Fridge'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +Microwave NULL +Thinkpad Laptop black +DELETE FROM assets +WHERE item_name in +(select b.item_name from assets b +where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +Microwave NULL +DELETE FROM assets WHERE item_name='Microwave'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color +FROM assets ORDER BY item_name; +item_name color +Fridge NULL +MariaDB T-shirt blue +drop table assets ; +# +# Test on fulltext columns +# +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); +INSERT INTO ft2(copy) VALUES +('MySQL vs MariaDB database'), +('Oracle vs MariaDB database'), +('PostgreSQL vs MariaDB database'), +('MariaDB overview'), +('Foreign keys'), +('Primary keys'), +('Indexes'), +('Transactions'), +('Triggers'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +MySQL vs MariaDB database +Oracle vs MariaDB database +PostgreSQL vs MariaDB database +DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +drop table ft2; +####################################### +# # +# Engine MEMORY # +# # +####################################### +set default_storage_engine=MEMORY; +create table t1 (c1 integer, c2 integer, c3 integer); +insert into t1(c1,c2,c3) +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); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create view v1 as select * from t1 where c2=2; +####################################### +# Test without any index # +####################################### +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +affected rows: 2 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 32 +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 32 +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with an index # +####################################### +create index t1_c2 on t1 (c2,c1); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Operation failed +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Operation failed +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +affected rows: 2 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Operation failed +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Operation failed +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32 +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32 +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 2 FirstMatch(t1) +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 2 FirstMatch(t1) +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a ALL t1_c2 NULL NULL NULL 32 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2 +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2 +delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Operation failed +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL t1_c2 NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a ALL t1_c2 NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Operation failed +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const 2 Using where +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const 2 FirstMatch(t1) +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const 2 Using where +3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func 2 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +####################################### +# Test with a primary key # +####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +# +# Delete with value from subquery on the same table +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 +explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 +delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); +affected rows: 4 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + possibly sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +affected rows: 2 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with EXISTS subquery over the updated table +# in WHERE + non-sargable condition +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 4 7 +2 5 8 +21 2 21 +21 4 23 +22 2 24 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 4 33 +32 2 34 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with order by +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 32 +explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED a ALL NULL NULL NULL NULL 32 +delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) +and c2 >= 3 order by c2; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with IN predicand over the updated table in WHERE +# +create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary +delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 3 3 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit - can be deleted +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary +delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete with a limit and an order by +# +create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 32 Using filesort +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete: 2 execution of PS +# +prepare create_tmp_stmt from +"create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from +"delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 6 38 +prepare insert_tmp_stmt from +"insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +2 1 4 +2 3 6 +2 4 7 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +drop table tmp; +# +# Delete in stored procedure +# +create procedure sp() +begin +delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +end +// +create table tmp as select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 +where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) +order by c3 desc limit 1; +CALL sp; +select * from t1; +c1 c2 c3 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; +# +# Delete in stored function +# +create function f1(IN a INT) returns int +begin +delete from t1 where c3 < a order by c3 limit 1; +return 1; +end;// +set @a:=7; +create table tmp as select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a +order by c3 limit 1; +select f1(@a); +f1(@a) +1 +select * from t1; +c1 c2 c3 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; +# +# Delete in trigger +# +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW +DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +DELETE FROM t1 WHERE c2>=3; +affected rows: 20 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +11 1 11 +11 2 12 +12 1 14 +12 2 15 +2 1 4 +2 2 5 +21 2 21 +22 2 24 +31 2 31 +32 2 34 +SELECT * FROM t2; +c1 +SELECT * FROM cnt; +del +20 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; +# +Delete with a reference to view in subquery +# +create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +delete from t1 where t1.c2 in ( select max(a.c2) from v1 a +where a.c1 = t1.c1); +affected rows: 8 +select * from t1; +c1 c2 c3 +1 1 1 +1 3 3 +11 1 11 +11 3 13 +12 1 14 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 3 22 +21 4 23 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 3 32 +31 4 33 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +explain select * from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +explain delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where +delete from v1 where v1.c1 in +(select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +affected rows: 0 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +# +# Delete from view using reference +# to the same view in subquery +# +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 +explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where +2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 +delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) +and c1 = 2 +and exists (select 'X' from v1 a where a.c1 = v1.c1); +affected rows: 1 +select * from t1; +c1 c2 c3 +1 1 1 +1 2 2 +1 3 3 +11 1 11 +11 2 12 +11 3 13 +12 1 14 +12 2 15 +12 3 16 +12 4 17 +12 5 18 +2 1 4 +2 3 6 +2 4 7 +2 5 8 +21 2 21 +21 3 22 +21 4 23 +22 2 24 +22 3 25 +22 4 26 +22 5 27 +22 6 28 +31 2 31 +31 3 32 +31 4 33 +32 2 34 +32 3 35 +32 4 36 +32 5 37 +32 6 38 +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; +drop view v1; +drop table t1; +set @@default_storage_engine=@save_default_engine; +# +# End of 11.0 tests +# |