ROUND() issue

am 08.01.2003 09:08:35 von jrd

behavior of ROUND() has changed from earlier version of 3.23 and later
versions of 3.23 (and versions of 4.0). this function is not rounding
up as it used to. it no longer rounds up when the thousands place is 5
(but will round up for 6 through 9).

How-To-Repeat:

as ROUND to round numbers of .NN5 to 2 decical places. for example:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9860 to server version: 3.23.41

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select ROUND(9.765, 2);
+-----------------+
| ROUND(9.765, 2) |
+-----------------+
| 9.77 |
+-----------------+
1 row in set (0.00 sec)

mysql> select ROUND(9 * .085, 2);
+--------------------+
| ROUND(9 * .085, 2) |
+--------------------+
| 0.77 |
+--------------------+
1 row in set (0.00 sec)

but

mysql> select ROUND(9.766, 2);
+-----------------+
| ROUND(9.766, 2) |
+-----------------+
| 9.77 |
+-----------------+
1 row in set (0.00 sec)

3.23.54a and 4.0.x all return "0.76". this seems very wrong to me. am i
missing something?

i don't know which version of 3.23 where this change happened but
couldn't find any mention of it in the release notes. both examples are
from a Debian 3.0 system (although i see the same issue on Mac OS X).

john

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13419@lists.mysql.com
To unsubscribe, e-mail

Re: ROUND() issue

am 08.01.2003 09:40:33 von ck

Am Mittwoch, 8. Januar 2003 09:08 schrieb john r.durand:
> behavior of ROUND() has changed from earlier version of 3.23 and later
> versions of 3.23 (and versions of 4.0). this function is not rounding
> up as it used to. it no longer rounds up when the thousands place is 5
> (but will round up for 6 through 9).
>
> How-To-Repeat:
>(...)
>
> mysql> select ROUND(9.765, 2);
> mysql> select ROUND(9 * .085, 2);
> mysql> select ROUND(9.766, 2);

Interestingly, here, with a MySQL 4.0.5a installation, the first and seco=
nd=20
statement return *.76, the last one returns *.77.

On an old MySQL 3.23.40 installation, all three result to *.77.
--=20
Christian Kohlschütter
ck@newsclub.de

http://www.newsclub.de - Der Meta-Nachrichten-Dienst

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13422@lists.mysql.com
To unsubscribe, e-mail

Re: ROUND() issue (repost)

am 08.01.2003 09:50:12 von ck

Re: ROUND() issue

am 08.01.2003 15:32:41 von jrd

On Wednesday, January 8, 2003, at 12:40 AM, Christian Kohlschütter=20
wrote:

> Am Mittwoch, 8. Januar 2003 09:08 schrieb john r.durand:
>> behavior of ROUND() has changed from earlier version of 3.23 and =
later
>> versions of 3.23 (and versions of 4.0). this function is not rounding
>> up as it used to. it no longer rounds up when the thousands place is=20=

>> 5
>> (but will round up for 6 through 9).
>>
>> How-To-Repeat:
>> (...)
>>
>> mysql> select ROUND(9.765, 2);
>> mysql> select ROUND(9 * .085, 2);
>> mysql> select ROUND(9.766, 2);
>
> Interestingly, here, with a MySQL 4.0.5a installation, the first and=20=

> second
> statement return *.76, the last one returns *.77.
>
> On an old MySQL 3.23.40 installation, all three result to *.77.

exactly. this is used to calculate 8.5% sales tax and it doesn't wok=20
properly in 3.23.54 or 4.0.x. i've had to keep running 3.23.41 for now=20=

but i'd really like to move to 4.0 as soon as it's released. i have to=20=

get this fixed before i can do that, however.

john

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13425@lists.mysql.com
To unsubscribe, e-mail

Re: ROUND() issue

am 09.01.2003 23:02:23 von Peter Zaitsev

On Wednesday 08 January 2003 11:08, john r. durand wrote:

> behavior of ROUND() has changed from earlier version of 3.23
> and later versions of 3.23 (and versions of 4.0). this
> function is not rounding up as it used to. it no longer
> rounds up when the thousands place is 5 (but will round up for
> 6 through 9).

Dear John,

Actually previous behavior was wrong and now it was fixed.

According to SQL standard round shall round 0.5 to the upper and
lower number in round robin fasion. This is done to improve
accuracity for mathemathical computaion - if you round only to
one way the mistake would be shifted which no scientist wants :)

Here is an example:

mysql> select ROUND(9.765, 2);
+-----------------+
| ROUND(9.765, 2) |
+-----------------+
| 9.76 |
+-----------------+
1 row in set (0.00 sec)

mysql> select ROUND(9.775, 2);
+-----------------+
| ROUND(9.775, 2) |
+-----------------+
| 9.78 |
+-----------------+
1 row in set (0.00 sec)

