agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES
2+ messages / 2 participants
[nested] [flat]
* BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES
@ 2026-09-18 02:42 PG Bug reporting form <noreply@postgresql.org>
2026-09-18 14:43 ` Re: BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-18 02:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: chaitanyyachoudhary@gmail.com
The following bug has been logged on the website:
Bug reference: 19694
Logged by: Chaitanya Choudhary
Email address: chaitanyyachoudhary@gmail.com
PostgreSQL version: 18.6
Operating system: macOS 26 (aarch64), Homebrew build of 18.6
Description:
An aggregate query with ORDER BY and FETCH FIRST n ROWS WITH TIES fails
with an error when the planner chooses the index path for MIN() or MAX().
The same query succeeds with ROWS ONLY, with LIMIT, or when the index path
is disabled.
Steps to reproduce:
CREATE TABLE t (v int);
INSERT INTO t SELECT g % 100 FROM generate_series(1, 1000) g;
CREATE INDEX ON t (v);
ANALYZE t;
SELECT min(v) FROM t ORDER BY 1 FETCH FIRST 1 ROWS WITH TIES;
Result:
ERROR: more than one row returned by a subquery used as an expression
Expected: one row, min = 0. An aggregate without GROUP BY returns exactly
one row, so WITH TIES cannot add rows.
These all return 0 as expected:
SELECT min(v) FROM t ORDER BY 1 FETCH FIRST 1 ROWS ONLY;
SELECT min(v) FROM t ORDER BY 1 LIMIT 1;
SET enable_indexscan = off; SET enable_indexonlyscan = off; SET
enable_bitmapscan = off;
SELECT min(v) FROM t ORDER BY 1 FETCH FIRST 1 ROWS WITH TIES;
The plan for the failing query:
Limit
InitPlan 1
-> Limit
-> Index Only Scan using t_v_idx on t
Index Cond: (v IS NOT NULL)
-> Sort
Sort Key: ((InitPlan 1).col1)
-> Result
Cause:
build_minmax_path() in src/backend/optimizer/plan/planagg.c builds the
subquery for the aggregate by copying the outer Query and then rewriting
sortClause, limitOffset and limitCount (LIMIT 1). It does not reset
limitOption, so the copy keeps LIMIT_OPTION_WITH_TIES from the outer query.
create_minmaxagg_plan() in src/backend/optimizer/plan/createplan.c then
builds the initplan's Limit node with subparse->limitOption:
plan = (Plan *) make_limit(plan,
subparse->limitOffset,
subparse->limitCount,
subparse->limitOption,
0, NULL, NULL, NULL);
A Limit node with WITH TIES and no tie columns (uniqNumCols = 0) treats
every row as a tie, so the initplan returns the whole index scan instead of
one row, and the initplan check raises the error.
Fix: set parse->limitOption = LIMIT_OPTION_COUNT in build_minmax_path()
next to where limitCount is set, or pass LIMIT_OPTION_COUNT in
create_minmaxagg_plan(). The initplan is always a plain LIMIT 1.
The same code is present on REL_18_STABLE and master as of 2026-09-17.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES
2026-09-18 02:42 BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-18 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-09-18 14:43 UTC (permalink / raw)
To: chaitanyyachoudhary@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> An aggregate query with ORDER BY and FETCH FIRST n ROWS WITH TIES fails
> with an error when the planner chooses the index path for MIN() or MAX().
Thanks for the report, will fix.
> The same code is present on REL_18_STABLE and master as of 2026-09-17.
Seems to have been broken since WITH TIES was implemented.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-18 14:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 02:42 BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES PG Bug reporting form <noreply@postgresql.org>
2026-09-18 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox