problem report: incorrect join behavior
Hi, In version 3.36 and the latest snapshot, it looks like a LEFT JOIN behaves like a plain JOIN in some circumstances. I have the following minimal case, which returns 1 row instead of the expected 2: SELECT *, sqlite_version() FROM ( SELECT NULL val FROM (SELECT 1) UNION ALL SELECT 'missing' FROM (SELECT 1) ) a LEFT JOIN (SELECT 1) ON a.val IS NULL /* returns val 1 sqlite_version() 1 3.37.0 */
(2) By David Raymond (dvdraymond) on 2021-07-22 13:20:56 in reply to 1 [link] [source]
A LEFT JOIN can sometimes be converted into an ordinary JOIN if there are terms in the WHERE clause that guarantee that the two joins will give identical results. In particular, if any column in the right-hand table of the LEFT JOIN must be non-NULL in order for the WHERE clause to be true, then the LEFT JOIN is demoted to an ordinary JOIN.
In your case the ON condition only references the left table, so there's no point in it being an outer join, and thus it treats it as an inner join.
That seems to be wrong. The ON condition of a left join should only result in left columns being null, no rows may be removed from a left join. Even if you did
x left join y on 0 you still get all of
select * from (select 1) a left outer join (select 2) b on false; 1|
So I think the optimization is too aggressive.
Sorry that should have been "right columns being NULL" not left.
Here is the cross-platform test case:
CREATE TABLE onerow(x INT); INSERT INTO onerow(x) VALUES(0); SELECT * FROM ( SELECT null AS aaa FROM onerow UNION ALL SELECT 'missing' AS aaa FROM onerow ) AS a LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;
Simplified test case that omits the UNION ALL:
CREATE TABLE onerow(x INT); INSERT INTO onerow(x) VALUES(0); SELECT * FROM (SELECT 'missing' AS aaa FROM onerow) AS a LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;
The above should return one row of output, and it does in PostgreSQL, MySQL, SQL Server, and SQLite version 3.34.0 and earlier. But in 3.35.0 through the latest check-in, it returns zero rows.
Interesting; I didn't think to try it without the UNION. I suspected UNION ALL had something to do with triggering the problem, because plain UNION does not show the bad behavior, i.e. this returns both rows: SELECT *, sqlite_version() FROM ( SELECT NULL val FROM (SELECT 1) UNION SELECT 'missing' FROM (SELECT 1) ) LEFT JOIN (SELECT 1) ON val IS NULL;
CREATE TABLE t1(a INT); INSERT INTO t1(a) VALUES(1); CREATE TABLE t2(b INT); SELECT * FROM (SELECT 3 AS c FROM t1) AS t3 LEFT JOIN t2 ON c IS NULL;
Should give one row of output, but it fact gives zero rows.
Just want to confirm that this patch also fixes the error in the application query where I first encountered it.
By the way, is it clear why the problem did not manifest with a plain UNION, but did show itself with UNION ALL or no union at all?