| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.7.14 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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."
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