Thread: parallel sequential scan returns extraneous rows

I have found a nasty bug when using parallel sequential scans with an =
exists clause on postgresql 9.6.1.  I have found that the rows returned =
using parallel sequential scan plans are incorrect (though I haven=E2=80=99=
t dug sufficiently to know in what ways).  See below for an example of =
the issue.

denver=3D# set max_parallel_workers_per_gather =3D 0;
SET
denver=3D# select count(*)
denver-# from users u=20
denver-# join address a on (a.users_id =3D u.id)=20
denver-# where exists (select 1 from address where users_id =3D u.id)
  count =20
---------
 9486910
(1 row)

                                                  QUERY PLAN             =
                                     =20
=
--------------------------------------------------------------------------=
-------------------------------------
 Aggregate  (cost=3D2117655.96..2117655.97 rows=3D1 width=3D8)
   ->  Merge Join  (cost=3D1257.34..2094242.48 rows=3D9365393 width=3D0)
         Merge Cond: (u.id =3D a.users_id)
         ->  Merge Semi Join  (cost=3D495.43..1607025.52 rows=3D2824687 =
width=3D8)
               Merge Cond: (u.id =3D address.users_id)
               ->  Index Only Scan using users_pk on users u  =
(cost=3D0.43..1018930.31 rows=3D11648927 width=3D4)
               ->  Index Only Scan using address_idx01 on address  =
(cost=3D0.43..456495.80 rows=3D9365393 width=3D4)
         ->  Index Only Scan using address_idx01 on address a  =
(cost=3D0.43..456495.80 rows=3D9365393 width=3D4)
(8 rows)



denver=3D# set max_parallel_workers_per_gather =3D 1;
SET
denver=3D# select count(*)
denver-# from users u=20
denver-# join address a on (a.users_id =3D u.id)=20
denver-# where exists (select 1 from address where users_id =3D u.id);
  count  =20
----------
 29288954
(1 row)

                                               QUERY PLAN                =
                               =20
=
--------------------------------------------------------------------------=
-------------------------------
 Aggregate  (cost=3D1889898.47..1889898.48 rows=3D1 width=3D8)
   ->  Hash Join  (cost=3D1401575.70..1866484.99 rows=3D9365393 width=3D0)=

         Hash Cond: (a.users_id =3D u.id)
         ->  Seq Scan on address a  (cost=3D0.00..299463.93 rows=3D9365393=
 width=3D4)
         ->  Hash  (cost=3D1355233.12..1355233.12 rows=3D2824687 =
width=3D8)
               ->  Gather  (cost=3D571820.86..1355233.12 rows=3D2824687 =
width=3D8)
                     Workers Planned: 1
                     ->  Hash Join  (cost=3D570820.86..1071764.42 =
rows=3D2824687 width=3D8)
                           Hash Cond: (address.users_id =3D u.id)
                           ->  Parallel Seq Scan on address  =
(cost=3D0.00..260900.55 rows=3D5509055 width=3D4)
                           ->  Hash  (cost=3D379705.27..379705.27 =
rows=3D11648927 width=3D4)
                                 ->  Seq Scan on users u  =
(cost=3D0.00..379705.27 rows=3D11648927 width=3D4)
(12 rows)

Michael Day <blake@rcmail.com> writes:
> I have found a nasty bug when using parallel sequential scans with an ex=
ists clause on postgresql 9.6.1.  I have found that the rows returned usin=
g parallel sequential scan plans are incorrect (though I haven=E2=80=99t d=
ug sufficiently to know in what ways).  See below for an example of the is=
sue.

Hm, looks like a planner error: it seems to be forgetting that the join
to "address" should be a semijoin.  "address" should either be on the
inside of a "Semi" join (as in your first, correct-looking plan) or be
passed through a unique-ification stage such as a HashAgg.  Clearly,
neither thing is happening in the second plan.

I couldn't reproduce this in a bit of trying, however.  Can you come
up with a self-contained test case?

            regards, tom lane

I was able to reproduce with this set of data.

create table users (id integer);
create table address (id integer, users_id integer);

insert into users select s from generate_series(1,1000000) s;
insert into address select s, s/2 from generate_series(1,2000000) s;

analyze users;
analyze address;

set max_parallel_workers_per_gather =3D 0;

select count(*)
from users u=20
join address a on (a.users_id =3D u.id)=20
where exists (select 1 from address where users_id =3D u.id);

set max_parallel_workers_per_gather =3D 1;

select count(*)
from users u=20
join address a on (a.users_id =3D u.id)=20
where exists (select 1 from address where users_id =3D u.id);


On 11/29/16, 11:19 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

    Michael Day <blake@rcmail.com> writes:
    > I have found a nasty bug when using parallel sequential scans with an=
 exists clause on postgresql 9.6.1.  I have found that the rows returned usi=
ng parallel sequential scan plans are incorrect (though I haven=E2=80=99t dug suff=
iciently to know in what ways).  See below for an example of the issue.
   =20
    Hm, looks like a planner error: it seems to be forgetting that the join
    to "address" should be a semijoin.  "address" should either be on the
    inside of a "Semi" join (as in your first, correct-looking plan) or be
    passed through a unique-ification stage such as a HashAgg.  Clearly,
    neither thing is happening in the second plan.
   =20
    I couldn't reproduce this in a bit of trying, however.  Can you come
    up with a self-contained test case?
   =20
                regards, tom lane
   =20

Michael Day <blake@rcmail.com> writes:
> I was able to reproduce with this set of data.

Ah, thanks for the test case!  Looks like the handling of
JOIN_UNIQUE_INNER cases for parallel plans is completely confused ---
it forgot about needing to unique-ify the inner rel, and I'm rather
surprised it didn't trip over some Asserts while at it.

Will fix, thanks.

            regards, tom lane