BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT
The following bug has been logged on the website:
Bug reference: 19619
Logged by: Michael Malis
Email address: malis@pgrust.com
PostgreSQL version: 18.6
Operating system: MacOS
Description:
The planner pushes WHERE scale(n) = 1 (and the equivalent HAVING) past
GROUP BY n and window PARTITION BY n. numeric values 1.0, 1.00 and
1.000 compare equal, so they form one group / one window partition, but
scale() distinguishes them. Pushing the filter into the scan drops two
of the three rows before the aggregate or window runs, so COUNT(*) is 1
instead of 3. Wrong answers, not a crash.
Version: PostgreSQL 18.6 on aarch64-apple-darwin24.5.0, compiled by
Apple clang version 17.0.0 (clang-1700.0.13.5), 64-bit
Configure: --enable-cassert --enable-debug
Reproducer
----------
CREATE TABLE nums (n numeric);
INSERT INTO nums VALUES (1.0), (1.00), (1.000);
ANALYZE nums;
-- Wrong: filter is pushed below the window
SELECT n, c FROM (
SELECT n, COUNT(*) OVER (PARTITION BY n) AS c
FROM nums
) s
WHERE scale(n) = 1
ORDER BY 1;
-- Correct: OFFSET 0 blocks pushdown; filter stays above the window
SELECT n, c FROM (
SELECT n, COUNT(*) OVER (PARTITION BY n) AS c
FROM nums
OFFSET 0
) s
WHERE scale(n) = 1
ORDER BY 1;
-- Same bug for GROUP BY
SELECT c FROM (
SELECT n, COUNT(*)::int AS c FROM nums GROUP BY n
) s
WHERE scale(n) = 1;
SELECT c FROM (
SELECT n, COUNT(*)::int AS c FROM nums GROUP BY n OFFSET 0
) s
WHERE scale(n) = 1;
-- Same bug for HAVING (moved to WHERE)
SELECT n, COUNT(*)::int AS c FROM nums GROUP BY n HAVING scale(n) = 1;
пт, 14 авг. 2026 г. в 23:09, PG Bug reporting form <noreply@postgresql.org>:
The planner pushes WHERE scale(n) = 1 (and the equivalent HAVING) past
GROUP BY n and window PARTITION BY n. numeric values 1.0, 1.00 and
1.000 compare equal, so they form one group / one window partition, but
scale() distinguishes them. Pushing the filter into the scan drops two
of the three rows before the aggregate or window runs, so COUNT(*) is 1
instead of 3. Wrong answers, not a crash.
Hi Michael!
This looks like a known limitation rather than a new bug.
Commit 44fb59fc605 taught the planner to refuse qual pushdown past
GROUP BY / DISTINCT / window PARTITION BY when the qual's equality
disagrees with the grouping. That covers a different btree opfamily,
and a nondeterministic collation. It deliberately does not catch a
function over a grouping column of a type whose equality is not
bitwise. The comment on expression_has_grouping_conflict() names
this exact case:
This leaves one case uncaught: with a deterministic collation, a function
over the column can still feed a finer comparison than the direct-operand
check sees, for example record_image_ops over a rebuilt record, or scale()
over numeric where two equal values differ in scale. Catching it would
require knowing that a type's equality is bitwise, which we do not test
here.
numeric 1.0, 1.00 and 1.000 compare equal, so they form one group.
scale() distinguishes them. Pushing WHERE or HAVING scale(n) = 1
below the grouping therefore drops rows before the aggregate or
window runs.
Tom's view on the HAVING form was that the query is ill-posed.
The group's representative among equal numerics is unspecified, so
HAVING scale(c) = 1 after grouping is not well defined.
/messages/by-id/1738062.1784927903@sss.pgh.pa.us
--
Regards,
Rachitskiy Andrey
Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:
Tom's view on the HAVING form was that the query is ill-posed.
The group's representative among equal numerics is unspecified, so
HAVING scale(c) = 1 after grouping is not well defined.
/messages/by-id/1738062.1784927903@sss.pgh.pa.us
Yeah. I'd be more excited about doing something about this if we'd
required opclasses to say whether their equality operator is image
equality or something weaker. But that was never designed into the
system, and retrofitting it now would be a mess. Since the only
benefit would be to queries that are arguably wrong anyway, it's
difficult to justify the effort.
regards, tom lane