SQLite
View Ticket
Not logged in
Ticket UUID: 4c86b126f22ad548fee0125337bdc9366912d9ac
Title: Incorrect query result with SQLITE_ENABLE_STAT4
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-02-11 02:48:03
Version Found In: 3.8.3
User Comments:
drh added on 2014-02-10 22:46:11:

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.


drh added on 2014-02-10 23:50:08:

Here is a simpler script to illustrate the problem. The query should return a single row, but it is returning no rows:

CREATE TABLE t1(x TEXT UNIQUE, y TEXT UNIQUE, z);
INSERT INTO t1 VALUES('ghi','jkl','y');
SELECT * FROM t1 WHERE (x='ghi' OR y='jkl') AND z IS NOT NULL;

The exact cause of the problem is still unknown. However, it appears to have something to do with an adverse interaction between an IS NOT NULL constraint and a constraint composed of two or more OR-connected terms. IS NOT NULL constraints are never considered except when SQLITE_ENABLE_STAT3 or _STAT4 are used, so that explains why those compile-time options are required in order to demonstrate the problem.


drh added on 2014-02-11 02:48:03:

Now fixed on trunk. Work-arounds:

  1. Omit the SQLITE_ENABLE_STAT3 and SQLITE_ENABLE_STAT4 compile-time options.
  1. For WHERE-clause terms of the form "column IS NOT NULL" add a unary "+" operator before the column name. Like this: "+column IS NOT NULL".
  1. After opening each database connection (in variable db) run code like the following to disable the STAT3 and STAT4 logic:
        if( strcmp(sqlite3_sourceid(),"2013-08-26 23:00")>0
         && strcmp(sqlite3_sourceid(),"2014-02-11")<0 ){
          sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, 0x800);
        }