Hi all,

I tried to divide 123.45 by 123.00 but PostgreSQL gives me a wrong
result:

hygea=> select 123.45 - 123.00;
         ?column?
-----------------
0.450000000000003
(1 row)

-Jose'-



Tue, 15 Dec 1998 13:41:26 +0100
Sferacarta Software <sferac@bo.nettuno.it>
123.45 - 123 = 0.45
Hello!

On Tue, 15 Dec 1998, Sferacarta Software wrote:
> I tried to divide 123.45 by 123.00 but PostgreSQL gives me a wrong
> result:
> 
> hygea=> select 123.45 - 123.00;
>          ?column?
> -----------------
> 0.450000000000003
> (1 row)

   I cannot understand anything. You said "to divide" and then "123.45 - 123.00".
Are you trying to divide or to substract?

Oleg.
---- 
    Oleg Broytmann  National Research Surgery Centre  http://sun.med.ru/~phd/
           Programmers don't die, they just GOSUB without RETURN.


Tue, 15 Dec 1998 16:08:48 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] 123.45 - 123 = 0.45
> Hi all,
> 
> I tried to divide 123.45 by 123.00 but PostgreSQL gives me a wrong
> result:
> 
> hygea=> select 123.45 - 123.00;
>          ?column?
> -----------------
> 0.450000000000003
> (1 row)
> 

Wow, I get the same thing here.

Even this doesn't work:

	test=> select float8(123.45) - float8(123.00);
	         ?column?
	-----------------
	0.450000000000003
	(1 row)

Now constants are automatically promoted to float8, so I expected the
same results, but what is going on here?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 15 Dec 1998 09:27:03 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 123.45 - 123 = 0.45
Try this:

#include <stdio.h>

main()
{
double f1 = 123.45;
double f2 = 123.00;
double r;

    r = f1 - f2;
    printf("%0.15f  %0.15f  %0.15f\n", f1, f2, r);
}

Internal representation of 123.45 is not exact.   In the conversion to
binary, an irrational number is created which is truncated to 64 bits.


Bruce Momjian wrote:

> > Hi all,
> >
> > I tried to divide 123.45 by 123.00 but PostgreSQL gives me a wrong
> > result:
> >
> > hygea=> select 123.45 - 123.00;
> >          ?column?
> > -----------------
> > 0.450000000000003
> > (1 row)
> >
>
> Wow, I get the same thing here.
>
> Even this doesn't work:
>
>         test=> select float8(123.45) - float8(123.00);
>                  ?column?
>         -----------------
>         0.450000000000003
>         (1 row)
>
> Now constants are automatically promoted to float8, so I expected the
> same results, but what is going on here?
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026


Tue, 15 Dec 1998 10:18:21 -0500
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] 123.45 - 123 = 0.45
On Tue, 15 Dec 1998, Sferacarta Software wrote:

> Hi all,
> 
> I tried to divide 123.45 by 123.00 but PostgreSQL gives me a wrong
> result:
> 
> hygea=> select 123.45 - 123.00;
>          ?column?
> -----------------
> 0.450000000000003
> (1 row)

Are you trying to subtract or divide?   Your select is subtraction,
your question was division.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================





Tue, 15 Dec 1998 10:34:42 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] 123.45 - 123 = 0.45
Hello Oleg,

martedï¿½, 15 dicembre 98, you wrote:

OB> Hello!

OB> On Tue, 15 Dec 1998, Sferacarta Software wrote:
>> I tried to divide 123.45 by 123.00 but PostgreSQL gives me a wrong
>> result:
>> 
>> hygea=> select 123.45 - 123.00;
>>          ?column?
>> -----------------
>> 0.450000000000003
>> (1 row)

OB>    I cannot understand anything. You said "to divide" and then "123.45 - 123.00".
OB> Are you trying to divide or to substract?

Sorry I want to say subtract.

-Jose'-



Tue, 15 Dec 1998 16:35:04 +0100
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [HACKERS] 123.45 - 123 = 0.45
> Try this:
> 
> #include <stdio.h>
> 
> main()
> {
> double f1 = 123.45;
> double f2 = 123.00;
> double r;
> 
>     r = f1 - f2;
>     printf("%0.15f  %0.15f  %0.15f\n", f1, f2, r);
> }
> 
> Internal representation of 123.45 is not exact.   In the conversion to
> binary, an irrational number is created which is truncated to 64 bits.

