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