SQLite User Forum

Unexpected results of `JOIN`
Login

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

(2) By Dan Kennedy (dan) on 2023-04-25 14:40:13 in reply to 1.1 [link] [source]

Thanks for reporting this. Should now be fixed here:

https://sqlite.org/src/info/1783655e

Dan.