Re: +/- Inf for float8's
Re: +/- Inf for float8's
От:
"Ross J. Reedstrom" <reedstrm@rice.edu>
Дата:
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. 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? 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., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
+/- Inf for float8's
От:
Tim Allen <tim@proximity.com.au>
Дата:
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 :). So, any suggestions as to how we can store +/- infinity as a valid float8 value in a database table? 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. Tim -- ----------------------------------------------- Tim Allen tim@proximity.com.au Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/
