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