Re: wrong query result due to wang plan

On Thu, Feb 16, 2023 at 3:16 PM tender wang <tndrwang(at)gmail(dot)com> wrote:

> 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);

Thanks
Richard