SQLite
View Ticket
Not logged in
Ticket UUID: dc6ebeda939608770b156cbbb5a1c15e1ca97001
Title: Incorrect DELETE due to the one-pass optimization
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-05-02 13:46:58
Version Found In: 3.12.2
User Comments:
drh added on 2016-05-02 12:00:40:

The DELETE statement on the third line of the example below only deletes a single row, rather than two rows as expected. The correct behavior, confirmed by running on PostgreSQL, is to delete two rows, such that the final answer is a single row with value "1".

CREATE TABLE t1(x INT);
INSERT INTO t1 VALUES(1),(2),(3);
DELETE FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS v WHERE v.x=t1.x-1);
SELECT * FROM t1;

Bisecting shows that this problem results from the one-pass optimization at check-in [8b93cc59370]. The problem was reported on the sqlite-users mailing list by Rob Golsteijn.


drh added on 2016-05-02 13:46:58:

Versions affected by this bug: 3.9.0 through 3.12.2.