sqlglot.dialects.clickhouse

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