>Thomas -
>A general design question. There seems to be a good reason to
>allow +/-Inf in float8 columns: Tim Allen has an need for them, for
>example. That's pretty straight forward, they seem to act properly if
>the underlying float libs handle them.

Thanks for pursuing this, Ross. I shall look forward to not having to use
a workaround in future versions.

>I'm not convinced NaN gives us anything useful, especially given how
>badly it breaks sorting. I've been digging into that code a little,
>and it's not going to be pretty. It strikes me as wrong to embed type
>specific info into the generic sorting routines.

Actually, we also have a use for NaN. The main thing we're using this for
is to store "fields", ie general descriptions of particular items of
metadata that occur in our application. Each field can have a validity
condition (ie min <= X <= max), and for open ranges we find the easiest
way to handle that without needing any extra bool flags or whatever is
just to set the minimum value to -infinity and/or the max to +infinity.

Our fields also have a default value, used in case the user didn't
actually enter anything. However, we want to be able to support something
like a NULL, so that if the user doesn't enter anything then in some cases
that means "there is no value". These values get passed around inside our
applications in various ways, in subsystems that don't have any direct
communication with the database, so using a database NULL doesn't do the
job. An NaN, however, is perfect for the job, because you can transmit
NaN's down sockets between processes, you can copy them around without
needing any special handling, and you can (currently) write them to and
read them from the database. So, for what it's worth, here's one vote for
keeping NaN's. As for sorting, we don't really care how they sort. Any
consistent behaviour will do for us.

Yes, there is a difference between an NaN and a NULL, but that difference
is not important in our application. We never do any serious arithmetic on
our float8 values, we just store them in the database and allow users to
view and edit the values.

>So, anyone have any ideas what NaN would be useful for? Especially given
>we have NULL available, which most (non DB) numeric applications don't.

It's this last point that makes NaN's useful; most non DB numeric
applications don't have a NULL, and NaN can make an adequate substitute.
One thing we could do, I suppose, is add code to our db interface layer to
translate NaN's to NULL's and vice versa. But if we don't have to, we'd be
happier :-).

>Ross

Tim

--
-----------------------------------------------
Tim Allen          tim@proximity.com.au
Proximity Pty Ltd  http://www.proximity.com.au/
  http://www4.tpg.com.au/users/rita_tim/


Wed, 16 Aug 2000 11:16:24 +1000 (EST)
Tim Allen <tim@proximity.com.au>
Re: Re: [GENERAL] +/- Inf for float8's
On Wed, Aug 16, 2000 at 11:16:24AM +1000, Tim Allen wrote:
> 
> Thanks for pursuing this, Ross. I shall look forward to not having to use
> a workaround in future versions.

See, Tim knows how to get work out of Open Source programmers. Flattery, not
flames ;-)

> 
> Actually, we also have a use for NaN. The main thing we're using this for
<snip>
> read them from the database. So, for what it's worth, here's one vote for
> keeping NaN's. As for sorting, we don't really care how they sort. Any
> consistent behaviour will do for us.
> 

Right. Currently, NaN's break sorting of everything else in the column. 
Not good. But Thomas mentioned a possible clever work around. I've got to
dig into the code a bit more to see if it'll work.

> Yes, there is a difference between an NaN and a NULL, but that difference
> is not important in our application. We never do any serious arithmetic on
> our float8 values, we just store them in the database and allow users to
> view and edit the values.
> 
> >So, anyone have any ideas what NaN would be useful for? Especially given
> >we have NULL available, which most (non DB) numeric applications don't.
> 
> It's this last point that makes NaN's useful; most non DB numeric
> applications don't have a NULL, and NaN can make an adequate substitute.
> One thing we could do, I suppose, is add code to our db interface layer to
> translate NaN's to NULL's and vice versa. But if we don't have to, we'd be
> happier :-).

Well, this is open source: all we need is one customer, if the idea
is sound. (Usually, that's the coder themselves, but not always. And
conversely, if it's a lousy idea, it doesn't matter howmany people
want it!) I had forgotten that the DB often interacts with non-DB
code. (What, you mean psql and hand typed queries isn't good enough for
your clients?) 'Course, I'm the type that's been known to code figures
directly in Postscript because the drawing package wouldn't do what I
wanted to.

I'll definitely look into this some more. If we can solve the sort
problem without to big a kludge, I think we might be able to let people
do serious math in the backend, let the non-finites fly!

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

Tue, 15 Aug 2000 21:26:54 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Re: [GENERAL] +/- Inf for float8's