MIME-Version: 1.0
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 12 Apr 2023 19:35:53 +0800
Message-ID: <CAMbWs48Nde1Mv=BJv6_vXmRKHMuHZm2Q_g4F6Z3_pn+3EV6BGQ@mail.gmail.com>
Subject: Wrong results from Parallel Hash Full Join
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005a391e05f9220315"
Archived-At: <https://www.postgresql.org/message-id/CAMbWs48Nde1Mv%3DBJv6_vXmRKHMuHZm2Q_g4F6Z3_pn%2B3EV6BGQ%40mail.gmail.com>
Precedence: bulk

--0000000000005a391e05f9220315
Content-Type: text/plain; charset="UTF-8"

I came across $subject and reduced the repro query as below.

create table a (i int);
create table b (i int);
insert into a values (1);
insert into b values (2);
update b set i = 2;

set min_parallel_table_scan_size to 0;
set parallel_tuple_cost to 0;
set parallel_setup_cost to 0;

# explain (costs off) select * from a full join b on a.i = b.i;
                QUERY PLAN
------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Hash Full Join
         Hash Cond: (a.i = b.i)
         ->  Parallel Seq Scan on a
         ->  Parallel Hash
               ->  Parallel Seq Scan on b
(7 rows)

# select * from a full join b on a.i = b.i;
 i | i
---+---
 1 |
(1 row)

Tuple (NULL, 2) is missing from the results.

Thanks
Richard

--0000000000005a391e05f9220315
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I came across $subject and reduced the repro query as belo=
w.<br><br>create table a (i int);<br>create table b (i int);<br>insert into=
 a values (1);<br>insert into b values (2);<br>update b set i =3D 2;<br><br=
>set min_parallel_table_scan_size to 0;<br>set parallel_tuple_cost to 0;<br=
>set parallel_setup_cost to 0;<br><br># explain (costs off) select * from a=
 full join b on a.i =3D b.i;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 QUERY PLAN<br>------------------------------------------<br>=
=C2=A0Gather<br>=C2=A0 =C2=A0Workers Planned: 2<br>=C2=A0 =C2=A0-&gt; =C2=
=A0Parallel Hash Full Join<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Hash Cond: =
(a.i =3D b.i)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Parallel Seq=
 Scan on a<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Parallel Hash<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Parall=
el Seq Scan on b<br>(7 rows)<br><br># select * from a full join b on a.i =
=3D b.i;<br>=C2=A0i | i<br>---+---<br>=C2=A01 |<br>(1 row)<br><br>Tuple (NU=
LL, 2) is missing from the results.<br><br>Thanks<br>Richard<br></div>

--0000000000005a391e05f9220315--