RE: The 85/0.0085 mistery ? - Mailing list pgsql-hackers

Nothing is wrong, that's just how computers work.  Fractional numbers are
stored in a variety of formats on different platforms.  Most support the
IEEE formats, but some use their own formats.  In all cases, that I'm aware
of, the numbers are stored as a mantissa and exponent.  The numbers are
"base 2", so certain base 10 numbers don't have an exact representation in
base 2, no matter how many bits are used in the mantissa.  Other base 10
numbers have problems with the "short" mantissa formats, but are represented
exactly with the "long" mantissa formats.

In general, it's recommended you don't check for exact equivalence when
doing calculations on real numbers and instead check for "almost exactness."
In other words, depending on the format used on your machine, 85/0.0085 may
be equal to 1000.000000000000001.

Of course, this may all be wrong, but that's my understanding.  I think they
mention this in the docs for the regression tests.  Check them out!

Fred Reimer
Eclipsys Corporation



> -----Original Message-----
> From: pgsql-hackers-owner@hub.org
> [mailto:pgsql-hackers-owner@hub.org]On
> Behalf Of Jean-Christophe Pazzaglia
> Sent: Friday, October 06, 2000 8:28 AM
> To: PostgreSQL-development
> Subject: [HACKERS] The 85/0.0085 mistery ?
>
>
> Hi,
>
>
> Well, I discovered a strange behavior (ie 85/0.0085 <> 10000 )
> Is my SQL wrong or  is it postgres ?
> We are running 7.02 (but also 6.x) on a alpha (alphaev6) box.
>
>
> Thanks in advance.
>
> jc
>
> Here is a snapshot
>
> jctest=> create table eigthyfive ( cs float4, csbytenthousand float4,
> csbyhundred float);
> CREATE
>
> jctest=> insert into eigthyfive VALUES (88,0.0088,0.88);
> INSERT 7973003 1
> jctest=> insert into eigthyfive VALUES (86,0.0086,0.86);
> INSERT 7973004 1
> jctest=> insert into eigthyfive VALUES (85,0.0085,0.85);
> INSERT 7973005 1
> jctest=>  insert into eigthyfive VALUES (84,0.0084,0.84);
> INSERT 7973006 1
>
> jctest=> select * from eigthyfive ;
>  cs | csbytenthousand | csbyhundred
> ----+-----------------+-------------
>  88 |          0.0088 |        0.88
>  86 |          0.0086 |        0.86
>  85 |          0.0085 |        0.85
>  84 |          0.0084 |        0.84
> (4 rows)
>
>  jctest=> select * from eigthyfive where (cs/csbytenthousand=10000);
>  cs | csbytenthousand | csbyhundred
> ----+-----------------+-------------
>  88 |          0.0088 |        0.88
>  86 |          0.0086 |        0.86
>  84 |          0.0084 |        0.84
> (3 rows)
>
> *** oh oh 85 disappeared ! ***
>
> BUT
>
> jctest=> select (85/0.0085=10000);
>  ?column?
> ----------
>  t
> (1 row)
>
>
> jctest=> select * from eigthyfive where (cs/csbyhundred=100);
>  cs | csbytenthousand | csbyhundred
> ----+-----------------+-------------
>  88 |          0.0088 |        0.88
>  86 |          0.0086 |        0.86
>  85 |          0.0085 |        0.85
>  84 |          0.0084 |        0.84
> (4 rows)
>
> ** 85 is back **
>
> jctest=> insert into eigthyfive VALUES (85,0.00085);
> INSERT 7973007 1
> jctest=> select * from eigthyfive where (cs/csbytenthousand=100000);
>  cs | csbytenthousand | csbyhundred
> ----+-----------------+-------------
>  85 |         0.00085 |
> (1 row)