From abf95afa2a1c23f3c7aa42f44fc665450ebeeea1 Mon Sep 17 00:00:00 2001
From: halfspawn <j.brauge@qualiac.com>
Date: Fri, 7 Jul 2017 17:50:09 +0200
Subject: MDEV-12137 DELETE statement with the same source and target

single-table deletes only
---
 mysql-test/r/delete_use_source.result         | 178 ++++++++++++++++++++++++++
 mysql-test/r/lowercase_view.result            |   3 -
 mysql-test/r/merge.result                     |  42 ++++--
 mysql-test/r/subselect.result                 |   5 +-
 mysql-test/r/subselect_no_exists_to_in.result |   5 +-
 mysql-test/r/subselect_no_mat.result          |   5 +-
 mysql-test/r/subselect_no_opts.result         |   5 +-
 mysql-test/r/subselect_no_scache.result       |   5 +-
 mysql-test/r/subselect_no_semijoin.result     |   5 +-
 mysql-test/r/view.result                      |   3 -
 mysql-test/t/delete_use_source.test           | 172 +++++++++++++++++++++++++
 mysql-test/t/lowercase_view.test              |   4 +-
 mysql-test/t/merge.test                       |  61 ++++++---
 mysql-test/t/subselect.test                   |   8 +-
 mysql-test/t/view.test                        |   7 +-
 15 files changed, 451 insertions(+), 57 deletions(-)
 create mode 100644 mysql-test/r/delete_use_source.result
 create mode 100644 mysql-test/t/delete_use_source.test

(limited to 'mysql-test')

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;
diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index df303807407..3b6aeb9548b 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -66,11 +66,8 @@ ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table '
 update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
 ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 delete from v2Aa where col1 = (select max(col1) from v1Aa);
-ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 'v2Aa'
 delete from v2aA where col1 = (select max(col1) from t1Aa);
-ERROR HY000: The definition of table 'v2aA' prevents operation DELETE on table 'v2aA'
 delete from v2Aa where col1 = (select max(col1) from v2aA);
