Bug #97941 not in (null) returns wrong result
Submitted: 10 Dec 2019 11:19 Modified: 10 Dec 2019 14:19
Reporter: Huanting Liu Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.29/5.7.25 OS:Any
Assigned to: CPU Architecture:Any

[10 Dec 2019 11:19] Huanting Liu
Description:
select * from t1 where a not exists( select null from t2) returns wrong result

How to repeat:
root@localhost:tmp 5.7.25-log 06:42:19> create table t1(a int,b int);
Query OK, 0 rows affected (0.00 sec)

root@localhost:tmp 5.7.25-log 06:42:25> create table t2(a int,b int);
Query OK, 0 rows affected (0.01 sec)

root@localhost:tmp 5.7.25-log 06:42:27> insert into t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost:tmp 5.7.25-log 06:42:46> insert into t2 values(1,1),(2,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost:tmp 5.7.25-log 06:42:49> select * from t1 where a not in (select null from t2);
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

root@localhost:tmp 5.7.25-log 06:42:55> explain select * from t1 where a not in (select null from t2);
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `tmp`.`t1`.`a` AS `a`,`tmp`.`t1`.`b` AS `b` from `tmp`.`t1` where (not(<in_optimizer>(`tmp`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `tmp`.`t2` where <if>(outer_field_is1`.`a`) = NULL) or <cache>(isnull(NULL))), true) having <if>(outer_field_is_not_null, <cache>(<is_not_null_test>(NULL)), true)))))
[10 Dec 2019 14:19] MySQL Verification Team
Hi Mr. Liu,

Thank you for your bug.

However, I can't repeat it. With latest versions of our server I get an empty result. Empty result is a correct results, because latest versions of our server respect latest SQL standards on the topic.

Can't repeat.