Bug #590 Different results with 4.0.13 and 3.23.51 versions
Submitted: 5 Jun 2003 3:02 Modified: 11 Jun 2003 9:31
Reporter: Toni Viemerö Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.13 OS:Linux (Linux (Debian))
Assigned to: Alexey Botchkov CPU Architecture:Any

[5 Jun 2003 3:02] Toni Viemerö
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 :)
[11 Jun 2003 9:31] Alexey Botchkov
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Behaviour of ISNULL() function was changed in 4.0, and i think in the right way.
Look here you wrote
IFNULL(ROUND(AVG(hlstats_Players.skill)), '-') AS skill
Result of this IFNULL will be of STRING type, not number. And it is sorted as string values. This is logically correct, or else we'll get problems with '-' in numeric column.