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
• Status: Closed
• 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)
• CPU Architecture: Any
• Tags: WBBugReporter

[7 Apr 2016 8:22] David Markovitz

Description:
"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.

Thanks,
Umesh

[11 Dec 2019 22:48] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.18