select ref_1.r_comment as c0, subq_0.c1 as c1 from public.region as sample_0 right join public.partsupp as sample_1 right join public.lineitem as sample_2 on (cast(null as path) = cast(null as path)) on (cast(null as "timestamp") < cast(null as "timestamp")) inner join public.lineitem as ref_0 on (true) left join (select sample_3.ps_availqty as c1, sample_3.ps_comment as c2 from public.partsupp as sample_3 where false order by c1, c2 ) as subq_0 on (sample_1.ps_supplycost = subq_0.c1 ) right join public.region as ref_1 on (sample_1.ps_availqty = ref_1.r_regionkey ) where ref_1.r_comment is not NULL order by c0, c1;
The repro can be reduced to the query below.
create table t (a int, b int);
# explain (costs off) select * from t t1 left join (t t2 inner join t t3 on false left join t t4 on t2.b = t4.b) on t1.a = t2.a; QUERY PLAN -------------------------- Result One-Time Filter: false (2 rows)
As we can see, the joinrel at the final level is marked as dummy, which is wrong. I traced this issue down to distribute_qual_to_rels() when we handle variable-free clause. If such a clause is not an outer-join clause, and it contains no volatile functions either, we assign it the full relid set of the current JoinDomain. I doubt this is always correct.
Such as in the query above, the clause 'false' is assigned relids {t2, t3, t4, t2/t4}. And that makes it a pushed down restriction to the second left join. This is all right if we plan this query in the user-given order. But if we've commuted the two left joins, which is legal, this pushed down and constant false restriction would make the final joinrel be dummy.
It seems we still need to check whether a variable-free qual comes from somewhere that is below the nullable side of an outer join before we decide that it can be evaluated at join domain level, just like we did before. So I wonder if we can add a 'below_outer_join' flag in JoinTreeItem, fill its value during deconstruct_recurse, and check it in distribute_qual_to_rels() like
/* eval at join domain level if not below outer join */ - relids = bms_copy(jtitem->jdomain->jd_relids); + relids = jtitem->below_outer_join ? + bms_copy(qualscope) : bms_copy(jtitem->jdomain->jd_relids);