/ Ticket Change Details
Login
Overview

Artifact ID: 00f210e18cf7332717046c4ce88f64490795e7a0304598361e416a4e00c98237
Ticket: ef9318757b152e3a26e95923e8fe8c178a7e629c
Incorrect result due to the skip-ahead-distinct optimization
User & Date: drh 2017-11-21 15:19:27
Changes

  1. Change foundin to "3.21.0"
  2. Change icomment to:

    The query in the SQL shown below gives an incorrect answer after ANALYZE has been run:

    CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
    CREATE INDEX t1b ON t1(b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
    CREATE INDEX t2y ON t2(y);
    WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
      INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
    WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
      INSERT INTO t2(x,y) SELECT x, 1 FROM c;
    SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
    ANALYZE;
    SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
    

    This problem seems to have been introduced by the skip-ahead-distinct optimization of check-in [f489b5bb6b35665], first included in SQLite version 3.19.0. The problem was reported on the public mailing list by David Raymond.

  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 due to the skip-ahead-distinct optimization

  8. Change type to "Code_Defect"