|Title:||Incorrect query results return in left join when join clause references other table|
|Last Modified:||2018-11-06 00:19:55|
|Version Found In:||184.108.40.206|
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: (text/x-fossil-plain)
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: (text/x-fossil-plain)
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: (text/x-fossil-plain)
This fix will appear in the 220.127.116.11 release of System.Data.SQLite.