Bug #15473 RAND(N) no longer produces repeatable sequence
Submitted: 5 Dec 2005 1:32 Modified: 17 Aug 2006 13:42
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0, 5.1 OS:
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[5 Dec 2005 1:32] Paul Dubois
Description:
The manual description for the RAND() function states:

RAND(),
RAND(N)

 Returns a random floating-point value v between 0 and 1
 inclusive (that is, in the range 0 <= v <= 1.0). If an integer
 argument N is specified, it is used as the seed value, which
 produces a repeatable sequence.

The last sentence was true in MySQL 3.23. It seems no longer
to be true in 4.0 and up.

How to repeat:
Test script:

SELECT RAND(1), RAND(), RAND(), RAND();
SELECT RAND(1), RAND(), RAND(), RAND();

Result in 3.23.59 (repeatable sequence):

+------------------+------------------+------------------+------------------+
| RAND(1)          | RAND()           | RAND()           | RAND()           |
+------------------+------------------+------------------+------------------+
| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 | 0.78874910230632 |
+------------------+------------------+------------------+------------------+
+------------------+------------------+------------------+------------------+
| RAND(1)          | RAND()           | RAND()           | RAND()           |
+------------------+------------------+------------------+------------------+
| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 | 0.78874910230632 |
+------------------+------------------+------------------+------------------+

Result in 4.0.27, 4.1.16, 5.0.17, 5.1.4 (not repeatable sequence):

+------------------+------------------+------------------+------------------+
| RAND(1)          | RAND()           | RAND()           | RAND()           |
+------------------+------------------+------------------+------------------+
| 0.40540353712198 | 0.61610276123146 | 0.51825823869394 | 0.74298294050897 |
+------------------+------------------+------------------+------------------+
+------------------+------------------+------------------+------------------+
| RAND(1)          | RAND()           | RAND()           | RAND()           |
+------------------+------------------+------------------+------------------+
| 0.40540353712198 | 0.16014001719667 | 0.57175129519007 | 0.37833645509401 |
+------------------+------------------+------------------+------------------+

(The first column in each line will be the same, but repeated executions
of the script produces different values in columns 2 through 4.)

Suggested fix:
Either this is an unintentional change (in which case it should
be fixed), or it is an intentional changed (in which case the manual
should be changed). But if it's an intentional change, how do you
obtain a repeatable sequence?
[9 Jul 2006 0:15] Paul Dubois
Any news on this?
[12 Jul 2006 17:34] Chad MILLER
Huh, this looks like it should have been tested in the top of the "func_math" test file:
   select rand(999999),rand();
[12 Jul 2006 17:48] Mats Kindahl
Having a non-deterministic sequence of random numbers will break replication (unless row-based replication is used, which is not an option for anything before 5.1). To be able to replicate statements containing RAND(), a deterministic pseudo-random number generator *has* to be used, so just documenting this is not an option.
[14 Jul 2006 1:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9156
[14 Jul 2006 2:07] Chad MILLER
This doesn't affect replication, which doesn't use rand(N), but instead sets the seed values directly.
[19 Jul 2006 23:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9357
[21 Jul 2006 15:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9417
[11 Aug 2006 14:00] Michael Widenius
The current behaviour is not a bug but instead intended behaviour.
(3.23 had a bug that was fixed a long time ago...)

The idea here is that one RAND() function should not depend on another RAND() function, just becasue they are in the same statement.

Iea, just becasue you want to change one RAND() function on a line to have a 'repeated series' over many rows, doesn't meant that all other RAND() functions should change. (If you want all to have repeated series, you should give every RAND() function one argument)

The current behaviour is replication safe as we store the inital seed value for the first RAND() value and all RAND() without arguments uses the same seed.

The proposed patch causes probles as RAND(1) gives a different value in case of:

SELECT RAND(1);
and
SELECT RAND(1),RAND(2);

(With the proposed patch, the second select is treated as 'SELECT RAND(1),RAND(2)', which is clearly wrong.
[11 Aug 2006 14:46] Chad MILLER
No patch applied, but documentation needs updating:

The argument to RAND is not a seed for all upcoming RAND()s;  It is a seed /only/ in this query, for this column. So, for instance selecting  RAND(1)  from a table with several rows will yield a repeatable sequence down the column, but selecting RAND(1), RAND(), RAND() will not.

The only way to mutate the global RNG seed, in the style of the POSIX srand() function is by setting the @@rand_seed1 and @@rand_seed2 variables, like so:

set @@rand_seed1=10000000, @@rand_seed2=1000000;
[11 Aug 2006 17:55] Paul Dubois
This has become a documentation issue. Assigning to myself,
since I raised the issue in the first place.
[17 Aug 2006 13:42] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.