Bug description
We used a TimescaleDb database which is a simple PostgreSQL database with Timescaledb extension. Since upgrade to 4.1.1, such simple query
SELECT last(my_value_column, my_time_column) from my_table
is failing.
But it is only failing in dashboard, not in SQLLab.
After investigation it is due to sqlglot parsing since the dialect of the database is "simple" Postgres.
TimescaleDb introduces a bunch of "hyperfunction" (see here), including last, which are not included in postgres dialect.
The error output on server logs is
sqlglot.errors.ParseError: The number of provided arguments (2) is greater than the maximum number of supported arguments (1)
Screenshots/recordings
No response
Superset version
4.1.1
Python version
3.10
Node version
18 or greater
Browser
Chrome
Additional context
Python stack trace
```Traceback (most recent call last):
File "/app/superset/sql/parse.py", line 256, in _parse
return sqlglot.parse(script, dialect=dialect)
File "/usr/local/lib/python3.10/site-packages/sqlglot/init.py", line 102, in parse
return Dialect.get_or_raise(read or dialect).parse(sql, opts)
File "/usr/local/lib/python3.10/site-packages/sqlglot/dialects/dialect.py", line 919, in parse
return self.parser(opts).parse(self.tokenize(sql), sql)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1395, in parse
return self._parse(
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1464, in _parse
expressions.append(parse_method(self))
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1702, in _parse_statement
expression = self._parse_set_operations(expression) if expression else self._parse_select()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 2956, in _parse_select
projections = self._parse_projections()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 2900, in _parse_projections
return self._parse_expressions()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6576, in _parse_expressions
return self._parse_csv(self._parse_expression)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6535, in _parse_csv
parse_result = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4347, in _parse_expression
return self._parse_alias(self._parse_assignment())
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4350, in _parse_assignment
this = self._parse_disjunction()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4371, in _parse_disjunction
return self._parse_tokens(self._parse_conjunction, self.DISJUNCTION)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6544, in _parse_tokens
this = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4374, in _parse_conjunction
return self._parse_tokens(self._parse_equality, self.CONJUNCTION)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6544, in _parse_tokens
this = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4377, in _parse_equality
return self._parse_tokens(self._parse_comparison, self.EQUALITY)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6544, in _parse_tokens
this = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4380, in _parse_comparison
return self._parse_tokens(self._parse_range, self.COMPARISON)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6544, in _parse_tokens
this = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4383, in _parse_range
this = this or self._parse_bitwise()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4539, in _parse_bitwise
this = self._parse_term()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4573, in _parse_term
this = self._parse_factor()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4596, in _parse_factor
this = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4616, in _parse_exponent
return self._parse_tokens(self._parse_unary, self.EXPONENT)
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6544, in _parse_tokens
this = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4621, in _parse_unary
return self._parse_at_time_zone(self._parse_type())
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4673, in _parse_type
this = self._parse_column()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4956, in _parse_column
this = self._parse_column_reference()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 4965, in _parse_column_reference
this = self._parse_field()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 5152, in _parse_field
field = self._parse_primary() or self._parse_function(
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 5175, in _parse_function
func = self._parse_function_call(
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 5239, in _parse_function_call
args = self._parse_csv(lambda: self._parse_lambda(alias=alias))
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6530, in _parse_csv
parse_result = parse_method()
File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 5239, in
The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/app/superset/viz.py", line 582, in get_df_payload df = self.get_df(query_obj) File "/usr/local/lib/python3.10/site-packages/deprecation.py", line 260, in _inner return function(args, kwargs) File "/app/superset/viz.py", line 284, in get_df self.results = self.datasource.query(query_obj) File "/app/superset/connectors/sqla/models.py", line 1729, in query query_str_ext = self.get_query_str_extended(query_obj) File "/app/superset/models/helpers.py", line 880, in get_query_str_extended sqlaq = self.get_sqla_query(*query_obj) File "/app/superset/models/helpers.py", line 1735, in get_sqla_query tbl, cte = self.get_from_clause(template_processor) File "/app/superset/connectors/sqla/models.py", line 1482, in get_from_clause from_sql = self.get_rendered_sql(template_processor) + "\n" File "/app/superset/models/helpers.py", line 1054, in get_rendered_sql script = SQLScript(sql, engine=self.db_engine_spec.engine) File "/app/superset/sql/parse.py", line 611, in init self.statements = statement_class.split_script(script, engine) File "/app/superset/sql/parse.py", line 283, in split_script for ast in cls._parse(script, engine) File "/app/superset/sql/parse.py", line 259, in _parse raise SupersetParseError( superset.exceptions.SupersetParseError: Error parsing near ')' at line 1:45
### 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.
**Comment From: dosubot[bot]**
<!-- Greeting -->
Hey @NHanser, I'm here to help you with your Superset issue. Let's tackle this bug together!
<!-- Answer -->
The issue you're encountering is due to the `sqlglot` library not recognizing TimescaleDB's hyperfunctions, such as `last`, which are not part of the standard PostgreSQL dialect. This results in a parsing error when these functions are used in the Superset dashboard, although they work in SQLLab.
To address this, you have a few options:
1. **Modify the SQL Dialect**: You could extend the `sqlglot` library to support TimescaleDB hyperfunctions by adding these functions to the PostgreSQL dialect. This would involve modifying the `sqlglot` library to recognize and correctly parse these functions.
2. **Use SQLLab for Queries**: As a temporary workaround, you can execute queries involving TimescaleDB hyperfunctions in SQLLab, where they are currently working, until a more permanent solution is implemented.
3. **Custom DB Engine Specification**: Consider creating or modifying a DB engine specification in Superset to handle TimescaleDB-specific SQL syntax. This might involve customizing how queries are parsed and executed for TimescaleDB.
Unfortunately, without modifying the `sqlglot` library or Superset's handling of SQL parsing, there isn't a straightforward configuration change to resolve this issue. If you choose to modify the library, ensure you have a good understanding of its internals and how it handles SQL parsing for different dialects <sup>[[1]](https://github.com/apache/superset/blob/master/superset/sql/parse.py)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
---
<sup>Help Dosu learn! Give it feedback: [Great Response](https://app.dosu.dev/response-feedback/aa0ab23c-e32d-4ce1-97bb-329f5b4809d5?feedback_type=great_response) | [Irrelevant Answer](https://app.dosu.dev/response-feedback/aa0ab23c-e32d-4ce1-97bb-329f5b4809d5?feedback_type=irrelevant_answer) | [Incorrect Sources](https://app.dosu.dev/response-feedback/aa0ab23c-e32d-4ce1-97bb-329f5b4809d5?feedback_type=incorrect_sources) | [Too Verbose](https://app.dosu.dev/response-feedback/aa0ab23c-e32d-4ce1-97bb-329f5b4809d5?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/aa0ab23c-e32d-4ce1-97bb-329f5b4809d5?feedback_type=hallucination) | [Other](https://app.dosu.dev/response-feedback/aa0ab23c-e32d-4ce1-97bb-329f5b4809d5?feedback_type=other)</sup>
**Comment From: NHanser**
For information, charts using such query were fully functionnal in 4.0.2
**Comment From: dcherukumilli**
We are running into the same kind of issues with CTE's if the postgresql statements have something like DEPTH SEARCH FIRST. These kind of CTE's worked in 4.0.1 but they are not working in charts in 4.1.1.
It appears the underlying sqlglot does not support DEPTH SEARCH FIRST in sql statements.
Here is an example of the SQLGlot issue with the SQL statement. I wonder if there was a good reason superset moved to sqlglot with all these incompatibilities.
from sqlglot import parse_one from sqlglot.errors import ParseError
sql = """ WITH RECURSIVE tmp AS ( SELECT uid, lkuid, 0 AS depth, ARRAY[uid, lkuid] AS path, FALSE AS cycle FROM bs WHERE uid = 1 UNION ALL SELECT bs.uid, bs.lkuid, tmp.depth + 1, tmp.path || bs.lkuid, bs.lkuid = ANY(tmp.path) FROM bs, tmp WHERE bs.uid = tmp.lkuid AND NOT bs.lkuid = ANY(tmp.path) ) SEARCH DEPTH FIRST BY uid SET ordercol SELECT * FROM tmp ORDER BY ordercol; """
try: parsed_statement = parse_one(sql, read="postgres") if parsed_statement: print("SQLGlot successfully parsed the PostgreSQL statement.") # Optional: Print the parsed statement in PostgreSQL dialect (pretty format) # print(parsed_statement.sql(dialect="postgres", pretty=True)) else: print("SQLGlot failed to parse the PostgreSQL statement (returned None).") except ParseError as e: print(f"SQLGlot ParseError: {e}") ```
Comment From: jopheno
If possible add a way of adding those custom functions to the dialect on superset.cfg to ease customization; Postgres offer a very friendly environment to create custom functions of all types, would be sad to have to stick with Vanilla Postgres, or have to go through a lot of trouble just to add to it;
We also use TimescaleDB in here, and although it works just fine on SQLLab, it doesn't work on charts...
Also, I was required to enable DDL & DML in order to use LAST hyperfunction from timescaledb; And, if possible, and if it already doesn't have, a whitelist of custom functions that could be executed without having to enable DDL & DML;
Comment From: rusackas
Can anyone test to see if this is still the case on 5.0.0, or on latest master (where SQLGlot has replaced SQLParse)?