(text/x-fossil-wiki)
Consider the test case below:
<pre>
CREATE TABLE t0(c0 TEXT, c1);
INSERT INTO t0(c0, c1) VALUES (-1, 0);
CREATE VIEW v0(c0, c1) AS SELECT t0.c0, AVG(t0.c1) FROM t0;
SELECT * FROM v0 WHERE v0.c1 < v0.c0; -- unexpected: row is not fetched
</pre>
Unexpectedly, the row is not fetched. Interestingly, but as expected, the row is also not fetched when negating the WHERE condition:
<pre>
SELECT * FROM v0 WHERE NOT(v0.c1 < v0.c0); -- expected: -1|0.0, actual: row is not fetched
</pre>
Evaluating the condition yields TRUE:
<pre>
SELECT v0.c1 < v0.c0 FROM v0; -- 1
</pre>
|