[HACKERS] somebody could explain this?

2005-11-04 Thread Cristian Prieto 
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

Re: [HACKERS] somebody could explain this?

2005-11-04 Thread Andrew Dunstan 


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

 



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] somebody could explain this?

2005-11-04 Thread Martijn van Oosterhout 
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.) = 14. You need to decide whether maybe
you want round() instead. Or decide the actual cutoff yourself. I
sometimes fix this by adding 0.1 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.


pgpkJMD5sNVGk.pgp
Description: PGP signature

Re: [HACKERS] somebody could explain this?

2005-11-04 Thread Tom Lane 
Cristian Prieto [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] somebody could explain this?

2005-11-04 Thread Csaba Nagy 
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


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

Re: [HACKERS] somebody could explain this?

2005-11-04 Thread Otto Hirr 

 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.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] somebody could explain this?

2005-11-04 Thread Greg Stark 
Otto Hirr [EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings