How PostgreSQL's floating-point hurts everyone everywhere

What happened?

This may be platform-specific behavior. I see on my Linux/Mandrake box
the following:

lockhart=# select 'nan'::float8;
ERROR: Bad float8 input format -- overflow
lockhart=# select 'infinity'::float8;
ERROR: Bad float8 input format -- overflow

Typically, machines will trap overflow/underflow/NaN problems in
floating point, but silently ignore these properties with integer
arithmetic. It would be nice to have consistant behavior across all
types, but I'll stick to floats for discussion now.

Lots of machines (but not all) now support IEEE arithmetic. On those
machines, istm that we can and should support some of the IEEE
conventions such as NaN and +Inf/-Inf. But on those machines which do
not have this concept, we can either try to detect this during data
entry, or trap this explicitly during floating point exceptions, or
watch the backend go up in flames (which would be the default behavior).

Same with divide-by-zero troubles.

I think the CheckFloat{4,8}Val() functions should be abandoned and the
floating point results should be allowed to overflow to +Infinity or
underflow to -Infinity. There also need to be isinf() and isnan()
functions, because surely "x = 'infinity'" isn't going to work.

It does work if "infinity" is first interpreted by our input routines.

I recall running into some of these issues when coding some data
handling routines on my late, great Alpha boxes. In this case, I tried
to use the isinf() (??) routine provided by DEC (and defined in IEEE?)
to test for bad values coming from a real-time GPS tracking system. But
until I futzed with the compiler options, calling isinf() failed on any
infinity value since the value was being checked during the call to the
routine, so the value was never getting to the test!

This is not a high-priority issue to me, nor do I feel particularly
qualified on the details, but at least we might agree that something's
wrong.

I'd think that, on some platforms, we can try coding things a bit
differently. But the code in there now does do some useful things for
some of the platforms we run on (though there are still holes in
possible failure modes). imho if we change things it would be to turn
some of the checking into NOOP macros on some platforms, but preserve
these for others.

- Thomas

At 4:47 PM +0000 7/20/00, Thomas Lockhart wrote:

Typically, machines will trap overflow/underflow/NaN problems in
floating point, but silently ignore these properties with integer
arithmetic. It would be nice to have consistant behavior across all
types, but I'll stick to floats for discussion now.

The IEEE standard says that that behavior must be configurable. The
standard behavior in Fortran is to ignore floating point exceptions
as well. Unfortunately the name of the C routine which changes it is
not defined in the standard.

This is a bit off-topic but we have this problem with the DS1
spacecraft software. Everything is run with the exceptions enabled
because we don't want to allow those values undetected in the
attitude control calculations. OTOH we are vulnerable to reboots
(and have had one) due to mistakes in other code.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

Peter Eisentraut wrote:

peter=# select 1.0/0.0;
ERROR: float8div: divide by zero error

Division by zero is not an "error" in floating point arithmetic.

No?

Oh, then 7 = 5 because:

Assuming 2a = b | * 2
4a = 2b | + 10a
14a = 10a + 2b | - 7b
14a - 7b = 10a - 5b | ()
7 (2a - b) = 5 (2a - b) | / (2a - b)
7 = 5

In the given context, you should find the mistake pretty
easy. Maybe the message should be changed to

ERROR: floatdiv: divide by zero nonsense
^^^^^^^^

because a division by zero results in nonsense? Or should it
just return a random value? What is the result of a division
by zero? It's definitely not infinity, as the above
demonstrates!

You might be looking from a managers PoV. Managers usually
use this kind of arithmetic to choose salaries. Any engineer
knows that

work = power * time

We all know that time is money and that power is knowlede. So

work = knowledge * money

and thus

work
money = ---------
knowledge

Obviously, the lesser you know, the more money you get,
independant of the work to be done. As soon as you know
nothing (zero), any money you get for the work is nonsense!

This applies well to the real world, so it makes sense from
an OO PoV. But in science, I think it's still an error.

Since PostgreSQL is an ORDBMS (not an OODBMS), I think it's
correct to report an error instead of returning some random.

:-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

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

I'd think that, on some platforms, we can try coding things a bit
differently. But the code in there now does do some useful things for
some of the platforms we run on (though there are still holes in
possible failure modes).

Yes. But on machines that do have IEEE-compliant math, it would be
nice to act more IEEE-ish than we do. Perhaps a compile-time option
for IEEE vs "traditional Postgres" behavior?

regards, tom lane

Yes. But on machines that do have IEEE-compliant math, it would be
nice to act more IEEE-ish than we do. Perhaps a compile-time option
for IEEE vs "traditional Postgres" behavior?

Sure. Sounds like a job for configure...

- Thomas

In case you didn't notice:

