The following bug has been logged on the website:
Bug reference: 19615
Logged by: Junwen An
Email address: feasiblechart@gmail.com
PostgreSQL version: 19beta2
Operating system: Linux Ubuntu
Description:
I found `COVAR_POP`/`REGR_SXY` returns 0.0 instead of NaN when one arg is
constant and the other has Inf (not first), which might be unexpected. I
could reproduce it on 19beta1, 19beta2, and 20devel, but not on 18.4.
Minimal repro:
CREATE TABLE t (y double precision);
INSERT INTO t VALUES (3), ('Infinity'), (4);
SELECT COVAR_POP(0::float8, y) FROM t;
-- Expected 1 row: NaN
-- Actual: 0.0
SELECT COVAR_POP(y, 0::float8) FROM t;
-- Expected 1 row: NaN
-- Actual: 0.0
SELECT COVAR_SAMP(0::float8, y) FROM t;
-- Expected 1 row: NaN
-- Actual: 0.0
SELECT REGR_SXY(0::float8, y) FROM t;
-- Expected 1 row: NaN
-- Actual: 0.0
Did some further experiments, and it seems this behavior also depends on the
position of 'Inf'
WITH t(ord, y) AS (
VALUES
(1, 'Infinity'::float8),
(2, 3::float8),
(3, 4::float8)
)
SELECT
covar_pop(0::float8, y ORDER BY ord) AS inf_first,
covar_pop(0::float8, y ORDER BY ord DESC) AS inf_last
FROM t;
inf_first | inf_last
-----------+----------
NaN | 0
