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

From: Tom Lane <[email-masked]>
Date: Tue, 06 Jan 2026 13:52:12 -0500

Eric Ridge <[email-masked]> writes:
> Here's an even more reduced test case.  No tables or data:

> # SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x WHERE animal ilike 'c%';

> pg15 returns:

>  animal  
> --------
>  CAT
> (1 row)

> and pg18 says:

> # SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x WHERE animal ilike 'c%';
> ERROR:  set-valued function called in context that cannot accept a set
> LINE 1: SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as ...
>                                     ^

I agree that this is a bug.  "git bisect" says it broke at

247dea89f7616fdf06b7272b74abafc29e8e5860 is the first bad commit
commit 247dea89f7616fdf06b7272b74abafc29e8e5860 (HEAD)
Author: Richard Guo <[email-masked]>
Date:   Tue Sep 10 12:35:34 2024 +0900

    Introduce an RTE for the grouping step
    
I've not probed further than that, but my guess is that now we check
for set-returning tlist items while the tlist still has grouping Vars,
thus missing the fact that there's a SRF represented by one of those
Vars.  This prompts us to flatten a subquery we shouldn't have
flattened (because that ends by introducing a SRF into the outer
WHERE).

			regards, tom lane

---

From: Eric Ridge <[email-masked]>
Date: Tue, 6 Jan 2026 14:25:07 -0500

> On Jan 6, 2026, at 1:52 PM, Tom Lane <[email-masked]> wrote:
> 

Thanks for the confirmation and your investigation.

Also, thanks for all you (all of you!) do for Postgres.  Many in the world, myself included, wouldn't be where we are in life without your work.

eric

---

From: Vincent Veyron <[email-masked]>
Date: Tue, 6 Jan 2026 22:05:04 +0100

On Tue, 6 Jan 2026 14:25:07 -0500
Eric Ridge <[email-masked]> wrote:

> Many in the world, myself included, wouldn't be where we are in life without your work.
>

I concur heartily. 

Best wishes to all.

-- 

					Bien à vous, Vincent Veyron 

https://compta.libremen.com
Logiciel libre de comptabilité générale et analytique en partie double

---

From: Richard Guo <[email-masked]>
Date: Wed, 7 Jan 2026 11:26:27 +0900

(resending, as the previous one has been held for moderation)

---------- Forwarded message ---------
From: Richard Guo <[email-masked]>
Date: Wed, Jan 7, 2026 at 11:18 AM
Subject: Re: pg18 bug? SELECT query doesn't work
To: Tom Lane <[email-masked]>
Cc: Eric Ridge <[email-masked]>, pgsql-general
<[email-masked]>, Pg Hackers
<[email-masked]>

