(text/x-fossil-wiki)
Consider the following test case:
<pre>
CREATE TABLE t0(c0);
CREATE TABLE t1(c0);
CREATE INDEX i0 ON t0(1) WHERE c0 ISNULL;
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t1(c0) VALUES (1);
SELECT * FROM t1 LEFT JOIN t0 WHERE t0.c0 ISNULL; -- unexpected: row is fetched
</pre>
Unexpectedly, a row is fetched. It is not fetched when the index is omitted and the following statement indicates that t0.c0 ISNULL should evaluate to false:
<pre>
SELECT t0.c0 ISNULL FROM t1 LEFT JOIN t0; -- 0
</pre>
|