Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41973

Multi-row REPLACE incorrectly reports duplicate primary key after an auto-generated row

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            faner fan
            Oleksandr Byelkin Oleksandr Byelkin
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 0d
                0d
                Remaining:
                Remaining Estimate - 1h
                1h
                Logged:
                Time Spent - Not Specified
                Not Specified

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.