Yaoxuan Wu created SPARK-60595:
----------------------------------
Summary: percentile_disc returns the wrong value when n * p is not
exact in floating point (e.g. p = 0.28 over 25 rows)
Key: SPARK-60595
URL: https://issues.apache.org/jira/browse/SPARK-60595
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.1.1, 4.2.0, 3.5.8, 4.0.0, 5.0.0
Reporter: Yaoxuan Wu
{code:sql}
SELECT percentile_disc(0.28) WITHIN GROUP (ORDER BY id) AS asc_028,
percentile_disc(0.28) WITHIN GROUP (ORDER BY id DESC) AS desc_028
FROM range(1, 26);
-- expected: 7.0, 19.0
-- actual: 8.0, 18.0
{code}
{{percentile_disc(p)}} is the first value whose {{cume_dist}} is >= p. Over
1..25, {{cume_dist}} of 7 is 7/25 = 0.28, so the result is 7 (Spark's own
{{cume_dist()}} agrees). The same for {{percentile_disc(0.56)}} over 50 rows
(29 instead of 28) and other (n, p) pairs such as (100, 0.07), (25|50|75|100,
0.28), (75, 0.68).
Cause: {{PercentileDisc.getPercentile}}
({{expressions/aggregate/percentiles.scala}}) computes {{val position =
accumulatedCounts.last._2 * percentile}} in double and then {{position.ceil}}.
25 * 0.28 = 7.000000000000001 in binary floating point, so the ceiling is 8.
{{cume_dist}} divides ({{count / n}}) instead, and 7 / 25 rounds to the same
double as 0.28, so the two disagree exactly at these boundaries. Comparing
{{counts(i) / n >= p}} as {{cume_dist}} does would fix it.
Reproduces on 3.5.8, 4.0.0, 4.1.1 and 4.2.0 (master has the same code).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
