MySQL Bugs: #1719: Strange results with RAND() in WHERE clause

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

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

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.

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.