/ Ticket Change Details
Login
Overview

Artifact ID: 7aedf2b61aaf31a427c8b1dcc2929d9d80572333
Ticket: b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4
Incorrect result from LEFT JOIN with OR in the WHERE clause
User & Date: drh 2012-03-09 16:58:06
Changes

  1. comment changed to:
    The following SQL demonstrates the problem:
    
    <blockquote><pre>
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
    CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
    CREATE TABLE t3(p INTEGER PRIMARY KEY, q);
    INSERT INTO t1 VALUES(2,3,4,5);
    INSERT INTO t1 VALUES(3,4,5,6);
    INSERT INTO t2 VALUES(2,4);
    INSERT INTO t3 VALUES(5,55);<p>
    SELECT *
      FROM t1 LEFT JOIN t2 ON y=b JOIN t3
     WHERE c==p OR d==p;
    </pre></blockquote>
    
    Prior to check-in [b23ae131874bc5c621f0] the query returns two rows.  But
    following that check-in, only one row is returned.  The two-row result is
    correct.
    
    This problem was discovered by Alan Chandler and reported on the mailing
    list as "Strange difference between sqlite 3.7.3 and 3.7.10".
    
  2. Untracked field detected: "Application_Fault"
  3. foundin changed to: "3.7.10"
  4. severity changed to: "Severe"
  5. status changed to: "Open"
  6. subsystem changed to: "Unknown"
  7. title changed to:
    Incorrect result from LEFT JOIN with OR in the WHERE clause
    
  8. type changed to: "Code_Defect"