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., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Index: backend/utils/adt/float.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt/float.c,v
retrieving revision 1.68
diff -c -r1.68 float.c
*** backend/utils/adt/float.c 2000/08/07 00:51:14 1.68
--- backend/utils/adt/float.c 2000/08/14 18:33:22
***************
*** 248,253 ****
--- 248,255 ----
val = NAN;
else if (strcasecmp(num, "Infinity") == 0)
val = HUGE_VAL;
+ else if (strcasecmp(num, "-Infinity") == 0)
+ val = -HUGE_VAL;
else
elog(ERROR, "Bad float8 input format '%s'", num);
}
***************
*** 255,264 ****
{
if (errno == ERANGE)
elog(ERROR, "Input '%s' is out of range for float8", num);
}
- CheckFloat8Val(val);
-
PG_RETURN_FLOAT8(val);
}
--- 257,265 ----
{
if (errno == ERANGE)
elog(ERROR, "Input '%s' is out of range for float8", num);
+ CheckFloat8Val(val);
}
PG_RETURN_FLOAT8(val);
}
***************
*** 275,282 ****
if (isnan(num))
PG_RETURN_CSTRING(strcpy(ascii, "NaN"));
! if (isinf(num))
PG_RETURN_CSTRING(strcpy(ascii, "Infinity"));
sprintf(ascii, "%.*g", DBL_DIG, num);
PG_RETURN_CSTRING(ascii);
--- 276,285 ----
if (isnan(num))
PG_RETURN_CSTRING(strcpy(ascii, "NaN"));
! if (isinf(num)>0)
PG_RETURN_CSTRING(strcpy(ascii, "Infinity"));
+ if (isinf(num)<0)
+ PG_RETURN_CSTRING(strcpy(ascii, "-Infinity"));
sprintf(ascii, "%.*g", DBL_DIG, num);
PG_RETURN_CSTRING(ascii);
