public inbox for [email protected]help / color / mirror / Atom feed
Re: pg18 bug? SELECT query doesn't work 8+ messages / 4 participants [nested] [flat]
* Re: pg18 bug? SELECT query doesn't work @ 2026-01-06 18:52 Tom Lane <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Tom Lane @ 2026-01-06 18:52 UTC (permalink / raw) To: Eric Ridge <[email protected]>; +Cc: pgsql-general; Richard Guo <[email protected]> Eric Ridge <[email protected]> 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 protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg18 bug? SELECT query doesn't work @ 2026-01-06 19:25 Eric Ridge <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Eric Ridge @ 2026-01-06 19:25 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-general; Richard Guo <[email protected]> > On Jan 6, 2026, at 1:52 PM, Tom Lane <[email protected]> wrote: > > Eric Ridge <[email protected]> writes: >> Here's an even more reduced test case. No tables or data: > > I agree that this is a bug. "git bisect" says it broke at 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg18 bug? SELECT query doesn't work @ 2026-01-06 21:05 Vincent Veyron <[email protected]> parent: Eric Ridge <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Vincent Veyron @ 2026-01-06 21:05 UTC (permalink / raw) To: Eric Ridge <[email protected]>; +Cc: pgsql-general On Tue, 6 Jan 2026 14:25:07 -0500 Eric Ridge <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Fwd: pg18 bug? SELECT query doesn't work @ 2026-01-07 02:26 Richard Guo <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Richard Guo @ 2026-01-07 02:26 UTC (permalink / raw) To: Tom Lane <[email protected]>; Eric Ridge <[email protected]>; +Cc: Pg Hackers <[email protected]> (resending, as the previous one has been held for moderation) ---------- Forwarded message --------- From: Richard Guo <[email protected]> Date: Wed, Jan 7, 2026 at 11:18 AM Subject: Re: pg18 bug? SELECT query doesn't work To: Tom Lane <[email protected]> Cc: Eric Ridge <[email protected]>, pgsql-general <[email protected]>, Pg Hackers <[email protected]> On Wed, Jan 7, 2026 at 3:52 AM Tom Lane <[email protected]> wrote: > Eric Ridge <[email protected]> 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 <[email protected]> > 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Fwd: pg18 bug? SELECT query doesn't work @ 2026-01-07 02:37 Tom Lane <[email protected]> parent: Richard Guo <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2026-01-07 02:37 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Eric Ridge <[email protected]>; Pg Hackers <[email protected]> Richard Guo <[email protected]> writes: > On Wed, Jan 7, 2026 at 3:52 AM Tom Lane <[email protected]> wrote: >> 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). > 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. 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Fwd: pg18 bug? SELECT query doesn't work @ 2026-01-07 07:31 Richard Guo <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Richard Guo @ 2026-01-07 07:31 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Eric Ridge <[email protected]>; Pg Hackers <[email protected]> On Wed, Jan 7, 2026 at 11:37 AM Tom Lane <[email protected]> wrote: > Richard Guo <[email protected]> writes: > > (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? 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. > 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. 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Fwd: pg18 bug? SELECT query doesn't work @ 2026-01-07 17:00 Tom Lane <[email protected]> parent: Richard Guo <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2026-01-07 17:00 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Eric Ridge <[email protected]>; Pg Hackers <[email protected]> Richard Guo <[email protected]> writes: > On Wed, Jan 7, 2026 at 11:37 AM Tom Lane <[email protected]> wrote: >> 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. > 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. 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Fwd: pg18 bug? SELECT query doesn't work @ 2026-01-08 03:23 Richard Guo <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Richard Guo @ 2026-01-08 03:23 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Eric Ridge <[email protected]>; Pg Hackers <[email protected]> On Thu, Jan 8, 2026 at 2:00 AM Tom Lane <[email protected]> wrote: > 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. 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. > 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. 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-01-08 03:23 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-06 18:52 Re: pg18 bug? SELECT query doesn't work Tom Lane <[email protected]> 2026-01-06 19:25 ` Eric Ridge <[email protected]> 2026-01-06 21:05 ` Vincent Veyron <[email protected]> 2026-01-07 02:26 ` Richard Guo <[email protected]> 2026-01-07 02:37 ` Tom Lane <[email protected]> 2026-01-07 07:31 ` Richard Guo <[email protected]> 2026-01-07 17:00 ` Tom Lane <[email protected]> 2026-01-08 03:23 ` Richard Guo <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox