I think the thing I find most interesting is that I got the original idea from
reading the O'Reilly book "MySQL and mSQL" where on page 110 there is an
example which uses virtually the same query:
SELECT name
FROM entries
WHERE id = ROUND( (RAND()*34) + 1)
and in the comments it states "...which should match one of the ID numbers in
the table."
Darla
Martin Ramsch wrote:
> 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
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread14981@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
