X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.225.181])
	by svr1.postgresql.org (Postfix) with ESMTP id 3F3CCCC476
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 15 Jul 2003 19:09:05 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new,
	port 10024) with ESMTP id 27239-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 15 Jul 2003 19:09:22 +0000 (GMT)
Received: from head-cfa.cfa.harvard.edu (head-cfa.cfa.harvard.edu
	[131.142.41.8])
	by svr1.postgresql.org (Postfix) with ESMTP id E5CB5CC471
	for <pgsql-sql@postgresql.org>; Tue, 15 Jul 2003 16:08:53 -0300 (ADT)
Received: from head-cfa.harvard.edu (artemis [131.142.42.189])
	by head-cfa.cfa.harvard.edu (8.12.8p1/8.12.8) with ESMTP id
	h6FJ9Mxj012372
	for <pgsql-sql@postgresql.org>; Tue, 15 Jul 2003 15:09:23 -0400 (EDT)
Message-ID: <3F145162.7010805@head-cfa.harvard.edu>
Date: Tue, 15 Jul 2003 15:09:22 -0400
From: "Michael S. Tibbetts" <mtibbetts@head-cfa.harvard.edu>
Organization: Smithsonian Astrophysical Observatory
User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.2b) Gecko/20021024
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: min() and NaN
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/271
X-Sequence-Number: 14385

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)