(text/x-fossil-wiki)
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.
|