Bug #32249 Intermittent 'duplicate entry' for key 'group_key' on FLOOR(RAND())
Submitted: 9 Nov 2007 21:12 Modified: 11 Nov 2007 8:35
Reporter: Domas Mituzas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: Rand()

[9 Nov 2007 21:12] Domas Mituzas 
Description:
This is similar to Bug#31349, but lives outside inet_aton(). 

It is quite odd - sometimes it returns proper resultset, sometimes it does not. 

Verified against: 
5.0: ChangeSet@1.2551, 2007-10-30 20:54:31+01:00, kent@mysql.com +1 -0
5.1: ChangeSet@1.2598, 2007-10-31 10:52:18+01:00, kent@kent-amd64.(none) +1 -0

How to repeat:
create table grouptest1(a int);
insert into grouptest1 values (0),(0),(0),(0),(0),(0),(0),(0),(0);
select count(*),floor(rand()*2) x from grouptest1 group by x;

mysql> select count(*),floor(rand()*2) x from grouptest1 group by x;
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'
mysql> select count(*),floor(rand()*2) x from grouptest1 group by x;
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'
mysql> select count(*),floor(rand()*2) x from grouptest1 group by x;
+----------+---+
| count(*) | x |
+----------+---+
|        4 | 0 | 
|        5 | 1 | 
+----------+---+
2 rows in set (0.01 sec)

Suggested fix:
-
[11 Nov 2007 8:35] Ramil Kalimullin 
From the manual (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand ):
"You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times".