Unable to parse parameters of aggregate functions in WITH alias (Dialects.CLICKHOUSE)

[UnamedRus]

Bug description

If i will try to define alias with parametrized aggregate function in query using WITH, superset will complain about not able to parse query text

WITH approx_top_k(100, 1)(number) as value SELECT value FROM numbers(10);

If i allow DML/DDL commands in connection properties, SQL LAB will show query result, but if i will try to build chart from this query, it will fail again.

For case without WITH

SQL LAB
Doesnt work without allow DML/DDL

SELECT approx_top_k(100, 1)(number) as value FROM numbers(10);

Unable to parse SQL
Error parsing near 'AS' at line 1:38

Chart
Doesnt work with allow DML/DDL

Error parsing near 'AS' at line 1:38


SELECT approx_top_k(100, 1)(number) AS value FROM numbers(10)
                                     ^

Screenshots/recordings

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

superset_app         |2025-01-01 20:00:31,303:DEBUG:superset.sql_parse:Parsing with sqlparse statement: WITH approx_top_k(100, 1)(number) as value SELECT value FROM numbers(10)
superset_app         |2025-01-01 20:00:31,314:DEBUG:superset.stats_logger:[stats_logger] (incr) SqlLabRestApi.execute_sql_query.error
superset_app         |2025-01-01 20:00:31,314:WARNING:superset.views.error_handling:SupersetErrorsException
superset_app         |Traceback (most recent call last):
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
superset_app         |    rv = self.dispatch_request()
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
superset_app         |    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
superset_app         |    return f(self, *args, **kwargs)
superset_app         |  File "/app/superset/views/base_api.py", line 119, in wraps
superset_app         |    duration, response = time_function(f, self, *args, **kwargs)
superset_app         |  File "/app/superset/utils/core.py", line 1363, in time_function
superset_app         |    response = func(*args, **kwargs)
superset_app         |  File "/app/superset/views/base_api.py", line 91, in wraps
superset_app         |    return f(self, *args, **kwargs)
superset_app         |  File "/app/superset/utils/log.py", line 303, in wrapper
superset_app         |    value = f(*args, **kwargs)
superset_app         |  File "/app/superset/sqllab/api.py", line 409, in execute_sql_query
superset_app         |    command_result: CommandResult = command.run()
superset_app         |  File "/app/superset/utils/decorators.py", line 266, in wrapped
superset_app         |    return on_error(ex)
superset_app         |  File "/app/superset/utils/decorators.py", line 236, in on_error
superset_app         |    raise ex
superset_app         |  File "/app/superset/utils/decorators.py", line 259, in wrapped
superset_app         |    result = func(*args, **kwargs)
superset_app         |  File "/app/superset/commands/sql_lab/execute.py", line 105, in run
superset_app         |    status = self._run_sql_json_exec_from_scratch()
superset_app         |  File "/app/superset/commands/sql_lab/execute.py", line 158, in _run_sql_json_exec_from_scratch
superset_app         |    return self._sql_json_executor.execute(
superset_app         |  File "/app/superset/sqllab/sql_json_executer.py", line 110, in execute
superset_app         |    raise SupersetErrorsException(
superset_app         |superset.exceptions.SupersetErrorsException: [SupersetError(message="Error parsing near 'SELECT' at line 1:49", error_type=<SupersetErrorType.INVALID_SQL_ERROR: 'INVALID_SQL_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'sql': 'WITH approx_top_k(100, 1)(number) as value SELECT value FROM numbers(10)', 'engine': 'clickhousedb', 'line': 1, 'column': 49, 'issue_codes': [{'code': 1003, 'message': 'Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.'}]}), SupersetError(message='This database does not allow for DDL/DML, and the query could not be parsed to confirm it is a read-only query. Please contact your administrator for more assistance.', error_type=<SupersetErrorType.DML_NOT_ALLOWED_ERROR: 'DML_NOT_ALLOWED_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'issue_codes': [{'code': 1022, 'message': 'Issue 1022 - Database does not allow data manipulation.'}]})]

superset_app         |2025-01-01 20:01:21,107:WARNING:superset.views.error_handling:SupersetErrorException
superset_app         |Traceback (most recent call last):
superset_app         |  File "/app/superset/sql/parse.py", line 257, in _parse
superset_app         |    return sqlglot.parse(script, dialect=dialect)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/__init__.py", line 102, in parse
superset_app         |    return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/dialects/dialect.py", line 919, in parse
superset_app         |    return self.parser(**opts).parse(self.tokenize(sql), sql)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1398, in parse
superset_app         |    return self._parse(
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1467, in _parse
superset_app         |    expressions.append(parse_method(self))
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1705, in _parse_statement
superset_app         |    expression = self._parse_set_operations(expression) if expression else self._parse_select()
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 2915, in _parse_select
superset_app         |    this = self._parse_statement()
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1704, in _parse_statement
superset_app         |    expression = self._parse_expression()
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/dialects/clickhouse.py", line 771, in _parse_expression
superset_app         |    this = super()._parse_expression()
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 4355, in _parse_expression
superset_app         |    return self._parse_alias(self._parse_assignment())
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/dialects/clickhouse.py", line 768, in _parse_alias
superset_app         |    return super()._parse_alias(this=this, explicit=explicit)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 6431, in _parse_alias
superset_app         |    this = self.expression(exp.Alias, comments=comments, this=this, alias=alias)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1531, in expression
superset_app         |    return self.validate_expression(instance)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1551, in validate_expression
superset_app         |    self.raise_error(error_message)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1511, in raise_error
superset_app         |    raise error
superset_app         |sqlglot.errors.ParseError: Required keyword: 'this' missing for <class 'sqlglot.expressions.Alias'>. Line 1, Col: 49.
superset_app         |  WITH approx_top_k(100, 1)(number) as value SELECT value FROM numbers(10)
superset_app         |
superset_app         |The above exception was the direct cause of the following exception:
superset_app         |
superset_app         |Traceback (most recent call last):
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
superset_app         |    rv = self.dispatch_request()
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
superset_app         |    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
superset_app         |  File "/app/.venv/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
superset_app         |    return f(self, *args, **kwargs)
superset_app         |  File "/app/superset/views/base_api.py", line 119, in wraps
superset_app         |    duration, response = time_function(f, self, *args, **kwargs)
superset_app         |  File "/app/superset/utils/core.py", line 1363, in time_function
superset_app         |    response = func(*args, **kwargs)
superset_app         |  File "/app/superset/utils/log.py", line 303, in wrapper
superset_app         |    value = f(*args, **kwargs)
superset_app         |  File "/app/superset/charts/data/api.py", line 260, in data
superset_app         |    return self._get_data_response(
superset_app         |  File "/app/superset/utils/log.py", line 303, in wrapper
superset_app         |    value = f(*args, **kwargs)
superset_app         |  File "/app/superset/charts/data/api.py", line 423, in _get_data_response
superset_app         |    result = command.run(force_cached=force_cached)
superset_app         |  File "/app/superset/commands/chart/data/get_data_command.py", line 45, in run
superset_app         |    payload = self._query_context.get_payload(
superset_app         |  File "/app/superset/common/query_context.py", line 102, in get_payload
superset_app         |    return self._processor.get_payload(cache_query_context, force_cached)
superset_app         |  File "/app/superset/common/query_context_processor.py", line 725, in get_payload
superset_app         |    query_results = [
superset_app         |  File "/app/superset/common/query_context_processor.py", line 726, in <listcomp>
superset_app         |    get_query_results(
superset_app         |  File "/app/superset/common/query_actions.py", line 227, in get_query_results
superset_app         |    return result_func(query_context, query_obj, force_cached)
superset_app         |  File "/app/superset/common/query_actions.py", line 103, in _get_full
superset_app         |    payload = query_context.get_df_payload(query_obj, force_cached=force_cached)
superset_app         |  File "/app/superset/common/query_context.py", line 123, in get_df_payload
superset_app         |    return self._processor.get_df_payload(
superset_app         |  File "/app/superset/common/query_context_processor.py", line 159, in get_df_payload
superset_app         |    query_result = self.get_query_result(query_obj)
superset_app         |  File "/app/superset/common/query_context_processor.py", line 236, in get_query_result
superset_app         |    result = query_context.datasource.exc_query(query_object.to_dict())
superset_app         |  File "/app/superset/models/helpers.py", line 974, in exc_query
superset_app         |    query_str_ext = self.get_query_str_extended(qry)
superset_app         |  File "/app/superset/models/helpers.py", line 879, in get_query_str_extended
superset_app         |    sqlaq = self.get_sqla_query(**query_obj)
superset_app         |  File "/app/superset/models/helpers.py", line 1729, in get_sqla_query
superset_app         |    tbl, cte = self.get_from_clause(template_processor)
superset_app         |  File "/app/superset/models/helpers.py", line 1077, in get_from_clause
superset_app         |    from_sql = self.get_rendered_sql(template_processor) + "\n"
superset_app         |  File "/app/superset/models/helpers.py", line 1056, in get_rendered_sql
superset_app         |    script = SQLScript(sql, engine=self.db_engine_spec.engine)
superset_app         |  File "/app/superset/sql/parse.py", line 612, in __init__
superset_app         |    self.statements = statement_class.split_script(script, engine)
superset_app         |  File "/app/superset/sql/parse.py", line 284, in split_script
superset_app         |    for ast in cls._parse(script, engine)
superset_app         |  File "/app/superset/sql/parse.py", line 260, in _parse
superset_app         |    raise SupersetParseError(
superset_app         |superset.exceptions.SupersetParseError: Error parsing near 'SELECT' at line 1:49

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey, @UnamedRus! I'm here to help you with any bugs, questions, or contributions you have in mind. Let's squash those bugs together!

The issue you're encountering seems to be related to the SQL parser in Superset not handling the specific syntax used in your ClickHouse query. The error message indicates a syntax error near 'SELECT', which suggests that the parser is having trouble with the WITH clause and the use of the approx_top_k function.

Here are a few things to consider:

1. Syntax Check: Ensure that the syntax of your query is correct according to ClickHouse's SQL dialect. The use of approx_top_k might require specific syntax that the parser isn't recognizing.

2. Parser Limitations: Superset's SQL parser might have limitations when dealing with certain ClickHouse-specific functions or syntax. This can lead to parsing errors, especially with complex expressions or functions like approx_top_k.

3. Workaround: As a workaround, you might try simplifying the query or breaking it into parts that can be executed separately. This could help identify which part of the query is causing the issue.

4. DML/DDL Settings: The error message also mentions that the database does not allow DDL/DML operations, which might be affecting the execution of your query. Ensure that your connection settings are configured to allow the necessary operations if applicable.

Unfortunately, without specific details on how Superset's parser handles ClickHouse functions like approx_top_k, it might be challenging to provide a precise solution. You may need to experiment with different query structures or consult ClickHouse documentation for alternative approaches to achieve the desired result.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}