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:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: Rand()

[21 Feb 2005 16:28] Martin Friebe
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:
[16 Apr 2005 2:17] ron le
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.
[23 May 2005 21:36] Dimitri Psaltopoulos
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.
[8 Jun 2005 12:05] Igor Babaev
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.