SQLite
View Ticket
Not logged in
Ticket UUID: 25e335f802ddc1ac3a44b5e9d1437c33a035f82a
Title: Incorrect result when ON clause refers to table to the right
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-01-10 17:46:18
Version Found In: 3.16.2
User Comments:
drh added on 2017-01-10 17:15:29: (text/x-fossil-wiki)
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.

<blockquote><verbatim>
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;
</verbatim></blockquote>

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: (text/x-fossil-wiki)
PostgreSQL throws an error.  Another option is to treat the query as if
it were the following:

<blockquote><verbatim>
SELECT * FROM aaa LEFT JOIN (ccc INNER JOIN bbb) ON a=b AND c=b;
</verbatim></blockquote>

drh added on 2017-01-10 17:46:18: (text/x-fossil-wiki)
The fix is to make SQLite work like PostgreSQL and throw an error.