(text/x-fossil-wiki)
The following SQL generates a single row of output where it should generate
an empty set:
<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY);
INSERT INTO t1(a) VALUES(1),(2);
CREATE TABLE t2(b INTEGER PRIMARY KEY);
INSERT INTO t2(b) VALUES(1);
SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
</verbatim></blockquote>
Apparently, the subquery "(SELECT a FROM t2)" is being compiled as if it
where "(SELECT b FROM t2)". This problem appears to go back to before
version 3.6.1, circa 2008.
Problem reported at
[http://stackoverflow.com/questions/35891605/why-do-i-receive-an-unexpected-result-set-in-sqlite-when-excluding-rows-via-sub|stackoverflow]
and relayed to the sqlite-users mailing list by Clemens Ladisch.
|