In cases where we have any clauses between two outer joins, these
clauses should be treated as degenerate clauses in the upper OJ, and
they may prevent us from re-ordering the two outer joins. Previously we
have the flag 'delay_upper_joins' to help avoid the re-ordering in such
cases.
In b448f1c8 remove_useless_result_rtes will remove useless FromExprs and
merge its quals up to parent. This makes flag 'delay_upper_joins' not
necessary any more if the clauses between the two outer joins come from
FromExprs. However, if the clauses between the two outer joins come
from JoinExpr of an inner join, it seems we have holes in preserving
ordering. As an example, consider
create table t (a int unique);
select * from t t1 left join (t t2 left join t t3 on t2.a = t3.a) inner join t t4 on coalesce(t3.a,1) = t4.a on t1.a = t2.a;
When building SpecialJoinInfo for outer join t1/t2, make_outerjoininfo
thinks identity 3 applies between OJ t1/t2 and OJ t2/t3, which is wrong
as there is an inner join between them.
This query will trigger the assertion for cross-checking on nullingrels
in search_indexed_tlist_for_var.
Thanks
Richard