CODEWITHSUNDEEP
mysqlsqlrandommysql-5.6
Alexey Kozulin
Alexey Kozulin

Reputation: 77

mysql Rand() function cause unexpected multirow results

When I try to get random row from table by id using RAND() function I get unexpected unstable results. The following query (where id column is primary key) returns 1, 2 or even more rows:

query and result

I tried next variant as well which produces same result:

SELECT id, word FROM words WHERE id = FLOOR(RAND() * 1000)

I found another solution for my task:

SELECT id, word FROM words ORDER BY RAND() LIMIT 1

But I want to know why MySQL behavior is so unexpected with using so elementary functionality. It scares me.

I experimented in different IDE with the same results.

Upvotes: 3

Views: 53

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272146

The behavior is not unexpected. The RAND() function is evaluated per-row:

SELECT RAND() FROM sometable LIMIT 10

+----------------------+
| RAND()               |
+----------------------+
|   0.7383128467372738 |
|   0.6141578719151746 |
|   0.8558508500976961 |
|   0.4367806654766022 |
|   0.6163508078235674 |
|   0.7714120734216757 |
|   0.0080079743713214 |
|   0.7258036823252251 |
|   0.6049945192458057 |
|   0.8475615799869984 |
+----------------------+

Keeping this in mind, this query:

SELECT * FROM words WHERE id = FLOOR(RAND() * 1000)

means that every row with id between 0 and 999 has 1/1000 probability of being SELECTed!

Upvotes: 3

Related Questions