SQLite

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.