Bug #115980 RANGE partitioned table partitioned by TO_SECONDS partition pruning error
Submitted: 2 Sep 2024 9:44 Modified: 5 Oct 2024 7:12
Reporter: Bob Wong Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.*，5.7.* OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[2 Sep 2024 9:44] Bob Wong 
Description:
A RANGE partitioned table is partitioned by the result of the TO_SECONDS function, and a partition pruning error occurs for specific values.

How to repeat:
mysql> CREATE TABLE `t_billing_feeextendinfo` (
    ->   `FeeId` varchar(64) NOT NULL COMMENT '',
    ->   `CustomerId` varchar(64) NOT NULL COMMENT 'ID',
    ->   `RelId` varchar(64) DEFAULT NULL COMMENT 'ID',
    ->   `BusinessUnit` text,
    ->   `ResourceTag` longtext,
    ->   `ResourceName` varchar(256) DEFAULT NULL,
    ->   `CreateTime` datetime NOT NULL,
  `ResourceID` varchar(128) DEFAULT NULL,
  `UpdateTime` datetime NOT NULL,
    ->   `CreateTime` datetime NOT NULL,
    ->   `UpdateTime` datetime NOT NULL,
  `extendParam1` varchar(256) DEFAULT NULL,
    ->   `extendParam1` varchar(256) DEFAULT NULL,
    ->   `extendParam2` varchar(256) DEFAULT NULL,
    ->   `extendParam3` varchar(256) DEFAULT NULL,
    ->   `extendParam4` int(11) DEFAULT NULL,
    ->   `extendParam5` int(11) DEFAULT NULL,
    ->   `PromotionType` varchar(64) DEFAULT NULL COMMENT '折扣类型(子费用有)，原扩展继续放值',
  `extendParam6` varchar(256) DEFAULT NULL,
    ->   `extendParam7` text,
  `Spot` int(3) DEFAULT NULL COMMENT '竞价，原扩展字段不再放值',
  `ReleaseType` int(3) DEFAULT NULL COMMENT '释放类型，原扩展字段不再放值',
    ->   `extendParam8` varchar(256) DEFAULT NULL,
    ->   `extendParam9` varchar(256) DEFAULT NULL,
    ->   `PriceFactorName` varchar(256) DEFAULT NULL COMMENT '',
    ->   `ChargeMode` varchar(64) DEFAULT NULL COMMENT '',
    ->   `PromotionId` varchar(128) DEFAULT NULL COMMENT 'IDIDID',
    ->   `PromotionType` varchar(64) DEFAULT NULL COMMENT '()',
    ->   `ResourceSpecSize` decimal(25,6) DEFAULT NULL COMMENT '',
    ->   `ResourceSpecSizeMeasure` int(11) DEFAULT NULL COMMENT '',
    ->   `Spot` int(3) DEFAULT NULL COMMENT '',
    ->   `ReleaseType` int(3) DEFAULT NULL COMMENT '',
    ->   PRIMARY KEY (`FeeId`,`CreateTime`),
    ->   KEY `idx_createTime` (`CreateTime`),
    ->   KEY `idx_resourceId` (`ResourceID`),
    ->   KEY `idx_relId` (`RelId`),
    ->   KEY `idx_priceFactorName` (`PriceFactorName`(255))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> /*!50500 PARTITION BY RANGE (TO_SECONDS(CreateTime))
    -> (PARTITION p20240814 VALUES LESS THAN (63890870400) ENGINE = InnoDB,
    ->  PARTITION p20240815 VALUES LESS THAN (63890956800) ENGINE = InnoDB,
    ->  PARTITION p20240816 VALUES LESS THAN (63891043200) ENGINE = InnoDB,
    ->  PARTITION p20240817 VALUES LESS THAN (63891129600) ENGINE = InnoDB,
    ->  PARTITION p20240818 VALUES LESS THAN (63891216000) ENGINE = InnoDB,
    ->  PARTITION p20240819 VALUES LESS THAN (63891302400) ENGINE = InnoDB,
    ->  PARTITION p20240820 VALUES LESS THAN (63891388800) ENGINE = InnoDB,
    ->  PARTITION p20240821 VALUES LESS THAN (63891475200) ENGINE = InnoDB,
    ->  PARTITION p20240822 VALUES LESS THAN (63891561600) ENGINE = InnoDB,
    ->  PARTITION p20240823 VALUES LESS THAN (63891648000) ENGINE = InnoDB,
    ->  PARTITION p20240824 VALUES LESS THAN (63891734400) ENGINE = InnoDB,
    ->  PARTITION p20240825 VALUES LESS THAN (63891820800) ENGINE = InnoDB,
    ->  PARTITION p20240826 VALUES LESS THAN (63891907200) ENGINE = InnoDB,
    ->  PARTITION p20240827 VALUES LESS THAN (63891993600) ENGINE = InnoDB,
    ->  PARTITION p20240828 VALUES LESS THAN (63892080000) ENGINE = InnoDB,
    ->  PARTITION p20240829 VALUES LESS THAN (63892166400) ENGINE = InnoDB,
    ->  PARTITION p20240830 VALUES LESS THAN (63892252800) ENGINE = InnoDB,
 PARTITION p20240909 VALUES LESS THAN (63893116800) ENGINE = InnoDB,
 PARTITION p20240910 VALUES LESS THAN (63893203200) ENGINE = InnoDB,
    ->  PARTITION p20240831 VALUES LESS THAN (63892339200) ENGINE = InnoDB,
    ->  PARTITION p20240901 VALUES LESS THAN (63892425600) ENGINE = InnoDB,
    ->  PARTITION p20240902 VALUES LESS THAN (63892512000) ENGINE = InnoDB,
    ->  PARTITION p20240903 VALUES LESS THAN (63892598400) ENGINE = InnoDB,
    ->  PARTITION p20240904 VALUES LESS THAN (63892684800) ENGINE = InnoDB,
    ->  PARTITION p20240905 VALUES LESS THAN (63892771200) ENGINE = InnoDB,
    ->  PARTITION p20240906 VALUES LESS THAN (63892857600) ENGINE = InnoDB,
    ->  PARTITION p20240907 VALUES LESS THAN (63892944000) ENGINE = InnoDB,
    ->  PARTITION p20240908 VALUES LESS THAN (63893030400) ENGINE = InnoDB,
    ->  PARTITION p20240909 VALUES LESS THAN (63893116800) ENGINE = InnoDB,
    ->  PARTITION p20240910 VALUES LESS THAN (63893203200) ENGINE = InnoDB,
    ->  PARTITION p20240911 VALUES LESS THAN (63893289600) ENGINE = InnoDB,
    ->  PARTITION p20240912 VALUES LESS THAN (63893376000) ENGINE = InnoDB,
    ->  PARTITION p20240913 VALUES LESS THAN (63893462400) ENGINE = InnoDB,
    ->  PARTITION p20240914 VALUES LESS THAN (63893548800) ENGINE = InnoDB,
    ->  PARTITION p20240915 VALUES LESS THAN (63893635200) ENGINE = InnoDB,
    ->  PARTITION p20240916 VALUES LESS THAN (63893721600) ENGINE = InnoDB,
    ->  PARTITION p20240917 VALUES LESS THAN (63893808000) ENGINE = InnoDB,
    ->  PARTITION p20240918 VALUES LESS THAN (63893894400) ENGINE = InnoDB,
    ->  PARTITION p20240919 VALUES LESS THAN (63893980800) ENGINE = InnoDB,
    ->  PARTITION p20240920 VALUES LESS THAN (63894067200) ENGINE = InnoDB,
    ->  PARTITION p20240921 VALUES LESS THAN (63894153600) ENGINE = InnoDB,
    ->  PARTITION p20240922 VALUES LESS THAN (63894240000) ENGINE = InnoDB,
    ->  PARTITION p20240923 VALUES LESS THAN (63894326400) ENGINE = InnoDB,
    ->  PARTITION p20240924 VALUES LESS THAN (63894412800) ENGINE = InnoDB,
    ->  PARTITION p20240925 VALUES LESS THAN (63894499200) ENGINE = InnoDB,
    ->  PARTITION p20240926 VALUES LESS THAN (63894585600) ENGINE = InnoDB,
    ->  PARTITION p20240927 VALUES LESS THAN (63894672000) ENGINE = InnoDB,
    ->  PARTITION p20240928 VALUES LESS THAN (63894758400) ENGINE = InnoDB,
    ->  PARTITION p20240929 VALUES LESS THAN (63894844800) ENGINE = InnoDB,
    ->  PARTITION p20240930 VALUES LESS THAN (63894931200) ENGINE = InnoDB,
    ->  PARTITION p20241001 VALUES LESS THAN (63895017600) ENGINE = InnoDB,
    ->  PARTITION p20241002 VALUES LESS THAN (63895104000) ENGINE = InnoDB) */
    -> ;
Query OK, 0 rows affected, 6 warnings (1.07 sec)

mysql>

mysql> explain select feeid, customerid, relid, businessunit, resourcetag, resourcename, resourceid, substring(convert_tz(createtime,'+00:00','+08:00'),1,19) as createtime, substring(convert_tz(updatetime,'+00:00','+08:00'),1,19) as updatetime, extendparam1, extendparam2, extendparam3, extendparam4, extendparam5, extendparam6 as extendparam6new, extendparam7, extendparam8, extendparam9, pricefactorname, chargemode, promotionid, promotiontype, resourcespecsize, resourcespecsizemeasure, spot, releasetype, date_format(substring(convert_tz(createtime,'+00:00','+08:00'),1,19),'%Y%m%d') as dtime from t_billing_feeextendinfo where createtime>= '2024-08-31 16:00:00'  and createtime< '2024-09-01 16:00:00';
+----+-------------+-------------------------+---------------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table                   | partitions          | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------------------+---------------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_billing_feeextendinfo | p20240814,p20240901 | range | idx_createTime | idx_createTime | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------------------------+---------------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

Partition pruning error, resulting in an extra p20240814 partition in the result set.
[5 Sep 2024 7:12] MySQL Verification Team 
Hello Bob Wong,

Thank you for the report and test case.
Could you please provide logical dump of the data(you may mark it as private
 if your prefer) which manifest the problem? Thank you.

regards,
Umesh
[6 Oct 2024 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".