Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
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 |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
Attachments
Issue Links
- relates to
-
MDEV-8787 Return Impossible WHERE instead of Full table scan on some admittedly false field=const expressions
-
- Open
-