[4.1.1] Timescaledb hyperfunctions not correctly parsed

[NHanser]

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

  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 <lambda>
    args = self._parse_csv(lambda: self._parse_lambda(alias=alias))
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 5356, in _parse_lambda
    this = self._parse_select_or_expression(alias=alias)
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 6580, in _parse_select_or_expression
    self._parse_expression() if alias else 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 5250, in _parse_function_call
    func = self.validate_expression(func, args)
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1548, in validate_expression
    self.raise_error(error_message)
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1508, in raise_error
    raise error
sqlglot.errors.ParseError: The number of provided arguments (2) is greater than the maximum number of supported arguments (1). Line 1, Col: 45

 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

• 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 @NHanser, I'm here to help you with your Superset issue. Let's tackle this bug together!

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 ^[1].

To continue talking to Dosu, mention @dosu.

---

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

[NHanser]

For information, charts using such query were fully functionnal in 4.0.2