sqlglot.dialects.exasol

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