Bug #82544 Duplicate entry '' for key '<group_key>' while doing group by

Submitted: 11 Aug 2016 14:36 Modified: 17 Aug 2016 14:57
Reporter: Shahriyar Rzayev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.14 OS:Any
Assigned to: CPU Architecture:Any

[11 Aug 2016 14:36] Shahriyar Rzayev
Description:
Running query is:

select  count(*), concat(version(),floor(rand(0)*2))x from information_schema.tables group by x;                    
ERROR 1062 (23000): Duplicate entry '5.7.14-labs-gr0801' for key '<group_key>'

select distinct floor(rand(0)*2) from information_schema.tables;
+------------------+
| floor(rand(0)*2) |
+------------------+
|                0 |
|                1 |
+------------------+
2 rows in set (0.01 sec)

In general the error will likely happen here:

mysql [localhost] {msandbox} (information_schema) > SELECT FLOOR(RAND(0)*2)x FROM information_schema.tables;
+---+
| x |
+---+
| 0 |
| 1 |
| 1 | <---- Duplicate entry here

select count(*), concat(version(),floor(rand(0)*2)) from information_schema.tables group by concat(2,floor(rand(0)*2));
ERROR 1062 (23000): Duplicate entry '21' for key '<group_key>'

Another variation here:

mysql [localhost] {msandbox} (information_schema) > select 'f', concat(version(),floor(rand(0)*2)) from information_schema.tables group by concat(2,floor(rand(0)*2));
+---+------------------------------------+
| f | concat(version(),floor(rand(0)*2)) |
+---+------------------------------------+
| f | 5.7.14-labs-gr0800                 |
| f | 5.7.14-labs-gr0801                 |
+---+------------------------------------+
2 rows in set (0.00 sec)

So it will happen while using aggregation. Question is -> why? :)

How to repeat:
See description
[11 Aug 2016 14:37] Shahriyar Rzayev
Changed category.
[11 Aug 2016 15:38] MySQL Verification Team
Hi!

You are using rand() in both a select list and in the group list. That can create huge runtime problems. How does this work:

select count(*), concat(version(),floor(rand(0)*2)),  concat(2,floor(rand(0)*2))  as xxx from
information_schema.tables group by xxx;
[15 Aug 2016 9:58] Shahriyar Rzayev
Hi,
The result is:

mysql [localhost] {msandbox} ((none)) > select count(*), concat(version(),floor(rand(0)*2)),  concat(2,floor(rand(0)*2))  as xxx from information_schema.tables group by xxx;
ERROR 1062 (23000): Duplicate entry '21' for key '<group_key>'
[16 Aug 2016 14:28] MySQL Verification Team
Hi,

You are quite right. rand() can not be used in GROUP BY reliably. Hence, we need to document it properly.

Thanks.
[17 Aug 2016 14:57] Paul DuBois
http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand says:

Use of a column with RAND() values in an ORDER BY or GROUP BY clause may yield unexpected results because for either clause a RAND() expression can be evaluated multiple times for the same row, each time returning a different result. 

What you are seeing is one manifestation of "unexpected results."

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.