List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 3 1999 7:44pm
Subject:Re: Trouble using RAND function
View as plain text  
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);
                    ^^^^^^term1^^^^^^          ^^^^^^term2^^^^^^

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
number each.

Is the 'bug fix' about changing behaviour along these lines?

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
Trouble using RAND functionDarla Baker3 Oct
  • Trouble using RAND functionJani Tolonen3 Oct
  • Re: Trouble using RAND functionDarla Baker3 Oct
    • Re: Trouble using RAND functionJani Tolonen3 Oct
      • Re: Trouble using RAND functionMartin Ramsch3 Oct
        • Re: Trouble using RAND functionMatthias Urlichs4 Oct
          • Re: Trouble using RAND functionMichael Widenius6 Oct
  • Re: Trouble using RAND functionStan P. van de Burgt3 Oct
    • Re: Trouble using RAND functionDarla Baker3 Oct
  • Re: Trouble using RAND functionDarla Baker3 Oct
    • Re: Trouble using RAND functionJani Tolonen4 Oct
  • Re: Trouble using RAND functionDarla Baker4 Oct