BUG #15324: Non-deterministic behaviour from parallelised sub-query - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15324
Logged by:          Andrew Fletcher
Email address:      andy@prestigedigital.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)