/ Ticket Change Details
Login
Overview

Artifact ID: da421d67abad684a48fb74c92457996af48054634dfbb234990c385433103abc
Ticket: 123c9ba32130a6c9d43278229da12a076583005d
Incorrect result when an index is used for an ordered join
User & Date: drh 2017-12-13 16:33:55
Changes

  1. foundin changed to: "3.21.0"
  2. icomment:
    The query in the SQL below gets a different answer after the index is
    created.  The answer without the index is correct.
    
    <blockquote><verbatim>
    CREATE TABLE t1(a, b);  INSERT INTO t1 VALUES(1,2);
    CREATE TABLE t2(x, y);  INSERT INTO t2 VALUES(1,3);
    SELECT y FROM t1, t2 WHERE a=x AND b<=y ORDER BY b DESC;
    CREATE INDEX t1ab ON t1(a,b);
    SELECT y FROM t1, t2 WHERE a=x AND b<=y ORDER BY b DESC;
    </verbatim></blockquote>
    
    This problem appears to have been introduced by check-in [559733b09e] on
    2016-05-20, 572 days ago.  The problem first appeared in version 3.14.0.
    The problem was discovered in the wild and reported to the SQLite developers
    via private email.
    
  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 when an index is used for an ordered join
    
  8. type changed to: "Code_Defect"