MySql query to return a random row returns unexpected number of rows

I am trying to get a random word from a long list of words in a MySql table: word_list [id*|wword].

My query looks at the moment like this:

SELECT * FROM word_list w where id = (SELECT FLOOR(1 + RAND()*(10-1)))

I thought the above query would return one and only one random row with Id between 1 and 9.

To my surprise it sometimes returns 0, 1, 2 or 3 rows. How is this possible?

Answer

It’s calculating a new random number for each row. When the result of the random expression matches the id, then that row is included in the result.

This should demonstrate how your query is working:

SELECT id, wword, FLOOR(1+RAND()*(10-1)) AS r FROM word_list;

This returns a different random number on each row (which is also what you’re doing with your random expression in the WHERE clause). Sometimes the random number returned as r is equal to id on the respective row. Not often, but occasionally. When these two numbers happen to be equal, that’s when a row would be returned in your query.

What you need instead is to calculate a random number once. For example:

SELECT word_list.*
FROM (SELECT FLOOR(1+RAND()*(10-1)) AS id) AS r
CROSS JOIN word_list USING (id);

Supposing your table is guaranteed to have one row for each id value between 1 and 10, this should return exactly one row.