sqlglot.dialects.clickhouse

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