Can some one plz explain this???

SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2);

Linux (Servidor Mysql 4)

20.04 | 20.04

Windows (Servidor Mysql 4)

20.05 | 20.05

Thanks

Printable View

- December 21st, 03:03 PMkkmigas@gmail.comMath BUG??
Can some one plz explain this???

SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2);

Linux (Servidor Mysql 4)

20.04 | 20.04

Windows (Servidor Mysql 4)

20.05 | 20.05

Thanks

- December 22nd, 12:41 PMLeeRe: Math BUG??
On 21 Dec 2006 07:03:22 -0800, "com" <com>

wrote:

Here's my results of:

SELECT VERSION();

SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2)

Linux

5.0.24a

20.45000 | 20.05

Windows

5.0.24-community-nt

20.45000 | 20.05

- December 22nd, 06:03 PMBrianRe: Math BUG??
Lee Peedin wrote:

>

>

> Here's my results of:

> SELECT VERSION();

> SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2)

>

> Linux

> 5.0.24a

> 20.45000 | 20.05

>

> Windows

> 5.0.24-community-nt

> 20.45000 | 20.05[/ref]

Linux 4.1.16

20.04 | 20.04

Linux 5.0.24-standard-log

20.045000 | 20.05

--

Brian Wakem

Email: http://homepage.ntlworld.com/b.wakem/myemail.png

- December 26th, 11:32 AMkkmigas@gmail.comRe: Math BUG??
Using the command with version heres the outputs

SELECT VERSION( ) , (( 200.45 *1 ) * ( 10 /100 )), ROUND( ( 200.45 *1 *

( 10 /100 ) ) , 2 ) , ROUND( 20.045, 2 ) ;

Windows 2003

5.0.22-community-nt 20.045000 20.05 20.05

Windows 2003

4.1.18-nt-max 20.05 20.05 20.05

Centos

4.1.21-standard 20.04 20.04 20.05

FreeBSD

3.23.58 20.05 20.05 20.05

After all my testing i've found that the only system that does the

Round and the math good is windows.

On Dec 22, 6:03 pm, Brian Wakem <com> wrote:

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

>

>

>

> 20.04 | 20.04

>

> Linux 5.0.24-standard-log

> 20.045000 | 20.05

>

> --

> Brian Wakem

> Email:http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]

- December 26th, 12:00 PMkkmigas@gmail.comRe: Math BUG??
It must be something with the libs im using on the background can any

one tell me witch ones do i need to change or point me to some website

that explains / solves this problem.

