Incorrect update caused by subquery in where clause
(1) By Zuming Jiang (zuming_jiang) on 2023-03-15 20:01:49 [source]
Set up the database
CREATE TABLE t1 (vkey INTEGER, c5 INTEGER);
INSERT INTO t1 VALUES(3,NULL);
INSERT INTO t1 VALUES(6,-54);
Test Case 1
update t1 set vkey = 100
where t1.c5 is null;
--- update {3|} to {100|}
It updates 1 row {3|} to {100|}.
Test Case 2
update t1 set vkey = 100
where not (((select min(vkey) from t1) * (-10)) >= t1.c5);
--- update 0 rows:
It updates nothing.
Then I combine the predicates (WHERE clauses) of Test Case 1 and Test Case 2 using or operation, the test case becomes:
Test Case 3
update t1 set vkey = 100
where (t1.c5 is null)
or
(not (((select min(vkey) from t1) * (-10)) >= t1.c5));
--- update 2 rows: {3|} to {100|}, {6|-54} to {100|-54}
Test Case 3 should not update the row {6|-54} because none of Test Case 1 and Test Case 2 update it.
However, Test Case 3 update the row {6|-54} to {100|-54}
I tested it on the trunk (commit: ffcad58)
(2) By Richard Hipp (drh) on 2023-03-16 10:32:30 in reply to 1 [link] [source]
This is, of course, the same problem as described by forum post e61252062c9d286d, only for UPDATE instead of DELETE. The subquery in the WHERE clause is behind a short-circuit operator and hence its evaluation gets delayed until after one or more rows have already been updated, thus changing the value computed by the subquery and thereby getting the wrong answer. The solution is to disable the one-pass optimization when there is a subquery in the WHERE clause.
Background: The one-pass optimization is a mechanism that tries to do an UPDATE (or a DELETE) in a single pass through the table. Without one-pass, an UPDATE (or DELETE) needs to first compute the set of all rows that ought to change, then go back and make the appropriate changes in a separate pass. Where it can be used, one-pass helps UPDATEs and DELETEs run faster by collapsing the detection of which rows ought to change and the actual changing of those rows into a single loop.
A fix has now been checked into both trunk and branch-3.41.
(3) By Zuming Jiang (zuming_jiang) on 2023-03-16 11:20:37 in reply to 2 [link] [source]
Thank you for the response and for fixing it!
(4) By TinaCurtis on 2023-04-14 06:55:27 in reply to 2 [link] [source]
I am glad to know it is fixed.