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

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions