Incorrect results from corr()

From: Neil Conway <neilc(at)samurai(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Cc: Gavin Sherry <swm(at)alcove(dot)com(dot)au>
Subject: Incorrect results from corr()
Date: 2007-09-19 01:27:33
Message-ID: 1190165253.7232.30.camel@dell.linuxdev.us.dell.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-patches

The builtin corr() aggregate doesn't produce the correct results in some
circumstances. Per the SQL spec, corr(x, y) is defined as equivalent to
covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y)).

postgres=# create table t1 (x float8, y float8);
CREATE TABLE
postgres=# copy t1 from stdin with csv;
0.940839937888086,0.539830380585045
0.84795232815668,0.396385048050433
0.601479615084827,0.899995123967528
0.785623408854008,0.302559469360858
0.829138438683003,0.0211085784249008
0.926528611686081,0.315794581547379
0.25934984581545,0.609216409735382
0.976522764191031,0.877208305988461
\.
postgres=# select corr(x, y) from t1;
corr
-------------------
0.214150892978763
(1 row)

postgres=# select covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y)) from
t1;
?column?
--------------------
-0.214150892978763
(1 row)

With the attached patch, we get the expected results:

postgres=# select corr(x, y) from t1;
corr
--------------------
-0.214150892978763
(1 row)

Credit: Jie Zhang at Greenplum and Gavin Sherry for reporting the issue.

Barring any objections, I'll apply this to HEAD and 8.2 later tonight or
tomorrow.

-Neil

Attachment Content-Type Size
corr-fix-1.patch text/x-patch 773 bytes

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message Andrew Dunstan 2007-09-19 02:31:10 Re: WIP - MSVC build script replacements
Previous Message Andrew Dunstan 2007-09-18 18:04:32 Re: invalidly encoded strings