sqlglot.dialects.duckdb

   1from __future__ import annotations
   2
   3import typing as t
   4
   5from sqlglot import exp, generator, parser, tokens, transforms
   6
   7from sqlglot.dialects.dialect import (
   8    Dialect,
   9    JSON_EXTRACT_TYPE,
  10    NormalizationStrategy,
  11    Version,
  12    approx_count_distinct_sql,
  13    arrow_json_extract_sql,
  14    binary_from_function,
  15    bool_xor_sql,
  16    build_default_decimal_type,
  17    count_if_to_sum,
  18    date_delta_to_binary_interval_op,
  19    date_trunc_to_time,
  20    datestrtodate_sql,
  21    no_datetime_sql,
  22    encode_decode_sql,
  23    build_formatted_time,
  24    inline_array_unless_query,
  25    no_comment_column_constraint_sql,
  26    no_time_sql,
  27    no_timestamp_sql,
  28    pivot_column_names,
  29    rename_func,
  30    remove_from_array_using_filter,
  31    strposition_sql,
  32    str_to_time_sql,
  33    timestamptrunc_sql,
  34    timestrtotime_sql,
  35    unit_to_str,
  36    sha256_sql,
  37    build_regexp_extract,
  38    explode_to_unnest_sql,
  39    no_make_interval_sql,
  40    groupconcat_sql,
  41)
  42from sqlglot.generator import unsupported_args
  43from sqlglot.helper import seq_get
  44from sqlglot.tokens import TokenType
  45from sqlglot.parser import binary_range_parser
  46
  47
  48# BigQuery -> DuckDB conversion for the DATE function
  49def _date_sql(self: DuckDB.Generator, expression: exp.Date) -> str:
  50    result = f"CAST({self.sql(expression, 'this')} AS DATE)"
  51    zone = self.sql(expression, "zone")
  52
  53    if zone:
  54        date_str = self.func("STRFTIME", result, "'%d/%m/%Y'")
  55        date_str = f"{date_str} || ' ' || {zone}"
  56
  57        # This will create a TIMESTAMP with time zone information
  58        result = self.func("STRPTIME", date_str, "'%d/%m/%Y %Z'")
  59
  60    return result
  61
  62
  63# BigQuery -> DuckDB conversion for the TIME_DIFF function
  64def _timediff_sql(self: DuckDB.Generator, expression: exp.TimeDiff) -> str:
  65    this = exp.cast(expression.this, exp.DataType.Type.TIME)
  66    expr = exp.cast(expression.expression, exp.DataType.Type.TIME)
  67
  68    # Although the 2 dialects share similar signatures, BQ seems to inverse
  69    # the sign of the result so the start/end time operands are flipped
  70    return self.func("DATE_DIFF", unit_to_str(expression), expr, this)
  71
  72
  73@unsupported_args(("expression", "DuckDB's ARRAY_SORT does not support a comparator."))
  74def _array_sort_sql(self: DuckDB.Generator, expression: exp.ArraySort) -> str:
  75    return self.func("ARRAY_SORT", expression.this)
  76
  77
  78def _sort_array_sql(self: DuckDB.Generator, expression: exp.SortArray) -> str:
  79    name = "ARRAY_REVERSE_SORT" if expression.args.get("asc") == exp.false() else "ARRAY_SORT"
  80    return self.func(name, expression.this)
  81
  82
  83def _build_sort_array_desc(args: t.List) -> exp.Expression:
  84    return exp.SortArray(this=seq_get(args, 0), asc=exp.false())
  85
  86
  87def _build_date_diff(args: t.List) -> exp.Expression:
  88    return exp.DateDiff(this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0))
  89
  90
  91def _build_generate_series(end_exclusive: bool = False) -> t.Callable[[t.List], exp.GenerateSeries]:
  92    def _builder(args: t.List) -> exp.GenerateSeries:
  93        # Check https://duckdb.org/docs/sql/functions/nested.html#range-functions
  94        if len(args) == 1:
  95            # DuckDB uses 0 as a default for the series' start when it's omitted
  96            args.insert(0, exp.Literal.number("0"))
  97
  98        gen_series = exp.GenerateSeries.from_arg_list(args)
  99        gen_series.set("is_end_exclusive", end_exclusive)
 100
 101        return gen_series
 102
 103    return _builder
 104
 105
 106def _build_make_timestamp(args: t.List) -> exp.Expression:
 107    if len(args) == 1:
 108        return exp.UnixToTime(this=seq_get(args, 0), scale=exp.UnixToTime.MICROS)
 109
 110    return exp.TimestampFromParts(
 111        year=seq_get(args, 0),
 112        month=seq_get(args, 1),
 113        day=seq_get(args, 2),
 114        hour=seq_get(args, 3),
 115        min=seq_get(args, 4),
 116        sec=seq_get(args, 5),
 117    )
 118
 119
 120def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[DuckDB.Parser], exp.Show]:
 121    def _parse(self: DuckDB.Parser) -> exp.Show:
 122        return self._parse_show_duckdb(*args, **kwargs)
 123
 124    return _parse
 125
 126
 127def _struct_sql(self: DuckDB.Generator, expression: exp.Struct) -> str:
 128    args: t.List[str] = []
 129
 130    # BigQuery allows inline construction such as "STRUCT<a STRING, b INTEGER>('str', 1)" which is
 131    # canonicalized to "ROW('str', 1) AS STRUCT(a TEXT, b INT)" in DuckDB
 132    # The transformation to ROW will take place if:
 133    #  1. The STRUCT itself does not have proper fields (key := value) as a "proper" STRUCT would
 134    #  2. A cast to STRUCT / ARRAY of STRUCTs is found
 135    ancestor_cast = expression.find_ancestor(exp.Cast)
 136    is_bq_inline_struct = (
 137        (expression.find(exp.PropertyEQ) is None)
 138        and ancestor_cast
 139        and any(
 140            casted_type.is_type(exp.DataType.Type.STRUCT)
 141            for casted_type in ancestor_cast.find_all(exp.DataType)
 142        )
 143    )
 144
 145    for i, expr in enumerate(expression.expressions):
 146        is_property_eq = isinstance(expr, exp.PropertyEQ)
 147        value = expr.expression if is_property_eq else expr
 148
 149        if is_bq_inline_struct:
 150            args.append(self.sql(value))
 151        else:
 152            if is_property_eq:
 153                if isinstance(expr.this, exp.Identifier):
 154                    key = self.sql(exp.Literal.string(expr.name))
 155                else:
 156                    key = self.sql(expr.this)
 157            else:
 158                key = self.sql(exp.Literal.string(f"_{i}"))
 159
 160            args.append(f"{key}: {self.sql(value)}")
 161
 162    csv_args = ", ".join(args)
 163
 164    return f"ROW({csv_args})" if is_bq_inline_struct else f"{{{csv_args}}}"
 165
 166
 167def _datatype_sql(self: DuckDB.Generator, expression: exp.DataType) -> str:
 168    if expression.is_type("array"):
 169        return f"{self.expressions(expression, flat=True)}[{self.expressions(expression, key='values', flat=True)}]"
 170
 171    # Modifiers are not supported for TIME, [TIME | TIMESTAMP] WITH TIME ZONE
 172    if expression.is_type(
 173        exp.DataType.Type.TIME, exp.DataType.Type.TIMETZ, exp.DataType.Type.TIMESTAMPTZ
 174    ):
 175        return expression.this.value
 176
 177    return self.datatype_sql(expression)
 178
 179
 180def _json_format_sql(self: DuckDB.Generator, expression: exp.JSONFormat) -> str:
 181    sql = self.func("TO_JSON", expression.this, expression.args.get("options"))
 182    return f"CAST({sql} AS TEXT)"
 183
 184
 185def _unix_to_time_sql(self: DuckDB.Generator, expression: exp.UnixToTime) -> str:
 186    scale = expression.args.get("scale")
 187    timestamp = expression.this
 188
 189    if scale in (None, exp.UnixToTime.SECONDS):
 190        return self.func("TO_TIMESTAMP", timestamp)
 191    if scale == exp.UnixToTime.MILLIS:
 192        return self.func("EPOCH_MS", timestamp)
 193    if scale == exp.UnixToTime.MICROS:
 194        return self.func("MAKE_TIMESTAMP", timestamp)
 195
 196    return self.func("TO_TIMESTAMP", exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)))
 197
 198
 199WRAPPED_JSON_EXTRACT_EXPRESSIONS = (exp.Binary, exp.Bracket, exp.In)
 200
 201
 202def _arrow_json_extract_sql(self: DuckDB.Generator, expression: JSON_EXTRACT_TYPE) -> str:
 203    arrow_sql = arrow_json_extract_sql(self, expression)
 204    if not expression.same_parent and isinstance(
 205        expression.parent, WRAPPED_JSON_EXTRACT_EXPRESSIONS
 206    ):
 207        arrow_sql = self.wrap(arrow_sql)
 208    return arrow_sql
 209
 210
 211def _implicit_datetime_cast(
 212    arg: t.Optional[exp.Expression], type: exp.DataType.Type = exp.DataType.Type.DATE
 213) -> t.Optional[exp.Expression]:
 214    return exp.cast(arg, type) if isinstance(arg, exp.Literal) else arg
 215
 216
 217def _date_diff_sql(self: DuckDB.Generator, expression: exp.DateDiff) -> str:
 218    this = _implicit_datetime_cast(expression.this)
 219    expr = _implicit_datetime_cast(expression.expression)
 220
 221    return self.func("DATE_DIFF", unit_to_str(expression), expr, this)
 222
 223
 224def _generate_datetime_array_sql(
 225    self: DuckDB.Generator, expression: t.Union[exp.GenerateDateArray, exp.GenerateTimestampArray]
 226) -> str:
 227    is_generate_date_array = isinstance(expression, exp.GenerateDateArray)
 228
 229    type = exp.DataType.Type.DATE if is_generate_date_array else exp.DataType.Type.TIMESTAMP
 230    start = _implicit_datetime_cast(expression.args.get("start"), type=type)
 231    end = _implicit_datetime_cast(expression.args.get("end"), type=type)
 232
 233    # BQ's GENERATE_DATE_ARRAY & GENERATE_TIMESTAMP_ARRAY are transformed to DuckDB'S GENERATE_SERIES
 234    gen_series: t.Union[exp.GenerateSeries, exp.Cast] = exp.GenerateSeries(
 235        start=start, end=end, step=expression.args.get("step")
 236    )
 237
 238    if is_generate_date_array:
 239        # The GENERATE_SERIES result type is TIMESTAMP array, so to match BQ's semantics for
 240        # GENERATE_DATE_ARRAY we must cast it back to DATE array
 241        gen_series = exp.cast(gen_series, exp.DataType.build("ARRAY<DATE>"))
 242
 243    return self.sql(gen_series)
 244
 245
 246def _json_extract_value_array_sql(
 247    self: DuckDB.Generator, expression: exp.JSONValueArray | exp.JSONExtractArray
 248) -> str:
 249    json_extract = exp.JSONExtract(this=expression.this, expression=expression.expression)
 250    data_type = "ARRAY<STRING>" if isinstance(expression, exp.JSONValueArray) else "ARRAY<JSON>"
 251    return self.sql(exp.cast(json_extract, to=exp.DataType.build(data_type)))
 252
 253
 254class DuckDB(Dialect):
 255    NULL_ORDERING = "nulls_are_last"
 256    SUPPORTS_USER_DEFINED_TYPES = True
 257    SAFE_DIVISION = True
 258    INDEX_OFFSET = 1
 259    CONCAT_COALESCE = True
 260    SUPPORTS_ORDER_BY_ALL = True
 261    SUPPORTS_FIXED_SIZE_ARRAYS = True
 262    STRICT_JSON_PATH_SYNTAX = False
 263    NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
 264
 265    # https://duckdb.org/docs/sql/introduction.html#creating-a-new-table
 266    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
 267
 268    DATE_PART_MAPPING = {
 269        **Dialect.DATE_PART_MAPPING,
 270        "DAYOFWEEKISO": "ISODOW",
 271    }
 272    DATE_PART_MAPPING.pop("WEEKDAY")
 273
 274    def to_json_path(self, path: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]:
 275        if isinstance(path, exp.Literal):
 276            # DuckDB also supports the JSON pointer syntax, where every path starts with a `/`.
 277            # Additionally, it allows accessing the back of lists using the `[#-i]` syntax.
 278            # This check ensures we'll avoid trying to parse these as JSON paths, which can
 279            # either result in a noisy warning or in an invalid representation of the path.
 280            path_text = path.name
 281            if path_text.startswith("/") or "[#" in path_text:
 282                return path
 283
 284        return super().to_json_path(path)
 285
 286    class Tokenizer(tokens.Tokenizer):
 287        BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
 288        HEREDOC_STRINGS = ["$"]
 289
 290        HEREDOC_TAG_IS_IDENTIFIER = True
 291        HEREDOC_STRING_ALTERNATIVE = TokenType.PARAMETER
 292
 293        KEYWORDS = {
 294            **tokens.Tokenizer.KEYWORDS,
 295            "//": TokenType.DIV,
 296            "**": TokenType.DSTAR,
 297            "^@": TokenType.CARET_AT,
 298            "@>": TokenType.AT_GT,
 299            "<@": TokenType.LT_AT,
 300            "ATTACH": TokenType.ATTACH,
 301            "BINARY": TokenType.VARBINARY,
 302            "BITSTRING": TokenType.BIT,
 303            "BPCHAR": TokenType.TEXT,
 304            "CHAR": TokenType.TEXT,
 305            "DATETIME": TokenType.TIMESTAMPNTZ,
 306            "DETACH": TokenType.DETACH,
 307            "FORCE": TokenType.FORCE,
 308            "INSTALL": TokenType.INSTALL,
 309            "LOGICAL": TokenType.BOOLEAN,
 310            "ONLY": TokenType.ONLY,
 311            "PIVOT_WIDER": TokenType.PIVOT,
 312            "POSITIONAL": TokenType.POSITIONAL,
 313            "RESET": TokenType.COMMAND,
 314            "SIGNED": TokenType.INT,
 315            "STRING": TokenType.TEXT,
 316            "SUMMARIZE": TokenType.SUMMARIZE,
 317            "TIMESTAMP": TokenType.TIMESTAMPNTZ,
 318            "TIMESTAMP_S": TokenType.TIMESTAMP_S,
 319            "TIMESTAMP_MS": TokenType.TIMESTAMP_MS,
 320            "TIMESTAMP_NS": TokenType.TIMESTAMP_NS,
 321            "TIMESTAMP_US": TokenType.TIMESTAMP,
 322            "UBIGINT": TokenType.UBIGINT,
 323            "UINTEGER": TokenType.UINT,
 324            "USMALLINT": TokenType.USMALLINT,
 325            "UTINYINT": TokenType.UTINYINT,
 326            "VARCHAR": TokenType.TEXT,
 327        }
 328        KEYWORDS.pop("/*+")
 329
 330        SINGLE_TOKENS = {
 331            **tokens.Tokenizer.SINGLE_TOKENS,
 332            "$": TokenType.PARAMETER,
 333        }
 334
 335        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
 336
 337    class Parser(parser.Parser):
 338        MAP_KEYS_ARE_ARBITRARY_EXPRESSIONS = True
 339
 340        BITWISE = {
 341            **parser.Parser.BITWISE,
 342            TokenType.TILDA: exp.RegexpLike,
 343        }
 344        BITWISE.pop(TokenType.CARET)
 345
 346        RANGE_PARSERS = {
 347            **parser.Parser.RANGE_PARSERS,
 348            TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
 349            TokenType.CARET_AT: binary_range_parser(exp.StartsWith),
 350        }
 351
 352        EXPONENT = {
 353            **parser.Parser.EXPONENT,
 354            TokenType.CARET: exp.Pow,
 355            TokenType.DSTAR: exp.Pow,
 356        }
 357
 358        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "STRUCT_PACK"}
 359
 360        SHOW_PARSERS = {
 361            "TABLES": _show_parser("TABLES"),
 362            "ALL TABLES": _show_parser("ALL TABLES"),
 363        }
 364
 365        FUNCTIONS = {
 366            **parser.Parser.FUNCTIONS,
 367            "ANY_VALUE": lambda args: exp.IgnoreNulls(this=exp.AnyValue.from_arg_list(args)),
 368            "ARRAY_REVERSE_SORT": _build_sort_array_desc,
 369            "ARRAY_SORT": exp.SortArray.from_arg_list,
 370            "BIT_AND": exp.BitwiseAndAgg.from_arg_list,
 371            "BIT_OR": exp.BitwiseOrAgg.from_arg_list,
 372            "BIT_XOR": exp.BitwiseXorAgg.from_arg_list,
 373            "DATEDIFF": _build_date_diff,
 374            "DATE_DIFF": _build_date_diff,
 375            "DATE_TRUNC": date_trunc_to_time,
 376            "DATETRUNC": date_trunc_to_time,
 377            "DECODE": lambda args: exp.Decode(
 378                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
 379            ),
 380            "EDITDIST3": exp.Levenshtein.from_arg_list,
 381            "ENCODE": lambda args: exp.Encode(
 382                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
 383            ),
 384            "EPOCH": exp.TimeToUnix.from_arg_list,
 385            "EPOCH_MS": lambda args: exp.UnixToTime(
 386                this=seq_get(args, 0), scale=exp.UnixToTime.MILLIS
 387            ),
 388            "GENERATE_SERIES": _build_generate_series(),
 389            "JSON": exp.ParseJSON.from_arg_list,
 390            "JSON_EXTRACT_PATH": parser.build_extract_json_with_path(exp.JSONExtract),
 391            "JSON_EXTRACT_STRING": parser.build_extract_json_with_path(exp.JSONExtractScalar),
 392            "LIST_CONTAINS": exp.ArrayContains.from_arg_list,
 393            "LIST_COSINE_DISTANCE": exp.CosineDistance.from_arg_list,
 394            "LIST_DISTANCE": exp.EuclideanDistance.from_arg_list,
 395            "LIST_FILTER": exp.ArrayFilter.from_arg_list,
 396            "LIST_HAS": exp.ArrayContains.from_arg_list,
 397            "LIST_HAS_ANY": exp.ArrayOverlaps.from_arg_list,
 398            "LIST_REVERSE_SORT": _build_sort_array_desc,
 399            "LIST_SORT": exp.SortArray.from_arg_list,
 400            "LIST_TRANSFORM": exp.Transform.from_arg_list,
 401            "LIST_VALUE": lambda args: exp.Array(expressions=args),
 402            "MAKE_TIME": exp.TimeFromParts.from_arg_list,
 403            "MAKE_TIMESTAMP": _build_make_timestamp,
 404            "QUANTILE_CONT": exp.PercentileCont.from_arg_list,
 405            "QUANTILE_DISC": exp.PercentileDisc.from_arg_list,
 406            "RANGE": _build_generate_series(end_exclusive=True),
 407            "REGEXP_EXTRACT": build_regexp_extract(exp.RegexpExtract),
 408            "REGEXP_EXTRACT_ALL": build_regexp_extract(exp.RegexpExtractAll),
 409            "REGEXP_MATCHES": exp.RegexpLike.from_arg_list,
 410            "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
 411                this=seq_get(args, 0),
 412                expression=seq_get(args, 1),
 413                replacement=seq_get(args, 2),
 414                modifiers=seq_get(args, 3),
 415            ),
 416            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
 417            "STRFTIME": build_formatted_time(exp.TimeToStr, "duckdb"),
 418            "STRING_SPLIT": exp.Split.from_arg_list,
 419            "STRING_SPLIT_REGEX": exp.RegexpSplit.from_arg_list,
 420            "STRING_TO_ARRAY": exp.Split.from_arg_list,
 421            "STRPTIME": build_formatted_time(exp.StrToTime, "duckdb"),
 422            "STRUCT_PACK": exp.Struct.from_arg_list,
 423            "STR_SPLIT": exp.Split.from_arg_list,
 424            "STR_SPLIT_REGEX": exp.RegexpSplit.from_arg_list,
 425            "TIME_BUCKET": exp.DateBin.from_arg_list,
 426            "TO_TIMESTAMP": exp.UnixToTime.from_arg_list,
 427            "UNNEST": exp.Explode.from_arg_list,
 428            "XOR": binary_from_function(exp.BitwiseXor),
 429        }
 430
 431        FUNCTIONS.pop("DATE_SUB")
 432        FUNCTIONS.pop("GLOB")
 433
 434        FUNCTION_PARSERS = {
 435            **parser.Parser.FUNCTION_PARSERS,
 436            **dict.fromkeys(
 437                ("GROUP_CONCAT", "LISTAGG", "STRINGAGG"), lambda self: self._parse_string_agg()
 438            ),
 439        }
 440        FUNCTION_PARSERS.pop("DECODE")
 441
 442        NO_PAREN_FUNCTION_PARSERS = {
 443            **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
 444            "MAP": lambda self: self._parse_map(),
 445            "@": lambda self: exp.Abs(this=self._parse_bitwise()),
 446        }
 447
 448        TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - {
 449            TokenType.SEMI,
 450            TokenType.ANTI,
 451        }
 452
 453        PLACEHOLDER_PARSERS = {
 454            **parser.Parser.PLACEHOLDER_PARSERS,
 455            TokenType.PARAMETER: lambda self: (
 456                self.expression(exp.Placeholder, this=self._prev.text)
 457                if self._match(TokenType.NUMBER) or self._match_set(self.ID_VAR_TOKENS)
 458                else None
 459            ),
 460        }
 461
 462        TYPE_CONVERTERS = {
 463            # https://duckdb.org/docs/sql/data_types/numeric
 464            exp.DataType.Type.DECIMAL: build_default_decimal_type(precision=18, scale=3),
 465            # https://duckdb.org/docs/sql/data_types/text
 466            exp.DataType.Type.TEXT: lambda dtype: exp.DataType.build("TEXT"),
 467        }
 468
 469        STATEMENT_PARSERS = {
 470            **parser.Parser.STATEMENT_PARSERS,
 471            TokenType.ATTACH: lambda self: self._parse_attach_detach(),
 472            TokenType.DETACH: lambda self: self._parse_attach_detach(is_attach=False),
 473            TokenType.FORCE: lambda self: self._parse_force(),
 474            TokenType.INSTALL: lambda self: self._parse_install(),
 475            TokenType.SHOW: lambda self: self._parse_show(),
 476        }
 477
 478        SET_PARSERS = {
 479            **parser.Parser.SET_PARSERS,
 480            "VARIABLE": lambda self: self._parse_set_item_assignment("VARIABLE"),
 481        }
 482
 483        def _parse_lambda(self, alias: bool = False) -> t.Optional[exp.Expression]:
 484            index = self._index
 485            if not self._match_text_seq("LAMBDA"):
 486                return super()._parse_lambda(alias=alias)
 487
 488            expressions = self._parse_csv(self._parse_lambda_arg)
 489            if not self._match(TokenType.COLON):
 490                self._retreat(index)
 491                return None
 492
 493            this = self._replace_lambda(self._parse_assignment(), expressions)
 494            return self.expression(exp.Lambda, this=this, expressions=expressions, colon=True)
 495
 496        def _parse_expression(self) -> t.Optional[exp.Expression]:
 497            # DuckDB supports prefix aliases, e.g. foo: 1
 498            if self._next and self._next.token_type == TokenType.COLON:
 499                alias = self._parse_id_var(tokens=self.ALIAS_TOKENS)
 500                self._match(TokenType.COLON)
 501                comments = self._prev_comments or []
 502
 503                this = self._parse_assignment()
 504                if isinstance(this, exp.Expression):
 505                    # Moves the comment next to the alias in `alias: expr /* comment */`
 506                    comments += this.pop_comments() or []
 507
 508                return self.expression(exp.Alias, comments=comments, this=this, alias=alias)
 509
 510            return super()._parse_expression()
 511
 512        def _parse_table(
 513            self,
 514            schema: bool = False,
 515            joins: bool = False,
 516            alias_tokens: t.Optional[t.Collection[TokenType]] = None,
 517            parse_bracket: bool = False,
 518            is_db_reference: bool = False,
 519            parse_partition: bool = False,
 520            consume_pipe: bool = False,
 521        ) -> t.Optional[exp.Expression]:
 522            # DuckDB supports prefix aliases, e.g. FROM foo: bar
 523            if self._next and self._next.token_type == TokenType.COLON:
 524                alias = self._parse_table_alias(
 525                    alias_tokens=alias_tokens or self.TABLE_ALIAS_TOKENS
 526                )
 527                self._match(TokenType.COLON)
 528                comments = self._prev_comments or []
 529            else:
 530                alias = None
 531                comments = []
 532
 533            table = super()._parse_table(
 534                schema=schema,
 535                joins=joins,
 536                alias_tokens=alias_tokens,
 537                parse_bracket=parse_bracket,
 538                is_db_reference=is_db_reference,
 539                parse_partition=parse_partition,
 540            )
 541            if isinstance(table, exp.Expression) and isinstance(alias, exp.TableAlias):
 542                # Moves the comment next to the alias in `alias: table /* comment */`
 543                comments += table.pop_comments() or []
 544                alias.comments = alias.pop_comments() + comments
 545                table.set("alias", alias)
 546
 547            return table
 548
 549        def _parse_table_sample(self, as_modifier: bool = False) -> t.Optional[exp.TableSample]:
 550            # https://duckdb.org/docs/sql/samples.html
 551            sample = super()._parse_table_sample(as_modifier=as_modifier)
 552            if sample and not sample.args.get("method"):
 553                if sample.args.get("size"):
 554                    sample.set("method", exp.var("RESERVOIR"))
 555                else:
 556                    sample.set("method", exp.var("SYSTEM"))
 557
 558            return sample
 559
 560        def _parse_bracket(
 561            self, this: t.Optional[exp.Expression] = None
 562        ) -> t.Optional[exp.Expression]:
 563            bracket = super()._parse_bracket(this)
 564
 565            if self.dialect.version < Version("1.2.0") and isinstance(bracket, exp.Bracket):
 566                # https://duckdb.org/2025/02/05/announcing-duckdb-120.html#breaking-changes
 567                bracket.set("returns_list_for_maps", True)
 568
 569            return bracket
 570
 571        def _parse_map(self) -> exp.ToMap | exp.Map:
 572            if self._match(TokenType.L_BRACE, advance=False):
 573                return self.expression(exp.ToMap, this=self._parse_bracket())
 574
 575            args = self._parse_wrapped_csv(self._parse_assignment)
 576            return self.expression(exp.Map, keys=seq_get(args, 0), values=seq_get(args, 1))
 577
 578        def _parse_struct_types(self, type_required: bool = False) -> t.Optional[exp.Expression]:
 579            return self._parse_field_def()
 580
 581        def _pivot_column_names(self, aggregations: t.List[exp.Expression]) -> t.List[str]:
 582            if len(aggregations) == 1:
 583                return super()._pivot_column_names(aggregations)
 584            return pivot_column_names(aggregations, dialect="duckdb")
 585
 586        def _parse_attach_detach(self, is_attach=True) -> exp.Attach | exp.Detach:
 587            def _parse_attach_option() -> exp.AttachOption:
 588                return self.expression(
 589                    exp.AttachOption,
 590                    this=self._parse_var(any_token=True),
 591                    expression=self._parse_field(any_token=True),
 592                )
 593
 594            self._match(TokenType.DATABASE)
 595            exists = self._parse_exists(not_=is_attach)
 596            this = self._parse_alias(self._parse_primary_or_var(), explicit=True)
 597
 598            if self._match(TokenType.L_PAREN, advance=False):
 599                expressions = self._parse_wrapped_csv(_parse_attach_option)
 600            else:
 601                expressions = None
 602
 603            return (
 604                self.expression(exp.Attach, this=this, exists=exists, expressions=expressions)
 605                if is_attach
 606                else self.expression(exp.Detach, this=this, exists=exists)
 607            )
 608
 609        def _parse_show_duckdb(self, this: str) -> exp.Show:
 610            return self.expression(exp.Show, this=this)
 611
 612        def _parse_force(self) -> exp.Install | exp.Command:
 613            # FORCE can only be followed by INSTALL or CHECKPOINT
 614            # In the case of CHECKPOINT, we fallback
 615            if not self._match(TokenType.INSTALL):
 616                return self._parse_as_command(self._prev)
 617
 618            return self._parse_install(force=True)
 619
 620        def _parse_install(self, force: bool = False) -> exp.Install:
 621            return self.expression(
 622                exp.Install,
 623                **{  # type: ignore
 624                    "this": self._parse_id_var(),
 625                    "from": self._parse_var_or_string() if self._match(TokenType.FROM) else None,
 626                    "force": force,
 627                },
 628            )
 629
 630        def _parse_primary(self) -> t.Optional[exp.Expression]:
 631            if self._match_pair(TokenType.HASH, TokenType.NUMBER):
 632                return exp.PositionalColumn(this=exp.Literal.number(self._prev.text))
 633
 634            return super()._parse_primary()
 635
 636    class Generator(generator.Generator):
 637        PARAMETER_TOKEN = "$"
 638        NAMED_PLACEHOLDER_TOKEN = "$"
 639        JOIN_HINTS = False
 640        TABLE_HINTS = False
 641        QUERY_HINTS = False
 642        LIMIT_FETCH = "LIMIT"
 643        STRUCT_DELIMITER = ("(", ")")
 644        RENAME_TABLE_WITH_DB = False
 645        NVL2_SUPPORTED = False
 646        SEMI_ANTI_JOIN_WITH_SIDE = False
 647        TABLESAMPLE_KEYWORDS = "USING SAMPLE"
 648        TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
 649        LAST_DAY_SUPPORTS_DATE_PART = False
 650        JSON_KEY_VALUE_PAIR_SEP = ","
 651        IGNORE_NULLS_IN_FUNC = True
 652        JSON_PATH_BRACKETED_KEY_SUPPORTED = False
 653        SUPPORTS_CREATE_TABLE_LIKE = False
 654        MULTI_ARG_DISTINCT = False
 655        CAN_IMPLEMENT_ARRAY_ANY = True
 656        SUPPORTS_TO_NUMBER = False
 657        SUPPORTS_WINDOW_EXCLUDE = True
 658        COPY_HAS_INTO_KEYWORD = False
 659        STAR_EXCEPT = "EXCLUDE"
 660        PAD_FILL_PATTERN_IS_REQUIRED = True
 661        ARRAY_CONCAT_IS_VAR_LEN = False
 662        ARRAY_SIZE_DIM_REQUIRED = False
 663        NORMALIZE_EXTRACT_DATE_PARTS = True
 664        SUPPORTS_LIKE_QUANTIFIERS = False
 665
 666        TRANSFORMS = {
 667            **generator.Generator.TRANSFORMS,
 668            exp.ApproxDistinct: approx_count_distinct_sql,
 669            exp.Array: inline_array_unless_query,
 670            exp.ArrayFilter: rename_func("LIST_FILTER"),
 671            exp.ArrayRemove: remove_from_array_using_filter,
 672            exp.ArraySort: _array_sort_sql,
 673            exp.ArraySum: rename_func("LIST_SUM"),
 674            exp.ArrayUniqueAgg: lambda self, e: self.func(
 675                "LIST", exp.Distinct(expressions=[e.this])
 676            ),
 677            exp.BitwiseAndAgg: rename_func("BIT_AND"),
 678            exp.BitwiseOrAgg: rename_func("BIT_OR"),
 679            exp.BitwiseXor: rename_func("XOR"),
 680            exp.BitwiseXorAgg: rename_func("BIT_XOR"),
 681            exp.CommentColumnConstraint: no_comment_column_constraint_sql,
 682            exp.CosineDistance: rename_func("LIST_COSINE_DISTANCE"),
 683            exp.CurrentDate: lambda *_: "CURRENT_DATE",
 684            exp.CurrentTime: lambda *_: "CURRENT_TIME",
 685            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
 686            exp.DayOfMonth: rename_func("DAYOFMONTH"),
 687            exp.DayOfWeek: rename_func("DAYOFWEEK"),
 688            exp.DayOfWeekIso: rename_func("ISODOW"),
 689            exp.DayOfYear: rename_func("DAYOFYEAR"),
 690            exp.DataType: _datatype_sql,
 691            exp.Date: _date_sql,
 692            exp.DateAdd: date_delta_to_binary_interval_op(),
 693            exp.DateFromParts: rename_func("MAKE_DATE"),
 694            exp.DateSub: date_delta_to_binary_interval_op(),
 695            exp.DateDiff: _date_diff_sql,
 696            exp.DateStrToDate: datestrtodate_sql,
 697            exp.Datetime: no_datetime_sql,
 698            exp.DatetimeSub: date_delta_to_binary_interval_op(),
 699            exp.DatetimeAdd: date_delta_to_binary_interval_op(),
 700            exp.DateToDi: lambda self,
 701            e: f"CAST(STRFTIME({self.sql(e, 'this')}, {DuckDB.DATEINT_FORMAT}) AS INT)",
 702            exp.Decode: lambda self, e: encode_decode_sql(self, e, "DECODE", replace=False),
 703            exp.DiToDate: lambda self,
 704            e: f"CAST(STRPTIME(CAST({self.sql(e, 'this')} AS TEXT), {DuckDB.DATEINT_FORMAT}) AS DATE)",
 705            exp.Encode: lambda self, e: encode_decode_sql(self, e, "ENCODE", replace=False),
 706            exp.EuclideanDistance: rename_func("LIST_DISTANCE"),
 707            exp.GenerateDateArray: _generate_datetime_array_sql,
 708            exp.GenerateTimestampArray: _generate_datetime_array_sql,
 709            exp.GroupConcat: lambda self, e: groupconcat_sql(self, e, within_group=False),
 710            exp.HexString: lambda self, e: self.hexstring_sql(e, binary_function_repr="FROM_HEX"),
 711            exp.Explode: rename_func("UNNEST"),
 712            exp.IntDiv: lambda self, e: self.binary(e, "//"),
 713            exp.IsInf: rename_func("ISINF"),
 714            exp.IsNan: rename_func("ISNAN"),
 715            exp.JSONBExists: rename_func("JSON_EXISTS"),
 716            exp.JSONExtract: _arrow_json_extract_sql,
 717            exp.JSONExtractArray: _json_extract_value_array_sql,
 718            exp.JSONExtractScalar: _arrow_json_extract_sql,
 719            exp.JSONFormat: _json_format_sql,
 720            exp.JSONValueArray: _json_extract_value_array_sql,
 721            exp.Lateral: explode_to_unnest_sql,
 722            exp.LogicalOr: rename_func("BOOL_OR"),
 723            exp.LogicalAnd: rename_func("BOOL_AND"),
 724            exp.MakeInterval: lambda self, e: no_make_interval_sql(self, e, sep=" "),
 725            exp.MD5Digest: lambda self, e: self.func("UNHEX", self.func("MD5", e.this)),
 726            exp.MonthsBetween: lambda self, e: self.func(
 727                "DATEDIFF",
 728                "'month'",
 729                exp.cast(e.expression, exp.DataType.Type.TIMESTAMP, copy=True),
 730                exp.cast(e.this, exp.DataType.Type.TIMESTAMP, copy=True),
 731            ),
 732            exp.PercentileCont: rename_func("QUANTILE_CONT"),
 733            exp.PercentileDisc: rename_func("QUANTILE_DISC"),
 734            # DuckDB doesn't allow qualified columns inside of PIVOT expressions.
 735            # See: https://github.com/duckdb/duckdb/blob/671faf92411182f81dce42ac43de8bfb05d9909e/src/planner/binder/tableref/bind_pivot.cpp#L61-L62
 736            exp.Pivot: transforms.preprocess([transforms.unqualify_columns]),
 737            exp.RegexpReplace: lambda self, e: self.func(
 738                "REGEXP_REPLACE",
 739                e.this,
 740                e.expression,
 741                e.args.get("replacement"),
 742                e.args.get("modifiers"),
 743            ),
 744            exp.RegexpLike: rename_func("REGEXP_MATCHES"),
 745            exp.RegexpILike: lambda self, e: self.func(
 746                "REGEXP_MATCHES", e.this, e.expression, exp.Literal.string("i")
 747            ),
 748            exp.RegexpSplit: rename_func("STR_SPLIT_REGEX"),
 749            exp.Return: lambda self, e: self.sql(e, "this"),
 750            exp.ReturnsProperty: lambda self, e: "TABLE" if isinstance(e.this, exp.Schema) else "",
 751            exp.Rand: rename_func("RANDOM"),
 752            exp.SHA: rename_func("SHA1"),
 753            exp.SHA2: sha256_sql,
 754            exp.Split: rename_func("STR_SPLIT"),
 755            exp.SortArray: _sort_array_sql,
 756            exp.StrPosition: strposition_sql,
 757            exp.StrToUnix: lambda self, e: self.func(
 758                "EPOCH", self.func("STRPTIME", e.this, self.format_time(e))
 759            ),
 760            exp.Struct: _struct_sql,
 761            exp.Transform: rename_func("LIST_TRANSFORM"),
 762            exp.TimeAdd: date_delta_to_binary_interval_op(),
 763            exp.Time: no_time_sql,
 764            exp.TimeDiff: _timediff_sql,
 765            exp.Timestamp: no_timestamp_sql,
 766            exp.TimestampDiff: lambda self, e: self.func(
 767                "DATE_DIFF", exp.Literal.string(e.unit), e.expression, e.this
 768            ),
 769            exp.TimestampTrunc: timestamptrunc_sql(),
 770            exp.TimeStrToDate: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.DATE)),
 771            exp.TimeStrToTime: timestrtotime_sql,
 772            exp.TimeStrToUnix: lambda self, e: self.func(
 773                "EPOCH", exp.cast(e.this, exp.DataType.Type.TIMESTAMP)
 774            ),
 775            exp.TimeToStr: lambda self, e: self.func("STRFTIME", e.this, self.format_time(e)),
 776            exp.TimeToUnix: rename_func("EPOCH"),
 777            exp.TsOrDiToDi: lambda self,
 778            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS TEXT), '-', ''), 1, 8) AS INT)",
 779            exp.TsOrDsAdd: date_delta_to_binary_interval_op(),
 780            exp.TsOrDsDiff: lambda self, e: self.func(
 781                "DATE_DIFF",
 782                f"'{e.args.get('unit') or 'DAY'}'",
 783                exp.cast(e.expression, exp.DataType.Type.TIMESTAMP),
 784                exp.cast(e.this, exp.DataType.Type.TIMESTAMP),
 785            ),
 786            exp.UnixToStr: lambda self, e: self.func(
 787                "STRFTIME", self.func("TO_TIMESTAMP", e.this), self.format_time(e)
 788            ),
 789            exp.DatetimeTrunc: lambda self, e: self.func(
 790                "DATE_TRUNC", unit_to_str(e), exp.cast(e.this, exp.DataType.Type.DATETIME)
 791            ),
 792            exp.UnixToTime: _unix_to_time_sql,
 793            exp.UnixToTimeStr: lambda self, e: f"CAST(TO_TIMESTAMP({self.sql(e, 'this')}) AS TEXT)",
 794            exp.VariancePop: rename_func("VAR_POP"),
 795            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
 796            exp.Xor: bool_xor_sql,
 797            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
 798                rename_func("LEVENSHTEIN")
 799            ),
 800            exp.JSONObjectAgg: rename_func("JSON_GROUP_OBJECT"),
 801            exp.JSONBObjectAgg: rename_func("JSON_GROUP_OBJECT"),
 802            exp.DateBin: rename_func("TIME_BUCKET"),
 803        }
 804
 805        SUPPORTED_JSON_PATH_PARTS = {
 806            exp.JSONPathKey,
 807            exp.JSONPathRoot,
 808            exp.JSONPathSubscript,
 809            exp.JSONPathWildcard,
 810        }
 811
 812        TYPE_MAPPING = {
 813            **generator.Generator.TYPE_MAPPING,
 814            exp.DataType.Type.BINARY: "BLOB",
 815            exp.DataType.Type.BPCHAR: "TEXT",
 816            exp.DataType.Type.CHAR: "TEXT",
 817            exp.DataType.Type.DATETIME: "TIMESTAMP",
 818            exp.DataType.Type.FLOAT: "REAL",
 819            exp.DataType.Type.JSONB: "JSON",
 820            exp.DataType.Type.NCHAR: "TEXT",
 821            exp.DataType.Type.NVARCHAR: "TEXT",
 822            exp.DataType.Type.UINT: "UINTEGER",
 823            exp.DataType.Type.VARBINARY: "BLOB",
 824            exp.DataType.Type.ROWVERSION: "BLOB",
 825            exp.DataType.Type.VARCHAR: "TEXT",
 826            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
 827            exp.DataType.Type.TIMESTAMP_S: "TIMESTAMP_S",
 828            exp.DataType.Type.TIMESTAMP_MS: "TIMESTAMP_MS",
 829            exp.DataType.Type.TIMESTAMP_NS: "TIMESTAMP_NS",
 830        }
 831
 832        # https://github.com/duckdb/duckdb/blob/ff7f24fd8e3128d94371827523dae85ebaf58713/third_party/libpg_query/grammar/keywords/reserved_keywords.list#L1-L77
 833        RESERVED_KEYWORDS = {
 834            "array",
 835            "analyse",
 836            "union",
 837            "all",
 838            "when",
 839            "in_p",
 840            "default",
 841            "create_p",
 842            "window",
 843            "asymmetric",
 844            "to",
 845            "else",
 846            "localtime",
 847            "from",
 848            "end_p",
 849            "select",
 850            "current_date",
 851            "foreign",
 852            "with",
 853            "grant",
 854            "session_user",
 855            "or",
 856            "except",
 857            "references",
 858            "fetch",
 859            "limit",
 860            "group_p",
 861            "leading",
 862            "into",
 863            "collate",
 864            "offset",
 865            "do",
 866            "then",
 867            "localtimestamp",
 868            "check_p",
 869            "lateral_p",
 870            "current_role",
 871            "where",
 872            "asc_p",
 873            "placing",
 874            "desc_p",
 875            "user",
 876            "unique",
 877            "initially",
 878            "column",
 879            "both",
 880            "some",
 881            "as",
 882            "any",
 883            "only",
 884            "deferrable",
 885            "null_p",
 886            "current_time",
 887            "true_p",
 888            "table",
 889            "case",
 890            "trailing",
 891            "variadic",
 892            "for",
 893            "on",
 894            "distinct",
 895            "false_p",
 896            "not",
 897            "constraint",
 898            "current_timestamp",
 899            "returning",
 900            "primary",
 901            "intersect",
 902            "having",
 903            "analyze",
 904            "current_user",
 905            "and",
 906            "cast",
 907            "symmetric",
 908            "using",
 909            "order",
 910            "current_catalog",
 911        }
 912
 913        UNWRAPPED_INTERVAL_VALUES = (exp.Literal, exp.Paren)
 914
 915        # DuckDB doesn't generally support CREATE TABLE .. properties
 916        # https://duckdb.org/docs/sql/statements/create_table.html
 917        PROPERTIES_LOCATION = {
 918            prop: exp.Properties.Location.UNSUPPORTED
 919            for prop in generator.Generator.PROPERTIES_LOCATION
 920        }
 921
 922        # There are a few exceptions (e.g. temporary tables) which are supported or
 923        # can be transpiled to DuckDB, so we explicitly override them accordingly
 924        PROPERTIES_LOCATION[exp.LikeProperty] = exp.Properties.Location.POST_SCHEMA
 925        PROPERTIES_LOCATION[exp.TemporaryProperty] = exp.Properties.Location.POST_CREATE
 926        PROPERTIES_LOCATION[exp.ReturnsProperty] = exp.Properties.Location.POST_ALIAS
 927        PROPERTIES_LOCATION[exp.SequenceProperties] = exp.Properties.Location.POST_EXPRESSION
 928
 929        IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS = (
 930            exp.FirstValue,
 931            exp.Lag,
 932            exp.LastValue,
 933            exp.Lead,
 934            exp.NthValue,
 935        )
 936
 937        def lambda_sql(
 938            self, expression: exp.Lambda, arrow_sep: str = "->", wrap: bool = True
 939        ) -> str:
 940            if expression.args.get("colon"):
 941                prefix = "LAMBDA "
 942                arrow_sep = ":"
 943                wrap = False
 944            else:
 945                prefix = ""
 946
 947            lambda_sql = super().lambda_sql(expression, arrow_sep=arrow_sep, wrap=wrap)
 948            return f"{prefix}{lambda_sql}"
 949
 950        def show_sql(self, expression: exp.Show) -> str:
 951            return f"SHOW {expression.name}"
 952
 953        def install_sql(self, expression: exp.Install) -> str:
 954            force = "FORCE " if expression.args.get("force") else ""
 955            this = self.sql(expression, "this")
 956            from_clause = expression.args.get("from")
 957            from_clause = f" FROM {from_clause}" if from_clause else ""
 958            return f"{force}INSTALL {this}{from_clause}"
 959
 960        def fromiso8601timestamp_sql(self, expression: exp.FromISO8601Timestamp) -> str:
 961            return self.sql(exp.cast(expression.this, exp.DataType.Type.TIMESTAMPTZ))
 962
 963        def strtotime_sql(self, expression: exp.StrToTime) -> str:
 964            if expression.args.get("safe"):
 965                formatted_time = self.format_time(expression)
 966                return f"CAST({self.func('TRY_STRPTIME', expression.this, formatted_time)} AS TIMESTAMP)"
 967            return str_to_time_sql(self, expression)
 968
 969        def strtodate_sql(self, expression: exp.StrToDate) -> str:
 970            if expression.args.get("safe"):
 971                formatted_time = self.format_time(expression)
 972                return f"CAST({self.func('TRY_STRPTIME', expression.this, formatted_time)} AS DATE)"
 973            return f"CAST({str_to_time_sql(self, expression)} AS DATE)"
 974
 975        def parsejson_sql(self, expression: exp.ParseJSON) -> str:
 976            arg = expression.this
 977            if expression.args.get("safe"):
 978                return self.sql(exp.case().when(exp.func("json_valid", arg), arg).else_(exp.null()))
 979            return self.func("JSON", arg)
 980
 981        def timefromparts_sql(self, expression: exp.TimeFromParts) -> str:
 982            nano = expression.args.get("nano")
 983            if nano is not None:
 984                expression.set(
 985                    "sec", expression.args["sec"] + nano.pop() / exp.Literal.number(1000000000.0)
 986                )
 987
 988            return rename_func("MAKE_TIME")(self, expression)
 989
 990        def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str:
 991            sec = expression.args["sec"]
 992
 993            milli = expression.args.get("milli")
 994            if milli is not None:
 995                sec += milli.pop() / exp.Literal.number(1000.0)
 996
 997            nano = expression.args.get("nano")
 998            if nano is not None:
 999                sec += nano.pop() / exp.Literal.number(1000000000.0)
