Unexpected result when using multiple joins and subqueries
(1) By Suyang Zhong (suyang) on 2024-12-07 09:27:57 [source]
Consider the test case below:
CREATE TABLE t0(c0 INT , c1 INT);
CREATE TABLE t1(c0 INT , c1 TEXT);
INSERT INTO t1 (c0, c1) VALUES (1, 0);
CREATE VIEW v0(c0) AS SELECT 'a' FROM t0 NATURAL LEFT JOIN t1;
INSERT INTO t0 (c1) VALUES (0);
SELECT t1.c0 FROM v0 INNER JOIN (SELECT '?' AS col0 FROM v0) AS sub0 ON (1|sub0.col0), t1 RIGHT JOIN (SELECT 0 AS col0 FROM v0) AS sub1 ON true;
-- 1
SELECT t1.c0 FROM v0 INNER JOIN (SELECT '?' AS col0 FROM v0) AS sub0 ON (1|sub0.col0), t1 RIGHT JOIN (SELECT 0 AS col0 FROM v0) AS sub1 ON true WHERE t1.c0;
-- empty result set
It is unexpected that the second query returns an empty result, as the first query returns one row and shows that t1.c0 is evaluated to 1.
I can reproduce it on the latest trunk version 87040342. I cannot reproduce it on 3.40.