http://biz.yahoo.com/prnews/000725/ca_inprise_2.html

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2582
Howitzvej 75 �ben 14.00-18.00 Email: kar@webline.dk
2000 Frederiksberg L�rdag 11.00-17.00 Web: www.suse.dk

Kaare Rasmussen <kar@webline.dk> wrote:

In case you didn't notice:

http://biz.yahoo.com/prnews/000725/ca_inprise_2.html

Yes, and now the story is up on slashdot:

http://slashdot.org/comments.pl?sid=00%2F07%2F25%2F1439226&amp;cid=&amp;pid=0&amp;startat=&amp;threshold=-1&amp;mode=nested&amp;commentsort=3&amp;op=Change

So if anyone feels like taking time out to do some
evangelizing, this might be a good moment to say a few words
about Postgresql vs. "Inprise, The Open Source Database".

In particular, this guy could use a detailed reply:

At 03:40 PM 7/25/00 -0700, Joe Brenner wrote:

Kaare Rasmussen <kar@webline.dk> wrote:

In case you didn't notice:

http://biz.yahoo.com/prnews/000725/ca_inprise_2.html

Yes, and now the story is up on slashdot:

http://slashdot.org/comments.pl?sid=00%2F07%2F25%2F1439226&amp;cid=&amp;pid=0&amp;start

at=&threshold=-1&mode=nested&commentsort=3&op=Change

So if anyone feels like taking time out to do some
evangelizing, this might be a good moment to say a few words
about Postgresql vs. "Inprise, The Open Source Database".

In particular, this guy could use a detailed reply:

Just be sure to avoid any talk of outer joins ...

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

$ find InterBase -name \*.c |xargs cat |wc
481977 1417203 11430946
$ find postgresql-7.0.2 -name \*.c |xargs cat |wc
329582 1087860 8649018

$ wc InterBase/dsql/parse.y
4217 13639 103059 InterBase/dsql/parse.y
$ wc postgresql-7.0.2/src/backend/parser/gram.y
5858 20413 149104 postgresql-7.0.2/src/backend/parser/gram.y

I downloaded it, just to have a poke around. It doesn't build very
easily, if at all. The best way I can describe the source is that it is
dry reading. Not much interesting commentary. Very big source files,
with very long functions and huge case statements is how I would
characterise it. I suspect it's reasonably well written but not very
extensible in nature. I don't think this is going to set the world on
fire anytime soon.

$ wc InterBase/dsql/parse.y
4217 13639 103059 InterBase/dsql/parse.y
$ wc postgresql-7.0.2/src/backend/parser/gram.y
5858 20413 149104 postgresql-7.0.2/src/backend/parser/gram.y

Hmm. I suspect that I could shrink our gram.y by ~25% just by removing
comments and C support routines, and by consolidating some execution
blocks onto fewer lines. Does it look like their parse.y is more dense
than ours, do they do a lot of postprocessing to eliminate the yacc
rules, or have we missed the boat on writing the grammar in yacc?

Just curious; I probably won't look myself since I don't want to run the
risk of compromising our code and licensing. Or is that not an issue
with the Inprise license?

- Thomas

Thomas Lockhart wrote:

$ wc InterBase/dsql/parse.y
4217 13639 103059 InterBase/dsql/parse.y
$ wc postgresql-7.0.2/src/backend/parser/gram.y
5858 20413 149104 postgresql-7.0.2/src/backend/parser/gram.y

Hmm. I suspect that I could shrink our gram.y by ~25% just by removing
comments and C support routines, and by consolidating some execution
blocks onto fewer lines. Does it look like their parse.y is more dense
than ours, do they do a lot of postprocessing to eliminate the yacc
rules, or have we missed the boat on writing the grammar in yacc?

Just curious; I probably won't look myself since I don't want to run the
risk of compromising our code and licensing. Or is that not an issue
with the Inprise license?

I had a bit of a look. There's no obvious reason, just maybe postgres
has a few more comments and a bit more code inside the action blocks. No
obvious problem here.

It would be a pity if we can't look and learn from Interbase in this
instance, because this is one area where there is at least a possibility
of borrowing something useful.

Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> wrote:

Thomas Lockhart <lockhart@alumni.caltech.edu> wrote:

Just curious; I probably won't look myself since I don't want to run the
risk of compromising our code and licensing. Or is that not an issue
with the Inprise license?

I had a bit of a look. There's no obvious reason, just maybe postgres
has a few more comments and a bit more code inside the action blocks. No
obvious problem here.

It would be a pity if we can't look and learn from Interbase in this
instance, because this is one area where there is at least a possibility
of borrowing something useful.

Well, the license is just the Mozilla Public License with
the names changed. I've just read through it several times,
and I think the main trouble with it is you probably really
do need to have a lawyer look at it... but I think you could
go as far as to include some of the Inprise source files
into postgresql:

http://www.inprise.com/IPL.html

3.7. Larger Works.

You may create a Larger Work by combining Covered Code with
other code not governed by the terms of this License and
distribute the Larger Work as a single product. In such a
case, You must make sure the requirements of this License
are fulfilled for the Covered Code.

The requirements seem to be pretty commonsense things...
If you use some source code from Inprise, you've got to
keep track of where the source came from, label it with
their license, list any modifications you've made, always
provide the source with any executables, etc.

There's also a bunch of stuff about how this license doesn't
release you from any third party intellectual property
claims (duh! Legal docs always seem to state the obvious at
great length). I might wonder what would happen if Borland
owned a software patent on some algorithm that's included in
this code...

But no, I *think* that's a non-issue:

The Initial Developer hereby grants You a world-wide,
royalty-free, non-exclusive license, subject to third
party intellectual property claims:

http://www.inprise.com/IPL.html

3.7. Larger Works.

You may create a Larger Work by combining Covered Code with
other code not governed by the terms of this License and
distribute the Larger Work as a single product. In such a
case, You must make sure the requirements of this License
are fulfilled for the Covered Code.

The requirements seem to be pretty commonsense things...
If you use some source code from Inprise, you've got to
keep track of where the source came from, label it with
their license, list any modifications you've made, always
provide the source with any executables, etc.

But the BSD license doesn't require source for distributed binaries.
Sounds like a GPL-style restriction.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

The requirements seem to be pretty commonsense things...
If you use some source code from Inprise, you've got to
keep track of where the source came from, label it with
their license, list any modifications you've made, always
provide the source with any executables, etc.

But the BSD license doesn't require source for distributed binaries.
Sounds like a GPL-style restriction.

What is more important to my mind is if the license permits a developer to look
at the code and get inspired, or if the developer's mind will be "tainted" just
by looking.
I hope someone can tell; I always wake up later with my head on the keyboard
when I try to read license stuff...

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2582
Howitzvej 75 �ben 14.00-18.00 Email: kar@webline.dk
2000 Frederiksberg L�rdag 11.00-17.00 Web: www.suse.dk

Kaare Rasmussen <kar@webline.dk> writes:

What is more important to my mind is if the license permits a
developer to look at the code and get inspired, or if the developer's
mind will be "tainted" just by looking.

It is not possible to be "tainted" by looking. There are only two kinds
of intellectual property rights (at least in the USA) and neither one
creates that risk:

1. Copyright means you can't take the code verbatim, just like you can't
plagiarize a novel. You can use the same ideas (plot, characters, etc)
but you have to express 'em in your own words. Structure the code
differently, use different names, write your own comments, etc, and
you're clear even if you lifted the algorithm lock stock & barrel.

2. Patent means you can't use the algorithm. However, looking doesn't
create extra risk here, because you can't use a patented algorithm
(without paying) no matter how you learned of it --- not even if you
invented it independently.

As far as I've heard, Inprise isn't claiming any patent rights in
connection with the Interbase code anyway, but it might be a good idea
for someone to check before we all start reading their code...

regards, tom lane

Kaare Rasmussen wrote:

The requirements seem to be pretty commonsense things...
If you use some source code from Inprise, you've got to
keep track of where the source came from, label it with
their license, list any modifications you've made, always
provide the source with any executables, etc.

But the BSD license doesn't require source for distributed binaries.
Sounds like a GPL-style restriction.

What is more important to my mind is if the license permits a developer to look
at the code and get inspired, or if the developer's mind will be "tainted" just
by looking.
I hope someone can tell; I always wake up later with my head on the keyboard
when I try to read license stuff...

I don't think the licence terms can have any effect on this. If you take
an idea from one code base and apply it to another code-bases with a
different licence, then the applicable law is going to be fair use. And
licence terms cannot affect fair use one way or the other.

* Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> [000730 16:52] wrote:

Kaare Rasmussen wrote:

The requirements seem to be pretty commonsense things...
If you use some source code from Inprise, you've got to
keep track of where the source came from, label it with
their license, list any modifications you've made, always
provide the source with any executables, etc.

But the BSD license doesn't require source for distributed binaries.
Sounds like a GPL-style restriction.

What is more important to my mind is if the license permits a developer to look
at the code and get inspired, or if the developer's mind will be "tainted" just
by looking.
I hope someone can tell; I always wake up later with my head on the keyboard
when I try to read license stuff...

I don't think the licence terms can have any effect on this. If you take
an idea from one code base and apply it to another code-bases with a
different licence, then the applicable law is going to be fair use. And
licence terms cannot affect fair use one way or the other.

With the obvious exception of patented algorithms. You do need to
be very careful, at least one major open source project violates
USL patents.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."