MySQL Bugs: #1494: RAND( a) return non-constant values when a is a constant column

Bug #1494	RAND( a) return non-constant values when a is a constant column
Submitted:	7 Oct 2003 4:16	Modified:	24 Oct 2003 4:56
Reporter:	eric jacolin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.12	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Consider a table with a constant integer column:

mysql> describe test1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> select * from test1;
+------+
| id   |
+------+
|    1 |
|    1 |
|    1 |
+------+

Now the following query:

mysql> select
    -> id, rand( id ) as rand
    -> from test1;
+------+------------------+
| id   | rand             |
+------+------------------+
|    1 | 0.15522042769494 |
|    1 | 0.62088174151339 |
|    1 | 0.63874745521578 |
+------+------------------+

rand should produce constant values, but doesn't!

How to repeat:
create a similar test table

Hi!

For the below:

mysql> select
    -> id, rand( id ) as rand
    -> from test1;

rand() creates a random variable, it does not select a random row. The behavior is what should be expected.  What I believe you want is ORDER BY RAND().

Not quite.

What I meant to do is obtain a random number that is constant for each id.
Therefore I seeded RAND() with the id column.

I still believe it is a bug.

Hi!

RAND(ID) does not mean that it will return a constant for invocation.The 'ID' is used to initialize the random generator for the first callto the RAND() function;  After that all future calls will return arandom number.  

If 'ID' is constant it means that the created randomnumbers will always be the same series, not that the result is constant.

If this would not be the case, you could never do something like:

SELECT * FROM table_name ORDER BY RAND(1);

Which returns the rows in random order, and still allow you to repeatthe query.

The docs team will update the documentation to make this a bit clearer.

Well, how then do you reconcile your argument with the following:

mysql> select rand(12);
+------------------+
| rand(12)         |
+------------------+
| 0.15741774081943 |
+------------------+
1 row in set (0.00 sec)

mysql> select rand(12);
+------------------+
| rand(12)         |
+------------------+
| 0.15741774081943 |
+------------------+
1 row in set (0.01 sec)

well, I would never have thought of using:
SELECT * FROM table_name ORDER BY RAND(1);
instead of the usual:
SELECT * FROM table_name ORDER BY RAND();

just because I would have expected RAND() to return a random number, but not RAND(1).

(apologies if I use "random" in a non-technical way, I trust that the context makes quite clear what I mean)

This is NOT a bug.

RAND(ID) does not mean that it will return a constant for invocation.
The 'ID' is used to initialize the random generator for the first call
to the RAND() function;  After that all future calls will return a
new random number.  The argument to RAND() is mainly used when you want to
get 'random, but repeatable' result.  If you issue the same query again
with the same RAND() argument you will get back the same result set as last time, which is very useful for testing...

If you always want to use the same RAND() result for all rows, you should do something like:

SET @A=RAND();
SELECT ... WHERE column=@A;

Regards,
Monty