|
create table t1 (a date);
|
insert into t1 values ('2021-12-12'),('2012-12-21');
|
create table t2 (b int);
|
insert into t2 values (null),(null); # Optional, fails either way
|
|
# returns NULL as expected
|
select max(b) from t2;
|
# returns empty result as expected
|
select a from t1 where a not in (null);
|
# returns a result set, unexpectedly
|
select a from t1 where a not in (select max(b) from t2);
|
|
drop table t1, t2;
|
|
10.2 85defc47
|
select max(b) from t2;
|
max(b)
|
NULL
|
select a from t1 where a not in (null);
|
a
|
select a from t1 where a not in (select max(b) from t2);
|
a
|
2021-12-12
|
2012-12-21
|
|
plan
|
explain extended select a from t1 where a not in (select max(b) from t2);
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select max(`test`.`t2`.`b`) from `test`.`t2` having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(max(`test`.`t2`.`b`))))))
|
Was also reproducible on 10.1, but not on 10.0.
Not reproducible on MySQL 5.7.
|