Warning!!

Thus spake The Hermit Hacker

Tomorrow afternoon, at ~5pm ADT, I will be tag'ng and releasing v6.4 to
the world *muhahahaha*

Any last minute "no, don't do it" naysayers out there? :)

I have this idea for a new feature...

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

On Tue, 3 Nov 1998, D'Arcy J.M. Cain wrote:

Thus spake The Hermit Hacker

Tomorrow afternoon, at ~5pm ADT, I will be tag'ng and releasing v6.4 to
the world *muhahahaha*

Any last minute "no, don't do it" naysayers out there? :)

I have this idea for a new feature...

Shhhh...bruce might hear you *rofl*

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

On Tue, 3 Nov 1998, D'Arcy J.M. Cain wrote:

Thus spake The Hermit Hacker

Tomorrow afternoon, at ~5pm ADT, I will be tag'ng and releasing v6.4 to
the world *muhahahaha*

Any last minute "no, don't do it" naysayers out there? :)

I have this idea for a new feature...

Shhhh...bruce might hear you *rofl*

New feature! Tell me more...

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..) A bug I didn't
encounter isn't a bug for me...

BUT...

Just now I saw and posted this...

testdb=> select cash_mul_flt8('$1', '123.74');
cash_mul_flt8
-------------
$123.73
(1 row)

If the pence is 0-4 it rounds down... surely that's a rounding bug because
it should be looking at the next significant figure?

It's really small, and insignificant but I guess I should tell somone...

On Tue, 3 Nov 1998, Bruce Momjian wrote:

On Tue, 3 Nov 1998, D'Arcy J.M. Cain wrote:

Thus spake The Hermit Hacker

Tomorrow afternoon, at ~5pm ADT, I will be tag'ng and releasing v6.4 to
the world *muhahahaha*

Any last minute "no, don't do it" naysayers out there? :)

I have this idea for a new feature...

Shhhh...bruce might hear you *rofl*

New feature! Tell me more...

-- 
Bruce Momjian                        |  http://www.op.net/~candle
maillist@candle.pha.pa.us            |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

James (james@linuxrocks.co.uk)
Vortex Internet
My Windows unders~1 long filena~1, and yours?

Any last minute "no, don't do it" naysayers out there? :)

I have this idea for a new feature...

*grin* Thanks D'Arcy. Glad to see we didn't pound your sense of humor
out of ya. Will try harder next time ;)

I've been able to reproduce Jose's report of problems when casting
floats to text. The problem is that there is no explicit float to text
conversion function, but for other reasons I had decided to make float
and datetime equivalent (to allow weird date arithmetic). When Postgres
starts fishing around for conversion functions, it matches them up:

tgl=> select text(f) from a where f = 1;
text
----------------------------
Sat Jan 01 00:00:01 2000 GMT
(1 row)

Yuck. Anyway, I don't think it is a show stopper, and can be fixed with
a patch and in the v6.4.1 release. Probably should not make datetime and
float binary equivalent types as they are now.

- Tom

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..)
Just now I saw and posted this...
If the pence is 0-4 it rounds down... surely that's a rounding bug
because it should be looking at the next significant figure?

tgl=> select cash_mul_flt8('$123.77', '1');
cash_mul_flt8
-------------
$123.77
(1 row)

tgl=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)

That's annoying; it's non-symmetric too. The money type is stored as an
integer, and the float type is an IEEE double; looks like we have an LSB
rounding problem. Not sure what to do about it other than remove the
function, which isn't desirable I'm sure...

- Tom

Don't remove it... After all, I'm using this workaround in my software
for now and it seems OK....

testdb=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)

testdb=> select cash_mul_flt8('$1', '123.77000001');
cash_mul_flt8
-------------
$123.77
(1 row)

It happens in float4 too....

On Wed, 4 Nov 1998, Thomas G. Lockhart wrote:

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..)
Just now I saw and posted this...
If the pence is 0-4 it rounds down... surely that's a rounding bug
because it should be looking at the next significant figure?

