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