1"""sqlglot expressions query."""
   2
   3from __future__ import annotations
   4
   5import typing as t
   6
   7from sqlglot.errors import ParseError
   8from sqlglot.helper import trait, ensure_list
   9from sqlglot.expressions.core import (
  10    Aliases,
  11    Column,
  12    Condition,
  13    Distinct,
  14    Dot,
  15    Expr,
  16    Expression,
  17    Func,
  18    Hint,
  19    Identifier,
  20    In,
  21    _apply_builder,
  22    _apply_child_list_builder,
  23    _apply_list_builder,
  24    _apply_conjunction_builder,
  25    _apply_set_operation,
  26    ExpOrStr,
  27    QUERY_MODIFIERS,
  28    maybe_parse,
  29    maybe_copy,
  30    to_identifier,
  31    convert,
  32    and_,
  33    alias_,
  34    column,
  35)
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39    from sqlglot.expressions.datatypes import DataType
  40    from sqlglot.expressions.constraints import ColumnConstraint
  41    from sqlglot.expressions.ddl import Create
  42    from sqlglot.expressions.array import Unnest
  43    from sqlglot._typing import E, ParserArgs, ParserNoDialectArgs
  44    from typing_extensions import Unpack
  45
  46    S = t.TypeVar("S", bound="SetOperation")
  47    Q = t.TypeVar("Q", bound="Query")
  48
  49
  50def _apply_cte_builder(
  51    instance: E,
  52    alias: ExpOrStr,
  53    as_: ExpOrStr,
  54    recursive: bool | None = None,
  55    materialized: bool | None = None,
  56    append: bool = True,
  57    dialect: DialectType = None,
  58    copy: bool = True,
  59    scalar: bool | None = None,
  60    **opts: Unpack[ParserNoDialectArgs],
  61) -> E:
  62    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
  63    as_expression = maybe_parse(as_, dialect=dialect, copy=copy, **opts)
  64    if scalar and not isinstance(as_expression, Subquery):
  65        # scalar CTE must be wrapped in a subquery
  66        as_expression = Subquery(this=as_expression)
  67    cte = CTE(this=as_expression, alias=alias_expression, materialized=materialized, scalar=scalar)
  68    return _apply_child_list_builder(
  69        cte,
  70        instance=instance,
  71        arg="with_",
  72        append=append,
  73        copy=copy,
  74        into=With,
  75        properties={"recursive": recursive} if recursive else {},
  76    )
  77
  78
  79@trait
  80class Selectable(Expr):
  81    @property
  82    def selects(self) -> list[Expr]:
  83        raise NotImplementedError("Subclasses must implement selects")
  84
  85    @property
  86    def named_selects(self) -> list[str]:
  87        return _named_selects(self)
  88
  89
  90def _named_selects(self: Expr) -> list[str]:
  91    selectable = t.cast(Selectable, self)
  92    return [select.output_name for select in selectable.selects]
  93
  94
  95@trait
  96class DerivedTable(Selectable):
  97    @property
  98    def selects(self) -> list[Expr]:
  99        this = self.this
 100        return this.selects if isinstance(this, Query) else []
 101
 102
 103@trait
 104class UDTF(DerivedTable):
 105    @property
 106    def selects(self) -> list[Expr]:
 107        alias = self.args.get("alias")
 108        return alias.columns if alias else []
 109
 110
 111@trait
 112class Query(Selectable):
 113    """Trait for any SELECT/UNION/etc. query expression."""
 114
 115    @property
 116    def ctes(self) -> list[CTE]:
 117        with_ = self.args.get("with_")
 118        return with_.expressions if with_ else []
 119
 120    def select(
 121        self: Q,
 122        *expressions: ExpOrStr | None,
 123        append: bool = True,
 124        dialect: DialectType = None,
 125        copy: bool = True,
 126        **opts: Unpack[ParserNoDialectArgs],
 127    ) -> Q:
 128        raise NotImplementedError("Query objects must implement `select`")
 129
 130    def subquery(self, alias: ExpOrStr | None = None, copy: bool = True) -> Subquery:
 131        """
 132        Returns a `Subquery` that wraps around this query.
 133
 134        Example:
 135            >>> subquery = Select().select("x").from_("tbl").subquery()
 136            >>> Select().select("x").from_(subquery).sql()
 137            'SELECT x FROM (SELECT x FROM tbl)'
 138
 139        Args:
 140            alias: an optional alias for the subquery.
 141            copy: if `False`, modify this expression instance in-place.
 142        """
 143        instance = maybe_copy(self, copy)
 144        if not isinstance(alias, Expr):
 145            alias = TableAlias(this=to_identifier(alias)) if alias else None
 146
 147        return Subquery(this=instance, alias=alias)
 148
 149    def limit(
 150        self: Q,
 151        expression: ExpOrStr | int,
 152        dialect: DialectType = None,
 153        copy: bool = True,
 154        **opts: Unpack[ParserNoDialectArgs],
 155    ) -> Q:
 156        """
 157        Adds a LIMIT clause to this query.
 158
 159        Example:
 160            >>> Select().select("1").union(Select().select("1")).limit(1).sql()
 161            'SELECT 1 UNION SELECT 1 LIMIT 1'
 162
 163        Args:
 164            expression: the SQL code string to parse.
 165                This can also be an integer.
 166                If a `Limit` instance is passed, it will be used as-is.
 167                If another `Expr` instance is passed, it will be wrapped in a `Limit`.
 168            dialect: the dialect used to parse the input expression.
 169            copy: if `False`, modify this expression instance in-place.
 170            opts: other options to use to parse the input expressions.
 171
 172        Returns:
 173            A limited Select expression.
 174        """
 175        return _apply_builder(
 176            expression=expression,
 177            instance=self,
 178            arg="limit",
 179            into=Limit,
 180            prefix="LIMIT",
 181            dialect=dialect,
 182            copy=copy,
 183            into_arg="expression",
 184            **opts,
 185        )
 186
 187    def offset(
 188        self: Q,
 189        expression: ExpOrStr | int,
 190        dialect: DialectType = None,
 191        copy: bool = True,
 192        **opts: Unpack[ParserNoDialectArgs],
 193    ) -> Q:
 194        """
 195        Set the OFFSET expression.
 196
 197        Example:
 198            >>> Select().from_("tbl").select("x").offset(10).sql()
 199            'SELECT x FROM tbl OFFSET 10'
 200
 201        Args:
 202            expression: the SQL code string to parse.
 203                This can also be an integer.
 204                If a `Offset` instance is passed, this is used as-is.
 205                If another `Expr` instance is passed, it will be wrapped in a `Offset`.
 206            dialect: the dialect used to parse the input expression.
 207            copy: if `False`, modify this expression instance in-place.
 208            opts: other options to use to parse the input expressions.
 209
 210        Returns:
 211            The modified Select expression.
 212        """
 213        return _apply_builder(
 214            expression=expression,
 215            instance=self,
 216            arg="offset",
 217            into=Offset,
 218            prefix="OFFSET",
 219            dialect=dialect,
 220            copy=copy,
 221            into_arg="expression",
 222            **opts,
 223        )
 224
 225    def order_by(
 226        self: Q,
 227        *expressions: ExpOrStr | None,
 228        append: bool = True,
 229        dialect: DialectType = None,
 230        copy: bool = True,
 231        **opts: Unpack[ParserNoDialectArgs],
 232    ) -> Q:
 233        """
 234        Set the ORDER BY expression.
 235
 236        Example:
 237            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
 238            'SELECT x FROM tbl ORDER BY x DESC'
 239
 240        Args:
 241            *expressions: the SQL code strings to parse.
 242                If a `Group` instance is passed, this is used as-is.
 243                If another `Expr` instance is passed, it will be wrapped in a `Order`.
 244            append: if `True`, add to any existing expressions.
 245                Otherwise, this flattens all the `Order` expression into a single expression.
 246            dialect: the dialect used to parse the input expression.
 247            copy: if `False`, modify this expression instance in-place.
 248            opts: other options to use to parse the input expressions.
 249
 250        Returns:
 251            The modified Select expression.
 252        """
 253        return _apply_child_list_builder(
 254            *expressions,
 255            instance=self,
 256            arg="order",
 257            append=append,
 258            copy=copy,
 259            prefix="ORDER BY",
 260            into=Order,
 261            dialect=dialect,
 262            **opts,
 263        )
 264
 265    def where(
 266        self: Q,
 267        *expressions: ExpOrStr | None,
 268        append: bool = True,
 269        dialect: DialectType = None,
 270        copy: bool = True,
 271        **opts: Unpack[ParserNoDialectArgs],
 272    ) -> Q:
 273        """
 274        Append to or set the WHERE expressions.
 275
 276        Examples:
 277            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
 278            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
 279
 280        Args:
 281            *expressions: the SQL code strings to parse.
 282                If an `Expr` instance is passed, it will be used as-is.
 283                Multiple expressions are combined with an AND operator.
 284            append: if `True`, AND the new expressions to any existing expression.
 285                Otherwise, this resets the expression.
 286            dialect: the dialect used to parse the input expressions.
 287            copy: if `False`, modify this expression instance in-place.
 288            opts: other options to use to parse the input expressions.
 289
 290        Returns:
 291            The modified expression.
 292        """
 293        return _apply_conjunction_builder(
 294            *[expr.this if isinstance(expr, Where) else expr for expr in expressions],
 295            instance=self,
 296            arg="where",
 297            append=append,
 298            into=Where,
 299            dialect=dialect,
 300            copy=copy,
 301            **opts,
 302        )
 303
 304    def with_(
 305        self: Q,
 306        alias: ExpOrStr,
 307        as_: ExpOrStr,
 308        recursive: bool | None = None,
 309        materialized: bool | None = None,
 310        append: bool = True,
 311        dialect: DialectType = None,
 312        copy: bool = True,
 313        scalar: bool | None = None,
 314        **opts: Unpack[ParserNoDialectArgs],
 315    ) -> Q:
 316        """
 317        Append to or set the common table expressions.
 318
 319        Example:
 320            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
 321            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
 322
 323        Args:
 324            alias: the SQL code string to parse as the table name.
 325                If an `Expr` instance is passed, this is used as-is.
 326            as_: the SQL code string to parse as the table expression.
 327                If an `Expr` instance is passed, it will be used as-is.
 328            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
 329            materialized: set the MATERIALIZED part of the expression.
 330            append: if `True`, add to any existing expressions.
 331                Otherwise, this resets the expressions.
 332            dialect: the dialect used to parse the input expression.
 333            copy: if `False`, modify this expression instance in-place.
 334            scalar: if `True`, this is a scalar common table expression.
 335            opts: other options to use to parse the input expressions.
 336
 337        Returns:
 338            The modified expression.
 339        """
 340        return _apply_cte_builder(
 341            self,
 342            alias,
 343            as_,
 344            recursive=recursive,
 345            materialized=materialized,
 346            append=append,
 347            dialect=dialect,
 348            copy=copy,
 349            scalar=scalar,
 350            **opts,
 351        )
 352
 353    def union(
 354        self,
 355        *expressions: ExpOrStr,
 356        distinct: bool = True,
 357        dialect: DialectType = None,
 358        copy: bool = True,
 359        **opts: Unpack[ParserNoDialectArgs],
 360    ) -> Union:
 361        """
 362        Builds a UNION expression.
 363
 364        Example:
 365            >>> import sqlglot
 366            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 367            'SELECT * FROM foo UNION SELECT * FROM bla'
 368
 369        Args:
 370            expressions: the SQL code strings.
 371                If `Expr` instances are passed, they will be used as-is.
 372            distinct: set the DISTINCT flag if and only if this is true.
 373            dialect: the dialect used to parse the input expression.
 374            opts: other options to use to parse the input expressions.
 375
 376        Returns:
 377            The new Union expression.
 378        """
 379        return union(self, *expressions, distinct=distinct, dialect=dialect, copy=copy, **opts)
 380
 381    def intersect(
 382        self,
 383        *expressions: ExpOrStr,
 384        distinct: bool = True,
 385        dialect: DialectType = None,
 386        copy: bool = True,
 387        **opts: Unpack[ParserNoDialectArgs],
 388    ) -> Intersect:
 389        """
 390        Builds an INTERSECT expression.
 391
 392        Example:
 393            >>> import sqlglot
 394            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 395            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 396
 397        Args:
 398            expressions: the SQL code strings.
 399                If `Expr` instances are passed, they will be used as-is.
 400            distinct: set the DISTINCT flag if and only if this is true.
 401            dialect: the dialect used to parse the input expression.
 402            opts: other options to use to parse the input expressions.
 403
 404        Returns:
 405            The new Intersect expression.
 406        """
 407        return intersect(self, *expressions, distinct=distinct, dialect=dialect, copy=copy, **opts)
 408
 409    def except_(
 410        self,
 411        *expressions: ExpOrStr,
 412        distinct: bool = True,
 413        dialect: DialectType = None,
 414        copy: bool = True,
 415        **opts: Unpack[ParserNoDialectArgs],
 416    ) -> Except:
 417        """
 418        Builds an EXCEPT expression.
 419
 420        Example:
 421            >>> import sqlglot
 422            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 423            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 424
 425        Args:
 426            expressions: the SQL code strings.
 427                If `Expr` instance are passed, they will be used as-is.
 428            distinct: set the DISTINCT flag if and only if this is true.
 429            dialect: the dialect used to parse the input expression.
 430            opts: other options to use to parse the input expressions.
 431
 432        Returns:
 433            The new Except expression.
 434        """
 435        return except_(self, *expressions, distinct=distinct, dialect=dialect, copy=copy, **opts)
 436
 437
 438class QueryBand(Expression):
 439    arg_types = {"this": True, "scope": False, "update": False}
 440
 441
 442class RecursiveWithSearch(Expression):
 443    arg_types = {"kind": True, "this": True, "expression": True, "using": False}
 444
 445
 446class With(Expression):
 447    arg_types = {"expressions": False, "recursive": False, "search": False, "udfs": False}
 448
 449    @property
 450    def recursive(self) -> bool:
 451        return bool(self.args.get("recursive"))
 452
 453
 454class CTE(Expression, DerivedTable):
 455    arg_types = {
 456        "this": True,
 457        "alias": True,
 458        "scalar": False,
 459        "materialized": False,
 460        "key_expressions": False,
 461    }
 462
 463
 464class ProjectionDef(Expression):
 465    arg_types = {"this": True, "expression": True}
 466
 467
 468class TableAlias(Expression):
 469    arg_types = {"this": False, "columns": False}
 470
 471    @property
 472    def columns(self) -> list[t.Any]:
 473        return self.args.get("columns") or []
 474
 475
 476class BitString(Expression, Condition):
 477    is_primitive = True
 478
 479
 480class HexString(Expression, Condition):
 481    arg_types = {"this": True, "is_integer": False}
 482    is_primitive = True
 483
 484
 485class ByteString(Expression, Condition):
 486    arg_types = {"this": True, "is_bytes": False}
 487    is_primitive = True
 488
 489
 490class RawString(Expression, Condition):
 491    is_primitive = True
 492
 493
 494class UnicodeString(Expression, Condition):
 495    arg_types = {"this": True, "escape": False}
 496
 497
 498class ColumnPosition(Expression):
 499    arg_types = {"this": False, "position": True}
 500
 501
 502class ColumnDef(Expression):
 503    arg_types = {
 504        "this": True,
 505        "kind": False,
 506        "constraints": False,
 507        "exists": False,
 508        "position": False,
 509        "default": False,
 510        "output": False,
 511    }
 512
 513    @property
 514    def constraints(self) -> list[ColumnConstraint]:
 515        return self.args.get("constraints") or []
 516
 517    @property
 518    def kind(self) -> DataType | None:
 519        return self.args.get("kind")
 520
 521
 522class Changes(Expression):
 523    arg_types = {"information": True, "at_before": False, "end": False}
 524
 525
 526class Connect(Expression):
 527    arg_types = {"start": False, "connect": True, "nocycle": False}
 528
 529
 530class Prior(Expression):
 531    pass
 532
 533
 534class Into(Expression):
 535    arg_types = {
 536        "this": False,
 537        "temporary": False,
 538        "unlogged": False,
 539        "bulk_collect": False,
 540        "expressions": False,
 541    }
 542
 543
 544class From(Expression):
 545    @property
 546    def name(self) -> str:
 547        return self.this.name
 548
 549    @property
 550    def alias_or_name(self) -> str:
 551        return self.this.alias_or_name
 552
 553
 554class Having(Expression):
 555    pass
 556
 557
 558class Index(Expression):
 559    arg_types = {
 560        "this": False,
 561        "table": False,
 562        "unique": False,
 563        "primary": False,
 564        "amp": False,  # teradata
 565        "params": False,
 566    }
 567
 568
 569class ConditionalInsert(Expression):
 570    arg_types = {"this": True, "expression": False, "else_": False}
 571
 572
 573class MultitableInserts(Expression):
 574    arg_types = {"expressions": True, "kind": True, "source": True}
 575
 576
 577class OnCondition(Expression):
 578    arg_types = {"error": False, "empty": False, "null": False}
 579
 580
 581class Introducer(Expression):
 582    arg_types = {"this": True, "expression": True}
 583
 584
 585class National(Expression):
 586    is_primitive = True
 587
 588
 589class Partition(Expression):
 590    arg_types = {"expressions": True, "subpartition": False}
 591
 592
 593class PartitionRange(Expression):
 594    arg_types = {"this": True, "expression": False, "expressions": False}
 595
 596
 597class PartitionId(Expression):
 598    pass
 599
 600
 601class Fetch(Expression):
 602    arg_types = {
 603        "direction": False,
 604        "count": False,
 605        "limit_options": False,
 606    }
 607
 608
 609class Grant(Expression):
 610    arg_types = {
 611        "privileges": True,
 612        "kind": False,
 613        "securable": True,
 614        "principals": True,
 615        "grant_option": False,
 616    }
 617
 618
 619class Revoke(Expression):
 620    arg_types = {**Grant.arg_types, "cascade": False}
 621
 622
 623class Group(Expression):
 624    arg_types = {
 625        "expressions": False,
 626        "grouping_sets": False,
 627        "grouping_sets_as_group_by_element": False,
 628        "cube": False,
 629        "rollup": False,
 630        "totals": False,
 631        "all": False,
 632    }
 633
 634
 635class Cube(Expression):
 636    arg_types = {"expressions": False}
 637
 638
 639class Rollup(Expression):
 640    arg_types = {"expressions": False}
 641
 642
 643class GroupingSets(Expression):
 644    arg_types = {"expressions": True}
 645
 646
 647class Lambda(Expression):
 648    arg_types = {"this": True, "expressions": True, "colon": False}
 649
 650
 651class Limit(Expression):
 652    arg_types = {
 653        "this": False,
 654        "expression": True,
 655        "offset": False,
 656        "limit_options": False,
 657        "expressions": False,
 658    }
 659
 660
 661class LimitOptions(Expression):
 662    arg_types = {
 663        "percent": False,
 664        "rows": False,
 665        "with_ties": False,
 666    }
 667
 668
 669class Join(Expression):
 670    arg_types = {
 671        "this": True,
 672        "on": False,
 673        "side": False,
 674        "kind": False,
 675        "using": False,
 676        "method": False,
 677        "global_": False,
 678        "hint": False,
 679        "match_condition": False,  # Snowflake
 680        "directed": False,  # Snowflake
 681        "expressions": False,
 682        "pivots": False,
 683    }
 684
 685    @property
 686    def method(self) -> str:
 687        return self.text("method").upper()
 688
 689    @property
 690    def kind(self) -> str:
 691        return self.text("kind").upper()
 692
 693    @property
 694    def side(self) -> str:
 695        return self.text("side").upper()
 696
 697    @property
 698    def hint(self) -> str:
 699        return self.text("hint").upper()
 700
 701    @property
 702    def alias_or_name(self) -> str:
 703        return self.this.alias_or_name
 704
 705    @property
 706    def is_semi_or_anti_join(self) -> bool:
 707        return self.kind in ("SEMI", "ANTI")
 708
 709    def on(
 710        self,
 711        *expressions: ExpOrStr | None,
 712        append: bool = True,
 713        dialect: DialectType = None,
 714        copy: bool = True,
 715        **opts: Unpack[ParserNoDialectArgs],
 716    ) -> Join:
 717        """
 718        Append to or set the ON expressions.
 719
 720        Example:
 721            >>> import sqlglot
 722            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
 723            'JOIN x ON y = 1'
 724
 725        Args:
 726            *expressions: the SQL code strings to parse.
 727                If an `Expr` instance is passed, it will be used as-is.
 728                Multiple expressions are combined with an AND operator.
 729            append: if `True`, AND the new expressions to any existing expression.
 730                Otherwise, this resets the expression.
 731            dialect: the dialect used to parse the input expressions.
 732            copy: if `False`, modify this expression instance in-place.
 733            opts: other options to use to parse the input expressions.
 734
 735        Returns:
 736            The modified Join expression.
 737        """
 738        join = _apply_conjunction_builder(
 739            *expressions,
 740            instance=self,
 741            arg="on",
 742            append=append,
 743            dialect=dialect,
 744            copy=copy,
 745            **opts,
 746        )
 747
 748        if join.kind == "CROSS":
 749            join.set("kind", None)
 750
 751        return join
 752
 753    def using(
 754        self,
 755        *expressions: ExpOrStr | None,
 756        append: bool = True,
 757        dialect: DialectType = None,
 758        copy: bool = True,
 759        **opts: Unpack[ParserNoDialectArgs],
 760    ) -> Join:
 761        """
 762        Append to or set the USING expressions.
 763
 764        Example:
 765            >>> import sqlglot
 766            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
 767            'JOIN x USING (foo, bla)'
 768
 769        Args:
 770            *expressions: the SQL code strings to parse.
 771                If an `Expr` instance is passed, it will be used as-is.
 772            append: if `True`, concatenate the new expressions to the existing "using" list.
 773                Otherwise, this resets the expression.
 774            dialect: the dialect used to parse the input expressions.
 775            copy: if `False`, modify this expression instance in-place.
 776            opts: other options to use to parse the input expressions.
 777
 778        Returns:
 779            The modified Join expression.
 780        """
 781        join = _apply_list_builder(
 782            *expressions,
 783            instance=self,
 784            arg="using",
 785            append=append,
 786            dialect=dialect,
 787            copy=copy,
 788            **opts,
 789        )
 790
 791        if join.kind == "CROSS":
 792            join.set("kind", None)
 793
 794        return join
 795
 796
 797class Lateral(Expression, UDTF):
 798    arg_types = {
 799        "this": True,
 800        "view": False,
 801        "outer": False,
 802        "alias": False,
 803        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
 804        "ordinality": False,
 805    }
 806
 807
 808class TableFromRows(Expression, UDTF):
 809    arg_types = {
 810        "this": True,
 811        "alias": False,
 812        "joins": False,
 813        "pivots": False,
 814        "sample": False,
 815    }
 816
 817
 818class MatchRecognizeMeasure(Expression):
 819    arg_types = {
 820        "this": True,
 821        "window_frame": False,
 822    }
 823
 824
 825class MatchRecognize(Expression):
 826    arg_types = {
 827        "partition_by": False,
 828        "order": False,
 829        "measures": False,
 830        "rows": False,
 831        "after": False,
 832        "pattern": False,
 833        "define": False,
 834        "alias": False,
 835    }
 836
 837
 838class Final(Expression):
 839    pass
 840
 841
 842class Offset(Expression):
 843    arg_types = {"this": False, "expression": True, "expressions": False}
 844
 845
 846class Order(Expression):
 847    arg_types = {"this": False, "expressions": True, "siblings": False}
 848
 849
 850class WithFill(Expression):
 851    arg_types = {
 852        "from_": False,
 853        "to": False,
 854        "step": False,
 855        "interpolate": False,
 856    }
 857
 858
 859class SkipJSONColumn(Expression):
 860    arg_types = {"regexp": False, "expression": True}
 861
 862
 863class Cluster(Expression):
 864    arg_types = {"expressions": True}
 865
 866
 867class Distribute(Order):
 868    pass
 869
 870
 871class Sort(Order):
 872    pass
 873
 874
 875class Qualify(Expression):
 876    pass
 877
 878
 879class InputOutputFormat(Expression):
 880    arg_types = {"input_format": False, "output_format": False}
 881
 882
 883class Return(Expression):
 884    pass
 885
 886
 887class Tuple(Expression):
 888    arg_types = {"expressions": False}
 889
 890    def isin(
 891        self,
 892        *expressions: t.Any,
 893        query: ExpOrStr | None = None,
 894        unnest: ExpOrStr | None | list[ExpOrStr] | tuple[ExpOrStr, ...] = None,
 895        copy: bool = True,
 896        **opts: Unpack[ParserArgs],
 897    ) -> In:
 898        return In(
 899            this=maybe_copy(self, copy),
 900            expressions=[convert(e, copy=copy) for e in expressions],
 901            query=maybe_parse(query, copy=copy, **opts) if query else None,
 902            unnest=(
 903                Unnest(
 904                    expressions=[
 905                        maybe_parse(e, copy=copy, **opts)
 906                        for e in t.cast(list[ExpOrStr], ensure_list(unnest))
 907                    ]
 908                )
 909                if unnest
 910                else None
 911            ),
 912        )
 913
 914
 915class QueryOption(Expression):
 916    arg_types = {"this": True, "expression": False}
 917
 918
 919# FOR { XML | JSON } query modifier; `kind` is the discriminant ("XML" or "JSON").
 920class ForClause(Expression):
 921    arg_types = {"kind": True, "expressions": False}
 922
 923
 924class WithTableHint(Expression):
 925    arg_types = {"expressions": True}
 926
 927
 928class IndexTableHint(Expression):
 929    arg_types = {"this": True, "expressions": False, "target": False}
 930
 931
 932class HistoricalData(Expression):
 933    arg_types = {"this": True, "kind": True, "expression": True}
 934
 935
 936class Put(Expression):
 937    arg_types = {"this": True, "target": True, "properties": False}
 938
 939
 940class Get(Expression):
 941    arg_types = {"this": True, "target": True, "properties": False}
 942
 943
 944class Table(Expression, Selectable):
 945    arg_types = {
 946        "this": False,
 947        "alias": False,
 948        "db": False,
 949        "catalog": False,
 950        "laterals": False,
 951        "joins": False,
 952        "pivots": False,
 953        "hints": False,
 954        "system_time": False,
 955        "version": False,
 956        "format": False,
 957        "pattern": False,
 958        "ordinality": False,
 959        "when": False,
 960        "only": False,
 961        "partition": False,
 962        "changes": False,
 963        "rows_from": False,
 964        "sample": False,
 965        "indexed": False,
 966    }
 967
 968    @property
 969    def name(self) -> str:
 970        if not self.this or isinstance(self.this, Func):
 971            return ""
 972        return self.this.name
 973
 974    @property
 975    def db(self) -> str:
 976        return self.text("db")
 977
 978    @property
 979    def catalog(self) -> str:
 980        return self.text("catalog")
 981
 982    @property
 983    def selects(self) -> list[Expr]:
 984        return []
 985
 986    @property
 987    def named_selects(self) -> list[str]:
 988        return []
 989
 990    @property
 991    def parts(self) -> list[Expr]:
 992        """Return the parts of a table in order catalog, db, table."""
 993        parts: list[Expr] = []
 994
 995        for arg in ("catalog", "db", "this"):
 996            part = self.args.get(arg)
 997
 998            if isinstance(part, Dot):
 999                parts.extend(part.flatten())
