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