title: PostgreSQL random in subquery
body: <p>I try to generate several thousands of random test data for some synthetic load testing, but I've run into a weird bug I don't understand.</p>
  
  <p>Here's the basic minimum reproducible code I managed to narrow it down. Let's create a table that has some unique rows:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE vals (
      value INT PRIMARY KEY
  );
  INSERT INTO vals SELECT generate_series(1, 10);
  </code></pre>
  
  <p>Let's check there are unique values:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT array(SELECT * FROM vals);
  
  &gt;&gt; {1,2,3,4,5,6,7,8,9,10}
  </code></pre>
  
  <p>Yep, that's good. Now let's create a table that has lots of user data that references to the <code>vals</code> table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE tmp (
      a INT REFERENCES vals,
      b INT[]
  );
  </code></pre>
  
  <p>And fill it with lots of random data:</p>
  
  <pre class="lang-sql prettyprint-override"><code>  WITH test_count AS (SELECT generate_series(1, 10000))
      -- some more CTEs, so I cannot give up on them
  INSERT
    INTO tmp
  SELECT
         (SELECT value FROM vals ORDER BY random() LIMIT 1),
         array(SELECT value FROM vals WHERE random() &gt; 0.85)
    FROM test_count;
  </code></pre>
  
  <p>But when we check it, there are 10000 rows filled with the same values:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT a, b FROM tmp;
  
  &gt;&gt; a | b
     ---------
     2 | {8,5}
  </code></pre>
  
  <p>I've found out that sometimes postgres optimizes calls <code>random()</code> in the same row to the same value, e.g. <code>SELECT random(), random()</code> would return <code>0.345, 0.345</code>: <a href="https://dba.stackexchange.com/questions/231100/postgresql-random-function-returns-duplicate-results-when-order-by-random-used-i">link</a>.</p>
  
  <p>But in this case random in-row are different, but random over all the rows is the same. </p>
  
  <p>➥ What is the way to fix it?</p>