Recursive CTEs and randomness - is there something I'm missing?

Pól Ua Laoínecháin <linehanp@xxxxxx> · Fri, 28 Feb 2020 07:28:09 +0000

Hi all,

I'm trying to generate a series of random strings (capital A-Z)
between 2 and 5 characters long (say, 10^6).

I'm using a recursive CTE to achieve this.

A fiddle is available here:

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=206a0c522e853f043c7e633d19852de2

The SQL:

WITH RECURSIVE rand (num, md, a_2_s) AS
(
  SELECT
    1,
    MD5(RANDOM()::TEXT),
    ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
                          FROM GENERATE_SERIES(1, 5)), '')
  UNION
    SELECT num + 1,
    MD5(RANDOM()::TEXT),
    ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
                          FROM GENERATE_SERIES(1, 5)), '')
  FROM rand
  WHERE num < 5
)
SELECT * FROM rand;

A typical result is shown below:

1  974ee059a1902e5ca1ec73c91275984b     GYXYS
2  6cf5a974d5859eae23cdb9c310e3a3bf       YFDPT
3  fa6be95eb720fe6f80c7c8fb6ba11171         YFDPT
4  fa54913b0bb43de0025b153fd71a5334      YFDPT
5  523fab9bdc6c4c51a89e0d901273fb69       YFDPT

Now, the interesting thing is the ARRAY_TO_STRING.

The fact that the last 4 are identical is not a coincidence. If I put
100 in the GENERATE_SERIES, I still get the same result, the first and
second records are different, but ALL subsequent instances of the
ARRAY_TO_STRING are identical! You can test this on the fiddle!

Now, I'm puzzled by this, since the MD5 records are ALWAYS different.

I would be grateful if anybody could explain this - I need the
ARRAY_TO_STRING, because an MD5 cannot be guaranteed to have at least
5 letters.

Should you require any further information, please don't hesitate to contact me.

TIA and rgs,

Pól...