MySQL Bugs: #8652: group by part of rand() returns duplicate key error

Bug #8652	group by part of rand() returns duplicate key error
Submitted:	21 Feb 2005 16:28	Modified:	8 Jun 2005 12:05
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Rand()

Description:
look at the queries below, grouping on certain parts of the result from rand, causes a duplicate key error.

the presence of the aggrecate function is important, all queries seem to work, without the aggregate function

How to repeat:
# this works
create table r1 (a int); insert into r1 values (1),(2),(1),(2),(1),(2),(1),(2),(1),(2),(1),(2),(1),(2);
select left(rand(),3),a from r1 group by 1;

# this doesnt
 select left(rand(),3),a, count(*) from r1 group by 1;
ERROR 1062 (23000): Duplicate entry '0.5' for key 1

select round(rand(1),1)  ,a, count(*) from r1 group by 1;
ERROR 1062 (23000): Duplicate entry '0.1' for key 1

#However thees work
 select round(rand(1),0)  ,a, count(*) from r1 group by 1;
+------------------+------+----------+
| round(rand(1),0) | a    | count(*) |
+------------------+------+----------+
|                0 |    2 |        9 |
|                1 |    1 |        5 |
+------------------+------+----------+

 select left(rand(1),2),a, count(*) from r1 group by 1;
+-----------------+------+----------+
| left(rand(1),2) | a    | count(*) |
+-----------------+------+----------+
| 0.              |    1 |       14 |
+-----------------+------+----------+

Suggested fix:
-

Found group by to have the same error but not consistent after updating the selected table. There are two action that sometime resolves the error: 1. analyze table xxx. 2. exit the current mysql client and  log back in. As mentioned, this is not consistent but it works most of the time but not for the test case given for this bug.

This is a bug. There is an issue with the GROUP BY clause and aggregate functions. 

The following code: 

SELECT ConsumedFoods.NDB_No, Count(ConsumedFoods.NDB_No) AS TimesConsumed						
FROM Meals INNER JOIN ConsumedFoods ON Meals.MealID = ConsumedFoods.MealID
WHERE Meals.OwnersAccountID = 1
GROUP BY ConsumedFoods.NDB_No;

Works fine in MS SQL Server and MS Access, but it gives a "Duplicate entry '####' for key 1". Where #### is the ID (NDB_No) of the entry that is supposed to be grouped by the relevant clause. If the aggregate function (Count) is removed, the query runs fine in mysql.

This problem happens because in a GROUP BY query a RAND expression can be evaluated several times for the same row, every time returning a new result.
The manual should have mentioned this behaviour not only for ORDER BY queries.