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