BUG #15758: regr_* return wrong answers for some valid data
От | PG Bug reporting form |
---|---|
Тема | BUG #15758: regr_* return wrong answers for some valid data |
Дата | |
Msg-id | 15758-a5122fa4ae3e4176@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15758: regr_* return wrong answers for some valid data
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15758 Logged by: Dingyuan Wang Email address: gumblex@aosc.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.
В списке pgsql-bugs по дате отправления: