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: | |
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
[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.