System.Data.SQLite

If the left join clause, if other table is referenced, query returns completely wrong data set. Its difficult to explain in words, so let me write the sql which reproduces the problem:

-- create table with an index
create table t (n int, m int, s varchar);
create index tx on t(m);

--add some data to table
WITH RECURSIVE
  cnt(x) AS (
     SELECT 1
     UNION ALL
     SELECT x+1 FROM cnt 
     LIMIT 100000
  )
insert into t(n, s) SELECT x, cast(x as text) FROM cnt;
update t set m = n where (n % 1000)=0;

--problematic query
select count(*)
from t t1
left join t t2 on (t1.m = t2.n) and (t1.m is not null);
--displays 100 which is incorrect.

--Lets try the same query after dropping the index
drop index tx;

select count(*)
from t t1
left join t t2 on (t1.m = t2.n) and (t1.m is not null);
--displays 100,000 which is correct.

Sorry I forgot to mention that I tried the same with official SQLite build (3.25.2) and this problem is not observed. It only happens with System.Data.SQLite package. That is why I am reporting it here.

Fixed in the SQLite core library via check-in:

    [https://www.sqlite.org/src/info/af39661e60f562b9]

This fix will appear in the 1.0.110.0 release of System.Data.SQLite.