/ Ticket Change Details
Login
Overview

Artifact ID: 979fa9ab39313bf3968abb91ecaacfca9de1d847
Ticket: f2369304e47167e3e644e2f1fe9736063391d7b7
Incorrect results when OR is used in the ON clause of a LEFT JOIN
User & Date: drh 2013-05-09 13:38:23
Changes

  1. foundin changed to: "3.7.16.2"
  2. icomment:
    The following SQL outputs a single row when it should output zero rows:
    
    <blockquote><verbatim>
    CREATE TABLE t1(x); INSERT INTO t1 VALUES(1);
    CREATE TABLE t2(y INTEGER PRIMARY KEY,a,b);
    INSERT INTO t2 VALUES(1,2,3);
    SELECT * FROM t1 LEFT JOIN t2 ON a=2 OR b=3 WHERE y IS NULL;
    </verbatim></blockquote>
    
    The result of the above is "1,NULL,NULL,NULL".  It is behaving
    as if the "y IS NULL" term of the WHERE clause has been moved inside 
    the ON clause of the LEFT JOIN.  This effect is only seen when there
    is an OR operator inside the ON clause.
    
    Bisecting reveals that this problem
    was introduced by on 2011-10-07 by check-in [b23ae131874bc5c6] and
    first appeared in release 3.7.9.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to:
    Incorrect results when OR is used in the ON clause of a LEFT JOIN
    
  8. type changed to: "Code_Defect"