Unexpected Result by RIGHT JOIN

(1) By Jinsheng Ba (bajinsheng) on 2022-06-06 09:03:55 [source]

The second SELECT returns a row that is not from the result of the first SELECT. According to the third SELECT, the second SELECT should not return anything.

CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2);
CREATE VIRTUAL TABLE rt1 USING rtree(c0, c1, c2);
INSERT INTO rt0(c0) VALUES (x'');
INSERT INTO rt1(c0) VALUES (1);

SELECT * FROM rt1 RIGHT OUTER JOIN rt0 ON rt1.c0;
-- 1|0.0|0.0|0|0.0|0.0
SELECT * FROM rt1 RIGHT OUTER JOIN rt0 ON rt1.c0 WHERE ((rt1.c1) NOTNULL)==rt0.c0;
-- |||0|0.0|0.0
SELECT * FROM rt1 RIGHT OUTER JOIN rt0 ON rt1.c0 WHERE ((rt1.c1) NOTNULL)!=rt0.c0;
-- 1|0.0|0.0|0|0.0|0.0

Environment:

Version: 3.39.0

Commit ID: f28de5b7

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-06-06 11:43:53 in reply to 1 [link] [source]

PostgreSQL confirms that the first SELECT statement below gives the correct answer (zero rows) and the second identical SELECT statement (the one that occurs after the CREATE INDEX) is wrong:

CREATE TABLE t1(a BOOLEAN); INSERT INTO t1 VALUES (false);
CREATE TABLE t2(x INT);     INSERT INTO t2 VALUES (0);
SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
CREATE INDEX t1a ON t1(a);
SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;

(3) By Richard Hipp (drh) on 2022-06-06 15:32:59 in reply to 1 [link] [source]

There was an optimization in SQLite that allowed a WHERE clause constraint to drive an index on the right table of a RIGHT JOIN. This test case refutes that optimizations. It the WHERE clause constraint includes a NOTNULL operator on the left operand of the RIGHT JOIN, then the optimization might not work.

The fix (check-in 4a31b7942a15c9c4) takes the easy way out and disables the optimization. It could have added new logic to only disable the optimization if the constraint made use of NOTNULL or similar operators that test the nullness of the left table. But that seems like a lot of trouble (and a big opportunity for new bugs) when the programmer could just put the constraint in the ON clause where it belongs.

In any event, it should be working now. Thanks for the bug report.

(4) By Jinsheng Ba (bajinsheng) on 2022-06-07 00:16:11 in reply to 3 [link] [source]

Thanks!