You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ru...@apache.org on 2026/09/22 17:18:47 UTC
(superset) branch master updated: feat(databend): add a sqlglot dialect based on Postgres (#43964)
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git
The following commit(s) were added to refs/heads/master by this push:
new 107a61c4caf feat(databend): add a sqlglot dialect based on Postgres (#43964)
107a61c4caf is described below
commit 107a61c4caff142301fff55a6775af40ab7ade66
Author: Pat Buxton <45...@users.noreply.github.com>
AuthorDate: Tue Sep 22 18:18:39 2026 +0100
feat(databend): add a sqlglot dialect based on Postgres (#43964)
Co-authored-by: Claude Opus 5 <no...@anthropic.com>
---
superset/sql/dialects/__init__.py | 2 +
superset/sql/dialects/databend.py | 604 ++++++++
superset/sql/parse.py | 3 +-
.../sql/dialects/databend_function_catalogue.txt | 741 ++++++++++
tests/unit_tests/sql/dialects/databend_tests.py | 1553 ++++++++++++++++++++
5 files changed, 2902 insertions(+), 1 deletion(-)
diff --git a/superset/sql/dialects/__init__.py b/superset/sql/dialects/__init__.py
index 59b73c6aa34..e42e51fc7ce 100644
--- a/superset/sql/dialects/__init__.py
+++ b/superset/sql/dialects/__init__.py
@@ -15,6 +15,7 @@
# specific language governing permissions and limitations
# under the License.
+from .databend import Databend
from .db2 import DB2
from .dremio import Dremio
from .firebolt import Firebolt, FireboltOld
@@ -26,6 +27,7 @@ from .vertica import Vertica
__all__ = [
"DB2",
+ "Databend",
"Dremio",
"Firebolt",
"FireboltOld",
diff --git a/superset/sql/dialects/databend.py b/superset/sql/dialects/databend.py
new file mode 100644
index 00000000000..4cd1502c5ba
--- /dev/null
+++ b/superset/sql/dialects/databend.py
@@ -0,0 +1,604 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+"""
+Databend dialect.
+
+Databend has no built-in sqlglot dialect, so ``SQLGLOT_DIALECTS`` had no entry for
+it and every Databend query fell back to the generic dialect. Superset regenerates
+each adhoc column and metric through that dialect (``sanitize_clause``, called from
+``_process_sql_expression`` in ``superset/models/helpers.py``), so the fallback's
+rendering reached the server on every compile.
+
+The base is Postgres, which matches ``databend-sqlalchemy``: its
+``DatabendCompiler`` and ``DatabendIdentifierPreparer`` derive from ``PGCompiler``
+and ``PGIdentifierPreparer``, so the SQL Superset compiles and the SQL this dialect
+regenerates come from the same family.
+
+ClickHouse looks like the closer fit -- Databend borrows much of its surface
+syntax, including ``SETTINGS`` and the ``to_start_of_*`` date helpers -- but its
+generator renames a large number of functions to spellings Databend does not
+have, ``argMax`` and ``countIf`` among them.
+
+What Postgres still gets wrong is overridden below. The sweeps in the test file
+are what keep that list honest across a sqlglot bump: they enumerate the server's
+own function catalogue and type list rather than re-reading a set of pairs
+somebody wrote down.
+
+Databend's ``SAMPLE ROW (n)`` and ``SAMPLE BLOCK (n)`` are not supported and
+raise. Parsing them faithfully means carrying the clause through generation as
+well, which is more than the sampling is worth until somebody asks for it.
+"""
+
+from __future__ import annotations
+
+import typing as t
+
+from sqlglot import exp, generator
+from sqlglot.dialects.dialect import rename_func
+from sqlglot.dialects.postgres import Postgres
+from sqlglot.tokens import TokenType
+
+# Names left unparsed so Databend's own spelling survives a round-trip; see
+# ``Parser.FUNCTIONS``.
+#
+# The date functions are unit-first in Databend and argument-first in Postgres.
+# QUANTILE is a parametric aggregate: sqlglot maps the bare name to a typed node,
+# which would take the level list for its arguments and leave the real argument
+# list stranded, so it has to reach the generic parametric parse as an anonymous
+# call like the rest of its family.
+LEFT_UNPARSED = {
+ "QUANTILE",
+ "DATEADD",
+ "DATEDIFF",
+ "DATESUB",
+ "DATE_ADD",
+ "DATE_DIFF",
+ "DATE_SUB",
+ # Databend's bitwise helpers are two-argument scalars; Postgres has the same
+ # names as one-argument aggregates, so it refuses the second argument and the
+ # expression never reaches the server at all.
+ "BIT_AND",
+ "BIT_OR",
+ "BIT_XOR",
+ # Databend's TO_CHAR takes a value alone as well as a value and a format;
+ # Postgres requires the format and raises without it.
+ "TO_CHAR",
+ # Not a name Databend has, but Postgres renders it as nothing at all -- the
+ # select list disappears, or a DDL DEFAULT is left dangling. Left unparsed so
+ # it reaches the server and is refused there instead.
+ "MAP_FROM_ENTRIES",
+ # Postgres splices the argument's *name* into an interval literal --
+ # INTERVAL 'days DAY' -- so a column reference silently becomes a string.
+ "DATE_FROM_UNIX_DATE",
+ # Postgres has no TRY and removes it, leaving the bare expression: TRY(a / b)
+ # became a / b, so a cell that answered NULL on a division by zero starts
+ # failing the whole query instead. Databend has no general TRY either -- only
+ # the try_to_* family -- so there is nothing to rewrite it to, and reaching
+ # the server unchanged at least fails loudly.
+ "TRY",
+ # Databend's CONVERT_TIMEZONE takes the zone and the timestamp, and has no
+ # three-argument form: "no function matches signature `convert_timezone(
+ # String, String, Timestamp)`". Postgres turns the call into AT TIME ZONE,
+ # which Databend's parser rejects outright -- "unexpected `TIME`" -- so
+ # neither arity survived the rewrite.
+ "CONVERT_TIMEZONE",
+ # Databend's TIMESTAMP_DIFF takes two timestamps and answers an
+ # interval. Postgres renders the node as TIMESTAMPDIFF, which is not a
+ # name Databend has, so its own spelling is kept instead.
+ "TIMESTAMP_DIFF",
+}
+
+
+def _date_format(args: list[t.Any]) -> exp.Expression:
+ """
+ Parse ``formatDateTime(...)`` as Databend's ``DATE_FORMAT(...)``.
+
+ ClickHouse's parser folded both spellings onto ``exp.TimeToStr`` and its
+ generator emitted ``formatDateTime``, so charts authored while that rewrite
+ was in effect may have the ClickHouse spelling stored. Databend rejects it --
+ "no function matches the given name: 'formatdatetime', do you mean
+ 'date_format'?" -- so it is healed on the way through. Both spellings take
+ the same ``%``-style format string, which is passed along untouched.
+ """
+ return exp.Anonymous(this="DATE_FORMAT", expressions=args)
+
+
+def _sha2(self: generator.Generator, expression: exp.SHA2) -> str:
+ """Databend spells this ``SHA2(x, 256)``; Postgres emits ``SHA256(x)``."""
+ return self.func("SHA2", expression.this, expression.args.get("length"))
+
+
+def _struct(self: generator.Generator, expression: exp.Struct) -> str:
+ """
+ Write Databend's object literal, ``{'k': 1}``.
+
+ Postgres has no such literal and renders the node as ``STRUCT(1 AS k)``.
+ Databend has no ``struct`` function at all -- ``object_construct`` and
+ ``tuple`` are the nearest names it has -- so that call is rejected outright.
+
+ An empty literal, ``{}``, is written the same way and is valid on the server.
+ A struct whose fields are not named is a different thing and is left to the
+ base generator.
+ """
+ fields = expression.expressions
+ if any(not isinstance(field, exp.PropertyEQ) for field in fields):
+ return self.struct_sql(expression)
+
+ pairs = ", ".join(
+ f"{self.sql(exp.Literal.string(field.name))}: {self.sql(field.expression)}"
+ for field in fields
+ )
+ return f"{{{pairs}}}"
+
+
+def _ilike(self: generator.Generator, expression: exp.ILike) -> str:
+ """
+ Spell a case-insensitive match the way the engine spec already does.
+
+ Databend has no ILIKE operator -- "unexpected `'%ell%'`" -- and
+ ``superset/db_engine_specs/databend.py`` patches ``DatabendCompiler`` to emit
+ ``LOWER(x) LIKE LOWER(y)`` for exactly that reason. Passing ILIKE through
+ left the two halves of Superset disagreeing about the same server: a filter
+ built from the UI worked and the same comparison written into an adhoc column
+ did not.
+ """
+ return self.sql(
+ exp.Like(
+ this=exp.Lower(this=expression.this),
+ expression=exp.Lower(this=expression.expression),
+ )
+ )
+
+
+def _json_extract(
+ self: generator.Generator, expression: exp.JSONExtract | exp.JSONExtractScalar
+) -> str:
+ """
+ Render a JSON path the way Databend spells it.
+
+ Postgres explodes a path into one argument per segment --
+ ``JSON_EXTRACT_PATH(j, 'a', 'b')`` -- but Databend's
+ ``JSON_EXTRACT_PATH_TEXT`` takes exactly two arguments, the second a dotted
+ path string: ``JSON_EXTRACT_PATH_TEXT(j, 'a.b')``. Renaming the function
+ without rebuilding the path silently collapses the segments into one key
+ (``'a.b'`` becoming ``'ab'``), which reads a field that is not there and
+ returns NULL rather than failing.
+
+ Only keys and subscripts can be written that way. Recursive descent, filter
+ selectors and unions cannot, and dropping them would repeat the same failure
+ one level down -- ``'a..b'`` flattening to ``'a'`` reads the wrong field and
+ returns NULL. Those, and a path given as an expression rather than a literal,
+ go to ``JSON_PATH_QUERY_FIRST``, which takes a path argument directly; if
+ sqlglot cannot render the path either, it raises, which is the right outcome
+ for a path this dialect cannot faithfully express.
+
+ One ambiguity is inherited from the parse and cannot be resolved here.
+ Databend's own ``JSON_EXTRACT_PATH_TEXT(j, 'a.b')``, meaning the path ``a``
+ then ``b``, and the quoted ``$."a.b"``, meaning the single key ``a.b``, both
+ parse to one ``JSONPathKey`` holding ``a.b`` -- the spellings are already
+ indistinguishable by the time generation runs. The dotted form is emitted,
+ which keeps Databend's own syntax round-tripping unchanged; a literal key
+ containing a dot has to be written with Databend's bracketed ``j['a.b']``,
+ which passes through this dialect unparsed.
+ """
+ path = expression.expression
+ segments: list[str] = []
+ flattened = isinstance(path, exp.JSONPath)
+
+ if flattened:
+ for segment in path.expressions:
+ if isinstance(segment, exp.JSONPathSubscript):
+ segments.append(f"[{segment.this}]")
+ elif isinstance(segment, exp.JSONPathKey):
+ segments.append(f".{segment.this}" if segments else str(segment.this))
+ elif not isinstance(segment, exp.JSONPathRoot):
+ flattened = False
+ break
+
+ if flattened and segments:
+ return self.func(
+ "JSON_EXTRACT_PATH_TEXT",
+ expression.this,
+ exp.Literal.string("".join(segments)),
+ )
+
+ return self.func("JSON_PATH_QUERY_FIRST", expression.this, path)
+
+
+def _is_constant_assignment(expression: exp.Expression) -> bool:
+ """
+ Return whether ``expression`` is a ``key = <constant>`` pair.
+
+ Databend requires constants here -- it answers anything else with "value
+ must be constant value" -- and the restriction matters beyond tidiness. The
+ leading clause is re-emitted from source text rather than from the tree, so
+ anything the parser accepts but the tree does not expose would be invisible
+ to the table extraction that RLS and the denylist rely on, yet still be sent
+ to the server verbatim. Accepting only constants keeps that from arising.
+
+ Both sides have to be checked. Reading only the value left the key free to be
+ anything at all: ``SETTINGS ((SELECT x FROM secret) = 1) SELECT a FROM t``
+ was absorbed, re-emitted with the subquery intact, and reported one table.
+ Databend happens to refuse a non-identifier key, but a guard that relies on
+ the server to enforce what its own docstring promises is not a guard.
+
+ A node that is not an assignment -- ``SETTINGS (foo)`` parses to a column --
+ has neither a value nor a key to read, so it is rejected by the same check.
+ """
+ value = expression.expression
+ if isinstance(value, exp.Neg):
+ value = value.this
+
+ return isinstance(expression.this, exp.Column) and isinstance(
+ value, (exp.Literal, exp.Boolean, exp.Null)
+ )
+
+
+# Postgres's own rendering of a positional TRIM, kept for the branch below that
+# still wants it.
+_POSTGRES_TRIM = Postgres.Generator.TRANSFORMS[exp.Trim]
+
+
+class Databend(Postgres):
+ # Databend writes digit groups the way ClickHouse does. Without this the
+ # tokenizer stops at the first underscore, so 1_000_000 parses as 1 followed
+ # by an alias named _000_000 -- a literal silently reduced to a millionth of
+ # itself, with no error anywhere.
+ NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
+
+ class Tokenizer(Postgres.Tokenizer):
+ # Databend accepts backtick-quoted identifiers as well as the
+ # double-quoted form Postgres uses.
+ IDENTIFIERS = ['"', "`"]
+
+ # A backslash escapes inside both strings and identifiers, as it does in
+ # ClickHouse. Postgres treats it as an ordinary character, so 'don't'
+ # ends the string early and the remainder fails to tokenize at all --
+ # a TokenError rather than a parse error, out of a very common spelling.
+ STRING_ESCAPES = ["'", "\\"]
+ IDENTIFIER_ESCAPES = ["\\"]
+
+ # 0x1A is an integer in Databend, as the server confirms by returning 26.
+ # Postgres reads x'1A' as a bit string, so leaving this alone changes the
+ # literal's type as well as its spelling.
+ HEX_STRINGS = [("0x", ""), ("0X", "")]
+
+ # Two width spellings mean different things in the two dialects, and
+ # both widen silently if left alone. Postgres reads INT8 as eight bytes
+ # where Databend reads one, so CAST(300 AS INT8) -- which overflows on
+ # the server -- would become CAST(300 AS BIGINT) and quietly succeed.
+ # Postgres reads FLOAT as double precision where Databend reads it as
+ # 32-bit. Confirmed with typeof() on the server; INT16/INT32/INT64
+ # already agree.
+ KEYWORDS = {
+ **Postgres.Tokenizer.KEYWORDS,
+ "INT8": TokenType.TINYINT,
+ "FLOAT": TokenType.FLOAT,
+ # Databend documents DIV alongside + - * / %. Postgres has no
+ # such operator, so the expression failed to parse at all --
+ # inside sanitize_clause, before the server ever saw it.
+ "DIV": TokenType.DIV,
+ }
+
+ class Parser(Postgres.Parser):
+ # Databend's colon reads a field out of a VARIANT, as Snowflake's does.
+ # Postgres reads it as the start of a named bind parameter, so ``j:a``
+ # became ``j AS %(a)s`` -- a pyformat placeholder on its way to a DBAPI,
+ # which is a worse thing to emit than a syntax error.
+ COLON_IS_VARIANT_EXTRACT = True
+
+ # Postgres's table minus the names in LEFT_UNPARSED, which carries the
+ # reason for each. Leaving a name out of the table keeps Databend's own
+ # spelling intact through a round-trip, which is what Superset does with
+ # every adhoc column and metric.
+ FUNCTIONS: dict[str, t.Callable[..., exp.Expression]] = {
+ key: value
+ for key, value in {
+ **Postgres.Parser.FUNCTIONS,
+ "FORMATDATETIME": _date_format,
+ }.items()
+ if key not in LEFT_UNPARSED
+ }
+
+ def _parse_bracket_key_value(
+ self, is_map: bool = False
+ ) -> exp.Expression | None:
+ """
+ Read an object literal's key with ``:`` meaning key-value.
+
+ Databend spells a path extraction ``v:a`` and an object literal
+ ``{'k': 1}`` with the same character, and the extraction is resolved
+ first, so the literal came back as
+ ``STRUCT(JSON_PATH_QUERY_FIRST('k', '1'))`` -- an object rewritten
+ into a lookup, which the server accepts and answers differently.
+
+ Only the key is read with extraction disabled, so a value that is
+ itself an extraction, ``{'k': v:a}``, still parses as one. Both forms
+ were run against the server.
+ """
+ if not is_map:
+ return super()._parse_bracket_key_value(is_map=is_map)
+
+ self.COLON_IS_VARIANT_EXTRACT = False
+ try:
+ key = self._parse_alias(self._parse_disjunction(), explicit=True)
+ finally:
+ self.COLON_IS_VARIANT_EXTRACT = True
+
+ return self._parse_slice(key)
+
+ def _parse_function(
+ self, *args: t.Any, **kwargs: t.Any
+ ) -> exp.Expression | None:
+ """
+ Parse Databend's parametric aggregates, ``NAME(params)(args)``.
+
+ Postgres has no such syntax, so the base parser reads the first list
+ as the arguments and then takes the second for an alias:
+ ``histogram(5)(a)`` becomes ``HISTOGRAM(5) AS (a)``, which the server
+ rejects. The whole family is affected -- quantile, quantile_cont,
+ quantile_disc, quantile_tdigest and histogram are all in the
+ catalogue -- so this is handled by shape rather than by name, which
+ also allows several parameters and several arguments.
+
+ The trigger is the shape alone -- an anonymous call followed by a
+ second parenthesised list -- so it is not confined to the select
+ list. A column-alias list, ``FROM my_udtf(1) (a, b)``, has the same
+ shape and is read the same way; that is not valid input to either
+ dialect, and ``AS g(x)`` round-trips, so it is a note rather than a
+ defect.
+
+ The window clause is attached here as well. Only what this method
+ returns is offered one, so building the aggregate and handing it
+ straight back left ``quantile(0.5)(a) OVER (...)`` -- valid
+ Databend -- failing to parse at all.
+ """
+ this = super()._parse_function(*args, **kwargs)
+
+ if (
+ isinstance(this, exp.Anonymous)
+ and self._curr
+ and self._curr.token_type == TokenType.L_PAREN
+ ):
+ aggregate = exp.ParameterizedAgg(
+ this=this.this,
+ expressions=self._parse_wrapped_csv(self._parse_lambda),
+ params=this.expressions,
+ )
+ return self._parse_window(aggregate)
+
+ return this
+
+ # Databend accepts ClickHouse's trailing ``SETTINGS k = v``.
+ QUERY_MODIFIER_PARSERS = {
+ **Postgres.Parser.QUERY_MODIFIER_PARSERS,
+ TokenType.SETTINGS: lambda self: (
+ "settings",
+ self._advance() or self._parse_csv(self._parse_assignment),
+ ),
+ }
+
+ def _starts_settings_clause(self) -> bool:
+ """Whether the SETTINGS ahead opens a clause rather than being an alias."""
+ if not self._curr or self._curr.token_type != TokenType.SETTINGS:
+ return False
+ return bool(
+ self._index + 2 < len(self._tokens)
+ and self._tokens[self._index + 2].token_type == TokenType.EQ
+ )
+
+ def _parse_table_alias(
+ self, alias_tokens: t.Collection[TokenType] | None = None
+ ) -> exp.TableAlias | None:
+ # SETTINGS is both a clause keyword and an ordinary identifier, so
+ # ``FROM t SETTINGS max_threads = 1`` and ``FROM t settings`` (a table
+ # aliased ``settings``) both have to work. Look ahead for the ``=``
+ # that only the clause has, the way the base parser disambiguates
+ # LIMIT and OFFSET. Removing SETTINGS from ``TABLE_ALIAS_TOKENS``
+ # instead would drop the alias silently and leave any reference to it
+ # dangling.
+ if self._starts_settings_clause():
+ return None
+ return super()._parse_table_alias(alias_tokens)
+
+ def _parse_statement(self) -> exp.Expression | None:
+ # Databend also accepts a *leading* ``SETTINGS (...)`` clause before
+ # the statement -- e.g.
+ # ``SETTINGS (max_execute_time_in_seconds=300) SELECT ...`` -- which no
+ # built-in dialect parses. It is absorbed here and re-emitted verbatim
+ # in ``Generator.generate``.
+ settings = None
+ if self._curr and self._curr.token_type == TokenType.SETTINGS:
+ index = self._index
+ start = self._curr
+ self._advance()
+ if self._curr and self._curr.token_type == TokenType.L_PAREN:
+ assignments = self._parse_wrapped_csv(self._parse_assignment)
+ if assignments and all(
+ _is_constant_assignment(assignment)
+ for assignment in assignments
+ ):
+ settings = self._find_sql(start, self._prev)
+ else:
+ self._retreat(index)
+ else:
+ self._retreat(index)
+
+ statement = super()._parse_statement()
+ if settings and statement:
+ statement.set("leading_settings", settings)
+ return statement
+
+ class Generator(Postgres.Generator):
+ # Rendering flags where the two bases disagree and Databend follows
+ # ClickHouse. Each was found by diffing the two generators rather than by
+ # meeting the symptom: COPY INTO needs its INTO; COUNT(DISTINCT a, b)
+ # is written
+ # directly rather than through a CASE that also drops rows where either
+ # column is null; ARRAY_LENGTH takes no dimension; MEDIAN exists.
+ COPY_HAS_INTO_KEYWORD = True
+ MULTI_ARG_DISTINCT = True
+ ARRAY_SIZE_DIM_REQUIRED = False
+ SUPPORTS_MEDIAN = True
+ STRUCT_DELIMITER = ("(", ")")
+
+ # Postgres renders these as spellings Databend's parser rejects, or as a
+ # wider type than was written. Each replacement is a name the server
+ # itself listed when asked for a bad one.
+ TYPE_MAPPING = {
+ **Postgres.Generator.TYPE_MAPPING,
+ exp.DataType.Type.BINARY: "BINARY",
+ exp.DataType.Type.VARBINARY: "VARBINARY",
+ exp.DataType.Type.FLOAT: "FLOAT",
+ exp.DataType.Type.TINYINT: "TINYINT",
+ }
+
+ # Postgres renames these to spellings Databend's catalogue does not have.
+ # Each replacement was checked against a live Databend (Query v1.2.790).
+ # Postgres has neither QUALIFY nor, in its own grammar, SEMI and ANTI
+ # joins, so its exp.Select chain rewrites both away: QUALIFY into a
+ # subquery carrying a generated _w column, which a SELECT * then lifts
+ # into the result set, and SEMI/ANTI into WHERE EXISTS. Databend has all
+ # three, and the base generator renders SEMI/ANTI natively once that
+ # chain is gone, so the entry is dropped rather than trimmed.
+ TRANSFORMS = {
+ **{
+ key: value
+ for key, value in Postgres.Generator.TRANSFORMS.items()
+ if key is not exp.Select
+ },
+ exp.AnyValue: rename_func("ANY"),
+ # Databend writes an array literal [1, 2, 3]; ARRAY[1, 2, 3] is
+ # Postgres-only and the server rejects it outright.
+ exp.Array: lambda self, e: f"[{self.expressions(e, flat=True)}]",
+ exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
+ exp.ArrayConcat: rename_func("ARRAY_CONCAT"),
+ exp.ArrayContains: rename_func("ARRAY_CONTAINS"),
+ # Postgres has no lambda, so it rewrites one into ARRAY(SELECT ... FROM
+ # UNNEST(a) WHERE ...) -- and ARRAY_ANY is built out of this node, so it
+ # inherited the subquery too. Databend takes the lambda as written.
+ exp.ArrayFilter: rename_func("ARRAY_FILTER"),
+ exp.ArrayIntersect: rename_func("ARRAY_INTERSECTION"),
+ exp.CurrentDate: rename_func("TODAY"),
+ # Databend has COUNT_IF. Postgres does not, and expands it to
+ # SUM(CASE WHEN ... THEN 1 ELSE 0 END), which is not the same
+ # aggregate: over no matching rows COUNT_IF answers 0 and the SUM
+ # answers NULL, so a chart that read zero starts reading blank.
+ exp.CountIf: rename_func("COUNT_IF"),
+ exp.DayOfMonth: rename_func("DAYOFMONTH"),
+ exp.DayOfWeek: rename_func("DAYOFWEEK"),
+ exp.DayOfYear: rename_func("DAYOFYEAR"),
+ # DIV is an operator in Databend, not a callable: DIV(7, 2) is
+ # answered with "missing lhs or rhs for the binary operator".
+ exp.ILike: _ilike,
+ exp.IntDiv: lambda self, e: (
+ f"{self.sql(e, 'this')} DIV {self.sql(e, 'expression')}"
+ ),
+ exp.JSONExtract: _json_extract,
+ exp.JSONExtractScalar: _json_extract,
+ exp.ParameterizedAgg: lambda self, e: (
+ f"{e.this}({self.expressions(e, key='params', flat=True)})"
+ f"({self.expressions(e, flat=True)})"
+ ),
+ exp.Rand: rename_func("RAND"),
+ exp.RegexpLike: rename_func("REGEXP_LIKE"),
+ # Postgres spells these as the ~ and ~* operators, neither of
+ # which Databend has; its REGEXP_LIKE takes the flags instead.
+ exp.RegexpILike: lambda self, e: self.func(
+ "REGEXP_LIKE", e.this, e.expression, exp.Literal.string("i")
+ ),
+ exp.SHA2: _sha2,
+ exp.Struct: _struct,
+ # Postgres has no TRY_CAST and degrades it to CAST, turning a
+ # column that returned NULLs into one that raises. Databend has
+ # try_cast.
+ exp.TryCast: lambda self, e: self.cast_sql(e, safe_prefix="TRY_"),
+ # Databend takes TRIM(x, chars) or a direction keyword, and rejects
+ # the bare TRIM(chars FROM x) that Postgres emits for the two-argument
+ # call: "unexpected `FROM`". LEADING, TRAILING and BOTH are accepted as
+ # Postgres already writes them, so those are handed back to it rather
+ # than rewritten.
+ exp.Trim: lambda self, e: (
+ self.func("TRIM", e.this, e.expression)
+ if e.expression and not e.args.get("position")
+ else _POSTGRES_TRIM(self, e)
+ ),
+ exp.WeekOfYear: rename_func("WEEKOFYEAR"),
+ }
+
+ def parameter_sql(self, expression: exp.Parameter) -> str:
+ """
+ Tell Databend's two prefixes apart: ``@stage`` and ``$1``.
+
+ ``@name`` addresses a stage and ``$n`` is a positional column
+ reference -- the documented way to read a staged file,
+ ``SELECT $1, $2 FROM @mystage``. Postgres renders both with
+ ``$`` and ClickHouse both with ``@``, so either single token turns one
+ into the other, and ``@1`` is a string constant on the server: picking
+ ``@`` returned constants where the user asked for columns, with no
+ error anywhere. The parse already separates them -- a positional
+ reference holds a literal, a stage holds a var -- so the prefix is
+ taken from that rather than fixed.
+ """
+ prefix = "$" if isinstance(expression.this, exp.Literal) else "@"
+ return f"{prefix}{self.sql(expression, 'this')}"
+
+ def datatype_sql(self, expression: exp.DataType) -> str:
+ """
+ Render the container types the way Databend spells them.
+
+ Postgres writes ``INT[]`` and ``MAP<TEXT, INT>``, which Databend
+ rejects outright -- "unexpected `[`" and "unexpected `<`". Only the
+ container is rewritten, so what comes out is ``ARRAY(INT)`` and
+ ``MAP(TEXT, INT)``: the element types are left to the mapping above,
+ and Databend accepts those spellings as aliases of its own INT32 and
+ STRING. ARRAY and MAP are its headline complex types, and a cast to
+ one is ordinary in an adhoc column.
+ """
+ if expression.expressions and expression.is_type(
+ exp.DataType.Type.ARRAY, exp.DataType.Type.MAP
+ ):
+ name = "ARRAY" if expression.is_type(exp.DataType.Type.ARRAY) else "MAP"
+ return f"{name}({self.expressions(expression, flat=True)})"
+
+ return super().datatype_sql(expression)
+
+ def after_limit_modifiers(self, expression: exp.Expression) -> list[str]:
+ """Re-emit a trailing ``SETTINGS k = v`` absorbed by the parser."""
+ modifiers = super().after_limit_modifiers(expression)
+ if not expression.args.get("settings"):
+ return modifiers
+
+ return modifiers + [
+ self.seg("SETTINGS ")
+ + self.expressions(expression, key="settings", flat=True)
+ ]
+
+ def generate(self, expression: exp.Expression, copy: bool = True) -> str:
+ # Re-emit the absorbed leading ``SETTINGS (...)`` at the statement
+ # root, so it survives regardless of the root's type -- a bare
+ # SELECT, a UNION, a parenthesized subquery, or a non-query
+ # statement. ``select_sql`` alone would drop it on anything but a
+ # plain SELECT.
+ settings = (
+ expression.args.get("leading_settings")
+ if isinstance(expression, exp.Expression)
+ else None
+ )
+ sql = super().generate(expression, copy=copy)
+ return f"{settings} {sql}" if settings else sql
diff --git a/superset/sql/parse.py b/superset/sql/parse.py
index a8ceb9a4d13..f69bc823335 100644
--- a/superset/sql/parse.py
+++ b/superset/sql/parse.py
@@ -51,6 +51,7 @@ from sqlglot.optimizer.scope import (
from superset.exceptions import QueryClauseValidationException, SupersetParseError
from superset.sql.dialects import (
+ Databend,
DB2,
Dremio,
Firebolt,
@@ -118,7 +119,7 @@ SQLGLOT_DIALECTS = {
"cockroachdb": Dialects.POSTGRES,
"couchbase": Dialects.MYSQL,
# "crate": ???
- # "databend": ???
+ "databend": Databend,
"databricks": Dialects.DATABRICKS,
"db2": DB2,
# "denodo": ???
diff --git a/tests/unit_tests/sql/dialects/databend_function_catalogue.txt b/tests/unit_tests/sql/dialects/databend_function_catalogue.txt
new file mode 100644
index 00000000000..1a0c2ddfaaa
--- /dev/null
+++ b/tests/unit_tests/sql/dialects/databend_function_catalogue.txt
@@ -0,0 +1,741 @@
+# Databend's function catalogue, captured from Databend Query v1.2.790 with:
+# SHOW FUNCTIONS
+#
+# Used by databend_tests.py to assert that no parse/generate round-trip emits a
+# function name Databend does not have. Regenerate against a newer Databend by
+# re-running the query above and replacing the names below.
+#
+# One direction only. A name being here means Databend knows it, and that is
+# what the sweeps rely on. A name being absent is NOT evidence Databend lacks
+# it: count_if, array_filter and array_apply all run on the server and none of
+# them is listed. Reading absence as proof is how COUNT_IF came to be rewritten
+# into a SUM(CASE ...) that answers NULL where COUNT_IF answers 0. Check the
+# server before concluding a name is missing.
+#
+# This is that query's output verbatim, not a curated list, so that regenerating
+# it stays a copy rather than an edit. A few entries -- rlike, like, glob, and,
+# or, xor and the comparison names among them -- are operators rather than
+# callable functions: Databend answers rlike('a', 'b') with "missing lhs or rhs
+# for the binary operator". They are listed here because Databend lists them,
+# and the assertion this file backs is only that a rewritten name is one Databend
+# knows, which is a weaker claim than it being callable in that position.
+
+abs
+acos
+add
+add_days
+add_hours
+add_minutes
+add_months
+add_quarters
+add_seconds
+add_weeks
+add_years
+age
+ai_embedding_vector
+ai_text_completion
+and
+and_filters
+any
+approx_count_distinct
+arg_max
+arg_min
+array
+array_agg
+array_aggregate
+array_any
+array_append
+array_approx_count_distinct
+array_avg
+array_compact
+array_concat
+array_construct
+array_contains
+array_count
+array_distinct
+array_except
+array_flatten
+array_generate_range
+array_get
+array_indexof
+array_insert
+array_intersection
+array_kurtosis
+array_length
+array_max
+array_median
+array_min
+array_overlap
+array_prepend
+array_remove
+array_remove_first
+array_remove_last
+array_reverse
+array_size
+array_skewness
+array_slice
+array_sort
+array_sort_asc_null_first
+array_sort_asc_null_last
+array_sort_desc_null_first
+array_sort_desc_null_last
+array_std
+array_stddev
+array_stddev_pop
+array_stddev_samp
+array_sum
+array_to_string
+array_unique
+arrays_zip
+as_array
+as_binary
+as_boolean
+as_date
+as_decimal
+as_float
+as_integer
+as_interval
+as_object
+as_string
+as_timestamp
+ascii
+asin
+assume_not_null
+atan
+atan2
+avg
+between_days
+between_dows
+between_doys
+between_epochs
+between_hours
+between_isodows
+between_isoyears
+between_microseconds
+between_millenniums
+between_minutes
+between_months
+between_quarters
+between_seconds
+between_weeks
+between_years
+between_yearweeks
+bin
+bit_and
+bit_length
+bit_not
+bit_or
+bit_shift_left
+bit_shift_right
+bit_xor
+bitmap_and
+bitmap_and_count
+bitmap_and_not
+bitmap_cardinality
+bitmap_contains
+bitmap_count
+bitmap_has_all
+bitmap_has_any
+bitmap_intersect
+bitmap_max
+bitmap_min
+bitmap_not
+bitmap_not_count
+bitmap_or
+bitmap_or_count
+bitmap_subset_in_range
+bitmap_subset_limit
+bitmap_union
+bitmap_xor
+bitmap_xor_count
+blake3
+bool_and
+bool_or
+build_bitmap
+cast
+cbrt
+ceil
+ceiling
+char
+char_length
+character_length
+check_json
+chr
+city64withseed
+client_session_id
+coalesce
+concat
+concat_ws
+connection_id
+contains
+convert_timezone
+cos
+cosine_distance
+cot
+count
+covar_pop
+covar_samp
+crc32
+current_available_roles
+current_catalog
+current_database
+current_role
+current_secondary_roles
+current_timestamp
+current_user
+currentdatabase
+currentuser
+database
+date
+date_add
+date_add_months
+date_diff
+date_format
+date_part
+date_sub
+date_subtract_months
+date_trunc
+day
+dayofmonth
+dayofweek
+dayofyear
+decode
+degrees
+delete_by_keypath
+diff_days
+diff_dows
+diff_doys
+diff_epochs
+diff_hours
+diff_isodows
+diff_isoyears
+diff_microseconds
+diff_millenniums
+diff_minutes
+diff_months
+diff_quarters
+diff_seconds
+diff_weeks
+diff_years
+diff_yearweeks
+div
+div0
+divide
+divnull
+epoch
+eq
+equal_null
+error_or
+exp
+extract
+factorial
+feistel_obfuscate
+flatten
+floor
+from_base64
+from_hex
+gen_random_uuid
+geo_distance
+geo_to_h3
+geohash_decode
+geohash_encode
+get
+get_by_keypath
+get_by_keypath_string
+get_ignore_case
+get_path
+get_string
+getvariable
+glob
+great_circle_angle
+great_circle_distance
+greatest
+greatest_ignore_nulls
+group_array_moving_avg
+group_array_moving_sum
+group_concat
+grouping
+gt
+gte
+h3_cell_area_m2
+h3_cell_area_rads2
+h3_distance
+h3_edge_angle
+h3_edge_length_km
+h3_edge_length_m
+h3_exact_edge_length_km
+h3_exact_edge_length_m
+h3_exact_edge_length_rads
+h3_get_base_cell
+h3_get_destination_index_from_unidirectional_edge
+h3_get_faces
+h3_get_indexes_from_unidirectional_edge
+h3_get_origin_index_from_unidirectional_edge
+h3_get_resolution
+h3_get_unidirectional_edge
+h3_get_unidirectional_edge_boundary
+h3_get_unidirectional_edges_from_hexagon
+h3_hex_area_km2
+h3_hex_area_m2
+h3_hex_ring
+h3_indexes_are_neighbors
+h3_is_pentagon
+h3_is_res_class_iii
+h3_is_valid
+h3_k_ring
+h3_line
+h3_num_hexagons
+h3_to_center_child
+h3_to_children
+h3_to_geo
+h3_to_geo_boundary
+h3_to_parent
+h3_to_string
+h3_unidirectional_edge_is_valid
+haversine
+hex
+hilbert_range_index
+histogram
+humanize_number
+humanize_size
+if
+iff
+ifnull
+ignore
+inet_aton
+inet_ntoa
+inner_product
+insert
+instr
+intdiv
+intersect_count
+ipv4_num_to_string
+ipv4_string_to_num
+is_array
+is_binary
+is_boolean
+is_date
+is_decimal
+is_double
+is_error
+is_float
+is_integer
+is_interval
+is_not_error
+is_not_null
+is_null
+is_null_value
+is_object
+is_real
+is_string
+is_timestamp
+is_true
+jaro_winkler
+jq
+json_agg
+json_array
+json_array_agg
+json_array_distinct
+json_array_elements
+json_array_except
+json_array_insert
+json_array_intersection
+json_array_overlap
+json_contains_in_left
+json_contains_in_right
+json_each
+json_exists_all_keys
+json_exists_any_keys
+json_exists_key
+json_extract_path_text
+json_object
+json_object_agg
+json_object_delete
+json_object_insert
+json_object_keep_null
+json_object_keys
+json_object_pick
+json_path_exists
+json_path_match
+json_path_query
+json_path_query_array
+json_path_query_first
+json_pretty
+json_strip_nulls
+json_to_string
+json_typeof
+kurtosis
+l1_distance
+l2_distance
+last_query_id
+lcase
+least
+least_ignore_nulls
+left
+length
+length_utf8
+like
+like_any
+list
+listagg
+ln
+locate
+log
+log10
+log2
+lower
+lpad
+lt
+lte
+ltrim
+map
+map_cat
+map_contains_key
+map_delete
+map_insert
+map_keys
+map_pick
+map_size
+map_values
+markov_generate
+markov_train
+max
+md5
+median
+median_tdigest
+median_tdigest_weighted
+mid
+millennium
+min
+minus
+mod
+mode
+modulo
+month
+months_between
+multiply
+neg
+negate
+not
+noteq
+now
+nullif
+nvl
+nvl2
+object_construct
+object_construct_keep_null
+object_delete
+object_insert
+object_keys
+object_pick
+oct
+octet_length
+or
+ord
+parse_json
+pi
+plus
+point_in_ellipses
+point_in_polygon
+position
+pow
+power
+quantile
+quantile_cont
+quantile_disc
+quantile_tdigest
+quantile_tdigest_weighted
+quarter
+quote
+radians
+rand
+range
+range_bound
+range_partition_id
+regexp
+regexp_extract
+regexp_extract_all
+regexp_instr
+regexp_like
+regexp_replace
+regexp_split_to_array
+regexp_split_to_table
+regexp_substr
+remove_nullable
+repeat
+replace
+retention
+reverse
+right
+rlike
+round
+rpad
+rtrim
+running_difference
+sha
+sha1
+sha2
+sign
+sin
+siphash
+siphash64
+skewness
+sleep
+slice
+soundex
+space
+split
+split_part
+sqrt
+st_area
+st_asbinary
+st_asewkb
+st_asewkt
+st_asgeojson
+st_astext
+st_aswkb
+st_aswkt
+st_collect
+st_contains
+st_convexhull
+st_dimension
+st_disjoint
+st_distance
+st_endpoint
+st_equals
+st_geogfromewkt
+st_geogfromtext
+st_geogfromwkt
+st_geographyfromewkt
+st_geographyfromtext
+st_geographyfromwkt
+st_geohash
+st_geom_point
+st_geometryfromewkb
+st_geometryfromewkt
+st_geometryfromtext
+st_geometryfromwkb
+st_geometryfromwkt
+st_geomfromewkb
+st_geomfromewkt
+st_geomfromgeohash
+st_geomfromtext
+st_geomfromwkb
+st_geomfromwkt
+st_geompointfromgeohash
+st_intersects
+st_length
+st_make_line
+st_makegeompoint
+st_makeline
+st_makepoint
+st_makepolygon
+st_npoints
+st_numpoints
+st_point
+st_pointn
+st_polygon
+st_setsrid
+st_srid
+st_startpoint
+st_transform
+st_within
+st_x
+st_xmax
+st_xmin
+st_y
+st_ymax
+st_ymin
+std
+stddev
+stddev_pop
+stddev_samp
+str_to_date
+str_to_timestamp
+str_to_year
+strcmp
+stream_has_data
+strftime
+string_agg
+string_to_h3
+strip_null_value
+sub_bitmap
+substr
+substr_utf8
+substring
+substring_utf8
+subtract
+subtract_days
+subtract_hours
+subtract_minutes
+subtract_months
+subtract_quarters
+subtract_seconds
+subtract_weeks
+subtract_years
+sum
+tan
+time_slot
+timestamp_diff
+timezone
+to_base64
+to_binary
+to_bitmap
+to_boolean
+to_centuries
+to_char
+to_date
+to_datetime
+to_day_of_month
+to_day_of_week
+to_day_of_year
+to_days
+to_decades
+to_decimal
+to_float32
+to_float64
+to_geometry
+to_hex
+to_hour
+to_hours
+to_int16
+to_int32
+to_int64
+to_int8
+to_interval
+to_iso_year
+to_jsonb_binary
+to_last_of_month
+to_last_of_quarter
+to_last_of_week
+to_last_of_year
+to_microsecond
+to_microseconds
+to_millennia
+to_milliseconds
+to_minute
+to_minutes
+to_monday
+to_month
+to_months
+to_next_friday
+to_next_monday
+to_next_saturday
+to_next_sunday
+to_next_thursday
+to_next_tuesday
+to_next_wednesday
+to_nullable
+to_number
+to_numeric
+to_previous_friday
+to_previous_monday
+to_previous_saturday
+to_previous_sunday
+to_previous_thursday
+to_previous_tuesday
+to_previous_wednesday
+to_quarter
+to_second
+to_seconds
+to_start_of_day
+to_start_of_fifteen_minutes
+to_start_of_five_minutes
+to_start_of_hour
+to_start_of_iso_week
+to_start_of_iso_year
+to_start_of_minute
+to_start_of_month
+to_start_of_quarter
+to_start_of_second
+to_start_of_ten_minutes
+to_start_of_week
+to_start_of_year
+to_string
+to_text
+to_timestamp
+to_uint16
+to_uint32
+to_uint64
+to_uint8
+to_unix_timestamp
+to_uuid
+to_varchar
+to_variant
+to_week_of_year
+to_weeks
+to_year
+to_years
+to_yyyymm
+to_yyyymmdd
+to_yyyymmddhh
+to_yyyymmddhhmmss
+today
+tomorrow
+translate
+trim
+trim_both
+trim_leading
+trim_trailing
+trunc
+truncate
+try_cast
+try_from_base64
+try_from_hex
+try_inet_aton
+try_inet_ntoa
+try_ipv4_num_to_string
+try_ipv4_string_to_num
+try_json_object
+try_json_object_keep_null
+try_object_construct
+try_object_construct_keep_null
+try_parse_json
+try_to_binary
+try_to_boolean
+try_to_date
+try_to_datetime
+try_to_decimal
+try_to_float32
+try_to_float64
+try_to_geometry
+try_to_int16
+try_to_int32
+try_to_int64
+try_to_int8
+try_to_interval
+try_to_number
+try_to_numeric
+try_to_string
+try_to_timestamp
+try_to_uint16
+try_to_uint32
+try_to_uint64
+try_to_uint8
+try_to_variant
+tuple
+typeof
+ucase
+unhex
+unicode
+uniq
+unnest
+upper
+user
+uuid
+var_pop
+var_samp
+variance_pop
+variance_samp
+vector_dims
+vector_norm
+version
+week
+weekofyear
+window_funnel
+xor
+xxhash32
+xxhash64
+year
+yearweek
+yesterday
diff --git a/tests/unit_tests/sql/dialects/databend_tests.py b/tests/unit_tests/sql/dialects/databend_tests.py
new file mode 100644
index 00000000000..5eac1bb549d
--- /dev/null
+++ b/tests/unit_tests/sql/dialects/databend_tests.py
@@ -0,0 +1,1553 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import re
+from pathlib import Path
+
+import pytest
+import sqlglot
+from sqlglot import exp
+from sqlglot.dialects.postgres import Postgres
+
+from superset.sql.dialects.databend import Databend
+
+
+def test_databend_dialect_registered() -> None:
+ from superset.sql.parse import SQLGLOT_DIALECTS
+
+ assert "databend" in SQLGLOT_DIALECTS
+ assert SQLGLOT_DIALECTS["databend"] is Databend
+
+
+def test_leading_settings_round_trips_unchanged() -> None:
+ """
+ A leading ``SETTINGS (...)`` timeout wrapper parses to a SELECT and is
+ re-emitted verbatim.
+ """
+ sql = 'SETTINGS (max_execute_time_in_seconds=300) SELECT "col" FROM t'
+ ast = sqlglot.parse_one(sql, Databend)
+
+ assert isinstance(ast, exp.Select)
+ assert ast.sql(dialect=Databend) == sql
+ assert [table.name for table in ast.find_all(exp.Table)] == ["t"]
+
+
+def test_leading_settings_multiple_values() -> None:
+ sql = "SETTINGS (a=1, b=2) SELECT 1"
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SETTINGS (a=1) SELECT 1 UNION ALL SELECT 2",
+ "SETTINGS (a=1) SELECT * FROM (SELECT id FROM u) AS sub",
+ "SETTINGS (a=1) WITH s AS (SELECT 1 AS one) SELECT * FROM s",
+ ],
+)
+def test_leading_settings_preserved_across_root_types(sql: str) -> None:
+ """
+ The wrapper must survive generation whatever the statement root is — not
+ only a bare SELECT. A UNION or a parenthesized subquery is re-emitted from
+ a generator hook other than ``select_sql``; emitting the settings only in
+ ``select_sql`` silently dropped the timeout guard on those.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT sample FROM t",
+ "SELECT a FROM sample",
+ "SELECT global FROM t",
+ "SELECT prewhere FROM t",
+ "SELECT sample, global, prewhere FROM t",
+ ],
+)
+def test_clickhouse_reserved_words_stay_valid_identifiers(sql: str) -> None:
+ """
+ GLOBAL / SAMPLE / PREWHERE are ClickHouse reserved words, but Databend accepts
+ them as identifiers. The Postgres base does too, so this no longer needs an
+ ``ID_VAR_TOKENS`` override — the assertion stays as a regression guard, because
+ a dataset whose column is named after one of them going unparseable is the very
+ "unqueryable virtual dataset" failure this dialect exists to prevent.
+ """
+ assert sqlglot.parse_one(sql, Databend) is not None
+
+
+def test_union_operator_still_parses() -> None:
+ """Restoring the reserved words above must not disturb the UNION operator."""
+ assert isinstance(
+ sqlglot.parse_one("SELECT 1 UNION ALL SELECT 2", Databend), exp.Union
+ )
+
+
+def test_bare_leading_settings_keyword_is_not_special_cased() -> None:
+ """
+ Only the parenthesized ``SETTINGS (...)`` wrapper is absorbed. A bare leading
+ ``SETTINGS`` keyword with no parentheses is retreated and left to the base
+ parser, which rejects it — this exercises the non-wrapper retreat path.
+ """
+ import sqlglot.errors
+
+ with pytest.raises(sqlglot.errors.ParseError):
+ sqlglot.parse_one("SETTINGS SELECT 1", Databend)
+
+
+def test_leading_settings_via_sqlscript_is_select() -> None:
+ from superset.sql.parse import SQLScript
+
+ sql = "SETTINGS (max_execute_time_in_seconds=300) SELECT col FROM t"
+ script = SQLScript(sql, "databend")
+
+ assert len(script.statements) == 1
+ assert not script.has_mutation()
+
+
+def test_leading_settings_survives_rls_regeneration() -> None:
+ """
+ User-visible path: when RLS is applied, virtual-dataset SQL is re-emitted
+ via ``SQLStatement.format`` (``superset/models/helpers.py``). The leading
+ ``SETTINGS`` must not be dropped there — for a UNION dataset too.
+ """
+ from superset.sql.parse import SQLStatement
+
+ sql = "SETTINGS (max_execute_time_in_seconds=300) SELECT 1 UNION ALL SELECT 2"
+ assert (
+ SQLStatement(sql, "databend")
+ .format()
+ .startswith("SETTINGS (max_execute_time_in_seconds=300)")
+ )
+
+
+def test_leading_settings_transparent_to_table_extraction() -> None:
+ """RLS depends on table extraction; the wrapper must not perturb it."""
+ from superset.sql.parse import SQLStatement
+
+ wrapped = SQLStatement("SETTINGS (a=1) SELECT c FROM myschema.t", "databend")
+ plain = SQLStatement("SELECT c FROM myschema.t", "databend")
+ extracted = {str(table) for table in wrapped.tables}
+ assert extracted == {str(table) for table in plain.tables} == {"myschema.t"}
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT 1",
+ "SELECT a, b FROM t WHERE c > 1",
+ 'SELECT COUNT(*) FROM "events" WHERE "type" = \'click\'',
+ "SELECT * FROM t SETTINGS max_threads = 1",
+ "SELECT `col` FROM `tbl`",
+ "SELECT * FROM a JOIN b ON a.id = b.id",
+ "SELECT DISTINCT x FROM t",
+ "SELECT x FROM t GROUP BY x HAVING COUNT(*) > 1",
+ "SELECT x FROM t ORDER BY x DESC LIMIT 10",
+ "WITH source AS (SELECT 1 AS one) SELECT * FROM source",
+ "SELECT * FROM (SELECT id FROM u) AS sub",
+ "SELECT CAST(a AS String) FROM t",
+ "INSERT INTO t VALUES (1)",
+ "ALTER TABLE foo ADD COLUMN bar INT",
+ "SELECT * FROM t WHERE name = 'O''Hara'",
+ ],
+)
+def test_no_regression_on_known_good_statements(sql: str) -> None:
+ """Statements that already parse under the generic dialect still parse."""
+ assert sqlglot.parse_one(sql, Databend) is not None
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT DATE_FORMAT(\"d\", '%m/%d')",
+ "SELECT DATE_FORMAT(DATE_TRUNC('week', \"d\"), '%m/%d')",
+ (
+ "SELECT CONCAT(DATE_FORMAT(DATE_TRUNC('week', \"d\"), '%m/%d'), ' - ', "
+ "DATE_FORMAT(DATE_TRUNC('week', \"d\") + INTERVAL '6' DAY, '%m/%d'))"
+ ),
+ ],
+)
+def test_date_format_is_not_rewritten_to_format_date_time(sql: str) -> None:
+ """
+ ClickHouse renders ``exp.TimeToStr`` as ``formatDateTime``, which Databend
+ rejects outright: "no function matches the given name: 'formatdatetime', do
+ you mean 'date_format'?". Inheriting that generation silently rewrote every
+ chart's ``DATE_FORMAT`` on the way to the server, breaking each one.
+ """
+ generated = sqlglot.parse_one(sql, Databend).sql(dialect=Databend)
+
+ assert "formatDateTime" not in generated
+ assert "DATE_FORMAT(" in generated
+
+
+def test_format_date_time_is_normalised_to_date_format() -> None:
+ """
+ An expression saved with the ClickHouse spelling — from a chart authored while
+ that rewrite was in effect — is healed on generation rather than passed through
+ to a server that does not accept it. Under the ClickHouse base this fell out of
+ the shared parse node; the Postgres base has no such node, so the dialect maps
+ ``formatDateTime`` explicitly. Both spellings take the same ``%``-style format
+ string, so it carries across untouched.
+ """
+ parsed = sqlglot.parse_one("SELECT formatDateTime(\"d\", '%m/%d')", Databend)
+ generated = parsed.sql(dialect=Databend)
+
+ assert generated == "SELECT DATE_FORMAT(\"d\", '%m/%d')"
+
+
+def test_date_format_generation_is_idempotent() -> None:
+ """Superset regenerates on every compile; the output must be a fixed point."""
+ once = sqlglot.parse_one(
+ "SELECT DATE_FORMAT(DATE_TRUNC('week', \"d\"), '%m/%d')", Databend
+ ).sql(dialect=Databend)
+
+ assert sqlglot.parse_one(once, Databend).sql(dialect=Databend) == once
+
+
+def test_date_format_survives_adhoc_column_sanitisation() -> None:
+ """
+ User-visible path: ``_process_sql_expression`` in ``superset/models/helpers``
+ puts every adhoc column and metric through ``sanitize_clause``, which parses
+ and *regenerates* the clause in the engine's dialect. That is where a chart's
+ stored ``DATE_FORMAT`` was turned into SQL Databend refuses.
+ """
+ from superset.sql.parse import sanitize_clause
+
+ sanitized = sanitize_clause(
+ "DATE_FORMAT(DATE_TRUNC('week', \"d\"), '%m/%d')", "databend"
+ )
+
+ assert "formatDateTime" not in sanitized
+ assert sanitized.startswith("DATE_FORMAT(")
+
+
+DATABEND_FUNCTION_CATALOGUE = {
+ line.strip().lower()
+ for line in (
+ Path(__file__)
+ .with_name("databend_function_catalogue.txt")
+ .read_text()
+ .splitlines()
+ )
+ if line.strip() and not line.startswith("#")
+}
+
+# Names the server resolves that SHOW FUNCTIONS does not list. The catalogue is a
+# sound positive oracle and an unsound negative one, so this is where the negative
+# direction gets corrected -- each entry run against Databend Query v1.2.790
+# before being added. COUNT_IF is the reason the set exists: reading its absence
+# as proof led to it being rewritten into a SUM(CASE ...) that answers NULL where
+# COUNT_IF answers 0.
+VERIFIED_PRESENT_BUT_UNLISTED = {
+ "array_apply",
+ "array_filter",
+ "count_if",
+}
+
+# Calls ClickHouse parses without checking and Databend rejects outright, so a
+# raise here is correct rather than a regression. ClickHouse renders an unknown
+# or over-supplied call as an anonymous pass-through where Postgres validates the
+# signature, which is the whole source of this list; it is not a judgement about
+# what Databend supports. Each was run against the server:
+# md5('a','b') -> no function matches signature `md5(String, String)`
+# date_part(NOW()) -> unexpected `NOW`, expecting DOW, ISOWEEK, ...
+# now(1, 2, 3) -> no function matches signature `now(UInt8, UInt8, UInt8)`
+# AND, OR and XOR are operators there, as DIV and RLIKE are, and are not
+# callable: xor(1) answers "missing lhs or rhs for the binary operator".
+CLICKHOUSE_ACCEPTS_UNVALIDATED = {
+ "and",
+ "date_part",
+ "extract",
+ "json_agg",
+ "md5",
+ "now",
+ "or",
+ "xor",
+}
+
+# SQL keywords and operator forms that are valid Databend syntax without being
+# catalogue entries. POSITION is deliberately absent: Databend accepts
+# ``POSITION(x IN y)`` but rejects ``POSITION(x, y)``, so it is checked by form.
+SYNTAX_NOT_IN_CATALOGUE = {
+ "case",
+ "select",
+ "in",
+}
+
+# Rewrites that still emit something Databend lacks, knowingly left alone: these
+# are Snowflake/T-SQL/BigQuery spellings or sqlglot's own canonical names
+# (``J_S_O_N_TABLE``, ``S_H_A2_DIGEST``), not things anyone writes in a Superset
+# adhoc metric against Databend, and several have no Databend equivalent to map
+# onto. Listed rather than silenced so a reviewer can see the whole residue.
+KNOWN_UNSUPPORTED = {
+ "APPLY",
+ "APPROXIMATE_JACCARD_INDEX",
+ "A_I_FORECAST",
+ "COLUMNS",
+ "CONVERT_TO_CHARSET",
+ "DATEFROMPARTS",
+ "DIV",
+ "DYNAMIC_IDENTIFIER",
+ "ENDSWITH",
+ "EXPLODING_GENERATE_SERIES",
+ "FARMFINGERPRINT64",
+ "GETBIT",
+ "ISINF",
+ "ISNAN",
+ "ISODOW",
+ "IS_ASCII",
+ "J_S_O_N_B_OBJECT_AGG",
+ "J_S_O_N_EXISTS",
+ "J_S_O_N_TABLE",
+ "M_L_FORECAST",
+ "NEXT_VALUE_FOR",
+ "OPEN_J_S_O_N",
+ "SPLIT_BY_STRING",
+ "STARTSWITH",
+ "S_H_A2_DIGEST",
+ "TIMEFROMPARTS",
+ "TIMESTAMPFROMPARTS",
+ "TIMESTAMPLTZFROMPARTS",
+ "TIMESTAMPTZFROMPARTS",
+ "TIMESTAMP_FROM_PARTS",
+ "TIME_FROM_PARTS",
+ "UNIX_SECONDS",
+ "UTC_TIME",
+ "X_M_L_TABLE",
+ "YEAROFWEEK",
+ "YEAROFWEEKISO",
+}
+
+# Postgres renders some rewrites as infix operators rather than calls, which a
+# catalogue lookup cannot see. Only spellings Databend has no form of are listed:
+# its parser rejects "~", "~*" and ILIKE outright, while the JSON operators
+# Postgres also emits (-> ->> #> #>> @> <@ @? @@ #- ? ?| ?&) are all accepted --
+# @> means JSON rather than array containment there, which is a semantic
+# difference this test is not able to judge.
+#
+# "~" and "~*" are load-bearing: deleting either regex override turns this sweep
+# red. ILIKE is not, and cannot be, because the sweep's population is
+# Parser.FUNCTIONS and ILIKE arrives as an operator rather than a call -- it is
+# here as a statement of what must never be emitted, with its own test carrying
+# the override. Said plainly so the next reader does not mistake it for cover it
+# is not giving.
+_OPERATORS_DATABEND_LACKS = re.compile(
+ r"(?<![<>!])(~\*|~)(?!=)|\bILIKE\b", re.IGNORECASE
+)
+
+_CALL = re.compile(r"([A-Za-z_][A-Za-z0-9_]*)\s*\(")
+_POSITION_IN = re.compile(r"POSITION\s*\([^()]*\sIN\s", re.IGNORECASE)
+
+
+def _names_databend_lacks(sql: str) -> list[str]:
+ """
+ Spellings in ``sql`` that Databend has no name or syntax for.
+
+ Function calls are checked against the catalogue. Operators are checked
+ against a short list instead, because a rewrite does not have to land on a
+ function call at all -- Postgres renders several of these as infix operators,
+ and a catalogue lookup never sees them.
+ """
+ unknown = []
+ for match in _CALL.finditer(sql):
+ name = match.group(1).lower()
+ if name == "position":
+ if not _POSITION_IN.match(sql[match.start() :]):
+ unknown.append(match.group(1))
+ elif (
+ name not in DATABEND_FUNCTION_CATALOGUE
+ and name not in VERIFIED_PRESENT_BUT_UNLISTED
+ and name not in SYNTAX_NOT_IN_CATALOGUE
+ ):
+ unknown.append(match.group(1))
+
+ unknown.extend(_OPERATORS_DATABEND_LACKS.findall(sql))
+ return unknown
+
+
+ARITIES = ("x", "x, y", "x, y, z")
+
+
+def _round_trips(function_name: str) -> list[tuple[str, str]]:
+ """
+ Every arity of ``function_name`` that parses, with what it regenerates to.
+
+ All of them, not the first that parses: ``DATE_DIFF(x, y)`` parses and
+ ``DATE_DIFF(x, y, z)`` is the form Databend actually uses, so stopping at the
+ first success hides whatever the three-argument call does.
+ """
+ results = []
+ for args in ARITIES:
+ try:
+ generated = sqlglot.transpile(
+ f"SELECT {function_name}({args})", read=Databend, write=Databend
+ )[0]
+ except Exception: # noqa: BLE001, S112 - wrong arity, try the next one
+ continue
+ results.append((args, generated[len("SELECT ") :]))
+ return results
+
+
+def _raises_where_clickhouse_did_not(function_name: str, args: str) -> bool:
+ """
+ Whether this dialect refuses a call the ClickHouse base accepted.
+
+ Swallowing the exception is what let ``to_char(x)`` and ``bit_and(a, b)``
+ through: both parsed before the re-base and raise now, and a sweep that skips
+ anything which fails to parse cannot see the failure the module docstring
+ calls the worse of the two, because it happens inside ``sanitize_clause``
+ rather than at the server.
+
+ A raise on its own means nothing -- most names refuse most arities -- so it
+ only counts when the dialect being replaced was happy with the same call.
+ """
+ statement = f"SELECT {function_name}({args})"
+ try:
+ sqlglot.transpile(statement, read=Databend, write=Databend)
+ except Exception: # noqa: BLE001, S110 - the raise is the measurement
+ pass
+ else:
+ return False
+
+ try:
+ sqlglot.transpile(statement, read="clickhouse", write="clickhouse")
+ except Exception: # noqa: BLE001 - both refuse it, so it is not a regression
+ return False
+
+ return True
+
+
+def _is_rewrite(function_name: str, generated: str) -> bool:
+ return generated.split("(")[0].strip().upper() != function_name.upper()
+
+
+def _call_arguments(generated: str) -> list[str] | None:
+ """The top-level argument list of ``generated``, or None if it is not one call."""
+ open_paren = generated.find("(")
+ if open_paren == -1 or not generated.endswith(")"):
+ return None
+
+ depth = 0
+ current = ""
+ arguments = []
+ for char in generated[open_paren:-1].removeprefix("("):
+ if char in "([":
+ depth += 1
+ elif char in ")]":
+ depth -= 1
+ if depth < 0: # the call closed early, so this is not a single call
+ return None
+ if char == "," and depth == 0:
+ arguments.append(current.strip())
+ current = ""
+ else:
+ current += char
+ arguments.append(current.strip())
+ return [argument for argument in arguments if argument]
+
+
+def test_no_transform_emits_a_function_databend_lacks() -> None:
+ """
+ Enumerate rather than hand-list. Every function name the parser recognises is
+ round-tripped, and any *rewrite* must land on a name Databend actually has.
+
+ The dialect began life based on ClickHouse purely to absorb the
+ ``SETTINGS (...)`` wrapper, and inherited that dialect's whole generation table
+ unreviewed: ``STDDEV`` emitted as ``stddevSamp``, ``SPLIT`` as
+ ``splitByString``, ``REGEXP_LIKE`` as ``match`` -- 44 such names in all, each
+ rejected outright by the server. A hand-written battery is what let them go
+ unnoticed, and it would not catch the next one a sqlglot bump introduces.
+ Anything new failing here needs a ``TRANSFORMS`` override, or a deliberate
+ entry in ``KNOWN_UNSUPPORTED``.
+ """
+ regressions = {}
+ for name in sorted(Databend.Parser.FUNCTIONS):
+ if name in KNOWN_UNSUPPORTED:
+ continue
+ for args, generated in _round_trips(name):
+ if not _is_rewrite(name, generated):
+ continue
+ if _names_databend_lacks(generated):
+ regressions[f"{name}({args})"] = generated
+
+ assert not regressions, (
+ "these round-trips emit a name Databend does not have: "
+ + "; ".join(f"{k} -> {v}" for k, v in sorted(regressions.items()))
+ )
+
+
+def test_a_kept_name_does_not_gain_or_reorder_arguments() -> None:
+ """
+ A name surviving a round-trip is not enough; the arguments have to survive too.
+
+ Checking only the name before the paren misses a rewrite that keeps the name
+ and changes the call. ``ARRAY_LENGTH(a)`` came back as ``ARRAY_LENGTH(a, 1)``
+ -- Databend's takes one argument and rejects that -- and it passed the name
+ check untouched.
+
+ Two changes are asserted on, because neither can ever be right: an argument
+ the caller did not write appearing, and the caller's arguments coming back in
+ a different order. Arguments being *dropped* is not asserted on, because this
+ enumeration probes one, two and three arguments against every name, so most
+ dropping is a fixed-arity function discarding a nonsense extra rather than a
+ defect. A rewrite that changes the name is skipped as well -- ``COUNT_IF(x)``
+ legitimately becomes a CASE expression -- leaving this to the name check and
+ the per-override tests.
+ """
+ placeholder = re.compile(r"\b([xyz])\b", re.IGNORECASE)
+ regressions = {}
+ for name in sorted(Databend.Parser.FUNCTIONS):
+ if name in KNOWN_UNSUPPORTED:
+ continue
+ # Only names Databend has: for anything else the shape is moot, since the
+ # call will not run there whatever its arguments look like.
+ if name.lower() not in DATABEND_FUNCTION_CATALOGUE:
+ continue
+ for args, generated in _round_trips(name):
+ if _is_rewrite(name, generated):
+ continue
+ written = _call_arguments(f"{name}({args})")
+ emitted = _call_arguments(generated)
+ if written is None or emitted is None:
+ continue
+
+ written_order = [f.lower() for f in placeholder.findall(" ".join(written))]
+ emitted_order = [f.lower() for f in placeholder.findall(" ".join(emitted))]
+ gained = len(emitted) > len(written)
+ reordered = emitted_order != written_order[: len(emitted_order)]
+ if gained or reordered:
+ regressions[f"{name}({args})"] = generated
+
+ assert not regressions, (
+ "these keep the function name but gain or reorder arguments: "
+ + "; ".join(f"{k} -> {v}" for k, v in sorted(regressions.items()))
+ )
+
+
+def test_known_unsupported_list_has_no_stale_entries() -> None:
+ """
+ Keep the escape hatch honest: an entry that no longer misbehaves -- because a
+ sqlglot release fixed it, or an override now covers it -- must be removed, or
+ the list slowly becomes somewhere real regressions can hide.
+ """
+ stale = []
+ for name in sorted(KNOWN_UNSUPPORTED):
+ if name not in Databend.Parser.FUNCTIONS:
+ continue
+ round_trips = _round_trips(name)
+ if not round_trips:
+ continue
+ if not any(
+ _is_rewrite(name, generated) and _names_databend_lacks(generated)
+ for _, generated in round_trips
+ ):
+ stale.append(name)
+
+ assert not stale, f"no longer unsupported, drop from KNOWN_UNSUPPORTED: {stale}"
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ ("ANY_VALUE(x)", "ANY(x)"),
+ ("APPROX_COUNT_DISTINCT(x)", "APPROX_COUNT_DISTINCT(x)"),
+ ("ARRAY_CONCAT(a, b)", "ARRAY_CONCAT(a, b)"),
+ ("ARRAY_CONTAINS(a, 1)", "ARRAY_CONTAINS(a, 1)"),
+ ("ARRAY_INTERSECTION(a, b)", "ARRAY_INTERSECTION(a, b)"),
+ ("CURRENT_DATE", "TODAY()"),
+ ("DAYOFMONTH(x)", "DAYOFMONTH(x)"),
+ ("DAYOFWEEK(x)", "DAYOFWEEK(x)"),
+ ("DAYOFYEAR(x)", "DAYOFYEAR(x)"),
+ ("JSON_EXTRACT(j, 'a')", "JSON_EXTRACT_PATH_TEXT(j, 'a')"),
+ ("MEDIAN(x)", "MEDIAN(x)"),
+ ("RAND()", "RAND()"),
+ ("REGEXP_LIKE(s, 'a')", "REGEXP_LIKE(s, 'a')"),
+ ("SHA2(x, 256)", "SHA2(x, 256)"),
+ ("WEEKOFYEAR(x)", "WEEKOFYEAR(x)"),
+ ],
+)
+def test_transform_overrides_emit_databend_spellings(
+ written: str, expected: str
+) -> None:
+ """
+ One case per name-level ``TRANSFORMS`` override, with the exact spelling
+ asserted. The overrides that reshape rather than rename -- ``Array``,
+ ``IntDiv``, ``ParameterizedAgg``, ``JSONExtract`` -- have their own tests
+ below, since a spelling table says nothing useful about them.
+
+ Each replacement was run against a live Databend (Query v1.2.790) before being
+ chosen.
+ Left to itself the Postgres base emits ``ANY_VALUE``, ``APPROX_DISTINCT``,
+ ``ARRAY_CAT``, ``= ANY(...)``, ``CURRENT_DATE``, ``DAY_OF_MONTH``,
+ ``DAY_OF_WEEK``, ``DAY_OF_YEAR``, ``JSON_EXTRACT_PATH``, ``PERCENTILE_CONT``,
+ ``RANDOM``, the ``~`` operator, ``SHA256`` and ``WEEK_OF_YEAR`` -- none of
+ which that server accepts.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {expected}"
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT * FROM t SETTINGS max_threads = 1",
+ "SELECT a FROM t WHERE b > 1 SETTINGS max_threads = 1",
+ ],
+)
+def test_trailing_settings_round_trips(sql: str) -> None:
+ """
+ Databend accepts ClickHouse's *trailing* ``SETTINGS k = v`` as well as the
+ leading parenthesised wrapper. Postgres has no such modifier, so re-basing had
+ to carry it across explicitly -- including keeping SETTINGS out of
+ ``TABLE_ALIAS_TOKENS``, without which ``FROM t SETTINGS ...`` binds it as the
+ table's alias and the assignment after it fails to parse.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+def test_postgres_style_casts_are_preserved() -> None:
+ """
+ The Postgres base renders casts as ``TEXT`` / ``BIGINT`` / ``DOUBLE PRECISION``
+ where the ClickHouse base emitted ``Nullable(String)`` and friends. Databend
+ accepts both (verified live), so the re-base leaves CAST rendering intact --
+ the largest single surface the base change could have broken.
+ """
+ generated = sqlglot.transpile(
+ "SELECT CAST(a AS TEXT), CAST(b AS BIGINT), CAST(c AS DOUBLE PRECISION)",
+ read=Databend,
+ write=Databend,
+ )[0]
+
+ assert "Nullable" not in generated
+ assert "TEXT" in generated
+ assert "BIGINT" in generated
+
+
+# Databend's accepted type names, as the server itself lists them when handed one
+# it does not know. Kept beside the function catalogue and used the same way.
+#
+# This is the parser's offer list, which is a little wider than what it will then
+# take: NUMERIC is named here but CAST(NULL AS NUMERIC(10, 2)) is rejected. The
+# dialect emits DECIMAL, so nothing depends on it -- the list is what may be
+# written, not a promise that every entry resolves.
+DATABEND_TYPE_NAMES = {
+ "ARRAY",
+ "BIGINT",
+ "BINARY",
+ "BITMAP",
+ "BLOB",
+ "BOOL",
+ "BOOLEAN",
+ "CHAR",
+ "CHARACTER",
+ "DATE",
+ "DATETIME",
+ "DECIMAL",
+ "DOUBLE",
+ "FLOAT",
+ "FLOAT32",
+ "FLOAT64",
+ "GEOGRAPHY",
+ "GEOMETRY",
+ "INT",
+ "INT16",
+ "INT32",
+ "INT64",
+ "INT8",
+ "INTEGER",
+ "INTERVAL",
+ "JSON",
+ "LONGBLOB",
+ "MAP",
+ "MEDIUMBLOB",
+ "NULLABLE",
+ "NUMERIC",
+ "REAL",
+ "SIGNED",
+ "SMALLINT",
+ "STRING",
+ "TEXT",
+ "TIMESTAMP",
+ "TINYBLOB",
+ "TINYINT",
+ "TUPLE",
+ "UINT16",
+ "UINT32",
+ "UINT64",
+ "UINT8",
+ "UNSIGNED",
+ "VARBINARY",
+ "VARCHAR",
+ "VARIANT",
+ "VECTOR",
+}
+
+# Names that are only a type once given arguments, so a bare CAST to them is not
+# a thing to sweep: ARRAY, MAP, TUPLE and NULLABLE all take a member type, and
+# the parameterised spellings are swept explicitly instead. SIGNED and UNSIGNED
+# are cast modifiers rather than types.
+TYPES_NEEDING_ARGUMENTS = {"ARRAY", "MAP", "NULLABLE", "SIGNED", "TUPLE", "UNSIGNED"}
+
+# Words that appear inside a rendered type without being type names of their own.
+RENDERED_TYPE_KEYWORDS = {"PRECISION"}
+
+# Written type name -> what the dialect must emit. Every entry was executed as
+# CAST(NULL AS <emitted>) on a live Databend (Query v1.2.790).
+TYPE_RENDERINGS = [
+ ("ARRAY(INT32)", "ARRAY(INT)"),
+ ("ARRAY(STRING)", "ARRAY(TEXT)"),
+ ("ARRAY(ARRAY(INT32))", "ARRAY(ARRAY(INT))"),
+ ("MAP(STRING, INT32)", "MAP(TEXT, INT)"),
+ ("MAP(STRING, VARIANT)", "MAP(TEXT, VARIANT)"),
+ ("TUPLE(INT32, STRING)", "TUPLE(INT32, STRING)"),
+ ("BINARY", "BINARY"),
+ ("VARBINARY", "VARBINARY"),
+ ("TINYINT", "TINYINT"),
+ ("INT8", "TINYINT"),
+ ("FLOAT", "FLOAT"),
+ ("VARIANT", "VARIANT"),
+ ("BITMAP", "BITMAP"),
+ ("GEOMETRY", "GEOMETRY"),
+]
+
+
+@pytest.mark.parametrize("written,expected", TYPE_RENDERINGS)
+def test_casts_use_databend_type_spellings(written: str, expected: str) -> None:
+ """
+ A cast must name a type Databend has, in the syntax Databend takes.
+
+ Postgres writes containers as ``INT[]`` and ``MAP<TEXT, INT>`` and binary as
+ ``BYTEA``; the server rejects all three outright. ARRAY, MAP and VARIANT are
+ Databend's headline complex types and a cast to one is ordinary in an adhoc
+ column, so this is not an edge.
+
+ Two entries are width, not syntax. Postgres reads ``INT8`` as eight bytes
+ where Databend reads one, and ``FLOAT`` as double precision where Databend
+ reads 32-bit, so both silently widen: ``CAST(300 AS INT8)`` overflows on the
+ server but ``CAST(300 AS BIGINT)`` returns 300, which is a query that failed
+ now succeeding with a different result.
+ """
+ generated = sqlglot.transpile(
+ f"SELECT CAST(x AS {written})", read=Databend, write=Databend
+ )[0]
+ assert generated == f"SELECT CAST(x AS {expected})"
+
+
+def test_no_cast_emits_a_type_databend_lacks() -> None:
+ """
+ The type-name equivalent of the function sweep, and the same lesson.
+
+ The function guard could not see this surface at all: it probes ``NAME(...)``
+ calls, so a type name never entered it, and ``ARRAY``, ``MAP`` and ``BINARY``
+ were all being rendered into spellings the server rejects while every test
+ passed. Sweeping the type list the way the function list is swept would have
+ caught all three without anyone having to think of arrays.
+
+ It sweeps the catalogue, not the fourteen pairs asserted above: iterating
+ those made this a second reading of literals already pinned character for
+ character one test earlier, so it could not fail while that one passed. That
+ is the same shape of mistake as the guard checking names rather than
+ round-trips, one surface further along.
+ """
+ unknown = {}
+ for written in sorted(DATABEND_TYPE_NAMES - TYPES_NEEDING_ARGUMENTS) + [
+ # The parameterised spellings too: the container types are exactly where
+ # the rendering went wrong, and a bare ARRAY says nothing about ARRAY(T).
+ "ARRAY(INT32)",
+ "ARRAY(ARRAY(INT32))",
+ "MAP(STRING, INT32)",
+ "TUPLE(INT32, STRING)",
+ "DECIMAL(10, 2)",
+ "VARCHAR(10)",
+ "VECTOR(3)",
+ "NULLABLE(INT32)",
+ ]:
+ generated = sqlglot.transpile(
+ f"SELECT CAST(x AS {written})", read=Databend, write=Databend
+ )[0]
+ emitted = generated[len("SELECT CAST(x AS ") : -1]
+
+ # The punctuation matters as much as the words. Databend writes every
+ # container type with parentheses, so a bracket or an angle means the
+ # Postgres spelling survived -- and checking names alone missed exactly
+ # that, since every word of INT[] and MAP<TEXT, INT> is a real type name.
+ if set("[]<>") & set(emitted):
+ unknown[written] = emitted
+
+ for name in re.findall(r"[A-Za-z_][A-Za-z0-9_]*", emitted):
+ if name.upper() not in DATABEND_TYPE_NAMES | RENDERED_TYPE_KEYWORDS:
+ unknown[written] = emitted
+
+ assert not unknown, "these casts name a type Databend does not have: " + "; ".join(
+ f"{k} -> {v}" for k, v in sorted(unknown.items())
+ )
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ ("JSON_EXTRACT(j, '$.a')", "JSON_EXTRACT_PATH_TEXT(j, 'a')"),
+ ("JSON_EXTRACT(j, '$.a.b')", "JSON_EXTRACT_PATH_TEXT(j, 'a.b')"),
+ ("JSON_EXTRACT(j, '$.a.b.c')", "JSON_EXTRACT_PATH_TEXT(j, 'a.b.c')"),
+ ("JSON_EXTRACT(j, '$.a[0].b')", "JSON_EXTRACT_PATH_TEXT(j, 'a[0].b')"),
+ ("JSON_EXTRACT_SCALAR(j, '$.a.b')", "JSON_EXTRACT_PATH_TEXT(j, 'a.b')"),
+ ("JSON_EXTRACT_PATH_TEXT(j, 'a.b')", "JSON_EXTRACT_PATH_TEXT(j, 'a.b')"),
+ ("j -> 'a'", "JSON_EXTRACT_PATH_TEXT(j, 'a')"),
+ ("j ->> 'a'", "JSON_EXTRACT_PATH_TEXT(j, 'a')"),
+ ],
+)
+def test_json_path_segments_are_not_collapsed(written: str, expected: str) -> None:
+ """
+ A multi-segment JSON path must stay multi-segment.
+
+ Postgres explodes a path into one argument per segment,
+ ``JSON_EXTRACT_PATH(j, 'a', 'b')``, while Databend takes exactly two
+ arguments with the path as one dotted string, ``JSON_EXTRACT_PATH_TEXT(j,
+ 'a.b')``. Renaming the function without rebuilding the path concatenates the
+ segments into a single key -- ``'a.b'`` became ``'ab'`` -- which is a field
+ that does not exist, so the server returns NULL instead of raising. Nested
+ JSON access is ordinary in adhoc columns, and this failure is silent, so it
+ is asserted per path shape rather than on one representative case.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {expected}"
+
+
+@pytest.mark.parametrize(
+ "path",
+ [
+ "a..b", # recursive descent
+ "a[?(@.x)]", # filter selector
+ "a[1,2]", # union
+ "$..b",
+ "$.a[?(@.x)]",
+ ],
+)
+def test_json_path_segments_that_cannot_be_flattened_are_not_dropped(path: str) -> None:
+ """
+ A path Databend cannot express must fail, not quietly lose a segment.
+
+ Databend's dotted path syntax has no way to write recursive descent, a filter
+ selector or a union, and these parse into segments this dialect cannot render.
+ Skipping them leaves a shorter path that is still valid SQL -- ``'a..b'``
+ becomes ``'a'`` -- so the server reads a different field and returns NULL,
+ which is the same silent wrongness that flattening the whole path caused.
+ Note the unrooted spellings: ``$..b`` is rejected by sqlglot before generation,
+ but ``a..b`` reaches this dialect.
+ """
+ with pytest.raises(ValueError, match="Unsupported expression type"):
+ sqlglot.transpile(
+ f"SELECT JSON_EXTRACT(j, '{path}')", read=Databend, write=Databend
+ )
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT DATE_ADD(DAY, 1, ds) FROM t",
+ "SELECT DATE_SUB(DAY, 1, ds) FROM t",
+ "SELECT DATE_DIFF(DAY, a, b) FROM t",
+ "SELECT DATEDIFF(DAY, a, b) FROM t",
+ ],
+)
+def test_unit_first_date_functions_round_trip_verbatim(sql: str) -> None:
+ """
+ Databend's date arithmetic puts the unit first; Postgres reads it last.
+
+ Parsing ``DATE_ADD(DAY, 1, ds)`` as Postgres does takes ``DAY`` for the value
+ and ``ds`` for the unit, and regenerates ``DAY + INTERVAL '1 DS'`` -- a
+ reference to a column named DAY and an interval in a unit named after the
+ date column. ``DATE_DIFF(DAY, a, b)`` fared worse, dropping its third
+ argument entirely and comparing the literal unit against a column. Neither
+ changes the function name, so a name-only check waves both through; both are
+ ordinary in a time-series chart.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT * FROM t QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1",
+ "SELECT a FROM t QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1",
+ ],
+)
+def test_qualify_is_not_desugared(sql: str) -> None:
+ """
+ Databend has QUALIFY; Postgres does not, and its Select transform chain
+ rewrites the clause into a subquery carrying a generated ``_w`` column. On an
+ explicit column list that is only verbose, but ``SELECT *`` then lifts ``_w``
+ into the result set, so the query returns a column nobody asked for. This is
+ not confined to SQL Lab: ``Database.apply_limit_to_sql`` regenerates through
+ this dialect, as does virtual-dataset column inference.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ ("[1, 2, 3]", "[1, 2, 3]"),
+ ("ARRAY_CONTAINS([1, 2], x)", "ARRAY_CONTAINS([1, 2], x)"),
+ ],
+)
+def test_array_literals_keep_databend_syntax(written: str, expected: str) -> None:
+ """
+ Databend writes an array literal ``[1, 2, 3]``. Postgres wraps it in its own
+ constructor, ``ARRAY[1, 2, 3]``, which the server rejects outright:
+ "unexpected `[`, expecting `(`".
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {expected}"
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ ("ARRAY_FILTER(a, x -> x > 1)", "ARRAY_FILTER(a, x -> x > 1)"),
+ ("FILTER(a, x -> x > 1)", "ARRAY_FILTER(a, x -> x > 1)"),
+ (
+ "ARRAY_ANY(a, x -> x > 1)",
+ "(ARRAY_LENGTH(a) = 0 OR ARRAY_LENGTH(ARRAY_FILTER(a, x -> x > 1)) <> 0)",
+ ),
+ ],
+)
+def test_a_lambda_over_an_array_stays_a_lambda(written: str, expected: str) -> None:
+ """
+ Postgres has no lambda, so it rewrites one into ``ARRAY(SELECT ... FROM
+ UNNEST(a) WHERE ...)``. Databend has no such constructor, and the array
+ override then bracketed the subquery, giving ``[SELECT x FROM UNNEST(a) ...]``
+ -- valid in no dialect at all.
+
+ ARRAY_ANY is assembled from the same node and so inherited the subquery
+ without appearing anywhere by name; it is the reason this is a transform
+ rather than a name left unparsed, which would have fixed only what was
+ written literally. Both renderings above were run against the server.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {expected}"
+
+
+def test_try_is_not_silently_removed() -> None:
+ """
+ Postgres drops the wrapper and leaves the expression bare, which is the one
+ rewrite that turns a query answering NULL into a query that raises. Databend
+ has no general TRY -- only ``try_to_date``, ``try_cast`` and the rest of that
+ family -- so the call is left as written for the server to refuse by name.
+ """
+ generated = sqlglot.transpile("SELECT TRY(a / b)", read=Databend, write=Databend)[0]
+ assert generated == "SELECT TRY(a / b)"
+
+
+@pytest.mark.parametrize(
+ "written",
+ [
+ "{'k': 1}",
+ "{'k': 1, 'j': 2}",
+ "{'k': {'j': 2}}",
+ "{'k': [1, 2]}",
+ "{}",
+ # The value may be an extraction even though the key's colon is not.
+ "{'k': JSON_EXTRACT_PATH_TEXT(v, 'a')}",
+ ],
+)
+def test_object_literals_survive_the_colon_rule(written: str) -> None:
+ """
+ Databend spells a path extraction ``v:a`` and an object literal ``{'k': 1}``
+ with the same character, and the extraction rule wins by default. The literal
+ came back as ``STRUCT(JSON_PATH_QUERY_FIRST('k', '1'))``: an object rewritten
+ into a lookup on a different value, which the server accepts and answers
+ differently rather than refusing.
+
+ Every form here was run against the server; the last is the reason the key
+ alone is read with the rule suspended rather than the whole element.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {written}"
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ ("STR_TO_DATE(s, '%Y-%m-%d')", "TO_DATE(s, 'YYYY-MM-DD')"),
+ (
+ "STR_TO_DATE(s, '%Y-%m-%d %H:%M:%S')",
+ "TO_DATE(s, 'YYYY-MM-DD HH24:MI:SS')",
+ ),
+ ("STR_TO_TIME(s, '%Y-%m-%d')", "TO_TIMESTAMP(s, 'YYYY-MM-DD')"),
+ (
+ "TIME_TO_STR(d, '%Y-%m-%d %H:%M:%S')",
+ "TO_CHAR(d, 'YYYY-MM-DD HH24:MI:SS')",
+ ),
+ # DATE_FORMAT is Databend's own, and its %-style format is left alone.
+ ("DATE_FORMAT(d, '%H:%M')", "DATE_FORMAT(d, '%H:%M')"),
+ ],
+)
+def test_format_strings_are_translated_not_carried(written: str, expected: str) -> None:
+ """
+ Databend takes both the %-style and the Oracle-style spelling, and the two
+ agree on the pair that is easy to get wrong -- ``%M`` and ``MI`` are minutes,
+ ``%m`` and ``MM`` are months. All of the above were run against the server.
+
+ This is worth pinning because no sweep can see it: the name is checked, the
+ argument count is checked, and a format string translated into nonsense is
+ still one string argument in the same position. The ClickHouse base dropped
+ the format outright -- ``TO_CHAR(d, 'YYYY-MM-DD')`` became
+ ``CAST(d AS Nullable(String))``, the default rendering, with the requested
+ format gone.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {expected}"
+
+
+@pytest.mark.parametrize(
+ "written,quoted",
+ [
+ ("SELECT 2024_total FROM t", 'SELECT "2024_total" FROM t'),
+ ("SELECT 3d FROM t", 'SELECT "3d" FROM t'),
+ ],
+)
+def test_an_unquoted_leading_digit_is_a_number_here_as_on_the_server(
+ written: str, quoted: str
+) -> None:
+ """
+ A name beginning with a digit splits into a number and an alias, which looks
+ like the digit-separator corruption in reverse and is not: Databend's own
+ tokenizer reads the leading digit as a number too, separator and all --
+ ``unexpected `2024_``` -- so a column named this way has to be quoted there
+ as well. The ClickHouse base kept it as one identifier, which is what
+ diverged.
+
+ The shape is asserted rather than the text, because sqlglot 30 renders the
+ number ``2024`` where 28 renders ``2024_``, and both are the same value.
+ """
+ alias = sqlglot.parse_one(written, Databend).selects[0]
+ assert isinstance(alias, exp.Alias)
+ assert isinstance(alias.this, exp.Literal)
+
+ assert sqlglot.transpile(quoted, read=Databend, write=Databend)[0] == quoted
+
+
+def test_nothing_new_hides_in_the_dropped_select_chain() -> None:
+ """
+ ``exp.Select`` is dropped from Postgres's ``TRANSFORMS`` wholesale, so
+ anything a future sqlglot adds to that chain is discarded here with no sweep
+ able to notice -- it is neither a name nor a type. Today the chain desugars
+ QUALIFY and SEMI/ANTI joins, both of which Databend supports natively, and
+ that is the entire reason dropping it is safe.
+
+ The chain is reached through the closure because sqlglot exposes it no other
+ way. If that shape changes this raises rather than passing quietly, which is
+ the wanted outcome: the assumption needs re-checking either way.
+ """
+ chain = Postgres.Generator.TRANSFORMS[exp.Select]
+ preprocessors = next(
+ cell.cell_contents
+ for cell in chain.__closure__
+ if isinstance(cell.cell_contents, list)
+ )
+
+ assert [step.__name__ for step in preprocessors] == [
+ "eliminate_semi_and_anti_joins",
+ "eliminate_qualify",
+ ]
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ ("a ILIKE '%x%'", "LOWER(a) LIKE LOWER('%x%')"),
+ ("NOT a ILIKE '%x%'", "NOT LOWER(a) LIKE LOWER('%x%')"),
+ ],
+)
+def test_ilike_is_lowered_the_way_the_engine_spec_lowers_it(
+ written: str, expected: str
+) -> None:
+ """
+ Databend has no ILIKE operator -- ``'Hello' ILIKE '%ell%'`` is a syntax error
+ -- and ``superset/db_engine_specs/databend.py`` already patches
+ ``DatabendCompiler`` to emit ``LOWER(x) LIKE LOWER(y)``. Passing ILIKE
+ through left the two halves of Superset disagreeing about the same server.
+
+ An operator is the shape the catalogue sweep cannot see, since it looks for
+ ``NAME(``; ILIKE joins ``~`` and ``~*`` in the operator list beside it.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {expected}"
+
+
+@pytest.mark.parametrize(
+ "written",
+ [
+ "CONVERT_TIMEZONE('America/Los_Angeles', ts)",
+ "CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', ts)",
+ ],
+)
+def test_convert_timezone_keeps_databends_spelling(written: str) -> None:
+ """
+ Databend's CONVERT_TIMEZONE takes the zone and the timestamp; there is no
+ three-argument form. Postgres turns either into ``AT TIME ZONE``, which the
+ server rejects -- "unexpected `TIME`" -- so the two-argument call that works
+ was broken and the three-argument one stopped failing by name.
+
+ A rewrite into *syntax* is the other thing the name sweep cannot see: the
+ output has no call name in it at all.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == f"SELECT {written}"
+
+
+def test_try_cast_keeps_its_try_semantics() -> None:
+ """
+ Postgres has no TRY_CAST and degrades it to CAST, so a column that returned
+ NULLs for unparseable values starts raising instead. Databend has ``try_cast``.
+ """
+ generated = sqlglot.transpile(
+ "SELECT TRY_CAST(a AS INT)", read=Databend, write=Databend
+ )[0]
+ assert generated == "SELECT TRY_CAST(a AS INT)"
+
+
+@pytest.mark.parametrize("written", ["REGEXP_I_LIKE(s, 'a')", "s ~* 'a'"])
+def test_case_insensitive_regex_does_not_become_an_operator(written: str) -> None:
+ """
+ Postgres renders this as ``x ~* y``. Databend has neither ``~`` nor ``~*`` --
+ its parser rejects both, listing the operators it does take -- and expects the
+ flags as a third argument to REGEXP_LIKE instead.
+
+ An operator is the one shape the catalogue check cannot see, since it looks
+ for ``NAME(``; this and the ``~`` behind the ``RegexpLike`` override are why
+ the check now looks for those two operators by hand as well.
+ """
+ generated = sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ assert generated == "SELECT REGEXP_LIKE(s, 'a', 'i')"
+
+
+def test_array_length_keeps_its_single_argument() -> None:
+ """
+ Postgres emits ``ARRAY_LENGTH(a, 1)``, naming the dimension to measure.
+ Databend's takes the array alone and rejects the second argument: "no
+ function matches signature `length(Array(UInt8), UInt8)`".
+ """
+ generated = sqlglot.transpile(
+ "SELECT ARRAY_LENGTH(a)", read=Databend, write=Databend
+ )[0]
+ assert generated == "SELECT ARRAY_LENGTH(a)"
+
+
+@pytest.mark.parametrize(
+ "written",
+ [
+ "QUANTILE(0.5)(x)",
+ "QUANTILE(0.9)(x)",
+ "QUANTILE(x)",
+ # Several levels, and several arguments: both are valid Databend.
+ "QUANTILE(0.5, 0.9)(x)",
+ "QUANTILE(0.5)(x, y)",
+ # The rest of the family, all in the checked-in catalogue.
+ "quantile_cont(0.5)(x)",
+ "quantile_disc(0.5)(x)",
+ "quantile_tdigest(0.5)(x)",
+ "quantile_tdigest_weighted(0.5)(x, y)",
+ "histogram(5)(x)",
+ ],
+)
+def test_parametric_aggregates_round_trip_verbatim(written: str) -> None:
+ """
+ Databend writes parametric aggregates ``NAME(params)(args)``, which Postgres
+ has no syntax for: it reads the first list as the arguments and then takes
+ the second for an alias, so ``histogram(5)(a)`` became ``HISTOGRAM(5) AS (a)``
+ and the server rejected it. The name survives that rewrite and only the
+ arguments move, so it is invisible to a name check.
+
+ Handled by shape rather than by name -- the family is the whole point, and
+ a per-name hook is what made ``QUANTILE`` work while its four neighbours in
+ the catalogue stayed broken. Sweeping every catalogue name through this form
+ finds none that regress and 503 that the ClickHouse base mangled.
+ """
+ assert (
+ sqlglot.transpile(f"SELECT {written}", read=Databend, write=Databend)[0]
+ == f"SELECT {written}"
+ )
+
+
+def test_json_path_that_is_not_a_literal_path_falls_back() -> None:
+ """
+ A path given as an expression cannot be flattened into a dotted string, so it
+ goes to ``JSON_PATH_QUERY_FIRST``, which accepts a path argument directly.
+ """
+ generated = sqlglot.transpile(
+ "SELECT JSON_EXTRACT(j, path_col)", read=Databend, write=Databend
+ )[0]
+ assert generated == "SELECT JSON_PATH_QUERY_FIRST(j, path_col)"
+
+
+@pytest.mark.parametrize(
+ "sql,expected",
+ [
+ ("SELECT * FROM t settings", "SELECT * FROM t AS settings"),
+ ("SELECT * FROM t AS settings", "SELECT * FROM t AS settings"),
+ (
+ "SELECT * FROM (SELECT 1 AS x) settings",
+ "SELECT * FROM (SELECT 1 AS x) AS settings",
+ ),
+ (
+ "SELECT a.x FROM t settings JOIN u ON settings.x = u.x",
+ "SELECT a.x FROM t AS settings JOIN u ON settings.x = u.x",
+ ),
+ (
+ "SELECT * FROM t SETTINGS max_threads = 1",
+ "SELECT * FROM t SETTINGS max_threads = 1",
+ ),
+ ],
+)
+def test_settings_works_as_both_clause_and_table_alias(sql: str, expected: str) -> None:
+ """
+ ``settings`` is a plausible table name, so a bare alias must survive.
+
+ The trailing ``SETTINGS k = v`` clause and an implicit alias both follow a
+ table, and only the lookahead for ``=`` tells them apart. Dropping SETTINGS
+ from ``TABLE_ALIAS_TOKENS`` to make the clause parse discarded the alias
+ without error instead, leaving any qualified reference to it dangling.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == expected
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SETTINGS (max_execute_time_in_seconds=300) SELECT 1",
+ "SETTINGS (a=1, b=2) SELECT 1",
+ "SETTINGS (a='x') SELECT 1",
+ "SETTINGS (a=-1) SELECT 1",
+ "SETTINGS (a=TRUE) SELECT 1",
+ ],
+)
+def test_constant_leading_settings_are_absorbed(sql: str) -> None:
+ """Databend's own setting values are constants, and those still round-trip."""
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+def test_leading_settings_rejects_non_constant_values() -> None:
+ """
+ Only constants may be absorbed, so nothing can hide in the wrapper.
+
+ The clause is re-emitted from source text rather than regenerated from the
+ tree, so anything the parser swallowed but did not expose as tree nodes would
+ be invisible to the table extraction behind RLS and the denylist, yet still
+ be forwarded to the server verbatim. Databend rejects a non-constant here
+ anyway ("value must be constant value"), so refusing to absorb it costs
+ nothing and keeps the tree an honest account of the statement.
+ """
+ import sqlglot.errors
+
+ for sql in [
+ # The value side, which was already refused.
+ "SETTINGS (a = (SELECT secret FROM sensitive)) SELECT 1 FROM public_table",
+ # The key side, which was not. This was absorbed and re-emitted with the
+ # subquery intact while the tree reported one table, which is precisely
+ # what the docstring above says cannot happen.
+ "SETTINGS ((SELECT secret FROM sensitive) = 1) SELECT 1 FROM public_table",
+ "SETTINGS (f(g) = 1) SELECT 1 FROM public_table",
+ # One good pair and one bad one is still bad.
+ "SETTINGS (a = 1, (SELECT 1 FROM sensitive) = 2) SELECT 1 FROM public_table",
+ ]:
+ with pytest.raises(sqlglot.errors.ParseError):
+ sqlglot.parse_one(sql, Databend)
+
+
+def test_nothing_absorbed_is_missing_from_the_tree() -> None:
+ """
+ State the property directly rather than by listing the shapes that break it.
+
+ Every table named anywhere in the re-emitted statement has to be reachable
+ through the tree, or RLS and the denylist are reading a different query from
+ the one the server runs.
+ """
+ sql = "SETTINGS (max_execute_time_in_seconds=300) SELECT a FROM t JOIN u ON a = b"
+ parsed = sqlglot.parse_one(sql, Databend)
+
+ assert parsed.sql(dialect=Databend) == sql
+ assert sorted(t.name for t in parsed.find_all(exp.Table)) == ["t", "u"]
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ # A stage is addressed with @, a positional column with $. Postgres
+ # renders both with $ and ClickHouse both with @, so a single token turns
+ # one into the other -- and @1 is a string constant on the server, so
+ # choosing @ returned constants where columns were asked for.
+ "SELECT $1, $2 FROM @mystage",
+ "SELECT * FROM @mystage",
+ "SELECT $1 FROM t",
+ "SELECT @var",
+ ],
+)
+def test_stage_and_positional_prefixes_are_distinguished(sql: str) -> None:
+ """
+ ``@name`` and ``$n`` both parse to a parameter, and they are not the same
+ thing: ``SELECT $1, $2 FROM @mystage/data.csv`` reads two columns out of a
+ staged file. Rendering them with one prefix compiles, runs, and answers with
+ the wrong values -- no error, which is the class this dialect exists to close.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ "SELECT quantile(0.5)(a) OVER (PARTITION BY b) FROM t",
+ "SELECT histogram(5)(a) OVER (PARTITION BY b) FROM t",
+ "SELECT quantile(0.5)(a) OVER (ORDER BY b) FROM t",
+ ],
+)
+def test_parametric_aggregates_take_a_window_clause(sql: str) -> None:
+ """
+ Both halves are valid Databend and they compose. Only what the function
+ parser returns is offered a window, so building the aggregate and handing it
+ straight back left this raising inside ``sanitize_clause`` -- before the
+ server saw it at all, which is the harder of the two failures.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ # Lexical settings, each verified on the server. A wrong reading here is
+ # silent: the literal still parses, as something else.
+ ("SELECT 0x1A", "SELECT 0x1A"),
+ (r"SELECT 'quote\'s'", "SELECT 'quote''s'"),
+ ("SELECT 7 DIV 2", "SELECT 7 DIV 2"),
+ ("SELECT a DIV b FROM t", "SELECT a DIV b FROM t"),
+ ("SELECT COUNT(DISTINCT a, b) FROM t", "SELECT COUNT(DISTINCT a, b) FROM t"),
+ ("COPY INTO t FROM 's3://x'", "COPY INTO t FROM 's3://x'"),
+ ("SELECT j:a", "SELECT JSON_EXTRACT_PATH_TEXT(j, 'a')"),
+ ("SELECT j:a:b", "SELECT JSON_EXTRACT_PATH_TEXT(j, 'a.b')"),
+ ("SELECT CAST(x AS INT8)", "SELECT CAST(x AS TINYINT)"),
+ ("SELECT CAST(x AS FLOAT)", "SELECT CAST(x AS FLOAT)"),
+ (
+ "SELECT * FROM t SEMI JOIN u ON t.id = u.id",
+ "SELECT * FROM t SEMI JOIN u ON t.id = u.id",
+ ),
+ (
+ "SELECT * FROM t ANTI JOIN u ON t.id = u.id",
+ "SELECT * FROM t ANTI JOIN u ON t.id = u.id",
+ ),
+ ],
+)
+def test_lexical_and_rendering_settings(written: str, expected: str) -> None:
+ """
+ One case per setting where the two bases disagree and Databend follows
+ ClickHouse. These arrived as class attributes, which coverage counts as
+ exercised merely by being read, so nothing but a case like this can tell
+ whether the value is right.
+
+ ``1_000_000`` is the one to keep: without the digit-separator setting it
+ parses as ``1`` followed by an alias named ``_000_000``, so a metric reading
+ ``revenue / 1_000_000`` quietly divides by one instead.
+ """
+ assert sqlglot.transpile(written, read=Databend, write=Databend)[0] == expected
+
+
+@pytest.mark.parametrize(
+ "sql", ["SELECT * FROM t SAMPLE ROW (10)", "SELECT * FROM t SAMPLE BLOCK (10)"]
+)
+def test_sample_clause_is_not_supported(sql: str) -> None:
+ """
+ Asserted so the limit is deliberate rather than discovered.
+
+ Databend accepts these; this dialect does not. The ClickHouse base parsed
+ them and then emitted ``SAMPLE ROW 10``, which the server rejects too, so no
+ query worked either way -- but the failure moved from the server to
+ ``sanitize_clause``, and that is worth stating rather than leaving to be
+ re-found.
+ """
+ import sqlglot.errors
+
+ with pytest.raises(sqlglot.errors.ParseError):
+ sqlglot.parse_one(sql, Databend)
+
+
+@pytest.mark.parametrize(
+ "written,value",
+ [
+ ("SELECT 1_000_000", 1000000),
+ ("SELECT 1_000", 1000),
+ ("SELECT 12_34_56", 123456),
+ ],
+)
+def test_digit_separators_keep_their_value(written: str, value: int) -> None:
+ """
+ The value survives, whatever the spelling.
+
+ Without the digit-separator setting the tokenizer stops at the first
+ underscore, so ``1_000_000`` parses as ``1`` followed by an alias named
+ ``_000_000``: a metric reading ``revenue / 1_000_000`` quietly divides by one
+ instead, with no error to notice. Asserted on the number rather than the text
+ because sqlglot re-emits the separators on one pinned version and normalises
+ them away on the next, and both are the same million.
+ """
+ parsed = sqlglot.parse_one(written, Databend).selects[0]
+
+ assert isinstance(parsed, exp.Literal)
+ assert int(parsed.this) == value
+
+
+@pytest.mark.parametrize(
+ "written,expected",
+ [
+ # The two-argument call is what Postgres renders wrongly.
+ ("SELECT TRIM(a, 'x')", "SELECT TRIM(a, 'x')"),
+ # The position forms are accepted as Postgres writes them, so they are
+ # handed back unchanged rather than normalised into LTRIM/RTRIM.
+ ("SELECT TRIM(BOTH 'x' FROM a)", "SELECT TRIM(BOTH 'x' FROM a)"),
+ ("SELECT TRIM(LEADING 'x' FROM a)", "SELECT TRIM(LEADING 'x' FROM a)"),
+ ("SELECT TRIM(TRAILING 'x' FROM a)", "SELECT TRIM(TRAILING 'x' FROM a)"),
+ ("SELECT TRIM(a)", "SELECT TRIM(a)"),
+ ],
+)
+def test_trim_keeps_a_form_databend_accepts(written: str, expected: str) -> None:
+ """
+ ``TRIM(a, 'x')`` was being rewritten into ``TRIM('x' FROM a)``, which the
+ server rejects -- "unexpected ``FROM``" -- so an adhoc column that worked
+ before the re-base stopped working after it.
+
+ It survived the enumerating guards because TRIM was exempt from the shape
+ sweep, in a set of names whose argument order sqlglot changes legitimately.
+ Removing that set surfaces this and nothing else
+ -- its other three members render without a top-level comma, so the shape
+ check reads one argument and never flagged them anyway. An exemption that is
+ inert for everything except the one defect it hides is worse than no
+ exemption, so it is gone rather than trimmed.
+ """
+ assert sqlglot.transpile(written, read=Databend, write=Databend)[0] == expected
+
+
+def test_no_catalogue_name_stops_parsing() -> None:
+ """
+ A name that used to parse and now raises is a regression, and the loudest
+ kind: it fails inside ``sanitize_clause``, so the chart breaks in Superset
+ rather than at the server.
+
+ The other sweeps cannot see this. They collect the arities that parse and
+ skip the rest, which is how ``to_char(x)`` and ``bit_and(a, b)`` -- both
+ valid Databend, both fine before the re-base -- passed a suite written to
+ catch exactly this. Measured against ClickHouse rather than in isolation,
+ since refusing a nonsense arity is not a defect.
+ """
+ regressions = {}
+ for name in sorted(DATABEND_FUNCTION_CATALOGUE):
+ if name.upper() in KNOWN_UNSUPPORTED:
+ continue
+ if name in CLICKHOUSE_ACCEPTS_UNVALIDATED:
+ continue
+ for args in ARITIES:
+ if _raises_where_clickhouse_did_not(name, args):
+ regressions[f"{name}({args})"] = "raises here, parsed under ClickHouse"
+
+ assert not regressions, (
+ "these no longer parse but did before the re-base: "
+ + "; ".join(sorted(regressions))
+ )
+
+
+@pytest.mark.parametrize(
+ "sql",
+ [
+ # Databend's TO_CHAR takes a value alone; Postgres demands a format and
+ # raises without one, inside sanitize_clause rather than at the server.
+ "SELECT TO_CHAR(d)",
+ "SELECT TO_CHAR(d, 'YYYY')",
+ # Two-argument scalars here, one-argument aggregates in Postgres, so the
+ # second argument was refused outright.
+ "SELECT BIT_AND(a, b)",
+ "SELECT BIT_OR(a, b)",
+ "SELECT BIT_XOR(a, b)",
+ ],
+)
+def test_names_postgres_arity_would_refuse(sql: str) -> None:
+ """
+ These parsed before the re-base and raised after it, which is the failure the
+ module docstring calls the worse of the two: the chart breaks in Superset
+ rather than at the server.
+
+ They walked past every sweep because ``_round_trips`` collected the arities
+ that parse and skipped the rest, so a name that stopped parsing looked the
+ same as a name with fewer arities. ``test_no_catalogue_name_stops_parsing``
+ exists for that, and goes red on its own if these are put back.
+ """
+ assert sqlglot.parse_one(sql, Databend).sql(dialect=Databend) == sql
+
+
+def test_count_if_is_not_expanded_into_a_sum() -> None:
+ """
+ ``COUNT_IF`` and ``SUM(CASE WHEN ... THEN 1 ELSE 0 END)`` are not the same
+ aggregate. Over no matching rows Databend answers 0 for the first and NULL
+ for the second, so a "count of rows matching X" metric renders blank instead
+ of zero the moment the filter matches nothing -- verified on the server.
+
+ Databend has ``count_if``; it is simply absent from ``SHOW FUNCTIONS``, and
+ taking that absence for evidence is what let the expansion stand.
+ """
+ generated = sqlglot.transpile(
+ "SELECT COUNT_IF(a > 1) FROM t", read=Databend, write=Databend
+ )[0]
+
+ assert generated == "SELECT COUNT_IF(a > 1) FROM t"
+
+
+def test_timestamp_diff_keeps_databend_spelling() -> None:
+ """
+ Databend has ``timestamp_diff`` and not ``timestampdiff``, which is what
+ Postgres renders the node as. Two timestamps in, an interval out.
+
+ This one was sitting in ``KNOWN_UNSUPPORTED``, put there early on when the
+ argument semantics were unclear -- so the name sweep had a real finding and
+ the exemption was swallowing it. Found by auditing that list for entries
+ naming something Databend actually has, which is the only kind that can hide
+ anything.
+ """
+ assert (
+ sqlglot.transpile("SELECT TIMESTAMP_DIFF(a, b)", read=Databend, write=Databend)[
+ 0
+ ]
+ == "SELECT TIMESTAMP_DIFF(a, b)"
+ )
+
+
+def test_no_expression_is_generated_away() -> None:
+ """
+ Whatever a call becomes, it has to remain something.
+
+ ``MAP_FROM_ENTRIES(pairs)`` rendered as nothing at all, leaving ``SELECT FROM
+ t`` -- a statement with no select list -- and in DDL a dangling ``DEFAULT``.
+ sqlglot reports that by logging a warning and returning an empty string, so
+ nothing raises and nothing is caught: the other sweeps compare names and
+ arguments, and there is no name left to compare.
+
+ The ClickHouse base loses a select list for no name at all, so zero is the
+ right threshold rather than a number to be tuned.
+ """
+ vanished = []
+ for name in sorted(Databend.Parser.FUNCTIONS):
+ for args, generated in _round_trips(name):
+ if not generated.strip():
+ vanished.append(f"{name}({args})")
+
+ assert not vanished, f"these generate away to nothing: {'; '.join(vanished)}"