MySQL Bugs: #27731: incorrect expression for generating random integer

Bug #27731	incorrect expression for generating random integer
Submitted:	10 Apr 2007 14:05	Modified:	1 Jun 2007 22:20
Reporter:	Jasper Knockaert
Status:	Closed
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.0.37	OS:	Any
Assigned to:	Paul DuBois	Target Version:
Triage:	D4 (Minor)

Description:
The in the manual (paragraph 12.5.2) suggested formula FLOOR(i + RAND() * (j – i) to
generate a random integer R in the range i<=R<=j is incorrect. Not only there is a
missing bracket, moreover the result is not random. It is easy to verify this by applying
the expression to the 0-1 range (i=0, j=1). The formula then boils down to FLOOR(RAND())
which evaluates to 0 with a probability very close to 1.

How to repeat:
SELECT FLOOR(RAND());

Suggested fix:
A more correct expression would be mod(floor(rand()*(j-i+1)),(j-i+1))+i

Thank you for a reasonable documentation request. Indeed at this page,
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html, we have a missing
bracket:

"To obtain a random integer R in the range i <= R <  j, use the expression FLOOR(i +
RAND() * (j – i)."

See also http://bugs.mysql.com/bug.php?id=17502 for discussion on what formuli to use.

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.

I added the missing parenthesis to the formula.

I did not change the formula. The manual states that the formula is applicable to the
range i <= R < j
(not I <= R <= j as the bug report says), and thus if i = 0 and j = 1, the range of
return values will be 0 <= R < 1, that is, always 0.