SQLite

Ticket Change Details
Login
Overview

Artifact ID: e3b3158ed3749774c76a54c06200b2a7dc33e85b
Ticket: 96c1454cbfd9509a1808b686ed11138715164869
Incorrect result with ORDER BY DESC and LIMIT (again)
User & Date: drh 2016-10-12 14:00:28
Changes

  1. foundin changed to: "3.14.2"
  2. icomment:
    The second query in the following SQL returns an incorrect result:
    
    <blockquote><verbatim>
    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;
    </verbatim></blockquote>
    
    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.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to:
    Incorrect result with ORDER BY DESC and LIMIT (again)
    
  8. type changed to: "Code_Defect"