Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19697: HAVING-to-WHERE transfer gives wrong count when
 scale(numeric) distinguishes equal grouping values
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: imchifan@163.com
Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 18 Sep 2026 07:02:16 +0000
Message-ID: <19697-6e7ccba388bbe858@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19697-6e7ccba388bbe858%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D on;
SET LOCAL enable_sort =3D 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) =3D 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)) =3D 1;

WITH optimized AS
(
  SELECT count(*) AS n
  FROM bugseer_postgres_00010_numeric_scale
  GROUP BY x
  HAVING scale(x) =3D 1
), baseline AS
(
  SELECT count(*) AS n
  FROM bugseer_postgres_00010_numeric_scale
  GROUP BY x
  HAVING scale(bugseer_postgres_00010_identity_numeric(x)) =3D 1
)
SELECT optimized.n AS optimized_n,
       baseline.n AS baseline_n,
       optimized.n =3D 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.