[
https://issues.apache.org/jira/browse/SPARK-58213?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-58213:
-----------------------------------
Labels: pull-request-available (was: )
> corr should return NULL when a column has zero variance
> -------------------------------------------------------
>
> Key: SPARK-58213
> URL: https://issues.apache.org/jira/browse/SPARK-58213
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: jiangxintong
> Priority: Major
> Labels: pull-request-available
>
> h2. Background
> {{corr(x, y)}} computes Pearson correlation as {{ck / sqrt(xMk * yMk)}}. When
> one of the columns is constant, the corresponding variance term ({{xMk}} or
> {{yMk}} in Welford's incremental algorithm) is exactly {{0.0}}, making the
> denominator zero. In ANSI mode this throws {{DIVIDE_BY_ZERO}}; in non-ANSI
> mode {{ck / 0.0}} returns {{NaN}} or {{±Infinity}}. Neither is a meaningful
> correlation for a constant column.
> The sibling function {{regr_r2}} (same {{PearsonCorrelation}} base class)
> already guards against {{yMk === 0.0}} and {{xMk === 0.0}} in its
> {{evaluateExpression}}. The Spark 3.1 migration guide lists {{corr}} among
> functions that should return {{NULL}} instead of {{NaN}} on divide-by-zero,
> but the guard was never added.
> h2. Repro
> {code:sql}
> SET spark.sql.ansi.enabled = true;
> SELECT corr(CAST(x AS DOUBLE), CAST(y AS DOUBLE))
> FROM VALUES (1, 1), (1, 2), (1, 3) AS t(x, y);
> -- Before fix: DIVIDE_BY_ZERO
> -- After fix: NULL
> {code}
> h2. Fix
> Add a guard {{xMk === 0.0 || yMk === 0.0}} in the {{evaluateExpression}} of
> {{Corr}}, branching to the existing {{divideByZeroEvalResult}}. This is the
> same mechanism already used by {{n === 1.0}} (single data point) and shared
> with {{regr_r2}}. The guard prevents the division from ever being evaluated
> when the denominator is zero, so {{DIVIDE_BY_ZERO}} is never thrown. Output
> is controlled by {{divideByZeroEvalResult}}:
> * *Default* (both ANSI and non-ANSI): {{NULL}}, matching the Spark 3.1
> migration guide and {{regr_r2}}'s behavior
> * *Legacy* ({{spark.sql.legacyStatisticalAggregate=true}}): {{NaN}},
> preserving the pre-3.1 behavior
> The Welford accumulator guarantees {{xMk}} and {{yMk}} are exact zero for
> constant input (deviation from mean is always 0), so the {{=== 0.0}}
> comparison is safe.
> h2. Testing
> * {{linear-regression.sql}}: x-constant and y-constant cases with inline
> {{VALUES}}, both returning {{NULL}}
> * Corresponding analyzer golden file regenerated
> * {{SQLQueryTestSuite}} passes
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
