|14:20||• Fixed ticket [f2369304]: Incorrect results when OR is used in the ON clause of a LEFT JOIN plus 5 other changes (artifact: 4712b561 user: drh)|
|14:20||Do not move WHERE clause terms inside OR expressions that are contained within an ON clause of a LEFT JOIN. Fix for ticket [f2369304e47167e3e]. (check-in: 1128575d user: drh tags: trunk)|
|13:38||• New ticket [f2369304] Incorrect results when OR is used in the ON clause of a LEFT JOIN. (artifact: 979fa9ab user: drh)|
|Title:||Incorrect results when OR is used in the ON clause of a LEFT JOIN|
|Last Modified:||2013-05-09 14:20:19|
|Version Found In:||22.214.171.124|
drh added on 2013-05-09 13:38:23:
The following SQL outputs a single row when it should output zero rows:
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;
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.