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