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: lambda self, e: self.func(
283            "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression
284        ),
285        exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this),
286        exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression),
287        exp.BitwiseRightShift: lambda self, e: self.func(
288            "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression
289        ),
290        exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression),
291        exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]),
292        exp.CurrentTime: lambda *_: "CURRENT_TIME",
293        exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
294        exp.CurrentUser: lambda *_: "CURRENT_USER",
295        exp.DateAdd: _date_delta_sql("DATE_ADD"),
296        exp.DateDiff: lambda self, e: self.func("DATE_DIFF", unit_to_str(e), e.expression, e.this),
297        exp.DateStrToDate: datestrtodate_sql,
298        exp.DateToDi: lambda self, e: (
299            f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {type(self.dialect).DATEINT_FORMAT}) AS INT)"
300        ),
301        exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True),
302        exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)",
303        exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"),
304        exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"),
305        exp.DiToDate: lambda self, e: (
306            f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {type(self.dialect).DATEINT_FORMAT}) AS DATE)"
307        ),
308        exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"),
309        exp.FileFormatProperty: lambda self, e: f"format={self.sql(exp.Literal.string(e.name))}",
310        exp.First: _first_last_sql,
311        exp.FromTimeZone: lambda self, e: (
312            f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'"
313        ),
314        exp.GenerateSeries: sequence_sql,
315        exp.GenerateDateArray: sequence_sql,
316        exp.If: if_sql(),
317        exp.ILike: no_ilike_sql,
318        exp.Initcap: _initcap_sql,
319        exp.Last: _first_last_sql,
320        exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
321        exp.Lateral: _explode_to_unnest_sql,
322        exp.Left: left_to_substring_sql,
323        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
324            rename_func("LEVENSHTEIN_DISTANCE")
325        ),
326        exp.LogicalAnd: rename_func("BOOL_AND"),
327        exp.LogicalOr: rename_func("BOOL_OR"),
328        exp.Pivot: no_pivot_sql,
329        exp.Quantile: _quantile_sql,
330        exp.RegexpExtract: regexp_extract_sql,
331        exp.RegexpExtractAll: regexp_extract_sql,
332        exp.Right: right_to_substring_sql,
333        exp.Schema: _schema_sql,
334        exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
335        exp.Select: transforms.preprocess(
336            [
337                transforms.eliminate_window_clause,
338                transforms.eliminate_qualify,
339                transforms.eliminate_distinct_on,
340                transforms.explode_projection_to_unnest(1),
341                transforms.eliminate_semi_and_anti_joins,
342                amend_exploded_column_table,
343            ]
344        ),
345        exp.SortArray: _no_sort_array,
346        exp.SqlSecurityProperty: lambda self, e: f"SECURITY {self.sql(e.this)}",
347        exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True),
348        exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
349        exp.StrToMap: rename_func("SPLIT_TO_MAP"),
350        exp.StrToTime: _str_to_time_sql,
351        exp.StructExtract: struct_extract_sql,
352        exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
353        exp.Timestamp: no_timestamp_sql,
354        exp.TimestampAdd: _date_delta_sql("DATE_ADD"),
355        exp.TimestampTrunc: timestamptrunc_sql(),
356        exp.TimeStrToDate: timestrtotime_sql,
357        exp.TimeStrToTime: timestrtotime_sql,
358        exp.TimeStrToUnix: lambda self, e: self.func(
359            "TO_UNIXTIME", self.func("DATE_PARSE", e.this, type(self.dialect).TIME_FORMAT)
360        ),
361        exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
362        exp.TimeToUnix: rename_func("TO_UNIXTIME"),
363        exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
364        exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]),
365        exp.TsOrDiToDi: lambda self, e: (
366            f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)"
367        ),
368        exp.TsOrDsAdd: _ts_or_ds_add_sql,
369        exp.TsOrDsDiff: _ts_or_ds_diff_sql,
370        exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
371        exp.Unhex: rename_func("FROM_HEX"),
372        exp.UnixToStr: lambda self, e: (
373            f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})"
374        ),
375        exp.UnixToTime: _unix_to_time_sql,
376        exp.UnixToTimeStr: lambda self, e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
377        exp.VariancePop: rename_func("VAR_POP"),
378        exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]),
379        exp.WithinGroup: transforms.preprocess([transforms.remove_within_group_for_percentiles]),
380        # Note: Presto's TRUNCATE always returns DOUBLE, even with decimals=0, whereas
381        # most dialects return INT (SQLite also returns REAL, see sqlite.py). This creates
382        # a bidirectional transpilation gap: Presto→Other may change float division to int
383        # division, and vice versa. Modeling precisely would require exp.FloatTrunc or
384        # similar, deemed overengineering for this subtle semantic difference.
385        exp.Trunc: rename_func("TRUNCATE"),
386        exp.Xor: bool_xor_sql,
387        exp.MD5Digest: rename_func("MD5"),
388        exp.SHA: rename_func("SHA1"),
389        exp.SHA1Digest: rename_func("SHA1"),
390        exp.SHA2: sha256_sql,
391        exp.SHA2Digest: sha2_digest_sql,
392    }
393
394    RESERVED_KEYWORDS = {
395        "alter",
396        "and",
397        "as",
398        "between",
399        "by",
400        "case",
401        "cast",
402        "constraint",
403        "create",
404        "cross",
405        "current_time",
406        "current_timestamp",
407        "deallocate",
408        "delete",
409        "describe",
410        "distinct",
411        "drop",
412        "else",
413        "end",
414        "escape",
415        "except",
416        "execute",
417        "exists",
418        "extract",
419        "false",
420        "for",
421        "from",
422        "full",
423        "group",
424        "having",
425        "in",
426        "inner",
427        "insert",
428        "intersect",
429        "into",
430        "is",
431        "join",
432        "left",
433        "like",
434        "natural",
435        "not",
436        "null",
437        "on",
438        "or",
439        "order",
440        "outer",
441        "prepare",
442        "right",
443        "select",
444        "table",
445        "then",
446        "true",
447        "union",
448        "using",
449        "values",
450        "when",
451        "where",
452        "with",
453    }
454
455    def extract_sql(self, expression: exp.Extract) -> str:
456        date_part = expression.name
457
458        if not date_part.startswith("EPOCH"):
459            return super().extract_sql(expression)
460
461        if date_part == "EPOCH_MILLISECOND":
462            scale = 10**3
463        elif date_part == "EPOCH_MICROSECOND":
464            scale = 10**6
465        elif date_part == "EPOCH_NANOSECOND":
466            scale = 10**9
467        else:
468            scale = None
469
470        value = expression.expression
471
472        ts = exp.cast(value, to=exp.DType.TIMESTAMP.into_expr())
473        to_unix: exp.Expr = exp.TimeToUnix(this=ts)
474
475        if scale:
476            to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale))
477
478        return self.sql(to_unix)
479
480    def jsonformat_sql(self, expression: exp.JSONFormat) -> str:
481        this = expression.this
482        is_json = expression.args.get("is_json")
483
484        if this and not (is_json or this.type):
485            from sqlglot.optimizer.annotate_types import annotate_types
486
487            this = annotate_types(this, dialect=self.dialect)
488
489        if not (is_json or this.is_type(exp.DType.JSON)):
490            this.replace(exp.cast(this, exp.DType.JSON))
491
492        return self.function_fallback_sql(expression)
493
494    def md5_sql(self, expression: exp.MD5) -> str:
495        this = expression.this
496
497        if not this.type:
498            from sqlglot.optimizer.annotate_types import annotate_types
499
500            this = annotate_types(this, dialect=self.dialect)
501
502        if this.is_type(*exp.DataType.TEXT_TYPES):
503            this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
504
505        return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
506
507    def strtounix_sql(self, expression: exp.StrToUnix) -> str:
508        # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
509        # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
510        # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback,
511        # which seems to be using the same time mapping as Hive, as per:
512        # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
513        this = expression.this
514        value_as_text = exp.cast(this, exp.DType.TEXT)
515        value_as_timestamp = exp.cast(this, exp.DType.TIMESTAMP) if this.is_string else this
516
517        parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression))
518
519        formatted_value = self.func("DATE_FORMAT", value_as_timestamp, self.format_time(expression))
520        parse_with_tz = self.func(
521            "PARSE_DATETIME",
522            formatted_value,
523            self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE),
524        )
525        coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz)
526        return self.func("TO_UNIXTIME", coalesced)
527
528    def bracket_sql(self, expression: exp.Bracket) -> str:
529        if expression.args.get("safe"):
530            return bracket_to_element_at_sql(self, expression)
531        return super().bracket_sql(expression)
532
533    def struct_sql(self, expression: exp.Struct) -> str:
534        if not expression.type:
535            from sqlglot.optimizer.annotate_types import annotate_types
536
537            annotate_types(expression, dialect=self.dialect)
538
539        values: list[str] = []
540        schema: list[str] = []
541        unknown_type = False
542
543        for e in expression.expressions:
544            if isinstance(e, exp.PropertyEQ):
545                if e.type and e.type.is_type(exp.DType.UNKNOWN):
546                    unknown_type = True
547                else:
548                    schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}")
549                values.append(self.sql(e, "expression"))
550            else:
551                values.append(self.sql(e))
552
553        size = len(expression.expressions)
554
555        if not size or len(schema) != size:
556            if unknown_type:
557                self.unsupported(
558                    "Cannot convert untyped key-value definitions (try annotate_types)."
559                )
560            return self.func("ROW", *values)
561        return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
562
563    def interval_sql(self, expression: exp.Interval) -> str:
564        if expression.this and expression.text("unit").upper().startswith("WEEK"):
565            return f"({expression.this.name} * INTERVAL '7' DAY)"
566        return super().interval_sql(expression)
567
568    def transaction_sql(self, expression: exp.Transaction) -> str:
569        modes = expression.args.get("modes")
570        modes = f" {', '.join(modes)}" if modes else ""
571        return f"START TRANSACTION{modes}"
572
573    def offset_limit_modifiers(
574        self, expression: exp.Expr, fetch: bool, limit: exp.Fetch | exp.Limit | None
575    ) -> list[str]:
576        return [
577            self.sql(expression, "offset"),
578            self.sql(limit),
579        ]
580
581    def create_sql(self, expression: exp.Create) -> str:
582        """
583        Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression),
584        so we need to remove them
585        """
586        kind = expression.args["kind"]
587        schema = expression.this
588        if kind == "VIEW" and schema.expressions:
589            expression.this.set("expressions", None)
590        return super().create_sql(expression)
591
592    def delete_sql(self, expression: exp.Delete) -> str:
593        """
594        Presto only supports DELETE FROM for a single table without an alias, so we need
595        to remove the unnecessary parts. If the original DELETE statement contains more
596        than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
597        """
598        tables = expression.args.get("tables") or [expression.this]
599        if len(tables) > 1:
600            return super().delete_sql(expression)
601
602        table = tables[0]
603        expression.set("this", table)
604        expression.set("tables", None)
605
606        if isinstance(table, exp.Table):
607            table_alias = table.args.get("alias")
608            if table_alias:
609                table_alias.pop()
610                expression = t.cast(exp.Delete, expression.transform(unqualify_columns))
611
612        return super().delete_sql(expression)
613
614    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
615        is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True)
616
617        # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks
618        # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino
619        if not expression.args.get("variant_extract") or is_json_extract:
620            return self.func(
621                "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
622            )
623
624        this = self.sql(expression, "this")
625
626        # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y
627        segments = []
628        for path_key in expression.expression.expressions[1:]:
629            if not isinstance(path_key, exp.JSONPathKey):
630                # Cannot transpile subscripts, wildcards etc to dot notation
631                self.unsupported(f"Cannot transpile JSONPath segment '{path_key}' to ROW access")
632                continue
633            key = path_key.this
634            if not exp.SAFE_IDENTIFIER_RE.match(key):
635                key = f'"{key}"'
636            segments.append(f".{key}")
637
638        expr = "".join(segments)
639
640        return f"{this}{expr}"
641
642    def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
643        return self.func(
644            "ARRAY_JOIN",
645            self.func("ARRAY_AGG", expression.this),
646            expression.args.get("separator"),
647        )