From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael S. Tibbetts <mtibbetts@head-cfa.cfa.harvard.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: min() and NaN
Date: Mon, 21 Jul 2003 11:41:43 -0400
Message-ID: <3F1C09B7.3990752@nsd.ca> (raw)
References: <3F145162.7010805@head-cfa.harvard.edu>
<19874.1058676022@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca, tgl@sss.pgh.pa.us, mtibbetts@head-cfa.cfa.harvard.edu
Subject: Re: min() and NaN
In-Reply-To: <3F1C09B7.3990752@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox