MDEV-30539 EXPLAIN EXTENDED: no message with queries for DML statements

EXPLAIN EXTENDED for an UPDATE/DELETE/INSERT/REPLACE statement did not
produce the warning containing the text representation of the query
obtained after the optimization phase. Such warning was produced for
SELECT statements, but not for DML statements.
The patch fixes this defect of EXPLAIN EXTENDED for DML statements.

18 files changed, 563 insertions, 57 deletions

diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc
index d22310c9813..bd0962d3876 100644
--- a/mysql-test/include/explain_non_select.inc
+++ b/mysql-test/include/explain_non_select.inc
@@ -788,6 +788,22 @@ INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
 
 DROP TABLE t1;
 
+--echo #75
+
+CREATE TABLE t1 (id INT PRIMARY KEY, i INT);
+--let $query = INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+--source include/explain_utils.inc
+DROP TABLE t1;
+
+--echo #76
+
+CREATE TABLE t1 (id INT PRIMARY KEY, i INT);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t2 VALUES (1,10), (3,10), (7,11), (3,11);
+--let $query = INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+--source include/explain_utils.inc
+DROP TABLE t1,t2;
+
 --echo #
 --echo # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH
 --echo #                EXPLAIN UPDATE/DEL/INS
diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result
index 111b4c8ae50..d60f10f85c8 100644
--- a/mysql-test/main/explain_non_select.result
+++ b/mysql-test/main/explain_non_select.result
@@ -157,9 +157,13 @@ id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 explain extended update t2 set b=3 where a in (3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`b` = 3 where `test`.`t2`.`a` in (3,4)
 explain extended delete from t2 where a in (3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`a` in (3,4)
 drop table t1,t2;
 #
 #  Check the special case where partition pruning removed all partitions
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index d6cf9ba685f..222c592cbce 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -1253,7 +1253,7 @@ EXPLAIN
 DROP TABLES t1, t2;
 # End of 10.3 tests
 #
-# MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in
+# MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in
 #
 create table t1 (c1 int, c2 int, c3 int, index idx(c2));
 insert into t1 values
diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test
index 48e6250393b..329394e8468 100644
--- a/mysql-test/main/multi_update.test
+++ b/mysql-test/main/multi_update.test
@@ -1134,7 +1134,7 @@ DROP TABLES t1, t2;
 --echo # End of 10.3 tests
 
 --echo #
---echo # MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in
+--echo # MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in
 --echo #
 
 create table t1 (c1 int, c2 int, c3 int, index idx(c2));
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result
index 7f24cb4896d..3ca9629d027 100644
--- a/mysql-test/main/myisam_explain_non_select_all.result
+++ b/mysql-test/main/myisam_explain_non_select_all.result
@@ -17,6 +17,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a < 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	update `test`.`t1` set `test`.`t1`.`a` = 10 where `test`.`t1`.`a` < 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -60,6 +62,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE a < 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`a` < 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -103,6 +107,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 USING t1 WHERE a = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` using `test`.`t1` where `test`.`t1`.`a` = 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -150,6 +156,8 @@ EXPLAIN EXTENDED UPDATE        t1, t2 SET t1.a = 10 WHERE t1.a = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`a` = 10 where `test`.`t1`.`a` = 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -200,6 +208,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = 10 where `test`.`t11`.`a` = 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -248,6 +258,8 @@ EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`a` = 10 where <in_optimizer>(1,<exists>(/* select#2 */ select 1 from `test`.`t2` where `test`.`t2`.`b` < 3 and 1))
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -300,6 +312,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`a` = 10 where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where `test`.`t1`.`a` < 3 and <cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -353,6 +366,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	update `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` set `test`.`t1`.`a` = 10 where `test`.`t2`.`b` < 3
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -405,6 +420,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = `test`.`t11`.`a` + 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -457,6 +474,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` `t11` set `test`.`t11`.`a` = `test`.`t11`.`a` + 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -511,6 +530,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = 10 where `test`.`t11`.`a` > 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -555,6 +576,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE a > 1 LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`a` > 1 limit 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -598,6 +621,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	delete  from `test`.`t1` where 0
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -638,6 +663,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 USING t1 WHERE 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	delete  from `test`.`t1` using `test`.`t1` where 0
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -678,6 +705,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE a = 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	1	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`a` = 3
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	5
@@ -719,6 +748,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE a < 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	1	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`a` < 3
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	5
@@ -758,6 +789,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -797,6 +830,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -840,6 +875,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	1	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where @a:=`test`.`t1`.`a` order by `test`.`t1`.`a` limit 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -884,6 +921,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 ORDER BY a ASC, b ASC LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	7
@@ -941,6 +980,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	100.00	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.b2,test.t1.b1	1	100.00	
+Warnings:
+Note	1003	delete  from `test`.`t1`,`test`.`t2`,`test`.`t3` using `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a2` = `test`.`t1`.`a1` and `test`.`t3`.`a3` = `test`.`t2`.`b2` and `test`.`t3`.`b3` = `test`.`t1`.`b1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	13
@@ -993,6 +1034,8 @@ EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`a` = 10 where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where <cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1043,6 +1086,8 @@ EXPLAIN EXTENDED DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ delete  from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where `test`.`t2`.`a2` > 2 and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`))
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1088,6 +1133,8 @@ EXPLAIN EXTENDED DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ delete  from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where `test`.`t2`.`a2` > 2 and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`))
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1132,6 +1179,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET i = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+Warnings:
+Note	1003	update `test`.`t1` set `test`.`t1`.`i` = 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -1175,6 +1224,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	5	NULL	Deleting all rows
+Warnings:
+Note	1003	delete  from `test`.`t1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -1221,6 +1272,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	NULL	a	15	NULL	5	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -1267,6 +1320,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED INSERT INTO t2 SELECT * FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	insert into `test`.`t2` select `test`.`t1`.`i` AS `i` from `test`.`t1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1311,6 +1366,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED REPLACE INTO t2 SELECT * FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	replace into `test`.`t2` select `test`.`t1`.`i` AS `i` from `test`.`t1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1351,6 +1408,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED INSERT INTO t1 SET i = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL
+Warnings:
+Note	1003	insert into `test`.`t1`(i) values (10)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -1374,6 +1433,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED REPLACE INTO t1 SET i = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL
+Warnings:
+Note	1003	replace into `test`.`t1`(i) values (10)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -1402,6 +1463,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	5	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`i` > 10 and `test`.`t1`.`i` <= 18 order by `test`.`t1`.`i` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1447,6 +1510,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`i` > 10 and `test`.`t1`.`i` <= 18 order by `test`.`t1`.`i` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1500,6 +1565,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -1554,6 +1621,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	NULL	a	15	NULL	5	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -1603,6 +1672,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -1657,6 +1728,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -1712,6 +1785,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index_merge	key1,key2	key1,key2	5,5	NULL	7	100.00	Using sort_union(key1,key2); Using where; Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`key1` < 13 or `test`.`t2`.`key2` < 14 order by `test`.`t2`.`key1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -1765,6 +1840,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	5	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t2` where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` desc limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1812,6 +1889,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 ORDER BY a, b DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -1866,6 +1945,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	NULL	a	6	NULL	5	100.00	
+Warnings:
+Note	1003	delete  from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -1915,6 +1996,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	5	100.00	Using where; Using buffer
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`a` = 10 where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1963,6 +2046,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`a` = 10 where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -2017,6 +2102,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -2072,6 +2159,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	NULL	a	15	NULL	5	100.00	Using where; Using buffer
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -2122,6 +2211,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -2176,6 +2267,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	8
@@ -2231,6 +2324,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index_merge	key1,key2	key1,key2	5,5	NULL	7	100.00	Using sort_union(key1,key2); Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`i` = 123 where `test`.`t2`.`key1` < 13 or `test`.`t2`.`key2` < 14 order by `test`.`t2`.`key1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2284,6 +2379,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	5	100.00	Using where; Using buffer
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`a` = 10 where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` desc limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -2332,6 +2429,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	26	100.00	Using filesort
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`c` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2387,6 +2486,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	index	NULL	a	6	NULL	5	100.00	Using buffer
+Warnings:
+Note	1003	update `test`.`t2` set `test`.`t2`.`c` = 10 order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2439,6 +2540,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	c1_idx	c1_idx	2	NULL	2	100.00	Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t1` set `test`.`t1`.`c2` = 0 where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2485,6 +2588,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	c1_idx	c1_idx	2	NULL	2	100.00	Using where; Using filesort
+Warnings:
+Note	1003	delete  from `test`.`t1` where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2534,6 +2639,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a=a+10 WHERE a > 34;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using buffer
+Warnings:
+Note	1003	update `test`.`t1` set `test`.`t1`.`a` = `test`.`t1`.`a` + 10 where `test`.`t1`.`a` > 34
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -2581,6 +2688,8 @@ EXPLAIN EXTENDED UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	update `test`.`t1` set NULL = 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	7
@@ -2624,6 +2733,8 @@ EXPLAIN EXTENDED UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 W
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	update `test`.`t1` set NULL = 10 where `test`.`t1`.`c3` = 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	7
@@ -2676,6 +2787,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DEPENDENT SUBQUERY	t2	ALL	IDX	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`f2` = (/* select#2 */ select max(`test`.`t2`.`f4`) from `test`.`t2` where `test`.`t2`.`f3` = `test`.`t1`.`f1`)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	7
@@ -2747,6 +2859,8 @@ EXPLAIN EXTENDED UPDATE        v1 SET a = 1 WHERE a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t11	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	update `test`.`t1` `t11` join `test`.`t1` `t12` set `test`.`t11`.`a` = 1 where `test`.`t11`.`a` > 0
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -2792,6 +2906,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t11	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t12	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	update `test`.`t1` join `test`.`t1` `t11` join `test`.`t1` `t12` set `test`.`t11`.`a` = 1 where `test`.`t11`.`a` = `test`.`t1`.`a`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -2841,6 +2957,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED DELETE   FROM v1 WHERE a < 4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ delete  from `test`.`t1` where `test`.`t1`.`a` < 4
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -2892,6 +3010,8 @@ EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.x	1	100.00	
+Warnings:
+Note	1003	delete  from `test`.`t1` using `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2943,6 +3063,8 @@ EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.x	1	100.00	
+Warnings:
+Note	1003	delete  from `test`.`t1` using `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	6
@@ -2987,6 +3109,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED INSERT INTO v1 VALUES (10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL
+Warnings:
+Note	1003	insert into `test`.`t1`(x) values (10)
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	2
@@ -3027,6 +3151,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED INSERT INTO v1 SELECT * FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
+Warnings:
+Note	1003	insert into `test`.`t2`(x) /* select#1 */ select NULL AS `a` from `test`.`t1`
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -3084,6 +3210,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	5	func	2	100.00	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`a` = 10 where <in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0)))
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -3140,6 +3268,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` set `test`.`t1`.`a` = 10 where 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -3198,6 +3328,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` set `test`.`t1`.`a` = 10 where 1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -3248,6 +3380,8 @@ JOIN t1 AS a12 ON a12.c1 = a11.c1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t3` set `test`.`t3`.`c3` = (/* select#2 */ select count(NULL) from `test`.`t1` `a11` straight_join `test`.`t2` `a21` join `test`.`t1` `a12` where 0)
 DROP TABLE t1, t2, t3;
 #73
 CREATE TABLE t1 (id INT);
@@ -3276,6 +3410,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using where; Using buffer
+Warnings:
+Note	1003	update `test`.`t1` set `test`.`t1`.`a` = `test`.`t1`.`a` + 1 where `test`.`t1`.`a` > 10
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -3315,6 +3451,8 @@ FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using where; Using filesort
+Warnings:
+Note	1003	update `test`.`t1` set `test`.`t1`.`a` = `test`.`t1`.`a` + 1 where `test`.`t1`.`a` > 10 order by `test`.`t1`.`a` + 20
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	3
@@ -3341,6 +3479,61 @@ Handler_read_key	4
 Sort_range	1
 
 DROP TABLE t1;
+#75
+CREATE TABLE t1 (id INT PRIMARY KEY, i INT);
+#
+# query:  INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+# select: 
+#
+EXPLAIN INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	NULL
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL
+Warnings:
+Note	1003	insert into `test`.`t1` values (3,10),(7,11),(3,11) on duplicate key update `test`.`t1`.`id` = last_insert_id(`test`.`t1`.`id`)
+# Status of EXPLAIN EXTENDED query
+Variable_name	Value
+Handler_read_key	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	4
+Handler_read_rnd	1
+Handler_write	3
+
+DROP TABLE t1;
+#76
+CREATE TABLE t1 (id INT PRIMARY KEY, i INT);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t2 VALUES (1,10), (3,10), (7,11), (3,11);
+#
+# query:  INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+# select: 
+#
+EXPLAIN INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	
+Warnings:
+Note	1003	insert into `test`.`t1` select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` on duplicate key update `test`.`t1`.`id` = last_insert_id(`test`.`t1`.`id`)
+# Status of EXPLAIN EXTENDED query
+Variable_name	Value
+Handler_read_key	7
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	7
+Handler_read_rnd	1
+Handler_read_rnd_next	5
+Handler_write	4
+
+DROP TABLE t1,t2;
 #
 # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH
 #                EXPLAIN UPDATE/DEL/INS
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index a343d5941f1..a8b391ffbe8 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -3766,7 +3766,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
         "select_id": 1,
         "steps": [
           {
-            "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3"
+            "expanded_query": "delete  from t0,t1 using t0 join t1 where t0.a = t1.a and t1.a < 3"
           }
         ]
       }
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 673b18cb3b0..408b1ec2666 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5566,11 +5566,15 @@ EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 F
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t3` set `test`.`t3`.`c3` = (/* select#2 */ select count(NULL) from `test`.`t1` `a11` straight_join `test`.`t2` `a21` join `test`.`t1` `a12` where 0)
 PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 )";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t3` set `test`.`t3`.`c3` = (/* select#2 */ select count(NULL) from `test`.`t1` `a11` straight_join `test`.`t2` `a21` join `test`.`t1` `a12` where 0)
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1, t2, t3;
 #
diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result
index 8efe75a1ffd..7bb81111aa8 100644
--- a/mysql-test/suite/versioning/r/delete_history.result
+++ b/mysql-test/suite/versioning/r/delete_history.result
@@ -171,6 +171,8 @@ x
 explain extended delete history from t1 before system_time '2039-01-01 23:00';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
+Warnings:
+Note	1003	delete  from `test`.`t1` FOR SYSTEM_TIME BEFORE TIMESTAMP '2039-01-01 23:00' where `test`.`t1`.`row_end` < '2039-01-01 23:00' and is_history(`test`.`t1`.`row_end`)
 create or replace procedure p() delete history from t1 before system_time '2039-01-01 23:00';
 call p;
 select * from t1;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 1b3fa3087e7..776dfffe113 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -1084,6 +1084,8 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list,
     DBUG_RETURN(TRUE);
 
   select_lex->fix_prepare_information(thd, conds, &fake_conds); 
+  if (!thd->lex->upd_del_where)
+    thd->lex->upd_del_where= *conds;
   DBUG_RETURN(FALSE);
 }
 
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 70e300997f9..6b76db8139e 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -161,7 +161,7 @@ void Explain_query::query_plan_ready()
   Send EXPLAIN output to the client.
 */
 
-int Explain_query::send_explain(THD *thd)
+int Explain_query::send_explain(THD *thd, bool extended)
 {
   select_result *result;
   LEX *lex= thd->lex;
@@ -174,8 +174,22 @@ int Explain_query::send_explain(THD *thd)
   if (thd->lex->explain_json)
     print_explain_json(result, thd->lex->analyze_stmt);
   else
+  {
     res= print_explain(result, lex->describe, thd->lex->analyze_stmt);
-
+    if (extended)
+    {
+      char buff[1024];
+      String str(buff,(uint32) sizeof(buff), system_charset_info);
+                 str.length(0);
+     /*
+       The warnings system requires input in utf8, @see
+        mysqld_show_warnings().
+     */
+     lex->unit.print(&str, QT_EXPLAIN_EXTENDED);
+                     push_warning(thd, Sql_condition::WARN_LEVEL_NOTE,
+                     ER_YES, str.c_ptr_safe());
+    }
+  }
   if (res)
     result->abort_result_set();
   else
@@ -185,6 +199,7 @@ int Explain_query::send_explain(THD *thd)
 }
 
 
