Re: BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values

пт, 18 сент. 2026 г. в 13:25, PG Bug reporting form <noreply(at)postgresql(dot)org
>:

> The following bug has been logged on the website:
>
> Bug reference: 19697
> Logged by: Qifan Liu
> Email address: imchifan(at)163(dot)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.
>
>
>
>
>
Hi, Liu!

This is the same hole as BUG #19619 and as Jacob Brazeal's report, and
the same class as BUG #19649. expression_has_grouping_conflict() refuses
a wrapped grouping column only for a nondeterministic collation. It does
not consult whether the grouping equality is image equality. scale(x)
is the example already named in that comment.

v5 for BUG #19649 closes it. A non-operand reference is accepted only
when BTEQUALIMAGE_PROC says the grouping equality is image equality.
numeric_ops does not register that procedure, so the HAVING clause stays
on the aggregate. With that patch the reporter's SQL keeps the filter
on HashAggregate and both queries return count 2.

The same SQL written as WHERE scale(x) = 1 over a grouped subquery is
also kept on the Agg. subquery_push_qual still moves the qual into the
subquery as HAVING. find_having_conflicts then refuses to lower it onto
the scan.

I am not sending a separate patch. The equalimage change is the one
already posted in the 19649 thread:
https://postgr.es/m/19649-2cabf1440793cc71@postgresql.org

Tom's earlier point was that we had never required opclasses to declare
whether equality is image equality, and that retrofitting such a flag
would be a mess:
https://postgr.es/m/3777654.1786733463@sss.pgh.pa.us

Andrei Lepikhov pointed out that btree already has that property for
deduplication. v5 reuses it rather than inventing a new one.

--
Regards,
Rachitskiy Andrey