Bug #99340 RAND not always returning the same value for equal argument N
Submitted: 23 Apr 2020 20:36 Modified: 24 Apr 2020 6:47
Reporter: Frederic Weigand Warr Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.19, 5.6.47, 5.7.29 OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2020 20:36] Frederic Weigand Warr
Description:
In some cases involving joins and unique keys, the RAND(N) function returns different values for the same argument N, contradicting the documentation which states: 

With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of RAND(). One implication of this behavior is that for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values.

The test case below is the simplest for which I was able to reproduce the problem. Removing the keys from the CREATE TABLE statements, or the AND clause from the first JOIN clause all resulted in expected behavior. The ORDER BY clause is not necessary to reproduce the problem, but illustrates how it can cause significantly incorrect results.

How to repeat:
CREATE TABLE test_i  (i INT PRIMARY KEY);
CREATE TABLE test_j  (j INT PRIMARY KEY);
CREATE TABLE test_ij (i INT, j INT, a INT, UNIQUE KEY (i, a));
CREATE TABLE test_ik (i INT, k INT);

INSERT INTO test_i  VALUES (1);
INSERT INTO test_j  VALUES (2);
INSERT INTO test_ij VALUES (1, 2, 1);
INSERT INTO test_ik VALUES (1, 3), (1, 4);

SELECT 
test_i.i, test_j.j, RAND(test_j.j) AS rand_j, k
FROM test_i
JOIN test_ij ON test_i.i  = test_ij.i AND test_ij.a = 1
JOIN test_j  ON test_ij.j = test_j.j
JOIN test_ik ON test_ik.i = test_i.i
WHERE test_i.i = 1
ORDER BY rand_j, k;

+---+---+---------------------+------+
| i | j | rand_j              | k    |
+---+---+---------------------+------+
| 1 | 2 | 0.12234661925802624 |    4 |
| 1 | 2 |  0.6555866465490187 |    3 |
+---+---+---------------------+------+
2 rows in set (0.00 sec)
[24 Apr 2020 6:47] MySQL Verification Team
Hello Frederic,

Thank you for the report and test case.
This is seen even with 5.6.47, 5.7.29 and 8.0.19 builds with provided test case.

regards,
Umesh