MySQL Bugs: #69999: Questionable RAND() argument range/type/error

Bug #69999	Questionable RAND() argument range/type/error
Submitted:	12 Aug 2013 17:44	Modified:	27 Nov 2014 21:06
Reporter:	matteo sisti sette	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Rand()

Description:
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

"If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values"

It should be specified which is the maximum allowed integer that can be used as seed.

How to repeat:
Read:
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

Try to figure out which is the maximum legal value for N in RAND(N)

(I know this sounds sarchastic: I'm writing this because the form tells me this field is required)

Suggested fix:
Specify which is the maximum allowed integer that can be used as seed.

Thank you for the report.

Maximum value for INTEGER is specified at http://dev.mysql.com/doc/refman/5.6/en/integer-types.html Why do you think it should be specified elsewhere? Why indication of the type is not enough for you?

To me it's not that obvious that the SEED can be as big as the biggest integer allowed by the integer type. We're talking about the seed, not the range. The seed sounds like some number to "start with", and it seems to me legitimate to wonder whether any integer value is really allowed or further restrictions apply. After all, that number is going to be manipulated, like multiplied or added to something, isn't it? To me it looks like a reasonable question whether or not too big an integer can produce an overflow, and hence unexpected results, or if it is safe.

Thank you for the feedback.

I can accept this is not very clear for some of users, therefore set this report as "Verified", although I still believe specifying type of the seed is enough.

Reassigning to myself.

This is what I find using a recent 5.6.22-bzr built 20140901:

1. RAND() accepts not only integers, but values with decimal fractional parts as well.

2. The maximum value accepted is 18446744073709551615 which is the same as for BIGINT SIGNED.

3. The minimum accepted value is -9223372036854775808 which is the same as for BIGINT SIGNED.

4. Out-of-range values trigger the nonsensical warning shown here: 

ysql> SELECT RAND(-18446744073709551614);
+-----------------------------+
| RAND(-18446744073709551614) |
+-----------------------------+
|         0.15522042769493574 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

I have a very difficult time believing that this is at all by design.

Therefore I am re-opening this as a Server bug. Development needs to verify that all of this is by design, or fix it. Unless someone suggests something better, I propose that we align the behaviour with the documentation as follows:

Argument type: integer (range same as any other integer type; i.e., kindly decide whether it's signed or unsigned, thanks).

Return appropriate warning/error for out-of-range values.

IMO should be fixed in 5.1+ or at least 5.5+.

Correction:
Item 2 should read, "2. The maximum value accepted is 18446744073709551615 which is the same as for BIGINT UNSIGNED." 

(Instead of "BIGINT SIGNED".)