tgl=> select cash_mul_flt8('$123.77', '1');
cash_mul_flt8
-------------
$123.77
(1 row)

tgl=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)

That's annoying; it's non-symmetric too. The money type is stored as an
integer, and the float type is an IEEE double; looks like we have an LSB
rounding problem. Not sure what to do about it other than remove the
function, which isn't desirable I'm sure...

- Tom

James (james@linuxrocks.co.uk)
Vortex Internet
My Windows unders~1 long filena~1, and yours?

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..) A bug I didn't
encounter isn't a bug for me...

BUT...

Just now I saw and posted this...

testdb=> select cash_mul_flt8('$1', '123.74');
cash_mul_flt8
-------------
$123.73
(1 row)

If the pence is 0-4 it rounds down... surely that's a rounding bug because
it should be looking at the next significant figure?

It's really small, and insignificant but I guess I should tell somone...

Works here on BSD/OS 4.0. Looks like an OS bug, perhaps.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..)
Just now I saw and posted this...
If the pence is 0-4 it rounds down... surely that's a rounding bug
because it should be looking at the next significant figure?

tgl=> select cash_mul_flt8('$123.77', '1');
cash_mul_flt8
-------------
$123.77
(1 row)

tgl=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)

That's annoying; it's non-symmetric too. The money type is stored as an
integer, and the float type is an IEEE double; looks like we have an LSB
rounding problem. Not sure what to do about it other than remove the
function, which isn't desirable I'm sure...

This is really bad. I can't imagine a 5-digit double is loosing
rounding on a double. There must be some other problem, but I can't
reproduce it here on BSD/OS.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

It only happens here for some figues, not all.... mostly xx.x[1234]

On Wed, 4 Nov 1998, Bruce Momjian wrote:

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..)
Just now I saw and posted this...
If the pence is 0-4 it rounds down... surely that's a rounding bug
because it should be looking at the next significant figure?

tgl=> select cash_mul_flt8('$123.77', '1');
cash_mul_flt8
-------------
$123.77
(1 row)

tgl=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)

That's annoying; it's non-symmetric too. The money type is stored as an
integer, and the float type is an IEEE double; looks like we have an LSB
rounding problem. Not sure what to do about it other than remove the
function, which isn't desirable I'm sure...

This is really bad. I can't imagine a 5-digit double is loosing
rounding on a double. There must be some other problem, but I can't
reproduce it here on BSD/OS.

-- 
Bruce Momjian                        |  http://www.op.net/~candle
maillist@candle.pha.pa.us            |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

James (james@linuxrocks.co.uk)
Vortex Internet
My Windows unders~1 long filena~1, and yours?

I hope it is! I dont have the skill set to track it down but mabe a
library?

On Wed, 4 Nov 1998, Bruce Momjian wrote:

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..) A bug I didn't
encounter isn't a bug for me...

BUT...

Just now I saw and posted this...

testdb=> select cash_mul_flt8('$1', '123.74');
cash_mul_flt8
-------------
$123.73
(1 row)

If the pence is 0-4 it rounds down... surely that's a rounding bug because
it should be looking at the next significant figure?

It's really small, and insignificant but I guess I should tell somone...

Works here on BSD/OS 4.0. Looks like an OS bug, perhaps.

-- 
Bruce Momjian                        |  http://www.op.net/~candle
maillist@candle.pha.pa.us            |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

James (james@linuxrocks.co.uk)
Vortex Internet
My Windows unders~1 long filena~1, and yours?

It only happens here for some figures, not all.... mostly xx.x[1234]

What system and compiler are you using?

- Tom

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:

tgl=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)
That's annoying; it's non-symmetric too.

And hardware-dependent, evidently, because I get the right thing on HP:

play=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.77
(1 row)

The money type is stored as an
integer, and the float type is an IEEE double; looks like we have an LSB
rounding problem. Not sure what to do about it other than remove the
function, which isn't desirable I'm sure...

What's needed is explicit rounding. Instead of letting the compiler do
the rounding during its implicit float-to-int conversion (which
generally truncates towards 0 or towards -infinity), do

