Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

On Thu, May 18, 2023 at 9:28 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > BTW, it seems that there is a minor thinko in the changes. In the
> > outer-join removal logic, we use syn_xxxhand to compute the relid set
> > for the join we are considering to remove. I think this might be not
> > right, because the outer joins may not be performed in syntactic order.
>
> No, I don't believe that. What we are interested in at this point is
> the semantic effect (or lack of it) of the potentially-removable join.
> It's fine to reason about that under the assumption that the joins will
> be done in syntactic order. If later parts of the planner decide to
> implement the joins in a different order, that cannot change the
> conclusion about whether it's safe to remove a join --- otherwise,
> either we were mistaken to remove the join, or the reordering logic
> is wrong.

Yeah, I think this is where the problem is. Using syn_xxxhand in
join_is_removable will cause us to be mistaken to think the join is
removable in some cases, because we might fail to notice the inner-rel
attributes are used above the join as we are checking the syntactic
relid set of the join.

Take the query shown upthread as an example, which would trigger an
Assert.

create table t (a int unique, b int);

explain (costs off)
select 1 from t t1
left join (t t2 left join t t3 on t2.a = 1) on t2.a = 1;
server closed the connection unexpectedly

We'd be mistaken to think t1/t2 join is removable, because we are
checking its syntactic relid set, which includes all rels, and so that
are not aware that t2.a is used by t2/t3 join.

Thanks
Richard