BUG #15758: regr_* return wrong answers for some valid data

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: gumblex(at)aosc(dot)io
Subject: BUG #15758: regr_* return wrong answers for some valid data
Date: 2019-04-16 07:54:57
Message-ID: 15758-a5122fa4ae3e4176@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15758
Logged by: Dingyuan Wang
Email address: gumblex(at)aosc(dot)io
PostgreSQL version: 11.2
Operating system: Debian
Description:

This can reproduce on at least version 10 or 11, on Debian amd64.
Given the following data:

CREATE TABLE testdata (
x double precision,
t1 double precision,
t2 double precision
);
INSERT INTO testdata VALUES (79.7439999999999998, 1506705739.38499999,
1506705817.38100004);
INSERT INTO testdata VALUES (123.903999999999996, 1506705766.89499998,
1506705824.0079999);
INSERT INTO testdata VALUES (87.2960099999999954, 1506705746.29299998,
1506705818.24300003);
INSERT INTO testdata VALUES (116.352000000000004, 1506705761.87299991,
1506705823.05500007);
INSERT INTO testdata VALUES (67.7120100000000065, 1506705734.74300003,
1506705815.5539999);
INSERT INTO testdata VALUES (72.9600099999999969, 1506705735.35100007,
1506705815.6500001);
INSERT INTO testdata VALUES (101.632000000000005, 1506705756.25999999,
1506705820.70600009);
INSERT INTO testdata VALUES (108.927999999999997, 1506705761.30699992,
1506705821.59500003);
INSERT INTO testdata VALUES (94.0799999999999983, 1506705747.37199998,
1506705819.82999992);

This select:

SELECT
regr_slope(x, t1) a1, regr_intercept(x, t1) b1, regr_r2(x, t1) r1,
regr_slope(x, t2) a2, regr_intercept(x, t2) b2, regr_r2(x, t2) r2,
regr_slope(t1, x) a3, regr_intercept(t1, x) b3, regr_r2(t1, x) r3,
regr_slope(t2, x) a4, regr_intercept(t2, x) b4, regr_r2(t2, x) r4
FROM testdata;

Outputs:

null | null | null |
0.131221234798431 | -197711616 | 0.020541283900245 |
0.613632754607929 | 1506705691.81008 | 1 |
0.156539327890021 | 1506705804.72837 | 0.020541283900245

Some numbers are obviously wrong. Using Python's Decimal arithmetic, the
reference answer should be:

1.5950991071, -2403344901.6928107374, 0.9788051681
6.3409218262, -9553903722.1136632550, 0.9926034164
0.6136328230, 1506705691.8100808484, 0.9788051681
0.1565392925, 1506705804.7283713567, 0.9926034164

I understand that there may be some floating point overflows/underflows, but
the major spreadsheets can calculate this well (to some precision). I doubt
other regr_* functions have similar problems.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Suresh Thelkar 2019-04-16 11:03:43 Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, select query does not return the expected results.
Previous Message David G. Johnston 2019-04-16 06:31:35 Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, select query does not return the expected results.