SQLite

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

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.