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