MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites

Created tests for "delete" based on update_use_source.test

4 files changed, 5845 insertions, 0 deletions

diff --git a/mysql-test/include/delete_use_source.inc b/mysql-test/include/delete_use_source.inc
new file mode 100644
index 00000000000..dd705864921
--- /dev/null
+++ b/mysql-test/include/delete_use_source.inc
@@ -0,0 +1,100 @@
+create table t1 (c1 integer, c2 integer, c3 integer);
+
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+
+create view v1 as select * from t1 where c2=2;
+--let $not_temp_table=1
+
+--echo Test without any index
+--source include/delete_use_source_cases.inc
+
+--echo Test with an index
+create index t1_c2 on t1 (c2,c1);
+--source include/delete_use_source_cases.inc
+
+--echo Test with a primary key
+drop index t1_c2 on t1;
+alter table t1 add primary key (c3);
+--source include/delete_use_source_cases.inc
+
+drop view v1;
+drop table t1;
+
+create temporary table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+
+--let $not_temp_table=0
+
+--echo Test without any index
+--source include/delete_use_source_cases.inc
+
+--echo Test with an index
+create index t1_c2 on t1 (c2,c1);
+--source include/delete_use_source_cases.inc
+
+--echo Test with a primary key
+drop index t1_c2 on t1;
+alter table t1 add primary key (c3);
+--source include/delete_use_source_cases.inc
+
+drop table t1;
+
+
+--echo #
+--echo # Test on dynamic columns (blob)
+--echo #
+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;
+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;
+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;
+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;
+drop table assets ;
+
+
+--echo #
+--echo # Test on fulltext columns
+--echo #
+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');
+DELETE FROM ft2 WHERE  MATCH(copy) AGAINST('database');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+drop table ft2;
+
diff --git a/mysql-test/include/delete_use_source_cases.inc b/mysql-test/include/delete_use_source_cases.inc
new file mode 100644
index 00000000000..e8d8f64c6d4
--- /dev/null
+++ b/mysql-test/include/delete_use_source_cases.inc
@@ -0,0 +1,241 @@
+--echo #
+--echo # Delete with value from subquery on the same table, no search clause. ALL access
+--echo #
+let $c = c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with search clause on the same table
+--echo #
+let $c = c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete via RANGE or INDEX access if an index or a primary key exists
+--echo #
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with order by
+--echo #
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete using the index or primary key (c3)
+--echo #
+let $c = c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with a limit - can be delete
+--echo #
+let $c = c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with a limit and an order by
+--echo #
+let $c = c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Double delete
+--echo #
+
+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;
+--sorted_result
+select * from t1;
+
+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;
+--sorted_result
+select * from t1;
+
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+--sorted_result
+select * from t1;
+
+drop table tmp;
+
+--echo #
+--echo # Delete from stored procedure
+--echo #
+
+delimiter //;
+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
+//
+delimiter ;//
+
+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;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+
+drop procedure sp;
+drop table tmp;
+
+--echo #
+--echo # Delete from function
+--echo #
+delimiter //;
+create function f1(IN a INT) returns int
+begin
+  delete from t1 where c3 < a  order by c3 limit 1;
+  return  1;
+end;//
+delimiter ;//
+
+set @a:=7;
+create table tmp as select * from t1 where c3 < @a order by c3 limit 1;
+select f1(@a);
+insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1;
+select f1(@a);
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+ 
+drop function f1;
+drop table tmp;
+
+if($not_temp_table){
+
+--echo #
+--echo # Delete from trigger
+--echo #
+
+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;
+--enable_info ONCE
+DELETE FROM t1 WHERE c2>=3;
+
+--sorted_result
+select * from t1;
+--sorted_result
+SELECT * FROM t2;
+SELECT * FROM cnt;
+
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+
+--echo #
+--echo Delete using a view in subquery
+--echo #
+let $c = t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+
+--echo #
+--echo # Delete throw a view
+--echo #
+let $c = v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+eval create table tmp as select * from v1 where $c;
+let $q = delete from v1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete through  a view and using the view in subquery
+--echo #
+let $c = v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+eval create table tmp as select * from v1 where $c;
+let $q = delete from v1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+}
diff --git a/mysql-test/main/delete_use_source.result b/mysql-test/main/delete_use_source.result
index 329203af613..b96d986fc9b 100644
--- a/mysql-test/main/delete_use_source.result
+++ b/mysql-test/main/delete_use_source.result
@@ -334,3 +334,5486 @@ c1	c2	c3
 2	1	4
 deallocate prepare stmt;
 drop table t1,t2;
