CTE in UPDATE re-evaluated when AFTER trigger fires, producing wrong results

[LeMikaelF]

Description

When an AFTER UPDATE trigger fires during a CTE-based UPDATE, the CTE is re-evaluated against the now-modified table state instead of using the originally-materialized snapshot. The trigger need not even touch the source table — merely firing any trigger causes the re-evaluation.

Reproducer

CREATE TABLE t(id INT PRIMARY KEY, val INT);
CREATE TABLE log(x TEXT);
CREATE TRIGGER tr AFTER UPDATE ON t BEGIN INSERT INTO log VALUES('fired'); END;
INSERT INTO t VALUES(1,10),(2,20),(3,30);

WITH c AS (SELECT id, val * 2 as doubled FROM t)
UPDATE t SET val = (SELECT doubled FROM c WHERE c.id = t.id) WHERE id <= 2;

SELECT * FROM t ORDER BY id;
-- Turso:  1|40, 2|40, 3|30  (id=1 WRONG: 10*2=20, but gets 40 from re-evaluation after row 1 updated to 20)
-- SQLite: 1|20, 2|40, 3|30

Without the trigger, the CTE UPDATE produces correct results. CTEs should be materialized once before the DML executes. The trigger's firing invalidates or resets the CTE's coroutine/cursor, causing re-execution against modified table state.

---

This issue brought to you by Mikaël and Claude Code.

[turso-github-handyman]

Fix is ready for review: compare with main