*** cash.c~	Wed Nov  4 10:11:57 1998
--- cash.c	Wed Nov  4 10:13:06 1998
***************
*** 17,22 ****
--- 17,23 ----
  #include <limits.h>
  #include <ctype.h>
  #include <locale.h>
+ #include <math.h>

#include "postgres.h"
#include "miscadmin.h"
***************
*** 419,425 ****
if (!PointerIsValid(result = palloc(sizeof(Cash))))
elog(ERROR, "Memory allocation failed, can't multiply cash", NULL);

! *result = ((*f) * (*c));

  	return result;
  }	/* cash_mul_flt8() */
--- 420,426 ----
  	if (!PointerIsValid(result = palloc(sizeof(Cash))))
  		elog(ERROR, "Memory allocation failed, can't multiply cash", NULL);

! *result = floor((*f) * (*c) + 0.5);

return result;
} /* cash_mul_flt8() */

(Alternatively you could do it with rint(), although I distrust rint()
because it's context-dependent...)

The other cash functions also need to be looked at for rounding
sloppiness, so I see no point in committing this fix by its lonesome.

Lessee, who's the author of cash.c ... looks like it's D'Arcy ...
D'Arcy, you wanna put this on your to-do list?

regards, tom lane

On Wed, 4 Nov 1998, Bruce Momjian wrote:

I'm very new to this list, and I have to first say that 6.4beta5 fixes
every problem I had encountered with 6.3.2 (A small list..)
Just now I saw and posted this...
If the pence is 0-4 it rounds down... surely that's a rounding bug
because it should be looking at the next significant figure?

tgl=> select cash_mul_flt8('$123.77', '1');
cash_mul_flt8
-------------
$123.77
(1 row)

It only happens here for some figues, not all.... mostly xx.x[1234]

I used your exact example, and it worked here.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sorry, should have been the first thing to say...

Linux 2.0.29
GCC 2.7.2
/lib/libc.so.5.3.12

For some reason it won't compile on my newer system GCC 2.8.1/glibc but
I'll look at that when I have some time....

On Wed, 4 Nov 1998, Thomas G. Lockhart wrote:

It only happens here for some figures, not all.... mostly xx.x[1234]

What system and compiler are you using?

- Tom

James (james@linuxrocks.co.uk)
Vortex Internet
My Windows unders~1 long filena~1, and yours?

Linux 2.0.29
GCC 2.7.2
/lib/libc.so.5.3.12

Similar to mine (Linux-2.0.30, gcc-2.7.2.1, same libc), which also
showed the symptom. I've also tried compiling in the past with egcs-1.1
and found many more floating point rounding differences.

So it's a gcc/libc problem. In it's defense, I'm more accustomed to
seeing more odd rounding behavior with other platforms.

btw, this is definitely *not* an issue to affect our upcoming release.

- Tom

Thus spake Tom Lane

And hardware-dependent, evidently, because I get the right thing on HP:

play=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.77
(1 row)

Works fine on NetBSD -current.

! *result = floor((*f) * (*c) + 0.5);

Is this correct? What about "select cash_mul_flt8('$1', '123.99')?"
Wouldn't that convert it to 124.00?

The other cash functions also need to be looked at for rounding
sloppiness, so I see no point in committing this fix by its lonesome.

Lessee, who's the author of cash.c ... looks like it's D'Arcy ...
D'Arcy, you wanna put this on your to-do list?

Well, I didn't do those portions but I can look at it at some point I
suppose. I'm just not sure that it is as simple as suggested above.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Well, I didn't do those portions but I can look at it at some point I
suppose. I'm just not sure that it is as simple as suggested above.

Right. I'd suggest going slow until we understand where this is actually
coming from. If you didn't add these in then it's possible I did; don't
remember though.

- Thomas

darcy@druid.net (D'Arcy J.M. Cain) writes:

! *result = floor((*f) * (*c) + 0.5);

Is this correct? What about "select cash_mul_flt8('$1', '123.99')?"
Wouldn't that convert it to 124.00?

No, because the cash type is actually "int". Presumably it's scaled
up by a factor of 100 compared to what's printed --- otherwise you'd
never be able to represent pennies at all. So the "round to nearest
integer" logic depicted above is really rounding to nearest penny.

regards, tom lane

A James Lewis <james@vrtx.net> writes:

Sorry, should have been the first thing to say...

Linux 2.0.29
GCC 2.7.2
/lib/libc.so.5.3.12

For some reason it won't compile on my newer system GCC 2.8.1/glibc but
I'll look at that when I have some time....

I just tried the same example on 6.4beta4, with linux2.0.36pre
something, glibc and egcs1.1. (It compiled fine, for what it's
worth.)

I also get this erroneous behaviour:

postgres=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)

and

postgres=> select cash_mul_flt8('$1', '123.99');
cash_mul_flt8
-------------
$123.98
(1 row)

This may be sense from a numerical analysis point of view, but it
suggests there's a problem somewhere: this is surely behaving in a
surprising way.

Should I add this to the TODO list?

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:

tgl=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.76
(1 row)
That's annoying; it's non-symmetric too.

And hardware-dependent, evidently, because I get the right thing on HP:

play=> select cash_mul_flt8('$1', '123.77');
cash_mul_flt8
-------------
$123.77
(1 row)

The money type is stored as an
integer, and the float type is an IEEE double; looks like we have an LSB
rounding problem. Not sure what to do about it other than remove the
function, which isn't desirable I'm sure...

What's needed is explicit rounding. Instead of letting the compiler do
the rounding during its implicit float-to-int conversion (which
generally truncates towards 0 or towards -infinity), do

*** cash.c~	Wed Nov  4 10:11:57 1998
--- cash.c	Wed Nov  4 10:13:06 1998
***************
*** 17,22 ****
--- 17,23 ----
#include <limits.h>
#include <ctype.h>
#include <locale.h>
+ #include <math.h>

#include "postgres.h"
#include "miscadmin.h"
***************
*** 419,425 ****
if (!PointerIsValid(result = palloc(sizeof(Cash))))
elog(ERROR, "Memory allocation failed, can't multiply cash", NULL);

! *result = ((*f) * (*c));

return result;
}	/* cash_mul_flt8() */
--- 420,426 ----
if (!PointerIsValid(result = palloc(sizeof(Cash))))
elog(ERROR, "Memory allocation failed, can't multiply cash", NULL);

