Richard Guo <guofenglinux@gmail.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.
