diff options
Diffstat (limited to 'mysql-test/main/myisam_explain_non_select_all.result')
| -rw-r--r-- | mysql-test/main/myisam_explain_non_select_all.result | 193 |
1 files changed, 193 insertions, 0 deletions
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 28107af3770..84b8d9ae4c5 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -15,6 +15,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 @@ -54,6 +56,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` using dual where `test`.`t1`.`a` < 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -93,6 +97,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 @@ -136,6 +142,8 @@ EXPLAIN EXTENDED UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t1`.`a` = 10 where `test`.`t1`.`a` = 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -182,6 +190,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 /* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = 10 where `test`.`t11`.`a` = 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -226,6 +236,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 t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 update `test`.`t1` semi join (`test`.`t2`) set `test`.`t1`.`a` = 10 where `test`.`t2`.`b` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -273,6 +285,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 update `test`.`t1` semi join (`test`.`t2`) set `test`.`t1`.`a` = 10 where `test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -325,6 +338,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 4 100.00 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 1004 100.00 Using where +Warnings: +Note 1003 update `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` set `test`.`t1`.`a` = 10 where `test`.`t3`.`b` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 @@ -373,6 +388,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 /* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = `test`.`t11`.`a` + 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -421,6 +438,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ update `test`.`t1` `t11` set `test`.`t11`.`a` = `test`.`t11`.`a` + 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -471,6 +490,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 /* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = 10 where `test`.`t11`.`a` > 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -511,6 +532,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` using dual where `test`.`t1`.`a` > 1 limit 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -550,6 +573,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` using dual where 0 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -586,6 +611,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 @@ -622,6 +649,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` using dual where `test`.`t1`.`a` = 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 5 @@ -659,6 +688,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` using dual where `test`.`t1`.`a` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 5 @@ -694,6 +725,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` using dual where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -729,6 +762,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` using dual where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -768,6 +803,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` using dual where @a:=`test`.`t1`.`a` order by `test`.`t1`.`a` limit 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -808,6 +845,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` using dual order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 @@ -861,6 +900,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 +Warnings: +Note 1003 delete from `test`.`t1`,`test`.`t2`,`test`.`t3` using `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a2` = `test`.`t1`.`a1` and `test`.`t3`.`a3` = `test`.`t2`.`b2` and `test`.`t3`.`b3` = `test`.`t1`.`b1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 13 @@ -909,6 +950,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 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t1) +Warnings: +Note 1003 update `test`.`t1` semi join (`test`.`t2`) set `test`.`t1`.`a` = 10 where `test`.`t2`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -954,6 +997,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` using dual 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 @@ -995,6 +1040,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 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 20.00 Using where; FirstMatch(t1) +Warnings: +Note 1003 delete from `test`.`t1` using (`test`.`t2`) where `test`.`t2`.`a2` > 2 and `test`.`t1`.`a1` = `test`.`t2`.`a2` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -1035,6 +1082,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 @@ -1074,6 +1123,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` using dual # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -1116,6 +1167,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` using dual 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 @@ -1158,6 +1211,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 @@ -1198,6 +1253,8 @@ FLUSH TABLES; EXPLAIN EXTENDED REPLACE INTO t2 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 replace into `test`.`t2` select `test`.`t1`.`i` AS `i` from `test`.`t1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -1236,6 +1293,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 @@ -1259,6 +1318,8 @@ FLUSH TABLES; EXPLAIN EXTENDED REPLACE INTO t1 SET i = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +Warnings: +Note 1003 replace into `test`.`t1`(i) values (10) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -1285,6 +1346,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` using dual 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 @@ -1326,6 +1389,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` using dual 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 @@ -1375,6 +1440,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` using dual 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 @@ -1421,6 +1488,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` using dual 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 @@ -1466,6 +1535,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` using dual 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 @@ -1516,6 +1587,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` using dual 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 @@ -1567,6 +1640,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` using dual 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 @@ -1616,6 +1691,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` using dual 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 @@ -1659,6 +1736,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` using dual order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 @@ -1709,6 +1788,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` using dual order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 @@ -1754,6 +1835,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 @@ -1798,6 +1881,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 @@ -1848,6 +1933,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 @@ -1895,6 +1982,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 @@ -1941,6 +2030,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 @@ -1991,6 +2082,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 @@ -2042,6 +2135,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 @@ -2091,6 +2186,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 @@ -2135,6 +2232,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 @@ -2186,6 +2285,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 @@ -2234,6 +2335,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 @@ -2276,6 +2379,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` using dual 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 @@ -2321,6 +2426,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 @@ -2364,6 +2471,8 @@ EXPLAIN EXTENDED UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 update `test`.`t1` set NULL = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 @@ -2403,6 +2512,8 @@ EXPLAIN EXTENDED UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 W id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 update `test`.`t1` set NULL = 10 where `test`.`t1`.`c3` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 @@ -2451,6 +2562,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref IDX IDX 5 test.t1.f1 1 100.00 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 @@ -2520,6 +2632,8 @@ EXPLAIN EXTENDED UPDATE v1 SET a = 1 WHERE a > 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 update `test`.`t1` `t11` join `test`.`t1` `t12` set `test`.`t11`.`a` = 1 where `test`.`t11`.`a` > 0 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -2561,6 +2675,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 update `test`.`t1` join `test`.`t1` `t11` join `test`.`t1` `t12` set `test`.`t11`.`a` = 1 where `test`.`t11`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 @@ -2606,6 +2722,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` using dual where `test`.`t1`.`a` < 4 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -2653,6 +2771,8 @@ EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 +Warnings: +Note 1003 delete from `test`.`t1` using `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 @@ -2700,6 +2820,8 @@ EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 +Warnings: +Note 1003 delete from `test`.`t1` using `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 @@ -2742,6 +2864,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 @@ -2780,6 +2904,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 @@ -2833,6 +2959,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 1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 /* select#1 */ update (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` set `test`.`t1`.`a` = 10 where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -2882,6 +3010,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 1 PRIMARY t2 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 (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` join `test`.`t2` set `test`.`t1`.`a` = 10 where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -2933,6 +3063,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> 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 (/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` set `test`.`t1`.`a` = 10 where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 @@ -2979,6 +3111,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 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +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); @@ -3005,6 +3139,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 @@ -3038,6 +3174,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 @@ -3061,6 +3199,61 @@ Handler_read_key 4 Sort_range 1 DROP TABLE t1; +#75 +CREATE TABLE t1 (id INT PRIMARY KEY, i INT); +# +# query: INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +# select: +# +EXPLAIN INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +Warnings: +Note 1003 insert into `test`.`t1` values (3,10),(7,11),(3,11) on duplicate key update `test`.`t1`.`id` = last_insert_id(`test`.`t1`.`id`) +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_key 4 +# Status of testing query execution: +Variable_name Value +Handler_read_key 4 +Handler_read_rnd 1 +Handler_write 3 + +DROP TABLE t1; +#76 +CREATE TABLE t1 (id INT PRIMARY KEY, i INT); +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t2 VALUES (1,10), (3,10), (7,11), (3,11); +# +# query: INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +# select: +# +EXPLAIN INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 insert into `test`.`t1` select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` on duplicate key update `test`.`t1`.`id` = last_insert_id(`test`.`t1`.`id`) +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_key 7 +# Status of testing query execution: +Variable_name Value +Handler_read_key 7 +Handler_read_rnd 1 +Handler_read_rnd_next 5 +Handler_write 4 + +DROP TABLE t1,t2; # # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH # EXPLAIN UPDATE/DEL/INS |