| CARVIEW |
Select Language
HTTP/1.1 200 OK
Cache-control: no-cache
Content-Security-Policy: default-src 'self' data:; script-src 'self' 'nonce-cf63070d8dd7d34828a39d83ee329cb7dddfe579b7e8f080'; style-src 'self' 'unsafe-inline'; img-src * data:
X-Frame-Options: SAMEORIGIN
Content-Type: text/html; charset=utf-8
Content-Encoding: gzip
Vary: Accept-Encoding
Content-length: 11452
SQLite User Forum: Unexpected results of `JOIN`
SQLite User Forum
Unexpected results of `JOIN`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