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
Commit ID: f6d6f969
OS: Ubuntu 20.04
Configuration Options: ./configure --enable-all
(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.