The equality predicate produces notes about unusable keys as expected:
CREATE OR REPLACE TABLE t1 (
|
c1 varchar(10),
|
KEY(c1)
|
) CHARACTER SET latin1;
|
INSERT INTO t1 VALUES ('a');
|
INSERT INTO t1 VALUES ('b');
|
INSERT INTO t1 VALUES ('c');
|
INSERT INTO t1 VALUES ('d');
|
INSERT INTO t1 VALUES ('e');
|
INSERT INTO t1 VALUES ('f');
|
INSERT INTO t1 VALUES ('g');
|
INSERT INTO t1 VALUES ('h');
|
INSERT INTO t1 VALUES ('i');
|
INSERT INTO t1 VALUES ('j');
|
SET note_verbosity=all;
|
EXPLAIN SELECT * FROM t1 WHERE c1=10;
|
SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------+
|
| Note | 1105 | Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` |
|
+-------+------+-------------------------------------------------------------------------------------------------+
|
But these predicates do not produce notes:
EXPLAIN SELECT * FROM t1 WHERE c1<10;
|
|
EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;
|
|
EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');
|
MDEV-32203 Raise notes when an index cannot be used on data type mismatch
MDEV-32957 Unusable key notes report wrong predicates for > and >=
