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

On Tue, Aug 26, 2025 at 4:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Here is a patch that fixes it that way.  I like this better than
> Sergey's approach because it is making the plans better not worse.
>
> There are a couple loose ends yet to be dealt with:
>
> * The fact that we now avoid duplicate unique-ifications is good,
> but I think it breaks the test case added by 44e95b572, since
> that's no longer demonstrating what it set out to demonstrate.
> (See the delta in aggregates.out.)  I'm not sure that that's a
> huge problem, but we probably at least ought to modify the comment
> on that test case.
>
> * What happens if we find that *all* the semi_rhs_exprs are known
> constant?  We'll compute empty pathkeys and groupClause, but then
> what?  It looks like create_final_unique_paths etc then build a
> useless Unique step.  Maybe we should just absorb the input
> relation's path list as-is?

Yeah, I think this is a better approach.

Instead of repeatedly calling make_pathkeys_for_sortclauses to detect
redundant expressions, I'm wondering if we could introduce a function
that detects whether a given expression is known equal to a constant
by the EquivalenceClass machinery.  This function should not be too
costly, as we'd only need to examine ECs that contain pseudoconstants.

We could then use this function to remove expressions that are known
constant from semi_rhs_exprs.  And if we find that all expressions
in semi_rhs_exprs are known constant (the second loose end you
mentioned), we can give up building unique paths and fall back to a
traditional JOIN_SEMI.

To be concrete, I'm imagining something like the attached patch.
Currently, it doesn't update the modified semi_rhs_exprs and
semi_operators back to SpecialJoinInfo, but that shouldn't be hard to
add.

One limitation is that the patch doesn't try to detect grouping
expressions that are known equal to each other, so you may still see
redundant grouping expressions, such as in the test case added by
44e95b572.  I'm not sure if that's a big issue.

Thanks
Richard