Edit on GitHub

Expressions

Every AST node in SQLGlot is represented by a subclass of Expression.

This module contains the implementation of all supported Expression types. Additionally, it exposes a number of helper functions, which are mainly used to programmatically build SQL expressions, such as select.


   1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61COLUMN_PARTS = ("this", "table", "db", "catalog")
  62
  63
  64class Expression(metaclass=_Expression):
  65    """
  66    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  67    context, such as its child expressions, their names (arg keys), and whether a given child expression
  68    is optional or not.
  69
  70    Attributes:
  71        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  72            and representing expressions as strings.
  73        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  74            arg keys to booleans that indicate whether the corresponding args are optional.
  75        parent: a reference to the parent expression (or None, in case of root expressions).
  76        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  77            uses to refer to it.
  78        index: the index of an expression if it is inside of a list argument in its parent.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83        meta: a dictionary that can be used to store useful metadata for a given expression.
  84
  85    Example:
  86        >>> class Foo(Expression):
  87        ...     arg_types = {"this": True, "expression": False}
  88
  89        The above definition informs us that Foo is an Expression that requires an argument called
  90        "this" and may also optionally receive an argument called "expression".
  91
  92    Args:
  93        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  94    """
  95
  96    key = "expression"
  97    arg_types = {"this": True}
  98    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  99
 100    def __init__(self, **args: t.Any):
 101        self.args: t.Dict[str, t.Any] = args
 102        self.parent: t.Optional[Expression] = None
 103        self.arg_key: t.Optional[str] = None
 104        self.index: t.Optional[int] = None
 105        self.comments: t.Optional[t.List[str]] = None
 106        self._type: t.Optional[DataType] = None
 107        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 108        self._hash: t.Optional[int] = None
 109
 110        for arg_key, value in self.args.items():
 111            self._set_parent(arg_key, value)
 112
 113    def __eq__(self, other) -> bool:
 114        return type(self) is type(other) and hash(self) == hash(other)
 115
 116    @property
 117    def hashable_args(self) -> t.Any:
 118        return frozenset(
 119            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 120            for k, v in self.args.items()
 121            if not (v is None or v is False or (type(v) is list and not v))
 122        )
 123
 124    def __hash__(self) -> int:
 125        if self._hash is not None:
 126            return self._hash
 127
 128        return hash((self.__class__, self.hashable_args))
 129
 130    @property
 131    def this(self) -> t.Any:
 132        """
 133        Retrieves the argument with key "this".
 134        """
 135        return self.args.get("this")
 136
 137    @property
 138    def expression(self) -> t.Any:
 139        """
 140        Retrieves the argument with key "expression".
 141        """
 142        return self.args.get("expression")
 143
 144    @property
 145    def expressions(self) -> t.List[t.Any]:
 146        """
 147        Retrieves the argument with key "expressions".
 148        """
 149        return self.args.get("expressions") or []
 150
 151    def text(self, key) -> str:
 152        """
 153        Returns a textual representation of the argument corresponding to "key". This can only be used
 154        for args that are strings or leaf Expression instances, such as identifiers and literals.
 155        """
 156        field = self.args.get(key)
 157        if isinstance(field, str):
 158            return field
 159        if isinstance(field, (Identifier, Literal, Var)):
 160            return field.this
 161        if isinstance(field, (Star, Null)):
 162            return field.name
 163        return ""
 164
 165    @property
 166    def is_string(self) -> bool:
 167        """
 168        Checks whether a Literal expression is a string.
 169        """
 170        return isinstance(self, Literal) and self.args["is_string"]
 171
 172    @property
 173    def is_number(self) -> bool:
 174        """
 175        Checks whether a Literal expression is a number.
 176        """
 177        return isinstance(self, Literal) and not self.args["is_string"]
 178
 179    @property
 180    def is_negative(self) -> bool:
 181        """
 182        Checks whether an expression is negative.
 183
 184        Handles both exp.Neg and Literal numbers with "-" which come from optimizer.simplify.
 185        """
 186        return isinstance(self, Neg) or (self.is_number and self.this.startswith("-"))
 187
 188    @property
 189    def is_int(self) -> bool:
 190        """
 191        Checks whether a Literal expression is an integer.
 192        """
 193        return self.is_number and is_int(self.name)
 194
 195    @property
 196    def is_star(self) -> bool:
 197        """Checks whether an expression is a star."""
 198        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 199
 200    @property
 201    def alias(self) -> str:
 202        """
 203        Returns the alias of the expression, or an empty string if it's not aliased.
 204        """
 205        if isinstance(self.args.get("alias"), TableAlias):
 206            return self.args["alias"].name
 207        return self.text("alias")
 208
 209    @property
 210    def alias_column_names(self) -> t.List[str]:
 211        table_alias = self.args.get("alias")
 212        if not table_alias:
 213            return []
 214        return [c.name for c in table_alias.args.get("columns") or []]
 215
 216    @property
 217    def name(self) -> str:
 218        return self.text("this")
 219
 220    @property
 221    def alias_or_name(self) -> str:
 222        return self.alias or self.name
 223
 224    @property
 225    def output_name(self) -> str:
 226        """
 227        Name of the output column if this expression is a selection.
 228
 229        If the Expression has no output name, an empty string is returned.
 230
 231        Example:
 232            >>> from sqlglot import parse_one
 233            >>> parse_one("SELECT a").expressions[0].output_name
 234            'a'
 235            >>> parse_one("SELECT b AS c").expressions[0].output_name
 236            'c'
 237            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 238            ''
 239        """
 240        return ""
 241
 242    @property
 243    def type(self) -> t.Optional[DataType]:
 244        return self._type
 245
 246    @type.setter
 247    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 248        if dtype and not isinstance(dtype, DataType):
 249            dtype = DataType.build(dtype)
 250        self._type = dtype  # type: ignore
 251
 252    def is_type(self, *dtypes) -> bool:
 253        return self.type is not None and self.type.is_type(*dtypes)
 254
 255    def is_leaf(self) -> bool:
 256        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 257
 258    @property
 259    def meta(self) -> t.Dict[str, t.Any]:
 260        if self._meta is None:
 261            self._meta = {}
 262        return self._meta
 263
 264    def __deepcopy__(self, memo):
 265        root = self.__class__()
 266        stack = [(self, root)]
 267
 268        while stack:
 269            node, copy = stack.pop()
 270
 271            if node.comments is not None:
 272                copy.comments = deepcopy(node.comments)
 273            if node._type is not None:
 274                copy._type = deepcopy(node._type)
 275            if node._meta is not None:
 276                copy._meta = deepcopy(node._meta)
 277            if node._hash is not None:
 278                copy._hash = node._hash
 279
 280            for k, vs in node.args.items():
 281                if hasattr(vs, "parent"):
 282                    stack.append((vs, vs.__class__()))
 283                    copy.set(k, stack[-1][-1])
 284                elif type(vs) is list:
 285                    copy.args[k] = []
 286
 287                    for v in vs:
 288                        if hasattr(v, "parent"):
 289                            stack.append((v, v.__class__()))
 290                            copy.append(k, stack[-1][-1])
 291                        else:
 292                            copy.append(k, v)
 293                else:
 294                    copy.args[k] = vs
 295
 296        return root
 297
 298    def copy(self):
 299        """
 300        Returns a deep copy of the expression.
 301        """
 302        return deepcopy(self)
 303
 304    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 305        if self.comments is None:
 306            self.comments = []
 307        if comments:
 308            for comment in comments:
 309                _, *meta = comment.split(SQLGLOT_META)
 310                if meta:
 311                    for kv in "".join(meta).split(","):
 312                        k, *v = kv.split("=")
 313                        value = v[0].strip() if v else True
 314                        self.meta[k.strip()] = value
 315                self.comments.append(comment)
 316
 317    def append(self, arg_key: str, value: t.Any) -> None:
 318        """
 319        Appends value to arg_key if it's a list or sets it as a new list.
 320
 321        Args:
 322            arg_key (str): name of the list expression arg
 323            value (Any): value to append to the list
 324        """
 325        if type(self.args.get(arg_key)) is not list:
 326            self.args[arg_key] = []
 327        self._set_parent(arg_key, value)
 328        values = self.args[arg_key]
 329        if hasattr(value, "parent"):
 330            value.index = len(values)
 331        values.append(value)
 332
 333    def set(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 334        """
 335        Sets arg_key to value.
 336
 337        Args:
 338            arg_key: name of the expression arg.
 339            value: value to set the arg to.
 340            index: if the arg is a list, this specifies what position to add the value in it.
 341        """
 342        if index is not None:
 343            expressions = self.args.get(arg_key) or []
 344
 345            if seq_get(expressions, index) is None:
 346                return
 347            if value is None:
 348                expressions.pop(index)
 349                for v in expressions[index:]:
 350                    v.index = v.index - 1
 351                return
 352
 353            if isinstance(value, list):
 354                expressions.pop(index)
 355                expressions[index:index] = value
 356            else:
 357                expressions[index] = value
 358
 359            value = expressions
 360        elif value is None:
 361            self.args.pop(arg_key, None)
 362            return
 363
 364        self.args[arg_key] = value
 365        self._set_parent(arg_key, value, index)
 366
 367    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 368        if hasattr(value, "parent"):
 369            value.parent = self
 370            value.arg_key = arg_key
 371            value.index = index
 372        elif type(value) is list:
 373            for index, v in enumerate(value):
 374                if hasattr(v, "parent"):
 375                    v.parent = self
 376                    v.arg_key = arg_key
 377                    v.index = index
 378
 379    @property
 380    def depth(self) -> int:
 381        """
 382        Returns the depth of this tree.
 383        """
 384        if self.parent:
 385            return self.parent.depth + 1
 386        return 0
 387
 388    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 389        """Yields the key and expression for all arguments, exploding list args."""
 390        # remove tuple when python 3.7 is deprecated
 391        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 392            if type(vs) is list:
 393                for v in reversed(vs) if reverse else vs:
 394                    if hasattr(v, "parent"):
 395                        yield v
 396            else:
 397                if hasattr(vs, "parent"):
 398                    yield vs
 399
 400    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 401        """
 402        Returns the first node in this tree which matches at least one of
 403        the specified types.
 404
 405        Args:
 406            expression_types: the expression type(s) to match.
 407            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 408
 409        Returns:
 410            The node which matches the criteria or None if no such node was found.
 411        """
 412        return next(self.find_all(*expression_types, bfs=bfs), None)
 413
 414    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 415        """
 416        Returns a generator object which visits all nodes in this tree and only
 417        yields those that match at least one of the specified expression types.
 418
 419        Args:
 420            expression_types: the expression type(s) to match.
 421            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 422
 423        Returns:
 424            The generator object.
 425        """
 426        for expression in self.walk(bfs=bfs):
 427            if isinstance(expression, expression_types):
 428                yield expression
 429
 430    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 431        """
 432        Returns a nearest parent matching expression_types.
 433
 434        Args:
 435            expression_types: the expression type(s) to match.
 436
 437        Returns:
 438            The parent node.
 439        """
 440        ancestor = self.parent
 441        while ancestor and not isinstance(ancestor, expression_types):
 442            ancestor = ancestor.parent
 443        return ancestor  # type: ignore
 444
 445    @property
 446    def parent_select(self) -> t.Optional[Select]:
 447        """
 448        Returns the parent select statement.
 449        """
 450        return self.find_ancestor(Select)
 451
 452    @property
 453    def same_parent(self) -> bool:
 454        """Returns if the parent is the same class as itself."""
 455        return type(self.parent) is self.__class__
 456
 457    def root(self) -> Expression:
 458        """
 459        Returns the root expression of this tree.
 460        """
 461        expression = self
 462        while expression.parent:
 463            expression = expression.parent
 464        return expression
 465
 466    def walk(
 467        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 468    ) -> t.Iterator[Expression]:
 469        """
 470        Returns a generator object which visits all nodes in this tree.
 471
 472        Args:
 473            bfs: if set to True the BFS traversal order will be applied,
 474                otherwise the DFS traversal will be used instead.
 475            prune: callable that returns True if the generator should stop traversing
 476                this branch of the tree.
 477
 478        Returns:
 479            the generator object.
 480        """
 481        if bfs:
 482            yield from self.bfs(prune=prune)
 483        else:
 484            yield from self.dfs(prune=prune)
 485
 486    def dfs(
 487        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 488    ) -> t.Iterator[Expression]:
 489        """
 490        Returns a generator object which visits all nodes in this tree in
 491        the DFS (Depth-first) order.
 492
 493        Returns:
 494            The generator object.
 495        """
 496        stack = [self]
 497
 498        while stack:
 499            node = stack.pop()
 500
 501            yield node
 502
 503            if prune and prune(node):
 504                continue
 505
 506            for v in node.iter_expressions(reverse=True):
 507                stack.append(v)
 508
 509    def bfs(
 510        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 511    ) -> t.Iterator[Expression]:
 512        """
 513        Returns a generator object which visits all nodes in this tree in
 514        the BFS (Breadth-first) order.
 515
 516        Returns:
 517            The generator object.
 518        """
 519        queue = deque([self])
 520
 521        while queue:
 522            node = queue.popleft()
 523
 524            yield node
 525
 526            if prune and prune(node):
 527                continue
 528
 529            for v in node.iter_expressions():
 530                queue.append(v)
 531
 532    def unnest(self):
 533        """
 534        Returns the first non parenthesis child or self.
 535        """
 536        expression = self
 537        while type(expression) is Paren:
 538            expression = expression.this
 539        return expression
 540
 541    def unalias(self):
 542        """
 543        Returns the inner expression if this is an Alias.
 544        """
 545        if isinstance(self, Alias):
 546            return self.this
 547        return self
 548
 549    def unnest_operands(self):
 550        """
 551        Returns unnested operands as a tuple.
 552        """
 553        return tuple(arg.unnest() for arg in self.iter_expressions())
 554
 555    def flatten(self, unnest=True):
 556        """
 557        Returns a generator which yields child nodes whose parents are the same class.
 558
 559        A AND B AND C -> [A, B, C]
 560        """
 561        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 562            if type(node) is not self.__class__:
 563                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 564
 565    def __str__(self) -> str:
 566        return self.sql()
 567
 568    def __repr__(self) -> str:
 569        return _to_s(self)
 570
 571    def to_s(self) -> str:
 572        """
 573        Same as __repr__, but includes additional information which can be useful
 574        for debugging, like empty or missing args and the AST nodes' object IDs.
 575        """
 576        return _to_s(self, verbose=True)
 577
 578    def sql(self, dialect: DialectType = None, **opts) -> str:
 579        """
 580        Returns SQL string representation of this tree.
 581
 582        Args:
 583            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 584            opts: other `sqlglot.generator.Generator` options.
 585
 586        Returns:
 587            The SQL string.
 588        """
 589        from sqlglot.dialects import Dialect
 590
 591        return Dialect.get_or_raise(dialect).generate(self, **opts)
 592
 593    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 594        """
 595        Visits all tree nodes (excluding already transformed ones)
 596        and applies the given transformation function to each node.
 597
 598        Args:
 599            fun: a function which takes a node as an argument and returns a
 600                new transformed node or the same node without modifications. If the function
 601                returns None, then the corresponding node will be removed from the syntax tree.
 602            copy: if set to True a new tree instance is constructed, otherwise the tree is
 603                modified in place.
 604
 605        Returns:
 606            The transformed tree.
 607        """
 608        root = None
 609        new_node = None
 610
 611        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 612            parent, arg_key, index = node.parent, node.arg_key, node.index
 613            new_node = fun(node, *args, **kwargs)
 614
 615            if not root:
 616                root = new_node
 617            elif new_node is not node:
 618                parent.set(arg_key, new_node, index)
 619
 620        assert root
 621        return root.assert_is(Expression)
 622
 623    @t.overload
 624    def replace(self, expression: E) -> E: ...
 625
 626    @t.overload
 627    def replace(self, expression: None) -> None: ...
 628
 629    def replace(self, expression):
 630        """
 631        Swap out this expression with a new expression.
 632
 633        For example::
 634
 635            >>> tree = Select().select("x").from_("tbl")
 636            >>> tree.find(Column).replace(column("y"))
 637            Column(
 638              this=Identifier(this=y, quoted=False))
 639            >>> tree.sql()
 640            'SELECT y FROM tbl'
 641
 642        Args:
 643            expression: new node
 644
 645        Returns:
 646            The new expression or expressions.
 647        """
 648        parent = self.parent
 649
 650        if not parent or parent is expression:
 651            return expression
 652
 653        key = self.arg_key
 654        value = parent.args.get(key)
 655
 656        if type(expression) is list and isinstance(value, Expression):
 657            # We are trying to replace an Expression with a list, so it's assumed that
 658            # the intention was to really replace the parent of this expression.
 659            value.parent.replace(expression)
 660        else:
 661            parent.set(key, expression, self.index)
 662
 663        if expression is not self:
 664            self.parent = None
 665            self.arg_key = None
 666            self.index = None
 667
 668        return expression
 669
 670    def pop(self: E) -> E:
 671        """
 672        Remove this expression from its AST.
 673
 674        Returns:
 675            The popped expression.
 676        """
 677        self.replace(None)
 678        return self
 679
 680    def assert_is(self, type_: t.Type[E]) -> E:
 681        """
 682        Assert that this `Expression` is an instance of `type_`.
 683
 684        If it is NOT an instance of `type_`, this raises an assertion error.
 685        Otherwise, this returns this expression.
 686
 687        Examples:
 688            This is useful for type security in chained expressions:
 689
 690            >>> import sqlglot
 691            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 692            'SELECT x, z FROM y'
 693        """
 694        if not isinstance(self, type_):
 695            raise AssertionError(f"{self} is not {type_}.")
 696        return self
 697
 698    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 699        """
 700        Checks if this expression is valid (e.g. all mandatory args are set).
 701
 702        Args:
 703            args: a sequence of values that were used to instantiate a Func expression. This is used
 704                to check that the provided arguments don't exceed the function argument limit.
 705
 706        Returns:
 707            A list of error messages for all possible errors that were found.
 708        """
 709        errors: t.List[str] = []
 710
 711        for k in self.args:
 712            if k not in self.arg_types:
 713                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 714        for k, mandatory in self.arg_types.items():
 715            v = self.args.get(k)
 716            if mandatory and (v is None or (isinstance(v, list) and not v)):
 717                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 718
 719        if (
 720            args
 721            and isinstance(self, Func)
 722            and len(args) > len(self.arg_types)
 723            and not self.is_var_len_args
 724        ):
 725            errors.append(
 726                f"The number of provided arguments ({len(args)}) is greater than "
 727                f"the maximum number of supported arguments ({len(self.arg_types)})"
 728            )
 729
 730        return errors
 731
 732    def dump(self):
 733        """
 734        Dump this Expression to a JSON-serializable dict.
 735        """
 736        from sqlglot.serde import dump
 737
 738        return dump(self)
 739
 740    @classmethod
 741    def load(cls, obj):
 742        """
 743        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 744        """
 745        from sqlglot.serde import load
 746
 747        return load(obj)
 748
 749    def and_(
 750        self,
 751        *expressions: t.Optional[ExpOrStr],
 752        dialect: DialectType = None,
 753        copy: bool = True,
 754        **opts,
 755    ) -> Condition:
 756        """
 757        AND this condition with one or multiple expressions.
 758
 759        Example:
 760            >>> condition("x=1").and_("y=1").sql()
 761            'x = 1 AND y = 1'
 762
 763        Args:
 764            *expressions: the SQL code strings to parse.
 765                If an `Expression` instance is passed, it will be used as-is.
 766            dialect: the dialect used to parse the input expression.
 767            copy: whether to copy the involved expressions (only applies to Expressions).
 768            opts: other options to use to parse the input expressions.
 769
 770        Returns:
 771            The new And condition.
 772        """
 773        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 774
 775    def or_(
 776        self,
 777        *expressions: t.Optional[ExpOrStr],
 778        dialect: DialectType = None,
 779        copy: bool = True,
 780        **opts,
 781    ) -> Condition:
 782        """
 783        OR this condition with one or multiple expressions.
 784
 785        Example:
 786            >>> condition("x=1").or_("y=1").sql()
 787            'x = 1 OR y = 1'
 788
 789        Args:
 790            *expressions: the SQL code strings to parse.
 791                If an `Expression` instance is passed, it will be used as-is.
 792            dialect: the dialect used to parse the input expression.
 793            copy: whether to copy the involved expressions (only applies to Expressions).
 794            opts: other options to use to parse the input expressions.
 795
 796        Returns:
 797            The new Or condition.
 798        """
 799        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 800
 801    def not_(self, copy: bool = True):
 802        """
 803        Wrap this condition with NOT.
 804
 805        Example:
 806            >>> condition("x=1").not_().sql()
 807            'NOT x = 1'
 808
 809        Args:
 810            copy: whether to copy this object.
 811
 812        Returns:
 813            The new Not instance.
 814        """
 815        return not_(self, copy=copy)
 816
 817    def as_(
 818        self,
 819        alias: str | Identifier,
 820        quoted: t.Optional[bool] = None,
 821        dialect: DialectType = None,
 822        copy: bool = True,
 823        **opts,
 824    ) -> Alias:
 825        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 826
 827    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 828        this = self.copy()
 829        other = convert(other, copy=True)
 830        if not isinstance(this, klass) and not isinstance(other, klass):
 831            this = _wrap(this, Binary)
 832            other = _wrap(other, Binary)
 833        if reverse:
 834            return klass(this=other, expression=this)
 835        return klass(this=this, expression=other)
 836
 837    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 838        return Bracket(
 839            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 840        )
 841
 842    def __iter__(self) -> t.Iterator:
 843        if "expressions" in self.arg_types:
 844            return iter(self.args.get("expressions") or [])
 845        # We define this because __getitem__ converts Expression into an iterable, which is
 846        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 847        # See: https://peps.python.org/pep-0234/
 848        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 849
 850    def isin(
 851        self,
 852        *expressions: t.Any,
 853        query: t.Optional[ExpOrStr] = None,
 854        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 855        copy: bool = True,
 856        **opts,
 857    ) -> In:
 858        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 859        if subquery and not isinstance(subquery, Subquery):
 860            subquery = subquery.subquery(copy=False)
 861
 862        return In(
 863            this=maybe_copy(self, copy),
 864            expressions=[convert(e, copy=copy) for e in expressions],
 865            query=subquery,
 866            unnest=(
 867                Unnest(
 868                    expressions=[
 869                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 870                        for e in ensure_list(unnest)
 871                    ]
 872                )
 873                if unnest
 874                else None
 875            ),
 876        )
 877
 878    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 879        return Between(
 880            this=maybe_copy(self, copy),
 881            low=convert(low, copy=copy, **opts),
 882            high=convert(high, copy=copy, **opts),
 883        )
 884
 885    def is_(self, other: ExpOrStr) -> Is:
 886        return self._binop(Is, other)
 887
 888    def like(self, other: ExpOrStr) -> Like:
 889        return self._binop(Like, other)
 890
 891    def ilike(self, other: ExpOrStr) -> ILike:
 892        return self._binop(ILike, other)
 893
 894    def eq(self, other: t.Any) -> EQ:
 895        return self._binop(EQ, other)
 896
 897    def neq(self, other: t.Any) -> NEQ:
 898        return self._binop(NEQ, other)
 899
 900    def rlike(self, other: ExpOrStr) -> RegexpLike:
 901        return self._binop(RegexpLike, other)
 902
 903    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 904        div = self._binop(Div, other)
 905        div.args["typed"] = typed
 906        div.args["safe"] = safe
 907        return div
 908
 909    def asc(self, nulls_first: bool = True) -> Ordered:
 910        return Ordered(this=self.copy(), nulls_first=nulls_first)
 911
 912    def desc(self, nulls_first: bool = False) -> Ordered:
 913        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 914
 915    def __lt__(self, other: t.Any) -> LT:
 916        return self._binop(LT, other)
 917
 918    def __le__(self, other: t.Any) -> LTE:
 919        return self._binop(LTE, other)
 920
 921    def __gt__(self, other: t.Any) -> GT:
 922        return self._binop(GT, other)
 923
 924    def __ge__(self, other: t.Any) -> GTE:
 925        return self._binop(GTE, other)
 926
 927    def __add__(self, other: t.Any) -> Add:
 928        return self._binop(Add, other)
 929
 930    def __radd__(self, other: t.Any) -> Add:
 931        return self._binop(Add, other, reverse=True)
 932
 933    def __sub__(self, other: t.Any) -> Sub:
 934        return self._binop(Sub, other)
 935
 936    def __rsub__(self, other: t.Any) -> Sub:
 937        return self._binop(Sub, other, reverse=True)
 938
 939    def __mul__(self, other: t.Any) -> Mul:
 940        return self._binop(Mul, other)
 941
 942    def __rmul__(self, other: t.Any) -> Mul:
 943        return self._binop(Mul, other, reverse=True)
 944
 945    def __truediv__(self, other: t.Any) -> Div:
 946        return self._binop(Div, other)
 947
 948    def __rtruediv__(self, other: t.Any) -> Div:
 949        return self._binop(Div, other, reverse=True)
 950
 951    def __floordiv__(self, other: t.Any) -> IntDiv:
 952        return self._binop(IntDiv, other)
 953
 954    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 955        return self._binop(IntDiv, other, reverse=True)
 956
 957    def __mod__(self, other: t.Any) -> Mod:
 958        return self._binop(Mod, other)
 959
 960    def __rmod__(self, other: t.Any) -> Mod:
 961        return self._binop(Mod, other, reverse=True)
 962
 963    def __pow__(self, other: t.Any) -> Pow:
 964        return self._binop(Pow, other)
 965
 966    def __rpow__(self, other: t.Any) -> Pow:
 967        return self._binop(Pow, other, reverse=True)
 968
 969    def __and__(self, other: t.Any) -> And:
 970        return self._binop(And, other)
 971
 972    def __rand__(self, other: t.Any) -> And:
 973        return self._binop(And, other, reverse=True)
 974
 975    def __or__(self, other: t.Any) -> Or:
 976        return self._binop(Or, other)
 977
 978    def __ror__(self, other: t.Any) -> Or:
 979        return self._binop(Or, other, reverse=True)
 980
 981    def __neg__(self) -> Neg:
 982        return Neg(this=_wrap(self.copy(), Binary))
 983
 984    def __invert__(self) -> Not:
 985        return not_(self.copy())
 986
 987
 988IntoType = t.Union[
 989    str,
 990    t.Type[Expression],
 991    t.Collection[t.Union[str, t.Type[Expression]]],
 992]
 993ExpOrStr = t.Union[str, Expression]
 994
 995
 996class Condition(Expression):
 997    """Logical conditions like x AND y, or simply x"""
 998
 999
1000class Predicate(Condition):
1001    """Relationships like x = y, x > 1, x >= y."""
1002
1003
1004class DerivedTable(Expression):
1005    @property
1006    def selects(self) -> t.List[Expression]:
1007        return self.this.selects if isinstance(self.this, Query) else []
1008
1009    @property
1010    def named_selects(self) -> t.List[str]:
1011        return [select.output_name for select in self.selects]
1012
1013
1014class Query(Expression):
1015    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1016        """
1017        Returns a `Subquery` that wraps around this query.
1018
1019        Example:
1020            >>> subquery = Select().select("x").from_("tbl").subquery()
1021            >>> Select().select("x").from_(subquery).sql()
1022            'SELECT x FROM (SELECT x FROM tbl)'
1023
1024        Args:
1025            alias: an optional alias for the subquery.
1026            copy: if `False`, modify this expression instance in-place.
1027        """
1028        instance = maybe_copy(self, copy)
1029        if not isinstance(alias, Expression):
1030            alias = TableAlias(this=to_identifier(alias)) if alias else None
1031
1032        return Subquery(this=instance, alias=alias)
1033
1034    def limit(
1035        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1036    ) -> Q:
1037        """
1038        Adds a LIMIT clause to this query.
1039
1040        Example:
1041            >>> select("1").union(select("1")).limit(1).sql()
1042            'SELECT 1 UNION SELECT 1 LIMIT 1'
1043
1044        Args:
1045            expression: the SQL code string to parse.
1046                This can also be an integer.
1047                If a `Limit` instance is passed, it will be used as-is.
1048                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1049            dialect: the dialect used to parse the input expression.
1050            copy: if `False`, modify this expression instance in-place.
1051            opts: other options to use to parse the input expressions.
1052
1053        Returns:
1054            A limited Select expression.
1055        """
1056        return _apply_builder(
1057            expression=expression,
1058            instance=self,
1059            arg="limit",
1060            into=Limit,
1061            prefix="LIMIT",
1062            dialect=dialect,
1063            copy=copy,
1064            into_arg="expression",
1065            **opts,
1066        )
1067
1068    def offset(
1069        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1070    ) -> Q:
1071        """
1072        Set the OFFSET expression.
1073
1074        Example:
1075            >>> Select().from_("tbl").select("x").offset(10).sql()
1076            'SELECT x FROM tbl OFFSET 10'
1077
1078        Args:
1079            expression: the SQL code string to parse.
1080                This can also be an integer.
1081                If a `Offset` instance is passed, this is used as-is.
1082                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1083            dialect: the dialect used to parse the input expression.
1084            copy: if `False`, modify this expression instance in-place.
1085            opts: other options to use to parse the input expressions.
1086
1087        Returns:
1088            The modified Select expression.
1089        """
1090        return _apply_builder(
1091            expression=expression,
1092            instance=self,
1093            arg="offset",
1094            into=Offset,
1095            prefix="OFFSET",
1096            dialect=dialect,
1097            copy=copy,
1098            into_arg="expression",
1099            **opts,
1100        )
1101
1102    def order_by(
1103        self: Q,
1104        *expressions: t.Optional[ExpOrStr],
1105        append: bool = True,
1106        dialect: DialectType = None,
1107        copy: bool = True,
1108        **opts,
1109    ) -> Q:
1110        """
1111        Set the ORDER BY expression.
1112
1113        Example:
1114            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1115            'SELECT x FROM tbl ORDER BY x DESC'
1116
1117        Args:
1118            *expressions: the SQL code strings to parse.
1119                If a `Group` instance is passed, this is used as-is.
1120                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1121            append: if `True`, add to any existing expressions.
1122                Otherwise, this flattens all the `Order` expression into a single expression.
1123            dialect: the dialect used to parse the input expression.
1124            copy: if `False`, modify this expression instance in-place.
1125            opts: other options to use to parse the input expressions.
1126
1127        Returns:
1128            The modified Select expression.
1129        """
1130        return _apply_child_list_builder(
1131            *expressions,
1132            instance=self,
1133            arg="order",
1134            append=append,
1135            copy=copy,
1136            prefix="ORDER BY",
1137            into=Order,
1138            dialect=dialect,
1139            **opts,
1140        )
1141
1142    @property
1143    def ctes(self) -> t.List[CTE]:
1144        """Returns a list of all the CTEs attached to this query."""
1145        with_ = self.args.get("with")
1146        return with_.expressions if with_ else []
1147
1148    @property
1149    def selects(self) -> t.List[Expression]:
1150        """Returns the query's projections."""
1151        raise NotImplementedError("Query objects must implement `selects`")
1152
1153    @property
1154    def named_selects(self) -> t.List[str]:
1155        """Returns the output names of the query's projections."""
1156        raise NotImplementedError("Query objects must implement `named_selects`")
1157
1158    def select(
1159        self: Q,
1160        *expressions: t.Optional[ExpOrStr],
1161        append: bool = True,
1162        dialect: DialectType = None,
1163        copy: bool = True,
1164        **opts,
1165    ) -> Q:
1166        """
1167        Append to or set the SELECT expressions.
1168
1169        Example:
1170            >>> Select().select("x", "y").sql()
1171            'SELECT x, y'
1172
1173        Args:
1174            *expressions: the SQL code strings to parse.
1175                If an `Expression` instance is passed, it will be used as-is.
1176            append: if `True`, add to any existing expressions.
1177                Otherwise, this resets the expressions.
1178            dialect: the dialect used to parse the input expressions.
1179            copy: if `False`, modify this expression instance in-place.
1180            opts: other options to use to parse the input expressions.
1181
1182        Returns:
1183            The modified Query expression.
1184        """
1185        raise NotImplementedError("Query objects must implement `select`")
1186
1187    def with_(
1188        self: Q,
1189        alias: ExpOrStr,
1190        as_: ExpOrStr,
1191        recursive: t.Optional[bool] = None,
1192        append: bool = True,
1193        dialect: DialectType = None,
1194        copy: bool = True,
1195        **opts,
1196    ) -> Q:
1197        """
1198        Append to or set the common table expressions.
1199
1200        Example:
1201            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1202            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1203
1204        Args:
1205            alias: the SQL code string to parse as the table name.
1206                If an `Expression` instance is passed, this is used as-is.
1207            as_: the SQL code string to parse as the table expression.
1208                If an `Expression` instance is passed, it will be used as-is.
1209            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1210            append: if `True`, add to any existing expressions.
1211                Otherwise, this resets the expressions.
1212            dialect: the dialect used to parse the input expression.
1213            copy: if `False`, modify this expression instance in-place.
1214            opts: other options to use to parse the input expressions.
1215
1216        Returns:
1217            The modified expression.
1218        """
1219        return _apply_cte_builder(
1220            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1221        )
1222
1223    def union(
1224        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1225    ) -> Union:
1226        """
1227        Builds a UNION expression.
1228
1229        Example:
1230            >>> import sqlglot
1231            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1232            'SELECT * FROM foo UNION SELECT * FROM bla'
1233
1234        Args:
1235            expression: the SQL code string.
1236                If an `Expression` instance is passed, it will be used as-is.
1237            distinct: set the DISTINCT flag if and only if this is true.
1238            dialect: the dialect used to parse the input expression.
1239            opts: other options to use to parse the input expressions.
1240
1241        Returns:
1242            The new Union expression.
1243        """
1244        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1245
1246    def intersect(
1247        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1248    ) -> Intersect:
1249        """
1250        Builds an INTERSECT expression.
1251
1252        Example:
1253            >>> import sqlglot
1254            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1255            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1256
1257        Args:
1258            expression: the SQL code string.
1259                If an `Expression` instance is passed, it will be used as-is.
1260            distinct: set the DISTINCT flag if and only if this is true.
1261            dialect: the dialect used to parse the input expression.
1262            opts: other options to use to parse the input expressions.
1263
1264        Returns:
1265            The new Intersect expression.
1266        """
1267        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1268
1269    def except_(
1270        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1271    ) -> Except:
1272        """
1273        Builds an EXCEPT expression.
1274
1275        Example:
1276            >>> import sqlglot
1277            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1278            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1279
1280        Args:
1281            expression: the SQL code string.
1282                If an `Expression` instance is passed, it will be used as-is.
1283            distinct: set the DISTINCT flag if and only if this is true.
1284            dialect: the dialect used to parse the input expression.
1285            opts: other options to use to parse the input expressions.
1286
1287        Returns:
1288            The new Except expression.
1289        """
1290        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1291
1292
1293class UDTF(DerivedTable):
1294    @property
1295    def selects(self) -> t.List[Expression]:
1296        alias = self.args.get("alias")
1297        return alias.columns if alias else []
1298
1299
1300class Cache(Expression):
1301    arg_types = {
1302        "this": True,
1303        "lazy": False,
1304        "options": False,
1305        "expression": False,
1306    }
1307
1308
1309class Uncache(Expression):
1310    arg_types = {"this": True, "exists": False}
1311
1312
1313class Refresh(Expression):
1314    pass
1315
1316
1317class DDL(Expression):
1318    @property
1319    def ctes(self) -> t.List[CTE]:
1320        """Returns a list of all the CTEs attached to this statement."""
1321        with_ = self.args.get("with")
1322        return with_.expressions if with_ else []
1323
1324    @property
1325    def selects(self) -> t.List[Expression]:
1326        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1327        return self.expression.selects if isinstance(self.expression, Query) else []
1328
1329    @property
1330    def named_selects(self) -> t.List[str]:
1331        """
1332        If this statement contains a query (e.g. a CTAS), this returns the output
1333        names of the query's projections.
1334        """
1335        return self.expression.named_selects if isinstance(self.expression, Query) else []
1336
1337
1338class DML(Expression):
1339    def returning(
1340        self,
1341        expression: ExpOrStr,
1342        dialect: DialectType = None,
1343        copy: bool = True,
1344        **opts,
1345    ) -> DML:
1346        """
1347        Set the RETURNING expression. Not supported by all dialects.
1348
1349        Example:
1350            >>> delete("tbl").returning("*", dialect="postgres").sql()
1351            'DELETE FROM tbl RETURNING *'
1352
1353        Args:
1354            expression: the SQL code strings to parse.
1355                If an `Expression` instance is passed, it will be used as-is.
1356            dialect: the dialect used to parse the input expressions.
1357            copy: if `False`, modify this expression instance in-place.
1358            opts: other options to use to parse the input expressions.
1359
1360        Returns:
1361            Delete: the modified expression.
1362        """
1363        return _apply_builder(
1364            expression=expression,
1365            instance=self,
1366            arg="returning",
1367            prefix="RETURNING",
1368            dialect=dialect,
1369            copy=copy,
1370            into=Returning,
1371            **opts,
1372        )
1373
1374
1375class Create(DDL):
1376    arg_types = {
1377        "with": False,
1378        "this": True,
1379        "kind": True,
1380        "expression": False,
1381        "exists": False,
1382        "properties": False,
1383        "replace": False,
1384        "unique": False,
1385        "indexes": False,
1386        "no_schema_binding": False,
1387        "begin": False,
1388        "end": False,
1389        "clone": False,
1390    }
1391
1392    @property
1393    def kind(self) -> t.Optional[str]:
1394        kind = self.args.get("kind")
1395        return kind and kind.upper()
1396
1397
1398class SequenceProperties(Expression):
1399    arg_types = {
1400        "increment": False,
1401        "minvalue": False,
1402        "maxvalue": False,
1403        "cache": False,
1404        "start": False,
1405        "owned": False,
1406        "options": False,
1407    }
1408
1409
1410class TruncateTable(Expression):
1411    arg_types = {
1412        "expressions": True,
1413        "is_database": False,
1414        "exists": False,
1415        "only": False,
1416        "cluster": False,
1417        "identity": False,
1418        "option": False,
1419        "partition": False,
1420    }
1421
1422
1423# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1424# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1425# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1426class Clone(Expression):
1427    arg_types = {"this": True, "shallow": False, "copy": False}
1428
1429
1430class Describe(Expression):
1431    arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
1432
1433
1434class Kill(Expression):
1435    arg_types = {"this": True, "kind": False}
1436
1437
1438class Pragma(Expression):
1439    pass
1440
1441
1442class Set(Expression):
1443    arg_types = {"expressions": False, "unset": False, "tag": False}
1444
1445
1446class Heredoc(Expression):
1447    arg_types = {"this": True, "tag": False}
1448
1449
1450class SetItem(Expression):
1451    arg_types = {
1452        "this": False,
1453        "expressions": False,
1454        "kind": False,
1455        "collate": False,  # MySQL SET NAMES statement
1456        "global": False,
1457    }
1458
1459
1460class Show(Expression):
1461    arg_types = {
1462        "this": True,
1463        "history": False,
1464        "terse": False,
1465        "target": False,
1466        "offset": False,
1467        "starts_with": False,
1468        "limit": False,
1469        "from": False,
1470        "like": False,
1471        "where": False,
1472        "db": False,
1473        "scope": False,
1474        "scope_kind": False,
1475        "full": False,
1476        "mutex": False,
1477        "query": False,
1478        "channel": False,
1479        "global": False,
1480        "log": False,
1481        "position": False,
1482        "types": False,
1483    }
1484
1485
1486class UserDefinedFunction(Expression):
1487    arg_types = {"this": True, "expressions": False, "wrapped": False}
1488
1489
1490class CharacterSet(Expression):
1491    arg_types = {"this": True, "default": False}
1492
1493
1494class With(Expression):
1495    arg_types = {"expressions": True, "recursive": False}
1496
1497    @property
1498    def recursive(self) -> bool:
1499        return bool(self.args.get("recursive"))
1500
1501
1502class WithinGroup(Expression):
1503    arg_types = {"this": True, "expression": False}
1504
1505
1506# clickhouse supports scalar ctes
1507# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1508class CTE(DerivedTable):
1509    arg_types = {
1510        "this": True,
1511        "alias": True,
1512        "scalar": False,
1513        "materialized": False,
1514    }
1515
1516
1517class TableAlias(Expression):
1518    arg_types = {"this": False, "columns": False}
1519
1520    @property
1521    def columns(self):
1522        return self.args.get("columns") or []
1523
1524
1525class BitString(Condition):
1526    pass
1527
1528
1529class HexString(Condition):
1530    pass
1531
1532
1533class ByteString(Condition):
1534    pass
1535
1536
1537class RawString(Condition):
1538    pass
1539
1540
1541class UnicodeString(Condition):
1542    arg_types = {"this": True, "escape": False}
1543
1544
1545class Column(Condition):
1546    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1547
1548    @property
1549    def table(self) -> str:
1550        return self.text("table")
1551
1552    @property
1553    def db(self) -> str:
1554        return self.text("db")
1555
1556    @property
1557    def catalog(self) -> str:
1558        return self.text("catalog")
1559
1560    @property
1561    def output_name(self) -> str:
1562        return self.name
1563
1564    @property
1565    def parts(self) -> t.List[Identifier]:
1566        """Return the parts of a column in order catalog, db, table, name."""
1567        return [
1568            t.cast(Identifier, self.args[part])
1569            for part in ("catalog", "db", "table", "this")
1570            if self.args.get(part)
1571        ]
1572
1573    def to_dot(self) -> Dot | Identifier:
1574        """Converts the column into a dot expression."""
1575        parts = self.parts
1576        parent = self.parent
1577
1578        while parent:
1579            if isinstance(parent, Dot):
1580                parts.append(parent.expression)
1581            parent = parent.parent
1582
1583        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1584
1585
1586class ColumnPosition(Expression):
1587    arg_types = {"this": False, "position": True}
1588
1589
1590class ColumnDef(Expression):
1591    arg_types = {
1592        "this": True,
1593        "kind": False,
1594        "constraints": False,
1595        "exists": False,
1596        "position": False,
1597    }
1598
1599    @property
1600    def constraints(self) -> t.List[ColumnConstraint]:
1601        return self.args.get("constraints") or []
1602
1603    @property
1604    def kind(self) -> t.Optional[DataType]:
1605        return self.args.get("kind")
1606
1607
1608class AlterColumn(Expression):
1609    arg_types = {
1610        "this": True,
1611        "dtype": False,
1612        "collate": False,
1613        "using": False,
1614        "default": False,
1615        "drop": False,
1616        "comment": False,
1617    }
1618
1619
1620class RenameColumn(Expression):
1621    arg_types = {"this": True, "to": True, "exists": False}
1622
1623
1624class RenameTable(Expression):
1625    pass
1626
1627
1628class SwapTable(Expression):
1629    pass
1630
1631
1632class Comment(Expression):
1633    arg_types = {
1634        "this": True,
1635        "kind": True,
1636        "expression": True,
1637        "exists": False,
1638        "materialized": False,
1639    }
1640
1641
1642class Comprehension(Expression):
1643    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1644
1645
1646# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1647class MergeTreeTTLAction(Expression):
1648    arg_types = {
1649        "this": True,
1650        "delete": False,
1651        "recompress": False,
1652        "to_disk": False,
1653        "to_volume": False,
1654    }
1655
1656
1657# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1658class MergeTreeTTL(Expression):
1659    arg_types = {
1660        "expressions": True,
1661        "where": False,
1662        "group": False,
1663        "aggregates": False,
1664    }
1665
1666
1667# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1668class IndexConstraintOption(Expression):
1669    arg_types = {
1670        "key_block_size": False,
1671        "using": False,
1672        "parser": False,
1673        "comment": False,
1674        "visible": False,
1675        "engine_attr": False,
1676        "secondary_engine_attr": False,
1677    }
1678
1679
1680class ColumnConstraint(Expression):
1681    arg_types = {"this": False, "kind": True}
1682
1683    @property
1684    def kind(self) -> ColumnConstraintKind:
1685        return self.args["kind"]
1686
1687
1688class ColumnConstraintKind(Expression):
1689    pass
1690
1691
1692class AutoIncrementColumnConstraint(ColumnConstraintKind):
1693    pass
1694
1695
1696class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1697    arg_types = {"this": True, "expression": True}
1698
1699
1700class CaseSpecificColumnConstraint(ColumnConstraintKind):
1701    arg_types = {"not_": True}
1702
1703
1704class CharacterSetColumnConstraint(ColumnConstraintKind):
1705    arg_types = {"this": True}
1706
1707
1708class CheckColumnConstraint(ColumnConstraintKind):
1709    arg_types = {"this": True, "enforced": False}
1710
1711
1712class ClusteredColumnConstraint(ColumnConstraintKind):
1713    pass
1714
1715
1716class CollateColumnConstraint(ColumnConstraintKind):
1717    pass
1718
1719
1720class CommentColumnConstraint(ColumnConstraintKind):
1721    pass
1722
1723
1724class CompressColumnConstraint(ColumnConstraintKind):
1725    pass
1726
1727
1728class DateFormatColumnConstraint(ColumnConstraintKind):
1729    arg_types = {"this": True}
1730
1731
1732class DefaultColumnConstraint(ColumnConstraintKind):
1733    pass
1734
1735
1736class EncodeColumnConstraint(ColumnConstraintKind):
1737    pass
1738
1739
1740# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1741class ExcludeColumnConstraint(ColumnConstraintKind):
1742    pass
1743
1744
1745class EphemeralColumnConstraint(ColumnConstraintKind):
1746    arg_types = {"this": False}
1747
1748
1749class WithOperator(Expression):
1750    arg_types = {"this": True, "op": True}
1751
1752
1753class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1754    # this: True -> ALWAYS, this: False -> BY DEFAULT
1755    arg_types = {
1756        "this": False,
1757        "expression": False,
1758        "on_null": False,
1759        "start": False,
1760        "increment": False,
1761        "minvalue": False,
1762        "maxvalue": False,
1763        "cycle": False,
1764    }
1765
1766
1767class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1768    arg_types = {"start": False, "hidden": False}
1769
1770
1771# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1772# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1773class IndexColumnConstraint(ColumnConstraintKind):
1774    arg_types = {
1775        "this": False,
1776        "expressions": False,
1777        "kind": False,
1778        "index_type": False,
1779        "options": False,
1780        "expression": False,  # Clickhouse
1781        "granularity": False,
1782    }
1783
1784
1785class InlineLengthColumnConstraint(ColumnConstraintKind):
1786    pass
1787
1788
1789class NonClusteredColumnConstraint(ColumnConstraintKind):
1790    pass
1791
1792
1793class NotForReplicationColumnConstraint(ColumnConstraintKind):
1794    arg_types = {}
1795
1796
1797class NotNullColumnConstraint(ColumnConstraintKind):
1798    arg_types = {"allow_null": False}
1799
1800
1801# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1802class OnUpdateColumnConstraint(ColumnConstraintKind):
1803    pass
1804
1805
1806# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1807class TransformColumnConstraint(ColumnConstraintKind):
1808    pass
1809
1810
1811class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1812    arg_types = {"desc": False}
1813
1814
1815class TitleColumnConstraint(ColumnConstraintKind):
1816    pass
1817
1818
1819class UniqueColumnConstraint(ColumnConstraintKind):
1820    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1821
1822
1823class UppercaseColumnConstraint(ColumnConstraintKind):
1824    arg_types: t.Dict[str, t.Any] = {}
1825
1826
1827class PathColumnConstraint(ColumnConstraintKind):
1828    pass
1829
1830
1831# computed column expression
1832# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1833class ComputedColumnConstraint(ColumnConstraintKind):
1834    arg_types = {"this": True, "persisted": False, "not_null": False}
1835
1836
1837class Constraint(Expression):
1838    arg_types = {"this": True, "expressions": True}
1839
1840
1841class Delete(DML):
1842    arg_types = {
1843        "with": False,
1844        "this": False,
1845        "using": False,
1846        "where": False,
1847        "returning": False,
1848        "limit": False,
1849        "tables": False,  # Multiple-Table Syntax (MySQL)
1850    }
1851
1852    def delete(
1853        self,
1854        table: ExpOrStr,
1855        dialect: DialectType = None,
1856        copy: bool = True,
1857        **opts,
1858    ) -> Delete:
1859        """
1860        Create a DELETE expression or replace the table on an existing DELETE expression.
1861
1862        Example:
1863            >>> delete("tbl").sql()
1864            'DELETE FROM tbl'
1865
1866        Args:
1867            table: the table from which to delete.
1868            dialect: the dialect used to parse the input expression.
1869            copy: if `False`, modify this expression instance in-place.
1870            opts: other options to use to parse the input expressions.
1871
1872        Returns:
1873            Delete: the modified expression.
1874        """
1875        return _apply_builder(
1876            expression=table,
1877            instance=self,
1878            arg="this",
1879            dialect=dialect,
1880            into=Table,
1881            copy=copy,
1882            **opts,
1883        )
1884
1885    def where(
1886        self,
1887        *expressions: t.Optional[ExpOrStr],
1888        append: bool = True,
1889        dialect: DialectType = None,
1890        copy: bool = True,
1891        **opts,
1892    ) -> Delete:
1893        """
1894        Append to or set the WHERE expressions.
1895
1896        Example:
1897            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1898            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1899
1900        Args:
1901            *expressions: the SQL code strings to parse.
1902                If an `Expression` instance is passed, it will be used as-is.
1903                Multiple expressions are combined with an AND operator.
1904            append: if `True`, AND the new expressions to any existing expression.
1905                Otherwise, this resets the expression.
1906            dialect: the dialect used to parse the input expressions.
1907            copy: if `False`, modify this expression instance in-place.
1908            opts: other options to use to parse the input expressions.
1909
1910        Returns:
1911            Delete: the modified expression.
1912        """
1913        return _apply_conjunction_builder(
1914            *expressions,
1915            instance=self,
1916            arg="where",
1917            append=append,
1918            into=Where,
1919            dialect=dialect,
1920            copy=copy,
1921            **opts,
1922        )
1923
1924
1925class Drop(Expression):
1926    arg_types = {
1927        "this": False,
1928        "kind": False,
1929        "expressions": False,
1930        "exists": False,
1931        "temporary": False,
1932        "materialized": False,
1933        "cascade": False,
1934        "constraints": False,
1935        "purge": False,
1936    }
1937
1938
1939class Filter(Expression):
1940    arg_types = {"this": True, "expression": True}
1941
1942
1943class Check(Expression):
1944    pass
1945
1946
1947# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1948class Connect(Expression):
1949    arg_types = {"start": False, "connect": True, "nocycle": False}
1950
1951
1952class Prior(Expression):
1953    pass
1954
1955
1956class Directory(Expression):
1957    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1958    arg_types = {"this": True, "local": False, "row_format": False}
1959
1960
1961class ForeignKey(Expression):
1962    arg_types = {
1963        "expressions": True,
1964        "reference": False,
1965        "delete": False,
1966        "update": False,
1967    }
1968
1969
1970class ColumnPrefix(Expression):
1971    arg_types = {"this": True, "expression": True}
1972
1973
1974class PrimaryKey(Expression):
1975    arg_types = {"expressions": True, "options": False}
1976
1977
1978# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1979# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1980class Into(Expression):
1981    arg_types = {"this": True, "temporary": False, "unlogged": False}
1982
1983
1984class From(Expression):
1985    @property
1986    def name(self) -> str:
1987        return self.this.name
1988
1989    @property
1990    def alias_or_name(self) -> str:
1991        return self.this.alias_or_name
1992
1993
1994class Having(Expression):
1995    pass
1996
1997
1998class Hint(Expression):
1999    arg_types = {"expressions": True}
2000
2001
2002class JoinHint(Expression):
2003    arg_types = {"this": True, "expressions": True}
2004
2005
2006class Identifier(Expression):
2007    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2008
2009    @property
2010    def quoted(self) -> bool:
2011        return bool(self.args.get("quoted"))
2012
2013    @property
2014    def hashable_args(self) -> t.Any:
2015        return (self.this, self.quoted)
2016
2017    @property
2018    def output_name(self) -> str:
2019        return self.name
2020
2021
2022# https://www.postgresql.org/docs/current/indexes-opclass.html
2023class Opclass(Expression):
2024    arg_types = {"this": True, "expression": True}
2025
2026
2027class Index(Expression):
2028    arg_types = {
2029        "this": False,
2030        "table": False,
2031        "unique": False,
2032        "primary": False,
2033        "amp": False,  # teradata
2034        "params": False,
2035    }
2036
2037
2038class IndexParameters(Expression):
2039    arg_types = {
2040        "using": False,
2041        "include": False,
2042        "columns": False,
2043        "with_storage": False,
2044        "partition_by": False,
2045        "tablespace": False,
2046        "where": False,
2047    }
2048
2049
2050class Insert(DDL, DML):
2051    arg_types = {
2052        "hint": False,
2053        "with": False,
2054        "is_function": False,
2055        "this": True,
2056        "expression": False,
2057        "conflict": False,
2058        "returning": False,
2059        "overwrite": False,
2060        "exists": False,
2061        "alternative": False,
2062        "where": False,
2063        "ignore": False,
2064        "by_name": False,
2065        "stored": False,
2066    }
2067
2068    def with_(
2069        self,
2070        alias: ExpOrStr,
2071        as_: ExpOrStr,
2072        recursive: t.Optional[bool] = None,
2073        append: bool = True,
2074        dialect: DialectType = None,
2075        copy: bool = True,
2076        **opts,
2077    ) -> Insert:
2078        """
2079        Append to or set the common table expressions.
2080
2081        Example:
2082            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2083            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2084
2085        Args:
2086            alias: the SQL code string to parse as the table name.
2087                If an `Expression` instance is passed, this is used as-is.
2088            as_: the SQL code string to parse as the table expression.
2089                If an `Expression` instance is passed, it will be used as-is.
2090            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2091            append: if `True`, add to any existing expressions.
2092                Otherwise, this resets the expressions.
2093            dialect: the dialect used to parse the input expression.
2094            copy: if `False`, modify this expression instance in-place.
2095            opts: other options to use to parse the input expressions.
2096
2097        Returns:
2098            The modified expression.
2099        """
2100        return _apply_cte_builder(
2101            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2102        )
2103
2104
2105class OnConflict(Expression):
2106    arg_types = {
2107        "duplicate": False,
2108        "expressions": False,
2109        "action": False,
2110        "conflict_keys": False,
2111        "constraint": False,
2112    }
2113
2114
2115class Returning(Expression):
2116    arg_types = {"expressions": True, "into": False}
2117
2118
2119# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2120class Introducer(Expression):
2121    arg_types = {"this": True, "expression": True}
2122
2123
2124# national char, like n'utf8'
2125class National(Expression):
2126    pass
2127
2128
2129class LoadData(Expression):
2130    arg_types = {
2131        "this": True,
2132        "local": False,
2133        "overwrite": False,
2134        "inpath": True,
2135        "partition": False,
2136        "input_format": False,
2137        "serde": False,
2138    }
2139
2140
2141class Partition(Expression):
2142    arg_types = {"expressions": True}
2143
2144
2145class PartitionRange(Expression):
2146    arg_types = {"this": True, "expression": True}
2147
2148
2149class Fetch(Expression):
2150    arg_types = {
2151        "direction": False,
2152        "count": False,
2153        "percent": False,
2154        "with_ties": False,
2155    }
2156
2157
2158class Group(Expression):
2159    arg_types = {
2160        "expressions": False,
2161        "grouping_sets": False,
2162        "cube": False,
2163        "rollup": False,
2164        "totals": False,
2165        "all": False,
2166    }
2167
2168
2169class Lambda(Expression):
2170    arg_types = {"this": True, "expressions": True}
2171
2172
2173class Limit(Expression):
2174    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2175
2176
2177class Literal(Condition):
2178    arg_types = {"this": True, "is_string": True}
2179
2180    @property
2181    def hashable_args(self) -> t.Any:
2182        return (self.this, self.args.get("is_string"))
2183
2184    @classmethod
2185    def number(cls, number) -> Literal:
2186        return cls(this=str(number), is_string=False)
2187
2188    @classmethod
2189    def string(cls, string) -> Literal:
2190        return cls(this=str(string), is_string=True)
2191
2192    @property
2193    def output_name(self) -> str:
2194        return self.name
2195
2196
2197class Join(Expression):
2198    arg_types = {
2199        "this": True,
2200        "on": False,
2201        "side": False,
2202        "kind": False,
2203        "using": False,
2204        "method": False,
2205        "global": False,
2206        "hint": False,
2207        "match_condition": False,  # Snowflake
2208    }
2209
2210    @property
2211    def method(self) -> str:
2212        return self.text("method").upper()
2213
2214    @property
2215    def kind(self) -> str:
2216        return self.text("kind").upper()
2217
2218    @property
2219    def side(self) -> str:
2220        return self.text("side").upper()
2221
2222    @property
2223    def hint(self) -> str:
2224        return self.text("hint").upper()
2225
2226    @property
2227    def alias_or_name(self) -> str:
2228        return self.this.alias_or_name
2229
2230    def on(
2231        self,
2232        *expressions: t.Optional[ExpOrStr],
2233        append: bool = True,
2234        dialect: DialectType = None,
2235        copy: bool = True,
2236        **opts,
2237    ) -> Join:
2238        """
2239        Append to or set the ON expressions.
2240
2241        Example:
2242            >>> import sqlglot
2243            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2244            'JOIN x ON y = 1'
2245
2246        Args:
2247            *expressions: the SQL code strings to parse.
2248                If an `Expression` instance is passed, it will be used as-is.
2249                Multiple expressions are combined with an AND operator.
2250            append: if `True`, AND the new expressions to any existing expression.
2251                Otherwise, this resets the expression.
2252            dialect: the dialect used to parse the input expressions.
2253            copy: if `False`, modify this expression instance in-place.
2254            opts: other options to use to parse the input expressions.
2255
2256        Returns:
2257            The modified Join expression.
2258        """
2259        join = _apply_conjunction_builder(
2260            *expressions,
2261            instance=self,
2262            arg="on",
2263            append=append,
2264            dialect=dialect,
2265            copy=copy,
2266            **opts,
2267        )
2268
2269        if join.kind == "CROSS":
2270            join.set("kind", None)
2271
2272        return join
2273
2274    def using(
2275        self,
2276        *expressions: t.Optional[ExpOrStr],
2277        append: bool = True,
2278        dialect: DialectType = None,
2279        copy: bool = True,
2280        **opts,
2281    ) -> Join:
2282        """
2283        Append to or set the USING expressions.
2284
2285        Example:
2286            >>> import sqlglot
2287            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2288            'JOIN x USING (foo, bla)'
2289
2290        Args:
2291            *expressions: the SQL code strings to parse.
2292                If an `Expression` instance is passed, it will be used as-is.
2293            append: if `True`, concatenate the new expressions to the existing "using" list.
2294                Otherwise, this resets the expression.
2295            dialect: the dialect used to parse the input expressions.
2296            copy: if `False`, modify this expression instance in-place.
2297            opts: other options to use to parse the input expressions.
2298
2299        Returns:
2300            The modified Join expression.
2301        """
2302        join = _apply_list_builder(
2303            *expressions,
2304            instance=self,
2305            arg="using",
2306            append=append,
2307            dialect=dialect,
2308            copy=copy,
2309            **opts,
2310        )
2311
2312        if join.kind == "CROSS":
2313            join.set("kind", None)
2314
2315        return join
2316
2317
2318class Lateral(UDTF):
2319    arg_types = {
2320        "this": True,
2321        "view": False,
2322        "outer": False,
2323        "alias": False,
2324        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2325    }
2326
2327
2328class MatchRecognizeMeasure(Expression):
2329    arg_types = {
2330        "this": True,
2331        "window_frame": False,
2332    }
2333
2334
2335class MatchRecognize(Expression):
2336    arg_types = {
2337        "partition_by": False,
2338        "order": False,
2339        "measures": False,
2340        "rows": False,
2341        "after": False,
2342        "pattern": False,
2343        "define": False,
2344        "alias": False,
2345    }
2346
2347
2348# Clickhouse FROM FINAL modifier
2349# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2350class Final(Expression):
2351    pass
2352
2353
2354class Offset(Expression):
2355    arg_types = {"this": False, "expression": True, "expressions": False}
2356
2357
2358class Order(Expression):
2359    arg_types = {
2360        "this": False,
2361        "expressions": True,
2362        "interpolate": False,
2363        "siblings": False,
2364    }
2365
2366
2367# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2368class WithFill(Expression):
2369    arg_types = {"from": False, "to": False, "step": False}
2370
2371
2372# hive specific sorts
2373# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2374class Cluster(Order):
2375    pass
2376
2377
2378class Distribute(Order):
2379    pass
2380
2381
2382class Sort(Order):
2383    pass
2384
2385
2386class Ordered(Expression):
2387    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2388
2389
2390class Property(Expression):
2391    arg_types = {"this": True, "value": True}
2392
2393
2394class AlgorithmProperty(Property):
2395    arg_types = {"this": True}
2396
2397
2398class AutoIncrementProperty(Property):
2399    arg_types = {"this": True}
2400
2401
2402# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2403class AutoRefreshProperty(Property):
2404    arg_types = {"this": True}
2405
2406
2407class BackupProperty(Property):
2408    arg_types = {"this": True}
2409
2410
2411class BlockCompressionProperty(Property):
2412    arg_types = {
2413        "autotemp": False,
2414        "always": False,
2415        "default": False,
2416        "manual": False,
2417        "never": False,
2418    }
2419
2420
2421class CharacterSetProperty(Property):
2422    arg_types = {"this": True, "default": True}
2423
2424
2425class ChecksumProperty(Property):
2426    arg_types = {"on": False, "default": False}
2427
2428
2429class CollateProperty(Property):
2430    arg_types = {"this": True, "default": False}
2431
2432
2433class CopyGrantsProperty(Property):
2434    arg_types = {}
2435
2436
2437class DataBlocksizeProperty(Property):
2438    arg_types = {
2439        "size": False,
2440        "units": False,
2441        "minimum": False,
2442        "maximum": False,
2443        "default": False,
2444    }
2445
2446
2447class DefinerProperty(Property):
2448    arg_types = {"this": True}
2449
2450
2451class DistKeyProperty(Property):
2452    arg_types = {"this": True}
2453
2454
2455class DistStyleProperty(Property):
2456    arg_types = {"this": True}
2457
2458
2459class EngineProperty(Property):
2460    arg_types = {"this": True}
2461
2462
2463class HeapProperty(Property):
2464    arg_types = {}
2465
2466
2467class ToTableProperty(Property):
2468    arg_types = {"this": True}
2469
2470
2471class ExecuteAsProperty(Property):
2472    arg_types = {"this": True}
2473
2474
2475class ExternalProperty(Property):
2476    arg_types = {"this": False}
2477
2478
2479class FallbackProperty(Property):
2480    arg_types = {"no": True, "protection": False}
2481
2482
2483class FileFormatProperty(Property):
2484    arg_types = {"this": True}
2485
2486
2487class FreespaceProperty(Property):
2488    arg_types = {"this": True, "percent": False}
2489
2490
2491class GlobalProperty(Property):
2492    arg_types = {}
2493
2494
2495class IcebergProperty(Property):
2496    arg_types = {}
2497
2498
2499class InheritsProperty(Property):
2500    arg_types = {"expressions": True}
2501
2502
2503class InputModelProperty(Property):
2504    arg_types = {"this": True}
2505
2506
2507class OutputModelProperty(Property):
2508    arg_types = {"this": True}
2509
2510
2511class IsolatedLoadingProperty(Property):
2512    arg_types = {"no": False, "concurrent": False, "target": False}
2513
2514
2515class JournalProperty(Property):
2516    arg_types = {
2517        "no": False,
2518        "dual": False,
2519        "before": False,
2520        "local": False,
2521        "after": False,
2522    }
2523
2524
2525class LanguageProperty(Property):
2526    arg_types = {"this": True}
2527
2528
2529# spark ddl
2530class ClusteredByProperty(Property):
2531    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2532
2533
2534class DictProperty(Property):
2535    arg_types = {"this": True, "kind": True, "settings": False}
2536
2537
2538class DictSubProperty(Property):
2539    pass
2540
2541
2542class DictRange(Property):
2543    arg_types = {"this": True, "min": True, "max": True}
2544
2545
2546# Clickhouse CREATE ... ON CLUSTER modifier
2547# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2548class OnCluster(Property):
2549    arg_types = {"this": True}
2550
2551
2552class LikeProperty(Property):
2553    arg_types = {"this": True, "expressions": False}
2554
2555
2556class LocationProperty(Property):
2557    arg_types = {"this": True}
2558
2559
2560class LockProperty(Property):
2561    arg_types = {"this": True}
2562
2563
2564class LockingProperty(Property):
2565    arg_types = {
2566        "this": False,
2567        "kind": True,
2568        "for_or_in": False,
2569        "lock_type": True,
2570        "override": False,
2571    }
2572
2573
2574class LogProperty(Property):
2575    arg_types = {"no": True}
2576
2577
2578class MaterializedProperty(Property):
2579    arg_types = {"this": False}
2580
2581
2582class MergeBlockRatioProperty(Property):
2583    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2584
2585
2586class NoPrimaryIndexProperty(Property):
2587    arg_types = {}
2588
2589
2590class OnProperty(Property):
2591    arg_types = {"this": True}
2592
2593
2594class OnCommitProperty(Property):
2595    arg_types = {"delete": False}
2596
2597
2598class PartitionedByProperty(Property):
2599    arg_types = {"this": True}
2600
2601
2602# https://www.postgresql.org/docs/current/sql-createtable.html
2603class PartitionBoundSpec(Expression):
2604    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2605    arg_types = {
2606        "this": False,
2607        "expression": False,
2608        "from_expressions": False,
2609        "to_expressions": False,
2610    }
2611
2612
2613class PartitionedOfProperty(Property):
2614    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2615    arg_types = {"this": True, "expression": True}
2616
2617
2618class RemoteWithConnectionModelProperty(Property):
2619    arg_types = {"this": True}
2620
2621
2622class ReturnsProperty(Property):
2623    arg_types = {"this": True, "is_table": False, "table": False}
2624
2625
2626class RowFormatProperty(Property):
2627    arg_types = {"this": True}
2628
2629
2630class RowFormatDelimitedProperty(Property):
2631    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2632    arg_types = {
2633        "fields": False,
2634        "escaped": False,
2635        "collection_items": False,
2636        "map_keys": False,
2637        "lines": False,
2638        "null": False,
2639        "serde": False,
2640    }
2641
2642
2643class RowFormatSerdeProperty(Property):
2644    arg_types = {"this": True, "serde_properties": False}
2645
2646
2647# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2648class QueryTransform(Expression):
2649    arg_types = {
2650        "expressions": True,
2651        "command_script": True,
2652        "schema": False,
2653        "row_format_before": False,
2654        "record_writer": False,
2655        "row_format_after": False,
2656        "record_reader": False,
2657    }
2658
2659
2660class SampleProperty(Property):
2661    arg_types = {"this": True}
2662
2663
2664class SchemaCommentProperty(Property):
2665    arg_types = {"this": True}
2666
2667
2668class SerdeProperties(Property):
2669    arg_types = {"expressions": True}
2670
2671
2672class SetProperty(Property):
2673    arg_types = {"multi": True}
2674
2675
2676class SharingProperty(Property):
2677    arg_types = {"this": False}
2678
2679
2680class SetConfigProperty(Property):
2681    arg_types = {"this": True}
2682
2683
2684class SettingsProperty(Property):
2685    arg_types = {"expressions": True}
2686
2687
2688class SortKeyProperty(Property):
2689    arg_types = {"this": True, "compound": False}
2690
2691
2692class SqlReadWriteProperty(Property):
2693    arg_types = {"this": True}
2694
2695
2696class SqlSecurityProperty(Property):
2697    arg_types = {"definer": True}
2698
2699
2700class StabilityProperty(Property):
2701    arg_types = {"this": True}
2702
2703
2704class TemporaryProperty(Property):
2705    arg_types = {"this": False}
2706
2707
2708class TransformModelProperty(Property):
2709    arg_types = {"expressions": True}
2710
2711
2712class TransientProperty(Property):
2713    arg_types = {"this": False}
2714
2715
2716class UnloggedProperty(Property):
2717    arg_types = {}
2718
2719
2720# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2721class ViewAttributeProperty(Property):
2722    arg_types = {"this": True}
2723
2724
2725class VolatileProperty(Property):
2726    arg_types = {"this": False}
2727
2728
2729class WithDataProperty(Property):
2730    arg_types = {"no": True, "statistics": False}
2731
2732
2733class WithJournalTableProperty(Property):
2734    arg_types = {"this": True}
2735
2736
2737class WithSystemVersioningProperty(Property):
2738    # this -> history table name, expression -> data consistency check
2739    arg_types = {"this": False, "expression": False}
2740
2741
2742class Properties(Expression):
2743    arg_types = {"expressions": True}
2744
2745    NAME_TO_PROPERTY = {
2746        "ALGORITHM": AlgorithmProperty,
2747        "AUTO_INCREMENT": AutoIncrementProperty,
2748        "CHARACTER SET": CharacterSetProperty,
2749        "CLUSTERED_BY": ClusteredByProperty,
2750        "COLLATE": CollateProperty,
2751        "COMMENT": SchemaCommentProperty,
2752        "DEFINER": DefinerProperty,
2753        "DISTKEY": DistKeyProperty,
2754        "DISTSTYLE": DistStyleProperty,
2755        "ENGINE": EngineProperty,
2756        "EXECUTE AS": ExecuteAsProperty,
2757        "FORMAT": FileFormatProperty,
2758        "LANGUAGE": LanguageProperty,
2759        "LOCATION": LocationProperty,
2760        "LOCK": LockProperty,
2761        "PARTITIONED_BY": PartitionedByProperty,
2762        "RETURNS": ReturnsProperty,
2763        "ROW_FORMAT": RowFormatProperty,
2764        "SORTKEY": SortKeyProperty,
2765    }
2766
2767    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2768
2769    # CREATE property locations
2770    # Form: schema specified
2771    #   create [POST_CREATE]
2772    #     table a [POST_NAME]
2773    #     (b int) [POST_SCHEMA]
2774    #     with ([POST_WITH])
2775    #     index (b) [POST_INDEX]
2776    #
2777    # Form: alias selection
2778    #   create [POST_CREATE]
2779    #     table a [POST_NAME]
2780    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2781    #     index (c) [POST_INDEX]
2782    class Location(AutoName):
2783        POST_CREATE = auto()
2784        POST_NAME = auto()
2785        POST_SCHEMA = auto()
2786        POST_WITH = auto()
2787        POST_ALIAS = auto()
2788        POST_EXPRESSION = auto()
2789        POST_INDEX = auto()
2790        UNSUPPORTED = auto()
2791
2792    @classmethod
2793    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2794        expressions = []
2795        for key, value in properties_dict.items():
2796            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2797            if property_cls:
2798                expressions.append(property_cls(this=convert(value)))
2799            else:
2800                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2801
2802        return cls(expressions=expressions)
2803
2804
2805class Qualify(Expression):
2806    pass
2807
2808
2809class InputOutputFormat(Expression):
2810    arg_types = {"input_format": False, "output_format": False}
2811
2812
2813# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2814class Return(Expression):
2815    pass
2816
2817
2818class Reference(Expression):
2819    arg_types = {"this": True, "expressions": False, "options": False}
2820
2821
2822class Tuple(Expression):
2823    arg_types = {"expressions": False}
2824
2825    def isin(
2826        self,
2827        *expressions: t.Any,
2828        query: t.Optional[ExpOrStr] = None,
2829        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2830        copy: bool = True,
2831        **opts,
2832    ) -> In:
2833        return In(
2834            this=maybe_copy(self, copy),
2835            expressions=[convert(e, copy=copy) for e in expressions],
2836            query=maybe_parse(query, copy=copy, **opts) if query else None,
2837            unnest=(
2838                Unnest(
2839                    expressions=[
2840                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2841                        for e in ensure_list(unnest)
2842                    ]
2843                )
2844                if unnest
2845                else None
2846            ),
2847        )
2848
2849
2850QUERY_MODIFIERS = {
2851    "match": False,
2852    "laterals": False,
2853    "joins": False,
2854    "connect": False,
2855    "pivots": False,
2856    "prewhere": False,
2857    "where": False,
2858    "group": False,
2859    "having": False,
2860    "qualify": False,
2861    "windows": False,
2862    "distribute": False,
2863    "sort": False,
2864    "cluster": False,
2865    "order": False,
2866    "limit": False,
2867    "offset": False,
2868    "locks": False,
2869    "sample": False,
2870    "settings": False,
2871    "format": False,
2872    "options": False,
2873}
2874
2875
2876# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2877# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2878class QueryOption(Expression):
2879    arg_types = {"this": True, "expression": False}
2880
2881
2882# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2883class WithTableHint(Expression):
2884    arg_types = {"expressions": True}
2885
2886
2887# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2888class IndexTableHint(Expression):
2889    arg_types = {"this": True, "expressions": False, "target": False}
2890
2891
2892# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2893class HistoricalData(Expression):
2894    arg_types = {"this": True, "kind": True, "expression": True}
2895
2896
2897class Table(Expression):
2898    arg_types = {
2899        "this": False,
2900        "alias": False,
2901        "db": False,
2902        "catalog": False,
2903        "laterals": False,
2904        "joins": False,
2905        "pivots": False,
2906        "hints": False,
2907        "system_time": False,
2908        "version": False,
2909        "format": False,
2910        "pattern": False,
2911        "ordinality": False,
2912        "when": False,
2913        "only": False,
2914        "partition": False,
2915    }
2916
2917    @property
2918    def name(self) -> str:
2919        if isinstance(self.this, Func):
2920            return ""
2921        return self.this.name
2922
2923    @property
2924    def db(self) -> str:
2925        return self.text("db")
2926
2927    @property
2928    def catalog(self) -> str:
2929        return self.text("catalog")
2930
2931    @property
2932    def selects(self) -> t.List[Expression]:
2933        return []
2934
2935    @property
2936    def named_selects(self) -> t.List[str]:
2937        return []
2938
2939    @property
2940    def parts(self) -> t.List[Expression]:
2941        """Return the parts of a table in order catalog, db, table."""
2942        parts: t.List[Expression] = []
2943
2944        for arg in ("catalog", "db", "this"):
2945            part = self.args.get(arg)
2946
2947            if isinstance(part, Dot):
2948                parts.extend(part.flatten())
2949            elif isinstance(part, Expression):
2950                parts.append(part)
2951
2952        return parts
2953
2954    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2955        parts = self.parts
2956        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2957        alias = self.args.get("alias")
2958        if alias:
2959            col = alias_(col, alias.this, copy=copy)
2960        return col
2961
2962
2963class Union(Query):
2964    arg_types = {
2965        "with": False,
2966        "this": True,
2967        "expression": True,
2968        "distinct": False,
2969        "by_name": False,
2970        **QUERY_MODIFIERS,
2971    }
2972
2973    def select(
2974        self,
2975        *expressions: t.Optional[ExpOrStr],
2976        append: bool = True,
2977        dialect: DialectType = None,
2978        copy: bool = True,
2979        **opts,
2980    ) -> Union:
2981        this = maybe_copy(self, copy)
2982        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2983        this.expression.unnest().select(
2984            *expressions, append=append, dialect=dialect, copy=False, **opts
2985        )
2986        return this
2987
2988    @property
2989    def named_selects(self) -> t.List[str]:
2990        return self.this.unnest().named_selects
2991
2992    @property
2993    def is_star(self) -> bool:
2994        return self.this.is_star or self.expression.is_star
2995
2996    @property
2997    def selects(self) -> t.List[Expression]:
2998        return self.this.unnest().selects
2999
3000    @property
3001    def left(self) -> Expression:
3002        return self.this
3003
3004    @property
3005    def right(self) -> Expression:
3006        return self.expression
3007
3008
3009class Except(Union):
3010    pass
3011
3012
3013class Intersect(Union):
3014    pass
3015
3016
3017class Unnest(UDTF):
3018    arg_types = {
3019        "expressions": True,
3020        "alias": False,
3021        "offset": False,
3022    }
3023
3024    @property
3025    def selects(self) -> t.List[Expression]:
3026        columns = super().selects
3027        offset = self.args.get("offset")
3028        if offset:
3029            columns = columns + [to_identifier("offset") if offset is True else offset]
3030        return columns
3031
3032
3033class Update(Expression):
3034    arg_types = {
3035        "with": False,
3036        "this": False,
3037        "expressions": True,
3038        "from": False,
3039        "where": False,
3040        "returning": False,
3041        "order": False,
3042        "limit": False,
3043    }
3044
3045
3046class Values(UDTF):
3047    arg_types = {"expressions": True, "alias": False}
3048
3049
3050class Var(Expression):
3051    pass
3052
3053
3054class Version(Expression):
3055    """
3056    Time travel, iceberg, bigquery etc
3057    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3058    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3059    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3060    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3061    this is either TIMESTAMP or VERSION
3062    kind is ("AS OF", "BETWEEN")
3063    """
3064
3065    arg_types = {"this": True, "kind": True, "expression": False}
3066
3067
3068class Schema(Expression):
3069    arg_types = {"this": False, "expressions": False}
3070
3071
3072# https://dev.mysql.com/doc/refman/8.0/en/select.html
3073# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3074class Lock(Expression):
3075    arg_types = {"update": True, "expressions": False, "wait": False}
3076
3077
3078class Select(Query):
3079    arg_types = {
3080        "with": False,
3081        "kind": False,
3082        "expressions": False,
3083        "hint": False,
3084        "distinct": False,
3085        "into": False,
3086        "from": False,
3087        **QUERY_MODIFIERS,
3088    }
3089
3090    def from_(
3091        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3092    ) -> Select:
3093        """
3094        Set the FROM expression.
3095
3096        Example:
3097            >>> Select().from_("tbl").select("x").sql()
3098            'SELECT x FROM tbl'
3099
3100        Args:
3101            expression : the SQL code strings to parse.
3102                If a `From` instance is passed, this is used as-is.
3103                If another `Expression` instance is passed, it will be wrapped in a `From`.
3104            dialect: the dialect used to parse the input expression.
3105            copy: if `False`, modify this expression instance in-place.
3106            opts: other options to use to parse the input expressions.
3107
3108        Returns:
3109            The modified Select expression.
3110        """
3111        return _apply_builder(
3112            expression=expression,
3113            instance=self,
3114            arg="from",
3115            into=From,
3116            prefix="FROM",
3117            dialect=dialect,
3118            copy=copy,
3119            **opts,
3120        )
3121
3122    def group_by(
3123        self,
3124        *expressions: t.Optional[ExpOrStr],
3125        append: bool = True,
3126        dialect: DialectType = None,
3127        copy: bool = True,
3128        **opts,
3129    ) -> Select:
3130        """
3131        Set the GROUP BY expression.
3132
3133        Example:
3134            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3135            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3136
3137        Args:
3138            *expressions: the SQL code strings to parse.
3139                If a `Group` instance is passed, this is used as-is.
3140                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3141                If nothing is passed in then a group by is not applied to the expression
3142            append: if `True`, add to any existing expressions.
3143                Otherwise, this flattens all the `Group` expression into a single expression.
3144            dialect: the dialect used to parse the input expression.
3145            copy: if `False`, modify this expression instance in-place.
3146            opts: other options to use to parse the input expressions.
3147
3148        Returns:
3149            The modified Select expression.
3150        """
3151        if not expressions:
3152            return self if not copy else self.copy()
3153
3154        return _apply_child_list_builder(
3155            *expressions,
3156            instance=self,
3157            arg="group",
3158            append=append,
3159            copy=copy,
3160            prefix="GROUP BY",
3161            into=Group,
3162            dialect=dialect,
3163            **opts,
3164        )
3165
3166    def sort_by(
3167        self,
3168        *expressions: t.Optional[ExpOrStr],
3169        append: bool = True,
3170        dialect: DialectType = None,
3171        copy: bool = True,
3172        **opts,
3173    ) -> Select:
3174        """
3175        Set the SORT BY expression.
3176
3177        Example:
3178            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3179            'SELECT x FROM tbl SORT BY x DESC'
3180
3181        Args:
3182            *expressions: the SQL code strings to parse.
3183                If a `Group` instance is passed, this is used as-is.
3184                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3185            append: if `True`, add to any existing expressions.
3186                Otherwise, this flattens all the `Order` expression into a single expression.
3187            dialect: the dialect used to parse the input expression.
3188            copy: if `False`, modify this expression instance in-place.
3189            opts: other options to use to parse the input expressions.
3190
3191        Returns:
3192            The modified Select expression.
3193        """
3194        return _apply_child_list_builder(
3195            *expressions,
3196            instance=self,
3197            arg="sort",
3198            append=append,
3199            copy=copy,
3200            prefix="SORT BY",
3201            into=Sort,
3202            dialect=dialect,
3203            **opts,
3204        )
3205
3206    def cluster_by(
3207        self,
3208        *expressions: t.Optional[ExpOrStr],
3209        append: bool = True,
3210        dialect: DialectType = None,
3211        copy: bool = True,
3212        **opts,
3213    ) -> Select:
3214        """
3215        Set the CLUSTER BY expression.
3216
3217        Example:
3218            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3219            'SELECT x FROM tbl CLUSTER BY x DESC'
3220
3221        Args:
3222            *expressions: the SQL code strings to parse.
3223                If a `Group` instance is passed, this is used as-is.
3224                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3225            append: if `True`, add to any existing expressions.
3226                Otherwise, this flattens all the `Order` expression into a single expression.
3227            dialect: the dialect used to parse the input expression.
3228            copy: if `False`, modify this expression instance in-place.
3229            opts: other options to use to parse the input expressions.
3230
3231        Returns:
3232            The modified Select expression.
3233        """
3234        return _apply_child_list_builder(
3235            *expressions,
3236            instance=self,
3237            arg="cluster",
3238            append=append,
3239            copy=copy,
3240            prefix="CLUSTER BY",
3241            into=Cluster,
3242            dialect=dialect,
3243            **opts,
3244        )
3245
3246    def select(
3247        self,
3248        *expressions: t.Optional[ExpOrStr],
3249        append: bool = True,
3250        dialect: DialectType = None,
3251        copy: bool = True,
3252        **opts,
3253    ) -> Select:
3254        return _apply_list_builder(
3255            *expressions,
3256            instance=self,
3257            arg="expressions",
3258            append=append,
3259            dialect=dialect,
3260            into=Expression,
3261            copy=copy,
3262            **opts,
3263        )
3264
3265    def lateral(
3266        self,
3267        *expressions: t.Optional[ExpOrStr],
3268        append: bool = True,
3269        dialect: DialectType = None,
3270        copy: bool = True,
3271        **opts,
3272    ) -> Select:
3273        """
3274        Append to or set the LATERAL expressions.
3275
3276        Example:
3277            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3278            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3279
3280        Args:
3281            *expressions: the SQL code strings to parse.
3282                If an `Expression` instance is passed, it will be used as-is.
3283            append: if `True`, add to any existing expressions.
3284                Otherwise, this resets the expressions.
3285            dialect: the dialect used to parse the input expressions.
3286            copy: if `False`, modify this expression instance in-place.
3287            opts: other options to use to parse the input expressions.
3288
3289        Returns:
3290            The modified Select expression.
3291        """
3292        return _apply_list_builder(
3293            *expressions,
3294            instance=self,
3295            arg="laterals",
3296            append=append,
3297            into=Lateral,
3298            prefix="LATERAL VIEW",
3299            dialect=dialect,
3300            copy=copy,
3301            **opts,
3302        )
3303
3304    def join(
3305        self,
3306        expression: ExpOrStr,
3307        on: t.Optional[ExpOrStr] = None,
3308        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3309        append: bool = True,
3310        join_type: t.Optional[str] = None,
3311        join_alias: t.Optional[Identifier | str] = None,
3312        dialect: DialectType = None,
3313        copy: bool = True,
3314        **opts,
3315    ) -> Select:
3316        """
3317        Append to or set the JOIN expressions.
3318
3319        Example:
3320            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3321            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3322
3323            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3324            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3325
3326            Use `join_type` to change the type of join:
3327
3328            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3329            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3330
3331        Args:
3332            expression: the SQL code string to parse.
3333                If an `Expression` instance is passed, it will be used as-is.
3334            on: optionally specify the join "on" criteria as a SQL string.
3335                If an `Expression` instance is passed, it will be used as-is.
3336            using: optionally specify the join "using" criteria as a SQL string.
3337                If an `Expression` instance is passed, it will be used as-is.
3338            append: if `True`, add to any existing expressions.
3339                Otherwise, this resets the expressions.
3340            join_type: if set, alter the parsed join type.
3341            join_alias: an optional alias for the joined source.
3342            dialect: the dialect used to parse the input expressions.
3343            copy: if `False`, modify this expression instance in-place.
3344            opts: other options to use to parse the input expressions.
3345
3346        Returns:
3347            Select: the modified expression.
3348        """
3349        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3350
3351        try:
3352            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3353        except ParseError:
3354            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3355
3356        join = expression if isinstance(expression, Join) else Join(this=expression)
3357
3358        if isinstance(join.this, Select):
3359            join.this.replace(join.this.subquery())
3360
3361        if join_type:
3362            method: t.Optional[Token]
3363            side: t.Optional[Token]
3364            kind: t.Optional[Token]
3365
3366            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3367
3368            if method:
3369                join.set("method", method.text)
3370            if side:
3371                join.set("side", side.text)
3372            if kind:
3373                join.set("kind", kind.text)
3374
3375        if on:
3376            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3377            join.set("on", on)
3378
3379        if using:
3380            join = _apply_list_builder(
3381                *ensure_list(using),
3382                instance=join,
3383                arg="using",
3384                append=append,
3385                copy=copy,
3386                into=Identifier,
3387                **opts,
3388            )
3389
3390        if join_alias:
3391            join.set("this", alias_(join.this, join_alias, table=True))
3392
3393        return _apply_list_builder(
3394            join,
3395            instance=self,
3396            arg="joins",
3397            append=append,
3398            copy=copy,
3399            **opts,
3400        )
3401
3402    def where(
3403        self,
3404        *expressions: t.Optional[ExpOrStr],
3405        append: bool = True,
3406        dialect: DialectType = None,
3407        copy: bool = True,
3408        **opts,
3409    ) -> Select:
3410        """
3411        Append to or set the WHERE expressions.
3412
3413        Example:
3414            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3415            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3416
3417        Args:
3418            *expressions: the SQL code strings to parse.
3419                If an `Expression` instance is passed, it will be used as-is.
3420                Multiple expressions are combined with an AND operator.
3421            append: if `True`, AND the new expressions to any existing expression.
3422                Otherwise, this resets the expression.
3423            dialect: the dialect used to parse the input expressions.
3424            copy: if `False`, modify this expression instance in-place.
3425            opts: other options to use to parse the input expressions.
3426
3427        Returns:
3428            Select: the modified expression.
3429        """
3430        return _apply_conjunction_builder(
3431            *expressions,
3432            instance=self,
3433            arg="where",
3434            append=append,
3435            into=Where,
3436            dialect=dialect,
3437            copy=copy,
3438            **opts,
3439        )
3440
3441    def having(
3442        self,
3443        *expressions: t.Optional[ExpOrStr],
3444        append: bool = True,
3445        dialect: DialectType = None,
3446        copy: bool = True,
3447        **opts,
3448    ) -> Select:
3449        """
3450        Append to or set the HAVING expressions.
3451
3452        Example:
3453            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3454            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3455
3456        Args:
3457            *expressions: the SQL code strings to parse.
3458                If an `Expression` instance is passed, it will be used as-is.
3459                Multiple expressions are combined with an AND operator.
3460            append: if `True`, AND the new expressions to any existing expression.
3461                Otherwise, this resets the expression.
3462            dialect: the dialect used to parse the input expressions.
3463            copy: if `False`, modify this expression instance in-place.
3464            opts: other options to use to parse the input expressions.
3465
3466        Returns:
3467            The modified Select expression.
3468        """
3469        return _apply_conjunction_builder(
3470            *expressions,
3471            instance=self,
3472            arg="having",
3473            append=append,
3474            into=Having,
3475            dialect=dialect,
3476            copy=copy,
3477            **opts,
3478        )
3479
3480    def window(
3481        self,
3482        *expressions: t.Optional[ExpOrStr],
3483        append: bool = True,
3484        dialect: DialectType = None,
3485        copy: bool = True,
3486        **opts,
3487    ) -> Select:
3488        return _apply_list_builder(
3489            *expressions,
3490            instance=self,
3491            arg="windows",
3492            append=append,
3493            into=Window,
3494            dialect=dialect,
3495            copy=copy,
3496            **opts,
3497        )
3498
3499    def qualify(
3500        self,
3501        *expressions: t.Optional[ExpOrStr],
3502        append: bool = True,
3503        dialect: DialectType = None,
3504        copy: bool = True,
3505        **opts,
3506    ) -> Select:
3507        return _apply_conjunction_builder(
3508            *expressions,
3509            instance=self,
3510            arg="qualify",
3511            append=append,
3512            into=Qualify,
3513            dialect=dialect,
3514            copy=copy,
3515            **opts,
3516        )
3517
3518    def distinct(
3519        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3520    ) -> Select:
3521        """
3522        Set the OFFSET expression.
3523
3524        Example:
3525            >>> Select().from_("tbl").select("x").distinct().sql()
3526            'SELECT DISTINCT x FROM tbl'
3527
3528        Args:
3529            ons: the expressions to distinct on
3530            distinct: whether the Select should be distinct
3531            copy: if `False`, modify this expression instance in-place.
3532
3533        Returns:
3534            Select: the modified expression.
3535        """
3536        instance = maybe_copy(self, copy)
3537        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3538        instance.set("distinct", Distinct(on=on) if distinct else None)
3539        return instance
3540
3541    def ctas(
3542        self,
3543        table: ExpOrStr,
3544        properties: t.Optional[t.Dict] = None,
3545        dialect: DialectType = None,
3546        copy: bool = True,
3547        **opts,
3548    ) -> Create:
3549        """
3550        Convert this expression to a CREATE TABLE AS statement.
3551
3552        Example:
3553            >>> Select().select("*").from_("tbl").ctas("x").sql()
3554            'CREATE TABLE x AS SELECT * FROM tbl'
3555
3556        Args:
3557            table: the SQL code string to parse as the table name.
3558                If another `Expression` instance is passed, it will be used as-is.
3559            properties: an optional mapping of table properties
3560            dialect: the dialect used to parse the input table.
3561            copy: if `False`, modify this expression instance in-place.
3562            opts: other options to use to parse the input table.
3563
3564        Returns:
3565            The new Create expression.
3566        """
3567        instance = maybe_copy(self, copy)
3568        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3569
3570        properties_expression = None
3571        if properties:
3572            properties_expression = Properties.from_dict(properties)
3573
3574        return Create(
3575            this=table_expression,
3576            kind="TABLE",
3577            expression=instance,
3578            properties=properties_expression,
3579        )
3580
3581    def lock(self, update: bool = True, copy: bool = True) -> Select:
3582        """
3583        Set the locking read mode for this expression.
3584
3585        Examples:
3586            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3587            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3588
3589            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3590            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3591
3592        Args:
3593            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3594            copy: if `False`, modify this expression instance in-place.
3595
3596        Returns:
3597            The modified expression.
3598        """
3599        inst = maybe_copy(self, copy)
3600        inst.set("locks", [Lock(update=update)])
3601
3602        return inst
3603
3604    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3605        """
3606        Set hints for this expression.
3607
3608        Examples:
3609            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3610            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3611
3612        Args:
3613            hints: The SQL code strings to parse as the hints.
3614                If an `Expression` instance is passed, it will be used as-is.
3615            dialect: The dialect used to parse the hints.
3616            copy: If `False`, modify this expression instance in-place.
3617
3618        Returns:
3619            The modified expression.
3620        """
3621        inst = maybe_copy(self, copy)
3622        inst.set(
3623            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3624        )
3625
3626        return inst
3627
3628    @property
3629    def named_selects(self) -> t.List[str]:
3630        return [e.output_name for e in self.expressions if e.alias_or_name]
3631
3632    @property
3633    def is_star(self) -> bool:
3634        return any(expression.is_star for expression in self.expressions)
3635
3636    @property
3637    def selects(self) -> t.List[Expression]:
3638        return self.expressions
3639
3640
3641UNWRAPPED_QUERIES = (Select, Union)
3642
3643
3644class Subquery(DerivedTable, Query):
3645    arg_types = {
3646        "this": True,
3647        "alias": False,
3648        "with": False,
3649        **QUERY_MODIFIERS,
3650    }
3651
3652    def unnest(self):
3653        """Returns the first non subquery."""
3654        expression = self
3655        while isinstance(expression, Subquery):
3656            expression = expression.