Thread: BUG in postgres mathematic
Postgres-7.0.3-2 RedHat-6.2 SELECT int8(5*27.81*100); 13904 SELECT int4(5*27.81*100); 13905 SELECT int8(27.81*100*5); 13905 -- ó Õ×ÁÖÅÎÉÅÍ, ÷ÁÝÅÎËÏ íÁËÓÉÍ, îÉÖÅÇÏÒÏÄÓËÉÅ ÉÎÆÏÒÍÁÃÉÏÎÎÙÅ ÓÅÔÉ (8312) 30-19-05, 34-00-02, 30-09-73 With best regards, Max Vaschenko, Nizhny Novgorod Information Networks.
This problem is not specific to Postgres. If you play around with a little C
program like:
#include <stdio.h>
int main(int argc, char * argv[])
{
float f = 27.81;
int i = 5;
int l = 100;
int ii = i*f*l;
long ll = l*f*i;
float ff = i*f*l;
printf("%i\n", ii);
printf("%li\n", ll);
printf("%.5f\n", ff);
printf("%i\n", (int) ff);
}
It prints:
13904
13904
13905.00000
13905
There is probably a good explanation for this. gcc 2.95 and egcs 2.91.66 do
this. Maybe a rounding problem.
On Thursday 25 January 2001 05:34, Max Vaschenko wrote:
> Postgres-7.0.3-2
> RedHat-6.2
>
> SELECT int8(5*27.81*100);
> 13904
>
> SELECT int4(5*27.81*100);
> 13905
>
> SELECT int8(27.81*100*5);
> 13905
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
"Robert B. Easter" <reaster@comptechnews.com> writes:
> This problem is not specific to Postgres.
The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety
floating-point roundoff error. However, I think Max has a fair
complaint here: it seems float-to-int8 conversion is truncating, not
rounding like the other conversions to integer do.
regression=# select 4.7::float8::int4;
?column?
----------
5
(1 row)
regression=# select 4.7::float8::int8;
?column?
----------
4
(1 row)
Seems to me this is a bug we should fix.
regards, tom lane
On Thursday 25 January 2001 22:52, Tom Lane wrote: > "Robert B. Easter" <reaster@comptechnews.com> writes: > > This problem is not specific to Postgres. > > The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety > floating-point roundoff error. However, I think Max has a fair > complaint here: it seems float-to-int8 conversion is truncating, not > rounding like the other conversions to integer do. > > regression=# select 4.7::float8::int4; > ?column? > ---------- > 5 > (1 row) > > regression=# select 4.7::float8::int8; > ?column? > ---------- > 4 > (1 row) > > Seems to me this is a bug we should fix. > > regards, tom lane Yeah, I agree. It isn't right that it truncates and that is something C does appearently. The fix is to pass the float through a rounding something like (long)(f + 0.5) or else C just truncates it off. This must already be happening for the int4 conversion or C would do the same thing to it. I didn't look at the Postgres sources yet, but it is probably one of those very easy things to fix. :) -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
Notice how the INT4 rounding is banker's rounding (round to the nearest even
number). That is what we would want the INT8 to do as well, not just a
simple round like I mentioned before. Again, the INT8 shows truncation. I've
been looking around the source code, but I can't see where all this
happens.
reaster=# SELECT 1.5::FLOAT::INT4;
?column?
----------
2
(1 row)
reaster=# SELECT 2.5::FLOAT::INT4;
?column?
----------
2
(1 row)
reaster=# SELECT 1.5::FLOAT::INT8;
?column?
----------
1
(1 row)
reaster=# SELECT 2.5::FLOAT::INT8;
?column?
----------
2
(1 row)
On Thursday 25 January 2001 22:52, Tom Lane wrote:
> "Robert B. Easter" <reaster@comptechnews.com> writes:
> > This problem is not specific to Postgres.
>
> The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety
> floating-point roundoff error. However, I think Max has a fair
> complaint here: it seems float-to-int8 conversion is truncating, not
> rounding like the other conversions to integer do.
>
> regression=# select 4.7::float8::int4;
> ?column?
> ----------
> 5
> (1 row)
>
> regression=# select 4.7::float8::int8;
> ?column?
> ----------
> 4
> (1 row)
>
> Seems to me this is a bug we should fix.
>
> regards, tom lane
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
>> The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety
>> floating-point roundoff error. However, I think Max has a fair
>> complaint here: it seems float-to-int8 conversion is truncating, not
>> rounding like the other conversions to integer do.
I have changed float8-to-int8 to start with an rint() call, the same as
float8-to-int4 and float8-to-int2. This should give the same roundoff
behavior as the other cases, including round-to-nearest-even if your
hardware supports IEEE-compliant float math.
Curiously, this change exposed what I take to be a platform dependency
in the int8 regress test. It was computing
int8(float8(4567890123456789::int8)) and expecting to get back exactly
4567890123456789. However, that value is 53 bits long and so there is
no margin for error in a standard IEEE float8 value. I find that at
least on HP hardware, rint() treats the value as inexact and rounds to
nearest even:
regression=# select round(4567890123456788::float8) - 4567890123456780::float8;
?column?
----------
8
(1 row)
regression=# select round(4567890123456789::float8) - 4567890123456780::float8;
?column?
----------
8
(1 row)
regression=# select round(4567890123456790::float8) - 4567890123456780::float8;
?column?
----------
10
(1 row)
regression=#
Whether this is a bug in rint or spec-compliant behavior is unclear, but
I'll bet HP's hardware is not the only platform that behaves this way.
Since I'm not eager to try to develop a new set of platform-specific
int8 expected files at this late hour, I just diked out that test
instead...
regards, tom lane
On Friday 26 January 2001 18:07, Tom Lane wrote:
> Curiously, this change exposed what I take to be a platform dependency
> in the int8 regress test. It was computing
> int8(float8(4567890123456789::int8)) and expecting to get back exactly
> 4567890123456789. However, that value is 53 bits long and so there is
> no margin for error in a standard IEEE float8 value. I find that at
> least on HP hardware, rint() treats the value as inexact and rounds to
> nearest even:
>
> regression=# select round(4567890123456788::float8) -
> 4567890123456780::float8; ?column?
> ----------
> 8
> (1 row)
>
> regression=# select round(4567890123456789::float8) -
> 4567890123456780::float8; ?column?
> ----------
> 8
> (1 row)
>
> regression=# select round(4567890123456790::float8) -
> 4567890123456780::float8; ?column?
> ----------
> 10
> (1 row)
>
> regression=#
>
> Whether this is a bug in rint or spec-compliant behavior is unclear, but
> I'll bet HP's hardware is not the only platform that behaves this way.
> Since I'm not eager to try to develop a new set of platform-specific
> int8 expected files at this late hour, I just diked out that test
> instead...
Here is what I get on Linux (PIII):
reaster=# select round(4567890123456788::float8) - 4567890123456780::float8;
?column?
----------
8
(1 row)
reaster=# select round(4567890123456789::float8) - 4567890123456780::float8;
?column?
----------
9
(1 row)
reaster=# select round(4567890123456790::float8) - 4567890123456780::float8;
?column?
----------
10
(1 row)
I'm not sure what the problem is either. The PIII has an 80-bit FPU but not
sure that matters. When there is no exponent, maybe only 52 bits are really
in the mantissa. If you try rounding numbers <= 4503599627370495 (2^52 - 1),
maybe you'll get expected results. The hidden bit is 0. Could be that round
or rint (whatever it is) always makes the hidden bit 1 when I think it should
only be 1 when the exponent is nonzero. I'm no float expert! :) Feel free
to correct me.
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------