/ View Ticket
Login
Ticket UUID: 65eb38f6e46de8c75e188a17ec4cf883bf933a58
Title: Incorrect answer on LEFT JOIN when STAT4 is enabled
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-11-05 08:09:34
Version Found In: 3.25.2
User Comments:
drh added on 2018-11-05 07:34:52:

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.


drh added on 2018-11-05 08:09:34:

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.