mysql> select ROUND(9.785, 2);
+-----------------+
| ROUND(9.785, 2) |
+-----------------+
| 9.78 |
+-----------------+
1 row in set (0.00 sec)

mysql> select ROUND(9.795, 2);
+-----------------+
| ROUND(9.795, 2) |
+-----------------+
| 9.80 |
+-----------------+
1 row in set (0.00 sec)

As you see even are rounded down while odd are rounded up.

If you would like to have rounding number ended by 5 always down
you shall use TRUNCATE instead:

mysql> select TRUNCATE(9.765+0.005, 2);
+--------------------------+
| TRUNCATE(9.765+0.005, 2) |
+--------------------------+
| 9.77 |
+--------------------------+
1 row in set (0.00 sec)

>
>
> i don't know which version of 3.23 where this change happened
> but couldn't find any mention of it in the release notes. both
> examples are from a Debian 3.0 system (although i see the same
> issue on Mac OS X).

Yes. You're right this change is unfortunately not noted in the
change log.

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13440@lists.mysql.com
To unsubscribe, e-mail

Re: ROUND() issue

am 10.01.2003 06:08:23 von jrd

> Actually previous behavior was wrong and now it was fixed.
>
> According to SQL standard round shall round 0.5 to the upper and
> lower number in round robin fasion. This is done to improve
> accuracity for mathemathical computaion - if you round only to
> one way the mistake would be shifted which no scientist wants :)

really? that seems very bizarre. why would one want to round up or down
in round robin fashion? that means that ROUND() can't be used to
calculate taxes (which is my issue) without adding a small value as you
suggest (and which i once did). this behavior seems quite strange.

also, you suggest using TRUNCATE(9.765+0.005, 2) to get the rounding
behavior i want. i that because TRUNCATE() is better defined that
ROUND()? (the manual states "Note that the behaviour of ROUND() when
the argument is half way between two integers depends on the C library
implementation. Some round to the nearest even number, always up,
always down, or always toward zero. If you need one kind of rounding,
you should use a well-defined function like TRUNCATE() or FLOOR()
instead.")

thanks!

john

On Thursday, January 9, 2003, at 02:02 PM, Peter Zaitsev wrote:

> On Wednesday 08 January 2003 11:08, john r. durand wrote:
>
>> behavior of ROUND() has changed from earlier version of 3.23
>> and later versions of 3.23 (and versions of 4.0). this
>> function is not rounding up as it used to. it no longer
>> rounds up when the thousands place is 5 (but will round up for
>> 6 through 9).
>
> Dear John,
>
> Actually previous behavior was wrong and now it was fixed.
>
> According to SQL standard round shall round 0.5 to the upper and
> lower number in round robin fasion. This is done to improve
> accuracity for mathemathical computaion - if you round only to
> one way the mistake would be shifted which no scientist wants :)
>
> Here is an example:
>
> mysql> select ROUND(9.765, 2);
> +-----------------+
> | ROUND(9.765, 2) |
> +-----------------+
> | 9.76 |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql> select ROUND(9.775, 2);
> +-----------------+
> | ROUND(9.775, 2) |
> +-----------------+
> | 9.78 |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql> select ROUND(9.785, 2);
> +-----------------+
> | ROUND(9.785, 2) |
> +-----------------+
> | 9.78 |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql> select ROUND(9.795, 2);
> +-----------------+
> | ROUND(9.795, 2) |
> +-----------------+
> | 9.80 |
> +-----------------+
> 1 row in set (0.00 sec)
>
>
> As you see even are rounded down while odd are rounded up.
>
> If you would like to have rounding number ended by 5 always down
> you shall use TRUNCATE instead:
>
> mysql> select TRUNCATE(9.765+0.005, 2);
> +--------------------------+
> | TRUNCATE(9.765+0.005, 2) |
> +--------------------------+
> | 9.77 |
> +--------------------------+
> 1 row in set (0.00 sec)
>
>
>
>>
>>
>> i don't know which version of 3.23 where this change happened
>> but couldn't find any mention of it in the release notes. both
>> examples are from a Debian 3.0 system (although i see the same
>> issue on Mac OS X).
>
> Yes. You're right this change is unfortunately not noted in the
> change log.
>
>
> --
> MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Peter Zaitsev
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Moscow, Russia
> <___/ www.mysql.com
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13443@lists.mysql.com
To unsubscribe, e-mail

Re: ROUND() issue

am 10.01.2003 13:56:11 von Peter Zaitsev

On Friday 10 January 2003 08:08, john r. durand wrote:
> > Actually previous behavior was wrong and now it was fixed.
> >
> > According to SQL standard round shall round 0.5 to the upper
> > and lower number in round robin fasion. This is done to
> > improve accuracity for mathemathical computaion - if you
> > round only to one way the mistake would be shifted which no
> > scientist wants :)
>
> really? that seems very bizarre. why would one want to round
> up or down in round robin fashion? that means that ROUND()
> can't be used to calculate taxes (which is my issue) without
> adding a small value as you suggest (and which i once did).
> this behavior seems quite strange.

