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:
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: Rand()

[12 Aug 2013 17:44] matteo sisti sette

"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:

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.
[31 Aug 2013 12:40] Sveta Smirnova
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?
[31 Aug 2013 20:20] matteo sisti sette
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.
[4 Oct 2013 19:31] Sveta Smirnova
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.
[5 Sep 2014 13:42] Jon Stephens
Reassigning to myself.
[8 Sep 2014 14:17] Jon Stephens
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)

| 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+.
[8 Sep 2014 14:42] Jon Stephens
Item 2 should read, "2. The maximum value accepted is 18446744073709551615 which is the same as for BIGINT UNSIGNED." 

(Instead of "BIGINT SIGNED".)