| 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 | |
[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.
Description: Attempting to compare an indexed ENUM to a 0+string constant expression in a WHERE clause, while correctly casting the expression to an integer, does not appear to (correctly?) use the ENUM's index. (This may not be specific to ENUM, but this was the context in which I stumbled upon this bug many years ago. And yes, the whole thing was basically a poor man's attempt to recreate the CAST() operator.) How to repeat: CREATE TABLE t ( flavor ENUM('vanilla', 'chocolate') NOT NULL, number INT UNSIGNED NOT NULL, INDEX (flavor, number) ); INSERT INTO t VALUES ('vanilla', 1), ('chocolate', 2); EXPLAIN SELECT * FROM t WHERE flavor = 1\G [...] key_len: 1 ref: const rows: 1 EXPLAIN SELECT * FROM t WHERE flavor = CAST('1' AS UNSIGNED)\G [...] key_len: 1 ref: const rows: 1 EXPLAIN SELECT * FROM t WHERE flavor = 0+'1'\G [...] key_len: 5 ref: NULL rows: 2 This can be further illustrated by filling the table with thousands of rows, and running a costly subquery: # Insert 10000 rows with random values perl -le 'print "insert into t values ", join "," => map { sprintf "(%d,%d)" => int(rand 2)+1, int(rand 1000000) } 1..10000' | mysql test SELECT SQL_NO_CACHE COUNT(*) FROM t AS t1 WHERE EXISTS (SELECT * FROM t AS t2 WHERE t2.flavor = 1 and t2.number = t1.number); [...] 1 row in set (0.02 sec) SELECT SQL_NO_CACHE COUNT(*) FROM t AS t1 WHERE EXISTS (SELECT * FROM t AS t2 WHERE t2.flavor = 0+'1' and t2.number = t1.number); [...] 1 row in set (22.31 sec)