System.Data.SQLite
Ticket Change Details
Not logged in
Overview

Artifact ID: 97b7fb2dd71c0fb77d28647bd4181e1b0e83f90a
Ticket: 03cb660e3b18105a66e449309b1901c259f38432
Incorrect query results return in left join when join clause references other table
User & Date: anonymous 2018-11-01 14:30:42
Changes

  1. Change foundin to "1.0.109.0"
  2. Change icomment to:

    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.

  3. Change login to "anonymous"
  4. Change mimetype to "text/x-fossil-plain"
  5. Change private_contact to "411f3ab164b4071cc732af82c07ab861c870b9d3"
  6. Change severity to "Severe"
  7. Change status to "Open"
  8. Change title to:

    Incorrect query results return in left join when join clause references other table

  9. Change type to "Code_Defect"