|Title:||Incorrect result when ON clause refers to table to the right|
|Last Modified:||2017-01-10 17:46:18|
|Version Found In:||3.16.2|
drh added on 2017-01-10 17:15:29:
In the SELECT on the last line of the SQL below, the ON clause of the LEFT JOIN refers to a column in the bbb table which is further to the right of the ON clause. This causes the LEFT JOIN to behave as an INNER JOIN in the current implementation. PostgreSQL throws an error.
CREATE TABLE aaa (a INTEGER); INSERT INTO aaa VALUES (1); INSERT INTO aaa VALUES (2); CREATE TABLE bbb (b INTEGER); INSERT INTO bbb VALUES (1); INSERT INTO bbb VALUES (2); CREATE TABLE ccc (c INTEGER); INSERT INTO ccc VALUES (2); SELECT * FROM aaa LEFT JOIN ccc ON c=bbb.b CROSS JOIN bbb ON a=b;
This behavior has been a part of SQLite since before version 3.0.0 (2004-06-18).
drh added on 2017-01-10 17:19:22:
PostgreSQL throws an error. Another option is to treat the query as if it were the following:
SELECT * FROM aaa LEFT JOIN (ccc INNER JOIN bbb) ON a=b AND c=b;
drh added on 2017-01-10 17:46:18:
The fix is to make SQLite work like PostgreSQL and throw an error.