DuckDB generator: WITHIN GROUP percentile with a CAST fraction produces unbalanced parentheses
Maintainers usually reply within 1 day
Nobody has claimed this yet.
Assessment
- Difficulty
- 1/5
- Estimated time
- Under an hour
- Newbie friendliness
- 85/100
Research direction
Start in sqlglot/dialects/duckdb.py at DuckDBGenerator.withingroup_sql, where rstrip(")") strips the CAST's closing paren along with the function's. Reproduce with the snippet in the issue, confirm the literal-fraction case still works, then replace the strip with one that removes exactly one trailing paren. Add cases for percentile_cont, percentile_disc and CAST(... AS DOUBLE PRECISION) to tests/dialects/test_duckdb.py; done means the transpiled output keeps the CAST balanced and parses in DuckDB.
Written by the indexing model from the issue text.
Description
Transpiling an ordered-set percentile whose fraction ends in ) (for example a CAST) to DuckDB produces invalid SQL.
import sqlglot
sql = "SELECT percentile_cont(cast(0.9 as float)) WITHIN GROUP (ORDER BY x ASC) AS p FROM t"
print(sqlglot.transpile(sql, read="postgres", write="duckdb")[0])
Output (30.33.0):
SELECT QUANTILE_CONT(x, CAST(0.9 AS DOUBLE ORDER BY x ASC) AS p FROM t
DuckDB rejects it: Parser Error: syntax error at or near "ORDER". A literal fraction works (QUANTILE_CONT(x, 0.9 ORDER BY x ASC)), and DuckDB runs the original percentile_cont(...) WITHIN GROUP (ORDER BY x) as written. percentile_disc and CAST(... AS DOUBLE PRECISION) fail the same way.
Cause: in DuckDBGenerator.withingroup_sql,
this = self.sql(expression, "this").rstrip(")")
return f"{this}{expression_sql})"
rstrip(")") removes every trailing parenthesis, not only the function's own, so the ) that closes the CAST is removed too. I suspect that removing exactly one ([:-1] or removesuffix(")")) is what is meant.
- 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 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 66/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