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