Re: BUG #15857: Parallel Hash Join makes join instead of exists

On Tue, Jun 18, 2019 at 7:40 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15857
> Logged by: Vladimir Kriukov
> Email address: krujkov(at)gmail(dot)com
> PostgreSQL version: 11.3
> Operating system: CentOS 7
> Description:
>
> CREATE TABLE a(id int);
> ALTER TABLE a ADD CONSTRAINT a_pkey PRIMARY KEY(id);
> INSERT INTO a(id) SELECT generate_series(1, 1000000);
> INSERT INTO a(id) SELECT generate_series(1000001, 10000000);
> CREATE TABLE b(id int, base_id int);
> ALTER TABLE b ADD CONSTRAINT b_pkey PRIMARY KEY(id);
> INSERT INTO b (id) select generate_series(1, 1000000);
> UPDATE b SET base_id = 1000000 - id;
> CREATE TABLE c(id int, base_id int);
> ALTER TABLE c ADD CONSTRAINT c_pkey PRIMARY KEY(id);
> INSERT INTO c (id) SELECT generate_series(1, 1000000);
> UPDATE c SET base_id = id / 10;
>
> VACUUM ANALYZE;
> SET random_page_cost = 1.1;
> SET work_mem = '3276kB';
> SET effective_cache_size = '90GB';
>
> -- This gives an incorrect result of 999991, when 100000 is expected on
> Postgres 11.3 and 12 beta 1.
> SELECT COUNT (*)
> FROM a
> JOIN b
> ON a.id=b.base_id
> WHERE EXISTS (
> SELECT 1
> FROM c
> WHERE c.base_id = a.id
> );
>
>
I think it is correct result. This:

> UPDATE c SET base_id = id / 10;

would result in 9 rows (id from 1 to 9) to be updated with base_id = 0, as
it should with integer division. These 9 rows will not match the condition:

WHERE c.base_id = a.id

as there is no row in a with a.id = 0

Pantelis