Re: Assert failure in create_lateral_join_info

Hi,

On Fri, 16 Oct 2026 at 06:28, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> 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.

Thanks for the script, I could repro it.

> 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.

The fix makes sense to me. Do we also need to change the comment above
find_dependent_phvs()? It still says find_dependent_phvs_in_jointree()
is for checking a subtree of the join tree. (With the patch it also
walks the whole query for nested PHVs..)

And +1 on the risky aspect of back-patching.

Regards,
Ayush