SQLite
View Ticket
Not logged in
Ticket UUID: 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: (text/x-fossil-wiki)
The following query returns a single row rather than two rows as it should:

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

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