-ERROR HY000: Table 'v2Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
 ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA'
 delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index f468f47c4c9..6da7eb38655 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -3743,33 +3743,47 @@ ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1
 update m1 set a = ((select max(a) from tmp, v1));
 ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
 delete from m1 where a = (select max(a) from m1);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from m2);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from t1);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from t2);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from t3, m1);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from t3, m2);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from t3, t1);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from t3, t2);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from tmp, m1);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from tmp, m2);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from tmp, t1);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from tmp, t2);
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 delete from m1 where a = (select max(a) from tmp, v1);
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 drop view v1;
 drop temporary table tmp;
 drop table t1, t2, t3, m1, m2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 95a872f1498..98a279c28e9 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -607,11 +607,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a);
 a	b
 2	12
 delete from t1 where b in (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+affected rows: 3
+insert into t1 values (0, 10),(1, 11),(2, 12);
 delete from t1 where b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 a	b
 0	10
 1	11
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 7f07b974bb2..5cee4076ed8 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -611,11 +611,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a);
 a	b
 2	12
 delete from t1 where b in (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+affected rows: 3
+insert into t1 values (0, 10),(1, 11),(2, 12);
 delete from t1 where b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 a	b
 0	10
 1	11
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 57c7a979d61..e7409b0b09c 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -614,11 +614,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a);
 a	b
 2	12
 delete from t1 where b in (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+affected rows: 3
+insert into t1 values (0, 10),(1, 11),(2, 12);
 delete from t1 where b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 a	b
 0	10
 1	11
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 6e8be4a02a7..3bdc91686d7 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -610,11 +610,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a);
 a	b
 2	12
 delete from t1 where b in (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+affected rows: 3
+insert into t1 values (0, 10),(1, 11),(2, 12);
 delete from t1 where b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 a	b
 0	10
 1	11
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1b437f6919d..3beba7c338d 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -613,11 +613,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a);
 a	b
 2	12
 delete from t1 where b in (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+affected rows: 3
+insert into t1 values (0, 10),(1, 11),(2, 12);
 delete from t1 where b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 a	b
 0	10
 1	11
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 6094c7e029d..f2d97078772 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -610,11 +610,12 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a);
 a	b
 2	12
 delete from t1 where b in (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+affected rows: 3
+insert into t1 values (0, 10),(1, 11),(2, 12);
 delete from t1 where b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 a	b
 0	10
 1	11
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 77794ac1c82..50a48ade3bd 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -994,11 +994,8 @@ ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3
 update v3 set v3.col1 = (select max(col1) from v3);
 ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 delete from v2 where col1 = (select max(col1) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'
 delete from v2 where col1 = (select max(col1) from t1);
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'
 delete from v2 where col1 = (select max(col1) from v2);
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
 ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'
 delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
diff --git a/mysql-test/t/delete_use_source.test b/mysql-test/t/delete_use_source.test
new file mode 100644
index 00000000000..ddd7858ee77
--- /dev/null
+++ b/mysql-test/t/delete_use_source.test
@@ -0,0 +1,172 @@
+-- source include/have_innodb.inc
+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);
+delimiter /;
+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;
+/
+delimiter ;/
+set @count=500;
+call gendata(0,@count);
+call gendata(1,50);
+call gendata(2,20);
+call gendata(3,20);
+commit;
+
+--echo #
+--echo # Delete with limit (quick select - range acces)
+--echo #
+
+start transaction;
+--enable_info
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+--disable_info
+select count(*) from view_delete where c1=0;
+rollback;
+
+--echo #
+--echo # Delete
+--echo #
+
+start transaction;
+--enable_info
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ;
+--disable_info
+rollback;
+
+--echo #
+--echo # Delete with exists
+--echo #
+
+start transaction;
+select count(*) from view_delete where c1=2;
+--enable_info
+delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+--disable_info
+select count(*) from view_delete where c1=2;
+rollback;
+
+--echo #
+--echo # Delete throw a view with limit (range access)
+--echo #
+
+start transaction;
+--echo # Acces by range (quick_select), initied = INDEX
+--echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
+--echo # | id   | select_type        | table      | type  | possible_keys | key           | key_len | ref                | rows | Extra       |
+--echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
+--echo # |    1 | PRIMARY            | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4       | NULL               |  550 | Using where |
+--echo # |    2 | DEPENDENT SUBQUERY | b          | ref   | tab_delete_c1 | tab_delete_c1 | 4       | test.tab_delete.c1 |   73 | Using index |
+--echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
+# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+--enable_info
+delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+--disable_info
+select count(*) from view_delete where c1=0;
+rollback;
+
+--echo #
+--echo # Delete throw a view (ALL access)
+--echo #
+
+start transaction;
+--echo # Acces by pointer, initied = RND
+--echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
+--echo # | id   | select_type        | table      | type | possible_keys | key           | key_len | ref                | rows | Extra       |
+--echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
+--echo # |    1 | PRIMARY            | tab_delete | ALL  | tab_delete_c1 | NULL          | NULL    | NULL               |  589 | Using where |
+--echo # |    2 | DEPENDENT SUBQUERY | b          | ref  | tab_delete_c1 | tab_delete_c1 | 4       | test.tab_delete.c1 |  295 | Using index |
+--echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
+# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500;
+--enable_info
+delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ;
+--disable_info
+select count(*) from view_delete where c1=0;
+rollback;
+
+
+--echo #
+--echo # Delete failed due to trigger
+--echo #
+
+start transaction;
+--enable_info
+--error ER_SIGNAL_EXCEPTION
+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;
+--disable_info
+rollback;
+start transaction;
+--enable_info
+--error ER_SIGNAL_EXCEPTION
+delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100;
+--disable_info
+select c1,count(*) from tab_delete group by c1;
+rollback;
+
+--echo #
+--echo # Delete throw a view with returning
+--echo #
+
+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;
+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;
+rollback;
+
+
+--echo #
+--echo # Delete from table with more than 150000 rows
+--echo #
+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;
+
+--echo with high memory for sort_buffer_size
+SET SESSION sort_buffer_size = 1024000;
+start transaction;
+--enable_info
+delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
+--disable_info
+rollback;
+
+--echo with few memory for sort_buffer_size
+SET SESSION sort_buffer_size = 1024;
+start transaction;
+--enable_info
+delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
+--disable_info
+rollback;
+
+drop procedure if exists gendata;
+drop view if exists view_delete;
+drop table if exists tab_delete;
diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test
index 52be911cde0..6d53e6d9130 100644
--- a/mysql-test/t/lowercase_view.test
+++ b/mysql-test/t/lowercase_view.test
@@ -73,11 +73,9 @@ update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
 update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
 -- error 1093
 update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
--- error 1443
+# Works since MDEV-12137 (no more error 1093)
 delete from v2Aa where col1 = (select max(col1) from v1Aa);
--- error 1443
 delete from v2aA where col1 = (select max(col1) from t1Aa);
--- error 1093
 delete from v2Aa where col1 = (select max(col1) from v2aA);
 -- error 1443
 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index 09f313616f1..c35dd39170b 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -2763,39 +2763,66 @@ update m1 set a = ((select max(a) from tmp, t2));
 update m1 set a = ((select max(a) from v1));
 --error ER_VIEW_PREVENT_UPDATE
 update m1 set a = ((select max(a) from tmp, v1));
-
-
---error ER_UPDATE_TABLE_USED
+# Works since MDEV-12137
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from m1);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from m2);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from t1);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from t2);
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 
---error ER_UPDATE_TABLE_USED
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from t3, m1);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from t3, m2);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from t3, t1);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from t3, t2);
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 
---error ER_UPDATE_TABLE_USED
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from tmp, m1);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from tmp, m2);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from tmp, t1);
---error ER_UPDATE_TABLE_USED
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously :ER_UPDATE_TABLE_USED
 delete from m1 where a = (select max(a) from tmp, t2);
