Overview
Artifact ID: | d28d654485803572fd084ed1b7ae92bf843af5e343e7af81db7681c4aa72100d |
---|---|
Ticket: | 892fc34f173e99d8aa03d52fbf42e0f9f2dfb210
Incorrect query result when a LEFT JOIN subquery is flattened |
User & Date: | drh 2017-06-20 16:15:03 |
Changes
- foundin changed to: "3.19.3"
- icomment:
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.
- login: "drh"
- mimetype: "text/x-fossil-wiki"
- severity changed to: "Severe"
- status changed to: "Open"
- title changed to:
Incorrect query result when a LEFT JOIN subquery is flattened
- type changed to: "Code_Defect"