Re: regr_slope returning NULL

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Steve Baldwin <steve(dot)baldwin(at)gmail(dot)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.
>
> regards, tom lane
>