CODEWITHSUNDEEP
mysql
James Geddes
James Geddes

Reputation: 884

Why does RAND sometimes produce multiple results in a MySQL call?

I have table agent which includes 13 agent IDs and agent names.

agent_id|agent_name|
--------|----------|
       1|Jack      |
       2|Jill      |
       3|Jo        |
...

When I run this query...

select agent_name from agent where agent_id = (FLOOR( 1 + RAND( ) * 13 ))

...MySQL sometimes returns 0 names and sometimes returns many names. Since (FLOOR( 1 + RAND( ) * 13 )) on its own always seems to return a single, non-zero integer, I would expect to get a single name back, but this is not the case. On testing the above query, the following number of names are returned on each execution;

Execution | Total Names Returned
1         | 3
2         | 2
3         | 1
4         | 0
5         | 1
6         | 1
7         | 0
8         | 0
9         | 1
10        | 4

Clearly, when one runs...

select agent_name from agent where agent_id = 3

...the same, single, name is returned each time.

I see that the docs explain that

RAND() in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join). Thus, for optimizer purposes, RAND() is not a constant value and cannot be used for index optimizations

I am not sure why this would mean that a single call would return many rows.

If I add LIMIT 1

select * from agent a where agent_id = (FLOOR( 1 + RAND() * 13 )) limit 1

...then the query sometimes returns NULL.

Questions

Upvotes: 0

Views: 237

Answers (2)

dkasipovic
dkasipovic

Reputation: 6120

As the documentation says, it is evaluated for each row. It means, the following:

  • MySQL gets all rows
  • For each row, executes RAND() and if RAND() == id, return the row

For example

Get all rows

  • ID = 1, RAND() = 3, no return
  • ID = 2, RAND() = 2, return
  • ID = 3, RAND() = 4, no return
  • ID = 4, RAND() = 4, return

In this case, you have two results.

If you want to get random agent, maybe better approach would be 

SELECT * FROM `agent` ORDER BY RAND() LIMIT 1

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

I assume that your first query is sometimes returning multiple records because RAND() is being evaluted for each record in the query. If you just want a single random agent, then use:

SELECT agent_name
FROM agent
ORDER BY RAND()
LIMIT 1;

To further explain your current observations, the following expression is being evaluated once per each agent record:

(FLOOR( 1 + RAND( ) * 13 ))

Imagine that for each record, the above happens, by chance, to equal the agent_id. If so, then your first query would return all 13 records. By the way, the documentation link you cited basically says this.

Upvotes: 2

Related Questions