Re: +/- Inf for float8's - Mailing list pgsql-general

On Mon, Aug 14, 2000 at 02:33:55PM +1000, Tim Allen wrote:
> I'm just trying out PG7.0.2, with a view to upgrading from 6.5.3, and I've
> found one quirk a little troublesome. Not sure whether I'll get any
> sympathy, but I shall ask anyway :).
>
> We find it convenient to be able to store +/- infinity for float8 values
> in some database tables. With Postgres 6.5.3, we were able to get away
> with this by using the values -1.79769313486232e+308 for -Inf and
> 1.79769313486232e+308 for Inf. This is probably not very portable, but
> anyway, it worked fine for us, on both x86 Linux and SGI IRIX. One thing,
> though, to get these numbers past the interface we had to put them in
> quotes. It seemed as though there was one level of parsing that didn't
> like these particular numbers, and one level of parsing that coped OK, and
> using quotes got it past the first level.
>
> Now, however (unfortunately for us), this inconsistency in the interface
> has been "fixed", and now we can't get this past the interface, either
> quoted or not. Fixing inconsistencies is, of course, in general, a good
> thing, which is why I'm not confident of getting much sympathy :).
>

Breaking working apps is never a good thing, but that's part of why it went
from 6.X to 7.X.

> So, any suggestions as to how we can store +/- infinity as a valid float8
> value in a database table?
>

Right: the SQL standard doesn't say anything about what to do for these
cases for floats (except by defining the syntax of an approximate numeric
constant as basically a float), but the IEEE754 does: as you discovered
below, they're NaN, -Infinity, and +Infinity.

> I notice, btw, that 'NaN' is accepted as a valid float8. Is there any
> particular reason why something similar for, eg '-Inf' and 'Inf' doesn't
> also exist? Just discovered, there is a special number 'Infinity', which
> seems to be recognised, except you can't insert it into a table because it
> reports an overflow error. Getting warm, it seems, but not there yet. And
> there doesn't seem to be a negative equivalent.

And this is a bug. From looking at the source, I see that Thomas added
code to accept 'NaN' and 'Infinity' (but not '-Infinity'), and Tom Lane
tweaked it, but it's never been able to get an Infinity all the way to
the table, as far as I can see: the value gets set to HUGE_VAL, but the
call to CheckFloat8Val compares against FLOAT8_MAX (and FLOAT8_MIN),
and complains, since HUGE_VAL is _defined_ to be larger than DBL_MAX.

And, there's no test case in the regression tests for inserting NaN or
Infinity. (Shame on Thomas ;-)

I think the right thing to do is move the call to CheckFloat8Val into a
branch of the test for NaN and Infinity, thereby not calling it if we've
been passed those constants. I'm compiling up a test of this right now,
and I'll submit a patch to Bruce if it passes regression. Looks like
that function hasn't been touch in a while, so the patch should apply
to 7.0.X as well as current CVS.

<some time later>

Looks like it works, and passes the regression tests as they are.  I'm
patching the tests to include the cases 'NaN', 'Infinity', and '-Infinity'
as valid float8s, and 'not a float' as an invalid representation, and
rerunning to get output to submit with the patch. This might be a bit
hairy, since there are 5 different expected/float8* files. Should I try
to hand patch them to deal with the new rows, or let them be regenerated
by people with the appropriate platforms?

<later again>

Bigger problem with changing the float8 regression tests: a lot of our
math functions seem to be guarded with CheckFloat8Val(result), so, if we
allow these values in a float8 column, most of the math functions with
elog(). It strikes me that there must have been a reason for this at one
time. There's even a #define UNSAFE_FLOATS, to disable these checks. By
reading the comments in old copies of float.c, it looks like this was
added for an old, buggy linux/Alpha libc that would throw floating point
exceptions, otherwise.

Is there an intrinsic problem with allowing values outside the range
FLOAT8_MAX <= x =>FLOAT8_MIN ? 'ORDER BY' seems to still work, with
'Infinity' and '-Infinity' sorting properly. Having a 'NaN' in there
breaks sorting however.  That's a current, live bug.  Could be fixed
by treating 'NaN' as a different flavor of NULL. Probably a fairly deep
change, however. Hmm, NULL in a float8 sorts to the end, regardless of
ASC or DESC, is that right?

Anyway, here's the patch for just float.c , if anyone wants to look
at it. As I said, it passes the existing float8 regression tests, but
raises a lot of interesting questions.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005