List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:April 1 2011 1:54pm
Subject:bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897
View as plain text  
#At file:///mnt/sda7/work/mysql-next-mr-opt-backporting-wl4897/ based on revid:tor.didriksen@stripped

 3358 Gleb Shchepa	2011-04-01
      WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
      
      WL#4897 implements EXPLAIN command for INSERT, REPLACE and
      single- and multi-table UPDATE and DELETE queries.
     @ mysql-test/include/explain_non_select.inc
        Coverage and regresstion tests and sanity checks for WL#4897.
     @ mysql-test/r/explain_non_select.result
        Coverage and regresstion tests and sanity checks for WL#4897.
     @ mysql-test/t/explain_non_select.test
        Coverage and regresstion tests and sanity checks for WL#4897.
     @ sql/CMakeLists.txt
        New opt_explain.cc source file has been added.
     @ sql/opt_explain.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        The select_describe() functions has been refactored into
        msg_describe() and select_describe () global functions and
        a few local auxiliary classes: Explain, Explain_msg,
        Explain_table_base, Explain_union and Explain_join.
        
        Also the functionality of select_describe() has been
        improved to serve top-JOIN-less EXPLAIN queries (EXPLAIN
        single-table UPDATE and DELETE) with the help of the new
        global table_describe() function and auxiliary Explain_table
        class.
        
        explain_send class and explain_data_modification() function
        have been added to adapt select_insert, multi_update and
        multi_delete classes to work in EXPLAIN context (to
        implement EXPLAIN INSERT...SELECT and EXPLAIN multi-table
        UPDATE and DELETE commands).
     @ sql/opt_explain.h
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        Signatures of new msg_describe(), bool table_describe() and
        explain_data_modification() functions have been added.
        
        Modified declaration for the select_describe() function has
        been moved there from the sql_select.cc file.
     @ sql/opt_range.h
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        Minor change: unnecessary friending of the select_describe()
        function has been removed.
     @ sql/sql_class.h
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        The select_result::reset_offset_limit_cnt() method has been added
        to reset protected offset_limit_cnt field.
     @ sql/sql_delete.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        The mysql_delete() function has been improved to work in the
        EXPLAIN multi-table DELETE context to produce EXPLAIN instead
        table data modification.
     @ sql/sql_insert.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        The mysql_insert() function has been improved to work in the
        EXPLAIN INSERT...SELECT context to produce EXPLAIN instead
        table data modification.
     @ sql/sql_parse.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        INSERT...SELECT, REPLACE...SELECT and multi-table UPDATE/DELETE
        handlers have been improved to work with EXPLAIN of these
        queries.
     @ sql/sql_select.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        The select_describe() function has been moved to opt_explain.{h,cc}
        files and refactored.
     @ sql/sql_show.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        Minor cleanup.
     @ sql/sql_update.cc
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        The mysql_delete() function has been improved to work in the
        EXPLAIN multi-table UPDATE context to produce EXPLAIN instead
        table data modification.
     @ sql/sql_yacc.yy
        WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
        
        New EXPLAIN INSERT/REPLACE/UPDATE/DELETE syntax has been added.

    added:
      mysql-test/include/explain_non_select.inc
      mysql-test/r/explain_non_select.result
      mysql-test/t/explain_non_select.test
      sql/opt_explain.cc
      sql/opt_explain.h
    modified:
      sql/CMakeLists.txt
      sql/opt_range.h
      sql/sql_class.h
      sql/sql_delete.cc
      sql/sql_insert.cc
      sql/sql_parse.cc
      sql/sql_select.cc
      sql/sql_show.cc
      sql/sql_update.cc
      sql/sql_yacc.yy
=== added file 'mysql-test/include/explain_non_select.inc'
--- a/mysql-test/include/explain_non_select.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/explain_non_select.inc	2011-04-01 13:53:57 +0000
@@ -0,0 +1,63 @@
+--echo #
+--echo # query:  $query
+--echo # select: $select
+--echo #
+
+if ($select) {
+--disable_query_log
+--eval $select INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/before_explain.txt'
+--enable_query_log
+}
+
+--eval EXPLAIN $query
+if (`SELECT ROW_COUNT() > 0`) {
+--echo # Erroneous query: EXPLAIN $query
+--die Unexpected ROW_COUNT() <> 0
+}
+
+--eval EXPLAIN EXTENDED $query
+if (`SELECT ROW_COUNT() > 0`) {
+--echo # Erroneous query: EXPLAIN EXTENDED $query
+--die Unexpected ROW_COUNT() <> 0
+}
+
+if ($select) {
+--eval EXPLAIN EXTENDED $select
+}
+
+--disable_query_log
+
+if ($select) {
+--eval $select INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/after_explain.txt'
+--diff_files '$MYSQLTEST_VARDIR/tmp/before_explain.txt' '$MYSQLTEST_VARDIR/tmp/after_explain.txt'
+--remove_file '$MYSQLTEST_VARDIR/tmp/before_explain.txt'
+--remove_file '$MYSQLTEST_VARDIR/tmp/after_explain.txt'
+}
+
+FLUSH STATUS;
+FLUSH TABLES;
+if ($select) {
+--disable_result_log
+--eval $select
+--enable_result_log
+--echo # Status of "equivalent" SELECT query execution:
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+                   Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+}
+
+FLUSH STATUS;
+FLUSH TABLES;
+--eval $query
+--echo # Status of testing query execution:
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+                   Variable_name LIKE 'Handler_read_%' OR
+                   Variable_name = 'Handler_write' OR
+                   Variable_name = 'Handler_update' OR
+                   Variable_name = 'Handler_delete') AND Value <> 0;
+
+--let $query=
+--let $select=
+
+--enable_query_log
+
+--echo

