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": True, "recursive": False, "search": 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        "cube": False,
 628        "rollup": False,
 629        "totals": False,
 630        "all": False,
 631    }
 632
 633
 634class Cube(Expression):
 635    arg_types = {"expressions": False}
 636
 637
 638class Rollup(Expression):
 639    arg_types = {"expressions": False}
 640
 641
 642class GroupingSets(Expression):
 643    arg_types = {"expressions": True}
 644
 645
 646class Lambda(Expression):
 647    arg_types = {"this": True, "expressions": True, "colon": False}
 648
 649
 650class Limit(Expression):
 651    arg_types = {
 652        "this": False,
 653        "expression": True,
 654        "offset": False,
 655        "limit_options": False,
 656        "expressions": False,
 657    }
 658
 659
 660class LimitOptions(Expression):
 661    arg_types = {
 662        "percent": False,
 663        "rows": False,
 664        "with_ties": False,
 665    }
 666
 667
 668class Join(Expression):
 669    arg_types = {
 670        "this": True,
 671        "on": False,
 672        "side": False,
 673        "kind": False,
 674        "using": False,
 675        "method": False,
 676        "global_": False,
 677        "hint": False,
 678        "match_condition": False,  # Snowflake
 679        "directed": False,  # Snowflake
 680        "expressions": False,
 681        "pivots": False,
 682    }
 683
 684    @property
 685    def method(self) -> str:
 686        return self.text("method").upper()
 687
 688    @property
 689    def kind(self) -> str:
 690        return self.text("kind").upper()
 691
 692    @property
 693    def side(self) -> str:
 694        return self.text("side").upper()
 695
 696    @property
 697    def hint(self) -> str:
 698        return self.text("hint").upper()
 699
 700    @property
 701    def alias_or_name(self) -> str:
 702        return self.this.alias_or_name
 703
 704    @property
 705    def is_semi_or_anti_join(self) -> bool:
 706        return self.kind in ("SEMI", "ANTI")
 707
 708    def on(
 709        self,
 710        *expressions: ExpOrStr | None,
 711        append: bool = True,
 712        dialect: DialectType = None,
 713        copy: bool = True,
 714        **opts: Unpack[ParserNoDialectArgs],
 715    ) -> Join:
 716        """
 717        Append to or set the ON expressions.
 718
 719        Example:
 720            >>> import sqlglot
 721            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
 722            'JOIN x ON y = 1'
 723
 724        Args:
 725            *expressions: the SQL code strings to parse.
 726                If an `Expr` instance is passed, it will be used as-is.
 727                Multiple expressions are combined with an AND operator.
 728            append: if `True`, AND the new expressions to any existing expression.
 729                Otherwise, this resets the expression.
 730            dialect: the dialect used to parse the input expressions.
 731            copy: if `False`, modify this expression instance in-place.
 732            opts: other options to use to parse the input expressions.
 733
 734        Returns:
 735            The modified Join expression.
 736        """
 737        join = _apply_conjunction_builder(
 738            *expressions,
 739            instance=self,
 740            arg="on",
 741            append=append,
 742            dialect=dialect,
 743            copy=copy,
 744            **opts,
 745        )
 746
 747        if join.kind == "CROSS":
 748            join.set("kind", None)
 749
 750        return join
 751
 752    def using(
 753        self,
 754        *expressions: ExpOrStr | None,
 755        append: bool = True,
 756        dialect: DialectType = None,
 757        copy: bool = True,
 758        **opts: Unpack[ParserNoDialectArgs],
 759    ) -> Join:
 760        """
 761        Append to or set the USING expressions.
 762
 763        Example:
 764            >>> import sqlglot
 765            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
 766            'JOIN x USING (foo, bla)'
 767
 768        Args:
 769            *expressions: the SQL code strings to parse.
 770                If an `Expr` instance is passed, it will be used as-is.
 771            append: if `True`, concatenate the new expressions to the existing "using" list.
 772                Otherwise, this resets the expression.
 773            dialect: the dialect used to parse the input expressions.
 774            copy: if `False`, modify this expression instance in-place.
 775            opts: other options to use to parse the input expressions.
 776
 777        Returns:
 778            The modified Join expression.
 779        """
 780        join = _apply_list_builder(
 781            *expressions,
 782            instance=self,
 783            arg="using",
 784            append=append,
 785            dialect=dialect,
 786            copy=copy,
 787            **opts,
 788        )
 789
 790        if join.kind == "CROSS":
 791            join.set("kind", None)
 792
 793        return join
 794
 795
 796class Lateral(Expression, UDTF):
 797    arg_types = {
 798        "this": True,
 799        "view": False,
 800        "outer": False,
 801        "alias": False,
 802        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
 803        "ordinality": False,
 804    }
 805
 806
 807class TableFromRows(Expression, UDTF):
 808    arg_types = {
 809        "this": True,
 810        "alias": False,
 811        "joins": False,
 812        "pivots": False,
 813        "sample": False,
 814    }
 815
 816
 817class MatchRecognizeMeasure(Expression):
 818    arg_types = {
 819        "this": True,
 820        "window_frame": False,
 821    }
 822
 823
 824class MatchRecognize(Expression):
 825    arg_types = {
 826        "partition_by": False,
 827        "order": False,
 828        "measures": False,
 829        "rows": False,
 830        "after": False,
 831        "pattern": False,
 832        "define": False,
 833        "alias": False,
 834    }
 835
 836
 837class Final(Expression):
 838    pass
 839
 840
 841class Offset(Expression):
 842    arg_types = {"this": False, "expression": True, "expressions": False}
 843
 844
 845class Order(Expression):
 846    arg_types = {"this": False, "expressions": True, "siblings": False}
 847
 848
 849class WithFill(Expression):
 850    arg_types = {
 851        "from_": False,
 852        "to": False,
 853        "step": False,
 854        "interpolate": False,
 855    }
 856
 857
 858class SkipJSONColumn(Expression):
 859    arg_types = {"regexp": False, "expression": True}
 860
 861
 862class Cluster(Expression):
 863    arg_types = {"expressions": True}
 864
 865
 866class Distribute(Order):
 867    pass
 868
 869
 870class Sort(Order):
 871    pass
 872
 873
 874class Qualify(Expression):
 875    pass
 876
 877
 878class InputOutputFormat(Expression):
 879    arg_types = {"input_format": False, "output_format": False}
 880
 881
 882class Return(Expression):
 883    pass
 884
 885
 886class Tuple(Expression):
 887    arg_types = {"expressions": False}
 888
 889    def isin(
 890        self,
 891        *expressions: t.Any,
 892        query: ExpOrStr | None = None,
 893        unnest: ExpOrStr | None | list[ExpOrStr] | tuple[ExpOrStr, ...] = None,
 894        copy: bool = True,
 895        **opts: Unpack[ParserArgs],
 896    ) -> In:
 897        return In(
 898            this=maybe_copy(self, copy),
 899            expressions=[convert(e, copy=copy) for e in expressions],
 900            query=maybe_parse(query, copy=copy, **opts) if query else None,
 901            unnest=(
 902                Unnest(
 903                    expressions=[
 904                        maybe_parse(e, copy=copy, **opts)
 905                        for e in t.cast(list[ExpOrStr], ensure_list(unnest))
 906                    ]
 907                )
 908                if unnest
 909                else None
 910            ),
 911        )
 912
 913
 914class QueryOption(Expression):
 915    arg_types = {"this": True, "expression": False}
 916
 917
 918# FOR { XML | JSON } query modifier; `kind` is the discriminant ("XML" or "JSON").
 919class ForClause(Expression):
 920    arg_types = {"kind": True, "expressions": False}
 921
 922
 923class WithTableHint(Expression):
 924    arg_types = {"expressions": True}
 925
 926
 927class IndexTableHint(Expression):
 928    arg_types = {"this": True, "expressions": False, "target": False}
 929
 930
 931class HistoricalData(Expression):
 932    arg_types = {"this": True, "kind": True, "expression": True}
 933
 934
 935class Put(Expression):
 936    arg_types = {"this": True, "target": True, "properties": False}
 937
 938
 939class Get(Expression):
 940    arg_types = {"this": True, "target": True, "properties": False}
 941
 942
 943class Table(Expression, Selectable):
 944    arg_types = {
 945        "this": False,
 946        "alias": False,
 947        "db": False,
 948        "catalog": False,
 949        "laterals": False,
 950        "joins": False,
 951        "pivots": False,
 952        "hints": False,
 953        "system_time": False,
 954        "version": False,
 955        "format": False,
 956        "pattern": False,
 957        "ordinality": False,
 958        "when": False,
 959        "only": False,
 960        "partition": False,
 961        "changes": False,
 962        "rows_from": False,
 963        "sample": False,
 964        "indexed": False,
 965    }
 966
 967    @property
 968    def name(self) -> str:
 969        if not self.this or isinstance(self.this, Func):
 970            return ""
 971        return self.this.name
 972
 973    @property
 974    def db(self) -> str:
 975        return self.text("db")
 976
 977    @property
 978    def catalog(self) -> str:
 979        return self.text("catalog")
 980
 981    @property
 982    def selects(self) -> list[Expr]:
 983        return []
 984
 985    @property
 986    def named_selects(self) -> list[str]:
 987        return []
 988
 989    @property
 990    def parts(self) -> list[Expr]:
 991        """Return the parts of a table in order catalog, db, table."""
 992        parts: list[Expr] = []
 993
 994        for arg in ("catalog", "db", "this"):
 995            part = self.args.get(arg)
 996
 997            if isinstance(part, Dot):
 998                parts.extend(part.flatten())
 999            elif isinstance(part, Expr):
