Thread

min() and NaN

Michael S. Tibbetts <mtibbetts@head-cfa.harvard.edu> — 2003-07-15T19:09:22Z

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'.

What's going here?  What should I expect the aggregate function min() to 
return in this case?  And why?

Any help is appreciated,
Mike


testdb=> \d min_with_nan
       Table "public.min_with_nan"
  Column |       Type       | Modifiers
--------+------------------+-----------
  col1   | double precision |

testdb=> select * from min_with_nan ;
  col1
-------
  3.141
  2.718
    NaN
     10
(4 rows)

testdb=> select min(col1) from min_with_nan ;
  min
-----
   10
(1 row)

testdb=> select min(col1) from min_with_nan where col1 != 'NaN';
   min
-------
  2.718
(1 row)

Re: min() and NaN

Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-20T04:40:22Z

"Michael S. Tibbetts" <mtibbetts@head-cfa.cfa.harvard.edu> 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 float8smaller,
which does this:

	result = ((arg1 > arg2) ? arg1 : arg2);

In most C implementations, any comparison involving a NaN will return
"false".  So when we hit the NaN, we have arg1 = min so far, arg2 = NaN,
comparison yields false, result is NaN.  On the next row, we have
arg1 = NaN, arg2 = next value, comparison yields false, result is next
value; and away it goes.

We could probably make it work the way you want with explicit tests for
NaN in float8smaller, arranged to make sure that the result is not NaN
unless both inputs are NaN.  But I'm not entirely convinced that we
should make it work like that.  The other float8 comparison operators
are designed to treat NaN as larger than every other float8 value (so
that it has a well-defined position when sorting), and I'm inclined to
think that float8smaller and float8larger probably should behave
likewise.  (That actually is the same as what you want for MIN(), but
not for MAX() ...)

Comments anyone?

			regards, tom lane

Re: min() and NaN

Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-07-20T06:11:34Z

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 value from the
> subset of rows following the 'NaN'.

This appears to be a bug in the min (and presumably max) aggregate
function for floats.  AFAICT It should use the same logic as the < (or >)
comparison operator for the type (SQL92 6.5 GR2b iii I believe), but it's
just doing a < (or >) in C on the two arguments which isn't the same.

Changing it to follow those rules would give 2.718 for min and it looks
like NaN for max.

Re: min() and NaN

Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-07-20T06:26:28Z

On Sun, 20 Jul 2003, Tom Lane wrote:

> "Michael S. Tibbetts" <mtibbetts@head-cfa.cfa.harvard.edu> 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 float8smaller,
> which does this:
>
> 	result = ((arg1 > arg2) ? arg1 : arg2);
>
> In most C implementations, any comparison involving a NaN will return
> "false".  So when we hit the NaN, we have arg1 = min so far, arg2 = NaN,
> comparison yields false, result is NaN.  On the next row, we have
> arg1 = NaN, arg2 = next value, comparison yields false, result is next
> value; and away it goes.
>
> We could probably make it work the way you want with explicit tests for
> NaN in float8smaller, arranged to make sure that the result is not NaN
> unless both inputs are NaN.  But I'm not entirely convinced that we
> should make it work like that.  The other float8 comparison operators
> are designed to treat NaN as larger than every other float8 value (so
> that it has a well-defined position when sorting), and I'm inclined to
> think that float8smaller and float8larger probably should behave
> likewise.  (That actually is the same as what you want for MIN(), but
> not for MAX() ...)

The spec seems to say that min/max should work the same way as the
comparison operators by saying that it returns the maximum or minimum
value as determined by the comparison rules of the comparison predicate
section.  That'd seem to be asking for the second version.

Re: min() and NaN

Jean-Luc Lachance <jllachan@nsd.ca> — 2003-07-21T15:41:43Z

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" <mtibbetts@head-cfa.cfa.harvard.edu> 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 float8smaller,
> which does this:
> 
>         result = ((arg1 > arg2) ? arg1 : arg2);
> 
> In most C implementations, any comparison involving a NaN will return
> "false".  So when we hit the NaN, we have arg1 = min so far, arg2 = NaN,
> comparison yields false, result is NaN.  On the next row, we have
> arg1 = NaN, arg2 = next value, comparison yields false, result is next
> value; and away it goes.
> 
> We could probably make it work the way you want with explicit tests for
> NaN in float8smaller, arranged to make sure that the result is not NaN
> unless both inputs are NaN.  But I'm not entirely convinced that we
> should make it work like that.  The other float8 comparison operators
> are designed to treat NaN as larger than every other float8 value (so
> that it has a well-defined position when sorting), and I'm inclined to
> think that float8smaller and float8larger probably should behave
> likewise.  (That actually is the same as what you want for MIN(), but
> not for MAX() ...)
> 
> Comments anyone?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: min() and NaN

Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-21T15:43:19Z

