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

On Thu, Jan 8, 2026 at 1:03 PM 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