/ 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. Change foundin to "3.7.17"
  2. Change icomment to:

    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.

    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;
    

    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. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Severe"
  6. Change status to "Open"
  7. Change title to:

    Incorrect join result or assertion fault due to transitive constraints

  8. Change type to "Code_Defect"