Bug #81995 col(const) IN (-1.49) returns true in WHERE condition with index on col
Submitted: 24 Jun 2016 6:51 Modified: 24 Jun 2016 8:15
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.8, 5.6.31, 5.7.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[24 Jun 2016 6:51] Su Dylan
Description:
Output:
===
mysql> create table bug(c int);
insert into bug values(-Query OK, 0 rows affected (0.00 sec)

mysql> insert into bug values(-1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c, c IN (-1.49) FROM bug WHERE (c IN (-1.49));
Empty set (0.00 sec)

mysql> create index i1 on bug(c);
LECT c, c IN (-1.49) FROM bug WHERE (c IN (-1.49));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT c, c IN (-1.49) FROM bug WHERE (c IN (-1.49));
+------+--------------+
| c    | c IN (-1.49) |
+------+--------------+
|   -1 |            0 |
+------+--------------+
1 row in set (0.00 sec)

mysql>
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.8-rc-log |
+--------------+
1 row in set (0.01 sec)

Problem:
===
"Empty set" is expected for both SELECT statements.

How to repeat:

drop table bug;
create table bug(c int);
insert into bug values(-1);
SELECT c, c IN (-1.49) FROM bug WHERE (c IN (-1.49));
create index i1 on bug(c);
SELECT c, c IN (-1.49) FROM bug WHERE (c IN (-1.49));

Suggested fix:
"Empty set" is returned for both SELECT statements.
[24 Jun 2016 8:15] Umesh Shastry
Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.31/5.7.13 are affected.

Thanks,
Umesh
[27 Jun 2016 9:13] Shane Bester
seems a bit similar to:
https://bugs.mysql.com/bug.php?id=45680
(wrong results when using index for lookup with implicitly casted values)