SQLite

View Ticket
Login
Ticket Hash: c31034044bb72c89319b5f2022518234409b4261
Title: LEFT JOIN in view malfunctions with NOTNULL
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-10-10 17:10:18
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-10-10 14:12:24:

Consider the following test case:

CREATE TABLE t0(c0);
CREATE TABLE t1(c1);
INSERT INTO t0(c0) VALUES(0);
CREATE VIEW v0(c0) AS SELECT t1.c1 FROM t0 LEFT JOIN t1;
SELECT * FROM v0 WHERE v0.c0 NOTNULL NOTNULL; -- expected: row is fetched, actual: no row is fetched

Unexpectedly, the SELECT does not fetch the row contained in the table, although the condition should evaluate to TRUE:

SELECT v0.c0 NOTNULL NOTNULL FROM v0; -- TRUE