Description:
I'm having some problems with MySQL precompiled 4.0.13 Linux binary.
Here's the query I'm making and it sorts by skill DESC, which should show
highest skilled first.
This query works fine with MySQL 3.23.51.
Query on Linux 4.0.13-max (precompiled from MySQL.com):
mysql> SELECT hlstats_Clans.clanId, hlstats_Clans.name, SUM(hlstats_Players.kills) AS kills, SUM(hlstats_Players.deaths) AS deaths, IFNULL(ROUND(AVG(hlstats_Players.skill)), '-') AS skill, COUNT(hlstats_Players.playerId) AS nummembers, IFNULL(SUM(hlstats_Players.kills)/SUM(hlstats_Players.deaths), '-') AS kpd FROM hlstats_Clans LEFT JOIN hlstats_Players ON hlstats_Players.clan=hlstats_Clans.clanId WHERE hlstats_Clans.game='cstrike' GROUP BY hlstats_Clans.clanId HAVING nummembers >= 2 ORDER BY skill DESC LIMIT 0,5;
+--------+---------------+-------+--------+-------+------------+------+
| clanId | name | kills | deaths | skill | nummembers | kpd |
+--------+---------------+-------+--------+-------+------------+------+
| 5 | rc | 27 | 48 | 998 | 4 | 0.56 |
| 6 | U | 335 | 552 | 998 | 2 | 0.61 |
| 9 | CScene | 25 | 55 | 975 | 2 | 0.45 |
| 4 | Clan Troopers | 3719 | 2120 | 1465 | 16 | 1.75 |
| 1 | SixPak | 2192 | 2531 | 1278 | 7 | 0.87 |
+--------+---------------+-------+--------+-------+------------+------+
Query on Linux 3.23.51-1woody4 (Debian Woody package):
mysql> SELECT hlstats_Clans.clanId, hlstats_Clans.name, SUM(hlstats_Players.kills) AS kills, SUM(hlstats_Players.deaths) AS deaths, IFNULL(ROUND(AVG(hlstats_Players.skill)), '-') AS skill, COUNT(hlstats_Players.playerId) AS nummembers, IFNULL(SUM(hlstats_Players.kills)/SUM(hlstats_Players.deaths), '-') AS kpd FROM hlstats_Clans LEFT JOIN hlstats_Players ON hlstats_Players.clan=hlstats_Clans.clanId WHERE hlstats_Clans.game='cstrike' GROUP BY hlstats_Clans.clanId HAVING nummembers >= 2 ORDER BY skill DESC LIMIT 0,5;
+--------+---------------+-------+--------+-------+------------+------+
| clanId | name | kills | deaths | skill | nummembers | kpd |
+--------+---------------+-------+--------+-------+------------+------+
| 4 | Clan Troopers | 3719 | 2120 | 1465 | 16 | 1.75 |
| 15 | [BB] | 506 | 476 | 1278 | 3 | 1.06 |
| 1 | SixPak | 2192 | 2531 | 1278 | 7 | 0.87 |
| 32 | DieSeL | 917 | 967 | 1210 | 4 | 0.95 |
| 24 | Matrix | 189 | 182 | 1204 | 2 | 1.04 |
+--------+---------------+-------+--------+-------+------------+------+
How to repeat:
Run the query again :)