|
2019-02-05
| ||
| 14:36 | • Fixed ticket [5948e09b]: Incorrect result from LEFT JOIN plus 5 other changes (artifact: 5851ddcf user: drh) | |
| 14:36 | The IS NOT NULL operator does not imply that the operand is never NULL. Fix for ticket [5948e09b8c415bc45da5cf] (check-in: d840e9bb user: drh tags: trunk) | |
| 13:55 | • New ticket [5948e09b] Incorrect result from LEFT JOIN. (artifact: 5400ddb5 user: drh) | |
| 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:
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. 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; Another manifestation of the problem which runs on historical versions of SQLite but which does not work on PostgreSQL is this: 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; 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. | ||||