Unexpected Result by FULL OUTER JOIN

(1) By Jinsheng Ba (bajinsheng) on 2022-06-17 07:25:01 [source]

The second SELECT returns more results than the first SELECT.

CREATE TABLE t1 (c0 );
CREATE TABLE t0 (c0);
INSERT INTO t0(c0) VALUES (NULL);

SELECT DISTINCT t1.c0 FROM t1 FULL OUTER JOIN t0;  -- {} Empty result
SELECT DISTINCT t1.c0 FROM t1 FULL OUTER JOIN t0 WHERE (t0.c0 ISNULL); -- Null

Environment:

Unreleased Version: 3.39.0

Commit ID: 3a461f61

(2) By Richard Hipp (drh) on 2022-06-17 11:45:15 in reply to 1 [link] [source]

Thanks for the report. Should now be fixed by check-in f23a429d4153518d.