[ 
https://issues.apache.org/jira/browse/SPARK-61530?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated SPARK-61530:
-----------------------------------
    Labels: pull-request-available sql  (was: sql)

> 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
>            Priority: Major
>              Labels: pull-request-available, sql
>
> {{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