MIME-Version: 1.0
References: <7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com>
 <C7FCAD3A-9859-4CCB-95DE-62ECF1368331@gmail.com> <1607957.1767725532@sss.pgh.pa.us>
 <CAMbWs49jxSN3AQ0xc9=Cyx8OgLbzu9su6QK68zKgZCsQxTk68w@mail.gmail.com>
 <CAMbWs49jgcUS1yowhmDrsY-yDurniYSQWL_RpD8gPjKsah4fpw@mail.gmail.com>
 <1654153.1767753474@sss.pgh.pa.us> <CAMbWs48nfVij2BuU=3cJhRLVpvEc6krW5erG-hTcTRk673i7TQ@mail.gmail.com>
 <1752324.1767805242@sss.pgh.pa.us> <CAMbWs49i=H-q_QZ6J+zkFQyR-EWuZvZGq8Oa40EykaB=0O0DrQ@mail.gmail.com>
 <1892152.1767844982@sss.pgh.pa.us>
In-Reply-To: <1892152.1767844982@sss.pgh.pa.us>
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 8 Jan 2026 22:30:11 +0900
Message-ID: <CAMbWs4-iqeDwW7ZmagGG5dr=8dcUsS9kX4W96vujN6w6NSHRaA@mail.gmail.com>
Subject: Re: Fwd: pg18 bug? SELECT query doesn't work
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Eric Ridge <eebbrr@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs4-iqeDwW7ZmagGG5dr%3D8dcUsS9kX4W96vujN6w6NSHRaA%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 8, 2026 at 1:03=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [ itch... ]  That statement is false in general, because subquery
> pullup within the subquery can replace a sub-subquery's output Vars
> with expressions.  It might be okay for this purpose, as I think we'd
> not pull up if the sub-subquery's output expressions are volatile or
> SRFs.  These assumptions had better be well commented though.

Ah, I see.  Once the sub-subqueries are flattened, the join alias
entries in the subquery can become arbitrary expressions rather than
simple Vars.  I suspect we have only avoided issues with join aliases
in subquery_is_pushdown_safe() for all these years by sheer luck:
since we don't pull up subqueries that output set-returning or
volatile functions, those 'arbitrary expressions' are unlikely to
include them.

How about we add a comment to check_output_expressions() along the
below lines?

/*
 * We need to expand grouping Vars to their underlying expressions (the
 * grouping clauses) because the grouping expressions themselves might be
 * volatile or set-returning.  However, we do not need to recurse deeper
 * into the arguments of those expressions.  If an argument references a
 * lower-level subquery output, we can rely on the fact that subqueries
 * containing volatile or set-returning functions in their targetlists are
 * never pulled up.
 *
 * We do not need to expand join alias Vars.  The underlying expression of
 * a join alias Var does not itself introduce volatility or set-returning
 * behavior.  As with grouping Vars, we rely on the pull-up restrictions to
 * guarantee that any referenced inputs from lower levels are free of such
 * functions.
 */

> The larger point here is that this behavior is all recursive,
> and we can happily end with an expression that's been pulled up
> several levels; we'd better make sure the right checks happen.
> So I'm a little bit distressed that planner.c's invocations of
> flatten_group_exprs are not at all analogous to its usage of
> flatten_join_alias_vars.  The latter pattern has a couple of
> decades of usage to lend credence to the assumption that it's
> correct.  flatten_group_exprs, um, not so much.  It may be
> fine, given the fact that grouping Vars can appear within
> much less of the query than join aliases.  But in view of the
> present bug, I'm feeling nervous.

I checked the invocations of flatten_join_alias_vars and
flatten_group_exprs in the planner to understand why they are not
being used analogously.

1. planner.c:1041

Here, we call flatten_join_alias_vars on the subquery because the
subquery may contain join aliases from the outer query level; since
these won't be expanded during the subquery's own planning, we must
expand them now.  A query illustrating this scenario is:

select * from tenk1 t1 full join tenk1 t2 using (unique1)
  join lateral (select unique1 offset 0) on true;

(BTW, the test cases added in da3df9987 for this logic are no longer
valid.  These queries still function correctly even if this code is
removed.  I think this is something we should fix.)

However, I don't think an analogous call to flatten_group_exprs is
necessary here.  Subqueries should not contain grouping-Vars from the
outer query, since FROM clause is processed before the GROUP BY step.
While it is true that a subquery could reference the output of another
subquery that happens to be a grouping-Var, that would be handled when
expanding grouping-Vars for that specific subquery.

2. prepjointree.c:1473

Here, we call flatten_join_alias_vars on the subquery's targetlist
during subquery flattening because once the the subquery's subqueries
are flattened, join alias entries may become arbitrary expressions
rather than simple Vars.

Again, I don't see a need for an analogous flatten_group_exprs call
here.  Any subquery containing grouping-Vars must involve grouping,
and we don't flatten such subqueries to begin with.

3. planner.c:1309

Here, flatten_join_alias_vars is called within preprocess_expression
for various expressions.  The analogous call to flatten_group_exprs
occurs at planner.c:1118.  I believe we only need to call
flatten_group_exprs on the targetList and havingQual, as these are the
only places where grouping-Vars can appear.

Based on the above, I suspect whether we should expect the invocations
of flatten_group_exprs to be analogous to those of flatten_join_alias_vars.

- Richard