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