How this can return more than 1 result?

How this query can return more than 1 row?

SELECT username FROM phpbb_users WHERE user_id = (SELECT (FLOOR(RAND() * (5050 - 50 + 1)) + 50));

Table meta:

``
CREATE TABLE phpbb_users (
user_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
user_type tinyint(2) NOT NULL DEFAULT '0',
group_id mediumint(8) unsigned NOT NULL DEFAULT '3',
user_permissions mediumtext COLLATE utf8_bin NOT NULL,
user_perm_from mediumint(8) unsigned NOT NULL DEFAULT '0',
user_ip varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
user_regdate int(11) unsigned NOT NULL DEFAULT '0',
username varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
username_clean varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
user_password varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
user_passchg int(11) unsigned NOT NULL DEFAULT '0',
user_pass_convert tinyint(1) unsigned NOT NULL DEFAULT '0',
user_email varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
user_email_hash bigint(20) NOT NULL DEFAULT '0',
user_birthday varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
user_lastvisit int(11) unsigned NOT NULL DEFAULT '0',
user_lastmark int(11) unsigned NOT NULL DEFAULT '0',
user_lastpost_time int(11) unsigned NOT NULL DEFAULT '0',
user_lastpage varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
user_last_confirm_key varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
user_last_search int(11) unsigned NOT NULL DEFAULT '0',
user_warnings tinyint(4) NOT NULL DEFAULT '0',
user_last_warning int(11) unsigned NOT NULL DEFAULT '0',
user_login_attempts tinyint(4) NOT NULL DEFAULT '0',
user_inactive_reason tinyint(2) NOT NULL DEFAULT '0',
user_inactive_time int(11) unsigned NOT NULL DEFAULT '0',
user_posts mediumint(8) unsigned NOT NULL DEFAULT '0',
user_lang varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '',
user_timezone decimal(5,2) NOT NULL DEFAULT '0.00',
user_dst` tinyint(1) unsigned NOT NULL

The reason is that the WHERE condition:

WHERE user_id = (SELECT (FLOOR(RAND() * (5050 - 50 + 1)) + 50))

is evaluated once per row. So, for every row, a new call to RAND() is made and thus the query can return 0, 1, 2, 3 or more rows. There is a (albeit very small) chance that the query will return all rows of the table!

A similar problem with a DELETE statement, where the call to RAND() was in the ORDER BY clause: Problem with MySQL subquery

If you want the query to never return more than 1 row, you can evaluate the random number before the query, so the RAND() is called once:

SET @random_number = FLOOR(RAND() * (5050 - 50 + 1)) + 50 ;
SELECT username FROM phpbb_users WHERE user_id = @random_number ;

As @a1ex07 mentioned, you could also use a derived table:

SELECT u.username 
FROM
    (SELECT FLOOR(RAND() * (5050 - 50 + 1)) + 50 AS random_number) AS r
  JOIN 
    phpbb_users AS u
      ON u.user_id = r.random_number ;

As a warning though, I can't find any documentation for this behaviour so it's quite possible that it may change in future versions (only tested in 5.1 and 5.5 versions.) The various optimizer enhancements in versions 5.6, 5.7 and MariaDB (5.1, 5.3+) may have already altered this behaviour.

StackExchange Database Administrators Q#89465, answer score: 11