+
 /*
   The main entry point to print EXPLAIN of the entire query
 */
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 7b5042b9ccf..3add40419cf 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -474,7 +474,7 @@ public:
                     bool is_analyze);
   
   /* Send tabular EXPLAIN to the client */
-  int send_explain(THD *thd);
+  int send_explain(THD *thd, bool extended);
   
   /* Return tabular EXPLAIN output as a text string */
   bool print_explain_str(THD *thd, String *out_str, bool is_analyze);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index f31c9eab428..9a760614f6a 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -827,7 +827,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
   save_insert_query_plan(thd, table_list);
   if (thd->lex->describe)
   {
-    retval= thd->lex->explain->send_explain(thd);
+    bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+    retval= thd->lex->explain->send_explain(thd, extended);
     goto abort;
   }
 
@@ -1250,7 +1251,8 @@ values_loop_end:
     goto abort;
   if (thd->lex->analyze_stmt)
   {
-    retval= thd->lex->explain->send_explain(thd);
+    bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+    retval= thd->lex->explain->send_explain(thd, extended);
     goto abort;
   }
   DBUG_PRINT("info", ("touched: %llu  copied: %llu  updated: %llu  deleted: %llu",
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index e14d9f7a740..e7689a99c22 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -780,6 +780,8 @@ void LEX::start(THD *thd_arg)
   frame_bottom_bound= NULL;
   win_spec= NULL;
 
+  upd_del_where= NULL;
+
   vers_conditions.empty();
   period_conditions.empty();
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 769b17508cf..6170637ad77 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1417,6 +1417,10 @@ public:
   }
   bool setup_ref_array(THD *thd, uint order_group_num);
   void print(THD *thd, String *str, enum_query_type query_type);
+  void print_item_list(THD *thd, String *str, enum_query_type query_type);
+  void print_set_clause(THD *thd, String *str, enum_query_type query_type);
+  void print_on_duplicate_key_clause(THD *thd, String *str,
+                                     enum_query_type query_type);
   static void print_order(String *str,
                           ORDER *order,
                           enum_query_type query_type);
@@ -3493,6 +3497,8 @@ public:
   Window_frame_bound *frame_bottom_bound;
   Window_spec *win_spec;
 
+  Item *upd_del_where;
+
   /* System Versioning */
   vers_select_conds_t vers_conditions;
   vers_select_conds_t period_conditions;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 1f1962a5d44..c495ae2d6c4 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4744,7 +4744,10 @@ mysql_execute_command(THD *thd)
       }
 
       if (!res && (explain || lex->analyze_stmt))
