Unexpected results of `JOIN`
(1.1) By ChiZhang on 2023-04-25 08:29:49 edited from 1.0 [source]
Consider the following queries:
CREATE TABLE vt0(c0);
CREATE TABLE rt1(c0);
CREATE VIEW v0(c0) AS SELECT ((rt1.c0) NOTNULL) FROM rt1;
INSERT INTO vt0(c0) VALUES (1);
INSERT INTO rt1(c0) VALUES (1);
SELECT COUNT(v0.c0) AS c0 FROM v0; -- 1
SELECT COUNT(*) FROM vt0 INNER JOIN v0 ON ((0)/(1)) FULL OUTER JOIN rt1; -- 0
SELECT COUNT(*) FROM vt0 INNER JOIN v0 ON ((0)/(SELECT COUNT(v0.c0) AS c0 FROM v0)) FULL OUTER JOIN rt1; -- 1
The second query JOIN
on a FALSE
condition, and the third query replace the constant 1
with another query, which returns 1
. The second query should equal to the third query, but they have different results.
I run this program with the last trunk version 8f637aae