On 2015-06-18 01:04 PM, Jean Chevalier 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.
To add: I thought this might be a peculiarity with the Count() aggregate function since the OP posted an example using count(), but it is in fact perfectly replicated in any Select, here is a script to demonstrate: -- Test Script for Liklihood() 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 * from Tbl left join Lkp on Tbl.i = Lkp.i and Lkp.bool = 'T'; -- i | x | y | z | i | bool -- ------------ | ------------ | ------------ | ------------ | ------ | ------ -- 1 | 1 | 1 | 1 | 1 | T -- 2 | 2 | 2 | 2 | Null | Null -- 3 | 3 | 3 | 3 | Null | Null -- 4 | 4 | 4 | 4 | Null | Null select count(*) from Tbl left join Lkp on Tbl.i = Lkp.i and Lkp.bool = 'T'; -- count(*) -- ------------ -- 4 select * from Tbl left join Lkp on likelihood(Tbl.i = Lkp.i, 0.5) and Lkp.bool = 'T'; -- i | x | y | z | i | bool -- ------------ | ------------ | ------------ | ------------ | ------------ | ----- -- 1 | 1 | 1 | 1 | 1 | T select count(*) from Tbl left join Lkp on likelihood(Tbl.i = Lkp.i, 0.5) and Lkp.bool = 'T'; -- count(*) -- ------------ -- 1 > > 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