sqlglot.generators.presto

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, transforms
  6from sqlglot.dialects.dialect import (
  7    bool_xor_sql,
  8    bracket_to_element_at_sql,
  9    datestrtodate_sql,
 10    encode_decode_sql,
 11    if_sql,
 12    left_to_substring_sql,
 13    no_ilike_sql,
 14    no_pivot_sql,
 15    no_timestamp_sql,
 16    regexp_extract_sql,
 17    rename_func,
 18    right_to_substring_sql,
 19    strposition_sql,
 20    struct_extract_sql,
 21    timestamptrunc_sql,
 22    timestrtotime_sql,
 23    ts_or_ds_add_cast,
 24    unit_to_str,
 25    week_unit_to_dow,
 26    sequence_sql,
 27    explode_to_unnest_sql,
 28)
 29from sqlglot.dialects.hive import Hive
 30from sqlglot.generator import unsupported_args
 31from sqlglot.optimizer.scope import find_all_in_scope
 32from sqlglot.transforms import unqualify_columns
 33
 34DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub]
 35
 36
 37def _sha2_digest_sql(self: PrestoGenerator, expression: exp.SHA2Digest) -> str:
 38    length = expression.text("length") or "256"
 39    if length not in ("256", "512"):
 40        self.unsupported(f"SHA{length} is not supported in Presto")
 41
 42    this = expression.this
 43    if this.is_type(*exp.DataType.TEXT_TYPES):
 44        # the native digest takes VARBINARY, so a text-typed argument needs encoding
 45        this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
 46
 47    return self.func(f"SHA{length}", this)
 48
 49
 50def _initcap_sql(self: PrestoGenerator, expression: exp.Initcap) -> str:
 51    delimiters = expression.expression
 52    if delimiters and not (
 53        delimiters.is_string and delimiters.this == self.dialect.INITCAP_DEFAULT_DELIMITER_CHARS
 54    ):
 55        self.unsupported("INITCAP does not support custom delimiters")
 56
 57    regex = r"(\w)(\w*)"
 58    return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))"
 59
 60
 61def _no_sort_array(self: PrestoGenerator, expression: exp.SortArray) -> str:
 62    if expression.args.get("asc") == exp.false():
 63        comparator = "(a, b) -> CASE WHEN a < b THEN 1 WHEN a > b THEN -1 ELSE 0 END"
 64    else:
 65        comparator = None
 66    return self.func("ARRAY_SORT", expression.this, comparator)
 67
 68
 69def _schema_sql(self: PrestoGenerator, expression: exp.Schema) -> str:
 70    if isinstance(expression.parent, exp.PartitionedByProperty):
 71        # Any columns in the ARRAY[] string literals should not be quoted
 72        expression.transform(lambda n: n.name if isinstance(n, exp.Identifier) else n, copy=False)
 73
 74        partition_exprs = [
 75            self.sql(c) if isinstance(c, (exp.Func, exp.Property)) else self.sql(c, "this")
 76            for c in expression.expressions
 77        ]
 78        return self.sql(exp.Array(expressions=[exp.Literal.string(c) for c in partition_exprs]))
 79
 80    if expression.parent:
 81        for schema in expression.parent.find_all(exp.Schema):
 82            if schema is expression:
 83                continue
 84
 85            column_defs = schema.find_all(exp.ColumnDef)
 86            if column_defs and isinstance(schema.parent, exp.Property):
 87                expression.expressions.extend(column_defs)
 88
 89    return self.schema_sql(expression)
 90
 91
 92def _quantile_sql(self: PrestoGenerator, expression: exp.Quantile) -> str:
 93    self.unsupported("Presto does not support exact quantiles")
 94    return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile"))
 95
 96
 97def _str_to_time_sql(
 98    self: PrestoGenerator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 99) -> str:
