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

On Thu, Jan 8, 2026 at 1:03 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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