Dear John,

I understnand you completely. Round is not convinient for such
type of applications (basically for any application which needs
0.5 to be rounded upwards) but imagine other task. You have
1000 numbers which you would like to round and sum as integer
type, imagine they have only one decimal place, such as

1.4
2.5
18.6

etc.

In case 5 would be always rounded upwards and the decimal place
would be evenly distributed. you would Mathemathical expectation
would be shifted from one for original data at 100 (1/10*1000),
while with round which rounds numbers ending with 5 to up or
down mathemathical expectation would be the same as for
original not rounded numbers.

This is important for scientific and computing applications this
is why it is done this way.

>
> also, you suggest using TRUNCATE(9.765+0.005, 2) to get the
> rounding behavior i want. i that because TRUNCATE() is better
> defined that ROUND()? (the manual states "Note that the
> behaviour of ROUND() when the argument is half way between two
> integers depends on the C library implementation. Some round
> to the nearest even number, always up, always down, or always
> toward zero. If you need one kind of rounding, you should use
> a well-defined function like TRUNCATE() or FLOOR() instead.")
>

Yes. This is proper workaround.

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13447@lists.mysql.com
To unsubscribe, e-mail

TRUNCATE() question

am 28.01.2003 14:34:15 von jrd

i'm having an issue with TRUNCATE() (used rather than ROUND() which
doesn't do what i need).

How-To-Repeat:

this behaves the same with 3.23 and 4.0.x.

mysql> select ((13.0) * 0.085);
+------------------+
| ((13.0) * 0.085) |
+------------------+
| 1.105 |
+------------------+
1 row in set (0.00 sec)

makes sense.

mysql> select ((13.0) * 0.085) + 0.005 as result;
+--------+
| result |
+--------+
| 1.110 |
+--------+
1 row in set (0.00 sec)

perfect.

mysql> select TRUNCATE(((13.0) * 0.085) + 0.005, 2) as result;
+--------+
| result |
+--------+
| 1.10 |
+--------+
1 row in set (0.00 sec)

what? this is wrong. i know that rounding issues cause subtle problems
but this seems like a bug. i'm using these queries to calculate
commerce transaction values (8.5% tax) and i'm getting results i can't
use. anyone have suggestions as to how i can get the correct result
reliably?

john

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13614@lists.mysql.com
To unsubscribe, e-mail

Re: TRUNCATE() question

am 28.01.2003 15:06:38 von Peter Zaitsev

On Tuesday 28 January 2003 16:34, john r. durand wrote:
> i'm having an issue with TRUNCATE() (used rather than ROUND()
> which doesn't do what i need).

I'm really sorry to upset you once again, but it is not the bug.
It is floating point in it worsest :)

It is not guarantied 0.05 would be presented as 0.05 insed of
floating point number. It may something like 0.04999998756

in this case your expression results in number a bit less than
1.111 and so gets truncated to 1.110 which is completely
correct:

You can check it simply:

mysql> select ((13.0 * 0.085) + 0.005)<1.11 ;
+-------------------------------+
| ((13.0 * 0.085) + 0.005)<1.11 |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)

The solution for your problem is just to add a bit more weight
before truncating:

mysql> select truncate(((13.0 * 0.085) + 0.005)+0.000001,2);
+-----------------------------------------------+
| truncate(((13.0 * 0.085) + 0.005)+0.000001,2) |
+-----------------------------------------------+
| 1.11 |
+-----------------------------------------------+
1 row in set (0.00 sec)

>
> How-To-Repeat:
>
> this behaves the same with 3.23 and 4.0.x.
>
> mysql> select ((13.0) * 0.085);
> +------------------+
>
> | ((13.0) * 0.085) |
>
> +------------------+
>
> | 1.105 |
>
> +------------------+
> 1 row in set (0.00 sec)
>
> makes sense.
>
> mysql> select ((13.0) * 0.085) + 0.005 as result;
> +--------+
>
> | result |
>
> +--------+
>
> | 1.110 |
>
> +--------+
> 1 row in set (0.00 sec)
>
> perfect.
>
> mysql> select TRUNCATE(((13.0) * 0.085) + 0.005, 2) as result;
> +--------+
>
> | result |
>
> +--------+
>
> | 1.10 |
>
> +--------+
> 1 row in set (0.00 sec)
>
> what? this is wrong. i know that rounding issues cause subtle
> problems but this seems like a bug. i'm using these queries to
> calculate commerce transaction values (8.5% tax) and i'm
> getting results i can't use. anyone have suggestions as to how
> i can get the correct result reliably?
>
>
> john

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13615@lists.mysql.com
To unsubscribe, e-mail

