[SPARK-32908] percentile_approx() returns incorrect results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3.4, 2.4.7, 3.0.1, 3.1.0
• Fix Version/s: 2.4.8, 3.0.2, 3.1.0
• Component/s: SQL
• Labels: correctness
• Assignee: Max Gekk
• Reporter: Max Gekk
• Created: 17/Sep/20 07:22
• Updated: 12/Dec/22 18:10
• Resolved: 18/Sep/20 01:50

Description

Read input data from the attached CSV file:

      val df = spark.read.option("header", "true")
        .option("inferSchema", "true")
        .csv("/Users/maximgekk/tmp/percentile_approx-input.csv")
        .repartition(1)
      df.createOrReplaceTempView(table)

Calculate the 0.77 percentile with accuracy 1e-05:

      spark.sql(
        s"""SELECT
           |  percentile_approx(tr_rat_resampling_score, 0.77, 100000)
           |FROM $table
           """.stripMargin).show

+------------------------------------------------------------------------+
|percentile_approx(tr_rat_resampling_score, CAST(0.77 AS DOUBLE), 100000)|
+------------------------------------------------------------------------+
|                                                                    1000|
+------------------------------------------------------------------------+

 The same for smaller accuracy 0.001:

+----------------------------------------------------------------------+
|percentile_approx(tr_rat_resampling_score, CAST(0.77 AS DOUBLE), 1000)|
+----------------------------------------------------------------------+
|                                                                    18|
+----------------------------------------------------------------------+

and better accuracy 1e-06:

+-------------------------------------------------------------------------+
|percentile_approx(tr_rat_resampling_score, CAST(0.77 AS DOUBLE), 1000000)|
+-------------------------------------------------------------------------+
|                                                                       17|
+-------------------------------------------------------------------------+

For the accuracy 1e-05, the result must be around 17-18 but not 1000.

Here is percentile calculation in Google Sheets for the same input:
https://docs.google.com/spreadsheets/d/1Y1i4Td6s9jZQ-bD4IRTESLXP3UxKpqJSXGtmx0Q5TA0/edit?usp=sharing

Issue Links

• is duplicated by: SPARK-30882 Inaccurate results with higher precision ApproximatePercentile results
• is duplicated by: SPARK-31430 Bug in the approximate quantile computation.
• is related to: SPARK-30882 Inaccurate results with higher precision ApproximatePercentile results
• links to: [Github] Pull Request #29784 (MaxGekk)
• links to: [Github] Pull Request #29784 (MaxGekk)
• links to: [Github] Pull Request #29786 (MaxGekk)
• links to: [Github] Pull Request #29786 (MaxGekk)