sqlglot.dialects.clickhouse

   1from __future__ import annotations
   2import typing as t
   3import datetime
   4from sqlglot import exp, generator, parser, tokens
   5from sqlglot.dialects.dialect import (
   6    Dialect,
   7    NormalizationStrategy,
   8    arg_max_or_min_no_count,
   9    build_date_delta,
  10    build_formatted_time,
  11    inline_array_sql,
  12    json_extract_segments,
  13    json_path_key_only_name,
  14    length_or_char_length_sql,
  15    no_pivot_sql,
  16    build_json_extract_path,
  17    rename_func,
  18    remove_from_array_using_filter,
  19    sha256_sql,
  20    strposition_sql,
  21    var_map_sql,
  22    timestamptrunc_sql,
  23    unit_to_var,
  24    trim_sql,
  25)
  26from sqlglot.generator import Generator
  27from sqlglot.helper import is_int, seq_get
  28from sqlglot.tokens import Token, TokenType
  29from sqlglot.generator import unsupported_args
  30
  31DATEΤΙΜΕ_DELTA = t.Union[exp.DateAdd, exp.DateDiff, exp.DateSub, exp.TimestampSub, exp.TimestampAdd]
  32
  33
  34def _build_date_format(args: t.List) -> exp.TimeToStr:
  35    expr = build_formatted_time(exp.TimeToStr, "clickhouse")(args)
  36
  37    timezone = seq_get(args, 2)
  38    if timezone:
  39        expr.set("zone", timezone)
  40
  41    return expr
  42
  43
  44def _unix_to_time_sql(self: ClickHouse.Generator, expression: exp.UnixToTime) -> str:
  45    scale = expression.args.get("scale")
  46    timestamp = expression.this
  47
  48    if scale in (None, exp.UnixToTime.SECONDS):
  49        return self.func("fromUnixTimestamp", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  50    if scale == exp.UnixToTime.MILLIS:
  51        return self.func("fromUnixTimestamp64Milli", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  52    if scale == exp.UnixToTime.MICROS:
  53        return self.func("fromUnixTimestamp64Micro", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  54    if scale == exp.UnixToTime.NANOS:
  55        return self.func("fromUnixTimestamp64Nano", exp.cast(timestamp, exp.DataType.Type.BIGINT))
  56
  57    return self.func(
  58        "fromUnixTimestamp",
  59        exp.cast(
  60            exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)), exp.DataType.Type.BIGINT
  61        ),
  62    )
  63
  64
  65def _lower_func(sql: str) -> str:
  66    index = sql.index("(")
  67    return sql[:index].lower() + sql[index:]
  68
  69
  70def _quantile_sql(self: ClickHouse.Generator, expression: exp.Quantile) -> str:
  71    quantile = expression.args["quantile"]
  72    args = f"({self.sql(expression, 'this')})"
  73
  74    if isinstance(quantile, exp.Array):
  75        func = self.func("quantiles", *quantile)
  76    else:
  77        func = self.func("quantile", quantile)
  78
  79    return func + args
  80
  81
  82def _build_count_if(args: t.List) -> exp.CountIf | exp.CombinedAggFunc:
  83    if len(args) == 1:
  84        return exp.CountIf(this=seq_get(args, 0))
  85
  86    return exp.CombinedAggFunc(this="countIf", expressions=args)
  87
  88
  89def _build_str_to_date(args: t.List) -> exp.Cast | exp.Anonymous:
  90    if len(args) == 3:
  91        return exp.Anonymous(this="STR_TO_DATE", expressions=args)
  92
  93    strtodate = exp.StrToDate.from_arg_list(args)
  94    return exp.cast(strtodate, exp.DataType.build(exp.DataType.Type.DATETIME))
  95
  96
  97def _datetime_delta_sql(name: str) -> t.Callable[[Generator, DATEΤΙΜΕ_DELTA], str]:
  98    def _delta_sql(self: Generator, expression: DATEΤΙΜΕ_DELTA) -> str:
  99        if not expression.unit:
 100            return rename_func(name)(self, expression)
 101
 102        return self.func(
 103            name,
 104            unit_to_var(expression),
 105            expression.expression,
 106            expression.this,
 107            expression.args.get("zone"),
 108        )
 109
 110    return _delta_sql
 111
 112
 113def _timestrtotime_sql(self: ClickHouse.Generator, expression: exp.TimeStrToTime):
 114    ts = expression.this
 115
 116    tz = expression.args.get("zone")
 117    if tz and isinstance(ts, exp.Literal):
 118        # Clickhouse will not accept timestamps that include a UTC offset, so we must remove them.
 119        # The first step to removing is parsing the string with `datetime.datetime.fromisoformat`.
 120        #
 121        # In python <3.11, `fromisoformat()` can only parse timestamps of millisecond (3 digit)
 122        # or microsecond (6 digit) precision. It will error if passed any other number of fractional
 123        # digits, so we extract the fractional seconds and pad to 6 digits before parsing.
 124        ts_string = ts.name.strip()
 125
 126        # separate [date and time] from [fractional seconds and UTC offset]
 127        ts_parts = ts_string.split(".")
 128        if len(ts_parts) == 2:
 129            # separate fractional seconds and UTC offset
 130            offset_sep = "+" if "+" in ts_parts[1] else "-"
 131            ts_frac_parts = ts_parts[1].split(offset_sep)
 132            num_frac_parts = len(ts_frac_parts)
 133
 134            # pad to 6 digits if fractional seconds present
 135            ts_frac_parts[0] = ts_frac_parts[0].ljust(6, "0")
 136            ts_string = "".join(
 137                [
 138                    ts_parts[0],  # date and time
 139                    ".",
 140                    ts_frac_parts[0],  # fractional seconds
 141                    offset_sep if num_frac_parts > 1 else "",
 142                    ts_frac_parts[1] if num_frac_parts > 1 else "",  # utc offset (if present)
 143                ]
 144            )
 145
 146        # return literal with no timezone, eg turn '2020-01-01 12:13:14-08:00' into '2020-01-01 12:13:14'
 147        # this is because Clickhouse encodes the timezone as a data type parameter and throws an error if
 148        # it's part of the timestamp string
 149        ts_without_tz = (
 150            datetime.datetime.fromisoformat(ts_string).replace(tzinfo=None).isoformat(sep=" ")
 151        )
 152        ts = exp.Literal.string(ts_without_tz)
 153
 154    # Non-nullable DateTime64 with microsecond precision
 155    expressions = [exp.DataTypeParam(this=tz)] if tz else []
 156    datatype = exp.DataType.build(
 157        exp.DataType.Type.DATETIME64,
 158        expressions=[exp.DataTypeParam(this=exp.Literal.number(6)), *expressions],
 159        nullable=False,
 160    )
 161
 162    return self.sql(exp.cast(ts, datatype, dialect=self.dialect))
 163
 164
 165def _map_sql(self: ClickHouse.Generator, expression: exp.Map | exp.VarMap) -> str:
 166    if not (expression.parent and expression.parent.arg_key == "settings"):
 167        return _lower_func(var_map_sql(self, expression))
 168
 169    keys = expression.args.get("keys")
 170    values = expression.args.get("values")
 171
 172    if not isinstance(keys, exp.Array) or not isinstance(values, exp.Array):
 173        self.unsupported("Cannot convert array columns into map.")
 174        return ""
 175
 176    args = []
 177    for key, value in zip(keys.expressions, values.expressions):
 178        args.append(f"{self.sql(key)}: {self.sql(value)}")
 179
 180    csv_args = ", ".join(args)
 181
 182    return f"{{{csv_args}}}"
 183
 184
 185class ClickHouse(Dialect):
 186    NORMALIZE_FUNCTIONS: bool | str = False
 187    NULL_ORDERING = "nulls_are_last"
 188    SUPPORTS_USER_DEFINED_TYPES = False
 189    SAFE_DIVISION = True
 190    LOG_BASE_FIRST: t.Optional[bool] = None
 191    FORCE_EARLY_ALIAS_REF_EXPANSION = True
 192    PRESERVE_ORIGINAL_NAMES = True
 193    NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
 194    IDENTIFIERS_CAN_START_WITH_DIGIT = True
 195    HEX_STRING_IS_INTEGER_TYPE = True
 196
 197    # https://github.com/ClickHouse/ClickHouse/issues/33935#issue-1112165779
 198    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
 199
 200    UNESCAPED_SEQUENCES = {
 201        "\\0": "\0",
 202    }
 203
 204    CREATABLE_KIND_MAPPING = {"DATABASE": "SCHEMA"}
 205
 206    SET_OP_DISTINCT_BY_DEFAULT: t.Dict[t.Type[exp.Expression], t.Optional[bool]] = {
 207        exp.Except: False,
 208        exp.Intersect: False,
 209        exp.Union: None,
 210    }
 211
 212    def generate_values_aliases(self, expression: exp.Values) -> t.List[exp.Identifier]:
 213        # Clickhouse allows VALUES to have an embedded structure e.g:
 214        # VALUES('person String, place String', ('Noah', 'Paris'), ...)
 215        # In this case, we don't want to qualify the columns
 216        values = expression.expressions[0].expressions
 217
 218        structure = (
 219            values[0]
 220            if (len(values) > 1 and values[0].is_string and isinstance(values[1], exp.Tuple))
 221            else None
 222        )
 223        if structure:
 224            # Split each column definition into the column name e.g:
 225            # 'person String, place String' -> ['person', 'place']
 226            structure_coldefs = [coldef.strip() for coldef in structure.name.split(",")]
 227            column_aliases = [
 228                exp.to_identifier(coldef.split(" ")[0]) for coldef in structure_coldefs
 229            ]
 230        else:
 231            # Default column aliases in CH are "c1", "c2", etc.
 232            column_aliases = [
 233                exp.to_identifier(f"c{i + 1}") for i in range(len(values[0].expressions))
 234            ]
 235
 236        return column_aliases
 237
 238    class Tokenizer(tokens.Tokenizer):
 239        COMMENTS = ["--", "#", "#!", ("/*", "*/")]
 240        IDENTIFIERS = ['"', "`"]
 241        IDENTIFIER_ESCAPES = ["\\"]
 242        STRING_ESCAPES = ["'", "\\"]
 243        BIT_STRINGS = [("0b", "")]
 244        HEX_STRINGS = [("0x", ""), ("0X", "")]
 245        HEREDOC_STRINGS = ["$"]
 246
 247        KEYWORDS = {
 248            **tokens.Tokenizer.KEYWORDS,
 249            ".:": TokenType.DOTCOLON,
 250            "ATTACH": TokenType.COMMAND,
 251            "DATE32": TokenType.DATE32,
 252            "DATETIME64": TokenType.DATETIME64,
 253            "DICTIONARY": TokenType.DICTIONARY,
 254            "DYNAMIC": TokenType.DYNAMIC,
 255            "ENUM8": TokenType.ENUM8,
 256            "ENUM16": TokenType.ENUM16,
 257            "EXCHANGE": TokenType.COMMAND,
 258            "FINAL": TokenType.FINAL,
 259            "FIXEDSTRING": TokenType.FIXEDSTRING,
 260            "FLOAT32": TokenType.FLOAT,
 261            "FLOAT64": TokenType.DOUBLE,
 262            "GLOBAL": TokenType.GLOBAL,
 263            "LOWCARDINALITY": TokenType.LOWCARDINALITY,
 264            "MAP": TokenType.MAP,
 265            "NESTED": TokenType.NESTED,
 266            "NOTHING": TokenType.NOTHING,
 267            "SAMPLE": TokenType.TABLE_SAMPLE,
 268            "TUPLE": TokenType.STRUCT,
 269            "UINT16": TokenType.USMALLINT,
 270            "UINT32": TokenType.UINT,
 271            "UINT64": TokenType.UBIGINT,
 272            "UINT8": TokenType.UTINYINT,
 273            "IPV4": TokenType.IPV4,
 274            "IPV6": TokenType.IPV6,
 275            "POINT": TokenType.POINT,
 276            "RING": TokenType.RING,
 277            "LINESTRING": TokenType.LINESTRING,
 278            "MULTILINESTRING": TokenType.MULTILINESTRING,
 279            "POLYGON": TokenType.POLYGON,
 280            "MULTIPOLYGON": TokenType.MULTIPOLYGON,
 281            "AGGREGATEFUNCTION": TokenType.AGGREGATEFUNCTION,
 282            "SIMPLEAGGREGATEFUNCTION": TokenType.SIMPLEAGGREGATEFUNCTION,
 283            "SYSTEM": TokenType.COMMAND,
 284            "PREWHERE": TokenType.PREWHERE,
 285        }
 286        KEYWORDS.pop("/*+")
 287
 288        SINGLE_TOKENS = {
 289            **tokens.Tokenizer.SINGLE_TOKENS,
 290            "$": TokenType.HEREDOC_STRING,
 291        }
 292
 293    class Parser(parser.Parser):
 294        # Tested in ClickHouse's playground, it seems that the following two queries do the same thing
 295        # * select x from t1 union all select x from t2 limit 1;
 296        # * select x from t1 union all (select x from t2 limit 1);
 297        MODIFIERS_ATTACHED_TO_SET_OP = False
 298        INTERVAL_SPANS = False
 299        OPTIONAL_ALIAS_TOKEN_CTE = False
 300        JOINS_HAVE_EQUAL_PRECEDENCE = True
 301
 302        FUNCTIONS = {
 303            **parser.Parser.FUNCTIONS,
 304            "ANY": exp.AnyValue.from_arg_list,
 305            "ARRAYSUM": exp.ArraySum.from_arg_list,
 306            "COUNTIF": _build_count_if,
 307            "DATE_ADD": build_date_delta(exp.DateAdd, default_unit=None),
 308            "DATEADD": build_date_delta(exp.DateAdd, default_unit=None),
 309            "DATE_DIFF": build_date_delta(exp.DateDiff, default_unit=None, supports_timezone=True),
 310            "DATEDIFF": build_date_delta(exp.DateDiff, default_unit=None, supports_timezone=True),
 311            "DATE_FORMAT": _build_date_format,
 312            "DATE_SUB": build_date_delta(exp.DateSub, default_unit=None),
 313            "DATESUB": build_date_delta(exp.DateSub, default_unit=None),
 314            "FORMATDATETIME": _build_date_format,
 315            "JSONEXTRACTSTRING": build_json_extract_path(
 316                exp.JSONExtractScalar, zero_based_indexing=False
 317            ),
 318            "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True),
 319            "MAP": parser.build_var_map,
 320            "MATCH": exp.RegexpLike.from_arg_list,
 321            "RANDCANONICAL": exp.Rand.from_arg_list,
 322            "STR_TO_DATE": _build_str_to_date,
 323            "TUPLE": exp.Struct.from_arg_list,
 324            "TIMESTAMP_SUB": build_date_delta(exp.TimestampSub, default_unit=None),
 325            "TIMESTAMPSUB": build_date_delta(exp.TimestampSub, default_unit=None),
 326            "TIMESTAMP_ADD": build_date_delta(exp.TimestampAdd, default_unit=None),
 327            "TIMESTAMPADD": build_date_delta(exp.TimestampAdd, default_unit=None),
 328            "UNIQ": exp.ApproxDistinct.from_arg_list,
 329            "XOR": lambda args: exp.Xor(expressions=args),
 330            "MD5": exp.MD5Digest.from_arg_list,
 331            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
 332            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
 333            "EDITDISTANCE": exp.Levenshtein.from_arg_list,
 334            "LEVENSHTEINDISTANCE": exp.Levenshtein.from_arg_list,
 335        }
 336        FUNCTIONS.pop("TRANSFORM")
 337
 338        AGG_FUNCTIONS = {
 339            "count",
 340            "min",
 341            "max",
 342            "sum",
 343            "avg",
 344            "any",
 345            "stddevPop",
 346            "stddevSamp",
 347            "varPop",
 348            "varSamp",
 349            "corr",
 350            "covarPop",
 351            "covarSamp",
 352            "entropy",
 353            "exponentialMovingAverage",
 354            "intervalLengthSum",
 355            "kolmogorovSmirnovTest",
 356            "mannWhitneyUTest",
 357            "median",
 358            "rankCorr",
 359            "sumKahan",
 360            "studentTTest",
 361            "welchTTest",
 362            "anyHeavy",
 363            "anyLast",
 364            "boundingRatio",
 365            "first_value",
 366            "last_value",
 367            "argMin",
 368            "argMax",
 369            "avgWeighted",
 370            "topK",
 371            "topKWeighted",
 372            "deltaSum",
 373            "deltaSumTimestamp",
 374            "groupArray",
 375            "groupArrayLast",
 376            "groupUniqArray",
 377            "groupArrayInsertAt",
 378            "groupArrayMovingAvg",
 379            "groupArrayMovingSum",
 380            "groupArraySample",
 381            "groupBitAnd",
 382            "groupBitOr",
 383            "groupBitXor",
 384            "groupBitmap",
 385            "groupBitmapAnd",
 386            "groupBitmapOr",
 387            "groupBitmapXor",
 388            "sumWithOverflow",
 389            "sumMap",
 390            "minMap",
 391            "maxMap",
 392            "skewSamp",
 393            "skewPop",
 394            "kurtSamp",
 395            "kurtPop",
 396            "uniq",
 397            "uniqExact",
 398            "uniqCombined",
 399            "uniqCombined64",
 400            "uniqHLL12",
 401            "uniqTheta",
 402            "quantile",
 403            "quantiles",
 404            "quantileExact",
 405            "quantilesExact",
 406            "quantileExactLow",
 407            "quantilesExactLow",
 408            "quantileExactHigh",
 409            "quantilesExactHigh",
 410            "quantileExactWeighted",
 411            "quantilesExactWeighted",
 412            "quantileTiming",
 413            "quantilesTiming",
 414            "quantileTimingWeighted",
 415            "quantilesTimingWeighted",
 416            "quantileDeterministic",
 417            "quantilesDeterministic",
 418            "quantileTDigest",
 419            "quantilesTDigest",
 420            "quantileTDigestWeighted",
 421            "quantilesTDigestWeighted",
 422            "quantileBFloat16",
 423            "quantilesBFloat16",
 424            "quantileBFloat16Weighted",
 425            "quantilesBFloat16Weighted",
 426            "simpleLinearRegression",
 427            "stochasticLinearRegression",
 428            "stochasticLogisticRegression",
 429            "categoricalInformationValue",
 430            "contingency",
 431            "cramersV",
 432            "cramersVBiasCorrected",
 433            "theilsU",
 434            "maxIntersections",
 435            "maxIntersectionsPosition",
 436            "meanZTest",
 437            "quantileInterpolatedWeighted",
 438            "quantilesInterpolatedWeighted",
 439            "quantileGK",
 440            "quantilesGK",
 441            "sparkBar",
 442            "sumCount",
 443            "largestTriangleThreeBuckets",
 444            "histogram",
 445            "sequenceMatch",
 446            "sequenceCount",
 447            "windowFunnel",
 448            "retention",
 449            "uniqUpTo",
 450            "sequenceNextNode",
 451            "exponentialTimeDecayedAvg",
 452        }
 453
 454        AGG_FUNCTIONS_SUFFIXES = [
 455            "If",
 456            "Array",
 457            "ArrayIf",
 458            "Map",
 459            "SimpleState",
 460            "State",
 461            "Merge",
 462            "MergeState",
 463            "ForEach",
 464            "Distinct",
 465            "OrDefault",
 466            "OrNull",
 467            "Resample",
 468            "ArgMin",
 469            "ArgMax",
 470        ]
 471
 472        FUNC_TOKENS = {
 473            *parser.Parser.FUNC_TOKENS,
 474            TokenType.AND,
 475            TokenType.OR,
 476            TokenType.SET,
 477        }
 478
 479        RESERVED_TOKENS = parser.Parser.RESERVED_TOKENS - {TokenType.SELECT}
 480
 481        ID_VAR_TOKENS = {
 482            *parser.Parser.ID_VAR_TOKENS,
 483            TokenType.LIKE,
 484        }
 485
 486        AGG_FUNC_MAPPING = (
 487            lambda functions, suffixes: {
 488                f"{f}{sfx}": (f, sfx) for sfx in (suffixes + [""]) for f in functions
 489            }
 490        )(AGG_FUNCTIONS, AGG_FUNCTIONS_SUFFIXES)
 491
 492        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "TUPLE"}
 493
 494        FUNCTION_PARSERS = {
 495            **parser.Parser.FUNCTION_PARSERS,
 496            "ARRAYJOIN": lambda self: self.expression(exp.Explode, this=self._parse_expression()),
 497            "QUANTILE": lambda self: self._parse_quantile(),
 498            "MEDIAN": lambda self: self._parse_quantile(),
 499            "COLUMNS": lambda self: self._parse_columns(),
 500        }
 501
 502        FUNCTION_PARSERS.pop("MATCH")
 503
 504        PROPERTY_PARSERS = {
 505            **parser.Parser.PROPERTY_PARSERS,
 506            "ENGINE": lambda self: self._parse_engine_property(),
 507        }
 508        PROPERTY_PARSERS.pop("DYNAMIC")
 509
 510        NO_PAREN_FUNCTION_PARSERS = parser.Parser.NO_PAREN_FUNCTION_PARSERS.copy()
 511        NO_PAREN_FUNCTION_PARSERS.pop("ANY")
 512
 513        NO_PAREN_FUNCTIONS = parser.Parser.NO_PAREN_FUNCTIONS.copy()
 514        NO_PAREN_FUNCTIONS.pop(TokenType.CURRENT_TIMESTAMP)
 515
 516        RANGE_PARSERS = {
 517            **parser.Parser.RANGE_PARSERS,
 518            TokenType.GLOBAL: lambda self, this: self._parse_global_in(this),
 519        }
 520
 521        # The PLACEHOLDER entry is popped because 1) it doesn't affect Clickhouse (it corresponds to
 522        # the postgres-specific JSONBContains parser) and 2) it makes parsing the ternary op simpler.
 523        COLUMN_OPERATORS = parser.Parser.COLUMN_OPERATORS.copy()
 524        COLUMN_OPERATORS.pop(TokenType.PLACEHOLDER)
 525
 526        JOIN_KINDS = {
 527            *parser.Parser.JOIN_KINDS,
 528            TokenType.ANY,
 529            TokenType.ASOF,
 530            TokenType.ARRAY,
 531        }
 532
 533        TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - {
 534            TokenType.ANY,
 535            TokenType.ARRAY,
 536            TokenType.FINAL,
 537            TokenType.FORMAT,
 538            TokenType.SETTINGS,
 539        }
 540
 541        ALIAS_TOKENS = parser.Parser.ALIAS_TOKENS - {
 542            TokenType.FORMAT,
 543        }
 544
 545        LOG_DEFAULTS_TO_LN = True
 546
 547        QUERY_MODIFIER_PARSERS = {
 548            **parser.Parser.QUERY_MODIFIER_PARSERS,
 549            TokenType.SETTINGS: lambda self: (
 550                "settings",
 551                self._advance() or self._parse_csv(self._parse_assignment),
 552            ),
 553            TokenType.FORMAT: lambda self: ("format", self._advance() or self._parse_id_var()),
 554        }
 555
 556        CONSTRAINT_PARSERS = {
 557            **parser.Parser.CONSTRAINT_PARSERS,
 558            "INDEX": lambda self: self._parse_index_constraint(),
 559            "CODEC": lambda self: self._parse_compress(),
 560        }
 561
 562        ALTER_PARSERS = {
 563            **parser.Parser.ALTER_PARSERS,
 564            "REPLACE": lambda self: self._parse_alter_table_replace(),
 565        }
 566
 567        SCHEMA_UNNAMED_CONSTRAINTS = {
 568            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
 569            "INDEX",
 570        }
 571
 572        PLACEHOLDER_PARSERS = {
 573            **parser.Parser.PLACEHOLDER_PARSERS,
 574            TokenType.L_BRACE: lambda self: self._parse_query_parameter(),
 575        }
 576
 577        def _parse_engine_property(self) -> exp.EngineProperty:
 578            self._match(TokenType.EQ)
 579            return self.expression(
 580                exp.EngineProperty,
 581                this=self._parse_field(any_token=True, anonymous_func=True),
 582            )
 583
 584        # https://clickhouse.com/docs/en/sql-reference/statements/create/function
 585        def _parse_user_defined_function_expression(self) -> t.Optional[exp.Expression]:
 586            return self._parse_lambda()
 587
 588        def _parse_types(
 589            self, check_func: bool = False, schema: bool = False, allow_identifiers: bool = True
 590        ) -> t.Optional[exp.Expression]:
 591            dtype = super()._parse_types(
 592                check_func=check_func, schema=schema, allow_identifiers=allow_identifiers
 593            )
 594            if isinstance(dtype, exp.DataType) and dtype.args.get("nullable") is not True:
 595                # Mark every type as non-nullable which is ClickHouse's default, unless it's
 596                # already marked as nullable. This marker helps us transpile types from other
 597                # dialects to ClickHouse, so that we can e.g. produce `CAST(x AS Nullable(String))`
 598                # from `CAST(x AS TEXT)`. If there is a `NULL` value in `x`, the former would
 599                # fail in ClickHouse without the `Nullable` type constructor.
 600                dtype.set("nullable", False)
 601
 602            return dtype
 603
 604        def _parse_extract(self) -> exp.Extract | exp.Anonymous:
 605            index = self._index
 606            this = self._parse_bitwise()
 607            if self._match(TokenType.FROM):
 608                self._retreat(index)
 609                return super()._parse_extract()
 610
 611            # We return Anonymous here because extract and regexpExtract have different semantics,
 612            # so parsing extract(foo, bar) into RegexpExtract can potentially break queries. E.g.,
 613            # `extract('foobar', 'b')` works, but ClickHouse crashes for `regexpExtract('foobar', 'b')`.
 614            #
 615            # TODO: can we somehow convert the former into an equivalent `regexpExtract` call?
 616            self._match(TokenType.COMMA)
 617            return self.expression(
 618                exp.Anonymous, this="extract", expressions=[this, self._parse_bitwise()]
 619            )
 620
 621        def _parse_assignment(self) -> t.Optional[exp.Expression]:
 622            this = super()._parse_assignment()
 623
 624            if self._match(TokenType.PLACEHOLDER):
 625                return self.expression(
 626                    exp.If,
 627                    this=this,
 628                    true=self._parse_assignment(),
 629                    false=self._match(TokenType.COLON) and self._parse_assignment(),
 630                )
 631
 632            return this
 633
 634        def _parse_query_parameter(self) -> t.Optional[exp.Expression]:
 635            """
 636            Parse a placeholder expression like SELECT {abc: UInt32} or FROM {table: Identifier}
 637            https://clickhouse.com/docs/en/sql-reference/syntax#defining-and-using-query-parameters
 638            """
 639            index = self._index
 640
 641            this = self._parse_id_var()
 642            self._match(TokenType.COLON)
 643            kind = self._parse_types(check_func=False, allow_identifiers=False) or (
 644                self._match_text_seq("IDENTIFIER") and "Identifier"
 645            )
 646
 647            if not kind:
 648                self._retreat(index)
 649                return None
 650            elif not self._match(TokenType.R_BRACE):
 651                self.raise_error("Expecting }")
 652
 653            if isinstance(this, exp.Identifier) and not this.quoted:
 654                this = exp.var(this.name)
 655
 656            return self.expression(exp.Placeholder, this=this, kind=kind)
 657
 658        def _parse_bracket(
 659            self, this: t.Optional[exp.Expression] = None
 660        ) -> t.Optional[exp.Expression]:
 661            l_brace = self._match(TokenType.L_BRACE, advance=False)
 662            bracket = super()._parse_bracket(this)
 663
 664            if l_brace and isinstance(bracket, exp.Struct):
 665                varmap = exp.VarMap(keys=exp.Array(), values=exp.Array())
 666                for expression in bracket.expressions:
 667                    if not isinstance(expression, exp.PropertyEQ):
 668                        break
 669
 670                    varmap.args["keys"].append("expressions", exp.Literal.string(expression.name))
 671                    varmap.args["values"].append("expressions", expression.expression)
 672
 673                return varmap
 674
 675            return bracket
 676
 677        def _parse_in(self, this: t.Optional[exp.Expression], is_global: bool = False) -> exp.In:
 678            this = super()._parse_in(this)
 679            this.set("is_global", is_global)
 680            return this
 681
 682        def _parse_global_in(self, this: t.Optional[exp.Expression]) -> exp.Not | exp.In:
 683            is_negated = self._match(TokenType.NOT)
 684            this = self._match(TokenType.IN) and self._parse_in(this, is_global=True)
 685            return self.expression(exp.Not, this=this) if is_negated else this
 686
 687        def _parse_table(
 688            self,
 689            schema: bool = False,
 690            joins: bool = False,
 691            alias_tokens: t.Optional[t.Collection[TokenType]] = None,
 692            parse_bracket: bool = False,
 693            is_db_reference: bool = False,
 694            parse_partition: bool = False,
 695            consume_pipe: bool = False,
 696        ) -> t.Optional[exp.Expression]:
 697            this = super()._parse_table(
 698                schema=schema,
 699                joins=joins,
 700                alias_tokens=alias_tokens,
 701                parse_bracket=parse_bracket,
 702                is_db_reference=is_db_reference,
 703            )
 704
 705            if isinstance(this, exp.Table):
 706                inner = this.this
 707                alias = this.args.get("alias")
 708
 709                if isinstance(inner, exp.GenerateSeries) and alias and not alias.columns:
 710                    alias.set("columns", [exp.to_identifier("generate_series")])
 711
 712            if self._match(TokenType.FINAL):
 713                this = self.expression(exp.Final, this=this)
 714
 715            return this
 716
 717        def _parse_position(self, haystack_first: bool = False) -> exp.StrPosition:
 718            return super()._parse_position(haystack_first=True)
 719
 720        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
 721        def _parse_cte(self) -> t.Optional[exp.CTE]:
 722            # WITH <identifier> AS <subquery expression>
 723            cte: t.Optional[exp.CTE] = self._try_parse(super()._parse_cte)
 724
 725            if not cte:
 726                # WITH <expression> AS <identifier>
 727                cte = self.expression(
 728                    exp.CTE,
 729                    this=self._parse_assignment(),
 730                    alias=self._parse_table_alias(),
 731                    scalar=True,
 732                )
 733
 734            return cte
 735
 736        def _parse_join_parts(
 737            self,
 738        ) -> t.Tuple[t.Optional[Token], t.Optional[Token], t.Optional[Token]]:
 739            is_global = self._match(TokenType.GLOBAL) and self._prev
 740            kind_pre = self._match_set(self.JOIN_KINDS, advance=False) and self._prev
 741
 742            if kind_pre:
 743                kind = self._match_set(self.JOIN_KINDS) and self._prev
 744                side = self._match_set(self.JOIN_SIDES) and self._prev
 745                return is_global, side, kind
 746
 747            return (
 748                is_global,
 749                self._match_set(self.JOIN_SIDES) and self._prev,
 750                self._match_set(self.JOIN_KINDS) and self._prev,
 751            )
 752
 753        def _parse_join(
 754            self, skip_join_token: bool = False, parse_bracket: bool = False
 755        ) -> t.Optional[exp.Join]:
 756            join = super()._parse_join(skip_join_token=skip_join_token, parse_bracket=True)
 757            if join:
 758                join.set("global", join.args.pop("method", None))
 759
 760                # tbl ARRAY JOIN arr <-- this should be a `Column` reference, not a `Table`
 761                # https://clickhouse.com/docs/en/sql-reference/statements/select/array-join
 762                if join.kind == "ARRAY":
 763                    for table in join.find_all(exp.Table):
 764                        table.replace(table.to_column())
 765
 766            return join
 767
 768        def _parse_function(
 769            self,
 770            functions: t.Optional[t.Dict[str, t.Callable]] = None,
 771            anonymous: bool = False,
 772            optional_parens: bool = True,
 773            any_token: bool = False,
 774        ) -> t.Optional[exp.Expression]:
 775            expr = super()._parse_function(
 776                functions=functions,
 777                anonymous=anonymous,
 778                optional_parens=optional_parens,
 779                any_token=any_token,
 780            )
 781
 782            func = expr.this if isinstance(expr, exp.Window) else expr
 783
 784            # Aggregate functions can be split in 2 parts: <func_name><suffix>
 785            parts = (
 786                self.AGG_FUNC_MAPPING.get(func.this) if isinstance(func, exp.Anonymous) else None
 787            )
 788
 789            if parts:
 790                anon_func: exp.Anonymous = t.cast(exp.Anonymous, func)
 791                params = self._parse_func_params(anon_func)
 792
 793                kwargs = {
 794                    "this": anon_func.this,
 795                    "expressions": anon_func.expressions,
 796                }
 797                if parts[1]:
 798                    exp_class: t.Type[exp.Expression] = (
 799                        exp.CombinedParameterizedAgg if params else exp.CombinedAggFunc
 800                    )
 801                else:
 802                    exp_class = exp.ParameterizedAgg if params else exp.AnonymousAggFunc
 803
 804                kwargs["exp_class"] = exp_class
 805                if params:
 806                    kwargs["params"] = params
 807
 808                func = self.expression(**kwargs)
 809
 810                if isinstance(expr, exp.Window):
 811                    # The window's func was parsed as Anonymous in base parser, fix its
 812                    # type to be ClickHouse style CombinedAnonymousAggFunc / AnonymousAggFunc
 813                    expr.set("this", func)
 814                elif params:
 815                    # Params have blocked super()._parse_function() from parsing the following window
 816                    # (if that exists) as they're standing between the function call and the window spec
 817                    expr = self._parse_window(func)
 818                else:
 819                    expr = func
 820
 821            return expr
 822
 823        def _parse_func_params(
 824            self, this: t.Optional[exp.Func] = None
 825        ) -> t.Optional[t.List[exp.Expression]]:
 826            if self._match_pair(TokenType.R_PAREN, TokenType.L_PAREN):
 827                return self._parse_csv(self._parse_lambda)
 828
 829            if self._match(TokenType.L_PAREN):
 830                params = self._parse_csv(self._parse_lambda)
 831                self._match_r_paren(this)
 832                return params
 833
 834            return None
 835
 836        def _parse_quantile(self) -> exp.Quantile:
 837            this = self._parse_lambda()
 838            params = self._parse_func_params()
 839            if params:
 840                return self.expression(exp.Quantile, this=params[0], quantile=this)
 841            return self.expression(exp.Quantile, this=this, quantile=exp.Literal.number(0.5))
 842
 843        def _parse_wrapped_id_vars(self, optional: bool = False) -> t.List[exp.Expression]:
 844            return super()._parse_wrapped_id_vars(optional=True)
 845
 846        def _parse_primary_key(
 847            self, wrapped_optional: bool = False, in_props: bool = False
 848        ) -> exp.PrimaryKeyColumnConstraint | exp.PrimaryKey:
 849            return super()._parse_primary_key(
 850                wrapped_optional=wrapped_optional or in_props, in_props=in_props
 851            )
 852
 853        def _parse_on_property(self) -> t.Optional[exp.Expression]:
 854            index = self._index
 855            if self._match_text_seq("CLUSTER"):
 856                this = self._parse_string() or self._parse_id_var()
 857                if this:
 858                    return self.expression(exp.OnCluster, this=this)
 859                else:
 860                    self._retreat(index)
 861            return None
 862
 863        def _parse_index_constraint(
 864            self, kind: t.Optional[str] = None
 865        ) -> exp.IndexColumnConstraint:
 866            # INDEX name1 expr TYPE type1(args) GRANULARITY value
 867            this = self._parse_id_var()
 868            expression = self._parse_assignment()
 869
 870            index_type = self._match_text_seq("TYPE") and (
 871                self._parse_function() or self._parse_var()
 872            )
 873
 874            granularity = self._match_text_seq("GRANULARITY") and self._parse_term()
 875
 876            return self.expression(
 877                exp.IndexColumnConstraint,
 878                this=this,
 879                expression=expression,
 880                index_type=index_type,
 881                granularity=granularity,
 882            )
 883
 884        def _parse_partition(self) -> t.Optional[exp.Partition]:
 885            # https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression
 886            if not self._match(TokenType.PARTITION):
 887                return None
 888
 889            if self._match_text_seq("ID"):
 890                # Corresponds to the PARTITION ID <string_value> syntax
 891                expressions: t.List[exp.Expression] = [
 892                    self.expression(exp.PartitionId, this=self._parse_string())
 893                ]
 894            else:
 895                expressions = self._parse_expressions()
 896
 897            return self.expression(exp.Partition, expressions=expressions)
 898
 899        def _parse_alter_table_replace(self) -> t.Optional[exp.Expression]:
 900            partition = self._parse_partition()
 901
 902            if not partition or not self._match(TokenType.FROM):
 903                return None
 904
 905            return self.expression(
 906                exp.ReplacePartition, expression=partition, source=self._parse_table_parts()
 907            )
 908
 909        def _parse_projection_def(self) -> t.Optional[exp.ProjectionDef]:
 910            if not self._match_text_seq("PROJECTION"):
 911                return None
 912
 913            return self.expression(
 914                exp.ProjectionDef,
 915                this=self._parse_id_var(),
 916                expression=self._parse_wrapped(self._parse_statement),
 917            )
 918
 919        def _parse_constraint(self) -> t.Optional[exp.Expression]:
 920            return super()._parse_constraint() or self._parse_projection_def()
 921
 922        def _parse_alias(
 923            self, this: t.Optional[exp.Expression], explicit: bool = False
 924        ) -> t.Optional[exp.Expression]:
 925            # In clickhouse "SELECT <expr> APPLY(...)" is a query modifier,
 926            # so "APPLY" shouldn't be parsed as <expr>'s alias. However, "SELECT <expr> apply" is a valid alias
 927            if self._match_pair(TokenType.APPLY, TokenType.L_PAREN, advance=False):
 928                return this
 929
 930            return super()._parse_alias(this=this, explicit=explicit)
 931
 932        def _parse_expression(self) -> t.Optional[exp.Expression]:
 933            this = super()._parse_expression()
 934
 935            # Clickhouse allows "SELECT <expr> [APPLY(func)] [...]]" modifier
 936            while self._match_pair(TokenType.APPLY, TokenType.L_PAREN):
 937                this = exp.Apply(this=this, expression=self._parse_var(any_token=True))
 938                self._match(TokenType.R_PAREN)
 939
 940            return this
 941
 942        def _parse_columns(self) -> exp.Expression:
 943            this: exp.Expression = self.expression(exp.Columns, this=self._parse_lambda())
 944
 945            while self._next and self._match_text_seq(")", "APPLY", "("):
 946                self._match(TokenType.R_PAREN)
 947                this = exp.Apply(this=this, expression=self._parse_var(any_token=True))
 948            return this
 949
 950        def _parse_value(self, values: bool = True) -> t.Optional[exp.Tuple]:
 951            value = super()._parse_value(values=values)
 952            if not value:
 953                return None
 954
 955            # In Clickhouse "SELECT * FROM VALUES (1, 2, 3)" generates a table with a single column, in contrast
 956            # to other dialects. For this case, we canonicalize the values into a tuple-of-tuples AST if it's not already one.
 957            # In INSERT INTO statements the same clause actually references multiple columns (opposite semantics),
 958            # but the final result is not altered by the extra parentheses.
 959            # Note: Clickhouse allows VALUES([structure], value, ...) so the branch checks for the last expression
 960            expressions = value.expressions
 961            if values and not isinstance(expressions[-1], exp.Tuple):
 962                value.set(
 963                    "expressions",
 964                    [self.expression(exp.Tuple, expressions=[expr]) for expr in expressions],
 965                )
 966
 967            return value
 968
 969    class Generator(generator.Generator):
 970        QUERY_HINTS = False
 971        STRUCT_DELIMITER = ("(", ")")
 972        NVL2_SUPPORTED = False
 973        TABLESAMPLE_REQUIRES_PARENS = False
 974        TABLESAMPLE_SIZE_IS_ROWS = False
 975        TABLESAMPLE_KEYWORDS = "SAMPLE"
 976        LAST_DAY_SUPPORTS_DATE_PART = False
 977        CAN_IMPLEMENT_ARRAY_ANY = True
 978        SUPPORTS_TO_NUMBER = False
 979        JOIN_HINTS = False
 980        TABLE_HINTS = False
 981        GROUPINGS_SEP = ""
 982        SET_OP_MODIFIERS = False
 983        ARRAY_SIZE_NAME = "LENGTH"
 984        WRAP_DERIVED_VALUES = False
 985
 986        STRING_TYPE_MAPPING = {
 987            exp.DataType.Type.BLOB: "String",
 988            exp.DataType.Type.CHAR: "String",
 989            exp.DataType.Type.LONGBLOB: "String",
 990            exp.DataType.Type.LONGTEXT: "String",
 991            exp.DataType.Type.MEDIUMBLOB: "String",
 992            exp.DataType.Type.MEDIUMTEXT: "String",
 993            exp.DataType.Type.TINYBLOB: "String",
 994            exp.DataType.Type.TINYTEXT: "String",
 995            exp.DataType.Type.TEXT: "String",
 996            exp.DataType.Type.VARBINARY: "String",
 997            exp.DataType.Type.VARCHAR: "String",
 998        }
 999
