Unexpected result when using multiple joins with view
(1) By Suyang Zhong (suyang) on 2024-12-01 07:05:00 [source]
Considering the test case below:
CREATE TABLE t0(c0 INT , c1 INT, c3 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0 (c1, c0, c3) VALUES (1, 0, 1);
CREATE VIEW v0(c0) AS SELECT 0 FROM t1 RIGHT JOIN t0 ON 1;
SELECT t0.c3 FROM v0 LEFT JOIN (SELECT 'a' AS col0 FROM v0 WHERE false) AS sub0 ON v0.c0, t0 RIGHT JOIN (SELECT (NULL) AS col0 FROM v0) AS sub1 ON t0.c3;
-- 1
SELECT t0.c3 FROM v0 LEFT JOIN (SELECT 'a' AS col0 FROM v0 WHERE false) AS sub0 ON v0.c0, t0 RIGHT JOIN (SELECT (NULL) AS col0 FROM v0) AS sub1 ON t0.c3 WHERE t0.c3;
-- empty result set
It is unexpected that the second query returns an empty result, as the first query returns one row and shows that t0.c3
is evaluated to 1.
I can reproduce it on the latest trunk version 301df5c2. I think 3.40 works well.
(2) By Richard Hipp (drh) on 2024-12-02 17:32:18 in reply to 1 [link] [source]
Thanks for the report. Now fixed on trunk.