Description:
CREATE TABLE `tlist` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY LIST (`a`)
(PARTITION p0 VALUES IN (0,1,2) ENGINE = InnoDB,
PARTITION p1 VALUES IN (3,4,5) ENGINE = InnoDB,
PARTITION p2 VALUES IN (6,7,8) ENGINE = InnoDB,
PARTITION p3 VALUES IN (9,10,11) ENGINE = InnoDB,
PARTITION p4 VALUES IN (NULL,-1) ENGINE = InnoDB);
mysql> explain select * from tlist where a not in (0, 1, 2, 3, 4, 5, 6, 7, 8, NULL);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tlist | p3,p4 | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Because not in expression contains `NULL` value, so I think the partitions should be NULL here.
If I insert some values into table and use sql above, the results are always NULL.
insert into tlist values (9, 1),(-1, 2);
mysql> select * from tlist where a not in (0, 1, 2, 3, 4, 5, 6, 7, 8, NULL);
Empty set (0.00 sec)
How to repeat:
Follow description
