Unexpected Result by Complicated JOINING

(1.1) By Jinsheng Ba (bajinsheng) on 2022-06-10 07:19:25 edited from 1.0 [source]

The t0.c1 IS TRUE is evaluated FALSE, but the first SELECT still fetch non-empty result.

CREATE TABLE t0(c0);
CREATE TABLE t1(c0);
ALTER TABLE t0 ADD c1 c2;
INSERT INTO t1 VALUES(NULL);
CREATE TABLE t2(c0);

SELECT * FROM t0 CROSS JOIN t2 ON t0.c1 IN (1) RIGHT OUTER JOIN t1 WHERE (t0.c1 IS TRUE); -- | | |
SELECT t0.c1 IS TRUE FROM t0 CROSS JOIN t2 ON t0.c1 IN (1) RIGHT OUTER JOIN t1; -- 0

Note that, if we integrate the ALTER to the first CREATE statement, the first SELECT will return an empty result.

Environment:

Version: 3.39.0

Commit ID: bbaf1f2e

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-06-10 10:36:37 in reply to 1.1 [link] [source]

Restatement of the problem:

CREATE TABLE t1(a INT,b BOOLEAN);
CREATE TABLE t2(c INT);  INSERT INTO t2 VALUES(NULL);
CREATE TABLE t3(d INT);
SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);

The last SELECT statement is returning one row when it should return zero rows. I think the redundant constraint ("b=TRUE" in the ON clause and "b IS TRUE" in the WHERE clause) may be confusing the RIGHT JOIN. Investigating now...

(3) By Richard Hipp (drh) on 2022-06-10 11:31:29 in reply to 1.1 [link] [source]

Problem resolved as of check-in cab9b4cccd13bf0a. Thank you for the bug report.

Note to third-party readers of this report: This problem is associated with RIGHT JOIN which is an unreleased new feature. It does not affect any released version of SQLite.