DuckDB generator: WITHIN GROUP percentile with a CAST fraction produces unbalanced parentheses

Open Beginner friendly
#9,305 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
1/5
Estimated time
Under an hour
Newbie friendliness
85/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
python, sql
Domain
databases

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

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.