[jira] [Created] (SPARK-55969) REGR_R2 returns wrong result

["Shaobo Guan (Jira)" <ji...@apache.org>]

Shaobo Guan created SPARK-55969:
-----------------------------------

             Summary: REGR_R2 returns wrong result
                 Key: SPARK-55969
                 URL: https://issues.apache.org/jira/browse/SPARK-55969
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 3.5.6
            Reporter: Shaobo Guan


REGR_R2 returns wrong results:

Row 6 (grp=same_x):
Column 'out': actual=1.0, expected=null
Row 7 (grp=same_y):
Column 'out': actual=null, expected=1.0

 

Repro
|CREATE|
| | | |OR REPLACE TEMPORARY VIEW t AS|
| | | |SELECT *|
| | | |FROM VALUES|
| | | |('all_null', NULL, NULL), ('all_null', NULL, NULL),|
| | | |('all_null', NULL, NULL),|
| | | |('single', 1.0, 2.0),|
| | | |('single_null', NULL, NULL),|
| | | |('same_x', 1.0, 5.0),|
| | | |('same_x', 2.0, 5.0),|
| | | |('same_x', 3.0, 5.0),|
| | | |('same_y', 5.0, 1.0),|
| | | |('same_y', 5.0, 2.0),|
| | | |('same_y', 5.0, 3.0) AS t(grp, y, x);

select grp, regr_r2(y, x) as out from t group by grp order by grp;
|

 

Notice y and x are passed in the flipped order as per [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#:~:text=Returns%20the%20average%20of%20the,regr_sxy(y%2C%20x)]

 

Why I believe this is wrong result?

Unfortunately, I gave all my stats101 knowledge to my college professor. So I googled it:
Summary of Behavioral Edge Cases 
||Scenario ||Data Condition||{{REGR_R2(y, x)}} Result||
|*Same*
𝑥{*}{*}|!wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==! 
VAR_POP(x)=0VAR_POP open paren x close paren equals 0VAR_POP(𝑥)=0|*NULL* (undefined slope)||
|*Same*
𝑦{*}{*}|!wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==! 
VAR_POP(y)=0VAR_POP open paren y close paren equals 0VAR_POP(𝑦)=0 AND
!wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==! 
VAR_POP(x)≠0VAR_POP open paren x close paren is not equal to 0VAR_POP(𝑥)≠0|*1* (perfect "fit" to horizontal line)||
|{*}Empty Set{*}|No rows or only NULL pairs|*NULL*|



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org