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:
None 
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
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)
[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.