Skip to content

Variance and standard deviation return incorrect results for large nearby values #6044

Description

@rich7420

Describe the bug

Native variance and standard deviation lose accuracy for large, nearby DOUBLE values. For 10000000000000000 and 10000000000000002, var_pop returns 2 instead of Spark's 1. This occurs with and without GROUP BY.

Steps to reproduce

With Comet and native shuffle enabled, write one Parquet file to preserve input order:

import spark.implicits._
spark.conf.set("spark.sql.adaptive.enabled", "false")
val path = java.nio.file.Files.createTempDirectory("variance-repro").resolve("data").toString
Seq((0, 10000000000000000d), (0, 10000000000000002d))
  .toDF("g", "v").coalesce(1).write.parquet(path)
spark.read.parquet(path).createOrReplaceTempView("variance_repro")
SELECT var_pop(v), var_samp(v), stddev_pop(v), stddev_samp(v)
FROM variance_repro;

Repeat with GROUP BY g.

Expected behavior

Expression Spark Comet
var_pop 1.0 2.0
var_samp 2.0 4.0
stddev_pop 1.0 1.4142135623730951
stddev_samp 1.4142135623730951 2.0

Additional context

Reproduced on main b7f35b6ac, Spark 3.5.9 and 4.1.3, with native Partial and Final aggregates asserted.

welford::variance_update computes delta * (value - new_mean). Spark's CentralMomentAgg computes delta * (delta - delta / new_count). Rounding the large new_mean before subtraction makes Comet add 4 to M2 where Spark adds 2.

The shared helper also serves corr, whose Spark formula differs from CentralMomentAgg; a fix needs to preserve that distinction. Unlike the tolerance cases in #1375 and #392, this example has a 100% relative variance error.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

area:aggregationHash aggregates, aggregate expressionsbugSomething isn't workingcorrectnesspriority:criticalData corruption, silent wrong results, security issues

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions