I've tried this on 11.2 (OS X 10.14.3, installed locally) and 10.6 (AWS RDS) instances with identical results. The values you show are identical to those returned by Oracle so that's great but why am I seeing different results?
Steve Baldwin <steve.baldwin@gmail.com> writes: > Consider the following: > ... > log=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id; > id | trend > ------+---------------------- > c742 | > 317e | > 5fe6 | 5.78750952760444e-06 > 3441 | > (4 rows)
Hm, I get
regression=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id; id | trend ------+----------------------- c742 | 19.607858781290517 317e | -1.0838511987808963 5fe6 | 5.787509483586743e-06 3441 | -3.828395463097356 (4 rows)
What platform are you doing this on, and what exactly is the PG version?
> If pg is correctly returning NULL, I'd be interested to understand the > circumstances under which this can occur.
The source code shows two cases in which NULL would be returned:
/* if N is 0 we should return NULL */ if (N < 1.0) PG_RETURN_NULL();
/* per spec, return NULL for a vertical line */ if (Sxx == 0) PG_RETURN_NULL();
Maybe the cases you're looking at are sufficiently numerically ill-conditioned that you could get Sxx == 0 depending on platform- specific roundoff error, but it seems fishy.