Bug #105808 Confusing partition pruning policy for `NULL condition` on DATATIME field.

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.

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) */

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.

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?

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.