Re: [BUG] Remove self joins causes 'variable not found in subplan target lists' error

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> On Thu, Aug 28, 2025 at 6:42 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 2. Try to fix up the SJE patch so that it calculates relid changes
>> honestly, or at least no less honestly than what happened before
>> a3179ab69.

> I took a look at #2. I don't have a good understanding of the SJE
> patch either, so I might be missing something.

Thanks for looking at that!

> Hence, attached is the fix for SJE after reverting a3179ab69. With
> it, all regression tests pass.

I think that these are bugs/oversights in SJE that we should probably
fix in HEAD and v18, even if we're not going to revert a3179ab69.
We don't have strong reason to believe that they're not reachable
some other way.

Attached are a finished version of my v3 patch for HEAD, and the
promised adaptation of it for v18. The only surprise I ran into
was that I had to adopt the same sort of copy-from-the-parent
logic as you have in HEAD in create_unique_paths, because trying
to derive a child's list independently runs into assertion failures
inside make_pathkeys_for_sortclauses. In retrospect, probably
I shouldn't have been surprised.

With one eye on the fast-approaching release freeze for 18rc1,
I plan to go ahead and push these. Please do review if you
have time, but I think getting some buildfarm cycles on these
is time-critical now. (For the same reason, I suggest pushing
those SJE corrections sooner not later.)

regards, tom lane