SQLite

View Ticket
Login
Ticket Hash: ec32177c99ccac2b180fd3ea2083d163109551ec
Title: Incorrect result with complex OR-connected WHERE and STAT4
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-01-27 13:56:20
Version Found In:
User Comments:
drh added on 2018-01-27 04:10:21: (text/x-fossil-wiki)
The following script shows a query that gives a different answer with and
without the t1b index.  The difference only appears when SQLite is compiled
using the SQLITE_ENABLE_STAT4 option.

<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
INSERT INTO t1(a,b) VALUES(1,1),(2,NULL),(3,NULL);
CREATE VIEW err AS
  SELECT a
    FROM t1 X
  WHERE 2 > (
    SELECT COUNT(*) FROM t1 Y
     WHERE ( X.b IS NOT NULL AND Y.b IS NULL )
        OR ( Y.b < X.b )
        OR ( X.b IS Y.B AND Y.a > X.a )
    );
SELECT * FROM err;
.print -----------------
CREATE INDEX t1b ON t1(b);
SELECT * FROM err;
</verbatim></blockquote>

Bisecting shows this problem first appeared with check-in
[712267c9c08fdcef] and subsequently in release 3.20.0
on 2017-08-01. The problem was reported on the sqlite-users
mailing list by Kenichi Ishigaki.

drh added on 2018-01-27 04:43:22: (text/x-fossil-wiki)
Here is another demonstration of the same problem that uses two separate
tables and omits the WHERE clause.  In this example, both queries return
a single row, but the answer is different.  The correct answer is 2, but
when the index is created the answer becomes 0.

<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
INSERT INTO t1(a,b) VALUES(1,1);
CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb);
INSERT INTO t2(aa,bb) VALUES(1,1),(2,NULL),(3,NULL);
CREATE VIEW err AS
  SELECT (
    SELECT COUNT(*) FROM t2
     WHERE ( t1.b IS NOT NULL AND t2.bb IS NULL )
        OR ( t2.bb < t1.b )
        OR ( t1.b IS t2.bb AND t2.aa > t1.a )
    )
    FROM t1;
SELECT * FROM err;
.print -----------------
CREATE INDEX t2b ON t2(bb);
SELECT * FROM err;
</verbatim></blockquote>