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)))))