-        res= thd->lex->explain->send_explain(thd);
+      {
+        bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+        res= thd->lex->explain->send_explain(thd, extended);
+      }
 
       /* revert changes for SP */
       MYSQL_INSERT_SELECT_DONE(res, (ulong) thd->get_row_count_func());
@@ -4813,7 +4816,10 @@ mysql_execute_command(THD *thd)
     if (thd->lex->analyze_stmt || thd->lex->describe)
     {
       if (!res)
-        res= thd->lex->explain->send_explain(thd);
+      {
+        bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+        res= thd->lex->explain->send_explain(thd, extended);
+      }
     }
 
     delete sel_result;
@@ -4875,7 +4881,10 @@ mysql_execute_command(THD *thd)
         else
         {
           if (lex->describe || lex->analyze_stmt)
-            res= thd->lex->explain->send_explain(thd);
+	  {
+            bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+            res= thd->lex->explain->send_explain(thd, extended);
+          }
         }
       multi_delete_error:
         delete result;
@@ -6463,7 +6472,10 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables)
           thd->protocol= save_protocol;
         }
         if (!res)
-          res= thd->lex->explain->send_explain(thd);
+	{
+          bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+          res= thd->lex->explain->send_explain(thd, extended);
+        }
       }
     }
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 2c12d1c4c65..c4fc2d19156 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -305,6 +305,9 @@ static Item **get_sargable_cond(JOIN *join, TABLE *table);
 
 bool is_eq_cond_injected_for_split_opt(Item_func_eq *eq_item);
 
