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    Dialect,
  8    NormalizationStrategy,
  9    binary_from_function,
 10    build_formatted_time,
 11    groupconcat_sql,
 12    rename_func,
 13    strposition_sql,
 14    timestrtotime_sql,
 15    timestamptrunc_sql,
 16    build_date_delta,
 17    no_last_day_sql,
 18    DATE_ADD_OR_SUB,
 19    build_timetostr_or_tochar,
 20)
 21from sqlglot.generator import unsupported_args
 22from sqlglot.helper import seq_get
 23from sqlglot.tokens import TokenType
 24from sqlglot.optimizer.scope import build_scope
 25
 26if t.TYPE_CHECKING:
 27    from sqlglot.dialects.dialect import DialectType
 28
 29
 30def _sha2_sql(self: Exasol.Generator, expression: exp.SHA2) -> str:
 31    length = expression.text("length")
 32    func_name = "HASH_SHA256" if length == "256" else "HASH_SHA512"
 33    return self.func(func_name, expression.this)
 34
 35
 36def _date_diff_sql(self: Exasol.Generator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
 37    unit = expression.text("unit").upper() or "DAY"
 38
 39    if unit not in DATE_UNITS:
 40        self.unsupported(f"'{unit}' is not supported in Exasol.")
 41        return self.function_fallback_sql(expression)
 42
 43    return self.func(f"{unit}S_BETWEEN", expression.this, expression.expression)
 44
 45
 46# https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/trunc%5Bate%5D%20(datetime).htm
 47# https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/trunc%5Bate%5D%20(number).htm
 48def _build_trunc(args: t.List[exp.Expression], dialect: DialectType) -> exp.Expression:
 49    first, second = seq_get(args, 0), seq_get(args, 1)
 50
 51    if not first or not second:
 52        return exp.Anonymous(this="TRUNC", expressions=args)
 53
 54    if not first.type:
 55        from sqlglot.optimizer.annotate_types import annotate_types
 56
 57        first = annotate_types(first, dialect=dialect)
 58
 59    if first.is_type(exp.DataType.Type.DATE, exp.DataType.Type.TIMESTAMP) and second.is_string:
 60        return exp.DateTrunc(this=first, unit=second)
 61
 62    return exp.Anonymous(this="TRUNC", expressions=args)
 63
 64
 65# https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/zeroifnull.htm
 66def _build_zeroifnull(args: t.List) -> exp.If:
 67    cond = exp.Is(this=seq_get(args, 0), expression=exp.Null())
 68    return exp.If(this=cond, true=exp.Literal.number(0), false=seq_get(args, 0))
 69
 70
 71# https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/nullifzero.htm
 72def _build_nullifzero(args: t.List) -> exp.If:
 73    cond = exp.EQ(this=seq_get(args, 0), expression=exp.Literal.number(0))
 74    return exp.If(this=cond, true=exp.Null(), false=seq_get(args, 0))
 75
 76
 77# https://docs.exasol.com/db/latest/sql/select.htm#:~:text=If%20you%20have,local.x%3E10
 78def _add_local_prefix_for_aliases(expression: exp.Expression) -> exp.Expression:
 79    if isinstance(expression, exp.Select):
 80        aliases: dict[str, bool] = {
 81            alias.name: bool(alias.args.get("quoted"))
 82            for sel in expression.selects
 83            if isinstance(sel, exp.Alias) and (alias := sel.args.get("alias"))
 84        }
 85
 86        table = expression.find(exp.Table)
 87        table_ident = table.this if table else None
 88
 89        if (
 90            table_ident
 91            and table_ident.name.upper() == "LOCAL"
 92            and not bool(table_ident.args.get("quoted"))
 93        ):
 94            table_ident.replace(exp.to_identifier(table_ident.name.upper(), quoted=True))
 95
 96        def prefix_local(node, visible_aliases: dict[str, bool]) -> exp.Expression:
 97            if isinstance(node, exp.Column) and not node.table:
 98                if node.name in visible_aliases:
 99                    return exp.Column(
100                        this=exp.to_identifier(node.name, quoted=visible_aliases[node.name]),
101                        table=exp.to_identifier("LOCAL", quoted=False),
102                    )
103            return node
104
105        for key in ("where", "group", "having"):
106            if arg := expression.args.get(key):
107                expression.set(key, arg.transform(lambda node: prefix_local(node, aliases)))
108
109        seen_aliases: dict[str, bool] = {}
110        new_selects: list[exp.Expression] = []
111        for sel in expression.selects:
112            if isinstance(sel, exp.Alias):
113                inner = sel.this.transform(lambda node: prefix_local(node, seen_aliases))
114                sel.set("this", inner)
115
116                alias_node = sel.args.get("alias")
117
118                seen_aliases[sel.alias] = bool(alias_node and getattr(alias_node, "quoted", False))
119                new_selects.append(sel)
120            else:
121                new_selects.append(sel.transform(lambda node: prefix_local(node, seen_aliases)))
122        expression.set("expressions", new_selects)
123
124    return expression
125
126
127def _trunc_sql(self: Exasol.Generator, kind: str, expression: exp.DateTrunc) -> str:
128    unit = expression.text("unit")
129    node = expression.this.this if isinstance(expression.this, exp.Cast) else expression.this
130    expr_sql = self.sql(node)
131    if isinstance(node, exp.Literal) and node.is_string:
132        expr_sql = (
133            f"{kind} '{node.this.replace('T', ' ')}'"
134            if kind == "TIMESTAMP"
135            else f"DATE '{node.this}'"
136        )
137    return f"DATE_TRUNC('{unit}', {expr_sql})"
138
139
140def _date_trunc_sql(self: Exasol.Generator, expression: exp.DateTrunc) -> str:
141    return _trunc_sql(self, "DATE", expression)
142
143
144def _timestamp_trunc_sql(self: Exasol.Generator, expression: exp.DateTrunc) -> str:
145    return _trunc_sql(self, "TIMESTAMP", expression)
146
147
148def is_case_insensitive(node: exp.Expression) -> bool:
149    return isinstance(node, exp.Collate) and node.text("expression").upper() == "UTF8_LCASE"
150
151
152def _substring_index_sql(self: Exasol.Generator, expression: exp.SubstringIndex) -> str:
153    this = expression.this
154    delimiter = expression.args["delimiter"]
155    count_node = expression.args["count"]
156    count_sql = self.sql(expression, "count")
157    num = count_node.to_py() if count_node.is_number else 0
158
159    haystack_sql = self.sql(this)
160    if num == 0:
161        return self.func("SUBSTR", haystack_sql, "1", "0")
162
163    from_right = num < 0
164    direction = "-1" if from_right else "1"
165    occur = self.func("ABS", count_sql) if from_right else count_sql
166
167    delimiter_sql = self.sql(delimiter)
168
169    position = self.func(
170        "INSTR",
171        self.func("LOWER", haystack_sql) if is_case_insensitive(this) else haystack_sql,
172        self.func("LOWER", delimiter_sql) if is_case_insensitive(delimiter) else delimiter_sql,
173        direction,
174        occur,
175    )
176    nullable_pos = self.func("NULLIF", position, "0")
177
178    if from_right:
179        start = self.func(
180            "NVL", f"{nullable_pos} + {self.func('LENGTH', delimiter_sql)}", direction
181        )
182        return self.func("SUBSTR", haystack_sql, start)
183
184    length = self.func("NVL", f"{nullable_pos} - 1", self.func("LENGTH", haystack_sql))
185    return self.func("SUBSTR", haystack_sql, direction, length)
186
187
188# 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.
189def _qualify_unscoped_star(expression: exp.Expression) -> exp.Expression:
190    """
191    Exasol doesn't support a bare * alongside other select items, so we rewrite it
192    Rewrite: SELECT *, <other> FROM <Table>
193    Into: SELECT T.*, <other> FROM <Table> AS T
194    """
195
196    if not isinstance(expression, exp.Select):
197        return expression
198
199    select_expressions = expression.expressions or []
200
201    def is_bare_star(expr: exp.Expression) -> bool:
202        return isinstance(expr, exp.Star) and expr.this is None
203
204    has_other_expression = False
205    bare_star_expr: exp.Expression | None = None
206    for expr in select_expressions:
207        has_bare_star = is_bare_star(expr)
208        if has_bare_star and bare_star_expr is None:
209            bare_star_expr = expr
210        elif not has_bare_star:
211            has_other_expression = True
212        if bare_star_expr and has_other_expression:
213            break
214
215    if not (bare_star_expr and has_other_expression):
216        return expression
217
218    scope = build_scope(expression)
219
220    if not scope or not scope.selected_sources:
221        return expression
222
223    table_identifiers: list[exp.Identifier] = []
224
225    for source_name, (source_expr, _) in scope.selected_sources.items():
226        ident = (
227            source_expr.this.copy()
228            if isinstance(source_expr, exp.Table) and isinstance(source_expr.this, exp.Identifier)
229            else exp.to_identifier(source_name)
230        )
231        table_identifiers.append(ident)
232
233    qualified_star_columns = [
234        exp.Column(this=bare_star_expr.copy(), table=ident) for ident in table_identifiers
235    ]
236
237    new_select_expressions: list[exp.Expression] = []
238
239    for select_expr in select_expressions:
240        new_select_expressions.extend(qualified_star_columns) if is_bare_star(
241            select_expr
242        ) else new_select_expressions.append(select_expr)
243
244    expression.set("expressions", new_select_expressions)
245    return expression
246
247
248def _add_date_sql(self: Exasol.Generator, expression: DATE_ADD_OR_SUB) -> str:
249    interval = expression.expression if isinstance(expression.expression, exp.Interval) else None
250
251    unit = (
252        (interval.text("unit") or "DAY").upper()
253        if interval is not None
254        else (expression.text("unit") or "DAY").upper()
255    )
256
257    if unit not in DATE_UNITS:
258        self.unsupported(f"'{unit}' is not supported in Exasol.")
259        return self.function_fallback_sql(expression)
260
261    offset_expr: exp.Expression = expression.expression
262    if interval is not None:
263        offset_expr = interval.this
264
265    if isinstance(expression, exp.DateSub):
266        offset_expr = exp.Neg(this=offset_expr)
267
268    return self.func(f"ADD_{unit}S", expression.this, offset_expr)
269
270
271DATE_UNITS = {"DAY", "WEEK", "MONTH", "YEAR", "HOUR", "MINUTE", "SECOND"}
272
273
274class Exasol(Dialect):
275    # https://docs.exasol.com/db/latest/sql_references/basiclanguageelements.htm#SQLidentifier
276    NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE
277    # https://docs.exasol.com/db/latest/sql_references/data_types/datatypesoverview.htm
278    SUPPORTS_USER_DEFINED_TYPES = False
279    # https://docs.exasol.com/db/latest/sql/select.htm
280    SUPPORTS_SEMI_ANTI_JOIN = False
281    SUPPORTS_COLUMN_JOIN_MARKS = True
282    NULL_ORDERING = "nulls_are_last"
283    # https://docs.exasol.com/db/latest/sql_references/literals.htm#StringLiterals
284    CONCAT_COALESCE = True
285
286    TIME_MAPPING = {
287        "yyyy": "%Y",
288        "YYYY": "%Y",
289        "yy": "%y",
290        "YY": "%y",
291        "mm": "%m",
292        "MM": "%m",
293        "MONTH": "%B",
294        "MON": "%b",
295        "dd": "%d",
296        "DD": "%d",
297        "DAY": "%A",
298        "DY": "%a",
299        "H12": "%I",
300        "H24": "%H",
301        "HH": "%H",
302        "ID": "%u",
303        "vW": "%V",
304        "IW": "%V",
305        "vYYY": "%G",
306        "IYYY": "%G",
307        "MI": "%M",
308        "SS": "%S",
309        "uW": "%W",
310        "UW": "%U",
311        "Z": "%z",
312    }
313
314    class Tokenizer(tokens.Tokenizer):
315        IDENTIFIERS = ['"', ("[", "]")]
316        KEYWORDS = {
317            **tokens.Tokenizer.KEYWORDS,
318            "USER": TokenType.CURRENT_USER,
319            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/if.htm
320            "ENDIF": TokenType.END,
321            "LONG VARCHAR": TokenType.TEXT,
322            "SEPARATOR": TokenType.SEPARATOR,
323            "SYSTIMESTAMP": TokenType.SYSTIMESTAMP,
324        }
325        KEYWORDS.pop("DIV")
326
327    class Parser(parser.Parser):
328        FUNCTIONS = {
329            **parser.Parser.FUNCTIONS,
330            **{
331                f"ADD_{unit}S": build_date_delta(exp.DateAdd, default_unit=unit)
332                for unit in DATE_UNITS
333            },
334            **{
335                f"{unit}S_BETWEEN": build_date_delta(exp.DateDiff, default_unit=unit)
336                for unit in DATE_UNITS
337            },
338            "BIT_AND": binary_from_function(exp.BitwiseAnd),
339            "BIT_OR": binary_from_function(exp.BitwiseOr),
340            "BIT_XOR": binary_from_function(exp.BitwiseXor),
341            "BIT_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)),
342            "BIT_LSHIFT": binary_from_function(exp.BitwiseLeftShift),
343            "BIT_RSHIFT": binary_from_function(exp.BitwiseRightShift),
344            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/date_trunc.htm#DATE_TRUNC
345            "DATE_TRUNC": lambda args: exp.TimestampTrunc(
346                this=seq_get(args, 1), unit=seq_get(args, 0)
347            ),
348            "DIV": binary_from_function(exp.IntDiv),
349            "EVERY": lambda args: exp.All(this=seq_get(args, 0)),
350            "EDIT_DISTANCE": exp.Levenshtein.from_arg_list,
351            "HASH_SHA": exp.SHA.from_arg_list,
352            "HASH_SHA1": exp.SHA.from_arg_list,
353            "HASH_MD5": exp.MD5.from_arg_list,
354            "HASHTYPE_MD5": exp.MD5Digest.from_arg_list,
355            "REGEXP_SUBSTR": exp.RegexpExtract.from_arg_list,
356            "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
357                this=seq_get(args, 0),
358                expression=seq_get(args, 1),
359                replacement=seq_get(args, 2),
360                position=seq_get(args, 3),
361                occurrence=seq_get(args, 4),
362            ),
363            "HASH_SHA256": lambda args: exp.SHA2(
364                this=seq_get(args, 0), length=exp.Literal.number(256)
365            ),
366            "HASH_SHA512": lambda args: exp.SHA2(
367                this=seq_get(args, 0), length=exp.Literal.number(512)
368            ),
369            "TRUNC": _build_trunc,
370            "TRUNCATE": _build_trunc,
371            "VAR_POP": exp.VariancePop.from_arg_list,
372            "APPROXIMATE_COUNT_DISTINCT": exp.ApproxDistinct.from_arg_list,
373            "TO_CHAR": build_timetostr_or_tochar,
374            "TO_DATE": build_formatted_time(exp.TsOrDsToDate, "exasol"),
375            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/convert_tz.htm
376            "CONVERT_TZ": lambda args: exp.ConvertTimezone(
377                source_tz=seq_get(args, 1),
378                target_tz=seq_get(args, 2),
379                timestamp=seq_get(args, 0),
380                options=seq_get(args, 3),
381            ),
382            "NULLIFZERO": _build_nullifzero,
383            "ZEROIFNULL": _build_zeroifnull,
384        }
385        CONSTRAINT_PARSERS = {
386            **parser.Parser.CONSTRAINT_PARSERS,
387            "COMMENT": lambda self: self.expression(
388                exp.CommentColumnConstraint,
389                this=self._match(TokenType.IS) and self._parse_string(),
390            ),
391        }
392
393        FUNC_TOKENS = {
394            *parser.Parser.FUNC_TOKENS,
395            TokenType.SYSTIMESTAMP,
396        }
397
398        NO_PAREN_FUNCTIONS = {
399            **parser.Parser.NO_PAREN_FUNCTIONS,
400            TokenType.SYSTIMESTAMP: exp.Systimestamp,
401        }
402
403        FUNCTION_PARSERS = {
404            **parser.Parser.FUNCTION_PARSERS,
405            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/listagg.htm
406            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/group_concat.htm
407            **dict.fromkeys(("GROUP_CONCAT", "LISTAGG"), lambda self: self._parse_group_concat()),
408        }
409
410        def _parse_column(self) -> t.Optional[exp.Expression]:
411            column = super()._parse_column()
412            if not isinstance(column, exp.Column):
413                return column
414            table_ident = column.args.get("table")
415            if (
416                isinstance(table_ident, exp.Identifier)
417                and table_ident.name.upper() == "LOCAL"
418                and not bool(table_ident.args.get("quoted"))
419            ):
420                column.set("table", None)
421            return column
422
423        ODBC_DATETIME_LITERALS = {
424            "d": exp.Date,
425            "ts": exp.Timestamp,
426        }
427
428    class Generator(generator.Generator):
429        # https://docs.exasol.com/db/latest/sql_references/data_types/datatypedetails.htm#StringDataType
430        STRING_TYPE_MAPPING = {
431            exp.DataType.Type.BLOB: "VARCHAR",
432            exp.DataType.Type.LONGBLOB: "VARCHAR",
433            exp.DataType.Type.LONGTEXT: "VARCHAR",
434            exp.DataType.Type.MEDIUMBLOB: "VARCHAR",
435            exp.DataType.Type.MEDIUMTEXT: "VARCHAR",
436            exp.DataType.Type.TINYBLOB: "VARCHAR",
437            exp.DataType.Type.TINYTEXT: "VARCHAR",
438            # https://docs.exasol.com/db/latest/sql_references/data_types/datatypealiases.htm
439            exp.DataType.Type.TEXT: "LONG VARCHAR",
440            exp.DataType.Type.VARBINARY: "VARCHAR",
441        }
442
443        # https://docs.exasol.com/db/latest/sql_references/data_types/datatypealiases.htm
444        TYPE_MAPPING = {
445            **generator.Generator.TYPE_MAPPING,
446            **STRING_TYPE_MAPPING,
447            exp.DataType.Type.TINYINT: "SMALLINT",
448            exp.DataType.Type.MEDIUMINT: "INT",
449            exp.DataType.Type.DECIMAL32: "DECIMAL",
450            exp.DataType.Type.DECIMAL64: "DECIMAL",
451            exp.DataType.Type.DECIMAL128: "DECIMAL",
452            exp.DataType.Type.DECIMAL256: "DECIMAL",
453            exp.DataType.Type.DATETIME: "TIMESTAMP",
454            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
455            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
456            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
457        }
458
459        def datatype_sql(self, expression: exp.DataType) -> str:
460            # Exasol supports a fixed default precision of 3 for TIMESTAMP WITH LOCAL TIME ZONE
461            # and does not allow specifying a different custom precision
462            if expression.is_type(exp.DataType.Type.TIMESTAMPLTZ):
463                return "TIMESTAMP WITH LOCAL TIME ZONE"
464
465            return super().datatype_sql(expression)
466
467        TRANSFORMS = {
468            **generator.Generator.TRANSFORMS,
469            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/every.htm
470            exp.All: rename_func("EVERY"),
471            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_and.htm
472            exp.BitwiseAnd: rename_func("BIT_AND"),
473            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_or.htm
474            exp.BitwiseOr: rename_func("BIT_OR"),
475            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_not.htm
476            exp.BitwiseNot: rename_func("BIT_NOT"),
477            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_lshift.htm
478            exp.BitwiseLeftShift: rename_func("BIT_LSHIFT"),
479            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_rshift.htm
480            exp.BitwiseRightShift: rename_func("BIT_RSHIFT"),
481            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/bit_xor.htm
482            exp.BitwiseXor: rename_func("BIT_XOR"),
483            exp.DateDiff: _date_diff_sql,
484            exp.DateAdd: _add_date_sql,
485            exp.TsOrDsAdd: _add_date_sql,
486            exp.DateSub: _add_date_sql,
487            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/div.htm#DIV
488            exp.IntDiv: rename_func("DIV"),
489            exp.TsOrDsDiff: _date_diff_sql,
490            exp.DateTrunc: _date_trunc_sql,
491            exp.DayOfWeek: lambda self, e: f"CAST(TO_CHAR({self.sql(e, 'this')}, 'D') AS INTEGER)",
492            exp.DatetimeTrunc: timestamptrunc_sql(),
493            exp.GroupConcat: lambda self, e: groupconcat_sql(
494                self, e, func_name="LISTAGG", within_group=True
495            ),
496            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/edit_distance.htm#EDIT_DISTANCE
497            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
498                rename_func("EDIT_DISTANCE")
499            ),
500            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/mod.htm
501            exp.Mod: rename_func("MOD"),
502            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_substr.htm
503            exp.RegexpExtract: unsupported_args("parameters", "group")(
504                rename_func("REGEXP_SUBSTR")
505            ),
506            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/regexp_replace.htm
507            exp.RegexpReplace: unsupported_args("modifiers")(rename_func("REGEXP_REPLACE")),
508            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/var_pop.htm
509            exp.VariancePop: rename_func("VAR_POP"),
510            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/approximate_count_distinct.htm
511            exp.ApproxDistinct: unsupported_args("accuracy")(
512                rename_func("APPROXIMATE_COUNT_DISTINCT")
513            ),
514            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_char%20(datetime).htm
515            exp.ToChar: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
516            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
517            exp.TsOrDsToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
518            exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
519            exp.TimeStrToTime: timestrtotime_sql,
520            exp.TimestampTrunc: _timestamp_trunc_sql,
521            exp.StrToTime: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
522            exp.CurrentUser: lambda *_: "CURRENT_USER",
523            exp.AtTimeZone: lambda self, e: self.func(
524                "CONVERT_TZ",
525                e.this,
526                "'UTC'",
527                e.args.get("zone"),
528            ),
529            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/instr.htm
530            exp.StrPosition: lambda self, e: (
531                strposition_sql(
532                    self, e, func_name="INSTR", supports_position=True, supports_occurrence=True
533                )
534            ),
535            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha%5B1%5D.htm#HASH_SHA%5B1%5D
536            exp.SHA: rename_func("HASH_SHA"),
537            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha256.htm
538            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hash_sha512.htm
539            exp.SHA2: _sha2_sql,
540            exp.MD5: rename_func("HASH_MD5"),
541            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/hashtype_md5.htm
542            exp.MD5Digest: rename_func("HASHTYPE_MD5"),
543            # https://docs.exasol.com/db/latest/sql/create_view.htm
544            exp.CommentColumnConstraint: lambda self, e: f"COMMENT IS {self.sql(e, 'this')}",
545            exp.Select: transforms.preprocess(
546                [
547                    _qualify_unscoped_star,
548                    _add_local_prefix_for_aliases,
549                ]
550            ),
551            exp.SubstringIndex: _substring_index_sql,
552            exp.WeekOfYear: rename_func("WEEK"),
553            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_date.htm
554            exp.Date: rename_func("TO_DATE"),
555            # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/to_timestamp.htm
556            exp.Timestamp: rename_func("TO_TIMESTAMP"),
557            exp.Quarter: lambda self, e: f"CEIL(MONTH(TO_DATE({self.sql(e, 'this')}))/3)",
558            exp.LastDay: no_last_day_sql,
559        }
560
561        def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
562            from_tz = expression.args.get("source_tz")
563            to_tz = expression.args.get("target_tz")
564            datetime = expression.args.get("timestamp")
565            options = expression.args.get("options")
566
567            return self.func("CONVERT_TZ", datetime, from_tz, to_tz, options)
568
569        def if_sql(self, expression: exp.If) -> str:
570            this = self.sql(expression, "this")
571            true = self.sql(expression, "true")
572            false = self.sql(expression, "false")
573            return f"IF {this} THEN {true} ELSE {false} ENDIF"
574
575        def collate_sql(self, expression: exp.Collate) -> str:
576            return self.sql(expression.this)
577
578        # https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/rank.htm
579        def rank_sql(self, expression: exp.Rank) -> str:
580            if expression.args.get("expressions"):
581                self.unsupported("Exasol does not support arguments in RANK")
582            return self.func("RANK")