1000            elif isinstance(part, Expr):
1001                parts.append(part)
1002
1003        return parts
1004
1005    def to_column(self, copy: bool = True) -> Expr:
1006        parts = self.parts
1007        last_part = parts[-1]
1008
1009        if isinstance(last_part, Identifier):
1010            col: Expr = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
1011        else:
1012            # This branch will be reached if a function or array is wrapped in a `Table`
1013            col = last_part
1014
1015        alias = self.args.get("alias")
1016        if alias:
1017            col = alias_(col, alias.this, copy=copy)
1018
1019        return col
1020
1021
1022class SetOperation(Expression, Query):
1023    arg_types = {
1024        "with_": False,
1025        "this": True,
1026        "expression": True,
1027        "distinct": False,
1028        "by_name": False,
1029        "side": False,
1030        "kind": False,
1031        "on": False,
1032        **QUERY_MODIFIERS,
1033    }
1034
1035    def select(
1036        self: S,
1037        *expressions: ExpOrStr | None,
1038        append: bool = True,
1039        dialect: DialectType = None,
1040        copy: bool = True,
1041        **opts: Unpack[ParserNoDialectArgs],
1042    ) -> S:
1043        this = maybe_copy(self, copy)
1044        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1045        this.expression.unnest().select(
1046            *expressions, append=append, dialect=dialect, copy=False, **opts
1047        )
1048        return this
1049
1050    @property
1051    def named_selects(self) -> list[str]:
1052        expr: Expr = self
1053        while isinstance(expr, SetOperation):
1054            if expr.args.get("by_name"):
1055                left = t.cast(Selectable, expr.this.unnest()).named_selects
1056                right = t.cast(Selectable, expr.expression.unnest()).named_selects
1057                return list(dict.fromkeys(left + right))
1058
1059            expr = expr.this.unnest()
1060        return _named_selects(expr)
1061
1062    @property
1063    def is_star(self) -> bool:
1064        return self.this.is_star or self.expression.is_star
1065
1066    @property
1067    def selects(self) -> list[Expr]:
1068        expr: Expr = self
1069        while isinstance(expr, SetOperation):
1070            expr = expr.this.unnest()
1071        return getattr(expr, "selects", [])
1072
1073    @property
1074    def left(self) -> Query:
1075        return self.this
1076
1077    @property
1078    def right(self) -> Query:
1079        return self.expression
1080
1081    @property
1082    def kind(self) -> str:
1083        return self.text("kind").upper()
1084
1085    @property
1086    def side(self) -> str:
1087        return self.text("side").upper()
1088
1089
1090class Union(SetOperation):
1091    pass
1092
1093
1094class Except(SetOperation):
1095    pass
1096
1097
1098class Intersect(SetOperation):
1099    pass
1100
1101
1102class Values(Expression, UDTF):
1103    arg_types = {
1104        "expressions": True,
1105        "alias": False,
1106        "order": False,
1107        "limit": False,
1108        "offset": False,
1109    }
1110
1111
1112class Version(Expression):
1113    """
1114    Time travel, iceberg, bigquery etc
1115    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
1116    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
1117    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
1118    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
1119    this is either TIMESTAMP or VERSION
1120    kind is ("AS OF", "BETWEEN")
1121    """
1122
1123    arg_types = {"this": True, "kind": True, "expression": False}
1124
1125
1126class Schema(Expression):
1127    arg_types = {"this": False, "expressions": False}
1128
1129
1130class Lock(Expression):
1131    arg_types = {"update": True, "expressions": False, "wait": False, "key": False}
1132
1133
1134class Select(Expression, Query):
1135    arg_types = {
1136        "with_": False,
1137        "kind": False,
1138        "expressions": False,
1139        "hint": False,
1140        "distinct": False,
1141        "into": False,
1142        "from_": False,
1143        "operation_modifiers": False,
1144        "exclude": False,
1145        **QUERY_MODIFIERS,
1146    }
1147
1148    def from_(
1149        self,
1150        expression: ExpOrStr,
1151        dialect: DialectType = None,
1152        copy: bool = True,
1153        **opts: Unpack[ParserNoDialectArgs],
1154    ) -> Select:
1155        """
1156        Set the FROM expression.
1157
1158        Example:
1159            >>> Select().from_("tbl").select("x").sql()
1160            'SELECT x FROM tbl'
1161
1162        Args:
1163            expression : the SQL code strings to parse.
1164                If a `From` instance is passed, this is used as-is.
1165                If another `Expr` instance is passed, it will be wrapped in a `From`.
1166            dialect: the dialect used to parse the input expression.
1167            copy: if `False`, modify this expression instance in-place.
1168            opts: other options to use to parse the input expressions.
1169
1170        Returns:
1171            The modified Select expression.
1172        """
1173        return _apply_builder(
1174            expression=expression,
1175            instance=self,
1176            arg="from_",
1177            into=From,
1178            prefix="FROM",
1179            dialect=dialect,
1180            copy=copy,
1181            **opts,
1182        )
1183
1184    def group_by(
1185        self,
1186        *expressions: ExpOrStr | None,
1187        append: bool = True,
1188        dialect: DialectType = None,
1189        copy: bool = True,
1190        **opts: Unpack[ParserNoDialectArgs],
1191    ) -> Select:
1192        """
1193        Set the GROUP BY expression.
1194
1195        Example:
1196            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1197            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1198
1199        Args:
1200            *expressions: the SQL code strings to parse.
1201                If a `Group` instance is passed, this is used as-is.
1202                If another `Expr` instance is passed, it will be wrapped in a `Group`.
1203                If nothing is passed in then a group by is not applied to the expression
1204            append: if `True`, add to any existing expressions.
1205                Otherwise, this flattens all the `Group` expression into a single expression.
1206            dialect: the dialect used to parse the input expression.
1207            copy: if `False`, modify this expression instance in-place.
1208            opts: other options to use to parse the input expressions.
1209
1210        Returns:
1211            The modified Select expression.
1212        """
1213        if not expressions:
1214            return self if not copy else self.copy()
1215
1216        return _apply_child_list_builder(
1217            *expressions,
1218            instance=self,
1219            arg="group",
1220            append=append,
1221            copy=copy,
1222            prefix="GROUP BY",
1223            into=Group,
1224            dialect=dialect,
1225            **opts,
1226        )
1227
1228    def sort_by(
1229        self,
1230        *expressions: ExpOrStr | None,
1231        append: bool = True,
1232        dialect: DialectType = None,
1233        copy: bool = True,
1234        **opts: Unpack[ParserNoDialectArgs],
1235    ) -> Select:
1236        """
1237        Set the SORT BY expression.
1238
1239        Example:
1240            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
1241            'SELECT x FROM tbl SORT BY x DESC'
1242
1243        Args:
1244            *expressions: the SQL code strings to parse.
1245                If a `Group` instance is passed, this is used as-is.
1246                If another `Expr` instance is passed, it will be wrapped in a `SORT`.
1247            append: if `True`, add to any existing expressions.
1248                Otherwise, this flattens all the `Order` expression into a single expression.
1249            dialect: the dialect used to parse the input expression.
1250            copy: if `False`, modify this expression instance in-place.
1251            opts: other options to use to parse the input expressions.
1252
1253        Returns:
1254            The modified Select expression.
1255        """
1256        return _apply_child_list_builder(
1257            *expressions,
1258            instance=self,
1259            arg="sort",
1260            append=append,
1261            copy=copy,
1262            prefix="SORT BY",
1263            into=Sort,
1264            dialect=dialect,
1265            **opts,
1266        )
1267
1268    def cluster_by(
1269        self,
1270        *expressions: ExpOrStr | None,
1271        append: bool = True,
1272        dialect: DialectType = None,
1273        copy: bool = True,
1274        **opts: Unpack[ParserNoDialectArgs],
1275    ) -> Select:
1276        """
1277        Set the CLUSTER BY expression.
1278
1279        Example:
1280            >>> Select().from_("tbl").select("x").cluster_by("x").sql(dialect="hive")
1281            'SELECT x FROM tbl CLUSTER BY x'
1282
1283        Args:
1284            *expressions: the SQL code strings to parse.
1285                If a `Group` instance is passed, this is used as-is.
1286                If another `Expr` instance is passed, it will be wrapped in a `Cluster`.
1287            append: if `True`, add to any existing expressions.
1288                Otherwise, this flattens all the `Order` expression into a single expression.
1289            dialect: the dialect used to parse the input expression.
1290            copy: if `False`, modify this expression instance in-place.
1291            opts: other options to use to parse the input expressions.
1292
1293        Returns:
1294            The modified Select expression.
1295        """
1296        return _apply_child_list_builder(
1297            *expressions,
1298            instance=self,
1299            arg="cluster",
1300            append=append,
1301            copy=copy,
1302            prefix="CLUSTER BY",
1303            into=Cluster,
1304            dialect=dialect,
1305            **opts,
1306        )
1307
1308    def select(
1309        self,
1310        *expressions: ExpOrStr | None,
1311        append: bool = True,
1312        dialect: DialectType = None,
1313        copy: bool = True,
1314        **opts: Unpack[ParserNoDialectArgs],
1315    ) -> Select:
1316        return _apply_list_builder(
1317            *expressions,
1318            instance=self,
1319            arg="expressions",
1320            append=append,
1321            dialect=dialect,
1322            into=Expr,
1323            copy=copy,
1324            **opts,
1325        )
1326
1327    def lateral(
1328        self,
1329        *expressions: ExpOrStr | None,
1330        append: bool = True,
1331        dialect: DialectType = None,
1332        copy: bool = True,
1333        **opts: Unpack[ParserNoDialectArgs],
1334    ) -> Select:
1335        """
1336        Append to or set the LATERAL expressions.
1337
1338        Example:
1339            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
1340            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
1341
1342        Args:
1343            *expressions: the SQL code strings to parse.
1344                If an `Expr` instance is passed, it will be used as-is.
1345            append: if `True`, add to any existing expressions.
1346                Otherwise, this resets the expressions.
1347            dialect: the dialect used to parse the input expressions.
1348            copy: if `False`, modify this expression instance in-place.
1349            opts: other options to use to parse the input expressions.
1350
1351        Returns:
1352            The modified Select expression.
1353        """
1354        return _apply_list_builder(
1355            *expressions,
1356            instance=self,
1357            arg="laterals",
1358            append=append,
1359            into=Lateral,
1360            prefix="LATERAL VIEW",
1361            dialect=dialect,
1362            copy=copy,
1363            **opts,
1364        )
1365
1366    def join(
1367        self,
1368        expression: ExpOrStr,
1369        on: ExpOrStr | list[ExpOrStr] | tuple[ExpOrStr, ...] | None = None,
1370        using: ExpOrStr | list[ExpOrStr] | tuple[ExpOrStr, ...] | None = None,
1371        append: bool = True,
1372        join_type: str | None = None,
1373        join_alias: Identifier | str | None = None,
1374        dialect: DialectType = None,
1375        copy: bool = True,
1376        **opts: Unpack[ParserNoDialectArgs],
1377    ) -> Select:
1378        """
1379        Append to or set the JOIN expressions.
1380
1381        Example:
1382            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
1383            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
1384
1385            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
1386            'SELECT 1 FROM a JOIN b USING (x, y, z)'
1387
1388            Use `join_type` to change the type of join:
1389
1390            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
1391            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
1392
1393        Args:
1394            expression: the SQL code string to parse.
1395                If an `Expr` instance is passed, it will be used as-is.
1396            on: optionally specify the join "on" criteria as a SQL string.
1397                If an `Expr` instance is passed, it will be used as-is.
1398            using: optionally specify the join "using" criteria as a SQL string.
1399                If an `Expr` instance is passed, it will be used as-is.
1400            append: if `True`, add to any existing expressions.
1401                Otherwise, this resets the expressions.
1402            join_type: if set, alter the parsed join type.
1403            join_alias: an optional alias for the joined source.
1404            dialect: the dialect used to parse the input expressions.
1405            copy: if `False`, modify this expression instance in-place.
1406            opts: other options to use to parse the input expressions.
1407
1408        Returns:
1409            Select: the modified expression.
1410        """
1411        parse_args: ParserArgs = {"dialect": dialect, **opts}
1412        try:
1413            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
1414        except ParseError:
1415            expression = maybe_parse(expression, into=(Join, Expr), **parse_args)
1416
1417        join = expression if isinstance(expression, Join) else Join(this=expression)
1418
1419        if isinstance(join.this, Select):
1420            join.this.replace(join.this.subquery())
1421
1422        if join_type:
1423            new_join: Join = maybe_parse(f"FROM _ {join_type} JOIN _", **parse_args).find(Join)
1424            method = new_join.method
1425            side = new_join.side
1426            kind = new_join.kind
1427
1428            if method:
1429                join.set("method", method)
1430            if side:
1431                join.set("side", side)
1432            if kind:
1433                join.set("kind", kind)
1434
1435        if on:
1436            on_exprs: list[ExpOrStr] = ensure_list(on)
1437            on = and_(*on_exprs, dialect=dialect, copy=copy, **opts)
1438            join.set("on", on)
1439
1440        if using:
1441            using_exprs: list[ExpOrStr] = ensure_list(using)
1442            join = _apply_list_builder(
1443                *using_exprs,
1444                instance=join,
1445                arg="using",
1446                append=append,
1447                copy=copy,
1448                into=Identifier,
1449                **opts,
1450            )
1451
1452        if join_alias:
1453            join.set("this", alias_(join.this, join_alias, table=True))
1454
1455        return _apply_list_builder(
1456            join,
1457            instance=self,
1458            arg="joins",
1459            append=append,
1460            copy=copy,
1461            **opts,
1462        )
1463
1464    def having(
1465        self,
1466        *expressions: ExpOrStr | None,
1467        append: bool = True,
1468        dialect: DialectType = None,
1469        copy: bool = True,
1470        **opts: Unpack[ParserNoDialectArgs],
1471    ) -> Select:
1472        """
1473        Append to or set the HAVING expressions.
1474
1475        Example:
1476            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
1477            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
1478
1479        Args:
1480            *expressions: the SQL code strings to parse.
1481                If an `Expr` instance is passed, it will be used as-is.
1482                Multiple expressions are combined with an AND operator.
1483            append: if `True`, AND the new expressions to any existing expression.
1484                Otherwise, this resets the expression.
1485            dialect: the dialect used to parse the input expressions.
1486            copy: if `False`, modify this expression instance in-place.
1487            opts: other options to use to parse the input expressions.
1488
1489        Returns:
1490            The modified Select expression.
1491        """
1492        return _apply_conjunction_builder(
1493            *expressions,
1494            instance=self,
1495            arg="having",
1496            append=append,
1497            into=Having,
1498            dialect=dialect,
1499            copy=copy,
1500            **opts,
1501        )
1502
1503    def window(
1504        self,
1505        *expressions: ExpOrStr | None,
1506        append: bool = True,
1507        dialect: DialectType = None,
1508        copy: bool = True,
1509        **opts: Unpack[ParserNoDialectArgs],
1510    ) -> Select:
1511        return _apply_list_builder(
1512            *expressions,
1513            instance=self,
1514            arg="windows",
1515            append=append,
1516            into=Window,
1517            dialect=dialect,
1518            copy=copy,
1519            **opts,
1520        )
1521
1522    def qualify(
1523        self,
1524        *expressions: ExpOrStr | None,
1525        append: bool = True,
1526        dialect: DialectType = None,
1527        copy: bool = True,
1528        **opts: Unpack[ParserNoDialectArgs],
1529    ) -> Select:
1530        return _apply_conjunction_builder(
1531            *expressions,
1532            instance=self,
1533            arg="qualify",
1534            append=append,
1535            into=Qualify,
1536            dialect=dialect,
1537            copy=copy,
1538            **opts,
1539        )
1540
1541    def distinct(self, *ons: ExpOrStr | None, distinct: bool = True, copy: bool = True) -> Select:
1542        """
1543        Set the OFFSET expression.
1544
1545        Example:
1546            >>> Select().from_("tbl").select("x").distinct().sql()
1547            'SELECT DISTINCT x FROM tbl'
1548
1549        Args:
1550            ons: the expressions to distinct on
1551            distinct: whether the Select should be distinct
1552            copy: if `False`, modify this expression instance in-place.
1553
1554        Returns:
1555            Select: the modified expression.
1556        """
1557        instance = maybe_copy(self, copy)
1558        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
1559        instance.set("distinct", Distinct(on=on) if distinct else None)
1560        return instance
1561
1562    def ctas(
1563        self,
1564        table: ExpOrStr,
1565        properties: dict | None = None,
1566        dialect: DialectType = None,
1567        copy: bool = True,
1568        **opts: Unpack[ParserNoDialectArgs],
1569    ) -> Create:
1570        """
1571        Convert this expression to a CREATE TABLE AS statement.
1572
1573        Example:
1574            >>> Select().select("*").from_("tbl").ctas("x").sql()
1575            'CREATE TABLE x AS SELECT * FROM tbl'
1576
1577        Args:
1578            table: the SQL code string to parse as the table name.
1579                If another `Expr` instance is passed, it will be used as-is.
1580            properties: an optional mapping of table properties
1581            dialect: the dialect used to parse the input table.
1582            copy: if `False`, modify this expression instance in-place.
1583            opts: other options to use to parse the input table.
1584
1585        Returns:
1586            The new Create expression.
1587        """
1588        instance = maybe_copy(self, copy)
1589        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
1590
1591        properties_expression = None
1592        if properties:
1593            from sqlglot.expressions.properties import Properties as _Properties
1594
1595            properties_expression = _Properties.from_dict(properties)
1596
1597        from sqlglot.expressions.ddl import Create as _Create
1598
1599        return _Create(
1600            this=table_expression,
1601            kind="TABLE",
1602            expression=instance,
1603            properties=properties_expression,
1604        )
1605
1606    def lock(self, update: bool = True, copy: bool = True) -> Select:
1607        """
1608        Set the locking read mode for this expression.
1609
1610        Examples:
1611            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
1612            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
1613
1614            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
1615            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
1616
1617        Args:
1618            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
1619            copy: if `False`, modify this expression instance in-place.
1620
1621        Returns:
1622            The modified expression.
1623        """
1624        inst = maybe_copy(self, copy)
1625        inst.set("locks", [Lock(update=update)])
1626
1627        return inst
1628
1629    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
1630        """
1631        Set hints for this expression.
1632
1633        Examples:
1634            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
1635            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
1636
1637        Args:
1638            hints: The SQL code strings to parse as the hints.
1639                If an `Expr` instance is passed, it will be used as-is.
1640            dialect: The dialect used to parse the hints.
1641            copy: If `False`, modify this expression instance in-place.
1642
1643        Returns:
1644            The modified expression.
1645        """
1646        inst = maybe_copy(self, copy)
1647        inst.set(
1648            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
1649        )
1650
1651        return inst
1652
1653    @property
1654    def named_selects(self) -> list[str]:
1655        selects = []
1656
1657        for e in self.expressions:
1658            if e.alias_or_name:
1659                selects.append(e.output_name)
1660            elif isinstance(e, Aliases):
1661                selects.extend([a.name for a in e.aliases])
1662        return selects
1663
1664    @property
1665    def is_star(self) -> bool:
1666        return any(expression.is_star for expression in self.expressions)
1667
1668    @property
1669    def selects(self) -> list[Expr]:
1670        return self.expressions
1671
1672
1673class Subquery(Expression, DerivedTable, Query):
1674    is_subquery: t.ClassVar[bool] = True
1675    arg_types = {
1676        "this": True,
1677        "alias": False,
1678        "with_": False,
1679        **QUERY_MODIFIERS,
1680    }
1681
1682    def unnest(self) -> Expr:
1683        """Returns the first non subquery."""
1684        expression: Expr = self
1685        while isinstance(expression, Subquery):
1686            expression = expression.this
1687        return expression
1688
1689    def unwrap(self) -> Subquery:
1690        expression = self
1691        while expression.same_parent and expression.is_wrapper:
1692            expression = t.cast(Subquery, expression.parent)
1693        return expression
1694
1695    def select(
1696        self,
1697        *expressions: ExpOrStr | None,
1698        append: bool = True,
1699        dialect: DialectType = None,
1700        copy: bool = True,
1701        **opts: Unpack[ParserNoDialectArgs],
1702    ) -> Subquery:
1703        this = maybe_copy(self, copy)
1704        inner = this.unnest()
1705        if hasattr(inner, "select"):
1706            inner.select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1707        return this
1708
1709    @property
1710    def is_wrapper(self) -> bool:
1711        """
1712        Whether this Subquery acts as a simple wrapper around another expression.
1713
1714        SELECT * FROM (((SELECT * FROM t)))
1715                      ^
1716                      This corresponds to a "wrapper" Subquery node
1717        """
1718        return all(v is None for k, v in self.args.items() if k != "this")
1719
1720    @property
1721    def is_star(self) -> bool:
1722        return self.this.is_star
1723
1724    @property
1725    def output_name(self) -> str:
1726        return self.alias
1727
1728
1729class TableSample(Expression):
1730    arg_types = {
1731        "expressions": False,
1732        "method": False,
1733        "bucket_numerator": False,
1734        "bucket_denominator": False,
1735        "bucket_field": False,
1736        "percent": False,
1737        "rows": False,
1738        "size": False,
1739        "seed": False,
1740    }
1741
1742
1743class Tag(Expression):
1744    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
1745
1746    arg_types = {
1747        "this": False,
1748        "prefix": False,
1749        "postfix": False,
1750    }
1751
1752
1753class Pivot(Expression):
1754    arg_types = {
1755        "this": False,
1756        "alias": False,
1757        "expressions": False,
1758        "fields": False,
1759        "unpivot": False,
1760        "using": False,
1761        "group": False,
1762        "columns": False,
1763        "include_nulls": False,
1764        "default_on_null": False,
1765        "into": False,
1766        "with_": False,
1767        "identify_pivot_strings": False,
1768        "prefixed_pivot_columns": False,
1769        "pivot_column_naming": False,
1770        "value_columns_first": False,
1771    }
1772
1773    @property
1774    def unpivot(self) -> bool:
1775        return bool(self.args.get("unpivot"))
1776
1777    @property
1778    def fields(self) -> list[Expr]:
1779        return self.args.get("fields", [])
1780
1781    def output_columns(self, pre_pivot_columns: t.Iterable[str]) -> dict[str, str]:
1782        """
1783        Returns an ordered map of post-rename output column name -> pre-rename
1784        source-side name, in the order the (UN)PIVOT produces them.
1785
1786        For callers that just want the names, iterate the dict (or call .keys()):
1787            >>> from sqlglot import parse_one, exp
1788            >>> piv = parse_one("SELECT * FROM t UNPIVOT(val FOR name IN (a, b))").find(exp.Pivot)
1789            >>> list(piv.output_columns(["a", "b", "c"]))
1790            ['c', 'name', 'val']
1791
1792        AST shape:
1793            PIVOT(SUM(val) FOR name IN ('a', 'b')):
1794                expressions: aggregate(s), e.g. [Sum(this=Column(val))]
1795                fields:      [In(this=Column(name), expressions=[Literal('a'), Literal('b')])]
1796                columns:     optional explicit output identifiers (e.g. set by Snowflake)
1797
1798            UNPIVOT(val FOR name IN (a, b)):
1799                expressions: value Identifier(s), or Tuple(Identifiers) for multi-value
1800                fields:      [In(this=Identifier(name), expressions=[Column(a), Column(b)])]
1801                             For literal-aliased entries (`a AS 'x'`) the IN expressions
1802                             are wrapped in PivotAlias(this=Column, alias=Literal).
1803
1804        Args:
1805            pre_pivot_columns: Columns visible to the operator before it runs
1806                (e.g. the source table or subquery's projections).
1807        """
1808        if self.unpivot:
1809            excluded: set[str] = set()
1810            name_columns: list[Identifier] = []
1811            for field in self.fields:
1812                if not isinstance(field, In):
1813                    continue
1814                if isinstance(field.this, Identifier):
1815                    name_columns.append(field.this)
1816                for e in field.expressions:
1817                    excluded.update(c.output_name for c in e.find_all(Column))
1818            value_columns = [
1819                ident
1820                for e in self.expressions
1821                for ident in (e.expressions if isinstance(e, Tuple) else [e])
1822                if isinstance(ident, Identifier)
1823            ]
1824            # T-SQL emits the value column(s) ahead of the name column, everyone else emits them after it
1825            ordered = (
1826                value_columns + name_columns
1827                if self.args.get("value_columns_first")
1828                else name_columns + value_columns
1829            )
1830            outputs = [i.name for i in ordered]
1831        else:
1832            excluded = {c.output_name for c in self.find_all(Column)}
1833            outputs = [c.output_name for c in self.args.get("columns") or []]
1834            if not outputs:
1835                outputs = [c.alias_or_name for c in self.expressions]
1836
1837        if not excluded or not outputs:
1838            return {}
1839
1840        pre_rename = [c for c in pre_pivot_columns if c not in excluded] + outputs
1841
1842        alias = self.args.get("alias")
1843        renames = alias.args.get("columns") if alias else None
1844
1845        # `PIVOT(...) AS alias(c1, c2, ...)` renames the operator's output columns
1846        # positionally from the front (DuckDB, Snowflake): the user's names cover
1847        # the leading N output columns, remaining columns keep their auto names.
1848        if renames:
1849            rename_names = [r.name for r in renames]
1850            post_rename = rename_names + pre_rename[len(rename_names) :]
1851        else:
1852            post_rename = pre_rename
1853
1854        return dict(zip(post_rename, pre_rename))
1855
1856
1857class UnpivotColumns(Expression):
1858    arg_types = {"this": True, "expressions": True}
1859
1860
1861class Window(Expression, Condition):
1862    arg_types = {
1863        "this": True,
1864        "partition_by": False,
1865        "order": False,
1866        "spec": False,
1867        "alias": False,
1868        "over": False,
1869        "first": False,
1870    }
1871
1872
1873class WindowSpec(Expression):
1874    arg_types = {
1875        "kind": False,
1876        "start": False,
1877        "start_side": False,
1878        "end": False,
1879        "end_side": False,
1880        "exclude": False,
1881    }
1882
1883
1884class PreWhere(Expression):
1885    pass
1886
1887
1888class Where(Expression):
1889    pass
1890
1891
1892class Analyze(Expression):
1893    arg_types = {
1894        "kind": False,
1895        "tables": False,
1896        "options": False,
1897        "mode": False,
1898        "partition": False,
1899        "expression": False,
1900        "properties": False,
1901    }
1902
1903
1904class AnalyzeStatistics(Expression):
1905    arg_types = {
1906        "kind": True,
1907        "option": False,
1908        "this": False,
1909        "expressions": False,
1910    }
1911
1912
1913class AnalyzeHistogram(Expression):
1914    arg_types = {
1915        "this": True,
1916        "expressions": True,
1917        "expression": False,
1918        "update_options": False,
1919    }
1920
1921
1922class AnalyzeSample(Expression):
1923    arg_types = {"kind": True, "sample": True}
1924
1925
1926class AnalyzeListChainedRows(Expression):
1927    arg_types = {"expression": False}
1928
1929
1930class AnalyzeDelete(Expression):
1931    arg_types = {"kind": False}
1932
1933
1934class AnalyzeWith(Expression):
1935    arg_types = {"expressions": True}
1936
1937
1938class AnalyzeValidate(Expression):
1939    arg_types = {
1940        "kind": True,
1941        "this": False,
1942        "expression": False,
1943    }
1944
1945
1946class AnalyzeColumns(Expression):
1947    pass
1948
1949
1950class UsingData(Expression):
1951    pass
1952
1953
1954class AddPartition(Expression):
1955    arg_types = {"this": True, "exists": False, "location": False}
1956
1957
1958class AttachOption(Expression):
1959    arg_types = {"this": True, "expression": False}
1960
1961
1962class DropPartition(Expression):
1963    arg_types = {"expressions": True, "exists": False}
1964
1965
1966class ReplacePartition(Expression):
1967    arg_types = {"expression": True, "source": True}
1968
1969
1970class TranslateCharacters(Expression):
1971    arg_types = {"this": True, "expression": True, "with_error": False}
1972
1973
1974class OverflowTruncateBehavior(Expression):
1975    arg_types = {"this": False, "with_count": True}
1976
1977
1978class JSON(Expression):
1979    arg_types = {"this": False, "with_": False, "unique": False}
1980
1981
1982class JSONPath(Expression):
1983    arg_types = {"expressions": True}
1984
1985    @property
1986    def output_name(self) -> str:
1987        last_segment = self.expressions[-1].this
1988        return last_segment if isinstance(last_segment, str) else ""
1989
1990
1991class JSONPathPart(Expression):
1992    arg_types = {}
1993
1994
1995class JSONPathFilter(JSONPathPart):
1996    arg_types = {"this": True}
1997
1998
1999class JSONPathKey(JSONPathPart):
2000    arg_types = {"this": True, "quoted": False}
2001
2002
2003class JSONPathRecursive(JSONPathPart):
2004    arg_types = {"this": False}
2005
2006
2007class JSONPathRoot(JSONPathPart):
2008    pass
2009
2010
2011class JSONPathScript(JSONPathPart):
2012    arg_types = {"this": True}
2013
2014
2015class JSONPathSlice(JSONPathPart):
2016    arg_types = {"start": False, "end": False, "step": False}
2017
2018
2019class JSONPathSelector(JSONPathPart):
2020    arg_types = {"this": True}
2021
2022
2023class JSONPathSubscript(JSONPathPart):
2024    arg_types = {"this": True}
2025
2026
2027class JSONPathUnion(JSONPathPart):
2028    arg_types = {"expressions": True}
2029
2030
2031class JSONPathWildcard(JSONPathPart):
2032    pass
2033
2034
2035class FormatJson(Expression):
2036    pass
2037
2038
2039class JSONKeyValue(Expression):
2040    arg_types = {"this": True, "expression": True}
2041
2042
2043class JSONColumnDef(Expression):
2044    arg_types = {
2045        "this": False,
2046        "kind": False,
2047        "path": False,
2048        "nested_schema": False,
2049        "ordinality": False,
2050        "format_json": False,
2051    }
2052
2053
2054class JSONSchema(Expression):
2055    arg_types = {"expressions": True}
2056
2057
2058class JSONValue(Expression):
2059    arg_types = {
2060        "this": True,
2061        "path": True,
2062        "returning": False,
2063        "on_condition": False,
2064    }
2065
2066
2067class JSONValueArray(Expression, Func):
2068    arg_types = {"this": True, "expression": False}
2069
2070
2071class OpenJSONColumnDef(Expression):
2072    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
2073
2074
2075class JSONExtractQuote(Expression):
2076    arg_types = {
2077        "option": True,
2078        "scalar": False,
2079    }
2080
2081
2082class ScopeResolution(Expression):
2083    arg_types = {"this": False, "expression": True}
2084
2085
2086class Stream(Expression):
2087    pass
2088
2089
2090class ModelAttribute(Expression):
2091    arg_types = {"this": True, "expression": True}
2092
2093
2094class XMLNamespace(Expression):
2095    pass
2096
2097
2098class XMLKeyValueOption(Expression):
2099    arg_types = {"this": True, "expression": False}
2100
2101
2102class Semicolon(Expression):
2103    arg_types = {}
2104
2105
2106class TableColumn(Expression):
2107    @property
2108    def output_name(self) -> str:
2109        return self.name
2110
2111
2112class Variadic(Expression):
2113    pass
2114
2115
2116class StoredProcedure(Expression):
2117    arg_types = {"this": True, "expressions": False, "wrapped": False}
2118
2119
2120class Block(Expression):
2121    arg_types = {"expressions": True, "begin": False}
2122
2123
2124class IfBlock(Expression):
2125    arg_types = {"this": True, "true": True, "false": False}
2126
2127
2128class CaseStatement(Expression):
2129    arg_types = {"this": False, "ifs": True, "default": False}
2130
2131
2132class WhileBlock(Expression):
2133    arg_types = {"this": True, "body": True, "label": False}
2134
2135
2136class LoopBlock(Expression):
2137    arg_types = {"body": True, "label": False}
2138
2139
2140class RepeatBlock(Expression):
2141    arg_types = {"body": True, "until": True, "label": False}
2142
2143
2144class Leave(Expression):
2145    pass
2146
2147
2148class Iterate(Expression):
2149    pass
2150
2151
2152class EndStatement(Expression):
2153    arg_types = {}
2154
2155
2156# https://trino.io/docs/current/udf.html
2157class FunctionSpecification(Expression):
2158    arg_types = {
2159        "this": True,
2160        "characteristics": False,
2161        "properties": False,
2162        "expression": True,
2163    }
2164
2165
2166UNWRAPPED_QUERIES = (Select, SetOperation)
2167
2168
2169def union(
2170    *expressions: ExpOrStr,
2171    distinct: bool = True,
2172    dialect: DialectType = None,
2173    copy: bool = True,
2174    **opts: Unpack[ParserNoDialectArgs],
2175) -> Union:
2176    """
2177    Initializes a syntax tree for the `UNION` operation.
2178
2179    Example:
2180        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
2181        'SELECT * FROM foo UNION SELECT * FROM bla'
2182
2183    Args:
2184        expressions: the SQL code strings, corresponding to the `UNION`'s operands.
2185            If `Expr` instances are passed, they will be used as-is.
2186        distinct: set the DISTINCT flag if and only if this is true.
2187        dialect: the dialect used to parse the input expression.
2188        copy: whether to copy the expression.
2189        opts: other options to use to parse the input expressions.
2190
2191    Returns:
2192        The new Union instance.
2193    """
2194    assert len(expressions) >= 2, "At least two expressions are required by `union`."
2195    return _apply_set_operation(
2196        *expressions, set_operation=Union, distinct=distinct, dialect=dialect, copy=copy, **opts
2197    )
2198
2199
2200def intersect(
2201    *expressions: ExpOrStr,
2202    distinct: bool = True,
2203    dialect: DialectType = None,
2204    copy: bool = True,
2205    **opts: Unpack[ParserNoDialectArgs],
2206) -> Intersect:
2207    """
2208    Initializes a syntax tree for the `INTERSECT` operation.
2209
2210    Example:
2211        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
2212        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
2213
2214    Args:
2215        expressions: the SQL code strings, corresponding to the `INTERSECT`'s operands.
2216            If `Expr` instances are passed, they will be used as-is.
2217        distinct: set the DISTINCT flag if and only if this is true.
2218        dialect: the dialect used to parse the input expression.
2219        copy: whether to copy the expression.
2220        opts: other options to use to parse the input expressions.
2221
2222    Returns:
2223        The new Intersect instance.
2224    """
2225    assert len(expressions) >= 2, "At least two expressions are required by `intersect`."
2226    return _apply_set_operation(
2227        *expressions, set_operation=Intersect, distinct=distinct, dialect=dialect, copy=copy, **opts
2228    )
2229
2230
2231def except_(
2232    *expressions: ExpOrStr,
2233    distinct: bool = True,
2234    dialect: DialectType = None,
2235    copy: bool = True,
2236    **opts: Unpack[ParserNoDialectArgs],
2237) -> Except:
2238    """
2239    Initializes a syntax tree for the `EXCEPT` operation.
2240
2241    Example:
2242        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
2243        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
2244
2245    Args:
2246        expressions: the SQL code strings, corresponding to the `EXCEPT`'s operands.
2247            If `Expr` instances are passed, they will be used as-is.
2248        distinct: set the DISTINCT flag if and only if this is true.
2249        dialect: the dialect used to parse the input expression.
2250        copy: whether to copy the expression.
2251        opts: other options to use to parse the input expressions.
2252
2253    Returns:
2254        The new Except instance.
2255    """
2256    assert len(expressions) >= 2, "At least two expressions are required by `except_`."
2257    return _apply_set_operation(
2258        *expressions, set_operation=Except, distinct=distinct, dialect=dialect, copy=copy, **opts
2259    )