Thanks for the bug report. The problem is now fixed on trunk. On 6/18/15, Jean Chevalier <jchevalier at gmx.net> wrote: > The likelihood() function, which should help select a query plan but > otherwise be logic-neutral, nevertheless seems to affect results returned by > a Left Join, when used as part of the join condition, possibly in other > circumstances. > > With the following sample data, the two SELECT queries should return the > same data, however, they don't: the second query returns a rowcount that > would correspond to that of an inner join: > > drop table if exists Tbl; create temp table Tbl (i int, x, y, z); insert > into Tbl values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4); > > drop table if exists Lkp; create temp table Lkp (i int, bool char); insert > into Lkp values (1,'T'), (2,'F'); > > select count(*) from Tbl left join Lkp on Tbl.i = Lkp.i and Lkp.bool = 'T'; > > select count(*) from Tbl left join Lkp on likelihood(Tbl.i = Lkp.i, 0.5) and > Lkp.bool = 'T'; > _______________________________________________ > 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