Prevent corr() from returning the wrong results for negative correlation

Neil Conway · Neil Conway · commit bbf4fdc25380 · 2007-09-19T22:31:48.000Z
values. The previous coding essentially assumed that x = sqrt(x*x), which
does not hold for x &lt; 0.

Thanks to Jie Zhang at Greenplum and Gavin Sherry for reporting this
issue.
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.150 2007/06/05 21:31:06 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.151 2007/09/19 22:31:48 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2274,8 +2274,7 @@ float8_corr(PG_FUNCTION_ARGS)
 	if (numeratorX <= 0 || numeratorY <= 0)
 		PG_RETURN_NULL();
 
-	PG_RETURN_FLOAT8(sqrt((numeratorXY * numeratorXY) /
-						  (numeratorX * numeratorY)));
+	PG_RETURN_FLOAT8(numeratorXY / sqrt(numeratorX * numeratorY));
 }
 
 Datum

Original file line number	Diff line number	Diff line change
`@@ -8,7 +8,7 @@`
`8`	`8`	`*`
`9`	`9`	`*`
`10`	`10`	`* IDENTIFICATION`
`11`		`- * $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.150 2007/06/05 21:31:06 tgl Exp $`
	`11`	`+ * $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.151 2007/09/19 22:31:48 neilc Exp $`
`12`	`12`	`*`
`13`	`13`	`*-------------------------------------------------------------------------`
`14`	`14`	`*/`
`@@ -2274,8 +2274,7 @@ float8_corr(PG_FUNCTION_ARGS)`
`2274`	`2274`	`if (numeratorX <= 0 \|\| numeratorY <= 0)`
`2275`	`2275`	`PG_RETURN_NULL();`
`2276`	`2276`
`2277`		`- PG_RETURN_FLOAT8(sqrt((numeratorXY * numeratorXY) /`
`2278`		`- (numeratorX * numeratorY)));`
	`2277`	`+ PG_RETURN_FLOAT8(numeratorXY / sqrt(numeratorX * numeratorY));`
`2279`	`2278`	`}`
`2280`	`2279`
`2281`	`2280`	`Datum`