Bug #39274 SELECT on PK with ROUND(RAND()) give wrong errors
Submitted: 5 Sep 2008 13:15 Modified: 2 Nov 2019 13:17
Reporter: Oli Sennhauser Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.66a, 5.1.26, 5.1.27-ndb-6.3.17 OS:Any
Assigned to: CPU Architecture:Any

[5 Sep 2008 13:15] Oli Sennhauser
Description:
Selecting random data from a table with ROUND(RAND()) gives sometimes ZERO and sometimes more than one row back!

How to repeat:
CREATE TABLE test (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT
  , data VARCHAR(32)
  , ts   TIMESTAMP
  , PRIMARY KEY (id)
) ENGINE = MYISAM;

INSERT INTO test VALUES (NULL, 'Blabla', NULL);
INSERT INTO test SELECT NULL, data, NULL FROM test;
... 5x

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
Empty set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
| 12 |
| 14 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
|  9 |
| 10 |
| 12 |
| 13 |
| 14 |
+----+
5 rows in set (0.00 sec)

mysql> select @rand := ROUND(RAND()*(16-8)+8, 0), test.* from test where id = @rand;
+------------------------------------+----+--------+---------------------+
| @rand := ROUND(RAND()*(16-8)+8, 0) | id | data   | ts                  |
+------------------------------------+----+--------+---------------------+
|                                 10 |  9 | Blabla | 2008-09-05 14:25:53 |
|                                 12 | 10 | Blabla | 2008-09-05 14:25:53 |
|                                  8 | 12 | Blabla | 2008-09-05 14:25:53 |
+------------------------------------+----+--------+---------------------+

mysql> select @rand := RAND(), test.* from test where id = ROUND(@rand*(32-8)+8, 0);
+------------------+----+--------+---------------------+
| @rand := RAND()  | id | data   | ts                  |
+------------------+----+--------+---------------------+
| 0.62503195984758 | 11 | Blabla | 2008-09-05 14:25:53 |
|  0.7731715373445 | 23 | Blabla | 2008-09-05 14:25:55 |
| 0.99076183791343 | 27 | Blabla | 2008-09-05 14:25:55 |
|  0.6342946082673 | 32 | Blabla | 2008-09-05 14:25:55 |
+------------------+----+--------+---------------------+

This is so simple, maybe I am doing something wrong, but I cannot see what...!

When doing the same in perl and generating the random value in perl it works.

