Обсуждение: somebody could explain this?

Hello, I'm using PostgreSQL 8.0.4 in Fedora Core 3, right now I'm learning a
little about the postgresql internals and the way some kind of SPs could be
written in c language; I found something really weird and I cannot explain
to me this behavior:

#include "postgres.h"
#include "fmgr.h"

PG_FUNCTION_INFO_V1(test);

Datum
repeat_item(PG_FUNCTION_ARGS)
{int num_times;num_times = PG_GETARG_FLOAT8(0) * 100;PG_RETURN_INT32(num_times);
}


Inside psql this happens:

# Create or replace function test(float) returns integer as 'test.so'
language 'c' stable;

select test(0.1);
Returns 10

Select test(0.11);
Returns 11

Select test(0.12)
Returns 11

Select test(0.13)
Returns 13

Select test(0.14)
Returns 14

Select test(0.15)
Returns 14


What Is happening here?

On Fri, Nov 04, 2005 at 10:16:50AM -0600, Cristian Prieto wrote:
> Hello, I'm using PostgreSQL 8.0.4 in Fedora Core 3, right now I'm learning a
> little about the postgresql internals and the way some kind of SPs could be
> written in c language; I found something really weird and I cannot explain
> to me this behavior:

What's happening here is that the multiplication, being floating point,
has some accumulated error such that when you multiply it by 100 and
convert it to an int, it hits the cutoff.

Note: converting a float to an int tends to invoke trunc() or something
similar. trunc(14.999999999999) = 14. You need to decide whether maybe
you want round() instead. Or decide the actual cutoff yourself. I
sometimes fix this by adding 0.00001 to numbers before converting to
integer, to avoid these issues.

Floating points numbers are accurate but not precise.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Classic behaviour, which has nothing to do with postgres. Try the 
program below to see the same effect. You probably should be using a 
rounding function to see what you seem to expect.

cheers

andrew



#include <stdio.h>

main()
{
double x[] = { 0.1, 0.11, 0.12, 0.13, 0.14, 0.15 };
int i,n;
for (i=0 ; i < 6; i++)
{
n = x[i] * 100;
printf("%d\n",n);
}

}


Cristian Prieto wrote:

>Hello, I'm using PostgreSQL 8.0.4 in Fedora Core 3, right now I'm learning a
>little about the postgresql internals and the way some kind of SPs could be
>written in c language; I found something really weird and I cannot explain
>to me this behavior:
>
>#include "postgres.h"
>#include "fmgr.h"
>
>PG_FUNCTION_INFO_V1(test);
>
>Datum
>repeat_item(PG_FUNCTION_ARGS)
>{
>    int num_times;
>    
>    num_times = PG_GETARG_FLOAT8(0) * 100;
>    PG_RETURN_INT32(num_times);
>}
>
>
>Inside psql this happens:
>
># Create or replace function test(float) returns integer as 'test.so'
>language 'c' stable;
>
>select test(0.1);
>Returns 10
>
>Select test(0.11);
>Returns 11
>
>Select test(0.12)
>Returns 11
>
>Select test(0.13)
>Returns 13
>
>Select test(0.14)
>Returns 14
>
>Select test(0.15)
>Returns 14
>
>
>What Is happening here?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>  
>

"Cristian Prieto" <cristian@clickdiario.com> writes:
> Datum
> repeat_item(PG_FUNCTION_ARGS)
> {
>     int num_times;
>     num_times = PG_GETARG_FLOAT8(0) * 100;
>     PG_RETURN_INT32(num_times);
> }

> # Create or replace function test(float) returns integer as 'test.so'
> language 'c' stable;

"float" is FLOAT4 not FLOAT8 ... your function is picking up some
garbage data.

There might also be some roundoff issues to think about --- I'd be
inclined to add an rint() call, or at least add 0.5 before truncating to
integer.
        regards, tom lane

[snip]
> Floating points numbers are accurate but not precise.

OK, now this one beats me... what's the difference between "accurate"
and "exact" ? I thought both mean something like "correct", but precise
refers to some action and accurate applies to a situation or
description...

