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`

От:
Tender Wang <tndrwang@gmail.com>
Дата:
Вложения:
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`

От:
Tender Wang <tndrwang@gmail.com>
Дата:
Вложения:
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
FAQ