On Sun, 1999-10-03 19:22:40 +0300, Jani Tolonen wrote:
> This is a bug. rand() doesn't work in the where statement. We'll
> fix this ASAP, send the patch to list and fix this in the newer
> versions of MySQL.
Maybe I'm completely wrong here, but I don't think that Darla Baker
has found a bug in the basic sense ...
Example (using MySQL V3.22.19b):
CREATE TABLE tmp ( id INT );
INSERT INTO tmp VALUES (1), (2), (3), (4);
SELECT * FROM tmp WHERE id=CEILING(RAND()*4);
The big question is, when does RAND() compute a random number?
Only once for the complete select statement?
Or once for each row?
The current behaviour is to compute a new random number for each row,
so for the above select statement the probability to get a certain
number of result rows is:
75% * 75% * 75% * 75% * (0 over 4) = 31.6% for no row
25% * 75% * 75% * 75% * (1 over 4) = 42.2% for 1 row
25% * 25% * 75% * 75% * (2 over 4) = 21.1% for 2 rows
25% * 25% * 25% * 75% * (3 over 4) = 4.7% for 3 rows
25% * 25% * 25% * 25% * (4 over 4) = 0.4% for 4 rows
And actually, running the query 1000 times I got
300, 439, 214, 43, and 4 results with 0, 1, 2, 3, and 4 rows.
So it does work!
I think, the 'once per row' behaviour makes most sense if RAND() is
used in the SELECT part or with SET in UPDATEs. And 'once per select'
makes more sense in the WHERE and HAVING part.
Think of an UPDATE statement like this:
"Update a random row with a random value"
UPDATE tmp SET id=CEILING(RAND()*4) WHERE id=CEILING(RAND()*4);
Here one would expect, that term2 is computed first giving a certain
fixed random value which is used to choose the relevant columns, and
then for each of these columns term1 is evaluated to give a new random
Is the 'bug fix' about changing behaviour along these lines?
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7