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