sqlglot.generators.clickhouse

  1from __future__ import annotations
  2
  3import datetime
  4import typing as t
  5
  6from sqlglot import exp, generator
  7from sqlglot.dialects.dialect import (
  8    arg_max_or_min_no_count,
  9    inline_array_sql,
 10    jarowinkler_similarity,
 11    json_extract_segments,
 12    json_path_key_only_name,
 13    length_or_char_length_sql,
 14    no_pivot_sql,
 15    rename_func,
 16    remove_from_array_using_filter,
 17    sha256_sql,
 18    strposition_sql,
 19    var_map_sql,
 20    weekstart_unit_to_str,
 21    unit_to_var,
 22    trim_sql,
 23    sha2_digest_sql,
 24)
 25from sqlglot.generator import unsupported_args
 26from sqlglot.helper import is_int
 27from collections import defaultdict
 28
 29DATETIME_DELTA = t.Union[exp.DateAdd, exp.DateDiff, exp.DateSub, exp.TimestampSub, exp.TimestampAdd]
 30
 31
 32def _unix_to_time_sql(self: ClickHouseGenerator, expression: exp.UnixToTime) -> str:
 33    scale = expression.args.get("scale")
 34    timestamp = expression.this
 35
 36    if scale in (None, exp.UnixToTime.SECONDS):
 37        return self.func("fromUnixTimestamp", exp.cast(timestamp, exp.DType.BIGINT))
 38    if scale == exp.UnixToTime.MILLIS:
 39        return self.func("fromUnixTimestamp64Milli", exp.cast(timestamp, exp.DType.BIGINT))
 40    if scale == exp.UnixToTime.MICROS:
 41        return self.func("fromUnixTimestamp64Micro", exp.cast(timestamp, exp.DType.BIGINT))
 42    if scale == exp.UnixToTime.NANOS:
 43        return self.func("fromUnixTimestamp64Nano", exp.cast(timestamp, exp.DType.BIGINT))
 44
 45    return self.func(
 46        "fromUnixTimestamp",
 47        exp.cast(exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)), exp.DType.BIGINT),
 48    )
 49
 50
 51def _lower_func(sql: str) -> str:
 52    index = sql.index("(")
 53    return sql[:index].lower() + sql[index:]
 54
 55
 56def _quantile_sql(self: ClickHouseGenerator, expression: exp.Quantile) -> str:
 57    quantile = expression.args["quantile"]
 58    args = f"({self.sql(expression, 'this')})"
 59
 60    if isinstance(quantile, exp.Array):
 61        func = self.func("quantiles", *quantile)
 62    else:
 63        func = self.func("quantile", quantile)
 64
 65    return func + args
 66
 67
 68def _datetime_delta_sql(name: str) -> t.Callable[[generator.Generator, DATETIME_DELTA], str]:
 69    def _delta_sql(self: generator.Generator, expression: DATETIME_DELTA) -> str:
 70        if not expression.unit:
 71            return rename_func(name)(self, expression)
 72
 73        return self.func(
 74            name,
 75            unit_to_var(expression),
 76            expression.expression,
 77            expression.this,
 78            expression.args.get("zone"),
 79        )
 80
 81    return _delta_sql
 82
 83
 84def _timestrtotime_sql(self: ClickHouseGenerator, expression: exp.TimeStrToTime):
 85    ts = expression.this
 86
 87    tz = expression.args.get("zone")
 88    if tz and isinstance(ts, exp.Literal):
 89        # Clickhouse will not accept timestamps that include a UTC offset, so we must remove them.
 90        # The first step to removing is parsing the string with `datetime.datetime.fromisoformat`.
 91        #
 92        # In python <3.11, `fromisoformat()` can only parse timestamps of millisecond (3 digit)
 93        # or microsecond (6 digit) precision. It will error if passed any other number of fractional
 94        # digits, so we extract the fractional seconds and pad to 6 digits before parsing.
 95        ts_string = ts.name.strip()
 96
 97        # separate [date and time] from [fractional seconds and UTC offset]
 98        ts_parts = ts_string.split(".")
 99        if len(ts_parts) == 2:
