Bug #103331 Not in clause get wrong result
Submitted: 15 Apr 2021 11:41 Modified: 20 May 2021 14:47
Reporter: Shenghui Wu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.22, 8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[15 Apr 2021 11:41] Shenghui Wu 
Description:
Not in clause get wrong result

How to repeat:
drop table t;
create table t(a int);
insert into t values(1),(2),(3),(4);
 SELECT  ( 2, 2 ) NOT IN 
 ( select 5,5 union select 7,7 union select null,null  )  as field1
 from t;

+--------+
| field1 |
+--------+
|   NULL |
|      1 |
|      1 |
|      1 |
+--------+
[15 Apr 2021 12:08] MySQL Verification Team 
Hello Shenghui Wu,

Thank you for the report and test case.

regards,
Umesh
[20 May 2021 14:47] Jon Stephens 
Documented fix as follows in the MySQL 8.0.26 changelog:

    Some NOT IN subqueries did not return correct results due to a
    regression in NULL handling.

Closed.