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

    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.

    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;
    

    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. 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:

    Index on expression leads to an incorrect LEFT JOIN

  8. Change type to "Code_Defect"