[MDEV-32837] long unique does not work like unique key when using replace Created: 2023-11-19  Updated: 2024-01-25  Resolved: 2024-01-25

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.5, 10.6
Fix Version/s: 10.9.8, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2, 11.4.1

Type: Bug Priority: Critical
Reporter: Michael Widenius Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-32839 LONG UNIQUE gives error when used wit... Closed

 Description   

REPLACE is supposed to remove all conflicting rows.
For UNIQUE HASH, REPLACE only removes the first conflicting row, not all conflicting rows, and generates an error:

Test case:

create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c) using hash) engine=myisam;
 
insert into t1 values (1,1,1),(2,2,2);
 
replace into t1 values (3,1,1);
 
select * from t1;
 
--error 1062
 
replace into t1 values (3,2,2);
 
select * from t1;


+---+------+------+
 
| a | b    | c    |
 
+---+------+------+
 
| 3 |    1 |    1 |
 
| 2 |    2 |    2 |
 
+---+------+------+

Test without USING HASH

create or replace table t1 (a int primary key, b int, c int, unique key `test` (b,c)) engine=myisam;
 
insert into t1 values (1,1,1),(2,2,2);
 
replace into t1 values (3,1,1);
 
select * from t1;
 
replace into t1 values (3,2,2);
 
select * from t1;


+---+------+------+
 
| a | b    | c    |
 
+---+------+------+
 
| 3 |    2 |    2 |
 
+---+------+------+



 Comments   
Comment by Alexander Barkov [ 2024-01-10 ]

Hello serg,

Please review a patch:

https://github.com/MariaDB/server/commit/424ee6eac783dd91d08305a9efff9347da6a6b4a

Comment by Alexander Barkov [ 2024-01-17 ]

Hello serg,

Please review a new patch version:

https://github.com/MariaDB/server/commit/af51be1106b6738f64ebe5ee7a18573a94fdf934

Generated at Thu Feb 08 10:34:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.