RE: TRUNCATE() question

am 28.01.2003 16:22:19 von Mary Stickney

it got truncated to 1.10 not 1.110

Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
Mary.Stickney@TAGTMI.COM

-----Original Message-----
From: Peter Zaitsev [mailto:peter@mysql.com]
Sent: Tuesday, January 28, 2003 8:07 AM
To: john r. durand; bugs@lists.mysql.com
Subject: Re: TRUNCATE() question

On Tuesday 28 January 2003 16:34, john r. durand wrote:
> i'm having an issue with TRUNCATE() (used rather than ROUND()
> which doesn't do what i need).

I'm really sorry to upset you once again, but it is not the bug.
It is floating point in it worsest :)

It is not guarantied 0.05 would be presented as 0.05 insed of
floating point number. It may something like 0.04999998756

in this case your expression results in number a bit less than
1.111 and so gets truncated to 1.110 which is completely
correct:

You can check it simply:

mysql> select ((13.0 * 0.085) + 0.005)<1.11 ;
+-------------------------------+
| ((13.0 * 0.085) + 0.005)<1.11 |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)

The solution for your problem is just to add a bit more weight
before truncating:

mysql> select truncate(((13.0 * 0.085) + 0.005)+0.000001,2);
+-----------------------------------------------+
| truncate(((13.0 * 0.085) + 0.005)+0.000001,2) |
+-----------------------------------------------+
| 1.11 |
+-----------------------------------------------+
1 row in set (0.00 sec)

>
> How-To-Repeat:
>
> this behaves the same with 3.23 and 4.0.x.
>
> mysql> select ((13.0) * 0.085);
> +------------------+
>
> | ((13.0) * 0.085) |
>
> +------------------+
>
> | 1.105 |
>
> +------------------+
> 1 row in set (0.00 sec)
>
> makes sense.
>
> mysql> select ((13.0) * 0.085) + 0.005 as result;
> +--------+
>
> | result |
>
> +--------+
>
> | 1.110 |
>
> +--------+
> 1 row in set (0.00 sec)
>
> perfect.
>
> mysql> select TRUNCATE(((13.0) * 0.085) + 0.005, 2) as result;
> +--------+
>
> | result |
>
> +--------+
>
> | 1.10 |
>
> +--------+
> 1 row in set (0.00 sec)
>
> what? this is wrong. i know that rounding issues cause subtle
> problems but this seems like a bug. i'm using these queries to
> calculate commerce transaction values (8.5% tax) and i'm
> getting results i can't use. anyone have suggestions as to how
> i can get the correct result reliably?
>
>
> john

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13615@lists.mysql.com
To unsubscribe, e-mail

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13616@lists.mysql.com
To unsubscribe, e-mail

Re: TRUNCATE() question

am 28.01.2003 16:27:58 von Peter Zaitsev

On Tuesday 28 January 2003 18:22, Mary Stickney wrote:
> it got truncated to 1.10 not 1.110

Exactly!

And this is what quite well explained:

mysql> select ((13.0 * 0.085) + 0.005) ;
+--------------------------+
| ((13.0 * 0.085) + 0.005) |
+--------------------------+
| 1.110 |
+--------------------------+
1 row in set (0.00 sec)

This number is internally represented something like

1.1099999999999999999345

now truncate(num,2) called it just takes first two places after
decimal point and ignores the rest:

This results in

1.10

What I was saying is everything which is >=1.10 and <1.11 is
truncated leaving 2 digits to 1.10

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13617@lists.mysql.com
To unsubscribe, e-mail

Re: TRUNCATE() question

am 28.01.2003 16:40:07 von Sergei Golubchik

Hi!

On Jan 28, Peter Zaitsev wrote:
> On Tuesday 28 January 2003 16:34, john r. durand wrote:
> > i'm having an issue with TRUNCATE() (used rather than ROUND()
> > which doesn't do what i need).
>
> I'm really sorry to upset you once again, but it is not the bug.
> It is floating point in its worst :)
>
> It is not guarantied 0.05 would be presented as 0.05 insed of
> floating point number. It may something like 0.04999998756

I would like to add, that it's how perl behaves too:

% perl -e 'print " ", ((13.0) * 0.085), "\n" '
1.105
% perl -e 'print " ", ((13.0) * 0.085)+0.005, "\n" '
1.11
% perl -e 'print " ", int((((13.0) * 0.085)+0.005)*100)/100, "\n"'
1.1
% perl -e 'print " ", int((((13.0) * 0.085)+0.0051)*100)/100, "\n"'
1.11
%

This behaviour is unavoidable deficiency of binary fixed-precision
representation of real decimal numbers. :(

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13618@lists.mysql.com
To unsubscribe, e-mail