[MDEV-32839] LONG UNIQUE gives error when used with REPLACE - Jira

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    |

+----+----------+---------------+------+---------+------+------+

relates to

MDEV-33003 long uniques don't work with spider

MDEV-33731 Server crashes when deleting partitions from a table with spider engine

MDEV-32837 long unique does not work like unique key when using replace

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

MariaDB Server