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/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: SUM(field) sometimes returns -0

Michael,
Michael Duncan wrote:
> 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)

If your column amount is FLOAT this is normal result. This is just hou floating
point arithmetic works.

It may happen that sum is: -0.00001234 which will be rounded to -0.00 not 0.00
Try simple check:

SELECT unit_num, SUM(amount) AS tot, ROUND(SUM(amount), 10) as prec
FROM journal_1
GROUP BY unit_num
HAVING tot<0
ORDER BY unit_num;

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: SUM(field) sometimes returns -0

Michael Duncan writes:
> 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?
>
>

[skip]

> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=sinisa@cytanet.com.cy


Hi!

First of all, this is a mailing list for fully repeatable test cases,
so in future also send or upload a dump of your table.

I have tested your case (with data inserted as per your output) and
it worked fine for me:


mysql> select unit, sum(amount) from xx group by unit;
+------+-------------+
| unit | sum(amount) |
+------+-------------+
| 1 | 3.3 |
| 2 | 2.2 |
| 6 | 0 |
+------+-------------+
3 rows in set (0.00 sec)

mysql> select unit, sum(amount) tot from xx group by unit having tot < 0.0;
Empty set (0.03 sec)


This is expected behaviour because MySQL has to rely on the native
compiler and native floating point functions library for all floating
point calculus.

So, results among OS's and CPU/FPU's may differ.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org