Suggested fix:
No idea.
[5 Sep 2008 13:57] Valeriy Kravchuk
I can easily repeat the behaviour described, but looks like the result may be even expected. Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.26-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select ROUND(RAND()*(16-8)+8, 0);
Field   1:  `ROUND(RAND()*(16-8)+8, 0)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+---------------------------+
| ROUND(RAND()*(16-8)+8, 0) |
+---------------------------+
|                        14 |
+---------------------------+
1 row in set (0.00 sec)

So, the result of ROUND(...,0) is still DOUBLE, so id is likely converted to double as well. Then, exact comarison of "double" values can give unexpected results...

Still, I'd say it is a bug:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.26-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from test;
+----+--------+---------------------+
| id | data   | ts                  |
+----+--------+---------------------+
|  1 | Blabla | 2008-09-05 16:38:33 |
|  2 | Blabla | 2008-09-05 16:38:34 |
|  3 | Blabla | 2008-09-05 16:38:35 |
|  4 | Blabla | 2008-09-05 16:38:35 |
|  5 | Blabla | 2008-09-05 16:38:36 |
|  6 | Blabla | 2008-09-05 16:38:36 |
|  7 | Blabla | 2008-09-05 16:38:36 |
|  8 | Blabla | 2008-09-05 16:38:36 |
|  9 | Blabla | 2008-09-05 16:38:37 |
| 10 | Blabla | 2008-09-05 16:38:37 |
| 11 | Blabla | 2008-09-05 16:38:37 |
| 12 | Blabla | 2008-09-05 16:38:37 |
| 13 | Blabla | 2008-09-05 16:38:37 |
| 14 | Blabla | 2008-09-05 16:38:37 |
| 15 | Blabla | 2008-09-05 16:38:37 |
| 16 | Blabla | 2008-09-05 16:38:37 |
| 17 | Blabla | 2008-09-05 16:38:38 |
| 18 | Blabla | 2008-09-05 16:38:38 |
| 19 | Blabla | 2008-09-05 16:38:38 |
| 20 | Blabla | 2008-09-05 16:38:38 |
| 21 | Blabla | 2008-09-05 16:38:38 |
| 22 | Blabla | 2008-09-05 16:38:38 |
| 23 | Blabla | 2008-09-05 16:38:38 |
| 24 | Blabla | 2008-09-05 16:38:38 |
| 25 | Blabla | 2008-09-05 16:38:38 |
| 26 | Blabla | 2008-09-05 16:38:38 |
| 27 | Blabla | 2008-09-05 16:38:38 |
| 28 | Blabla | 2008-09-05 16:38:38 |
| 29 | Blabla | 2008-09-05 16:38:38 |
| 30 | Blabla | 2008-09-05 16:38:38 |
| 31 | Blabla | 2008-09-05 16:38:38 |
| 32 | Blabla | 2008-09-05 16:38:38 |
+----+--------+---------------------+
32 rows in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = cast(ROUND(RAND()*(16-8)+8, 0) as unsigned
);
+----+
| id |
+----+
| 16 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = cast(ROUND(RAND()*(16-8)+8, 0) as unsigned
);
Empty set (0.00 sec)

mysql> SELECT id FROM test WHERE abs(id - ROUND(RAND()*(16-8)+8, 0)) < 0.0001;
+----+
| id |
+----+
| 13 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE abs(id - ROUND(RAND()*(16-8)+8, 0)) < 0.0001;
Empty set (0.00 sec)

mysql> explain SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  |
rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 4       | NULL |
  32 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
| 16 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
| 10 |
| 13 |
| 14 |
+----+
3 rows in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
|  9 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
Empty set (0.00 sec)

ROUND(RAND()*(16-8)+8, 0) should produce integers (OK, double with zero decimals, but I tried to cast to unsigned as well) in a range of [8,16], while there are all ids in [1,32] range.
[8 Sep 2008 5:47] Valeriy Kravchuk
Versions 5.0.x are also affected:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test (
    ->     id   INT UNSIGNED NOT NULL AUTO_INCREMENT
    ->   , data VARCHAR(32)
    ->   , ts   TIMESTAMP
    ->   , PRIMARY KEY (id)
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO test VALUES (NULL, 'Blabla', NULL);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO test SELECT NULL, data, NULL FROM test;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test SELECT NULL, data, NULL FROM test;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test SELECT NULL, data, NULL FROM test;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test SELECT NULL, data, NULL FROM test;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test SELECT NULL, data, NULL FROM test;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
Empty set (0.02 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
| 12 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
+----+
| id |
+----+
| 14 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM test WHERE id = ROUND(RAND()*(16-8)+8, 0);
Empty set (0.00 sec)
[8 Sep 2008 18:27] Omer Barnir
Workaround: set the value of ROUND(RAND()) into a variable and use the variable in the where clause
[28 Oct 2009 14:13] Marcus Heasman
I am having similar problems to this, hope what I have to say helps.

I was busy trying to get a random record out of a table of 2.5 mill rows with non-consecutive ID's.

SELECT artID FROM artArtists WHERE artID = (SELECT FLOOR(RAND()*394899))
(artID being an IDENTITY column)

This should DEFINITELY only produce ONE row, but occasionally, it comes back with *TWO* records.

Perhaps this is the root cause of the incorrect results being returned.
[28 Oct 2009 14:15] Marcus Heasman
Version 5.1.35
[22 Jul 2013 7:34] Simon Mudd
Also an issue in 5.5.23 and 5.6.11.
[20 Oct 2014 11:26] MySQL Verification Team
Even if rand() is evaluated on each row, how do you explain this result when rand is given a seed?

...
mysql> select a from t1 where a=ceil(rand(1)*2);
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

---
drop table if exists t1;
create table t1(a int primary key)engine=innodb;
insert into t1 values (1),(2);
select rand(1),rand(1),rand(1);
select a from t1 where a=ceil(rand(1)*2);
---
[20 Oct 2014 11:59] Sergei Golubchik
Shane, try (in your test case):

select rand(1),rand(1),rand(1) from t1;

a seed only fixes the random sequence to be repeatable, it does not make rand() to return always the same value.
[20 Oct 2014 12:05] Sergei Golubchik
And, anyway,

MariaDB [test]> select a, rand(1), ceil(rand(1)*2) from t1;
+---+---------------------+-----------------+
| a | rand(1)             | ceil(rand(1)*2) |
+---+---------------------+-----------------+
| 1 | 0.40540353712197724 |               1 |
| 2 |  0.8716141803857071 |               2 |
+---+---------------------+-----------------+
2 rows in set (0.01 sec)
[23 Oct 2018 22:10] Matthew Boehm
Also in 5.7.23. Is there no resolution/fix for this 10 year old bug? Is this an optimizer bug? Why is this listed as a Feature Request?

db1-T1 mysql> SELECT id FROM title WHERE id = (FLOOR(1 + RAND() * 1000));
+----+
| id |
+----+
| 20 |
+----+
1 row in set (3.77 sec)

db1-T1 mysql> SELECT id FROM title WHERE id = (FLOOR(1 + RAND() * 1000));
+-----+
| id  |
+-----+
| 144 |
| 761 |
+-----+
2 rows in set (1.58 sec)

db1-T1 mysql> SELECT id FROM title WHERE id = (FLOOR(1 + RAND() * 1000));
+-----+
| id  |
+-----+
| 525 |
+-----+
1 row in set (3.59 sec)

db1-T1 mysql> SELECT id FROM title WHERE id = (FLOOR(1 + RAND() * 1000));
+-----+
| id  |
+-----+
| 159 |
| 523 |
| 865 |
+-----+
3 rows in set (1.84 sec)

show create table title\G
*************************** 1. row ***************************
       Table: title
Create Table: CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(400) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)
[2 Nov 2019 13:17] Roy Lyseng
Posted by developer:
 
RAND() does behave according to documentation, as written above.

It is possible to retrieve exactly one row by the following join:

select id
from test join (select ceil(rand(1)*max(id)) as r from test) as dt
     on test.id = r;

Notice it requires that id's are consecutive, and extreme values of RAND are probably not handled correctly. Using a window function and ROW_NUMBER may overcome this.

Another way is to execute ORDER BY RAND() and using LIMIT 1 to select only one row.

Thus, this feature request will be closed.

There might be another request for a random value that is evaluated exactly once for a statement, but it should be opened as a new feature request.
[10 Mar 2020 11:05] Arkadiusz Góralski
This doesn't seem to be working according to the documentation, because:

In the documentation for rand():
"To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i))."

It states "obtain a random integer", so we can expect that FLOOR(RAND()) returns an integer.

In the documentation for floor():
"The FLOOR() function returns the largest integer value that is smaller than or equal to a number."

So it suggest that floor will also return an integer.

So given the table structure mentioned by OP:
CREATE TABLE test (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT
  , data VARCHAR(32)
  , ts   TIMESTAMP
  , PRIMARY KEY (id)
)

And the query:
select id from test where id=FLOOR(RAND()*(16-8)+8)

Should always return only one row because both the id column and FLOOR(RAND()) should be integers, but sadly it doesn't do that. 

So the documentation should be changed to say that FLOOR(RAND()) isn't actually an integer and when it's used in WHERE clause this will produce random amounts of returned rows.