Bug #64769 | comparing indexed ENUM with 0+string constant does not use index properly | ||
---|---|---|---|
Submitted: | 26 Mar 2012 22:27 | Modified: | 27 Mar 2012 6:41 |
Reporter: | Frederic Briere | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.1.61 | OS: | Any (Debian unstable) |
Assigned to: | CPU Architecture: | Any |
[26 Mar 2012 22:27]
Frederic Briere
[27 Mar 2012 6:41]
Valeriy Kravchuk
Thank you for the problem report. This is easy to verify: mysql> select version(); +--------------+ | version() | +--------------+ | 5.1.61-debug | +--------------+ 1 row in set (0.17 sec) mysql> CREATE TABLE t ( -> flavor ENUM('vanilla', 'chocolate') NOT NULL, -> number INT UNSIGNED NOT NULL, -> INDEX (flavor, number) -> ); Query OK, 0 rows affected (0.47 sec) mysql> INSERT INTO t VALUES ('vanilla', 1), ('chocolate', 2); Query OK, 2 rows affected (0.18 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> EXPLAIN extended SELECT * FROM t WHERE flavor = 0+'1'; +----+-------------+-------+-------+---------------+--------+---------+------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+--------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | index | flavor | flavor | 5 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+-------+-------+---------------+--------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.08 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t`.`flavor` AS `flavor`,`test`.`t`.`number` AS `number` from `test`.`t` where (`test`.`t`.`flavor` = (0 + '1')) 1 row in set (0.01 sec) mysql> EXPLAIN extended SELECT * FROM t WHERE flavor = 1; +----+-------------+-------+------+---------------+--------+---------+-------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t | ref | flavor | flavor | 1 | const | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t`.`flavor` AS `flavor`,`test`.`t`.`number` AS `number` from `test`.`t` where (`test`.`t`.`flavor` = 1) 1 row in set (0.00 sec) So, looks like optimizer (wrongly?) assumes that this (0 + '1') expression should be evaluated for every row.