UnamedRus opened a new issue, #31674: URL: https://github.com/apache/superset/issues/31674
### 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 ```sql 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. ### 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 - [X] I have searched Superset docs and Slack and didn't find a solution to my problem. - [X] I have searched the GitHub issue tracker and didn't find a similar bug report. - [X] 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. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
