Description:
With `optimizer_switch='hypergraph_optimizer=on'` (settable on the 9.7.1 release
build; `off` by default), a **single-table** `UPDATE` or `DELETE` that has both an
`ORDER BY` clause and a `WHERE` subquery which the optimizer converts into a hash
semijoin/antijoin writes the wrong rows. Three rows in two tables are enough. The
statement reports one affected row instead of three: two matching rows are
silently left unchanged, the row that is modified is the last row of the scan, and
the value it receives was computed from the first row of the sort sequence.
The decisive control is that the **equivalent SELECT is correct**. Under the same
session settings, `SELECT id, v+1 FROM a WHERE EXISTS (...) ORDER BY id` returns
11, 21, 31. Only the write path is affected, so this is not a projection of a
read-path defect. For context, in our own testing this switch produced zero result
differences over 1198 activated SELECT test cells.
MySQL's own WL#6057, "Make semijoin work with single-table UPDATE/DELETE", states
as requirement F1:
> if a single-table UPDATE/DELETE has a [NOT] IN/EXISTS(subquery) predicate, and
> has no ORDER BY and no LIMIT, and the target table doesn't support
> read-before-write-removal, and semijoin or subquery materialization are allowed
> [...] then the statement should be internally converted to a multi-table one.
and explains the `ORDER BY`/`LIMIT` exclusion by noting that those clauses are not
supported by multi-table UPDATE/DELETE. The traditional optimizer honours this
precondition; the hypergraph optimizer performs the semijoin conversion for a
single-table UPDATE/DELETE that does have `ORDER BY`, which is exactly the case
WL#6057 excludes.
How to repeat:
```sql
DROP DATABASE IF EXISTS bugrep_mysql2;
CREATE DATABASE bugrep_mysql2;
USE bugrep_mysql2;
CREATE TABLE a (id INT PRIMARY KEY, v INT);
CREATE TABLE b (id INT PRIMARY KEY);
INSERT INTO a VALUES (1,10),(2,20),(3,30);
INSERT INTO b VALUES (1),(2),(3);
-- Step 1: baseline, default optimizer_switch (hypergraph_optimizer=off)
SET SESSION optimizer_switch = DEFAULT;
UPDATE a SET v = v + 1 WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
SELECT ROW_COUNT() AS baseline_affected;
-- actual: 3 expected: 3
SELECT * FROM a ORDER BY id;
-- actual: (1,11) (2,21) (3,31) expected: (1,11) (2,21) (3,31)
UPDATE a SET v = v - 1; -- reset to (1,10),(2,20),(3,30)
-- Step 2: control -- the equivalent SELECT under hypergraph is CORRECT
SET SESSION optimizer_switch = 'hypergraph_optimizer=on';
SELECT id, v + 1 AS expected FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
-- actual: (1,11) (2,21) (3,31) -- correct row set and correct values
-- Step 3: the same predicate in an UPDATE -- BUG
UPDATE a SET v = v + 1 WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
SELECT ROW_COUNT() AS hypergraph_affected;
-- actual: 1 expected: 3
SELECT @@warning_count AS warnings;
-- actual: 0 -- silent
SELECT * FROM a ORDER BY id;
-- actual: (1,10) (2,20) (3,11) expected: (1,11) (2,21) (3,31)
-- ^^^^^^ ^^^^^^ not updated at all
-- ^^^^^^ updated, and got 11 = (v of id=1) + 1
-- Step 4: DELETE is affected the same way
DROP TABLE a;
CREATE TABLE a (id INT PRIMARY KEY, v INT);
INSERT INTO a VALUES (1,10),(2,20),(3,30);
DELETE FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
SELECT ROW_COUNT() AS delete_affected;
-- actual: 1 expected: 3
SELECT * FROM a ORDER BY id;
-- actual: (1,10) (2,20) expected: empty table
-- Step 5: plan
DROP TABLE a;
CREATE TABLE a (id INT PRIMARY KEY, v INT);
INSERT INTO a VALUES (1,10),(2,20),(3,30);
EXPLAIN ANALYZE
UPDATE a SET v = v + 1 WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
-- Step 6: the inline hint form reproduces it with an otherwise default session
DROP TABLE a;
CREATE TABLE a (id INT PRIMARY KEY, v INT);
INSERT INTO a VALUES (1,10),(2,20),(3,30);
SET SESSION optimizer_switch = DEFAULT; -- hypergraph_optimizer=off
UPDATE /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */ a
SET v = v + 1 WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
SELECT ROW_COUNT() AS inline_hint_affected;
-- actual: 1 expected: 3
SELECT * FROM a ORDER BY id;
-- actual: (1,10) (2,20) (3,11)
-- Step 7: workaround -- semijoin=off, still under hypergraph_optimizer=on
SET SESSION optimizer_switch = DEFAULT;
SET SESSION optimizer_switch = 'hypergraph_optimizer=on,semijoin=off';
DROP TABLE a;
CREATE TABLE a (id INT PRIMARY KEY, v INT);
INSERT INTO a VALUES (1,10),(2,20),(3,30);
UPDATE a SET v = v + 1 WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) ORDER BY id;
SELECT ROW_COUNT() AS workaround_affected;
-- actual: 3 expected: 3
SELECT * FROM a ORDER BY id;
-- actual: (1,11) (2,21) (3,31) -- correct
```
Note on `SET SESSION optimizer_switch`: assigning individual flags is
incremental, which is why step 7 resets to `DEFAULT` first. Without that reset, a
later `hypergraph_optimizer=on` would inherit `semijoin=off` from an earlier
statement and the defect would appear to have vanished.
How to repeat:
Measured on MySQL 9.7.1 (Homebrew, macOS 26.4, arm64), default server
configuration apart from the session `optimizer_switch` values shown. The script
above is self-contained; run it with
`mysql -h 127.0.0.1 -u root --table < file`. Failure observations:
- Step 3: `ROW_COUNT()` is 1 instead of 3, `@@warning_count` is 0, and the table
holds `(1,10) (2,20) (3,11)` instead of `(1,11) (2,21) (3,31)`.
- Step 4: the `DELETE` removes only `id=3` instead of emptying the table.
- Step 2 shows the read path computing the correct row set and values under the
same settings.
The defect scales with the table: with six rows 1..6 in `a` and a matching
six-row table, the same `UPDATE ... ORDER BY id` reports 1 affected row and
produces `(1,10) (2,20) (3,30) (4,40) (5,50) (6,11)` — five matching rows skipped,
and the last row again receiving the first row's `v+1`.
Trigger boundary (every row measured on 9.7.1). Triggers the bug, all producing
"1 row affected" and the table state `(1,10),(2,20),(3,11)`:
| Statement shape | Result |
|---|---|
| `WHERE EXISTS (...)` + `ORDER BY id` | 1 affected, expected 3 |
| `WHERE NOT EXISTS (...)` + `ORDER BY id` (hash antijoin) | 1 affected, expected 3 |
| `WHERE id IN (SELECT ...)` + `ORDER BY id` | 1 affected, expected 3 |
| `ORDER BY v` (non-indexed column) | 1 affected, expected 3 |
| `ORDER BY id LIMIT 2` | 1 affected, expected 2 — and it modifies `id=3`, which is outside the LIMIT window |
| `DELETE ... ORDER BY id` | 1 affected, expected 3 |
Both InnoDB and MyISAM are affected. Does not trigger:
| Statement shape | Result | Plan |
|---|---|---|
| subquery, **no** `ORDER BY` | 3 affected, correct | `Update a (buffered)` + `Hash semijoin`, no `Sort row IDs` |
| `ORDER BY`, **no** subquery | 3 affected, correct | `Update a (immediate)` |
| scalar subquery only in the `SET` clause | correct | no semijoin |
| multi-table `UPDATE`/`DELETE` | n/a | `ORDER BY` not permitted syntactically |
| `hypergraph_optimizer=off` (default) | 3 affected, correct | traditional path |
The discriminator is the **plan shape, not the statement shape**: the result is
wrong exactly when the plan contains `Update/Delete ... (buffered)` together with
`Sort row IDs` and `Hash semijoin`/`Hash antijoin`. Note that adding a secondary
index can make the optimizer pick `Update a (immediate)` instead and thereby hide
the symptom on that particular statement; that masks the bug, it does not fix it.
Suggested fix:
Plan / evidence:
`EXPLAIN ANALYZE` of the failing `UPDATE` in step 5:
```
-> Update a (buffered) (cost=4.29..4.46 rows=1.73) (actual time=0.0235..0.0235 rows=0 loops=1)
-> Sort row IDs: a.id (cost=4.06..4.06 rows=1.73) (actual time=0.0208..0.0226 rows=3 loops=1)
-> Hash semijoin (FirstMatch) (a.id = b.id) (cost=2.76..3.4 rows=1.73) (actual time=0.0119..0.013 rows=3 loops=1)
-> Index scan on a using PRIMARY (cost=0.359..1.08 rows=3) (actual time=0.00196..0.00275 rows=3 loops=1)
-> Hash
-> Covering index scan on b using PRIMARY (cost=0.295..0.885 rows=3) (actual time=0.00467..0.00617 rows=3 loops=1)
```
`Sort row IDs` reports `actual rows=3`, so all three qualifying rows do reach the
sort node, yet only one row is written. The `DELETE` produces the structurally
identical plan with `Delete from a (buffered)` on top. Removing the `ORDER BY`
removes the `Sort row IDs` node — the plan is then `Update a (buffered)` directly
above `Hash semijoin` — and the statement is correct, which is what isolates the
sort node as the point of failure.
Hypothesis:
We suspect that `Sort row IDs` stores handler row IDs (`table->file->ref`) rather
than full rows, and that the hash semijoin's row buffer, when replaying a buffered
row, restores the column values but not the handler's row position. If so, all
three entries in the sort buffer would carry whichever row ID the handler happened
to hold last (the last row scanned), the buffered update would collapse them into
a single write, and the value written would come from the first record of the sort
sequence. That would account for all three observed symptoms at once: one write
instead of three, the write landing on the last row, and the written value
originating from the first row. This is an inference from the plan and the
observed results; we have not confirmed it against the source.
