Re: +/- Inf for float8's
От | Ross J. Reedstrom |
---|---|
Тема | Re: +/- Inf for float8's |
Дата | |
Msg-id | 20000814133341.A8686@rice.edu обсуждение исходный текст |
Ответ на | +/- Inf for float8's (Tim Allen <tim@proximity.com.au>) |
Список | 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
Вложения
В списке pgsql-general по дате отправления: