/ View Ticket
Login
Ticket Hash: 623eff57e76d45f64f7429ee8b28ea87fdfd2e18
Title: LEFT JOIN in view malfunctions with partial index on table
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-11-03 00:07:56
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-11-02 21:41:44: (text/x-fossil-wiki)
Consider the following test case:

<pre>
CREATE TABLE t0(c0);
CREATE TABLE t1(c0);
INSERT INTO t1(c0) VALUES (0);
CREATE INDEX i0 ON t0(0) WHERE NULL IN (c0);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t1 LEFT JOIN t0;
SELECT COUNT(*) FROM v0 WHERE NULL IN (v0.c0); -- expected: 0, actual: 1
</pre>

I would expect that the COUNT(*) yields 0, since the WHERE condition yields NULL for the single row contained in the table:

<pre>
SELECT NULL IN (v0.c0) FROM v0; -- NULL
</pre>