/ Ticket Change Details
Login
Overview

Artifact ID: a444fa6b9bb777649d9ce5698f3fad0eb800551c
Ticket: 4c86b126f22ad548fee0125337bdc9366912d9ac
Incorrect query result with SQLITE_ENABLE_STAT4
User & Date: drh 2014-02-10 22:46:11
Changes

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

    When SQLite is compiled using SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4, it gets the wrong answer for the first of the two queries below:

    CREATE TABLE nodes(
       local_relpath  TEXT PRIMARY KEY,
       moved_to  TEXT
    );
    INSERT INTO nodes VALUES('A',NULL);
    INSERT INTO nodes VALUES('A/B',NULL);
    INSERT INTO nodes VALUES('',NULL);
    INSERT INTO nodes VALUES('A/B/C-move',NULL);
    INSERT INTO nodes VALUES('A/B/C','A/B/C-move');
    INSERT INTO nodes VALUES('A/B-move',NULL);
    INSERT INTO nodes VALUES('A/B-move/C-move',NULL);
    INSERT INTO nodes VALUES('A/B-move/C','x');
    
    SELECT local_relpath, moved_to
     FROM nodes
    WHERE (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND (local_relpath < 'A/B0')))
      AND moved_to IS NOT NULL;
    
    .print ------------------------------------------------
    
    SELECT local_relpath, moved_to
     FROM nodes
    WHERE (+local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND (local_relpath < 'A/B0')))
      AND moved_to IS NOT NULL;
    

    The bug appears to have been inserted into trunk with the STAT4 changes of check-in [a32af0abe5fa6d] which occurred just after the release of 3.8.0. The bug first appeared in 3.8.1.

    Note that it is not necessary to run ANALYZE or to have sqlite_stat3 or sqlite_stat4 tables in the database. The only requirement is to compile with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4.

    The extra unary "+" operator in the second queries disable the OR optimization and enables the second query to get the correct answer regardless.

    This bug was discovered in the wild by the SVN developers.

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Critical"
  6. Change status to "Open"
  7. Change title to "Incorrect query result with SQLITE_ENABLE_STAT4"
  8. Change type to "Code_Defect"