[
https://issues.apache.org/jira/browse/SPARK-58213?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
jiangxintong updated SPARK-58213:
---------------------------------
Description:
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
was:
## 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.
## Repro
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
## 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.
## Testing
- linear-regression.sql: x-constant and y-constant cases with inline VALUES,
both returning NULL
- Corresponding analyzer golden file regenerated
- SQLQueryTestSuite passes
> 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
>
> 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]
