Yibo Dong created SPARK-61530:
---------------------------------
Summary: percentile_approx returns incorrect endpoints when
merging NaN-containing partitions
Key: SPARK-61530
URL: https://issues.apache.org/jira/browse/SPARK-61530
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.3.0
Environment: {code}
Spark: 4.3.0
Commit: 98bc0212c94705434471627068f41e374ccab143
Java: OpenJDK 17.0.17
OS: macOS 15.7.4
Master: local[2]
{code}
Reporter: Yibo Dong
{{percentile_approx}} returns incorrect minimum and maximum percentiles when
aggregating partitions containing NaN and Infinity values.
For the six input values {{-Infinity}}, {{-1}}, {{0}}, {{1}}, {{Infinity}}, and
{{NaN}}:
With seven input partitions, the 0th percentile returns NaN instead of
-Infinity.
With three input partitions, the 100th percentile returns Infinity instead of
NaN.
Both results disagree with Spark SQL's ordering semantics and the corresponding
exact {{MIN}} and {{MAX}} aggregates.
h2. Steps to Reproduce
Save the following SQL as {{tmp.sql}}:
{code}
WITH input AS (
SELECT /*+ REPARTITION(7) */ x
FROM VALUES
(CAST('-Infinity' AS DOUBLE)), (-1.0D), (0.0D),
(1.0D), (CAST('Infinity' AS DOUBLE)),
(CAST('NaN' AS DOUBLE)) AS t(x)
)
SELECT percentile_approx(x, 0.0D, 1000000) AS approximate_min,
min(x) AS exact_min
FROM input;
WITH input AS (
SELECT /*+ REPARTITION(3) */ x
FROM VALUES
(CAST('-Infinity' AS DOUBLE)), (-1.0D), (0.0D),
(1.0D), (CAST('Infinity' AS DOUBLE)),
(CAST('NaN' AS DOUBLE)) AS t(x)
)
SELECT percentile_approx(x, 1.0D, 1000000) AS approximate_max,
max(x) AS exact_max
FROM input;
{code}
Execute:
{code}
spark-sql --master 'local[2]'
--conf spark.ui.enabled=false -f tmp.sql
{code}
h2. Expected Result
Spark SQL orders these values as follows:
{code}
-Infinity < -1 < 0 < 1 < Infinity < NaN
{code}
Accordingly, the expected results are:
{code}
Query 1:
approximate_min | exact_min
----------------+----------
-Infinity | -Infinity
Query 2:
approximate_max | exact_max
----------------+----------
NaN | NaN
{code}
These queries test the 0th and 100th percentile, with an accuracy setting of
1000000 for only six input rows.
Spark's [NaN
semantics|https://spark.apache.org/docs/4.3.0/sql-ref-datatypes.html#nan-semantics]
explicitly place NaN above all numeric values.
h2. Actual Result
{code}
Query 1:
approximate_min | exact_min
----------------+----------
NaN | -Infinity
Query 2:
approximate_max | exact_max
----------------+----------
Infinity | NaN
{code}
Both approximate percentile results disagree with the exact endpoints computed
over the same input rows.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]