(text/x-fossil-wiki)
The following SQL returns a different result in SQLite version 3.19.3 compared
to SQLite version 3.18.0 and PostgreSQL. The result is also different if
automatic indexes are disabled using "PRAGMA automatic_index=OFF".
<blockquote><verbatim>
CREATE TABLE t1(id INTEGER PRIMARY KEY);
CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
INSERT INTO t1(id) VALUES(456);
INSERT INTO t3(id) VALUES(1),(2);
SELECT t1.id, x2.id, x3.id
FROM t1
LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
</verbatim></blockquote>
This problem was introduced by the LEFT JOIN subquery flattener optimization
of check-in [41c27bc0ff] (which is also the cause of other bugs such as
[cad1ab4cb7b0] and [7fde638e94287d]). The problem first appeared in the
3.19.0 release (2017-05-22).
This problem was reported on the SQLite users mailing list by Eric Sink.
|