[sqlite] Likelihood() in Left Join affecting results

[R.Smith]

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