- 
---error ER_VIEW_PREVENT_UPDATE
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+
+# previously : ER_VIEW_PREVENT_UPDATE
 delete from m1 where a = (select max(a) from v1);
---error ER_VIEW_PREVENT_UPDATE
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
+# previously : ER_VIEW_PREVENT_UPDATE
 delete from m1 where a = (select max(a) from tmp, v1);
+insert into t1 (a) values (1);
+insert into t2 (a) values (1);
 
 drop view v1;
 drop temporary table tmp;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 282013222de..59694635c70 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -340,12 +340,16 @@ insert into t1 values (0, 10),(1, 11),(2, 12);
 insert into t2 values (1, 21),(2, 12),(3, 23);
 select * from t1;
 select * from t1 where b = (select b from t2 where t1.a = t2.a);
--- error ER_UPDATE_TABLE_USED
+# Works since MDEV-12137
+# previously : ER_UPDATE_TABLE_USED
+--enable_info
 delete from t1 where b in (select b from t1);
+--disable_info
+insert into t1 values (0, 10),(1, 11),(2, 12);
 -- error ER_SUBQUERY_NO_1_ROW
 delete from t1 where b = (select b from t2);
 delete from t1 where b = (select b from t2 where t1.a = t2.a);
-select * from t1;
+select * from t1 order by b;
 drop table t1, t2;
 
 #multi-delete with subselects
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index fa130afc84b..9ed37a2bf65 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -914,11 +914,12 @@ update v3 set v3.col1 = (select max(col1) from t1);
 update v3 set v3.col1 = (select max(col1) from v2);
 -- error ER_UPDATE_TABLE_USED
 update v3 set v3.col1 = (select max(col1) from v3);
--- error ER_VIEW_PREVENT_UPDATE
+# Works since MDEV-12137
+# Previously error ER_VIEW_PREVENT_UPDATE
 delete from v2 where col1 = (select max(col1) from v1);
--- error ER_VIEW_PREVENT_UPDATE
+# Previously error ER_VIEW_PREVENT_UPDATE
 delete from v2 where col1 = (select max(col1) from t1);
--- error ER_UPDATE_TABLE_USED
+# Previously error ER_UPDATE_TABLE_USED
 delete from v2 where col1 = (select max(col1) from v2);
 -- error ER_VIEW_PREVENT_UPDATE
 delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
-- 
cgit v1.2.1


From c65cce3698edde272b8452c1fecec3d9522cea6e Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Fri, 7 Jul 2017 19:55:31 +0200
Subject: MDEV-12137 DELETE statement with the same source and target

* various cleanups (mostly cosmetic)
* remove useless tests (that were tesing the error condition)
* optimize delete_use_source test (from 6 mins to 50 seconds,
  mainly by removing two huge rollbacks at the end).
---
 mysql-test/r/delete_use_source.result | 145 ++++++++++++-----------------
 mysql-test/r/lowercase_view.result    |   3 -
 mysql-test/r/merge.result             |  42 ---------
 mysql-test/r/view.result              |   3 -
 mysql-test/t/delete_use_source.test   | 168 +++++++++++++---------------------
 mysql-test/t/lowercase_view.test      |   4 -
 mysql-test/t/merge.test               |  60 ------------
 mysql-test/t/view.test                |   7 --
 8 files changed, 123 insertions(+), 309 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/delete_use_source.result b/mysql-test/r/delete_use_source.result
