BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`
BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website: Bug reference: 19804 Logged by: Alperen Keles Email address: alpkeles99@gmail.com PostgreSQL version: 18.6 Operating system: Ubuntu 24.04 x86_64 Description: Hello, This query fails at plan time with an internal error: CREATE TABLE a (x int); CREATE TABLE b (id int, y int) PARTITION BY RANGE (id); CREATE TABLE b1 PARTITION OF b FOR VALUES FROM (MINVALUE) TO (0); CREATE TABLE b2 PARTITION OF b FOR VALUES FROM (0) TO (MAXVALUE); ANALYZE; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SELECT count(DISTINCT a.x + 1) FROM a JOIN b TABLESAMPLE BERNOULLI (100) ON b.y = a.x + 1 GROUP BY b.y * 2; ERROR: XX000: variable not found in subplan target list LOCATION: fix_upper_expr_mutator, setrefs.c:3314 (18.6) EXPLAIN fails the same way. It also fails with two rows in each table, and with array_agg(a.x + 1 ORDER BY a.x + 1) in place of count(DISTINCT ...). It returns no rows, as expected, with enable_presorted_aggregate = off, with the join clause written as a.x + 1 = b.y, with GROUP BY b.y, without the TABLESAMPLE, with b not partitioned, or with the default parallel cost settings. Reproduced on 18.6 (REL_18_6, built without assertions), REL_16_STABLE 0d8bfc5e366 (16.15), REL_17_STABLE 4cd931b4314 (17.11), REL_18_STABLE 5b5c68194c5, 19rc1 (REL_19_STABLE 1212d5d33b1) and master 27417270ab4, on Ubuntu 24.04 x86_64. 14 returns no rows. On 16, 19rc1 and master it also fails with TABLESAMPLE SYSTEM (100). Found with a query fuzzer. Regards, Alperen Keles
Re: BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`
От:
Tender Wang <tndrwang@gmail.com>
Дата:
PG Bug reporting form 于2026年10月29日周四 18:43写道:
> This query fails at plan time with an internal error:
>
> CREATE TABLE a (x int);
> CREATE TABLE b (id int, y int) PARTITION BY RANGE (id);
> CREATE TABLE b1 PARTITION OF b FOR VALUES FROM (MINVALUE) TO (0);
> CREATE TABLE b2 PARTITION OF b FOR VALUES FROM (0) TO (MAXVALUE);
> ANALYZE;
> SET parallel_setup_cost = 0;
> SET parallel_tuple_cost = 0;
> SELECT count(DISTINCT a.x + 1)
> FROM a JOIN b TABLESAMPLE BERNOULLI (100) ON b.y = a.x + 1
> GROUP BY b.y * 2;
>
> ERROR: XX000: variable not found in subplan target list
> LOCATION: fix_upper_expr_mutator, setrefs.c:3314 (18.6)
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.
prepare_sort_from_pathkeys() therefore calls
find_computable_ec_member(). Its pull_var_clause() call extracts [b.y,
a.x] from the targetlist, so the first EC member, b.y, passes the
computability check.
prepare_sort_from_pathkeys() then adds b.y as a resjunk column
directly to the Gather's targetlist, since Gather is
projection-capable.
However, the NestLoop below it still outputs only [b.y * 2, a.x]. The
Gather cannot retrieve b.y from those output columns, and
set_upper_references() consequently fails.
Without TABLESAMPLE, the chosen plan has the Sort directly above the
NestLoop, with the Gather below the join. In that case, adding b.y to
the NestLoop's targetlist works because b.y is available from its join
input.
The distinction seems to be that, for Gather, a Var occurring inside
an existing target expression is not necessarily available to its
projection: that expression may already have been computed by the
child.
As an experiment, restoring the old is_exprlist_member() check makes
this query work, since it rejects b.y and selects a.x + 1 instead.
However, that also breaks the regression test added by 9f954177, so
simply reverting the change is not sufficient.
I do not have a proposed fix yet, but wanted to share these findings.
--
Thanks,
Tender Wang
Re: BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Tender Wang writes:
> PG Bug reporting form 于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
Re: BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`
Tom Lane 于2026年10月30日周五 00:17写道: > > 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. Thanks for the explanation. Attached is a WIP patch following your suggestion to pass the available projection inputs explicitly to prepare_sort_from_pathkeys(). The patch leaves find_computable_ec_member() unchanged. Instead, the caller supplies independently available Vars and quasi-Vars, without extracting Vars from computed child output expressions. For the reported query, the Gather's child outputs b.y * 2 and a.x, so the available input list contains a.x but not b.y. This makes EC selection choose a.x + 1. For Append and MergeAppend, the patch builds the child plans first and prepares their sort columns using the concrete plans. The parent targetlist determines the common column positions, since distinct parent columns can translate to identical expressions in one UNION ALL child. The sort metadata is then obtained from the first child and checked against the remaining children. Collecting the inputs also needs some additional handling for outer-join nullingrels, nestloop parameters, and PHVs evaluated locally. For outer joins, the patch allows output Vars and PHVs to have a superset of the input's nullingrels. I am less certain that the local PHV handling is the best approach, and would appreciate your thoughts on that part. I also adjusted relation_can_be_sorted_early(). It still accepts a directly matching reltarget expression, but its computability fallback now considers only independently exposed Vars and quasi-Vars. This is conservative because no particular Plan is available to inspect at that stage; it might reject some otherwise valid early-sort opportunities. The reported case is fixed, and all regression tests pass with assertions enabled. This has become a larger change than I expected, so I am sending it as WIP to get feedback. -- Thanks, Tender Wang
Re: BUG #19804: "variable not found in subplan target list", DISTINCT agg over Gather`
Hi, Tender Wang 于2026年10月30日周五 16:08写道: > > This has become a larger change than I expected, so I am sending it as > WIP to get feedback. > Attached is v2. The main change in this version fixes the compiler warnings reported by CI for non-assert builds. The variables nodenumsortkeys, nodeCollations, and nodeNullsFirst were assigned only for use in Assert() calls, so they became unused when assertions were compiled out. I removed those assertion-only variables. create_append_plan() now uses an explicit first_subplan flag when it needs information from the first child. I also adjusted the sort-column handling so that the common sortColIdx chosen from the parent Append/MergeAppend targetlist is retained and checked against every child, including the first one. For MergeAppend, the first actual child still supplies the comparator metadata the executor needs, but it no longer replaces the parent-selected column layout. The other changes are minor cleanup and formatting. -- Thanks, Tender Wang