Unexpected Expression on ON clause

(1) By Jinsheng Ba (bajinsheng) on 2022-06-20 07:23:55 [source]

Please see this.

Not sure why rt0 is allowed in the first ON clause, and the second SELECT returns more results than the first SELECT.

CREATE VIRTUAL TABLE rt0 USING rtree_i32(c0, c1, c2);
CREATE TABLE t0 (c0);
CREATE TABLE t1 (c0);
INSERT INTO rt0 VALUES (0, 0, 0);

SELECT * FROM t1 JOIN t0 ON rt0.c1>t0.c0 RIGHT OUTER JOIN rt0;  -- {} Empty result
SELECT * FROM t1 JOIN t0 ON rt0.c1>t0.c0 RIGHT OUTER JOIN rt0 WHERE rt0.c1 = 0;  -- ||0|0|0

Environment:

Version: 3.39.0 (Unreleased)

Commit ID: 11162446

(2) By Richard Hipp (drh) on 2022-06-20 12:49:07 in reply to 1 [link] [source]

This appears to be a new manifestation of the same bug that was reported by forum post 8e4c352937e82929. The prior fix was incomplete, however, which allowed this new manifestation of the problem to continue to cause problems.

A more complete fix is now on trunk (as of check-in fb0a23b6789da8e9) and should completely resolve the issue.

Simplified test case that does not involve virtual tables:

CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
CREATE TABLE t3(c INTEGER PRIMARY KEY, d INT);
CREATE INDEX t3d ON t3(d);
INSERT INTO t3 VALUES(0, 0);
SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE +d = 0;
SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE d = 0;

Both queries should return no rows. Prior to this fix, the second query was incorrectly propagating the constant 0 from the WHERE clause into the ON clause of the join, transforming the ON clause into "b<0".

Thanks for the bug report.

(3) By Jinsheng Ba (bajinsheng) on 2022-06-21 01:19:32 in reply to 2 [link] [source]

Thanks for explanation!