[jira] [Updated] (SPARK-58213) corr should return NULL when a column has zero variance

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

     [ 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org