+#
+# End of 10.10 tests
+#
+set @save_default_engine= @@default_storage_engine;
+set default_storage_engine=InnoDB;
+create table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+create view v1 as select * from t1 where c2=2;
+Test without any index
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.00	100.00	16.67	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.88	100.00	25.81	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	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	8	8.00	100.00	12.50	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	1.00	100.00	100.00	Using where; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	2	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	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	2	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	2	Using where
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	2	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	2	Using where
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	2	8.00	100.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	7.50	100.00	13.33	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	2	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	2	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	2	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	2	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	2	Using where; Using index
+analyze delete from t1 where c1 <2 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	2	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	2	3.67	100.00	18.18	Using where; Using index
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	range	t1_c2	t1_c2	5	NULL	2	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	range	t1_c2	t1_c2	5	NULL	4	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+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	range	t1_c2	t1_c2	5	NULL	4	4.00	100.00	100.00	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	4	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	4	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	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	4	8.00	100.00	12.50	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	1.00	100.00	100.00	Using index; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	4	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	t1_c2	NULL	NULL	NULL	4	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	2	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	2	8.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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	const	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	range	t1_c2	t1_c2	10	NULL	2	Using index condition; Using where
+3	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	1	Using index
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	2	Using where
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	range	t1_c2	t1_c2	10	NULL	2	2.00	100.00	100.00	Using index condition; Using where
+3	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	1	1.00	100.00	100.00	Using index
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	2	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	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: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.38	100.00	29.63	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	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	8	1.00	100.00	100.00	Using where; Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	100.00	Using where; End temporary
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	5	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	8.00	100.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	3.50	100.00	28.57	Using where
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	1.00	100.00	100.00	
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+drop view v1;
+drop table t1;
+create temporary table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+Test without any index
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.00	100.00	16.67	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	15	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	15	Using where
+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	15	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	15	3.88	100.00	25.81	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	15	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	15	Using where; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	15	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	15	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	15	8.00	100.00	12.50	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	15	1.00	100.00	100.00	Using where; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	22	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	22	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+Test with an index
+create index t1_c2 on t1 (c2,c1);
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	Using where; Using index
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	3.67	100.00	18.18	Using where; Using index
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	range	t1_c2	t1_c2	5	NULL	4	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	4	Using index
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	range	t1_c2	t1_c2	5	NULL	8	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	4	Using index
+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	range	t1_c2	t1_c2	5	NULL	8	4.00	100.00	100.00	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	4	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	15	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c3	4	Using index; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	15	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	4	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	15	8.00	100.00	12.50	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	4	1.00	100.00	100.00	Using index; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	22	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	index_subquery	t1_c2	t1_c2	5	func	4	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	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: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	12	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	12	Using where
+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	12	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	12	3.38	100.00	29.63	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	12	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	12	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	12	1.00	100.00	100.00	Using where; Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	100.00	Using where; End temporary
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+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;
+set default_storage_engine=Aria;
+create table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+create view v1 as select * from t1 where c2=2;
+Test without any index
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.38	100.00	29.63	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	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	8	2.00	100.00	50.00	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	2.00	100.00	50.00	Using where; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	8.00	100.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	5.00	100.00	20.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	Using where; Using index
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	3.67	100.00	18.18	Using where; Using index
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	range	t1_c2	t1_c2	5	NULL	5	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	range	t1_c2	t1_c2	5	NULL	5	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+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	range	t1_c2	t1_c2	5	NULL	5	4.00	100.00	100.00	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c3	2	Using index; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	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	8	1.00	100.00	100.00	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	1.00	100.00	100.00	Using index; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	index_subquery	t1_c2	t1_c2	5	func	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	const	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	range	t1_c2	t1_c2	10	NULL	2	Using index condition; Using where
+3	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	Using index
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	range	t1_c2	t1_c2	10	NULL	2	2.00	25.00	100.00	Using index condition; Using where
+3	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	1.00	100.00	100.00	Using index
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	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: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.38	100.00	29.63	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	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	8	3.00	100.00	100.00	Using where; Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	33.33	Using where; End temporary
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	8.00	100.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	5.50	100.00	18.18	Using where
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	1.00	100.00	100.00	
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+drop view v1;
+drop table t1;
+create temporary table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+Test without any index
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.38	100.00	29.63	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	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	8	2.00	100.00	50.00	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	2.00	100.00	50.00	Using where; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+Test with an index
+create index t1_c2 on t1 (c2,c1);
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	Using where; Using index
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	3.67	100.00	18.18	Using where; Using index
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	range	t1_c2	t1_c2	5	NULL	5	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	range	t1_c2	t1_c2	5	NULL	5	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+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	range	t1_c2	t1_c2	5	NULL	5	4.00	100.00	100.00	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c3	2	Using index; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	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	8	1.00	100.00	100.00	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	1.00	100.00	100.00	Using index; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	index_subquery	t1_c2	t1_c2	5	func	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	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: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.00	100.00	16.67	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.12	100.00	32.00	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	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	8	4.00	100.00	100.00	Using where; Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	25.00	Using where; End temporary
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+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;
+set default_storage_engine=MyISAM;
+create table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+create view v1 as select * from t1 where c2=2;
+Test without any index
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.62	100.00	27.59	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	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	8	2.00	100.00	50.00	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	2.00	100.00	50.00	Using where; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	8.00	100.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	2.50	100.00	40.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	Using where; Using index
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	3.67	100.00	18.18	Using where; Using index
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	range	t1_c2	t1_c2	5	NULL	4	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	range	t1_c2	t1_c2	5	NULL	4	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+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	range	t1_c2	t1_c2	5	NULL	4	4.00	100.00	100.00	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c3	2	Using index; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	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	8	1.00	100.00	100.00	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	1.00	100.00	100.00	Using index; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	index_subquery	t1_c2	t1_c2	5	func	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	t1_c2	t1_c2	5	const	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	range	t1_c2	t1_c2	10	NULL	2	Using index condition; Using where
+3	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	Using index
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	range	t1_c2	t1_c2	10	NULL	2	2.00	25.00	100.00	Using index condition; Using where
+3	DEPENDENT SUBQUERY	t1	ref	t1_c2	t1_c2	10	const,test.t1.c1	2	1.00	100.00	100.00	Using index
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	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: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.67	100.00	18.18	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.12	100.00	24.24	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	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	8	2.00	100.00	100.00	Using where; Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	50.00	Using where; End temporary
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+#
+# Delete from 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: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+SELECT * FROM t2;
+c1
+1
+2
+3
+4
+SELECT * FROM cnt;
+del
+4
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+#
+Delete using a 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 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	8	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	12.50	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete throw a view
+#
+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 > 3;
+explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 > 3;
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete through  a view and using the view in subquery
+#
+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 < 10 and exists (select 'X' from v1 a where a.c1 = v1.c1);
+explain delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	
+analyze delete from v1 where v1.c2 in (select max(a.c2)from t1 a where a.c3 = v1.c3) and c1 < 10 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	8	8.00	100.00	25.00	Using where
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	1.50	100.00	66.67	Using where
+2	DEPENDENT SUBQUERY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.c3	1	1.00	100.00	100.00	
+select * from t1;
+c1	c2	c3
+1	1	1
+1	3	3
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+drop view v1;
+drop table t1;
+create temporary table t1 (c1 integer, c2 integer, c3 integer);
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+Test without any index
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	4.33	100.00	15.38	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.62	100.00	27.59	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	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	8	2.00	100.00	50.00	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	2.00	100.00	50.00	Using where; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+Test with an index
+create index t1_c2 on t1 (c2,c1);
+#
+# Delete with value from subquery on the same table, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+affected rows: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	Using where; Using index
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	index	NULL	t1_c2	10	NULL	8	3.67	100.00	18.18	Using where; Using index
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	range	t1_c2	t1_c2	5	NULL	4	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	range	t1_c2	t1_c2	5	NULL	4	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+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	range	t1_c2	t1_c2	5	NULL	4	4.00	100.00	100.00	Using index condition; Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	1.00	100.00	100.00	Using index
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c3	2	Using index; FirstMatch(t1)
+delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+affected rows: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	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	8	1.00	100.00	100.00	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	5	test.t1.c1	1	1.00	100.00	100.00	Using index; FirstMatch(t1)
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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	8	Using where; Using filesort
+2	DEPENDENT SUBQUERY	a	index_subquery	t1_c2	t1_c2	5	func	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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+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, no search clause. ALL access
+#
+create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+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	8	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: 1
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with search clause on the same table
+#
+create table tmp as select * from t1 where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+explain delete from t1 where c1 <2 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+analyze delete from t1 where c1 <2 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	8	8.00	100.00	25.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.67	100.00	18.18	Using where
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete via RANGE or INDEX access if an index or a primary key exists
+#
+create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with order by
+#
+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 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	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+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	8	8.00	100.00	50.00	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	3.88	100.00	25.81	Using where
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete using the index or primary key (c3)
+#
+create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+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	8	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: 2
+select * from t1;
+c1	c2	c3
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Delete with a limit - can be delete
+#
+create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+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	8	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	8	2.00	100.00	100.00	Using where; Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.a.c2	1	1.00	100.00	50.00	Using where; End temporary
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+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 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+#
+# Double delete
+#
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+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
+2	1	4
+2	3	6
+2	4	7
+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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+#
+# Delete from 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
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+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;
+set @@default_storage_engine= @save_default_engine;
+#
+# End of 10.11 tests
+#
diff --git a/mysql-test/main/delete_use_source.test b/mysql-test/main/delete_use_source.test
index 9625431c1a8..8bd6ac4f7d2 100644
--- a/mysql-test/main/delete_use_source.test
+++ b/mysql-test/main/delete_use_source.test
@@ -255,3 +255,24 @@ select * from t2;
 deallocate prepare stmt;
 
 drop table t1,t2;
+
+--echo #
+--echo # End of 10.10 tests
+--echo #
+
+set @save_default_engine= @@default_storage_engine;
+
+set default_storage_engine=InnoDB;
+--source include/delete_use_source.inc
+
+set default_storage_engine=Aria;
+--source include/delete_use_source.inc
+
+set default_storage_engine=MyISAM;
+--source include/delete_use_source.inc
+
+set @@default_storage_engine= @save_default_engine;
+
+--echo #
+--echo # End of 10.11 tests
+--echo #