SQLite

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.

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;