SQLite

View Ticket
Login
Ticket Hash: 96c1454cbfd9509a1808b686ed11138715164869
Title: Incorrect result with ORDER BY DESC and LIMIT (again)
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-10-12 15:35:04
Version Found In: 3.14.2
User Comments:
drh added on 2016-10-12 14:00:28:

The second query in the following SQL returns an incorrect result:

CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT);
INSERT INTO t1(x,y) VALUES(1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
CREATE TABLE t2(z INT);
INSERT INTO t2(z) VALUES(1),(2),(3),(4),(5),(6),(7);
SELECT 1,x,y FROM t1 WHERE x IN (SELECT z FROM t2) ORDER BY y DESC;
SELECT 2,x,y FROM t1 WHERE x IN (SELECT z FROM t2) ORDER BY y DESC LIMIT 3;

This problem first appeared with the ORDER BY LIMIT optimization of check-in [559733b09e9630fa] on 2016-05-20 and first appeared in the 3.14.0 release. The problem was reported on the mailing list by Keith Medcalf.