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."
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.
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