=== added file 'mysql-test/r/explain_non_select.result'
--- a/mysql-test/r/explain_non_select.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/explain_non_select.result	2011-04-01 13:53:57 +0000
@@ -0,0 +1,1816 @@
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  UPDATE t1 SET a = 10 WHERE a < 10
+# select: SELECT * FROM t1     WHERE a < 10
+#
+EXPLAIN UPDATE t1 SET a = 10 WHERE a < 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 10)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+Handler_update	3
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  DELETE   FROM t1 WHERE a < 10
+# select: SELECT * FROM t1 WHERE a < 10
+#
+EXPLAIN DELETE   FROM t1 WHERE a < 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 10)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	3
+Handler_read_rnd_next	4
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  DELETE   FROM t1 USING t1 WHERE a = 1
+# select: SELECT * FROM t1          WHERE a = 1
+#
+EXPLAIN DELETE   FROM t1 USING t1 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+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
+EXPLAIN EXTENDED SELECT * FROM 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	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_rnd_next	4
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1, t2 SET t1.a = 10 WHERE t1.a = 1
+# select: SELECT * FROM t1, t2               WHERE t1.a = 1
+#
+EXPLAIN UPDATE        t1, t2 SET t1.a = 10 WHERE t1.a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+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	Using join buffer (BNL, incremental buffers)
+EXPLAIN EXTENDED SELECT * FROM t1, t2               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	Using join buffer (BNL, incremental buffers)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = 1)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	8
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	8
+Handler_update	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1
+# select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a = 1
+#
+EXPLAIN UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN EXTENDED UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1;
+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	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a = 1;
+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	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` = 1)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	12
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	12
+Handler_update	1
+Handler_write	3
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3)
+# select: SELECT * FROM t1            WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3)
+#
+EXPLAIN UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
+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
+EXPLAIN EXTENDED SELECT * FROM t1            WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
+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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`b` < 3)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	8
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	7
+Handler_update	3
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3)
+# select: SELECT * FROM t1            WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3)
+#
+EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
+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	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+EXPLAIN EXTENDED SELECT * FROM t1            WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
+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
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	8
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	11
+Handler_update	2
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
+# select: SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
+#
+EXPLAIN UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers)
+EXPLAIN EXTENDED UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3);
+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
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers)
+EXPLAIN EXTENDED SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3);
+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
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	12
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	31
+Handler_update	2
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10
+# select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12
+#
+EXPLAIN UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN EXTENDED UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10;
+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	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12;
+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	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	12
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd	3
+Handler_read_rnd_next	25
+Handler_update	3
+Handler_write	12
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10
+# select: SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12
+#
+EXPLAIN UPDATE        t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+EXPLAIN EXTENDED UPDATE        t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10;
+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
+EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12;
+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 `test`.`t11`.`a` AS `a`,'1' AS `1` from `test`.`t1` `t11`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd_next	5
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd	3
+Handler_read_rnd_next	9
+Handler_update	3
+Handler_write	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1
+# select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a > 1
+#
+EXPLAIN UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN EXTENDED UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1;
+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	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a > 1;
+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	Using join buffer (BNL, incremental buffers)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` > 1)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	12
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	16
+Handler_update	2
+Handler_write	3
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  DELETE   FROM t1 WHERE a > 1 LIMIT 1
+# select: SELECT * FROM t1 WHERE a > 1 LIMIT 1
+#
+EXPLAIN DELETE   FROM t1 WHERE a > 1 LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 1) limit 1
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	2
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_rnd_next	2
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  DELETE   FROM t1 WHERE 0
+# select: SELECT * FROM t1 WHERE 0
+#
+EXPLAIN DELETE   FROM t1 WHERE 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+# Status of testing query execution:
+Variable_name	Value
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  DELETE   FROM t1 USING t1 WHERE 0
+# select: SELECT * FROM t1          WHERE 0
+#
+EXPLAIN DELETE   FROM t1 USING t1 WHERE 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+# Status of testing query execution:
+Variable_name	Value
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
+INSERT INTO t1 VALUES (3, 3), (7, 7);
+#
+# query:  DELETE   FROM t1 WHERE a = 3
+# select: SELECT * FROM t1 WHERE a = 3
+#
+EXPLAIN DELETE   FROM t1 WHERE a = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using where
+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,b	a	5	NULL	1	100.00	Using where
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	a	a	5	const	1	100.00	
+Warnings:
+Note	1003	select '3' AS `a`,'3' AS `b` from `test`.`t1` where 1
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_key	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
+INSERT INTO t1 VALUES (3, 3), (7, 7);
+#
+# query:  DELETE   FROM t1 WHERE a < 3
+# select: SELECT * FROM t1 WHERE a < 3
+#
+EXPLAIN DELETE   FROM t1 WHERE a < 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using where
+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,b	a	5	NULL	1	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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 index condition
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 3)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	1
+
+DROP TABLE t1;
+CREATE TABLE t1 ( a int PRIMARY KEY );
+#
+# query:  DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+# select: SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+#
+EXPLAIN DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where
+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	1	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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 noticed after reading const tables
+Warnings:
+Note	1003	select NULL AS `a` from `test`.`t1` where 0 order by NULL
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd_next	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	1
+
+INSERT INTO t1 VALUES (1), (2), (3);
+#
+# query:  DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+# select: SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+#
+EXPLAIN DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where
+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
+EXPLAIN EXTENDED SELECT * 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	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_next	3
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	3
+Handler_read_key	1
+Handler_read_next	3
+
+DROP TABLE t1;
+CREATE TABLE t1(a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (4),(3),(1),(2);
+#
+# query:  DELETE   FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1
+# select: SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1
+#
+EXPLAIN DELETE   FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	PRIMARY	4	NULL	4	Using where
+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	ALL	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	400.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (@a:=`test`.`t1`.`a`) order by `test`.`t1`.`a` limit 1
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_first	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a));
+INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), ();
+UPDATE t1 SET a = c, b = c;
+#
+# query:  DELETE   FROM t1 ORDER BY a ASC, b ASC LIMIT 1
+# select: SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1
+#
+EXPLAIN DELETE   FROM t1 ORDER BY a ASC, b ASC LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	c,b,a	NULL	NULL	NULL	10	Using filesort
+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	c,b,a	NULL	NULL	NULL	10	100.00	Using filesort
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	11
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_rnd	1
+Handler_read_rnd_next	11
+Sort_rows	10
+Sort_scan	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL);
+CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2));
+CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3));
+INSERT INTO t1 VALUES (1,1), (2,1), (1,3);
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+INSERT INTO t3 VALUES (1,1), (2,1), (1,3);
+#
+# query:  DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3
+# select: SELECT *        FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3
+#
+EXPLAIN DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.b2,test.t1.b1	1	
+EXPLAIN EXTENDED DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3;
+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	
+EXPLAIN EXTENDED SELECT *        FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3;
+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	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.b2,test.t1.b1	1	100.00	Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`b3` AS `b3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`b3` = `test`.`t1`.`b1`) and (`test`.`t3`.`a3` = `test`.`t2`.`b2`) and (`test`.`t2`.`a2` = `test`.`t1`.`a1`))
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	6
+Handler_read_next	3
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	8
+Handler_read_key	6
+Handler_read_next	3
+Handler_read_rnd	5
+Handler_read_rnd_next	4
+
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+#
+# query:  UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2)
+# select: SELECT * FROM t1     WHERE a IN (SELECT a FROM t2)
+#
+EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+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
+EXPLAIN EXTENDED SELECT * FROM t1     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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	8
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	10
+Handler_update	3
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a1 INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 (a2 VARCHAR(10));
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5);
+SET @save_optimizer_switch= @@optimizer_switch;
+SET @@optimizer_switch= 'semijoin=off';
+#
+# query:  DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+# select: SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+#
+EXPLAIN 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	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+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
+EXPLAIN EXTENDED SELECT * 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 `test`.`t1`.`a1` AS `a1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(select 1 from `test`.`t2` where ((`test`.`t2`.`a2` > 2) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`))))
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	30
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	3
+Handler_read_rnd_next	30
+
+SET @@optimizer_switch= @save_optimizer_switch;
+TRUNCATE t1;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+#
+# query:  DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+# select: SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+#
+EXPLAIN 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	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+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
+EXPLAIN EXTENDED SELECT * 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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` > 2) and (`test`.`t1`.`a1` = `test`.`t2`.`a2`))
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	12
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	3
+Handler_read_rnd_next	30
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT, j INT);
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+#
+# query:  UPDATE t1 SET i = 10
+# select: SELECT * FROM t1
+#
+EXPLAIN UPDATE t1 SET i = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+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	
+EXPLAIN EXTENDED 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	5	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	6
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	6
+Handler_update	5
+
+DROP TABLE t1;
+CREATE TABLE t1 (i INT, j INT);
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+#
+# query:  DELETE FROM t1
+# select: SELECT * FROM t1
+#
+EXPLAIN DELETE FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	5	Using delete_all_rows
+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	Using delete_all_rows
+EXPLAIN EXTENDED 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	5	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	6
+# Status of testing query execution:
+Variable_name	Value
+
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
+#
+# query:  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	a	15	NULL	17602	Using where
+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	a	a	15	NULL	17602	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	352040.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_first	1
+Handler_read_next	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (i INT);
+#
+# query:  INSERT INTO t2 SELECT * FROM t1
+# select: SELECT * FROM t1
+#
+EXPLAIN INSERT INTO t2 SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+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	
+EXPLAIN EXTENDED 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	select `test`.`t1`.`i` AS `i` from `test`.`t1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+Handler_write	3
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (i INT);
+#
+# query:  REPLACE INTO t2 SELECT * FROM t1
+# select: SELECT * FROM t1
+#
+EXPLAIN REPLACE INTO t2 SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+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	
+EXPLAIN EXTENDED 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	select `test`.`t1`.`i` AS `i` from `test`.`t1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd_next	4
+Handler_write	3
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+#
+# query:  INSERT INTO t1 SET i = 10
+# select: 
+#
+EXPLAIN INSERT INTO t1 SET i = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+EXPLAIN EXTENDED INSERT INTO t1 SET i = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+# Status of testing query execution:
+Variable_name	Value
+Handler_write	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+#
+# query:  REPLACE INTO t1 SET i = 10
+# select: 
+#
+EXPLAIN REPLACE INTO t1 SET i = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+EXPLAIN EXTENDED REPLACE INTO t1 SET i = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+# Status of testing query execution:
+Variable_name	Value
+Handler_write	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT, i INT PRIMARY KEY);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+#
+# query:  DELETE   FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+# select: SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	8	Using where
+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	8	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	8	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_key	1
+Handler_read_next	4
+
+DROP TABLE t1;
+CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1)));
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+#
+# query:  DELETE   FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+# select: SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t1	ALL	i	NULL	NULL	NULL	26	Using where; Using filesort
+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	i	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+EXPLAIN EXTENDED SELECT * 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	i	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	5
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_rnd	5
+Handler_read_rnd_next	27
+Sort_rows	8
+Sort_scan	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+#
+# query:  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using where; Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
+#
+# query:  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	a	15	NULL	17602	Using where
+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	a	a	15	NULL	17602	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	352040.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_first	1
+Handler_read_next	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+#
+# query:  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using where; Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
+ENGINE=HEAP;
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+#
+# query:  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using where; Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	1
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2))
+ENGINE= MyISAM;
+INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
+#
+# query:  DELETE   FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
+# select: SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
+#
+EXPLAIN DELETE   FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index_merge	key1,key2	key1,key2	5,5	NULL	7	Using sort_union(key1,key2); Using where; Using filesort
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	2
+Handler_read_next	7
+Handler_read_rnd	4
+Sort_range	1
+Sort_rows	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	4
+Handler_read_key	2
+Handler_read_next	7
+Handler_read_rnd	8
+Sort_range	1
+Sort_rows	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i INT PRIMARY KEY);
+INSERT INTO t2 (i) SELECT i FROM t1;
+#
+# query:  DELETE   FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+# select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	Using where
+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	8	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	8	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_prev	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_key	1
+Handler_read_prev	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
+INSERT INTO t2 SELECT i, i, i FROM t1;
+#
+# query:  DELETE   FROM t2 ORDER BY a, b DESC LIMIT 5
+# select: SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 ORDER BY a, b DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using filesort
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	5
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_rnd	5
+Handler_read_rnd_next	27
+Sort_rows	26
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
+INSERT INTO t2 (a, b) SELECT i, i FROM t1;
+INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
+#
+# query:  DELETE   FROM t2 ORDER BY a DESC, b DESC LIMIT 5
+# select: SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5
+#
+EXPLAIN DELETE   FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	a	6	NULL	5	
+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	ALL	a	a	6	NULL	5	100.00	
+EXPLAIN EXTENDED SELECT * 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	352040.00	
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_last	1
+Handler_read_prev	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	5
+Handler_read_last	1
+Handler_read_prev	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i INT PRIMARY KEY);
+INSERT INTO t2 (i) SELECT i FROM t1;
+#
+# query:  UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+# select: SELECT * FROM  t2    WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	Using where
+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	8	100.00	Using where
+EXPLAIN EXTENDED SELECT * FROM  t2    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	8	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_next	4
+Handler_read_rnd	5
+Handler_update	5
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1)));
+INSERT INTO t2 (i) SELECT i FROM t1;
+#
+# query:  UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+# select: SELECT * FROM t2     WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	ALL	i	NULL	NULL	NULL	26	Using where; Using filesort
+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	i	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+EXPLAIN EXTENDED SELECT * FROM t2     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	i	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	5
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd	5
+Handler_read_rnd_next	27
+Handler_update	5
+Sort_rows	5
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+#
+# query:  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using where; Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Handler_update	1
+Sort_rows	1
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
+#
+# query:  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	ALL	a	a	15	NULL	17602	Using where
+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	a	a	15	NULL	17602	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	352040.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_next	4
+Handler_read_rnd	5
+Handler_update	5
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+#
+# query:  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using where; Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
+ENGINE=HEAP;
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+#
+# query:  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+# select: SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using where; Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
+Warnings:
+Warning	1713	Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd	1
+Handler_read_rnd_next	27
+Sort_rows	1
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2))
+ENGINE= MyISAM;
+INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
+#
+# query:  UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1
+# select: SELECT * FROM t2      WHERE key1 < 13 or key2 < 14 ORDER BY key1
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	index_merge	key1,key2	key1,key2	5,5	NULL	7	Using sort_union(key1,key2); Using where; Using filesort
+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
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	2
+Handler_read_next	7
+Handler_read_rnd	4
+Sort_range	1
+Sort_rows	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	2
+Handler_read_next	7
+Handler_read_rnd	8
+Handler_update	4
+Sort_range	1
+Sort_rows	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i INT PRIMARY KEY);
+INSERT INTO t2 (i) SELECT i FROM t1;
+#
+# query:  UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+# select: SELECT * FROM t2     WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	Using where
+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	8	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	8	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_prev	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_prev	4
+Handler_read_rnd	5
+Handler_update	5
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
+INSERT INTO t2 SELECT i, i, i FROM t1;
+#
+# query:  UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5
+# select: SELECT * FROM t2     ORDER BY a, b DESC LIMIT 5
+#
+EXPLAIN UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	Using filesort
+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	a	NULL	NULL	NULL	26	100.00	Using filesort
+EXPLAIN EXTENDED SELECT * 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	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_rnd_next	27
+Sort_rows	5
+Sort_scan	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_rnd	5
+Handler_read_rnd_next	27
+Handler_update	4
+Sort_rows	5
+Sort_scan	1
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+(30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
+INSERT INTO t2 (a, b) SELECT i, i FROM t1;
+INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
+#
+# query:  UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5
+# select: SELECT * FROM t2     ORDER BY a DESC, b DESC LIMIT 5
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	ALL	a	a	6	NULL	5	
+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	ALL	a	a	6	NULL	5	100.00	
+EXPLAIN EXTENDED SELECT * 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	352040.00	
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_last	1
+Handler_read_prev	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_last	1
+Handler_read_prev	4
+Handler_read_rnd	5
+Handler_update	5
+
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+pk INT NOT NULL AUTO_INCREMENT,
+c1_idx CHAR(1) DEFAULT 'y',
+c2 INT,
+PRIMARY KEY (pk),
+INDEX c1_idx (c1_idx)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4);
+#
+# query:  UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+# select: SELECT * FROM t1     WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t1	range	PRIMARY,c1_idx	c1_idx	2	NULL	2	Using where
+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	PRIMARY,c1_idx	c1_idx	2	NULL	2	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	ref	c1_idx	c1_idx	2	const	2	100.00	Using index condition; Using where
+Warnings:
+Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	5
+Handler_read_prev	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	8
+Handler_read_prev	1
+Handler_read_rnd	2
+Handler_update	2
+
+#
+# query:  DELETE   FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+# select: SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t1	range	PRIMARY,c1_idx	c1_idx	2	NULL	2	Using where
+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	PRIMARY,c1_idx	c1_idx	2	NULL	2	100.00	Using where
+EXPLAIN EXTENDED SELECT * 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	ref	c1_idx	c1_idx	2	const	2	100.00	Using index condition; Using where
+Warnings:
+Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	5
+Handler_read_prev	1
+# Status of testing query execution:
+Variable_name	Value
+Handler_delete	2
+Handler_read_key	5
+Handler_read_prev	1
+
+DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+#
+# query:  UPDATE t1 SET a=a+10 WHERE a > 34
+# select: SELECT * FROM t1     WHERE a > 34
+#
+EXPLAIN UPDATE t1 SET a=a+10 WHERE a > 34;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where
+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	3	100.00	Using where
+EXPLAIN EXTENDED SELECT * FROM t1     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	3	100.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 34)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_next	2
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	1
+Handler_read_next	2
+Handler_read_rnd	2
+Handler_update	2
+
+DROP TABLE t1;
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
+CREATE TABLE t2 (c1 INT, c2 INT);
+INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20);
+#
+# query:  UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10
+# select: SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
+#
+EXPLAIN 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	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+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	
+EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
+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	select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,NULL AS `c1`,NULL AS `c2` from `test`.`t1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd_next	4
+
+#
+# query:  UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10
+# select: SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1                WHERE t1.c3 = 10
+#
+EXPLAIN UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+EXPLAIN EXTENDED UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 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	Using where
+EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1                WHERE t1.c3 = 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	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,NULL AS `c1`,NULL AS `c2` from `test`.`t1` where (`test`.`t1`.`c3` = 10)
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd_next	4
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_first	1
+Handler_read_rnd_next	4
+
+DROP TABLE t1, t2;
+CREATE TABLE t1(f1 INT, f2 INT);
+CREATE TABLE t2(f3 INT, f4 INT);
+CREATE INDEX IDX ON t2(f3);
+INSERT INTO t1 VALUES(1,0),(2,0);
+INSERT INTO t2 VALUES(1,1),(2,2);
+#
+# query:  UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)
+# select: SELECT              (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1
+#
+EXPLAIN UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	ref	IDX	IDX	5	test.t1.f1	2	
+EXPLAIN EXTENDED UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DEPENDENT SUBQUERY	t2	ref	IDX	IDX	5	test.t1.f1	2	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+EXPLAIN EXTENDED SELECT              (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DEPENDENT SUBQUERY	t2	ref	IDX	IDX	5	test.t1.f1	2	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+Note	1003	select (select max(`test`.`t2`.`f4`) from `test`.`t2` where (`test`.`t2`.`f3` = `test`.`t1`.`f1`)) AS `(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)` from `test`.`t1`
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
+Handler_read_key	2
+Handler_read_next	2
+Handler_read_rnd_next	3
+# Status of testing query execution:
+Variable_name	Value
+Handler_read_key	2
+Handler_read_next	2
+Handler_read_rnd_next	3
+Handler_update	2
+
+DROP TABLE t1, t2;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1);
+SET @a = NULL;
+EXPLAIN DELETE FROM t1 WHERE (@a:= a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
+ERROR 42S22: Unknown column 'uknown_column' in 'where clause'
+EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
+ERROR 42S22: Unknown column 'uknown_column' in 'where clause'
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT);
+EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2;
+ERROR 42S22: Unknown column 'f2' in 'order clause'
+UPDATE t1 SET f2=1 ORDER BY f2;
+ERROR 42S22: Unknown column 'f2' in 'order clause'
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT, KEY( a ) );
+INSERT INTO t1 VALUES (0), (1);
+CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
+SET SESSION sql_safe_updates = 1;
+EXPLAIN EXTENDED UPDATE IGNORE v1 SET a = 1;
+ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
+UPDATE IGNORE v1 SET a = 1;
+ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
+SET SESSION sql_safe_updates = DEFAULT;
+DROP TABLE t1;
+DROP VIEW v1;

=== added file 'mysql-test/t/explain_non_select.test'
--- a/mysql-test/t/explain_non_select.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/explain_non_select.test	2011-04-01 13:53:57 +0000
@@ -0,0 +1,550 @@
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  UPDATE t1 SET a = 10 WHERE a < 10
+--let $select = SELECT * FROM t1     WHERE a < 10
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  DELETE   FROM t1 WHERE a < 10
+--let $select = SELECT * FROM t1 WHERE a < 10
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  DELETE   FROM t1 USING t1 WHERE a = 1
+--let $select = SELECT * FROM t1          WHERE a = 1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1, t2 SET t1.a = 10 WHERE t1.a = 1
+--let $select = SELECT * FROM t1, t2               WHERE t1.a = 1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1
+--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a = 1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3)
+--let $select = SELECT * FROM t1            WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3)
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3)
+--let $select = SELECT * FROM t1            WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3)
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
+--let $select = SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10
+--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10
+--let $select = SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE        t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1
+--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a > 1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  DELETE   FROM t1 WHERE a > 1 LIMIT 1
+--let $select = SELECT * FROM t1 WHERE a > 1 LIMIT 1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  DELETE   FROM t1 WHERE 0
+--let $select = SELECT * FROM t1 WHERE 0
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  DELETE   FROM t1 USING t1 WHERE 0
+--let $select = SELECT * FROM t1          WHERE 0
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
+INSERT INTO t1 VALUES (3, 3), (7, 7);
+--let $query =  DELETE   FROM t1 WHERE a = 3
+--let $select = SELECT * FROM t1 WHERE a = 3
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b));
+INSERT INTO t1 VALUES (3, 3), (7, 7);
+--let $query =  DELETE   FROM t1 WHERE a < 3
+--let $select = SELECT * FROM t1 WHERE a < 3
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 ( a int PRIMARY KEY );
+--let $query =  DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+--let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+--source include/explain_non_select.inc
+INSERT INTO t1 VALUES (1), (2), (3);
+--let $query =  DELETE   FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+--let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1(a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (4),(3),(1),(2);
+--let $query =  DELETE   FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1
+--let $select = SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a));
+INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), ();
+UPDATE t1 SET a = c, b = c;
+--let $query =  DELETE   FROM t1 ORDER BY a ASC, b ASC LIMIT 1
+--let $select = SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL);
+CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2));
+CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3));
+INSERT INTO t1 VALUES (1,1), (2,1), (1,3);
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+INSERT INTO t3 VALUES (1,1), (2,1), (1,3);
+--let $query =  DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3
+--let $select = SELECT *        FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3
+--source include/explain_non_select.inc
+DROP TABLE t1, t2, t3;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+--let $query =  UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2)
+--let $select = SELECT * FROM t1     WHERE a IN (SELECT a FROM t2)
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a1 INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 (a2 VARCHAR(10));
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5);
+SET @save_optimizer_switch= @@optimizer_switch;
+SET @@optimizer_switch= 'semijoin=off';
+--let $query =  DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+--let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+--source include/explain_non_select.inc
+SET @@optimizer_switch= @save_optimizer_switch;
+TRUNCATE t1;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+--let $query =  DELETE   FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+--let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2)
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT, j INT);
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+--let $query =  UPDATE t1 SET i = 10
+--let $select = SELECT * FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT, j INT);
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+--let $query =  DELETE FROM t1
+--let $select = SELECT * FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
+--let $query =  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (i INT);
+--let $query =  INSERT INTO t2 SELECT * FROM t1
+--let $select =                SELECT * FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 (i INT);
+--let $query =  REPLACE INTO t2 SELECT * FROM t1
+--let $select =                 SELECT * FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+--let $query = INSERT INTO t1 SET i = 10
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT);
+--let $query = REPLACE INTO t1 SET i = 10
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, i INT PRIMARY KEY);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+--let $query =  DELETE   FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1)));
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+--let $query =  DELETE   FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+--let $query =  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
+--let $query =  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+--let $query =  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
+  ENGINE=HEAP;
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+--let $query =  DELETE   FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2))
+  ENGINE= MyISAM;
+INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
+--let $query =  DELETE   FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
+--let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i INT PRIMARY KEY);
+INSERT INTO t2 (i) SELECT i FROM t1;
+--let $query =  DELETE   FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                          (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
+INSERT INTO t2 SELECT i, i, i FROM t1;
+--let $query =  DELETE   FROM t2 ORDER BY a, b DESC LIMIT 5
+--let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
+INSERT INTO t2 (a, b) SELECT i, i FROM t1;
+INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
+--let $query =  DELETE   FROM t2 ORDER BY a DESC, b DESC LIMIT 5
+--let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i INT PRIMARY KEY);
+INSERT INTO t2 (i) SELECT i FROM t1;
+--let $query =  UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--let $select = SELECT * FROM  t2    WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1)));
+INSERT INTO t2 (i) SELECT i FROM t1;
+--let $query =  UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--let $select = SELECT * FROM t2     WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+--let $query =  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c));
+INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1;
+INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2;
+--let $query =  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c));
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+--let $query =  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c))
+  ENGINE=HEAP;
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+--let $query =  UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5
+--let $select = SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2))
+  ENGINE= MyISAM;
+INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
+--let $query =  UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1
+--let $select = SELECT * FROM t2      WHERE key1 < 13 or key2 < 14 ORDER BY key1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2(a INT, i INT PRIMARY KEY);
+INSERT INTO t2 (i) SELECT i FROM t1;
+--let $query =  UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+--let $select = SELECT * FROM t2     WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b));
+INSERT INTO t2 SELECT i, i, i FROM t1;
+--let $query =  UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5
+--let $select = SELECT * FROM t2     ORDER BY a, b DESC LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b));
+INSERT INTO t2 (a, b) SELECT i, i FROM t1;
+INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2;
+--let $query =  UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5
+--let $select = SELECT * FROM t2     ORDER BY a DESC, b DESC LIMIT 5
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+--disable_warnings
+CREATE TABLE t1 (
+  pk INT NOT NULL AUTO_INCREMENT,
+  c1_idx CHAR(1) DEFAULT 'y',
+  c2 INT,
+  PRIMARY KEY (pk),
+  INDEX c1_idx (c1_idx)
+) ENGINE=InnoDB;
+--enable_warnings
+INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4);
+--let $query =  UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+--let $select = SELECT * FROM t1     WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+--source include/explain_non_select.inc
+--let $query =  DELETE   FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+--let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); 
+--let $query =  UPDATE t1 SET a=a+10 WHERE a > 34
+--let $select = SELECT * FROM t1     WHERE a > 34
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
+CREATE TABLE t2 (c1 INT, c2 INT);
+INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20);
+--let $query =  UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10
+--let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
+--source include/explain_non_select.inc
+--let $query =  UPDATE        t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10
+--let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1                WHERE t1.c3 = 10
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+CREATE TABLE t1(f1 INT, f2 INT);
+CREATE TABLE t2(f3 INT, f4 INT);
+CREATE INDEX IDX ON t2(f3);
+INSERT INTO t1 VALUES(1,0),(2,0);
+INSERT INTO t2 VALUES(1,1),(2,2);
+--let $query =  UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)
+--let $select = SELECT              (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1);
+SET @a = NULL;
+EXPLAIN DELETE FROM t1 WHERE (@a:= a);
+if (`SELECT @a IS NOT NULL`) {
+  die Unexpectedly modified user variable;
+}
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+--error ER_BAD_FIELD_ERROR
+                 DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
+--error ER_BAD_FIELD_ERROR
+EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT);
+--error ER_BAD_FIELD_ERROR
+EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2;
+--error ER_BAD_FIELD_ERROR
+UPDATE t1 SET f2=1 ORDER BY f2;
+DROP TABLE t1;
+
+CREATE TABLE t1 ( a INT, KEY( a ) );
+INSERT INTO t1 VALUES (0), (1);
+CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
+SET SESSION sql_safe_updates = 1;
+--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
+EXPLAIN EXTENDED UPDATE IGNORE v1 SET a = 1;
+--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
+UPDATE IGNORE v1 SET a = 1;
+SET SESSION sql_safe_updates = DEFAULT;
+DROP TABLE t1;
+DROP VIEW v1;
+

=== modified file 'sql/CMakeLists.txt'
--- a/sql/CMakeLists.txt	2011-02-02 08:30:13 +0000
+++ b/sql/CMakeLists.txt	2011-04-01 13:53:57 +0000
@@ -72,6 +72,7 @@ SET(SQL_SHARED_SOURCES
   mf_iocache.cc
   my_decimal.cc
   net_serv.cc
+  opt_explain.cc
   opt_range.cc
   opt_sum.cc 
   parse_file.cc

=== added file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc	1970-01-01 00:00:00 +0000
+++ b/sql/opt_explain.cc	2011-04-01 13:53:57 +0000
@@ -0,0 +1,1450 @@
+/* Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software Foundation,
+   51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+#include "opt_explain.h"
+#include "sql_partition.h" // for make_used_partitions_str()
+
+
+/**
+  A base for all Explain_* classes
+
+  This class hierarchy is a successor of the old select_describe() function
+  implementation. It extends old select_describe() functionality to deal with
+  single-table data-modifying commands (UPDATE and DELETE).
+*/
+
+class Explain
+{
+private:
+  List<Item> items; ///< item list to feed select_result::send_data()
+  Item_null *nil; ///< pre-allocated NULL item to fill empty columns in EXPLAIN
+
+protected:
+  /*
+    Next "col_*" fields are intended for the filling by "explain_*()" methods.
+    Then the make_list() method links these Items into "items" list.
+
+    NOTE: NULL value means that Item_null object will be pushed into "items"
+          list instead.
+  */
+  Item_uint   *col_id; ///< "id" column: seq. number of SELECT withing the query
+  Item_string *col_select_type; ///< "select_type" column
+  Item_string *col_table_name; ///< "table" to which the row of output refers
+  Item_string *col_partitions; ///< "partitions" column
+  Item_string *col_join_type; ///< "type" column, see join_type_str array
+  Item_string *col_possible_keys; ///< "possible_keys": comma-separated list
+  Item_string *col_key; ///< "key" column: index that is actually decided to use
+  Item_string *col_key_len; ///< "key_length" column: length of the "key" above
+  Item_string *col_ref; ///< "ref":columns/constants which are compared to "key"
+  Item_int    *col_rows; ///< "rows": estimated number of examined table rows
+  Item_float  *col_filtered; ///< "filtered": % of rows filtered by condition
+  Item_string *col_extra; ///< "extra" column: additional information
+
+  THD *thd; ///< cached THD pointer
+  const CHARSET_INFO *cs; ///< cached pointer to system_charset_info
+  JOIN *join; ///< top-level JOIN (if any) provided by caller
+  SELECT_LEX *select_lex; ///< cached select_lex pointer
+
+  select_result *external_result; ///< result stream (if any) provided by caller
+
+public:
+  explicit Explain(JOIN *join_arg= NULL)
+  : nil(NULL),
+    thd(current_thd),
+    cs(system_charset_info),
+    join(join_arg),
+    select_lex(join ? join->select_lex : &thd->lex->select_lex),
+    external_result(join ? join->result : NULL)
+  {
+    init_columns();
+  }
+  virtual ~Explain() {}
+
+  bool send();
+
+private:
+  void init_columns();
+  bool make_list();
+  bool push(Item *item) { return items.push_back(item ? item : nil); }
+
+protected:
+  bool describe(uint8 mask) { return thd->lex->describe & mask; }
+
+  /**
+    Prepare the self-allocated result object
+
+    For queries with top-level JOIN the caller provides pre-allocated
+    select_send object. Then that JOIN object prepares the select_send
+    object calling result->prepare() in JOIN::prepare(),
+    result->initalize_tables() in JOIN::optimize() and result->prepare2()
+    in JOIN::exe().
+    However witout the presence of the top-level JOIN we have to
+    prepare/initialize select_send object manually.
+  */
+  bool prepare(select_result *result)
+  {
+    DBUG_ASSERT(join == NULL);
+    List<Item> dummy;
+    return result->prepare(dummy, select_lex->master_unit()) ||
+           result->prepare2();
+  }
+
+  virtual bool send_to(select_result *to);
+
+  /*
+    Rest of the methods are overloadable functions those calculate and fill
+    "col_*" fields with Items for further sending as EXPLAIN columns.
+
+    "explain_*" methods return FALSE on success and TRUE on error (usually OOM).
+  */
+  virtual bool explain_id();
+  virtual bool explain_select_type();
+  virtual bool explain_table_name() { return FALSE; }
+  virtual bool explain_partitions() { return FALSE; }
+  virtual bool explain_join_type() { return FALSE; }
+  virtual bool explain_possible_keys() { return FALSE; }
+  /* fill col_key and and col_key_len fields together */
+  virtual bool explain_key_and_len() { return FALSE; }
+  virtual bool explain_ref() { return FALSE; }
+  /* fill col_rows and col_filtered fields together */
+  virtual bool explain_rows_and_filtered() { return FALSE; }
+  virtual bool explain_extra();
+};
+
+
+/**
+  Explain_msg class outputs a trivial EXPLAIN row with "extra" column
+
+  Fromer part of the old select_describe() function.
+  This class is intended for simple cases to produce EXPLAIN output
+  with "No tables used", "No matching records" etc.
+  Optionally it can output number of estimated rows in the "row"
+  column.
+
+  NOTE: this class also produces EXPLAIN rows for inner units (if any).
+*/
+
+class Explain_msg: public Explain
+{
+private:
+  const char *message; ///< cached "message" argument
+  const ha_rows rows; ///< HA_POS_ERROR or cached "rows" argument
+
+public:
+  Explain_msg(JOIN *join_arg, const char *message_arg)
+  : Explain(join_arg), message(message_arg), rows(HA_POS_ERROR)
+  {}
+  
+  explicit Explain_msg(const char *message_arg, ha_rows rows_arg= HA_POS_ERROR)
+  : message(message_arg), rows(rows_arg)
+  {}
+
+protected:
+  virtual bool explain_rows_and_filtered();
+  virtual bool explain_extra();
+};
+
+
+/**
+  Explain_union class outputs EXPLAIN row for UNION
+
+  Former part of the old select_describe() function.
+*/
+
+class Explain_union : public Explain
+{
+private:
+  char table_name_buffer[NAME_CHAR_LEN];
+
+public:
+  Explain_union(JOIN *join_arg) : Explain(join_arg)
+  {
+    /* it's a UNION: */
+    DBUG_ASSERT(join_arg->select_lex == join_arg->unit->fake_select_lex);
+  }
+
+protected:
+  virtual bool explain_id();
+  virtual bool explain_table_name();
+  virtual bool explain_join_type();
+  virtual bool explain_extra();
+};
+
+
+
+/**
+  Common base class for Explain_join and Explain_table
+
+  Former part of the old select_describe() function.
+*/
+
+class Explain_table_base : public Explain {
+protected:
+  TABLE *table;
+  key_map usable_keys;
+
+  char buff_possible_keys[512];
+  String str_possible_keys;
+
+public:
+  explicit Explain_table_base(JOIN *join_arg)
+  : Explain(join_arg), table(NULL),
+    str_possible_keys(buff_possible_keys, sizeof(buff_possible_keys), cs)
+  {}
+
+  explicit Explain_table_base(TABLE *table_arg)
+  : table(table_arg),
+    str_possible_keys(buff_possible_keys, sizeof(buff_possible_keys), cs)
+  {}
+
+protected:
+  virtual bool explain_partitions();
+  virtual bool explain_possible_keys();
+};
+
+
+/**
+  Explain_join class produces EXPLAIN output for JOINs
+
+  Former part of the old select_describe() function.
+*/
+
+class Explain_join : public Explain_table_base
+{
+private:
+  const bool need_tmp_table;
+  const bool need_order;
+  const bool distinct;
+
+  uint tabnum;
+  JOIN_TAB *tab;
+  int quick_type;
+  table_map used_tables;
+  uint last_sjm_table;
+
+  char table_name_buffer[NAME_LEN];
+
+  char buff_key[512];
+  String str_key;
+
+  char buff_key_len[512];
+  String str_key_len;
+
+  char buff_ref[512];
+  String str_ref;
+
+  char buff_extra[512];
+  String str_extra;
+
+public:
+  Explain_join(JOIN *join_arg,
+               bool need_tmp_table_arg, bool need_order_arg,
+               bool distinct_arg) 
+  : Explain_table_base(join_arg), need_tmp_table(need_tmp_table_arg),
+    need_order(need_order_arg), distinct(distinct_arg),
+    tabnum(0), used_tables(0), last_sjm_table(MAX_TABLES),
+    str_key(buff_key, sizeof(buff_key), cs),
+    str_key_len(buff_key_len, sizeof(buff_key_len), cs),
+    str_ref(buff_ref, sizeof(buff_ref), cs),
+    str_extra(buff_extra, sizeof(buff_extra), cs)
+  {
+    /* it is not UNION: */
+    DBUG_ASSERT(join_arg->select_lex != join_arg->unit->fake_select_lex);
+  }
+
+protected:
+  virtual bool send_to(select_result *to);
+  virtual bool explain_table_name();
+  virtual bool explain_join_type();
+  virtual bool explain_key_and_len();
+  virtual bool explain_ref();
+  virtual bool explain_rows_and_filtered();
+  virtual bool explain_extra();
+};
+
+
+/**
+  Explain_table class produce EXPLAIN output for queries without top-level JOIN
+
+  This class is a simplified version of the Explain_join class. It works in the
+  context of queries which implementation lacks top-level JOIN object (EXPLAIN
+  single-table UPDATE and DELETE).
+*/
+
+class Explain_table: public Explain_table_base
+{
+private:
+  const SQL_SELECT *select;    ///< cached "select" argument
+  const uint       key;        ///< cached "key" number argument
+  const ha_rows    limit;      ///< HA_POS_ERROR or cached "limit" argument
+  const bool       need_sort;  ///< cached need_sort argument
+
+  /*
+    Pre-allocated buffers and String wrapper objects for them
+  */
+  char buff_key[512];
+  String str_key;
+  char buff_key_len[512];
+  String str_key_len;
+  char buff_extra[512];
+  String str_extra;
+
+public:
+  Explain_table(TABLE *table_arg, SQL_SELECT *select_arg,
+                uint key_arg, ha_rows limit_arg, bool need_sort_arg)
+  : Explain_table_base(table_arg), select(select_arg), key(key_arg),
+    limit(limit_arg), need_sort(need_sort_arg),
+    str_key(buff_key, sizeof(buff_key), cs),
+    str_key_len(buff_key_len, sizeof(buff_key_len), cs),
+    str_extra(buff_extra, sizeof(buff_extra), cs)
+  {
+    usable_keys= table->keys_in_use_for_query;
+  }
+  
+private:
+  virtual bool explain_table_name();
+  virtual bool explain_join_type();
+  virtual bool explain_key_and_len();
+  virtual bool explain_rows_and_filtered();
+  virtual bool explain_extra();
+};
+
+
+static join_type calc_join_type(int quick_type)
+{
+  if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
+      (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
+      (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
+    return JT_INDEX_MERGE;
+  else
+    return JT_RANGE;
+}
+
+
+/* Explain class methods ******************************************************/
+
+
+/**
+  Explain class main function
+  
+  This method:
+    a) allocates a select_send object (if no one pre-allocated available),
+    b) calculates and sends whole EXPLAIN data.
+
+  @returns
+    @retval FALSE  Ok
+    @retval TRUE   Error
+*/
+
+bool Explain::send()
+{
+  /* Don't log this into the slow query log */
+  thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED |
+                         SERVER_QUERY_NO_GOOD_INDEX_USED);
+
+  select_result *result;
+  if (external_result == NULL)
+  {
+    /* Create select_result object if the called doesn't provide one: */
+    result= new select_send;
+    if (!(result= new select_send))
+      return TRUE;
+    if (thd->send_explain_fields(result) || prepare(result))
+    {
+      delete result;
+      return TRUE;
+    }
+  }
+  else
+  {
+    result= external_result;
+    external_result->reset_offset_limit_cnt();
+  }
+
+  if (!(nil= new Item_null))
+    return TRUE;
+  bool ret= send_to(result);
+
+  if (ret && join)
+    join->error= 1;
+
+  for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit();
+       unit && !ret;
+       unit= unit->next_unit())
+    ret= mysql_explain_union(thd, unit, result);
+
+  if (external_result == NULL)
+  {
+    if (ret)
+      result->abort_result_set();
+    else
+      result->send_eof();
+    delete result;
+  }
+  return ret;
+}
+
+
+/**
+  Reset all "col_*" fields
+*/
+
+void Explain::init_columns()
+{
+  col_id= NULL;
+  col_select_type= NULL;
+  col_table_name= NULL;
+  col_partitions= NULL;
+  col_join_type= NULL;
+  col_possible_keys= NULL;
+  col_key= NULL;
+  col_key_len= NULL;
+  col_ref= NULL;
+  col_rows= NULL;
+  col_filtered= NULL;
+  col_extra= NULL;
+}
+
+
+/**
+  Calculate EXPLAIN column values and link them into "items" list
+
+  @returns
+    @retval FALSE  Ok
+    @retval TRUE   Error
+*/
+
+bool Explain::make_list()
+{
+  if (explain_id() ||
+      explain_select_type() ||
+      explain_table_name() ||
+      explain_partitions() ||
+      explain_join_type() ||
+      explain_possible_keys() ||
+      explain_key_and_len() ||
+      explain_ref() ||
+      explain_rows_and_filtered() ||
+      explain_extra())
+    return TRUE;
+    
+  /* 
+    NOTE: the number/types of items pushed into item_list must be in sync with
+    EXPLAIN column types as they're "defined" in THD::send_explain_fields()
+  */
+  return push(col_id) ||
+         push(col_select_type) ||
+         push(col_table_name) ||
+         (describe(DESCRIBE_PARTITIONS) && push(col_partitions)) ||
+         push(col_join_type) ||
+         push(col_possible_keys) ||
+         push(col_key) ||
+         push(col_key_len) ||
+         push(col_ref) ||
+         push(col_rows) ||
+         (describe(DESCRIBE_EXTENDED) && push(col_filtered)) ||
+         push(col_extra);
+}
+
+
+/**
+  Make "items" list and send it to select_result output stream
+
+  This method is virtual since the base implementation is intended for sending
+  the "items" list once, but the overloaded Explain_join implementation sends
+  it many times (once for each JOIN::join_tab[] element).
+
+  @returns
+    @retval FALSE  Ok
+    @retval TRUE   Error
+*/
+
+bool Explain::send_to(select_result *to)
+{
+  bool ret= make_list() || to->send_data(items);
+  items.empty();
+  init_columns();
+  return ret;
+}
+
+
+bool Explain::explain_id()
+{
+  col_id= new Item_uint(select_lex->select_number);
+  return col_id == NULL;
+}
+
+
+bool Explain::explain_select_type()
+{
+  if (select_lex->type)
+    col_select_type= new Item_string(select_lex->type,
+                                     strlen(select_lex->type), cs);
+  else if (select_lex->first_inner_unit() || select_lex->next_select())
+    col_select_type= new Item_string(STRING_WITH_LEN("PRIMARY"), cs);
+  else
+    col_select_type= new Item_string(STRING_WITH_LEN("SIMPLE"), cs);
+  return col_select_type == NULL;                                       
+}
+
+
+bool Explain::explain_extra()
+{
+  col_extra= new Item_string("", 0, cs);
+  return col_extra == NULL;
+}
+
+
+/* Explain_msg class methods **************************************************/
+
+
+bool Explain_msg::explain_rows_and_filtered()
+{
+  if (rows == HA_POS_ERROR)
+    return FALSE;
+  col_rows= new Item_int(rows, MY_INT64_NUM_DECIMAL_DIGITS);
+  return col_rows == NULL;
+}
+
+
+bool Explain_msg::explain_extra()
+{
+  col_extra= new Item_string(message, strlen(message), cs);
+  return col_extra == NULL;
+}
+
+
+/* Explain_union class methods ************************************************/
+
+
+bool Explain_union::explain_id()
+{
+  col_id= NULL;
+  return FALSE;
+}
+
+
+bool Explain_union::explain_table_name()
+{
+  SELECT_LEX *last_select= join->unit->first_select()->last_select();
+  // # characters needed to print select_number of last select
+  int last_length= (int)log10((double)last_select->select_number)+1;
+
+  SELECT_LEX *sl= join->unit->first_select();
+  uint len= 6, lastop= 0;
+  memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
+  /*
+    - len + lastop: current position in table_name_buffer
+    - 6 + last_length: the number of characters needed to print
+      '...,'<last_select->select_number>'>\0'
+  */
+  for (; 
+       sl && len + lastop + 6 + last_length < NAME_CHAR_LEN; 
+       sl= sl->next_select())
+  {
+    len+= lastop;
+    lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
+                        "%u,", sl->select_number);
+  }
+  if (sl || len + lastop >= NAME_CHAR_LEN)
+  {
+    memcpy(table_name_buffer + len, STRING_WITH_LEN("...,"));
+    len+= 4;
+    lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
+                        "%u,", last_select->select_number);
+  }
+  len+= lastop;
+  table_name_buffer[len - 1]= '>';  // change ',' to '>'
+
+  col_table_name= new Item_string(table_name_buffer, len, cs);
+
+  return col_table_name == NULL;
+}
+
+
+bool Explain_union::explain_join_type()
+{
+  col_join_type= new Item_string(join_type_str[JT_ALL],
+                                 strlen(join_type_str[JT_ALL]), cs);
+  return col_join_type == NULL;
+}
+
+
+bool Explain_union::explain_extra()
+{
+  /* 
+    Moved from select_describe():
+
+    here we assume that the query will return at least two rows, so we
+    show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
+    and no filesort will be actually done, but executing all selects in
+    the UNION to provide precise EXPLAIN information will hardly be
+    appreciated :)
+  */
+  if (join->unit->global_parameters->order_list.first)
+  {
+    col_extra= new Item_string(STRING_WITH_LEN("Using filesort"), cs);
+    return col_extra == NULL;
+  }
+  return Explain::explain_extra();
+}
+
+
+/* Explain_table_base class methods *******************************************/
+
+
+bool Explain_table_base::explain_partitions()
+{
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+  if (!table->derived_select_number && table->part_info)
+  {          
+    col_partitions= new Item_string(cs);
+    if (col_partitions == NULL)
+      return TRUE;
+    make_used_partitions_str(table->part_info, &col_partitions->str_value);
+  }
+#endif
+  return FALSE;
+}
+
+
+bool Explain_table_base::explain_possible_keys()
+{
+  if (usable_keys.is_clear_all())
+    return FALSE;
+
+  str_possible_keys.length(0);
+
+  for (uint j=0 ; j < table->s->keys ; j++)
+  {
+    if (usable_keys.is_set(j))
+    {
+      if (str_possible_keys.length())
+        str_possible_keys.append(',');
+      str_possible_keys.append(table->key_info[j].name, 
+                               strlen(table->key_info[j].name), cs);
+    }
+  }
+  if (str_possible_keys.length())
+  {
+    col_possible_keys= new Item_string(str_possible_keys.ptr(),
+                                       str_possible_keys.length(), cs);
+    return col_possible_keys == NULL;
+  }
+  return FALSE;
+}
+
+
+/* Explain_join class methods *************************************************/
+
+
+bool Explain_join::send_to(select_result *to)
+{
+  for (; tabnum < join->tables; tabnum++)
+  {
+    tab= join->join_tab + tabnum;
+    table= tab->table;
+    usable_keys= tab->keys;
+    quick_type= -1;
+    
+    if (tab->type == JT_ALL && tab->select && tab->select->quick)
+    {
+      quick_type= tab->select->quick->get_type();
+      tab->type= calc_join_type(quick_type);
+    }
+
+    if (Explain_table_base::send_to(external_result))
+      return TRUE;
+      
+    used_tables|= table->map;
+  }
+  return FALSE;
+}
+
+
+bool Explain_join::explain_table_name()
+{
+  if (table->derived_select_number)
+  {
+    /* Derived table name generation */
+    int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer) - 1,
+                         "<derived%u>", table->derived_select_number);
+    col_table_name= new Item_string(table_name_buffer, len, cs);
+  }
+  else
+  {
+    TABLE_LIST *real_table= table->pos_in_table_list; 
+    col_table_name= new Item_string(real_table->alias,
+                                    strlen(real_table->alias), cs);
+  }
+  return col_table_name == NULL;
+}
+
+
+bool Explain_join::explain_join_type()
+{
+  col_join_type= new Item_string(join_type_str[tab->type],
+                                 strlen(join_type_str[tab->type]), cs);
+  return col_join_type == NULL;
+}
+
+
+bool Explain_join::explain_key_and_len()
+{
+  str_key.length(0);
+  str_key_len.length(0);
+  
+  if (tab->ref.key_parts)
+  {
+    KEY *key_info= table->key_info + tab->ref.key;
+    uint length;
+    col_key= new Item_string(key_info->name, strlen(key_info->name), cs);
+    length= longlong2str(tab->ref.key_length, buff_key_len, 10) - buff_key_len;
+    col_key_len= new Item_string(buff_key_len, length, cs);
+    return col_key == NULL || col_key_len == NULL;
+  }
+  else if (tab->type == JT_NEXT)
+  {
+    KEY *key_info= table->key_info + tab->index;
+    uint length;
+    col_key= new Item_string(key_info->name, strlen(key_info->name), cs);
+    length= longlong2str(key_info->key_length, buff_key_len, 10) - 
+            buff_key_len;
+    col_key_len= new Item_string(buff_key_len, length, cs);
+    return col_key == NULL || col_key_len == NULL;
+  }
+  else if (tab->select && tab->select->quick)
+  {
+    tab->select->quick->add_keys_and_lengths(&str_key, &str_key_len);
+    col_key= new Item_string(str_key.ptr(), str_key.length(), cs);
+    col_key_len= new Item_string(str_key_len.ptr(), str_key_len.length(), cs);
+    return col_key == NULL || col_key_len == NULL;
+  }
+  else
+  {
+    TABLE_LIST *table_list= tab->table->pos_in_table_list;
+    if (table_list->schema_table &&
+        table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
+    {
+      const char *f_name;
+      int f_idx;
+      if (table_list->has_db_lookup_value)
+      {
+        f_idx= table_list->schema_table->idx_field1;
+        f_name= table_list->schema_table->fields_info[f_idx].field_name;
+        str_key.append(f_name, strlen(f_name), cs);
+      }          
+      if (table_list->has_table_lookup_value)
+      {
+        if (table_list->has_db_lookup_value)
+          str_key.append(',');
+        f_idx= table_list->schema_table->idx_field2;
+        f_name= table_list->schema_table->fields_info[f_idx].field_name;
+        str_key.append(f_name, strlen(f_name), cs);
+      }
+      if (str_key.length())
+      {
+        col_key= new Item_string(str_key.ptr(), str_key.length(), cs);
+        return col_key == NULL;
+      }
+    }
+  }
+  return FALSE;
+}
+
+
+bool Explain_join::explain_ref()
+{
+  str_ref.length(0);
+
+  if (tab->ref.key_parts)
+  {
+    for (store_key **ref= tab->ref.key_copy; *ref; ref++)
+    {
+      if (str_ref.length())
+        str_ref.append(',');
+      str_ref.append((*ref)->name(), strlen((*ref)->name()), cs);
+    }
+    col_ref= new Item_string(str_ref.ptr(), str_ref.length(), cs);
+    return col_ref == NULL;
+  }
+  return FALSE;
+}
+
+
+bool Explain_join::explain_rows_and_filtered()
+{
+  if (tab->table->pos_in_table_list->schema_table)
+    return FALSE;
+
+  double examined_rows;
+  if (tab->select && tab->select->quick)
+    examined_rows= rows2double(tab->select->quick->records);
+  else if (tab->type == JT_NEXT || tab->type == JT_ALL)
+  {
+    if (tab->limit)
+      examined_rows= rows2double(tab->limit);
+    else
+    {
+      tab->table->file->info(HA_STATUS_VARIABLE);
+      examined_rows= rows2double(tab->table->file->stats.records);
+    }
+  }
+  else
+    examined_rows= join->best_positions[tabnum].records_read; 
+
+  col_rows= new Item_int((longlong) (ulonglong) examined_rows, 
+                         MY_INT64_NUM_DECIMAL_DIGITS);
+  if (col_rows == NULL)
+    return TRUE;
+
+  /* Add "filtered" field */
+  if (describe(DESCRIBE_EXTENDED))
+  {
+    float f= 0.0; 
+    if (examined_rows)
+      f= (float) (100.0 * join->best_positions[tabnum].records_read /
+                  examined_rows);
+    col_filtered= new Item_float(f, 2);
+    if (col_filtered == NULL)
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
+bool Explain_join::explain_extra()
+{
+  str_extra.length(0);
+
+  my_bool key_read=table->key_read;
+  if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
+      table->covering_keys.is_set(tab->index))
+    key_read=1;
+  if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
+      !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
+    key_read=1;
+    
+  if (tab->info)
+    col_extra= new Item_string(tab->info,strlen(tab->info), cs);
+  else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+  {
+    if (tab->packed_info & TAB_INFO_USING_INDEX)
+      str_extra.append(STRING_WITH_LEN("; Using index"));
+    if (tab->packed_info & TAB_INFO_USING_WHERE)
+      str_extra.append(STRING_WITH_LEN("; Using where"));
+    if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+      str_extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+    /* Skip initial "; "*/
+    const char *str= str_extra.ptr();
+    uint32 len= str_extra.length();
+    if (len)
+    {
+      str += 2;
+      len -= 2;
+    }
+    col_extra= new Item_string(str, len, cs);
+  }
+  else
+  {
+    uint keyno= MAX_KEY;
+    if (tab->ref.key_parts)
+      keyno= tab->ref.key;
+    else if (tab->select && tab->select->quick)
+      keyno = tab->select->quick->index;
+
+    if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
+         table->file->pushed_idx_cond) || tab->cache_idx_cond)
+      str_extra.append(STRING_WITH_LEN("; Using index condition"));
+
+    if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || 
+        quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
+        quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
+    {
+      str_extra.append(STRING_WITH_LEN("; Using "));
+      tab->select->quick->add_info_string(&str_extra);
+    }
+    if (tab->select)
+    {
+      if (tab->use_quick == QS_DYNAMIC_RANGE)
+      {
+        /* 4 bits per 1 hex digit + terminating '\0' */
+        char buf[MAX_KEY / 4 + 1];
+        str_extra.append(STRING_WITH_LEN("; Range checked for each "
+                                     "record (index map: 0x"));
+        str_extra.append(tab->keys.print(buf));
+        str_extra.append(')');
+      }
+      else if (tab->select->cond)
+      {
+        const Item *pushed_cond= tab->table->file->pushed_cond;
+
+        if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
+            pushed_cond)
+        {
+          str_extra.append(STRING_WITH_LEN("; Using where with pushed "
+                                       "condition"));
+          if (describe(DESCRIBE_EXTENDED))
+          {
+            str_extra.append(STRING_WITH_LEN(": "));
+            ((Item *)pushed_cond)->print(&str_extra, QT_ORDINARY);
+          }
+        }
+        else
+          str_extra.append(STRING_WITH_LEN("; Using where"));
+      }
+    }
+    TABLE_LIST *table_list= tab->table->pos_in_table_list;
+    if (table_list->schema_table &&
+        table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
+    {
+      if (!table_list->table_open_method)
+        str_extra.append(STRING_WITH_LEN("; Skip_open_table"));
+      else if (table_list->table_open_method == OPEN_FRM_ONLY)
+        str_extra.append(STRING_WITH_LEN("; Open_frm_only"));
+      else
+        str_extra.append(STRING_WITH_LEN("; Open_full_table"));
+      if (table_list->has_db_lookup_value &&
+          table_list->has_table_lookup_value)
+        str_extra.append(STRING_WITH_LEN("; Scanned 0 databases"));
+      else if (table_list->has_db_lookup_value ||
+               table_list->has_table_lookup_value)
+        str_extra.append(STRING_WITH_LEN("; Scanned 1 database"));
+      else
+        str_extra.append(STRING_WITH_LEN("; Scanned all databases"));
+    }
+    if (key_read)
+    {
+      if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
+      {
+        QUICK_GROUP_MIN_MAX_SELECT *qgs= 
+          (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
+        str_extra.append(STRING_WITH_LEN("; Using index for group-by"));
+        qgs->append_loose_scan_type(&str_extra);
+      }
+      else
+        str_extra.append(STRING_WITH_LEN("; Using index"));
+    }
+    if (table->reginfo.not_exists_optimize)
+      str_extra.append(STRING_WITH_LEN("; Not exists"));
+
+    if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
+        !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
+         (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED))) 
+    {
+      /*
+        During normal execution of a query, multi_range_read_init() is
+        called to initialize MRR. If HA_MRR_SORTED is set at this point,
+        multi_range_read_init() for any native MRR implementation will
+        revert to default MRR because they cannot produce sorted output
+        currently.
+        Calling multi_range_read_init() can potentially be costly, so it
+        is not done when executing an EXPLAIN. We therefore make the
+        assumption that HA_MRR_SORTED means no MRR. If some MRR native
+        implementation will support sorted output in the future, a
+        function "bool mrr_supports_sorted()" should be added in the
+        handler.
+      */
+      str_extra.append(STRING_WITH_LEN("; Using MRR"));
+    }
+    if (tabnum == 0 && need_tmp_table)
+      str_extra.append(STRING_WITH_LEN("; Using temporary"));
+
+    if (tabnum == 0 && need_order)
+      str_extra.append(STRING_WITH_LEN("; Using filesort"));
+    
+    if (distinct && test_all_bits(used_tables,thd->used_tables))
+      str_extra.append(STRING_WITH_LEN("; Distinct"));
+
+    if (tab->loosescan_match_tab)
+    {
+      str_extra.append(STRING_WITH_LEN("; LooseScan"));
+    }
+
+    if (tab->flush_weedout_table)
+      str_extra.append(STRING_WITH_LEN("; Start temporary"));
+    if (tab->check_weed_out_table)
+      str_extra.append(STRING_WITH_LEN("; End temporary"));
+    else if (tab->do_firstmatch)
+    {
+      if (tab->do_firstmatch == join->join_tab - 1)
+        str_extra.append(STRING_WITH_LEN("; FirstMatch"));
+      else
+      {
+        str_extra.append(STRING_WITH_LEN("; FirstMatch("));
+        TABLE *prev_table=tab->do_firstmatch->table;
+        if (prev_table->derived_select_number)
+        {
+          char namebuf[NAME_LEN];
+          /* Derived table name generation */
+          int len= my_snprintf(namebuf, sizeof(namebuf)-1,
+                               "<derived%u>",
+                               prev_table->derived_select_number);
+          str_extra.append(namebuf, len);
+        }
+        else
+          str_extra.append(prev_table->pos_in_table_list->alias);
+        str_extra.append(STRING_WITH_LEN(")"));
+      }
+    }
+    uint sj_strategy= join->best_positions[tabnum].sj_strategy;
+    if (sj_is_materialize_strategy(sj_strategy))
+    {
+      if (join->best_positions[tabnum].n_sj_tables == 1)
+        str_extra.append(STRING_WITH_LEN("; Materialize"));
+      else
+      {
+        last_sjm_table= tabnum + join->best_positions[tabnum].n_sj_tables - 1;
+        str_extra.append(STRING_WITH_LEN("; Start materialize"));
+      }
+      if (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
+          str_extra.append(STRING_WITH_LEN("; Scan"));
+    }
+    else if (last_sjm_table == tabnum)
+    {
+      str_extra.append(STRING_WITH_LEN("; End materialize"));
+    }
+
+    for (uint part= 0; part < tab->ref.key_parts; part++)
+    {
+      if (tab->ref.cond_guards[part])
+      {
+        str_extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+        break;
+      }
+    }
+
+    if (tabnum > 0 && tab[-1].next_select == sub_select_cache)
+    {
+      str_extra.append(STRING_WITH_LEN("; Using join buffer ("));
+      if ((tab->use_join_cache & JOIN_CACHE::ALG_BNL))
+        str_extra.append(STRING_WITH_LEN("BNL"));
+      else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA))
+        str_extra.append(STRING_WITH_LEN("BKA"));
+      else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA_UNIQUE))
+        str_extra.append(STRING_WITH_LEN("BKA_UNIQUE"));
+      else
+        DBUG_ASSERT(0);
+      if (tab->use_join_cache & JOIN_CACHE::NON_INCREMENTAL_BUFFER)
+        str_extra.append(STRING_WITH_LEN(", regular buffers)"));
+      else
+        str_extra.append(STRING_WITH_LEN(", incremental buffers)"));
+    }
+
+    /* Skip initial "; "*/
+    const char *str= str_extra.ptr();
+    uint32 len= str_extra.length();
+    if (len)
+    {
+      str += 2;
+      len -= 2;
+    }
+    col_extra= new Item_string(str, len, cs);
+  }
+  return col_extra == NULL;
+}
+
+
+/* Explain_table class methods ************************************************/
+
+
+bool Explain_table::explain_table_name()
+{
+  col_table_name= new Item_string(table->alias, strlen(table->alias), cs);
+  return col_table_name == NULL;                                    
+}
+
+
+bool Explain_table::explain_join_type()
+{
+  join_type jt;
+  if (select && select->quick)
+    jt= calc_join_type(select->quick->get_type());
+  else
+    jt= JT_ALL;
+
+  col_join_type= new Item_string(join_type_str[jt],
+                                 strlen(join_type_str[jt]), cs);
+  return col_join_type == NULL;                                   
+}
+
+
+bool Explain_table::explain_key_and_len()
+{
+  str_key.length(0);
+  str_key_len.length(0);
+
+  if (key != MAX_KEY)
+  {
+    KEY *key_info= table->key_info + key;
+    col_key= new Item_string(key_info->name, strlen(key_info->name), cs);
+    int length= longlong2str(key_info->key_length, buff_key_len, 10) - 
+                             buff_key_len;
+    col_key_len= new Item_string(buff_key_len, length, cs);
+    return col_key == NULL || col_key_len == NULL;
+  }
+  else if (select && select->quick)
+  {
+    select->quick->add_keys_and_lengths(&str_key, &str_key_len);
+    col_key= new Item_string(str_key.ptr(), str_key.length(), cs);
+    col_key_len= new Item_string(str_key_len.ptr(), str_key_len.length(), cs);
+    return col_key == NULL || col_key_len == NULL;
+  }
+  return FALSE;
+}
+
+
+bool Explain_table::explain_rows_and_filtered()
+{
+  double examined_rows;
+  if (select && select->quick)
+    examined_rows= rows2double(select->quick->records);
+  else if (!select && !need_sort && limit != HA_POS_ERROR)
+    examined_rows= rows2double(limit);
+  else
+  {
+    table->file->info(HA_STATUS_VARIABLE);
+    examined_rows= rows2double(table->file->stats.records);
+  }
+  col_rows= new Item_int((longlong) (ulonglong) examined_rows,
+                         MY_INT64_NUM_DECIMAL_DIGITS);
+  if (col_rows == NULL)
+    return TRUE;
+
+  if (describe(DESCRIBE_EXTENDED))
+  {
+    col_filtered= new Item_float(100.0, 2);
+    if (col_filtered == NULL)
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
+bool Explain_table::explain_extra()
+{
+  str_extra.length(0);
+
+  uint keyno= (select && select->quick) ? select->quick->index : key;
+
+  if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
+      table->file->pushed_idx_cond)
+    str_extra.append(STRING_WITH_LEN("; Using index condition"));
+
+  int quick_type= (select && select->quick) ? select->quick->get_type() : -1;
+
+  switch (quick_type) {
+  case QUICK_SELECT_I::QS_TYPE_ROR_UNION:
+  case QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT:
+  case QUICK_SELECT_I::QS_TYPE_INDEX_MERGE:
+    str_extra.append(STRING_WITH_LEN("; Using "));
+    select->quick->add_info_string(&str_extra);
+    break;
+  default: ;
+  }
+
+  if (select && select->cond)
+  {
+    const Item *pushed_cond= table->file->pushed_cond;
+
+    if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
+        pushed_cond)
+    {
+      str_extra.append(STRING_WITH_LEN("; Using where with pushed "
+                                       "condition"));
+      if (describe(DESCRIBE_EXTENDED))
+      {
+        str_extra.append(STRING_WITH_LEN(": "));
+        ((Item *)pushed_cond)->print(&str_extra, QT_ORDINARY);
+      }
+    }
+    else
+      str_extra.append(STRING_WITH_LEN("; Using where"));
+  }
+
+  if (table->reginfo.not_exists_optimize)
+    str_extra.append(STRING_WITH_LEN("; Not exists"));
+
+  if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
+      !(((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags &
+       (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED))) 
+  {
+    /*
+      During normal execution of a query, multi_range_read_init() is
+      called to initialize MRR. If HA_MRR_SORTED is set at this point,
+      multi_range_read_init() for any native MRR implementation will
+      revert to default MRR because they cannot produce sorted output
+      currently.
+      Calling multi_range_read_init() can potentially be costly, so it
+      is not done when executing an EXPLAIN. We therefore make the
+      assumption that HA_MRR_SORTED means no MRR. If some MRR native
+      implementation will support sorted output in the future, a
+      function "bool mrr_supports_sorted()" should be added in the
+      handler.
+    */
+    str_extra.append(STRING_WITH_LEN("; Using MRR"));
+  }
+  if (need_sort)
+    str_extra.append(STRING_WITH_LEN("; Using filesort"));
+
+  /* Skip initial "; "*/
+  const char *str= str_extra.ptr();
+  uint32 len= str_extra.length();
+  if (len)
+  {
+    str += 2;
+    len -= 2;
+  }
+  col_extra= new Item_string(str, len, cs);
+  return col_extra == NULL;
+}
+
+
+/**
+  EXPLAIN functionality for insert_select, multi_update and multi_delete
+
+  This class objects substitute insert_select, multi_update and multi_delete
+  data interceptor objects to implement EXPLAIN for INSERT, REPLACE and
+  multi-table UPDATE and DELETE queries.
+  explain_send class object initializes tables like insert_select, multi_update
+  or multi_delete data interceptor do, but it suppress table data modification
+  by the underlying interceptor object.
+  Thus, we can use explain_send object in the context of EXPLAIN INSERT/
+  REPLACE/UPDATE/DELETE query like we use select_send in the context of
+  EXPLAIN SELECT command:
+    1) in presence of lex->describe flag we pass explain_send object to the
+       mysql_select() function,
+    2) it call prepare(), prepare2() and initialize_tables() methods to
+       mark modified tables etc.
+
+*/
+
+class explain_send : public select_send {
+protected:
+  /**
+    Bits for result_state bitmap
+
+    As far as we use explain_send object in a place of select_send, explain_send
+    have to pass multiple invocation of its prepare(), prepare2() and
+    initialize_tables() methods, since JOIN::exec() of subqueries runs
+    these methods of select_send multiple times by design.
+    insert_select, multi_update and multi_delete class methods are not intended
+    for multiple invocations, so result_state bitmap guards data interceptor
+    object from method re-invocation.
+  */
+  enum result_state_enum { 
+    SELECT_RESULT_PREPARED    = 0x01, // 1st bit set: prepare() is complete
+    SELECT_RESULT_PREPARED2   = 0x02, // 2nd bit set: prepare2() is complete
+    SELECT_RESULT_INITIALIZED = 0x04  // 3rd bit set: initialize_tables() done
+  };
+  int result_state; ///< bitmap of result_state_enum bits
+
+  /**
+    Pointer to underlying insert_select, multi_update or multi_delete object
+  */
+  select_result_interceptor *interceptor;
+
+public:
+  explain_send(select_result_interceptor *interceptor_arg) 
+  : result_state(0), interceptor(interceptor_arg)
+  {}
+
+protected:
+  virtual int prepare(List<Item> &list, SELECT_LEX_UNIT *u)
+  {
+    if (result_state & SELECT_RESULT_PREPARED)
+      return FALSE;
+    else
+      result_state|= SELECT_RESULT_PREPARED;
+    return select_send::prepare(list, u) || interceptor->prepare(list, u);
+  }
+
+  virtual int prepare2(void)
+  {
+    if (result_state & SELECT_RESULT_PREPARED2)
+      return FALSE;
+    else
+      result_state|= SELECT_RESULT_PREPARED2;
+    return select_send::prepare2() || interceptor->prepare2();
+  }
+
+  virtual bool initialize_tables(JOIN *join)
+  {
+    if (result_state & SELECT_RESULT_INITIALIZED)
+      return FALSE;
+    else
+      result_state|= SELECT_RESULT_INITIALIZED;
+    return select_send::initialize_tables(join) ||
+           interceptor->initialize_tables(join);
+  }
+
+  virtual void cleanup()
+  {
+    select_send::cleanup();
+    interceptor->cleanup();
+  }
+};
+
+
+/******************************************************************************
+  External function implementations
+******************************************************************************/
+
+
+/**
+  Send a messages as an "extra" column value
+
+  This function forms the 1st row of the QEP output with a simple text message.
+  This is useful to explain such trivial cases as "No tables used" etc.
+  
+  NOTE: Also this function explains the rest of QEP (subqueries or joined
+        tables if any).
+
+  @param message  text message for the "extra" column.
+  @param rows     HA_POS_ERROR or a value for the "rows" column.
+
+  @returns
+    @retval FALSE  OK
+    @retval TRUE   Error
+*/
+
+bool msg_describe(JOIN *join, const char *message)
+{
+  return Explain_msg(join, message).send();
+}
+
+
+/**
+  Send a messages as an "extra" column value
+
+  This function forms the 1st row of the QEP output with a simple text message.
+  This is useful to explain such trivial cases as "No tables used" etc.
+  
+  NOTE: Also this function explains the rest of QEP (subqueries if any).
+
+  @param message  text message for the "extra" column.
+  @param rows     HA_POS_ERROR or a value for the "rows" column.
+
+  @returns
+    @retval FALSE  OK
+    @retval TRUE   Error
+*/
+
+bool msg_describe(const char *message, ha_rows rows)
+{
+  return Explain_msg(message, rows).send();
+}
+
+
+/**
+  EXPLAIN handling for single-table UPDATE and DELETE queries
+
+  Send to the client a QEP data set for single-table EXPLAIN UPDATE/DELETE
+  queries. As far as single-table UPDATE/DELETE are implemented without
+  the regular JOIN tree, we can't reuse mysql_explain_union() directly,
+  thus we deal with this single table in a special way and then call
+  mysql_explain_union() for subqueries (if any).
+
+  @param table      TABLE object to update/delete rows in the UPDATE/DELETE
+                    query.
+  @param select     SQL_SELECT object that represents quick access methods/
+                    WHERE clause.
+  @param key        MAX_KEY or and index number of the key that was chosen to
+                    access table data.
+  @param limit      HA_POS_ERROR or LIMIT value.
+  @param need_sort  TRUE if it requires filesort() -- "Using filesort"
+                    string in the "extra" column.
+
+  @returns
+    @retval FALSE  OK
+    @retval TRUE   Error
+*/
+
+bool table_describe(TABLE *table, SQL_SELECT *select, uint key, ha_rows limit,
+                    bool need_sort)
+{
+  return Explain_table(table, select, key, limit, need_sort).send();
+}
+
+
+/**
+  EXPLAIN handling for EXPLAIN SELECT queries
+
+  Send a description about what how the select will be done to the client
+
+  @param join            JOIN
+  @param need_tmp_table  TRUE if it requires a temporary table --
+                         "Using temporary" string in the "extra" column.
+  @param need_order      TRUE if it requires filesort() -- "Using filesort"
+                         string in the "extra" column.
+  @param distinct        TRUE if there is the DISTINCT clause (not optimized
+                         out) -- "Distinct" string in the "extra" column.
+
+  @returns
+    @retval FALSE  OK
+    @retval TRUE   Error
+*/
+
+bool select_describe(JOIN *join, bool need_tmp_table, bool need_order,
+                     bool distinct)
+{
+  DBUG_ENTER("select_describe");
+  DBUG_PRINT("info", ("Select 0x%lx, type %s",
+		      (ulong)join->select_lex, join->select_lex->type));
+  if (join->select_lex == join->unit->fake_select_lex)
+    DBUG_RETURN(Explain_union(join).send());
+  else
+    DBUG_RETURN(Explain_join(join, need_tmp_table, need_order, distinct).send());
+}
+
+
+/**
+  EXPLAIN handling for INSERT, REPLACE and multi-table UPDATE/DELETE queries
+
+  Send to the client a QEP data set for data-modifying commands those have a
+  regular JOIN tree (INSERT...SELECT, REPLACE...SELECT and multi-table
+  UPDATE and DELETE queries) like mysql_select() does for SELECT queries in
+  the "describe" mode.
+
+  NOTE: See table_describe() for single-table UPDATE/DELETE EXPLAIN handling.
+
+  NOTE: Unlike the mysql_select() function, explain_data_modification()
+        calls abort_result_set() itself in the case of failure (OOM etc.)
+        since explain_data_modification() uses internally created select_result
+        stream.
+
+  @param result  pointer to select_insert, multi_delete or multi_update object:
+                 the function uses it to call result->prepare(),
+                 result->prepare2() and result->initialize_tables() only but
+                 not to modify table data or to send a result to client.
+  @returns
+    @retval FALSE  OK
+    @retval TRUE   Error
+*/
+
+bool explain_data_modification(select_result_interceptor *result)
+{
+  THD *thd= current_thd;
+  explain_send explain(result);
+  bool res= thd->send_explain_fields(&explain) ||
+            mysql_explain_union(thd, &thd->lex->unit, &explain) ||
+            thd->is_error();
+  if (res)
+    explain.abort_result_set();
+  else
+    explain.send_eof();
+  return res;
+}
+

=== added file 'sql/opt_explain.h'
--- a/sql/opt_explain.h	1970-01-01 00:00:00 +0000
+++ b/sql/opt_explain.h	2011-04-01 13:53:57 +0000
@@ -0,0 +1,30 @@
+/* Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software Foundation,
+   51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+
+#ifndef SQL_EXPLAIN_INCLUDED
+#define SQL_EXPLAIN_INCLUDED
+
+#include "sql_select.h"
+
+bool msg_describe(JOIN *join, const char *message);
+bool msg_describe(const char *message, ha_rows rows= HA_POS_ERROR);
+bool table_describe(TABLE *table, SQL_SELECT *select, uint key, ha_rows limit,
+                    bool need_sort);
+bool select_describe(JOIN *join, bool need_tmp_table, bool need_order,
+                     bool distinct);
+bool explain_data_modification(select_result_interceptor *result);
+
+#endif /* SQL_EXPLAIN_INCLUDED */

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2011-03-22 11:44:40 +0000
+++ b/sql/opt_range.h	2011-04-01 13:53:57 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -427,8 +427,6 @@ protected:
   friend uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range);
   friend range_seq_t quick_range_seq_init(void *init_param,
                                           uint n_ranges, uint flags);
-  friend void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
-                              bool distinct,const char *message);
   friend class QUICK_SELECT_DESC;
   friend class QUICK_INDEX_MERGE_SELECT;
   friend class QUICK_ROR_INTERSECT_SELECT;

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2011-03-24 08:00:03 +0000
+++ b/sql/sql_class.h	2011-04-01 13:53:57 +0000
@@ -3111,6 +3111,14 @@ public:
   */
   virtual void cleanup();
   void set_thd(THD *thd_arg) { thd= thd_arg; }
+
+  /*
+    If we execute EXPLAIN SELECT ... LIMIT (or any other EXPLAIN query)
+    we have to ignore LIMIT value sending EXPLAIN output rows since
+    LIMIT value belongs to the underlying query, not to the whole EXPLAIN.
+  */
+  void reset_offset_limit_cnt() { unit->offset_limit_cnt= 0; }
+
 #ifdef EMBEDDED_LIBRARY
   virtual void begin_dataset() {}
 #else

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2011-03-17 17:39:31 +0000
+++ b/sql/sql_delete.cc	2011-04-01 13:53:57 +0000
@@ -35,6 +35,7 @@
 #include "sp_head.h"
 #include "sql_trigger.h"
 #include "transaction.h"
+#include "opt_explain.h"
 #include "records.h"                            // init_read_record,
                                                 // end_read_record
 
@@ -60,6 +61,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   ha_rows	deleted= 0;
   bool          reverse= FALSE;
   bool          skip_record;
+  bool          need_sort= FALSE;
   ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
                            order_list->first : NULL);
   uint usable_index= MAX_KEY;
@@ -144,6 +146,15 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     /* Update the table->file->stats.records number */
     table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
     ha_rows const maybe_deleted= table->file->stats.records;
+
+    if (thd->lex->describe)
+    {
+      bool err= msg_describe("Using delete_all_rows", maybe_deleted);
+      delete select;
+      free_underlaid_joins(thd, select_lex);
+      DBUG_RETURN(err);
+    }
+
     DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
     if (!(error=table->file->ha_delete_all_rows()))
     {
@@ -169,14 +180,31 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     Item::cond_result result;
     conds= remove_eq_conds(thd, conds, &result);
     if (result == Item::COND_FALSE)             // Impossible where
+    {
       limit= 0;
+
+      if (thd->lex->describe)
+      {
+        bool err= msg_describe("Impossible WHERE");
+        delete select;
+        free_underlaid_joins(thd, select_lex);
+        DBUG_RETURN(err);
+      }
+    }
   }
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (prune_partitions(thd, table, conds))
-  {
+  { // No matching records
+    if (thd->lex->describe)
+    {
+      bool err= msg_describe("No matching records");
+      delete select;
+      free_underlaid_joins(thd, select_lex);
+      DBUG_RETURN(err);
+    }
+
     free_underlaid_joins(thd, select_lex);
-    // No matching record
     my_ok(thd, 0);
     DBUG_RETURN(0);
   }
@@ -219,23 +247,34 @@ bool mysql_delete(THD *thd, TABLE_LIST *
       DBUG_RETURN(TRUE);
     }
   }
+
+  if (order)
+  {
+    table->update_const_key_parts(conds);
+    order= simple_remove_const(order, conds);
+
+    usable_index= get_index_for_order(order, table, select, limit,
+                                      &need_sort, &reverse);
+  }
+
+  if (thd->lex->describe)
+  {
+    bool err= table_describe(table, select, usable_index, limit, need_sort);
+    delete select;
+    free_underlaid_joins(thd, select_lex);
+    DBUG_RETURN(err);
+  }
+
   if (options & OPTION_QUICK)
     (void) table->file->extra(HA_EXTRA_QUICK);
 
-  if (order)
+  if (need_sort)
   {
     uint         length= 0;
     SORT_FIELD  *sortorder;
     ha_rows examined_rows;
     ha_rows found_rows;
     
-    table->update_const_key_parts(conds);
-    order= simple_remove_const(order, conds);
-
-    bool need_sort;
-    usable_index= get_index_for_order(order, table, select, limit,
-                                      &need_sort, &reverse);
-    if (need_sort)
     {
       DBUG_ASSERT(usable_index == MAX_KEY);
       table->sort.io_cache= (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
@@ -361,6 +400,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     (void) table->file->extra(HA_EXTRA_NORMAL);
 
 cleanup:
+  DBUG_ASSERT(!thd->lex->describe);
   /*
     Invalidate the table in the query cache if something changed. This must
     be before binlog writing and ha_autocommit_...

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2011-02-15 17:14:15 +0000
+++ b/sql/sql_insert.cc	2011-04-01 13:53:57 +0000
@@ -76,6 +76,7 @@
 #include "transaction.h"
 #include "sql_audit.h"
 #include "debug_sync.h"
+#include "opt_explain.h"
 
 #ifndef EMBEDDED_LIBRARY
 static bool delayed_get_table(THD *thd, MDL_request *grl_protection_request,
@@ -770,6 +771,19 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   /* Restore the current context. */
   ctx_state.restore_state(context, table_list);
 
+  if (thd->lex->describe)
+  {
+    /*
+      Obviously INSERT without the SELECT is not suitable for EXPLAIN, since we
+      don't plan how read tables.
+      So we simply send "No tables used" and stop execution here.
+    */
+
+    bool err= msg_describe("No tables used");
+    free_underlaid_joins(thd, &thd->lex->select_lex);
+    DBUG_RETURN(err);
+  }
+
   /*
     Fill in the given fields and dump it to the table file
   */

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-03-17 17:39:31 +0000
+++ b/sql/sql_parse.cc	2011-04-01 13:53:57 +0000
@@ -96,6 +96,7 @@
 #include "set_var.h"
 #include "mysql/psi/mysql_statement.h"
 #include "sql_bootstrap.h"
+#include "opt_explain.h"
 
 #define FLAGSTR(V,F) ((V)&(F)?#F" ":"")
 
@@ -825,7 +826,7 @@ out:
 
   This is a helper function to mysql_execute_command.
 
-  @note SQLCOM_MULTI_UPDATE is an exception and delt with elsewhere.
+  @note SQLCOM_UPDATE_MULTI is an exception and delt with elsewhere.
 
   @see mysql_execute_command
   @returns Status code
@@ -2910,7 +2911,7 @@ end_with_restore_list:
   case SQLCOM_REPLACE_SELECT:
   case SQLCOM_INSERT_SELECT:
   {
-    select_result *sel_result;
+    select_insert *sel_result;
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if ((res= insert_precheck(thd, all_tables)))
       break;
@@ -2941,21 +2942,26 @@ end_with_restore_list:
                                                  lex->duplicates,
                                                  lex->ignore)))
       {
-	res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE);
-        /*
-          Invalidate the table in the query cache if something changed
-          after unlocking when changes become visible.
-          TODO: this is workaround. right way will be move invalidating in
-          the unlock procedure.
-        */
-        if (!res && first_table->lock_type ==  TL_WRITE_CONCURRENT_INSERT &&
-            thd->lock)
+        if (lex->describe)
+          res= explain_data_modification(sel_result);
+        else
         {
-          /* INSERT ... SELECT should invalidate only the very first table */
-          TABLE_LIST *save_table= first_table->next_local;
-          first_table->next_local= 0;
-          query_cache_invalidate3(thd, first_table, 1);
-          first_table->next_local= save_table;
+          res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE);
+          /*
+            Invalidate the table in the query cache if something changed
+            after unlocking when changes become visible.
+            TODO: this is workaround. right way will be move invalidating in
+            the unlock procedure.
+          */
+          if (!res && first_table->lock_type ==  TL_WRITE_CONCURRENT_INSERT &&
+              thd->lock)
+          {
+            /* INSERT ... SELECT should invalidate only the very first table */
+            TABLE_LIST *save_table= first_table->next_local;
+            first_table->next_local= 0;
+            query_cache_invalidate3(thd, first_table, 1);
+            first_table->next_local= save_table;
+          }
         }
         delete sel_result;
       }
@@ -3019,21 +3025,26 @@ end_with_restore_list:
     if (!thd->is_fatal_error &&
         (del_result= new multi_delete(aux_tables, lex->table_count)))
     {
-      res= mysql_select(thd, &select_lex->ref_pointer_array,
-			select_lex->get_table_list(),
-			select_lex->with_wild,
-			select_lex->item_list,
-			select_lex->where,
-			0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL,
-			(ORDER *)NULL,
-			(select_lex->options | thd->variables.option_bits |
-			SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
-                        OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
-			del_result, unit, select_lex);
-      res|= thd->is_error();
+      if (lex->describe)
+        res= explain_data_modification(del_result);
+      else
+      {
+        res= mysql_select(thd, &select_lex->ref_pointer_array,
+                          select_lex->get_table_list(),
+                          select_lex->with_wild,
+                          select_lex->item_list,
+                          select_lex->where,
+                          0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL,
+                          (ORDER *)NULL,
+                          (select_lex->options | thd->variables.option_bits |
+                          SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
+                          OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
+                          del_result, unit, select_lex);
+        res|= thd->is_error();
+        if (res)
+          del_result->abort_result_set();
+      }
       MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
-      if (res)
-        del_result->abort_result_set();
       delete del_result;
     }
     else

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-03-29 07:30:44 +0000
+++ b/sql/sql_select.cc	2011-04-01 13:53:57 +0000
@@ -48,6 +48,7 @@
 #include <my_bit.h>
 #include <hash.h>
 #include <ft_global.h>
+#include "opt_explain.h"
 
 #define PREV_BITS(type,A)	((type) (((type) 1 << (A)) -1))
 
@@ -250,8 +251,6 @@ static bool setup_sum_funcs(THD *thd, It
 static bool prepare_sum_aggregators(Item_sum **func_ptr, bool need_distinct);
 static bool init_sum_functions(Item_sum **func, Item_sum **end);
 static bool update_sum_func(Item_sum **func);
-void select_describe(JOIN *join, bool need_tmp_table,bool need_order,
-			    bool distinct, const char *message=NullS);
 static Item *remove_additional_cond(Item* conds);
 static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
 static bool replace_subcondition(JOIN *join, Item **tree, 
@@ -2794,8 +2793,8 @@ JOIN::exec()
   if (!tables_list && (tables || !select_lex->with_sum_func))
   {                                           // Only test of functions
     if (select_options & SELECT_DESCRIBE)
-      select_describe(this, FALSE, FALSE, FALSE,
-		      (zero_result_cause?zero_result_cause:"No tables used"));
+      msg_describe(this, zero_result_cause ? zero_result_cause 
+                                           : "No tables used");
     else
     {
       if (result->send_result_set_metadata(*columns_list,
@@ -2889,10 +2888,12 @@ JOIN::exec()
                                     keys_in_use_for_query))))
       order=0;
     having= tmp_having;
-    select_describe(this, need_tmp,
-		    order != 0 && !skip_sort_order,
-		    select_distinct,
-                    !tables ? "No tables used" : NullS);
+    if (tables)
+      select_describe(this, need_tmp,
+                      order != 0 && !skip_sort_order,
+                      select_distinct);
+    else
+      msg_describe(this, "No tables used");
     DBUG_VOID_RETURN;
   }
 
@@ -11938,7 +11939,7 @@ return_zero_rows(JOIN *join, select_resu
 
   if (select_options & SELECT_DESCRIBE)
   {
-    select_describe(join, FALSE, FALSE, FALSE, info);
+    msg_describe(join, info);
     DBUG_RETURN(0);
   }
 
@@ -22763,590 +22764,6 @@ void JOIN::clear()
   }
 }
 
-/**
-  EXPLAIN handling.
-
-  Send a description about what how the select will be done to stdout.
-*/
-
-void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
-                     bool distinct,const char *message)
-{
-  List<Item> field_list;
-  List<Item> item_list;
-  THD *thd=join->thd;
-  select_result *result=join->result;
-  Item *item_null= new Item_null();
-  CHARSET_INFO *cs= system_charset_info;
-  int quick_type;
-  DBUG_ENTER("select_describe");
-  DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
-		      (ulong)join->select_lex, join->select_lex->type,
-		      message ? message : "NULL"));
-  /* Don't log this into the slow query log */
-  thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
-  join->unit->offset_limit_cnt= 0;
-
-  /* 
-    NOTE: the number/types of items pushed into item_list must be in sync with
-    EXPLAIN column types as they're "defined" in THD::send_explain_fields()
-  */
-  if (message)
-  {
-    item_list.push_back(new Item_int((int32)
-				     join->select_lex->select_number));
-    item_list.push_back(new Item_string(join->select_lex->type,
-					strlen(join->select_lex->type), cs));
-    for (uint i=0 ; i < 7; i++)
-      item_list.push_back(item_null);
-    if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
-      item_list.push_back(item_null);
-    if (join->thd->lex->describe & DESCRIBE_EXTENDED)
-      item_list.push_back(item_null);
-  
-    item_list.push_back(new Item_string(message,strlen(message),cs));
-    if (result->send_data(item_list))
-      join->error= 1;
-  }
-  else if (join->select_lex == join->unit->fake_select_lex)
-  {
-    /* 
-      here we assume that the query will return at least two rows, so we
-      show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
-      and no filesort will be actually done, but executing all selects in
-      the UNION to provide precise EXPLAIN information will hardly be
-      appreciated :)
-    */
-    char table_name_buffer[NAME_CHAR_LEN];
-    item_list.empty();
-    /* id */
-    item_list.push_back(new Item_null);
-    /* select_type */
-    item_list.push_back(new Item_string(join->select_lex->type,
-					strlen(join->select_lex->type),
-					cs));
-    /* table */
-    {
-      SELECT_LEX *last_select= join->unit->first_select()->last_select();
-      // # characters needed to print select_number of last select
-      int last_length= (int)log10((double)last_select->select_number)+1;
-
-      SELECT_LEX *sl= join->unit->first_select();
-      uint len= 6, lastop= 0;
-      memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
-      /*
-        - len + lastop: current position in table_name_buffer
-        - 6 + last_length: the number of characters needed to print
-          '...,'<last_select->select_number>'>\0'
-      */
-      for (; 
-           sl && len + lastop + 6 + last_length < NAME_CHAR_LEN; 
-           sl= sl->next_select())
-      {
-        len+= lastop;
-        lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
-                            "%u,", sl->select_number);
-      }
-      if (sl || len + lastop >= NAME_CHAR_LEN)
-      {
-        memcpy(table_name_buffer + len, STRING_WITH_LEN("...,"));
-        len+= 4;
-        lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
-                            "%u,", last_select->select_number);
-      }
-      len+= lastop;
-      table_name_buffer[len - 1]= '>';  // change ',' to '>'
-      item_list.push_back(new Item_string(table_name_buffer, len, cs));
-    }
-    /* partitions */
-    if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
-      item_list.push_back(item_null);
-    /* type */
-    item_list.push_back(new Item_string(join_type_str[JT_ALL],
-					  strlen(join_type_str[JT_ALL]),
-					  cs));
-    /* possible_keys */
-    item_list.push_back(item_null);
-    /* key*/
-    item_list.push_back(item_null);
-    /* key_len */
-    item_list.push_back(item_null);
-    /* ref */
-    item_list.push_back(item_null);
-    /* in_rows */
-    if (join->thd->lex->describe & DESCRIBE_EXTENDED)
-      item_list.push_back(item_null);
-    /* rows */
-    item_list.push_back(item_null);
-    /* extra */
-    if (join->unit->global_parameters->order_list.first)
-      item_list.push_back(new Item_string("Using filesort",
-					  14, cs));
-    else
-      item_list.push_back(new Item_string("", 0, cs));
-
-    if (result->send_data(item_list))
-      join->error= 1;
-  }
-  else
-  {
-    table_map used_tables=0;
-    uint last_sjm_table= MAX_TABLES;
-    for (uint i=0 ; i < join->tables ; i++)
-    {
-      JOIN_TAB *tab=join->join_tab+i;
-      TABLE *table=tab->table;
-      TABLE_LIST *table_list= tab->table->pos_in_table_list;
-      char buff[512]; 
-      char buff1[512], buff2[512], buff3[512];
-      char keylen_str_buf[64];
-      String extra(buff, sizeof(buff),cs);
-      char table_name_buffer[NAME_LEN];
-      String tmp1(buff1,sizeof(buff1),cs);
-      String tmp2(buff2,sizeof(buff2),cs);
-      String tmp3(buff3,sizeof(buff3),cs);
-      extra.length(0);
-      tmp1.length(0);
-      tmp2.length(0);
-      tmp3.length(0);
-
-      quick_type= -1;
-      item_list.empty();
-      /* id */
-      item_list.push_back(new Item_uint((uint32)
-				       join->select_lex->select_number));
-      /* select_type */
-      item_list.push_back(new Item_string(join->select_lex->type,
-                                          strlen(join->select_lex->type),
-                                          cs));
-      if (tab->type == JT_ALL && tab->select && tab->select->quick)
-      {
-        quick_type= tab->select->quick->get_type();
-        if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
-            (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
-            (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
-          tab->type = JT_INDEX_MERGE;
-        else
-	  tab->type = JT_RANGE;
-      }
-      /* table */
-      if (table->derived_select_number)
-      {
-	/* Derived table name generation */
-	int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
-			     "<derived%u>",
-			     table->derived_select_number);
-	item_list.push_back(new Item_string(table_name_buffer, len, cs));
-      }
-      else
-      {
-        TABLE_LIST *real_table= table->pos_in_table_list; 
-	item_list.push_back(new Item_string(real_table->alias,
-					    strlen(real_table->alias),
-					    cs));
-      }
-      /* "partitions" column */
-      if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
-      {
-#ifdef WITH_PARTITION_STORAGE_ENGINE
-        partition_info *part_info;
-        if (!table->derived_select_number && 
-            (part_info= table->part_info))
-        {          
-          Item_string *item_str= new Item_string(cs);
-          make_used_partitions_str(part_info, &item_str->str_value);
-          item_list.push_back(item_str);
-        }
-        else
-          item_list.push_back(item_null);
-#else
-        /* just produce empty column if partitioning is not compiled in */
-        item_list.push_back(item_null); 
-#endif
-      }
-      /* "type" column */
-      item_list.push_back(new Item_string(join_type_str[tab->type],
-					  strlen(join_type_str[tab->type]),
-					  cs));
-      /* Build "possible_keys" value and add it to item_list */
-      if (!tab->keys.is_clear_all())
-      {
-        uint j;
-        for (j=0 ; j < table->s->keys ; j++)
-        {
-          if (tab->keys.is_set(j))
-          {
-            if (tmp1.length())
-              tmp1.append(',');
-            tmp1.append(table->key_info[j].name, 
-			strlen(table->key_info[j].name),
-			system_charset_info);
-          }
-        }
-      }
-      if (tmp1.length())
-	item_list.push_back(new Item_string(tmp1.ptr(),tmp1.length(),cs));
-      else
-	item_list.push_back(item_null);
-
-      /* Build "key", "key_len", and "ref" values and add them to item_list */
-      if (tab->ref.key_parts)
-      {
-	KEY *key_info=table->key_info+ tab->ref.key;
-        register uint length;
-	item_list.push_back(new Item_string(key_info->name,
-					    strlen(key_info->name),
-					    system_charset_info));
-        length= longlong2str(tab->ref.key_length, keylen_str_buf, 10) - 
-                keylen_str_buf;
-        item_list.push_back(new Item_string(keylen_str_buf, length,
-                                            system_charset_info));
-	for (store_key **ref=tab->ref.key_copy ; *ref ; ref++)
-	{
-	  if (tmp2.length())
-	    tmp2.append(',');
-	  tmp2.append((*ref)->name(), strlen((*ref)->name()),
-		      system_charset_info);
-	}
-	item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
-      }
-      else if (tab->type == JT_NEXT)
-      {
-	KEY *key_info=table->key_info+ tab->index;
-        register uint length;
-	item_list.push_back(new Item_string(key_info->name,
-					    strlen(key_info->name),cs));
-        length= longlong2str(key_info->key_length, keylen_str_buf, 10) - 
-                keylen_str_buf;
-        item_list.push_back(new Item_string(keylen_str_buf, 
-                                            length,
-                                            system_charset_info));
-	item_list.push_back(item_null);
-      }
-      else if (tab->select && tab->select->quick)
-      {
-        tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
-	item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
-	item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
-	item_list.push_back(item_null);
-      }
-      else
-      {
-        if (table_list->schema_table &&
-            table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
-        {
-          const char *tmp_buff;
-          int f_idx;
-          if (table_list->has_db_lookup_value)
-          {
-            f_idx= table_list->schema_table->idx_field1;
-            tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
-            tmp2.append(tmp_buff, strlen(tmp_buff), cs);
-          }          
-          if (table_list->has_table_lookup_value)
-          {
-            if (table_list->has_db_lookup_value)
-              tmp2.append(',');
-            f_idx= table_list->schema_table->idx_field2;
-            tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
-            tmp2.append(tmp_buff, strlen(tmp_buff), cs);
-          }
-          if (tmp2.length())
-            item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
-          else
-            item_list.push_back(item_null);
-        }
-        else
-          item_list.push_back(item_null);
-	item_list.push_back(item_null);
-	item_list.push_back(item_null);
-      }
-      
-      /* Add "rows" field to item_list. */
-      if (table_list->schema_table)
-      {
-        /* in_rows */
-        if (join->thd->lex->describe & DESCRIBE_EXTENDED)
-          item_list.push_back(item_null);
-        /* rows */
-        item_list.push_back(item_null);
-      }
-      else
-      {
-        double examined_rows;
-        if (tab->select && tab->select->quick)
-          examined_rows= rows2double(tab->select->quick->records);
-        else if (tab->type == JT_NEXT || tab->type == JT_ALL)
-        {
-          if (tab->limit)
-            examined_rows= rows2double(tab->limit);
-          else
-          {
-            tab->table->file->info(HA_STATUS_VARIABLE);
-            examined_rows= rows2double(tab->table->file->stats.records);
-          }
-        }
-        else
-          examined_rows= join->best_positions[i].records_read; 
- 
-        item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, 
-                                         MY_INT64_NUM_DECIMAL_DIGITS));
-
-        /* Add "filtered" field to item_list. */
-        if (join->thd->lex->describe & DESCRIBE_EXTENDED)
-        {
-          float f= 0.0; 
-          if (examined_rows)
-            f= (float) (100.0 * join->best_positions[i].records_read /
-                        examined_rows);
-          item_list.push_back(new Item_float(f, 2));
-        }
-      }
-
-      /* Build "Extra" field and add it to item_list. */
-      my_bool key_read=table->key_read;
-      if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
-          table->covering_keys.is_set(tab->index))
-	key_read=1;
-      if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
-          !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
-        key_read=1;
-        
-      if (tab->info)
-	item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
-      else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
-      {
-        if (tab->packed_info & TAB_INFO_USING_INDEX)
-          extra.append(STRING_WITH_LEN("; Using index"));
-        if (tab->packed_info & TAB_INFO_USING_WHERE)
-          extra.append(STRING_WITH_LEN("; Using where"));
-        if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
-          extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
-        /* Skip initial "; "*/
-        const char *str= extra.ptr();
-        uint32 len= extra.length();
-        if (len)
-        {
-          str += 2;
-          len -= 2;
-        }
-	item_list.push_back(new Item_string(str, len, cs));
-      }
-      else
-      {
-        uint keyno= MAX_KEY;
-        if (tab->ref.key_parts)
-          keyno= tab->ref.key;
-        else if (tab->select && tab->select->quick)
-          keyno = tab->select->quick->index;
-
-        if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
-             table->file->pushed_idx_cond) || tab->cache_idx_cond)
-          extra.append(STRING_WITH_LEN("; Using index condition"));
-
-        if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || 
-            quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
-            quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
-        {
-          extra.append(STRING_WITH_LEN("; Using "));
-          tab->select->quick->add_info_string(&extra);
-        }
-        if (tab->select)
-	{
-	  if (tab->use_quick == QS_DYNAMIC_RANGE)
-	  {
-            /* 4 bits per 1 hex digit + terminating '\0' */
-            char buf[MAX_KEY / 4 + 1];
-            extra.append(STRING_WITH_LEN("; Range checked for each "
-                                         "record (index map: 0x"));
-            extra.append(tab->keys.print(buf));
-            extra.append(')');
-	  }
-	  else if (tab->select->cond)
-          {
-            const Item *pushed_cond= tab->table->file->pushed_cond;
-
-            if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
-                pushed_cond)
-            {
-              extra.append(STRING_WITH_LEN("; Using where with pushed "
-                                           "condition"));
-              if (thd->lex->describe & DESCRIBE_EXTENDED)
-              {
-                extra.append(STRING_WITH_LEN(": "));
-                ((Item *)pushed_cond)->print(&extra, QT_ORDINARY);
-              }
-            }
-            else
-              extra.append(STRING_WITH_LEN("; Using where"));
-          }
-	}
-        if (table_list->schema_table &&
-            table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
-        {
-          if (!table_list->table_open_method)
-            extra.append(STRING_WITH_LEN("; Skip_open_table"));
-          else if (table_list->table_open_method == OPEN_FRM_ONLY)
-            extra.append(STRING_WITH_LEN("; Open_frm_only"));
-          else
-            extra.append(STRING_WITH_LEN("; Open_full_table"));
-          if (table_list->has_db_lookup_value &&
-              table_list->has_table_lookup_value)
-            extra.append(STRING_WITH_LEN("; Scanned 0 databases"));
-          else if (table_list->has_db_lookup_value ||
-                   table_list->has_table_lookup_value)
-            extra.append(STRING_WITH_LEN("; Scanned 1 database"));
-          else
-            extra.append(STRING_WITH_LEN("; Scanned all databases"));
-        }
-        if (key_read)
-        {
-          if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
-          {
-            QUICK_GROUP_MIN_MAX_SELECT *qgs= 
-              (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
-            extra.append(STRING_WITH_LEN("; Using index for group-by"));
-            qgs->append_loose_scan_type(&extra);
-          }
-          else
-            extra.append(STRING_WITH_LEN("; Using index"));
-        }
-        if (table->reginfo.not_exists_optimize)
-          extra.append(STRING_WITH_LEN("; Not exists"));
-
-        if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
-            !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
-             (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED))) 
-        {
-          /*
-            During normal execution of a query, multi_range_read_init() is
-            called to initialize MRR. If HA_MRR_SORTED is set at this point,
-            multi_range_read_init() for any native MRR implementation will
-            revert to default MRR because they cannot produce sorted output
-            currently.
-            Calling multi_range_read_init() can potentially be costly, so it
-            is not done when executing an EXPLAIN. We therefore make the
-            assumption that HA_MRR_SORTED means no MRR. If some MRR native
-            implementation will support sorted output in the future, a
-            function "bool mrr_supports_sorted()" should be added in the
-            handler.
-          */
-          extra.append(STRING_WITH_LEN("; Using MRR"));
-        }
-        if (need_tmp_table)
-        {
-          need_tmp_table=0;
-          extra.append(STRING_WITH_LEN("; Using temporary"));
-        }
-        if (need_order)
-        {
-          need_order=0;
-          extra.append(STRING_WITH_LEN("; Using filesort"));
-        }
-        if (distinct & test_all_bits(used_tables,thd->used_tables))
-          extra.append(STRING_WITH_LEN("; Distinct"));
-
-        if (tab->loosescan_match_tab)
-        {
-          extra.append(STRING_WITH_LEN("; LooseScan"));
-        }
-
-        if (tab->flush_weedout_table)
-          extra.append(STRING_WITH_LEN("; Start temporary"));
-        if (tab->check_weed_out_table)
-          extra.append(STRING_WITH_LEN("; End temporary"));
-        else if (tab->do_firstmatch)
-        {
-          if (tab->do_firstmatch == join->join_tab - 1)
-            extra.append(STRING_WITH_LEN("; FirstMatch"));
-          else
-          {
-            extra.append(STRING_WITH_LEN("; FirstMatch("));
-            TABLE *prev_table=tab->do_firstmatch->table;
-            if (prev_table->derived_select_number)
-            {
-              char namebuf[NAME_LEN];
-              /* Derived table name generation */
-              int len= my_snprintf(namebuf, sizeof(namebuf)-1,
-                                   "<derived%u>",
-                                   prev_table->derived_select_number);
-              extra.append(namebuf, len);
-            }
-            else
-              extra.append(prev_table->pos_in_table_list->alias);
-            extra.append(STRING_WITH_LEN(")"));
-          }
-        }
-        uint sj_strategy= join->best_positions[i].sj_strategy;
-        if (sj_is_materialize_strategy(sj_strategy))
-        {
-          if (join->best_positions[i].n_sj_tables == 1)
-            extra.append(STRING_WITH_LEN("; Materialize"));
-          else
-          {
-            last_sjm_table= i + join->best_positions[i].n_sj_tables - 1;
-            extra.append(STRING_WITH_LEN("; Start materialize"));
-          }
-          if (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
-              extra.append(STRING_WITH_LEN("; Scan"));
-        }
-        else if (last_sjm_table == i)
-        {
-          extra.append(STRING_WITH_LEN("; End materialize"));
-        }
-
-        for (uint part= 0; part < tab->ref.key_parts; part++)
-        {
-          if (tab->ref.cond_guards[part])
-          {
-            extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
-            break;
-          }
-        }
-
-        if (i > 0 && tab[-1].next_select == sub_select_cache)
-        {
-          extra.append(STRING_WITH_LEN("; Using join buffer ("));
-          if ((tab->use_join_cache & JOIN_CACHE::ALG_BNL))
-            extra.append(STRING_WITH_LEN("BNL"));
-          else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA))
-            extra.append(STRING_WITH_LEN("BKA"));
-          else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA_UNIQUE))
-            extra.append(STRING_WITH_LEN("BKA_UNIQUE"));
-          else
-            DBUG_ASSERT(0);
-          if (tab->use_join_cache & JOIN_CACHE::NON_INCREMENTAL_BUFFER)
-            extra.append(STRING_WITH_LEN(", regular buffers)"));
-          else
-            extra.append(STRING_WITH_LEN(", incremental buffers)"));
-        }
-
-        /* Skip initial "; "*/
-        const char *str= extra.ptr();
-        uint32 len= extra.length();
-        if (len)
-        {
-          str += 2;
-          len -= 2;
-        }
-        item_list.push_back(new Item_string(str, len, cs));
-      }
-      // For next iteration
-      used_tables|=table->map;
-      if (result->send_data(item_list))
-	join->error= 1;
-    }
-  }
-  for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
-       unit;
-       unit= unit->next_unit())
-  {
-    if (mysql_explain_union(thd, unit, result))
-      DBUG_VOID_RETURN;
-  }
-  DBUG_VOID_RETURN;
-}
-
 
 bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
 {

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2011-03-28 08:10:39 +0000
+++ b/sql/sql_show.cc	2011-04-01 13:53:57 +0000
@@ -20,7 +20,7 @@
 #include "sql_priv.h"
 #include "unireg.h"
 #include "sql_acl.h"                        // fill_schema_*_privileges
-#include "sql_select.h"                         // For select_describe
+#include "sql_select.h"
 #include "sql_base.h"                       // close_tables_for_reopen
 #include "sql_show.h"
 #include "sql_table.h"                        // filename_to_tablename,

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2011-02-25 16:41:57 +0000
+++ b/sql/sql_update.cc	2011-04-01 13:53:57 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -38,6 +38,7 @@
 #include "records.h"                            // init_read_record,
                                                 // end_read_record
 #include "filesort.h"                           // filesort
+#include "opt_explain.h"
 #include "sql_derived.h" // mysql_derived_prepare,
                          // mysql_handle_derived,
                          // mysql_derived_filling
@@ -392,7 +393,14 @@ int mysql_update(THD *thd,
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (prune_partitions(thd, table, conds))
-  {
+  { // No matching records
+    if (thd->lex->describe)
+    {
+      bool err= msg_describe("No matching records");
+      free_underlaid_joins(thd, select_lex);
+      DBUG_RETURN(err);
+    }
+
     free_underlaid_joins(thd, select_lex);
     my_ok(thd);				// No matching records
     DBUG_RETURN(0);
@@ -419,6 +427,13 @@ int mysql_update(THD *thd,
     {
       DBUG_RETURN(1);				// Error in where
     }
+    if (thd->lex->describe)
+    {
+      bool err= msg_describe("Impossible WHERE");
+      delete select;
+      free_underlaid_joins(thd, select_lex);
+      DBUG_RETURN(err);
+    }
     my_ok(thd);				// No matching records
     DBUG_RETURN(0);
   }
@@ -450,6 +465,15 @@ int mysql_update(THD *thd,
     used_key_is_modified= is_key_used(table, used_index, table->write_set);
   }
 
+  if (thd->lex->describe)
+  {
+    bool err= table_describe(table, select, used_index, limit, 
+                             order && (need_sort||used_key_is_modified));
+    delete select;
+    free_underlaid_joins(thd, select_lex);
+    DBUG_RETURN(err);
+  }
+
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (used_key_is_modified || order ||
       partition_key_modified(table, table->write_set))
@@ -1338,24 +1362,29 @@ bool mysql_multi_update(THD *thd,
                               (MODE_STRICT_TRANS_TABLES |
                                MODE_STRICT_ALL_TABLES));
 
-  List<Item> total_list;
+  if (thd->lex->describe)
+    res= explain_data_modification(*result);
+  else
+  {
+    List<Item> total_list;
 
-  res= mysql_select(thd, &select_lex->ref_pointer_array,
-                    table_list, select_lex->with_wild,
-                    total_list,
-                    conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
-                    (ORDER *)NULL,
-                    options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
-                    OPTION_SETUP_TABLES_DONE,
-                    *result, unit, select_lex);
-
-  DBUG_PRINT("info",("res: %d  report_error: %d", res, (int) thd->is_error()));
-  res|= thd->is_error();
-  if (unlikely(res))
-  {
-    /* If we had a another error reported earlier then this will be ignored */
-    (*result)->send_error(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR));
-    (*result)->abort_result_set();
+    res= mysql_select(thd, &select_lex->ref_pointer_array,
+                      table_list, select_lex->with_wild,
+                      total_list,
+                      conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
+                      (ORDER *)NULL,
+                      options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
+                      OPTION_SETUP_TABLES_DONE,
+                      *result, unit, select_lex);
+
+    DBUG_PRINT("info",("res: %d  report_error: %d",res, (int) thd->is_error()));
+    res|= thd->is_error();
+    if (unlikely(res))
+    {
+      /* If we had a another error reported earlier then this will be ignored */
+      (*result)->send_error(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR));
+      (*result)->abort_result_set();
+    }
   }
   thd->abort_on_warning= 0;
   DBUG_RETURN(res);

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2011-03-09 20:54:55 +0000
+++ b/sql/sql_yacc.yy	2011-04-01 13:53:57 +0000
@@ -11376,6 +11376,34 @@ describe:
             LEX *lex=Lex;
             lex->select_lex.options|= SELECT_DESCRIBE;
           }
+        | describe_command opt_extended_describe
+          { Lex->describe|= DESCRIBE_NORMAL; }
+          insert 
+          {
+            LEX *lex=Lex;
+            lex->select_lex.options|= SELECT_DESCRIBE;
+          }
+        | describe_command opt_extended_describe
+          { Lex->describe|= DESCRIBE_NORMAL; }
+          replace 
+          {
+            LEX *lex=Lex;
+            lex->select_lex.options|= SELECT_DESCRIBE;
+          }
+        | describe_command opt_extended_describe
+          { Lex->describe|= DESCRIBE_NORMAL; }
+          update 
+          {
+            LEX *lex=Lex;
+            lex->select_lex.options|= SELECT_DESCRIBE;
+          }
+        | describe_command opt_extended_describe
+          { Lex->describe|= DESCRIBE_NORMAL; }
+          delete 
+          {
+            LEX *lex=Lex;
+            lex->select_lex.options|= SELECT_DESCRIBE;
+          }
         ;
 
 describe_command:


Attachment: [text/bzr-bundle] bzr/gleb.shchepa@oracle.com-20110401135357-zy52d4oplvkki5ya.bundle
Thread
bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa1 Apr
  • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Guilhem Bichot28 Apr
    • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa25 May
      • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Guilhem Bichot27 May
  • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Guilhem Bichot9 May
    • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa25 May
      • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Guilhem Bichot27 May
        • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa2 Jun
          • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Guilhem Bichot9 Jun
            • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa9 Jun
    • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Roy Lyseng27 May
      • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa2 Jun
  • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Roy Lyseng27 May
    • Re: bzr commit into mysql-trunk branch (gleb.shchepa:3358) WL#4897Gleb Shchepa2 Jun