Re: BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT

пт, 14 авг. 2026 г. в 23:09, PG Bug reporting form <noreply(at)postgresql(dot)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.
https://postgr.es/m/1738062.1784927903@sss.pgh.pa.us

--
Regards,
Rachitskiy Andrey