Investigating a MySQL RAND() Bug in Derived Tables (MySQL 5.7.25)

Background : MySQL's RAND() function generates a random floating‑point number in the range >=0 and <1.0 .

The author encountered a strange bug in a mainstream MySQL version and decided to share the findings to help others avoid the same pitfall.

Demonstration : The tests were performed on MySQL 5.7.25.

1. Create a test table

CREATE TABLE test (`id` INT(3) NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=`InnoDB`;

2. Insert 10 rows

INSERT INTO test VALUES(),(),(),(),(),(),(),(),(),();

3. Execute the problematic query multiple times

SELECT sub.rnd FROM (SELECT FLOOR(RAND()*10) rnd FROM test) sub WHERE sub.rnd<3;

The result does not respect the sub.rnd<3 filter, as shown in the following screenshot:

4. Fixing randomness by setting a seed

Setting the seed to 100 and running the inner query repeatedly yields the expected deterministic values (screenshots omitted for brevity), indicating that the issue is not with RAND() itself.

5. Apply the seed in the inner query

SELECT sub.rnd FROM (SELECT FLOOR(RAND(100)*10) rnd FROM test) sub WHERE sub.rnd<3;

The unexpected behavior persists, and an EXPLAIN shows the query plan.

6. Remove the test table

SELECT sub.rnd FROM (SELECT FLOOR(RAND(100)*10) rnd) sub WHERE sub.rnd<3;

Now the query returns correct results, suggesting that materialisation of the derived table influences the bug.

7. Add a large LIMIT to the inner query

SELECT sub.rnd FROM (SELECT FLOOR(RAND(100)*10) rnd FROM test LIMIT 10000) sub WHERE sub.rnd<3;

With the LIMIT, the result is correct, confirming the hypothesis.

8. Flatten the query

SELECT FLOOR(RAND(100)*10) rnd FROM test HAVING rnd<3;

Even after flattening, the bug persists, reinforcing that the issue lies in the re‑evaluation of RAND() when the derived table is not materialised.

9. Root cause hypothesis

The author hypothesised that RAND() is recomputed in the outer layer of a non‑materialised derived table.

A search on the MySQL bug tracker revealed Bug #86624 (reported in mid‑2017) with the status “won’t fix”.

https://bugs.mysql.com/bug.php?id=86624

The official work‑arounds are:

For MySQL 5.7: add LIMIT <a very large number> to the derived table.

For MySQL 8.0: add the NO_MERGE hint.

With these adjustments the query behaves as expected.

— End of article.