Subject: Re: Fwd: pg18 bug? SELECT query doesn't work

From: Tom Lane <[email-masked]>
Date: Wed, 07 Jan 2026 23:03:02 -0500

Richard Guo <[email-masked]> writes:
> The underlying expression of a join alias Var can only be a Var
> (potentially coerced) from one of the join's input rels, or a COALESCE
> expression containing the two input Vars.  Therefore, it should not be
> able to contain SRFs or volatile functions, and thus we do not need to
> expand it beforehand.

[ 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.

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.

			regards, tom lane

---

From: Richard Guo <[email-masked]>
Date: Thu, 8 Jan 2026 22:30:11 +0900

> 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.
 */

> 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

---

From: Richard Guo <[email-masked]>
Date: Fri, 9 Jan 2026 18:52:00 +0900

> How about we add a comment to check_output_expressions() along the

I've worked on the comment a bit more in the attached patch, which
also includes my previously proposed code changes and some test cases.

I think this patch is suitable for fixing the current bug in the back
branches.  We can use a separate patch for the more ambitious goal of
moving the push-down of subquery's restriction clauses into
subquery_planner().

Any thoughts?

- Richard

---

From: Richard Guo <[email-masked]>
Date: Fri, 16 Jan 2026 12:27:36 +0900

> moving the push-down of subquery's restriction clauses into

I plan to push and backpatch this patch early next week, barring any
objections.

- Richard

---

From: Eric Ridge <[email-masked]>
Date: Sat, 17 Jan 2026 11:18:28 -0500

objections.

Thank you for your work on this. It’s sincerely appreciated.

eric

---

From: Richard Guo <[email-masked]>
Date: Mon, 19 Jan 2026 11:39:37 +0900

On Sun, Jan 18, 2026 at 1:18 AM Eric Ridge <[email-masked]> wrote:

> > I plan to push and backpatch this patch early next week, barring any objections.

> Thank you for your work on this. It’s sincerely appreciated.

Pushed and backpatched.  Thanks for the report, Eric.  The fix will be
included in the next minor release this Feb.

- Richard