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