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