[
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]