NaN with STDDEV() with mixed ::float4 ::float8 values

Hello-

While computing standard deviation on a float8 column, I noticed that
sometimes STDDEV returned "NaN". I've tracked down the cause and
thought I'd show everyone. This may or may not be a bug, I don't know.
Notice that the second insert statement is putting a ::float4 into a
float8 column.

The reason for the NaN is probably due to some precision issue between
float4 and float8 which is causing the "variance" of the mixed ::float4
::float8 column to be negative.

template1=# create table test (a float4, b float8);
CREATE
template1=# insert into test (a, b) values (1/11::float4, 1/11::float8);
INSERT 62077086 1
template1=# insert into test (a, b) values (1/11::float4, 1/11::float4);
INSERT 62077087 1
template1=# select * from test;
a | b
-----------+--------------------
0.0909091 | 0.0909090909090909
0.0909091 | 0.0909090909090909
(2 rows)

template1=# select stddev(a), stddev(b) from test;
stddev | stddev
--------+--------
0 | NaN
(1 row)

template1=# select stddev(a::float4), stddev(b::float8) from test;
stddev | stddev
--------+--------
0 | NaN
(1 row)

By explicitly casting column b to ::float4, the NaN disappears.

template1=# select stddev(a::float4), stddev(b::float4) from test;
stddev | stddev
--------+--------
0 | 0
(1 row)

The variance of the columns shows the problem (standard deviation is the
sqrt of variance):

template1=# select variance(a), variance(b) from test;
variance | variance
----------+-----------------------
0 | -4.59091857411831e-19
(1 row)

template1=# select variance(a::float4), variance(b::float4) from test;
variance | variance
----------+----------
0 | 0
(1 row)

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Moléculas Naturais, Rio de Janeiro, Brasil
email: lapham(at)extracta(dot)com(dot)br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------