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