Bug #31349 ERROR 1062 (23000): Duplicate entry '' for key 'group_key'
Submitted: 2 Oct 2007 18:13 Modified: 20 Dec 2007 0:43
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.23-debug, 5.0.48-debug OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: group_key
Triage: D2 (Serious)

[2 Oct 2007 18:13] Shane Bester
Description:
In 5.x it seems unexpected that the internal temp table should have this duplicate key problem.  4.1.21 doesn't appear to have this problem.  Seems related to the 'not null' property of the column.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1` (`a` int not null) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `t1` values (-1),(-2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select min(`a`) from t1 group by inet_ntoa(`a`);
ERROR 1062 (23000): Duplicate entry '' for key 'group_key'

How to repeat:
drop table if exists t1;
create table `t1` (`a` int not null) engine=myisam;
insert into `t1` values (-1),(-2);
select min(`a`) from t1 group by inet_ntoa(`a`);
[3 Oct 2007 10:54] Shane Bester
I could only repeat on a debug build of mysqld
[12 Oct 2007 16:08] Konstantin Osipov
Wrong result, upgrading.
[19 Oct 2007 9:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35894

ChangeSet@1.2542, 2007-10-19 14:54:05+05:00, ramil@mysql.com +3 -0
  Fix for bug #31349: ERROR 1062 (23000): Duplicate entry '' for key 'group_key'
  
  Problem: lying to the optimizer that a function (Item_func_inet_ntoa)
  cannot return NULL values leads to unexpected results (in the case group
  keys creation/comparison is broken). 
  
  Fix: Item_func_inet_ntoa::maybe_null should be set properly.
[7 Dec 2007 23:08] Bugs System
Pushed into 6.0.5-alpha
[7 Dec 2007 23:09] Bugs System
Pushed into 5.1.23-rc
[7 Dec 2007 23:10] Bugs System
Pushed into 5.0.54
[20 Dec 2007 0:43] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

INET_NTOA() did not properly report that it can return NULL, causing
unique-key constraint violations for GROUP BY operations.