danger about mysql's IN and NOT IN

Question

I have read the documents about mysql's IN and NULL. Look at here: Subqueries with ANY, IN, or SOME , Subqueries with ALL

I did some experiments.

create table(MySQL 5.6)

CREATE TABLE `test` (
  `uid` bigint(20) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO  test(uid) values (1),(2),(3),(4),(5);

statement1.1:

select 2 union all (select null);
+------+
|      |
+------+
|    2 |
| NULL |
+------+

statement1.2:

select uid from `test` where uid not in (select 2 union all (select null));  
+------+
|      |
+------+
|      |
+------+

statement1.3:

select uid from `test` where 1 not in (select 2 union all (select null));  
+------+
|      |
+------+
|      |
+------+

So far, everything has been done as planned. But now we modify the statement, confusing things happen.

statement2.1:

select 2 union all (select null from test);
+------+
|      |
+------+
|   2  |
+------+
| NULL |
+------+
| NULL |
+------+
| NULL |
+------+
| NULL |
+------+
| NULL |
+------+

statement2.2:

select uid from `test` where uid not in (select 2 union all (select null from test));
+------+
|      |
+------+
|  3   |
+------+
|  4   |
+------+
|  5   |
+------+

statement2.3:

select uid from `test` where 1 not in (select 2 union all (select null from test)); 
+------+
|      |
+------+
|      |
+------+

statement2.4:

select uid from `test` where uid not in (select 3 union all (select null from test));
+------+
|      |
+------+
|  2   |
+------+
|  4   |
+------+
|  5   |
+------+

statement2.5:

select uid from `test` where 1 not in (select 3 union all (select null from test)); 
+------+
|      |
+------+
|      |
+------+

someone can explain the statement2.2, 2.4? The result is inconsistent when we use expression or constant as the operand? And why 1 don't appeared in 2.2 and 2.4's result? It seems that 2.3 and 2.5 should be right as the mysql's document described.

Answer 1

NOT IN with subquery that returns NULL should always return 0 rows.

Related: NOT IN clause and NULL values.

---

It looks like a bug and it's not reproducible on MySQL 8.0.

Query:

select uid from test where uid not in (select 2 union all (select null));
select uid from test where 1 not in (select 2 union all (select null));
select uid from test where uid not in(select 2 union all (select null from test));
select uid from test where uid not in(select 3 union all (select null from test));
select uid from test where 1 not in (select 3 union all (select null from test));
-- 0 rows selected 

DBFiddle Demo