|
Unusable key notes report wrong predicates for > and >=
SET note_verbosity=unusable_keys;
|
CREATE OR REPLACE TABLE t1 (a INT, i CHAR(2), KEY(i));
|
DELIMITER $$
|
FOR i IN 1..31
|
DO
|
INSERT INTO t1 VALUES (a, 10+i);
|
END FOR;
|
$$
|
DELIMITER ;
|
EXPLAIN SELECT * FROM t1 WHERE i>30 ORDER BY i LIMIT 5;
|
SHOW WARNINGS;
|
+-------+------+---------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------+
|
| Note | 1105 | Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "30" of type `int` |
|
+-------+------+---------------------------------------------------------------------------------------------+
|
Notice, when the > operator is used, it says >= in the note.
And the other way around:
EXPLAIN SELECT * FROM t1 WHERE i>=30 ORDER BY i LIMIT 5;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------+
|
| Note | 1105 | Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "30" of type `int` |
|
+-------+------+--------------------------------------------------------------------------------------------+
|
When the >= operator is used, it says > in the note.
|