Details
Minor Description
CREATE TABLE t1 (f enum('foo','bar',''), KEY (f)); |
INSERT IGNORE INTO t1 VALUES ('foo'),('qux'); |
|
|
CREATE TABLE t2 (f enum('foo','bar','')); |
INSERT IGNORE INTO t2 VALUES ('foo'),('qux'); |
|
|
SELECT MIN(f) FROM t1 WHERE f = 'x'; |
SELECT MIN(f) FROM t2 WHERE f = 'x'; |
|
|
# Cleanup
|
DROP TABLE t1, t2; |
The difference between the tables is that t1 has a key while t2 doesn't; otherwise the queries and data are identical.
Note that the inserted data is out of the ENUM range, hence INSERT IGNORE.
The values end up looking like empty strings.
For the table with a key, the query returns an empty string.
For the table without a key, the query returns NULL.
One of them must be wrong. The first one seems to be more likely to be wrong.
|
10.2 96de6bfd |
EXPLAIN EXTENDED SELECT MIN(f) FROM t1 WHERE f = 'x'; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away |
Warnings:
|
Note 1003 select min(`test`.`t1`.`f`) AS `MIN(f)` from `test`.`t1` where multiple equal('x', `test`.`t1`.`f`) |
SELECT MIN(f) FROM t1 WHERE f = 'x'; |
MIN(f) |
|
XPLAIN EXTENDED SELECT MIN(f) FROM t2 WHERE f = 'x'; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where |
Warnings:
|
Note 1003 select min(`test`.`t2`.`f`) AS `MIN(f)` from `test`.`t2` where `test`.`t2`.`f` = 'x' |
SELECT MIN(f) FROM t2 WHERE f = 'x'; |
MIN(f) |
NULL |
Reproducible on all current 10.2+, with at least MyISAM, InnoDB, Aria.
Also reproducible on MySQL 5.7, 8.0.