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

    The same LEFT JOIN query gives different results depending on whether or not automatic indexes are enabled. The code below illustrates:

    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;
    

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

    Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index

  8. Change type to "Code_Defect"