Assert failure in create_lateral_join_info

The query below triggers an assertion failure on v14 to master:

create table t (a int);

select ss.z
from (select 1) s1 left join
((select 1) s2 left join (select 1 as x) v on true
cross join (lateral (select coalesce(v.x, 0) as z, a from t) ss
cross join lateral (select ss.a offset 0) g))
on true;

TRAP: failed Assert("!bms_is_member(rti, lateral_relids)")

The problem is in remove_useless_result_rtes(), which gets rid of
RTE_RESULT RTEs from the jointree, since an RTE_RESULT returns one row
and no columns. The tricky part is PHVs that are supposed to be
evaluated at the RTE_RESULT; they have to go somewhere else once it's
removed. If the RTE_RESULT is under an inner join, remove_result_refs()
makes them be evaluated at the join of its sibling rels instead. That
is fine as long as no sibling needs the PHVs before that join is
formed, and find_dependent_phvs_in_jointree() is what checks this, by
looking for the PHVs in the siblings' join quals and LATERAL RTEs.

In this query, the reference to v.x inside ss sits above the s2/v left
join, so it gets wrapped as PHV(1). That left join is then removed,
and PHV(1) is relabeled to be evaluated at s2. The reference to ss.z
in the targetlist sits above the outer left join, and since coalesce()
is not strict, it gets wrapped as PHV(coalesce(PHV(1), 0)), which is
evaluated at t. Meanwhile g laterally references t.

Now when we consider removing s2, whose sibling is (ss cross join g),
the check doesn't find anything, because PHV(1) only shows up inside
the outer PHV in the targetlist. So s2 is removed and PHV(1) is moved
up to the join of t and g. But the outer PHV is still evaluated at t,
and it now needs a value that only exists at the join of t and g.
That makes t laterally dependent on g, and since g is already
laterally dependent on t, we end up with a cycle! :-O

So ISTM the problem is that find_dependent_phvs_in_jointree() doesn't
notice a PHV that is evaluated at some but not all of the sibling rels
and contains one of the RTE_RESULT's PHVs. Such a PHV can live
anywhere in the query, so looking at the jointree alone isn't enough.

I'm trying to fix this in the attached patch by also searching the
whole query for such PHVs with a new walker, and keeping the
RTE_RESULT if we find one. I also thought about a more unified
approach that we use a single walk over the query that keeps track of
where each expression gets evaluated, and refuses the removal whenever
one of the RTE_RESULT's PHVs is used below the join of all the sibling
rels. But that seems too risky for a fix that needs to be
back-patched.

- Richard