public inbox for [email protected]
help / color / mirror / Atom feedBUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join
3+ messages / 3 participants
[nested] [flat]
* BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join
@ 2026-06-10 15:44 PG Bug reporting form <[email protected]>
2026-06-10 20:19 ` Re: BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join Matheus Alcantara <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-06-10 15:44 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19517
Logged by: Ibrahim Halatci
Email address: [email protected]
PostgreSQL version: 19beta1
Operating system: Linux x86_64
Description:
Description
-----------
The "Implement Eager Aggregation" optimization (commit by Richard Guo,
2025-10-08, master) can push a partial aggregate into the inner side of a
semi-join. In that position the partial count(*) counts inner-side rows
that match the semi-join condition, but the finalize aggregate above the
join then sums those PARTIAL counts as if they were counts of outer rows.
The query therefore returns inflated counts.
Disabling hash aggregation (SET enable_hashagg = off) is a reliable
workaround because it forces the planner away from the bad plan shape.
Minimal self-contained reproducer
---------------------------------
DROP TABLE IF EXISTS u, e;
CREATE TABLE u(user_id int, value_2 int);
CREATE TABLE e(user_id int, value_2 int);
INSERT INTO u SELECT 5, s % 4 FROM generate_series(1, 26) s;
INSERT INTO e SELECT s % 5, s % 4 FROM generate_series(1, 100) s;
ANALYZE u;
ANALYZE e;
-- Wrong result on PG19beta1:
SELECT u.user_id, count(*)
FROM u
WHERE u.value_2 = ANY (SELECT e.value_2
FROM e
WHERE u.user_id > e.user_id)
GROUP BY u.user_id;
-- Observed on PG19beta1: (5, 130)
-- Expected (and result on PG18, PG17, PG16): (5, 26)
-- Workaround restores the correct result:
SET enable_hashagg = off;
SELECT u.user_id, count(*)
FROM u
WHERE u.value_2 = ANY (SELECT e.value_2
FROM e
WHERE u.user_id > e.user_id)
GROUP BY u.user_id;
-- Returns: (5, 26)
RESET enable_hashagg;
EXPLAIN (VERBOSE, COSTS OFF) of the bad plan
--------------------------------------------
The diagnostic indicator is a "Hash Semi Join" whose Output list contains
"(PARTIAL count(*))", sourced from a "Partial HashAggregate" keyed on the
inner relation's columns. The "Finalize HashAggregate" above the join then
sums these inner-side partial counts instead of counting matched outer rows.
Likely cause
------------
Eager Aggregation appears to consider the RHS of a SEMI/ANTI join a legal
site to push a partial aggregate, but for SEMI joins each outer row matches
at most one inner row by the join's own semantics, so partial counts taken
on the inner side cannot be combined into a correct outer-side count(*).
Either eager aggregation must be disabled for the RHS of SEMI/ANTI joins,
or the partial state must be projected through the semi-join such that
each surviving outer row contributes exactly one partial.
Two follow-up commits to eager aggregation landed on 2026-04-06
("Fix volatile function evaluation in eager aggregation" and
"Fix collation handling for grouping keys in eager aggregation"), but
neither addresses semi-join correctness.
Search performed before reporting
---------------------------------
- pgsql-bugs archives for 2026-04, 2026-05, and 2026-06: no matching
report.
- pgsql-hackers search "eager aggregation semi": 0 hits.
- pgsql-hackers search "eager aggregation bug": only the original
development thread by Richard Guo.
- PostgreSQL 19 Open Items wiki (checked 2026-06-10): not listed under
Open Issues, Resolved before 19beta1/19beta2, Non-bugs, or Won't Fix.
Impact on downstream
--------------------
This was discovered while bringing up Citus on PG19; the regression
test multi_subquery_in_where_reference_clause exposes the same plan shape
through a distributed query whose worker-side SQL reduces to the pattern
above. We have applied SET enable_hashagg = off locally as a temporary
workaround.
Thank you,
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join
2026-06-10 15:44 BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join PG Bug reporting form <[email protected]>
@ 2026-06-10 20:19 ` Matheus Alcantara <[email protected]>
2026-06-11 01:03 ` Re: BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join Tender Wang <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Matheus Alcantara @ 2026-06-10 20:19 UTC (permalink / raw)
To: [email protected]; [email protected]
On Wed Jun 10, 2026 at 12:44 PM -03, PG Bug reporting form wrote:
> Likely cause
> ------------
> Eager Aggregation appears to consider the RHS of a SEMI/ANTI join a legal
> site to push a partial aggregate, but for SEMI joins each outer row matches
> at most one inner row by the join's own semantics, so partial counts taken
> on the inner side cannot be combined into a correct outer-side count(*).
> Either eager aggregation must be disabled for the RHS of SEMI/ANTI joins,
> or the partial state must be projected through the semi-join such that
> each surviving outer row contributes exactly one partial.
>
> Two follow-up commits to eager aggregation landed on 2026-04-06
> ("Fix volatile function evaluation in eager aggregation" and
> "Fix collation handling for grouping keys in eager aggregation"), but
> neither addresses semi-join correctness.
>
Wondering if commit ffeda04259b (Fix eager aggregation for semi/antijoin
inner rels) is about fixing this issue. Can you please check it? I think
that 19beta1 was created before the fix was applied on master.
> Search performed before reporting
> ---------------------------------
> - pgsql-bugs archives for 2026-04, 2026-05, and 2026-06: no matching
> report.
> - pgsql-hackers search "eager aggregation semi": 0 hits.
> - pgsql-hackers search "eager aggregation bug": only the original
> development thread by Richard Guo.
> - PostgreSQL 19 Open Items wiki (checked 2026-06-10): not listed under
> Open Issues, Resolved before 19beta1/19beta2, Non-bugs, or Won't Fix.
>
For the reference this previous bug was reported at [1] on eager
aggregation thread.
[1] https://www.postgresql.org/message-id/[email protected]...
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join
2026-06-10 15:44 BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join PG Bug reporting form <[email protected]>
2026-06-10 20:19 ` Re: BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join Matheus Alcantara <[email protected]>
@ 2026-06-11 01:03 ` Tender Wang <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Tender Wang @ 2026-06-11 01:03 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; Matheus Alcantara <[email protected]>
Hi all
Matheus Alcantara <[email protected]> 于2026年6月11日周四 04:19写道：
>
> On Wed Jun 10, 2026 at 12:44 PM -03, PG Bug reporting form wrote:
> > Likely cause
> > ------------
> > Eager Aggregation appears to consider the RHS of a SEMI/ANTI join a legal
> > site to push a partial aggregate, but for SEMI joins each outer row matches
> > at most one inner row by the join's own semantics, so partial counts taken
> > on the inner side cannot be combined into a correct outer-side count(*).
> > Either eager aggregation must be disabled for the RHS of SEMI/ANTI joins,
> > or the partial state must be projected through the semi-join such that
> > each surviving outer row contributes exactly one partial.
> >
> > Two follow-up commits to eager aggregation landed on 2026-04-06
> > ("Fix volatile function evaluation in eager aggregation" and
> > "Fix collation handling for grouping keys in eager aggregation"), but
> > neither addresses semi-join correctness.
> >
>
> Wondering if commit ffeda04259b (Fix eager aggregation for semi/antijoin
> inner rels) is about fixing this issue. Can you please check it? I think
> that 19beta1 was created before the fix was applied on master.
>
Yes, this commit ffeda04259b has fixed this problem. But it wasn't
included in the 19beta1.
I tried on HEAD, and the result was correct.
postgres=# SELECT u.user_id, count(*)
FROM u
WHERE u.value_2 = ANY (SELECT e.value_2
FROM e
WHERE u.user_id > e.user_id)
GROUP BY u.user_id;
user_id | count
---------+-------
5 | 26
(1 row)
postgres=# explain SELECT u.user_id, count(*)
FROM u
WHERE u.value_2 = ANY (SELECT e.value_2
FROM e
WHERE u.user_id > e.user_id)
GROUP BY u.user_id;
QUERY PLAN
---------------------------------------------------------------------
HashAggregate (cost=4.76..4.77 rows=1 width=12)
Group Key: u.user_id
-> Hash Semi Join (cost=3.25..4.71 rows=9 width=4)
Hash Cond: (u.value_2 = e.value_2)
Join Filter: (u.user_id > e.user_id)
-> Seq Scan on u (cost=0.00..1.26 rows=26 width=8)
-> Hash (cost=2.00..2.00 rows=100 width=8)
-> Seq Scan on e (cost=0.00..2.00 rows=100 width=8)
(8 rows)
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-06-11 01:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-10 15:44 BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join PG Bug reporting form <[email protected]>
2026-06-10 20:19 ` Matheus Alcantara <[email protected]>
2026-06-11 01:03 ` Tender Wang <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox