sqlglot.generators.exasol

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, transforms
  6from sqlglot.dialects.dialect import (
  7    DATE_ADD_OR_SUB,
  8    groupconcat_sql,
  9    no_last_day_sql,
 10    rename_func,
 11    strposition_sql,
 12    timestrtotime_sql,
 13    timestamptrunc_sql,
 14)
 15from sqlglot.errors import UnsupportedError
 16from sqlglot.generator import unsupported_args
 17from sqlglot.optimizer.scope import build_scope
 18from sqlglot.parsers.exasol import DATE_UNITS
 19
 20
 21def _sha2_sql(self: ExasolGenerator, expression: exp.SHA2) -> str:
 22    length = expression.text("length")
 23    func_name = "HASH_SHA256" if length == "256" else "HASH_SHA512"
 24    return self.func(func_name, expression.this)
 25
 26
 27def _date_diff_sql(self: ExasolGenerator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
 28    unit = expression.text("unit").upper() or "DAY"
 29
 30    if unit not in DATE_UNITS:
 31        self.unsupported(f"'{unit}' is not supported in Exasol.")
 32        return self.function_fallback_sql(expression)
 33
 34    return self.func(f"{unit}S_BETWEEN", expression.this, expression.expression)
 35
 36
 37# https://docs.exasol.com/db/latest/sql/select.htm#:~:text=If%20you%20have,local.x%3E10
 38def _add_local_prefix_for_aliases(expression: exp.Expr) -> exp.Expr:
 39    if isinstance(expression, exp.Select):
 40        aliases: dict[str, bool] = {
 41            alias.name: bool(alias.args.get("quoted"))
 42            for sel in expression.selects
 43            if isinstance(sel, exp.Alias) and (alias := sel.args.get("alias"))
 44        }
 45
 46        table = expression.find(exp.Table)
 47        table_ident = table.this if table else None
 48
 49        if (
 50            table_ident
 51            and table_ident.name.upper() == "LOCAL"
 52            and not bool(table_ident.args.get("quoted"))
 53        ):
 54            table_ident.replace(exp.to_identifier(table_ident.name.upper(), quoted=True))
 55
 56        def prefix_local(node, visible_aliases: dict[str, bool]) -> exp.Expr:
 57            if isinstance(node, exp.Column) and not node.table:
 58                if node.name in visible_aliases:
 59                    return exp.Column(
 60                        this=exp.to_identifier(node.name, quoted=visible_aliases[node.name]),
 61                        table=exp.to_identifier("LOCAL", quoted=False),
 62                    )
 63            return node
 64
 65        for key in ("where", "group", "having"):
 66            if arg := expression.args.get(key):
 67                expression.set(key, arg.transform(lambda node: prefix_local(node, aliases)))
 68
 69        seen_aliases: dict[str, bool] = {}
 70        new_selects: list[exp.Expr] = []
 71        for sel in expression.selects:
 72            if isinstance(sel, exp.Alias):
 73                inner = sel.this.transform(lambda node: prefix_local(node, seen_aliases))
 74                sel.set("this", inner)
 75
 76                alias_node = sel.args.get("alias")
 77
 78                seen_aliases[sel.alias] = bool(alias_node and getattr(alias_node, "quoted", False))
 79                new_selects.append(sel)
 80            else:
 81                new_selects.append(sel.transform(lambda node: prefix_local(node, seen_aliases)))
 82        expression.set("expressions", new_selects)
 83
 84    return expression
 85
 86
 87def _trunc_sql(
 88    self: ExasolGenerator, kind: str, expression: exp.DateTrunc | exp.TimestampTrunc
 89) -> str:
 90    unit = expression.text("unit")
 91    node = expression.this.this if isinstance(expression.this, exp.Cast) else expression.this
 92    expr_sql = self.sql(node)
 93    if isinstance(node, exp.Literal) and node.is_string:
 94        expr_sql = (
 95            f"{kind} '{node.this.replace('T', ' ')}'"
 96            if kind == "TIMESTAMP"
 97            else f"DATE '{node.this}'"
 98        )
 99    return f"DATE_TRUNC('{unit}', {expr_sql})"
100
101
102def _date_trunc_sql(self: ExasolGenerator, expression: exp.DateTrunc) -> str:
103    return _trunc_sql(self, "DATE", expression)
104
105
106def _timestamp_trunc_sql(
107    self: ExasolGenerator, expression: exp.DateTrunc | exp.TimestampTrunc
108) -> str:
109    return _trunc_sql(self, "TIMESTAMP", expression)
110
111
112def is_case_insensitive(node: exp.Expr) -> bool:
113    return isinstance(node, exp.Collate) and node.text("expression").upper() == "UTF8_LCASE"
114
115
116def _substring_index_sql(self: ExasolGenerator, expression: exp.SubstringIndex) -> str:
117    this = expression.this
118    delimiter = expression.args["delimiter"]
119    count_node = expression.args["count"]
120    count_sql = self.sql(expression, "count")
121    num = count_node.to_py() if count_node.is_number else 0
122
123    haystack_sql = self.sql(this)
124    if num == 0:
125        return self.func("SUBSTR", haystack_sql, "1", "0")
126
127    from_right = num < 0
128    direction = "-1" if from_right else "1"
129    occur = self.func("ABS", count_sql) if from_right else count_sql
130
131    delimiter_sql = self.sql(delimiter)
132
133    position = self.func(
134        "INSTR",
135        self.func("LOWER", haystack_sql) if is_case_insensitive(this) else haystack_sql,
136        self.func("LOWER", delimiter_sql) if is_case_insensitive(delimiter) else delimiter_sql,
137        direction,
138        occur,
139    )
140    nullable_pos = self.func("NULLIF", position, "0")
141
142    if from_right:
143        start = self.func(
144            "NVL", f"{nullable_pos} + {self.func('LENGTH', delimiter_sql)}", direction
145        )
146        return self.func("SUBSTR", haystack_sql, start)
147
148    length = self.func("NVL", f"{nullable_pos} - 1", self.func("LENGTH", haystack_sql))
149    return self.func("SUBSTR", haystack_sql, direction, length)
150
151
152# https://docs.exasol.com/db/latest/sql/select.htm#:~:text=The%20select_list%20defines%20the%20columns%20of%20the%20result%20table.%20If%20*%20is%20used%2C%20all%20columns%20are%20listed.%20You%20can%20use%20an%20expression%20like%20t.*%20to%20list%20all%20columns%20of%20the%20table%20t%2C%20the%20view%20t%2C%20or%20the%20object%20with%20the%20table%20alias%20t.
153def _qualify_unscoped_star(expression: exp.Expr) -> exp.Expr:
154    """
155    Exasol doesn't support a bare * alongside other select items, so we rewrite it
156    Rewrite: SELECT *, <other> FROM <Table>
157    Into: SELECT T.*, <other> FROM <Table> AS T
158    """
159
160    if not isinstance(expression, exp.Select):
161        return expression
162
163    select_expressions = expression.expressions or []
164
165    def is_bare_star(expr: exp.Expr) -> bool:
166        return isinstance(expr, exp.Star) and expr.this is None
167
168    has_other_expression = False
169    bare_star_expr: exp.Expr | None = None
170    for expr in select_expressions:
171        has_bare_star = is_bare_star(expr)
172        if has_bare_star and bare_star_expr is None:
173            bare_star_expr = expr
174        elif not has_bare_star:
175            has_other_expression = True
176        if bare_star_expr and has_other_expression:
177            break
178
179    if not (bare_star_expr and has_other_expression):
180        return expression
181
182    scope = build_scope(expression)
183
184    if not scope or not scope.selected_sources:
185        return expression
186
187    table_identifiers: list[exp.Identifier] = []
188
189    for source_name, (source_expr, _) in scope.selected_sources.items():
190        ident = (
191            source_expr.this.copy()
192            if isinstance(source_expr, exp.Table) and isinstance(source_expr.this, exp.Identifier)
193            else exp.to_identifier(source_name)
194        )
195        table_identifiers.append(ident)
196
197    qualified_star_columns = [
198        exp.Column(this=bare_star_expr.copy(), table=ident) for ident in table_identifiers
199    ]
200
201    new_select_expressions: list[exp.Expr] = []
202
203    for select_expr in select_expressions:
204        new_select_expressions.extend(qualified_star_columns) if is_bare_star(
205            select_expr
206        ) else new_select_expressions.append(select_expr)
207
208    expression.set("expressions", new_select_expressions)
209    return expression
210
211
212def _add_date_sql(self: ExasolGenerator, expression: DATE_ADD_OR_SUB) -> str:
213    interval = expression.expression if isinstance(expression.expression, exp.Interval) else None
214
215    unit = (
216        (interval.text("unit") or "DAY").upper()
217        if interval is not None
218        else (expression.text("unit") or "DAY").upper()
219    )
220
221    if unit not in DATE_UNITS:
222        self.unsupported(f"'{unit}' is not supported in Exasol.")
223        return self.function_fallback_sql(expression)
224
225    offset_expr: exp.Expr = expression.expression
226    if interval is not None:
227        offset_expr = interval.this
228
229    if isinstance(expression, exp.DateSub):
230        offset_expr = exp.Neg(this=offset_expr)
231
232    return self.func(f"ADD_{unit}S", expression.this, offset_expr)
233
234
235def _group_by_all(expression: exp.Expr) -> exp.Expr:
236    if not isinstance(expression, exp.Select):
237        return expression
238
239    group = expression.args.get("group")
240    if not group or not group.args.get("all"):
241        return expression
242
243    if expression.is_star:
244        if any(proj.find(exp.AggFunc) for proj in expression.expressions):
245            raise UnsupportedError(
246                "GROUP BY ALL with star projection and aggregates is not supported by Exasol"
247            )
248        expression.set("distinct", exp.Distinct())
249        expression.set("group", None)
250        return expression
251
252    group_positions = [
253        exp.Literal.number(i)
254        for i, proj in enumerate(expression.expressions, start=1)
255        if not proj.find(exp.AggFunc)
256    ]
257
258    if not group_positions:
259        expression.set("group", None)
260        return expression
261
262    group.set("expressions", group_positions)
263    group.set("all", None)
264
265    return expression
266
267
268class ExasolGenerator(generator.Generator):
269    SELECT_KINDS: tuple[str, ...] = ()
270    TRY_SUPPORTED = False
271    SUPPORTS_UESCAPE = False
272    SUPPORTS_DECODE_CASE = False
273
274    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
275
276    # https://docs.exasol.com/db/latest/sql_references/data_types/datatypedetails.htm#StringDataType
277    STRING_TYPE_MAPPING: t.ClassVar = {
278        exp.DType.BLOB: "VARCHAR",
279        exp.DType.LONGBLOB: "VARCHAR",
280        exp.DType.LONGTEXT: "VARCHAR",
281        exp.DType.MEDIUMBLOB: "VARCHAR",
282        exp.DType.MEDIUMTEXT: "VARCHAR",
283        exp.DType.TINYBLOB: "VARCHAR",
284        exp.DType.TINYTEXT: "VARCHAR",
285        # https://docs.exasol.com/db/latest/sql_references/data_types/datatypealiases.htm
286        exp.DType.TEXT: "LONG VARCHAR",
287        exp.DType.VARBINARY: "VARCHAR",
288    }
289
290    # https://docs.exasol.com/db/latest/sql_references/data_types/datatypealiases.htm
291    TYPE_MAPPING = {
292        **generator.Generator.TYPE_MAPPING,
293        **STRING_TYPE_MAPPING,
294        exp.DType.TINYINT: "SMALLINT",
295        exp.DType.MEDIUMINT: "INT",
296        exp.DType.DECIMAL32: "DECIMAL",
297        exp.DType.DECIMAL64: "DECIMAL",
298        exp.DType.DECIMAL128: "DECIMAL",
299        exp.DType.DECIMAL256: "DECIMAL",
300        exp.DType.DATETIME: "TIMESTAMP",
301        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
302        exp.DType.TIMESTAMPLTZ: "TIMESTAMP",
303        exp.DType.TIMESTAMPNTZ: "TIMESTAMP",
304    }
305
306    def datatype_sql(self, expression: exp.DataType) -> str:
307        # Exasol supports a fixed default precision of 3 for TIMESTAMP WITH LOCAL TIME ZONE
308        # and does not allow specifying a different custom precision
309        if expression.is_type(exp.DType.TIMESTAMPLTZ):
310            return "TIMESTAMP WITH LOCAL TIME ZONE"
311
312        return super().datatype_sql(expression)
313
314    TRANSFORMS = {
315        **generator.Generator.TRANSFORMS,
316        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/every.htm
317        exp.All: rename_func("EVERY"),
318        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_and.htm
319        exp.BitwiseAnd: rename_func("BIT_AND"),
320        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_or.htm
321        exp.BitwiseOr: rename_func("BIT_OR"),
322        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_not.htm
323        exp.BitwiseNot: rename_func("BIT_NOT"),
324        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_lshift.htm
325        exp.BitwiseLeftShift: rename_func("BIT_LSHIFT"),
326        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_rshift.htm
327        exp.BitwiseRightShift: rename_func("BIT_RSHIFT"),
328        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_xor.htm
329        exp.BitwiseXor: rename_func("BIT_XOR"),
330        exp.CurrentSchema: lambda *_: "CURRENT_SCHEMA",
331        exp.DateDiff: _date_diff_sql,
332        exp.DateAdd: _add_date_sql,
333        exp.TsOrDsAdd: _add_date_sql,
334        exp.DateSub: _add_date_sql,
335        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/div.htm#DIV
336        exp.IntDiv: rename_func("DIV"),
337        exp.TsOrDsDiff: _date_diff_sql,
338        exp.DateTrunc: _date_trunc_sql,
339        exp.DayOfWeek: lambda self, e: f"CAST(TO_CHAR({self.sql(e, 'this')}, 'D') AS INTEGER)",
340        exp.DatetimeTrunc: timestamptrunc_sql(),
341        exp.GroupConcat: lambda self, e: groupconcat_sql(
342            self, e, func_name="LISTAGG", within_group=True
343        ),
344        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/edit_distance.htm#EDIT_DISTANCE
345        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
346            rename_func("EDIT_DISTANCE")
347        ),
348        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/mod.htm
349        exp.Mod: rename_func("MOD"),
350        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/from_posix_time.htm
351        exp.UnixToTime: lambda self, e: self.func("FROM_POSIX_TIME", e.this),
352        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/rank.htm
353        exp.Rank: unsupported_args("expressions")(lambda *_: "RANK()"),
354        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/dense_rank.htm
355        exp.DenseRank: unsupported_args("expressions")(lambda *_: "DENSE_RANK()"),
356        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_substr.htm
357        exp.RegexpExtract: unsupported_args("parameters", "group")(rename_func("REGEXP_SUBSTR")),
358        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_replace.htm
359        exp.RegexpReplace: unsupported_args("modifiers")(rename_func("REGEXP_REPLACE")),
360        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/var_pop.htm
361        exp.VariancePop: rename_func("VAR_POP"),
362        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/approximate_count_distinct.htm
363        exp.ApproxDistinct: unsupported_args("accuracy")(rename_func("APPROXIMATE_COUNT_DISTINCT")),
364        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_char%20(datetime).htm
365        exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
366        exp.ToChar: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
367        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
368        exp.TsOrDsToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
369        exp.TimeStrToTime: timestrtotime_sql,
370        exp.TimestampTrunc: _timestamp_trunc_sql,
371        exp.StrToTime: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
372        exp.CurrentUser: lambda *_: "CURRENT_USER",
373        exp.AtTimeZone: lambda self, e: self.func(
374            "CONVERT_TZ",
375            e.this,
376            "'UTC'",
377            e.args.get("zone"),
378        ),
379        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/instr.htm
380        exp.StrPosition: lambda self, e: strposition_sql(
381            self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
382        ),
383        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha%5B1%5D.htm#HASH_SHA%5B1%5D
384        exp.SHA: rename_func("HASH_SHA"),
385        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha256.htm
386        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha512.htm
387        exp.SHA2: _sha2_sql,
388        exp.MD5: rename_func("HASH_MD5"),
389        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hashtype_md5.htm
390        exp.MD5Digest: rename_func("HASHTYPE_MD5"),
391        # https://docs.exasol.com/db/latest/sql/create_view.htm
392        exp.CommentColumnConstraint: lambda self, e: f"COMMENT IS {self.sql(e, 'this')}",
393        exp.Select: transforms.preprocess(
394            [
395                _qualify_unscoped_star,
396                _add_local_prefix_for_aliases,
397                _group_by_all,
398            ]
399        ),
400        exp.SubstringIndex: _substring_index_sql,
401        exp.WeekOfYear: rename_func("WEEK"),
402        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
403        exp.Date: rename_func("TO_DATE"),
404        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_timestamp.htm
405        exp.Timestamp: rename_func("TO_TIMESTAMP"),
406        exp.Quarter: lambda self, e: f"CEIL(MONTH(TO_DATE({self.sql(e, 'this')}))/3)",
407        exp.LastDay: no_last_day_sql,
408    }
409
410    # https://docs.exasol.com/db/7.1/sql_references/system_tables/metadata/exa_sql_keywords.htm
411    RESERVED_KEYWORDS = {
412        "absolute",
413        "action",
414        "add",
415        "after",
416        "all",
417        "allocate",
418        "alter",
419        "and",
420        "any",
421        "append",
422        "are",
423        "array",
424        "as",
425        "asc",
426        "asensitive",
427        "assertion",
428        "at",
429        "attribute",
430        "authid",
431        "authorization",
432        "before",
433        "begin",
434        "between",
435        "bigint",
436        "binary",
437        "bit",
438        "blob",
439        "blocked",
440        "bool",
441        "boolean",
442        "both",
443        "by",
444        "byte",
445        "call",
446        "called",
447        "cardinality",
448        "cascade",
449        "cascaded",
450        "case",
451        "casespecific",
452        "cast",
453        "catalog",
454        "chain",
455        "char",
456        "character",
457        "character_set_catalog",
458        "character_set_name",
459        "character_set_schema",
460        "characteristics",
461        "check",
462        "checked",
463        "clob",
464        "close",
465        "coalesce",
466        "collate",
467        "collation",
468        "collation_catalog",
469        "collation_name",
470        "collation_schema",
471        "column",
472        "commit",
473        "condition",
474        "connect_by_iscycle",
475        "connect_by_isleaf",
476        "connect_by_root",
477        "connection",
478        "constant",
479        "constraint",
480        "constraint_state_default",
481        "constraints",
482        "constructor",
483        "contains",
484        "continue",
485        "control",
486        "convert",
487        "corresponding",
488        "create",
489        "cs",
490        "csv",
491        "cube",
492        "current",
493        "current_cluster",
494        "current_cluster_uid",
495        "current_date",
496        "current_path",
497        "current_role",
498        "current_schema",
499        "current_session",
500        "current_statement",
501        "current_time",
502        "current_timestamp",
503        "current_user",
504        "cursor",
505        "cycle",
506        "data",
507        "datalink",
508        "datetime_interval_code",
509        "datetime_interval_precision",
510        "day",
511        "dbtimezone",
512        "deallocate",
513        "dec",
514        "decimal",
515        "declare",
516        "default",
517        "default_like_escape_character",
518        "deferrable",
519        "deferred",
520        "defined",
521        "definer",
522        "delete",
523        "deref",
524        "derived",
525        "desc",
526        "describe",
527        "descriptor",
528        "deterministic",
529        "disable",
530        "disabled",
531        "disconnect",
532        "dispatch",
533        "distinct",
534        "dlurlcomplete",
535        "dlurlpath",
536        "dlurlpathonly",
537        "dlurlscheme",
538        "dlurlserver",
539        "dlvalue",
540        "do",
541        "domain",
542        "double",
543        "drop",
544        "dynamic",
545        "dynamic_function",
546        "dynamic_function_code",
547        "each",
548        "else",
549        "elseif",
550        "elsif",
551        "emits",
552        "enable",
553        "enabled",
554        "end",
555        "end-exec",
556        "endif",
557        "enforce",
558        "equals",
559        "errors",
560        "escape",
561        "except",
562        "exception",
563        "exec",
564        "execute",
565        "exists",
566        "exit",
567        "export",
568        "external",
569        "extract",
570        "false",
571        "fbv",
572        "fetch",
573        "file",
574        "final",
575        "first",
576        "float",
577        "following",
578        "for",
579        "forall",
580        "force",
581        "format",
582        "found",
583        "free",
584        "from",
585        "fs",
586        "full",
587        "function",
588        "general",
589        "generated",
590        "geometry",
591        "get",
592        "global",
593        "go",
594        "goto",
595        "grant",
596        "granted",
597        "group",
598        "group_concat",
599        "grouping",
600        "groups",
601        "hashtype",
602        "hashtype_format",
603        "having",
604        "high",
605        "hold",
606        "hour",
607        "identity",
608        "if",
609        "ifnull",
610        "immediate",
611        "impersonate",
612        "implementation",
613        "import",
614        "in",
615        "index",
616        "indicator",
617        "inner",
618        "inout",
619        "input",
620        "insensitive",
621        "insert",
622        "instance",
623        "instantiable",
624        "int",
625        "integer",
626        "integrity",
627        "intersect",
628        "interval",
629        "into",
630        "inverse",
631        "invoker",
632        "is",
633        "iterate",
634        "join",
635        "key_member",
636        "key_type",
637        "large",
638        "last",
639        "lateral",
640        "ldap",
641        "leading",
642        "leave",
643        "left",
644        "level",
645        "like",
646        "limit",
647        "listagg",
648        "localtime",
649        "localtimestamp",
650        "locator",
651        "log",
652        "longvarchar",
653        "loop",
654        "low",
655        "map",
656        "match",
657        "matched",
658        "merge",
659        "method",
660        "minus",
661        "minute",
662        "mod",
663        "modifies",
664        "modify",
665        "module",
666        "month",
667        "names",
668        "national",
669        "natural",
670        "nchar",
671        "nclob",
672        "new",
673        "next",
674        "nls_date_format",
675        "nls_date_language",
676        "nls_first_day_of_week",
677        "nls_numeric_characters",
678        "nls_timestamp_format",
679        "no",
680        "nocycle",
681        "nologging",
682        "none",
683        "not",
684        "null",
685        "nullif",
686        "number",
687        "numeric",
688        "nvarchar",
689        "nvarchar2",
690        "object",
691        "of",
692        "off",
693        "old",
694        "on",
695        "only",
696        "open",
697        "option",
698        "options",
699        "or",
700        "order",
701        "ordering",
702        "ordinality",
703        "others",
704        "out",
705        "outer",
706        "output",
707        "over",
708        "overlaps",
709        "overlay",
710        "overriding",
711        "pad",
712        "parallel_enable",
713        "parameter",
714        "parameter_specific_catalog",
715        "parameter_specific_name",
716        "parameter_specific_schema",
717        "parquet",
718        "partial",
719        "path",
720        "permission",
721        "placing",
722        "plus",
723        "preceding",
724        "preferring",
725        "prepare",
726        "preserve",
727        "prior",
728        "privileges",
729        "procedure",
730        "profile",
731        "qualify",
732        "random",
733        "range",
734        "read",
735        "reads",
736        "real",
737        "recovery",
738        "recursive",
739        "ref",
740        "references",
741        "referencing",
742        "refresh",
743        "regexp_like",
744        "relative",
745        "release",
746        "rename",
747        "repeat",
748        "replace",
749        "restore",
750        "restrict",
751        "result",
752        "return",
753        "returned_length",
754        "returned_octet_length",
755        "returns",
756        "revoke",
757        "right",
758        "rollback",
759        "rollup",
760        "routine",
761        "row",
762        "rows",
763        "rowtype",
764        "savepoint",
765        "schema",
766        "scope",
767        "scope_user",
768        "script",
769        "scroll",
770        "search",
771        "second",
772        "section",
773        "security",
774        "select",
775        "selective",
776        "self",
777        "sensitive",
778        "separator",
779        "sequence",
780        "session",
781        "session_user",
782        "sessiontimezone",
783        "set",
784        "sets",
785        "shortint",
786        "similar",
787        "smallint",
788        "some",
789        "source",
790        "space",
791        "specific",
792        "specifictype",
793        "sql",
794        "sql_bigint",
795        "sql_bit",
796        "sql_char",
797        "sql_date",
798        "sql_decimal",
799        "sql_double",
800        "sql_float",
801        "sql_integer",
802        "sql_longvarchar",
803        "sql_numeric",
804        "sql_preprocessor_script",
805        "sql_real",
806        "sql_smallint",
807        "sql_timestamp",
808        "sql_tinyint",
809        "sql_type_date",
810        "sql_type_timestamp",
811        "sql_varchar",
812        "sqlexception",
813        "sqlstate",
814        "sqlwarning",
815        "start",
816        "state",
817        "statement",
818        "static",
819        "structure",
820        "style",
821        "substring",
822        "subtype",
823        "sysdate",
824        "system",
825        "system_user",
826        "systimestamp",
827        "table",
828        "temporary",
829        "text",
830        "then",
831        "time",
832        "timestamp",
833        "timezone_hour",
834        "timezone_minute",
835        "tinyint",
836        "to",
837        "trailing",
838        "transaction",
839        "transform",
840        "transforms",
841        "translation",
842        "treat",
843        "trigger",
844        "trim",
845        "true",
846        "truncate",
847        "under",
848        "union",
849        "unique",
850        "unknown",
851        "unlink",
852        "unnest",
853        "until",
854        "update",
855        "usage",
856        "user",
857        "using",
858        "value",
859        "values",
860        "varchar",
861        "varchar2",
862        "varray",
863        "verify",
864        "view",
865        "when",
866        "whenever",
867        "where",
868        "while",
869        "window",
870        "with",
871        "within",
872        "without",
873        "work",
874        "year",
875        "yes",
876        "zone",
877    }
878
879    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
880        from_tz = expression.args.get("source_tz")
881        to_tz = expression.args.get("target_tz")
882        datetime = expression.args.get("timestamp")
883        options = expression.args.get("options")
884
885        return self.func("CONVERT_TZ", datetime, from_tz, to_tz, options)
886
887    def if_sql(self, expression: exp.If) -> str:
888        this = self.sql(expression, "this")
889        true = self.sql(expression, "true")
890        false = self.sql(expression, "false")
891        return f"IF {this} THEN {true} ELSE {false} ENDIF"
892
893    def collate_sql(self, expression: exp.Collate) -> str:
894        return self.sql(expression.this)
895
896    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
897        sql = self.func(
898            "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
899        )
900
901        emits = self.sql(expression, "emits")
902        if emits:
903            sql = f"{sql} EMITS {emits}"
904
905        return sql
906
907    @unsupported_args("flag")
908    def regexplike_sql(self, expression: exp.RegexpLike) -> str:
909        if not expression.args.get("full_match"):
910            pattern = expression.expression
911            if pattern.is_string:
912                expression.set("expression", exp.Literal.string(f".*{pattern.name}.*"))
913            else:
914                expression.set(
915                    "expression",
916                    exp.Paren(
917                        this=exp.Concat(
918                            expressions=[
919                                exp.Literal.string(".*"),
920                                pattern,
921                                exp.Literal.string(".*"),
922                            ]
923                        )
924                    ),
925                )
926        return self.binary(expression, "REGEXP_LIKE")