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