> flattened (because that ends by introducing a SRF into the outer

Thanks for the report and the diagnosis.

The first part of your diagnosis is correct.  This issue is caused by
a failure to notice the SRF in the target list, as the item is hidden
under a grouped Var.  This doesn't lead to incorrect subquery
flattening though, since such a subquery must involve grouping, and
is_simple_subquery() would refuse to flatten it.  Instead, it
incorrectly indicates that the subquery's restriction clause is safe
to push down, which mistakenly introduces SRFs into the subquery's
WHERE quals.

I think the problem is that when we check whether a subquery's
restriction clauses are safe to push down, we are still working with a
'raw' parse tree that hasn't been preprocessed.  We might be able to
fix this specific issue by manually flattening the grouped Vars in the
subquery's tlist before performing the safety check:

 check_output_expressions(Query *subquery, pushdown_safety_info *safetyInfo)
 {
    ListCell   *lc;
+   List       *flattened_targetList = subquery->targetList;

-   foreach(lc, subquery->targetList)
+   if (subquery->hasGroupRTE)
+   {
+       flattened_targetList = (List *)
+           flatten_group_exprs(NULL, subquery, (Node *) subquery->targetList);
+   }
+
+   foreach(lc, flattened_targetList)
    {
        TargetEntry *tle = (TargetEntry *) lfirst(lc);

(I wonder whether this same issue exists for join alias Vars.)

- Richard

---

From: Tom Lane <[email-masked]>
Date: Tue, 06 Jan 2026 21:37:54 -0500

> to push down, which mistakenly introduces SRFs into the subquery's

Got it.

> (I wonder whether this same issue exists for join alias Vars.)

Seems highly likely that we'd have noticed if it did.  I think
flatten_join_alias_vars happens early enough that no interesting
decisions have been made yet.  I wonder whether we could fix the
current problem by doing grouping-Var expansion earlier?

I'm also wondering (don't recall the details of your patch)
whether you are repeating eval_const_expressions after
grouping-Var expansion.  If not, there are going to be bugs
there, like failure to handle named args in function calls.
That could be another reason to make this happen earlier.

			regards, tom lane

---

From: Richard Guo <[email-masked]>
Date: Wed, 7 Jan 2026 16:31:27 +0900

> current problem by doing grouping-Var expansion earlier?

Hmm, I don't think so.  The decision on whether to push down a
subquery's restriction clauses is made even before we invoke
subquery_planner() on that subquery.  At that stage, join alias Vars
have not yet been flattened, meaning the underlying expressions are
still hidden.  What I was wondering is whether this could cause
subquery_is_pushdown_safe() to make the wrong decision.

For the same reason, it seems that doing grouping-Var expansion
earlier wouldn't help with this bug, unless we move that expansion
ahead of the subquery_planner() call entirely.

In addition, it seems to me that it would cause problems if we move
the expansion of grouped Vars to before we've done with expression
preprocessing on targetlist and havingQual.  For example, consider
this query:

    select not a from t group by rollup(not a) having not not a;

If we do grouping-Var expansion before the havingQual is preprocessed,
the HAVING clause "not not a" would be reduced to "a" and thus fail to
be matched to lower tlist.

> That could be another reason to make this happen earlier.

Currently we're not repeating eval_const_expressions after the
grouping-Var expansion, but I fail to wrap my head around why that
would be a problem.  I ran a simple test with named args in function
calls:

create table t (i int);

CREATE OR REPLACE FUNCTION add_three(
    a int DEFAULT 0,
    b int DEFAULT 0,
    c int DEFAULT 0
)
RETURNS int AS $$
    SELECT a + b + c;
$$ LANGUAGE sql;

explain (verbose, costs off)
select add_three(i, c => 10) from t group by 1 having add_three(i, c
=> 10) > 100;
                QUERY PLAN
------------------------------------------
 HashAggregate
   Output: (((i + 0) + 10))
   Group Key: ((t.i + 0) + 10)
   ->  Seq Scan on public.t
         Output: ((i + 0) + 10)
         Filter: (((t.i + 0) + 10) > 100)
(6 rows)

... and the named args are expanded as expected.

- Richard

---

From: Tom Lane <[email-masked]>
Date: Wed, 07 Jan 2026 12:00:42 -0500

> would be a problem.

What I was mainly concerned about was whether the replacement
expressions ever got passed through eval_const_expressions().
I see now that they do, at planner.c:1069.

It's still not really desirable that this is done separately;
for example, I think it breaks the assumption that we will have
AND/OR flatness everywhere.  But I think that only leads to
possible inefficiencies not wrong answers.  And I do take your
point about needing to preserve the separate identities of
these subexpressions.  So let's let that go for now.

The main problem, as you say, is that allpaths.c is coming to
conclusions about the contents of output expressions of the
subquery without having done any of this.  The only really
simple answer I can see is to make a copy of the subquery's
tlist and apply these transformations to it before we run
the subquery_is_pushdown_safe logic.  That's ... ugly.

Perhaps another idea could be to shove the responsibility for this
down into subquery_planner (or make it call a callback at the right
point), and handle transferring of parent restriction clauses into
HAVING only after we've finished preprocessing the subquery's tlist.
That's an uncomfortably big change to be making in a released branch,
but it might still be a better way than duplicating preprocessing.

			regards, tom lane

---

From: Richard Guo <[email-masked]>
Date: Thu, 8 Jan 2026 12:23:36 +0900

> the subquery_is_pushdown_safe logic.  That's ... ugly.

It seems that allpaths.c checks the subquery's output only for two
specific cases: to determine if it contains SRFs or volatile
functions.  Because of this, it seems that we don't need to apply the
full set of transformations to it.  We only need to account for join
alias Vars and grouping Vars, as these can hide underlying
expressions.

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.  (This also answers my previous question about
whether the current bug exists for join alias Vars.)

Therefore, it seems to me that we only need to expand the grouping
Vars beforehand when checking the subquery's output, as in the changes
I proposed earlier.  It's still ugly, but less so I think.

> but it might still be a better way than duplicating preprocessing.

Agreed.  I think this is the theoretically correct way to handle the
push-down of a subquery's restriction clauses.  However, it seems like
a non-trivial project, and it seems to require changing the signature
of subquery_planner(), as we'd need to pass the subquery's RTE and
RelOptInfo into it.  So this looks too risky for stable branches.  But
maybe we can do that in dev branch.

- Richard