agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values
2+ messages / 2 participants
[nested] [flat]
* BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values
@ 2026-09-18 07:02 PG Bug reporting form <noreply@postgresql.org>
2026-09-18 09:37 ` Re: BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-18 07:02 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values
2026-09-18 07:02 BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-18 09:37 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Rachitskiy @ 2026-09-18 09:37 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
пт, 18 сент. 2026 г. в 13:25, PG Bug reporting form <noreply@postgresql.org
>:
> 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.
>
>
>
>
>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-18 09:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 07:02 BUG #19697: HAVING-to-WHERE transfer gives wrong count when scale(numeric) distinguishes equal grouping values PG Bug reporting form <noreply@postgresql.org>
2026-09-18 09:37 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox