System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 03cb660e3b18105a66e449309b1901c259f38432
Title: Incorrect query results return in left join when join clause references other table
Status: Closed Type: Code_Defect
Severity: Severe Priority: Medium
Subsystem: Native_Assembly Resolution: Core_Bug
Last Modified: 2018-11-06 00:19:55
Version Found In: 1.0.109.0
User Comments:
anonymous added on 2018-11-01 14:30:42:
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.

anonymous added on 2018-11-01 14:40:49:
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.

mistachkin added on 2018-11-06 00:19:27:
Fixed in the SQLite core library via check-in:

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

mistachkin added on 2018-11-06 00:19:55:
This fix will appear in the 1.0.110.0 release of System.Data.SQLite.