SQLite

View Ticket
Login
Ticket Hash: 5948e09b8c415bc45da5cfc75c8eb327bbb53886
Title: Incorrect result from LEFT JOIN
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-02-05 14:36:43
Version Found In: 3.26.0
User Comments:
drh added on 2019-02-05 13:55:55: (text/x-fossil-wiki)
The query at the end of the following SQL returns no rows, but it should return
one row.  The correct answer (one row) is confirmed by running the same
SQL against PostgreSQL.

<blockquote><verbatim>
CREATE TABLE t1(a INT);
INSERT INTO t1(a) VALUES(1);
CREATE TABLE t2(b INT);
SELECT a, b
  FROM t1 LEFT JOIN t2 ON true
 WHERE (b IS NOT NULL) IS false;
</verbatim></blockquote>

Another manifestation of the problem which runs on historical versions
of SQLite but which does not work on PostgreSQL is this:

<blockquote><verbatim>
CREATE TABLE t1(a INT);
INSERT INTO t1(a) VALUES(1);
CREATE TABLE t2(b INT);
SELECT a, b
  FROM t1 LEFT JOIN t2 ON 0
 WHERE (b IS NOT NULL)=0;
</verbatim></blockquote>

This problem first appeared with check-in [dd568c27b1d76563] - the
LEFT JOIN strength reduction optimization - and first appeared in
release 3.23.0 (2018-04-02).  The problem was reported
on the SQLite users mailing list.