SQLite: View Ticket

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.