Unexpected Result by WHERE when Joining Tables

(1) By Jinsheng Ba (bajinsheng) on 2022-05-24 08:06:22 [source]

See this, in a specific multi-table join scenario, the query with the WHERE clause returns more results than the same query without WHERE.

CREATE TABLE t0(c0, c1);
CREATE TABLE t1(c0);
INSERT INTO t0 VALUES(1, 1);
INSERT INTO t1 VALUES(1);
CREATE VIEW v0(c0, c1) AS SELECT 1, 0 FROM t0;
SELECT  * FROM v0 CROSS JOIN t1 ON (t0.c0 NOT NULL) LEFT OUTER JOIN t0 ON '';
-- Empty result
SELECT  * FROM v0 CROSS JOIN t1 ON (t0.c0 NOT NULL) LEFT OUTER JOIN t0 ON '' WHERE t0.c1 ISNULL;
-- 1|0|1||

Environment:

Version: 3.39.0

Commit ID: 392e8482

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Holger J (holgerj) on 2022-05-24 12:29:20 in reply to 1 [link] [source]

The ON clause of JOIN requires a boolean value (condition). Using an empty string doesn't make sense here.

Yes, it could have been marked as an error, but the general rule is: If you write something which doesn't make sense, don't expect sensible results.

(3) By Richard Hipp (drh) on 2022-05-24 13:54:21 in reply to 2 [link] [source]

An alternative formulation of the problem is this:

CREATE TABLE t0(t TEXT, u TEXT);  INSERT INTO t0 VALUES('t', 'u');
CREATE TABLE t1(v TEXT, w TEXT);  INSERT INTO t1 VALUES('v', 'w');
CREATE TABLE t2(x TEXT, y TEXT);  INSERT INTO t2 VALUES('x', 'y');
SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false;
SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false
 WHERE t2.y ISNULL;

This alternative works on PostgreSQL, except for one key point: PostgreSQL complains that the first ON clause references the t2 table which is not part of the join to which that ON clause refers. And so PG won't run either query. SQLite has historically been more relaxed about this.

Prior to the (unreleased) RIGHT JOIN enhancement, both queries above returned an empty set. This is true all the way back to version 3.0.0 (2004). But in the latest trunk check-in, the second query returns one row. I think this needs to be fixed so that they once again both return an empty set.

(4) By Richard Hipp (drh) on 2022-05-24 16:06:36 in reply to 3 [link] [source]

Should now be working again on trunk.

(5) By Jinsheng Ba (bajinsheng) on 2022-05-25 00:48:45 in reply to 4 [link] [source]

Yes！ It works now.