summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergey Petrunya <psergey@askmonty.org>2013-10-09 17:20:42 +0400
committerSergey Petrunya <psergey@askmonty.org>2013-10-09 17:20:42 +0400
commit582ecb2a253ed2c9e26a2e61102da4bf4ede72d3 (patch)
treebc51bf4a9ba7d1a6e681d14a5d65fd4565b4d043
parent7e919c52a52d372d68b930b1cd5b763364264629 (diff)
downloadmariadb-git-582ecb2a253ed2c9e26a2e61102da4bf4ede72d3.tar.gz
MDEV-3798: EXPLAIN UPDATE/DELETE:
Backport mysql-test/t/myisam_explain_non_select_all.test from mysql-5.6 - the .result file was modified because MariaDB choses different query plans in a number cases. Also, we don't have some of the "incorrect EXPLAIN output" bugs that they still have. The .test file and includes were taken verbatim with one exception: two tests were disabled with --disable parsing: 1. @@sql_safe_updates is not enforced EXPLAINs of multitable updates. In MariaDB, the execution itself will produce ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE, but EXPLAIN won't. 2. Their case #71 hits some old bug in MyISAM (See their comments in explain_non_select.inc for details).
-rw-r--r--mysql-test/include/explain_non_select.inc833
-rw-r--r--mysql-test/include/explain_utils.inc161
-rw-r--r--mysql-test/r/myisam_explain_non_select_all.result2903
-rw-r--r--mysql-test/t/myisam_explain_non_select_all.test21
4 files changed, 3918 insertions, 0 deletions
diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc
new file mode 100644
index 00000000000..a0f86e744b0
--- /dev/null
+++ b/mysql-test/include/explain_non_select.inc
@@ -0,0 +1,833 @@
+# This file is a collection of regression and coverage tests
+# for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE.
+
+-- disable_query_log
+-- disable_result_log
+# SET GLOBAL innodb_stats_persistent=0;
+-- enable_result_log
+-- enable_query_log
+
+# set end_markers_in_json=on;
+
+--echo #1
+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_utils.inc
+DROP TABLE t1;
+
+--echo #2
+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_utils.inc
+DROP TABLE t1;
+
+--echo #3
+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_utils.inc
+DROP TABLE t1;
+
+--echo #4
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #5
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #6
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #7
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #7
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #8
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #9
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #10
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #11
+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_utils.inc
+DROP TABLE t1;
+
+--echo #12
+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_utils.inc
+DROP TABLE t1;
+
+--echo #13
+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_utils.inc
+DROP TABLE t1;
+
+--echo #14
+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_utils.inc
+DROP TABLE t1;
+
+--echo #15
+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_utils.inc
+DROP TABLE t1;
+
+--echo #16
+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_utils.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_utils.inc
+DROP TABLE t1;
+
+--echo #17
+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_utils.inc
+DROP TABLE t1;
+
+--echo #18
+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_utils.inc
+DROP TABLE t1;
+
+--echo #19
+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_utils.inc
+DROP TABLE t1, t2, t3;
+
+--echo #20
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #21
+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;
+--disable_query_log
+if (`select locate('semijoin', @@optimizer_switch) > 0`)
+{
+ SET @@optimizer_switch= 'semijoin=off';
+}
+--enable_query_log
+--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_utils.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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #22
+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_utils.inc
+DROP TABLE t1;
+
+--echo #23
+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_utils.inc
+DROP TABLE t1;
+
+--echo #24
+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
+--let $no_rows = 1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+
+--echo #25
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #26
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #27
+CREATE TABLE t1 (i INT);
+--let $query = INSERT INTO t1 SET i = 10
+--source include/explain_utils.inc
+DROP TABLE t1;
+
+--echo #28
+CREATE TABLE t1 (i INT);
+--let $query = REPLACE INTO t1 SET i = 10
+--source include/explain_utils.inc
+DROP TABLE t1;
+
+--echo #29
+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_utils.inc
+DROP TABLE t1;
+
+--echo #30
+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_utils.inc
+DROP TABLE t1;
+
+--echo #31
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #32
+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
+--let $no_rows = 1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+
+--echo #33
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #34
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #35
+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),(36),(37),(38),(39),
+ (40),(41),(42);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #36
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #37
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #38
+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
+--let $no_rows = 1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+
+--echo #39
+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
+--let $no_rows = 1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+
+--echo #40
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #41
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #42
+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
+--let $no_rows = 1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+
+--echo #43
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #44
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #45
+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),(36),(37),(38),(39),
+ (40),(41),(42);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #46
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #47
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #48
+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
+--let $no_rows = 1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+
+--echo #49
+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)
+);
+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_utils.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_utils.inc
+DROP TABLE t1;
+
+--echo #50
+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_utils.inc
+DROP TABLE t1;
+
+--echo #51
+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_utils.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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #52
+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_utils.inc
+DROP TABLE t1, t2;
+
+--echo #55
+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;
+
+--echo #56
+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;
+
+--echo #57
+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;
+
+--disable_parsing
+--echo #59
+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;
+--enable_parsing
+
+--echo #62
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0), (1);
+CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
+--let $query = UPDATE v1 SET a = 1 WHERE a > 0
+--let $select = SELECT * FROM v1 WHERE a > 0
+--source include/explain_utils.inc
+--let $query = UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a
+--let $select = SELECT * FROM t1, v1 WHERE t1.a = v1.a
+--source include/explain_utils.inc
+DROP TABLE t1;
+DROP VIEW v1;
+
+--echo #63
+CREATE TABLE t1 (a INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 (a) AS SELECT a FROM t1;
+--let $query = DELETE FROM v1 WHERE a < 4
+--let $select = SELECT * FROM v1 WHERE a < 4
+--source include/explain_utils.inc
+DROP TABLE t1;
+DROP VIEW v1;
+
+--echo #64
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
+CREATE TABLE t2 (x INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4);
+CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
+--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
+--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a
+--source include/explain_utils.inc
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+--echo #65
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
+CREATE TABLE t2 (x INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4);
+CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
+--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
+--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a
+--source include/explain_utils.inc
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+--echo #66
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 (x) AS SELECT a FROM t1;
+--let $query = INSERT INTO v1 VALUES (10)
+--let $select = SELECT NULL
+--source include/explain_utils.inc
+DROP TABLE t1;
+DROP VIEW v1;
+
+--echo #67
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE VIEW v1 (x) AS SELECT b FROM t2;
+--let $query = INSERT INTO v1 SELECT * FROM t1
+--let $select = SELECT * FROM t1
+--source include/explain_utils.inc
+DROP TABLE t1, t2;
+DROP VIEW v1;
+
+--echo #68
+CREATE TABLE t1 (i INT);
+EXPLAIN INSERT DELAYED INTO t1 VALUES (1);
+DROP TABLE t1;
+
+--echo #69
+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 * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+--let $select = SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+--source include/explain_utils.inc
+--let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+--let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+--source include/explain_utils.inc
+--let $query = UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+--let $select = SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+--source include/explain_utils.inc
+DROP TABLE t1,t2;
+
+--echo #70
+CREATE TABLE t1 (c1 INT KEY);
+CREATE TABLE t2 (c2 INT);
+CREATE TABLE t3 (c3 INT);
+EXPLAIN EXTENDED UPDATE t3 SET c3 = (
+ SELECT COUNT(d1.c1)
+ FROM (
+ SELECT a11.c1 FROM t1 AS a11
+ STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1
+ JOIN t1 AS a12 ON a12.c1 = a11.c1
+ ) d1
+);
+
+DROP TABLE t1, t2, t3;
+
+--disable_parsing
+--echo #71
+#
+# Bug: after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT
+# to a # MyISAM table the SELECT query may fail with the
+# "1030: Got error 124 from storage engine" error message.
+#
+CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX i1(c1));
+INSERT INTO t1 VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0);
+--disable_query_log
+let $1=7;
+SET @d=8;
+while ($1) {
+ eval INSERT INTO t1 SELECT c1 + @d, c2 + @d FROM t1;
+ eval SET @d = @d*2;
+ dec $1;
+}
+--enable_query_log
+CREATE TABLE t2 LIKE t1;
+
+# replace "rows" column for InnoDB
+--replace_column 9 X
+EXPLAIN INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 SELECT * FROM t1;
+--disable_result_log
+SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
+--enable_result_log
+
+DROP TABLE t1, t2;
+--enable_parsing
+
+--echo #73
+
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+
+DROP TABLE t1,t2;
+
+--echo #74
+
+CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+--echo # used key is modified & Using temporary
+
+--let $query = UPDATE t1 SET a=a+1 WHERE a>10
+--let $select = SELECT a t1 FROM t1 WHERE a>10
+--source include/explain_utils.inc
+
+--echo # used key is modified & Using filesort
+
+--let $query = UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20
+--let $select = SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20
+--source include/explain_utils.inc
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH
+--echo # EXPLAIN UPDATE/DEL/INS
+--echo #
+
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+
+--delimiter |
+CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END|
+CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END|
+CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END|
+CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END|
+CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END|
+CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END|
+CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END|
+CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END|
+CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END|
+CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END|
+CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
+CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
+CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END|
+CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END|
+CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END|
+CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END|
+--delimiter ;
+
+let $i=16;
+while($i) {
+ eval CALL p$i();
+ eval DROP PROCEDURE p$i;
+ dec $i;
+}
+
+DROP TABLE t1, t2;
+
+--echo #
+
+-- disable_query_log
+-- disable_result_log
+# SET GLOBAL innodb_stats_persistent=default;
+-- enable_result_log
+-- enable_query_log
diff --git a/mysql-test/include/explain_utils.inc b/mysql-test/include/explain_utils.inc
new file mode 100644
index 00000000000..505798e432a
--- /dev/null
+++ b/mysql-test/include/explain_utils.inc
@@ -0,0 +1,161 @@
+# This file is a collection of utility tests
+# for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE.
+#
+# Since MTR doesn't have functions, we use this file instead
+# including it many times.
+#
+# Parameters:
+#
+# $query: INSERT/REPLACE/UPDATE/DELETE query to explain
+# NOTE: this file resets this variable
+#
+# $select: may be empty; the SELECT query similar to $query
+# We use it to compare:
+# 1) table data before and after EXPLAIN $query evaluation;
+# 2) EXPLAIN $query and EXPLAIN $select output and
+# handler/filesort statistics
+# NOTE: this file resets this variable
+# $innodb: take $no_rows parameter into account if not 0;
+# $no_rows: filter out "rows" and "filtered" columns of EXPLAIN if not 0;
+# it may be necessary for InnoDB tables since InnoDB's table row
+# counter can't return precise and repeatable values;
+# NOTE: ANALYZE TABLE doesn't help
+# NOTE: this file resets this variable
+
+--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
+}
+
+if ($innodb) {
+ if ($no_rows) {
+--replace_column 9 X
+ }
+}
+--eval EXPLAIN $query
+if (`SELECT ROW_COUNT() > 0`) {
+--echo # Erroneous query: EXPLAIN $query
+--die Unexpected ROW_COUNT() <> 0
+}
+
+FLUSH STATUS;
+FLUSH TABLES;
+if ($innodb) {
+ if ($no_rows) {
+--replace_column 9 X 10 X
+ }
+}
+--eval EXPLAIN EXTENDED $query
+if (`SELECT ROW_COUNT() > 0`) {
+--echo # Erroneous query: EXPLAIN EXTENDED $query
+--die Unexpected ROW_COUNT() <> 0
+}
+--echo # Status of EXPLAIN EXTENDED query
+--disable_query_log
+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;
+--enable_query_log
+
+if ($json) {
+if ($innodb) {
+ if ($no_rows) {
+--replace_regex /"rows": [0-9]+/"rows": "X"/ /"filtered": [0-9.]+/"filtered": "X"/
+ }
+}
+--eval EXPLAIN FORMAT=JSON $query;
+if ($validation) {
+--disable_query_log
+--replace_result $MASTER_MYSOCK MASTER_MYSOCK
+--exec $MYSQL -S $MASTER_MYSOCK -u root -r test -e "EXPLAIN FORMAT=JSON $query;" > $MYSQLTEST_VARDIR/tmp/explain.json
+--replace_regex /[-]*// /FILE.[\/\\:_\.0-9A-Za-z]*/Validation:/
+--exec python $MYSQL_TEST_DIR/suite/opt_trace/validate_json.py $MYSQLTEST_VARDIR/tmp/explain.json
+--remove_file '$MYSQLTEST_VARDIR/tmp/explain.json'
+--enable_query_log
+}
+}
+
+if ($select) {
+FLUSH STATUS;
+FLUSH TABLES;
+if ($innodb) {
+ if ($no_rows) {
+--replace_column 9 X 10 X
+ }
+}
+--eval EXPLAIN EXTENDED $select
+--echo # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+--disable_query_log
+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;
+--enable_query_log
+if ($json) {
+if ($innodb) {
+ if ($no_rows) {
+--replace_regex /"rows": [0-9]+/"rows": "X"/ /"filtered": [0-9.]+/"filtered": "X"/
+ }
+}
+--eval EXPLAIN FORMAT=JSON $select;
+if ($validation) {
+--disable_query_log
+--replace_result $MASTER_MYSOCK MASTER_MYSOCK
+--exec $MYSQL -S $MASTER_MYSOCK -u root -r test -e "EXPLAIN FORMAT=JSON $select;" > $MYSQLTEST_VARDIR/tmp/explain.json
+--replace_regex /[-]*// /FILE.[\/\\:_\.0-9A-Za-z]*/Validation:/
+--exec python $MYSQL_TEST_DIR/suite/opt_trace/validate_json.py $MYSQLTEST_VARDIR/tmp/explain.json
+--remove_file '$MYSQLTEST_VARDIR/tmp/explain.json'
+--enable_query_log
+}
+}
+}
+
+--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_%' OR
+ Variable_name = 'Handler_write' OR
+ Variable_name = 'Handler_update' OR
+ Variable_name = 'Handler_delete') 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=
+--let $no_rows=
+
+--enable_query_log
+
+--echo
diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result
new file mode 100644
index 00000000000..167e9129957
--- /dev/null
+++ b/mysql-test/r/myisam_explain_non_select_all.result
@@ -0,0 +1,2903 @@
+set @save_storage_engine= @@session.default_storage_engine;
+set session default_storage_engine = MyISAM;
+#1
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a < 10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#2
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE a < 10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#3
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#4
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 (flat, BNL join)
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#5
+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
+2 DERIVED t2 ALL NULL NULL NULL NULL 3
+FLUSH STATUS;
+FLUSH TABLES;
+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
+2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` where (`test`.`t11`.`a` = 1)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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 12
+Handler_update 1
+
+DROP TABLE t1, t2;
+#6
+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
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+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
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+Handler_read_rnd_next 1
+FLUSH STATUS;
+FLUSH TABLES;
+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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`b` < 3)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 1
+Handler_read_rnd_next 8
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd_next 5
+Handler_update 3
+
+DROP TABLE t1, t2;
+#7
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 (flat, BNL join)
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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 2
+
+DROP TABLE t1, t2;
+#7
+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
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+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
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+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` < 3))
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 12
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 16
+Handler_update 2
+
+DROP TABLE t1, t2;
+#8
+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
+2 DERIVED t2 ALL NULL NULL NULL NULL 3
+FLUSH STATUS;
+FLUSH TABLES;
+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
+2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2`
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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 3
+Handler_read_rnd_deleted 1
+Handler_read_rnd_next 24
+Handler_update 3
+
+DROP TABLE t1, t2;
+#9
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+Handler_read_rnd_next 1
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+Handler_read_rnd_next 1
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_rnd_next 5
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd 3
+Handler_read_rnd_next 9
+Handler_update 3
+
+DROP TABLE t1, t2;
+#10
+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
+2 DERIVED t2 ALL NULL NULL NULL NULL 3
+FLUSH STATUS;
+FLUSH TABLES;
+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
+2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` where (`test`.`t11`.`a` > 1)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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 16
+Handler_update 2
+
+DROP TABLE t1, t2;
+#11
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE a > 1 LIMIT 1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#12
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+# Status of testing query execution:
+Variable_name Value
+
+DROP TABLE t1;
+#13
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+# Status of testing query execution:
+Variable_name Value
+
+DROP TABLE t1;
+#14
+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 a 5 NULL 1 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE a = 3;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range a a 5 NULL 1 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+Handler_read_key 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;
+#15
+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 a 5 NULL 1 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE a < 3;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range a a 5 NULL 1 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#16
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+Handler_read_rnd_next 1
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#17
+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 index NULL PRIMARY 4 NULL 1 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 100.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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#18
+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 NULL NULL NULL NULL 10 Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#19
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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`.`t2`.`a2` = `test`.`t1`.`a1`) and (`test`.`t3`.`a3` = `test`.`t2`.`b2`) and (`test`.`t3`.`b3` = `test`.`t1`.`b1`))
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#20
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where 1
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 3
+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;
+#21
+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;
+#
+# 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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t2`.`a2` > 2) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`)))))
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 5
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 (flat, BNL join)
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#22
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET i = 10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#23
+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 Deleting all rows
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL 5 NULL Deleting all rows
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#24
+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 index NULL a 15 NULL 5 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 100.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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#25
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED INSERT INTO t2 SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#26
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED REPLACE INTO t2 SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#27
+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 INSERT t1 ALL NULL NULL NULL NULL NULL NULL
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED INSERT INTO t1 SET i = 10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+# Status of testing query execution:
+Variable_name Value
+Handler_write 1
+
+DROP TABLE t1;
+#28
+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 INSERT t1 ALL NULL NULL NULL NULL NULL NULL
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED REPLACE INTO t1 SET i = 10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+# Status of testing query execution:
+Variable_name Value
+Handler_write 1
+
+DROP TABLE t1;
+#29
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#30
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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:
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#31
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#32
+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 index NULL a 15 NULL 5 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 100.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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#33
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#34
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#35
+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),(36),(37),(38),(39),
+(40),(41),(42);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#36
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 where
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#37
+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 NULL NULL NULL NULL 26 Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a, b DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#38
+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 index NULL a 6 NULL 5
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index NULL a 6 NULL 5 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 100.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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#39
+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; Using buffer
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using buffer
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#40
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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:
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#41
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#42
+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 index NULL a 15 NULL 5 Using where; Using buffer
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where; Using buffer
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 100.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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#43
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#44
+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 NULL NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#45
+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),(36),(37),(38),(39),
+(40),(41),(42);
+CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
+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
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#46
+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; Using buffer
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using buffer
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 where
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#47
+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 NULL NULL NULL NULL 26 Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#48
+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 index NULL a 6 NULL 5 Using buffer
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Using buffer
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 100.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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#49
+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)
+);
+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 c1_idx c1_idx 2 NULL 2 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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; Using filesort
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 1
+Handler_read_next 2
+Sort_range 1
+Sort_rows 2
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 1
+Handler_read_next 2
+Handler_read_rnd 2
+Handler_update 2
+Sort_range 1
+Sort_rows 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 c1_idx c1_idx 2 NULL 2 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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; Using filesort
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 1
+Handler_read_next 2
+Sort_range 1
+Sort_rows 2
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 2
+Handler_read_key 1
+Handler_read_next 2
+Handler_read_rnd 2
+Sort_range 1
+Sort_rows 2
+
+DROP TABLE t1;
+#50
+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; Using buffer
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET a=a+10 WHERE a > 34;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using buffer
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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;
+#51
+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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+Handler_read_rnd_next 1
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+Handler_read_rnd_next 1
+# 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
+
+#
+# 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
+FLUSH STATUS;
+FLUSH TABLES;
+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
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+Handler_read_rnd_next 1
+FLUSH STATUS;
+FLUSH TABLES;
+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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+Handler_read_rnd_next 1
+# 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
+
+DROP TABLE t1, t2;
+#52
+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 ALL IDX NULL NULL NULL 2 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+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 ALL IDX NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+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 ALL IDX NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+Note 1003 select <expr_cache><`test`.`t1`.`f1`>((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 EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 2
+Handler_read_rnd_next 9
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd_next 9
+Handler_update 2
+
+DROP TABLE t1, t2;
+#55
+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;
+#56
+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;
+#57
+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;
+#62
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0), (1);
+CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
+#
+# query: UPDATE v1 SET a = 1 WHERE a > 0
+# select: SELECT * FROM v1 WHERE a > 0
+#
+EXPLAIN UPDATE v1 SET a = 1 WHERE a > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE v1 SET a = 1 WHERE a > 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM v1 WHERE a > 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t12`.`a` AS `b` from `test`.`t1` `t11` join `test`.`t1` `t12` where (`test`.`t11`.`a` > 0)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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 1
+Handler_read_rnd_deleted 1
+Handler_read_rnd_next 8
+
+#
+# query: UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a
+# select: SELECT * FROM t1, v1 WHERE t1.a = v1.a
+#
+EXPLAIN UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1, v1 WHERE t1.a = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t11`.`a` AS `a`,`test`.`t12`.`a` AS `b` from `test`.`t1` join `test`.`t1` `t11` join `test`.`t1` `t12` where (`test`.`t11`.`a` = `test`.`t1`.`a`)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_rnd_next 9
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd 2
+Handler_read_rnd_deleted 1
+Handler_read_rnd_next 18
+Handler_update 1
+
+DROP TABLE t1;
+DROP VIEW v1;
+#63
+CREATE TABLE t1 (a INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 (a) AS SELECT a FROM t1;
+#
+# query: DELETE FROM v1 WHERE a < 4
+# select: SELECT * FROM v1 WHERE a < 4
+#
+EXPLAIN DELETE FROM v1 WHERE a < 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM v1 WHERE a < 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM v1 WHERE a < 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 4)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# 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_first 1
+Handler_read_next 3
+
+DROP TABLE t1;
+DROP VIEW v1;
+#64
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
+CREATE TABLE t2 (x INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4);
+CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
+#
+# query: DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
+# select: SELECT * FROM t2, v1 WHERE t2.x = v1.a
+#
+EXPLAIN DELETE v1 FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
+Warnings:
+Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 4
+Handler_read_rnd_next 5
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 4
+Handler_read_key 4
+Handler_read_rnd 4
+Handler_read_rnd_next 5
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+#65
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10);
+CREATE TABLE t2 (x INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4);
+CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1;
+#
+# query: DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
+# select: SELECT * FROM t2, v1 WHERE t2.x = v1.a
+#
+EXPLAIN DELETE v1 FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
+Warnings:
+Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 4
+Handler_read_rnd_next 5
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 4
+Handler_read_key 4
+Handler_read_rnd 4
+Handler_read_rnd_next 5
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+#66
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 (x) AS SELECT a FROM t1;
+#
+# query: INSERT INTO v1 VALUES (10)
+# select: SELECT NULL
+#
+EXPLAIN INSERT INTO v1 VALUES (10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED INSERT INTO v1 VALUES (10);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT NULL;
+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
+Warnings:
+Note 1003 select NULL AS `NULL`
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+# Status of testing query execution:
+Variable_name Value
+Handler_write 1
+
+DROP TABLE t1;
+DROP VIEW v1;
+#67
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE VIEW v1 (x) AS SELECT b FROM t2;
+#
+# query: INSERT INTO v1 SELECT * FROM t1
+# select: SELECT * FROM t1
+#
+EXPLAIN INSERT INTO v1 SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED INSERT INTO v1 SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+Handler_read_rnd_next 1
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
+Warnings:
+Note 1003 select NULL AS `a` from `test`.`t1`
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+Handler_read_rnd_next 1
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_rnd_next 1
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd_next 1
+
+DROP TABLE t1, t2;
+DROP VIEW v1;
+#68
+CREATE TABLE t1 (i INT);
+EXPLAIN INSERT DELAYED INTO t1 VALUES (1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL
+DROP TABLE t1;
+#69
+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 * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+# select: SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+#
+EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+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 <derived3> index_subquery key0 key0 5 func 2
+3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 100.00
+3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00
+3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where 1
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 10
+Sort_rows 3
+Sort_scan 1
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 8
+Handler_update 1
+Sort_rows 3
+Sort_scan 1
+
+#
+# query: UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+# select: SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+#
+EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3
+3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
+2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00
+3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00
+3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 10
+Sort_rows 3
+Sort_scan 1
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 10
+Sort_rows 3
+Sort_scan 1
+
+#
+# query: UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+# select: SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x)
+#
+EXPLAIN UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
+3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3
+4 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort
+2 DERIVED t2 ALL NULL NULL NULL NULL 3
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+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 <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
+3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00
+4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
+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 <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
+3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00
+4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 10
+Sort_rows 3
+Sort_scan 1
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 3
+Handler_read_rnd_next 10
+Sort_rows 3
+Sort_scan 1
+
+DROP TABLE t1,t2;
+#70
+CREATE TABLE t1 (c1 INT KEY);
+CREATE TABLE t2 (c2 INT);
+CREATE TABLE t3 (c3 INT);
+EXPLAIN EXTENDED UPDATE t3 SET c3 = (
+SELECT COUNT(d1.c1)
+FROM (
+SELECT a11.c1 FROM t1 AS a11
+STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1
+JOIN t1 AS a12 ON a12.c1 = a11.c1
+) d1
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE t1, t2, t3;
+#73
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+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; Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+DROP TABLE t1,t2;
+#74
+CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
+Warnings:
+Warning 1286 Unknown storage engine 'InnoDB'
+Warning 1266 Using storage engine MyISAM for table 't1'
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+# used key is modified & Using temporary
+#
+# query: UPDATE t1 SET a=a+1 WHERE a>10
+# select: SELECT a t1 FROM t1 WHERE a>10
+#
+EXPLAIN UPDATE t1 SET a=a+1 WHERE a>10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using buffer
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using buffer
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 20.00 Using where; Using index
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `t1` from `test`.`t1` where (`test`.`t1`.`a` > 10)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_first 1
+Handler_read_next 5
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 1
+
+# used key is modified & Using filesort
+#
+# query: UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20
+# select: SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20
+#
+EXPLAIN UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using filesort
+# Status of EXPLAIN EXTENDED query
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 20.00 Using where; Using index; Using filesort
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `t1` from `test`.`t1` where (`test`.`t1`.`a` > 10) order by (`test`.`t1`.`a` + 20)
+# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_rnd_next 6
+Sort_scan 1
+# Status of testing query execution:
+Variable_name Value
+Handler_read_key 1
+Sort_range 1
+
+DROP TABLE t1;
+#
+# Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH
+# EXPLAIN UPDATE/DEL/INS
+#
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END|
+CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END|
+CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END|
+CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END|
+CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END|
+CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END|
+CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END|
+CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END|
+CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END|
+CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END|
+CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
+CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END|
+CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END|
+CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END|
+CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END|
+CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END|
+CALL p16();
+DROP PROCEDURE p16;
+CALL p15();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
+DROP PROCEDURE p15;
+CALL p14();
+DROP PROCEDURE p14;
+CALL p13();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL 0 Deleting all rows
+DROP PROCEDURE p13;
+CALL p12();
+DROP PROCEDURE p12;
+CALL p11();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP PROCEDURE p11;
+CALL p10();
+DROP PROCEDURE p10;
+CALL p9();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 0
+DROP PROCEDURE p9;
+CALL p8();
+DROP PROCEDURE p8;
+CALL p7();
+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
+DROP PROCEDURE p7;
+CALL p6();
+DROP PROCEDURE p6;
+CALL p5();
+id select_type table type possible_keys key key_len ref rows Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL
+DROP PROCEDURE p5;
+CALL p4();
+DROP PROCEDURE p4;
+CALL p3();
+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
+DROP PROCEDURE p3;
+CALL p2();
+DROP PROCEDURE p2;
+CALL p1();
+id select_type table type possible_keys key key_len ref rows Extra
+1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+#
+set default_storage_engine= @save_storage_engine;
+set optimizer_switch=default;
diff --git a/mysql-test/t/myisam_explain_non_select_all.test b/mysql-test/t/myisam_explain_non_select_all.test
new file mode 100644
index 00000000000..ccd4ebd6297
--- /dev/null
+++ b/mysql-test/t/myisam_explain_non_select_all.test
@@ -0,0 +1,21 @@
+#
+# Run explain_non_select.inc on MyISAM with all of the so-called 6.0 features.
+#
+
+#--source include/have_semijoin.inc
+#--source include/have_materialization.inc
+#--source include/have_firstmatch.inc
+#--source include/have_loosescan.inc
+#--source include/have_index_condition_pushdown.inc
+#--source include/have_mrr.inc
+
+#set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
+
+set @save_storage_engine= @@session.default_storage_engine;
+set session default_storage_engine = MyISAM;
+--let $json = 0
+--let $validation = 0
+--source include/explain_non_select.inc
+set default_storage_engine= @save_storage_engine;
+
+set optimizer_switch=default;