/ View Ticket
Login
2017-01-10
17:46 Fixed ticket [25e335f8]: Incorrect result when ON clause refers to table to the right plus 4 other changes (artifact: 70ac38b2 user: drh)
17:37
Add a test case for ticket [25e335f802dd]. (check-in: e500c15a user: dan tags: trunk)
17:33
Throw an error if the ON clause of a LEFT JOIN references tables to the right of the ON clause. Fix for ticket [25e335f802dd]. (check-in: c92ecff2 user: drh tags: trunk)
17:19 Ticket [25e335f8] Incorrect result when ON clause refers to table to the right status still Open with 6 other changes (artifact: b5f4cd32 user: drh)
17:15 New ticket [25e335f8]. (artifact: 464fffdc user: drh)

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:

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.