Обсуждение: BUG #14008: corr, covar_pop function returns different values on the same dataset
BUG #14008: corr, covar_pop function returns different values on the same dataset
От
Stepya@ukr.net
Дата:
The following bug has been logged on the website: Bug reference: 14008 Logged by: Stepan Email address: Stepya@ukr.net PostgreSQL version: 9.5.1 Operating system: RedHat Linux 64-bit Description: Aggregation functions line corr, covar_pop returns different values depending on sorting order of the dataset. The issue is reproducible on PostgreSQL version 9.5.0 and 9.5.1. See script how to reproduce it. drop table if exists t ; drop table if exists t2 ; create table T (pk bigint, fk bigint); insert into T select generate_series as PK, floor(random()*(567890-10)+10) as FK from generate_series(551475,551475+1000000); -- Create table with the same data but different sorting order create table T2 as select * from T order by 1 desc; select count(1), stddev(PK), stddev(FK), covar_pop(pk, fk) from T UNION ALL select count(1), stddev(PK), stddev(FK), covar_pop(pk, fk) from T2; -- Last column only returns different values!!!! -- To be sure both tables contain the same data. select pk, fk from T except select pk, fk from T2; --Returns 0 rows select pk, fk from T2 except select pk, fk from T; --Returns 0 rows Stepan.
Stepya@ukr.net writes: > Aggregation functions line corr, covar_pop returns different values > depending on sorting order of the dataset. AFAICS, that's just rounding error, which is inevitable when working with floating-point data. regards, tom lane