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