Subject: BUG #19517: Eager Aggregation produces wrong count(*) when pushed into RHS of Hash Semi Join

From: PG Bug reporting form <[email-masked]>
Date: Wed, 10 Jun 2026 15:44:31 +0000

The following bug has been logged on the website:

Bug reference:      19517
Logged by:          Ibrahim Halatci
Email address:      [email-masked]
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,

---

From: "Matheus Alcantara" <[email-masked]>
Date: Wed, 10 Jun 2026 17:19:09 -0300

> Likely cause

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

For the reference this previous bug was reported at [1] on eager
aggregation thread.

[1] https://www.postgresql.org/message-id/CAJgoLk+d_P5sKrx-SZt01Acm_j0QnWn6aKJzFJ=[email-masked]

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

---

From: Tender Wang <[email-masked]>
Date: Thu, 11 Jun 2026 09:03:22 +0800

Hi all

> 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