Thread

BUG #19340: Wrong result from CORR() function

PG Bug reporting form <noreply@postgresql.org> — 2025-12-02T07:57:35Z

The following bug has been logged on the website:

Bug reference:      19340
Logged by:          Oleg Ivanov
Email address:      o15611@gmail.com
PostgreSQL version: 18.1
Operating system:   all
Description:        

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,24) ;
 corr
------
     
(1 row)

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,25) ;
        corr
---------------------
 -0.1877294297321991
(1 row)

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,31) ;
         corr
----------------------
 -0.03366532289960463
(1 row)

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,32) ;
         corr
----------------------
 0.037939234274452456
(1 row)

Another example:

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,36) ;
 corr
------
     
(1 row)

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,37) ;
        corr
---------------------
 0.37167954745944803
(1 row)

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,113) ;
         corr
----------------------
 0.022884787550167176
(1 row)

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,114) ;
         corr
-----------------------
 -0.004981175111303341
(1 row)

In the Oracle Database:
SQL> select corr( 0.09 , 0.09000001 ) FROM (select rownum as id from dual
connect by level <=330);

CORR(0.09,0.09000001)
---------------------

If argument is the constant, function CORR() must give a 0 or NaN.
Consequences of this bug: statistic functions are used to make business
descision. Wrong and completely different results can lead to make mistakes.