SQLite

When the SQLITE_ENABLE_STAT4 compile-time option is used, the SELECT statement at the end of the following SQL returns an incorrect answer. The correct answer is 5, but the SELECT statement returns 1:

CREATE TABLE t1(a INT);
CREATE INDEX t1a ON t1(a);
INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
CREATE TABLE t2(dummy INT);
SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;

This problem was originally reported against System.Data.SQLite (SDS). https://system.data.sqlite.org/index.html/info/03cb660e3b18105a. The test case above is a simplification of the test case provided on that earlier ticket.

The problem first appeared in SQLite version 3.7.6 (2011-04-12) and is associated with check-in [499edcbc8ab70fcf] that added the STAT2 changes to the query planner.