Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4, 11.8, 12.3, 13.1, 11.8.9, 12.3.3, 13.1.1
-
None
-
Official Docker images, x86_64, default configuration
-
Not for ES Release Notes
Description
A multi-table UPDATE whose SET expression reads a column updated by the same statement gives a different result when the table has a STORED generated column that does not depend on any column used in the statement.
CREATE TABLE t3 (a INT, b INT, c INT, s INT AS (c + 1) STORED); |
CREATE TABLE t4 (a INT PRIMARY KEY, b INT); |
INSERT INTO t3 (a, b, c) VALUES (2, 2, 0), (3, 3, 0), (4, 4, 0); |
INSERT INTO t4 VALUES (2, 0), (3, 0), (4, 0); |
UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10 WHERE t4.a = t3.b; |
SELECT b FROM t4; |
-- control: same table without s
|
ALTER TABLE t3 DROP COLUMN s; |
UPDATE t3 SET a = a - 100; |
UPDATE t4 SET b = 0; |
UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10 WHERE t4.a = t3.b; |
SELECT b FROM t4; |
with s: 112, 113, 114
|
without s: 12, 13, 14
|
Expected: 12, 13, 14, the same as without s. An unrelated STORED column should not change the result.
This is not explained by the unspecified assignment order of multi-table updates. With SIMULTANEOUS_ASSIGNMENT, for which the document(UPDATE) says "UPDATE statements evaluate all assignments simultaneously", the results are the same:
SET sql_mode = CONCAT(@@sql_mode, ',SIMULTANEOUS_ASSIGNMENT'); |
-- then the statements above |
with s: 112, 113, 114
|
without s: 12, 13, 14
|
With simultaneous evaluation, t4.b = t3.a + 10 must read the old t3.a, so only 12, 13, 14 is correct.
Attachments
Issue Links
- is caused by
-
-
- Closed
-