agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions
2+ messages / 2 participants
[nested] [flat]
* BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions
@ 2026-09-26 08:55 PG Bug reporting form <noreply@postgresql.org>
2026-09-26 14:39 ` Re: BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-26 08:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 1482694023@qq.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions
2026-09-26 08:55 BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-26 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-09-26 14:39 UTC (permalink / raw)
To: 1482694023@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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;
This is a near-duplicate of many recent reports[1][2][3][4][5].
Not one of them has offered a reason why they think that testing
scale() of a grouped numeric column is a useful or even well-defined
thing to do. Do you have a real-world use case? What is it?
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/CA%2BCOZaA0tnyza0pHx6-UEdM5dcLGP4RJJ0KOHFq%2BwVQCY2Jyiw%4...
[2] https://www.postgresql.org/message-id/flat/19588-e32b82433b3660ea%40postgresql.org
[3] https://www.postgresql.org/message-id/flat/19619-fc646db1c6b2dc90%40postgresql.org
[4] https://www.postgresql.org/message-id/flat/19697-6e7ccba388bbe858%40postgresql.org
[5] https://www.postgresql.org/message-id/flat/19713-635b8656d07464df%40postgresql.org
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-26 14:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-26 08:55 BUG #19722: Window PARTITION BY numeric treats equal values with different scales as separate partitions PG Bug reporting form <noreply@postgresql.org>
2026-09-26 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox