Re: [SQL] min() and NaN

2003-07-24 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: Does postgres intend to support all the different types of NaN? Does you intend to have +Inf and -Inf and underflow detection and all the other goodies you actually need to make it useful? We have some of that; it needs work, and it's always going to be

Re: [SQL] min() and NaN

2003-07-24 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: NULL can be special, because it acts specially in comparisons anyway. But NaN is just a value of the datatype. Does postgres intend to support all the different types of NaN? Does you intend to have +Inf and -Inf and underflow detection and all the other

Re: [SQL] min() and NaN

2003-07-22 Thread Bruce Momjian
Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. When doing ORDER BY, we have to put the NULL value somewhere, so we put it at the end, but with aggregates, we aren't required to

Re: [SQL] min() and NaN

2003-07-22 Thread Stephan Szabo
On Tue, 22 Jul 2003, Bruce Momjian wrote: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. When doing ORDER BY, we have to put the NULL value somewhere, so we put it at the

Re: [SQL] min() and NaN

2003-07-22 Thread Jean-Luc Lachance
Hey! here is a (stupid maybe) idea. Why not disallow 'NaN' for a float? JLL Stephan Szabo wrote: On Tue, 22 Jul 2003, Bruce Momjian wrote: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems

Re: [SQL] min() and NaN

2003-07-22 Thread Bruce Momjian
Stephan Szabo wrote: On Tue, 22 Jul 2003, Bruce Momjian wrote: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. When doing ORDER BY, we have to put the NULL value

Re: [SQL] min() and NaN

2003-07-22 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. Good idea, but I don't think we can get away with it. The spec says that MAX/MIN have

Re: [SQL] min() and NaN

2003-07-22 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. Good idea, but I don't think we can get away with it. The spec

Re: [SQL] min() and NaN

2003-07-22 Thread Michael Tibbetts
Treating NaN's as larger(or smaller) than all ordinary values seems a fine way to go. It avoids the situation where you request MIN and get an ordinary value which is greater than the minimum ordinary value in the table. If MIN(or MAX given the ordering you're suggesting) returns NaN, the

Re: [SQL] min() and NaN

2003-07-21 Thread Jean-Luc Lachance
If a compare with NaN is always false, how about rewriting it as: result = ((arg1 arg2) ? arg2 : arg1). Or better yet, swap arg1 and arg2 when calling float8smaller. Use flaost8smaller( current_min, value). JLL Tom Lane wrote: Michael S. Tibbetts [EMAIL PROTECTED] writes: I'd expect the

Re: [SQL] min() and NaN

2003-07-21 Thread Tom Lane
Jean-Luc Lachance [EMAIL PROTECTED] writes: If a compare with NaN is always false, how about rewriting it as: result = ((arg1 arg2) ? arg2 : arg1). That just changes the failure mode. regards, tom lane ---(end of

Re: [SQL] min() and NaN

2003-07-21 Thread Bruce Momjian
Is this a TODO? --- Tom Lane wrote: Jean-Luc Lachance [EMAIL PROTECTED] writes: If a compare with NaN is always false, how about rewriting it as: result = ((arg1 arg2) ? arg2 : arg1). That just changes the failure

Re: [SQL] min() and NaN

2003-07-20 Thread Tom Lane
Michael S. Tibbetts [EMAIL PROTECTED] writes: I'd expect the aggregate function min() to return the minimum, valid numeric value. Instead, it seems to return the minimum value from the subset of rows following the 'NaN'. Not real surprising given than min() is implemented with

Re: [SQL] min() and NaN

2003-07-20 Thread Stephan Szabo
On Sun, 20 Jul 2003, Tom Lane wrote: Michael S. Tibbetts [EMAIL PROTECTED] writes: I'd expect the aggregate function min() to return the minimum, valid numeric value. Instead, it seems to return the minimum value from the subset of rows following the 'NaN'. Not real surprising given

Re: [SQL] min() and NaN

2003-07-20 Thread Stephan Szabo
On Tue, 15 Jul 2003, Michael S. Tibbetts wrote: Hi, I have a table containing a double precision column. That column contains at least one judiciously placed NaN. I'd expect the aggregate function min() to return the minimum, valid numeric value. Instead, it seems to return the minimum

[SQL] min() and NaN

2003-07-19 Thread Michael S. Tibbetts
Hi, I have a table containing a double precision column. That column contains at least one judiciously placed NaN. I'd expect the aggregate function min() to return the minimum, valid numeric value. Instead, it seems to return the minimum value from the subset of rows following the 'NaN'.