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: 1482694023@qq.com
Subject: BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions
Date: Sat, 26 Sep 2026 08:55:20 +0000
Message-ID: <19722-1b775eae8675c158@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19722
Logged by:          N J
Email address:      1482694023@qq.com
PostgreSQL version: 18.6
Operating system:   Windows 11 64-bit
Description:        

Environment:
PostgreSQL: 18.6
OS: Windows 11 64-bit

Reproduction SQL:
CREATE TEMP TABLE decimal_source (raw_value text NOT NULL);
INSERT INTO decimal_source VALUES
    ('1.0'), ('1.00'), ('1.000');

SELECT amount, partition_size
FROM (
    SELECT raw_value::numeric AS amount,
           COUNT(*) OVER (PARTITION BY raw_value::numeric) AS partition_size
    FROM decimal_source
) AS windowed
WHERE scale(amount) = 1;

Observed result:
 amount | partition_size
--------+----------------
    1.0 |              1

Expected result:
 amount | partition_size
--------+----------------
    1.0 |              3

Bug analysis:
All three text values convert to numerically equal numeric values (1.0,
1.00, 1.000 all represent the same number). According to standard SQL
semantics, PARTITION BY groups rows by value equality, so all three rows
should belong to the same window partition and COUNT(*) OVER should return 3
for every row.

The actual result shows partition_size = 1, which means values with
different decimal scales are incorrectly treated as distinct partition keys.
The window partition logic appears to use the internal representation
(including scale metadata) instead of logical numeric equality to determine
partition membership.

This is a correctness bug: numerically equal values must be grouped into the
same window partition regardless of their precision/scale.

view thread (2+ messages)  latest in thread

Message-ID: <19722-1b775eae8675c158@postgresql.org>
Permalink:  ../19722-1b775eae8675c158@postgresql.org/
Also on:    postgresql.org/message-id/19722-1b775eae8675c158@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, 1482694023@qq.com
  Subject: Re: BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions
  In-Reply-To: <19722-1b775eae8675c158@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