|
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a ENUM('a','b','c','1'),KEY(a));
|
INSERT INTO t1 VALUES ('a'),('b'),('c'),('1');
|
EXPLAIN SELECT * FROM t1 WHERE a=100.1e0;
|
returns
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
Looks correct. The constant 100.1e0 will never return any rows from the column t1.a.
However, if I test more impossible constants of various data types:
EXPLAIN SELECT * FROM t1 WHERE a=100;
|
EXPLAIN SELECT * FROM t1 WHERE a=100.1;
|
EXPLAIN SELECT * FROM t1 WHERE a='xx';
|
EXPLAIN SELECT * FROM t1 WHERE a='99999999';
|
EXPLAIN SELECT * FROM t1 WHERE a='100';
|
EXPLAIN SELECT * FROM t1 WHERE a='1x';
|
EXPLAIN SELECT * FROM t1 WHERE a='1.0';
|
EXPLAIN SELECT * FROM t1 WHERE a='1.1';
|
it does not detect Impossible WHERE and is going to do range search:
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | t1 | ref | a | a | 2 | const | 1 | Using where; Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
|