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 |
|
+---+------+------+
|
Critical MDEV-32839 LONG UNIQUE gives error when used with REPLACE
