SQLite

View Ticket
Login
2018-01-27
13:56 Fixed ticket [ec32177c]: Incorrect result with complex OR-connected WHERE and STAT4 plus 3 other changes (artifact: 83c4e1e2 user: drh)
13:55
New test case for ticket [ec32177c99ccac2b1] that works without the STAT4. (check-in: 5259d484 user: drh tags: trunk)
05:40
Proposed fix for the query planner problem of ticket [ec32177c99ccac2b1]. (check-in: eef8cbef user: drh tags: trunk)
04:43 Ticket [ec32177c] Incorrect result with complex OR-connected WHERE and STAT4 status still Open with 6 other changes (artifact: f5d59ef0 user: drh)
04:10 New ticket [ec32177c]. (artifact: 014b0310 user: drh)

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:

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.

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;

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:

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.

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;