/ Ticket Change Details
Login
Overview

Artifact ID: e5a141f80639276d87deee614753354cdd7fd85587abeb3da7fe1262b4d01901
Ticket: 4ba5abf65c5b0f9a96a7a40cd18b854478d40ced
Index on expression leads to an incorrect LEFT JOIN
User & Date: drh 2018-04-24 14:30:17
Changes

  1. foundin changed to: "3.23.1"
  2. icomment:
    The following SQL demonstrates how an index on an expression that can be
    non-NULL even if all its arguments are NULL can lead to an incorrect answer
    in a LEFT JOIN.
    
    <blockquote><verbatim>
    CREATE TABLE t1(x);
    CREATE TABLE t2(y, z);
    INSERT INTO t1 VALUES('key');
    INSERT INTO t2 VALUES('key', -1);
    SELECT count(*) FROM t1 LEFT JOIN t2 ON (x=y) WHERE ifnull(z, 0) >=0;
    CREATE INDEX t2i ON t2(y, ifnull(z, 0));
    SELECT count(*) FROM t1 LEFT JOIN t2 ON (x=y) WHERE ifnull(z, 0) >=0;
    </verbatim></blockquote>
    
    This problem has been in the code ever since support for indexes on
    expression was added by check-in [2131a5ca53f0e9b0b98a9dd9a20e495]
    on 2015-09-04.  The problem first appeared in the 3.9.0 release.
    
    This problem was discovered during internal testing and analysis
    and has not been observed in the wild.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to:
    Index on expression leads to an incorrect LEFT JOIN
    
  8. type changed to: "Code_Defect"