Bug #115830 `Replace into` may be invalid when functional key is unique

Submitted: 14 Aug 2024 2:24 Modified: 14 Aug 2024 6:04
Reporter: huahua xu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.39, 8.4.2, 9.0.1 OS:Any
Assigned to: CPU Architecture:Any

[14 Aug 2024 2:24] huahua xu
Description:
The `repalce into` statment maybe do not work when the table has unique and functional key. But it would be ok after create another unique and functional key on it.

How to repeat:
mysql> create table test2uk(c1 int,c2 int,c3 int,c4 int);

mysql> create unique index iii on test2uk((c2+c3));

mysql> create unique index iii1 on test2uk(c4);

mysql> insert into test2uk values(1,1,1,1), (2,1,2,2);

mysql>  replace into test2uk values(3,2,0,2);
ERROR 1062 (23000): Duplicate entry '2' for key 'test2uk.iii1'

mysql> create unique index iii2 on test2uk((c1+1));

mysql> replace into test2uk values(3,2,0,2);
Query OK, 3 rows affected (0.01 sec)
[14 Aug 2024 6:04] MySQL Verification Team
Hello huahua xu,

Thank you for the report and test case.

regards,
Umesh
[20 Oct 8:37] Knut Anders Hatlen
This appears to have the same cause as bug#121507. The issue is not specific to functional indexes: the index creation sequence can leave InnoDB checking unique-key conflicts in a different order from the SQL layer's index order.

The REPLACE implementation uses last_uniq_key() to decide whether it can replace a delete-and-insert operation with an UPDATE. That decision assumes that unique-key conflicts are reported in SQL index order.

There is already a handler flag, HA_DUPLICATE_KEY_NOT_IN_ORDER, that disables this optimization for engines that cannot guarantee that ordering. Bug#109445 notes that no engine currently sets it.

Having InnoDB return this flag is therefore a candidate fix. It would direct REPLACE through the delete-and-retry path. I have not tested a patch setting the flag. However, I tested both this report's functional-index reproducer and the reduced prefix-index reproducer in bug#121507 with a DELETE trigger present. This also disables the optimization, and both statements then succeed, deleting both conflicting rows.
[20 Oct 8:41] Knut Anders Hatlen
Bug#121507 has been marked as a duplicate of this report. It includes a reduced reproducer using a prefix unique index rather than a functional index, along with discussion of compatibility considerations.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.070 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.