Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
Linux/amd64
Description
On MariaDB 12.3.3 on Linux/amd64, I get error 1736 when ALTER TABLE ... EXCHANGE PARTITION compares matching generated-column definitions whose DATE_FORMAT format or locale argument comes from a column. The equivalent definition with constant format and locale arguments exchanges successfully.
Save the following as case.sql:
CREATE DATABASE exchange_test; |
USE exchange_test; |
|
|
CREATE TABLE control_tbl ( |
id INT PRIMARY KEY, d DATE, fmt VARCHAR(16), loc VARCHAR(16), |
g VARCHAR(64) AS (DATE_FORMAT(d,'%Y','en_US')) PERSISTENT |
) ENGINE=InnoDB PARTITION BY LIST(id) |
(PARTITION p1 VALUES IN (1)); |
CREATE TABLE control_stage LIKE control_tbl; |
ALTER TABLE control_stage REMOVE PARTITIONING; |
INSERT INTO control_stage(id,d,fmt,loc) |
VALUES (1,'2024-02-03','%Y','en_US'); |
|
|
CREATE TABLE format_tbl ( |
id INT PRIMARY KEY, d DATE, fmt VARCHAR(16), loc VARCHAR(16), |
g VARCHAR(64) AS (DATE_FORMAT(d,fmt,'en_US')) PERSISTENT |
) ENGINE=InnoDB PARTITION BY LIST(id) |
(PARTITION p1 VALUES IN (1)); |
CREATE TABLE format_stage LIKE format_tbl; |
ALTER TABLE format_stage REMOVE PARTITIONING; |
INSERT INTO format_stage(id,d,fmt,loc) |
VALUES (1,'2024-02-03','%Y','en_US'); |
|
|
CREATE TABLE locale_tbl ( |
id INT PRIMARY KEY, d DATE, fmt VARCHAR(16), loc VARCHAR(16), |
g VARCHAR(64) AS (DATE_FORMAT(d,'%Y',loc)) PERSISTENT |
) ENGINE=InnoDB PARTITION BY LIST(id) |
(PARTITION p1 VALUES IN (1)); |
CREATE TABLE locale_stage LIKE locale_tbl; |
ALTER TABLE locale_stage REMOVE PARTITIONING; |
INSERT INTO locale_stage(id,d,fmt,loc) |
VALUES (1,'2024-02-03','%Y','en_US'); |
|
|
DELIMITER //
|
CREATE PROCEDURE run_exchange() |
BEGIN
|
DECLARE e INT DEFAULT 0; |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION |
GET DIAGNOSTICS CONDITION 1 e=MYSQL_ERRNO;
|
|
|
ALTER TABLE control_tbl EXCHANGE PARTITION p1 WITH TABLE control_stage; |
SELECT 'control' AS case_name, e AS error_number, |
(SELECT COUNT(*) FROM control_tbl WHERE g='2024') AS partition_rows, |
(SELECT COUNT(*) FROM control_stage) AS staging_rows; |
|
|
SET e=0; |
ALTER TABLE format_tbl EXCHANGE PARTITION p1 WITH TABLE format_stage; |
SELECT 'format_column' AS case_name, e AS error_number, |
(SELECT COUNT(*) FROM format_tbl WHERE g='2024') AS partition_rows, |
(SELECT COUNT(*) FROM format_stage WHERE g='2024') AS staging_rows; |
|
|
SET e=0; |
ALTER TABLE locale_tbl EXCHANGE PARTITION p1 WITH TABLE locale_stage; |
SELECT 'locale_column' AS case_name, e AS error_number, |
(SELECT COUNT(*) FROM locale_tbl WHERE g='2024') AS partition_rows, |
(SELECT COUNT(*) FROM locale_stage WHERE g='2024') AS staging_rows; |
END// |
DELIMITER ;
|
|
|
CALL run_exchange();
|
DROP DATABASE exchange_test; |
Run it with an authenticated MariaDB client:
mariadb --batch --raw < case.sql |
I get:
case_name error_number partition_rows staging_rows
|
control 0 1 0
|
format_column 1736 0 1
|
locale_column 1736 0 1
|
All three exchanges should report error_number=0, move the staging row into the partition, and produce partition_rows=1 and staging_rows=0. Each staging table was created with LIKE from its corresponding partitioned table before partitioning was removed, so the generated-column definitions match. The successful constant-argument control confirms that the exchange setup itself is valid.
This occurs when EXCHANGE PARTITION compares matching generated DATE_FORMAT expressions across tables and the format or locale argument is a column.
Source inspection supports the hypothesis that the specialized DATE_FORMAT equality comparison preserves the cross-table comparison policy for the date argument but loses it for the format and locale arguments. Those operands are then compared as table-specific fields, causing matching definitions to be rejected.
Attachments
Issue Links
- relates to
-
-
- Closed
-