Bug #112761 Partition prune always with the first partition when range(to_days(xxx))

• Submitted: 18 Oct 2023 10:55
• Modified: 18 Oct 2023 11:59
• Reporter: XIAOJING LI
• Status: Can't repeat
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 8.0.2
• OS: Any
• CPU Architecture: Any
• Tags: partition, partition by range, to_days

[18 Oct 2023 10:55] XIAOJING LI

Description:
Partition prune cannot remove the first partition when :
1, range partitioning
2, part_expr is to_days('xxxx')
3, the sql where conditions range is more than 1 months

The part_id 0 is alwags set in part_info->read_partitions after partition pruning

How to repeat:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL COMMENT '?????id',
  `r_time` datetime(3) NOT NULL COMMENT '????',
  PRIMARY KEY (`id`,`r_time`)
)
 PARTITION BY RANGE (to_days(`r_time`))
(PARTITION p_min VALUES LESS THAN (to_days('2023-01-01 00:00:00')),
 PARTITION p2023_q1 VALUES LESS THAN (to_days('2023-04-01 00:00:00')),
 PARTITION p2023_q2 VALUES LESS THAN (to_days('2023-07-01 00:00:00')) ,
 PARTITION p2023_q3 VALUES LESS THAN (to_days('2023-10-01 00:00:00')),
 PARTITION p2023_q4 VALUES LESS THAN (to_days('2024-01-01 00:00:00')) ,
 PARTITION p2024_q1 VALUES LESS THAN (to_days('2024-04-01 00:00:00'))
);

// p_min is always in used partitions list
mysql> explain select * from t1 where r_time between '2023-08-17 00:00:00'  and '2023-9-18 23:59:59';
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions     | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | p_min,p2023_q3 | index | PRIMARY       | PRIMARY | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> explain select * from t1 where r_time between '2023-08-17 00:00:00'  and '2023-10-18 23:59:59';
+----+-------------+-------+-------------------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions              | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+-------------------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | p_min,p2023_q3,p2023_q4 | index | PRIMARY       | PRIMARY | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+-------------------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

// start and end is in one month is OK.
mysql> explain select * from t1 where r_time between '2023-08-17 00:00:00'  and '2023-8-18 23:59:59';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | p2023_q3   | index | PRIMARY       | PRIMARY | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
In sql/partition.cc function get_part_iter_for_interval_via_mapping, code:

    if (check_zero_dates && !zero_in_start_date && !part_expr->null_value) {
      MYSQL_TIME end_date;
      bool zero_in_end_date = field->get_date(&end_date, 0);
      /*
        This is an optimization for TO_DAYS()/TO_SECONDS() to avoid scanning
        the NULL partition for ranges that cannot include a date with 0 as
        month/day.
      */
      DBUG_PRINT("info", ("zero end %u %04d-%02d-%02d", zero_in_end_date,
                          end_date.year, end_date.month, end_date.day));
      DBUG_ASSERT(
          !memcmp(((Item_func *)part_expr)->func_name(), "to_days", 7) ||
          !memcmp(((Item_func *)part_expr)->func_name(), "to_seconds", 10));
      if (!zero_in_end_date && start_date.month == end_date.month &&
          start_date.year == end_date.year)
        part_iter->ret_null_part = part_iter->ret_null_part_orig = false;
    }

if it can be modified like this:

    if (check_zero_dates && !zero_in_start_date && !part_expr->null_value) {
      part_iter->ret_null_part = part_iter->ret_null_part_orig = false;
    }

[18 Oct 2023 11:59] MySQL Verification Team

Hi Mr. Li,

Thank you for your bug report.

The expected optimiser decisions are quite understandable, since all partitions are empty.

Please, provide us with a test case that will fill up all partitions.

If we are able to repeat the behaviour, we shall change the status accordingly.

Thanks in advance.