MySQL Bugs: #6652: SUM does not return correct values unless the field is aliased

Bug #6652	SUM does not return correct values unless the field is aliased
Submitted:	16 Nov 2004 2:16	Modified:	16 Nov 2004 12:30
Reporter:	Timothy Crider	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.1-alpha	OS:	Linux (Fedora Core 2)
Assigned to:		CPU Architecture:	Any

Description:
When I run a query using SUM on a table it does not return the correct values, unless the field is aliased

Works:
SELECT `gamerTag`, SUM(`kills`) as `kills` FROM `h2_players` GROUP BY `gamertag` ORDER BY `kills` DESC LIMIT 5;

Fails:
SELECT `gamerTag`, SUM(`kills`) FROM `h2_players` GROUP BY `gamertag` ORDER BY `kills` DESC LIMIT 5; 

Here is the table structure, and if need be I can post the MYI/MYD/FRM files where you can download them.

I'm running Fedora Core 2, Single Proc, using the latest alpha release of MySQL 5.

How to repeat:
mysql> SELECT `gamerTag`, SUM(`kills`) as `kills` FROM `h2_players` GROUP BY `gamertag` ORDER BY `kills` DESC LIMIT 5;
+--------------+-------+
| gamerTag     | kills |
+--------------+-------+
| Nomb         |   939 |
| DBAY24       |   828 |
| ratpack1     |   490 |
| G1 of DBAY24 |   488 |
| Horus        |   402 |
+--------------+-------+
5 rows in set (0.06 sec)

mysql> SELECT `gamerTag`, SUM(`kills`) FROM `h2_players` GROUP BY `gamertag` ORDER BY `kills` DESC LIMIT 5;           
+----------------+--------------+
| gamerTag       | SUM(`kills`) |
+----------------+--------------+
| Pooners        |          219 |
| DBAY24         |          828 |
| Quiksilver2490 |          174 |
| Romans 10 9    |          122 |
| Go1dust        |           45 |
+----------------+--------------+
5 rows in set (0.06 sec)

This is not a bug, you're just sorting on different things.

The first case you sort on the summed up number which is now called kills.
In the second case you sort on the column kills, so in effect you get back the sum of the 5 rows with the most kills, you're not ordering by the sum there.