Bug #39274 SELECT on PK with ROUND(RAND()) give wrong errors
Submitted: 5 Sep 2008 13:15 Modified: 5 Sep 2008 13:57
Reporter: Oli Sennhauser Email Updates:
Status: Verified 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
Triage: Triaged: D5 (Feature request)

[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] Shane Bester
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 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`)
)