Databricks/Spark: PERCENTILE_CONT/DISC … WITHIN GROUP is rewritten to PERCENTILE_APPROX, dropping the sort direction

Open
#9,306 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
66/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
python, spark, sql
Domain
databases

Research direction

Start in sqlglot/dialects/spark.py, where Spark2Generator maps exp.WithinGroup through transforms.remove_within_group_for_percentiles; confirm that SparkGenerator and the Databricks generator inherit this mapping. Run the reproduction from the issue to see the approximation and lost DESC. The fix should scope the rewrite to Spark 2 only and preserve WITHIN GROUP for Spark 3+/Databricks, with tests in the Spark and Databricks dialect test files covering both PERCENTILE_CONT and PERCENTILE_DISC and sort direction.

Written by the indexing model from the issue text.

Description

When writing the databricks or spark dialects, an exact PERCENTILE_CONT or PERCENTILE_DISC with WITHIN GROUP is rewritten to PERCENTILE_APPROX(col, p). This has two effects:

  1. An exact percentile silently becomes an approximation.
  2. ORDER BY … DESC is dropped, so the result is a different value, not just a less precise one.

The same rewrite happens when the source dialect is Databricks, so Databricks to Databricks changes the query's meaning.

Reproduction (sqlglot 30.24.0)

import sqlglot

for sql in [
"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) FROM t",
"SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY x DESC) FROM t",
"SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY x) FROM t",
]:
for dialect in ("postgres", "duckdb", "snowflake", "databricks", "spark"):
print(dialect.ljust(11), sqlglot.transpile(sql, read="postgres", write=dialect)[0])

Databricks to Databricks

print(sqlglot.transpile(
"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x DESC) FROM t",
read="databricks", write="databricks")[0])

Actual output (second query shown; the other two behave the same way)

postgres SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY x DESC) FROM t
duckdb SELECT QUANTILE_CONT(x, 0.25 ORDER BY x DESC NULLS FIRST) FROM t
snowflake SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY x DESC) FROM t
databricks SELECT PERCENTILE_APPROX(x, 0.25) FROM t
spark SELECT PERCENTILE_APPROX(x, 0.25) FROM t

The Databricks to Databricks case prints SELECT PERCENTILE_APPROX(x, 0.5) FROM t.

Expected

Databricks supports the exact form, so the output should keep it, for example:

SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY x DESC) FROM t

Cause

Spark2Generator has:

exp.WithinGroup: transforms.preprocess([transforms.remove_within_group_for_percentiles]),

SparkGenerator (the Spark 3+ dialect) and DatabricksGenerator inherit it without overriding it. remove_within_group_for_percentiles replaces the percentile with exp.ApproxQuantile built from the sort column and the fraction, and discards the Order node, including its direction. That seems right for Spark 2, which lacks the WITHIN GROUP form, but it also applies to Spark 3+ and Databricks, which have it as of Spark 3.3.0

Dominant language
Python
Stars
9.8k
Forks
1.4k
Avg merge
10h 52m
Merged PRs (30d)
303

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from tobymao/sqlglot

All issues in tobymao/sqlglot

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.