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 | 81 |
1 files changed, 75 insertions, 6 deletions
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index cc42c16d..e28c2a0f 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -1506,7 +1506,7 @@ 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 Warnings: -Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` FLUSH STATUS; FLUSH TABLES; @@ -1514,7 +1514,7 @@ 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 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` 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 @@ -1526,7 +1526,9 @@ 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 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` 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 @@ -1551,6 +1553,71 @@ Sort_rows 8 Sort_scan 1 DROP TABLE t1; +#30a +# +# MDEV-32957 Unusable key notes report wrong predicates for > and >= +# +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 +# +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +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 +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` +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 +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` +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 +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 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` +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 +Handler_read_key 4 +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 4 +Handler_read_rnd_next 27 +Sort_priority_queue_sorts 1 +Sort_rows 5 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_key 4 +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), @@ -2057,7 +2124,7 @@ 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 Warnings: -Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` FLUSH STATUS; FLUSH TABLES; @@ -2065,7 +2132,7 @@ EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 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 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` 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 @@ -2077,7 +2144,9 @@ EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 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 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` 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 |