sqlglot.dialects.clickhouse

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