SQLite User Forum

Unexpected result when using multiple joins and subqueries
Login

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.