100    return self.func("DATE_PARSE", expression.this, self.format_time(expression))
101
102
103def _ts_or_ds_to_date_sql(self: PrestoGenerator, expression: exp.TsOrDsToDate) -> str:
104    time_format = self.format_time(expression)
105    dialect_class = type(self.dialect)
106    if time_format and time_format not in (dialect_class.TIME_FORMAT, dialect_class.DATE_FORMAT):
107        return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DType.DATE))
108    return self.sql(exp.cast(exp.cast(expression.this, exp.DType.TIMESTAMP), exp.DType.DATE))
109
110
111def _ts_or_ds_add_sql(self: PrestoGenerator, expression: exp.TsOrDsAdd) -> str:
112    expression = ts_or_ds_add_cast(expression)
113    unit = unit_to_str(expression)
114    return self.func("DATE_ADD", unit, expression.expression, expression.this)
115
116
117def _ts_or_ds_diff_sql(self: PrestoGenerator, expression: exp.TsOrDsDiff) -> str:
118    this = exp.cast(expression.this, exp.DType.TIMESTAMP)
119    expr = exp.cast(expression.expression, exp.DType.TIMESTAMP)
120    unit = unit_to_str(expression)
121    return self.func("DATE_DIFF", unit, expr, this)
122
123
124def _first_last_sql(self: PrestoGenerator, expression: exp.Func) -> str:
125    """
126    Trino doesn't support FIRST / LAST as functions, but they're valid in the context
127    of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases
128    they're converted into an ARBITRARY call.
129
130    Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions
131    """
132    if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize):
133        return self.function_fallback_sql(expression)
134
135    return rename_func("ARBITRARY")(self, expression)
136
137
138def _unix_to_time_sql(self: PrestoGenerator, expression: exp.UnixToTime) -> str:
139    scale = expression.args.get("scale")
140    timestamp = self.sql(expression, "this")
141    if scale in (None, exp.UnixToTime.SECONDS):
142        return rename_func("FROM_UNIXTIME")(self, expression)
143
144    return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))"
145
146
147def _to_int(self: PrestoGenerator, expression: exp.Expr) -> exp.Expr:
148    if not expression.type:
149        from sqlglot.optimizer.annotate_types import annotate_types
150
151        annotate_types(expression, dialect=self.dialect)
152    if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES:
153        return exp.cast(expression, to=exp.DType.BIGINT)
154    return expression
155
156
157def _date_delta_sql(
158    name: str, negate_interval: bool = False
159) -> t.Callable[[PrestoGenerator, DATE_ADD_OR_SUB], str]:
160    def _delta_sql(self: PrestoGenerator, expression: DATE_ADD_OR_SUB) -> str:
161        interval = _to_int(self, expression.expression)
162        return self.func(
163            name,
164            unit_to_str(expression),
165            interval * (-1) if negate_interval else interval,
166            expression.this,
167        )
168
169    return _delta_sql
170
171
172def _date_diff_sql(
173    self: PrestoGenerator, expression: exp.DateDiff | exp.DatetimeDiff | exp.TimestampDiff
174) -> str:
175    # Presto/Trino only expose date_diff(unit, ts1, ts2); it returns ts2 - ts1, so the
176    # operands are emitted as (expression, this) to preserve `this - expression` semantics.
177    this: exp.Expr = expression.this
178    expr: exp.Expr = expression.expression
179    unit = unit_to_str(expression)
180
181    # DATE_DIFF counts complete units between its operands, whereas dialects that set
182    # date_part_boundary count unit boundary crossings, so the operands are truncated
183    # down to the unit to make the two coincide
184    if unit and expression.args.get("date_part_boundary"):
185        raw_unit = expression.args.get("unit")
186        dow = week_unit_to_dow(raw_unit)
187
188        if dow is not None:
189            unit = exp.Literal.string("WEEK")
190
191            # DATE_TRUNC('WEEK', ...) is Monday-based; shifting both operands by the same
192            # delta realigns it to the requested week start without changing the diff
193            shift_days = 1 if dow == 7 else 1 - dow
194            if shift_days:
195                delta = exp.Interval(this=exp.Literal.string(str(shift_days)), unit=exp.var("DAY"))
196                this = exp.Add(this=this, expression=delta)
197                expr = exp.Add(this=expr, expression=delta.copy())
198
199        if not isinstance(raw_unit, exp.WeekStart) or dow is not None:
200            this = exp.DateTrunc(unit=unit.copy(), this=this)
201            expr = exp.DateTrunc(unit=unit.copy(), this=expr)
202
203    return self.func("DATE_DIFF", unit, expr, this)
204
205
206def _explode_to_unnest_sql(self: PrestoGenerator, expression: exp.Lateral) -> str:
207    explode = expression.this
208    if isinstance(explode, exp.Explode):
209        exploded_type = explode.this.type
210        alias = expression.args.get("alias")
211
212        # This attempts a best-effort transpilation of LATERAL VIEW EXPLODE on a struct array
213        if (
214            isinstance(alias, exp.TableAlias)
215            and isinstance(exploded_type, exp.DataType)
216            and exploded_type.is_type(exp.DType.ARRAY)
217            and exploded_type.expressions
218            and exploded_type.expressions[0].is_type(exp.DType.STRUCT)
219        ):
220            # When unnesting a ROW in Presto, it produces N columns, so we need to fix the alias
221            alias.set("columns", [c.this.copy() for c in exploded_type.expressions[0].expressions])
222    elif isinstance(explode, exp.Inline):
223        explode.replace(exp.Explode(this=explode.this.copy()))
224
225    return explode_to_unnest_sql(self, expression)
226
227
228def amend_exploded_column_table(expression: exp.Expr) -> exp.Expr:
229    # We check for expression.type because the columns can be amended only if types were inferred
230    if isinstance(expression, exp.Select) and expression.type:
231        for lateral in expression.args.get("laterals") or []:
232            alias = lateral.args.get("alias")
233            if (
234                not isinstance(lateral.this, exp.Explode)
235                or not isinstance(alias, exp.TableAlias)
236                or len(alias.columns) != 1
237            ):
238                continue
239
240            new_table = alias.this
241            old_table = alias.columns[0].name.lower()
242
243            # When transpiling a LATERAL VIEW EXPLODE Spark query, the exploded fields may be qualified
244            # with the struct column, resulting in invalid Presto references that need to be amended
245            for column in find_all_in_scope(expression, exp.Column):
246                if column.db.lower() == old_table:
247                    column.set("table", column.args["db"].pop())
248                elif column.table.lower() == old_table:
249                    column.set("table", new_table.copy())
250                elif column.name.lower() == old_table and isinstance(column.parent, exp.Dot):
251                    column.parent.replace(exp.column(column.parent.expression, table=new_table))
252
253    return expression
254
255
256class PrestoGenerator(generator.Generator):
257    SELECT_KINDS: tuple[str, ...] = ()
258    SUPPORTS_DECODE_CASE = False
259
260    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
261
262    INTERVAL_ALLOWS_PLURAL_FORM = False
263    JOIN_HINTS = False
264    TABLE_HINTS = False
265    QUERY_HINTS = False
266    IS_BOOL_ALLOWED = False
267    TZ_TO_WITH_TIME_ZONE = True
268    NVL2_SUPPORTED = False
269    STRUCT_DELIMITER = ("(", ")")
270    LIMIT_ONLY_LITERALS = True
271    SUPPORTS_SINGLE_ARG_CONCAT = False
272    LIKE_PROPERTY_INSIDE_SCHEMA = True
273    MULTI_ARG_DISTINCT = False
274    SUPPORTS_TO_NUMBER = False
275    HEX_FUNC = "TO_HEX"
276    PARSE_JSON_NAME: str | None = "JSON_PARSE"
277    PAD_FILL_PATTERN_IS_REQUIRED = True
278    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
279    SUPPORTS_MEDIAN = False
280    ARRAY_SIZE_NAME = "CARDINALITY"
281
282    PROPERTIES_LOCATION = {
283        **generator.Generator.PROPERTIES_LOCATION,
284        exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
285        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
286    }
287
288    TYPE_MAPPING = {
289        **generator.Generator.TYPE_MAPPING,
290        exp.DType.BINARY: "VARBINARY",
291        exp.DType.BIT: "BOOLEAN",
292        exp.DType.DATETIME: "TIMESTAMP",
293        exp.DType.DATETIME64: "TIMESTAMP",
294        exp.DType.FLOAT: "REAL",
295        exp.DType.HLLSKETCH: "HYPERLOGLOG",
296        exp.DType.INT: "INTEGER",
297        exp.DType.STRUCT: "ROW",
298        exp.DType.TEXT: "VARCHAR",
299        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
300        exp.DType.TIMESTAMPNTZ: "TIMESTAMP",
301        exp.DType.TIMETZ: "TIME",
302    }
303
304    TRANSFORMS = {
305        **generator.Generator.TRANSFORMS,
306        exp.AnyValue: rename_func("ARBITRARY"),
307        exp.ApproxQuantile: lambda self, e: self.func(
308            "APPROX_PERCENTILE",
309            e.this,
310            e.args.get("weight"),
311            e.args.get("quantile"),
312            e.args.get("accuracy"),
313        ),
314        exp.ArgMax: rename_func("MAX_BY"),
315        exp.ArgMin: rename_func("MIN_BY"),
316        exp.Array: transforms.preprocess(
317            [transforms.inherit_struct_field_names],
318            generator=lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
319        ),
320        exp.ArrayAny: rename_func("ANY_MATCH"),
321        exp.ArrayConcat: rename_func("CONCAT"),
322        exp.ArrayContains: rename_func("CONTAINS"),
323        exp.ArrayToString: rename_func("ARRAY_JOIN"),
324        exp.ArrayUniqueAgg: rename_func("SET_AGG"),
325        exp.ArraySlice: rename_func("SLICE"),
326        exp.AtTimeZone: rename_func("AT_TIMEZONE"),
327        exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression),
328        exp.BitwiseLeftShift: rename_func("BITWISE_LEFT_SHIFT"),
329        exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this),
330        exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression),
331        exp.BitwiseRightShift: rename_func("BITWISE_RIGHT_SHIFT"),
332        exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression),
333        exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]),
334        exp.CurrentTime: lambda *_: "CURRENT_TIME",
335        exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
336        exp.CurrentUser: lambda *_: "CURRENT_USER",
337        exp.DateAdd: _date_delta_sql("DATE_ADD"),
338        exp.DateDiff: _date_diff_sql,
339        exp.DatetimeDiff: _date_diff_sql,
340        exp.TimestampDiff: _date_diff_sql,
341        exp.DateStrToDate: datestrtodate_sql,
342        exp.DateToDi: lambda self, e: (
343            f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {type(self.dialect).DATEINT_FORMAT}) AS INT)"
344        ),
345        exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True),
346        exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)",
347        exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"),
348        exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"),
349        exp.DiToDate: lambda self, e: (
350            f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {type(self.dialect).DATEINT_FORMAT}) AS DATE)"
351        ),
352        exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"),
353        exp.FileFormatProperty: lambda self, e: f"format={self.sql(exp.Literal.string(e.name))}",
354        exp.First: _first_last_sql,
355        exp.FromISO8601Date: rename_func("FROM_ISO8601_DATE"),
356        exp.FromISO8601Timestamp: rename_func("FROM_ISO8601_TIMESTAMP"),
357        exp.FromTimeZone: lambda self, e: (
358            f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'"
359        ),
360        exp.GenerateSeries: sequence_sql,
361        exp.GenerateDateArray: sequence_sql,
362        exp.If: if_sql(),
363        exp.ILike: no_ilike_sql,
364        exp.Initcap: _initcap_sql,
365        exp.Last: _first_last_sql,
366        exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
367        exp.Lateral: _explode_to_unnest_sql,
368        exp.Left: left_to_substring_sql,
369        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
370            rename_func("LEVENSHTEIN_DISTANCE")
371        ),
372        exp.LogicalAnd: rename_func("BOOL_AND"),
373        exp.LogicalOr: rename_func("BOOL_OR"),
374        exp.Pivot: no_pivot_sql,
375        exp.Quantile: _quantile_sql,
376        exp.RegexpExtract: regexp_extract_sql,
377        exp.RegexpExtractAll: regexp_extract_sql,
378        exp.Right: right_to_substring_sql,
379        exp.Schema: _schema_sql,
380        exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
381        exp.Select: transforms.preprocess(
382            [
383                transforms.eliminate_window_clause,
384                transforms.eliminate_qualify,
385                transforms.eliminate_distinct_on,
386                transforms.explode_projection_to_unnest(1),
387                transforms.eliminate_semi_and_anti_joins,
388                amend_exploded_column_table,
389            ]
390        ),
391        exp.SortArray: _no_sort_array,
392        exp.SqlSecurityProperty: lambda self, e: f"SECURITY {self.sql(e.this)}",
393        exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True),
394        exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
395        exp.StrToMap: rename_func("SPLIT_TO_MAP"),
396        exp.StrToTime: _str_to_time_sql,
397        exp.StructExtract: struct_extract_sql,
398        exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
399        exp.Timestamp: no_timestamp_sql,
400        exp.TimestampAdd: _date_delta_sql("DATE_ADD"),
401        exp.TimestampTrunc: timestamptrunc_sql(),
402        exp.TimeStrToDate: timestrtotime_sql,
403        exp.TimeStrToTime: timestrtotime_sql,
404        exp.TimeStrToUnix: lambda self, e: self.func(
405            "TO_UNIXTIME", self.func("DATE_PARSE", e.this, type(self.dialect).TIME_FORMAT)
406        ),
407        exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
408        exp.TimeToUnix: rename_func("TO_UNIXTIME"),
409        exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
410        exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]),
411        exp.TsOrDiToDi: lambda self, e: (
412            f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)"
413        ),
414        exp.TsOrDsAdd: _ts_or_ds_add_sql,
415        exp.TsOrDsDiff: _ts_or_ds_diff_sql,
416        exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
417        exp.Unhex: rename_func("FROM_HEX"),
418        exp.UnixToStr: lambda self, e: (
419            f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})"
420        ),
421        exp.UnixToTime: _unix_to_time_sql,
422        exp.UnixToTimeStr: lambda self, e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
423        exp.VariancePop: rename_func("VAR_POP"),
424        exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]),
425        exp.WithinGroup: transforms.preprocess([transforms.remove_within_group_for_percentiles]),
426        # Note: Presto's TRUNCATE always returns DOUBLE, even with decimals=0, whereas
427        # most dialects return INT (SQLite also returns REAL, see sqlite.py). This creates
428        # a bidirectional transpilation gap: Presto→Other may change float division to int
429        # division, and vice versa. Modeling precisely would require exp.FloatTrunc or
430        # similar, deemed overengineering for this subtle semantic difference.
431        exp.Trunc: rename_func("TRUNCATE"),
432        exp.Xor: bool_xor_sql,
433        exp.MD5Digest: rename_func("MD5"),
434        exp.SHA: rename_func("SHA1"),
435        exp.SHA1Digest: rename_func("SHA1"),
436        exp.SHA2Digest: _sha2_digest_sql,
437        exp.Substring: rename_func("SUBSTR"),
438    }
439
440    RESERVED_KEYWORDS = {
441        "alter",
442        "and",
443        "as",
444        "between",
445        "by",
446        "case",
447        "cast",
448        "constraint",
449        "create",
450        "cross",
451        "current_time",
452        "current_timestamp",
453        "deallocate",
454        "delete",
455        "describe",
456        "distinct",
457        "drop",
458        "else",
459        "end",
460        "escape",
461        "except",
462        "execute",
463        "exists",
464        "extract",
465        "false",
466        "for",
467        "from",
468        "full",
469        "group",
470        "having",
471        "in",
472        "inner",
473        "insert",
474        "intersect",
475        "into",
476        "is",
477        "join",
478        "left",
479        "like",
480        "natural",
481        "not",
482        "null",
483        "on",
484        "or",
485        "order",
486        "outer",
487        "prepare",
488        "right",
489        "select",
490        "table",
491        "then",
492        "true",
493        "union",
494        "using",
495        "values",
496        "when",
497        "where",
498        "with",
499    }
500
501    def extract_sql(self, expression: exp.Extract) -> str:
502        date_part = expression.name
503
504        if not date_part.startswith("EPOCH"):
505            return super().extract_sql(expression)
506
507        if date_part == "EPOCH_MILLISECOND":
508            scale = 10**3
509        elif date_part == "EPOCH_MICROSECOND":
510            scale = 10**6
511        elif date_part == "EPOCH_NANOSECOND":
512            scale = 10**9
513        else:
514            scale = None
515
516        value = expression.expression
517
518        ts = exp.cast(value, to=exp.DType.TIMESTAMP.into_expr())
519        to_unix: exp.Expr = exp.TimeToUnix(this=ts)
520
521        if scale:
522            to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale))
523
524        return self.sql(to_unix)
525
526    def jsonformat_sql(self, expression: exp.JSONFormat) -> str:
527        this = expression.this
528        is_json = expression.args.get("is_json")
529
530        if this and not (is_json or this.type):
531            from sqlglot.optimizer.annotate_types import annotate_types
532
533            this = annotate_types(this, dialect=self.dialect)
534
535        if not (is_json or this.is_type(exp.DType.JSON)):
536            this.replace(exp.cast(this, exp.DType.JSON))
537
538        return self.function_fallback_sql(expression)
539
540    def md5_sql(self, expression: exp.MD5) -> str:
541        this = expression.this
542
543        if not this.type:
544            from sqlglot.optimizer.annotate_types import annotate_types
545
546            this = annotate_types(this, dialect=self.dialect)
547
548        if this.is_type(*exp.DataType.TEXT_TYPES):
549            this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
550
551        return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
552
553    def sha2_sql(self, expression: exp.SHA2) -> str:
554        length = expression.text("length") or "256"
555        if length not in ("256", "512"):
556            self.unsupported(f"SHA{length} is not supported in Presto")
557
558        this = expression.this
559
560        if this.is_type(*exp.DataType.TEXT_TYPES):
561            this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
562
563        return self.func("LOWER", self.func("TO_HEX", self.func(f"SHA{length}", self.sql(this))))
564
565    def strtounix_sql(self, expression: exp.StrToUnix) -> str:
566        # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
567        # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
568        # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback,
569        # which seems to be using the same time mapping as Hive, as per:
570        # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
571        this = expression.this
572        value_as_text = exp.cast(this, exp.DType.TEXT)
573        value_as_timestamp = exp.cast(this, exp.DType.TIMESTAMP) if this.is_string else this
574
575        parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression))
576
577        formatted_value = self.func("DATE_FORMAT", value_as_timestamp, self.format_time(expression))
578        parse_with_tz = self.func(
579            "PARSE_DATETIME",
580            formatted_value,
581            self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE),
582        )
583        coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz)
584        return self.func("TO_UNIXTIME", coalesced)
585
586    def bracket_sql(self, expression: exp.Bracket) -> str:
587        if expression.args.get("safe"):
588            return bracket_to_element_at_sql(self, expression)
589        return super().bracket_sql(expression)
590
591    def struct_sql(self, expression: exp.Struct) -> str:
592        if not expression.type:
593            from sqlglot.optimizer.annotate_types import annotate_types
594
595            annotate_types(expression, dialect=self.dialect)
596
597        values: list[str] = []
598        schema: list[str] = []
599        unknown_type = False
600
601        for e in expression.expressions:
602            if isinstance(e, exp.PropertyEQ):
603                if e.type and e.type.is_type(exp.DType.UNKNOWN):
604                    unknown_type = True
605                else:
606                    schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}")
607                values.append(self.sql(e, "expression"))
608            else:
609                values.append(self.sql(e))
610
611        size = len(expression.expressions)
612
613        if not size or len(schema) != size:
614            if unknown_type:
615                self.unsupported(
616                    "Cannot convert untyped key-value definitions (try annotate_types)."
617                )
618            return self.func("ROW", *values)
619        return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
620
621    def interval_sql(self, expression: exp.Interval) -> str:
622        if expression.this and expression.text("unit").upper().startswith("WEEK"):
623            return f"({expression.this.name} * INTERVAL '7' DAY)"
624        return super().interval_sql(expression)
625
626    def transaction_sql(self, expression: exp.Transaction) -> str:
627        modes = expression.args.get("modes")
628        modes = f" {', '.join(modes)}" if modes else ""
629        return f"START TRANSACTION{modes}"
630
631    def offset_limit_modifiers(
632        self, expression: exp.Expr, fetch: bool, limit: exp.Fetch | exp.Limit | None
633    ) -> list[str]:
634        return [
635            self.sql(expression, "offset"),
636            self.sql(limit),
637        ]
638
639    def create_sql(self, expression: exp.Create) -> str:
640        """
641        Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression),
642        so we need to remove them
643        """
644        kind = expression.args["kind"]
645        schema = expression.this
646        if kind == "VIEW" and schema.expressions:
647            expression.this.set("expressions", None)
648        return super().create_sql(expression)
649
650    def delete_sql(self, expression: exp.Delete) -> str:
651        """
652        Presto only supports DELETE FROM for a single table without an alias, so we need
653        to remove the unnecessary parts. If the original DELETE statement contains more
654        than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
655        """
656        tables = expression.args.get("tables") or [expression.this]
657        if len(tables) > 1:
658            return super().delete_sql(expression)
659
660        table = tables[0]
661        expression.set("this", table)
662        expression.set("tables", None)
663
664        if isinstance(table, exp.Table):
665            table_alias = table.args.get("alias")
666            if table_alias:
667                table_alias.pop()
668                expression = t.cast(exp.Delete, expression.transform(unqualify_columns))
669
670        return super().delete_sql(expression)
671
672    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
673        is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True)
674
675        # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks
676        # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino
677        if not expression.args.get("variant_extract") or is_json_extract:
678            return self.func(
679                "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
680            )
681
682        this = self.sql(expression, "this")
683
684        # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y
685        segments = []
686        for path_key in expression.expression.expressions[1:]:
687            if not isinstance(path_key, exp.JSONPathKey):
688                # Cannot transpile subscripts, wildcards etc to dot notation
689                self.unsupported(f"Cannot transpile JSONPath segment '{path_key}' to ROW access")
690                continue
691            key = path_key.this
692            if not exp.SAFE_IDENTIFIER_RE.match(key):
693                key = f'"{key}"'
694            segments.append(f".{key}")
695
696        expr = "".join(segments)
697
698        return f"{this}{expr}"
699
700    def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
701        return self.func(
702            "ARRAY_JOIN",
703            self.func("ARRAY_AGG", expression.this),
704            expression.args.get("separator"),
705        )