/ View Ticket
Login
2017-06-20
18:13 Fixed ticket [892fc34f]: Incorrect query result when a LEFT JOIN subquery is flattened plus 5 other changes (artifact: 879fcac8 user: drh)
17:43
Ensure that the query planner knows that any column of a flattened LEFT JOIN can be NULL even if that column is labeled with "NOT NULL". Fix for ticket [892fc34f173e99d8]. (check-in: 48346268 user: dan tags: trunk)
16:15 New ticket [892fc34f] Incorrect query result when a LEFT JOIN subquery is flattened. (artifact: d28d6544 user: drh)

Ticket UUID: 892fc34f173e99d8aa03d52fbf42e0f9f2dfb210
Title: Incorrect query result when a LEFT JOIN subquery is flattened
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-06-20 18:13:44
Version Found In: 3.19.3
User Comments:
drh added on 2017-06-20 16:15:03:

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".

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;

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.