/ 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. Change foundin to "3.21.0"
  2. Change icomment to:

    The query in the SQL below gets a different answer after the index is created. The answer without the index is correct.

    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;
    

    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. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Severe"
  6. Change status to "Open"
  7. Change title to:

    Incorrect result when an index is used for an ordered join

  8. Change type to "Code_Defect"