agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT
3+ messages / 3 participants
[nested] [flat]
* BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT
@ 2026-08-14 17:41 PG Bug reporting form <noreply@postgresql.org>
2026-08-14 18:30 ` Re: BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-08-14 17:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: malis@pgrust.com
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT
2026-08-14 17:41 BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-14 18:30 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-14 18:51 ` Re: BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Andrey Rachitskiy @ 2026-08-14 18:30 UTC (permalink / raw)
To: malis@pgrust.com; pgsql-bugs@lists.postgresql.org
пт, 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.
https://postgr.es/m/1738062.1784927903@sss.pgh.pa.us
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT
2026-08-14 17:41 BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT PG Bug reporting form <noreply@postgresql.org>
2026-08-14 18:30 ` Re: BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-08-14 18:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-08-14 18:51 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: malis@pgrust.com; pgsql-bugs@lists.postgresql.org
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.
> https://postgr.es/m/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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-08-14 18:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-14 17:41 BUG #19619: WHERE scale(n) = 1 pushed past GROUP BY / window PARTITION BY, wrong COUNT PG Bug reporting form <noreply@postgresql.org>
2026-08-14 18:30 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-14 18:51 ` 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