MySQL Bugs: #41596: rand() seem return more than one result

Bug #41596	rand() seem return more than one result
Submitted:	18 Dec 2008 17:03	Modified:	19 Dec 2008 15:59
Reporter:	Sylvain Lasnier	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	Ver 14.12 Distrib 5.0.67, for debian-lin	OS:	Linux (Ubuntu 8.10)
Assigned to:		CPU Architecture:	Any
Tags:	rand

Description:
Hi,

I try to get 1 random row from a table.
Sometime, I get more than 1 result.

How to repeat:
mysql>  SELECT id FROM `8iq1_link` WHERE id in (round(RAND()*10000));
+------+
| id   |
+------+
|  307 | 
| 2403 | 
| 9966 | 
+------+
3 rows in set (0.02 sec)

mysql>  SELECT id FROM `8iq1_link` WHERE id in (round(RAND()*10000));
+------+
| id   |
+------+
| 1586 | 
| 5503 | 
+------+
2 rows in set (0.02 sec)

mysql>  SELECT id FROM `8iq1_link` WHERE id =(round(RAND()*10000));
+------+
| id   |
+------+
| 3714 | 
| 8292 | 
+------+
2 rows in set (0.02 sec)

mysql> explain 8iq1_link;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | 
| uid         | int(9)              | NO   |     | NULL    |                | 
| name        | varchar(255)        | NO   | MUL |         |                | 
| url         | varchar(150)        | NO   | UNI |         |                | 
| description | text                | NO   |     | NULL    |                | 
| keywords    | text                | YES  |     | NULL    |                | 
| state       | tinyint(1)          | NO   |     | 0       |                | 
| category    | int(4) unsigned     | NO   | MUL | 0       |                | 
| pr          | tinyint(2)          | NO   |     | 0       |                | 
| image       | varchar(200)        | NO   |     |         |                | 
| hits        | int(6)              | NO   |     | 0       |                | 
| prio        | tinyint(1) unsigned | NO   |     | 0       |                | 
| vote        | int(4)              | NO   |     | 0       |                | 
| broken      | tinyint(1)          | NO   |     | 0       |                | 
| email       | varchar(255)        | NO   |     | NULL    |                | 
| date        | date                | NO   |     | NULL    |                | 
+-------------+---------------------+------+-----+---------+----------------+

There are no table update during this test

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional Info;

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

miguel@hegel:~/dbs$ 5.0/bin/mysql -uroot -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.76-valgrind-max-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select round(RAND()*10000);
Field   1:  `round(RAND()*10000)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 4
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+---------------------+
| round(RAND()*10000) |
+---------------------+
|                9894 | 
+---------------------+
1 row in set (0.00 sec)

mysql>

Hi Miguel,
Thanks for your quick answer.

I make a test with truncate(), floor() and ceiling(X) functions which 
"Returns the smallest integer value not less than X." 
see http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_ceil 

Same strange results.
I read nothing in your links which can explain that.
What I see is function ceiling() and floor() don't return one value but more values and = symbol operator is able to compare several values in same time.

I don't think this is a feature.
I would replace "order by rand() limit 10" syntax by another SQL solution to avoid table scan 20.000 rows for each request to print a web page.

Many thanks
Sylvain

Examples of problem :

mysql> SELECT id FROM `8iq1_link` WHERE id =  (ceiling(rand()*10000));
Field   1:  `id`
Catalog:    `def`
Database:   `prod_annuairemoto`
Table:      `8iq1_link`
Org_table:  `8iq1_link`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 4
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY 

+------+
| id   |
+------+
|   42 | 
| 1852 | 
| 3585 | 
| 7922 | 
+------+
4 rows in set (0.02 sec)

mysql> SELECT id FROM `8iq1_link` WHERE id =  (floor(rand()*10000));
Field   1:  `id`
Catalog:    `def`
Database:   `prod_annuairemoto`
Table:      `8iq1_link`
Org_table:  `8iq1_link`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 4
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY 

+------+
| id   |
+------+
| 3201 | 
| 8403 | 
| 8781 | 
+------+
3 rows in set (0.01 sec)

This is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand:

"RAND() in a WHERE clause is re-evaluated every time the WHERE is executed."

See also related feature request to change this, http://bugs.mysql.com/bug.php?id=39274.

> "RAND() in a WHERE clause is re-evaluated every time the WHERE is executed."

My God.
Thanks Valeriy

Will use variable