+void print_list_item(String *str, List_item *list,
+		     enum_query_type query_type);
+
 #ifndef DBUG_OFF
 
 /*
@@ -27981,6 +27984,162 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str,
   }
 }
 
+enum explainable_cmd_type
+{
+  SELECT_CMD, INSERT_CMD, REPLACE_CMD, UPDATE_CMD, DELETE_CMD, NO_CMD
+};
+
+static
+const char * const explainable_cmd_name []=
+{
+  "select ",
+  "insert ",
+  "replace ",
+  "update ",
+  "delete ",
+};
+
+static
+char const *get_explainable_cmd_name(enum explainable_cmd_type cmd)
+{
+  return explainable_cmd_name[cmd];
+}
+
+static
+enum explainable_cmd_type get_explainable_cmd_type(THD *thd)
+{
+  switch (thd->lex->sql_command) {
+  case SQLCOM_SELECT:
+    return SELECT_CMD;
+  case SQLCOM_INSERT:
+  case SQLCOM_INSERT_SELECT:
+    return INSERT_CMD;
+  case SQLCOM_REPLACE:
+  case SQLCOM_REPLACE_SELECT:
+    return REPLACE_CMD;
+  case SQLCOM_UPDATE:
+  case SQLCOM_UPDATE_MULTI:
+    return UPDATE_CMD;
+  case SQLCOM_DELETE:
+  case SQLCOM_DELETE_MULTI:
+    return DELETE_CMD;
+  default:
+    return SELECT_CMD;
+  }
+}
+
+
+void TABLE_LIST::print_leaf_tables(THD *thd, String *str,
+                              enum_query_type query_type)
+{
+  if (merge_underlying_list)
+  {
+    for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local)
+      tbl->print_leaf_tables(thd, str, query_type);
+  }
+  else
+    print(thd, 0, str, query_type);
+}
+
+
+void st_select_lex::print_item_list(THD *thd, String *str,
+                                    enum_query_type query_type)
+{
+  bool first= 1;
+  /*
+    outer_select() can not be used here because it is for name resolution
+    and will return NULL at any end of name resolution chain (view/derived)
+  */
+  bool top_level= (get_master()->get_master() == 0);
+  List_iterator_fast<Item> it(item_list);
+  Item *item;
+  while ((item= it++))
+  {
+    if (first)
+      first= 0;
+    else
+      str->append(',');
+
+    if ((is_subquery_function() && item->is_autogenerated_name) ||
+        !item->name.str)
+    {
+      /*
+        Do not print auto-generated aliases in subqueries. It has no purpose
+        in a view definition or other contexts where the query is printed.
+      */
+      item->print(str, query_type);
+    }
+    else
+    {
+      /*
+        Do not print illegal names (if it is not top level SELECT).
+        Top level view checked (and correct name are assigned),
+        other cases of top level SELECT are not important, because
+        it is not "table field".
+      */
+      if (top_level ||
+          !item->is_autogenerated_name ||
+          !check_column_name(item->name.str))
+        item->print_item_w_name(str, query_type);
+      else
+        item->print(str, query_type);
+    }
+  }
+}
+
+
+void st_select_lex::print_set_clause(THD *thd, String *str,
+                                     enum_query_type query_type)
+{
+  bool first= 1;
+  /*
+    outer_select() can not be used here because it is for name resolution
+    and will return NULL at any end of name resolution chain (view/derived)
+  */
+  List_iterator_fast<Item> it(item_list);
+  List_iterator_fast<Item> vt(thd->lex->value_list);
+  Item *item;
+  Item *val;
+  while ((item= it++, val= vt++ ))
+  {
+    if (first)
+    {
+      str->append(STRING_WITH_LEN(" set "));
+      first= 0;
+    }
+    else
+      str->append(',');
+
+    item->print(str, query_type);
+    str->append(STRING_WITH_LEN(" = "));
+    val->print(str, query_type);
+  }
+}
+
+
+void st_select_lex::print_on_duplicate_key_clause(THD *thd, String *str,
+                                                  enum_query_type query_type)
+{
+  bool first= 1;
+  List_iterator_fast<Item> it(thd->lex->update_list);
+  List_iterator_fast<Item> vt(thd->lex->value_list);
+  Item *item;
+  Item *val;
+  while ((item= it++, val= vt++ ))
+  {
+    if (first)
+    {
+      str->append(STRING_WITH_LEN(" on duplicate key update "));
+      first= 0;
+    }
+    else
+      str->append(',');
+
+    item->print(str, query_type);
+    str->append(STRING_WITH_LEN(" = "));
+    val->print(str, query_type);
+  }
+}
 
 void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
 {
@@ -27998,6 +28157,61 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
     return;
   }
 
