Re: BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`

Tender Wang <tndrwang(at)gmail(dot)com> writes:
> PG Bug reporting form <noreply(at)postgresql(dot)org> 于2026年10月29日周四 18:43写道：
>> SELECT count(DISTINCT a.x + 1)
>> FROM a JOIN b TABLESAMPLE BERNOULLI (100) ON b.y = a.x + 1
>> GROUP BY b.y * 2;

> I investigated this with gdb. The failure appears to involve the
> computability check introduced by 9f954177.
> The Gather and its NestLoop subplan initially both have the targetlist:
> [b.y * 2, a.x]
> When preparing the sort for the presorted aggregate, the first pathkey
> matches the existing expression b.y * 2. The second pathkey refers to
> the join EC {b.y, a.x + 1}, but neither member matches an existing
> targetlist entry.

Yeah. So, looking back at the reasoning for 9f954177:

... We will not
compute the EC expression in a plan node atop the one returning
the passed-in targetlist; rather, the EC expression will be
computed as an additional column of that targetlist. So any
Var or quasi-Var used in the given tlist is also available to the
EC expression. In simple cases this makes no difference because
the given tlist is just a list of Vars or quasi-Vars --- but if
we are considering an appendrel member produced by flattening
a UNION ALL, the tlist may contain expressions, resulting in
failure to match and a "could not find pathkey item to sort"
error.

The hidden assumption in that is that the node just below this one
outputs a tlist that is just Vars and quasi-Vars (ie PHVs), so that
pull_var_clause() will produce an accurate representation of what
is available as input. Clearly, that fails here.

It looks to me like prepare_sort_from_pathkeys() could look at
the subplan(s) of the given Plan node to see what they are actually
returning, but I wonder whether it'd be better to change its API
to have the caller specify what the input is. We're going to have
to modify the way that create_append_plan does this in any case,
since right now it does prepare_sort_from_pathkeys() on the Append
plan before it's attached any child plans.

The comment for relation_can_be_sorted_early looks like that function
might need rejiggering too, if prepare_sort_from_pathkeys starts to
behave differently. I didn't look closely though.

regards, tom lane