Multi-table UPDATE ... ORDER BY using a temporary table: SET expressions read the last row scanned, not the current row

Test case

CREATE TABLE t1 (id INT PRIMARY KEY);

CREATE TABLE t2 (id INT PRIMARY KEY, v INT);

INSERT INTO t1 VALUES (1);

INSERT INTO t2 VALUES (1,10),(2,20),(3,30);

UPDATE t1, t2 SET t2.v = t2.v + 1 ORDER BY t1.id;

SELECT * FROM t2;

Expected:

1  11

2  21

3  31

Actual (12.3.3-MariaDB-ubu2404):

1  31

2  31

3  31

No error, no warning, ROW_COUNT() = 3. Without ORDER BY, the result is correct.

id  select_type  table  type  rows  Extra

1   SIMPLE       t1     ALL   1     Using temporary; Using filesort

1   SIMPLE       t2     ALL   3

Analysis

When the join needs a temporary table, multi_update::send_data() is called while the rows are read back from that temporary table, after the join has finished. At that point the record buffer of t2 still holds the last row scanned during the join.

• multi_update::prepare2() (sql_update.cc) only replaces the Item_temptable_rowid items with temporary table fields. The rowid is therefore correct, and the right rows are updated.
• The SET expressions (values_for_table[], built from lex->value_list) are never switched to the temporary table. fill_record(... *values_for_table[offset] ...) evaluates t2.v + 1 against the stale t2->record[0].

This also happens when a table read after the updated table filters rows out: the value comes from a row that is not even part of the result. Real case: a multi-table UPDATE joined to a view containing ORDER BY copied a JSON column from an English row into the French row.

Relation to MDEV-31828 / MDEV-35955

MDEV-31828 was closed as a duplicate of MDEV-35955. The MDEV-35955 fix (5f68fd52a944) corrects the ORDER BY references to the temporary table. It does not touch the SET values. MDEV-35955's tests only use constant SET values (SET t1.v=-1), so they cannot catch this.

The MDEV-31828 test case passes on 12.3.3 only because its filter is on the first table: the last y row read happens to be the right one.

Workaround

Avoid ORDER BY in multi-table UPDATE, including an ORDER BY inherited from a merged view; or rewrite as a single-table UPDATE with WHERE ... IN (SELECT ...).