The 85/0.0085 mistery ?

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

"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

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

"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

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