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