agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: chaitanyyachoudhary@gmail.com
Subject: BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES
Date: Fri, 18 Sep 2026 02:42:42 +0000
Message-ID: <19694-4e61bd0475398eef@postgresql.org> (raw)

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.

view thread (2+ messages)  latest in thread

Message-ID: <19694-4e61bd0475398eef@postgresql.org>
Permalink:  ../19694-4e61bd0475398eef@postgresql.org/
Also on:    postgresql.org/message-id/19694-4e61bd0475398eef@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, chaitanyyachoudhary@gmail.com
  Subject: Re: BUG #19694: MIN()/MAX() fails with "more than one row returned by a subquery" under FETCH FIRST ... WITH TIES
  In-Reply-To: <19694-4e61bd0475398eef@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox