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