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