Bug #119204 The change of join order may lead to inconsistent update data

Submitted: 22 Oct 2025 8:10 Modified: 23 Oct 2025 10:04
Reporter: lixuan tian Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0,9.5 OS:Any
Assigned to: CPU Architecture:Any

[22 Oct 2025 8:10] lixuan tian
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
```
[23 Oct 2025 10:04] Chaithra Marsur Gopala Reddy
Hi  lixuan tian,

Thank you for the test case. Verified as described.

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

Page generated in 0.038 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.