MIME-Version: 1.0
References: <7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com>
 <C7FCAD3A-9859-4CCB-95DE-62ECF1368331@gmail.com> <1607957.1767725532@sss.pgh.pa.us>
 <CAMbWs49jxSN3AQ0xc9=Cyx8OgLbzu9su6QK68zKgZCsQxTk68w@mail.gmail.com>
In-Reply-To: <CAMbWs49jxSN3AQ0xc9=Cyx8OgLbzu9su6QK68zKgZCsQxTk68w@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 7 Jan 2026 11:26:27 +0900
Message-ID: <CAMbWs49jgcUS1yowhmDrsY-yDurniYSQWL_RpD8gPjKsah4fpw@mail.gmail.com>
Subject: Fwd: pg18 bug? SELECT query doesn't work
To: Tom Lane <tgl@sss.pgh.pa.us>, Eric Ridge <eebbrr@gmail.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs49jgcUS1yowhmDrsY-yDurniYSQWL_RpD8gPjKsah4fpw%40mail.gmail.com>
Precedence: bulk

(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=E2=80=AFAM
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=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Eric Ridge <eebbrr@gmail.com> writes:
> > # SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FR=
OM 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@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 =3D subquery->targetList;

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

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

- Richard