SQLite
View Ticket
Not logged in
Ticket UUID: a306e56ff68b8fa56d91c5b804bf57983b8b8e94
Title: Failed DELETE with PRAGMA reverse_unordered_selects
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-04-09 17:20:31
Version Found In: 3.12.1
User Comments:
drh added on 2016-04-09 16:31:11: (text/x-fossil-wiki)
The DELETE operation on the penultimate line of the script below fails to
delete every row for which b==2, as shown by the SELECT on the last line:

<blockquote><verbatim>
PRAGMA page_size=1024;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
CREATE INDEX x1 ON t1(b, c);
INSERT INTO t1(a,b,c) VALUES(1, 1, zeroblob(80));
INSERT INTO t1(a,b,c) SELECT a+1, 1, c FROM t1;
INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1;
INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1;
INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1;
PRAGMA reverse_unordered_selects = ON;
DELETE FROM t1 WHERE b=2;
SELECT a FROM t1 WHERE b=2;
</verbatim></blockquote>

The underlying cause of this malfunction appears to be a defect in the
OP_Prev opcode of the VDBE.  However, we have so far been unable to cause
incorrect behavior except by using reverse_unordered_selects and the
multi-row on-pass delete optimization that was introduced in version 3.9.0
(check-in [8b93cc5937]).