Thanks Tom,
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?
This is from my local install:
log=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit
Cheers,
Steve
On Sun, Mar 24, 2019 at 4:34 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Steve Baldwin <steve.baldwin@xxxxxxxxx> 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.
regards, tom lane
