Bug #41666 rand() used in scalar functions returns multiple rows
Submitted: 22 Dec 2008 1:01 Modified: 22 Dec 2008 5:07
Reporter: Hal Spitz Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:Ver 14.12 Distrib 5.0.67, for apple-darw OS:MacOS
Assigned to: CPU Architecture:Any
Tags: Rand(), scalar functions, wrong results

[22 Dec 2008 1:01] Hal Spitz
Description:
When using the rand() function in the floor() function in the form of:

select * from table
where primary_key = floor(rand() * 100)

the system returns a random number of rows -- from 0 to multiple rows.  Ignoring the zero row return case, which is wrong, the multiple row return case is extremely concerning as primary_key = anything MUST be return either zero or ONE row.

Note: This bug looks to have the same root cause as bug 39274

How to repeat:
create table testcase (f1 int primary key)

insert into testcase values (1)
insert into testcase values (2)
   .
   .
   .
insert into testcase values (100)

Note: there are no gaps in the data range -- it goes from 1 to 100 for a total of 100 rows

select * from testcase where f1 = floor(1 + rand() * 100)

Run this query multiple times to see the random number of rows returns.  I don't believe it should be possible to return anything other than one row.

Suggested fix:
fix the rand() function to not return random results

Note: random numbers are fine :-)
[22 Dec 2008 4:55] Valeriy Kravchuk
I think this is a duplicate of a feature request, http://bugs.mysql.com/bug.php?id=39274. 

Current behaviour is clearly described in the manual, http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand:

"Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed."
[22 Dec 2008 5:07] Hal Spitz
Please re-read the bug description.

There is only ONE rand() call, and it's inside of a function that returns a scalar just for good measure.   Ignoring that, since the function on the right hand side of an equality test and the field it's being tested against is a PRIMARY KEY it can't EVER return more than one value. Yet it does.

Lastly, the composition of the floor function is from the manual, which clearly states that it returns a single value.
[22 Dec 2008 8:06] Valeriy Kravchuk
Look at the EXPLAIN results, please (my table has only 2 rows):

mysql> explain select * from testcase where f1 = floor(1 + rand() * 100)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testcase
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where; Using index
1 row in set (0.00 sec)

"index" in type filed means that the entire primary key index is scanned, and for each record rand() is called and expression is calculated. This is how it works (and documented) now. 

I agree that this is wrong, that's why we have a request to change current behaviour. This you report adds nothing new to the other bug I mentioned, so it is a duplicate.