Re: [BUG] Remove self joins causes 'variable not found in subplan target lists' error - Mailing list pgsql-hackers

On Fri, Aug 29, 2025 at 2:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

I reviewed the v5 patch and found one issue.  For a child relation, we
shouldn't assume that the parent's unique rel or unique path always
exists.  In cases where all RHS columns are equated to constants, we
currently don't build the unique rel/path for the parent table.  This
issue results in SIGSEGV for the query below in both v18 and master.

create table p (a int, b int) partition by range(a);
create table p1 partition of p for values from (0) to (10);
create table p2 partition of p for values from (10) to (20);

set enable_partitionwise_join to on;

explain (costs off)
select * from p t1 where exists
  (select 1 from p t2 where t1.a = t2.a and t1.a = 1);
server closed the connection unexpectedly

The fix is very simple:

@@ -8307,6 +8307,15 @@ create_unique_paths(PlannerInfo *root,
RelOptInfo *rel, SpecialJoinInfo *sjinfo)
    if (!(sjinfo->semi_can_btree || sjinfo->semi_can_hash))
        return NULL;

+   /*
+    * Punt if this is a child relation and we failed to build a unique-ified
+    * relation for its parent.  This can happen if all the RHS columns are
+    * found to be equated to constants when unique-ifying the parent table,
+    * leaving no columns to unique-ify.
+    */
+   if (IS_OTHER_REL(rel) && rel->top_parent->unique_rel == NULL)
+       return NULL;
+

I plan to push the fix to both v18 and master, if there are no
objections.

Thanks
Richard