(text/x-fossil-wiki)
The same LEFT JOIN query gives different results depending on whether or
not automatic indexes are enabled. The code below illustrates:
<blockquote><verbatim>
CREATE TABLE t1(w int);
CREATE TABLE t2(y int);
INSERT INTO t1 VALUES(NULL);
INSERT INTO t2 VALUES(1);
CREATE INDEX t2y ON t2(y);
SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w;
PRAGMA automatic_index=off;
SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w;
</verbatim></blockquote>
Both queries should return no rows. But when automatic indexes are turned on
(the default) a single row of two NULLs is returned.
Bisecting shows this problem was introduced by check-in [3428043cd0029445]
This problem was discovered during internal testing and analysis and has
never been reported in the wild.
|