SQLite

Ticket Change Details
Login
Overview

Artifact ID: 4776331e7fafe22dc1aaffd1b94308fc35d2c229cff5bf7c4834062f2e90b969
Ticket: ce68383bf6aba2f8e2b0344d067364e22daf59dc
Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index
User & Date: drh 2017-07-18 16:57:15
Changes

  1. foundin changed to: "3.19.3"
  2. icomment:
    The same LEFT JOIN query gives different results depending on whether or
    not automatic indexes are enabled.  The code below illustrates:
    
    <blockquote><verbatim>
    CREATE TABLE t1(w int);
    CREATE TABLE t2(y int);
    INSERT INTO t1 VALUES(NULL);
    INSERT INTO t2 VALUES(1);
    CREATE INDEX t2y ON t2(y);
    SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w;
    PRAGMA automatic_index=off;
    SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w;
    </verbatim></blockquote>
    
    Both queries should return no rows.  But when automatic indexes are turned on
    (the default) a single row of two NULLs is returned.
    
    Bisecting shows this problem was introduced by check-in [3428043cd0029445]
    
    This problem was discovered during internal testing and analysis and has
    never been reported in the wild.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Important"
  6. status changed to: "Open"
  7. title changed to:
    Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index
    
  8. type changed to: "Code_Defect"