agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: imchifan@163.com
Subject: BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values
Date: Fri, 18 Sep 2026 07:02:16 +0000
Message-ID: <19697-6e7ccba388bbe858@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19697
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux/amd64
Description:
PostgreSQL version: PostgreSQL 20devel at
a12600b762c36d91450ce085fa25ef75250bc1c2; PostgreSQL 18.6; PostgreSQL 17.11
Operating system: Linux/amd64
Description
-----------
A HAVING predicate using scale(numeric) is transferred below grouping even
though numeric equality considers 1.0 and 1.00 equal while scale()
distinguishes them. The pushed predicate removes one member of the group,
producing an incorrect aggregate count.
Steps to reproduce
------------------
Run the following input with psql:
\set ON_ERROR_STOP on
BEGIN;
SET LOCAL enable_hashagg = on;
SET LOCAL enable_sort = off;
CREATE TEMP TABLE bugseer_postgres_00010_numeric_scale (x numeric);
INSERT INTO bugseer_postgres_00010_numeric_scale VALUES (1.0), (1.00);
CREATE FUNCTION bugseer_postgres_00010_identity_numeric(numeric)
RETURNS numeric
LANGUAGE plpgsql VOLATILE STRICT
AS 'BEGIN RETURN $1; END';
EXPLAIN (COSTS OFF)
SELECT x, count(*) AS n
FROM bugseer_postgres_00010_numeric_scale
GROUP BY x
HAVING scale(x) = 1;
EXPLAIN (COSTS OFF)
SELECT x, count(*) AS n
FROM bugseer_postgres_00010_numeric_scale
GROUP BY x
HAVING scale(bugseer_postgres_00010_identity_numeric(x)) = 1;
WITH optimized AS
(
SELECT count(*) AS n
FROM bugseer_postgres_00010_numeric_scale
GROUP BY x
HAVING scale(x) = 1
), baseline AS
(
SELECT count(*) AS n
FROM bugseer_postgres_00010_numeric_scale
GROUP BY x
HAVING scale(bugseer_postgres_00010_identity_numeric(x)) = 1
)
SELECT optimized.n AS optimized_n,
baseline.n AS baseline_n,
optimized.n = baseline.n AS invariant_holds
FROM optimized FULL JOIN baseline ON true;
Actual result
-------------
The first plan applies scale(x) as a sequential-scan filter, while the
value-preserving volatile form retains its filter on HashAggregate:
optimized_n | baseline_n | invariant_holds
-------------+------------+-----------------
1 | 2 | f
(1 row)
Expected result
---------------
Equivalent pre-group and post-group predicates must preserve the group
membership and return aggregate count 2, so invariant_holds should be true.
Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and
PostgreSQL 17.11. The reproducer sets enable_hashagg to on and enable_sort
to off for the transaction.
view thread (2+ messages) latest in thread
Message-ID: <19697-6e7ccba388bbe858@postgresql.org>
Permalink: ../19697-6e7ccba388bbe858@postgresql.org/
Also on: postgresql.org/message-id/19697-6e7ccba388bbe858@postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, imchifan@163.com
Subject: Re: BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values
In-Reply-To: <19697-6e7ccba388bbe858@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox