The following two SELECT statements are equivalent (first SELECT statement has one more pair of parentheses in the ON clause), but have different results.

CREATE TABLE t0 (c0);
CREATE TABLE t1(c0);
CREATE VIEW v0(c0) AS SELECT 0.4 FROM t0;
INSERT INTO t1(c0) VALUES (1);
INSERT INTO t0 VALUES (NULL);
SELECT v0.c0 FROM t1 INNER JOIN v0 ON ((((((((v0.c0)))))))AND(t0.c0)) COLLATE RTRIM LEFT OUTER JOIN t0 ON (('a') ISNULL) WHERE (((CASE ((t1.c0) BETWEEN (t1.c0) AND (v0.c0))  WHEN t1.c0 THEN (NOT (v0.c0)) END) ISNULL));
-- 0.4
SELECT v0.c0 FROM t1 INNER JOIN v0 ON (((((((v0.c0)))))))AND(t0.c0) COLLATE RTRIM LEFT OUTER JOIN t0 ON (('a') ISNULL) WHERE (((CASE ((t1.c0) BETWEEN (t1.c0) AND (v0.c0))  WHEN t1.c0 THEN (NOT (v0.c0)) END) ISNULL));
-- Empty result

Environment:

Version: 3.39.0

Commit ID: 764b71267e

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

I think Warren replied to this earlier. However due to a malfunction in this forum (a Fossil bug) his post was deleted. I'm sorry I didn't get to read it.

I don't think the two queries are equivalent because the parentheses cause the COLLATE operator to be applied to AND in one case and t0.c0 in the other.

Hi, I think this may be a regression issue which does not appear in version 3.31.1.

I further minimize the queries and let us see the two queries. For the same tables above, if we execute

SELECT v0.c0 FROM t1 INNER JOIN v0 ON ((v0.c0)AND(t0.c0)) COLLATE RTRIM LEFT OUTER JOIN t0 ON (('a') ISNULL);

The result is empty. When we add a WHERE clause, the result is not empty:

SELECT v0.c0 FROM t1 INNER JOIN v0 ON ((v0.c0)AND(t0.c0)) COLLATE RTRIM LEFT OUTER JOIN t0 ON (('a') ISNULL) WHERE ((CASE (t1.c0) BETWEEN (t1.c0) AND (v0.c0)  WHEN t1.c0 THEN (v0.c0) END) ISNULL);
-- 0.4

In my understanding, the WHERE should filter the rows based on the joined table, so the second query should not return more results than the first query.

Thanks for the bug report.

The problem bisects to check-in 67510354e69bab98 and is fixed by check-in c7e3a13a3288c577. Please try again with the latest trunk check-in.