Jean-Luc Lachance <jllachan@nsd.ca> 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

Re: min() and NaN

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T01:34:43Z

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:
> Jean-Luc Lachance <jllachan@nsd.ca> 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 broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: min() and NaN

Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-22T06:09:09Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a TODO?

It'll only take ten minutes to make it a DONE, once we figure out what
the behavior ought to be.  So far I think both Stephan and I argued that
MIN/MAX ought to treat NaN as larger than all ordinary values, for
consistency with the comparison operators.  That was not the behavior
Michael wanted, but I don't see that we have much choice given the
wording of the SQL spec.  Does anyone want to argue against that
definition?

			regards, tom lane

Re: min() and NaN

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T16:14:41Z

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 put the NULL
somewhere, so we ignore it.  Should that be the same for NaN?  I just
don't see how we can arbitrarly say it is greater/less than other
values.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Is this a TODO?
> 
> It'll only take ten minutes to make it a DONE, once we figure out what
> the behavior ought to be.  So far I think both Stephan and I argued that
> MIN/MAX ought to treat NaN as larger than all ordinary values, for
> consistency with the comparison operators.  That was not the behavior
> Michael wanted, but I don't see that we have much choice given the
> wording of the SQL spec.  Does anyone want to argue against that
> definition?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: min() and NaN

Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-07-22T18:32:36Z

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 end, but with aggregates, we aren't required to put the NULL
> somewhere, so we ignore it.  Should that be the same for NaN?  I just
> don't see how we can arbitrarly say it is greater/less than other
> values.

But we already do. When doing a less than/greater than comparison, 'NaN'
is considered greater than normal values which is different from NULL
which returns unknown for both.

Re: min() and NaN

Jean-Luc Lachance <jllachan@nsd.ca> — 2003-07-22T18:50:11Z

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
> > 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 put the NULL
> > somewhere, so we ignore it.  Should that be the same for NaN?  I just
> > don't see how we can arbitrarly say it is greater/less than other
> > values.
> 
> But we already do. When doing a less than/greater than comparison, 'NaN'
> is considered greater than normal values which is different from NULL
> which returns unknown for both.

Re: min() and NaN

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T18:59:19Z

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 somewhere, so we put
> > it at the end, but with aggregates, we aren't required to put the NULL
> > somewhere, so we ignore it.  Should that be the same for NaN?  I just
> > don't see how we can arbitrarly say it is greater/less than other
> > values.
> 
> But we already do. When doing a less than/greater than comparison, 'NaN'
> is considered greater than normal values which is different from NULL
> which returns unknown for both.

But maybe that logic is the same as ORDER BY, where we have to give it
some location in sorting order, while with aggregates we don't.

I am not strong on this, but just point it out.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: min() and NaN

Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-22T19:33:19Z

Bruce Momjian <pgman@candle.pha.pa.us> 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 to be consistent with the comparison operators (and
therefore with ORDER BY):

            iii) If MAX or MIN is specified, then the result is respec-
                 tively the maximum or minimum value in TXA. These results
                 are determined using the comparison rules specified in
                 Subclause 8.2, "<comparison predicate>".

NULL can be special, because it acts specially in comparisons anyway.
But NaN is just a value of the datatype.

I'd be willing to go against the spec if I thought that having
ignore-NaNs behavior was sufficiently important, but I don't think it's
important enough to disregard the spec...

			regards, tom lane

Re: min() and NaN

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T20:19:57Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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 to be consistent with the comparison operators (and
> therefore with ORDER BY):
> 
>             iii) If MAX or MIN is specified, then the result is respec-
>                  tively the maximum or minimum value in TXA. These results
>                  are determined using the comparison rules specified in
>                  Subclause 8.2, "<comparison predicate>".
> 
> NULL can be special, because it acts specially in comparisons anyway.
> But NaN is just a value of the datatype.
> 
> I'd be willing to go against the spec if I thought that having
> ignore-NaNs behavior was sufficiently important, but I don't think it's
> important enough to disregard the spec...

Yep.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: min() and NaN

Greg Stark <gsstark@mit.edu> — 2003-07-24T18:35:46Z

Tom Lane <tgl@sss.pgh.pa.us> 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 goodies
you actually need to make it useful?

If not it seems more useful to just use the handy unknown-value thing SQL
already has and turn NaN into a NULL. 

-- 
greg

Re: min() and NaN

Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-24T18:44:10Z

Greg Stark <gsstark@mit.edu> 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
dependent on the platform having proper IEEE support, but that's no
excuse to throw it away.

			regards, tom lane