MIME-Version: 1.0
References: <19517-4c53dcdcef98d263@postgresql.org>
 <DJ5NBJ46WCKG.33LJCP1X8ZQH2@gmail.com>
In-Reply-To: <DJ5NBJ46WCKG.33LJCP1X8ZQH2@gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Thu, 11 Jun 2026 09:03:22 +0800
Message-ID: 
 <CAHewXNm8cGaSTXuZgmJLhXYHsatqhAuOpo5fnCSQQog3aRhFrA@mail.gmail.com>
Subject: Re: BUG #19517: Eager Aggregation produces wrong count(*) when pushed
 into RHS of Hash Semi Join
To: ihalatci@gmail.com
Cc: pgsql-bugs@lists.postgresql.org,
	Matheus Alcantara <matheusssilv97@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXNm8cGaSTXuZgmJLhXYHsatqhAuOpo5fnCSQQog3aRhFrA%40mail.gmail.com>
Precedence: bulk

Hi all

Matheus Alcantara <matheusssilv97@gmail.com> =E4=BA=8E2026=E5=B9=B46=E6=9C=
=8811=E6=97=A5=E5=91=A8=E5=9B=9B 04:19=E5=86=99=E9=81=93=EF=BC=9A
>
> 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 leg=
al
> > site to push a partial aggregate, but for SEMI joins each outer row mat=
ches
> > at most one inner row by the join's own semantics, so partial counts ta=
ken
> > 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 join=
s,
> > 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=3D# SELECT u.user_id, count(*)
FROM   u
WHERE  u.value_2 =3D 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=3D# explain SELECT u.user_id, count(*)
FROM   u
WHERE  u.value_2 =3D ANY (SELECT e.value_2
                        FROM   e
                        WHERE  u.user_id > e.user_id)
GROUP BY u.user_id;
                             QUERY PLAN
---------------------------------------------------------------------
 HashAggregate  (cost=3D4.76..4.77 rows=3D1 width=3D12)
   Group Key: u.user_id
   ->  Hash Semi Join  (cost=3D3.25..4.71 rows=3D9 width=3D4)
         Hash Cond: (u.value_2 =3D e.value_2)
         Join Filter: (u.user_id > e.user_id)
         ->  Seq Scan on u  (cost=3D0.00..1.26 rows=3D26 width=3D8)
         ->  Hash  (cost=3D2.00..2.00 rows=3D100 width=3D8)
               ->  Seq Scan on e  (cost=3D0.00..2.00 rows=3D100 width=3D8)
(8 rows)


--=20
Thanks,
Tender Wang