Description:
MySQL 8.0.42 introduced a regression in partition handling for tables with timestamp columns using `DEFAULT CURRENT_TIMESTAMP`.
When inserting rows into a partitioned table where the partition key column has a default value of `CURRENT_TIMESTAMP`, subsequent inserts fail with error 1748 after the current time crosses into a new partition boundary.
*Expected behavior*: `INSERT` should succeed, with the row being placed in the correct partition based on the evaluated default value (`CURRENT_TIMESTAMP` at time of insert).
*Actual behavior*: `INSERT` fails with:
```
ERROR 1748 (HY000): Found a row not matching the given partition set
```
This regression was introduced in MySQL 8.0.42. The bug appears to be related to changes made for Bug #37397306 (referenced in the 8.0.42 release notes: https://docs.oracle.com/cd/E17952_01/mysql-8.0-relnotes-en/news-8-0-42.html).
The issue appears to be in the `set_used_partition()` function in `sql/partition_info.cc`, where the partition pruning logic does not properly handle the case where default values (like `CURRENT_TIMESTAMP`) need to be evaluated at insert time rather than using a cached/stale value.
How to repeat:
Run the following SQL script. The final `INSERT` statement will fail with `ERROR 1748`:
```
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
DELIMITER |
CREATE PROCEDURE create_partition_table()
BEGIN
DECLARE start_time DATETIME;
DECLARE partition_sql TEXT;
DECLARE i INT DEFAULT 0;
-- Start 10 seconds ago, create 41 partitions (covers ~40 seconds ahead)
SET start_time = DATE_FORMAT(NOW() - INTERVAL 10 SECOND, '%Y-%m-%d %H:%i:%s');
SET partition_sql = CONCAT(
'CREATE TABLE `dp_tb_partition_issue_test` (\n',
' `id` bigint NOT NULL,\n',
' `col1` bigint NOT NULL,\n',
' `pt_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n',
' PRIMARY KEY (`id`,`pt_timestamp`)\n',
') ENGINE=InnoDB DEFAULT CHARSET=utf8mb3\n',
'PARTITION BY RANGE (UNIX_TIMESTAMP(`pt_timestamp`))\n(\n'
);
WHILE i < 41 DO
SET partition_sql = CONCAT(
partition_sql,
' PARTITION p', DATE_FORMAT(start_time + INTERVAL i SECOND, '%Y%m%d%H%i%s'),
' VALUES LESS THAN (UNIX_TIMESTAMP(''',
DATE_FORMAT(start_time + INTERVAL (i+1) SECOND, '%Y-%m-%d %H:%i:%s'),
''')) ENGINE = InnoDB,\n'
);
SET i = i + 1;
END WHILE;
SET partition_sql = CONCAT(partition_sql, ' PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB\n);');
DROP TABLE IF EXISTS dp_tb_partition_issue_test;
SET @sql = partition_sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END|
CREATE PROCEDURE `dp_insert_row`(IN i BIGINT)
BEGIN
INSERT INTO `dp_tb_partition_issue_test` (`id`, `col1`) VALUES (i, 999999);
END|
DELIMITER ;
CALL create_partition_table();
CALL dp_insert_row(1); -- First insert succeeds
SELECT SLEEP(2); -- Wait for time to cross partition boundary
CALL dp_insert_row(2); -- Second insert FAILS with ERROR 1748
```
Note: The sleep duration ensures the current time moves into a different partition than when the first insert occurred. This reproduces the issue where the partition selection uses stale timestamp information rather than evaluating `CURRENT_TIMESTAMP` at insert time.
Suggested fix:
The `set_used_partition()` function in `sql/partition_info.cc` needs to properly evaluate default values (such as `CURRENT_TIMESTAMP`) when determining the target partition for `INSERT operations`, rather than relying on potentially stale cached values.
The fix should ensure that when a partition key column uses a default value expression, that expression is evaluated at the time of partition selection to get the current value.
