Professional Web Applications Themes

Math BUG?? - MySQL

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...

  1. #1

    Default Math 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

    kkmigas@gmail.com Guest

  2. #2

    Default Re: 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

    Lee Guest

  3. #3

    Default Re: 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
    Brian Guest

  4. #4

    Default Re: 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]

    kkmigas@gmail.com Guest

  5. #5

    Default Re: 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]

    kkmigas@gmail.com Guest

  6. #6

    Default Re: 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
    ==================
    Jerry Guest

  7. #7

    Default Re: 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]

    kkmigas@gmail.com Guest

  8. #8

    Default Re: 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
    ==================
    Jerry Guest

  9. #9

    Default Re: 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.
    Dennis Guest

  10. #10

    Default Re: 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]

    kkmigas@gmail.com Guest

  11. Moderated Post

    Default Re: Math BUG??

    Removed by Administrator
    toby Guest
    Moderated Post

Similar Threads

  1. math problem will Math.floor help
    By javman webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 5
    Last Post: February 4th, 01:28 AM
  2. Check my math!
    By Craig Bailey in forum PHP Development
    Replies: 19
    Last Post: January 14th, 05:46 PM
  3. Replies: 4
    Last Post: December 19th, 07:09 AM
  4. OT: Math help
    By Joseph D. DeJohn in forum ASP.NET General
    Replies: 2
    Last Post: August 7th, 02:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

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