SQLite

View Ticket
Login
2018-11-05
13:48
Disable the IS NOT NULL optimization when the IS NOT NULL operator is part of the ON clause of a LEFT JOIN. Fix for ticket [65eb38f6e46de8c75e188a17ec]. (check-in: 8d09ce5d user: drh tags: branch-3.25)
08:09 Fixed ticket [65eb38f6]: Incorrect answer on LEFT JOIN when STAT4 is enabled plus 6 other changes (artifact: 88c47ba1 user: drh)
07:53
Disable the IS NOT NULL optimization when the IS NOT NULL operator is part of the ON clause of a LEFT JOIN. Fix for ticket [65eb38f6e46de8c75e188a17ec]. (check-in: af39661e user: drh tags: trunk)
07:34 New ticket [65eb38f6] Incorrect answer on LEFT JOIN when STAT4 is enabled. (artifact: cf10feff user: drh)

Ticket Hash: 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.