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.
Describe the bug
Native variance and standard deviation lose accuracy for large, nearby DOUBLE values. For
10000000000000000and10000000000000002,var_popreturns 2 instead of Spark's 1. This occurs with and withoutGROUP BY.Steps to reproduce
With Comet and native shuffle enabled, write one Parquet file to preserve input order:
Repeat with
GROUP BY g.Expected behavior
Additional context
Reproduced on main
b7f35b6ac, Spark 3.5.9 and 4.1.3, with native Partial and Final aggregates asserted.welford::variance_updatecomputesdelta * (value - new_mean). Spark'sCentralMomentAggcomputesdelta * (delta - delta / new_count). Rounding the largenew_meanbefore subtraction makes Comet add 4 to M2 where Spark adds 2.The shared helper also serves
corr, whose Spark formula differs fromCentralMomentAgg; a fix needs to preserve that distinction. Unlike the tolerance cases in #1375 and #392, this example has a 100% relative variance error.