Bug #11376 Problem with Group By
Submitted: 16 Jun 2005 11:19 Modified: 16 Aug 2005 19:19
Reporter: Tom Chantry Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7 OS:Microsoft Windows (win2k)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[16 Jun 2005 11:19] Tom Chantry
Description:
Sorry if this is inclluded in any other bug, but I cant find anything ...
There seems to be a problem using group by in conjunction with an index

How to repeat:
Create data table as per http://bugs.mysql.com/bug.php?id=11066

then

mysql> insert into data values('Example1','2005-04-01',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into data values('Example1','2005-05-01',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into data values('Example2','2005-04-01',0);
Query OK, 1 row affected (0.00 sec)

mysql> select name,max(date_) as md from data where name = 'example1' or name =
'example2' group by name order by md;
+----------+------------+
| name     | md         |
+----------+------------+
| Example2 | 2005-04-01 |
| Example1 | 2005-05-01 |
+----------+------------+
2 rows in set (0.00 sec)

(That is correct result)

mysql> alter table data add primary key(Name,Date_);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select name,max(date_) as md from data where name = 'example1' or name =
'example2' group by name order by md;
+----------+------------+
| name     | md         |
+----------+------------+
| Example2 | 2005-04-01 |
| Example2 | 2005-04-01 |
+----------+------------+
2 rows in set (0.02 sec)

Which evidently is the wrong result
(btw you may need to do an optimize table after adding the key)
[16 Jun 2005 11:58] Aleksey Kishkin
tested  on win xp sp2
[16 Aug 2005 19:19] Evgeny Potemkin
Tested on linux x86_64, mysql v5.0.12