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

LONG UNIQUE gives error when used with REPLACE

    XMLWordPrintable

Details

    Description

      REPLACE with unique key works in some cases, not in other cases:

      CREATE OR REPLACE TABLE t1 (
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `M_Number` varchar(30) DEFAULT NULL,
        `A_Number` varchar(30) DEFAULT NULL,
        `Type` varchar(255) DEFAULT NULL,
        `S_Id` varchar(30) DEFAULT NULL,
        `U_Id` varchar(255) DEFAULT NULL,
        `IND` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE `problem_key` (`A_Number`,`S_Id`,`U_Id`,`M_Number`,`Type`,`IND`) USING HASH
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
      INSERT INTO  t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'D');
      REPLACE INTO  t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'D');
      INSERT INTO  t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'E');
      # This should not generate a duplicate key error
      REPLACE INTO  t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'E');
      ERROR: Duplicate entry '0001009089999-NETSTES-PSIT-00004--E' for key 'problem_key'
       
      select * from t1;
       
      +----+----------+---------------+------+---------+------+------+
      | id | M_Number | A_Number      | Type | S_Id    | U_Id | IND  |
      +----+----------+---------------+------+---------+------+------+
      |  0 | 00004    | 0001009089999 |      | NETSTES | PSIT | D    |
      |  2 | 00004    | 0001009089999 |      | NETSTES | PSIT | E    |
      +----+----------+---------------+------+---------+------+------+
      

      There are two bugs here:
      The last REPLACE should replace the third inserted row and not give an error
      The first replace should not change the ID to 0

      One can get the correct result by removing 'USING HASH' above:

      +----+----------+---------------+------+---------+------+------+
      | id | M_Number | A_Number      | Type | S_Id    | U_Id | IND  |
      +----+----------+---------------+------+---------+------+------+
      |  2 | 00004    | 0001009089999 |      | NETSTES | PSIT | D    |
      |  4 | 00004    | 0001009089999 |      | NETSTES | PSIT | E    |
      +----+----------+---------------+------+---------+------+------+
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              monty Michael Widenius
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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