+  bool top_level= (get_master()->get_master() == 0);
+  enum explainable_cmd_type sel_type= SELECT_CMD;
+  if (top_level)
+    sel_type= get_explainable_cmd_type(thd);
+
+  if (sel_type == INSERT_CMD || sel_type == REPLACE_CMD)
+  {
+    str->append(get_explainable_cmd_name(sel_type));
+    str->append(STRING_WITH_LEN("into "));
+    TABLE_LIST *tbl= thd->lex->query_tables;
+    while (tbl->merge_underlying_list)
+      tbl= tbl->merge_underlying_list;
+    tbl->print(thd, 0, str, query_type);
+    if (thd->lex->field_list.elements)
+    {
+      str->append ('(');
+      List_iterator_fast<Item> it(thd->lex->field_list);
+      Item *item;
+      bool first= true;
+      while ((item= it++))
+      {
+        if (first)
+          first= false;
+        else
+          str->append(',');
+	str->append(item->name);
+      }
+      str->append(')');
+    }
+
+    str->append(' ');
+
+    if (thd->lex->sql_command == SQLCOM_INSERT ||
+        thd->lex->sql_command == SQLCOM_REPLACE)
+    {
+      str->append(STRING_WITH_LEN("values "));
+      bool is_first_elem= true;
+      List_iterator_fast<List_item> li(thd->lex->many_values);
+      List_item *list;
+
+      while ((list= li++))
+      {
+        if (is_first_elem)
+          is_first_elem= false;
+        else
+          str->append(',');
+
+        print_list_item(str, list, query_type);
+      }
+      if (thd->lex->update_list.elements)
+        print_on_duplicate_key_clause(thd, str, query_type);
+      return;
+    }
+  }
+
   if ((query_type & QT_SHOW_SELECT_NUMBER) &&
       thd->lex->all_selects_list &&
       thd->lex->all_selects_list->link_next &&
@@ -28021,7 +28235,10 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
     str->append(" */ ");
   }
 