1000
1001            if milli or nano:
1002                expression.set("sec", sec)
1003
1004            return rename_func("MAKE_TIMESTAMP")(self, expression)
1005
1006        def tablesample_sql(
1007            self,
1008            expression: exp.TableSample,
1009            tablesample_keyword: t.Optional[str] = None,
1010        ) -> str:
1011            if not isinstance(expression.parent, exp.Select):
1012                # This sample clause only applies to a single source, not the entire resulting relation
1013                tablesample_keyword = "TABLESAMPLE"
1014
1015            if expression.args.get("size"):
1016                method = expression.args.get("method")
1017                if method and method.name.upper() != "RESERVOIR":
1018                    self.unsupported(
1019                        f"Sampling method {method} is not supported with a discrete sample count, "
1020                        "defaulting to reservoir sampling"
1021                    )
1022                    expression.set("method", exp.var("RESERVOIR"))
1023
1024            return super().tablesample_sql(expression, tablesample_keyword=tablesample_keyword)
1025
1026        def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
1027            if isinstance(expression.parent, exp.UserDefinedFunction):
1028                return self.sql(expression, "this")
1029            return super().columndef_sql(expression, sep)
1030
1031        def join_sql(self, expression: exp.Join) -> str:
1032            if (
1033                not expression.args.get("using")
1034                and not expression.args.get("on")
1035                and not expression.method
1036                and (expression.kind in ("", "INNER", "OUTER"))
1037            ):
1038                # Some dialects support `LEFT/INNER JOIN UNNEST(...)` without an explicit ON clause
1039                # DuckDB doesn't, but we can just add a dummy ON clause that is always true
1040                if isinstance(expression.this, exp.Unnest):
1041                    return super().join_sql(expression.on(exp.true()))
1042
1043                expression.args.pop("side", None)
1044                expression.args.pop("kind", None)
1045
1046            return super().join_sql(expression)
1047
1048        def generateseries_sql(self, expression: exp.GenerateSeries) -> str:
1049            # GENERATE_SERIES(a, b) -> [a, b], RANGE(a, b) -> [a, b)
1050            if expression.args.get("is_end_exclusive"):
1051                return rename_func("RANGE")(self, expression)
1052
1053            return self.function_fallback_sql(expression)
1054
1055        def countif_sql(self, expression: exp.CountIf) -> str:
1056            if self.dialect.version >= Version("1.2"):
1057                return self.function_fallback_sql(expression)
1058
1059            # https://github.com/tobymao/sqlglot/pull/4749
1060            return count_if_to_sum(self, expression)
1061
1062        def bracket_sql(self, expression: exp.Bracket) -> str:
1063            if self.dialect.version >= Version("1.2"):
1064                return super().bracket_sql(expression)
1065
1066            # https://duckdb.org/2025/02/05/announcing-duckdb-120.html#breaking-changes
1067            this = expression.this
1068            if isinstance(this, exp.Array):
1069                this.replace(exp.paren(this))
1070
1071            bracket = super().bracket_sql(expression)
1072
1073            if not expression.args.get("returns_list_for_maps"):
1074                if not this.type:
1075                    from sqlglot.optimizer.annotate_types import annotate_types
1076
1077                    this = annotate_types(this, dialect=self.dialect)
1078
1079                if this.is_type(exp.DataType.Type.MAP):
1080                    bracket = f"({bracket})[1]"
1081
1082            return bracket
1083
1084        def withingroup_sql(self, expression: exp.WithinGroup) -> str:
1085            expression_sql = self.sql(expression, "expression")
1086
1087            func = expression.this
1088            if isinstance(func, exp.PERCENTILES):
1089                # Make the order key the first arg and slide the fraction to the right
1090                # https://duckdb.org/docs/sql/aggregates#ordered-set-aggregate-functions
1091                order_col = expression.find(exp.Ordered)
1092                if order_col:
1093                    func.set("expression", func.this)
1094                    func.set("this", order_col.this)
1095
1096            this = self.sql(expression, "this").rstrip(")")
1097
1098            return f"{this}{expression_sql})"
1099
1100        def length_sql(self, expression: exp.Length) -> str:
1101            arg = expression.this
1102
1103            # Dialects like BQ and Snowflake also accept binary values as args, so
1104            # DDB will attempt to infer the type or resort to case/when resolution
1105            if not expression.args.get("binary") or arg.is_string:
1106                return self.func("LENGTH", arg)
1107
1108            if not arg.type:
1109                from sqlglot.optimizer.annotate_types import annotate_types
1110
1111                arg = annotate_types(arg, dialect=self.dialect)
1112
1113            if arg.is_type(*exp.DataType.TEXT_TYPES):
1114                return self.func("LENGTH", arg)
1115
1116            # We need these casts to make duckdb's static type checker happy
1117            blob = exp.cast(arg, exp.DataType.Type.VARBINARY)
1118            varchar = exp.cast(arg, exp.DataType.Type.VARCHAR)
1119
1120            case = (
1121                exp.case(self.func("TYPEOF", arg))
1122                .when("'BLOB'", self.func("OCTET_LENGTH", blob))
1123                .else_(
1124                    exp.Anonymous(this="LENGTH", expressions=[varchar])
1125                )  # anonymous to break length_sql recursion
1126            )
1127
1128            return self.sql(case)
1129
1130        def objectinsert_sql(self, expression: exp.ObjectInsert) -> str:
1131            this = expression.this
1132            key = expression.args.get("key")
1133            key_sql = key.name if isinstance(key, exp.Expression) else ""
1134            value_sql = self.sql(expression, "value")
1135
1136            kv_sql = f"{key_sql} := {value_sql}"
1137
1138            # If the input struct is empty e.g. transpiling OBJECT_INSERT(OBJECT_CONSTRUCT(), key, value) from Snowflake
1139            # then we can generate STRUCT_PACK which will build it since STRUCT_INSERT({}, key := value) is not valid DuckDB
1140            if isinstance(this, exp.Struct) and not this.expressions:
1141                return self.func("STRUCT_PACK", kv_sql)
1142
1143            return self.func("STRUCT_INSERT", this, kv_sql)
1144
1145        def unnest_sql(self, expression: exp.Unnest) -> str:
1146            explode_array = expression.args.get("explode_array")
1147            if explode_array:
1148                # In BigQuery, UNNESTing a nested array leads to explosion of the top-level array & struct
1149                # This is transpiled to DDB by transforming "FROM UNNEST(...)" to "FROM (SELECT UNNEST(..., max_depth => 2))"
1150                expression.expressions.append(
1151                    exp.Kwarg(this=exp.var("max_depth"), expression=exp.Literal.number(2))
1152                )
1153
1154                # If BQ's UNNEST is aliased, we transform it from a column alias to a table alias in DDB
1155                alias = expression.args.get("alias")
1156                if isinstance(alias, exp.TableAlias):
1157                    expression.set("alias", None)
1158                    if alias.columns:
1159                        alias = exp.TableAlias(this=seq_get(alias.columns, 0))
1160
1161                unnest_sql = super().unnest_sql(expression)
1162                select = exp.Select(expressions=[unnest_sql]).subquery(alias)
1163                return self.sql(select)
1164
1165            return super().unnest_sql(expression)
1166
1167        def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
1168            this = expression.this
1169
1170            if isinstance(this, self.IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS):
1171                # DuckDB should render IGNORE NULLS only for the general-purpose
1172                # window functions that accept it e.g. FIRST_VALUE(... IGNORE NULLS) OVER (...)
1173                return super().ignorenulls_sql(expression)
1174
1175            if isinstance(this, exp.First):
1176                this = exp.AnyValue(this=this.this)
1177
1178            if not isinstance(this, exp.AnyValue):
1179                self.unsupported("IGNORE NULLS is not supported for non-window functions.")
1180
1181            return self.sql(this)
1182
1183        def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
1184            if isinstance(expression.this, self.IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS):
1185                # DuckDB should render RESPECT NULLS only for the general-purpose
1186                # window functions that accept it e.g. FIRST_VALUE(... RESPECT NULLS) OVER (...)
1187                return super().respectnulls_sql(expression)
1188
1189            self.unsupported("RESPECT NULLS is not supported for non-window functions.")
1190            return self.sql(expression, "this")
1191
1192        def arraytostring_sql(self, expression: exp.ArrayToString) -> str:
1193            this = self.sql(expression, "this")
1194            null_text = self.sql(expression, "null")
1195
1196            if null_text:
1197                this = f"LIST_TRANSFORM({this}, x -> COALESCE(x, {null_text}))"
1198
1199            return self.func("ARRAY_TO_STRING", this, expression.expression)
1200
1201        @unsupported_args("position", "occurrence")
1202        def regexpextract_sql(self, expression: exp.RegexpExtract) -> str:
1203            group = expression.args.get("group")
1204            params = expression.args.get("parameters")
1205
1206            # Do not render group if there is no following argument,
1207            # and it's the default value for this dialect
1208            if (
1209                not params
1210                and group
1211                and group.name == str(self.dialect.REGEXP_EXTRACT_DEFAULT_GROUP)
1212            ):
1213                group = None
1214            return self.func(
1215                "REGEXP_EXTRACT", expression.this, expression.expression, group, params
1216            )
1217
1218        @unsupported_args("culture")
1219        def numbertostr_sql(self, expression: exp.NumberToStr) -> str:
1220            fmt = expression.args.get("format")
1221            if fmt and fmt.is_int:
1222                return self.func("FORMAT", f"'{{:,.{fmt.name}f}}'", expression.this)
1223
1224            self.unsupported("Only integer formats are supported by NumberToStr")
1225            return self.function_fallback_sql(expression)
1226
1227        def autoincrementcolumnconstraint_sql(self, _) -> str:
1228            self.unsupported("The AUTOINCREMENT column constraint is not supported by DuckDB")
1229            return ""
1230
1231        def aliases_sql(self, expression: exp.Aliases) -> str:
1232            this = expression.this
1233            if isinstance(this, exp.Posexplode):
1234                return self.posexplode_sql(this)
1235
1236            return super().aliases_sql(expression)
1237
1238        def posexplode_sql(self, expression: exp.Posexplode) -> str:
1239            this = expression.this
1240            parent = expression.parent
1241
1242            # The default Spark aliases are "pos" and "col", unless specified otherwise
1243            pos, col = exp.to_identifier("pos"), exp.to_identifier("col")
1244
1245            if isinstance(parent, exp.Aliases):
1246                # Column case: SELECT POSEXPLODE(col) [AS (a, b)]
1247                pos, col = parent.expressions
1248            elif isinstance(parent, exp.Table):
1249                # Table case: SELECT * FROM POSEXPLODE(col) [AS (a, b)]
1250                alias = parent.args.get("alias")
1251                if alias:
1252                    pos, col = alias.columns or [pos, col]
1253                    alias.pop()
1254
1255            # Translate POSEXPLODE to UNNEST + GENERATE_SUBSCRIPTS
1256            # Note: In Spark pos is 0-indexed, but in DuckDB it's 1-indexed, so we subtract 1 from GENERATE_SUBSCRIPTS
1257            unnest_sql = self.sql(exp.Unnest(expressions=[this], alias=col))
1258            gen_subscripts = self.sql(
1259                exp.Alias(
1260                    this=exp.Anonymous(
1261                        this="GENERATE_SUBSCRIPTS", expressions=[this, exp.Literal.number(1)]
1262                    )
1263                    - exp.Literal.number(1),
1264                    alias=pos,
1265                )
1266            )
1267
1268            posexplode_sql = self.format_args(gen_subscripts, unnest_sql)
1269
1270            if isinstance(parent, exp.From) or (parent and isinstance(parent.parent, exp.From)):
1271                # SELECT * FROM POSEXPLODE(col) -> SELECT * FROM (SELECT GENERATE_SUBSCRIPTS(...), UNNEST(...))
1272                return self.sql(exp.Subquery(this=exp.Select(expressions=[posexplode_sql])))
1273
1274            return posexplode_sql
1275
1276        def addmonths_sql(self, expression: exp.AddMonths) -> str:
1277            this = expression.this
1278
1279            if not this.type:
1280                from sqlglot.optimizer.annotate_types import annotate_types
1281
1282                this = annotate_types(this, dialect=self.dialect)
1283
1284            if this.is_type(*exp.DataType.TEXT_TYPES):
1285                this = exp.Cast(this=this, to=exp.DataType(this=exp.DataType.Type.TIMESTAMP))
1286
1287            func = self.func(
1288                "DATE_ADD", this, exp.Interval(this=expression.expression, unit=exp.var("MONTH"))
1289            )
1290
1291            # DuckDB's DATE_ADD function returns TIMESTAMP/DATETIME by default, even when the input is DATE
1292            # To match for example Snowflake's ADD_MONTHS behavior (which preserves the input type)
1293            # We need to cast the result back to the original type when the input is DATE or TIMESTAMPTZ
1294            # Example: ADD_MONTHS('2023-01-31'::date, 1) should return DATE, not TIMESTAMP
1295            if this.is_type(exp.DataType.Type.DATE, exp.DataType.Type.TIMESTAMPTZ):
1296                return self.sql(exp.Cast(this=func, to=this.type))
1297
1298            return self.sql(func)
1299
1300        def format_sql(self, expression: exp.Format) -> str:
1301            if expression.name.lower() == "%s" and len(expression.expressions) == 1:
1302                return self.func("FORMAT", "'{}'", expression.expressions[0])
1303
1304            return self.function_fallback_sql(expression)