Обсуждение: 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.8886 |          0.0086 |        0.8685 |
0.0085|        0.8584 |          0.0084 |        0.84
 
(4 rows)
jctest=> select * from eigthyfive where (cs/csbytenthousand=10000);cs | csbytenthousand | csbyhundred
----+-----------------+-------------88 |          0.0088 |        0.8886 |          0.0086 |        0.8684 |
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.8886 |          0.0086 |        0.8685 |
0.0085|        0.8584 |          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)

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)

"Frederick W. Reimer" wrote:
>
> 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.

thank you for the lesson,
I probably have done too much database this time :o

what I was really affraid was the BUT clause ...

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

... It seems that this is due to the single precision
(float4) instead of double precision (float)
(and even with a test done in base 3 :P)
BTW there is no round(float4) function

thanks

jc

"Frederick W. Reimer" <fwr@ga.prestige.net> writes:
> In general, it's recommended you don't check for exact equivalence when
> doing calculations on real numbers and instead check for "almost exactness."

Or use datatype NUMERIC, which behaves in a more intuitive way (at least
for people who are accustomed to thinking in decimal).  But yes, exact
equality checks on float values are usually foolish.
        regards, tom lane

Tom Lane wrote:
> 
> "Frederick W. Reimer" <fwr@ga.prestige.net> writes:
> > In general, it's recommended you don't check for exact equivalence when
> > doing calculations on real numbers and instead check for "almost exactness."
> 


perhaps "where trunc(85/0.0085) = 10000" works better.


> Or use datatype NUMERIC, which behaves in a more intuitive way (at least
> for people who are accustomed to thinking in decimal).  But yes, exact
> equality checks on float values are usually foolish.

But ...

hannu=# create table ntest(n numeric(3), n1 numeric(6,4));
CREATE
hannu=# insert into ntest values(80, 80/10000);
INSERT 311338 1
hannu=# insert into ntest values(81, 81/10000);
INSERT 311339 1
hannu=# select count(*) from ntest where n/n1 = 10000;
ERROR:  division by zero on numeric
hannu=# select * from ntest;n  |   n1   
----+--------80 | 0.000081 | 0.0000

eek !

I understand why this is so, but should it be ?

Can't we assume that 80/10000 is numeric and back-propagate that 
knowledge to constituents so that 80 and 10000 are also considered 
numetics. 

Or even better assume that 80/10000 is a rational number ;)

-------------------
Hannu

Hannu Krosing wrote:
> 
> Tom Lane wrote:
> >
> > "Frederick W. Reimer" <fwr@ga.prestige.net> writes:
> > > In general, it's recommended you don't check for exact equivalence when
> > > doing calculations on real numbers and instead check for "almost exactness."
> >
> 
> perhaps "where trunc(85/0.0085) = 10000" works better.

ok I tested it and it does not work ;(

hannu=# select trunc(85.0::float4/0.0085::float4) = '10000';
ERROR:  Function 'trunc(float4)' does not exist       Unable to identify a function that satisfies the given argument
types      You may need to add explicit typecasts
 
hannu=# select trunc(float8(85.0::float4/0.0085::float4));trunc 
------- 9999
(1 row)

But this does work 

hannu=# select text(85.0::float4/0.0085::float4) = '10000';?column? 
----------t
(1 row)
As does this

hannu=# select round(float8(85.0::float4/0.0085::float4));round 
-------10000
(1 row)

--------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> Can't we assume that 80/10000 is numeric

I don't think so, unless you want to break a lot of existing
applications that assume integer/integer means integer division.
        regards, tom lane