Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19694: MIN()/MAX() fails with "more than one row returned by a
 subquery" under FETCH FIRST ... WITH TIES
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: chaitanyyachoudhary@gmail.com
Reply-To: chaitanyyachoudhary@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 18 Sep 2026 02:42:42 +0000
Message-ID: <19694-4e61bd0475398eef@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19694-4e61bd0475398eef%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D 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 =3D off; SET enable_indexonlyscan =3D off; SET
enable_bitmapscan =3D 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 =3D (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 =3D 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 =3D 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.