index c7ca43083cf..7886caf6ba4 100644
--- a/mysql-test/r/delete_use_source.result
+++ b/mysql-test/r/delete_use_source.result
@@ -1,43 +1,18 @@
-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;
+create table t1(c1 integer not null,c2 integer not null, key (c1)) engine=InnoDb;
+create view v1 as select * from t1 where c1 in (0,1);
+insert t1 select 0,seq from seq_1_to_500;
+insert t1 select 1,seq from seq_1_to_50;
+insert t1 select 2,seq from seq_1_to_20;
+insert t1 select 3,seq from seq_1_to_20;
 #
 # 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;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.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;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
 affected rows: 0
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
 count(*)
 499
 rollback;
@@ -45,21 +20,21 @@ rollback;
 # Delete
 #
 start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ;
 affected rows: 500
 rollback;
 #
 # Delete with exists
 #
 start transaction;
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
 count(*)
 0
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 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);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
 affected rows: 0
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
 count(*)
 0
 rollback;
@@ -67,18 +42,15 @@ 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;
+explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	c1	c1	4	NULL	550	Using where
+2	DEPENDENT SUBQUERY	b	ref	c1	c1	4	test.t1.c1	82	Using index
+delete from v1 where (select count(*) from t1 b where b.c1=v1.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;
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
 affected rows: 0
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
 count(*)
 499
 rollback;
@@ -86,41 +58,50 @@ 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 ;
+explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	c1	NULL	NULL	NULL	717	Using where
+2	DEPENDENT SUBQUERY	b	ref	c1	c1	4	test.t1.c1	82	Using index
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
 affected rows: 500
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
 count(*)
 0
 rollback;
 #
 # Delete failed due to trigger
 #
+create trigger trg after delete on t1 for each row
+begin
+declare c int;
+begin
+if old.c1 = 1 then
+select count(*) into c from t1 where c1!=old.c1;
+SIGNAL SQLSTATE '45000' set table_name=c;
+end if;
+end;
+end;
+/
 start transaction;
-delete from 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;
+delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10;
 ERROR 45000: Unhandled user-defined exception condition
 rollback;
 start transaction;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100;
 ERROR 45000: Unhandled user-defined exception condition
-select c1,count(*) from tab_delete group by c1;
+select c1,count(*) from t1 group by c1;
 c1	count(*)
 0	500
 1	50
 2	20
 3	20
 rollback;
+drop trigger trg;
 #
 # 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;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2;
 c1	c2
 0	1
 0	2
@@ -134,7 +115,7 @@ c1	c2
 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;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 desc limit 10 returning c1,c2;
 c1	c2
 0	491
 0	492
@@ -147,32 +128,24 @@ c1	c2
 0	499
 0	500
 rollback;
+drop view v1;
+drop table t1;
 #
 # 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;
+create table t1(c1 integer not null,c2 integer not null, key (c1));
+insert t1 select 0,seq from seq_1_to_128000;
+insert t1 select 1,seq from seq_1_to_25600;
+select count(*) from t1;
 count(*)
-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);
+153600
+# with a lot of memory for sort_buffer_size
+set session sort_buffer_size = 1024000;
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
 affected rows: 128000
-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);
+# with little memory for sort_buffer_size
+insert t1 select 0,seq from seq_1_to_128000;
+set session sort_buffer_size = 1024;
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
 affected rows: 128000
-rollback;
-drop procedure if exists gendata;
-drop view if exists view_delete;
-drop table if exists tab_delete;
+drop table t1;
diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index 3b6aeb9548b..6ccfe29b2cd 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -65,9 +65,6 @@ update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
 ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v3aA'
 update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
 ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete from v2Aa where col1 = (select max(col1) from v1Aa);
-delete from v2aA where col1 = (select max(col1) from t1Aa);
-delete from v2Aa where col1 = (select max(col1) from v2aA);
 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
 ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA'
 delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 6da7eb38655..f55251ac199 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -3742,48 +3742,6 @@ update m1 set a = ((select max(a) from v1));
 ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
 update m1 set a = ((select max(a) from tmp, v1));
 ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
-delete from m1 where a = (select max(a) from m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from t3, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-delete from m1 where a = (select max(a) from tmp, v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
 drop view v1;
 drop temporary table tmp;
 drop table t1, t2, t3, m1, m2;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 50a48ade3bd..ef2e0935592 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -993,9 +993,6 @@ update v3 set v3.col1 = (select max(col1) from v2);
 ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'
 update v3 set v3.col1 = (select max(col1) from v3);
 ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete from v2 where col1 = (select max(col1) from v1);
-delete from v2 where col1 = (select max(col1) from t1);
-delete from v2 where col1 = (select max(col1) from v2);
 delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
 ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'
 delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
diff --git a/mysql-test/t/delete_use_source.test b/mysql-test/t/delete_use_source.test
index ddd7858ee77..a54d12c3d65 100644
--- a/mysql-test/t/delete_use_source.test
+++ b/mysql-test/t/delete_use_source.test
@@ -1,37 +1,12 @@
--- source include/have_innodb.inc
-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);
-delimiter /;
-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;
-/
-delimiter ;/
-set @count=500;
-call gendata(0,@count);
-call gendata(1,50);
-call gendata(2,20);
-call gendata(3,20);
-commit;
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+create table t1(c1 integer not null,c2 integer not null, key (c1)) engine=InnoDb;
+create view v1 as select * from t1 where c1 in (0,1);
+
+insert t1 select 0,seq from seq_1_to_500;
+insert t1 select 1,seq from seq_1_to_50;
+insert t1 select 2,seq from seq_1_to_20;
+insert t1 select 3,seq from seq_1_to_20;
 
 --echo #
 --echo # Delete with limit (quick select - range acces)
@@ -39,10 +14,10 @@ commit;
 
 start transaction;
 --enable_info
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 limit 1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 limit 1;
 --disable_info
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
 rollback;
 
 --echo #
@@ -50,9 +25,8 @@ rollback;
 --echo #
 
 start transaction;
---enable_info
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) = 500 ;
---disable_info
+--enable_info ONCE
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 ;
 rollback;
 
 --echo #
