[
https://issues.apache.org/jira/browse/SPARK-55969?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hyukjin Kwon resolved SPARK-55969.
----------------------------------
Fix Version/s: 4.2.0
3.5.9
4.1.2
4.0.3
Resolution: Fixed
Issue resolved by pull request 54901
[https://github.com/apache/spark/pull/54901]
> 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
> Priority: Major
> Labels: pull-request-available
> Fix For: 4.2.0, 3.5.9, 4.1.2, 4.0.3
>
>
> 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 stats 101 knowledge back 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: [email protected]
For additional commands, e-mail: [email protected]
