Bug #79830 IN operator works incorrectly with NULL

• Submitted: 4 Jan 2016 9:42
• Modified: 4 Jan 2016 12:04
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.7.8, 5.5.48, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[4 Jan 2016 9:42] Su Dylan

Description:
Output:
=======
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1(c1 TIME);
INSERT INTO t1 VALUQuery OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('10:22:33');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c1, c1 IN ('10:22:33'), c1 IN ('10:22:33', NULL) FROM t1;
+----------+--------------------+--------------------------+
| c1       | c1 IN ('10:22:33') | c1 IN ('10:22:33', NULL) |
+----------+--------------------+--------------------------+
| 10:22:33 |                  1 |                     NULL |
+----------+--------------------+--------------------------+
1 row in set (0.00 sec)

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

Problem:
========
c1 IN ('10:22:33', NULL)  should return true, not NULL.

How to repeat:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 TIME);
INSERT INTO t1 VALUES('10:22:33');
SELECT c1, c1 IN ('10:22:33'), c1 IN ('10:22:33', NULL) FROM t1;

Suggested fix:
c1 IN ('10:22:33', NULL)  should return true, not NULL.

[4 Jan 2016 12:04] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed this with 5.5.48,5.6.28,5.7.10/11.

Thanks,
Umesh