Unexpected Result by RIGHT JOIN Again

(1) By Jinsheng Ba (bajinsheng) on 2022-06-10 14:34:42 [source]

The expression

t3.c0=t2.c0

should be false, but the first SELECT returns one row.

CREATE TABLE t0 (c0 INTEGER);
INSERT INTO t0 VALUES ('x');
CREATE TABLE t1(c0 INTEGER);
INSERT INTO t1 VALUES ('y');
CREATE TABLE t2 (c0 INTEGER);
CREATE TABLE t3 (c0 INTEGER);

SELECT * FROM t2 LEFT OUTER JOIN t3 RIGHT OUTER JOIN t1 RIGHT OUTER JOIN t0 WHERE (t3.c0=t2.c0); -- ||y|x
SELECT (t3.c0=t2.c0) IS TRUE FROM t2 LEFT OUTER JOIN t3 RIGHT OUTER JOIN t1 RIGHT OUTER JOIN t0 ; -- 0

Environment:

Version: 3.39.0

Commit ID: cab9b4cc

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-06-10 16:46:13 in reply to 1 [link] [source]

Thanks for the bug report.

This problem comes about because an optimization associated with automatic indexes that always works in the presence of LEFT JOIN turns out to not work if there is a RIGHT JOIN someplace in the query. Terms of the WHERE clause can only be used by automatic indexes under the same circumstances as they can be used to drive an index - which is more restrictive under RIGHT JOIN that has historically been the case when there were only LEFT JOINs.

Note for new readers: The "automatic index" feature is similar to a "hash join" in other RDBMSes.

Now fixed as of check-in 342c501f53252334