[
https://issues.apache.org/jira/browse/SPARK-58213?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
jiangxintong updated SPARK-58213:
---------------------------------
Description:
## 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
>
> ## 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
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
