Databricks/Spark: PERCENTILE_CONT/DISC … WITHIN GROUP is rewritten to PERCENTILE_APPROX, dropping the sort direction
Maintainers usually reply within 1 day
Nobody has claimed this yet.
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 66/100
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:
- An exact percentile silently becomes an approximation.
- 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
- percentile_cont (https://docs.databricks.com/aws/en/sql/language-manual/functions/percentile_cont): percentile_cont(percentile) WITHIN GROUP (ORDER BY sortKey [ASC | DESC]), Databricks SQL and Runtime 10.4 LTS and above.
- percentile_disc (https://docs.databricks.com/aws/en/sql/language-manual/functions/percentile_disc): the same syntax, Runtime 11.3 LTS and above.
- approx_percentile (https://docs.databricks.com/aws/en/sql/language-manual/functions/approx_percentile) is documented as a separate, approximate function.
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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from tobymao/sqlglot
-
Difficulty 1/5 Under an hour Newbie friendliness 85/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
Maintainers usually reply within 1 day
-
Difficulty 3/5 1-2 days Newbie friendliness 65/100
Maintainers usually reply within 1 day
Similar issues
-
Difficulty 1/5 Under an hour Newbie friendliness 86/100
py-econometrics/pyfixest#2072 ·
Maintainers usually reply within 1 day
-
~planning docs
Difficulty 2/5 1-3 hours Newbie friendliness 64/100
Kromatic-Innovation/athenaeum#3084 ·
Maintainers usually reply within 1 day
-
Bosch Room Thermostat II number entities offer -500 to 3000 °C setpoint limits and a -32768 to 32767 °C outdoor temperaturePossibly taken @zigpy-review-bot claimed this today. Openbot: code audit
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
zigpy/zha-device-handlers#5769 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 Half a day Newbie friendliness 80/100
R0Y00/Xbox2Windows#254 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 85/100
Kludex/python-multipart#375 ·
Maintainers usually reply within 1 day