Horton

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

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es
From: Richard Guo <guofenglinux@gmail.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>, Alexander Korotkov <aekorotkov@gmail.com>
Cc: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2023-11-20T08:49:13Z
Lists: pgsql-bugs

Attachments

On Fri, Nov 10, 2023 at 4:20 PM Richard Guo <guofenglinux@gmail.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

Commits

  1. Fix a comment for remove_self_joins_recurse()

  2. Don't constrain self-join removal due to PHVs

  3. Handle PlaceHolderVar case in replace_varno_walker

  4. Fix how SJE checks against PHVs

  5. citext: Allow tests to pass in OpenSSL FIPS mode