! *result = floor((*f) * (*c) + 0.5);

return result;
} /* cash_mul_flt8() */

(Alternatively you could do it with rint(), although I distrust rint()
because it's context-dependent...)

The other cash functions also need to be looked at for rounding
sloppiness, so I see no point in committing this fix by its lonesome.

Lessee, who's the author of cash.c ... looks like it's D'Arcy ...
D'Arcy, you wanna put this on your to-do list?

regards, tom lane

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Should I add this to the TODO list?

What's needed is explicit rounding. Instead of letting the compiler do
the rounding during its implicit float-to-int conversion (which
generally truncates towards 0 or towards -infinity), do
[snip]
The other cash functions also need to be looked at for rounding
sloppiness, so I see no point in committing this fix by its lonesome.

Yes, that still needs to be dealt with. I suggest

* Fix roundoff problems in "cash" datatype.

Actually, the whole cash datatype ought to be obsoleted by real
fixed-point-numeric support, but that's a *much* bigger project...

regards, tom lane

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Should I add this to the TODO list?

What's needed is explicit rounding. Instead of letting the compiler do
the rounding during its implicit float-to-int conversion (which
generally truncates towards 0 or towards -infinity), do
[snip]
The other cash functions also need to be looked at for rounding
sloppiness, so I see no point in committing this fix by its lonesome.

Yes, that still needs to be dealt with. I suggest

* Fix roundoff problems in "cash" datatype.

Added.

Actually, the whole cash datatype ought to be obsoleted by real
fixed-point-numeric support, but that's a *much* bigger project...

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026