Bug #105808 Confusing partition pruning policy for `NULL condition` on DATATIME field.
Submitted: 6 Dec 2021 12:18 Modified: 6 Dec 2021 14:14
Reporter: biao li Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:MySQL 8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: datatime, partition, prune

[6 Dec 2021 12:18] biao li
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.
[6 Dec 2021 12:21] biao li
Add table definition:

CREATE TABLE `test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `endTime` datetime NOT NULL,
  PRIMARY KEY (`id`,`endTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/*!50500 PARTITION BY RANGE  COLUMNS(endTime)
(PARTITION p0 VALUES LESS THAN ('1970-01-01') ENGINE = InnoDB,
 PARTITION p210815 VALUES LESS THAN ('2021-08-15') ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
[6 Dec 2021 13:34] MySQL Verification Team
Hi Mr. li,

Thank you for your bug report.

However, this is not a bug.

We have changed the manner in which we store NULL values in 8.0. Beside that, a query that you run is highly optimised and uses the index. Changes that were made make a significant performance improvement for MOST of the queries. That means that some of the queries will be slower.

Not a bug.
[6 Dec 2021 14:14] biao li
Can you explain why "select count(*) from test where (endtime is null or
endtime > '2021-08-14')" does not query partition p0, but "select count(*) from test where endtime is null" query on partition p0, also because NULL values in 8.0?
[6 Dec 2021 14:15] MySQL Verification Team
Hi,

The answer is simple.

NULLs are kept in one partition and the other range in another, so the optimiser calculated the costs and got a different result.

Not a bug.