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.
