MDEV-12137 DELETE statement with the same source and target

single-table deletes only

1 files changed, 178 insertions, 0 deletions

diff --git a/mysql-test/r/delete_use_source.result b/mysql-test/r/delete_use_source.result
new file mode 100644
index 00000000000..c7ca43083cf
--- /dev/null
+++ b/mysql-test/r/delete_use_source.result
@@ -0,0 +1,178 @@
+set sql_mode=oracle;
+use test;
+create or replace table tab_delete(c1 integer not null,c2 integer not null) engine=InnoDb;
+create index tab_delete_c1 on tab_delete(c1);
+create or replace view view_delete as select * from tab_delete where c1 in (0,1);
+CREATE or replace PROCEDURE gendata(a int, count int ) AS
+i INT:=0;
+BEGIN
+FOR i IN 1 .. count
+LOOP
+insert into tab_delete values (a,i);
+END LOOP;
+END;
+/
+create or replace trigger trg after delete on tab_delete for each row
+begin
+declare c int;
+begin
+if old.c1 = 1 then
+select count(*) into c from tab_delete where c1!=old.c1;
+SIGNAL SQLSTATE '45000' set table_name=c;
+end if;
+end;
+end;
+/
+set @count=500;
+call gendata(0,@count);
+call gendata(1,50);
+call gendata(2,20);
+call gendata(3,20);
+commit;
+#
+# Delete with limit (quick select - range acces)
+#
+start transaction;
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+affected rows: 1
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+affected rows: 0
+select count(*) from view_delete where c1=0;
+count(*)
+499
+rollback;
+#
+# Delete
+#
+start transaction;
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ;
+affected rows: 500
+rollback;
+#
+# Delete with exists
+#
+start transaction;
+select count(*) from view_delete where c1=2;
+count(*)
+0
+delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+affected rows: 20
+delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+affected rows: 0
+select count(*) from view_delete where c1=2;
+count(*)
+0
+rollback;
+#
+# Delete throw a view with limit (range access)
+#
+start transaction;
+# Acces by range (quick_select), initied = INDEX
+# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
+# | id   | select_type        | table      | type  | possible_keys | key           | key_len | ref                | rows | Extra       |
+# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
+# |    1 | PRIMARY            | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4       | NULL               |  550 | Using where |
+# |    2 | DEPENDENT SUBQUERY | b          | ref   | tab_delete_c1 | tab_delete_c1 | 4       | test.tab_delete.c1 |   73 | Using index |
+# +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
+delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+affected rows: 1
+delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+affected rows: 0
+select count(*) from view_delete where c1=0;
+count(*)
+499
+rollback;
+#
+# Delete throw a view (ALL access)
+#
+start transaction;
+# Acces by pointer, initied = RND
+# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
+# | id   | select_type        | table      | type | possible_keys | key           | key_len | ref                | rows | Extra       |
+# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
+# |    1 | PRIMARY            | tab_delete | ALL  | tab_delete_c1 | NULL          | NULL    | NULL               |  589 | Using where |
+# |    2 | DEPENDENT SUBQUERY | b          | ref  | tab_delete_c1 | tab_delete_c1 | 4       | test.tab_delete.c1 |  295 | Using index |
+# +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
+delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ;
+affected rows: 500
+select count(*) from view_delete where c1=0;
+count(*)
+0
+rollback;
+#
+# Delete failed due to trigger
+#
+start transaction;
+delete from tab_delete where c1=1 and (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c2 asc limit 10;
+ERROR 45000: Unhandled user-defined exception condition
+rollback;
+start transaction;
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100;
+ERROR 45000: Unhandled user-defined exception condition
+select c1,count(*) from tab_delete group by c1;
+c1	count(*)
+0	500
+1	50
+2	20
+3	20
+rollback;
+#
+# Delete throw a view with returning
+#
+start transaction;
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 asc limit 10 returning c1,c2;
+c1	c2
+0	1
+0	2
+0	3
+0	4
+0	5
+0	6
+0	7
+0	8
+0	9
+0	10
+rollback;
+start transaction;
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 order by c2 desc limit 10 returning c1,c2;
+c1	c2
+0	491
+0	492
+0	493
+0	494
+0	495
+0	496
+0	497
+0	498
+0	499
+0	500
+rollback;
+#
+# Delete from table with more than 150000 rows
+#
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+insert into tab_delete select * from tab_delete;
+select count(*) from tab_delete;
+count(*)
+151040
+with high memory for sort_buffer_size
+SET SESSION sort_buffer_size = 1024000;
+start transaction;
+delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
+affected rows: 128000
+rollback;
+with few memory for sort_buffer_size
+SET SESSION sort_buffer_size = 1024;
+start transaction;
+delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
+affected rows: 128000
+rollback;
+drop procedure if exists gendata;
+drop view if exists view_delete;
+drop table if exists tab_delete;