Yes, someone pointed this out to me in private e-mail, and I wrote a C
program to confirm it.  I just had never seen such rounding on such
small non-irrational numbers.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 15 Dec 1998 11:20:15 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 123.45 - 123 = 0.45
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> [ 123.45 - 123.00 = 0.450000000000003 ]

> Now constants are automatically promoted to float8, so I expected the
> same results, but what is going on here?

Plain old, garden-variety, floating point roundoff error.  Do the
same calculation in any other program and you'll get the same result
(on the same hardware anyway), if the other program insists on showing
16 digits of precision.

IEEE 64-bit floats only have about 17 decimal digits of accuracy.
So float8(123.45) is good to about 14 digits after the decimal point.
Subtract off the 123, and print what's left with 16 digits, and
by golly you find out that the original number wasn't exactly 123.45,
just an approximation to it.  Along about the 15th digit after the
decimal point, you start finding crud.

In short: no surprises here for anyone who's used float math for any
length of time.

Sooner or later we ought to try to implement true fixed-point
arbitrary-precision numeric data types per the SQL spec.  That'll be
a lot slower than hardware float math, but its roundoff properties will
be less surprising to novices.

			regards, tom lane

Tue, 15 Dec 1998 11:43:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 123.45 - 123 = 0.45 
Tom Lane wrote:

> Sooner or later we ought to try to implement true fixed-point
> arbitrary-precision numeric data types per the SQL spec.  That'll be
> a lot slower than hardware float math, but its roundoff properties will
> be less surprising to novices.

    I  think  the string math done in bc(1) could be a good point
    to start from. In the old version (1.3 if I  remember  right)
    of  the minix sources (yepp - still have them), there are the
    algorithm's to get  sine,  logarithm  and  the  like  in  any
    precision  as  bc  functions.  Internally, only the four base
    operations, (think think) pow() and sqrt() are implemented in
    C.  Anything else is done with them on the higher level.

    We  need  to define what the precision of a result should be,
    if it is not assigned to a column (where the precision can be
    the  atttypmod).  Is  there any standard defined for? If not,
    what about this:

    Internal representation holds different precision for DISPLAY
    and CALC.

    On  any operation, the DISPLAY precision is set to the higher
    of the two operands.

    On add/subtract, the CALC precision becomes the higher of the
    two.

    On multiply, the CALC precision is adjusted to hold the exact
    result up to a (variable settable?) maximum.

    On divide, the CALC precision is set to max and after  it  to
    the number of used digits.

    If  the  result get's assigned to an attribute, it is rounded
    to it's atttypmod and both precisions set to that.

    The types output function rounds it to the DISPLAY precision.

    The  input  function  sets  both  precisions to the number of
    digits present after decimal point.

    Needless to say that there will be special functions to round
    explicitly and set the precisions.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Tue, 15 Dec 1998 21:15:11 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 123.45 - 123 = 0.45
>     We  need  to define what the precision of a result should be,
>     if it is not assigned to a column (where the precision can be
>     the  atttypmod).  Is  there any standard defined for? If not,
>     what about this:
> 
>     Internal representation holds different precision for DISPLAY
>     and CALC.
> 
>     On  any operation, the DISPLAY precision is set to the higher
>     of the two operands.
> 
>     On add/subtract, the CALC precision becomes the higher of the
>     two.
> 
>     On multiply, the CALC precision is adjusted to hold the exact
>     result up to a (variable settable?) maximum.
> 
>     On divide, the CALC precision is set to max and after  it  to
>     the number of used digits.
> 
>     If  the  result get's assigned to an attribute, it is rounded
>     to it's atttypmod and both precisions set to that.
> 
>     The types output function rounds it to the DISPLAY precision.
> 
>     The  input  function  sets  both  precisions to the number of
>     digits present after decimal point.
> 
>     Needless to say that there will be special functions to round
>     explicitly and set the precisions.

I have a routine that does the necessary rounding on 8 byte floating points to a
precision up to 8 decimal places. Not exactly based on higher math but it
does the job in many financial applications on all assignments, comparisons 
and when displayed.

I never use it on arithmetic operations.

The interface is as follows

double RoundDouble(double value, int decimals);

I am happy to submit it for use in the postgres code.

Regards
Theo

Wed, 16 Dec 1998 07:22:41 +0200 (SAST)
Theo Kramer <theo@flame.flame.co.za>
Re: [HACKERS] 123.45 - 123 = 0.45