1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61
  62
  63class Expression(metaclass=_Expression):
  64    """
  65    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  66    context, such as its child expressions, their names (arg keys), and whether a given child expression
  67    is optional or not.
  68
  69    Attributes:
  70        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  71            and representing expressions as strings.
  72        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  73            arg keys to booleans that indicate whether the corresponding args are optional.
  74        parent: a reference to the parent expression (or None, in case of root expressions).
  75        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  76            uses to refer to it.
  77        index: the index of an expression if it is inside of a list argument in its parent
  78        comments: a list of comments that are associated with a given expression. This is used in
  79            order to preserve comments when transpiling SQL code.
  80        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  81            optimizer, in order to enable some transformations that require type information.
  82        meta: a dictionary that can be used to store useful metadata for a given expression.
  83
  84    Example:
  85        >>> class Foo(Expression):
  86        ...     arg_types = {"this": True, "expression": False}
  87
  88        The above definition informs us that Foo is an Expression that requires an argument called
  89        "this" and may also optionally receive an argument called "expression".
  90
  91    Args:
  92        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  93    """
  94
  95    key = "expression"
  96    arg_types = {"this": True}
  97    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  98
  99    def __init__(self, **args: t.Any):
 100        self.args: t.Dict[str, t.Any] = args
 101        self.parent: t.Optional[Expression] = None
 102        self.arg_key: t.Optional[str] = None
 103        self.index: t.Optional[int] = None
 104        self.comments: t.Optional[t.List[str]] = None
 105        self._type: t.Optional[DataType] = None
 106        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 107        self._hash: t.Optional[int] = None
 108
 109        for arg_key, value in self.args.items():
 110            self._set_parent(arg_key, value)
 111
 112    def __eq__(self, other) -> bool:
 113        return type(self) is type(other) and hash(self) == hash(other)
 114
 115    @property
 116    def hashable_args(self) -> t.Any:
 117        return frozenset(
 118            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 119            for k, v in self.args.items()
 120            if not (v is None or v is False or (type(v) is list and not v))
 121        )
 122
 123    def __hash__(self) -> int:
 124        if self._hash is not None:
 125            return self._hash
 126
 127        return hash((self.__class__, self.hashable_args))
 128
 129    @property
 130    def this(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "this".
 133        """
 134        return self.args.get("this")
 135
 136    @property
 137    def expression(self) -> t.Any:
 138        """
 139        Retrieves the argument with key "expression".
 140        """
 141        return self.args.get("expression")
 142
 143    @property
 144    def expressions(self) -> t.List[t.Any]:
 145        """
 146        Retrieves the argument with key "expressions".
 147        """
 148        return self.args.get("expressions") or []
 149
 150    def text(self, key) -> str:
 151        """
 152        Returns a textual representation of the argument corresponding to "key". This can only be used
 153        for args that are strings or leaf Expression instances, such as identifiers and literals.
 154        """
 155        field = self.args.get(key)
 156        if isinstance(field, str):
 157            return field
 158        if isinstance(field, (Identifier, Literal, Var)):
 159            return field.this
 160        if isinstance(field, (Star, Null)):
 161            return field.name
 162        return ""
 163
 164    @property
 165    def is_string(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a string.
 168        """
 169        return isinstance(self, Literal) and self.args["is_string"]
 170
 171    @property
 172    def is_number(self) -> bool:
 173        """
 174        Checks whether a Literal expression is a number.
 175        """
 176        return isinstance(self, Literal) and not self.args["is_string"]
 177
 178    @property
 179    def is_int(self) -> bool:
 180        """
 181        Checks whether a Literal expression is an integer.
 182        """
 183        return self.is_number and is_int(self.name)
 184
 185    @property
 186    def is_star(self) -> bool:
 187        """Checks whether an expression is a star."""
 188        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 189
 190    @property
 191    def alias(self) -> str:
 192        """
 193        Returns the alias of the expression, or an empty string if it's not aliased.
 194        """
 195        if isinstance(self.args.get("alias"), TableAlias):
 196            return self.args["alias"].name
 197        return self.text("alias")
 198
 199    @property
 200    def alias_column_names(self) -> t.List[str]:
 201        table_alias = self.args.get("alias")
 202        if not table_alias:
 203            return []
 204        return [c.name for c in table_alias.args.get("columns") or []]
 205
 206    @property
 207    def name(self) -> str:
 208        return self.text("this")
 209
 210    @property
 211    def alias_or_name(self) -> str:
 212        return self.alias or self.name
 213
 214    @property
 215    def output_name(self) -> str:
 216        """
 217        Name of the output column if this expression is a selection.
 218
 219        If the Expression has no output name, an empty string is returned.
 220
 221        Example:
 222            >>> from sqlglot import parse_one
 223            >>> parse_one("SELECT a").expressions[0].output_name
 224            'a'
 225            >>> parse_one("SELECT b AS c").expressions[0].output_name
 226            'c'
 227            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 228            ''
 229        """
 230        return ""
 231
 232    @property
 233    def type(self) -> t.Optional[DataType]:
 234        return self._type
 235
 236    @type.setter
 237    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 238        if dtype and not isinstance(dtype, DataType):
 239            dtype = DataType.build(dtype)
 240        self._type = dtype  # type: ignore
 241
 242    def is_type(self, *dtypes) -> bool:
 243        return self.type is not None and self.type.is_type(*dtypes)
 244
 245    def is_leaf(self) -> bool:
 246        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 247
 248    @property
 249    def meta(self) -> t.Dict[str, t.Any]:
 250        if self._meta is None:
 251            self._meta = {}
 252        return self._meta
 253
 254    def __deepcopy__(self, memo):
 255        root = self.__class__()
 256        stack = [(self, root)]
 257
 258        while stack:
 259            node, copy = stack.pop()
 260
 261            if node.comments is not None:
 262                copy.comments = deepcopy(node.comments)
 263            if node._type is not None:
 264                copy._type = deepcopy(node._type)
 265            if node._meta is not None:
 266                copy._meta = deepcopy(node._meta)
 267            if node._hash is not None:
 268                copy._hash = node._hash
 269
 270            for k, vs in node.args.items():
 271                if hasattr(vs, "parent"):
 272                    stack.append((vs, vs.__class__()))
 273                    copy.set(k, stack[-1][-1])
 274                elif type(vs) is list:
 275                    copy.args[k] = []
 276
 277                    for v in vs:
 278                        if hasattr(v, "parent"):
 279                            stack.append((v, v.__class__()))
 280                            copy.append(k, stack[-1][-1])
 281                        else:
 282                            copy.append(k, v)
 283                else:
 284                    copy.args[k] = vs
 285
 286        return root
 287
 288    def copy(self):
 289        """
 290        Returns a deep copy of the expression.
 291        """
 292        return deepcopy(self)
 293
 294    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 295        if self.comments is None:
 296            self.comments = []
 297        if comments:
 298            for comment in comments:
 299                _, *meta = comment.split(SQLGLOT_META)
 300                if meta:
 301                    for kv in "".join(meta).split(","):
 302                        k, *v = kv.split("=")
 303                        value = v[0].strip() if v else True
 304                        self.meta[k.strip()] = value
 305                self.comments.append(comment)
 306
 307    def append(self, arg_key: str, value: t.Any) -> None:
 308        """
 309        Appends value to arg_key if it's a list or sets it as a new list.
 310
 311        Args:
 312            arg_key (str): name of the list expression arg
 313            value (Any): value to append to the list
 314        """
 315        if type(self.args.get(arg_key)) is not list:
 316            self.args[arg_key] = []
 317        self._set_parent(arg_key, value)
 318        values = self.args[arg_key]
 319        if hasattr(value, "parent"):
 320            value.index = len(values)
 321        values.append(value)
 322
 323    def set(self, arg_key: str, value: t.Any) -> None:
 324        """
 325        Sets arg_key to value.
 326
 327        Args:
 328            arg_key: name of the expression arg.
 329            value: value to set the arg to.
 330        """
 331        if value is None:
 332            self.args.pop(arg_key, None)
 333        else:
 334            self.args[arg_key] = value
 335            self._set_parent(arg_key, value)
 336
 337    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 338        if hasattr(value, "parent"):
 339            value.parent = self
 340            value.arg_key = arg_key
 341            value.index = index
 342        elif type(value) is list:
 343            for index, v in enumerate(value):
 344                if hasattr(v, "parent"):
 345                    v.parent = self
 346                    v.arg_key = arg_key
 347                    v.index = index
 348
 349    @property
 350    def depth(self) -> int:
 351        """
 352        Returns the depth of this tree.
 353        """
 354        if self.parent:
 355            return self.parent.depth + 1
 356        return 0
 357
 358    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 359        """Yields the key and expression for all arguments, exploding list args."""
 360        # remove tuple when python 3.7 is deprecated
 361        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 362            if type(vs) is list:
 363                for v in reversed(vs) if reverse else vs:
 364                    if hasattr(v, "parent"):
 365                        yield v
 366            else:
 367                if hasattr(vs, "parent"):
 368                    yield vs
 369
 370    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 371        """
 372        Returns the first node in this tree which matches at least one of
 373        the specified types.
 374
 375        Args:
 376            expression_types: the expression type(s) to match.
 377            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 378
 379        Returns:
 380            The node which matches the criteria or None if no such node was found.
 381        """
 382        return next(self.find_all(*expression_types, bfs=bfs), None)
 383
 384    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 385        """
 386        Returns a generator object which visits all nodes in this tree and only
 387        yields those that match at least one of the specified expression types.
 388
 389        Args:
 390            expression_types: the expression type(s) to match.
 391            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 392
 393        Returns:
 394            The generator object.
 395        """
 396        for expression in self.walk(bfs=bfs):
 397            if isinstance(expression, expression_types):
 398                yield expression
 399
 400    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 401        """
 402        Returns a nearest parent matching expression_types.
 403
 404        Args:
 405            expression_types: the expression type(s) to match.
 406
 407        Returns:
 408            The parent node.
 409        """
 410        ancestor = self.parent
 411        while ancestor and not isinstance(ancestor, expression_types):
 412            ancestor = ancestor.parent
 413        return ancestor  # type: ignore
 414
 415    @property
 416    def parent_select(self) -> t.Optional[Select]:
 417        """
 418        Returns the parent select statement.
 419        """
 420        return self.find_ancestor(Select)
 421
 422    @property
 423    def same_parent(self) -> bool:
 424        """Returns if the parent is the same class as itself."""
 425        return type(self.parent) is self.__class__
 426
 427    def root(self) -> Expression:
 428        """
 429        Returns the root expression of this tree.
 430        """
 431        expression = self
 432        while expression.parent:
 433            expression = expression.parent
 434        return expression
 435
 436    def walk(
 437        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 438    ) -> t.Iterator[Expression]:
 439        """
 440        Returns a generator object which visits all nodes in this tree.
 441
 442        Args:
 443            bfs (bool): if set to True the BFS traversal order will be applied,
 444                otherwise the DFS traversal will be used instead.
 445            prune ((node, parent, arg_key) -> bool): callable that returns True if
 446                the generator should stop traversing this branch of the tree.
 447
 448        Returns:
 449            the generator object.
 450        """
 451        if bfs:
 452            yield from self.bfs(prune=prune)
 453        else:
 454            yield from self.dfs(prune=prune)
 455
 456    def dfs(
 457        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 458    ) -> t.Iterator[Expression]:
 459        """
 460        Returns a generator object which visits all nodes in this tree in
 461        the DFS (Depth-first) order.
 462
 463        Returns:
 464            The generator object.
 465        """
 466        stack = [self]
 467
 468        while stack:
 469            node = stack.pop()
 470
 471            yield node
 472
 473            if prune and prune(node):
 474                continue
 475
 476            for v in node.iter_expressions(reverse=True):
 477                stack.append(v)
 478
 479    def bfs(
 480        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 481    ) -> t.Iterator[Expression]:
 482        """
 483        Returns a generator object which visits all nodes in this tree in
 484        the BFS (Breadth-first) order.
 485
 486        Returns:
 487            The generator object.
 488        """
 489        queue = deque([self])
 490
 491        while queue:
 492            node = queue.popleft()
 493
 494            yield node
 495
 496            if prune and prune(node):
 497                continue
 498
 499            for v in node.iter_expressions():
 500                queue.append(v)
 501
 502    def unnest(self):
 503        """
 504        Returns the first non parenthesis child or self.
 505        """
 506        expression = self
 507        while type(expression) is Paren:
 508            expression = expression.this
 509        return expression
 510
 511    def unalias(self):
 512        """
 513        Returns the inner expression if this is an Alias.
 514        """
 515        if isinstance(self, Alias):
 516            return self.this
 517        return self
 518
 519    def unnest_operands(self):
 520        """
 521        Returns unnested operands as a tuple.
 522        """
 523        return tuple(arg.unnest() for arg in self.iter_expressions())
 524
 525    def flatten(self, unnest=True):
 526        """
 527        Returns a generator which yields child nodes whose parents are the same class.
 528
 529        A AND B AND C -> [A, B, C]
 530        """
 531        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 532            if type(node) is not self.__class__:
 533                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 534
 535    def __str__(self) -> str:
 536        return self.sql()
 537
 538    def __repr__(self) -> str:
 539        return _to_s(self)
 540
 541    def to_s(self) -> str:
 542        """
 543        Same as __repr__, but includes additional information which can be useful
 544        for debugging, like empty or missing args and the AST nodes' object IDs.
 545        """
 546        return _to_s(self, verbose=True)
 547
 548    def sql(self, dialect: DialectType = None, **opts) -> str:
 549        """
 550        Returns SQL string representation of this tree.
 551
 552        Args:
 553            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 554            opts: other `sqlglot.generator.Generator` options.
 555
 556        Returns:
 557            The SQL string.
 558        """
 559        from sqlglot.dialects import Dialect
 560
 561        return Dialect.get_or_raise(dialect).generate(self, **opts)
 562
 563    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 564        """
 565        Visits all tree nodes (excluding already transformed ones)
 566        and applies the given transformation function to each node.
 567
 568        Args:
 569            fun (function): a function which takes a node as an argument and returns a
 570                new transformed node or the same node without modifications. If the function
 571                returns None, then the corresponding node will be removed from the syntax tree.
 572            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 573                modified in place.
 574
 575        Returns:
 576            The transformed tree.
 577        """
 578        root = None
 579        new_node = None
 580
 581        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 582            new_node = fun(node, *args, **kwargs)
 583
 584            if root:
 585                if new_node is not node:
 586                    node.replace(new_node)
 587            else:
 588                root = new_node
 589
 590        assert root
 591        return root.assert_is(Expression)
 592
 593    @t.overload
 594    def replace(self, expression: E) -> E: ...
 595
 596    @t.overload
 597    def replace(self, expression: None) -> None: ...
 598
 599    def replace(self, expression):
 600        """
 601        Swap out this expression with a new expression.
 602
 603        For example::
 604
 605            >>> tree = Select().select("x").from_("tbl")
 606            >>> tree.find(Column).replace(column("y"))
 607            Column(
 608              this=Identifier(this=y, quoted=False))
 609            >>> tree.sql()
 610            'SELECT y FROM tbl'
 611
 612        Args:
 613            expression: new node
 614
 615        Returns:
 616            The new expression or expressions.
 617        """
 618        parent = self.parent
 619
 620        if not parent:
 621            return expression
 622
 623        key = self.arg_key
 624        value = parent.args.get(key)
 625
 626        if isinstance(value, list):
 627            index = self.index
 628
 629            if isinstance(expression, list):
 630                value.pop(index)
 631                value[index:index] = expression
 632                parent._set_parent(key, value)
 633            else:
 634                if expression is None:
 635                    value.pop(index)
 636
 637                    for v in value[index:]:
 638                        v.index = v.index - 1
 639                else:
 640                    value[index] = expression
 641                    parent._set_parent(key, expression, index=index)
 642        elif value is not None:
 643            if expression is None:
 644                parent.args.pop(key)
 645            else:
 646                parent.set(key, expression)
 647
 648        if expression is not self:
 649            self.parent = None
 650            self.arg_key = None
 651            self.index = None
 652
 653        return expression
 654
 655    def pop(self: E) -> E:
 656        """
 657        Remove this expression from its AST.
 658
 659        Returns:
 660            The popped expression.
 661        """
 662        self.replace(None)
 663        return self
 664
 665    def assert_is(self, type_: t.Type[E]) -> E:
 666        """
 667        Assert that this `Expression` is an instance of `type_`.
 668
 669        If it is NOT an instance of `type_`, this raises an assertion error.
 670        Otherwise, this returns this expression.
 671
 672        Examples:
 673            This is useful for type security in chained expressions:
 674
 675            >>> import sqlglot
 676            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 677            'SELECT x, z FROM y'
 678        """
 679        if not isinstance(self, type_):
 680            raise AssertionError(f"{self} is not {type_}.")
 681        return self
 682
 683    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 684        """
 685        Checks if this expression is valid (e.g. all mandatory args are set).
 686
 687        Args:
 688            args: a sequence of values that were used to instantiate a Func expression. This is used
 689                to check that the provided arguments don't exceed the function argument limit.
 690
 691        Returns:
 692            A list of error messages for all possible errors that were found.
 693        """
 694        errors: t.List[str] = []
 695
 696        for k in self.args:
 697            if k not in self.arg_types:
 698                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 699        for k, mandatory in self.arg_types.items():
 700            v = self.args.get(k)
 701            if mandatory and (v is None or (isinstance(v, list) and not v)):
 702                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 703
 704        if (
 705            args
 706            and isinstance(self, Func)
 707            and len(args) > len(self.arg_types)
 708            and not self.is_var_len_args
 709        ):
 710            errors.append(
 711                f"The number of provided arguments ({len(args)}) is greater than "
 712                f"the maximum number of supported arguments ({len(self.arg_types)})"
 713            )
 714
 715        return errors
 716
 717    def dump(self):
 718        """
 719        Dump this Expression to a JSON-serializable dict.
 720        """
 721        from sqlglot.serde import dump
 722
 723        return dump(self)
 724
 725    @classmethod
 726    def load(cls, obj):
 727        """
 728        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 729        """
 730        from sqlglot.serde import load
 731
 732        return load(obj)
 733
 734    def and_(
 735        self,
 736        *expressions: t.Optional[ExpOrStr],
 737        dialect: DialectType = None,
 738        copy: bool = True,
 739        **opts,
 740    ) -> Condition:
 741        """
 742        AND this condition with one or multiple expressions.
 743
 744        Example:
 745            >>> condition("x=1").and_("y=1").sql()
 746            'x = 1 AND y = 1'
 747
 748        Args:
 749            *expressions: the SQL code strings to parse.
 750                If an `Expression` instance is passed, it will be used as-is.
 751            dialect: the dialect used to parse the input expression.
 752            copy: whether to copy the involved expressions (only applies to Expressions).
 753            opts: other options to use to parse the input expressions.
 754
 755        Returns:
 756            The new And condition.
 757        """
 758        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 759
 760    def or_(
 761        self,
 762        *expressions: t.Optional[ExpOrStr],
 763        dialect: DialectType = None,
 764        copy: bool = True,
 765        **opts,
 766    ) -> Condition:
 767        """
 768        OR this condition with one or multiple expressions.
 769
 770        Example:
 771            >>> condition("x=1").or_("y=1").sql()
 772            'x = 1 OR y = 1'
 773
 774        Args:
 775            *expressions: the SQL code strings to parse.
 776                If an `Expression` instance is passed, it will be used as-is.
 777            dialect: the dialect used to parse the input expression.
 778            copy: whether to copy the involved expressions (only applies to Expressions).
 779            opts: other options to use to parse the input expressions.
 780
 781        Returns:
 782            The new Or condition.
 783        """
 784        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 785
 786    def not_(self, copy: bool = True):
 787        """
 788        Wrap this condition with NOT.
 789
 790        Example:
 791            >>> condition("x=1").not_().sql()
 792            'NOT x = 1'
 793
 794        Args:
 795            copy: whether to copy this object.
 796
 797        Returns:
 798            The new Not instance.
 799        """
 800        return not_(self, copy=copy)
 801
 802    def as_(
 803        self,
 804        alias: str | Identifier,
 805        quoted: t.Optional[bool] = None,
 806        dialect: DialectType = None,
 807        copy: bool = True,
 808        **opts,
 809    ) -> Alias:
 810        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 811
 812    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 813        this = self.copy()
 814        other = convert(other, copy=True)
 815        if not isinstance(this, klass) and not isinstance(other, klass):
 816            this = _wrap(this, Binary)
 817            other = _wrap(other, Binary)
 818        if reverse:
 819            return klass(this=other, expression=this)
 820        return klass(this=this, expression=other)
 821
 822    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 823        return Bracket(
 824            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 825        )
 826
 827    def __iter__(self) -> t.Iterator:
 828        if "expressions" in self.arg_types:
 829            return iter(self.args.get("expressions") or [])
 830        # We define this because __getitem__ converts Expression into an iterable, which is
 831        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 832        # See: https://peps.python.org/pep-0234/
 833        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 834
 835    def isin(
 836        self,
 837        *expressions: t.Any,
 838        query: t.Optional[ExpOrStr] = None,
 839        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 840        copy: bool = True,
 841        **opts,
 842    ) -> In:
 843        return In(
 844            this=maybe_copy(self, copy),
 845            expressions=[convert(e, copy=copy) for e in expressions],
 846            query=maybe_parse(query, copy=copy, **opts) if query else None,
 847            unnest=(
 848                Unnest(
 849                    expressions=[
 850                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 851                        for e in ensure_list(unnest)
 852                    ]
 853                )
 854                if unnest
 855                else None
 856            ),
 857        )
 858
 859    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 860        return Between(
 861            this=maybe_copy(self, copy),
 862            low=convert(low, copy=copy, **opts),
 863            high=convert(high, copy=copy, **opts),
 864        )
 865
 866    def is_(self, other: ExpOrStr) -> Is:
 867        return self._binop(Is, other)
 868
 869    def like(self, other: ExpOrStr) -> Like:
 870        return self._binop(Like, other)
 871
 872    def ilike(self, other: ExpOrStr) -> ILike:
 873        return self._binop(ILike, other)
 874
 875    def eq(self, other: t.Any) -> EQ:
 876        return self._binop(EQ, other)
 877
 878    def neq(self, other: t.Any) -> NEQ:
 879        return self._binop(NEQ, other)
 880
 881    def rlike(self, other: ExpOrStr) -> RegexpLike:
 882        return self._binop(RegexpLike, other)
 883
 884    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 885        div = self._binop(Div, other)
 886        div.args["typed"] = typed
 887        div.args["safe"] = safe
 888        return div
 889
 890    def desc(self, nulls_first: bool = False) -> Ordered:
 891        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 892
 893    def __lt__(self, other: t.Any) -> LT:
 894        return self._binop(LT, other)
 895
 896    def __le__(self, other: t.Any) -> LTE:
 897        return self._binop(LTE, other)
 898
 899    def __gt__(self, other: t.Any) -> GT:
 900        return self._binop(GT, other)
 901
 902    def __ge__(self, other: t.Any) -> GTE:
 903        return self._binop(GTE, other)
 904
 905    def __add__(self, other: t.Any) -> Add:
 906        return self._binop(Add, other)
 907
 908    def __radd__(self, other: t.Any) -> Add:
 909        return self._binop(Add, other, reverse=True)
 910
 911    def __sub__(self, other: t.Any) -> Sub:
 912        return self._binop(Sub, other)
 913
 914    def __rsub__(self, other: t.Any) -> Sub:
 915        return self._binop(Sub, other, reverse=True)
 916
 917    def __mul__(self, other: t.Any) -> Mul:
 918        return self._binop(Mul, other)
 919
 920    def __rmul__(self, other: t.Any) -> Mul:
 921        return self._binop(Mul, other, reverse=True)
 922
 923    def __truediv__(self, other: t.Any) -> Div:
 924        return self._binop(Div, other)
 925
 926    def __rtruediv__(self, other: t.Any) -> Div:
 927        return self._binop(Div, other, reverse=True)
 928
 929    def __floordiv__(self, other: t.Any) -> IntDiv:
 930        return self._binop(IntDiv, other)
 931
 932    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 933        return self._binop(IntDiv, other, reverse=True)
 934
 935    def __mod__(self, other: t.Any) -> Mod:
 936        return self._binop(Mod, other)
 937
 938    def __rmod__(self, other: t.Any) -> Mod:
 939        return self._binop(Mod, other, reverse=True)
 940
 941    def __pow__(self, other: t.Any) -> Pow:
 942        return self._binop(Pow, other)
 943
 944    def __rpow__(self, other: t.Any) -> Pow:
 945        return self._binop(Pow, other, reverse=True)
 946
 947    def __and__(self, other: t.Any) -> And:
 948        return self._binop(And, other)
 949
 950    def __rand__(self, other: t.Any) -> And:
 951        return self._binop(And, other, reverse=True)
 952
 953    def __or__(self, other: t.Any) -> Or:
 954        return self._binop(Or, other)
 955
 956    def __ror__(self, other: t.Any) -> Or:
 957        return self._binop(Or, other, reverse=True)
 958
 959    def __neg__(self) -> Neg:
 960        return Neg(this=_wrap(self.copy(), Binary))
 961
 962    def __invert__(self) -> Not:
 963        return not_(self.copy())
 964
 965
 966IntoType = t.Union[
 967    str,
 968    t.Type[Expression],
 969    t.Collection[t.Union[str, t.Type[Expression]]],
 970]
 971ExpOrStr = t.Union[str, Expression]
 972
 973
 974class Condition(Expression):
 975    """Logical conditions like x AND y, or simply x"""
 976
 977
 978class Predicate(Condition):
 979    """Relationships like x = y, x > 1, x >= y."""
 980
 981
 982class DerivedTable(Expression):
 983    @property
 984    def selects(self) -> t.List[Expression]:
 985        return self.this.selects if isinstance(self.this, Query) else []
 986
 987    @property
 988    def named_selects(self) -> t.List[str]:
 989        return [select.output_name for select in self.selects]
 990
 991
 992class Query(Expression):
 993    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
 994        """
 995        Returns a `Subquery` that wraps around this query.
 996
 997        Example:
 998            >>> subquery = Select().select("x").from_("tbl").subquery()
 999            >>> Select().select("x").from_(subquery).sql()
1000            'SELECT x FROM (SELECT x FROM tbl)'
1001
1002        Args:
1003            alias: an optional alias for the subquery.
1004            copy: if `False`, modify this expression instance in-place.
1005        """
1006        instance = maybe_copy(self, copy)
1007        if not isinstance(alias, Expression):
1008            alias = TableAlias(this=to_identifier(alias)) if alias else None
1009
1010        return Subquery(this=instance, alias=alias)
1011
1012    def limit(
1013        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1014    ) -> Select:
1015        """
1016        Adds a LIMIT clause to this query.
1017
1018        Example:
1019            >>> select("1").union(select("1")).limit(1).sql()
1020            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1021
1022        Args:
1023            expression: the SQL code string to parse.
1024                This can also be an integer.
1025                If a `Limit` instance is passed, it will be used as-is.
1026                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1027            dialect: the dialect used to parse the input expression.
1028            copy: if `False`, modify this expression instance in-place.
1029            opts: other options to use to parse the input expressions.
1030
1031        Returns:
1032            A limited Select expression.
1033        """
1034        return (
1035            select("*")
1036            .from_(self.subquery(alias="_l_0", copy=copy))
1037            .limit(expression, dialect=dialect, copy=False, **opts)
1038        )
1039
1040    @property
1041    def ctes(self) -> t.List[CTE]:
1042        """Returns a list of all the CTEs attached to this query."""
1043        with_ = self.args.get("with")
1044        return with_.expressions if with_ else []
1045
1046    @property
1047    def selects(self) -> t.List[Expression]:
1048        """Returns the query's projections."""
1049        raise NotImplementedError("Query objects must implement `selects`")
1050
1051    @property
1052    def named_selects(self) -> t.List[str]:
1053        """Returns the output names of the query's projections."""
1054        raise NotImplementedError("Query objects must implement `named_selects`")
1055
1056    def select(
1057        self: Q,
1058        *expressions: t.Optional[ExpOrStr],
1059        append: bool = True,
1060        dialect: DialectType = None,
1061        copy: bool = True,
1062        **opts,
1063    ) -> Q:
1064        """
1065        Append to or set the SELECT expressions.
1066
1067        Example:
1068            >>> Select().select("x", "y").sql()
1069            'SELECT x, y'
1070
1071        Args:
1072            *expressions: the SQL code strings to parse.
1073                If an `Expression` instance is passed, it will be used as-is.
1074            append: if `True`, add to any existing expressions.
1075                Otherwise, this resets the expressions.
1076            dialect: the dialect used to parse the input expressions.
1077            copy: if `False`, modify this expression instance in-place.
1078            opts: other options to use to parse the input expressions.
1079
1080        Returns:
1081            The modified Query expression.
1082        """
1083        raise NotImplementedError("Query objects must implement `select`")
1084
1085    def with_(
1086        self: Q,
1087        alias: ExpOrStr,
1088        as_: ExpOrStr,
1089        recursive: t.Optional[bool] = None,
1090        append: bool = True,
1091        dialect: DialectType = None,
1092        copy: bool = True,
1093        **opts,
1094    ) -> Q:
1095        """
1096        Append to or set the common table expressions.
1097
1098        Example:
1099            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1100            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1101
1102        Args:
1103            alias: the SQL code string to parse as the table name.
1104                If an `Expression` instance is passed, this is used as-is.
1105            as_: the SQL code string to parse as the table expression.
1106                If an `Expression` instance is passed, it will be used as-is.
1107            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1108            append: if `True`, add to any existing expressions.
1109                Otherwise, this resets the expressions.
1110            dialect: the dialect used to parse the input expression.
1111            copy: if `False`, modify this expression instance in-place.
1112            opts: other options to use to parse the input expressions.
1113
1114        Returns:
1115            The modified expression.
1116        """
1117        return _apply_cte_builder(
1118            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1119        )
1120
1121    def union(
1122        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1123    ) -> Union:
1124        """
1125        Builds a UNION expression.
1126
1127        Example:
1128            >>> import sqlglot
1129            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1130            'SELECT * FROM foo UNION SELECT * FROM bla'
1131
1132        Args:
1133            expression: the SQL code string.
1134                If an `Expression` instance is passed, it will be used as-is.
1135            distinct: set the DISTINCT flag if and only if this is true.
1136            dialect: the dialect used to parse the input expression.
1137            opts: other options to use to parse the input expressions.
1138
1139        Returns:
1140            The new Union expression.
1141        """
1142        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1143
1144    def intersect(
1145        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1146    ) -> Intersect:
1147        """
1148        Builds an INTERSECT expression.
1149
1150        Example:
1151            >>> import sqlglot
1152            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1153            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1154
1155        Args:
1156            expression: the SQL code string.
1157                If an `Expression` instance is passed, it will be used as-is.
1158            distinct: set the DISTINCT flag if and only if this is true.
1159            dialect: the dialect used to parse the input expression.
1160            opts: other options to use to parse the input expressions.
1161
1162        Returns:
1163            The new Intersect expression.
1164        """
1165        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1166
1167    def except_(
1168        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1169    ) -> Except:
1170        """
1171        Builds an EXCEPT expression.
1172
1173        Example:
1174            >>> import sqlglot
1175            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1176            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1177
1178        Args:
1179            expression: the SQL code string.
1180                If an `Expression` instance is passed, it will be used as-is.
1181            distinct: set the DISTINCT flag if and only if this is true.
1182            dialect: the dialect used to parse the input expression.
1183            opts: other options to use to parse the input expressions.
1184
1185        Returns:
1186            The new Except expression.
1187        """
1188        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1189
1190
1191class UDTF(DerivedTable):
1192    @property
1193    def selects(self) -> t.List[Expression]:
1194        alias = self.args.get("alias")
1195        return alias.columns if alias else []
1196
1197
1198class Cache(Expression):
1199    arg_types = {
1200        "this": True,
1201        "lazy": False,
1202        "options": False,
1203        "expression": False,
1204    }
1205
1206
1207class Uncache(Expression):
1208    arg_types = {"this": True, "exists": False}
1209
1210
1211class Refresh(Expression):
1212    pass
1213
1214
1215class DDL(Expression):
1216    @property
1217    def ctes(self) -> t.List[CTE]:
1218        """Returns a list of all the CTEs attached to this statement."""
1219        with_ = self.args.get("with")
1220        return with_.expressions if with_ else []
1221
1222    @property
1223    def selects(self) -> t.List[Expression]:
1224        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1225        return self.expression.selects if isinstance(self.expression, Query) else []
1226
1227    @property
1228    def named_selects(self) -> t.List[str]:
1229        """
1230        If this statement contains a query (e.g. a CTAS), this returns the output
1231        names of the query's projections.
1232        """
1233        return self.expression.named_selects if isinstance(self.expression, Query) else []
1234
1235
1236class DML(Expression):
1237    def returning(
1238        self,
1239        expression: ExpOrStr,
1240        dialect: DialectType = None,
1241        copy: bool = True,
1242        **opts,
1243    ) -> DML:
1244        """
1245        Set the RETURNING expression. Not supported by all dialects.
1246
1247        Example:
1248            >>> delete("tbl").returning("*", dialect="postgres").sql()
1249            'DELETE FROM tbl RETURNING *'
1250
1251        Args:
1252            expression: the SQL code strings to parse.
1253                If an `Expression` instance is passed, it will be used as-is.
1254            dialect: the dialect used to parse the input expressions.
1255            copy: if `False`, modify this expression instance in-place.
1256            opts: other options to use to parse the input expressions.
1257
1258        Returns:
1259            Delete: the modified expression.
1260        """
1261        return _apply_builder(
1262            expression=expression,
1263            instance=self,
1264            arg="returning",
1265            prefix="RETURNING",
1266            dialect=dialect,
1267            copy=copy,
1268            into=Returning,
1269            **opts,
1270        )
1271
1272
1273class Create(DDL):
1274    arg_types = {
1275        "with": False,
1276        "this": True,
1277        "kind": True,
1278        "expression": False,
1279        "exists": False,
1280        "properties": False,
1281        "replace": False,
1282        "unique": False,
1283        "indexes": False,
1284        "no_schema_binding": False,
1285        "begin": False,
1286        "end": False,
1287        "clone": False,
1288    }
1289
1290    @property
1291    def kind(self) -> t.Optional[str]:
1292        kind = self.args.get("kind")
1293        return kind and kind.upper()
1294
1295
1296class SequenceProperties(Expression):
1297    arg_types = {
1298        "increment": False,
1299        "minvalue": False,
1300        "maxvalue": False,
1301        "cache": False,
1302        "start": False,
1303        "owned": False,
1304        "options": False,
1305    }
1306
1307
1308class TruncateTable(Expression):
1309    arg_types = {
1310        "expressions": True,
1311        "is_database": False,
1312        "exists": False,
1313        "only": False,
1314        "cluster": False,
1315        "identity": False,
1316        "option": False,
1317        "partition": False,
1318    }
1319
1320
1321# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1322# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1323# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1324class Clone(Expression):
1325    arg_types = {"this": True, "shallow": False, "copy": False}
1326
1327
1328class Describe(Expression):
1329    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1330
1331
1332class Kill(Expression):
1333    arg_types = {"this": True, "kind": False}
1334
1335
1336class Pragma(Expression):
1337    pass
1338
1339
1340class Set(Expression):
1341    arg_types = {"expressions": False, "unset": False, "tag": False}
1342
1343
1344class Heredoc(Expression):
1345    arg_types = {"this": True, "tag": False}
1346
1347
1348class SetItem(Expression):
1349    arg_types = {
1350        "this": False,
1351        "expressions": False,
1352        "kind": False,
1353        "collate": False,  # MySQL SET NAMES statement
1354        "global": False,
1355    }
1356
1357
1358class Show(Expression):
1359    arg_types = {
1360        "this": True,
1361        "history": False,
1362        "terse": False,
1363        "target": False,
1364        "offset": False,
1365        "starts_with": False,
1366        "limit": False,
1367        "from": False,
1368        "like": False,
1369        "where": False,
1370        "db": False,
1371        "scope": False,
1372        "scope_kind": False,
1373        "full": False,
1374        "mutex": False,
1375        "query": False,
1376        "channel": False,
1377        "global": False,
1378        "log": False,
1379        "position": False,
1380        "types": False,
1381    }
1382
1383
1384class UserDefinedFunction(Expression):
1385    arg_types = {"this": True, "expressions": False, "wrapped": False}
1386
1387
1388class CharacterSet(Expression):
1389    arg_types = {"this": True, "default": False}
1390
1391
1392class With(Expression):
1393    arg_types = {"expressions": True, "recursive": False}
1394
1395    @property
1396    def recursive(self) -> bool:
1397        return bool(self.args.get("recursive"))
1398
1399
1400class WithinGroup(Expression):
1401    arg_types = {"this": True, "expression": False}
1402
1403
1404# clickhouse supports scalar ctes
1405# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1406class CTE(DerivedTable):
1407    arg_types = {"this": True, "alias": True, "scalar": False}
1408
1409
1410class TableAlias(Expression):
1411    arg_types = {"this": False, "columns": False}
1412
1413    @property
1414    def columns(self):
1415        return self.args.get("columns") or []
1416
1417
1418class BitString(Condition):
1419    pass
1420
1421
1422class HexString(Condition):
1423    pass
1424
1425
1426class ByteString(Condition):
1427    pass
1428
1429
1430class RawString(Condition):
1431    pass
1432
1433
1434class UnicodeString(Condition):
1435    arg_types = {"this": True, "escape": False}
1436
1437
1438class Column(Condition):
1439    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1440
1441    @property
1442    def table(self) -> str:
1443        return self.text("table")
1444
1445    @property
1446    def db(self) -> str:
1447        return self.text("db")
1448
1449    @property
1450    def catalog(self) -> str:
1451        return self.text("catalog")
1452
1453    @property
1454    def output_name(self) -> str:
1455        return self.name
1456
1457    @property
1458    def parts(self) -> t.List[Identifier]:
1459        """Return the parts of a column in order catalog, db, table, name."""
1460        return [
1461            t.cast(Identifier, self.args[part])
1462            for part in ("catalog", "db", "table", "this")
1463            if self.args.get(part)
1464        ]
1465
1466    def to_dot(self) -> Dot | Identifier:
1467        """Converts the column into a dot expression."""
1468        parts = self.parts
1469        parent = self.parent
1470
1471        while parent:
1472            if isinstance(parent, Dot):
1473                parts.append(parent.expression)
1474            parent = parent.parent
1475
1476        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1477
1478
1479class ColumnPosition(Expression):
1480    arg_types = {"this": False, "position": True}
1481
1482
1483class ColumnDef(Expression):
1484    arg_types = {
1485        "this": True,
1486        "kind": False,
1487        "constraints": False,
1488        "exists": False,
1489        "position": False,
1490    }
1491
1492    @property
1493    def constraints(self) -> t.List[ColumnConstraint]:
1494        return self.args.get("constraints") or []
1495
1496    @property
1497    def kind(self) -> t.Optional[DataType]:
1498        return self.args.get("kind")
1499
1500
1501class AlterColumn(Expression):
1502    arg_types = {
1503        "this": True,
1504        "dtype": False,
1505        "collate": False,
1506        "using": False,
1507        "default": False,
1508        "drop": False,
1509        "comment": False,
1510    }
1511
1512
1513class RenameColumn(Expression):
1514    arg_types = {"this": True, "to": True, "exists": False}
1515
1516
1517class RenameTable(Expression):
1518    pass
1519
1520
1521class SwapTable(Expression):
1522    pass
1523
1524
1525class Comment(Expression):
1526    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1527
1528
1529class Comprehension(Expression):
1530    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1531
1532
1533# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1534class MergeTreeTTLAction(Expression):
1535    arg_types = {
1536        "this": True,
1537        "delete": False,
1538        "recompress": False,
1539        "to_disk": False,
1540        "to_volume": False,
1541    }
1542
1543
1544# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1545class MergeTreeTTL(Expression):
1546    arg_types = {
1547        "expressions": True,
1548        "where": False,
1549        "group": False,
1550        "aggregates": False,
1551    }
1552
1553
1554# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1555class IndexConstraintOption(Expression):
1556    arg_types = {
1557        "key_block_size": False,
1558        "using": False,
1559        "parser": False,
1560        "comment": False,
1561        "visible": False,
1562        "engine_attr": False,
1563        "secondary_engine_attr": False,
1564    }
1565
1566
1567class ColumnConstraint(Expression):
1568    arg_types = {"this": False, "kind": True}
1569
1570    @property
1571    def kind(self) -> ColumnConstraintKind:
1572        return self.args["kind"]
1573
1574
1575class ColumnConstraintKind(Expression):
1576    pass
1577
1578
1579class AutoIncrementColumnConstraint(ColumnConstraintKind):
1580    pass
1581
1582
1583class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1584    arg_types = {"this": True, "expression": True}
1585
1586
1587class CaseSpecificColumnConstraint(ColumnConstraintKind):
1588    arg_types = {"not_": True}
1589
1590
1591class CharacterSetColumnConstraint(ColumnConstraintKind):
1592    arg_types = {"this": True}
1593
1594
1595class CheckColumnConstraint(ColumnConstraintKind):
1596    arg_types = {"this": True, "enforced": False}
1597
1598
1599class ClusteredColumnConstraint(ColumnConstraintKind):
1600    pass
1601
1602
1603class CollateColumnConstraint(ColumnConstraintKind):
1604    pass
1605
1606
1607class CommentColumnConstraint(ColumnConstraintKind):
1608    pass
1609
1610
1611class CompressColumnConstraint(ColumnConstraintKind):
1612    pass
1613
1614
1615class DateFormatColumnConstraint(ColumnConstraintKind):
1616    arg_types = {"this": True}
1617
1618
1619class DefaultColumnConstraint(ColumnConstraintKind):
1620    pass
1621
1622
1623class EncodeColumnConstraint(ColumnConstraintKind):
1624    pass
1625
1626
1627# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1628class ExcludeColumnConstraint(ColumnConstraintKind):
1629    pass
1630
1631
1632class WithOperator(Expression):
1633    arg_types = {"this": True, "op": True}
1634
1635
1636class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1637    # this: True -> ALWAYS, this: False -> BY DEFAULT
1638    arg_types = {
1639        "this": False,
1640        "expression": False,
1641        "on_null": False,
1642        "start": False,
1643        "increment": False,
1644        "minvalue": False,
1645        "maxvalue": False,
1646        "cycle": False,
1647    }
1648
1649
1650class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1651    arg_types = {"start": False, "hidden": False}
1652
1653
1654# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1655class IndexColumnConstraint(ColumnConstraintKind):
1656    arg_types = {
1657        "this": False,
1658        "schema": True,
1659        "kind": False,
1660        "index_type": False,
1661        "options": False,
1662    }
1663
1664
1665class InlineLengthColumnConstraint(ColumnConstraintKind):
1666    pass
1667
1668
1669class NonClusteredColumnConstraint(ColumnConstraintKind):
1670    pass
1671
1672
1673class NotForReplicationColumnConstraint(ColumnConstraintKind):
1674    arg_types = {}
1675
1676
1677class NotNullColumnConstraint(ColumnConstraintKind):
1678    arg_types = {"allow_null": False}
1679
1680
1681# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1682class OnUpdateColumnConstraint(ColumnConstraintKind):
1683    pass
1684
1685
1686# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1687class TransformColumnConstraint(ColumnConstraintKind):
1688    pass
1689
1690
1691class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1692    arg_types = {"desc": False}
1693
1694
1695class TitleColumnConstraint(ColumnConstraintKind):
1696    pass
1697
1698
1699class UniqueColumnConstraint(ColumnConstraintKind):
1700    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1701
1702
1703class UppercaseColumnConstraint(ColumnConstraintKind):
1704    arg_types: t.Dict[str, t.Any] = {}
1705
1706
1707class PathColumnConstraint(ColumnConstraintKind):
1708    pass
1709
1710
1711# computed column expression
1712# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1713class ComputedColumnConstraint(ColumnConstraintKind):
1714    arg_types = {"this": True, "persisted": False, "not_null": False}
1715
1716
1717class Constraint(Expression):
1718    arg_types = {"this": True, "expressions": True}
1719
1720
1721class Delete(DML):
1722    arg_types = {
1723        "with": False,
1724        "this": False,
1725        "using": False,
1726        "where": False,
1727        "returning": False,
1728        "limit": False,
1729        "tables": False,  # Multiple-Table Syntax (MySQL)
1730    }
1731
1732    def delete(
1733        self,
1734        table: ExpOrStr,
1735        dialect: DialectType = None,
1736        copy: bool = True,
1737        **opts,
1738    ) -> Delete:
1739        """
1740        Create a DELETE expression or replace the table on an existing DELETE expression.
1741
1742        Example:
1743            >>> delete("tbl").sql()
1744            'DELETE FROM tbl'
1745
1746        Args:
1747            table: the table from which to delete.
1748            dialect: the dialect used to parse the input expression.
1749            copy: if `False`, modify this expression instance in-place.
1750            opts: other options to use to parse the input expressions.
1751
1752        Returns:
1753            Delete: the modified expression.
1754        """
1755        return _apply_builder(
1756            expression=table,
1757            instance=self,
1758            arg="this",
1759            dialect=dialect,
1760            into=Table,
1761            copy=copy,
1762            **opts,
1763        )
1764
1765    def where(
1766        self,
1767        *expressions: t.Optional[ExpOrStr],
1768        append: bool = True,
1769        dialect: DialectType = None,
1770        copy: bool = True,
1771        **opts,
1772    ) -> Delete:
1773        """
1774        Append to or set the WHERE expressions.
1775
1776        Example:
1777            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1778            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1779
1780        Args:
1781            *expressions: the SQL code strings to parse.
1782                If an `Expression` instance is passed, it will be used as-is.
1783                Multiple expressions are combined with an AND operator.
1784            append: if `True`, AND the new expressions to any existing expression.
1785                Otherwise, this resets the expression.
1786            dialect: the dialect used to parse the input expressions.
1787            copy: if `False`, modify this expression instance in-place.
1788            opts: other options to use to parse the input expressions.
1789
1790        Returns:
1791            Delete: the modified expression.
1792        """
1793        return _apply_conjunction_builder(
1794            *expressions,
1795            instance=self,
1796            arg="where",
1797            append=append,
1798            into=Where,
1799            dialect=dialect,
1800            copy=copy,
1801            **opts,
1802        )
1803
1804
1805class Drop(Expression):
1806    arg_types = {
1807        "this": False,
1808        "kind": False,
1809        "expressions": False,
1810        "exists": False,
1811        "temporary": False,
1812        "materialized": False,
1813        "cascade": False,
1814        "constraints": False,
1815        "purge": False,
1816    }
1817
1818
1819class Filter(Expression):
1820    arg_types = {"this": True, "expression": True}
1821
1822
1823class Check(Expression):
1824    pass
1825
1826
1827# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1828class Connect(Expression):
1829    arg_types = {"start": False, "connect": True}
1830
1831
1832class Prior(Expression):
1833    pass
1834
1835
1836class Directory(Expression):
1837    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1838    arg_types = {"this": True, "local": False, "row_format": False}
1839
1840
1841class ForeignKey(Expression):
1842    arg_types = {
1843        "expressions": True,
1844        "reference": False,
1845        "delete": False,
1846        "update": False,
1847    }
1848
1849
1850class ColumnPrefix(Expression):
1851    arg_types = {"this": True, "expression": True}
1852
1853
1854class PrimaryKey(Expression):
1855    arg_types = {"expressions": True, "options": False}
1856
1857
1858# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1859# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1860class Into(Expression):
1861    arg_types = {"this": True, "temporary": False, "unlogged": False}
1862
1863
1864class From(Expression):
1865    @property
1866    def name(self) -> str:
1867        return self.this.name
1868
1869    @property
1870    def alias_or_name(self) -> str:
1871        return self.this.alias_or_name
1872
1873
1874class Having(Expression):
1875    pass
1876
1877
1878class Hint(Expression):
1879    arg_types = {"expressions": True}
1880
1881
1882class JoinHint(Expression):
1883    arg_types = {"this": True, "expressions": True}
1884
1885
1886class Identifier(Expression):
1887    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1888
1889    @property
1890    def quoted(self) -> bool:
1891        return bool(self.args.get("quoted"))
1892
1893    @property
1894    def hashable_args(self) -> t.Any:
1895        return (self.this, self.quoted)
1896
1897    @property
1898    def output_name(self) -> str:
1899        return self.name
1900
1901
1902# https://www.postgresql.org/docs/current/indexes-opclass.html
1903class Opclass(Expression):
1904    arg_types = {"this": True, "expression": True}
1905
1906
1907class Index(Expression):
1908    arg_types = {
1909        "this": False,
1910        "table": False,
1911        "unique": False,
1912        "primary": False,
1913        "amp": False,  # teradata
1914        "params": False,
1915    }
1916
1917
1918class IndexParameters(Expression):
1919    arg_types = {
1920        "using": False,
1921        "include": False,
1922        "columns": False,
1923        "with_storage": False,
1924        "partition_by": False,
1925        "tablespace": False,
1926        "where": False,
1927    }
1928
1929
1930class Insert(DDL, DML):
1931    arg_types = {
1932        "hint": False,
1933        "with": False,
1934        "is_function": False,
1935        "this": True,
1936        "expression": False,
1937        "conflict": False,
1938        "returning": False,
1939        "overwrite": False,
1940        "exists": False,
1941        "partition": False,
1942        "alternative": False,
1943        "where": False,
1944        "ignore": False,
1945        "by_name": False,
1946    }
1947
1948    def with_(
1949        self,
1950        alias: ExpOrStr,
1951        as_: ExpOrStr,
1952        recursive: t.Optional[bool] = None,
1953        append: bool = True,
1954        dialect: DialectType = None,
1955        copy: bool = True,
1956        **opts,
1957    ) -> Insert:
1958        """
1959        Append to or set the common table expressions.
1960
1961        Example:
1962            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1963            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1964
1965        Args:
1966            alias: the SQL code string to parse as the table name.
1967                If an `Expression` instance is passed, this is used as-is.
1968            as_: the SQL code string to parse as the table expression.
1969                If an `Expression` instance is passed, it will be used as-is.
1970            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1971            append: if `True`, add to any existing expressions.
1972                Otherwise, this resets the expressions.
1973            dialect: the dialect used to parse the input expression.
1974            copy: if `False`, modify this expression instance in-place.
1975            opts: other options to use to parse the input expressions.
1976
1977        Returns:
1978            The modified expression.
1979        """
1980        return _apply_cte_builder(
1981            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1982        )
1983
1984
1985class OnConflict(Expression):
1986    arg_types = {
1987        "duplicate": False,
1988        "expressions": False,
1989        "action": False,
1990        "conflict_keys": False,
1991        "constraint": False,
1992    }
1993
1994
1995class Returning(Expression):
1996    arg_types = {"expressions": True, "into": False}
1997
1998
1999# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2000class Introducer(Expression):
2001    arg_types = {"this": True, "expression": True}
2002
2003
2004# national char, like n'utf8'
2005class National(Expression):
2006    pass
2007
2008
2009class LoadData(Expression):
2010    arg_types = {
2011        "this": True,
2012        "local": False,
2013        "overwrite": False,
2014        "inpath": True,
2015        "partition": False,
2016        "input_format": False,
2017        "serde": False,
2018    }
2019
2020
2021class Partition(Expression):
2022    arg_types = {"expressions": True}
2023
2024
2025class PartitionRange(Expression):
2026    arg_types = {"this": True, "expression": True}
2027
2028
2029class Fetch(Expression):
2030    arg_types = {
2031        "direction": False,
2032        "count": False,
2033        "percent": False,
2034        "with_ties": False,
2035    }
2036
2037
2038class Group(Expression):
2039    arg_types = {
2040        "expressions": False,
2041        "grouping_sets": False,
2042        "cube": False,
2043        "rollup": False,
2044        "totals": False,
2045        "all": False,
2046    }
2047
2048
2049class Lambda(Expression):
2050    arg_types = {"this": True, "expressions": True}
2051
2052
2053class Limit(Expression):
2054    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2055
2056
2057class Literal(Condition):
2058    arg_types = {"this": True, "is_string": True}
2059
2060    @property
2061    def hashable_args(self) -> t.Any:
2062        return (self.this, self.args.get("is_string"))
2063
2064    @classmethod
2065    def number(cls, number) -> Literal:
2066        return cls(this=str(number), is_string=False)
2067
2068    @classmethod
2069    def string(cls, string) -> Literal:
2070        return cls(this=str(string), is_string=True)
2071
2072    @property
2073    def output_name(self) -> str:
2074        return self.name
2075
2076
2077class Join(Expression):
2078    arg_types = {
2079        "this": True,
2080        "on": False,
2081        "side": False,
2082        "kind": False,
2083        "using": False,
2084        "method": False,
2085        "global": False,
2086        "hint": False,
2087    }
2088
2089    @property
2090    def method(self) -> str:
2091        return self.text("method").upper()
2092
2093    @property
2094    def kind(self) -> str:
2095        return self.text("kind").upper()
2096
2097    @property
2098    def side(self) -> str:
2099        return self.text("side").upper()
2100
2101    @property
2102    def hint(self) -> str:
2103        return self.text("hint").upper()
2104
2105    @property
2106    def alias_or_name(self) -> str:
2107        return self.this.alias_or_name
2108
2109    def on(
2110        self,
2111        *expressions: t.Optional[ExpOrStr],
2112        append: bool = True,
2113        dialect: DialectType = None,
2114        copy: bool = True,
2115        **opts,
2116    ) -> Join:
2117        """
2118        Append to or set the ON expressions.
2119
2120        Example:
2121            >>> import sqlglot
2122            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2123            'JOIN x ON y = 1'
2124
2125        Args:
2126            *expressions: the SQL code strings to parse.
2127                If an `Expression` instance is passed, it will be used as-is.
2128                Multiple expressions are combined with an AND operator.
2129            append: if `True`, AND the new expressions to any existing expression.
2130                Otherwise, this resets the expression.
2131            dialect: the dialect used to parse the input expressions.
2132            copy: if `False`, modify this expression instance in-place.
2133            opts: other options to use to parse the input expressions.
2134
2135        Returns:
2136            The modified Join expression.
2137        """
2138        join = _apply_conjunction_builder(
2139            *expressions,
2140            instance=self,
2141            arg="on",
2142            append=append,
2143            dialect=dialect,
2144            copy=copy,
2145            **opts,
2146        )
2147
2148        if join.kind == "CROSS":
2149            join.set("kind", None)
2150
2151        return join
2152
2153    def using(
2154        self,
2155        *expressions: t.Optional[ExpOrStr],
2156        append: bool = True,
2157        dialect: DialectType = None,
2158        copy: bool = True,
2159        **opts,
2160    ) -> Join:
2161        """
2162        Append to or set the USING expressions.
2163
2164        Example:
2165            >>> import sqlglot
2166            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2167            'JOIN x USING (foo, bla)'
2168
2169        Args:
2170            *expressions: the SQL code strings to parse.
2171                If an `Expression` instance is passed, it will be used as-is.
2172            append: if `True`, concatenate the new expressions to the existing "using" list.
2173                Otherwise, this resets the expression.
2174            dialect: the dialect used to parse the input expressions.
2175            copy: if `False`, modify this expression instance in-place.
2176            opts: other options to use to parse the input expressions.
2177
2178        Returns:
2179            The modified Join expression.
2180        """
2181        join = _apply_list_builder(
2182            *expressions,
2183            instance=self,
2184            arg="using",
2185            append=append,
2186            dialect=dialect,
2187            copy=copy,
2188            **opts,
2189        )
2190
2191        if join.kind == "CROSS":
2192            join.set("kind", None)
2193
2194        return join
2195
2196
2197class Lateral(UDTF):
2198    arg_types = {
2199        "this": True,
2200        "view": False,
2201        "outer": False,
2202        "alias": False,
2203        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2204    }
2205
2206
2207class MatchRecognize(Expression):
2208    arg_types = {
2209        "partition_by": False,
2210        "order": False,
2211        "measures": False,
2212        "rows": False,
2213        "after": False,
2214        "pattern": False,
2215        "define": False,
2216        "alias": False,
2217    }
2218
2219
2220# Clickhouse FROM FINAL modifier
2221# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2222class Final(Expression):
2223    pass
2224
2225
2226class Offset(Expression):
2227    arg_types = {"this": False, "expression": True, "expressions": False}
2228
2229
2230class Order(Expression):
2231    arg_types = {
2232        "this": False,
2233        "expressions": True,
2234        "interpolate": False,
2235        "siblings": False,
2236    }
2237
2238
2239# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2240class WithFill(Expression):
2241    arg_types = {"from": False, "to": False, "step": False}
2242
2243
2244# hive specific sorts
2245# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2246class Cluster(Order):
2247    pass
2248
2249
2250class Distribute(Order):
2251    pass
2252
2253
2254class Sort(Order):
2255    pass
2256
2257
2258class Ordered(Expression):
2259    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2260
2261
2262class Property(Expression):
2263    arg_types = {"this": True, "value": True}
2264
2265
2266class AlgorithmProperty(Property):
2267    arg_types = {"this": True}
2268
2269
2270class AutoIncrementProperty(Property):
2271    arg_types = {"this": True}
2272
2273
2274# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2275class AutoRefreshProperty(Property):
2276    arg_types = {"this": True}
2277
2278
2279class BackupProperty(Property):
2280    arg_types = {"this": True}
2281
2282
2283class BlockCompressionProperty(Property):
2284    arg_types = {
2285        "autotemp": False,
2286        "always": False,
2287        "default": False,
2288        "manual": False,
2289        "never": False,
2290    }
2291
2292
2293class CharacterSetProperty(Property):
2294    arg_types = {"this": True, "default": True}
2295
2296
2297class ChecksumProperty(Property):
2298    arg_types = {"on": False, "default": False}
2299
2300
2301class CollateProperty(Property):
2302    arg_types = {"this": True, "default": False}
2303
2304
2305class CopyGrantsProperty(Property):
2306    arg_types = {}
2307
2308
2309class DataBlocksizeProperty(Property):
2310    arg_types = {
2311        "size": False,
2312        "units": False,
2313        "minimum": False,
2314        "maximum": False,
2315        "default": False,
2316    }
2317
2318
2319class DefinerProperty(Property):
2320    arg_types = {"this": True}
2321
2322
2323class DistKeyProperty(Property):
2324    arg_types = {"this": True}
2325
2326
2327class DistStyleProperty(Property):
2328    arg_types = {"this": True}
2329
2330
2331class EngineProperty(Property):
2332    arg_types = {"this": True}
2333
2334
2335class HeapProperty(Property):
2336    arg_types = {}
2337
2338
2339class ToTableProperty(Property):
2340    arg_types = {"this": True}
2341
2342
2343class ExecuteAsProperty(Property):
2344    arg_types = {"this": True}
2345
2346
2347class ExternalProperty(Property):
2348    arg_types = {"this": False}
2349
2350
2351class FallbackProperty(Property):
2352    arg_types = {"no": True, "protection": False}
2353
2354
2355class FileFormatProperty(Property):
2356    arg_types = {"this": True}
2357
2358
2359class FreespaceProperty(Property):
2360    arg_types = {"this": True, "percent": False}
2361
2362
2363class GlobalProperty(Property):
2364    arg_types = {}
2365
2366
2367class IcebergProperty(Property):
2368    arg_types = {}
2369
2370
2371class InheritsProperty(Property):
2372    arg_types = {"expressions": True}
2373
2374
2375class InputModelProperty(Property):
2376    arg_types = {"this": True}
2377
2378
2379class OutputModelProperty(Property):
2380    arg_types = {"this": True}
2381
2382
2383class IsolatedLoadingProperty(Property):
2384    arg_types = {
2385        "no": False,
2386        "concurrent": False,
2387        "for_all": False,
2388        "for_insert": False,
2389        "for_none": False,
2390    }
2391
2392
2393class JournalProperty(Property):
2394    arg_types = {
2395        "no": False,
2396        "dual": False,
2397        "before": False,
2398        "local": False,
2399        "after": False,
2400    }
2401
2402
2403class LanguageProperty(Property):
2404    arg_types = {"this": True}
2405
2406
2407# spark ddl
2408class ClusteredByProperty(Property):
2409    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2410
2411
2412class DictProperty(Property):
2413    arg_types = {"this": True, "kind": True, "settings": False}
2414
2415
2416class DictSubProperty(Property):
2417    pass
2418
2419
2420class DictRange(Property):
2421    arg_types = {"this": True, "min": True, "max": True}
2422
2423
2424# Clickhouse CREATE ... ON CLUSTER modifier
2425# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2426class OnCluster(Property):
2427    arg_types = {"this": True}
2428
2429
2430class LikeProperty(Property):
2431    arg_types = {"this": True, "expressions": False}
2432
2433
2434class LocationProperty(Property):
2435    arg_types = {"this": True}
2436
2437
2438class LockProperty(Property):
2439    arg_types = {"this": True}
2440
2441
2442class LockingProperty(Property):
2443    arg_types = {
2444        "this": False,
2445        "kind": True,
2446        "for_or_in": False,
2447        "lock_type": True,
2448        "override": False,
2449    }
2450
2451
2452class LogProperty(Property):
2453    arg_types = {"no": True}
2454
2455
2456class MaterializedProperty(Property):
2457    arg_types = {"this": False}
2458
2459
2460class MergeBlockRatioProperty(Property):
2461    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2462
2463
2464class NoPrimaryIndexProperty(Property):
2465    arg_types = {}
2466
2467
2468class OnProperty(Property):
2469    arg_types = {"this": True}
2470
2471
2472class OnCommitProperty(Property):
2473    arg_types = {"delete": False}
2474
2475
2476class PartitionedByProperty(Property):
2477    arg_types = {"this": True}
2478
2479
2480# https://www.postgresql.org/docs/current/sql-createtable.html
2481class PartitionBoundSpec(Expression):
2482    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2483    arg_types = {
2484        "this": False,
2485        "expression": False,
2486        "from_expressions": False,
2487        "to_expressions": False,
2488    }
2489
2490
2491class PartitionedOfProperty(Property):
2492    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2493    arg_types = {"this": True, "expression": True}
2494
2495
2496class RemoteWithConnectionModelProperty(Property):
2497    arg_types = {"this": True}
2498
2499
2500class ReturnsProperty(Property):
2501    arg_types = {"this": True, "is_table": False, "table": False}
2502
2503
2504class RowFormatProperty(Property):
2505    arg_types = {"this": True}
2506
2507
2508class RowFormatDelimitedProperty(Property):
2509    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2510    arg_types = {
2511        "fields": False,
2512        "escaped": False,
2513        "collection_items": False,
2514        "map_keys": False,
2515        "lines": False,
2516        "null": False,
2517        "serde": False,
2518    }
2519
2520
2521class RowFormatSerdeProperty(Property):
2522    arg_types = {"this": True, "serde_properties": False}
2523
2524
2525# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2526class QueryTransform(Expression):
2527    arg_types = {
2528        "expressions": True,
2529        "command_script": True,
2530        "schema": False,
2531        "row_format_before": False,
2532        "record_writer": False,
2533        "row_format_after": False,
2534        "record_reader": False,
2535    }
2536
2537
2538class SampleProperty(Property):
2539    arg_types = {"this": True}
2540
2541
2542class SchemaCommentProperty(Property):
2543    arg_types = {"this": True}
2544
2545
2546class SerdeProperties(Property):
2547    arg_types = {"expressions": True}
2548
2549
2550class SetProperty(Property):
2551    arg_types = {"multi": True}
2552
2553
2554class SharingProperty(Property):
2555    arg_types = {"this": False}
2556
2557
2558class SetConfigProperty(Property):
2559    arg_types = {"this": True}
2560
2561
2562class SettingsProperty(Property):
2563    arg_types = {"expressions": True}
2564
2565
2566class SortKeyProperty(Property):
2567    arg_types = {"this": True, "compound": False}
2568
2569
2570class SqlReadWriteProperty(Property):
2571    arg_types = {"this": True}
2572
2573
2574class SqlSecurityProperty(Property):
2575    arg_types = {"definer": True}
2576
2577
2578class StabilityProperty(Property):
2579    arg_types = {"this": True}
2580
2581
2582class TemporaryProperty(Property):
2583    arg_types = {"this": False}
2584
2585
2586class TransformModelProperty(Property):
2587    arg_types = {"expressions": True}
2588
2589
2590class TransientProperty(Property):
2591    arg_types = {"this": False}
2592
2593
2594class UnloggedProperty(Property):
2595    arg_types = {}
2596
2597
2598class VolatileProperty(Property):
2599    arg_types = {"this": False}
2600
2601
2602class WithDataProperty(Property):
2603    arg_types = {"no": True, "statistics": False}
2604
2605
2606class WithJournalTableProperty(Property):
2607    arg_types = {"this": True}
2608
2609
2610class WithSystemVersioningProperty(Property):
2611    # this -> history table name, expression -> data consistency check
2612    arg_types = {"this": False, "expression": False}
2613
2614
2615class Properties(Expression):
2616    arg_types = {"expressions": True}
2617
2618    NAME_TO_PROPERTY = {
2619        "ALGORITHM": AlgorithmProperty,
2620        "AUTO_INCREMENT": AutoIncrementProperty,
2621        "CHARACTER SET": CharacterSetProperty,
2622        "CLUSTERED_BY": ClusteredByProperty,
2623        "COLLATE": CollateProperty,
2624        "COMMENT": SchemaCommentProperty,
2625        "DEFINER": DefinerProperty,
2626        "DISTKEY": DistKeyProperty,
2627        "DISTSTYLE": DistStyleProperty,
2628        "ENGINE": EngineProperty,
2629        "EXECUTE AS": ExecuteAsProperty,
2630        "FORMAT": FileFormatProperty,
2631        "LANGUAGE": LanguageProperty,
2632        "LOCATION": LocationProperty,
2633        "LOCK": LockProperty,
2634        "PARTITIONED_BY": PartitionedByProperty,
2635        "RETURNS": ReturnsProperty,
2636        "ROW_FORMAT": RowFormatProperty,
2637        "SORTKEY": SortKeyProperty,
2638    }
2639
2640    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2641
2642    # CREATE property locations
2643    # Form: schema specified
2644    #   create [POST_CREATE]
2645    #     table a [POST_NAME]
2646    #     (b int) [POST_SCHEMA]
2647    #     with ([POST_WITH])
2648    #     index (b) [POST_INDEX]
2649    #
2650    # Form: alias selection
2651    #   create [POST_CREATE]
2652    #     table a [POST_NAME]
2653    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2654    #     index (c) [POST_INDEX]
2655    class Location(AutoName):
2656        POST_CREATE = auto()
2657        POST_NAME = auto()
2658        POST_SCHEMA = auto()
2659        POST_WITH = auto()
2660        POST_ALIAS = auto()
2661        POST_EXPRESSION = auto()
2662        POST_INDEX = auto()
2663        UNSUPPORTED = auto()
2664
2665    @classmethod
2666    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2667        expressions = []
2668        for key, value in properties_dict.items():
2669            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2670            if property_cls:
2671                expressions.append(property_cls(this=convert(value)))
2672            else:
2673                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2674
2675        return cls(expressions=expressions)
2676
2677
2678class Qualify(Expression):
2679    pass
2680
2681
2682class InputOutputFormat(Expression):
2683    arg_types = {"input_format": False, "output_format": False}
2684
2685
2686# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2687class Return(Expression):
2688    pass
2689
2690
2691class Reference(Expression):
2692    arg_types = {"this": True, "expressions": False, "options": False}
2693
2694
2695class Tuple(Expression):
2696    arg_types = {"expressions": False}
2697
2698    def isin(
2699        self,
2700        *expressions: t.Any,
2701        query: t.Optional[ExpOrStr] = None,
2702        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2703        copy: bool = True,
2704        **opts,
2705    ) -> In:
2706        return In(
2707            this=maybe_copy(self, copy),
2708            expressions=[convert(e, copy=copy) for e in expressions],
2709            query=maybe_parse(query, copy=copy, **opts) if query else None,
2710            unnest=(
2711                Unnest(
2712                    expressions=[
2713                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2714                        for e in ensure_list(unnest)
2715                    ]
2716                )
2717                if unnest
2718                else None
2719            ),
2720        )
2721
2722
2723QUERY_MODIFIERS = {
2724    "match": False,
2725    "laterals": False,
2726    "joins": False,
2727    "connect": False,
2728    "pivots": False,
2729    "prewhere": False,
2730    "where": False,
2731    "group": False,
2732    "having": False,
2733    "qualify": False,
2734    "windows": False,
2735    "distribute": False,
2736    "sort": False,
2737    "cluster": False,
2738    "order": False,
2739    "limit": False,
2740    "offset": False,
2741    "locks": False,
2742    "sample": False,
2743    "settings": False,
2744    "format": False,
2745    "options": False,
2746}
2747
2748
2749# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2750# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2751class QueryOption(Expression):
2752    arg_types = {"this": True, "expression": False}
2753
2754
2755# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2756class WithTableHint(Expression):
2757    arg_types = {"expressions": True}
2758
2759
2760# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2761class IndexTableHint(Expression):
2762    arg_types = {"this": True, "expressions": False, "target": False}
2763
2764
2765# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2766class HistoricalData(Expression):
2767    arg_types = {"this": True, "kind": True, "expression": True}
2768
2769
2770class Table(Expression):
2771    arg_types = {
2772        "this": False,
2773        "alias": False,
2774        "db": False,
2775        "catalog": False,
2776        "laterals": False,
2777        "joins": False,
2778        "pivots": False,
2779        "hints": False,
2780        "system_time": False,
2781        "version": False,
2782        "format": False,
2783        "pattern": False,
2784        "ordinality": False,
2785        "when": False,
2786        "only": False,
2787    }
2788
2789    @property
2790    def name(self) -> str:
2791        if isinstance(self.this, Func):
2792            return ""
2793        return self.this.name
2794
2795    @property
2796    def db(self) -> str:
2797        return self.text("db")
2798
2799    @property
2800    def catalog(self) -> str:
2801        return self.text("catalog")
2802
2803    @property
2804    def selects(self) -> t.List[Expression]:
2805        return []
2806
2807    @property
2808    def named_selects(self) -> t.List[str]:
2809        return []
2810
2811    @property
2812    def parts(self) -> t.List[Expression]:
2813        """Return the parts of a table in order catalog, db, table."""
2814        parts: t.List[Expression] = []
2815
2816        for arg in ("catalog", "db", "this"):
2817            part = self.args.get(arg)
2818
2819            if isinstance(part, Dot):
2820                parts.extend(part.flatten())
2821            elif isinstance(part, Expression):
2822                parts.append(part)
2823
2824        return parts
2825
2826    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2827        parts = self.parts
2828        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2829        alias = self.args.get("alias")
2830        if alias:
2831            col = alias_(col, alias.this, copy=copy)
2832        return col
2833
2834
2835class Union(Query):
2836    arg_types = {
2837        "with": False,
2838        "this": True,
2839        "expression": True,
2840        "distinct": False,
2841        "by_name": False,
2842        **QUERY_MODIFIERS,
2843    }
2844
2845    def select(
2846        self,
2847        *expressions: t.Optional[ExpOrStr],
2848        append: bool = True,
2849        dialect: DialectType = None,
2850        copy: bool = True,
2851        **opts,
2852    ) -> Union:
2853        this = maybe_copy(self, copy)
2854        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2855        this.expression.unnest().select(
2856            *expressions, append=append, dialect=dialect, copy=False, **opts
2857        )
2858        return this
2859
2860    @property
2861    def named_selects(self) -> t.List[str]:
2862        return self.this.unnest().named_selects
2863
2864    @property
2865    def is_star(self) -> bool:
2866        return self.this.is_star or self.expression.is_star
2867
2868    @property
2869    def selects(self) -> t.List[Expression]:
2870        return self.this.unnest().selects
2871
2872    @property
2873    def left(self) -> Expression:
2874        return self.this
2875
2876    @property
2877    def right(self) -> Expression:
2878        return self.expression
2879
2880
2881class Except(Union):
2882    pass
2883
2884
2885class Intersect(Union):
2886    pass
2887
2888
2889class Unnest(UDTF):
2890    arg_types = {
2891        "expressions": True,
2892        "alias": False,
2893        "offset": False,
2894    }
2895
2896    @property
2897    def selects(self) -> t.List[Expression]:
2898        columns = super().selects
2899        offset = self.args.get("offset")
2900        if offset:
2901            columns = columns + [to_identifier("offset") if offset is True else offset]
2902        return columns
2903
2904
2905class Update(Expression):
2906    arg_types = {
2907        "with": False,
2908        "this": False,
2909        "expressions": True,
2910        "from": False,
2911        "where": False,
2912        "returning": False,
2913        "order": False,
2914        "limit": False,
2915    }
2916
2917
2918class Values(UDTF):
2919    arg_types = {"expressions": True, "alias": False}
2920
2921
2922class Var(Expression):
2923    pass
2924
2925
2926class Version(Expression):
2927    """
2928    Time travel, iceberg, bigquery etc
2929    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2930    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2931    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2932    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2933    this is either TIMESTAMP or VERSION
2934    kind is ("AS OF", "BETWEEN")
2935    """
2936
2937    arg_types = {"this": True, "kind": True, "expression": False}
2938
2939
2940class Schema(Expression):
2941    arg_types = {"this": False, "expressions": False}
2942
2943
2944# https://dev.mysql.com/doc/refman/8.0/en/select.html
2945# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2946class Lock(Expression):
2947    arg_types = {"update": True, "expressions": False, "wait": False}
2948
2949
2950class Select(Query):
2951    arg_types = {
2952        "with": False,
2953        "kind": False,
2954        "expressions": False,
2955        "hint": False,
2956        "distinct": False,
2957        "into": False,
2958        "from": False,
2959        **QUERY_MODIFIERS,
2960    }
2961
2962    def from_(
2963        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2964    ) -> Select:
2965        """
2966        Set the FROM expression.
2967
2968        Example:
2969            >>> Select().from_("tbl").select("x").sql()
2970            'SELECT x FROM tbl'
2971
2972        Args:
2973            expression : the SQL code strings to parse.
2974                If a `From` instance is passed, this is used as-is.
2975                If another `Expression` instance is passed, it will be wrapped in a `From`.
2976            dialect: the dialect used to parse the input expression.
2977            copy: if `False`, modify this expression instance in-place.
2978            opts: other options to use to parse the input expressions.
2979
2980        Returns:
2981            The modified Select expression.
2982        """
2983        return _apply_builder(
2984            expression=expression,
2985            instance=self,
2986            arg="from",
2987            into=From,
2988            prefix="FROM",
2989            dialect=dialect,
2990            copy=copy,
2991            **opts,
2992        )
2993
2994    def group_by(
2995        self,
2996        *expressions: t.Optional[ExpOrStr],
2997        append: bool = True,
2998        dialect: DialectType = None,
2999        copy: bool = True,
3000        **opts,
3001    ) -> Select:
3002        """
3003        Set the GROUP BY expression.
3004
3005        Example:
3006            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3007            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3008
3009        Args:
3010            *expressions: the SQL code strings to parse.
3011                If a `Group` instance is passed, this is used as-is.
3012                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3013                If nothing is passed in then a group by is not applied to the expression
3014            append: if `True`, add to any existing expressions.
3015                Otherwise, this flattens all the `Group` expression into a single expression.
3016            dialect: the dialect used to parse the input expression.
3017            copy: if `False`, modify this expression instance in-place.
3018            opts: other options to use to parse the input expressions.
3019
3020        Returns:
3021            The modified Select expression.
3022        """
3023        if not expressions:
3024            return self if not copy else self.copy()
3025
3026        return _apply_child_list_builder(
3027            *expressions,
3028            instance=self,
3029            arg="group",
3030            append=append,
3031            copy=copy,
3032            prefix="GROUP BY",
3033            into=Group,
3034            dialect=dialect,
3035            **opts,
3036        )
3037
3038    def order_by(
3039        self,
3040        *expressions: t.Optional[ExpOrStr],
3041        append: bool = True,
3042        dialect: DialectType = None,
3043        copy: bool = True,
3044        **opts,
3045    ) -> Select:
3046        """
3047        Set the ORDER BY expression.
3048
3049        Example:
3050            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
3051            'SELECT x FROM tbl ORDER BY x DESC'
3052
3053        Args:
3054            *expressions: the SQL code strings to parse.
3055                If a `Group` instance is passed, this is used as-is.
3056                If another `Expression` instance is passed, it will be wrapped in a `Order`.
3057            append: if `True`, add to any existing expressions.
3058                Otherwise, this flattens all the `Order` expression into a single expression.
3059            dialect: the dialect used to parse the input expression.
3060            copy: if `False`, modify this expression instance in-place.
3061            opts: other options to use to parse the input expressions.
3062
3063        Returns:
3064            The modified Select expression.
3065        """
3066        return _apply_child_list_builder(
3067            *expressions,
3068            instance=self,
3069            arg="order",
3070            append=append,
3071            copy=copy,
3072            prefix="ORDER BY",
3073            into=Order,
3074            dialect=dialect,
3075            **opts,
3076        )
3077
3078    def sort_by(
3079        self,
3080        *expressions: t.Optional[ExpOrStr],
3081        append: bool = True,
3082        dialect: DialectType = None,
3083        copy: bool = True,
3084        **opts,
3085    ) -> Select:
3086        """
3087        Set the SORT BY expression.
3088
3089        Example:
3090            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3091            'SELECT x FROM tbl SORT BY x DESC'
3092
3093        Args:
3094            *expressions: the SQL code strings to parse.
3095                If a `Group` instance is passed, this is used as-is.
3096                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3097            append: if `True`, add to any existing expressions.
3098                Otherwise, this flattens all the `Order` expression into a single expression.
3099            dialect: the dialect used to parse the input expression.
3100            copy: if `False`, modify this expression instance in-place.
3101            opts: other options to use to parse the input expressions.
3102
3103        Returns:
3104            The modified Select expression.
3105        """
3106        return _apply_child_list_builder(
3107            *expressions,
3108            instance=self,
3109            arg="sort",
3110            append=append,
3111            copy=copy,
3112            prefix="SORT BY",
3113            into=Sort,
3114            dialect=dialect,
3115            **opts,
3116        )
3117
3118    def cluster_by(
3119        self,
3120        *expressions: t.Optional[ExpOrStr],
3121        append: bool = True,
3122        dialect: DialectType = None,
3123        copy: bool = True,
3124        **opts,
3125    ) -> Select:
3126        """
3127        Set the CLUSTER BY expression.
3128
3129        Example:
3130            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3131            'SELECT x FROM tbl CLUSTER BY x DESC'
3132
3133        Args:
3134            *expressions: the SQL code strings to parse.
3135                If a `Group` instance is passed, this is used as-is.
3136                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3137            append: if `True`, add to any existing expressions.
3138                Otherwise, this flattens all the `Order` expression into a single expression.
3139            dialect: the dialect used to parse the input expression.
3140            copy: if `False`, modify this expression instance in-place.
3141            opts: other options to use to parse the input expressions.
3142
3143        Returns:
3144            The modified Select expression.
3145        """
3146        return _apply_child_list_builder(
3147            *expressions,
3148            instance=self,
3149            arg="cluster",
3150            append=append,
3151            copy=copy,
3152            prefix="CLUSTER BY",
3153            into=Cluster,
3154            dialect=dialect,
3155            **opts,
3156        )
3157
3158    def limit(
3159        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3160    ) -> Select:
3161        return _apply_builder(
3162            expression=expression,
3163            instance=self,
3164            arg="limit",
3165            into=Limit,
3166            prefix="LIMIT",
3167            dialect=dialect,
3168            copy=copy,
3169            into_arg="expression",
3170            **opts,
3171        )
3172
3173    def offset(
3174        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3175    ) -> Select:
3176        """
3177        Set the OFFSET expression.
3178
3179        Example:
3180            >>> Select().from_("tbl").select("x").offset(10).sql()
3181            'SELECT x FROM tbl OFFSET 10'
3182
3183        Args:
3184            expression: the SQL code string to parse.
3185                This can also be an integer.
3186                If a `Offset` instance is passed, this is used as-is.
3187                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3188            dialect: the dialect used to parse the input expression.
3189            copy: if `False`, modify this expression instance in-place.
3190            opts: other options to use to parse the input expressions.
3191
3192        Returns:
3193            The modified Select expression.
3194        """
3195        return _apply_builder(
3196            expression=expression,
3197            instance=self,
3198            arg="offset",
3199            into=Offset,
3200            prefix="OFFSET",
3201            dialect=dialect,
3202            copy=copy,
3203            into_arg="expression",
3204            **opts,
3205        )
3206
3207    def select(
3208        self,
3209        *expressions: t.Optional[ExpOrStr],
3210        append: bool = True,
3211        dialect: DialectType = None,
3212        copy: bool = True,
3213        **opts,
3214    ) -> Select:
3215        return _apply_list_builder(
3216            *expressions,
3217            instance=self,
3218            arg="expressions",
3219            append=append,
3220            dialect=dialect,
3221            into=Expression,
3222            copy=copy,
3223            **opts,
3224        )
3225
3226    def lateral(
3227        self,
3228        *expressions: t.Optional[ExpOrStr],
3229        append: bool = True,
3230        dialect: DialectType = None,
3231        copy: bool = True,
3232        **opts,
3233    ) -> Select:
3234        """
3235        Append to or set the LATERAL expressions.
3236
3237        Example:
3238            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3239            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3240
3241        Args:
3242            *expressions: the SQL code strings to parse.
3243                If an `Expression` instance is passed, it will be used as-is.
3244            append: if `True`, add to any existing expressions.
3245                Otherwise, this resets the expressions.
3246            dialect: the dialect used to parse the input expressions.
3247            copy: if `False`, modify this expression instance in-place.
3248            opts: other options to use to parse the input expressions.
3249
3250        Returns:
3251            The modified Select expression.
3252        """
3253        return _apply_list_builder(
3254            *expressions,
3255            instance=self,
3256            arg="laterals",
3257            append=append,
3258            into=Lateral,
3259            prefix="LATERAL VIEW",
3260            dialect=dialect,
3261            copy=copy,
3262            **opts,
3263        )
3264
3265    def join(
3266        self,
3267        expression: ExpOrStr,
3268        on: t.Optional[ExpOrStr] = None,
3269        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3270        append: bool = True,
3271        join_type: t.Optional[str] = None,
3272        join_alias: t.Optional[Identifier | str] = None,
3273        dialect: DialectType = None,
3274        copy: bool = True,
3275        **opts,
3276    ) -> Select:
3277        """
3278        Append to or set the JOIN expressions.
3279
3280        Example:
3281            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3282            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3283
3284            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3285            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3286
3287            Use `join_type` to change the type of join:
3288
3289            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3290            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3291
3292        Args:
3293            expression: the SQL code string to parse.
3294                If an `Expression` instance is passed, it will be used as-is.
3295            on: optionally specify the join "on" criteria as a SQL string.
3296                If an `Expression` instance is passed, it will be used as-is.
3297            using: optionally specify the join "using" criteria as a SQL string.
3298                If an `Expression` instance is passed, it will be used as-is.
3299            append: if `True`, add to any existing expressions.
3300                Otherwise, this resets the expressions.
3301            join_type: if set, alter the parsed join type.
3302            join_alias: an optional alias for the joined source.
3303            dialect: the dialect used to parse the input expressions.
3304            copy: if `False`, modify this expression instance in-place.
3305            opts: other options to use to parse the input expressions.
3306
3307        Returns:
3308            Select: the modified expression.
3309        """
3310        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3311
3312        try:
3313            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3314        except ParseError:
3315            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3316
3317        join = expression if isinstance(expression, Join) else Join(this=expression)
3318
3319        if isinstance(join.this, Select):
3320            join.this.replace(join.this.subquery())
3321
3322        if join_type:
3323            method: t.Optional[Token]
3324            side: t.Optional[Token]
3325            kind: t.Optional[Token]
3326
3327            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3328
3329            if method:
3330                join.set("method", method.text)
3331            if side:
3332                join.set("side", side.text)
3333            if kind:
3334                join.set("kind", kind.text)
3335
3336        if on:
3337            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3338            join.set("on", on)
3339
3340        if using:
3341            join = _apply_list_builder(
3342                *ensure_list(using),
3343                instance=join,
3344                arg="using",
3345                append=append,
3346                copy=copy,
3347                into=Identifier,
3348                **opts,
3349            )
3350
3351        if join_alias:
3352            join.set("this", alias_(join.this, join_alias, table=True))
3353
3354        return _apply_list_builder(
3355            join,
3356            instance=self,
3357            arg="joins",
3358            append=append,
3359            copy=copy,
3360            **opts,
3361        )
3362
3363    def where(
3364        self,
3365        *expressions: t.Optional[ExpOrStr],
3366        append: bool = True,
3367        dialect: DialectType = None,
3368        copy: bool = True,
3369        **opts,
3370    ) -> Select:
3371        """
3372        Append to or set the WHERE expressions.
3373
3374        Example:
3375            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3376            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3377
3378        Args:
3379            *expressions: the SQL code strings to parse.
3380                If an `Expression` instance is passed, it will be used as-is.
3381                Multiple expressions are combined with an AND operator.
3382            append: if `True`, AND the new expressions to any existing expression.
3383                Otherwise, this resets the expression.
3384            dialect: the dialect used to parse the input expressions.
3385            copy: if `False`, modify this expression instance in-place.
3386            opts: other options to use to parse the input expressions.
3387
3388        Returns:
3389            Select: the modified expression.
3390        """
3391        return _apply_conjunction_builder(
3392            *expressions,
3393            instance=self,
3394            arg="where",
3395            append=append,
3396            into=Where,
3397            dialect=dialect,
3398            copy=copy,
3399            **opts,
3400        )
3401
3402    def having(
3403        self,
3404        *expressions: t.Optional[ExpOrStr],
3405        append: bool = True,
3406        dialect: DialectType = None,
3407        copy: bool = True,
3408        **opts,
3409    ) -> Select:
3410        """
3411        Append to or set the HAVING expressions.
3412
3413        Example:
3414            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3415            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3416
3417        Args:
3418            *expressions: the SQL code strings to parse.
3419                If an `Expression` instance is passed, it will be used as-is.
3420                Multiple expressions are combined with an AND operator.
3421            append: if `True`, AND the new expressions to any existing expression.
3422                Otherwise, this resets the expression.
3423            dialect: the dialect used to parse the input expressions.
3424            copy: if `False`, modify this expression instance in-place.
3425            opts: other options to use to parse the input expressions.
3426
3427        Returns:
3428            The modified Select expression.
3429        """
3430        return _apply_conjunction_builder(
3431            *expressions,
3432            instance=self,
3433            arg="having",
3434            append=append,
3435            into=Having,
3436            dialect=dialect,
3437            copy=copy,
3438            **opts,
3439        )
3440
3441    def window(
3442        self,
3443        *expressions: t.Optional[ExpOrStr],
3444        append: bool = True,
3445        dialect: DialectType = None,
3446        copy: bool = True,
3447        **opts,
3448    ) -> Select:
3449        return _apply_list_builder(
3450            *expressions,
3451            instance=self,
3452            arg="windows",
3453            append=append,
3454            into=Window,
3455            dialect=dialect,
3456            copy=copy,
3457            **opts,
3458        )
3459
3460    def qualify(
3461        self,
3462        *expressions: t.Optional[ExpOrStr],
3463        append: bool = True,
3464        dialect: DialectType = None,
3465        copy: bool = True,
3466        **opts,
3467    ) -> Select:
3468        return _apply_conjunction_builder(
3469            *expressions,
3470            instance=self,
3471            arg="qualify",
3472            append=append,
3473            into=Qualify,
3474            dialect=dialect,
3475            copy=copy,
3476            **opts,
3477        )
3478
3479    def distinct(
3480        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3481    ) -> Select:
3482        """
3483        Set the OFFSET expression.
3484
3485        Example:
3486            >>> Select().from_("tbl").select("x").distinct().sql()
3487            'SELECT DISTINCT x FROM tbl'
3488
3489        Args:
3490            ons: the expressions to distinct on
3491            distinct: whether the Select should be distinct
3492            copy: if `False`, modify this expression instance in-place.
3493
3494        Returns:
3495            Select: the modified expression.
3496        """
3497        instance = maybe_copy(self, copy)
3498        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3499        instance.set("distinct", Distinct(on=on) if distinct else None)
3500        return instance
3501
3502    def ctas(
3503        self,
3504        table: ExpOrStr,
3505        properties: t.Optional[t.Dict] = None,
3506        dialect: DialectType = None,
3507        copy: bool = True,
3508        **opts,
3509    ) -> Create:
3510        """
3511        Convert this expression to a CREATE TABLE AS statement.
3512
3513        Example:
3514            >>> Select().select("*").from_("tbl").ctas("x").sql()
3515            'CREATE TABLE x AS SELECT * FROM tbl'
3516
3517        Args:
3518            table: the SQL code string to parse as the table name.
3519                If another `Expression` instance is passed, it will be used as-is.
3520            properties: an optional mapping of table properties
3521            dialect: the dialect used to parse the input table.
3522            copy: if `False`, modify this expression instance in-place.
3523            opts: other options to use to parse the input table.
3524
3525        Returns:
3526            The new Create expression.
3527        """
3528        instance = maybe_copy(self, copy)
3529        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3530
3531        properties_expression = None
3532        if properties:
3533            properties_expression = Properties.from_dict(properties)
3534
3535        return Create(
3536            this=table_expression,
3537            kind="TABLE",
3538            expression=instance,
3539            properties=properties_expression,
3540        )
3541
3542    def lock(self, update: bool = True, copy: bool = True) -> Select:
3543        """
3544        Set the locking read mode for this expression.
3545
3546        Examples:
3547            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3548            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3549
3550            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3551            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3552
3553        Args:
3554            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3555            copy: if `False`, modify this expression instance in-place.
3556
3557        Returns:
3558            The modified expression.
3559        """
3560        inst = maybe_copy(self, copy)
3561        inst.set("locks", [Lock(update=update)])
3562
3563        return inst
3564
3565    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3566        """
3567        Set hints for this expression.
3568
3569        Examples:
3570            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3571            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3572
3573        Args:
3574            hints: The SQL code strings to parse as the hints.
3575                If an `Expression` instance is passed, it will be used as-is.
3576            dialect: The dialect used to parse the hints.
3577            copy: If `False`, modify this expression instance in-place.
3578
3579        Returns:
3580            The modified expression.
3581        """
3582        inst = maybe_copy(self, copy)
3583        inst.set(
3584            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3585        )
3586
3587        return inst
3588
3589    @property
3590    def named_selects(self) -> t.List[str]:
3591        return [e.output_name for e in self.expressions if e.alias_or_name]
3592
3593    @property
3594    def is_star(self) -> bool:
3595        return any(expression.is_star for expression in self.expressions)
3596
3597    @property
3598    def selects(self) -> t.List[Expression]:
3599        return self.expressions
3600
3601
3602UNWRAPPED_QUERIES = (Select, Union)
3603
3604
3605class Subquery(DerivedTable, Query):
3606    arg_types = {
3607        "this": True,
3608        "alias": False,
3609        "with": False,
3610        **QUERY_MODIFIERS,
3611    }
3612
3613    def unnest(self):
3614        """Returns the first non subquery."""
3615        expression = self
3616        while isinstance(expression, Subquery):
3617            expression = expression.this
3618        return expression
3619
3620    def unwrap(self) -> Subquery:
3621        expression = self
3622        while expression.same_parent and expression.is_wrapper:
3623            expression = t.cast(Subquery, expression.parent)
3624        return expression
3625
3626    def select(
3627        self,
3628        *expressions: t.Optional[ExpOrStr],
3629        append: bool = True,
3630        dialect: DialectType = None,
3631        copy: bool = True,
3632        **opts,
3633    ) -> Subquery:
3634        this = maybe_copy(self, copy)
3635        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3636        return this
3637
3638    @property
3639    def is_wrapper(self) -> bool:
3640        """
3641        Whether this Subquery acts as a simple wrapper around another expression.
3642
3643        SELECT * FROM (((SELECT * FROM t)))
3644                      ^
3645                      This corresponds to a "wrapper" Subquery node
3646        """
3647        return all(v is None for k, v in self.args.items() if k != "this")
3648
3649    @property
3650    def is_star(self) -> bool:
3651        return self.this.is_star
3652
3653    @property
3654    def output_name(self) -> str:
3655        return self.alias
3656
3657
3658class TableSample(Expression):
3659    arg_types = {
3660        "this": False,
3661        "expressions": False,
3662        "method": False,
3663        "bucket_numerator": False,
3664        "bucket_denominator": False,
3665        "bucket_field": False,
3666        "percent": False,
3667        "rows": False,
3668        "size": False,
3669        "seed": False,
3670    }
3671
3672
3673class Tag(Expression):
3674    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3675
3676    arg_types = {
3677        "this": False,
3678        "prefix": False,
3679        "postfix": False,
3680    }
3681
3682
3683# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3684# https://duckdb.org/docs/sql/statements/pivot
3685class Pivot(Expression):
3686    arg_types = {
3687        "this": False,
3688        "alias": False,
3689        "expressions": False,
3690        "field": False,
3691        "unpivot": False,
3692        "using": False,
3693        "group": False,
3694        "columns": False,
3695        "include_nulls": False,
3696    }
3697
3698    @property
3699    def unpivot(self) -> bool:
3700        return bool(self.args.get("unpivot"))
3701
3702
3703class Window(Condition):
3704    arg_types = {
3705        "this": True,
3706        "partition_by": False,
3707        "order": False,
3708        "spec": False,
3709        "alias": False,
3710        "over": False,
3711        "first": False,
3712    }
3713
3714
3715class WindowSpec(Expression):
3716    arg_types = {
3717        "kind": False,
3718        "start": False,
3719        "start_side": False,
3720        "end": False,
3721        "end_side": False,
3722    }
3723
3724
3725class PreWhere(Expression):
3726    pass
3727
3728
3729class Where(Expression):
3730    pass
3731
3732
3733class Star(Expression):
3734    arg_types = {"except": False, "replace": False}
3735
3736    @property
3737    def name(self) -> str:
3738        return "*"
3739
3740    @property
3741    def output_name(self) -> str:
3742        return self.name
3743
3744
3745class Parameter(Condition):
3746    arg_types = {"this": True, "expression": False}
3747
3748
3749class SessionParameter(Condition):
3750    arg_types = {"this": True, "kind": False}
3751
3752
3753class Placeholder(Condition):
3754    arg_types = {"this": False, "kind": False}
3755
3756
3757class Null(Condition):
3758    arg_types: t.Dict[str, t.Any] = {}
3759
3760    @property
3761    def name(self) -> str:
3762        return "NULL"
3763
3764
3765class Boolean(Condition):
3766    pass
3767
3768
3769class DataTypeParam(Expression):
3770    arg_types = {"this": True, "expression": False}
3771
3772    @property
3773    def name(self) -> str:
3774        return self.this.name
3775
3776
3777class DataType(Expression):
3778    arg_types = {
3779        "this": True,
3780        "expressions": False,
3781        "nested": False,
3782        "values": False,
3783        "prefix": False,
3784        "kind": False,
3785    }
3786
3787    class Type(AutoName):
3788        ARRAY = auto()
3789        AGGREGATEFUNCTION = auto()
3790        SIMPLEAGGREGATEFUNCTION = auto()
3791        BIGDECIMAL = auto()
3792        BIGINT = auto()
3793        BIGSERIAL = auto()
3794        BINARY = auto()
3795        BIT = auto()
3796        BOOLEAN = auto()
3797        BPCHAR = auto()
3798        CHAR = auto()
3799        DATE = auto()
3800        DATE32 = auto()
3801        DATEMULTIRANGE = auto()
3802        DATERANGE = auto()
3803        DATETIME = auto()
3804        DATETIME64 = auto()
3805        DECIMAL = auto()
3806        DOUBLE = auto()
3807        ENUM = auto()
3808        ENUM8 = auto()
3809        ENUM16 = auto()
3810        FIXEDSTRING = auto()
3811        FLOAT = auto()
3812        GEOGRAPHY = auto()
3813        GEOMETRY = auto()
3814        HLLSKETCH = auto()
3815        HSTORE = auto()
3816        IMAGE = auto()
3817        INET = auto()
3818        INT = auto()
3819        INT128 = auto()
3820        INT256 = auto()
3821        INT4MULTIRANGE = auto()
3822        INT4RANGE = auto()
3823        INT8MULTIRANGE = auto()
3824        INT8RANGE = auto()
3825        INTERVAL = auto()
3826        IPADDRESS = auto()
3827        IPPREFIX = auto()
3828        IPV4 = auto()
3829        IPV6 = auto()
3830        JSON = auto()
3831        JSONB = auto()
3832        LONGBLOB = auto()
3833        LONGTEXT = auto()
3834        LOWCARDINALITY = auto()
3835        MAP = auto()
3836        MEDIUMBLOB = auto()
3837        MEDIUMINT = auto()
3838        MEDIUMTEXT = auto()
3839        MONEY = auto()
3840        NAME = auto()
3841        NCHAR = auto()
3842        NESTED = auto()
3843        NULL = auto()
3844        NULLABLE = auto()
3845        NUMMULTIRANGE = auto()
3846        NUMRANGE = auto()
3847        NVARCHAR = auto()
3848        OBJECT = auto()
3849        ROWVERSION = auto()
3850        SERIAL = auto()
3851        SET = auto()
3852        SMALLINT = auto()
3853        SMALLMONEY = auto()
3854        SMALLSERIAL = auto()
3855        STRUCT = auto()
3856        SUPER = auto()
3857        TEXT = auto()
3858        TINYBLOB = auto()
3859        TINYTEXT = auto()
3860        TIME = auto()
3861        TIMETZ = auto()
3862        TIMESTAMP = auto()
3863        TIMESTAMPLTZ = auto()
3864        TIMESTAMPTZ = auto()
3865        TIMESTAMP_S = auto()
3866        TIMESTAMP_MS = auto()
3867        TIMESTAMP_NS = auto()
3868        TINYINT = auto()
3869        TSMULTIRANGE = auto()
3870        TSRANGE = auto()
3871        TSTZMULTIRANGE = auto()
3872        TSTZRANGE = auto()
3873        UBIGINT = auto()
3874        UINT = auto()
3875        UINT128 = auto()
3876        UINT256 = auto()
3877        UMEDIUMINT = auto()
3878        UDECIMAL = auto()
3879        UNIQUEIDENTIFIER = auto()
3880        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3881        USERDEFINED = "USER-DEFINED"
3882        USMALLINT = auto()
3883        UTINYINT = auto()
3884        UUID = auto()
3885        VARBINARY = auto()
3886        VARCHAR = auto()
3887        VARIANT = auto()
3888        XML = auto()
3889        YEAR = auto()
3890
3891    STRUCT_TYPES = {
3892        Type.NESTED,
3893        Type.OBJECT,
3894        Type.STRUCT,
3895    }
3896
3897    NESTED_TYPES = {
3898        *STRUCT_TYPES,
3899        Type.ARRAY,
3900        Type.MAP,
3901    }
3902
3903    TEXT_TYPES = {
3904        Type.CHAR,
3905        Type.NCHAR,
3906        Type.NVARCHAR,
3907        Type.TEXT,
3908        Type.VARCHAR,
3909        Type.NAME,
3910    }
3911
3912    INTEGER_TYPES = {
3913        Type.BIGINT,
3914        Type.BIT,
3915        Type.INT,
3916        Type.INT128,
3917        Type.INT256,
3918        Type.MEDIUMINT,
3919        Type.SMALLINT,
3920        Type.TINYINT,
3921        Type.UBIGINT,
3922        Type.UINT,
3923        Type.UINT128,
3924        Type.UINT256,
3925        Type.UMEDIUMINT,
3926        Type.USMALLINT,
3927        Type.UTINYINT,
3928    }
3929
3930    FLOAT_TYPES = {
3931        Type.DOUBLE,
3932        Type.FLOAT,
3933    }
3934
3935    REAL_TYPES = {
3936        *FLOAT_TYPES,
3937        Type.BIGDECIMAL,
3938        Type.DECIMAL,
3939        Type.MONEY,
3940        Type.SMALLMONEY,
3941        Type.UDECIMAL,
3942    }
3943
3944    NUMERIC_TYPES = {
3945        *INTEGER_TYPES,
3946        *REAL_TYPES,
3947    }
3948
3949    TEMPORAL_TYPES = {
3950        Type.DATE,
3951        Type.DATE32,
3952        Type.DATETIME,
3953        Type.DATETIME64,
3954        Type.TIME,
3955        Type.TIMESTAMP,
3956        Type.TIMESTAMPLTZ,
3957        Type.TIMESTAMPTZ,
3958        Type.TIMESTAMP_MS,
3959        Type.TIMESTAMP_NS,
3960        Type.TIMESTAMP_S,
3961        Type.TIMETZ,
3962    }
3963
3964    @classmethod
3965    def build(
3966        cls,
3967        dtype: DATA_TYPE,
3968        dialect: DialectType = None,
3969        udt: bool = False,
3970        copy: bool = True,
3971        **kwargs,
3972    ) -> DataType:
3973        """
3974        Constructs a DataType object.
3975
3976        Args:
3977            dtype: the data type of interest.
3978            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3979            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3980                DataType, thus creating a user-defined type.
3981            copy: whether to copy the data type.
3982            kwargs: additional arguments to pass in the constructor of DataType.
3983
3984        Returns:
3985            The constructed DataType object.
3986        """
3987        from sqlglot import parse_one
3988
3989        if isinstance(dtype, str):
3990            if dtype.upper() == "UNKNOWN":
3991                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3992
3993            try:
3994                data_type_exp = parse_one(
3995                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3996                )
3997            except ParseError:
3998                if udt:
3999                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4000                raise
4001        elif isinstance(dtype, DataType.Type):
4002            data_type_exp = DataType(this=dtype)
4003        elif isinstance(dtype, DataType):
4004            return maybe_copy(dtype, copy)
4005        else:
4006            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4007
4008        return DataType(**{**data_type_exp.args, **kwargs})
4009
4010    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4011        """
4012        Checks whether this DataType matches one of the provided data types. Nested types or precision
4013        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4014
4015        Args:
4016            dtypes: the data types to compare this DataType to.
4017
4018        Returns:
4019            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4020        """
4021        for dtype in dtypes:
4022            other = DataType.build(dtype, copy=False, udt=True)
4023
4024            if (
4025                other.expressions
4026                or self.this == DataType.Type.USERDEFINED
4027                or other.this == DataType.Type.USERDEFINED
4028            ):
4029                matches = self == other
4030            else:
4031                matches = self.this == other.this
4032
4033            if matches:
4034                return True
4035        return False
4036
4037
4038DATA_TYPE = t.Union[str, DataType, DataType.Type]
4039
4040
4041# https://www.postgresql.org/docs/15/datatype-pseudo.html
4042class PseudoType(DataType):
4043    arg_types = {"this": True}
4044
4045
4046# https://www.postgresql.org/docs/15/datatype-oid.html
4047class ObjectIdentifier(DataType):
4048    arg_types = {"this": True}
4049
4050
4051# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4052class SubqueryPredicate(Predicate):
4053    pass
4054
4055
4056class All(SubqueryPredicate):
4057    pass
4058
4059
4060class Any(SubqueryPredicate):
4061    pass
4062
4063
4064class Exists(SubqueryPredicate):
4065    pass
4066
4067
4068# Commands to interact with the databases or engines. For most of the command
4069# expressions we parse whatever comes after the command's name as a string.
4070class Command(Expression):
4071    arg_types = {"this": True, "expression": False}
4072
4073
4074class Transaction(Expression):
4075    arg_types = {"this": False, "modes": False, "mark": False}
4076
4077
4078class Commit(Expression):
4079    arg_types = {"chain": False, "this": False, "durability": False}
4080
4081
4082class Rollback(Expression):
4083    arg_types = {"savepoint": False, "this": False}
4084
4085
4086class AlterTable(Expression):
4087    arg_types = {
4088        "this": True,
4089        "actions": True,
4090        "exists": False,
4091        "only": False,
4092        "options": False,
4093    }
4094
4095
4096class AddConstraint(Expression):
4097    arg_types = {"expressions": True}
4098
4099
4100class DropPartition(Expression):
4101    arg_types = {"expressions": True, "exists": False}
4102
4103
4104# Binary expressions like (ADD a b)
4105class Binary(Condition):
4106    arg_types = {"this": True, "expression": True}
4107
4108    @property
4109    def left(self) -> Expression:
4110        return self.this
4111
4112    @property
4113    def right(self) -> Expression:
4114        return self.expression
4115
4116
4117class Add(Binary):
4118    pass
4119
4120
4121class Connector(Binary):
4122    pass
4123
4124
4125class And(Connector):
4126    pass
4127
4128
4129class Or(Connector):
4130    pass
4131
4132
4133class BitwiseAnd(Binary):
4134    pass
4135
4136
4137class BitwiseLeftShift(Binary):
4138    pass
4139
4140
4141class BitwiseOr(Binary):
4142    pass
4143
4144
4145class BitwiseRightShift(Binary):
4146    pass
4147
4148
4149class BitwiseXor(Binary):
4150    pass
4151
4152
4153class Div(Binary):
4154    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4155
4156
4157class Overlaps(Binary):
4158    pass
4159
4160
4161class Dot(Binary):
4162    @property
4163    def is_star(self) -> bool:
4164        return self.expression.is_star
4165
4166    @property
4167    def name(self) -> str:
4168        return self.expression.name
4169
4170    @property
4171    def output_name(self) -> str:
4172        return self.name
4173
4174    @classmethod
4175    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4176        """Build a Dot object with a sequence of expressions."""
4177        if len(expressions) < 2:
4178            raise ValueError("Dot requires >= 2 expressions.")
4179
4180        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4181
4182    @property
4183    def parts(self) -> t.List[Expression]:
4184        """Return the parts of a table / column in order catalog, db, table."""
4185        this, *parts = self.flatten()
4186
4187        parts.reverse()
4188
4189        for arg in ("this", "table", "db", "catalog"):
4190            part = this.args.get(arg)
4191
4192            if isinstance(part, Expression):
4193                parts.append(part)
4194
4195        parts.reverse()
4196        return parts
4197
4198
4199class DPipe(Binary):
4200    arg_types = {"this": True, "expression": True, "safe": False}
4201
4202
4203class EQ(Binary, Predicate):
4204    pass
4205
4206
4207class NullSafeEQ(Binary, Predicate):
4208    pass
4209
4210
4211class NullSafeNEQ(Binary, Predicate):
4212    pass
4213
4214
4215# Represents e.g. := in DuckDB which is mostly used for setting parameters
4216class PropertyEQ(Binary):
4217    pass
4218
4219
4220class Distance(Binary):
4221    pass
4222
4223
4224class Escape(Binary):
4225    pass
4226
4227
4228class Glob(Binary, Predicate):
4229    pass
4230
4231
4232class GT(Binary, Predicate):
4233    pass
4234
4235
4236class GTE(Binary, Predicate):
4237    pass
4238
4239
4240class ILike(Binary, Predicate):
4241    pass
4242
4243
4244class ILikeAny(Binary, Predicate):
4245    pass
4246
4247
4248class IntDiv(Binary):
4249    pass
4250
4251
4252class Is(Binary, Predicate):
4253    pass
4254
4255
4256class Kwarg(Binary):
4257    """Kwarg in special functions like func(kwarg => y)."""
4258
4259
4260class Like(Binary, Predicate):
4261    pass
4262
4263
4264class LikeAny(Binary, Predicate):
4265    pass
4266
4267
4268class LT(Binary, Predicate):
4269    pass
4270
4271
4272class LTE(Binary, Predicate):
4273    pass
4274
4275
4276class Mod(Binary):
4277    pass
4278
4279
4280class Mul(Binary):
4281    pass
4282
4283
4284class NEQ(Binary, Predicate):
4285    pass
4286
4287
4288# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4289class Operator(Binary):
4290    arg_types = {"this": True, "operator": True, "expression": True}
4291
4292
4293class SimilarTo(Binary, Predicate):
4294    pass
4295
4296
4297class Slice(Binary):
4298    arg_types = {"this": False, "expression": False}
4299
4300
4301class Sub(Binary):
4302    pass
4303
4304
4305# Unary Expressions
4306# (NOT a)
4307class Unary(Condition):
4308    pass
4309
4310
4311class BitwiseNot(Unary):
4312    pass
4313
4314
4315class Not(Unary):
4316    pass
4317
4318
4319class Paren(Unary):
4320    @property
4321    def output_name(self) -> str:
4322        return self.this.name
4323
4324
4325class Neg(Unary):
4326    pass
4327
4328
4329class Alias(Expression):
4330    arg_types = {"this": True, "alias": False}
4331
4332    @property
4333    def output_name(self) -> str:
4334        return self.alias
4335
4336
4337# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4338# other dialects require identifiers. This enables us to transpile between them easily.
4339class PivotAlias(Alias):
4340    pass
4341
4342
4343class Aliases(Expression):
4344    arg_types = {"this": True, "expressions": True}
4345
4346    @property
4347    def aliases(self):
4348        return self.expressions
4349
4350
4351# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4352class AtIndex(Expression):
4353    arg_types = {"this": True, "expression": True}
4354
4355
4356class AtTimeZone(Expression):
4357    arg_types = {"this": True, "zone": True}
4358
4359
4360class FromTimeZone(Expression):
4361    arg_types = {"this": True, "zone": True}
4362
4363
4364class Between(Predicate):
4365    arg_types = {"this": True, "low": True, "high": True}
4366
4367
4368class Bracket(Condition):
4369    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4370    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4371
4372    @property
4373    def output_name(self) -> str:
4374        if len(self.expressions) == 1:
4375            return self.expressions[0].output_name
4376
4377        return super().output_name
4378
4379
4380class Distinct(Expression):
4381    arg_types = {"expressions": False, "on": False}
4382
4383
4384class In(Predicate):
4385    arg_types = {
4386        "this": True,
4387        "expressions": False,
4388        "query": False,
4389        "unnest": False,
4390        "field": False,
4391        "is_global": False,
4392    }
4393
4394
4395# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4396class ForIn(Expression):
4397    arg_types = {"this": True, "expression": True}
4398
4399
4400class TimeUnit(Expression):
4401    """Automatically converts unit arg into a var."""
4402
4403    arg_types = {"unit": False}
4404
4405    UNABBREVIATED_UNIT_NAME = {
4406        "D": "DAY",
4407        "H": "HOUR",
4408        "M": "MINUTE",
4409        "MS": "MILLISECOND",
4410        "NS": "NANOSECOND",
4411        "Q": "QUARTER",
4412        "S": "SECOND",
4413        "US": "MICROSECOND",
4414        "W": "WEEK",
4415        "Y": "YEAR",
4416    }
4417
4418    VAR_LIKE = (Column, Literal, Var)
4419
4420    def __init__(self, **args):
4421        unit = args.get("unit")
4422        if isinstance(unit, self.VAR_LIKE):
4423            args["unit"] = Var(
4424                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4425            )
4426        elif isinstance(unit, Week):
4427            unit.set("this", Var(this=unit.this.name.upper()))
4428
4429        super().__init__(**args)
4430
4431    @property
4432    def unit(self) -> t.Optional[Var]:
4433        return self.args.get("unit")
4434
4435
4436class IntervalOp(TimeUnit):
4437    arg_types = {"unit": True, "expression": True}
4438
4439    def interval(self):
4440        return Interval(
4441            this=self.expression.copy(),
4442            unit=self.unit.copy(),
4443        )
4444
4445
4446# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4447# https://trino.io/docs/current/language/types.html#interval-day-to-second
4448# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4449class IntervalSpan(DataType):
4450    arg_types = {"this": True, "expression": True}
4451
4452
4453class Interval(TimeUnit):
4454    arg_types = {"this": False, "unit": False}
4455
4456
4457class IgnoreNulls(Expression):
4458    pass
4459
4460
4461class RespectNulls(Expression):
4462    pass
4463
4464
4465# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4466class HavingMax(Expression):
4467    arg_types = {"this": True, "expression": True, "max": True}
4468
4469
4470# Functions
4471class Func(Condition):
4472    """
4473    The base class for all function expressions.
4474
4475    Attributes:
4476        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4477            treated as a variable length argument and the argument's value will be stored as a list.
4478        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4479            function expression. These values are used to map this node to a name during parsing as
4480            well as to provide the function's name during SQL string generation. By default the SQL
4481            name is set to the expression's class name transformed to snake case.
4482    """
4483
4484    is_var_len_args = False
4485
4486    @classmethod
4487    def from_arg_list(cls, args):
4488        if cls.is_var_len_args:
4489            all_arg_keys = list(cls.arg_types)
4490            # If this function supports variable length argument treat the last argument as such.
4491            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4492            num_non_var = len(non_var_len_arg_keys)
4493
4494            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4495            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4496        else:
4497            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4498
4499        return cls(**args_dict)
4500
4501    @classmethod
4502    def sql_names(cls):
4503        if cls is Func:
4504            raise NotImplementedError(
4505                "SQL name is only supported by concrete function implementations"
4506            )
4507        if "_sql_names" not in cls.__dict__:
4508            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4509        return cls._sql_names
4510
4511    @classmethod
4512    def sql_name(cls):
4513        return cls.sql_names()[0]
4514
4515    @classmethod
4516    def default_parser_mappings(cls):
4517        return {name: cls.from_arg_list for name in cls.sql_names()}
4518
4519
4520class AggFunc(Func):
4521    pass
4522
4523
4524class ParameterizedAgg(AggFunc):
4525    arg_types = {"this": True, "expressions": True, "params": True}
4526
4527
4528class Abs(Func):
4529    pass
4530
4531
4532class ArgMax(AggFunc):
4533    arg_types = {"this": True, "expression": True, "count": False}
4534    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4535
4536
4537class ArgMin(AggFunc):
4538    arg_types = {"this": True, "expression": True, "count": False}
4539    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4540
4541
4542class ApproxTopK(AggFunc):
4543    arg_types = {"this": True, "expression": False, "counters": False}
4544
4545
4546class Flatten(Func):
4547    pass
4548
4549
4550# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4551class Transform(Func):
4552    arg_types = {"this": True, "expression": True}
4553
4554
4555class Anonymous(Func):
4556    arg_types = {"this": True, "expressions": False}
4557    is_var_len_args = True
4558
4559    @property
4560    def name(self) -> str:
4561        return self.this if isinstance(self.this, str) else self.this.name
4562
4563
4564class AnonymousAggFunc(AggFunc):
4565    arg_types = {"this": True, "expressions": False}
4566    is_var_len_args = True
4567
4568
4569# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4570class CombinedAggFunc(AnonymousAggFunc):
4571    arg_types = {"this": True, "expressions": False, "parts": True}
4572
4573
4574class CombinedParameterizedAgg(ParameterizedAgg):
4575    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4576
4577
4578# https://docs.snowflake.com/en/sql-reference/functions/hll
4579# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4580class Hll(AggFunc):
4581    arg_types = {"this": True, "expressions": False}
4582    is_var_len_args = True
4583
4584
4585class ApproxDistinct(AggFunc):
4586    arg_types = {"this": True, "accuracy": False}
4587    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4588
4589
4590class Array(Func):
4591    arg_types = {"expressions": False}
4592    is_var_len_args = True
4593
4594
4595# https://docs.snowflake.com/en/sql-reference/functions/to_array
4596class ToArray(Func):
4597    pass
4598
4599
4600# https://docs.snowflake.com/en/sql-reference/functions/to_char
4601# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4602class ToChar(Func):
4603    arg_types = {"this": True, "format": False, "nlsparam": False}
4604
4605
4606# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4607# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4608class ToNumber(Func):
4609    arg_types = {
4610        "this": True,
4611        "format": False,
4612        "nlsparam": False,
4613        "precision": False,
4614        "scale": False,
4615    }
4616
4617
4618# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4619class Convert(Func):
4620    arg_types = {"this": True, "expression": True, "style": False}
4621
4622
4623class GenerateSeries(Func):
4624    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4625
4626
4627class ArrayAgg(AggFunc):
4628    pass
4629
4630
4631class ArrayUniqueAgg(AggFunc):
4632    pass
4633
4634
4635class ArrayAll(Func):
4636    arg_types = {"this": True, "expression": True}
4637
4638
4639# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4640class ArrayAny(Func):
4641    arg_types = {"this": True, "expression": True}
4642
4643
4644class ArrayConcat(Func):
4645    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4646    arg_types = {"this": True, "expressions": False}
4647    is_var_len_args = True
4648
4649
4650class ArrayContains(Binary, Func):
4651    pass
4652
4653
4654class ArrayContained(Binary):
4655    pass
4656
4657
4658class ArrayFilter(Func):
4659    arg_types = {"this": True, "expression": True}
4660    _sql_names = ["FILTER", "ARRAY_FILTER"]
4661
4662
4663class ArrayToString(Func):
4664    arg_types = {"this": True, "expression": True, "null": False}
4665    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4666
4667
4668class ArrayOverlaps(Binary, Func):
4669    pass
4670
4671
4672class ArraySize(Func):
4673    arg_types = {"this": True, "expression": False}
4674    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4675
4676
4677class ArraySort(Func):
4678    arg_types = {"this": True, "expression": False}
4679
4680
4681class ArraySum(Func):
4682    arg_types = {"this": True, "expression": False}
4683
4684
4685class ArrayUnionAgg(AggFunc):
4686    pass
4687
4688
4689class Avg(AggFunc):
4690    pass
4691
4692
4693class AnyValue(AggFunc):
4694    pass
4695
4696
4697class Lag(AggFunc):
4698    arg_types = {"this": True, "offset": False, "default": False}
4699
4700
4701class Lead(AggFunc):
4702    arg_types = {"this": True, "offset": False, "default": False}
4703
4704
4705# some dialects have a distinction between first and first_value, usually first is an aggregate func
4706# and first_value is a window func
4707class First(AggFunc):
4708    pass
4709
4710
4711class Last(AggFunc):
4712    pass
4713
4714
4715class FirstValue(AggFunc):
4716    pass
4717
4718
4719class LastValue(AggFunc):
4720    pass
4721
4722
4723class NthValue(AggFunc):
4724    arg_types = {"this": True, "offset": True}
4725
4726
4727class Case(Func):
4728    arg_types = {"this": False, "ifs": True, "default": False}
4729
4730    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4731        instance = maybe_copy(self, copy)
4732        instance.append(
4733            "ifs",
4734            If(
4735                this=maybe_parse(condition, copy=copy, **opts),
4736                true=maybe_parse(then, copy=copy, **opts),
4737            ),
4738        )
4739        return instance
4740
4741    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4742        instance = maybe_copy(self, copy)
4743        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4744        return instance
4745
4746
4747class Cast(Func):
4748    arg_types = {
4749        "this": True,
4750        "to": True,
4751        "format": False,
4752        "safe": False,
4753        "action": False,
4754    }
4755
4756    @property
4757    def name(self) -> str:
4758        return self.this.name
4759
4760    @property
4761    def to(self) -> DataType:
4762        return self.args["to"]
4763
4764    @property
4765    def output_name(self) -> str:
4766        return self.name
4767
4768    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4769        """
4770        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4771        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4772        array<int> != array<float>.
4773
4774        Args:
4775            dtypes: the data types to compare this Cast's DataType to.
4776
4777        Returns:
4778            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4779        """
4780        return self.to.is_type(*dtypes)
4781
4782
4783class TryCast(Cast):
4784    pass
4785
4786
4787class CastToStrType(Func):
4788    arg_types = {"this": True, "to": True}
4789
4790
4791class Collate(Binary, Func):
4792    pass
4793
4794
4795class Ceil(Func):
4796    arg_types = {"this": True, "decimals": False}
4797    _sql_names = ["CEIL", "CEILING"]
4798
4799
4800class Coalesce(Func):
4801    arg_types = {"this": True, "expressions": False}
4802    is_var_len_args = True
4803    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4804
4805
4806class Chr(Func):
4807    arg_types = {"this": True, "charset": False, "expressions": False}
4808    is_var_len_args = True
4809    _sql_names = ["CHR", "CHAR"]
4810
4811
4812class Concat(Func):
4813    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4814    is_var_len_args = True
4815
4816
4817class ConcatWs(Concat):
4818    _sql_names = ["CONCAT_WS"]
4819
4820
4821# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4822class ConnectByRoot(Func):
4823    pass
4824
4825
4826class Count(AggFunc):
4827    arg_types = {"this": False, "expressions": False}
4828    is_var_len_args = True
4829
4830
4831class CountIf(AggFunc):
4832    _sql_names = ["COUNT_IF", "COUNTIF"]
4833
4834
4835# cube root
4836class Cbrt(Func):
4837    pass
4838
4839
4840class CurrentDate(Func):
4841    arg_types = {"this": False}
4842
4843
4844class CurrentDatetime(Func):
4845    arg_types = {"this": False}
4846
4847
4848class CurrentTime(Func):
4849    arg_types = {"this": False}
4850
4851
4852class CurrentTimestamp(Func):
4853    arg_types = {"this": False, "transaction": False}
4854
4855
4856class CurrentUser(Func):
4857    arg_types = {"this": False}
4858
4859
4860class DateAdd(Func, IntervalOp):
4861    arg_types = {"this": True, "expression": True, "unit": False}
4862
4863
4864class DateSub(Func, IntervalOp):
4865    arg_types = {"this": True, "expression": True, "unit": False}
4866
4867
4868class DateDiff(Func, TimeUnit):
4869    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4870    arg_types = {"this": True, "expression": True, "unit": False}
4871
4872
4873class DateTrunc(Func):
4874    arg_types = {"unit": True, "this": True, "zone": False}
4875
4876    def __init__(self, **args):
4877        unit = args.get("unit")
4878        if isinstance(unit, TimeUnit.VAR_LIKE):
4879            args["unit"] = Literal.string(
4880                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4881            )
4882        elif isinstance(unit, Week):
4883            unit.set("this", Literal.string(unit.this.name.upper()))
4884
4885        super().__init__(**args)
4886
4887    @property
4888    def unit(self) -> Expression:
4889        return self.args["unit"]
4890
4891
4892class DatetimeAdd(Func, IntervalOp):
4893    arg_types = {"this": True, "expression": True, "unit": False}
4894
4895
4896class DatetimeSub(Func, IntervalOp):
4897    arg_types = {"this": True, "expression": True, "unit": False}
4898
4899
4900class DatetimeDiff(Func, TimeUnit):
4901    arg_types = {"this": True, "expression": True, "unit": False}
4902
4903
4904class DatetimeTrunc(Func, TimeUnit):
4905    arg_types = {"this": True, "unit": True, "zone": False}
4906
4907
4908class DayOfWeek(Func):
4909    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4910
4911
4912class DayOfMonth(Func):
4913    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4914
4915
4916class DayOfYear(Func):
4917    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4918
4919
4920class ToDays(Func):
4921    pass
4922
4923
4924class WeekOfYear(Func):
4925    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4926
4927
4928class MonthsBetween(Func):
4929    arg_types = {"this": True, "expression": True, "roundoff": False}
4930
4931
4932class LastDay(Func, TimeUnit):
4933    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4934    arg_types = {"this": True, "unit": False}
4935
4936
4937class Extract(Func):
4938    arg_types = {"this": True, "expression": True}
4939
4940
4941class Timestamp(Func):
4942    arg_types = {"this": False, "expression": False, "with_tz": False}
4943
4944
4945class TimestampAdd(Func, TimeUnit):
4946    arg_types = {"this": True, "expression": True, "unit": False}
4947
4948
4949class TimestampSub(Func, TimeUnit):
4950    arg_types = {"this": True, "expression": True, "unit": False}
4951
4952
4953class TimestampDiff(Func, TimeUnit):
4954    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4955    arg_types = {"this": True, "expression": True, "unit": False}
4956
4957
4958class TimestampTrunc(Func, TimeUnit):
4959    arg_types = {"this": True, "unit": True, "zone": False}
4960
4961
4962class TimeAdd(Func, TimeUnit):
4963    arg_types = {"this": True, "expression": True, "unit": False}
4964
4965
4966class TimeSub(Func, TimeUnit):
4967    arg_types = {"this": True, "expression": True, "unit": False}
4968
4969
4970class TimeDiff(Func, TimeUnit):
4971    arg_types = {"this": True, "expression": True, "unit": False}
4972
4973
4974class TimeTrunc(Func, TimeUnit):
4975    arg_types = {"this": True, "unit": True, "zone": False}
4976
4977
4978class DateFromParts(Func):
4979    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4980    arg_types = {"year": True, "month": True, "day": True}
4981
4982
4983class TimeFromParts(Func):
4984    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4985    arg_types = {
4986        "hour": True,
4987        "min": True,
4988        "sec": True,
4989        "nano": False,
4990        "fractions": False,
4991        "precision": False,
4992    }
4993
4994
4995class DateStrToDate(Func):
4996    pass
4997
4998
4999class DateToDateStr(Func):
5000    pass
5001
5002
5003class DateToDi(Func):
5004    pass
5005
5006
5007# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5008class Date(Func):
5009    arg_types = {"this": False, "zone": False, "expressions": False}
5010    is_var_len_args = True
5011
5012
5013class Day(Func):
5014    pass
5015
5016
5017class Decode(Func):
5018    arg_types = {"this": True, "charset": True, "replace": False}
5019
5020
5021class DiToDate(Func):
5022    pass
5023
5024
5025class Encode(Func):
5026    arg_types = {"this": True, "charset": True}
5027
5028
5029class Exp(Func):
5030    pass
5031
5032
5033# https://docs.snowflake.com/en/sql-reference/functions/flatten
5034class Explode(Func):
5035    arg_types = {"this": True, "expressions": False}
5036    is_var_len_args = True
5037
5038
5039class ExplodeOuter(Explode):
5040    pass
5041
5042
5043class Posexplode(Explode):
5044    pass
5045
5046
5047class PosexplodeOuter(Posexplode, ExplodeOuter):
5048    pass
5049
5050
5051class Floor(Func):
5052    arg_types = {"this": True, "decimals": False}
5053
5054
5055class FromBase64(Func):
5056    pass
5057
5058
5059class ToBase64(Func):
5060    pass
5061
5062
5063class Greatest(Func):
5064    arg_types = {"this": True, "expressions": False}
5065    is_var_len_args = True
5066
5067
5068class GroupConcat(AggFunc):
5069    arg_types = {"this": True, "separator": False}
5070
5071
5072class Hex(Func):
5073    pass
5074
5075
5076class Xor(Connector, Func):
5077    arg_types = {"this": False, "expression": False, "expressions": False}
5078
5079
5080class If(Func):
5081    arg_types = {"this": True, "true": True, "false": False}
5082    _sql_names = ["IF", "IIF"]
5083
5084
5085class Nullif(Func):
5086    arg_types = {"this": True, "expression": True}
5087
5088
5089class Initcap(Func):
5090    arg_types = {"this": True, "expression": False}
5091
5092
5093class IsNan(Func):
5094    _sql_names = ["IS_NAN", "ISNAN"]
5095
5096
5097class IsInf(Func):
5098    _sql_names = ["IS_INF", "ISINF"]
5099
5100
5101class JSONPath(Expression):
5102    arg_types = {"expressions": True}
5103
5104    @property
5105    def output_name(self) -> str:
5106        last_segment = self.expressions[-1].this
5107        return last_segment if isinstance(last_segment, str) else ""
5108
5109
5110class JSONPathPart(Expression):
5111    arg_types = {}
5112
5113
5114class JSONPathFilter(JSONPathPart):
5115    arg_types = {"this": True}
5116
5117
5118class JSONPathKey(JSONPathPart):
5119    arg_types = {"this": True}
5120
5121
5122class JSONPathRecursive(JSONPathPart):
5123    arg_types = {"this": False}
5124
5125
5126class JSONPathRoot(JSONPathPart):
5127    pass
5128
5129
5130class JSONPathScript(JSONPathPart):
5131    arg_types = {"this": True}
5132
5133
5134class JSONPathSlice(JSONPathPart):
5135    arg_types = {"start": False, "end": False, "step": False}
5136
5137
5138class JSONPathSelector(JSONPathPart):
5139    arg_types = {"this": True}
5140
5141
5142class JSONPathSubscript(JSONPathPart):
5143    arg_types = {"this": True}
5144
5145
5146class JSONPathUnion(JSONPathPart):
5147    arg_types = {"expressions": True}
5148
5149
5150class JSONPathWildcard(JSONPathPart):
5151    pass
5152
5153
5154class FormatJson(Expression):
5155    pass
5156
5157
5158class JSONKeyValue(Expression):
5159    arg_types = {"this": True, "expression": True}
5160
5161
5162class JSONObject(Func):
5163    arg_types = {
5164        "expressions": False,
5165        "null_handling": False,
5166        "unique_keys": False,
5167        "return_type": False,
5168        "encoding": False,
5169    }
5170
5171
5172class JSONObjectAgg(AggFunc):
5173    arg_types = {
5174        "expressions": False,
5175        "null_handling": False,
5176        "unique_keys": False,
5177        "return_type": False,
5178        "encoding": False,
5179    }
5180
5181
5182# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5183class JSONArray(Func):
5184    arg_types = {
5185        "expressions": True,
5186        "null_handling": False,
5187        "return_type": False,
5188        "strict": False,
5189    }
5190
5191
5192# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5193class JSONArrayAgg(Func):
5194    arg_types = {
5195        "this": True,
5196        "order": False,
5197        "null_handling": False,
5198        "return_type": False,
5199        "strict": False,
5200    }
5201
5202
5203# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5204# Note: parsing of JSON column definitions is currently incomplete.
5205class JSONColumnDef(Expression):
5206    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5207
5208
5209class JSONSchema(Expression):
5210    arg_types = {"expressions": True}
5211
5212
5213# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5214class JSONTable(Func):
5215    arg_types = {
5216        "this": True,
5217        "schema": True,
5218        "path": False,
5219        "error_handling": False,
5220        "empty_handling": False,
5221    }
5222
5223
5224class OpenJSONColumnDef(Expression):
5225    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5226
5227
5228class OpenJSON(Func):
5229    arg_types = {"this": True, "path": False, "expressions": False}
5230
5231
5232class JSONBContains(Binary):
5233    _sql_names = ["JSONB_CONTAINS"]
5234
5235
5236class JSONExtract(Binary, Func):
5237    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5238    _sql_names = ["JSON_EXTRACT"]
5239    is_var_len_args = True
5240
5241    @property
5242    def output_name(self) -> str:
5243        return self.expression.output_name if not self.expressions else ""
5244
5245
5246class JSONExtractScalar(Binary, Func):
5247    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5248    _sql_names = ["JSON_EXTRACT_SCALAR"]
5249    is_var_len_args = True
5250
5251    @property
5252    def output_name(self) -> str:
5253        return self.expression.output_name
5254
5255
5256class JSONBExtract(Binary, Func):
5257    _sql_names = ["JSONB_EXTRACT"]
5258
5259
5260class JSONBExtractScalar(Binary, Func):
5261    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5262
5263
5264class JSONFormat(Func):
5265    arg_types = {"this": False, "options": False}
5266    _sql_names = ["JSON_FORMAT"]
5267
5268
5269# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5270class JSONArrayContains(Binary, Predicate, Func):
5271    _sql_names = ["JSON_ARRAY_CONTAINS"]
5272
5273
5274class ParseJSON(Func):
5275    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5276    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5277    arg_types = {"this": True, "expressions": False}
5278    is_var_len_args = True
5279
5280
5281class Least(Func):
5282    arg_types = {"this": True, "expressions": False}
5283    is_var_len_args = True
5284
5285
5286class Left(Func):
5287    arg_types = {"this": True, "expression": True}
5288
5289
5290class Right(Func):
5291    arg_types = {"this": True, "expression": True}
5292
5293
5294class Length(Func):
5295    _sql_names = ["LENGTH", "LEN"]
5296
5297
5298class Levenshtein(Func):
5299    arg_types = {
5300        "this": True,
5301        "expression": False,
5302        "ins_cost": False,
5303        "del_cost": False,
5304        "sub_cost": False,
5305    }
5306
5307
5308class Ln(Func):
5309    pass
5310
5311
5312class Log(Func):
5313    arg_types = {"this": True, "expression": False}
5314
5315
5316class LogicalOr(AggFunc):
5317    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5318
5319
5320class LogicalAnd(AggFunc):
5321    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5322
5323
5324class Lower(Func):
5325    _sql_names = ["LOWER", "LCASE"]
5326
5327
5328class Map(Func):
5329    arg_types = {"keys": False, "values": False}
5330
5331    @property
5332    def keys(self) -> t.List[Expression]:
5333        keys = self.args.get("keys")
5334        return keys.expressions if keys else []
5335
5336    @property
5337    def values(self) -> t.List[Expression]:
5338        values = self.args.get("values")
5339        return values.expressions if values else []
5340
5341
5342class MapFromEntries(Func):
5343    pass
5344
5345
5346class StarMap(Func):
5347    pass
5348
5349
5350class VarMap(Func):
5351    arg_types = {"keys": True, "values": True}
5352    is_var_len_args = True
5353
5354    @property
5355    def keys(self) -> t.List[Expression]:
5356        return self.args["keys"].expressions
5357
5358    @property
5359    def values(self) -> t.List[Expression]:
5360        return self.args["values"].expressions
5361
5362
5363# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5364class MatchAgainst(Func):
5365    arg_types = {"this": True, "expressions": True, "modifier": False}
5366
5367
5368class Max(AggFunc):
5369    arg_types = {"this": True, "expressions": False}
5370    is_var_len_args = True
5371
5372
5373class MD5(Func):
5374    _sql_names = ["MD5"]
5375
5376
5377# Represents the variant of the MD5 function that returns a binary value
5378class MD5Digest(Func):
5379    _sql_names = ["MD5_DIGEST"]
5380
5381
5382class Min(AggFunc):
5383    arg_types = {"this": True, "expressions": False}
5384    is_var_len_args = True
5385
5386
5387class Month(Func):
5388    pass
5389
5390
5391class AddMonths(Func):
5392    arg_types = {"this": True, "expression": True}
5393
5394
5395class Nvl2(Func):
5396    arg_types = {"this": True, "true": True, "false": False}
5397
5398
5399# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5400class Predict(Func):
5401    arg_types = {"this": True, "expression": True, "params_struct": False}
5402
5403
5404class Pow(Binary, Func):
5405    _sql_names = ["POWER", "POW"]
5406
5407
5408class PercentileCont(AggFunc):
5409    arg_types = {"this": True, "expression": False}
5410
5411
5412class PercentileDisc(AggFunc):
5413    arg_types = {"this": True, "expression": False}
5414
5415
5416class Quantile(AggFunc):
5417    arg_types = {"this": True, "quantile": True}
5418
5419
5420class ApproxQuantile(Quantile):
5421    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5422
5423
5424class Rand(Func):
5425    _sql_names = ["RAND", "RANDOM"]
5426    arg_types = {"this": False}
5427
5428
5429class Randn(Func):
5430    arg_types = {"this": False}
5431
5432
5433class RangeN(Func):
5434    arg_types = {"this": True, "expressions": True, "each": False}
5435
5436
5437class ReadCSV(Func):
5438    _sql_names = ["READ_CSV"]
5439    is_var_len_args = True
5440    arg_types = {"this": True, "expressions": False}
5441
5442
5443class Reduce(Func):
5444    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5445
5446
5447class RegexpExtract(Func):
5448    arg_types = {
5449        "this": True,
5450        "expression": True,
5451        "position": False,
5452        "occurrence": False,
5453        "parameters": False,
5454        "group": False,
5455    }
5456
5457
5458class RegexpReplace(Func):
5459    arg_types = {
5460        "this": True,
5461        "expression": True,
5462        "replacement": False,
5463        "position": False,
5464        "occurrence": False,
5465        "parameters": False,
5466        "modifiers": False,
5467    }
5468
5469
5470class RegexpLike(Binary, Func):
5471    arg_types = {"this": True, "expression": True, "flag": False}
5472
5473
5474class RegexpILike(Binary, Func):
5475    arg_types = {"this": True, "expression": True, "flag": False}
5476
5477
5478# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5479# limit is the number of times a pattern is applied
5480class RegexpSplit(Func):
5481    arg_types = {"this": True, "expression": True, "limit": False}
5482
5483
5484class Repeat(Func):
5485    arg_types = {"this": True, "times": True}
5486
5487
5488# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5489# tsql third argument function == trunctaion if not 0
5490class Round(Func):
5491    arg_types = {"this": True, "decimals": False, "truncate": False}
5492
5493
5494class RowNumber(Func):
5495    arg_types: t.Dict[str, t.Any] = {}
5496
5497
5498class SafeDivide(Func):
5499    arg_types = {"this": True, "expression": True}
5500
5501
5502class SHA(Func):
5503    _sql_names = ["SHA", "SHA1"]
5504
5505
5506class SHA2(Func):
5507    _sql_names = ["SHA2"]
5508    arg_types = {"this": True, "length": False}
5509
5510
5511class Sign(Func):
5512    _sql_names = ["SIGN", "SIGNUM"]
5513
5514
5515class SortArray(Func):
5516    arg_types = {"this": True, "asc": False}
5517
5518
5519class Split(Func):
5520    arg_types = {"this": True, "expression": True, "limit": False}
5521
5522
5523# Start may be omitted in the case of postgres
5524# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5525class Substring(Func):
5526    arg_types = {"this": True, "start": False, "length": False}
5527
5528
5529class StandardHash(Func):
5530    arg_types = {"this": True, "expression": False}
5531
5532
5533class StartsWith(Func):
5534    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5535    arg_types = {"this": True, "expression": True}
5536
5537
5538class StrPosition(Func):
5539    arg_types = {
5540        "this": True,
5541        "substr": True,
5542        "position": False,
5543        "instance": False,
5544    }
5545
5546
5547class StrToDate(Func):
5548    arg_types = {"this": True, "format": True}
5549
5550
5551class StrToTime(Func):
5552    arg_types = {"this": True, "format": True, "zone": False}
5553
5554
5555# Spark allows unix_timestamp()
5556# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5557class StrToUnix(Func):
5558    arg_types = {"this": False, "format": False}
5559
5560
5561# https://prestodb.io/docs/current/functions/string.html
5562# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5563class StrToMap(Func):
5564    arg_types = {
5565        "this": True,
5566        "pair_delim": False,
5567        "key_value_delim": False,
5568        "duplicate_resolution_callback": False,
5569    }
5570
5571
5572class NumberToStr(Func):
5573    arg_types = {"this": True, "format": True, "culture": False}
5574
5575
5576class FromBase(Func):
5577    arg_types = {"this": True, "expression": True}
5578
5579
5580class Struct(Func):
5581    arg_types = {"expressions": False}
5582    is_var_len_args = True
5583
5584
5585class StructExtract(Func):
5586    arg_types = {"this": True, "expression": True}
5587
5588
5589# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5590# https://docs.snowflake.com/en/sql-reference/functions/insert
5591class Stuff(Func):
5592    _sql_names = ["STUFF", "INSERT"]
5593    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5594
5595
5596class Sum(AggFunc):
5597    pass
5598
5599
5600class Sqrt(Func):
5601    pass
5602
5603
5604class Stddev(AggFunc):
5605    pass
5606
5607
5608class StddevPop(AggFunc):
5609    pass
5610
5611
5612class StddevSamp(AggFunc):
5613    pass
5614
5615
5616class TimeToStr(Func):
5617    arg_types = {"this": True, "format": True, "culture": False}
5618
5619
5620class TimeToTimeStr(Func):
5621    pass
5622
5623
5624class TimeToUnix(Func):
5625    pass
5626
5627
5628class TimeStrToDate(Func):
5629    pass
5630
5631
5632class TimeStrToTime(Func):
5633    pass
5634
5635
5636class TimeStrToUnix(Func):
5637    pass
5638
5639
5640class Trim(Func):
5641    arg_types = {
5642        "this": True,
5643        "expression": False,
5644        "position": False,
5645        "collation": False,
5646    }
5647
5648
5649class TsOrDsAdd(Func, TimeUnit):
5650    # return_type is used to correctly cast the arguments of this expression when transpiling it
5651    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5652
5653    @property
5654    def return_type(self) -> DataType:
5655        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5656
5657
5658class TsOrDsDiff(Func, TimeUnit):
5659    arg_types = {"this": True, "expression": True, "unit": False}
5660
5661
5662class TsOrDsToDateStr(Func):
5663    pass
5664
5665
5666class TsOrDsToDate(Func):
5667    arg_types = {"this": True, "format": False, "safe": False}
5668
5669
5670class TsOrDsToTime(Func):
5671    pass
5672
5673
5674class TsOrDiToDi(Func):
5675    pass
5676
5677
5678class Unhex(Func):
5679    pass
5680
5681
5682# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5683class UnixDate(Func):
5684    pass
5685
5686
5687class UnixToStr(Func):
5688    arg_types = {"this": True, "format": False}
5689
5690
5691# https://prestodb.io/docs/current/functions/datetime.html
5692# presto has weird zone/hours/minutes
5693class UnixToTime(Func):
5694    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5695
5696    SECONDS = Literal.number(0)
5697    DECIS = Literal.number(1)
5698    CENTIS = Literal.number(2)
5699    MILLIS = Literal.number(3)
5700    DECIMILLIS = Literal.number(4)
5701    CENTIMILLIS = Literal.number(5)
5702    MICROS = Literal.number(6)
5703    DECIMICROS = Literal.number(7)
5704    CENTIMICROS = Literal.number(8)
5705    NANOS = Literal.number(9)
5706
5707
5708class UnixToTimeStr(Func):
5709    pass
5710
5711
5712class TimestampFromParts(Func):
5713    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5714    arg_types = {
5715        "year": True,
5716        "month": True,
5717        "day": True,
5718        "hour": True,
5719        "min": True,
5720        "sec": True,
5721        "nano": False,
5722        "zone": False,
5723        "milli": False,
5724    }
5725
5726
5727class Upper(Func):
5728    _sql_names = ["UPPER", "UCASE"]
5729
5730
5731class Variance(AggFunc):
5732    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5733
5734
5735class VariancePop(AggFunc):
5736    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5737
5738
5739class Week(Func):
5740    arg_types = {"this": True, "mode": False}
5741
5742
5743class XMLTable(Func):
5744    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5745
5746
5747class Year(Func):
5748    pass
5749
5750
5751class Use(Expression):
5752    arg_types = {"this": True, "kind": False}
5753
5754
5755class Merge(Expression):
5756    arg_types = {
5757        "this": True,
5758        "using": True,
5759        "on": True,
5760        "expressions": True,
5761        "with": False,
5762    }
5763
5764
5765class When(Func):
5766    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5767
5768
5769# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5770# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5771class NextValueFor(Func):
5772    arg_types = {"this": True, "order": False}
5773
5774
5775def _norm_arg(arg):
5776    return arg.lower() if type(arg) is str else arg
5777
5778
5779ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5780FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5781
5782JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5783
5784
5785# Helpers
5786@t.overload
5787def maybe_parse(
5788    sql_or_expression: ExpOrStr,
5789    *,
5790    into: t.Type[E],
5791    dialect: DialectType = None,
5792    prefix: t.Optional[str] = None,
5793    copy: bool = False,
5794    **opts,
5795) -> E: ...
5796
5797
5798@t.overload
5799def maybe_parse(
5800    sql_or_expression: str | E,
5801    *,
5802    into: t.Optional[IntoType] = None,
5803    dialect: DialectType = None,
5804    prefix: t.Optional[str] = None,
5805    copy: bool = False,
5806    **opts,
5807) -> E: ...
5808
5809
5810def maybe_parse(
5811    sql_or_expression: ExpOrStr,
5812    *,
5813    into: t.Optional[IntoType] = None,
5814    dialect: DialectType = None,
5815    prefix: t.Optional[str] = None,
5816    copy: bool = False,
5817    **opts,
5818) -> Expression:
5819    """Gracefully handle a possible string or expression.
5820
5821    Example:
5822        >>> maybe_parse("1")
5823        Literal(this=1, is_string=False)
5824        >>> maybe_parse(to_identifier("x"))
5825        Identifier(this=x, quoted=False)
5826
5827    Args:
5828        sql_or_expression: the SQL code string or an expression
5829        into: the SQLGlot Expression to parse into
5830        dialect: the dialect used to parse the input expressions (in the case that an
5831            input expression is a SQL string).
5832        prefix: a string to prefix the sql with before it gets parsed
5833            (automatically includes a space)
5834        copy: whether to copy the expression.
5835        **opts: other options to use to parse the input expressions (again, in the case
5836            that an input expression is a SQL string).
5837
5838    Returns:
5839        Expression: the parsed or given expression.
5840    """
5841    if isinstance(sql_or_expression, Expression):
5842        if copy:
5843            return sql_or_expression.copy()
5844        return sql_or_expression
5845
5846    if sql_or_expression is None:
5847        raise ParseError("SQL cannot be None")
5848
5849    import sqlglot
5850
5851    sql = str(sql_or_expression)
5852    if prefix:
5853        sql = f"{prefix} {sql}"
5854
5855    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5856
5857
5858@t.overload
5859def maybe_copy(instance: None, copy: bool = True) -> None: ...
5860
5861
5862@t.overload
5863def maybe_copy(instance: E, copy: bool = True) -> E: ...
5864
5865
5866def maybe_copy(instance, copy=True):
5867    return instance.copy() if copy and instance else instance
5868
5869
5870def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5871    """Generate a textual representation of an Expression tree"""
5872    indent = "\n" + ("  " * (level + 1))
5873    delim = f",{indent}"
5874
5875    if isinstance(node, Expression):
5876        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5877
5878        if (node.type or verbose) and not isinstance(node, DataType):
5879            args["_type"] = node.type
5880        if node.comments or verbose:
5881            args["_comments"] = node.comments
5882
5883        if verbose:
5884            args["_id"] = id(node)
5885
5886        # Inline leaves for a more compact representation
5887        if node.is_leaf():
5888            indent = ""
5889            delim = ", "
5890
5891        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5892        return f"{node.__class__.__name__}({indent}{items})"
5893
5894    if isinstance(node, list):
5895        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5896        items = f"{indent}{items}" if items else ""
5897        return f"[{items}]"
5898
5899    # Indent multiline strings to match the current level
5900    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5901
5902
5903def _is_wrong_expression(expression, into):
5904    return isinstance(expression, Expression) and not isinstance(expression, into)
5905
5906
5907def _apply_builder(
5908    expression,
5909    instance,
5910    arg,
5911    copy=True,
5912    prefix=None,
5913    into=None,
5914    dialect=None,
5915    into_arg="this",
5916    **opts,
5917):
5918    if _is_wrong_expression(expression, into):
5919        expression = into(**{into_arg: expression})
5920    instance = maybe_copy(instance, copy)
5921    expression = maybe_parse(
5922        sql_or_expression=expression,
5923        prefix=prefix,
5924        into=into,
5925        dialect=dialect,
5926        **opts,
5927    )
5928    instance.set(arg, expression)
5929    return instance
5930
5931
5932def _apply_child_list_builder(
5933    *expressions,
5934    instance,
5935    arg,
5936    append=True,
5937    copy=True,
5938    prefix=None,
5939    into=None,
5940    dialect=None,
5941    properties=None,
5942    **opts,
5943):
5944    instance = maybe_copy(instance, copy)
5945    parsed = []
5946    for expression in expressions:
5947        if expression is not None:
5948            if _is_wrong_expression(expression, into):
5949                expression = into(expressions=[expression])
5950
5951            expression = maybe_parse(
5952                expression,
5953                into=into,
5954                dialect=dialect,
5955                prefix=prefix,
5956                **opts,
5957            )
5958            parsed.extend(expression.expressions)
5959
5960    existing = instance.args.get(arg)
5961    if append and existing:
5962        parsed = existing.expressions + parsed
5963
5964    child = into(expressions=parsed)
5965    for k, v in (properties or {}).items():
5966        child.set(k, v)
5967    instance.set(arg, child)
5968
5969    return instance
5970
5971
5972def _apply_list_builder(
5973    *expressions,
5974    instance,
5975    arg,
5976    append=True,
5977    copy=True,
5978    prefix=None,
5979    into=None,
5980    dialect=None,
5981    **opts,
5982):
5983    inst = maybe_copy(instance, copy)
5984
5985    expressions = [
5986        maybe_parse(
5987            sql_or_expression=expression,
5988            into=into,
5989            prefix=prefix,
5990            dialect=dialect,
5991            **opts,
5992        )
5993        for expression in expressions
5994        if expression is not None
5995    ]
5996
5997    existing_expressions = inst.args.get(arg)
5998    if append and existing_expressions:
5999        expressions = existing_expressions + expressions
6000
6001    inst.set(arg, expressions)
6002    return inst
6003
6004
6005def _apply_conjunction_builder(
6006    *expressions,
6007    instance,
6008    arg,
6009    into=None,
6010    append=True,
6011    copy=True,
6012    dialect=None,
6013    **opts,
6014):
6015    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6016    if not expressions:
6017        return instance
6018
6019    inst = maybe_copy(instance, copy)
6020
6021    existing = inst.args.get(arg)
6022    if append and existing is not None:
6023        expressions = [existing.this if into else existing] + list(expressions)
6024
6025    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6026
6027    inst.set(arg, into(this=node) if into else node)
6028    return inst
6029
6030
6031def _apply_cte_builder(
6032    instance: E,
6033    alias: ExpOrStr,
6034    as_: ExpOrStr,
6035    recursive: t.Optional[bool] = None,
6036    append: bool = True,
6037    dialect: DialectType = None,
6038    copy: bool = True,
6039    **opts,
6040) -> E:
6041    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6042    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6043    cte = CTE(this=as_expression, alias=alias_expression)
6044    return _apply_child_list_builder(
6045        cte,
6046        instance=instance,
6047        arg="with",
6048        append=append,
6049        copy=copy,
6050        into=With,
6051        properties={"recursive": recursive or False},
6052    )
6053
6054
6055def _combine(
6056    expressions: t.Sequence[t.Optional[ExpOrStr]],
6057    operator: t.Type[Connector],
6058    dialect: DialectType = None,
6059    copy: bool = True,
6060    **opts,
6061) -> Expression:
6062    conditions = [
6063        condition(expression, dialect=dialect, copy=copy, **opts)
6064        for expression in expressions
6065        if expression is not None
6066    ]
6067
6068    this, *rest = conditions
6069    if rest:
6070        this = _wrap(this, Connector)
6071    for expression in rest:
6072        this = operator(this=this, expression=_wrap(expression, Connector))
6073
6074    return this
6075
6076
6077def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6078    return Paren(this=expression) if isinstance(expression, kind) else expression
6079
6080
6081def union(
6082    left: ExpOrStr,
6083    right: ExpOrStr,
6084    distinct: bool = True,
6085    dialect: DialectType = None,
6086    copy: bool = True,
6087    **opts,
6088) -> Union:
6089    """
6090    Initializes a syntax tree from one UNION expression.
6091
6092    Example:
6093        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6094        'SELECT * FROM foo UNION SELECT * FROM bla'
6095
6096    Args:
6097        left: the SQL code string corresponding to the left-hand side.
6098            If an `Expression` instance is passed, it will be used as-is.
6099        right: the SQL code string corresponding to the right-hand side.
6100            If an `Expression` instance is passed, it will be used as-is.
6101        distinct: set the DISTINCT flag if and only if this is true.
6102        dialect: the dialect used to parse the input expression.
6103        copy: whether to copy the expression.
6104        opts: other options to use to parse the input expressions.
6105
6106    Returns:
6107        The new Union instance.
6108    """
6109    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6110    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6111
6112    return Union(this=left, expression=right, distinct=distinct)
6113
6114
6115def intersect(
6116    left: ExpOrStr,
6117    right: ExpOrStr,
6118    distinct: bool = True,
6119    dialect: DialectType = None,
6120    copy: bool = True,
6121    **opts,
6122) -> Intersect:
6123    """
6124    Initializes a syntax tree from one INTERSECT expression.
6125
6126    Example:
6127        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6128        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6129
6130    Args:
6131        left: the SQL code string corresponding to the left-hand side.
6132            If an `Expression` instance is passed, it will be used as-is.
6133        right: the SQL code string corresponding to the right-hand side.
6134            If an `Expression` instance is passed, it will be used as-is.
6135        distinct: set the DISTINCT flag if and only if this is true.
6136        dialect: the dialect used to parse the input expression.
6137        copy: whether to copy the expression.
6138        opts: other options to use to parse the input expressions.
6139
6140    Returns:
6141        The new Intersect instance.
6142    """
6143    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6144    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6145
6146    return Intersect(this=left, expression=right, distinct=distinct)
6147
6148
6149def except_(
6150    left: ExpOrStr,
6151    right: ExpOrStr,
6152    distinct: bool = True,
6153    dialect: DialectType = None,
6154    copy: bool = True,
6155    **opts,
6156) -> Except:
6157    """
6158    Initializes a syntax tree from one EXCEPT expression.
6159
6160    Example:
6161        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6162        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6163
6164    Args:
6165        left: the SQL code string corresponding to the left-hand side.
6166            If an `Expression` instance is passed, it will be used as-is.
6167        right: the SQL code string corresponding to the right-hand side.
6168            If an `Expression` instance is passed, it will be used as-is.
6169        distinct: set the DISTINCT flag if and only if this is true.
6170        dialect: the dialect used to parse the input expression.
6171        copy: whether to copy the expression.
6172        opts: other options to use to parse the input expressions.
6173
6174    Returns:
6175        The new Except instance.
6176    """
6177    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6178    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6179
6180    return Except(this=left, expression=right, distinct=distinct)
6181
6182
6183def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6184    """
6185    Initializes a syntax tree from one or multiple SELECT expressions.
6186
6187    Example:
6188        >>> select("col1", "col2").from_("tbl").sql()
6189        'SELECT col1, col2 FROM tbl'
6190
6191    Args:
6192        *expressions: the SQL code string to parse as the expressions of a
6193            SELECT statement. If an Expression instance is passed, this is used as-is.
6194        dialect: the dialect used to parse the input expressions (in the case that an
6195            input expression is a SQL string).
6196        **opts: other options to use to parse the input expressions (again, in the case
6197            that an input expression is a SQL string).
6198
6199    Returns:
6200        Select: the syntax tree for the SELECT statement.
6201    """
6202    return Select().select(*expressions, dialect=dialect, **opts)
6203
6204
6205def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6206    """
6207    Initializes a syntax tree from a FROM expression.
6208
6209    Example:
6210        >>> from_("tbl").select("col1", "col2").sql()
6211        'SELECT col1, col2 FROM tbl'
6212
6213    Args:
6214        *expression: the SQL code string to parse as the FROM expressions of a
6215            SELECT statement. If an Expression instance is passed, this is used as-is.
6216        dialect: the dialect used to parse the input expression (in the case that the
6217            input expression is a SQL string).
6218        **opts: other options to use to parse the input expressions (again, in the case
6219            that the input expression is a SQL string).
6220
6221    Returns:
6222        Select: the syntax tree for the SELECT statement.
6223    """
6224    return Select().from_(expression, dialect=dialect, **opts)
6225
6226
6227def update(
6228    table: str | Table,
6229    properties: dict,
6230    where: t.Optional[ExpOrStr] = None,
6231    from_: t.Optional[ExpOrStr] = None,
6232    dialect: DialectType = None,
6233    **opts,
6234) -> Update:
6235    """
6236    Creates an update statement.
6237
6238    Example:
6239        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6240        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6241
6242    Args:
6243        *properties: dictionary of properties to set which are
6244            auto converted to sql objects eg None -> NULL
6245        where: sql conditional parsed into a WHERE statement
6246        from_: sql statement parsed into a FROM statement
6247        dialect: the dialect used to parse the input expressions.
6248        **opts: other options to use to parse the input expressions.
6249
6250    Returns:
6251        Update: the syntax tree for the UPDATE statement.
6252    """
6253    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6254    update_expr.set(
6255        "expressions",
6256        [
6257            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6258            for k, v in properties.items()
6259        ],
6260    )
6261    if from_:
6262        update_expr.set(
6263            "from",
6264            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6265        )
6266    if isinstance(where, Condition):
6267        where = Where(this=where)
6268    if where:
6269        update_expr.set(
6270            "where",
6271            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6272        )
6273    return update_expr
6274
6275
6276def delete(
6277    table: ExpOrStr,
6278    where: t.Optional[ExpOrStr] = None,
6279    returning: t.Optional[ExpOrStr] = None,
6280    dialect: DialectType = None,
6281    **opts,
6282) -> Delete:
6283    """
6284    Builds a delete statement.
6285
6286    Example:
6287        >>> delete("my_table", where="id > 1").sql()
6288        'DELETE FROM my_table WHERE id > 1'
6289
6290    Args:
6291        where: sql conditional parsed into a WHERE statement
6292        returning: sql conditional parsed into a RETURNING statement
6293        dialect: the dialect used to parse the input expressions.
6294        **opts: other options to use to parse the input expressions.
6295
6296    Returns:
6297        Delete: the syntax tree for the DELETE statement.
6298    """
6299    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6300    if where:
6301        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6302    if returning:
6303        delete_expr = t.cast(
6304            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6305        )
6306    return delete_expr
6307
6308
6309def insert(
6310    expression: ExpOrStr,
6311    into: ExpOrStr,
6312    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6313    overwrite: t.Optional[bool] = None,
6314    returning: t.Optional[ExpOrStr] = None,
6315    dialect: DialectType = None,
6316    copy: bool = True,
6317    **opts,
6318) -> Insert:
6319    """
6320    Builds an INSERT statement.
6321
6322    Example:
6323        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6324        'INSERT INTO tbl VALUES (1, 2, 3)'
6325
6326    Args:
6327        expression: the sql string or expression of the INSERT statement
6328        into: the tbl to insert data to.
6329        columns: optionally the table's column names.
6330        overwrite: whether to INSERT OVERWRITE or not.
6331        returning: sql conditional parsed into a RETURNING statement
6332        dialect: the dialect used to parse the input expressions.
6333        copy: whether to copy the expression.
6334        **opts: other options to use to parse the input expressions.
6335
6336    Returns:
6337        Insert: the syntax tree for the INSERT statement.
6338    """
6339    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6340    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6341
6342    if columns:
6343        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6344
6345    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6346
6347    if returning:
6348        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6349
6350    return insert
6351
6352
6353def condition(
6354    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6355) -> Condition:
6356    """
6357    Initialize a logical condition expression.
6358
6359    Example:
6360        >>> condition("x=1").sql()
6361        'x = 1'
6362
6363        This is helpful for composing larger logical syntax trees:
6364        >>> where = condition("x=1")
6365        >>> where = where.and_("y=1")
6366        >>> Select().from_("tbl").select("*").where(where).sql()
6367        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6368
6369    Args:
6370        *expression: the SQL code string to parse.
6371            If an Expression instance is passed, this is used as-is.
6372        dialect: the dialect used to parse the input expression (in the case that the
6373            input expression is a SQL string).
6374        copy: Whether to copy `expression` (only applies to expressions).
6375        **opts: other options to use to parse the input expressions (again, in the case
6376            that the input expression is a SQL string).
6377
6378    Returns:
6379        The new Condition instance
6380    """
6381    return maybe_parse(
6382        expression,
6383        into=Condition,
6384        dialect=dialect,
6385        copy=copy,
6386        **opts,
6387    )
6388
6389
6390def and_(
6391    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6392) -> Condition:
6393    """
6394    Combine multiple conditions with an AND logical operator.
6395
6396    Example:
6397        >>> and_("x=1", and_("y=1", "z=1")).sql()
6398        'x = 1 AND (y = 1 AND z = 1)'
6399
6400    Args:
6401        *expressions: the SQL code strings to parse.
6402            If an Expression instance is passed, this is used as-is.
6403        dialect: the dialect used to parse the input expression.
6404        copy: whether to copy `expressions` (only applies to Expressions).
6405        **opts: other options to use to parse the input expressions.
6406
6407    Returns:
6408        And: the new condition
6409    """
6410    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6411
6412
6413def or_(
6414    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6415) -> Condition:
6416    """
6417    Combine multiple conditions with an OR logical operator.
6418
6419    Example:
6420        >>> or_("x=1", or_("y=1", "z=1")).sql()
6421        'x = 1 OR (y = 1 OR z = 1)'
6422
6423    Args:
6424        *expressions: the SQL code strings to parse.
6425            If an Expression instance is passed, this is used as-is.
6426        dialect: the dialect used to parse the input expression.
6427        copy: whether to copy `expressions` (only applies to Expressions).
6428        **opts: other options to use to parse the input expressions.
6429
6430    Returns:
6431        Or: the new condition
6432    """
6433    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6434
6435
6436def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6437    """
6438    Wrap a condition with a NOT operator.
6439
6440    Example:
6441        >>> not_("this_suit='black'").sql()
6442        "NOT this_suit = 'black'"
6443
6444    Args:
6445        expression: the SQL code string to parse.
6446            If an Expression instance is passed, this is used as-is.
6447        dialect: the dialect used to parse the input expression.
6448        copy: whether to copy the expression or not.
6449        **opts: other options to use to parse the input expressions.
6450
6451    Returns:
6452        The new condition.
6453    """
6454    this = condition(
6455        expression,
6456        dialect=dialect,
6457        copy=copy,
6458        **opts,
6459    )
6460    return Not(this=_wrap(this, Connector))
6461
6462
6463def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6464    """
6465    Wrap an expression in parentheses.
6466
6467    Example:
6468        >>> paren("5 + 3").sql()
6469        '(5 + 3)'
6470
6471    Args:
6472        expression: the SQL code string to parse.
6473            If an Expression instance is passed, this is used as-is.
6474        copy: whether to copy the expression or not.
6475
6476    Returns:
6477        The wrapped expression.
6478    """
6479    return Paren(this=maybe_parse(expression, copy=copy))
6480
6481
6482SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6483
6484
6485@t.overload
6486def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6487
6488
6489@t.overload
6490def to_identifier(
6491    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6492) -> Identifier: ...
6493
6494
6495def to_identifier(name, quoted=None, copy=True):
6496    """Builds an identifier.
6497
6498    Args:
6499        name: The name to turn into an identifier.
6500        quoted: Whether to force quote the identifier.
6501        copy: Whether to copy name if it's an Identifier.
6502
6503    Returns:
6504        The identifier ast node.
6505    """
6506
6507    if name is None:
6508        return None
6509
6510    if isinstance(name, Identifier):
6511        identifier = maybe_copy(name, copy)
6512    elif isinstance(name, str):
6513        identifier = Identifier(
6514            this=name,
6515            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6516        )
6517    else:
6518        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6519    return identifier
6520
6521
6522def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6523    """
6524    Parses a given string into an identifier.
6525
6526    Args:
6527        name: The name to parse into an identifier.
6528        dialect: The dialect to parse against.
6529
6530    Returns:
6531        The identifier ast node.
6532    """
6533    try:
6534        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6535    except ParseError:
6536        expression = to_identifier(name)
6537
6538    return expression
6539
6540
6541INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6542
6543
6544def to_interval(interval: str | Literal) -> Interval:
6545    """Builds an interval expression from a string like '1 day' or '5 months'."""
6546    if isinstance(interval, Literal):
6547        if not interval.is_string:
6548            raise ValueError("Invalid interval string.")
6549
6550        interval = interval.this
6551
6552    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6553
6554    if not interval_parts:
6555        raise ValueError("Invalid interval string.")
6556
6557    return Interval(
6558        this=Literal.string(interval_parts.group(1)),
6559        unit=Var(this=interval_parts.group(2).upper()),
6560    )
6561
6562
6563@t.overload
6564def to_table(sql_path: str | Table, **kwargs) -> Table: ...
6565
6566
6567@t.overload
6568def to_table(sql_path: None, **kwargs) -> None: ...
6569
6570
6571def to_table(
6572    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6573) -> t.Optional[Table]:
6574    """
6575    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6576    If a table is passed in then that table is returned.
6577
6578    Args:
6579        sql_path: a `[catalog].[schema].[table]` string.
6580        dialect: the source dialect according to which the table name will be parsed.
6581        copy: Whether to copy a table if it is passed in.
6582        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6583
6584    Returns:
6585        A table expression.
6586    """
6587    if sql_path is None or isinstance(sql_path, Table):
6588        return maybe_copy(sql_path, copy=copy)
6589    if not isinstance(sql_path, str):
6590        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6591
6592    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6593    if table:
6594        for k, v in kwargs.items():
6595            table.set(k, v)
6596
6597    return table
6598
6599
6600def to_column(sql_path: str | Column, **kwargs) -> Column:
6601    """
6602    Create a column from a `[table].[column]` sql path. Schema is optional.
6603
6604    If a column is passed in then that column is returned.
6605
6606    Args:
6607        sql_path: `[table].[column]` string
6608    Returns:
6609        Table: A column expression
6610    """
6611    if sql_path is None or isinstance(sql_path, Column):
6612        return sql_path
6613    if not isinstance(sql_path, str):
6614        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6615    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6616
6617
6618def alias_(
6619    expression: ExpOrStr,
6620    alias: t.Optional[str | Identifier],
6621    table: bool | t.Sequence[str | Identifier] = False,
6622    quoted: t.Optional[bool] = None,
6623    dialect: DialectType = None,
6624    copy: bool = True,
6625    **opts,
6626):
6627    """Create an Alias expression.
6628
6629    Example:
6630        >>> alias_('foo', 'bar').sql()
6631        'foo AS bar'
6632
6633        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6634        '(SELECT 1, 2) AS bar(a, b)'
6635
6636    Args:
6637        expression: the SQL code strings to parse.
6638            If an Expression instance is passed, this is used as-is.
6639        alias: the alias name to use. If the name has
6640            special characters it is quoted.
6641        table: Whether to create a table alias, can also be a list of columns.
6642        quoted: whether to quote the alias
6643        dialect: the dialect used to parse the input expression.
6644        copy: Whether to copy the expression.
6645        **opts: other options to use to parse the input expressions.
6646
6647    Returns:
6648        Alias: the aliased expression
6649    """
6650    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6651    alias = to_identifier(alias, quoted=quoted)
6652
6653    if table:
6654        table_alias = TableAlias(this=alias)
6655        exp.set("alias", table_alias)
6656
6657        if not isinstance(table, bool):
6658            for column in table:
6659                table_alias.append("columns", to_identifier(column, quoted=quoted))
6660
6661        return exp
6662
6663    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6664    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6665    # for the complete Window expression.
6666    #
6667    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6668
6669    if "alias" in exp.arg_types and not isinstance(exp, Window):
6670        exp.set("alias", alias)
6671        return exp
6672    return Alias(this=exp, alias=alias)
6673
6674
6675def subquery(
6676    expression: ExpOrStr,
6677    alias: t.Optional[Identifier | str] = None,
6678    dialect: DialectType = None,
6679    **opts,
6680) -> Select:
6681    """
6682    Build a subquery expression that's selected from.
6683
6684    Example:
6685        >>> subquery('select x from tbl', 'bar').select('x').sql()
6686        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6687
6688    Args:
6689        expression: the SQL code strings to parse.
6690            If an Expression instance is passed, this is used as-is.
6691        alias: the alias name to use.
6692        dialect: the dialect used to parse the input expression.
6693        **opts: other options to use to parse the input expressions.
6694
6695    Returns:
6696        A new Select instance with the subquery expression included.
6697    """
6698
6699    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6700    return Select().from_(expression, dialect=dialect, **opts)
6701
6702
6703@t.overload
6704def column(
6705    col: str | Identifier,
6706    table: t.Optional[str | Identifier] = None,
6707    db: t.Optional[str | Identifier] = None,
6708    catalog: t.Optional[str | Identifier] = None,
6709    *,
6710    fields: t.Collection[t.Union[str, Identifier]],
6711    quoted: t.Optional[bool] = None,
6712    copy: bool = True,
6713) -> Dot:
6714    pass
6715
6716
6717@t.overload
6718def column(
6719    col: str | Identifier,
6720    table: t.Optional[str | Identifier] = None,
6721    db: t.Optional[str | Identifier] = None,
6722    catalog: t.Optional[str | Identifier] = None,
6723    *,
6724    fields: Lit[None] = None,
6725    quoted: t.Optional[bool] = None,
6726    copy: bool = True,
6727) -> Column:
6728    pass
6729
6730
6731def column(
6732    col,
6733    table=None,
6734    db=None,
6735    catalog=None,
6736    *,
6737    fields=None,
6738    quoted=None,
6739    copy=True,
6740):
6741    """
6742    Build a Column.
6743
6744    Args:
6745        col: Column name.
6746        table: Table name.
6747        db: Database name.
6748        catalog: Catalog name.
6749        fields: Additional fields using dots.
6750        quoted: Whether to force quotes on the column's identifiers.
6751        copy: Whether to copy identifiers if passed in.
6752
6753    Returns:
6754        The new Column instance.
6755    """
6756    this = Column(
6757        this=to_identifier(col, quoted=quoted, copy=copy),
6758        table=to_identifier(table, quoted=quoted, copy=copy),
6759        db=to_identifier(db, quoted=quoted, copy=copy),
6760        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6761    )
6762
6763    if fields:
6764        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6765    return this
6766
6767
6768def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6769    """Cast an expression to a data type.
6770
6771    Example:
6772        >>> cast('x + 1', 'int').sql()
6773        'CAST(x + 1 AS INT)'
6774
6775    Args:
6776        expression: The expression to cast.
6777        to: The datatype to cast to.
6778        copy: Whether to copy the supplied expressions.
6779
6780    Returns:
6781        The new Cast instance.
6782    """
6783    expression = maybe_parse(expression, copy=copy, **opts)
6784    data_type = DataType.build(to, copy=copy, **opts)
6785    expression = Cast(this=expression, to=data_type)
6786    expression.type = data_type
6787    return expression
6788
6789
6790def table_(
6791    table: Identifier | str,
6792    db: t.Optional[Identifier | str] = None,
6793    catalog: t.Optional[Identifier | str] = None,
6794    quoted: t.Optional[bool] = None,
6795    alias: t.Optional[Identifier | str] = None,
6796) -> Table:
6797    """Build a Table.
6798
6799    Args:
6800        table: Table name.
6801        db: Database name.
6802        catalog: Catalog name.
6803        quote: Whether to force quotes on the table's identifiers.
6804        alias: Table's alias.
6805
6806    Returns:
6807        The new Table instance.
6808    """
6809    return Table(
6810        this=to_identifier(table, quoted=quoted) if table else None,
6811        db=to_identifier(db, quoted=quoted) if db else None,
6812        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6813        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6814    )
6815
6816
6817def values(
6818    values: t.Iterable[t.Tuple[t.Any, ...]],
6819    alias: t.Optional[str] = None,
6820    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6821) -> Values:
6822    """Build VALUES statement.
6823
6824    Example:
6825        >>> values([(1, '2')]).sql()
6826        "VALUES (1, '2')"
6827
6828    Args:
6829        values: values statements that will be converted to SQL
6830        alias: optional alias
6831        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6832         If either are provided then an alias is also required.
6833
6834    Returns:
6835        Values: the Values expression object
6836    """
6837    if columns and not alias:
6838        raise ValueError("Alias is required when providing columns")
6839
6840    return Values(
6841        expressions=[convert(tup) for tup in values],
6842        alias=(
6843            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6844            if columns
6845            else (TableAlias(this=to_identifier(alias)) if alias else None)
6846        ),
6847    )
6848
6849
6850def var(name: t.Optional[ExpOrStr]) -> Var:
6851    """Build a SQL variable.
6852
6853    Example:
6854        >>> repr(var('x'))
6855        'Var(this=x)'
6856
6857        >>> repr(var(column('x', table='y')))
6858        'Var(this=x)'
6859
6860    Args:
6861        name: The name of the var or an expression who's name will become the var.
6862
6863    Returns:
6864        The new variable node.
6865    """
6866    if not name:
6867        raise ValueError("Cannot convert empty name into var.")
6868
6869    if isinstance(name, Expression):
6870        name = name.name
6871    return Var(this=name)
6872
6873
6874def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6875    """Build ALTER TABLE... RENAME... expression
6876
6877    Args:
6878        old_name: The old name of the table
6879        new_name: The new name of the table
6880
6881    Returns:
6882        Alter table expression
6883    """
6884    old_table = to_table(old_name)
6885    new_table = to_table(new_name)
6886    return AlterTable(
6887        this=old_table,
6888        actions=[
6889            RenameTable(this=new_table),
6890        ],
6891    )
6892
6893
6894def rename_column(
6895    table_name: str | Table,
6896    old_column_name: str | Column,
6897    new_column_name: str | Column,
6898    exists: t.Optional[bool] = None,
6899) -> AlterTable:
6900    """Build ALTER TABLE... RENAME COLUMN... expression
6901
6902    Args:
6903        table_name: Name of the table
6904        old_column: The old name of the column
6905        new_column: The new name of the column
6906        exists: Whether to add the `IF EXISTS` clause
6907
6908    Returns:
6909        Alter table expression
6910    """
6911    table = to_table(table_name)
6912    old_column = to_column(old_column_name)
6913    new_column = to_column(new_column_name)
6914    return AlterTable(
6915        this=table,
6916        actions=[
6917            RenameColumn(this=old_column, to=new_column, exists=exists),
6918        ],
6919    )
6920
6921
6922def convert(value: t.Any, copy: bool = False) -> Expression:
6923    """Convert a python value into an expression object.
6924
6925    Raises an error if a conversion is not possible.
6926
6927    Args:
6928        value: A python object.
6929        copy: Whether to copy `value` (only applies to Expressions and collections).
6930
6931    Returns:
6932        Expression: the equivalent expression object.
6933    """
6934    if isinstance(value, Expression):
6935        return maybe_copy(value, copy)
6936    if isinstance(value, str):
6937        return Literal.string(value)
6938    if isinstance(value, bool):
6939        return Boolean(this=value)
6940    if value is None or (isinstance(value, float) and math.isnan(value)):
6941        return null()
6942    if isinstance(value, numbers.Number):
6943        return Literal.number(value)
6944    if isinstance(value, datetime.datetime):
6945        datetime_literal = Literal.string(
6946            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6947        )
6948        return TimeStrToTime(this=datetime_literal)
6949    if isinstance(value, datetime.date):
6950        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6951        return DateStrToDate(this=date_literal)
6952    if isinstance(value, tuple):
6953        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6954    if isinstance(value, list):
6955        return Array(expressions=[convert(v, copy=copy) for v in value])
6956    if isinstance(value, dict):
6957        return Map(
6958            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6959            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6960        )
6961    raise ValueError(f"Cannot convert {value}")
6962
6963
6964def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6965    """
6966    Replace children of an expression with the result of a lambda fun(child) -> exp.
6967    """
6968    for k, v in tuple(expression.args.items()):
6969        is_list_arg = type(v) is list
6970
6971        child_nodes = v if is_list_arg else [v]
6972        new_child_nodes = []
6973
6974        for cn in child_nodes:
6975            if isinstance(cn, Expression):
6976                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6977                    new_child_nodes.append(child_node)
6978            else:
6979                new_child_nodes.append(cn)
6980
6981        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
6982
6983
6984def replace_tree(
6985    expression: Expression,
6986    fun: t.Callable,
6987    prune: t.Optional[t.Callable[[Expression], bool]] = None,
6988) -> Expression:
6989    """
6990    Replace an entire tree with the result of function calls on each node.
6991
6992    This will be traversed in reverse dfs, so leaves first.
6993    If new nodes are created as a result of function calls, they will also be traversed.
6994    """
6995    stack = list(expression.dfs(prune=prune))
6996
6997    while stack:
6998        node = stack.pop()
6999        new_node = fun(node)
7000
7001        if new_node is not node:
7002            node.replace(new_node)
7003
7004            if isinstance(new_node, Expression):
7005                stack.append(new_node)
7006
7007    return new_node
7008
7009
7010def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7011    """
7012    Return all table names referenced through columns in an expression.
7013
7014    Example:
7015        >>> import sqlglot
7016        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7017        ['a', 'c']
7018
7019    Args:
7020        expression: expression to find table names.
7021        exclude: a table name to exclude
7022
7023    Returns:
7024        A list of unique names.
7025    """
7026    return {
7027        table
7028        for table in (column.table for column in expression.find_all(Column))
7029        if table and table != exclude
7030    }
7031
7032
7033def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7034    """Get the full name of a table as a string.
7035
7036    Args:
7037        table: Table expression node or string.
7038        dialect: The dialect to generate the table name for.
7039        identify: Determines when an identifier should be quoted. Possible values are:
7040            False (default): Never quote, except in cases where it's mandatory by the dialect.
7041            True: Always quote.
7042
7043    Examples:
7044        >>> from sqlglot import exp, parse_one
7045        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7046        'a.b.c'
7047
7048    Returns:
7049        The table name.
7050    """
7051
7052    table = maybe_parse(table, into=Table, dialect=dialect)
7053
7054    if not table:
7055        raise ValueError(f"Cannot parse {table}")
7056
7057    return ".".join(
7058        (
7059            part.sql(dialect=dialect, identify=True, copy=False)
7060            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7061            else part.name
7062        )
7063        for part in table.parts
7064    )
7065
7066
7067def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7068    """Returns a case normalized table name without quotes.
7069
7070    Args:
7071        table: the table to normalize
7072        dialect: the dialect to use for normalization rules
7073        copy: whether to copy the expression.
7074
7075    Examples:
7076        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7077        'A-B.c'
7078    """
7079    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7080
7081    return ".".join(
7082        p.name
7083        for p in normalize_identifiers(
7084            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7085        ).parts
7086    )
7087
7088
7089def replace_tables(
7090    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7091) -> E:
7092    """Replace all tables in expression according to the mapping.
7093
7094    Args:
7095        expression: expression node to be transformed and replaced.
7096        mapping: mapping of table names.
7097        dialect: the dialect of the mapping table
7098        copy: whether to copy the expression.
7099
7100    Examples:
7101        >>> from sqlglot import exp, parse_one
7102        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7103        'SELECT * FROM c /* a.b */'
7104
7105    Returns:
7106        The mapped expression.
7107    """
7108
7109    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7110
7111    def _replace_tables(node: Expression) -> Expression:
7112        if isinstance(node, Table):
7113            original = normalize_table_name(node, dialect=dialect)
7114            new_name = mapping.get(original)
7115
7116            if new_name:
7117                table = to_table(
7118                    new_name,
7119                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7120                    dialect=dialect,
7121                )
7122                table.add_comments([original])
7123                return table
7124        return node
7125
7126    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7127
7128
7129def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7130    """Replace placeholders in an expression.
7131
7132    Args:
7133        expression: expression node to be transformed and replaced.
7134        args: positional names that will substitute unnamed placeholders in the given order.
7135        kwargs: keyword arguments that will substitute named placeholders.
7136
7137    Examples:
7138        >>> from sqlglot import exp, parse_one
7139        >>> replace_placeholders(
7140        ...     parse_one("select * from :tbl where ? = ?"),
7141        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7142        ... ).sql()
7143        "SELECT * FROM foo WHERE str_col = 'b'"
7144
7145    Returns:
7146        The mapped expression.
7147    """
7148
7149    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7150        if isinstance(node, Placeholder):
7151            if node.name:
7152                new_name = kwargs.get(node.name)
7153                if new_name is not None:
7154                    return convert(new_name)
7155            else:
7156                try:
7157                    return convert(next(args))
7158                except StopIteration:
7159                    pass
7160        return node
7161
7162    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7163
7164
7165def expand(
7166    expression: Expression,
7167    sources: t.Dict[str, Query],
7168    dialect: DialectType = None,
7169    copy: bool = True,
7170) -> Expression:
7171    """Transforms an expression by expanding all referenced sources into subqueries.
7172
7173    Examples:
7174        >>> from sqlglot import parse_one
7175        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7176        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7177
7178        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7179        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7180
7181    Args:
7182        expression: The expression to expand.
7183        sources: A dictionary of name to Queries.
7184        dialect: The dialect of the sources dict.
7185        copy: Whether to copy the expression during transformation. Defaults to True.
7186
7187    Returns:
7188        The transformed expression.
7189    """
7190    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7191
7192    def _expand(node: Expression):
7193        if isinstance(node, Table):
7194            name = normalize_table_name(node, dialect=dialect)
7195            source = sources.get(name)
7196            if source:
7197                subquery = source.subquery(node.alias or name)
7198                subquery.comments = [f"source: {name}"]
7199                return subquery.transform(_expand, copy=False)
7200        return node
7201
7202    return expression.transform(_expand, copy=copy)
7203
7204
7205def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7206    """
7207    Returns a Func expression.
7208
7209    Examples:
7210        >>> func("abs", 5).sql()
7211        'ABS(5)'
7212
7213        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7214        'CAST(5 AS DOUBLE)'
7215
7216    Args:
7217        name: the name of the function to build.
7218        args: the args used to instantiate the function of interest.
7219        copy: whether to copy the argument expressions.
7220        dialect: the source dialect.
7221        kwargs: the kwargs used to instantiate the function of interest.
7222
7223    Note:
7224        The arguments `args` and `kwargs` are mutually exclusive.
7225
7226    Returns:
7227        An instance of the function of interest, or an anonymous function, if `name` doesn't
7228        correspond to an existing `sqlglot.expressions.Func` class.
7229    """
7230    if args and kwargs:
7231        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7232
7233    from sqlglot.dialects.dialect import Dialect
7234
7235    dialect = Dialect.get_or_raise(dialect)
7236
7237    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7238    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7239
7240    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7241    if constructor:
7242        if converted:
7243            if "dialect" in constructor.__code__.co_varnames:
7244                function = constructor(converted, dialect=dialect)
7245            else:
7246                function = constructor(converted)
7247        elif constructor.__name__ == "from_arg_list":
7248            function = constructor.__self__(**kwargs)  # type: ignore
7249        else:
7250            constructor = FUNCTION_BY_NAME.get(name.upper())
7251            if constructor:
7252                function = constructor(**kwargs)
7253            else:
7254                raise ValueError(
7255                    f"Unable to convert '{name}' into a Func. Either manually construct "
7256                    "the Func expression of interest or parse the function call."
7257                )
7258    else:
7259        kwargs = kwargs or {"expressions": converted}
7260        function = Anonymous(this=name, **kwargs)
7261
7262    for error_message in function.error_messages(converted):
7263        raise ValueError(error_message)
7264
7265    return function
7266
7267
7268def case(
7269    expression: t.Optional[ExpOrStr] = None,
7270    **opts,
7271) -> Case:
7272    """
7273    Initialize a CASE statement.
7274
7275    Example:
7276        case().when("a = 1", "foo").else_("bar")
7277
7278    Args:
7279        expression: Optionally, the input expression (not all dialects support this)
7280        **opts: Extra keyword arguments for parsing `expression`
7281    """
7282    if expression is not None:
7283        this = maybe_parse(expression, **opts)
7284    else:
7285        this = None
7286    return Case(this=this, ifs=[])
7287
7288
7289def cast_unless(
7290    expression: ExpOrStr,
7291    to: DATA_TYPE,
7292    *types: DATA_TYPE,
7293    **opts: t.Any,
7294) -> Expression | Cast:
7295    """
7296    Cast an expression to a data type unless it is a specified type.
7297
7298    Args:
7299        expression: The expression to cast.
7300        to: The data type to cast to.
7301        **types: The types to exclude from casting.
7302        **opts: Extra keyword arguments for parsing `expression`
7303    """
7304    expr = maybe_parse(expression, **opts)
7305    if expr.is_type(*types):
7306        return expr
7307    return cast(expr, to, **opts)
7308
7309
7310def array(
7311    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7312) -> Array:
7313    """
7314    Returns an array.
7315
7316    Examples:
7317        >>> array(1, 'x').sql()
7318        'ARRAY(1, x)'
7319
7320    Args:
7321        expressions: the expressions to add to the array.
7322        copy: whether to copy the argument expressions.
7323        dialect: the source dialect.
7324        kwargs: the kwargs used to instantiate the function of interest.
7325
7326    Returns:
7327        An array expression.
7328    """
7329    return Array(
7330        expressions=[
7331            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7332            for expression in expressions
7333        ]
7334    )
7335
7336
7337def tuple_(
7338    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7339) -> Tuple:
7340    """
7341    Returns an tuple.
7342
7343    Examples:
7344        >>> tuple_(1, 'x').sql()
7345        '(1, x)'
7346
7347    Args:
7348        expressions: the expressions to add to the tuple.
7349        copy: whether to copy the argument expressions.
7350        dialect: the source dialect.
7351        kwargs: the kwargs used to instantiate the function of interest.
7352
7353    Returns:
7354        A tuple expression.
7355    """
7356    return Tuple(
7357        expressions=[
7358            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7359            for expression in expressions
7360        ]
7361    )
7362
7363
7364def true() -> Boolean:
7365    """
7366    Returns a true Boolean expression.
7367    """
7368    return Boolean(this=True)
7369
7370
7371def false() -> Boolean:
7372    """
7373    Returns a false Boolean expression.
7374    """
7375    return Boolean(this=False)
7376
7377
7378def null() -> Null:
7379    """
7380    Returns a Null expression.
7381    """
7382    return Null()