SQLite

View Ticket
Login
Plaintext
2017-07-17
19:14
Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. (check-in: 52674f94 user: drh tags: push-down-backport)
2015-08-22
01:32 • Fixed ticket [c2a19d81]: Incorrect LEFT JOIN when FROM clause contains nested subqueries plus 5 other changes (artifact: 05ca3244 user: drh)
01:32
Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. (check-in: 351bc22f user: drh tags: trunk)
2015-08-21
23:25 • New ticket [c2a19d81] Incorrect LEFT JOIN when FROM clause contains nested subqueries. (artifact: 7b645a97 user: drh)
Ticket Hash: c2a19d81652f40568c770c43ae2c2fbe67ef6679
Title: Incorrect LEFT JOIN when FROM clause contains nested subqueries
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-08-22 01:32:45
Version Found In: 3.8.11.1
User Comments:
drh added on 2015-08-21 23:25:40:

The following query returns a single row rather than two rows as it should: 

SELECT *
FROM (
    SELECT 'apple' fruit
    UNION ALL SELECT 'banana'
) a
JOIN (
    SELECT 'apple' fruit
    UNION ALL SELECT 'banana'
) b ON a.fruit=b.fruit
LEFT JOIN (
    SELECT 1 isyellow
) c ON b.fruit='banana';

The error seems to have been introduced by check-in [6df18e949d367629] on 2015-06-02 and first appeared in version 3.8.11.