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