SQLite
View Ticket
Not logged in
Ticket UUID: b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4
Title: Incorrect result from LEFT JOIN with OR in the WHERE clause
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2012-03-13 14:10:35
Version Found In: 3.7.10
Description:
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".