1000        SUPPORTED_JSON_PATH_PARTS = {
1001            exp.JSONPathKey,
1002            exp.JSONPathRoot,
1003            exp.JSONPathSubscript,
1004        }
1005
1006        TYPE_MAPPING = {
1007            **generator.Generator.TYPE_MAPPING,
1008            **STRING_TYPE_MAPPING,
1009            exp.DataType.Type.ARRAY: "Array",
1010            exp.DataType.Type.BOOLEAN: "Bool",
1011            exp.DataType.Type.BIGINT: "Int64",
1012            exp.DataType.Type.DATE32: "Date32",
1013            exp.DataType.Type.DATETIME: "DateTime",
1014            exp.DataType.Type.DATETIME2: "DateTime",
1015            exp.DataType.Type.SMALLDATETIME: "DateTime",
1016            exp.DataType.Type.DATETIME64: "DateTime64",
1017            exp.DataType.Type.DECIMAL: "Decimal",
1018            exp.DataType.Type.DECIMAL32: "Decimal32",
1019            exp.DataType.Type.DECIMAL64: "Decimal64",
1020            exp.DataType.Type.DECIMAL128: "Decimal128",
1021            exp.DataType.Type.DECIMAL256: "Decimal256",
1022            exp.DataType.Type.TIMESTAMP: "DateTime",
1023            exp.DataType.Type.TIMESTAMPNTZ: "DateTime",
1024            exp.DataType.Type.TIMESTAMPTZ: "DateTime",
1025            exp.DataType.Type.DOUBLE: "Float64",
1026            exp.DataType.Type.ENUM: "Enum",
1027            exp.DataType.Type.ENUM8: "Enum8",
1028            exp.DataType.Type.ENUM16: "Enum16",
1029            exp.DataType.Type.FIXEDSTRING: "FixedString",
1030            exp.DataType.Type.FLOAT: "Float32",
1031            exp.DataType.Type.INT: "Int32",
1032            exp.DataType.Type.MEDIUMINT: "Int32",
1033            exp.DataType.Type.INT128: "Int128",
1034            exp.DataType.Type.INT256: "Int256",
1035            exp.DataType.Type.LOWCARDINALITY: "LowCardinality",
1036            exp.DataType.Type.MAP: "Map",
1037            exp.DataType.Type.NESTED: "Nested",
1038            exp.DataType.Type.NOTHING: "Nothing",
1039            exp.DataType.Type.SMALLINT: "Int16",
1040            exp.DataType.Type.STRUCT: "Tuple",
1041            exp.DataType.Type.TINYINT: "Int8",
1042            exp.DataType.Type.UBIGINT: "UInt64",
1043            exp.DataType.Type.UINT: "UInt32",
1044            exp.DataType.Type.UINT128: "UInt128",
1045            exp.DataType.Type.UINT256: "UInt256",
1046            exp.DataType.Type.USMALLINT: "UInt16",
1047            exp.DataType.Type.UTINYINT: "UInt8",
1048            exp.DataType.Type.IPV4: "IPv4",
1049            exp.DataType.Type.IPV6: "IPv6",
1050            exp.DataType.Type.POINT: "Point",
1051            exp.DataType.Type.RING: "Ring",
1052            exp.DataType.Type.LINESTRING: "LineString",
1053            exp.DataType.Type.MULTILINESTRING: "MultiLineString",
1054            exp.DataType.Type.POLYGON: "Polygon",
1055            exp.DataType.Type.MULTIPOLYGON: "MultiPolygon",
1056            exp.DataType.Type.AGGREGATEFUNCTION: "AggregateFunction",
1057            exp.DataType.Type.SIMPLEAGGREGATEFUNCTION: "SimpleAggregateFunction",
1058            exp.DataType.Type.DYNAMIC: "Dynamic",
1059        }
1060
1061        TRANSFORMS = {
1062            **generator.Generator.TRANSFORMS,
1063            exp.AnyValue: rename_func("any"),
1064            exp.ApproxDistinct: rename_func("uniq"),
1065            exp.ArrayConcat: rename_func("arrayConcat"),
1066            exp.ArrayFilter: lambda self, e: self.func("arrayFilter", e.expression, e.this),
1067            exp.ArrayRemove: remove_from_array_using_filter,
1068            exp.ArraySum: rename_func("arraySum"),
1069            exp.ArgMax: arg_max_or_min_no_count("argMax"),
1070            exp.ArgMin: arg_max_or_min_no_count("argMin"),
1071            exp.Array: inline_array_sql,
1072            exp.CastToStrType: rename_func("CAST"),
1073            exp.CountIf: rename_func("countIf"),
1074            exp.CompressColumnConstraint: lambda self,
1075            e: f"CODEC({self.expressions(e, key='this', flat=True)})",
1076            exp.ComputedColumnConstraint: lambda self,
1077            e: f"{'MATERIALIZED' if e.args.get('persisted') else 'ALIAS'} {self.sql(e, 'this')}",
1078            exp.CurrentDate: lambda self, e: self.func("CURRENT_DATE"),
1079            exp.DateAdd: _datetime_delta_sql("DATE_ADD"),
1080            exp.DateDiff: _datetime_delta_sql("DATE_DIFF"),
1081            exp.DateStrToDate: rename_func("toDate"),
1082            exp.DateSub: _datetime_delta_sql("DATE_SUB"),
1083            exp.Explode: rename_func("arrayJoin"),
1084            exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
1085            exp.IsNan: rename_func("isNaN"),
1086            exp.JSONCast: lambda self, e: f"{self.sql(e, 'this')}.:{self.sql(e, 'to')}",
1087            exp.JSONExtract: json_extract_segments("JSONExtractString", quoted_index=False),
1088            exp.JSONExtractScalar: json_extract_segments("JSONExtractString", quoted_index=False),
1089            exp.JSONPathKey: json_path_key_only_name,
1090            exp.JSONPathRoot: lambda *_: "",
1091            exp.Length: length_or_char_length_sql,
1092            exp.Map: _map_sql,
1093            exp.Median: rename_func("median"),
1094            exp.Nullif: rename_func("nullIf"),
1095            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
1096            exp.Pivot: no_pivot_sql,
1097            exp.Quantile: _quantile_sql,
1098            exp.RegexpLike: lambda self, e: self.func("match", e.this, e.expression),
1099            exp.Rand: rename_func("randCanonical"),
1100            exp.StartsWith: rename_func("startsWith"),
1101            exp.EndsWith: rename_func("endsWith"),
1102            exp.StrPosition: lambda self, e: strposition_sql(
1103                self,
1104                e,
1105                func_name="POSITION",
1106                supports_position=True,
1107                use_ansi_position=False,
1108            ),
1109            exp.TimeToStr: lambda self, e: self.func(
1110                "formatDateTime", e.this, self.format_time(e), e.args.get("zone")
1111            ),
1112            exp.TimeStrToTime: _timestrtotime_sql,
1113            exp.TimestampAdd: _datetime_delta_sql("TIMESTAMP_ADD"),
1114            exp.TimestampSub: _datetime_delta_sql("TIMESTAMP_SUB"),
1115            exp.VarMap: _map_sql,
1116            exp.Xor: lambda self, e: self.func("xor", e.this, e.expression, *e.expressions),
1117            exp.MD5Digest: rename_func("MD5"),
1118            exp.MD5: lambda self, e: self.func("LOWER", self.func("HEX", self.func("MD5", e.this))),
1119            exp.SHA: rename_func("SHA1"),
1120            exp.SHA2: sha256_sql,
1121            exp.UnixToTime: _unix_to_time_sql,
1122            exp.TimestampTrunc: timestamptrunc_sql(zone=True),
1123            exp.Trim: lambda self, e: trim_sql(self, e, default_trim_type="BOTH"),
1124            exp.Variance: rename_func("varSamp"),
1125            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
1126            exp.Stddev: rename_func("stddevSamp"),
1127            exp.Chr: rename_func("CHAR"),
1128            exp.Lag: lambda self, e: self.func(
1129                "lagInFrame", e.this, e.args.get("offset"), e.args.get("default")
1130            ),
1131            exp.Lead: lambda self, e: self.func(
1132                "leadInFrame", e.this, e.args.get("offset"), e.args.get("default")
1133            ),
1134            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
1135                rename_func("editDistance")
1136            ),
1137        }
1138
1139        PROPERTIES_LOCATION = {
1140            **generator.Generator.PROPERTIES_LOCATION,
1141            exp.OnCluster: exp.Properties.Location.POST_NAME,
1142            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
1143            exp.ToTableProperty: exp.Properties.Location.POST_NAME,
1144            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
1145        }
1146
1147        # There's no list in docs, but it can be found in Clickhouse code
1148        # see `ClickHouse/src/Parsers/ParserCreate*.cpp`
1149        ON_CLUSTER_TARGETS = {
1150            "SCHEMA",  # Transpiled CREATE SCHEMA may have OnCluster property set
1151            "DATABASE",
1152            "TABLE",
1153            "VIEW",
1154            "DICTIONARY",
1155            "INDEX",
1156            "FUNCTION",
1157            "NAMED COLLECTION",
1158        }
1159
1160        # https://clickhouse.com/docs/en/sql-reference/data-types/nullable
1161        NON_NULLABLE_TYPES = {
1162            exp.DataType.Type.ARRAY,
1163            exp.DataType.Type.MAP,
1164            exp.DataType.Type.STRUCT,
1165            exp.DataType.Type.POINT,
1166            exp.DataType.Type.RING,
1167            exp.DataType.Type.LINESTRING,
1168            exp.DataType.Type.MULTILINESTRING,
1169            exp.DataType.Type.POLYGON,
1170            exp.DataType.Type.MULTIPOLYGON,
1171        }
1172
1173        def strtodate_sql(self, expression: exp.StrToDate) -> str:
1174            strtodate_sql = self.function_fallback_sql(expression)
1175
1176            if not isinstance(expression.parent, exp.Cast):
1177                # StrToDate returns DATEs in other dialects (eg. postgres), so
1178                # this branch aims to improve the transpilation to clickhouse
1179                return self.cast_sql(exp.cast(expression, "DATE"))
1180
1181            return strtodate_sql
1182
1183        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
1184            this = expression.this
1185
1186            if isinstance(this, exp.StrToDate) and expression.to == exp.DataType.build("datetime"):
1187                return self.sql(this)
1188
1189            return super().cast_sql(expression, safe_prefix=safe_prefix)
1190
1191        def trycast_sql(self, expression: exp.TryCast) -> str:
1192            dtype = expression.to
1193            if not dtype.is_type(*self.NON_NULLABLE_TYPES, check_nullable=True):
1194                # Casting x into Nullable(T) appears to behave similarly to TRY_CAST(x AS T)
1195                dtype.set("nullable", True)
1196
1197            return super().cast_sql(expression)
1198
1199        def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
1200            this = self.json_path_part(expression.this)
1201            return str(int(this) + 1) if is_int(this) else this
1202
1203        def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
1204            return f"AS {self.sql(expression, 'this')}"
1205
1206        def _any_to_has(
1207            self,
1208            expression: exp.EQ | exp.NEQ,
1209            default: t.Callable[[t.Any], str],
1210            prefix: str = "",
1211        ) -> str:
1212            if isinstance(expression.left, exp.Any):
1213                arr = expression.left
1214                this = expression.right
1215            elif isinstance(expression.right, exp.Any):
1216                arr = expression.right
1217                this = expression.left
1218            else:
1219                return default(expression)
1220
1221            return prefix + self.func("has", arr.this.unnest(), this)
1222
1223        def eq_sql(self, expression: exp.EQ) -> str:
1224            return self._any_to_has(expression, super().eq_sql)
1225
1226        def neq_sql(self, expression: exp.NEQ) -> str:
1227            return self._any_to_has(expression, super().neq_sql, "NOT ")
1228
1229        def regexpilike_sql(self, expression: exp.RegexpILike) -> str:
1230            # Manually add a flag to make the search case-insensitive
1231            regex = self.func("CONCAT", "'(?i)'", expression.expression)
1232            return self.func("match", expression.this, regex)
1233
1234        def datatype_sql(self, expression: exp.DataType) -> str:
1235            # String is the standard ClickHouse type, every other variant is just an alias.
1236            # Additionally, any supplied length parameter will be ignored.
1237            #
1238            # https://clickhouse.com/docs/en/sql-reference/data-types/string
1239            if expression.this in self.STRING_TYPE_MAPPING:
1240                dtype = "String"
1241            else:
1242                dtype = super().datatype_sql(expression)
1243
1244            # This section changes the type to `Nullable(...)` if the following conditions hold:
1245            # - It's marked as nullable - this ensures we won't wrap ClickHouse types with `Nullable`
1246            #   and change their semantics
1247            # - It's not the key type of a `Map`. This is because ClickHouse enforces the following
1248            #   constraint: "Type of Map key must be a type, that can be represented by integer or
1249            #   String or FixedString (possibly LowCardinality) or UUID or IPv6"
1250            # - It's not a composite type, e.g. `Nullable(Array(...))` is not a valid type
1251            parent = expression.parent
1252            nullable = expression.args.get("nullable")
1253            if nullable is True or (
1254                nullable is None
1255                and not (
1256                    isinstance(parent, exp.DataType)
1257                    and parent.is_type(exp.DataType.Type.MAP, check_nullable=True)
1258                    and expression.index in (None, 0)
1259                )
1260                and not expression.is_type(*self.NON_NULLABLE_TYPES, check_nullable=True)
1261            ):
1262                dtype = f"Nullable({dtype})"
1263
1264            return dtype
1265
1266        def cte_sql(self, expression: exp.CTE) -> str:
1267            if expression.args.get("scalar"):
1268                this = self.sql(expression, "this")
1269                alias = self.sql(expression, "alias")
1270                return f"{this} AS {alias}"
1271
1272            return super().cte_sql(expression)
1273
1274        def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
1275            return super().after_limit_modifiers(expression) + [
1276                (
1277                    self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
1278                    if expression.args.get("settings")
1279                    else ""
1280                ),
1281                (
1282                    self.seg("FORMAT ") + self.sql(expression, "format")
1283                    if expression.args.get("format")
1284                    else ""
1285                ),
1286            ]
1287
1288        def placeholder_sql(self, expression: exp.Placeholder) -> str:
1289            return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"
1290
1291        def oncluster_sql(self, expression: exp.OnCluster) -> str:
1292            return f"ON CLUSTER {self.sql(expression, 'this')}"
1293
1294        def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
1295            if expression.kind in self.ON_CLUSTER_TARGETS and locations.get(
1296                exp.Properties.Location.POST_NAME
1297            ):
1298                this_name = self.sql(
1299                    expression.this if isinstance(expression.this, exp.Schema) else expression,
1300                    "this",
1301                )
1302                this_properties = " ".join(
1303                    [self.sql(prop) for prop in locations[exp.Properties.Location.POST_NAME]]
1304                )
1305                this_schema = self.schema_columns_sql(expression.this)
1306                this_schema = f"{self.sep()}{this_schema}" if this_schema else ""
1307
1308                return f"{this_name}{self.sep()}{this_properties}{this_schema}"
1309
1310            return super().createable_sql(expression, locations)
1311
1312        def create_sql(self, expression: exp.Create) -> str:
1313            # The comment property comes last in CTAS statements, i.e. after the query
1314            query = expression.expression
1315            if isinstance(query, exp.Query):
1316                comment_prop = expression.find(exp.SchemaCommentProperty)
1317                if comment_prop:
1318                    comment_prop.pop()
1319                    query.replace(exp.paren(query))
1320            else:
1321                comment_prop = None
1322
1323            create_sql = super().create_sql(expression)
1324
1325            comment_sql = self.sql(comment_prop)
1326            comment_sql = f" {comment_sql}" if comment_sql else ""
1327
1328            return f"{create_sql}{comment_sql}"
1329
1330        def prewhere_sql(self, expression: exp.PreWhere) -> str:
1331            this = self.indent(self.sql(expression, "this"))
1332            return f"{self.seg('PREWHERE')}{self.sep()}{this}"
1333
1334        def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
1335            this = self.sql(expression, "this")
1336            this = f" {this}" if this else ""
1337            expr = self.sql(expression, "expression")
1338            expr = f" {expr}" if expr else ""
1339            index_type = self.sql(expression, "index_type")
1340            index_type = f" TYPE {index_type}" if index_type else ""
1341            granularity = self.sql(expression, "granularity")
1342            granularity = f" GRANULARITY {granularity}" if granularity else ""
1343
1344            return f"INDEX{this}{expr}{index_type}{granularity}"
1345
1346        def partition_sql(self, expression: exp.Partition) -> str:
1347            return f"PARTITION {self.expressions(expression, flat=True)}"
1348
1349        def partitionid_sql(self, expression: exp.PartitionId) -> str:
1350            return f"ID {self.sql(expression.this)}"
1351
1352        def replacepartition_sql(self, expression: exp.ReplacePartition) -> str:
1353            return (
1354                f"REPLACE {self.sql(expression.expression)} FROM {self.sql(expression, 'source')}"
1355            )
1356
1357        def projectiondef_sql(self, expression: exp.ProjectionDef) -> str:
1358            return f"PROJECTION {self.sql(expression.this)} {self.wrap(expression.expression)}"
1359
1360        def is_sql(self, expression: exp.Is) -> str:
1361            is_sql = super().is_sql(expression)
1362
1363            if isinstance(expression.parent, exp.Not):
1364                # value IS NOT NULL -> NOT (value IS NULL)
1365                is_sql = self.wrap(is_sql)
1366
1367            return is_sql
1368
1369        def in_sql(self, expression: exp.In) -> str:
1370            in_sql = super().in_sql(expression)
1371
1372            if isinstance(expression.parent, exp.Not) and expression.args.get("is_global"):
1373                in_sql = in_sql.replace("GLOBAL IN", "GLOBAL NOT IN", 1)
1374
1375            return in_sql
1376
1377        def not_sql(self, expression: exp.Not) -> str:
1378            if isinstance(expression.this, exp.In) and expression.this.args.get("is_global"):
1379                # let `GLOBAL IN` child interpose `NOT`
1380                return self.sql(expression, "this")
1381
1382            return super().not_sql(expression)
1383
1384        def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
1385            # If the VALUES clause contains tuples of expressions, we need to treat it
1386            # as a table since Clickhouse will automatically alias it as such.
1387            alias = expression.args.get("alias")
1388
1389            if alias and alias.args.get("columns") and expression.expressions:
1390                values = expression.expressions[0].expressions
1391                values_as_table = any(isinstance(value, exp.Tuple) for value in values)
1392            else:
1393                values_as_table = True
1394
1395            return super().values_sql(expression, values_as_table=values_as_table)