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)
