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

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

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

| id | M_Number | A_Number      | Type | S_Id    | U_Id | IND  |

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

|  2 | 00004    | 0001009089999 |      | NETSTES | PSIT | D    |

|  4 | 00004    | 0001009089999 |      | NETSTES | PSIT | E    |

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

[MDEV-32839] LONG UNIQUE gives error when used with REPLACE Created: 2023-11-19 Updated: 2023-12-12 Resolved: 2023-12-12
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Manipulation - Insert
Affects Version/s:	10.5, 10.6, 10.11, 11.0, 11.1, 11.2
Fix Version/s:	10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3