MySQL Bugs: #10021: group by fields must on a index ?

Bug #10021	group by fields must on a index ?
Submitted:	20 Apr 2005 2:46	Modified:	1 Jun 2005 18:31
Reporter:	yue gong	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.4/5.0.5 BK	OS:	Windows (win2000)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
CREATE TABLE `temp` (
  `xueHao` varchar(127) character set gb2312 collate gb2312_bin NOT NULL default '',
  `chengJi` int(11) NOT NULL default '0',
  `quanZhong` int(11) NOT NULL default '0',
  KEY `xueHao` (`xueHao`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

INSERT INTO `temp` VALUES (0x3334353637363233, 8, 5);
INSERT INTO `temp` VALUES (0x3334353637363234, 5, 5);
INSERT INTO `temp` VALUES (0x3334353637363233, 8, 5);
INSERT INTO `temp` VALUES (0x3334353637363234, 5, 5);
INSERT INTO `temp` VALUES (0x3334353637363233, 9, 10);
INSERT INTO `temp` VALUES (0x3334353637363234, 5, 10);
INSERT INTO `temp` VALUES (0x3334353637363233, 8, 6);
INSERT INTO `temp` VALUES (0x3334353637363234, 8, 6);

when I execut :
select xueHao,sum(chengJi)
                  from temp
                  group by xueHao

if without the key clause (KEY `xueHao` (`xueHao`) ) 

it reports : #1062 - Duplicate entry '34567623' for key 1
        

How to repeat:
every time

Notice that this only happens with InnoDB tables:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.5-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql>
mysql> CREATE TABLE `temp` (
    ->   `xueHao` varchar(127) character set gb2312 collate gb2312_bin NOT NULL default
    -> '',
    ->   `chengJi` int(11) NOT NULL default '0',
    ->   `quanZhong` int(11) NOT NULL default '0'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 8, 5);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 5, 5);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 8, 5);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 5, 5);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 9, 10);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 5, 10);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 8, 6);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 8, 6);
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> select xueHao,sum(chengJi)
    -> from temp
    -> group by xueHao;
ERROR 1062 (23000): Duplicate entry '34567623' for key 1
mysql>
mysql> drop table if exists temp;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE `temp` (
    ->   `xueHao` varchar(127) character set gb2312 collate gb2312_bin NOT NULL default
    -> '',
    ->   `chengJi` int(11) NOT NULL default '0',
    ->   `quanZhong` int(11) NOT NULL default '0'
    -> ) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 8, 5);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 5, 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 8, 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 5, 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 9, 10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 5, 10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363233, 8, 6);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `temp` VALUES (0x3334353637363234, 8, 6);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select xueHao,sum(chengJi)
    -> from temp
    -> group by xueHao;
+----------+--------------+
| xueHao   | sum(chengJi) |
+----------+--------------+
| 34567623 | 33           |
| 34567624 | 23           |
+----------+--------------+
2 rows in set (0.00 sec)

mysql>

This could be a character set issue because:

 ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.5-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t0(a varchar(127),b int, c int);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t0 values ('a',8,5),('a',9,10),('b',10,1);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select a, sum(b) from t0 group by a;
+------+--------+
| a    | sum(b) |
+------+--------+
| a    | 17     |
| b    | 10     |
+------+--------+
2 rows in set (0.08 sec)

I've got a similar problem (with MySQL 5.0.4-beta on Windows 2000):

DROP TABLE IF EXISTS `ipaddies`;
CREATE TABLE `ipaddies` (
  `id` int(11) NOT NULL auto_increment,
  `ip` varchar(20) NOT NULL default '',
  `land` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

-- 
-- Daten für Tabelle `ipaddies`
-- 

INSERT INTO `ipaddies` VALUES (1, '30.150.25.211', 'USA');
INSERT INTO `ipaddies` VALUES (2, '137.163.187.129', 'Finnland');
INSERT INTO `ipaddies` VALUES (3, '32.127.88.79', 'Groß Britannien');
INSERT INTO `ipaddies` VALUES (4, '153.94.115.141', 'Deutschland');
INSERT INTO `ipaddies` VALUES (5, '16.135.1.184', 'USA');
INSERT INTO `ipaddies` VALUES (6, '47.35.160.103', 'Kanada');
INSERT INTO `ipaddies` VALUES (7, '153.94.115.141', 'Deutschland');
INSERT INTO `ipaddies` VALUES (8, '156.6.146.142', 'USA');
INSERT INTO `ipaddies` VALUES (9, '144.122.84.30', 'Türkei');
INSERT INTO `ipaddies` VALUES (10, '54.131.228.45', 'USA');

When I execute the query:

select ip, land, count(*) as anz_ip from ipaddies 
group by ip, land 
order by anz_ip desc

I get the error message:

#1062 - Duplicate entry '153.94.115.141-Deutschland' for key 1

So it seems that the cause of the problem is not the InnoDB table.

With server from BK 5.0.7, indeed the issue goes away:

mysql> select ip, land, count(*) as anz_ip from ipaddies
    -> group by ip, land
    -> order by anz_ip desc;
+-----------------+-----------------+--------+
| ip              | land            | anz_ip |
+-----------------+-----------------+--------+
| 153.94.115.141  | Deutschland     |      2 |
| 32.127.88.79    | Groß Britannien |      1 |
| 137.163.187.129 | Finnland        |      1 |
| 30.150.25.211   | USA             |      1 |
| 54.131.228.45   | USA             |      1 |
| 144.122.84.30   | T?rkei          |      1 |
| 156.6.146.142   | USA             |      1 |
| 47.35.160.103   | Kanada          |      1 |
| 16.135.1.184    | USA             |      1 |
+-----------------+-----------------+--------+
9 rows in set (0.05 sec)

mysql> alter table ipaddies engine=innodb;
Query OK, 10 rows affected (0.19 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select ip, land, count(*) as anz_ip from ipaddies
    -> group by ip, land
    -> order by anz_ip desc;
+-----------------+-----------------+--------+
| ip              | land            | anz_ip |
+-----------------+-----------------+--------+
| 153.94.115.141  | Deutschland     |      2 |
| 32.127.88.79    | Groß Britannien |      1 |
| 137.163.187.129 | Finnland        |      1 |
| 30.150.25.211   | USA             |      1 |
| 54.131.228.45   | USA             |      1 |
| 144.122.84.30   | T?rkei          |      1 |
| 156.6.146.142   | USA             |      1 |
| 47.35.160.103   | Kanada          |      1 |
| 16.135.1.184    | USA             |      1 |
+-----------------+-----------------+--------+
9 rows in set (0.00 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.7-beta-nt |
+---------------+
1 row in set (0.01 sec)