SQLite User Forum

Unexpected result when using multiple joins with view
Login

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.