Message-ID: <3F1C09B7.3990752@nsd.ca>
Date: Mon, 21 Jul 2003 11:41:43 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Michael S. Tibbetts" <mtibbetts@head-cfa.cfa.harvard.edu>,
	pgsql-sql@postgresql.org
Subject: Re: min() and NaN
References: <3F145162.7010805@head-cfa.harvard.edu>
	<19874.1058676022@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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