Description:
In MySQL 8.0.27, When we query NULL value of DateTime on partition table(partition key is datatime), the parttion table's pruning is very confusing.
In MySQL 8.0.27, we execute following SQL statements:
mysql> explain select count(*) from test where endtime is null;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | p0 | index | PRIMARY | PRIMARY | 13 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain update test set startTime=date_add(now(), interval 3 second) where (endTime is null) ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | UPDATE | test | p0 | index | NULL | PRIMARY | 13 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Although I cannot set NULL on field endtime, but I have to read all records on partition p0. However, if I add more conditions:
mysql> explain select count(*) from test where (endtime is null or endtime > '2021-08-14');
+----+-------------+-------+--------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | p210815,pmax | index | PRIMARY | PRIMARY | 13 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+--------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
The query plan really confused me, because now mysql pruned partition p0;
In mysql 5.7, everytings is as expected.
mysql> explain select count(*) from test where endtime is null;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | PRIMARY | 13 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from test where (endtime is null or endtime > '2021-08-14');
+----+-------------+-------+--------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | p210815,pmax | index | NULL | PRIMARY | 13 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+--------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain update test set startTime=date_add(now(), interval 3 second) where (endTime is null) ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------+
| 1 | UPDATE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching rows after partition pruning |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------+
How to repeat:
See description.
Suggested fix:
Partition table's pruning should work when we query NULL DATETIME field, just like a NULL value on other type field or MySQL 5.7's behavior.