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