-  str->append(STRING_WITH_LEN("select "));
+  if (sel_type == SELECT_CMD ||
+      sel_type == INSERT_CMD ||
+      sel_type == REPLACE_CMD)
+    str->append(STRING_WITH_LEN("select "));
 
   if (join && join->cleaned)
   {
@@ -28067,57 +28284,66 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
   }
 
   //Item List
-  bool first= 1;
+  if (sel_type == SELECT_CMD ||
+      sel_type == INSERT_CMD ||
+      sel_type == REPLACE_CMD)
+    print_item_list(thd, str, query_type);
   /*
-    outer_select() can not be used here because it is for name resolution
-    and will return NULL at any end of name resolution chain (view/derived)
+    from clause
+    TODO: support USING/FORCE/IGNORE index
   */
-  bool top_level= (get_master()->get_master() == 0);
-  List_iterator_fast<Item> it(item_list);
-  Item *item;
-  while ((item= it++))
+  if (table_list.elements)
   {
-    if (first)
-      first= 0;
-    else
-      str->append(',');
-
-    if ((is_subquery_function() && item->is_autogenerated_name) ||
-        !item->name.str)
+    if (sel_type == SELECT_CMD ||
+        sel_type == INSERT_CMD ||
+        sel_type == REPLACE_CMD)
     {
-      /*
-        Do not print auto-generated aliases in subqueries. It has no purpose
-        in a view definition or other contexts where the query is printed.
-      */
-      item->print(str, query_type);
+      str->append(STRING_WITH_LEN(" from "));
+      /* go through join tree */
+      print_join(thd, join? join->eliminated_tables: 0, str, &top_join_list,
+                 query_type);
     }
-    else
+    if (sel_type == UPDATE_CMD || sel_type == DELETE_CMD)
+      str->append(STRING_WITH_LEN(get_explainable_cmd_name(sel_type)));
+    if (sel_type == DELETE_CMD)
     {
-      /*
-        Do not print illegal names (if it is not top level SELECT).
-        Top level view checked (and correct name are assigned),
-        other cases of top level SELECT are not important, because
-        it is not "table field".
-      */
-      if (top_level ||
-          !item->is_autogenerated_name ||
-          !check_column_name(item->name.str))
-        item->print_item_w_name(str, query_type);
+      str->append(STRING_WITH_LEN(" from "));
+      bool first= true;
+      for (TABLE_LIST *target_tbl= thd->lex->auxiliary_table_list.first;
+           target_tbl;
+           target_tbl= target_tbl->next_local)
+      {
+        if (first)
+          first= false;
+        else
+          str->append(',');
+        target_tbl->correspondent_table->print_leaf_tables(thd, str,
+                                                           query_type);
+      }
+
+      if (!first)
+        str->append(STRING_WITH_LEN(" using "));
+    }
+    if (sel_type == UPDATE_CMD || sel_type == DELETE_CMD)
+    {
+      if (join)
+        print_join(thd, 0, str, &top_join_list, query_type);
       else
-        item->print(str, query_type);
+      {
+        bool first= true;
+        List_iterator_fast<TABLE_LIST> li(leaf_tables);
+        TABLE_LIST *tbl;
+        while ((tbl= li++))
+        {
+          if (first)
+            first= false;
+          else
+            str->append(',');
+          tbl->print(thd, 0, str, query_type);
+        }
+      }
     }
   }
