Details
Major Description
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.
Attachments
Issue Links
- relates to
-
MDEV-32203 Raise notes when an index cannot be used on data type mismatch
-
- Closed
-
-
MDEV-32958 Unusable key notes do not get reported for some operations
-
- Closed
-