Fwd: pg18 bug? SELECT query doesn't work

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

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


On Wed, Jan 7, 2026 at 3:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Eric Ridge <eebbrr@gmail.com> writes:
> > # SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x
WHEREanimal 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@postgresql.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