From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael S. Tibbetts <mtibbetts@head-cfa.cfa.harvard.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: min() and NaN
Date: Sun, 20 Jul 2003 00:40:22 -0400
Message-ID: <19874.1058676022@sss.pgh.pa.us> (raw)
In-Reply-To: <3F145162.7010805@head-cfa.harvard.edu>
References: <3F145162.7010805@head-cfa.harvard.edu>
"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
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: tgl@sss.pgh.pa.us, mtibbetts@head-cfa.cfa.harvard.edu
Subject: Re: min() and NaN
In-Reply-To: <19874.1058676022@sss.pgh.pa.us>
* 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