Unexpected Result by Union

(1) By Jinsheng Ba (bajinsheng) on 2022-07-13 13:27:51 [source]

The third SELECT returns more results than the first two SELECTs.

CREATE TABLE t0 (c0);
CREATE TABLE t1 (c0, c1);
CREATE VIEW v0(c0) AS SELECT NULL FROM t1 FULL OUTER JOIN t0 ON t1.c1 UNION ALL SELECT t1.c0 FROM t1;

INSERT INTO t0 VALUES (NULL);
INSERT INTO t1(c0, c1) VALUES ('x', NULL);


SELECT DISTINCT * FROM t1, v0 WHERE t1.c0; -- {} empty result
SELECT DISTINCT * FROM t1, v0 WHERE t1.c0 ISNULL; -- {} empty result
SELECT DISTINCT * FROM t1, v0 WHERE t1.c0 UNION SELECT DISTINCT * FROM t1, v0 WHERE t1.c0 ISNULL; -- {||}

Environment:

Version: 3.40.0 (Unreleased)

Commit ID: d1d019bf

(2) By Richard Hipp (drh) on 2022-07-13 15:55:36 in reply to 1 [link] [source]

Thanks for the bug report.

Here is a revised test case that also runs under PostgreSQL-14:

CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT, c INT);
CREATE VIEW t3(d) AS SELECT NULL FROM t2 FULL OUTER JOIN t1 ON c=a UNION ALL SELECT b FROM t2;
INSERT INTO t1(a) VALUES (NULL);
INSERT INTO t2(b, c) VALUES (99, NULL);
SELECT DISTINCT * FROM t2, t3 WHERE b<>0 UNION SELECT DISTINCT * FROM t2, t3 WHERE b ISNULL;

For this revised test case, the correct answer is two rows of result. But SQLite was returning three rows.

The problem is in the query flattener - the optimization that combines subqueries or views into the outer query. The query flattener does not normally run if the subquery contains a RIGHT or FULL JOIN and is not the first element of the outer query. This is because prior elements in the outer query would not know that they are part of the left operand of RIGHT JOIN. The error here is that the UNION ALL in the subquery (really a view in this case) was preventing the detection of the RIGHT JOIN.

Fixed by check-in 274e244c85935084.