|16:35||• Fixed ticket [6f2222d5]: Incorrect output on a LEFT JOIN plus 6 other changes (artifact: 63d1ebb2 user: drh)|
|16:29||If a table is the right operand of a LEFT JOIN, then any column of that table can be NULL even if that column has a NOT NULL constraint. Fix for ticket [6f2222d550f5b0ee7ed]. (check-in: 5a80957b user: drh tags: branch-3.8.7)|
|16:27||If a table is the right operand of a LEFT JOIN, then any column of that table can be NULL even if that column has a NOT NULL constraint. Fix for ticket [6f2222d550f5b0ee7ed]. (check-in: 6f6fcbe4 user: drh tags: trunk)|
|15:40||• New ticket [6f2222d5] Incorrect output on a LEFT JOIN. (artifact: 897deb67 user: drh)|
|Title:||Incorrect output on a LEFT JOIN|
|Last Modified:||2014-12-04 16:35:32|
|Version Found In:||3.8.6|
drh added on 2014-12-04 15:40:03:
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:
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;
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:
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.