SQLite
View Ticket
Not logged in
Ticket UUID: 6f2222d550f5b0ee7ed37601c3322521a2e9c1be
Title: Incorrect output on a LEFT JOIN
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-12-04 16:35:32
Version Found In: 3.8.6
User Comments:
drh added on 2014-12-04 15:40:03: (text/x-fossil-wiki)
In the following SQL, the final SELECT statement should return just a single
row, but beginning in SQLite 3.8.6 it returns 3 rows:

<blockquote><verbatim>
CREATE TABLE x1(a);
INSERT INTO x1 VALUES(1);
CREATE TABLE x2(b NOT NULL);    -- Remove the NOT NULL and things work
CREATE TABLE x3(c, d);
INSERT INTO x3 VALUES('a', NULL);
INSERT INTO x3 VALUES('b', NULL);
INSERT INTO x3 VALUES('c', NULL);
SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b;
</verbatim></blockquote>

As the comment states, removing the NOT NULL constraint on the x2.b column
causes the output to be correct.

The problem appears to have been introduced by checkin [952868216854e83]
on 2014-08-05.

drh added on 2014-12-04 16:35:32: (text/x-fossil-wiki)
The problem was that the query planner was using NOT NULL constraints
on table columns to optimize out certain operations.  However, for the
right-hand table of a LEFT JOIN, any column can be NULL despite having
a NOT NULL constraint.  The sqlite3ExprCanBeNull() routine in the query
planner had to be enhanced to recognize that corner case.