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