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