SQLite
View Ticket
Not logged in
Ticket UUID: 2df0107bd268f4d72ae4c258010ef810ec2211a4
Title: Incorrect result from LEFT JOIN with a subquery on the LHS
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-12-01 23:37:24
Version Found In: 3.15.2
User Comments:
drh added on 2016-12-01 19:11:12:

The result of the first SELECT in the SQL below is correct. But in the second version, where the left-hand side of the LEFT JOIN is coming from an equivalent subquery, no rows are returned.

CREATE TABLE x1(x,y,z);
INSERT INTO x1 VALUES(0,0,1);
CREATE TABLE x2(a);
SELECT avg(z) FROM x1 LEFT JOIN x2 ON x GROUP BY y;
SELECT avg(z) FROM (SELECT * FROM x1) LEFT JOIN x2 ON x GROUP BY y;

This problem has existed in the SQLite query planner for over 10 years, since before version 3.3.0 (2006-01-11).


drh added on 2016-12-01 19:34:06:

Another test case that does not involve an aggregate query:

CREATE TABLE x1(x,y,z);
INSERT INTO x1 VALUES(0,0,1);
CREATE TABLE x2(a);
SELECT z FROM (SELECT * FROM x1) LEFT JOIN x2 ON x;

One row of result should be returned above, but all versions of SQLite from 3.15.2 back to 3.3.0 (and likely before that too) return no rows. Except, when the query flattener optimization is turned off using the sqlite3_test_control() interface, the correct answer is obtained in all cases.