Description:
I got the wrong result when add 'join_prefix' in update sql.
In the update of multi-table associations, when the join order changes, it will lead to inconsistent update results.
How to repeat:
create table t1 (a int, b varchar(500), c date, d int, primary key(a, b, c));
create table t2 like t1;
insert into t1 values (1, 'a', '2025-10-22', 1), (2, 'b', '2025-10-22', 2), (3, 'c', '2025-10-22', 3);
insert into t2 values (1, 'a', '2025-10-22', 1), (2, 'b', '2025-10-22', 2), (3, 'c', '2025-10-22', 3);
mysql> update t1, t2 set t1.d = t1.d + 3, t2.d = t2.d + 3;
Query OK, 6 rows affected (0.001 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from t2;
+---+---+------------+------+
| a | b | c | d |
+---+---+------------+------+
| 1 | a | 2025-10-22 | 4 |
| 2 | b | 2025-10-22 | 5 |
| 3 | c | 2025-10-22 | 6 |
+---+---+------------+------+
3 rows in set (0.000 sec)
mysql> update /*+ join_prefix(t2, t1) */ t1, t2 set t1.d = t1.d + 3, t2.d = t2.d + 3;
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from t2;
+---+---+------------+------+
| a | b | c | d |
+---+---+------------+------+
| 1 | a | 2025-10-22 | 4 |
| 2 | b | 2025-10-22 | 2 |
| 3 | c | 2025-10-22 | 3 |
+---+---+------------+------+
3 rows in set (0.000 sec)
Suggested fix:
After the check_unique_constraint function in the sql_update.cc file is executed, it should be "continue" instead of "return"
source code:
```
// check if a record exists with the same hash value
if (!check_unique_constraint(tmp_table))
return false; // skip adding duplicate record to the temp table
```
