/ View Ticket
Ticket Hash: 9fe487ba3c064b4e8549449981c073068a806201
Title: Join on two rtree tables malfunctions
Status: Fixed Type: Code_Defect
Severity: Minor Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-12-05 14:42:46
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-12-05 14:20:31:

Consider the following test case:

CREATE VIRTUAL TABLE rt0 USING rtree(c0, a, b);
CREATE VIRTUAL TABLE rt1 USING rtree(c0, a, b);
INSERT INTO rt1(c0) VALUES (x'00');
INSERT INTO rt0(c0) VALUES ('a');
SELECT * FROM rt1, rt0 WHERE rt0.c0 = CAST(rt1.c0 AS TEXT); -- unexpected: row is fetched

Unexpectedly, the row is fetched, which is unexpected, because the predicate evaluates to FALSE:

SELECT rt0.c0 = CAST(rt1.c0 AS TEXT) FROM rt1, rt0; -- 0