Wrong result upon query with aggregate function returning NULL in subquery

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;

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

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