Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN

On Fri, Nov 10, 2023 at 4:20 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

> Looking closer at the codes, I think we still have some loose ends
> regarding how SJE handles PHVs.
>
> ...
>
> 2) Currently remove_self_joins_one_group() checks PHVs as below
>
> /* there isn't any other place to eval PHV */
> if (bms_is_subset(phinfo->ph_eval_at, joinrelids) ||
> bms_is_subset(phinfo->ph_needed, joinrelids))
> break;
>
> I'm wondering if we can relax this restriction because it seems to me
> that a PHV evaluated/needed at or below the self join should not have
> problem if we remove the self join.
>

After some more thought, I think PHVs should not impose any constraints
on removing self joins. If the removed rel is contained in the relids
that a PHV is evaluated/needed at or laterally references, it can just
be replaced with the rel that is kept.

Attached is a patch to remove such constraints. Any comments or
feedback are welcome.

Thanks
Richard