Bug #80990 "... not in (select null from ...)" should return no rows
Submitted: 7 Apr 2016 8:22 Modified: 11 Dec 2019 22:48
Reporter: David Markovitz Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.11, 5.6.29, 5.5 OS:Windows (Microsoft Windows 7 Enterprise Service Pack 1)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[7 Apr 2016 8:22] David Markovitz
"where i not in (select i...)" should return no rows when there are nulls in the sub-query.
This common use case works as designed.

I've created an uncommon use-case, for education purposes only, where all the returned sub-query values are hard-coded null.
"where i not in (select null ...)"
This use case returns rows, which is a bug.

How to repeat:
create table t1 (i int);
create table t2 (i int);
insert into t1(i) values (1),(5);
insert into t2(i) values (1),(null),(2),(3);

select * from t1 where i not in (select i from t2 where i is not null);
/* returns 5 which is true */

select * from t1 where i not in (select i from t2);
/* returns no rows which is true because there is a null value in t2 */

select * from t1 where i not in (select null from t2);
/* returns 5 although it should have returned no rows */
[7 Apr 2016 9:09] MySQL Verification Team
Hello David,

Thank you for the report and test case.
Observed that 5.5 daily build, 5.6.29 and 5.7.11 are affected.

[11 Dec 2019 22:48] Roy Lyseng
Posted by developer:
Fixed in 8.0.18