MySQL Bugs: #119626: Adding an index using the inplace algorithm will result in a "Duplicate entry" error in the replace into syntax.

Bug #119626	Adding an index using the inplace algorithm will result in a "Duplicate entry" error in the replace into syntax.
Submitted:	5 Jan 13:33	Modified:	6 Jan 1:30
Reporter:	fei yang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The replace into statement will throw an error when executing the following statement:

set sql_mode="";
CREATE TABLE t1 (c1 INT1, c4 DOUBLE, c3 BIT (7) NULL, PRIMARY KEY (c1, c4), UNIQUE (c3))ENGINE=INNODB;
CREATE UNIQUE INDEX i3 ON t1(c4 DESC) INVISIBLE ALGORITHM = INPLACE;
INSERT INTO t1 (c4,c1,c3) VALUES (1.7976931348623157E308,7,b'00');
INSERT INTO t1 (c4,c1,c3) VALUES (-9.183,0,1);
REPLACE INTO t1 (c4,c1,c3) VALUES (-9.183,7,0); 

However, when we specify CREATE UNIQUE INDEX i3 ON t1(c4 DESC) INVISIBLE ALGORITHM = COPY, no error will be reported.

The issue appears to stem from the fact that after adding an index using the `INPLACE` method, the order of `indexes` in `dict_table_t` is `[primary, c3, i3]`, while the order of `key_info` in the `TABLE` is `[primary, i3, c3]`. When executing `REPLACE INTO`, InnoDB throws an error when inserting index `c3` because the value 7 is duplicated. Upon returning to the SQL layer, since `c3` is the last index in the `TABLE` object, `last_uniq_key` returns true. Subsequently, the SQL layer calls `ha_update_row` to handle the conflicting key, leading to the failure.

How to repeat:
Refer to above

Suggested fix:
Perhaps `last_uniq_key` should used another way to determine whether a conflicting index is the last one.

Probably a duplicate of Bug#98600.

And more information in this blog post:

https://jfg-mysql.blogspot.com/2021/11/duplicate-entry-in-alter-table-and-in-optimize-tabl...

(sorry, I was too quick on this one, this is another issue)

yes, it seems different from the Bug#98600, the duplicate entry error appear after add index, and this issue seems specific to `replace into`.

What makes this use case special is that the values inserted in `replace into`, -9.183 and 7, are both duplicates.