sqlglot.dialects.presto

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens, transforms
  6from sqlglot.dialects.dialect import (
  7    Dialect,
  8    NormalizationStrategy,
  9    binary_from_function,
 10    bool_xor_sql,
 11    date_trunc_to_time,
 12    datestrtodate_sql,
 13    encode_decode_sql,
 14    build_formatted_time,
 15    if_sql,
 16    left_to_substring_sql,
 17    no_ilike_sql,
 18    no_pivot_sql,
 19    no_safe_divide_sql,
 20    no_timestamp_sql,
 21    regexp_extract_sql,
 22    rename_func,
 23    right_to_substring_sql,
 24    sha256_sql,
 25    struct_extract_sql,
 26    str_position_sql,
 27    timestamptrunc_sql,
 28    timestrtotime_sql,
 29    ts_or_ds_add_cast,
 30    unit_to_str,
 31    sequence_sql,
 32    build_regexp_extract,
 33    explode_to_unnest_sql,
 34)
 35from sqlglot.dialects.hive import Hive
 36from sqlglot.dialects.mysql import MySQL
 37from sqlglot.helper import apply_index_offset, seq_get
 38from sqlglot.tokens import TokenType
 39from sqlglot.transforms import unqualify_columns
 40
 41DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub]
 42
 43
 44def _initcap_sql(self: Presto.Generator, expression: exp.Initcap) -> str:
 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: Presto.Generator, 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: Presto.Generator, expression: exp.Schema) -> str:
 58    if isinstance(expression.parent, exp.Property):
 59        columns = ", ".join(f"'{c.name}'" for c in expression.expressions)
 60        return f"ARRAY[{columns}]"
 61
 62    if expression.parent:
 63        for schema in expression.parent.find_all(exp.Schema):
 64            column_defs = schema.find_all(exp.ColumnDef)
 65            if column_defs and isinstance(schema.parent, exp.Property):
 66                expression.expressions.extend(column_defs)
 67
 68    return self.schema_sql(expression)
 69
 70
 71def _quantile_sql(self: Presto.Generator, expression: exp.Quantile) -> str:
 72    self.unsupported("Presto does not support exact quantiles")
 73    return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile"))
 74
 75
 76def _str_to_time_sql(
 77    self: Presto.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 78) -> str:
 79    return self.func("DATE_PARSE", expression.this, self.format_time(expression))
 80
 81
 82def _ts_or_ds_to_date_sql(self: Presto.Generator, expression: exp.TsOrDsToDate) -> str:
 83    time_format = self.format_time(expression)
 84    if time_format and time_format not in (Presto.TIME_FORMAT, Presto.DATE_FORMAT):
 85        return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DataType.Type.DATE))
 86    return self.sql(
 87        exp.cast(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP), exp.DataType.Type.DATE)
 88    )
 89
 90
 91def _ts_or_ds_add_sql(self: Presto.Generator, expression: exp.TsOrDsAdd) -> str:
 92    expression = ts_or_ds_add_cast(expression)
 93    unit = unit_to_str(expression)
 94    return self.func("DATE_ADD", unit, expression.expression, expression.this)
 95
 96
 97def _ts_or_ds_diff_sql(self: Presto.Generator, expression: exp.TsOrDsDiff) -> str:
 98    this = exp.cast(expression.this, exp.DataType.Type.TIMESTAMP)
 99    expr = exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP)
