pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced
2+ messages / 2 participants
[nested] [flat]

* BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced
@ 2026-10-05 13:52 PG Bug reporting form <noreply@postgresql.org>
  2026-10-05 15:21 ` Re: BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-10-05 13:52 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 1482694023@qq.com

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.








^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced
  2026-10-05 13:52 BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-05 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2026-10-05 15:21 UTC (permalink / raw)
  To: 1482694023@qq.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> 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

Thanks for the report, but this was reported and fixed a couple
weeks ago:

https://www.postgresql.org/message-id/flat/19694-4e61bd0475398eef%40postgresql.org

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-10-05 15:21 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-05 13:52 BUG #19751: MIN aggregate + FETCH FIRST 1 ROW WITH TIES causes false 21000 error when index scan is forced PG Bug reporting form <noreply@postgresql.org>
2026-10-05 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>

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