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 Update(Expression):
3123    arg_types = {
3124        "with": False,
3125        "this": False,
3126        "expressions": True,
3127        "from": False,
3128        "where": False,
3129        "returning": False,
3130        "order": False,
3131        "limit": False,
3132    }
3133
3134
3135class Values(UDTF):
3136    arg_types = {"expressions": True, "alias": False}
3137
3138
3139class Var(Expression):
3140    pass
3141
3142
3143class Version(Expression):
3144    """
3145    Time travel, iceberg, bigquery etc
3146    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3147    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3148    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3149    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3150    this is either TIMESTAMP or VERSION
3151    kind is ("AS OF", "BETWEEN")
3152    """
3153
3154    arg_types = {"this": True, "kind": True, "expression": False}
3155
3156
3157class Schema(Expression):
3158    arg_types = {"this": False, "expressions": False}
3159
3160
3161# https://dev.mysql.com/doc/refman/8.0/en/select.html
3162# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3163class Lock(Expression):
3164    arg_types = {"update": True, "expressions": False, "wait": False}
3165
3166
3167class Select(Query):
3168    arg_types = {
3169        "with": False,
3170        "kind": False,
3171        "expressions": False,
3172        "hint": False,
3173        "distinct": False,
3174        "into": False,
3175        "from": False,
3176        **QUERY_MODIFIERS,
3177    }
3178
3179    def from_(
3180        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3181    ) -> Select:
3182        """
3183        Set the FROM expression.
3184
3185        Example:
3186            >>> Select().from_("tbl").select("x").sql()
3187            'SELECT x FROM tbl'
3188
3189        Args:
3190            expression : the SQL code strings to parse.
3191                If a `From` instance is passed, this is used as-is.
3192                If another `Expression` instance is passed, it will be wrapped in a `From`.
3193            dialect: the dialect used to parse the input expression.
3194            copy: if `False`, modify this expression instance in-place.
3195            opts: other options to use to parse the input expressions.
3196
3197        Returns:
3198            The modified Select expression.
3199        """
3200        return _apply_builder(
3201            expression=expression,
3202            instance=self,
3203            arg="from",
3204            into=From,
3205            prefix="FROM",
3206            dialect=dialect,
3207            copy=copy,
3208            **opts,
3209        )
3210
3211    def group_by(
3212        self,
3213        *expressions: t.Optional[ExpOrStr],
3214        append: bool = True,
3215        dialect: DialectType = None,
3216        copy: bool = True,
3217        **opts,
3218    ) -> Select:
3219        """
3220        Set the GROUP BY expression.
3221
3222        Example:
3223            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3224            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3225
3226        Args:
3227            *expressions: the SQL code strings to parse.
3228                If a `Group` instance is passed, this is used as-is.
3229                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3230                If nothing is passed in then a group by is not applied to the expression
3231            append: if `True`, add to any existing expressions.
3232                Otherwise, this flattens all the `Group` expression into a single expression.
3233            dialect: the dialect used to parse the input expression.
3234            copy: if `False`, modify this expression instance in-place.
3235            opts: other options to use to parse the input expressions.
3236
3237        Returns:
3238            The modified Select expression.
3239        """
3240        if not expressions:
3241            return self if not copy else self.copy()
3242
3243        return _apply_child_list_builder(
3244            *expressions,
3245            instance=self,
3246            arg="group",
3247            append=append,
3248            copy=copy,
3249            prefix="GROUP BY",
3250            into=Group,
3251            dialect=dialect,
3252            **opts,
3253        )
3254
3255    def sort_by(
3256        self,
3257        *expressions: t.Optional[ExpOrStr],
3258        append: bool = True,
3259        dialect: DialectType = None,
3260        copy: bool = True,
3261        **opts,
3262    ) -> Select:
3263        """
3264        Set the SORT BY expression.
3265
3266        Example:
3267            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3268            'SELECT x FROM tbl SORT BY x DESC'
3269
3270        Args:
3271            *expressions: the SQL code strings to parse.
3272                If a `Group` instance is passed, this is used as-is.
3273                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3274            append: if `True`, add to any existing expressions.
3275                Otherwise, this flattens all the `Order` expression into a single expression.
3276            dialect: the dialect used to parse the input expression.
3277            copy: if `False`, modify this expression instance in-place.
3278            opts: other options to use to parse the input expressions.
3279
3280        Returns:
3281            The modified Select expression.
3282        """
3283        return _apply_child_list_builder(
3284            *expressions,
3285            instance=self,
3286            arg="sort",
3287            append=append,
3288            copy=copy,
3289            prefix="SORT BY",
3290            into=Sort,
3291            dialect=dialect,
3292            **opts,
3293        )
3294
3295    def cluster_by(
3296        self,
3297        *expressions: t.Optional[ExpOrStr],
3298        append: bool = True,
3299        dialect: DialectType = None,
3300        copy: bool = True,
3301        **opts,
3302    ) -> Select:
3303        """
3304        Set the CLUSTER BY expression.
3305
3306        Example:
3307            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3308            'SELECT x FROM tbl CLUSTER BY x DESC'
3309
3310        Args:
3311            *expressions: the SQL code strings to parse.
3312                If a `Group` instance is passed, this is used as-is.
3313                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3314            append: if `True`, add to any existing expressions.
3315                Otherwise, this flattens all the `Order` expression into a single expression.
3316            dialect: the dialect used to parse the input expression.
3317            copy: if `False`, modify this expression instance in-place.
3318            opts: other options to use to parse the input expressions.
3319
3320        Returns:
3321            The modified Select expression.
3322        """
3323        return _apply_child_list_builder(
3324            *expressions,
3325            instance=self,
3326            arg="cluster",
3327            append=append,
3328            copy=copy,
3329            prefix="CLUSTER BY",
3330            into=Cluster,
3331            dialect=dialect,
3332            **opts,
3333        )
3334
3335    def select(
3336        self,
3337        *expressions: t.Optional[ExpOrStr],
3338        append: bool = True,
3339        dialect: DialectType = None,
3340        copy: bool = True,
3341        **opts,
3342    ) -> Select:
3343        return _apply_list_builder(
3344            *expressions,
3345            instance=self,
3346            arg="expressions",
3347            append=append,
3348            dialect=dialect,
3349            into=Expression,
3350            copy=copy,
3351            **opts,
3352        )
3353
3354    def lateral(
3355        self,
3356        *expressions: t.Optional[ExpOrStr],
3357        append: bool = True,
3358        dialect: DialectType = None,
3359        copy: bool = True,
3360        **opts,
3361    ) -> Select:
3362        """
3363        Append to or set the LATERAL expressions.
3364
3365        Example:
3366            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3367            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3368
3369        Args:
3370            *expressions: the SQL code strings to parse.
3371                If an `Expression` instance is passed, it will be used as-is.
3372            append: if `True`, add to any existing expressions.
3373                Otherwise, this resets the expressions.
3374            dialect: the dialect used to parse the input expressions.
3375            copy: if `False`, modify this expression instance in-place.
3376            opts: other options to use to parse the input expressions.
3377
3378        Returns:
3379            The modified Select expression.
3380        """
3381        return _apply_list_builder(
3382            *expressions,
3383            instance=self,
3384            arg="laterals",
3385            append=append,
3386            into=Lateral,
3387            prefix="LATERAL VIEW",
3388            dialect=dialect,
3389            copy=copy,
3390            **opts,
3391        )
3392
3393    def join(
3394        self,
3395        expression: ExpOrStr,
3396        on: t.Optional[ExpOrStr] = None,
3397        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3398        append: bool = True,
3399        join_type: t.Optional[str] = None,
3400        join_alias: t.Optional[Identifier | str] = None,
3401        dialect: DialectType = None,
3402        copy: bool = True,
3403        **opts,
3404    ) -> Select:
3405        """
3406        Append to or set the JOIN expressions.
3407
3408        Example:
3409            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3410            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3411
3412            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3413            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3414
3415            Use `join_type` to change the type of join:
3416
3417            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3418            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3419
3420        Args:
3421            expression: the SQL code string to parse.
3422                If an `Expression` instance is passed, it will be used as-is.
3423            on: optionally specify the join "on" criteria as a SQL string.
3424                If an `Expression` instance is passed, it will be used as-is.
3425            using: optionally specify the join "using" criteria as a SQL string.
3426                If an `Expression` instance is passed, it will be used as-is.
3427            append: if `True`, add to any existing expressions.
3428                Otherwise, this resets the expressions.
3429            join_type: if set, alter the parsed join type.
3430            join_alias: an optional alias for the joined source.
3431            dialect: the dialect used to parse the input expressions.
3432            copy: if `False`, modify this expression instance in-place.
3433            opts: other options to use to parse the input expressions.
3434
3435        Returns:
3436            Select: the modified expression.
3437        """
3438        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3439
3440        try:
3441            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3442        except ParseError:
3443            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3444
3445        join = expression if isinstance(expression, Join) else Join(this=expression)
3446
3447        if isinstance(join.this, Select):
3448            join.this.replace(join.this.subquery())
3449
3450        if join_type:
3451            method: t.Optional[Token]
3452            side: t.Optional[Token]
3453            kind: t.Optional[Token]
3454
3455            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3456
3457            if method:
3458                join.set("method", method.text)
3459            if side:
3460                join.set("side", side.text)
3461            if kind:
3462                join.set("kind", kind.text)
3463
3464        if on:
3465            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3466            join.set("on", on)
3467
3468        if using:
3469            join = _apply_list_builder(
3470                *ensure_list(using),
3471                instance=join,
3472                arg="using",
3473                append=append,
3474                copy=copy,
3475                into=Identifier,
3476                **opts,
3477            )
3478
3479        if join_alias:
3480            join.set("this", alias_(join.this, join_alias, table=True))
3481
3482        return _apply_list_builder(
3483            join,
3484            instance=self,
3485            arg="joins",
3486            append=append,
3487            copy=copy,
3488            **opts,
3489        )
3490
3491    def where(
3492        self,
3493        *expressions: t.Optional[ExpOrStr],
3494        append: bool = True,
3495        dialect: DialectType = None,
3496        copy: bool = True,
3497        **opts,
3498    ) -> Select:
3499        """
3500        Append to or set the WHERE expressions.
3501
3502        Example:
3503            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3504            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3505
3506        Args:
3507            *expressions: the SQL code strings to parse.
3508                If an `Expression` instance is passed, it will be used as-is.
3509                Multiple expressions are combined with an AND operator.
3510            append: if `True`, AND the new expressions to any existing expression.
3511                Otherwise, this resets the expression.
3512            dialect: the dialect used to parse the input expressions.
3513            copy: if `False`, modify this expression instance in-place.
3514            opts: other options to use to parse the input expressions.
3515
3516        Returns:
3517            Select: the modified expression.
3518        """
3519        return _apply_conjunction_builder(
3520            *expressions,
3521            instance=self,
3522            arg="where",
3523            append=append,
3524            into=Where,
3525            dialect=dialect,
3526            copy=copy,
3527            **opts,
3528        )
3529
3530    def having(
3531        self,
3532        *expressions: t.Optional[ExpOrStr],
3533        append: bool = True,
3534        dialect: DialectType = None,
3535        copy: bool = True,
3536        **opts,
3537    ) -> Select:
3538        """
3539        Append to or set the HAVING expressions.
3540
3541        Example:
3542            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3543            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3544
3545        Args:
3546            *expressions: the SQL code strings to parse.
3547                If an `Expression` instance is passed, it will be used as-is.
3548                Multiple expressions are combined with an AND operator.
3549            append: if `True`, AND the new expressions to any existing expression.
3550                Otherwise, this resets the expression.
3551            dialect: the dialect used to parse the input expressions.
3552            copy: if `False`, modify this expression instance in-place.
3553            opts: other options to use to parse the input expressions.
3554
3555        Returns:
3556            The modified Select expression.
3557        """
3558        return _apply_conjunction_builder(
3559            *expressions,
3560            instance=self,
3561            arg="having",
3562            append=append,
3563            into=Having,
3564            dialect=dialect,
3565            copy=copy,
3566            **opts,
3567        )
3568
3569    def window(
3570        self,
3571        *expressions: t.Optional[ExpOrStr],
3572        append: bool = True,
3573        dialect: DialectType = None,
3574        copy: bool = True,
3575        **opts,
3576    ) -> Select:
3577        return _apply_list_builder(
3578            *expressions,
3579            instance=self,
3580            arg="windows",
3581            append=append,
3582            into=Window,
3583            dialect=dialect,
3584            copy=copy,
3585            **opts,
3586        )
3587
3588    def qualify(
3589        self,
3590        *expressions: t.Optional[ExpOrStr],
3591        append: bool = True,
3592        dialect: DialectType = None,
3593        copy: bool = True,
3594        **opts,
3595    ) -> Select:
3596        return _apply_conjunction_builder(
3597            *expressions,
3598            instance=self,
3599            arg="qualify",
3600            append=append,
3601            into=Qualify,
3602            dialect=dialect,
3603            copy=copy,
3604            **opts,
3605        )
3606
3607    def distinct(
3608        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3609    ) -> Select:
3610        """
3611        Set the OFFSET expression.
3612
3613        Example:
3614            >>> Select().from_("tbl").select("x").distinct().sql()
3615            'SELECT DISTINCT x FROM tbl'
3616
3617        Args:
3618            ons: the expressions to distinct on
3619            distinct: whether the Select should be distinct
3620            copy: if `False`, modify this expression instance in-place.
3621
3622        Returns:
3623            Select: the modified expression.
3624        """
3625        instance = maybe_copy(self, copy)
3626        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3627        instance.set("distinct", Distinct(on=on) if distinct else None)
3628        return instance
3629
3630    def ctas(
3631        self,
3632        table: ExpOrStr,
3633        properties: t.Optional[t.Dict] = None,
3634        dialect: DialectType = None,
3635        copy: bool = True,
3636        **opts,
3637    ) -> Create:
3638        """
3639        Convert this expression to a CREATE TABLE AS statement.
3640
3641        Example:
3642            >>> Select().select("*").from_("tbl").ctas("x").sql()
3643            'CREATE TABLE x AS SELECT * FROM tbl'
3644
3645        Args:
3646            table: the SQL code string to parse as the table name.
3647                If another `Expression` instance is passed, it will be used as-is.
3648            properties: an optional mapping of table properties
3649            dialect: the dialect used to parse the input table.
3650            copy: if `False`, modify this expression instance in-place.
3651            opts: other options to use to parse the input table.
3652
3653        Returns:
3654            The new Create expression.
3655        """
3656        instance = maybe_copy(self, copy)
3657        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3658
3659        properties_expression = None
3660        if properties:
3661            properties_expression = Properties.from_dict(properties)
3662
3663        return Create(
3664            this=table_expression,
3665            kind="TABLE",
3666            expression=instance,
3667            properties=properties_expression,
3668        )
3669
3670    def lock(self, update: bool = True, copy: bool = True) -> Select:
3671        """
3672        Set the locking read mode for this expression.
3673
3674        Examples:
3675            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3676            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3677
3678            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3679            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3680
3681        Args:
3682            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3683            copy: if `False`, modify this expression instance in-place.
3684
3685        Returns:
3686            The modified expression.
3687        """
3688        inst = maybe_copy(self, copy)
3689        inst.set("locks", [Lock(update=update)])
3690
3691        return inst
3692
3693    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3694        """
3695        Set hints for this expression.
3696
3697        Examples:
3698            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3699            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3700
3701        Args:
3702            hints: The SQL code strings to parse as the hints.
3703                If an `Expression` instance is passed, it will be used as-is.
3704            dialect: The dialect used to parse the hints.
3705            copy: If `False`, modify this expression instance in-place.
3706
3707        Returns:
3708            The modified expression.
3709        """
3710        inst = maybe_copy(self, copy)
3711        inst.set(
3712            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3713        )
3714
3715        return inst
3716
3717    @property
3718    def named_selects(self) -> t.List[str]:
3719        return [e.output_name for e in self.expressions if e.alias_or_name]
3720
3721    @property
3722    def is_star(self) -> bool:
3723        return any(expression.is_star for expression in self.expressions)
3724
3725    @property
3726    def selects(self) -> t.List[Expression]:
3727        return self.expressions
3728
3729
3730UNWRAPPED_QUERIES = (Select, Union)
3731
3732
3733class Subquery(DerivedTable, Query):
3734    arg_types = {
3735        "this": True,
3736        "alias": False,
3737        "with": False,
3738        **QUERY_MODIFIERS,
3739    }
3740
3741    def unnest(self):
3742        """Returns the first non subquery."""
3743        expression = self
3744        while isinstance(expression, Subquery):
3745            expression = expression.this
3746        return expression
3747
3748    def unwrap(self) -> Subquery:
3749        expression = self
3750        while expression.same_parent and expression.is_wrapper:
3751            expression = t.cast(Subquery, expression.parent)
3752        return expression
3753
3754    def select(
3755        self,
3756        *expressions: t.Optional[ExpOrStr],
3757        append: bool = True,
3758        dialect: DialectType = None,
3759        copy: bool = True,
3760        **opts,
3761    ) -> Subquery:
3762        this = maybe_copy(self, copy)
3763        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3764        return this
3765
3766    @property
3767    def is_wrapper(self) -> bool:
3768        """
3769        Whether this Subquery acts as a simple wrapper around another expression.
3770
3771        SELECT * FROM (((SELECT * FROM t)))
3772                      ^
3773                      This corresponds to a "wrapper" Subquery node
3774        """
3775        return all(v is None for k, v in self.args.items() if k != "this")
3776
3777    @property
3778    def is_star(self) -> bool:
3779        return self.this.is_star
3780
3781    @property
3782    def output_name(self) -> str:
3783        return self.alias
3784
3785
3786class TableSample(Expression):
3787    arg_types = {
3788        "this": False,
3789        "expressions": False,
3790        "method": False,
3791        "bucket_numerator": False,
3792        "bucket_denominator": False,
3793        "bucket_field": False,
3794        "percent": False,
3795        "rows": False,
3796        "size": False,
3797        "seed": False,
3798    }
3799
3800
3801class Tag(Expression):
3802    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3803
3804    arg_types = {
3805        "this": False,
3806        "prefix": False,
3807        "postfix": False,
3808    }
3809
3810
3811# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3812# https://duckdb.org/docs/sql/statements/pivot
3813class Pivot(Expression):
3814    arg_types = {
3815        "this": False,
3816        "alias": False,
3817        "expressions": False,
3818        "field": False,
3819        "unpivot": False,
3820        "using": False,
3821        "group": False,
3822        "columns": False,
3823        "include_nulls": False,
3824    }
3825
3826    @property
3827    def unpivot(self) -> bool:
3828        return bool(self.args.get("unpivot"))
3829
3830
3831class Window(Condition):
3832    arg_types = {
3833        "this": True,
3834        "partition_by": False,
3835        "order": False,
3836        "spec": False,
3837        "alias": False,
3838        "over": False,
3839        "first": False,
3840    }
3841
3842
3843class WindowSpec(Expression):
3844    arg_types = {
3845        "kind": False,
3846        "start": False,
3847        "start_side": False,
3848        "end": False,
3849        "end_side": False,
3850    }
3851
3852
3853class PreWhere(Expression):
3854    pass
3855
3856
3857class Where(Expression):
3858    pass
3859
3860
3861class Star(Expression):
3862    arg_types = {"except": False, "replace": False, "rename": False}
3863
3864    @property
3865    def name(self) -> str:
3866        return "*"
3867
3868    @property
3869    def output_name(self) -> str:
3870        return self.name
3871
3872
3873class Parameter(Condition):
3874    arg_types = {"this": True, "expression": False}
3875
3876
3877class SessionParameter(Condition):
3878    arg_types = {"this": True, "kind": False}
3879
3880
3881class Placeholder(Condition):
3882    arg_types = {"this": False, "kind": False}
3883
3884    @property
3885    def name(self) -> str:
3886        return self.this or "?"
3887
3888
3889class Null(Condition):
3890    arg_types: t.Dict[str, t.Any] = {}
3891
3892    @property
3893    def name(self) -> str:
3894        return "NULL"
3895
3896
3897class Boolean(Condition):
3898    pass
3899
3900
3901class DataTypeParam(Expression):
3902    arg_types = {"this": True, "expression": False}
3903
3904    @property
3905    def name(self) -> str:
3906        return self.this.name
3907
3908
3909class DataType(Expression):
3910    arg_types = {
3911        "this": True,
3912        "expressions": False,
3913        "nested": False,
3914        "values": False,
3915        "prefix": False,
3916        "kind": False,
3917    }
3918
3919    class Type(AutoName):
3920        ARRAY = auto()
3921        AGGREGATEFUNCTION = auto()
3922        SIMPLEAGGREGATEFUNCTION = auto()
3923        BIGDECIMAL = auto()
3924        BIGINT = auto()
3925        BIGSERIAL = auto()
3926        BINARY = auto()
3927        BIT = auto()
3928        BOOLEAN = auto()
3929        BPCHAR = auto()
3930        CHAR = auto()
3931        DATE = auto()
3932        DATE32 = auto()
3933        DATEMULTIRANGE = auto()
3934        DATERANGE = auto()
3935        DATETIME = auto()
3936        DATETIME64 = auto()
3937        DECIMAL = auto()
3938        DOUBLE = auto()
3939        ENUM = auto()
3940        ENUM8 = auto()
3941        ENUM16 = auto()
3942        FIXEDSTRING = auto()
3943        FLOAT = auto()
3944        GEOGRAPHY = auto()
3945        GEOMETRY = auto()
3946        HLLSKETCH = auto()
3947        HSTORE = auto()
3948        IMAGE = auto()
3949        INET = auto()
3950        INT = auto()
3951        INT128 = auto()
3952        INT256 = auto()
3953        INT4MULTIRANGE = auto()
3954        INT4RANGE = auto()
3955        INT8MULTIRANGE = auto()
3956        INT8RANGE = auto()
3957        INTERVAL = auto()
3958        IPADDRESS = auto()
3959        IPPREFIX = auto()
3960        IPV4 = auto()
3961        IPV6 = auto()
3962        JSON = auto()
3963        JSONB = auto()
3964        LIST = auto()
3965        LONGBLOB = auto()
3966        LONGTEXT = auto()
3967        LOWCARDINALITY = auto()
3968        MAP = auto()
3969        MEDIUMBLOB = auto()
3970        MEDIUMINT = auto()
3971        MEDIUMTEXT = auto()
3972        MONEY = auto()
3973        NAME = auto()
3974        NCHAR = auto()
3975        NESTED = auto()
3976        NULL = auto()
3977        NULLABLE = auto()
3978        NUMMULTIRANGE = auto()
3979        NUMRANGE = auto()
3980        NVARCHAR = auto()
3981        OBJECT = auto()
3982        ROWVERSION = auto()
3983        SERIAL = auto()
3984        SET = auto()
3985        SMALLINT = auto()
3986        SMALLMONEY = auto()
3987        SMALLSERIAL = auto()
3988        STRUCT = auto()
3989        SUPER = auto()
3990        TEXT = auto()
3991        TINYBLOB = auto()
3992        TINYTEXT = auto()
3993        TIME = auto()
3994        TIMETZ = auto()
3995        TIMESTAMP = auto()
3996        TIMESTAMPNTZ = auto()
3997        TIMESTAMPLTZ = auto()
3998        TIMESTAMPTZ = auto()
3999        TIMESTAMP_S = auto()
4000        TIMESTAMP_MS = auto()
4001        TIMESTAMP_NS = auto()
4002        TINYINT = auto()
4003        TSMULTIRANGE = auto()
4004        TSRANGE = auto()
4005        TSTZMULTIRANGE = auto()
4006        TSTZRANGE = auto()
4007        UBIGINT = auto()
4008        UINT = auto()
4009        UINT128 = auto()
4010        UINT256 = auto()
4011        UMEDIUMINT = auto()
4012        UDECIMAL = auto()
4013        UNIQUEIDENTIFIER = auto()
4014        UNKNOWN = auto()  # Sentinel value, useful for type annotation
4015        USERDEFINED = "USER-DEFINED"
4016        USMALLINT = auto()
4017        UTINYINT = auto()
4018        UUID = auto()
4019        VARBINARY = auto()
4020        VARCHAR = auto()
4021        VARIANT = auto()
4022        XML = auto()
4023        YEAR = auto()
4024        TDIGEST = auto()
4025
4026    STRUCT_TYPES = {
4027        Type.NESTED,
4028        Type.OBJECT,
4029        Type.STRUCT,
4030    }
4031
4032    NESTED_TYPES = {
4033        *STRUCT_TYPES,
4034        Type.ARRAY,
4035        Type.MAP,
4036    }
4037
4038    TEXT_TYPES = {
4039        Type.CHAR,
4040        Type.NCHAR,
4041        Type.NVARCHAR,
4042        Type.TEXT,
4043        Type.VARCHAR,
4044        Type.NAME,
4045    }
4046
4047    SIGNED_INTEGER_TYPES = {
4048        Type.BIGINT,
4049        Type.INT,
4050        Type.INT128,
4051        Type.INT256,
4052        Type.MEDIUMINT,
4053        Type.SMALLINT,
4054        Type.TINYINT,
4055    }
4056
4057    UNSIGNED_INTEGER_TYPES = {
4058        Type.UBIGINT,
4059        Type.UINT,
4060        Type.UINT128,
4061        Type.UINT256,
4062        Type.UMEDIUMINT,
4063        Type.USMALLINT,
4064        Type.UTINYINT,
4065    }
4066
4067    INTEGER_TYPES = {
4068        *SIGNED_INTEGER_TYPES,
4069        *UNSIGNED_INTEGER_TYPES,
4070        Type.BIT,
4071    }
4072
4073    FLOAT_TYPES = {
4074        Type.DOUBLE,
4075        Type.FLOAT,
4076    }
4077
4078    REAL_TYPES = {
4079        *FLOAT_TYPES,
4080        Type.BIGDECIMAL,
4081        Type.DECIMAL,
4082        Type.MONEY,
4083        Type.SMALLMONEY,
4084        Type.UDECIMAL,
4085    }
4086
4087    NUMERIC_TYPES = {
4088        *INTEGER_TYPES,
4089        *REAL_TYPES,
4090    }
4091
4092    TEMPORAL_TYPES = {
4093        Type.DATE,
4094        Type.DATE32,
4095        Type.DATETIME,
4096        Type.DATETIME64,
4097        Type.TIME,
4098        Type.TIMESTAMP,
4099        Type.TIMESTAMPNTZ,
4100        Type.TIMESTAMPLTZ,
4101        Type.TIMESTAMPTZ,
4102        Type.TIMESTAMP_MS,
4103        Type.TIMESTAMP_NS,
4104        Type.TIMESTAMP_S,
4105        Type.TIMETZ,
4106    }
4107
4108    @classmethod
4109    def build(
4110        cls,
4111        dtype: DATA_TYPE,
4112        dialect: DialectType = None,
4113        udt: bool = False,
4114        copy: bool = True,
4115        **kwargs,
4116    ) -> DataType:
4117        """
4118        Constructs a DataType object.
4119
4120        Args:
4121            dtype: the data type of interest.
4122            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4123            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4124                DataType, thus creating a user-defined type.
4125            copy: whether to copy the data type.
4126            kwargs: additional arguments to pass in the constructor of DataType.
4127
4128        Returns:
4129            The constructed DataType object.
4130        """
4131        from sqlglot import parse_one
4132
4133        if isinstance(dtype, str):
4134            if dtype.upper() == "UNKNOWN":
4135                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4136
4137            try:
4138                data_type_exp = parse_one(
4139                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4140                )
4141            except ParseError:
4142                if udt:
4143                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4144                raise
4145        elif isinstance(dtype, DataType.Type):
4146            data_type_exp = DataType(this=dtype)
4147        elif isinstance(dtype, DataType):
4148            return maybe_copy(dtype, copy)
4149        else:
4150            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4151
4152        return DataType(**{**data_type_exp.args, **kwargs})
4153
4154    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4155        """
4156        Checks whether this DataType matches one of the provided data types. Nested types or precision
4157        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4158
4159        Args:
4160            dtypes: the data types to compare this DataType to.
4161
4162        Returns:
4163            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4164        """
4165        for dtype in dtypes:
4166            other = DataType.build(dtype, copy=False, udt=True)
4167
4168            if (
4169                other.expressions
4170                or self.this == DataType.Type.USERDEFINED
4171                or other.this == DataType.Type.USERDEFINED
4172            ):
4173                matches = self == other
4174            else:
4175                matches = self.this == other.this
4176
4177            if matches:
4178                return True
4179        return False
4180
4181
4182DATA_TYPE = t.Union[str, DataType, DataType.Type]
4183
4184
4185# https://www.postgresql.org/docs/15/datatype-pseudo.html
4186class PseudoType(DataType):
4187    arg_types = {"this": True}
4188
4189
4190# https://www.postgresql.org/docs/15/datatype-oid.html
4191class ObjectIdentifier(DataType):
4192    arg_types = {"this": True}
4193
4194
4195# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4196class SubqueryPredicate(Predicate):
4197    pass
4198
4199
4200class All(SubqueryPredicate):
4201    pass
4202
4203
4204class Any(SubqueryPredicate):
4205    pass
4206
4207
4208class Exists(SubqueryPredicate):
4209    pass
4210
4211
4212# Commands to interact with the databases or engines. For most of the command
4213# expressions we parse whatever comes after the command's name as a string.
4214class Command(Expression):
4215    arg_types = {"this": True, "expression": False}
4216
4217
4218class Transaction(Expression):
4219    arg_types = {"this": False, "modes": False, "mark": False}
4220
4221
4222class Commit(Expression):
4223    arg_types = {"chain": False, "this": False, "durability": False}
4224
4225
4226class Rollback(Expression):
4227    arg_types = {"savepoint": False, "this": False}
4228
4229
4230class AlterTable(Expression):
4231    arg_types = {
4232        "this": True,
4233        "actions": True,
4234        "exists": False,
4235        "only": False,
4236        "options": False,
4237        "cluster": False,
4238    }
4239
4240
4241class AddConstraint(Expression):
4242    arg_types = {"expressions": True}
4243
4244
4245class DropPartition(Expression):
4246    arg_types = {"expressions": True, "exists": False}
4247
4248
4249# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#replace-partition
4250class ReplacePartition(Expression):
4251    arg_types = {"expression": True, "source": True}
4252
4253
4254# Binary expressions like (ADD a b)
4255class Binary(Condition):
4256    arg_types = {"this": True, "expression": True}
4257
4258    @property
4259    def left(self) -> Expression:
4260        return self.this
4261
4262    @property
4263    def right(self) -> Expression:
4264        return self.expression
4265
4266
4267class Add(Binary):
4268    pass
4269
4270
4271class Connector(Binary):
4272    pass
4273
4274
4275class And(Connector):
4276    pass
4277
4278
4279class Or(Connector):
4280    pass
4281
4282
4283class BitwiseAnd(Binary):
4284    pass
4285
4286
4287class BitwiseLeftShift(Binary):
4288    pass
4289
4290
4291class BitwiseOr(Binary):
4292    pass
4293
4294
4295class BitwiseRightShift(Binary):
4296    pass
4297
4298
4299class BitwiseXor(Binary):
4300    pass
4301
4302
4303class Div(Binary):
4304    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4305
4306
4307class Overlaps(Binary):
4308    pass
4309
4310
4311class Dot(Binary):
4312    @property
4313    def is_star(self) -> bool:
4314        return self.expression.is_star
4315
4316    @property
4317    def name(self) -> str:
4318        return self.expression.name
4319
4320    @property
4321    def output_name(self) -> str:
4322        return self.name
4323
4324    @classmethod
4325    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4326        """Build a Dot object with a sequence of expressions."""
4327        if len(expressions) < 2:
4328            raise ValueError("Dot requires >= 2 expressions.")
4329
4330        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4331
4332    @property
4333    def parts(self) -> t.List[Expression]:
4334        """Return the parts of a table / column in order catalog, db, table."""
4335        this, *parts = self.flatten()
4336
4337        parts.reverse()
4338
4339        for arg in COLUMN_PARTS:
4340            part = this.args.get(arg)
4341
4342            if isinstance(part, Expression):
4343                parts.append(part)
4344
4345        parts.reverse()
4346        return parts
4347
4348
4349class DPipe(Binary):
4350    arg_types = {"this": True, "expression": True, "safe": False}
4351
4352
4353class EQ(Binary, Predicate):
4354    pass
4355
4356
4357class NullSafeEQ(Binary, Predicate):
4358    pass
4359
4360
4361class NullSafeNEQ(Binary, Predicate):
4362    pass
4363
4364
4365# Represents e.g. := in DuckDB which is mostly used for setting parameters
4366class PropertyEQ(Binary):
4367    pass
4368
4369
4370class Distance(Binary):
4371    pass
4372
4373
4374class Escape(Binary):
4375    pass
4376
4377
4378class Glob(Binary, Predicate):
4379    pass
4380
4381
4382class GT(Binary, Predicate):
4383    pass
4384
4385
4386class GTE(Binary, Predicate):
4387    pass
4388
4389
4390class ILike(Binary, Predicate):
4391    pass
4392
4393
4394class ILikeAny(Binary, Predicate):
4395    pass
4396
4397
4398class IntDiv(Binary):
4399    pass
4400
4401
4402class Is(Binary, Predicate):
4403    pass
4404
4405
4406class Kwarg(Binary):
4407    """Kwarg in special functions like func(kwarg => y)."""
4408
4409
4410class Like(Binary, Predicate):
4411    pass
4412
4413
4414class LikeAny(Binary, Predicate):
4415    pass
4416
4417
4418class LT(Binary, Predicate):
4419    pass
4420
4421
4422class LTE(Binary, Predicate):
4423    pass
4424
4425
4426class Mod(Binary):
4427    pass
4428
4429
4430class Mul(Binary):
4431    pass
4432
4433
4434class NEQ(Binary, Predicate):
4435    pass
4436
4437
4438# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4439class Operator(Binary):
4440    arg_types = {"this": True, "operator": True, "expression": True}
4441
4442
4443class SimilarTo(Binary, Predicate):
4444    pass
4445
4446
4447class Slice(Binary):
4448    arg_types = {"this": False, "expression": False}
4449
4450
4451class Sub(Binary):
4452    pass
4453
4454
4455# Unary Expressions
4456# (NOT a)
4457class Unary(Condition):
4458    pass
4459
4460
4461class BitwiseNot(Unary):
4462    pass
4463
4464
4465class Not(Unary):
4466    pass
4467
4468
4469class Paren(Unary):
4470    @property
4471    def output_name(self) -> str:
4472        return self.this.name
4473
4474
4475class Neg(Unary):
4476    pass
4477
4478
4479class Alias(Expression):
4480    arg_types = {"this": True, "alias": False}
4481
4482    @property
4483    def output_name(self) -> str:
4484        return self.alias
4485
4486
4487# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4488# other dialects require identifiers. This enables us to transpile between them easily.
4489class PivotAlias(Alias):
4490    pass
4491
4492
4493class Aliases(Expression):
4494    arg_types = {"this": True, "expressions": True}
4495
4496    @property
4497    def aliases(self):
4498        return self.expressions
4499
4500
4501# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4502class AtIndex(Expression):
4503    arg_types = {"this": True, "expression": True}
4504
4505
4506class AtTimeZone(Expression):
4507    arg_types = {"this": True, "zone": True}
4508
4509
4510class FromTimeZone(Expression):
4511    arg_types = {"this": True, "zone": True}
4512
4513
4514class Between(Predicate):
4515    arg_types = {"this": True, "low": True, "high": True}
4516
4517
4518class Bracket(Condition):
4519    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4520    arg_types = {
4521        "this": True,
4522        "expressions": True,
4523        "offset": False,
4524        "safe": False,
4525        "returns_list_for_maps": False,
4526    }
4527
4528    @property
4529    def output_name(self) -> str:
4530        if len(self.expressions) == 1:
4531            return self.expressions[0].output_name
4532
4533        return super().output_name
4534
4535
4536class Distinct(Expression):
4537    arg_types = {"expressions": False, "on": False}
4538
4539
4540class In(Predicate):
4541    arg_types = {
4542        "this": True,
4543        "expressions": False,
4544        "query": False,
4545        "unnest": False,
4546        "field": False,
4547        "is_global": False,
4548    }
4549
4550
4551# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4552class ForIn(Expression):
4553    arg_types = {"this": True, "expression": True}
4554
4555
4556class TimeUnit(Expression):
4557    """Automatically converts unit arg into a var."""
4558
4559    arg_types = {"unit": False}
4560
4561    UNABBREVIATED_UNIT_NAME = {
4562        "D": "DAY",
4563        "H": "HOUR",
4564        "M": "MINUTE",
4565        "MS": "MILLISECOND",
4566        "NS": "NANOSECOND",
4567        "Q": "QUARTER",
4568        "S": "SECOND",
4569        "US": "MICROSECOND",
4570        "W": "WEEK",
4571        "Y": "YEAR",
4572    }
4573
4574    VAR_LIKE = (Column, Literal, Var)
4575
4576    def __init__(self, **args):
4577        unit = args.get("unit")
4578        if isinstance(unit, self.VAR_LIKE):
4579            args["unit"] = Var(
4580                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4581            )
4582        elif isinstance(unit, Week):
4583            unit.set("this", Var(this=unit.this.name.upper()))
4584
4585        super().__init__(**args)
4586
4587    @property
4588    def unit(self) -> t.Optional[Var | IntervalSpan]:
4589        return self.args.get("unit")
4590
4591
4592class IntervalOp(TimeUnit):
4593    arg_types = {"unit": True, "expression": True}
4594
4595    def interval(self):
4596        return Interval(
4597            this=self.expression.copy(),
4598            unit=self.unit.copy(),
4599        )
4600
4601
4602# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4603# https://trino.io/docs/current/language/types.html#interval-day-to-second
4604# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4605class IntervalSpan(DataType):
4606    arg_types = {"this": True, "expression": True}
4607
4608
4609class Interval(TimeUnit):
4610    arg_types = {"this": False, "unit": False}
4611
4612
4613class IgnoreNulls(Expression):
4614    pass
4615
4616
4617class RespectNulls(Expression):
4618    pass
4619
4620
4621# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4622class HavingMax(Expression):
4623    arg_types = {"this": True, "expression": True, "max": True}
4624
4625
4626# Functions
4627class Func(Condition):
4628    """
4629    The base class for all function expressions.
4630
4631    Attributes:
4632        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4633            treated as a variable length argument and the argument's value will be stored as a list.
4634        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4635            function expression. These values are used to map this node to a name during parsing as
4636            well as to provide the function's name during SQL string generation. By default the SQL
4637            name is set to the expression's class name transformed to snake case.
4638    """
4639
4640    is_var_len_args = False
4641
4642    @classmethod
4643    def from_arg_list(cls, args):
4644        if cls.is_var_len_args:
4645            all_arg_keys = list(cls.arg_types)
4646            # If this function supports variable length argument treat the last argument as such.
4647            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4648            num_non_var = len(non_var_len_arg_keys)
4649
4650            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4651            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4652        else:
4653            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4654
4655        return cls(**args_dict)
4656
4657    @classmethod
4658    def sql_names(cls):
4659        if cls is Func:
4660            raise NotImplementedError(
4661                "SQL name is only supported by concrete function implementations"
4662            )
4663        if "_sql_names" not in cls.__dict__:
4664            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4665        return cls._sql_names
4666
4667    @classmethod
4668    def sql_name(cls):
4669        return cls.sql_names()[0]
4670
4671    @classmethod
4672    def default_parser_mappings(cls):
4673        return {name: cls.from_arg_list for name in cls.sql_names()}
4674
4675
4676class AggFunc(Func):
4677    pass
4678
4679
4680class ParameterizedAgg(AggFunc):
4681    arg_types = {"this": True, "expressions": True, "params": True}
4682
4683
4684class Abs(Func):
4685    pass
4686
4687
4688class ArgMax(AggFunc):
4689    arg_types = {"this": True, "expression": True, "count": False}
4690    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4691
4692
4693class ArgMin(AggFunc):
4694    arg_types = {"this": True, "expression": True, "count": False}
4695    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4696
4697
4698class ApproxTopK(AggFunc):
4699    arg_types = {"this": True, "expression": False, "counters": False}
4700
4701
4702class Flatten(Func):
4703    pass
4704
4705
4706# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4707class Transform(Func):
4708    arg_types = {"this": True, "expression": True}
4709
4710
4711class Anonymous(Func):
4712    arg_types = {"this": True, "expressions": False}
4713    is_var_len_args = True
4714
4715    @property
4716    def name(self) -> str:
4717        return self.this if isinstance(self.this, str) else self.this.name
4718
4719
4720class AnonymousAggFunc(AggFunc):
4721    arg_types = {"this": True, "expressions": False}
4722    is_var_len_args = True
4723
4724
4725# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4726class CombinedAggFunc(AnonymousAggFunc):
4727    arg_types = {"this": True, "expressions": False, "parts": True}
4728
4729
4730class CombinedParameterizedAgg(ParameterizedAgg):
4731    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4732
4733
4734# https://docs.snowflake.com/en/sql-reference/functions/hll
4735# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4736class Hll(AggFunc):
4737    arg_types = {"this": True, "expressions": False}
4738    is_var_len_args = True
4739
4740
4741class ApproxDistinct(AggFunc):
4742    arg_types = {"this": True, "accuracy": False}
4743    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4744
4745
4746class Array(Func):
4747    arg_types = {"expressions": False}
4748    is_var_len_args = True
4749
4750
4751# https://docs.snowflake.com/en/sql-reference/functions/to_array
4752class ToArray(Func):
4753    pass
4754
4755
4756# https://materialize.com/docs/sql/types/list/
4757class List(Func):
4758    arg_types = {"expressions": False}
4759    is_var_len_args = True
4760
4761
4762# https://docs.snowflake.com/en/sql-reference/functions/to_char
4763# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4764class ToChar(Func):
4765    arg_types = {"this": True, "format": False, "nlsparam": False}
4766
4767
4768# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4769# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4770class ToNumber(Func):
4771    arg_types = {
4772        "this": True,
4773        "format": False,
4774        "nlsparam": False,
4775        "precision": False,
4776        "scale": False,
4777    }
4778
4779
4780# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4781class Convert(Func):
4782    arg_types = {"this": True, "expression": True, "style": False}
4783
4784
4785class GenerateSeries(Func):
4786    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4787
4788
4789class ArrayAgg(AggFunc):
4790    pass
4791
4792
4793class ArrayUniqueAgg(AggFunc):
4794    pass
4795
4796
4797class ArrayAll(Func):
4798    arg_types = {"this": True, "expression": True}
4799
4800
4801# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4802class ArrayAny(Func):
4803    arg_types = {"this": True, "expression": True}
4804
4805
4806class ArrayConcat(Func):
4807    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4808    arg_types = {"this": True, "expressions": False}
4809    is_var_len_args = True
4810
4811
4812class ArrayConstructCompact(Func):
4813    arg_types = {"expressions": True}
4814    is_var_len_args = True
4815
4816
4817class ArrayContains(Binary, Func):
4818    _sql_names = ["ARRAY_CONTAINS", "ARRAY_HAS"]
4819
4820
4821class ArrayContainsAll(Binary, Func):
4822    _sql_names = ["ARRAY_CONTAINS_ALL", "ARRAY_HAS_ALL"]
4823
4824
4825class ArrayFilter(Func):
4826    arg_types = {"this": True, "expression": True}
4827    _sql_names = ["FILTER", "ARRAY_FILTER"]
4828
4829
4830class ArrayToString(Func):
4831    arg_types = {"this": True, "expression": True, "null": False}
4832    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4833
4834
4835class StringToArray(Func):
4836    arg_types = {"this": True, "expression": True, "null": False}
4837    _sql_names = ["STRING_TO_ARRAY", "SPLIT_BY_STRING"]
4838
4839
4840class ArrayOverlaps(Binary, Func):
4841    pass
4842
4843
4844class ArraySize(Func):
4845    arg_types = {"this": True, "expression": False}
4846    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4847
4848
4849class ArraySort(Func):
4850    arg_types = {"this": True, "expression": False}
4851
4852
4853class ArraySum(Func):
4854    arg_types = {"this": True, "expression": False}
4855
4856
4857class ArrayUnionAgg(AggFunc):
4858    pass
4859
4860
4861class Avg(AggFunc):
4862    pass
4863
4864
4865class AnyValue(AggFunc):
4866    pass
4867
4868
4869class Lag(AggFunc):
4870    arg_types = {"this": True, "offset": False, "default": False}
4871
4872
4873class Lead(AggFunc):
4874    arg_types = {"this": True, "offset": False, "default": False}
4875
4876
4877# some dialects have a distinction between first and first_value, usually first is an aggregate func
4878# and first_value is a window func
4879class First(AggFunc):
4880    pass
4881
4882
4883class Last(AggFunc):
4884    pass
4885
4886
4887class FirstValue(AggFunc):
4888    pass
4889
4890
4891class LastValue(AggFunc):
4892    pass
4893
4894
4895class NthValue(AggFunc):
4896    arg_types = {"this": True, "offset": True}
4897
4898
4899class Case(Func):
4900    arg_types = {"this": False, "ifs": True, "default": False}
4901
4902    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4903        instance = maybe_copy(self, copy)
4904        instance.append(
4905            "ifs",
4906            If(
4907                this=maybe_parse(condition, copy=copy, **opts),
4908                true=maybe_parse(then, copy=copy, **opts),
4909            ),
4910        )
4911        return instance
4912
4913    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4914        instance = maybe_copy(self, copy)
4915        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4916        return instance
4917
4918
4919class Cast(Func):
4920    arg_types = {
4921        "this": True,
4922        "to": True,
4923        "format": False,
4924        "safe": False,
4925        "action": False,
4926    }
4927
4928    @property
4929    def name(self) -> str:
4930        return self.this.name
4931
4932    @property
4933    def to(self) -> DataType:
4934        return self.args["to"]
4935
4936    @property
4937    def output_name(self) -> str:
4938        return self.name
4939
4940    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4941        """
4942        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4943        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4944        array<int> != array<float>.
4945
4946        Args:
4947            dtypes: the data types to compare this Cast's DataType to.
4948
4949        Returns:
4950            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4951        """
4952        return self.to.is_type(*dtypes)
4953
4954
4955class TryCast(Cast):
4956    pass
4957
4958
4959class Try(Func):
4960    pass
4961
4962
4963class CastToStrType(Func):
4964    arg_types = {"this": True, "to": True}
4965
4966
4967class Collate(Binary, Func):
4968    pass
4969
4970
4971class Ceil(Func):
4972    arg_types = {"this": True, "decimals": False}
4973    _sql_names = ["CEIL", "CEILING"]
4974
4975
4976class Coalesce(Func):
4977    arg_types = {"this": True, "expressions": False}
4978    is_var_len_args = True
4979    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4980
4981
4982class Chr(Func):
4983    arg_types = {"this": True, "charset": False, "expressions": False}
4984    is_var_len_args = True
4985    _sql_names = ["CHR", "CHAR"]
4986
4987
4988class Concat(Func):
4989    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4990    is_var_len_args = True
4991
4992
4993class ConcatWs(Concat):
4994    _sql_names = ["CONCAT_WS"]
4995
4996
4997# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4998class ConnectByRoot(Func):
4999    pass
5000
5001
5002class Count(AggFunc):
5003    arg_types = {"this": False, "expressions": False}
5004    is_var_len_args = True
5005
5006
5007class CountIf(AggFunc):
5008    _sql_names = ["COUNT_IF", "COUNTIF"]
5009
5010
5011# cube root
5012class Cbrt(Func):
5013    pass
5014
5015
5016class CurrentDate(Func):
5017    arg_types = {"this": False}
5018
5019
5020class CurrentDatetime(Func):
5021    arg_types = {"this": False}
5022
5023
5024class CurrentTime(Func):
5025    arg_types = {"this": False}
5026
5027
5028class CurrentTimestamp(Func):
5029    arg_types = {"this": False, "transaction": False}
5030
5031
5032class CurrentUser(Func):
5033    arg_types = {"this": False}
5034
5035
5036class DateAdd(Func, IntervalOp):
5037    arg_types = {"this": True, "expression": True, "unit": False}
5038
5039
5040class DateSub(Func, IntervalOp):
5041    arg_types = {"this": True, "expression": True, "unit": False}
5042
5043
5044class DateDiff(Func, TimeUnit):
5045    _sql_names = ["DATEDIFF", "DATE_DIFF"]
5046    arg_types = {"this": True, "expression": True, "unit": False}
5047
5048
5049class DateTrunc(Func):
5050    arg_types = {"unit": True, "this": True, "zone": False}
5051
5052    def __init__(self, **args):
5053        unit = args.get("unit")
5054        if isinstance(unit, TimeUnit.VAR_LIKE):
5055            args["unit"] = Literal.string(
5056                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
5057            )
5058        elif isinstance(unit, Week):
5059            unit.set("this", Literal.string(unit.this.name.upper()))
5060
5061        super().__init__(**args)
5062
5063    @property
5064    def unit(self) -> Expression:
5065        return self.args["unit"]
5066
5067
5068class DatetimeAdd(Func, IntervalOp):
5069    arg_types = {"this": True, "expression": True, "unit": False}
5070
5071
5072class DatetimeSub(Func, IntervalOp):
5073    arg_types = {"this": True, "expression": True, "unit": False}
5074
5075
5076class DatetimeDiff(Func, TimeUnit):
5077    arg_types = {"this": True, "expression": True, "unit": False}
5078
5079
5080class DatetimeTrunc(Func, TimeUnit):
5081    arg_types = {"this": True, "unit": True, "zone": False}
5082
5083
5084class DayOfWeek(Func):
5085    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
5086
5087
5088class DayOfMonth(Func):
5089    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
5090
5091
5092class DayOfYear(Func):
5093    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
5094
5095
5096class ToDays(Func):
5097    pass
5098
5099
5100class WeekOfYear(Func):
5101    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
5102
5103
5104class MonthsBetween(Func):
5105    arg_types = {"this": True, "expression": True, "roundoff": False}
5106
5107
5108class LastDay(Func, TimeUnit):
5109    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
5110    arg_types = {"this": True, "unit": False}
5111
5112
5113class Extract(Func):
5114    arg_types = {"this": True, "expression": True}
5115
5116
5117class Timestamp(Func):
5118    arg_types = {"this": False, "expression": False, "with_tz": False}
5119
5120
5121class TimestampAdd(Func, TimeUnit):
5122    arg_types = {"this": True, "expression": True, "unit": False}
5123
5124
5125class TimestampSub(Func, TimeUnit):
5126    arg_types = {"this": True, "expression": True, "unit": False}
5127
5128
5129class TimestampDiff(Func, TimeUnit):
5130    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5131    arg_types = {"this": True, "expression": True, "unit": False}
5132
5133
5134class TimestampTrunc(Func, TimeUnit):
5135    arg_types = {"this": True, "unit": True, "zone": False}
5136
5137
5138class TimeAdd(Func, TimeUnit):
5139    arg_types = {"this": True, "expression": True, "unit": False}
5140
5141
5142class TimeSub(Func, TimeUnit):
5143    arg_types = {"this": True, "expression": True, "unit": False}
5144
5145
5146class TimeDiff(Func, TimeUnit):
5147    arg_types = {"this": True, "expression": True, "unit": False}
5148
5149
5150class TimeTrunc(Func, TimeUnit):
5151    arg_types = {"this": True, "unit": True, "zone": False}
5152
5153
5154class DateFromParts(Func):
5155    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5156    arg_types = {"year": True, "month": True, "day": True}
5157
5158
5159class TimeFromParts(Func):
5160    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5161    arg_types = {
5162        "hour": True,
5163        "min": True,
5164        "sec": True,
5165        "nano": False,
5166        "fractions": False,
5167        "precision": False,
5168    }
5169
5170
5171class DateStrToDate(Func):
5172    pass
5173
5174
5175class DateToDateStr(Func):
5176    pass
5177
5178
5179class DateToDi(Func):
5180    pass
5181
5182
5183# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5184class Date(Func):
5185    arg_types = {"this": False, "zone": False, "expressions": False}
5186    is_var_len_args = True
5187
5188
5189class Day(Func):
5190    pass
5191
5192
5193class Decode(Func):
5194    arg_types = {"this": True, "charset": True, "replace": False}
5195
5196
5197class DiToDate(Func):
5198    pass
5199
5200
5201class Encode(Func):
5202    arg_types = {"this": True, "charset": True}
5203
5204
5205class Exp(Func):
5206    pass
5207
5208
5209# https://docs.snowflake.com/en/sql-reference/functions/flatten
5210class Explode(Func):
5211    arg_types = {"this": True, "expressions": False}
5212    is_var_len_args = True
5213
5214
5215class ExplodeOuter(Explode):
5216    pass
5217
5218
5219class Posexplode(Explode):
5220    pass
5221
5222
5223class PosexplodeOuter(Posexplode, ExplodeOuter):
5224    pass
5225
5226
5227class Unnest(Func, UDTF):
5228    arg_types = {
5229        "expressions": True,
5230        "alias": False,
5231        "offset": False,
5232    }
5233
5234    @property
5235    def selects(self) -> t.List[Expression]:
5236        columns = super().selects
5237        offset = self.args.get("offset")
5238        if offset:
5239            columns = columns + [to_identifier("offset") if offset is True else offset]
5240        return columns
5241
5242
5243class Floor(Func):
5244    arg_types = {"this": True, "decimals": False}
5245
5246
5247class FromBase64(Func):
5248    pass
5249
5250
5251class ToBase64(Func):
5252    pass
5253
5254
5255class GapFill(Func):
5256    arg_types = {
5257        "this": True,
5258        "ts_column": True,
5259        "bucket_width": True,
5260        "partitioning_columns": False,
5261        "value_columns": False,
5262        "origin": False,
5263        "ignore_nulls": False,
5264    }
5265
5266
5267class GenerateDateArray(Func):
5268    arg_types = {"start": True, "end": True, "interval": False}
5269
5270
5271class Greatest(Func):
5272    arg_types = {"this": True, "expressions": False}
5273    is_var_len_args = True
5274
5275
5276class GroupConcat(AggFunc):
5277    arg_types = {"this": True, "separator": False}
5278
5279
5280class Hex(Func):
5281    pass
5282
5283
5284class LowerHex(Hex):
5285    pass
5286
5287
5288class Xor(Connector, Func):
5289    arg_types = {"this": False, "expression": False, "expressions": False}
5290
5291
5292class If(Func):
5293    arg_types = {"this": True, "true": True, "false": False}
5294    _sql_names = ["IF", "IIF"]
5295
5296
5297class Nullif(Func):
5298    arg_types = {"this": True, "expression": True}
5299
5300
5301class Initcap(Func):
5302    arg_types = {"this": True, "expression": False}
5303
5304
5305class IsNan(Func):
5306    _sql_names = ["IS_NAN", "ISNAN"]
5307
5308
5309class IsInf(Func):
5310    _sql_names = ["IS_INF", "ISINF"]
5311
5312
5313class JSONPath(Expression):
5314    arg_types = {"expressions": True}
5315
5316    @property
5317    def output_name(self) -> str:
5318        last_segment = self.expressions[-1].this
5319        return last_segment if isinstance(last_segment, str) else ""
5320
5321
5322class JSONPathPart(Expression):
5323    arg_types = {}
5324
5325
5326class JSONPathFilter(JSONPathPart):
5327    arg_types = {"this": True}
5328
5329
5330class JSONPathKey(JSONPathPart):
5331    arg_types = {"this": True}
5332
5333
5334class JSONPathRecursive(JSONPathPart):
5335    arg_types = {"this": False}
5336
5337
5338class JSONPathRoot(JSONPathPart):
5339    pass
5340
5341
5342class JSONPathScript(JSONPathPart):
5343    arg_types = {"this": True}
5344
5345
5346class JSONPathSlice(JSONPathPart):
5347    arg_types = {"start": False, "end": False, "step": False}
5348
5349
5350class JSONPathSelector(JSONPathPart):
5351    arg_types = {"this": True}
5352
5353
5354class JSONPathSubscript(JSONPathPart):
5355    arg_types = {"this": True}
5356
5357
5358class JSONPathUnion(JSONPathPart):
5359    arg_types = {"expressions": True}
5360
5361
5362class JSONPathWildcard(JSONPathPart):
5363    pass
5364
5365
5366class FormatJson(Expression):
5367    pass
5368
5369
5370class JSONKeyValue(Expression):
5371    arg_types = {"this": True, "expression": True}
5372
5373
5374class JSONObject(Func):
5375    arg_types = {
5376        "expressions": False,
5377        "null_handling": False,
5378        "unique_keys": False,
5379        "return_type": False,
5380        "encoding": False,
5381    }
5382
5383
5384class JSONObjectAgg(AggFunc):
5385    arg_types = {
5386        "expressions": False,
5387        "null_handling": False,
5388        "unique_keys": False,
5389        "return_type": False,
5390        "encoding": False,
5391    }
5392
5393
5394# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5395class JSONArray(Func):
5396    arg_types = {
5397        "expressions": True,
5398        "null_handling": False,
5399        "return_type": False,
5400        "strict": False,
5401    }
5402
5403
5404# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5405class JSONArrayAgg(Func):
5406    arg_types = {
5407        "this": True,
5408        "order": False,
5409        "null_handling": False,
5410        "return_type": False,
5411        "strict": False,
5412    }
5413
5414
5415# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5416# Note: parsing of JSON column definitions is currently incomplete.
5417class JSONColumnDef(Expression):
5418    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5419
5420
5421class JSONSchema(Expression):
5422    arg_types = {"expressions": True}
5423
5424
5425# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5426class JSONTable(Func):
5427    arg_types = {
5428        "this": True,
5429        "schema": True,
5430        "path": False,
5431        "error_handling": False,
5432        "empty_handling": False,
5433    }
5434
5435
5436class OpenJSONColumnDef(Expression):
5437    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5438
5439
5440class OpenJSON(Func):
5441    arg_types = {"this": True, "path": False, "expressions": False}
5442
5443
5444class JSONBContains(Binary):
5445    _sql_names = ["JSONB_CONTAINS"]
5446
5447
5448class JSONExtract(Binary, Func):
5449    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5450    _sql_names = ["JSON_EXTRACT"]
5451    is_var_len_args = True
5452
5453    @property
5454    def output_name(self) -> str:
5455        return self.expression.output_name if not self.expressions else ""
5456
5457
5458class JSONExtractScalar(Binary, Func):
5459    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5460    _sql_names = ["JSON_EXTRACT_SCALAR"]
5461    is_var_len_args = True
5462
5463    @property
5464    def output_name(self) -> str:
5465        return self.expression.output_name
5466
5467
5468class JSONBExtract(Binary, Func):
5469    _sql_names = ["JSONB_EXTRACT"]
5470
5471
5472class JSONBExtractScalar(Binary, Func):
5473    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5474
5475
5476class JSONFormat(Func):
5477    arg_types = {"this": False, "options": False}
5478    _sql_names = ["JSON_FORMAT"]
5479
5480
5481# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5482class JSONArrayContains(Binary, Predicate, Func):
5483    _sql_names = ["JSON_ARRAY_CONTAINS"]
5484
5485
5486class ParseJSON(Func):
5487    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5488    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5489    arg_types = {"this": True, "expressions": False}
5490    is_var_len_args = True
5491
5492
5493class Least(Func):
5494    arg_types = {"this": True, "expressions": False}
5495    is_var_len_args = True
5496
5497
5498class Left(Func):
5499    arg_types = {"this": True, "expression": True}
5500
5501
5502class Right(Func):
5503    arg_types = {"this": True, "expression": True}
5504
5505
5506class Length(Func):
5507    _sql_names = ["LENGTH", "LEN"]
5508
5509
5510class Levenshtein(Func):
5511    arg_types = {
5512        "this": True,
5513        "expression": False,
5514        "ins_cost": False,
5515        "del_cost": False,
5516        "sub_cost": False,
5517    }
5518
5519
5520class Ln(Func):
5521    pass
5522
5523
5524class Log(Func):
5525    arg_types = {"this": True, "expression": False}
5526
5527
5528class LogicalOr(AggFunc):
5529    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5530
5531
5532class LogicalAnd(AggFunc):
5533    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5534
5535
5536class Lower(Func):
5537    _sql_names = ["LOWER", "LCASE"]
5538
5539
5540class Map(Func):
5541    arg_types = {"keys": False, "values": False}
5542
5543    @property
5544    def keys(self) -> t.List[Expression]:
5545        keys = self.args.get("keys")
5546        return keys.expressions if keys else []
5547
5548    @property
5549    def values(self) -> t.List[Expression]:
5550        values = self.args.get("values")
5551        return values.expressions if values else []
5552
5553
5554# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5555class ToMap(Func):
5556    pass
5557
5558
5559class MapFromEntries(Func):
5560    pass
5561
5562
5563class StarMap(Func):
5564    pass
5565
5566
5567class VarMap(Func):
5568    arg_types = {"keys": True, "values": True}
5569    is_var_len_args = True
5570
5571    @property
5572    def keys(self) -> t.List[Expression]:
5573        return self.args["keys"].expressions
5574
5575    @property
5576    def values(self) -> t.List[Expression]:
5577        return self.args["values"].expressions
5578
5579
5580# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5581class MatchAgainst(Func):
5582    arg_types = {"this": True, "expressions": True, "modifier": False}
5583
5584
5585class Max(AggFunc):
5586    arg_types = {"this": True, "expressions": False}
5587    is_var_len_args = True
5588
5589
5590class MD5(Func):
5591    _sql_names = ["MD5"]
5592
5593
5594# Represents the variant of the MD5 function that returns a binary value
5595class MD5Digest(Func):
5596    _sql_names = ["MD5_DIGEST"]
5597
5598
5599class Min(AggFunc):
5600    arg_types = {"this": True, "expressions": False}
5601    is_var_len_args = True
5602
5603
5604class Month(Func):
5605    pass
5606
5607
5608class AddMonths(Func):
5609    arg_types = {"this": True, "expression": True}
5610
5611
5612class Nvl2(Func):
5613    arg_types = {"this": True, "true": True, "false": False}
5614
5615
5616# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5617class Predict(Func):
5618    arg_types = {"this": True, "expression": True, "params_struct": False}
5619
5620
5621class Pow(Binary, Func):
5622    _sql_names = ["POWER", "POW"]
5623
5624
5625class PercentileCont(AggFunc):
5626    arg_types = {"this": True, "expression": False}
5627
5628
5629class PercentileDisc(AggFunc):
5630    arg_types = {"this": True, "expression": False}
5631
5632
5633class Quantile(AggFunc):
5634    arg_types = {"this": True, "quantile": True}
5635
5636
5637class ApproxQuantile(Quantile):
5638    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5639
5640
5641class Quarter(Func):
5642    pass
5643
5644
5645class Rand(Func):
5646    _sql_names = ["RAND", "RANDOM"]
5647    arg_types = {"this": False}
5648
5649
5650class Randn(Func):
5651    arg_types = {"this": False}
5652
5653
5654class RangeN(Func):
5655    arg_types = {"this": True, "expressions": True, "each": False}
5656
5657
5658class ReadCSV(Func):
5659    _sql_names = ["READ_CSV"]
5660    is_var_len_args = True
5661    arg_types = {"this": True, "expressions": False}
5662
5663
5664class Reduce(Func):
5665    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5666
5667
5668class RegexpExtract(Func):
5669    arg_types = {
5670        "this": True,
5671        "expression": True,
5672        "position": False,
5673        "occurrence": False,
5674        "parameters": False,
5675        "group": False,
5676    }
5677
5678
5679class RegexpReplace(Func):
5680    arg_types = {
5681        "this": True,
5682        "expression": True,
5683        "replacement": False,
5684        "position": False,
5685        "occurrence": False,
5686        "modifiers": False,
5687    }
5688
5689
5690class RegexpLike(Binary, Func):
5691    arg_types = {"this": True, "expression": True, "flag": False}
5692
5693
5694class RegexpILike(Binary, Func):
5695    arg_types = {"this": True, "expression": True, "flag": False}
5696
5697
5698# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5699# limit is the number of times a pattern is applied
5700class RegexpSplit(Func):
5701    arg_types = {"this": True, "expression": True, "limit": False}
5702
5703
5704class Repeat(Func):
5705    arg_types = {"this": True, "times": True}
5706
5707
5708# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5709# tsql third argument function == trunctaion if not 0
5710class Round(Func):
5711    arg_types = {"this": True, "decimals": False, "truncate": False}
5712
5713
5714class RowNumber(Func):
5715    arg_types: t.Dict[str, t.Any] = {}
5716
5717
5718class SafeDivide(Func):
5719    arg_types = {"this": True, "expression": True}
5720
5721
5722class SHA(Func):
5723    _sql_names = ["SHA", "SHA1"]
5724
5725
5726class SHA2(Func):
5727    _sql_names = ["SHA2"]
5728    arg_types = {"this": True, "length": False}
5729
5730
5731class Sign(Func):
5732    _sql_names = ["SIGN", "SIGNUM"]
5733
5734
5735class SortArray(Func):
5736    arg_types = {"this": True, "asc": False}
5737
5738
5739class Split(Func):
5740    arg_types = {"this": True, "expression": True, "limit": False}
5741
5742
5743# Start may be omitted in the case of postgres
5744# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5745class Substring(Func):
5746    arg_types = {"this": True, "start": False, "length": False}
5747
5748
5749class StandardHash(Func):
5750    arg_types = {"this": True, "expression": False}
5751
5752
5753class StartsWith(Func):
5754    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5755    arg_types = {"this": True, "expression": True}
5756
5757
5758class StrPosition(Func):
5759    arg_types = {
5760        "this": True,
5761        "substr": True,
5762        "position": False,
5763        "instance": False,
5764    }
5765
5766
5767class StrToDate(Func):
5768    arg_types = {"this": True, "format": False}
5769
5770
5771class StrToTime(Func):
5772    arg_types = {"this": True, "format": True, "zone": False}
5773
5774
5775# Spark allows unix_timestamp()
5776# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5777class StrToUnix(Func):
5778    arg_types = {"this": False, "format": False}
5779
5780
5781# https://prestodb.io/docs/current/functions/string.html
5782# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5783class StrToMap(Func):
5784    arg_types = {
5785        "this": True,
5786        "pair_delim": False,
5787        "key_value_delim": False,
5788        "duplicate_resolution_callback": False,
5789    }
5790
5791
5792class NumberToStr(Func):
5793    arg_types = {"this": True, "format": True, "culture": False}
5794
5795
5796class FromBase(Func):
5797    arg_types = {"this": True, "expression": True}
5798
5799
5800class Struct(Func):
5801    arg_types = {"expressions": False}
5802    is_var_len_args = True
5803
5804
5805class StructExtract(Func):
5806    arg_types = {"this": True, "expression": True}
5807
5808
5809# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5810# https://docs.snowflake.com/en/sql-reference/functions/insert
5811class Stuff(Func):
5812    _sql_names = ["STUFF", "INSERT"]
5813    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5814
5815
5816class Sum(AggFunc):
5817    pass
5818
5819
5820class Sqrt(Func):
5821    pass
5822
5823
5824class Stddev(AggFunc):
5825    pass
5826
5827
5828class StddevPop(AggFunc):
5829    pass
5830
5831
5832class StddevSamp(AggFunc):
5833    pass
5834
5835
5836class TimeToStr(Func):
5837    arg_types = {"this": True, "format": True, "culture": False, "timezone": False}
5838
5839
5840class TimeToTimeStr(Func):
5841    pass
5842
5843
5844class TimeToUnix(Func):
5845    pass
5846
5847
5848class TimeStrToDate(Func):
5849    pass
5850
5851
5852class TimeStrToTime(Func):
5853    pass
5854
5855
5856class TimeStrToUnix(Func):
5857    pass
5858
5859
5860class Trim(Func):
5861    arg_types = {
5862        "this": True,
5863        "expression": False,
5864        "position": False,
5865        "collation": False,
5866    }
5867
5868
5869class TsOrDsAdd(Func, TimeUnit):
5870    # return_type is used to correctly cast the arguments of this expression when transpiling it
5871    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5872
5873    @property
5874    def return_type(self) -> DataType:
5875        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5876
5877
5878class TsOrDsDiff(Func, TimeUnit):
5879    arg_types = {"this": True, "expression": True, "unit": False}
5880
5881
5882class TsOrDsToDateStr(Func):
5883    pass
5884
5885
5886class TsOrDsToDate(Func):
5887    arg_types = {"this": True, "format": False, "safe": False}
5888
5889
5890class TsOrDsToTime(Func):
5891    pass
5892
5893
5894class TsOrDsToTimestamp(Func):
5895    pass
5896
5897
5898class TsOrDiToDi(Func):
5899    pass
5900
5901
5902class Unhex(Func):
5903    pass
5904
5905
5906# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5907class UnixDate(Func):
5908    pass
5909
5910
5911class UnixToStr(Func):
5912    arg_types = {"this": True, "format": False}
5913
5914
5915# https://prestodb.io/docs/current/functions/datetime.html
5916# presto has weird zone/hours/minutes
5917class UnixToTime(Func):
5918    arg_types = {
5919        "this": True,
5920        "scale": False,
5921        "zone": False,
5922        "hours": False,
5923        "minutes": False,
5924        "format": False,
5925    }
5926
5927    SECONDS = Literal.number(0)
5928    DECIS = Literal.number(1)
5929    CENTIS = Literal.number(2)
5930    MILLIS = Literal.number(3)
5931    DECIMILLIS = Literal.number(4)
5932    CENTIMILLIS = Literal.number(5)
5933    MICROS = Literal.number(6)
5934    DECIMICROS = Literal.number(7)
5935    CENTIMICROS = Literal.number(8)
5936    NANOS = Literal.number(9)
5937
5938
5939class UnixToTimeStr(Func):
5940    pass
5941
5942
5943class TimestampFromParts(Func):
5944    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5945    arg_types = {
5946        "year": True,
5947        "month": True,
5948        "day": True,
5949        "hour": True,
5950        "min": True,
5951        "sec": True,
5952        "nano": False,
5953        "zone": False,
5954        "milli": False,
5955    }
5956
5957
5958class Upper(Func):
5959    _sql_names = ["UPPER", "UCASE"]
5960
5961
5962class Corr(Binary, AggFunc):
5963    pass
5964
5965
5966class Variance(AggFunc):
5967    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5968
5969
5970class VariancePop(AggFunc):
5971    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5972
5973
5974class CovarSamp(Binary, AggFunc):
5975    pass
5976
5977
5978class CovarPop(Binary, AggFunc):
5979    pass
5980
5981
5982class Week(Func):
5983    arg_types = {"this": True, "mode": False}
5984
5985
5986class XMLTable(Func):
5987    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5988
5989
5990class Year(Func):
5991    pass
5992
5993
5994class Use(Expression):
5995    arg_types = {"this": True, "kind": False}
5996
5997
5998class Merge(Expression):
5999    arg_types = {
6000        "this": True,
6001        "using": True,
6002        "on": True,
6003        "expressions": True,
6004        "with": False,
6005    }
6006
6007
6008class When(Func):
6009    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
6010
6011
6012# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
6013# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
6014class NextValueFor(Func):
6015    arg_types = {"this": True, "order": False}
6016
6017
6018# Refers to a trailing semi-colon. This is only used to preserve trailing comments
6019# select 1; -- my comment
6020class Semicolon(Expression):
6021    arg_types = {}
6022
6023
6024def _norm_arg(arg):
6025    return arg.lower() if type(arg) is str else arg
6026
6027
6028ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
6029FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
6030
6031JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
6032
6033PERCENTILES = (PercentileCont, PercentileDisc)
6034
6035
6036# Helpers
6037@t.overload
6038def maybe_parse(
6039    sql_or_expression: ExpOrStr,
6040    *,
6041    into: t.Type[E],
6042    dialect: DialectType = None,
6043    prefix: t.Optional[str] = None,
6044    copy: bool = False,
6045    **opts,
6046) -> E: ...
6047
6048
6049@t.overload
6050def maybe_parse(
6051    sql_or_expression: str | E,
6052    *,
6053    into: t.Optional[IntoType] = None,
6054    dialect: DialectType = None,
6055    prefix: t.Optional[str] = None,
6056    copy: bool = False,
6057    **opts,
6058) -> E: ...
6059
6060
6061def maybe_parse(
6062    sql_or_expression: ExpOrStr,
6063    *,
6064    into: t.Optional[IntoType] = None,
6065    dialect: DialectType = None,
6066    prefix: t.Optional[str] = None,
6067    copy: bool = False,
6068    **opts,
6069) -> Expression:
6070    """Gracefully handle a possible string or expression.
6071
6072    Example:
6073        >>> maybe_parse("1")
6074        Literal(this=1, is_string=False)
6075        >>> maybe_parse(to_identifier("x"))
6076        Identifier(this=x, quoted=False)
6077
6078    Args:
6079        sql_or_expression: the SQL code string or an expression
6080        into: the SQLGlot Expression to parse into
6081        dialect: the dialect used to parse the input expressions (in the case that an
6082            input expression is a SQL string).
6083        prefix: a string to prefix the sql with before it gets parsed
6084            (automatically includes a space)
6085        copy: whether to copy the expression.
6086        **opts: other options to use to parse the input expressions (again, in the case
6087            that an input expression is a SQL string).
6088
6089    Returns:
6090        Expression: the parsed or given expression.
6091    """
6092    if isinstance(sql_or_expression, Expression):
6093        if copy:
6094            return sql_or_expression.copy()
6095        return sql_or_expression
6096
6097    if sql_or_expression is None:
6098        raise ParseError("SQL cannot be None")
6099
6100    import sqlglot
6101
6102    sql = str(sql_or_expression)
6103    if prefix:
6104        sql = f"{prefix} {sql}"
6105
6106    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
6107
6108
6109@t.overload
6110def maybe_copy(instance: None, copy: bool = True) -> None: ...
6111
6112
6113@t.overload
6114def maybe_copy(instance: E, copy: bool = True) -> E: ...
6115
6116
6117def maybe_copy(instance, copy=True):
6118    return instance.copy() if copy and instance else instance
6119
6120
6121def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
6122    """Generate a textual representation of an Expression tree"""
6123    indent = "\n" + ("  " * (level + 1))
6124    delim = f",{indent}"
6125
6126    if isinstance(node, Expression):
6127        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
6128
6129        if (node.type or verbose) and not isinstance(node, DataType):
6130            args["_type"] = node.type
6131        if node.comments or verbose:
6132            args["_comments"] = node.comments
6133
6134        if verbose:
6135            args["_id"] = id(node)
6136
6137        # Inline leaves for a more compact representation
6138        if node.is_leaf():
6139            indent = ""
6140            delim = ", "
6141
6142        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
6143        return f"{node.__class__.__name__}({indent}{items})"
6144
6145    if isinstance(node, list):
6146        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
6147        items = f"{indent}{items}" if items else ""
6148        return f"[{items}]"
6149
6150    # Indent multiline strings to match the current level
6151    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
6152
6153
6154def _is_wrong_expression(expression, into):
6155    return isinstance(expression, Expression) and not isinstance(expression, into)
6156
6157
6158def _apply_builder(
6159    expression,
6160    instance,
6161    arg,
6162    copy=True,
6163    prefix=None,
6164    into=None,
6165    dialect=None,
6166    into_arg="this",
6167    **opts,
6168):
6169    if _is_wrong_expression(expression, into):
6170        expression = into(**{into_arg: expression})
6171    instance = maybe_copy(instance, copy)
6172    expression = maybe_parse(
6173        sql_or_expression=expression,
6174        prefix=prefix,
6175        into=into,
6176        dialect=dialect,
6177        **opts,
6178    )
6179    instance.set(arg, expression)
6180    return instance
6181
6182
6183def _apply_child_list_builder(
6184    *expressions,
6185    instance,
6186    arg,
6187    append=True,
6188    copy=True,
6189    prefix=None,
6190    into=None,
6191    dialect=None,
6192    properties=None,
6193    **opts,
6194):
6195    instance = maybe_copy(instance, copy)
6196    parsed = []
6197    properties = {} if properties is None else properties
6198
6199    for expression in expressions:
6200        if expression is not None:
6201            if _is_wrong_expression(expression, into):
6202                expression = into(expressions=[expression])
6203
6204            expression = maybe_parse(
6205                expression,
6206                into=into,
6207                dialect=dialect,
6208                prefix=prefix,
6209                **opts,
6210            )
6211            for k, v in expression.args.items():
6212                if k == "expressions":
6213                    parsed.extend(v)
6214                else:
6215                    properties[k] = v
6216
6217    existing = instance.args.get(arg)
6218    if append and existing:
6219        parsed = existing.expressions + parsed
6220
6221    child = into(expressions=parsed)
6222    for k, v in properties.items():
6223        child.set(k, v)
6224    instance.set(arg, child)
6225
6226    return instance
6227
6228
6229def _apply_list_builder(
6230    *expressions,
6231    instance,
6232    arg,
6233    append=True,
6234    copy=True,
6235    prefix=None,
6236    into=None,
6237    dialect=None,
6238    **opts,
6239):
6240    inst = maybe_copy(instance, copy)
6241
6242    expressions = [
6243        maybe_parse(
6244            sql_or_expression=expression,
6245            into=into,
6246            prefix=prefix,
6247            dialect=dialect,
6248            **opts,
6249        )
6250        for expression in expressions
6251        if expression is not None
6252    ]
6253
6254    existing_expressions = inst.args.get(arg)
6255    if append and existing_expressions:
6256        expressions = existing_expressions + expressions
6257
6258    inst.set(arg, expressions)
6259    return inst
6260
6261
6262def _apply_conjunction_builder(
6263    *expressions,
6264    instance,
6265    arg,
6266    into=None,
6267    append=True,
6268    copy=True,
6269    dialect=None,
6270    **opts,
6271):
6272    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6273    if not expressions:
6274        return instance
6275
6276    inst = maybe_copy(instance, copy)
6277
6278    existing = inst.args.get(arg)
6279    if append and existing is not None:
6280        expressions = [existing.this if into else existing] + list(expressions)
6281
6282    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6283
6284    inst.set(arg, into(this=node) if into else node)
6285    return inst
6286
6287
6288def _apply_cte_builder(
6289    instance: E,
6290    alias: ExpOrStr,
6291    as_: ExpOrStr,
6292    recursive: t.Optional[bool] = None,
6293    append: bool = True,
6294    dialect: DialectType = None,
6295    copy: bool = True,
6296    **opts,
6297) -> E:
6298    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6299    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6300    cte = CTE(this=as_expression, alias=alias_expression)
6301    return _apply_child_list_builder(
6302        cte,
6303        instance=instance,
6304        arg="with",
6305        append=append,
6306        copy=copy,
6307        into=With,
6308        properties={"recursive": recursive or False},
6309    )
6310
6311
6312def _combine(
6313    expressions: t.Sequence[t.Optional[ExpOrStr]],
6314    operator: t.Type[Connector],
6315    dialect: DialectType = None,
6316    copy: bool = True,
6317    **opts,
6318) -> Expression:
6319    conditions = [
6320        condition(expression, dialect=dialect, copy=copy, **opts)
6321        for expression in expressions
6322        if expression is not None
6323    ]
6324
6325    this, *rest = conditions
6326    if rest:
6327        this = _wrap(this, Connector)
6328    for expression in rest:
6329        this = operator(this=this, expression=_wrap(expression, Connector))
6330
6331    return this
6332
6333
6334def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6335    return Paren(this=expression) if isinstance(expression, kind) else expression
6336
6337
6338def union(
6339    left: ExpOrStr,
6340    right: ExpOrStr,
6341    distinct: bool = True,
6342    dialect: DialectType = None,
6343    copy: bool = True,
6344    **opts,
6345) -> Union:
6346    """
6347    Initializes a syntax tree from one UNION expression.
6348
6349    Example:
6350        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6351        'SELECT * FROM foo UNION SELECT * FROM bla'
6352
6353    Args:
6354        left: the SQL code string corresponding to the left-hand side.
6355            If an `Expression` instance is passed, it will be used as-is.
6356        right: the SQL code string corresponding to the right-hand side.
6357            If an `Expression` instance is passed, it will be used as-is.
6358        distinct: set the DISTINCT flag if and only if this is true.
6359        dialect: the dialect used to parse the input expression.
6360        copy: whether to copy the expression.
6361        opts: other options to use to parse the input expressions.
6362
6363    Returns:
6364        The new Union instance.
6365    """
6366    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6367    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6368
6369    return Union(this=left, expression=right, distinct=distinct)
6370
6371
6372def intersect(
6373    left: ExpOrStr,
6374    right: ExpOrStr,
6375    distinct: bool = True,
6376    dialect: DialectType = None,
6377    copy: bool = True,
6378    **opts,
6379) -> Intersect:
6380    """
6381    Initializes a syntax tree from one INTERSECT expression.
6382
6383    Example:
6384        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6385        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6386
6387    Args:
6388        left: the SQL code string corresponding to the left-hand side.
6389            If an `Expression` instance is passed, it will be used as-is.
6390        right: the SQL code string corresponding to the right-hand side.
6391            If an `Expression` instance is passed, it will be used as-is.
6392        distinct: set the DISTINCT flag if and only if this is true.
6393        dialect: the dialect used to parse the input expression.
6394        copy: whether to copy the expression.
6395        opts: other options to use to parse the input expressions.
6396
6397    Returns:
6398        The new Intersect instance.
6399    """
6400    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6401    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6402
6403    return Intersect(this=left, expression=right, distinct=distinct)
6404
6405
6406def except_(
6407    left: ExpOrStr,
6408    right: ExpOrStr,
6409    distinct: bool = True,
6410    dialect: DialectType = None,
6411    copy: bool = True,
6412    **opts,
6413) -> Except:
6414    """
6415    Initializes a syntax tree from one EXCEPT expression.
6416
6417    Example:
6418        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6419        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6420
6421    Args:
6422        left: the SQL code string corresponding to the left-hand side.
6423            If an `Expression` instance is passed, it will be used as-is.
6424        right: the SQL code string corresponding to the right-hand side.
6425            If an `Expression` instance is passed, it will be used as-is.
6426        distinct: set the DISTINCT flag if and only if this is true.
6427        dialect: the dialect used to parse the input expression.
6428        copy: whether to copy the expression.
6429        opts: other options to use to parse the input expressions.
6430
6431    Returns:
6432        The new Except instance.
6433    """
6434    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6435    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6436
6437    return Except(this=left, expression=right, distinct=distinct)
6438
6439
6440def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6441    """
6442    Initializes a syntax tree from one or multiple SELECT expressions.
6443
6444    Example:
6445        >>> select("col1", "col2").from_("tbl").sql()
6446        'SELECT col1, col2 FROM tbl'
6447
6448    Args:
6449        *expressions: the SQL code string to parse as the expressions of a
6450            SELECT statement. If an Expression instance is passed, this is used as-is.
6451        dialect: the dialect used to parse the input expressions (in the case that an
6452            input expression is a SQL string).
6453        **opts: other options to use to parse the input expressions (again, in the case
6454            that an input expression is a SQL string).
6455
6456    Returns:
6457        Select: the syntax tree for the SELECT statement.
6458    """
6459    return Select().select(*expressions, dialect=dialect, **opts)
6460
6461
6462def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6463    """
6464    Initializes a syntax tree from a FROM expression.
6465
6466    Example:
6467        >>> from_("tbl").select("col1", "col2").sql()
6468        'SELECT col1, col2 FROM tbl'
6469
6470    Args:
6471        *expression: the SQL code string to parse as the FROM expressions of a
6472            SELECT statement. If an Expression instance is passed, this is used as-is.
6473        dialect: the dialect used to parse the input expression (in the case that the
6474            input expression is a SQL string).
6475        **opts: other options to use to parse the input expressions (again, in the case
6476            that the input expression is a SQL string).
6477
6478    Returns:
6479        Select: the syntax tree for the SELECT statement.
6480    """
6481    return Select().from_(expression, dialect=dialect, **opts)
6482
6483
6484def update(
6485    table: str | Table,
6486    properties: dict,
6487    where: t.Optional[ExpOrStr] = None,
6488    from_: t.Optional[ExpOrStr] = None,
6489    dialect: DialectType = None,
6490    **opts,
6491) -> Update:
6492    """
6493    Creates an update statement.
6494
6495    Example:
6496        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6497        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6498
6499    Args:
6500        *properties: dictionary of properties to set which are
6501            auto converted to sql objects eg None -> NULL
6502        where: sql conditional parsed into a WHERE statement
6503        from_: sql statement parsed into a FROM statement
6504        dialect: the dialect used to parse the input expressions.
6505        **opts: other options to use to parse the input expressions.
6506
6507    Returns:
6508        Update: the syntax tree for the UPDATE statement.
6509    """
6510    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6511    update_expr.set(
6512        "expressions",
6513        [
6514            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6515            for k, v in properties.items()
6516        ],
6517    )
6518    if from_:
6519        update_expr.set(
6520            "from",
6521            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6522        )
6523    if isinstance(where, Condition):
6524        where = Where(this=where)
6525    if where:
6526        update_expr.set(
6527            "where",
6528            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6529        )
6530    return update_expr
6531
6532
6533def delete(
6534    table: ExpOrStr,
6535    where: t.Optional[ExpOrStr] = None,
6536    returning: t.Optional[ExpOrStr] = None,
6537    dialect: DialectType = None,
6538    **opts,
6539) -> Delete:
6540    """
6541    Builds a delete statement.
6542
6543    Example:
6544        >>> delete("my_table", where="id > 1").sql()
6545        'DELETE FROM my_table WHERE id > 1'
6546
6547    Args:
6548        where: sql conditional parsed into a WHERE statement
6549        returning: sql conditional parsed into a RETURNING statement
6550        dialect: the dialect used to parse the input expressions.
6551        **opts: other options to use to parse the input expressions.
6552
6553    Returns:
6554        Delete: the syntax tree for the DELETE statement.
6555    """
6556    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6557    if where:
6558        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6559    if returning:
6560        delete_expr = t.cast(
6561            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6562        )
6563    return delete_expr
6564
6565
6566def insert(
6567    expression: ExpOrStr,
6568    into: ExpOrStr,
6569    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6570    overwrite: t.Optional[bool] = None,
6571    returning: t.Optional[ExpOrStr] = None,
6572    dialect: DialectType = None,
6573    copy: bool = True,
6574    **opts,
6575) -> Insert:
6576    """
6577    Builds an INSERT statement.
6578
6579    Example:
6580        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6581        'INSERT INTO tbl VALUES (1, 2, 3)'
6582
6583    Args:
6584        expression: the sql string or expression of the INSERT statement
6585        into: the tbl to insert data to.
6586        columns: optionally the table's column names.
6587        overwrite: whether to INSERT OVERWRITE or not.
6588        returning: sql conditional parsed into a RETURNING statement
6589        dialect: the dialect used to parse the input expressions.
6590        copy: whether to copy the expression.
6591        **opts: other options to use to parse the input expressions.
6592
6593    Returns:
6594        Insert: the syntax tree for the INSERT statement.
6595    """
6596    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6597    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6598
6599    if columns:
6600        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6601
6602    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6603
6604    if returning:
6605        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6606
6607    return insert
6608
6609
6610def condition(
6611    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6612) -> Condition:
6613    """
6614    Initialize a logical condition expression.
6615
6616    Example:
6617        >>> condition("x=1").sql()
6618        'x = 1'
6619
6620        This is helpful for composing larger logical syntax trees:
6621        >>> where = condition("x=1")
6622        >>> where = where.and_("y=1")
6623        >>> Select().from_("tbl").select("*").where(where).sql()
6624        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6625
6626    Args:
6627        *expression: the SQL code string to parse.
6628            If an Expression instance is passed, this is used as-is.
6629        dialect: the dialect used to parse the input expression (in the case that the
6630            input expression is a SQL string).
6631        copy: Whether to copy `expression` (only applies to expressions).
6632        **opts: other options to use to parse the input expressions (again, in the case
6633            that the input expression is a SQL string).
6634
6635    Returns:
6636        The new Condition instance
6637    """
6638    return maybe_parse(
6639        expression,
6640        into=Condition,
6641        dialect=dialect,
6642        copy=copy,
6643        **opts,
6644    )
6645
6646
6647def and_(
6648    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6649) -> Condition:
6650    """
6651    Combine multiple conditions with an AND logical operator.
6652
6653    Example:
6654        >>> and_("x=1", and_("y=1", "z=1")).sql()
6655        'x = 1 AND (y = 1 AND z = 1)'
6656
6657    Args:
6658        *expressions: the SQL code strings to parse.
6659            If an Expression instance is passed, this is used as-is.
6660        dialect: the dialect used to parse the input expression.
6661        copy: whether to copy `expressions` (only applies to Expressions).
6662        **opts: other options to use to parse the input expressions.
6663
6664    Returns:
6665        The new condition
6666    """
6667    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6668
6669
6670def or_(
6671    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6672) -> Condition:
6673    """
6674    Combine multiple conditions with an OR logical operator.
6675
6676    Example:
6677        >>> or_("x=1", or_("y=1", "z=1")).sql()
6678        'x = 1 OR (y = 1 OR z = 1)'
6679
6680    Args:
6681        *expressions: the SQL code strings to parse.
6682            If an Expression instance is passed, this is used as-is.
6683        dialect: the dialect used to parse the input expression.
6684        copy: whether to copy `expressions` (only applies to Expressions).
6685        **opts: other options to use to parse the input expressions.
6686
6687    Returns:
6688        The new condition
6689    """
6690    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6691
6692
6693def xor(
6694    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6695) -> Condition:
6696    """
6697    Combine multiple conditions with an XOR logical operator.
6698
6699    Example:
6700        >>> xor("x=1", xor("y=1", "z=1")).sql()
6701        'x = 1 XOR (y = 1 XOR z = 1)'
6702
6703    Args:
6704        *expressions: the SQL code strings to parse.
6705            If an Expression instance is passed, this is used as-is.
6706        dialect: the dialect used to parse the input expression.
6707        copy: whether to copy `expressions` (only applies to Expressions).
6708        **opts: other options to use to parse the input expressions.
6709
6710    Returns:
6711        The new condition
6712    """
6713    return t.cast(Condition, _combine(expressions, Xor, dialect, copy=copy, **opts))
6714
6715
6716def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6717    """
6718    Wrap a condition with a NOT operator.
6719
6720    Example:
6721        >>> not_("this_suit='black'").sql()
6722        "NOT this_suit = 'black'"
6723
6724    Args:
6725        expression: the SQL code string to parse.
6726            If an Expression instance is passed, this is used as-is.
6727        dialect: the dialect used to parse the input expression.
6728        copy: whether to copy the expression or not.
6729        **opts: other options to use to parse the input expressions.
6730
6731    Returns:
6732        The new condition.
6733    """
6734    this = condition(
6735        expression,
6736        dialect=dialect,
6737        copy=copy,
6738        **opts,
6739    )
6740    return Not(this=_wrap(this, Connector))
6741
6742
6743def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6744    """
6745    Wrap an expression in parentheses.
6746
6747    Example:
6748        >>> paren("5 + 3").sql()
6749        '(5 + 3)'
6750
6751    Args:
6752        expression: the SQL code string to parse.
6753            If an Expression instance is passed, this is used as-is.
6754        copy: whether to copy the expression or not.
6755
6756    Returns:
6757        The wrapped expression.
6758    """
6759    return Paren(this=maybe_parse(expression, copy=copy))
6760
6761
6762SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6763
6764
6765@t.overload
6766def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6767
6768
6769@t.overload
6770def to_identifier(
6771    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6772) -> Identifier: ...
6773
6774
6775def to_identifier(name, quoted=None, copy=True):
6776    """Builds an identifier.
6777
6778    Args:
6779        name: The name to turn into an identifier.
6780        quoted: Whether to force quote the identifier.
6781        copy: Whether to copy name if it's an Identifier.
6782
6783    Returns:
6784        The identifier ast node.
6785    """
6786
6787    if name is None:
6788        return None
6789
6790    if isinstance(name, Identifier):
6791        identifier = maybe_copy(name, copy)
6792    elif isinstance(name, str):
6793        identifier = Identifier(
6794            this=name,
6795            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6796        )
6797    else:
6798        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6799    return identifier
6800
6801
6802def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6803    """
6804    Parses a given string into an identifier.
6805
6806    Args:
6807        name: The name to parse into an identifier.
6808        dialect: The dialect to parse against.
6809
6810    Returns:
6811        The identifier ast node.
6812    """
6813    try:
6814        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6815    except ParseError:
6816        expression = to_identifier(name)
6817
6818    return expression
6819
6820
6821INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6822
6823
6824def to_interval(interval: str | Literal) -> Interval:
6825    """Builds an interval expression from a string like '1 day' or '5 months'."""
6826    if isinstance(interval, Literal):
6827        if not interval.is_string:
6828            raise ValueError("Invalid interval string.")
6829
6830        interval = interval.this
6831
6832    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6833
6834    if not interval_parts:
6835        raise ValueError("Invalid interval string.")
6836
6837    return Interval(
6838        this=Literal.string(interval_parts.group(1)),
6839        unit=Var(this=interval_parts.group(2).upper()),
6840    )
6841
6842
6843def to_table(
6844    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
6845) -> Table:
6846    """
6847    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6848    If a table is passed in then that table is returned.
6849
6850    Args:
6851        sql_path: a `[catalog].[schema].[table]` string.
6852        dialect: the source dialect according to which the table name will be parsed.
6853        copy: Whether to copy a table if it is passed in.
6854        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6855
6856    Returns:
6857        A table expression.
6858    """
6859    if isinstance(sql_path, Table):
6860        return maybe_copy(sql_path, copy=copy)
6861
6862    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6863
6864    for k, v in kwargs.items():
6865        table.set(k, v)
6866
6867    return table
6868
6869
6870def to_column(
6871    sql_path: str | Column,
6872    quoted: t.Optional[bool] = None,
6873    dialect: DialectType = None,
6874    copy: bool = True,
6875    **kwargs,
6876) -> Column:
6877    """
6878    Create a column from a `[table].[column]` sql path. Table is optional.
6879    If a column is passed in then that column is returned.
6880
6881    Args:
6882        sql_path: a `[table].[column]` string.
6883        quoted: Whether or not to force quote identifiers.
6884        dialect: the source dialect according to which the column name will be parsed.
6885        copy: Whether to copy a column if it is passed in.
6886        kwargs: the kwargs to instantiate the resulting `Column` expression with.
6887
6888    Returns:
6889        A column expression.
6890    """
6891    if isinstance(sql_path, Column):
6892        return maybe_copy(sql_path, copy=copy)
6893
6894    try:
6895        col = maybe_parse(sql_path, into=Column, dialect=dialect)
6896    except ParseError:
6897        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
6898
6899    for k, v in kwargs.items():
6900        col.set(k, v)
6901
6902    if quoted:
6903        for i in col.find_all(Identifier):
6904            i.set("quoted", True)
6905
6906    return col
6907
6908
6909def alias_(
6910    expression: ExpOrStr,
6911    alias: t.Optional[str | Identifier],
6912    table: bool | t.Sequence[str | Identifier] = False,
6913    quoted: t.Optional[bool] = None,
6914    dialect: DialectType = None,
6915    copy: bool = True,
6916    **opts,
6917):
6918    """Create an Alias expression.
6919
6920    Example:
6921        >>> alias_('foo', 'bar').sql()
6922        'foo AS bar'
6923
6924        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6925        '(SELECT 1, 2) AS bar(a, b)'
6926
6927    Args:
6928        expression: the SQL code strings to parse.
6929            If an Expression instance is passed, this is used as-is.
6930        alias: the alias name to use. If the name has
6931            special characters it is quoted.
6932        table: Whether to create a table alias, can also be a list of columns.
6933        quoted: whether to quote the alias
6934        dialect: the dialect used to parse the input expression.
6935        copy: Whether to copy the expression.
6936        **opts: other options to use to parse the input expressions.
6937
6938    Returns:
6939        Alias: the aliased expression
6940    """
6941    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6942    alias = to_identifier(alias, quoted=quoted)
6943
6944    if table:
6945        table_alias = TableAlias(this=alias)
6946        exp.set("alias", table_alias)
6947
6948        if not isinstance(table, bool):
6949            for column in table:
6950                table_alias.append("columns", to_identifier(column, quoted=quoted))
6951
6952        return exp
6953
6954    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6955    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6956    # for the complete Window expression.
6957    #
6958    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6959
6960    if "alias" in exp.arg_types and not isinstance(exp, Window):
6961        exp.set("alias", alias)
6962        return exp
6963    return Alias(this=exp, alias=alias)
6964
6965
6966def subquery(
6967    expression: ExpOrStr,
6968    alias: t.Optional[Identifier | str] = None,
6969    dialect: DialectType = None,
6970    **opts,
6971) -> Select:
6972    """
6973    Build a subquery expression that's selected from.
6974
6975    Example:
6976        >>> subquery('select x from tbl', 'bar').select('x').sql()
6977        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6978
6979    Args:
6980        expression: the SQL code strings to parse.
6981            If an Expression instance is passed, this is used as-is.
6982        alias: the alias name to use.
6983        dialect: the dialect used to parse the input expression.
6984        **opts: other options to use to parse the input expressions.
6985
6986    Returns:
6987        A new Select instance with the subquery expression included.
6988    """
6989
6990    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
6991    return Select().from_(expression, dialect=dialect, **opts)
6992
6993
6994@t.overload
6995def column(
6996    col: str | Identifier,
6997    table: t.Optional[str | Identifier] = None,
6998    db: t.Optional[str | Identifier] = None,
6999    catalog: t.Optional[str | Identifier] = None,
7000    *,
7001    fields: t.Collection[t.Union[str, Identifier]],
7002    quoted: t.Optional[bool] = None,
7003    copy: bool = True,
7004) -> Dot:
7005    pass
7006
7007
7008@t.overload
7009def column(
7010    col: str | Identifier,
7011    table: t.Optional[str | Identifier] = None,
7012    db: t.Optional[str | Identifier] = None,
7013    catalog: t.Optional[str | Identifier] = None,
7014    *,
7015    fields: Lit[None] = None,
7016    quoted: t.Optional[bool] = None,
7017    copy: bool = True,
7018) -> Column:
7019    pass
7020
7021
7022def column(
7023    col,
7024    table=None,
7025    db=None,
7026    catalog=None,
7027    *,
7028    fields=None,
7029    quoted=None,
7030    copy=True,
7031):
7032    """
7033    Build a Column.
7034
7035    Args:
7036        col: Column name.
7037        table: Table name.
7038        db: Database name.
7039        catalog: Catalog name.
7040        fields: Additional fields using dots.
7041        quoted: Whether to force quotes on the column's identifiers.
7042        copy: Whether to copy identifiers if passed in.
7043
7044    Returns:
7045        The new Column instance.
7046    """
7047    this = Column(
7048        this=to_identifier(col, quoted=quoted, copy=copy),
7049        table=to_identifier(table, quoted=quoted, copy=copy),
7050        db=to_identifier(db, quoted=quoted, copy=copy),
7051        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
7052    )
7053
7054    if fields:
7055        this = Dot.build(
7056            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
7057        )
7058    return this
7059
7060
7061def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
7062    """Cast an expression to a data type.
7063
7064    Example:
7065        >>> cast('x + 1', 'int').sql()
7066        'CAST(x + 1 AS INT)'
7067
7068    Args:
7069        expression: The expression to cast.
7070        to: The datatype to cast to.
7071        copy: Whether to copy the supplied expressions.
7072
7073    Returns:
7074        The new Cast instance.
7075    """
7076    expr = maybe_parse(expression, copy=copy, **opts)
7077    data_type = DataType.build(to, copy=copy, **opts)
7078
7079    if expr.is_type(data_type):
7080        return expr
7081
7082    expr = Cast(this=expr, to=data_type)
7083    expr.type = data_type
7084
7085    return expr
7086
7087
7088def table_(
7089    table: Identifier | str,
7090    db: t.Optional[Identifier | str] = None,
7091    catalog: t.Optional[Identifier | str] = None,
7092    quoted: t.Optional[bool] = None,
7093    alias: t.Optional[Identifier | str] = None,
7094) -> Table:
7095    """Build a Table.
7096
7097    Args:
7098        table: Table name.
7099        db: Database name.
7100        catalog: Catalog name.
7101        quote: Whether to force quotes on the table's identifiers.
7102        alias: Table's alias.
7103
7104    Returns:
7105        The new Table instance.
7106    """
7107    return Table(
7108        this=to_identifier(table, quoted=quoted) if table else None,
7109        db=to_identifier(db, quoted=quoted) if db else None,
7110        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
7111        alias=TableAlias(this=to_identifier(alias)) if alias else None,
7112    )
7113
7114
7115def values(
7116    values: t.Iterable[t.Tuple[t.Any, ...]],
7117    alias: t.Optional[str] = None,
7118    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
7119) -> Values:
7120    """Build VALUES statement.
7121
7122    Example:
7123        >>> values([(1, '2')]).sql()
7124        "VALUES (1, '2')"
7125
7126    Args:
7127        values: values statements that will be converted to SQL
7128        alias: optional alias
7129        columns: Optional list of ordered column names or ordered dictionary of column names to types.
7130         If either are provided then an alias is also required.
7131
7132    Returns:
7133        Values: the Values expression object
7134    """
7135    if columns and not alias:
7136        raise ValueError("Alias is required when providing columns")
7137
7138    return Values(
7139        expressions=[convert(tup) for tup in values],
7140        alias=(
7141            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
7142            if columns
7143            else (TableAlias(this=to_identifier(alias)) if alias else None)
7144        ),
7145    )
7146
7147
7148def var(name: t.Optional[ExpOrStr]) -> Var:
7149    """Build a SQL variable.
7150
7151    Example:
7152        >>> repr(var('x'))
7153        'Var(this=x)'
7154
7155        >>> repr(var(column('x', table='y')))
7156        'Var(this=x)'
7157
7158    Args:
7159        name: The name of the var or an expression who's name will become the var.
7160
7161    Returns:
7162        The new variable node.
7163    """
7164    if not name:
7165        raise ValueError("Cannot convert empty name into var.")
7166
7167    if isinstance(name, Expression):
7168        name = name.name
7169    return Var(this=name)
7170
7171
7172def rename_table(
7173    old_name: str | Table,
7174    new_name: str | Table,
7175    dialect: DialectType = None,
7176) -> AlterTable:
7177    """Build ALTER TABLE... RENAME... expression
7178
7179    Args:
7180        old_name: The old name of the table
7181        new_name: The new name of the table
7182        dialect: The dialect to parse the table.
7183
7184    Returns:
7185        Alter table expression
7186    """
7187    old_table = to_table(old_name, dialect=dialect)
7188    new_table = to_table(new_name, dialect=dialect)
7189    return AlterTable(
7190        this=old_table,
7191        actions=[
7192            RenameTable(this=new_table),
7193        ],
7194    )
7195
7196
7197def rename_column(
7198    table_name: str | Table,
7199    old_column_name: str | Column,
7200    new_column_name: str | Column,
7201    exists: t.Optional[bool] = None,
7202    dialect: DialectType = None,
7203) -> AlterTable:
7204    """Build ALTER TABLE... RENAME COLUMN... expression
7205
7206    Args:
7207        table_name: Name of the table
7208        old_column: The old name of the column
7209        new_column: The new name of the column
7210        exists: Whether to add the `IF EXISTS` clause
7211        dialect: The dialect to parse the table/column.
7212
7213    Returns:
7214        Alter table expression
7215    """
7216    table = to_table(table_name, dialect=dialect)
7217    old_column = to_column(old_column_name, dialect=dialect)
7218    new_column = to_column(new_column_name, dialect=dialect)
7219    return AlterTable(
7220        this=table,
7221        actions=[
7222            RenameColumn(this=old_column, to=new_column, exists=exists),
7223        ],
7224    )
7225
7226
7227def convert(value: t.Any, copy: bool = False) -> Expression:
7228    """Convert a python value into an expression object.
7229
7230    Raises an error if a conversion is not possible.
7231
7232    Args:
7233        value: A python object.
7234        copy: Whether to copy `value` (only applies to Expressions and collections).
7235
7236    Returns:
7237        The equivalent expression object.
7238    """
7239    if isinstance(value, Expression):
7240        return maybe_copy(value, copy)
7241    if isinstance(value, str):
7242        return Literal.string(value)
7243    if isinstance(value, bool):
7244        return Boolean(this=value)
7245    if value is None or (isinstance(value, float) and math.isnan(value)):
7246        return null()
7247    if isinstance(value, numbers.Number):
7248        return Literal.number(value)
7249    if isinstance(value, bytes):
7250        return HexString(this=value.hex())
7251    if isinstance(value, datetime.datetime):
7252        datetime_literal = Literal.string(
7253            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat(
7254                sep=" "
7255            )
7256        )
7257        return TimeStrToTime(this=datetime_literal)
7258    if isinstance(value, datetime.date):
7259        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7260        return DateStrToDate(this=date_literal)
7261    if isinstance(value, tuple):
7262        if hasattr(value, "_fields"):
7263            return Struct(
7264                expressions=[
7265                    PropertyEQ(
7266                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7267                    )
7268                    for k in value._fields
7269                ]
7270            )
7271        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7272    if isinstance(value, list):
7273        return Array(expressions=[convert(v, copy=copy) for v in value])
7274    if isinstance(value, dict):
7275        return Map(
7276            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7277            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7278        )
7279    if hasattr(value, "__dict__"):
7280        return Struct(
7281            expressions=[
7282                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7283                for k, v in value.__dict__.items()
7284            ]
7285        )
7286    raise ValueError(f"Cannot convert {value}")
7287
7288
7289def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7290    """
7291    Replace children of an expression with the result of a lambda fun(child) -> exp.
7292    """
7293    for k, v in tuple(expression.args.items()):
7294        is_list_arg = type(v) is list
7295
7296        child_nodes = v if is_list_arg else [v]
7297        new_child_nodes = []
7298
7299        for cn in child_nodes:
7300            if isinstance(cn, Expression):
7301                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7302                    new_child_nodes.append(child_node)
7303            else:
7304                new_child_nodes.append(cn)
7305
7306        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7307
7308
7309def replace_tree(
7310    expression: Expression,
7311    fun: t.Callable,
7312    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7313) -> Expression:
7314    """
7315    Replace an entire tree with the result of function calls on each node.
7316
7317    This will be traversed in reverse dfs, so leaves first.
7318    If new nodes are created as a result of function calls, they will also be traversed.
7319    """
7320    stack = list(expression.dfs(prune=prune))
7321
7322    while stack:
7323        node = stack.pop()
7324        new_node = fun(node)
7325
7326        if new_node is not node:
7327            node.replace(new_node)
7328
7329            if isinstance(new_node, Expression):
7330                stack.append(new_node)
7331
7332    return new_node
7333
7334
7335def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7336    """
7337    Return all table names referenced through columns in an expression.
7338
7339    Example:
7340        >>> import sqlglot
7341        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7342        ['a', 'c']
7343
7344    Args:
7345        expression: expression to find table names.
7346        exclude: a table name to exclude
7347
7348    Returns:
7349        A list of unique names.
7350    """
7351    return {
7352        table
7353        for table in (column.table for column in expression.find_all(Column))
7354        if table and table != exclude
7355    }
7356
7357
7358def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7359    """Get the full name of a table as a string.
7360
7361    Args:
7362        table: Table expression node or string.
7363        dialect: The dialect to generate the table name for.
7364        identify: Determines when an identifier should be quoted. Possible values are:
7365            False (default): Never quote, except in cases where it's mandatory by the dialect.
7366            True: Always quote.
7367
7368    Examples:
7369        >>> from sqlglot import exp, parse_one
7370        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7371        'a.b.c'
7372
7373    Returns:
7374        The table name.
7375    """
7376
7377    table = maybe_parse(table, into=Table, dialect=dialect)
7378
7379    if not table:
7380        raise ValueError(f"Cannot parse {table}")
7381
7382    return ".".join(
7383        (
7384            part.sql(dialect=dialect, identify=True, copy=False)
7385            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7386            else part.name
7387        )
7388        for part in table.parts
7389    )
7390
7391
7392def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7393    """Returns a case normalized table name without quotes.
7394
7395    Args:
7396        table: the table to normalize
7397        dialect: the dialect to use for normalization rules
7398        copy: whether to copy the expression.
7399
7400    Examples:
7401        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7402        'A-B.c'
7403    """
7404    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7405
7406    return ".".join(
7407        p.name
7408        for p in normalize_identifiers(
7409            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7410        ).parts
7411    )
7412
7413
7414def replace_tables(
7415    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7416) -> E:
7417    """Replace all tables in expression according to the mapping.
7418
7419    Args:
7420        expression: expression node to be transformed and replaced.
7421        mapping: mapping of table names.
7422        dialect: the dialect of the mapping table
7423        copy: whether to copy the expression.
7424
7425    Examples:
7426        >>> from sqlglot import exp, parse_one
7427        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7428        'SELECT * FROM c /* a.b */'
7429
7430    Returns:
7431        The mapped expression.
7432    """
7433
7434    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7435
7436    def _replace_tables(node: Expression) -> Expression:
7437        if isinstance(node, Table):
7438            original = normalize_table_name(node, dialect=dialect)
7439            new_name = mapping.get(original)
7440
7441            if new_name:
7442                table = to_table(
7443                    new_name,
7444                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7445                    dialect=dialect,
7446                )
7447                table.add_comments([original])
7448                return table
7449        return node
7450
7451    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7452
7453
7454def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7455    """Replace placeholders in an expression.
7456
7457    Args:
7458        expression: expression node to be transformed and replaced.
7459        args: positional names that will substitute unnamed placeholders in the given order.
7460        kwargs: keyword arguments that will substitute named placeholders.
7461
7462    Examples:
7463        >>> from sqlglot import exp, parse_one
7464        >>> replace_placeholders(
7465        ...     parse_one("select * from :tbl where ? = ?"),
7466        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7467        ... ).sql()
7468        "SELECT * FROM foo WHERE str_col = 'b'"
7469
7470    Returns:
7471        The mapped expression.
7472    """
7473
7474    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7475        if isinstance(node, Placeholder):
7476            if node.this:
7477                new_name = kwargs.get(node.this)
7478                if new_name is not None:
7479                    return convert(new_name)
7480            else:
7481                try:
7482                    return convert(next(args))
7483                except StopIteration:
7484                    pass
7485        return node
7486
7487    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7488
7489
7490def expand(
7491    expression: Expression,
7492    sources: t.Dict[str, Query],
7493    dialect: DialectType = None,
7494    copy: bool = True,
7495) -> Expression:
7496    """Transforms an expression by expanding all referenced sources into subqueries.
7497
7498    Examples:
7499        >>> from sqlglot import parse_one
7500        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7501        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7502
7503        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7504        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7505
7506    Args:
7507        expression: The expression to expand.
7508        sources: A dictionary of name to Queries.
7509        dialect: The dialect of the sources dict.
7510        copy: Whether to copy the expression during transformation. Defaults to True.
7511
7512    Returns:
7513        The transformed expression.
7514    """
7515    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7516
7517    def _expand(node: Expression):
7518        if isinstance(node, Table):
7519            name = normalize_table_name(node, dialect=dialect)
7520            source = sources.get(name)
7521            if source:
7522                subquery = source.subquery(node.alias or name)
7523                subquery.comments = [f"source: {name}"]
7524                return subquery.transform(_expand, copy=False)
7525        return node
7526
7527    return expression.transform(_expand, copy=copy)
7528
7529
7530def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7531    """
7532    Returns a Func expression.
7533
7534    Examples:
7535        >>> func("abs", 5).sql()
7536        'ABS(5)'
7537
7538        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7539        'CAST(5 AS DOUBLE)'
7540
7541    Args:
7542        name: the name of the function to build.
7543        args: the args used to instantiate the function of interest.
7544        copy: whether to copy the argument expressions.
7545        dialect: the source dialect.
7546        kwargs: the kwargs used to instantiate the function of interest.
7547
7548    Note:
7549        The arguments `args` and `kwargs` are mutually exclusive.
7550
7551    Returns:
7552        An instance of the function of interest, or an anonymous function, if `name` doesn't
7553        correspond to an existing `sqlglot.expressions.Func` class.
7554    """
7555    if args and kwargs:
7556        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7557
7558    from sqlglot.dialects.dialect import Dialect
7559
7560    dialect = Dialect.get_or_raise(dialect)
7561
7562    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7563    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7564
7565    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7566    if constructor:
7567        if converted:
7568            if "dialect" in constructor.__code__.co_varnames:
7569                function = constructor(converted, dialect=dialect)
7570            else:
7571                function = constructor(converted)
7572        elif constructor.__name__ == "from_arg_list":
7573            function = constructor.__self__(**kwargs)  # type: ignore
7574        else:
7575            constructor = FUNCTION_BY_NAME.get(name.upper())
7576            if constructor:
7577                function = constructor(**kwargs)
7578            else:
7579                raise ValueError(
7580                    f"Unable to convert '{name}' into a Func. Either manually construct "
7581                    "the Func expression of interest or parse the function call."
7582                )
7583    else:
7584        kwargs = kwargs or {"expressions": converted}
7585        function = Anonymous(this=name, **kwargs)
7586
7587    for error_message in function.error_messages(converted):
7588        raise ValueError(error_message)
7589
7590    return function
7591
7592
7593def case(
7594    expression: t.Optional[ExpOrStr] = None,
7595    **opts,
7596) -> Case:
7597    """
7598    Initialize a CASE statement.
7599
7600    Example:
7601        case().when("a = 1", "foo").else_("bar")
7602
7603    Args:
7604        expression: Optionally, the input expression (not all dialects support this)
7605        **opts: Extra keyword arguments for parsing `expression`
7606    """
7607    if expression is not None:
7608        this = maybe_parse(expression, **opts)
7609    else:
7610        this = None
7611    return Case(this=this, ifs=[])
7612
7613
7614def array(
7615    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7616) -> Array:
7617    """
7618    Returns an array.
7619
7620    Examples:
7621        >>> array(1, 'x').sql()
7622        'ARRAY(1, x)'
7623
7624    Args:
7625        expressions: the expressions to add to the array.
7626        copy: whether to copy the argument expressions.
7627        dialect: the source dialect.
7628        kwargs: the kwargs used to instantiate the function of interest.
7629
7630    Returns:
7631        An array expression.
7632    """
7633    return Array(
7634        expressions=[
7635            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7636            for expression in expressions
7637        ]
7638    )
7639
7640
7641def tuple_(
7642    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7643) -> Tuple:
7644    """
7645    Returns an tuple.
7646
7647    Examples:
7648        >>> tuple_(1, 'x').sql()
7649        '(1, x)'
7650
7651    Args:
7652        expressions: the expressions to add to the tuple.
7653        copy: whether to copy the argument expressions.
7654        dialect: the source dialect.
7655        kwargs: the kwargs used to instantiate the function of interest.
7656
7657    Returns:
7658        A tuple expression.
7659    """
7660    return Tuple(
7661        expressions=[
7662            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7663            for expression in expressions
7664        ]
7665    )
7666
7667
7668def true() -> Boolean:
7669    """
7670    Returns a true Boolean expression.
7671    """
7672    return Boolean(this=True)
7673
7674
7675def false() -> Boolean:
7676    """
7677    Returns a false Boolean expression.
7678    """
7679    return Boolean(this=False)
7680
7681
7682def null() -> Null:
7683    """
7684    Returns a Null expression.
7685    """
7686    return Null()
7687
7688
7689NONNULL_CONSTANTS = (
7690    Literal,
7691    Boolean,
7692)
7693
7694CONSTANTS = (
7695    Literal,
7696    Boolean,
7697    Null,
7698)