Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
Linux x86_64
Description
A multi-row REPLACE fails when its first input row uses an auto-generated primary key and a later row supplies an explicit primary key. I get error 1062 for the combined statement, although executing the same two replacements separately succeeds.
This occurs when a multi-row REPLACE combines an auto-generated key in one input row with an explicit primary key in a later row.
The following reproducer preserves the tested SQL, with only incidental identifiers shortened. Save it as case.sql and run it with the MariaDB client:
mariadb < case.sql |
CREATE DATABASE replace_test; |
USE replace_test; |
CREATE TABLE combined (id INT AUTO_INCREMENT PRIMARY KEY, u INT UNIQUE) ENGINE=InnoDB; |
CREATE TABLE split LIKE combined; |
INSERT INTO combined VALUES (1,10),(2,20); |
INSERT INTO split VALUES (1,10),(2,20); |
DELIMITER //
|
CREATE PROCEDURE run_case() |
BEGIN
|
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION |
GET DIAGNOSTICS CONDITION 1 @err_no=MYSQL_ERRNO, @err_msg=MESSAGE_TEXT;
|
SET @err_no=0, @err_msg=''; |
REPLACE INTO combined VALUES (NULL,10),(2,30); |
END// |
DELIMITER ;
|
CALL run_case();
|
SELECT 'combined_error' AS label, @err_no AS errno, @err_msg AS message; |
REPLACE INTO split VALUES (NULL,10); |
REPLACE INTO split VALUES (2,30); |
SELECT 'combined_rows' AS label, |
GROUP_CONCAT(CONCAT(id,':',u) ORDER BY id) AS rows_value |
FROM combined; |
SELECT 'split_rows' AS label, |
GROUP_CONCAT(CONCAT(id,':',u) ORDER BY id) AS rows_value |
FROM split; |
SELECT 'oracle' AS label, @err_no=0 AND |
(SELECT GROUP_CONCAT(CONCAT(id,':',u) ORDER BY id) FROM combined)='2:30,3:10' |
AND (SELECT GROUP_CONCAT(CONCAT(id,':',u) ORDER BY id) FROM split)='2:30,3:10' AS passed; |
DROP DATABASE replace_test; |
SELECT 'cleanup' AS label, COUNT(*)=0 AS passed |
FROM information_schema.schemata WHERE schema_name='replace_test'; |
On MariaDB 12.3.3, the recorded result was:
combined_error 1062 Duplicate entry '2' for key 'PRIMARY'
|
combined_rows 1:10,2:20
|
split_rows 2:30,3:10
|
oracle 0
|
cleanup 1
|
The combined statement therefore rolls back and leaves 1:10,2:20, while the equivalent split statements produce 2:30,3:10. The combined and split forms should both succeed and produce 2:30,3:10; using an auto-generated value for the first input row must not cause that row's generated identity state to reject the later explicit key.