SQLite

View Ticket
Login
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: (text/x-fossil-wiki)
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:

<blockquote><verbatim>
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;
</verbatim></blockquote>

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: (text/x-fossil-wiki)
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.