SQLite

Ticket Change Details
Login
Overview

Artifact ID: d1fbaa0a04dabea0a6bd4f063f9a228a3c279660
Ticket: d805526eae253103dc307740dcf859b6d701c2f4
Incorrect join result or assertion fault due to transitive constraints
User & Date: drh 2013-07-08 18:02:53
Changes

  1. foundin changed to: "3.7.17"
  2. icomment:
    The following SQL should return a single row of result.  But due to the
    transitive constraint optimization, it either hits an assertion fault or
    it returns zero rows (depending on whether the SQLITE_DEBUG compile-time option
    is used.)  Note that SQLITE_ENABLE_STAT3 is required for this bug to appear.
    
    <blockquote><verbatim>
    CREATE TABLE t1(w INTEGER PRIMARY KEY, x);
    CREATE TABLE t2(y INTEGER, z);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t2 VALUES(1,3);
    SELECT *
      FROM t1 CROSS JOIN t2
     WHERE w=y AND y IS NOT NULL;
    </verbatim></blockquote>
    
    The problem appears to have been introduced by check-in [38852f158ab].
    
    Low-level technical details:  The "y IS NOT NULL" constraint is translated
    into a "y>NULL" virtual constraint when SQLITE_ENABLE_STAT3 is defined.  
    The y>NULL constraint tagged to indicate that the usual not-NULL check on 
    the right-hand side should be
    skipped since this is an artificial constraint.  The normal index handler 
    knows to check for that tag and skip the not-NULL check.  But after the
    transitive constraints were added, there is now also a "w>NULL" virtual
    constraint (because w=x).  The w>NULL constraint is also tagged, but the 
    INTEGER PRIMARY KEY handlers does not know to check for that tag and to 
    skip the not-NULL check.  It sees that the right-hand side of the constraint
    is NULL and all fails the constraint, resulting in no output rows.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to:
    Incorrect join result or assertion fault due to transitive constraints
    
  8. type changed to: "Code_Defect"