-
-  /*
-    from clause
-    TODO: support USING/FORCE/IGNORE index
-  */
-  if (table_list.elements)
-  {
-    str->append(STRING_WITH_LEN(" from "));
-    /* go through join tree */
-    print_join(thd, join? join->eliminated_tables: 0, str, &top_join_list, query_type);
-  }
   else if (where)
   {
     /*
@@ -28127,10 +28353,15 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
     str->append(STRING_WITH_LEN(" from DUAL "));
   }
 
+  if (sel_type == UPDATE_CMD)
+    print_set_clause(thd, str, query_type);
+
   // Where
   Item *cur_where= where;
   if (join)
     cur_where= join->conds;
+  else if (sel_type == UPDATE_CMD || sel_type == DELETE_CMD)
+    cur_where= thd->lex->upd_del_where;
   if (cur_where || cond_value != Item::COND_UNDEF)
   {
     str->append(STRING_WITH_LEN(" where "));
@@ -28187,6 +28418,15 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
   else if (lock_type == TL_WRITE)
     str->append(" for update");
 
+  if ((sel_type == INSERT_CMD || sel_type == REPLACE_CMD) &&
+      thd->lex->update_list.elements)
+    print_on_duplicate_key_clause(thd, str, query_type);
+
+  // returning clause
+  if (sel_type == DELETE_CMD && !item_list.elements)
+  {
+    print_item_list(thd, str, query_type);
+  }
   // PROCEDURE unsupported here
 }
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index a2980717018..834fa6111e5 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1371,7 +1371,8 @@ produce_explain_and_leave:
     goto err;
 
 emit_explain_and_leave:
-  int err2= thd->lex->explain->send_explain(thd);
+  bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+  int err2= thd->lex->explain->send_explain(thd, extended);
 
   delete select;
   free_underlaid_joins(thd, select_lex);
@@ -1445,6 +1446,8 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
 
 
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
+  if (!thd->lex->upd_del_where)
+    thd->lex->upd_del_where= *conds;
   DBUG_RETURN(FALSE);
 }
 
@@ -1974,7 +1977,10 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields,
   else
   {
     if (thd->lex->describe || thd->lex->analyze_stmt)
-      res= thd->lex->explain->send_explain(thd);
+    {
+      bool extended= thd->lex->describe & DESCRIBE_EXTENDED;
+      res= thd->lex->explain->send_explain(thd, extended);
+    }
   }
   thd->abort_on_warning= 0;
   DBUG_RETURN(res);
diff --git a/sql/table.h b/sql/table.h
index c4b0d78959d..6f7f4e63473 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2708,6 +2708,8 @@ struct TABLE_LIST
   }
   void print(THD *thd, table_map eliminated_tables, String *str, 
              enum_query_type query_type);
+  void print_leaf_tables(THD *thd, String *str,
+                         enum_query_type query_type);
   bool check_single_table(TABLE_LIST **table, table_map map,
                           TABLE_LIST *view);
   bool set_insert_values(MEM_ROOT *mem_root);