Fwd: pg18 bug? SELECT query doesn't work

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

---------- Forwarded message ---------
From: Richard Guo <guofenglinux(at)gmail(dot)com>
Date: Wed, Jan 7, 2026 at 11:18 AM
Subject: Re: pg18 bug? SELECT query doesn't work
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Eric Ridge <eebbrr(at)gmail(dot)com>, pgsql-general
<pgsql-general(at)postgresql(dot)org>, Pg Hackers
<pgsql-hackers(at)lists(dot)postgresql(dot)org>

On Wed, Jan 7, 2026 at 3:52 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Eric Ridge <eebbrr(at)gmail(dot)com> writes:
> > # 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 <rguo(at)postgresql(dot)org>
> 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).

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