Thanks for the test case. The error seems to have been introduced by check-in https://www.sqlite.org/src/info/6df18e949d367629 which does some aggressive transformations on nested queries for improved performance.
On 8/21/15, Mark Brand <mabrand at mabrand.nl> wrote: > Hi, > > For the query below, versions 3.8.11.0 and 3.8.11.1 return only 1 row. > For some reason, the LEFT JOIN seems to behave like a JOIN. > > Older versions (tested 3.8.7.4 and 3.8.10.2) correctly return 2 rows. > > 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' > ; > > regards, > > Mark > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org