BUG #15324: Non-deterministic behaviour from parallelised sub-query

The following bug has been logged on the website:

Bug reference: 15324
Logged by: Andrew Fletcher
Email address: andy(at)prestigedigital(dot)com
PostgreSQL version: 10.5
Operating system: macOS High Sierra 10.13.6
Description:

Reproductions -

Make sure postgresql.conf sets max_parallel_workers_per_gather to 2 or
more

max_parallel_workers_per_gather = 2

Repro 1:

Create table from the following sql file -

https://www.dropbox.com/s/3cm643vmugcgkxh/events.sql.zip?dl=0

Execute this query (multiple times!)

select * from events where account in (select account from events where
data->>'page' = 'success.html' limit 3);

Incorrect output -

account | type | data
---------+----------+--------------------------
304873 | pageview | {"page": "success.html"}
304875 | pageview | {"page": "c.html"}
304875 | pageview | {"page": "success.html"}
304885 | pageview | {"page": "a.html"}
304885 | pageview | {"page": "success.html"}
(5 rows)

Correct output -

account | type | data
---------+----------+--------------------------
304873 | pageview | {"page": "success.html"}
304875 | pageview | {"page": "c.html"}
304875 | pageview | {"page": "success.html"}
304885 | pageview | {"page": "a.html"}
304885 | pageview | {"page": "success.html"}
304873 | pageview | {"page": "b.html"}
(6 rows)

Repro 2 -

Create table from the following sql file -

https://www.dropbox.com/s/mzglgm4a5x1mqno/repro1.sql.zip?dl=0

Execute this query (multiple times!)

select * from repro1 where account in (select account from repro1 where page
= 'success.html' limit 3);

Incorrect Output -

account | page
---------+--------------
14 | a.html
14 | success.html
65 | b.html
65 | success.html
80 | b.html
80 | success.html
24084 | a.html
24084 | success.html
24085 | c.html
24085 | success.html
24095 | a.html
24095 | success.html
(12 rows)

Correct output -

account | page
---------+--------------
14 | a.html
14 | success.html
65 | b.html
65 | success.html
80 | b.html
80 | success.html
(6 rows)

Full version string -

PostgreSQL 10.5 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version
9.1.0 (clang-902.0.39.2), 64-bit

Also reproduced (with slightly different non-determinism) on -

PostgreSQL 9.6.3, compiled by Visual C++ build 1800, 32-bit on Windows 10
Pro 1709, build 16299.547

Known workarounds -

1. max_parallel_workers_per_gather = 0
2. Add order by account asc to the subquery (works for both repros)