0

With given table and data:

CREATE TABLE `people` (
  `id` integer auto_increment,
  `name` varchar(20),
  PRIMARY KEY(`id`)
);

INSERT INTO `people` (`name`) VALUES ('John'), ('Emma'), ('George'), ('Barry'), ('Lisa'), ('Bob'), ('Olivia');

A query:

SET @all = (SELECT COUNT(id) FROM `people`);    
SELECT * FROM `people` WHERE `id` = FLOOR(1 + RAND() * @all);

Results in:

id  name
2   Emma
5   Lisa

or

id  name
3   George
4   Barry

or

id  name
5   Lisa

or 

id  name
1   John
6   Bob

sometimes result has 3 rows

id  name
3   George
4   Barry
7   Olivia

once I got result with 4 rows

id  name
4   Barry
5   Lisa
6   Bob
7   Olivia

or empty result

When I check the value generated by the expression FLOOR(1 + RAND() * @all) it appears to be integer value, eg. 5, not a decimal.

But when I store the value to a variable first and then use the variable in the query, the result is as expected, always one row:

SET @r = FLOOR(1 + RAND() * @all);
SELECT * FROM `people` WHERE `id` = @r;

I guess that when I store the result of FLOOR into a variable it gets converted, but how does mysql compares a value that in result it is equal to many different ones, my first guess was it is rounded somehow, but when I got values that delta of which is > 4 then my next guess would be some bit comparison and decimal bit representation shenanigans, but I have no idea.

Mr_KoKa
  • 598
  • 4
  • 13
  • 1
    This is the wrong approach to select a random record. There *WILL* be gaps between your IDs! Always consilder this. Read: https://stackoverflow.com/a/19422/43959 – Kaii Nov 06 '18 at 00:24
  • 1
    `FLOOR(1 + RAND() * @all)` is evaluated for every row in the `WHERE` clause so you can get multiple matches. See https://stackoverflow.com/questions/45656145/rand-in-where-clause-in-mysql – Nick Nov 06 '18 at 00:26
  • @Kaii, I won't use it, I've used offset with limit, that was just for the question purposes. – Mr_KoKa Nov 06 '18 at 00:48
  • @Nick, That makes sense, thanks. – Mr_KoKa Nov 06 '18 at 00:48

0 Answers0