Bug #81995 col(const) IN (-1.49) returns true in WHERE condition with index on col

• Submitted: 24 Jun 2016 6:51
• Modified: 24 Dec 2019 13:39
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.7.8, 5.6.31, 5.7.13
• OS: Any
• 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] MySQL Verification Team

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] MySQL Verification Team

seems a bit similar to:
https://bugs.mysql.com/bug.php?id=45680
(wrong results when using index for lookup with implicitly casted values)

[24 Dec 2019 13:39] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.18