100            # separate fractional seconds and UTC offset
101            offset_sep = "+" if "+" in ts_parts[1] else "-"
102            ts_frac_parts = ts_parts[1].split(offset_sep)
103            num_frac_parts = len(ts_frac_parts)
104
105            # pad to 6 digits if fractional seconds present
106            ts_frac_parts[0] = ts_frac_parts[0].ljust(6, "0")
107            ts_string = "".join(
108                [
109                    ts_parts[0],  # date and time
110                    ".",
111                    ts_frac_parts[0],  # fractional seconds
112                    offset_sep if num_frac_parts > 1 else "",
113                    ts_frac_parts[1] if num_frac_parts > 1 else "",  # utc offset (if present)
114                ]
115            )
116
117        # return literal with no timezone, eg turn '2020-01-01 12:13:14-08:00' into '2020-01-01 12:13:14'
118        # this is because Clickhouse encodes the timezone as a data type parameter and throws an error if
119        # it's part of the timestamp string
120        ts_without_tz = (
121            datetime.datetime.fromisoformat(ts_string).replace(tzinfo=None).isoformat(sep=" ")
122        )
123        ts = exp.Literal.string(ts_without_tz)
124
125    # Non-nullable DateTime64 with microsecond precision
126    expressions = [exp.DataTypeParam(this=tz)] if tz else []
127    datatype = exp.DType.DATETIME64.into_expr(
128        expressions=[exp.DataTypeParam(this=exp.Literal.number(6)), *expressions],
129        nullable=False,
130    )
131
132    return self.sql(exp.cast(ts, datatype, dialect=self.dialect))
133
134
135def _map_sql(self: ClickHouseGenerator, expression: exp.Map | exp.VarMap) -> str:
136    if not (expression.parent and expression.parent.arg_key == "settings"):
137        return _lower_func(var_map_sql(self, expression))
138
139    keys = expression.args.get("keys")
140    values = expression.args.get("values")
141
142    if not isinstance(keys, exp.Array) or not isinstance(values, exp.Array):
143        self.unsupported("Cannot convert array columns into map.")
144        return ""
145
146    args = []
147    for key, value in zip(keys.expressions, values.expressions):
148        args.append(f"{self.sql(key)}: {self.sql(value)}")
149
150    csv_args = ", ".join(args)
151
152    return f"{{{csv_args}}}"
153
154
155def _json_cast_sql(self: ClickHouseGenerator, expression: exp.JSONCast) -> str:
156    this = self.sql(expression, "this")
157    to = expression.to
158    to_sql = self.sql(to)
159
160    if to.expressions:
161        to_sql = self.sql(exp.to_identifier(to_sql))
162
163    return f"{this}.:{to_sql}"
164
165
166class ClickHouseGenerator(generator.Generator):
167    BYTE_STRING_FUNCTION = "unhex"
168    SELECT_KINDS: tuple[str, ...] = ()
169    TRY_SUPPORTED = False
170    SUPPORTS_UESCAPE = False
171    SUPPORTS_DECODE_CASE = False
172
173    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
174
175    QUERY_HINTS = False
176    STRUCT_DELIMITER = ("(", ")")
177    NVL2_SUPPORTED = False
178    ALTER_SET_TYPE = "TYPE"
179    SUPPORTS_ALTER_COLUMN_IF_EXISTS = True
180    TABLESAMPLE_REQUIRES_PARENS = False
181    TABLESAMPLE_SIZE_IS_ROWS = False
182    TABLESAMPLE_KEYWORDS = "SAMPLE"
183    LAST_DAY_SUPPORTS_DATE_PART = False
184    CAN_IMPLEMENT_ARRAY_ANY = True
185    SUPPORTS_TO_NUMBER = False
186    JOIN_HINTS = False
187    TABLE_HINTS = False
188    GROUPINGS_SEP = ""
189    SET_OP_MODIFIERS = False
190    SET_OP_LIMITS = True
191    ARRAY_SIZE_NAME = "LENGTH"
192    WRAP_DERIVED_VALUES = False
193    AUTO_REFRESH_BARE_INTERVALS = True
194
195    STRING_TYPE_MAPPING: t.ClassVar = {
196        exp.DType.BLOB: "String",
197        exp.DType.CHAR: "String",
198        exp.DType.LONGBLOB: "String",
199        exp.DType.LONGTEXT: "String",
200        exp.DType.MEDIUMBLOB: "String",
201        exp.DType.MEDIUMTEXT: "String",
202        exp.DType.TINYBLOB: "String",
203        exp.DType.TINYTEXT: "String",
204        exp.DType.TEXT: "String",
205        exp.DType.VARBINARY: "String",
206        exp.DType.VARCHAR: "String",
207    }
208
209    SUPPORTED_JSON_PATH_PARTS = {
210        exp.JSONPathKey,
211        exp.JSONPathRoot,
212        exp.JSONPathSubscript,
213    }
214
215    TYPE_MAPPING = {
216        **generator.Generator.TYPE_MAPPING,
217        exp.DType.BLOB: "String",
218        exp.DType.CHAR: "String",
219        exp.DType.LONGBLOB: "String",
220        exp.DType.LONGTEXT: "String",
221        exp.DType.MEDIUMBLOB: "String",
222        exp.DType.MEDIUMTEXT: "String",
223        exp.DType.TINYBLOB: "String",
224        exp.DType.TINYTEXT: "String",
225        exp.DType.TEXT: "String",
226        exp.DType.VARBINARY: "String",
227        exp.DType.VARCHAR: "String",
228        exp.DType.ARRAY: "Array",
229        exp.DType.BOOLEAN: "Bool",
230        exp.DType.BIGINT: "Int64",
231        exp.DType.DATE32: "Date32",
232        exp.DType.DATETIME: "DateTime",
233        exp.DType.DATETIME2: "DateTime",
234        exp.DType.SMALLDATETIME: "DateTime",
235        exp.DType.DATETIME64: "DateTime64",
236        exp.DType.DECIMAL: "Decimal",
237        exp.DType.DECIMAL32: "Decimal32",
238        exp.DType.DECIMAL64: "Decimal64",
239        exp.DType.DECIMAL128: "Decimal128",
240        exp.DType.DECIMAL256: "Decimal256",
241        exp.DType.TIMESTAMP: "DateTime",
242        exp.DType.TIMESTAMPNTZ: "DateTime",
243        exp.DType.TIMESTAMPTZ: "DateTime",
244        exp.DType.DOUBLE: "Float64",
245        exp.DType.ENUM: "Enum",
246        exp.DType.ENUM8: "Enum8",
247        exp.DType.ENUM16: "Enum16",
248        exp.DType.FIXEDSTRING: "FixedString",
249        exp.DType.FLOAT: "Float32",
250        exp.DType.INT: "Int32",
251        exp.DType.MEDIUMINT: "Int32",
252        exp.DType.INT128: "Int128",
253        exp.DType.INT256: "Int256",
254        exp.DType.LOWCARDINALITY: "LowCardinality",
255        exp.DType.MAP: "Map",
256        exp.DType.NESTED: "Nested",
257        exp.DType.NOTHING: "Nothing",
258        exp.DType.SMALLINT: "Int16",
259        exp.DType.STRUCT: "Tuple",
260        exp.DType.TINYINT: "Int8",
261        exp.DType.UBIGINT: "UInt64",
262        exp.DType.UINT: "UInt32",
263        exp.DType.UINT128: "UInt128",
264        exp.DType.UINT256: "UInt256",
265        exp.DType.USMALLINT: "UInt16",
266        exp.DType.UTINYINT: "UInt8",
267        exp.DType.IPV4: "IPv4",
268        exp.DType.IPV6: "IPv6",
269        exp.DType.POINT: "Point",
270        exp.DType.RING: "Ring",
271        exp.DType.LINESTRING: "LineString",
272        exp.DType.MULTILINESTRING: "MultiLineString",
273        exp.DType.POLYGON: "Polygon",
274        exp.DType.MULTIPOLYGON: "MultiPolygon",
275        exp.DType.AGGREGATEFUNCTION: "AggregateFunction",
276        exp.DType.SIMPLEAGGREGATEFUNCTION: "SimpleAggregateFunction",
277        exp.DType.DYNAMIC: "Dynamic",
278    }
279
280    TRANSFORMS = {
281        **{k: v for k, v in generator.Generator.TRANSFORMS.items() if k != exp.AutoRefreshProperty},
282        exp.AnyValue: rename_func("any"),
283        exp.ApproxDistinct: rename_func("uniq"),
284        exp.ArrayDistinct: rename_func("arrayDistinct"),
285        exp.ArrayConcat: rename_func("arrayConcat"),
286        exp.ArrayContains: rename_func("has"),
287        exp.ArrayFilter: lambda self, e: self.func("arrayFilter", e.expression, e.this),
288        exp.Transform: lambda self, e: self.func("arrayMap", e.expression, e.this),
289        exp.ArrayRemove: remove_from_array_using_filter,
290        exp.ArrayReverse: rename_func("arrayReverse"),
291        exp.ArraySlice: rename_func("arraySlice"),
292        exp.ArraySum: rename_func("arraySum"),
293        exp.ArrayMax: rename_func("arrayMax"),
294        exp.ArrayMin: rename_func("arrayMin"),
295        exp.ArgMax: arg_max_or_min_no_count("argMax"),
296        exp.ArgMin: arg_max_or_min_no_count("argMin"),
297        exp.Array: inline_array_sql,
298        exp.BitwiseAndAgg: rename_func("groupBitAnd"),
299        exp.BitwiseOrAgg: rename_func("groupBitOr"),
300        exp.BitwiseXorAgg: rename_func("groupBitXor"),
301        exp.CityHash64: rename_func("cityHash64"),
302        exp.CastToStrType: rename_func("CAST"),
303        exp.CurrentDatabase: rename_func("CURRENT_DATABASE"),
304        exp.CurrentSchemas: rename_func("CURRENT_SCHEMAS"),
305        exp.CountIf: rename_func("countIf"),
306        exp.CosineDistance: rename_func("cosineDistance"),
307        exp.CompressColumnConstraint: lambda self, e: (
308            f"CODEC({self.expressions(e, key='this', flat=True)})"
309        ),
310        exp.ComputedColumnConstraint: lambda self, e: (
311            f"{'MATERIALIZED' if e.args.get('persisted') else 'ALIAS'} {self.sql(e, 'this')}"
312        ),
313        exp.CurrentDate: lambda self, e: self.func("CURRENT_DATE"),
314        exp.CurrentVersion: rename_func("VERSION"),
315        exp.DateAdd: _datetime_delta_sql("DATE_ADD"),
316        exp.DateDiff: _datetime_delta_sql("DATE_DIFF"),
317        exp.DateStrToDate: rename_func("toDate"),
318        exp.DateSub: _datetime_delta_sql("DATE_SUB"),
319        exp.DayOfMonth: rename_func("toDayOfMonth"),
320        exp.DayOfWeekIso: rename_func("toDayOfWeek"),
321        exp.DayOfYear: rename_func("toDayOfYear"),
322        exp.Explode: rename_func("arrayJoin"),
323        exp.FarmFingerprint: rename_func("farmFingerprint64"),
324        exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
325        exp.IsInf: rename_func("isInfinite"),
326        exp.IsNan: rename_func("isNaN"),
327        exp.JarowinklerSimilarity: jarowinkler_similarity("jaroWinklerSimilarity"),
328        exp.JSONCast: _json_cast_sql,
329        exp.JSONExtract: json_extract_segments("JSONExtractRaw", quoted_index=False),
330        exp.JSONExtractScalar: json_extract_segments("JSONExtractString", quoted_index=False),
331        exp.JSONPathKey: json_path_key_only_name,
332        exp.JSONPathRoot: lambda *_: "",
333        exp.Length: length_or_char_length_sql,
334        exp.Map: _map_sql,
335        exp.Median: rename_func("median"),
336        exp.Nullif: rename_func("nullIf"),
337        exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
338        exp.Pivot: no_pivot_sql,
339        exp.Quantile: _quantile_sql,
340        exp.RegexpLike: lambda self, e: self.func("match", e.this, e.expression),
341        exp.Rand: rename_func("randCanonical"),
342        exp.StartsWith: rename_func("startsWith"),
343        exp.Struct: rename_func("tuple"),
344        exp.Trunc: rename_func("trunc"),
345        exp.EndsWith: rename_func("endsWith"),
346        exp.EuclideanDistance: rename_func("L2Distance"),
347        exp.StrPosition: lambda self, e: strposition_sql(
348            self,
349            e,
350            func_name="POSITION",
351            supports_position=True,
352            use_ansi_position=False,
353        ),
354        exp.TimeToStr: lambda self, e: self.func(
355            "formatDateTime",
356            e.this.this if isinstance(e.this, exp.TsOrDsToTimestamp) else e.this,
357            self.format_time(e),
358            e.args.get("zone"),
359        ),
360        exp.TimeStrToTime: _timestrtotime_sql,
361        exp.TimestampAdd: _datetime_delta_sql("TIMESTAMP_ADD"),
362        exp.TimestampSub: _datetime_delta_sql("TIMESTAMP_SUB"),
363        exp.Typeof: rename_func("toTypeName"),
364        exp.VarMap: _map_sql,
365        exp.Xor: lambda self, e: self.func("xor", e.this, e.expression),
366        exp.MD5Digest: rename_func("MD5"),
367        exp.MD5: lambda self, e: self.func("LOWER", self.func("HEX", self.func("MD5", e.this))),
368        exp.SHA: rename_func("SHA1"),
369        exp.SHA1Digest: rename_func("SHA1"),
370        exp.SHA2: sha256_sql,
371        exp.SHA2Digest: sha2_digest_sql,
372        exp.Split: lambda self, e: self.func(
373            "splitByString", e.args.get("expression"), e.this, e.args.get("limit")
374        ),
375        exp.RegexpSplit: lambda self, e: self.func(
376            "splitByRegexp", e.args.get("expression"), e.this, e.args.get("limit")
377        ),
378        exp.UnixToTime: _unix_to_time_sql,
379        exp.Trim: lambda self, e: trim_sql(self, e, default_trim_type="BOTH"),
380        exp.Variance: rename_func("varSamp"),
381        exp.VariancePop: rename_func("varPop"),
382        exp.WeekOfYear: rename_func("toISOWeek"),
383        exp.YearOfWeekIso: rename_func("toISOYear"),
384        exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
385        exp.Stddev: rename_func("stddevSamp"),
386        exp.Chr: rename_func("CHAR"),
387        exp.Lag: rename_func("lag"),
388        exp.Lead: rename_func("lead"),
389        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
390            rename_func("editDistance")
391        ),
392        exp.ParseDatetime: lambda self, e: self.func(
393            "parseDateTime", e.this, e.args.get("format"), e.args.get("zone")
394        ),
395    }
396
397    PROPERTIES_LOCATION = {
398        **generator.Generator.PROPERTIES_LOCATION,
399        exp.AutoRefreshProperty: exp.Properties.Location.POST_NAME,
400        exp.DefinerProperty: exp.Properties.Location.POST_SCHEMA,
401        exp.OnCluster: exp.Properties.Location.POST_NAME,
402        exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
403        exp.ToTableProperty: exp.Properties.Location.POST_NAME,
404        exp.UuidProperty: exp.Properties.Location.POST_NAME,
405        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
406    }
407
408    # There's no list in docs, but it can be found in Clickhouse code
409    # see `ClickHouse/src/Parsers/ParserCreate*.cpp`
410    ON_CLUSTER_TARGETS: t.ClassVar = {
411        "SCHEMA",  # Transpiled CREATE SCHEMA may have OnCluster property set
412        "DATABASE",
413        "TABLE",
414        "VIEW",
415        "DICTIONARY",
416        "INDEX",
417        "FUNCTION",
418        "NAMED COLLECTION",
419    }
420
421    # https://clickhouse.com/docs/en/sql-reference/data-types/nullable
422    NON_NULLABLE_TYPES: t.ClassVar = {
423        exp.DType.ARRAY,
424        exp.DType.MAP,
425        exp.DType.STRUCT,
426        exp.DType.POINT,
427        exp.DType.RING,
428        exp.DType.LINESTRING,
429        exp.DType.MULTILINESTRING,
430        exp.DType.POLYGON,
431        exp.DType.MULTIPOLYGON,
432    }
433
434    @unsupported_args("json_scope")
435    def contains_sql(self, expression: exp.Contains) -> str:
436        # ClickHouse has no CONTAINS; the comparison needs parentheses inside another operator
437        result = exp.StrPosition(this=expression.this, substr=expression.expression) > 0
438        if isinstance(expression.parent, exp.Binary) and not isinstance(
439            expression.parent, exp.Connector
440        ):
441            return self.sql(exp.paren(result, copy=False))
442        return self.sql(result)
443
444    def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
445        this = expression.this
446        separator = expression.args.get("separator")
447
448        if isinstance(this, exp.Limit) and this.this:
449            limit = this
450            this = limit.this.pop()
451            return self.sql(
452                exp.ParameterizedAgg(
453                    this="groupConcat",
454                    params=[this],
455                    expressions=[separator, limit.expression],
456                )
457            )
458
459        if separator:
460            return self.sql(
461                exp.ParameterizedAgg(
462                    this="groupConcat",
463                    params=[this],
464                    expressions=[separator],
465                )
466            )
467
468        return self.func("groupConcat", this)
469
470    def select_sql(self, expression: exp.Select) -> str:
471        limit = expression.args.get("limit")
472        if isinstance(limit, exp.Fetch) and not expression.args.get("order"):
473            count = limit.args.get("count")
474            expression.set(
475                "limit", exp.Limit(expression=count if count is not None else exp.Literal.number(1))
476            )
477        return super().select_sql(expression)
478
479    def offset_sql(self, expression: exp.Offset) -> str:
480        offset = super().offset_sql(expression)
481
482        # OFFSET ... FETCH syntax requires a "ROW" or "ROWS" keyword
483        # https://clickhouse.com/docs/sql-reference/statements/select/offset
484        parent = expression.parent
485        if isinstance(parent, exp.Select) and isinstance(parent.args.get("limit"), exp.Fetch):
486            offset = f"{offset} ROWS"
487
488        return offset
489
490    def strtodate_sql(self, expression: exp.StrToDate) -> str:
491        strtodate_sql = self.function_fallback_sql(expression)
492
493        if not isinstance(expression.parent, exp.Cast):
494            # StrToDate returns DATEs in other dialects (eg. postgres), so
495            # this branch aims to improve the transpilation to clickhouse
496            return self.cast_sql(exp.cast(expression, "DATE"))
497
498        return strtodate_sql
499
500    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
501        this = expression.this
502
503        if isinstance(this, exp.StrToDate) and expression.to == exp.DType.DATETIME.into_expr():
504            return self.sql(this)
505
506        this = this.unnest()
507        if (
508            isinstance(this, exp.JSONExtract)
509            and this.args.get("requires_json")
510            and expression.to.is_type(*exp.DataType.TEXT_TYPES)
511        ):
512            return self.func("JSONExtract", this, exp.Literal.string("Nullable(String)"))
513
514        return super().cast_sql(expression, safe_prefix=safe_prefix)
515
516    def trycast_sql(self, expression: exp.TryCast) -> str:
517        dtype = expression.to
518        if not dtype.is_type(*self.NON_NULLABLE_TYPES, check_nullable=True):
519            # Casting x into Nullable(T) appears to behave similarly to TRY_CAST(x AS T)
520            dtype.set("nullable", True)
521
522        return super().cast_sql(expression)
523
524    def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
525        this = self.json_path_part(expression.this)
526        return str(int(this) + 1) if is_int(this) else this
527
528    def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
529        return f"AS {self.sql(expression, 'this')}"
530
531    def _any_to_has(
532        self,
533        expression: exp.EQ | exp.NEQ,
534        default: t.Callable[[t.Any], str],
535        prefix: str = "",
536    ) -> str:
537        if isinstance(expression.left, exp.Any):
538            arr = expression.left
539            this = expression.right
540        elif isinstance(expression.right, exp.Any):
541            arr = expression.right
542            this = expression.left
543        else:
544            return default(expression)
545
546        return prefix + self.func("has", arr.this.unnest(), this)
547
548    def eq_sql(self, expression: exp.EQ) -> str:
549        return self._any_to_has(expression, super().eq_sql)
550
551    def neq_sql(self, expression: exp.NEQ) -> str:
552        return self._any_to_has(expression, super().neq_sql, "NOT ")
553
554    def regexpcount_sql(self, expression: exp.RegexpCount) -> str:
555        this = expression.this
556        pattern = expression.expression
557        position = expression.args.get("position")
558        parameters = expression.args.get("parameters")
559
560        if position:
561            # SUBSTRING counts bytes in ClickHouse, the position counts characters
562            this = self.func("substringUTF8", this, position)
563
564        if parameters:
565            flags = parameters.name if parameters.is_string else ""
566            if not parameters.is_string or set(flags) - set("cims"):
567                self.unsupported("Only literal 'c', 'i', 'm' and 's' regexp flags are supported")
568            # "c" (case-sensitive) is the default, and the last of "c" and "i" wins
569            if flags.rfind("c") > flags.rfind("i"):
570                flags = flags.replace("i", "")
571            flags = "".join(f for f in flags if f in "ims")
572            if flags:
573                # countMatches has no flags argument, so they are embedded in the pattern
574                pattern = self.func("CONCAT", exp.Literal.string(f"(?{flags})"), pattern)
575
576        return self.func("countMatches", this, pattern)
577
578    def regexpilike_sql(self, expression: exp.RegexpILike) -> str:
579        # Manually add a flag to make the search case-insensitive
580        regex = self.func("CONCAT", "'(?i)'", expression.expression)
581        return self.func("match", expression.this, regex)
582
583    def datatype_sql(self, expression: exp.DataType) -> str:
584        # String is the standard ClickHouse type, every other variant is just an alias.
585        # Additionally, any supplied length parameter will be ignored.
586        #
587        # https://clickhouse.com/docs/en/sql-reference/data-types/string
588        if expression.this in self.STRING_TYPE_MAPPING:
589            dtype = "String"
590        else:
591            dtype = super().datatype_sql(expression)
592
593        # This section changes the type to `Nullable(...)` if the following conditions hold:
594        # - It's marked as nullable - this ensures we won't wrap ClickHouse types with `Nullable`
595        #   and change their semantics
596        # - It's not the key type of a `Map`. This is because ClickHouse enforces the following
597        #   constraint: "Type of Map key must be a type, that can be represented by integer or
598        #   String or FixedString (possibly LowCardinality) or UUID or IPv6"
599        # - It's not a composite type, e.g. `Nullable(Array(...))` is not a valid type
600        parent = expression.parent
601        nullable = expression.args.get("nullable")
602        if nullable is True or (
603            nullable is None
604            and not (
605                isinstance(parent, exp.DataType)
606                and parent.is_type(exp.DType.MAP, check_nullable=True)
607                and expression.index in (None, 0)
608            )
609            and not expression.is_type(*self.NON_NULLABLE_TYPES, check_nullable=True)
610        ):
611            dtype = f"Nullable({dtype})"
612
613        return dtype
614
615    def cte_sql(self, expression: exp.CTE) -> str:
616        if expression.args.get("scalar"):
617            this = self.sql(expression, "this")
618            alias = self.sql(expression, "alias")
619            return f"{this} AS {alias}"
620
621        return super().cte_sql(expression)
622
623    def after_limit_modifiers(self, expression: exp.Expr) -> list[str]:
624        return super().after_limit_modifiers(expression) + [
625            (
626                self.seg("SETTINGS ") + self.expressions(expression, key="settings", flat=True)
627                if expression.args.get("settings")
628                else ""
629            ),
630            (
631                self.seg("FORMAT ") + self.sql(expression, "format")
632                if expression.args.get("format")
633                else ""
634            ),
635        ]
636
637    def placeholder_sql(self, expression: exp.Placeholder) -> str:
638        return f"{{{expression.name}: {self.sql(expression, 'kind')}}}"
639
640    def oncluster_sql(self, expression: exp.OnCluster) -> str:
641        return f"ON CLUSTER {self.sql(expression, 'this')}"
642
643    def _refresh_interval_sql(self, expression: exp.Expr) -> str:
644        if isinstance(expression, exp.Add):
645            return f"{self._refresh_interval_sql(expression.this)} {self._refresh_interval_sql(expression.expression)}"
646        return self.sql(expression.assert_is(exp.Interval))
647
648    def autorefreshproperty_sql(self, expression: exp.AutoRefreshProperty) -> str:
649        cadence = self.sql(expression, "cadence")
650        interval = expression.this
651        schedule = (
652            f" {cadence} {self._refresh_interval_sql(interval)}" if cadence and interval else ""
653        )
654        offset = expression.args.get("offset")
655        offset = f" OFFSET {self._refresh_interval_sql(offset)}" if offset else ""
656        randomize = expression.args.get("randomize")
657        randomize = f" RANDOMIZE FOR {self._refresh_interval_sql(randomize)}" if randomize else ""
658        dependencies = self.expressions(expression, flat=True)
659        dependencies = f" DEPENDS ON {dependencies}" if dependencies else ""
660        settings = self.sql(expression, "settings")
661        settings = f" {settings}" if settings else ""
662        append = " APPEND" if expression.args.get("append") else ""
663
664        return f"REFRESH{schedule}{offset}{randomize}{dependencies}{settings}{append}"
665
666    def createable_sql(self, expression: exp.Create, locations: defaultdict) -> str:
667        if expression.kind in self.ON_CLUSTER_TARGETS and locations.get(
668            exp.Properties.Location.POST_NAME
669        ):
670            this_name = self.sql(
671                expression.this if isinstance(expression.this, exp.Schema) else expression,
672                "this",
673            )
674            this_properties = " ".join(
675                [self.sql(prop) for prop in locations[exp.Properties.Location.POST_NAME]]
676            )
677            this_schema = self.schema_columns_sql(expression.this)
678            this_schema = f"{self.sep()}{this_schema}" if this_schema else ""
679
680            return f"{this_name}{self.sep()}{this_properties}{this_schema}"
681
682        return super().createable_sql(expression, locations)
683
684    def create_sql(self, expression: exp.Create) -> str:
685        # The comment property comes last in CTAS statements, i.e. after the query
686        query = expression.expression
687        if isinstance(query, exp.Query):
688            comment_prop = expression.find(exp.SchemaCommentProperty)
689            if comment_prop:
690                comment_prop.pop()
691                if not isinstance(query, exp.Subquery):
692                    query.replace(exp.paren(query))
693        else:
694            comment_prop = None
695
696        create_sql = super().create_sql(expression)
697
698        comment_sql = self.sql(comment_prop)
699        comment_sql = f" {comment_sql}" if comment_sql else ""
700
701        return f"{create_sql}{comment_sql}"
702
703    def prewhere_sql(self, expression: exp.PreWhere) -> str:
704        this = self.indent(self.sql(expression, "this"))
705        return f"{self.seg('PREWHERE')}{self.sep()}{this}"
706
707    def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
708        this = self.sql(expression, "this")
709        this = f" {this}" if this else ""
710        expr = self.sql(expression, "expression")
711        expr = f" {expr}" if expr else ""
712        index_type = self.sql(expression, "index_type")
713        index_type = f" TYPE {index_type}" if index_type else ""
714        granularity = self.sql(expression, "granularity")
715        granularity = f" GRANULARITY {granularity}" if granularity else ""
716
717        return f"INDEX{this}{expr}{index_type}{granularity}"
718
719    def partition_sql(self, expression: exp.Partition) -> str:
720        return f"PARTITION {self.expressions(expression, flat=True)}"
721
722    def partitionid_sql(self, expression: exp.PartitionId) -> str:
723        return f"ID {self.sql(expression.this)}"
724
725    def replacepartition_sql(self, expression: exp.ReplacePartition) -> str:
726        return f"REPLACE {self.sql(expression.expression)} FROM {self.sql(expression, 'source')}"
727
728    def projectiondef_sql(self, expression: exp.ProjectionDef) -> str:
729        return f"PROJECTION {self.sql(expression.this)} {self.wrap(expression.expression)}"
730
731    def nestedjsonselect_sql(self, expression: exp.NestedJSONSelect) -> str:
732        return f"{self.sql(expression, 'this')}.^{self.sql(expression, 'expression')}"
733
734    def is_sql(self, expression: exp.Is) -> str:
735        is_sql = super().is_sql(expression)
736
737        if isinstance(expression.parent, exp.Not):
738            # value IS NOT NULL -> NOT (value IS NULL)
739            is_sql = self.wrap(is_sql)
740
741        return is_sql
742
743    def in_sql(self, expression: exp.In) -> str:
744        in_sql = super().in_sql(expression)
745
746        if isinstance(expression.parent, exp.Not) and expression.args.get("is_global"):
747            in_sql = in_sql.replace("GLOBAL IN", "GLOBAL NOT IN", 1)
748
749        return in_sql
750
751    def not_sql(self, expression: exp.Not) -> str:
752        if isinstance(expression.this, exp.In):
753            if expression.this.args.get("is_global"):
754                # let `GLOBAL IN` child interpose `NOT`
755                return self.sql(expression, "this")
756
757            expression.set("this", exp.paren(expression.this, copy=False))
758
759        return super().not_sql(expression)
760
761    def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
762        # If the VALUES clause contains tuples of expressions, we need to treat it
763        # as a table since Clickhouse will automatically alias it as such.
764        alias = expression.args.get("alias")
765
766        if alias and alias.args.get("columns") and expression.expressions:
767            values = expression.expressions[0].expressions
768            values_as_table = any(isinstance(value, exp.Tuple) for value in values)
769        else:
770            values_as_table = True
771
772        return super().values_sql(expression, values_as_table=values_as_table)
773
774    def timestamptrunc_sql(self, expression: exp.DateTrunc | exp.TimestampTrunc) -> str:
775        unit = weekstart_unit_to_str(self, expression)
776        # https://clickhouse.com/docs/whats-new/changelog/2023#improvement
777        if self.dialect.version < (23, 12) and unit and unit.is_string:
778            unit = exp.Literal.string(unit.name.lower())
779        return self.func("dateTrunc", unit, expression.this, expression.args.get("zone"))
780
781    def datetrunc_sql(self, expression: exp.DateTrunc) -> str:
782        return self.timestamptrunc_sql(expression)