From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1482694023@qq.com
Subject: BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced
Date: Mon, 05 Oct 2026 13:52:10 +0000
Message-ID: <19751-db95b8241f0e33d9@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19751
Logged by: N J
Email address: 1482694023@qq.com
PostgreSQL version: 18.6
Operating system: Windows 11 64-bit
Description:
Environment:
PostgreSQL: [fill in your exact version]
OS: Windows 11 64-bit
Steps to reproduce:
1. Create test table and insert data:
CREATE TEMP TABLE readings (
region_id integer NOT NULL,
reading integer NOT NULL
);
INSERT INTO readings (region_id, reading) VALUES
(2, 7),
(2, 1);
2. Create index and collect statistics:
CREATE INDEX ON readings (region_id, reading);
ANALYZE readings;
3. Force index scan and execute the query:
SET enable_seqscan = off;
SELECT MIN(reading) AS minimum
FROM (
SELECT reading
FROM readings
WHERE region_id = 2
) AS scoped
ORDER BY minimum
FETCH FIRST 1 ROW WITH TIES;
Observed result:
ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000
Expected result:
minimum
---------
1
(1 row)
Bug analysis:
This is a valid standard SQL query. The subquery is only used as a derived
table in the FROM clause, there is no scalar subquery in the statement. The
21000 cardinality error should never be raised here.
The error only triggers under these conditions:
- Sequential scan is disabled, so the optimizer uses an index scan to
optimize the MIN() aggregate
- The query includes FETCH FIRST 1 ROW WITH TIES
- The inner derived table returns multiple rows
It appears that when the optimizer combines the index-optimized MIN() path
with the WITH TIES logic, it incorrectly generates an execution plan that
treats the internal index scan subplan as a scalar subquery, resulting in a
spurious error when the subplan returns multiple rows. This is an optimizer
correctness bug.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 1482694023@qq.com
Subject: Re: BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced
In-Reply-To: <19751-db95b8241f0e33d9@postgresql.org>
References: <19751-db95b8241f0e33d9@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox