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