1000                parts.append(part)
1001
1002        return parts
1003
1004    def to_column(self, copy: bool = True) -> Expr:
1005        parts = self.parts
1006        last_part = parts[-1]
1007
1008        if isinstance(last_part, Identifier):
1009            col: Expr = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
1010        else:
1011            # This branch will be reached if a function or array is wrapped in a `Table`
1012            col = last_part
1013
1014        alias = self.args.get("alias")
1015        if alias:
1016            col = alias_(col, alias.this, copy=copy)
1017
1018        return col
1019
1020
1021class SetOperation(Expression, Query):
1022    arg_types = {
1023        "with_": False,
1024        "this": True,
1025        "expression": True,
1026        "distinct": False,
1027        "by_name": False,
1028        "side": False,
1029        "kind": False,
1030        "on": False,
1031        **QUERY_MODIFIERS,
1032    }
1033
1034    def select(
1035        self: S,
1036        *expressions: ExpOrStr | None,
1037        append: bool = True,
1038        dialect: DialectType = None,
1039        copy: bool = True,
1040        **opts: Unpack[ParserNoDialectArgs],
1041    ) -> S:
1042        this = maybe_copy(self, copy)
1043        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1044        this.expression.unnest().select(
1045            *expressions, append=append, dialect=dialect, copy=False, **opts
1046        )
1047        return this
1048
1049    @property
1050    def named_selects(self) -> list[str]:
1051        expr: Expr = self
1052        while isinstance(expr, SetOperation):
1053            expr = expr.this.unnest()
1054        return _named_selects(expr)
1055
1056    @property
1057    def is_star(self) -> bool:
1058        return self.this.is_star or self.expression.is_star
1059
1060    @property
1061    def selects(self) -> list[Expr]:
1062        expr: Expr = self
1063        while isinstance(expr, SetOperation):
1064            expr = expr.this.unnest()
1065        return getattr(expr, "selects", [])
1066
1067    @property
1068    def left(self) -> Query:
1069        return self.this
1070
1071    @property
1072    def right(self) -> Query:
1073        return self.expression
1074
1075    @property
1076    def kind(self) -> str:
1077        return self.text("kind").upper()
1078
1079    @property
1080    def side(self) -> str:
1081        return self.text("side").upper()
1082
1083
1084class Union(SetOperation):
1085    pass
1086
1087
1088class Except(SetOperation):
1089    pass
1090
1091
1092class Intersect(SetOperation):
1093    pass
1094
1095
1096class Values(Expression, UDTF):
1097    arg_types = {
1098        "expressions": True,
1099        "alias": False,
1100        "order": False,
1101        "limit": False,
1102        "offset": False,
1103    }
1104
1105
1106class Version(Expression):
1107    """
1108    Time travel, iceberg, bigquery etc
1109    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
1110    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
1111    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
1112    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
1113    this is either TIMESTAMP or VERSION
1114    kind is ("AS OF", "BETWEEN")
1115    """
1116
1117    arg_types = {"this": True, "kind": True, "expression": False}
1118
1119
1120class Schema(Expression):
1121    arg_types = {"this": False, "expressions": False}
1122
1123
1124class Lock(Expression):
1125    arg_types = {"update": True, "expressions": False, "wait": False, "key": False}
1126
1127
1128class Select(Expression, Query):
1129    arg_types = {
1130        "with_": False,
1131        "kind": False,
1132        "expressions": False,
1133        "hint": False,
1134        "distinct": False,
1135        "into": False,
1136        "from_": False,
1137        "operation_modifiers": False,
1138        "exclude": False,
1139        **QUERY_MODIFIERS,
1140    }
1141
1142    def from_(
1143        self,
1144        expression: ExpOrStr,
1145        dialect: DialectType = None,
1146        copy: bool = True,
1147        **opts: Unpack[ParserNoDialectArgs],
1148    ) -> Select:
1149        """
1150        Set the FROM expression.
1151
1152        Example:
1153            >>> Select().from_("tbl").select("x").sql()
1154            'SELECT x FROM tbl'
1155
1156        Args:
1157            expression : the SQL code strings to parse.
1158                If a `From` instance is passed, this is used as-is.
1159                If another `Expr` instance is passed, it will be wrapped in a `From`.
1160            dialect: the dialect used to parse the input expression.
1161            copy: if `False`, modify this expression instance in-place.
1162            opts: other options to use to parse the input expressions.
1163
1164        Returns:
1165            The modified Select expression.
1166        """
1167        return _apply_builder(
1168            expression=expression,
1169            instance=self,
1170            arg="from_",
1171            into=From,
1172            prefix="FROM",
1173            dialect=dialect,
1174            copy=copy,
1175            **opts,
1176        )
1177
1178    def group_by(
1179        self,
1180        *expressions: ExpOrStr | None,
1181        append: bool = True,
1182        dialect: DialectType = None,
1183        copy: bool = True,
1184        **opts: Unpack[ParserNoDialectArgs],
1185    ) -> Select:
1186        """
1187        Set the GROUP BY expression.
1188
1189        Example:
1190            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1191            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1192
1193        Args:
1194            *expressions: the SQL code strings to parse.
1195                If a `Group` instance is passed, this is used as-is.
1196                If another `Expr` instance is passed, it will be wrapped in a `Group`.
1197                If nothing is passed in then a group by is not applied to the expression
1198            append: if `True`, add to any existing expressions.
1199                Otherwise, this flattens all the `Group` expression into a single expression.
1200            dialect: the dialect used to parse the input expression.
1201            copy: if `False`, modify this expression instance in-place.
1202            opts: other options to use to parse the input expressions.
1203
1204        Returns:
1205            The modified Select expression.
1206        """
1207        if not expressions:
1208            return self if not copy else self.copy()
1209
1210        return _apply_child_list_builder(
1211            *expressions,
1212            instance=self,
1213            arg="group",
1214            append=append,
1215            copy=copy,
1216            prefix="GROUP BY",
1217            into=Group,
1218            dialect=dialect,
1219            **opts,
1220        )
1221
1222    def sort_by(
1223        self,
1224        *expressions: ExpOrStr | None,
1225        append: bool = True,
1226        dialect: DialectType = None,
1227        copy: bool = True,
1228        **opts: Unpack[ParserNoDialectArgs],
1229    ) -> Select:
1230        """
1231        Set the SORT BY expression.
1232
1233        Example:
1234            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
1235            'SELECT x FROM tbl SORT BY x DESC'
1236
1237        Args:
1238            *expressions: the SQL code strings to parse.
1239                If a `Group` instance is passed, this is used as-is.
1240                If another `Expr` instance is passed, it will be wrapped in a `SORT`.
1241            append: if `True`, add to any existing expressions.
1242                Otherwise, this flattens all the `Order` expression into a single expression.
1243            dialect: the dialect used to parse the input expression.
1244            copy: if `False`, modify this expression instance in-place.
1245            opts: other options to use to parse the input expressions.
1246
1247        Returns:
1248            The modified Select expression.
1249        """
1250        return _apply_child_list_builder(
1251            *expressions,
1252            instance=self,
1253            arg="sort",
1254            append=append,
1255            copy=copy,
1256            prefix="SORT BY",
1257            into=Sort,
1258            dialect=dialect,
1259            **opts,
1260        )
1261
1262    def cluster_by(
1263        self,
1264        *expressions: ExpOrStr | None,
1265        append: bool = True,
1266        dialect: DialectType = None,
1267        copy: bool = True,
1268        **opts: Unpack[ParserNoDialectArgs],
1269    ) -> Select:
1270        """
1271        Set the CLUSTER BY expression.
1272
1273        Example:
1274            >>> Select().from_("tbl").select("x").cluster_by("x").sql(dialect="hive")
1275            'SELECT x FROM tbl CLUSTER BY x'
1276
1277        Args:
1278            *expressions: the SQL code strings to parse.
1279                If a `Group` instance is passed, this is used as-is.
1280                If another `Expr` instance is passed, it will be wrapped in a `Cluster`.
1281            append: if `True`, add to any existing expressions.
1282                Otherwise, this flattens all the `Order` expression into a single expression.
1283            dialect: the dialect used to parse the input expression.
1284            copy: if `False`, modify this expression instance in-place.
1285            opts: other options to use to parse the input expressions.
1286
1287        Returns:
1288            The modified Select expression.
1289        """
1290        return _apply_child_list_builder(
1291            *expressions,
1292            instance=self,
1293            arg="cluster",
1294            append=append,
1295            copy=copy,
1296            prefix="CLUSTER BY",
1297            into=Cluster,
1298            dialect=dialect,
1299            **opts,
1300        )
1301
1302    def select(
1303        self,
1304        *expressions: ExpOrStr | None,
1305        append: bool = True,
1306        dialect: DialectType = None,
1307        copy: bool = True,
1308        **opts: Unpack[ParserNoDialectArgs],
1309    ) -> Select:
1310        return _apply_list_builder(
1311            *expressions,
1312            instance=self,
1313            arg="expressions",
1314            append=append,
1315            dialect=dialect,
1316            into=Expr,
1317            copy=copy,
1318            **opts,
1319        )
1320
1321    def lateral(
1322        self,
1323        *expressions: ExpOrStr | None,
1324        append: bool = True,
1325        dialect: DialectType = None,
1326        copy: bool = True,
1327        **opts: Unpack[ParserNoDialectArgs],
1328    ) -> Select:
1329        """
1330        Append to or set the LATERAL expressions.
1331
1332        Example:
1333            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
1334            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
1335
1336        Args:
1337            *expressions: the SQL code strings to parse.
1338                If an `Expr` instance is passed, it will be used as-is.
1339            append: if `True`, add to any existing expressions.
1340                Otherwise, this resets the expressions.
1341            dialect: the dialect used to parse the input expressions.
1342            copy: if `False`, modify this expression instance in-place.
1343            opts: other options to use to parse the input expressions.
1344
1345        Returns:
1346            The modified Select expression.
1347        """
1348        return _apply_list_builder(
1349            *expressions,
1350            instance=self,
1351            arg="laterals",
1352            append=append,
1353            into=Lateral,
1354            prefix="LATERAL VIEW",
1355            dialect=dialect,
1356            copy=copy,
1357            **opts,
1358        )
1359
1360    def join(
1361        self,
1362        expression: ExpOrStr,
1363        on: ExpOrStr | list[ExpOrStr] | tuple[ExpOrStr, ...] | None = None,
1364        using: ExpOrStr | list[ExpOrStr] | tuple[ExpOrStr, ...] | None = None,
1365        append: bool = True,
1366        join_type: str | None = None,
1367        join_alias: Identifier | str | None = None,
1368        dialect: DialectType = None,
1369        copy: bool = True,
1370        **opts: Unpack[ParserNoDialectArgs],
1371    ) -> Select:
1372        """
1373        Append to or set the JOIN expressions.
1374
1375        Example:
1376            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
1377            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
1378
1379            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
1380            'SELECT 1 FROM a JOIN b USING (x, y, z)'
1381
1382            Use `join_type` to change the type of join:
1383
1384            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
1385            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
1386
1387        Args:
1388            expression: the SQL code string to parse.
1389                If an `Expr` instance is passed, it will be used as-is.
1390            on: optionally specify the join "on" criteria as a SQL string.
1391                If an `Expr` instance is passed, it will be used as-is.
1392            using: optionally specify the join "using" criteria as a SQL string.
1393                If an `Expr` instance is passed, it will be used as-is.
1394            append: if `True`, add to any existing expressions.
1395                Otherwise, this resets the expressions.
1396            join_type: if set, alter the parsed join type.
1397            join_alias: an optional alias for the joined source.
1398            dialect: the dialect used to parse the input expressions.
1399            copy: if `False`, modify this expression instance in-place.
1400            opts: other options to use to parse the input expressions.
1401
1402        Returns:
1403            Select: the modified expression.
1404        """
1405        parse_args: ParserArgs = {"dialect": dialect, **opts}
1406        try:
1407            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
1408        except ParseError:
1409            expression = maybe_parse(expression, into=(Join, Expr), **parse_args)
1410
1411        join = expression if isinstance(expression, Join) else Join(this=expression)
1412
1413        if isinstance(join.this, Select):
1414            join.this.replace(join.this.subquery())
1415
1416        if join_type:
1417            new_join: Join = maybe_parse(f"FROM _ {join_type} JOIN _", **parse_args).find(Join)
1418            method = new_join.method
1419            side = new_join.side
1420            kind = new_join.kind
1421
1422            if method:
1423                join.set("method", method)
1424            if side:
1425                join.set("side", side)
1426            if kind:
1427                join.set("kind", kind)
1428
1429        if on:
1430            on_exprs: list[ExpOrStr] = ensure_list(on)
1431            on = and_(*on_exprs, dialect=dialect, copy=copy, **opts)
1432            join.set("on", on)
1433
1434        if using:
1435            using_exprs: list[ExpOrStr] = ensure_list(using)
1436            join = _apply_list_builder(
1437                *using_exprs,
1438                instance=join,
1439                arg="using",
1440                append=append,
1441                copy=copy,
1442                into=Identifier,
1443                **opts,
1444            )
1445
1446        if join_alias:
1447            join.set("this", alias_(join.this, join_alias, table=True))
1448
1449        return _apply_list_builder(
1450            join,
1451            instance=self,
1452            arg="joins",
1453            append=append,
1454            copy=copy,
1455            **opts,
1456        )
1457
1458    def having(
1459        self,
1460        *expressions: ExpOrStr | None,
1461        append: bool = True,
1462        dialect: DialectType = None,
1463        copy: bool = True,
1464        **opts: Unpack[ParserNoDialectArgs],
1465    ) -> Select:
1466        """
1467        Append to or set the HAVING expressions.
1468
1469        Example:
1470            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
1471            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
1472
1473        Args:
1474            *expressions: the SQL code strings to parse.
1475                If an `Expr` instance is passed, it will be used as-is.
1476                Multiple expressions are combined with an AND operator.
1477            append: if `True`, AND the new expressions to any existing expression.
1478                Otherwise, this resets the expression.
1479            dialect: the dialect used to parse the input expressions.
1480            copy: if `False`, modify this expression instance in-place.
1481            opts: other options to use to parse the input expressions.
1482
1483        Returns:
1484            The modified Select expression.
1485        """
1486        return _apply_conjunction_builder(
1487            *expressions,
1488            instance=self,
1489            arg="having",
1490            append=append,
1491            into=Having,
1492            dialect=dialect,
1493            copy=copy,
1494            **opts,
1495        )
1496
1497    def window(
1498        self,
1499        *expressions: ExpOrStr | None,
1500        append: bool = True,
1501        dialect: DialectType = None,
1502        copy: bool = True,
1503        **opts: Unpack[ParserNoDialectArgs],
1504    ) -> Select:
1505        return _apply_list_builder(
1506            *expressions,
1507            instance=self,
1508            arg="windows",
1509            append=append,
1510            into=Window,
1511            dialect=dialect,
1512            copy=copy,
1513            **opts,
1514        )
1515
1516    def qualify(
1517        self,
1518        *expressions: ExpOrStr | None,
1519        append: bool = True,
1520        dialect: DialectType = None,
1521        copy: bool = True,
1522        **opts: Unpack[ParserNoDialectArgs],
1523    ) -> Select:
1524        return _apply_conjunction_builder(
1525            *expressions,
1526            instance=self,
1527            arg="qualify",
1528            append=append,
1529            into=Qualify,
1530            dialect=dialect,
1531            copy=copy,
1532            **opts,
1533        )
1534
1535    def distinct(self, *ons: ExpOrStr | None, distinct: bool = True, copy: bool = True) -> Select:
1536        """
1537        Set the OFFSET expression.
1538
1539        Example:
1540            >>> Select().from_("tbl").select("x").distinct().sql()
1541            'SELECT DISTINCT x FROM tbl'
1542
1543        Args:
1544            ons: the expressions to distinct on
1545            distinct: whether the Select should be distinct
1546            copy: if `False`, modify this expression instance in-place.
1547
1548        Returns:
1549            Select: the modified expression.
1550        """
1551        instance = maybe_copy(self, copy)
1552        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
1553        instance.set("distinct", Distinct(on=on) if distinct else None)
1554        return instance
1555
1556    def ctas(
1557        self,
1558        table: ExpOrStr,
1559        properties: dict | None = None,
1560        dialect: DialectType = None,
1561        copy: bool = True,
1562        **opts: Unpack[ParserNoDialectArgs],
1563    ) -> Create:
1564        """
1565        Convert this expression to a CREATE TABLE AS statement.
1566
1567        Example:
1568            >>> Select().select("*").from_("tbl").ctas("x").sql()
1569            'CREATE TABLE x AS SELECT * FROM tbl'
1570
1571        Args:
1572            table: the SQL code string to parse as the table name.
1573                If another `Expr` instance is passed, it will be used as-is.
1574            properties: an optional mapping of table properties
1575            dialect: the dialect used to parse the input table.
1576            copy: if `False`, modify this expression instance in-place.
1577            opts: other options to use to parse the input table.
1578
1579        Returns:
1580            The new Create expression.
1581        """
1582        instance = maybe_copy(self, copy)
1583        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
1584
1585        properties_expression = None
1586        if properties:
1587            from sqlglot.expressions.properties import Properties as _Properties
1588
1589            properties_expression = _Properties.from_dict(properties)
1590
1591        from sqlglot.expressions.ddl import Create as _Create
1592
1593        return _Create(
1594            this=table_expression,
1595            kind="TABLE",
1596            expression=instance,
1597            properties=properties_expression,
1598        )
1599
1600    def lock(self, update: bool = True, copy: bool = True) -> Select:
1601        """
1602        Set the locking read mode for this expression.
1603
1604        Examples:
1605            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
1606            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
1607
1608            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
1609            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
1610
1611        Args:
1612            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
1613            copy: if `False`, modify this expression instance in-place.
1614
1615        Returns:
1616            The modified expression.
1617        """
1618        inst = maybe_copy(self, copy)
1619        inst.set("locks", [Lock(update=update)])
1620
1621        return inst
1622
1623    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
1624        """
1625        Set hints for this expression.
1626
1627        Examples:
1628            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
1629            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
1630
1631        Args:
1632            hints: The SQL code strings to parse as the hints.
1633                If an `Expr` instance is passed, it will be used as-is.
1634            dialect: The dialect used to parse the hints.
1635            copy: If `False`, modify this expression instance in-place.
1636
1637        Returns:
1638            The modified expression.
1639        """
1640        inst = maybe_copy(self, copy)
1641        inst.set(
1642            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
1643        )
1644
1645        return inst
1646
1647    @property
1648    def named_selects(self) -> list[str]:
1649        selects = []
1650
1651        for e in self.expressions:
1652            if e.alias_or_name:
1653                selects.append(e.output_name)
1654            elif isinstance(e, Aliases):
1655                selects.extend([a.name for a in e.aliases])
1656        return selects
1657
1658    @property
1659    def is_star(self) -> bool:
1660        return any(expression.is_star for expression in self.expressions)
1661
1662    @property
1663    def selects(self) -> list[Expr]:
1664        return self.expressions
1665
1666
1667class Subquery(Expression, DerivedTable, Query):
1668    is_subquery: t.ClassVar[bool] = True
1669    arg_types = {
1670        "this": True,
1671        "alias": False,
1672        "with_": False,
1673        **QUERY_MODIFIERS,
1674    }
1675
1676    def unnest(self) -> Expr:
1677        """Returns the first non subquery."""
1678        expression: Expr = self
1679        while isinstance(expression, Subquery):
1680            expression = expression.this
1681        return expression
1682
1683    def unwrap(self) -> Subquery:
1684        expression = self
1685        while expression.same_parent and expression.is_wrapper:
1686            expression = t.cast(Subquery, expression.parent)
1687        return expression
1688
1689    def select(
1690        self,
1691        *expressions: ExpOrStr | None,
1692        append: bool = True,
1693        dialect: DialectType = None,
1694        copy: bool = True,
1695        **opts: Unpack[ParserNoDialectArgs],
1696    ) -> Subquery:
1697        this = maybe_copy(self, copy)
1698        inner = this.unnest()
1699        if hasattr(inner, "select"):
1700            inner.select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1701        return this
1702
1703    @property
1704    def is_wrapper(self) -> bool:
1705        """
1706        Whether this Subquery acts as a simple wrapper around another expression.
1707
1708        SELECT * FROM (((SELECT * FROM t)))
1709                      ^
1710                      This corresponds to a "wrapper" Subquery node
1711        """
1712        return all(v is None for k, v in self.args.items() if k != "this")
1713
1714    @property
1715    def is_star(self) -> bool:
1716        return self.this.is_star
1717
1718    @property
1719    def output_name(self) -> str:
1720        return self.alias
1721
1722
1723class TableSample(Expression):
1724    arg_types = {
1725        "expressions": False,
1726        "method": False,
1727        "bucket_numerator": False,
1728        "bucket_denominator": False,
1729        "bucket_field": False,
1730        "percent": False,
1731        "rows": False,
1732        "size": False,
1733        "seed": False,
1734    }
1735
1736
1737class Tag(Expression):
1738    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
1739
1740    arg_types = {
1741        "this": False,
1742        "prefix": False,
1743        "postfix": False,
1744    }
1745
1746
1747class Pivot(Expression):
1748    arg_types = {
1749        "this": False,
1750        "alias": False,
1751        "expressions": False,
1752        "fields": False,
1753        "unpivot": False,
1754        "using": False,
1755        "group": False,
1756        "columns": False,
1757        "include_nulls": False,
1758        "default_on_null": False,
1759        "into": False,
1760        "with_": False,
1761        "identify_pivot_strings": False,
1762        "prefixed_pivot_columns": False,
1763        "pivot_column_naming": False,
1764    }
1765
1766    @property
1767    def unpivot(self) -> bool:
1768        return bool(self.args.get("unpivot"))
1769
1770    @property
1771    def fields(self) -> list[Expr]:
1772        return self.args.get("fields", [])
1773
1774    def output_columns(self, pre_pivot_columns: t.Iterable[str]) -> dict[str, str]:
1775        """
1776        Returns an ordered map of post-rename output column name -> pre-rename
1777        source-side name, in the order the (UN)PIVOT produces them.
1778
1779        For callers that just want the names, iterate the dict (or call .keys()):
1780            >>> from sqlglot import parse_one, exp
1781            >>> piv = parse_one("SELECT * FROM t UNPIVOT(val FOR name IN (a, b))").find(exp.Pivot)
1782            >>> list(piv.output_columns(["a", "b", "c"]))
1783            ['c', 'name', 'val']
1784
1785        AST shape:
1786            PIVOT(SUM(val) FOR name IN ('a', 'b')):
1787                expressions: aggregate(s), e.g. [Sum(this=Column(val))]
1788                fields:      [In(this=Column(name), expressions=[Literal('a'), Literal('b')])]
1789                columns:     optional explicit output identifiers (e.g. set by Snowflake)
1790
1791            UNPIVOT(val FOR name IN (a, b)):
1792                expressions: value Identifier(s), or Tuple(Identifiers) for multi-value
1793                fields:      [In(this=Identifier(name), expressions=[Column(a), Column(b)])]
1794                             For literal-aliased entries (`a AS 'x'`) the IN expressions
1795                             are wrapped in PivotAlias(this=Column, alias=Literal).
1796
1797        Args:
1798            pre_pivot_columns: Columns visible to the operator before it runs
1799                (e.g. the source table or subquery's projections).
1800        """
1801        if self.unpivot:
1802            excluded: set[str] = set()
1803            name_columns: list[Identifier] = []
1804            for field in self.fields:
1805                if not isinstance(field, In):
1806                    continue
1807                if isinstance(field.this, Identifier):
1808                    name_columns.append(field.this)
1809                for e in field.expressions:
1810                    excluded.update(c.output_name for c in e.find_all(Column))
1811            value_columns = [
1812                ident
1813                for e in self.expressions
1814                for ident in (e.expressions if isinstance(e, Tuple) else [e])
1815                if isinstance(ident, Identifier)
1816            ]
1817            outputs = [i.name for i in name_columns + value_columns]
1818        else:
1819            excluded = {c.output_name for c in self.find_all(Column)}
1820            outputs = [c.output_name for c in self.args.get("columns") or []]
1821            if not outputs:
1822                outputs = [c.alias_or_name for c in self.expressions]
1823
1824        if not excluded or not outputs:
1825            return {}
1826
1827        pre_rename = [c for c in pre_pivot_columns if c not in excluded] + outputs
1828
1829        alias = self.args.get("alias")
1830        renames = alias.args.get("columns") if alias else None
1831
1832        # `PIVOT(...) AS alias(c1, c2, ...)` renames the operator's output columns
1833        # positionally from the front (DuckDB, Snowflake): the user's names cover
1834        # the leading N output columns, remaining columns keep their auto names.
1835        if renames:
1836            rename_names = [r.name for r in renames]
1837            post_rename = rename_names + pre_rename[len(rename_names) :]
1838        else:
1839            post_rename = pre_rename
1840
1841        return dict(zip(post_rename, pre_rename))
1842
1843
1844class UnpivotColumns(Expression):
1845    arg_types = {"this": True, "expressions": True}
1846
1847
1848class Window(Expression, Condition):
1849    arg_types = {
1850        "this": True,
1851        "partition_by": False,
1852        "order": False,
1853        "spec": False,
1854        "alias": False,
1855        "over": False,
1856        "first": False,
1857    }
1858
1859
1860class WindowSpec(Expression):
1861    arg_types = {
1862        "kind": False,
1863        "start": False,
1864        "start_side": False,
1865        "end": False,
1866        "end_side": False,
1867        "exclude": False,
1868    }
1869
1870
1871class PreWhere(Expression):
1872    pass
1873
1874
1875class Where(Expression):
1876    pass
1877
1878
1879class Analyze(Expression):
1880    arg_types = {
1881        "kind": False,
1882        "this": False,
1883        "options": False,
1884        "mode": False,
1885        "partition": False,
1886        "expression": False,
1887        "properties": False,
1888    }
1889
1890
1891class AnalyzeStatistics(Expression):
1892    arg_types = {
1893        "kind": True,
1894        "option": False,
1895        "this": False,
1896        "expressions": False,
1897    }
1898
1899
1900class AnalyzeHistogram(Expression):
1901    arg_types = {
1902        "this": True,
1903        "expressions": True,
1904        "expression": False,
1905        "update_options": False,
1906    }
1907
1908
1909class AnalyzeSample(Expression):
1910    arg_types = {"kind": True, "sample": True}
1911
1912
1913class AnalyzeListChainedRows(Expression):
1914    arg_types = {"expression": False}
1915
1916
1917class AnalyzeDelete(Expression):
1918    arg_types = {"kind": False}
1919
1920
1921class AnalyzeWith(Expression):
1922    arg_types = {"expressions": True}
1923
1924
1925class AnalyzeValidate(Expression):
1926    arg_types = {
1927        "kind": True,
1928        "this": False,
1929        "expression": False,
1930    }
1931
1932
1933class AnalyzeColumns(Expression):
1934    pass
1935
1936
1937class UsingData(Expression):
1938    pass
1939
1940
1941class AddPartition(Expression):
1942    arg_types = {"this": True, "exists": False, "location": False}
1943
1944
1945class AttachOption(Expression):
1946    arg_types = {"this": True, "expression": False}
1947
1948
1949class DropPartition(Expression):
1950    arg_types = {"expressions": True, "exists": False}
1951
1952
1953class ReplacePartition(Expression):
1954    arg_types = {"expression": True, "source": True}
1955
1956
1957class TranslateCharacters(Expression):
1958    arg_types = {"this": True, "expression": True, "with_error": False}
1959
1960
1961class OverflowTruncateBehavior(Expression):
1962    arg_types = {"this": False, "with_count": True}
1963
1964
1965class JSON(Expression):
1966    arg_types = {"this": False, "with_": False, "unique": False}
1967
1968
1969class JSONPath(Expression):
1970    arg_types = {"expressions": True, "escape": False}
1971
1972    @property
1973    def output_name(self) -> str:
1974        last_segment = self.expressions[-1].this
1975        return last_segment if isinstance(last_segment, str) else ""
1976
1977
1978class JSONPathPart(Expression):
1979    arg_types = {}
1980
1981
1982class JSONPathFilter(JSONPathPart):
1983    arg_types = {"this": True}
1984
1985
1986class JSONPathKey(JSONPathPart):
1987    arg_types = {"this": True}
1988
1989
1990class JSONPathRecursive(JSONPathPart):
1991    arg_types = {"this": False}
1992
1993
1994class JSONPathRoot(JSONPathPart):
1995    pass
1996
1997
1998class JSONPathScript(JSONPathPart):
1999    arg_types = {"this": True}
2000
2001
2002class JSONPathSlice(JSONPathPart):
2003    arg_types = {"start": False, "end": False, "step": False}
2004
2005
2006class JSONPathSelector(JSONPathPart):
2007    arg_types = {"this": True}
2008
2009
2010class JSONPathSubscript(JSONPathPart):
2011    arg_types = {"this": True}
2012
2013
2014class JSONPathUnion(JSONPathPart):
2015    arg_types = {"expressions": True}
2016
2017
2018class JSONPathWildcard(JSONPathPart):
2019    pass
2020
2021
2022class FormatJson(Expression):
2023    pass
2024
2025
2026class JSONKeyValue(Expression):
2027    arg_types = {"this": True, "expression": True}
2028
2029
2030class JSONColumnDef(Expression):
2031    arg_types = {
2032        "this": False,
2033        "kind": False,
2034        "path": False,
2035        "nested_schema": False,
2036        "ordinality": False,
2037        "format_json": False,
2038    }
2039
2040
2041class JSONSchema(Expression):
2042    arg_types = {"expressions": True}
2043
2044
2045class JSONValue(Expression):
2046    arg_types = {
2047        "this": True,
2048        "path": True,
2049        "returning": False,
2050        "on_condition": False,
2051    }
2052
2053
2054class JSONValueArray(Expression, Func):
2055    arg_types = {"this": True, "expression": False}
2056
2057
2058class OpenJSONColumnDef(Expression):
2059    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
2060
2061
2062class JSONExtractQuote(Expression):
2063    arg_types = {
2064        "option": True,
2065        "scalar": False,
2066    }
2067
2068
2069class ScopeResolution(Expression):
2070    arg_types = {"this": False, "expression": True}
2071
2072
2073class Stream(Expression):
2074    pass
2075
2076
2077class ModelAttribute(Expression):
2078    arg_types = {"this": True, "expression": True}
2079
2080
2081class XMLNamespace(Expression):
2082    pass
2083
2084
2085class XMLKeyValueOption(Expression):
2086    arg_types = {"this": True, "expression": False}
2087
2088
2089class Semicolon(Expression):
2090    arg_types = {}
2091
2092
2093class TableColumn(Expression):
2094    @property
2095    def output_name(self) -> str:
2096        return self.name
2097
2098
2099class Variadic(Expression):
2100    pass
2101
2102
2103class StoredProcedure(Expression):
2104    arg_types = {"this": True, "expressions": False, "wrapped": False}
2105
2106
2107class Block(Expression):
2108    arg_types = {"expressions": True}
2109
2110
2111class IfBlock(Expression):
2112    arg_types = {"this": True, "true": True, "false": False}
2113
2114
2115class WhileBlock(Expression):
2116    arg_types = {"this": True, "body": True}
2117
2118
2119class EndStatement(Expression):
2120    arg_types = {}
2121
2122
2123UNWRAPPED_QUERIES = (Select, SetOperation)
2124
2125
2126def union(
2127    *expressions: ExpOrStr,
2128    distinct: bool = True,
2129    dialect: DialectType = None,
2130    copy: bool = True,
2131    **opts: Unpack[ParserNoDialectArgs],
2132) -> Union:
2133    """
2134    Initializes a syntax tree for the `UNION` operation.
2135
2136    Example:
2137        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
2138        'SELECT * FROM foo UNION SELECT * FROM bla'
2139
2140    Args:
2141        expressions: the SQL code strings, corresponding to the `UNION`'s operands.
2142            If `Expr` instances are passed, they will be used as-is.
2143        distinct: set the DISTINCT flag if and only if this is true.
2144        dialect: the dialect used to parse the input expression.
2145        copy: whether to copy the expression.
2146        opts: other options to use to parse the input expressions.
2147
2148    Returns:
2149        The new Union instance.
2150    """
2151    assert len(expressions) >= 2, "At least two expressions are required by `union`."
2152    return _apply_set_operation(
2153        *expressions, set_operation=Union, distinct=distinct, dialect=dialect, copy=copy, **opts
2154    )
2155
2156
2157def intersect(
2158    *expressions: ExpOrStr,
2159    distinct: bool = True,
2160    dialect: DialectType = None,
2161    copy: bool = True,
2162    **opts: Unpack[ParserNoDialectArgs],
2163) -> Intersect:
2164    """
2165    Initializes a syntax tree for the `INTERSECT` operation.
2166
2167    Example:
2168        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
2169        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
2170
2171    Args:
2172        expressions: the SQL code strings, corresponding to the `INTERSECT`'s operands.
2173            If `Expr` instances are passed, they will be used as-is.
2174        distinct: set the DISTINCT flag if and only if this is true.
2175        dialect: the dialect used to parse the input expression.
2176        copy: whether to copy the expression.
2177        opts: other options to use to parse the input expressions.
2178
2179    Returns:
2180        The new Intersect instance.
2181    """
2182    assert len(expressions) >= 2, "At least two expressions are required by `intersect`."
2183    return _apply_set_operation(
2184        *expressions, set_operation=Intersect, distinct=distinct, dialect=dialect, copy=copy, **opts
2185    )
2186
2187
2188def except_(
2189    *expressions: ExpOrStr,
2190    distinct: bool = True,
2191    dialect: DialectType = None,
2192    copy: bool = True,
2193    **opts: Unpack[ParserNoDialectArgs],
2194) -> Except:
2195    """
2196    Initializes a syntax tree for the `EXCEPT` operation.
2197
2198    Example:
2199        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
2200        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
2201
2202    Args:
2203        expressions: the SQL code strings, corresponding to the `EXCEPT`'s operands.
2204            If `Expr` instances are passed, they will be used as-is.
2205        distinct: set the DISTINCT flag if and only if this is true.
2206        dialect: the dialect used to parse the input expression.
2207        copy: whether to copy the expression.
2208        opts: other options to use to parse the input expressions.
2209
2210    Returns:
2211        The new Except instance.
2212    """
2213    assert len(expressions) >= 2, "At least two expressions are required by `except_`."
2214    return _apply_set_operation(
2215        *expressions, set_operation=Except, distinct=distinct, dialect=dialect, copy=copy, **opts
2216    )