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