Bug #78958 NULL=ANY( subquery ) returns 0

• Submitted: 26 Oct 2015 2:37
• Modified: 27 Nov 2019 22:13
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.6.27, 5.7.10
• OS: Any
• CPU Architecture: Any

[26 Oct 2015 2:37] Su Dylan

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 int primary key);
insert into t1 values(1);
select NQuery OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
ct c1Query OK, 1 row affected (0.00 sec)

mysql> select NULL=ANY(select c1 from t1);
+-----------------------------+
| NULL=ANY(select c1 from t1) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

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

Problem:
========
NULL=ANY( subquery ) should return NULL.

How to repeat:

drop table if exists t1;
create table t1(c1 int primary key);
insert into t1 values(1);
select NULL=ANY(select c1 from t1);

Suggested fix:
NULL=ANY( subquery ) returns NULL.

[26 Oct 2015 8:44] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed this with 5.6.27 and 5.7.10 builds.

Thanks,
Umesh

[30 Oct 2015 9:57] Pavel Katiushyn

Other strange behaviour:

mysql> create table t1(c1 int,c2 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1,2),(2,null),(2,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    2 | NULL |
|    2 |    3 |
+------+------+
3 rows in set (0.00 sec)

When I query with "IN" I get correct result despite of NULL value:
mysql> select * from t1 where c1 in (select c2 from t1);
+------+------+
| c1   | c2   |
+------+------+
|    2 | NULL |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

But when I use "NOT IN" I get completely empty result:
mysql> select * from t1 where c1 not in (select c2 from t1);
Empty set (0.00 sec)

[27 Nov 2019 22:13] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.29