On Dec 26, 11:32 am, "com" <com> wrote: [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

>

> > 20.04 | 20.04[/ref]

>

> [/ref]

- December 26th, 02:22 PMJerryRe: Math BUG??
com wrote:

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>> [/ref]

>

>[/ref]

It's not a a "problem" - it's how floating point numbers are stored, and

the results for your operation will always be somewhat unpredictable.

That's true not only in SQL - but in any language which uses floating

point numbers.

The reason this happens is that 20.045 is not exactly 20.045. If you

print it out to enough decimal places, it will come out to something

like 20.04499999967. Rounding this value would (correctly) give a

result of 20.04.

The same thing happens in decimal if you try adding 1/3 3 times. You

get .9999999... instead of 1 because 1/3 cannot be expressed directly in

decimal form.

The way to resolve it is to add a small value (smaller than the rounding

error) to the number. In your case a value of .001 should suffice

(since you're rounding to 2 decimal places).

--

==================

Remove the "x" from my email address

Jerry Stuckle

JDS Computer Training Corp.

net

==================

- December 26th, 03:09 PMkkmigas@gmail.comRe: Math BUG??
Any calculator is able to return the same value has the above.

The so special calc that i am making is quite simple.

200.45 is the price

1 = quantity

10 is the discount

((200.45 * 1) * (10/100))

Price * Qt. * (Discount/100)

I am trying to get how much is the Discount in Price * Qt.

200.45 * 10% gives very simple result ( 200.45 * 0.10 ) = 20.045

the only Mysql Servers that returns the right value are

5.0.22-community-nt 20.045000 20.05 20.05

5.0.22-standard 20.045000 20.05 20.05

After all my tests it seems that i am going to mysql 5 sooner than

expected since it does all the math correctly.

Thanks for all the help. Hope this can also helps someone else.

Best Regards

Luis Miguel Freitas

On Dec 26, 2:22 pm, Jerry Stuckle <net> wrote:

>

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> the results for your operation will always be somewhat unpredictable.

> That's true not only in SQL - but in any language which uses floating

> point numbers.

>

> The reason this happens is that 20.045 is not exactly 20.045. If you

> print it out to enough decimal places, it will come out to something

> like 20.04499999967. Rounding this value would (correctly) give a

> result of 20.04.

>

> The same thing happens in decimal if you try adding 1/3 3 times. You

> get .9999999... instead of 1 because 1/3 cannot be expressed directly in

> decimal form.

>

> The way to resolve it is to add a small value (smaller than the rounding

> error) to the number. In your case a value of .001 should suffice

> (since you're rounding to 2 decimal places).

>

> --

> ==================

> Remove the "x" from my email address

> Jerry Stuckle

> JDS Computer Training Corp.

> net

> ==================[/ref]

- December 26th, 03:58 PMJerryRe: Math BUG??
com wrote:

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>

>>the results for your operation will always be somewhat unpredictable.

>>That's true not only in SQL - but in any language which uses floating

>>point numbers.

>>

>>The reason this happens is that 20.045 is not exactly 20.045. If you

>>print it out to enough decimal places, it will come out to something

>>like 20.04499999967. Rounding this value would (correctly) give a

>>result of 20.04.

>>

>>The same thing happens in decimal if you try adding 1/3 3 times. You

>>get .9999999... instead of 1 because 1/3 cannot be expressed directly in

>>decimal form.

>>

>>The way to resolve it is to add a small value (smaller than the rounding

>>error) to the number. In your case a value of .001 should suffice

>>(since you're rounding to 2 decimal places).

>>

>>--

>>==================

>>Remove the "x" from my email address

>>Jerry Stuckle

>>JDS Computer Training Corp.

>>net

>>==================[/ref]

>

>

> Any calculator is able to return the same value has the above.

>

> The so special calc that i am making is quite simple.

>

> 200.45 is the price

> 1 = quantity

> 10 is the discount

>

> ((200.45 * 1) * (10/100))

> Price * Qt. * (Discount/100)

>

> I am trying to get how much is the Discount in Price * Qt.

>

> 200.45 * 10% gives very simple result ( 200.45 * 0.10 ) = 20.045

>

> the only Mysql Servers that returns the right value are

> 5.0.22-community-nt 20.045000 20.05 20.05

> 5.0.22-standard 20.045000 20.05 20.05

>

> After all my tests it seems that i am going to mysql 5 sooner than

> expected since it does all the math correctly.

>

> Thanks for all the help. Hope this can also helps someone else.

> Best Regards

> Luis Miguel Freitas

>[/ref]

(Top posting fixed)

"Any calculator" is not the same as a computer. This problem has

existed since the dawn of computing - I first saw it when I started with

Fortran in 1967. That's why there are DECIMAL data types - the data is

internally stored in a different format, which does have exact decimal

values.

But floating point values all suffer this problem - in all computers,

big and small.

And yes, you might have seen a version of MySQL 5.0 which "solves" this

problem - but it is not a "guaranteed" fix, because it's not a

"problem". ANY time you round as closely as you are doing, the results

will be indeterminate. The same is true with truncating;

0.2 * 5 does not necessarily equal 1.

--

==================

Remove the "x" from my email address

Jerry Stuckle

JDS Computer Training Corp.

net

==================

- December 26th, 04:11 PMDennisRe: Math BUG??
On 26 Dec 2006 07:09:29 -0800, "com" <com>

wrote:

If you need 100% accuracy don't use floating point. You should be able

to figure out a way to do what you want with integers, integer division,

mod (modulo), etc.

--

Dennis K.

- December 26th, 04:29 PMkkmigas@gmail.comRe: Math BUG??
So your saying that theres no way to "fix" this "problem" you have to

look at the values and get make the necessary changes.

But shouldn't mysql server 4 "fix" the "problem" like in supposedly in

other systems like Mysql 5 and Oracle.

I've tested some values in oracle and all so excel and i allways get

On Dec 26, 3:58 pm, Jerry Stuckle <net> wrote:

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

> [/ref]

>

> >

> > The so special calc that i am making is quite simple.

> >

> > 200.45 is the price

> > 1 = quantity

> > 10 is the discount

> >

> > ((200.45 * 1) * (10/100))

> > Price * Qt. * (Discount/100)

> >

> > I am trying to get how much is the Discount in Price * Qt.

> >

> > 200.45 * 10% gives very simple result ( 200.45 * 0.10 ) = 20.045

> >

> > the only Mysql Servers that returns the right value are

> > 5.0.22-community-nt 20.045000 20.05 20.05

> > 5.0.22-standard 20.045000 20.05 20.05

> >

> > After all my tests it seems that i am going to mysql 5 sooner than

> > expected since it does all the math correctly.

> >

> > Thanks for all the help. Hope this can also helps someone else.

> > Best Regards

> > Luis Miguel Freitas

> >[/ref]

>

> (Top posting fixed)

>

> "Any calculator" is not the same as a computer. This problem has

> existed since the dawn of computing - I first saw it when I started with

> Fortran in 1967. That's why there are DECIMAL data types - the data is

> internally stored in a different format, which does have exact decimal

> values.

>

> But floating point values all suffer this problem - in all computers,

> big and small.

>

> And yes, you might have seen a version of MySQL 5.0 which "solves" this

> problem - but it is not a "guaranteed" fix, because it's not a

> "problem". ANY time you round as closely as you are doing, the results

> will be indeterminate. The same is true with truncating;

>

> 0.2 * 5 does not necessarily equal 1.

>

> --

> ==================

> Remove the "x" from my email address

> Jerry Stuckle

> JDS Computer Training Corp.

> net

> ==================[/ref]

- December 26th, 06:36 PMtobyRe: Math BUG??
com wrote:

While using floating point, it's not possible, as others have

explained.

The problem does not occur in your example statement in MySQL 5,

because it doesn't use floating point for this calculation: [3],

http://dev.mysql.com/doc/refman/5.0/en/precision-math.html

(You can still this up by using floating point column types,

however.)

What you're seeing is a consequence of the fact that floating point (in

this case IEEE754) cannot represent 200.45 (or 20.045) exactly[2]. In

rounding 20.045 to two places, the inexact quantity is compared >=

20.045(approx), which determines if it will round to 20.04 or 20.05.

Depending on the particular sequence of computations that produced the

value, the value will be in error, either slightly larger or smaller

than 20.045, and so the rounding result can't really be predicted in

this case (where the value is on the rounding criterion). [1]

As Jerry says, adding an insignificant constant would work around this

case (if you know the input is whole cents).

Using a DECIMAL column for 'price' won't help in MySQL 4, because

computations on DECIMALs are still done using IEEE double precision

floating point (see below, and

http://dev.mysql.com/doc/refman/4.1/en/choosing-types.html).

Using INTEGERs and counting whole cents should work, but it's only safe

because X.5 happens to be representable exactly in floating point

(dividing two integers produces a floating point result in MySQL 4). If

the dividend represents whole cents, in your example, discount =

20045/10 = 2004.5, which, in IEEE, should always round to 2005.0).

[1] http://dev.mysql.com/doc/refman/4.1/en/problems-with-float.html

(Be very wary of the user contributed "workarounds" on that page.)

[2] You can confirm this like so:

mysql> select format(200.45,20);

....

| 200.44999999999998863132 |

....

mysql> select format(200.45/10,20);

....

| 20.04499999999999815259 |

....

mysql> select format('200.45'/'10',20);

....

| 20.04500000000000170530 |

....

mysql> CREATE TABLE blah(price DECIMAL(8,2));

Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO blah VALUES('200.45');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT FORMAT(price/10,20) FROM blah;

....

| 20.04499999999999815259 |

....

mysql> \s

....

Server version: 4.0.27

[3] In MySQL 5.0:

mysql> select format(200.45,20);

....

| 200.45000000000000000000 |

....

mysql> select format(200.45/10,20);

....

| 20.04500000000000000000 |

....

mysql> CREATE TABLE blah(price DECIMAL(8,2));

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO blah VALUES('200.45');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT FORMAT(price/10,20) FROM blah;

....

| 20.04500000000000000000 |

....

mysql> \s

....

Server version: 5.0.24a-pro-gpl-log MySQL Pro (GPL)

As long as they're using floating point, this is sheer coincidence.

> >

> >

> >

> >

> >

> > [/ref][/ref]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139