From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Lori Corbani <Lori.Corbani@jax.org>,
 Thomas Munro <thomas.munro@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: [EXTERNAL]Re: BUG #19094: select statement on postgres 17 vs
 postgres 18 is returning different/duplicate results
In-reply-to: <2441795.1761593103@sss.pgh.pa.us>
References: <19094-6ed410eb5b256abd@postgresql.org>
 <1970841.1761402665@sss.pgh.pa.us>
 <CY4PR06MB3575BFEBB314BB94654A7CC991FCA@CY4PR06MB3575.namprd06.prod.outlook.com>
 <CY4PR06MB3575C513D645156C738ABBDC91FCA@CY4PR06MB3575.namprd06.prod.outlook.com>
 <2441795.1761593103@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 27 Oct 2025 15:25:03 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2447088.1761595976.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 27 Oct 2025 16:12:56 -0400
Message-ID: <2447089.1761595976@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2447089.1761595976%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> After a good deal of time bisecting, I find the first commit that
> shows the problem is

> Author: Richard Guo <rguo@postgresql.org>
> Branch: master Release: REL_18_BR [aa86129e1] 2024-07-05 09:26:48 +0900
>     Support "Right Semi Join" plan shapes

After looking a little closer, we've got:

                if (node->js.jointype =3D=3D JOIN_RIGHT_SEMI &&
                    HeapTupleHeaderHasMatch(HJTUPLE_MINTUPLE(node->hj_CurT=
uple)))
                    continue;
                ...
                if (joinqual =3D=3D NULL || ExecQual(joinqual, econtext))
                {
                    if (!HeapTupleHeaderHasMatch(HJTUPLE_MINTUPLE(node->hj=
_CurTuple)))
                        HeapTupleHeaderSetMatch(HJTUPLE_MINTUPLE(node->hj_=
CurTuple));

As far as I can see, in a parallel hash join node->hj_CurTuple will
be pointing into a shared hash table, so that this code is fooling
with a shared HasMatch flag bit with no sort of lock whatsoever.
This query doesn't have any joinqual at the PHJ level if I'm reading
EXPLAIN correctly, so the window to get it wrong isn't very wide.
Nonetheless, if two parallel workers inspect the same inner tuple
at about the same time, this code would allow both of them to
return it.

I'm unsure if we've got any infrastructure that'd allow setting the
tuple's match bit in a more atomic fashion.  We might have to revert
aa86129e1.

			regards, tom lane