Bug #1719 Strange results with RAND() in WHERE clause
Submitted: 30 Oct 2003 18:23 Modified: 31 Oct 2003 0:01
Reporter: Dean Ellis Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.17 and 4.1.1 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[30 Oct 2003 18:23] Dean Ellis
Description:
Queries with RAND() in the WHERE clause produce strange results.

How to repeat:
CREATE TABLE testbug ( col int );
INSERT INTO testbug VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Repeat a few times:
SELECT col FROM testbug WHERE col = TRUNCATE(RAND()*10+1,0);

Occasionally this produces multi-row result sets (with ROUND, TRUNCATE and CASTing  as UNSIGNED).

Suggested fix:
n/a
[31 Oct 2003 0:01] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Why do you think it's a bug.
The behaviour is perfectly expected.

when MySQL tests "col = TRUNCATE(RAND()*10+1,0)", RAND is called for each row, and it of course returns different values each time. You can get any number of rows from 0 to 10.
[31 Oct 2003 6:56] Dean Ellis
I was unsure if it was a bug or designed behaviour, but my expectation was that it would evaluate TRUNCATE(RAND()*10,0) once and treat it as a constant, rather than evaluating it for every row.