I'm actually curios what it means. Merriam-Webster refers for both to
"correct" as a synonym.

Cheers,
Csaba.

Csaba Nagy wrote:
> [snip]
>
> > Floating points numbers are accurate but not precise.
>
> OK, now this one beats me... what's the difference between "accurate"
> and "exact" ? I thought both mean something like "correct", but
> precise refers to some action and accurate applies to a situation or
> description...

Accurate means close to the real value, precise means having a lot of 
detail.

So 3 is more accurate than 4 as a representation of "Pi", but both are 
not very precise.

5.32290753057207250735 is a very precise representation of "Pi" but 
totally inaccurate.

This also means that the statement at the top is wrong.  It should be 
the other way around.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Fri, Nov 04, 2005 at 18:30:56 +0100, Csaba Nagy <nagy@ecircle-ag.com> wrote:
> [snip]
> > Floating points numbers are accurate but not precise.
> 
> OK, now this one beats me... what's the difference between "accurate"
> and "exact" ? I thought both mean something like "correct", but precise
> refers to some action and accurate applies to a situation or
> description...
> 
> I'm actually curios what it means. Merriam-Webster refers for both to
> "correct" as a synonym.

My memory from science courses a long time ago, is that precision refers
to how much information you have (e.g. the number of digits in a number)
and accuracy refers to how close your information is to reality.
Using a precision that was much higher than justified by accuracy used to
get points deducted from lab report grades.

In mathematics "precise" has a somewhat different meaning, but isn't a synonym
for "accurate" in that context.

Cristian,

I bet it's related to some rounding issue and the fact that floating
formats are approximative even for small integers.
Probably 12 ands up being slightly less in floating format (something
like 11.999...), and the cast to integer is truncating it.
Not 100% sure though... read up on your API, I'm not a C programmer :-)

HTH,
Csaba.


On Fri, 2005-11-04 at 17:16, Cristian Prieto wrote:
> Hello, I'm using PostgreSQL 8.0.4 in Fedora Core 3, right now I'm learning a
> little about the postgresql internals and the way some kind of SPs could be
> written in c language; I found something really weird and I cannot explain
> to me this behavior:
> 
> #include "postgres.h"
> #include "fmgr.h"
> 
> PG_FUNCTION_INFO_V1(test);
> 
> Datum
> repeat_item(PG_FUNCTION_ARGS)
> {
>     int num_times;
>     
>     num_times = PG_GETARG_FLOAT8(0) * 100;
>     PG_RETURN_INT32(num_times);
> }
> 
> 
> Inside psql this happens:
> 
> # Create or replace function test(float) returns integer as 'test.so'
> language 'c' stable;
> 
> select test(0.1);
> Returns 10
> 
> Select test(0.11);
> Returns 11
> 
> Select test(0.12)
> Returns 11
> 
> Select test(0.13)
> Returns 13
> 
> Select test(0.14)
> Returns 14
> 
> Select test(0.15)
> Returns 14
> 
> 
> What Is happening here?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

> What's happening here is that the multiplication, being 
> floating point,
> has some accumulated error such that when you multiply it by 100 and
> convert it to an int, it hits the cutoff.

Trivia...
I heard a story many years ago that landed a programmer in prison...
He worked on the program that calculated interest that was to be
deposited into an account. Instead of rounding or truncating the
amount beyond what the bank wanted to use, he saved the small fractional
dollars (really micro-pennies) into an account. Soon he had several
hundred thousand in the account - there is a lot of multiplication...
He got caught.

"Otto Hirr" <otto.hirr@olabinc.com> writes:

> Trivia...
> I heard a story many years ago that landed a programmer in prison...
> He worked on the program that calculated interest that was to be
> deposited into an account. Instead of rounding or truncating the
> amount beyond what the bank wanted to use, he saved the small fractional
> dollars (really micro-pennies) into an account. Soon he had several
> hundred thousand in the account - there is a lot of multiplication...
> He got caught.

Uh yeah, that story would be the plot to Superman II (and Office Space too).


-- 
greg