/ Ticket Change Details
Login
Overview

Artifact ID: 5400ddb5c1397eb5047abda43b19db21bf962d38f4202a42b027ed99270a724a
Ticket: 5948e09b8c415bc45da5cfc75c8eb327bbb53886
Incorrect result from LEFT JOIN
User & Date: drh 2019-02-05 13:55:55
Changes

  1. foundin changed to: "3.26.0"
  2. icomment:
    The query at the end of the following SQL returns no rows, but it should return
    one row.  The correct answer (one row) is confirmed by running the same
    SQL against PostgreSQL.
    
    <blockquote><verbatim>
    CREATE TABLE t1(a INT);
    INSERT INTO t1(a) VALUES(1);
    CREATE TABLE t2(b INT);
    SELECT a, b
      FROM t1 LEFT JOIN t2 ON true
     WHERE (b IS NOT NULL) IS false;
    </verbatim></blockquote>
    
    Another manifestation of the problem which runs on historical versions
    of SQLite but which does not work on PostgreSQL is this:
    
    <blockquote><verbatim>
    CREATE TABLE t1(a INT);
    INSERT INTO t1(a) VALUES(1);
    CREATE TABLE t2(b INT);
    SELECT a, b
      FROM t1 LEFT JOIN t2 ON 0
     WHERE (b IS NOT NULL)=0;
    </verbatim></blockquote>
    
    This problem first appeared with check-in [dd568c27b1d76563] - the
    LEFT JOIN strength reduction optimization - and first appeared in
    release 3.23.0 (2018-04-02).  The problem was reported
    on the SQLite users mailing list.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to: "Incorrect result from LEFT JOIN"
  8. type changed to: "Code_Defect"