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)