Incorrect results caused by complex JOIN tables

(1.1) By Zuming Jiang (zuming_jiang) on 2023-05-09 18:11:04 edited from 1.0 [source]

Set up the database

CREATE TABLE t1 (pkey INTEGER); INSERT INTO t1 VALUES(0); CREATE TABLE t2 (pkey INTEGER); CREATE TABLE t3 (pkey INTEGER);

Test Case 1 select case when (true) then 1 else rank() over (partition by subq_0.c3) end as c3 from (((t2 as ref_0 inner join t3 as ref_1 on (false)) right outer join t1 as ref_4 on (true)) cross join (select ref_5.pkey as c0, null as c1, null as c2, null as c3, null as c4, null as c5 from t1 as ref_5 order by c0 asc) as subq_0 ); --- return 0 row: {}

Then I change case when (true) then 1 else rank() over (partition by subq_0.c3) end to 1. The results should not change. The test case becomes:

Test Case 2

select 1 as c3 from (((t2 as ref_0 inner join t3 as ref_1 on (false)) right outer join t1 as ref_4 on (true)) cross join (select ref_5.pkey as c0, null as c1, null as c2, null as c3, null as c4, null as c5 from t1 as ref_5 order by c0 asc) as subq_0 ); --- return 1 row: {1}

Test Case 1 returns 0 row, while Test Case 2 returns 1 rows. They are not consistent.

SQLite version: 3.42.0 2023-05-03 14:10:05 b8a84a1bf53fa07ef01c57c6db6329ba439774a262b3adcbe94e7bd77525e296

(2) By Richard Hipp (drh) on 2023-05-10 00:36:53 in reply to 1.1 [link] [source]

Should be fixed by check-in da3fba18742b6e0b. The problem was an error in the push-down optimization.

(3) By Richard Hipp (drh) on 2023-05-10 13:18:26 in reply to 1.1 [link] [source]

The following is a simplified test case. The query at the end should return a single row with column values: NULL NULL 3 4

CREATE TABLE t1(a INT);  INSERT INTO t1(a) VALUES(1);
CREATE TABLE t2(b INT);  INSERT INTO t2(b) VALUES(2);
CREATE TABLE t3(c INT);  INSERT INTO t3(c) VALUES(3);
CREATE TABLE t4(d INT);  INSERT INTO t4(d) VALUES(4);
CREATE VIEW v5(e) AS SELECT DISTINCT d FROM t4;
SELECT * FROM  t1 JOIN t2 ON false RIGHT JOIN t3 ON true JOIN v5 ON true;

Table t1, t2, t3, and t4 are just ordinary tables. The view v5 uses DISTINCT so that it cannot be flattened into the outer query by the query flattening optimizations.

Because v5 cannot be flattened, the push-down optimization tries to push WHERE, ON, and USING clause constraints from the outer query down in the v5 view in order to limit the amount of work that the v5 view has to do. The bug came about when the optimizer mistakenly pushing the "ON false" constraint from the t1/t2 join down into the v5 view, effectively shutting down that view, and leading to an incorrect result. The effect was like moving the "ON false" constraint from the left side of the RIGHT JOIN over to the right side of that join.

(4) By Zuming Jiang (zuming_jiang) on 2023-05-11 09:49:02 in reply to 3 [link] [source]

Thanks for fixing it!