@@ -60,12 +34,12 @@ rollback;
 --echo #
 
 start transaction;
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
 --enable_info
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
-delete from tab_delete where c1=2 and exists(select 'x' from tab_delete b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
+delete from t1 where c1=2 and exists(select 'x' from t1 b where b.c2<10);
 --disable_info
-select count(*) from view_delete where c1=2;
+select count(*) from v1 where c1=2;
 rollback;
 
 --echo #
@@ -73,19 +47,12 @@ rollback;
 --echo #
 
 start transaction;
---echo # Acces by range (quick_select), initied = INDEX
---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
---echo # | id   | select_type        | table      | type  | possible_keys | key           | key_len | ref                | rows | Extra       |
---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
---echo # |    1 | PRIMARY            | tab_delete | range | tab_delete_c1 | tab_delete_c1 | 4       | NULL               |  550 | Using where |
---echo # |    2 | DEPENDENT SUBQUERY | b          | ref   | tab_delete_c1 | tab_delete_c1 | 4       | test.tab_delete.c1 |   73 | Using index |
---echo # +------+--------------------+------------+-------+---------------+---------------+---------+--------------------+------+-------------+
-# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
 --enable_info
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 limit 1;
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
 --disable_info
-select count(*) from view_delete where c1=0;
+select count(*) from v1 where c1=0;
 rollback;
 
 --echo #
@@ -93,80 +60,73 @@ rollback;
 --echo #
 
 start transaction;
---echo # Acces by pointer, initied = RND
---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
---echo # | id   | select_type        | table      | type | possible_keys | key           | key_len | ref                | rows | Extra       |
---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
---echo # |    1 | PRIMARY            | tab_delete | ALL  | tab_delete_c1 | NULL          | NULL    | NULL               |  589 | Using where |
---echo # |    2 | DEPENDENT SUBQUERY | b          | ref  | tab_delete_c1 | tab_delete_c1 | 4       | test.tab_delete.c1 |  295 | Using index |
---echo # +------+--------------------+------------+------+---------------+---------------+---------+--------------------+------+-------------+
-# explain delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500;
---enable_info
-delete from view_delete where (select count(*) from tab_delete b where b.c1=view_delete.c1) = 500 ;
---disable_info
-select count(*) from view_delete where c1=0;
+explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
+--enable_info ONCE
+delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
+select count(*) from v1 where c1=0;
 rollback;
 
-
 --echo #
 --echo # Delete failed due to trigger
 --echo #
 
+delimiter /;
+create trigger trg after delete on t1 for each row
+begin
+  declare c int;
+  begin
+  if old.c1 = 1 then
+    select count(*) into c from t1 where c1!=old.c1;
+    SIGNAL SQLSTATE '45000' set table_name=c;
+  end if;
+  end;
+end;
+/
+delimiter ;/
+
 start transaction;
---enable_info
 --error ER_SIGNAL_EXCEPTION
-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;
---disable_info
+delete from t1 where c1=1 and (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c2 asc limit 10;
 rollback;
 start transaction;
---enable_info
 --error ER_SIGNAL_EXCEPTION
-delete from tab_delete where (select count(*) from tab_delete b where b.c1=tab_delete.c1) > 0 order by c1 desc limit 100;
---disable_info
-select c1,count(*) from tab_delete group by c1;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) > 0 order by c1 desc limit 100;
+select c1,count(*) from t1 group by c1;
 rollback;
 
+drop trigger trg;
+
 --echo #
 --echo # Delete throw a view with returning
 --echo #
 
 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;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 asc limit 10 returning c1,c2;
 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;
+delete from t1 where (select count(*) from t1 b where b.c1=t1.c1) = 500 order by c2 desc limit 10 returning c1,c2;
 rollback;
 
+drop view v1;
+drop table t1;
 
 --echo #
 --echo # Delete from table with more than 150000 rows
 --echo #
-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;
-
---echo with high memory for sort_buffer_size
-SET SESSION sort_buffer_size = 1024000;
-start transaction;
---enable_info
-delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
---disable_info
-rollback;
+create table t1(c1 integer not null,c2 integer not null, key (c1));
+insert t1 select 0,seq from seq_1_to_128000;
+insert t1 select 1,seq from seq_1_to_25600;
+select count(*) from t1;
 
---echo with few memory for sort_buffer_size
-SET SESSION sort_buffer_size = 1024;
-start transaction;
---enable_info
-delete from tab_delete where c1=0 and exists(select 'x' from tab_delete b where b.c1<10);
---disable_info
-rollback;
+--echo # with a lot of memory for sort_buffer_size
+set session sort_buffer_size = 1024000;
+--enable_info ONCE
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
+
+--echo # with little memory for sort_buffer_size
+insert t1 select 0,seq from seq_1_to_128000;
+set session sort_buffer_size = 1024;
+--enable_info ONCE
+delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
 
-drop procedure if exists gendata;
-drop view if exists view_delete;
-drop table if exists tab_delete;
+drop table t1;
diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test
index 6d53e6d9130..4c91383db60 100644
--- a/mysql-test/t/lowercase_view.test
+++ b/mysql-test/t/lowercase_view.test
@@ -73,10 +73,6 @@ update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
 update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
 -- error 1093
 update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
-# Works since MDEV-12137 (no more error 1093)
-delete from v2Aa where col1 = (select max(col1) from v1Aa);
-delete from v2aA where col1 = (select max(col1) from t1Aa);
-delete from v2Aa where col1 = (select max(col1) from v2aA);
 -- error 1443
 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
 -- error 1443
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index c35dd39170b..95c78caf034 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -2763,66 +2763,6 @@ update m1 set a = ((select max(a) from tmp, t2));
 update m1 set a = ((select max(a) from v1));
 --error ER_VIEW_PREVENT_UPDATE
 update m1 set a = ((select max(a) from tmp, v1));
-# Works since MDEV-12137
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from t3, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, m1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, m2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, t1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously :ER_UPDATE_TABLE_USED
-delete from m1 where a = (select max(a) from tmp, t2);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-
-# previously : ER_VIEW_PREVENT_UPDATE
-delete from m1 where a = (select max(a) from v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
-# previously : ER_VIEW_PREVENT_UPDATE
-delete from m1 where a = (select max(a) from tmp, v1);
-insert into t1 (a) values (1);
-insert into t2 (a) values (1);
 
 drop view v1;
 drop temporary table tmp;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9ed37a2bf65..00a040ccfdc 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -914,13 +914,6 @@ update v3 set v3.col1 = (select max(col1) from t1);
 update v3 set v3.col1 = (select max(col1) from v2);
 -- error ER_UPDATE_TABLE_USED
 update v3 set v3.col1 = (select max(col1) from v3);
-# Works since MDEV-12137
-# Previously error ER_VIEW_PREVENT_UPDATE
-delete from v2 where col1 = (select max(col1) from v1);
-# Previously error ER_VIEW_PREVENT_UPDATE
-delete from v2 where col1 = (select max(col1) from t1);
-# Previously error ER_UPDATE_TABLE_USED
-delete from v2 where col1 = (select max(col1) from v2);
 -- error ER_VIEW_PREVENT_UPDATE
 delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
 -- error ER_VIEW_PREVENT_UPDATE
-- 
cgit v1.2.1