SUM(field) sometimes returns -0

While developing an app I came across this anomoly.

Just to make sure it wasn't VB/ODBC/Win2K/network/exteneral gremlin/...
I did the query on the console of the machine running mySQL.
There are 18 units in this table the ones not listed in the first query 
do return a valid 0 (+0) balance.

I can allow for this by double checking the returned data in my app but 
it shouldn't be happening should it?


mysql> SELECT unit_num, SUM(amount) AS tot FROM journal_1 GROUP BY 
unit_num HAVING tot<0 ORDER BY unit_num;
+----------+---------+
| unit_num | tot     |
+----------+---------+
|        5 |  -73.79 |
|        6 |   -0.00 |  <- is -0<0 ? I suppose it must be
|        7 | -173.32 |
|        9 |   -0.00 |
|       13 | -173.32 |
|       15 |   -0.00 |
|       17 |   -0.00 |
|       18 |   -0.03 |
+----------+---------+
8 rows in set (0.01 sec)


mysql> SELECT unit_num, amount FROM journal_1 where unit_num=6;
+----------+---------+
| unit_num | amount  |
+----------+---------+
|        6 |  166.80 |
|        6 |   16.53 |
|        6 | -166.80 |
|        6 |  -16.53 |
|        6 |  157.79 |
|        6 |   15.78 |
|        6 |    1.33 |
|        6 |  157.79 |
|        6 |   15.78 |
|        6 | -157.79 |
|        6 |  -15.78 |
|        6 |   -1.33 |
|        6 | -157.79 |
|        6 |  -15.78 |
|        6 |  157.79 |
|        6 |   15.78 |
|        6 | -157.79 |
|        6 |  -15.78 |
+----------+---------+
18 rows in set (0.00 sec)

This unit has similar data but adds up to +0

mysql> SELECT unit_num, amount FROM journal_1 where unit_num=1;
+----------+----------+
| unit_num | amount   |
+----------+----------+
|        1 |  128.890 |
|        1 |   12.770 |
|        1 | -128.890 |
|        1 |  -12.770 |
|        1 |  121.930 |
|        1 |   12.190 |
|        1 | -121.930 |
|        1 |  -12.190 |
|        1 |  121.930 |
|        1 |   12.190 |
|        1 | -121.930 |
|        1 |  -12.190 |
|        1 |  121.930 |
|        1 |   12.190 |
|        1 | -121.930 |
|        1 |  -12.190 |
+----------+----------+
16 rows in set (0.00 sec)











For the record.
mysql> status;
--------------
mysql  Ver 12.21 Distrib 4.0.14, for pc-linux (i686)

Connection id:          113
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.0.14-standard
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 day 6 hours 50 min 1 sec

Threads: 3  Questions: 56231  Slow queries: 22  Opens: 20044  Flush 
tables: 1  Open tables: 64  Queries per second avg: 0.507
--------------


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]