100    unit = unit_to_str(expression)
101    return self.func("DATE_DIFF", unit, expr, this)
102
103
104def _build_approx_percentile(args: t.List) -> exp.Expression:
105    if len(args) == 4:
106        return exp.ApproxQuantile(
107            this=seq_get(args, 0),
108            weight=seq_get(args, 1),
109            quantile=seq_get(args, 2),
110            accuracy=seq_get(args, 3),
111        )
112    if len(args) == 3:
113        return exp.ApproxQuantile(
114            this=seq_get(args, 0), quantile=seq_get(args, 1), accuracy=seq_get(args, 2)
115        )
116    return exp.ApproxQuantile.from_arg_list(args)
117
118
119def _build_from_unixtime(args: t.List) -> exp.Expression:
120    if len(args) == 3:
121        return exp.UnixToTime(
122            this=seq_get(args, 0),
123            hours=seq_get(args, 1),
124            minutes=seq_get(args, 2),
125        )
126    if len(args) == 2:
127        return exp.UnixToTime(this=seq_get(args, 0), zone=seq_get(args, 1))
128
129    return exp.UnixToTime.from_arg_list(args)
130
131
132def _first_last_sql(self: Presto.Generator, expression: exp.Func) -> str:
133    """
134    Trino doesn't support FIRST / LAST as functions, but they're valid in the context
135    of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases
136    they're converted into an ARBITRARY call.
137
138    Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions
139    """
140    if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize):
141        return self.function_fallback_sql(expression)
142
143    return rename_func("ARBITRARY")(self, expression)
144
145
146def _unix_to_time_sql(self: Presto.Generator, expression: exp.UnixToTime) -> str:
147    scale = expression.args.get("scale")
148    timestamp = self.sql(expression, "this")
149    if scale in (None, exp.UnixToTime.SECONDS):
150        return rename_func("FROM_UNIXTIME")(self, expression)
151
152    return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))"
153
154
155def _to_int(self: Presto.Generator, expression: exp.Expression) -> exp.Expression:
156    if not expression.type:
157        from sqlglot.optimizer.annotate_types import annotate_types
158
159        annotate_types(expression, dialect=self.dialect)
160    if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES:
161        return exp.cast(expression, to=exp.DataType.Type.BIGINT)
162    return expression
163
164
165def _build_to_char(args: t.List) -> exp.TimeToStr:
166    fmt = seq_get(args, 1)
167    if isinstance(fmt, exp.Literal):
168        # We uppercase this to match Teradata's format mapping keys
169        fmt.set("this", fmt.this.upper())
170
171    # We use "teradata" on purpose here, because the time formats are different in Presto.
172    # See https://prestodb.io/docs/current/functions/teradata.html?highlight=to_char#to_char
173    return build_formatted_time(exp.TimeToStr, "teradata")(args)
174
175
176def _date_delta_sql(
177    name: str, negate_interval: bool = False
178) -> t.Callable[[Presto.Generator, DATE_ADD_OR_SUB], str]:
179    def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str:
180        interval = _to_int(self, expression.expression)
181        return self.func(
182            name,
183            unit_to_str(expression),
184            interval * (-1) if negate_interval else interval,
185            expression.this,
186        )
187
188    return _delta_sql
189
190
191class Presto(Dialect):
192    INDEX_OFFSET = 1
193    NULL_ORDERING = "nulls_are_last"
194    TIME_FORMAT = MySQL.TIME_FORMAT
195    STRICT_STRING_CONCAT = True
196    SUPPORTS_SEMI_ANTI_JOIN = False
197    TYPED_DIVISION = True
198    TABLESAMPLE_SIZE_IS_PERCENT = True
199    LOG_BASE_FIRST: t.Optional[bool] = None
200
201    TIME_MAPPING = MySQL.TIME_MAPPING
202
203    # https://github.com/trinodb/trino/issues/17
204    # https://github.com/trinodb/trino/issues/12289
205    # https://github.com/prestodb/presto/issues/2863
206    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
207
208    # The result of certain math functions in Presto/Trino is of type
209    # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT
210    ANNOTATORS = {
211        **Dialect.ANNOTATORS,
212        exp.Floor: lambda self, e: self._annotate_by_args(e, "this"),
213        exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"),
214        exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"),
215        exp.Round: lambda self, e: self._annotate_by_args(e, "this"),
216        exp.Sign: lambda self, e: self._annotate_by_args(e, "this"),
217        exp.Abs: lambda self, e: self._annotate_by_args(e, "this"),
218        exp.Rand: lambda self, e: self._annotate_by_args(e, "this")
219        if e.this
220        else self._set_type(e, exp.DataType.Type.DOUBLE),
221    }
222
223    class Tokenizer(tokens.Tokenizer):
224        UNICODE_STRINGS = [
225            (prefix + q, q)
226            for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
227            for prefix in ("U&", "u&")
228        ]
229
230        KEYWORDS = {
231            **tokens.Tokenizer.KEYWORDS,
232            "START": TokenType.BEGIN,
233            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
234            "ROW": TokenType.STRUCT,
235            "IPADDRESS": TokenType.IPADDRESS,
236            "IPPREFIX": TokenType.IPPREFIX,
237            "TDIGEST": TokenType.TDIGEST,
238            "HYPERLOGLOG": TokenType.HLLSKETCH,
239        }
240        KEYWORDS.pop("/*+")
241        KEYWORDS.pop("QUALIFY")
242
243    class Parser(parser.Parser):
244        VALUES_FOLLOWED_BY_PAREN = False
245
246        FUNCTIONS = {
247            **parser.Parser.FUNCTIONS,
248            "ARBITRARY": exp.AnyValue.from_arg_list,
249            "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list,
250            "APPROX_PERCENTILE": _build_approx_percentile,
251            "BITWISE_AND": binary_from_function(exp.BitwiseAnd),
252            "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)),
253            "BITWISE_OR": binary_from_function(exp.BitwiseOr),
254            "BITWISE_XOR": binary_from_function(exp.BitwiseXor),
255            "CARDINALITY": exp.ArraySize.from_arg_list,
256            "CONTAINS": exp.ArrayContains.from_arg_list,
257            "DATE_ADD": lambda args: exp.DateAdd(
258                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
259            ),
260            "DATE_DIFF": lambda args: exp.DateDiff(
261                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
262            ),
263            "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"),
264            "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"),
265            "DATE_TRUNC": date_trunc_to_time,
266            "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list,
267            "ELEMENT_AT": lambda args: exp.Bracket(
268                this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True
269            ),
270            "FROM_HEX": exp.Unhex.from_arg_list,
271            "FROM_UNIXTIME": _build_from_unixtime,
272            "FROM_UTF8": lambda args: exp.Decode(
273                this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8")
274            ),
275            "NOW": exp.CurrentTimestamp.from_arg_list,
276            "REGEXP_EXTRACT": build_regexp_extract,
277            "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
278                this=seq_get(args, 0),
279                expression=seq_get(args, 1),
280                replacement=seq_get(args, 2) or exp.Literal.string(""),
281            ),
282            "ROW": exp.Struct.from_arg_list,
283            "SEQUENCE": exp.GenerateSeries.from_arg_list,
284            "SET_AGG": exp.ArrayUniqueAgg.from_arg_list,
285            "SPLIT_TO_MAP": exp.StrToMap.from_arg_list,
286            "STRPOS": lambda args: exp.StrPosition(
287                this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2)
288            ),
289            "TO_CHAR": _build_to_char,
290            "TO_UNIXTIME": exp.TimeToUnix.from_arg_list,
291            "TO_UTF8": lambda args: exp.Encode(
292                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
293            ),
294            "MD5": exp.MD5Digest.from_arg_list,
295            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
296            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
297        }
298
299        FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy()
300        FUNCTION_PARSERS.pop("TRIM")
301
302    class Generator(generator.Generator):
303        INTERVAL_ALLOWS_PLURAL_FORM = False
304        JOIN_HINTS = False
305        TABLE_HINTS = False
306        QUERY_HINTS = False
307        IS_BOOL_ALLOWED = False
308        TZ_TO_WITH_TIME_ZONE = True
309        NVL2_SUPPORTED = False
310        STRUCT_DELIMITER = ("(", ")")
311        LIMIT_ONLY_LITERALS = True
312        SUPPORTS_SINGLE_ARG_CONCAT = False
313        LIKE_PROPERTY_INSIDE_SCHEMA = True
314        MULTI_ARG_DISTINCT = False
315        SUPPORTS_TO_NUMBER = False
316        HEX_FUNC = "TO_HEX"
317        PARSE_JSON_NAME = "JSON_PARSE"
318        PAD_FILL_PATTERN_IS_REQUIRED = True
319        EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
320
321        PROPERTIES_LOCATION = {
322            **generator.Generator.PROPERTIES_LOCATION,
323            exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
324            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
325        }
326
327        TYPE_MAPPING = {
328            **generator.Generator.TYPE_MAPPING,
329            exp.DataType.Type.BINARY: "VARBINARY",
330            exp.DataType.Type.BIT: "BOOLEAN",
331            exp.DataType.Type.DATETIME: "TIMESTAMP",
332            exp.DataType.Type.DATETIME64: "TIMESTAMP",
333            exp.DataType.Type.FLOAT: "REAL",
334            exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG",
335            exp.DataType.Type.INT: "INTEGER",
336            exp.DataType.Type.STRUCT: "ROW",
337            exp.DataType.Type.TEXT: "VARCHAR",
338            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
339            exp.DataType.Type.TIMETZ: "TIME",
340        }
341
342        TRANSFORMS = {
343            **generator.Generator.TRANSFORMS,
344            exp.AnyValue: rename_func("ARBITRARY"),
345            exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"),
346            exp.ArgMax: rename_func("MAX_BY"),
347            exp.ArgMin: rename_func("MIN_BY"),
348            exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
349            exp.ArrayAny: rename_func("ANY_MATCH"),
350            exp.ArrayConcat: rename_func("CONCAT"),
351            exp.ArrayContains: rename_func("CONTAINS"),
352            exp.ArraySize: rename_func("CARDINALITY"),
353            exp.ArrayToString: rename_func("ARRAY_JOIN"),
354            exp.ArrayUniqueAgg: rename_func("SET_AGG"),
355            exp.AtTimeZone: rename_func("AT_TIMEZONE"),
356            exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression),
357            exp.BitwiseLeftShift: lambda self, e: self.func(
358                "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression
359            ),
360            exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this),
361            exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression),
362            exp.BitwiseRightShift: lambda self, e: self.func(
363                "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression
364            ),
365            exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression),
366            exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]),
367            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
368            exp.DateAdd: _date_delta_sql("DATE_ADD"),
369            exp.DateDiff: lambda self, e: self.func(
370                "DATE_DIFF", unit_to_str(e), e.expression, e.this
371            ),
372            exp.DateStrToDate: datestrtodate_sql,
373            exp.DateToDi: lambda self,
374            e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)",
375            exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True),
376            exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)",
377            exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"),
378            exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"),
379            exp.DiToDate: lambda self,
380            e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)",
381            exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"),
382            exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'",
383            exp.First: _first_last_sql,
384            exp.FirstValue: _first_last_sql,
385            exp.FromTimeZone: lambda self,
386            e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'",
387            exp.GenerateSeries: sequence_sql,
388            exp.GenerateDateArray: sequence_sql,
389            exp.Group: transforms.preprocess([transforms.unalias_group]),
390            exp.If: if_sql(),
391            exp.ILike: no_ilike_sql,
392            exp.Initcap: _initcap_sql,
393            exp.JSONExtract: lambda self, e: self.jsonextract_sql(e),
394            exp.Last: _first_last_sql,
395            exp.LastValue: _first_last_sql,
396            exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
397            exp.Lateral: explode_to_unnest_sql,
398            exp.Left: left_to_substring_sql,
399            exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"),
400            exp.LogicalAnd: rename_func("BOOL_AND"),
401            exp.LogicalOr: rename_func("BOOL_OR"),
402            exp.Pivot: no_pivot_sql,
403            exp.Quantile: _quantile_sql,
404            exp.RegexpExtract: regexp_extract_sql,
405            exp.Right: right_to_substring_sql,
406            exp.SafeDivide: no_safe_divide_sql,
407            exp.Schema: _schema_sql,
408            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
409            exp.Select: transforms.preprocess(
410                [
411                    transforms.eliminate_qualify,
412                    transforms.eliminate_distinct_on,
413                    transforms.explode_to_unnest(1),
414                    transforms.eliminate_semi_and_anti_joins,
415                ]
416            ),
417            exp.SortArray: _no_sort_array,
418            exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True),
419            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
420            exp.StrToMap: rename_func("SPLIT_TO_MAP"),
421            exp.StrToTime: _str_to_time_sql,
422            exp.StructExtract: struct_extract_sql,
423            exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
424            exp.Timestamp: no_timestamp_sql,
425            exp.TimestampAdd: _date_delta_sql("DATE_ADD"),
426            exp.TimestampTrunc: timestamptrunc_sql(),
427            exp.TimeStrToDate: timestrtotime_sql,
428            exp.TimeStrToTime: timestrtotime_sql,
429            exp.TimeStrToUnix: lambda self, e: self.func(
430                "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT)
431            ),
432            exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
433            exp.TimeToUnix: rename_func("TO_UNIXTIME"),
434            exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
435            exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]),
436            exp.TsOrDiToDi: lambda self,
437            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
438            exp.TsOrDsAdd: _ts_or_ds_add_sql,
439            exp.TsOrDsDiff: _ts_or_ds_diff_sql,
440            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
441            exp.Unhex: rename_func("FROM_HEX"),
442            exp.UnixToStr: lambda self,
443            e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
444            exp.UnixToTime: _unix_to_time_sql,
445            exp.UnixToTimeStr: lambda self,
446            e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
447            exp.VariancePop: rename_func("VAR_POP"),
448            exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]),
449            exp.WithinGroup: transforms.preprocess(
450                [transforms.remove_within_group_for_percentiles]
451            ),
452            exp.Xor: bool_xor_sql,
453            exp.MD5Digest: rename_func("MD5"),
454            exp.SHA: rename_func("SHA1"),
455            exp.SHA2: sha256_sql,
456        }
457
458        RESERVED_KEYWORDS = {
459            "alter",
460            "and",
461            "as",
462            "between",
463            "by",
464            "case",
465            "cast",
466            "constraint",
467            "create",
468            "cross",
469            "current_time",
470            "current_timestamp",
471            "deallocate",
472            "delete",
473            "describe",
474            "distinct",
475            "drop",
476            "else",
477            "end",
478            "escape",
479            "except",
480            "execute",
481            "exists",
482            "extract",
483            "false",
484            "for",
485            "from",
486            "full",
487            "group",
488            "having",
489            "in",
490            "inner",
491            "insert",
492            "intersect",
493            "into",
494            "is",
495            "join",
496            "left",
497            "like",
498            "natural",
499            "not",
500            "null",
501            "on",
502            "or",
503            "order",
504            "outer",
505            "prepare",
506            "right",
507            "select",
508            "table",
509            "then",
510            "true",
511            "union",
512            "using",
513            "values",
514            "when",
515            "where",
516            "with",
517        }
518
519        def md5_sql(self, expression: exp.MD5) -> str:
520            this = expression.this
521
522            if not this.type:
523                from sqlglot.optimizer.annotate_types import annotate_types
524
525                this = annotate_types(this)
526
527            if this.is_type(*exp.DataType.TEXT_TYPES):
528                this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
529
530            return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
531
532        def strtounix_sql(self, expression: exp.StrToUnix) -> str:
533            # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
534            # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
535            # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback,
536            # which seems to be using the same time mapping as Hive, as per:
537            # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
538            this = expression.this
539            value_as_text = exp.cast(this, exp.DataType.Type.TEXT)
540            value_as_timestamp = (
541                exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this
542            )
543
544            parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression))
545
546            formatted_value = self.func(
547                "DATE_FORMAT", value_as_timestamp, self.format_time(expression)
548            )
549            parse_with_tz = self.func(
550                "PARSE_DATETIME",
551                formatted_value,
552                self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE),
553            )
554            coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz)
555            return self.func("TO_UNIXTIME", coalesced)
556
557        def bracket_sql(self, expression: exp.Bracket) -> str:
558            if expression.args.get("safe"):
559                return self.func(
560                    "ELEMENT_AT",
561                    expression.this,
562                    seq_get(
563                        apply_index_offset(
564                            expression.this,
565                            expression.expressions,
566                            1 - expression.args.get("offset", 0),
567                        ),
568                        0,
569                    ),
570                )
571            return super().bracket_sql(expression)
572
573        def struct_sql(self, expression: exp.Struct) -> str:
574            from sqlglot.optimizer.annotate_types import annotate_types
575
576            expression = annotate_types(expression)
577            values: t.List[str] = []
578            schema: t.List[str] = []
579            unknown_type = False
580
581            for e in expression.expressions:
582                if isinstance(e, exp.PropertyEQ):
583                    if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN):
584                        unknown_type = True
585                    else:
586                        schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}")
587                    values.append(self.sql(e, "expression"))
588                else:
589                    values.append(self.sql(e))
590
591            size = len(expression.expressions)
592
593            if not size or len(schema) != size:
594                if unknown_type:
595                    self.unsupported(
596                        "Cannot convert untyped key-value definitions (try annotate_types)."
597                    )
598                return self.func("ROW", *values)
599            return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
600
601        def interval_sql(self, expression: exp.Interval) -> str:
602            if expression.this and expression.text("unit").upper().startswith("WEEK"):
603                return f"({expression.this.name} * INTERVAL '7' DAY)"
604            return super().interval_sql(expression)
605
606        def transaction_sql(self, expression: exp.Transaction) -> str:
607            modes = expression.args.get("modes")
608            modes = f" {', '.join(modes)}" if modes else ""
609            return f"START TRANSACTION{modes}"
610
611        def offset_limit_modifiers(
612            self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
613        ) -> t.List[str]:
614            return [
615                self.sql(expression, "offset"),
616                self.sql(limit),
617            ]
618
619        def create_sql(self, expression: exp.Create) -> str:
620            """
621            Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression),
622            so we need to remove them
623            """
624            kind = expression.args["kind"]
625            schema = expression.this
626            if kind == "VIEW" and schema.expressions:
627                expression.this.set("expressions", None)
628            return super().create_sql(expression)
629
630        def delete_sql(self, expression: exp.Delete) -> str:
631            """
632            Presto only supports DELETE FROM for a single table without an alias, so we need
633            to remove the unnecessary parts. If the original DELETE statement contains more
634            than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
635            """
636            tables = expression.args.get("tables") or [expression.this]
637            if len(tables) > 1:
638                return super().delete_sql(expression)
639
640            table = tables[0]
641            expression.set("this", table)
642            expression.set("tables", None)
643
644            if isinstance(table, exp.Table):
645                table_alias = table.args.get("alias")
646                if table_alias:
647                    table_alias.pop()
648                    expression = t.cast(exp.Delete, expression.transform(unqualify_columns))
649
650            return super().delete_sql(expression)
651
652        def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
653            is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True)
654
655            # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks
656            # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino
657            if not expression.args.get("variant_extract") or is_json_extract:
658                return self.func(
659                    "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
660                )
661
662            this = self.sql(expression, "this")
663
664            # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y
665            segments = []
666            for path_key in expression.expression.expressions[1:]:
667                if not isinstance(path_key, exp.JSONPathKey):
668                    # Cannot transpile subscripts, wildcards etc to dot notation
669                    self.unsupported(
670                        f"Cannot transpile JSONPath segment '{path_key}' to ROW access"
671                    )
672                    continue
673                key = path_key.this
674                if not exp.SAFE_IDENTIFIER_RE.match(key):
675                    key = f'"{key}"'
676                segments.append(f".{key}")
677
678            expr = "".join(segments)
679
680            return f"{this}{expr}"
681
682        def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
683            return self.func(
684                "ARRAY_JOIN",
685                self.func("ARRAY_AGG", expression.this),
686                expression.args.get("separator"),
687            )