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]

Reply via email to