Unexpected Result by RIGHT JOIN on RTree Tables

(1) By Jinsheng Ba (bajinsheng) on 2022-06-01 07:39:37 [source]

(null, false)==(rt0.c0, rt0.c2) is evaluated to NULL at the second SELECT, but the third SELECT returns non-empty results although its WHERE clause should be evaluated to NULL.

CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2);
CREATE TABLE t1(c0);
INSERT INTO rt0(c1, c2) VALUES (x'01', x'02');

SELECT * FROM t1 RIGHT OUTER JOIN rt0;
-- |1|0.0|0.0
SELECT ((null, false)==(rt0.c0, rt0.c2)) FROM t1 RIGHT OUTER JOIN rt0;
-- Null
SELECT * FROM t1 RIGHT OUTER JOIN rt0 WHERE ((null, false)==(rt0.c0, rt0.c2));
-- |1|0.0|0.0

Environment:

Version: 3.39.0

Commit ID: f6d6f969

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-06-01 13:37:54 in reply to 1 [link] [source]

Here is an alternative test case that does not involve the use of RTREE:

CREATE TABLE t1(id INTEGER PRIMARY KEY, x INT);
CREATE TABLE t2(y INT);
INSERT INTO t1(id,x) VALUES(1, 0);
SELECT * FROM t2 RIGHT JOIN t1 ON true WHERE (+id,x)=(99,0);
SELECT * FROM t2 RIGHT JOIN t1 ON true WHERE (id,x)=(99,0);

The two SELECT statements should get the same answer, but only the first one is correct. The problem seems to be that some row-value constraints in the WHERE clause of a RIGHT-JOIN are not being handled correctly.

(3) By Richard Hipp (drh) on 2022-06-01 16:05:51 in reply to 1 [link] [source]

Should now be fixed on trunk.