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 typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    ensure_list,
  30    seq_get,
  31    split_num_words,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39E = t.TypeVar("E", bound="Expression")
  40
  41
  42class _Expression(type):
  43    def __new__(cls, clsname, bases, attrs):
  44        klass = super().__new__(cls, clsname, bases, attrs)
  45
  46        # When an Expression class is created, its key is automatically set to be
  47        # the lowercase version of the class' name.
  48        klass.key = clsname.lower()
  49
  50        # This is so that docstrings are not inherited in pdoc
  51        klass.__doc__ = klass.__doc__ or ""
  52
  53        return klass
  54
  55
  56class Expression(metaclass=_Expression):
  57    """
  58    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  59    context, such as its child expressions, their names (arg keys), and whether a given child expression
  60    is optional or not.
  61
  62    Attributes:
  63        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  64            and representing expressions as strings.
  65        arg_types: determines what arguments (child nodes) are supported by an expression. It
  66            maps arg keys to booleans that indicate whether the corresponding args are optional.
  67
  68    Example:
  69        >>> class Foo(Expression):
  70        ...     arg_types = {"this": True, "expression": False}
  71
  72        The above definition informs us that Foo is an Expression that requires an argument called
  73        "this" and may also optionally receive an argument called "expression".
  74
  75    Args:
  76        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  77        parent: a reference to the parent expression (or None, in case of root expressions).
  78        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  79            uses to refer to it.
  80        comments: a list of comments that are associated with a given expression. This is used in
  81            order to preserve comments when transpiling SQL code.
  82        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  83            optimizer, in order to enable some transformations that require type information.
  84    """
  85
  86    key = "expression"
  87    arg_types = {"this": True}
  88    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  89
  90    def __init__(self, **args: t.Any):
  91        self.args: t.Dict[str, t.Any] = args
  92        self.parent: t.Optional[Expression] = None
  93        self.arg_key: t.Optional[str] = None
  94        self.comments: t.Optional[t.List[str]] = None
  95        self._type: t.Optional[DataType] = None
  96        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  97        self._hash: t.Optional[int] = None
  98
  99        for arg_key, value in self.args.items():
 100            self._set_parent(arg_key, value)
 101
 102    def __eq__(self, other) -> bool:
 103        return type(self) is type(other) and hash(self) == hash(other)
 104
 105    @property
 106    def hashable_args(self) -> t.Any:
 107        args = (self.args.get(k) for k in self.arg_types)
 108
 109        return tuple(
 110            (tuple(_norm_arg(a) for a in arg) if arg else None)
 111            if type(arg) is list
 112            else (_norm_arg(arg) if arg is not None and arg is not False else None)
 113            for arg in args
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self):
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self):
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def name(self) -> str:
 200        return self.text("this")
 201
 202    @property
 203    def alias_or_name(self):
 204        return self.alias or self.name
 205
 206    @property
 207    def output_name(self):
 208        """
 209        Name of the output column if this expression is a selection.
 210
 211        If the Expression has no output name, an empty string is returned.
 212
 213        Example:
 214            >>> from sqlglot import parse_one
 215            >>> parse_one("SELECT a").expressions[0].output_name
 216            'a'
 217            >>> parse_one("SELECT b AS c").expressions[0].output_name
 218            'c'
 219            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 220            ''
 221        """
 222        return ""
 223
 224    @property
 225    def type(self) -> t.Optional[DataType]:
 226        return self._type
 227
 228    @type.setter
 229    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 230        if dtype and not isinstance(dtype, DataType):
 231            dtype = DataType.build(dtype)
 232        self._type = dtype  # type: ignore
 233
 234    @property
 235    def meta(self) -> t.Dict[str, t.Any]:
 236        if self._meta is None:
 237            self._meta = {}
 238        return self._meta
 239
 240    def __deepcopy__(self, memo):
 241        copy = self.__class__(**deepcopy(self.args))
 242        if self.comments is not None:
 243            copy.comments = deepcopy(self.comments)
 244
 245        if self._type is not None:
 246            copy._type = self._type.copy()
 247
 248        if self._meta is not None:
 249            copy._meta = deepcopy(self._meta)
 250
 251        return copy
 252
 253    def copy(self):
 254        """
 255        Returns a deep copy of the expression.
 256        """
 257        new = deepcopy(self)
 258        new.parent = self.parent
 259        return new
 260
 261    def append(self, arg_key, value):
 262        """
 263        Appends value to arg_key if it's a list or sets it as a new list.
 264
 265        Args:
 266            arg_key (str): name of the list expression arg
 267            value (Any): value to append to the list
 268        """
 269        if not isinstance(self.args.get(arg_key), list):
 270            self.args[arg_key] = []
 271        self.args[arg_key].append(value)
 272        self._set_parent(arg_key, value)
 273
 274    def set(self, arg_key, value):
 275        """
 276        Sets `arg_key` to `value`.
 277
 278        Args:
 279            arg_key (str): name of the expression arg.
 280            value: value to set the arg to.
 281        """
 282        self.args[arg_key] = value
 283        self._set_parent(arg_key, value)
 284
 285    def _set_parent(self, arg_key, value):
 286        if hasattr(value, "parent"):
 287            value.parent = self
 288            value.arg_key = arg_key
 289        elif type(value) is list:
 290            for v in value:
 291                if hasattr(v, "parent"):
 292                    v.parent = self
 293                    v.arg_key = arg_key
 294
 295    @property
 296    def depth(self):
 297        """
 298        Returns the depth of this tree.
 299        """
 300        if self.parent:
 301            return self.parent.depth + 1
 302        return 0
 303
 304    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 305        """Yields the key and expression for all arguments, exploding list args."""
 306        for k, vs in self.args.items():
 307            if type(vs) is list:
 308                for v in vs:
 309                    if hasattr(v, "parent"):
 310                        yield k, v
 311            else:
 312                if hasattr(vs, "parent"):
 313                    yield k, vs
 314
 315    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 316        """
 317        Returns the first node in this tree which matches at least one of
 318        the specified types.
 319
 320        Args:
 321            expression_types: the expression type(s) to match.
 322
 323        Returns:
 324            The node which matches the criteria or None if no such node was found.
 325        """
 326        return next(self.find_all(*expression_types, bfs=bfs), None)
 327
 328    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 329        """
 330        Returns a generator object which visits all nodes in this tree and only
 331        yields those that match at least one of the specified expression types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335
 336        Returns:
 337            The generator object.
 338        """
 339        for expression, *_ in self.walk(bfs=bfs):
 340            if isinstance(expression, expression_types):
 341                yield expression
 342
 343    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 344        """
 345        Returns a nearest parent matching expression_types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349
 350        Returns:
 351            The parent node.
 352        """
 353        ancestor = self.parent
 354        while ancestor and not isinstance(ancestor, expression_types):
 355            ancestor = ancestor.parent
 356        return t.cast(E, ancestor)
 357
 358    @property
 359    def parent_select(self):
 360        """
 361        Returns the parent select statement.
 362        """
 363        return self.find_ancestor(Select)
 364
 365    @property
 366    def same_parent(self):
 367        """Returns if the parent is the same class as itself."""
 368        return type(self.parent) is self.__class__
 369
 370    def root(self) -> Expression:
 371        """
 372        Returns the root expression of this tree.
 373        """
 374        expression = self
 375        while expression.parent:
 376            expression = expression.parent
 377        return expression
 378
 379    def walk(self, bfs=True, prune=None):
 380        """
 381        Returns a generator object which visits all nodes in this tree.
 382
 383        Args:
 384            bfs (bool): if set to True the BFS traversal order will be applied,
 385                otherwise the DFS traversal will be used instead.
 386            prune ((node, parent, arg_key) -> bool): callable that returns True if
 387                the generator should stop traversing this branch of the tree.
 388
 389        Returns:
 390            the generator object.
 391        """
 392        if bfs:
 393            yield from self.bfs(prune=prune)
 394        else:
 395            yield from self.dfs(prune=prune)
 396
 397    def dfs(self, parent=None, key=None, prune=None):
 398        """
 399        Returns a generator object which visits all nodes in this tree in
 400        the DFS (Depth-first) order.
 401
 402        Returns:
 403            The generator object.
 404        """
 405        parent = parent or self.parent
 406        yield self, parent, key
 407        if prune and prune(self, parent, key):
 408            return
 409
 410        for k, v in self.iter_expressions():
 411            yield from v.dfs(self, k, prune)
 412
 413    def bfs(self, prune=None):
 414        """
 415        Returns a generator object which visits all nodes in this tree in
 416        the BFS (Breadth-first) order.
 417
 418        Returns:
 419            The generator object.
 420        """
 421        queue = deque([(self, self.parent, None)])
 422
 423        while queue:
 424            item, parent, key = queue.popleft()
 425
 426            yield item, parent, key
 427            if prune and prune(item, parent, key):
 428                continue
 429
 430            for k, v in item.iter_expressions():
 431                queue.append((v, item, k))
 432
 433    def unnest(self):
 434        """
 435        Returns the first non parenthesis child or self.
 436        """
 437        expression = self
 438        while type(expression) is Paren:
 439            expression = expression.this
 440        return expression
 441
 442    def unalias(self):
 443        """
 444        Returns the inner expression if this is an Alias.
 445        """
 446        if isinstance(self, Alias):
 447            return self.this
 448        return self
 449
 450    def unnest_operands(self):
 451        """
 452        Returns unnested operands as a tuple.
 453        """
 454        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 455
 456    def flatten(self, unnest=True):
 457        """
 458        Returns a generator which yields child nodes who's parents are the same class.
 459
 460        A AND B AND C -> [A, B, C]
 461        """
 462        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 463            if not type(node) is self.__class__:
 464                yield node.unnest() if unnest else node
 465
 466    def __str__(self):
 467        return self.sql()
 468
 469    def __repr__(self):
 470        return self._to_s()
 471
 472    def sql(self, dialect: DialectType = None, **opts) -> str:
 473        """
 474        Returns SQL string representation of this tree.
 475
 476        Args:
 477            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 478            opts: other `sqlglot.generator.Generator` options.
 479
 480        Returns:
 481            The SQL string.
 482        """
 483        from sqlglot.dialects import Dialect
 484
 485        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 486
 487    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 488        indent = "" if not level else "\n"
 489        indent += "".join(["  "] * level)
 490        left = f"({self.key.upper()} "
 491
 492        args: t.Dict[str, t.Any] = {
 493            k: ", ".join(
 494                v._to_s(hide_missing=hide_missing, level=level + 1)
 495                if hasattr(v, "_to_s")
 496                else str(v)
 497                for v in ensure_list(vs)
 498                if v is not None
 499            )
 500            for k, vs in self.args.items()
 501        }
 502        args["comments"] = self.comments
 503        args["type"] = self.type
 504        args = {k: v for k, v in args.items() if v or not hide_missing}
 505
 506        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 507        right += ")"
 508
 509        return indent + left + right
 510
 511    def transform(self, fun, *args, copy=True, **kwargs):
 512        """
 513        Recursively visits all tree nodes (excluding already transformed ones)
 514        and applies the given transformation function to each node.
 515
 516        Args:
 517            fun (function): a function which takes a node as an argument and returns a
 518                new transformed node or the same node without modifications. If the function
 519                returns None, then the corresponding node will be removed from the syntax tree.
 520            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 521                modified in place.
 522
 523        Returns:
 524            The transformed tree.
 525        """
 526        node = self.copy() if copy else self
 527        new_node = fun(node, *args, **kwargs)
 528
 529        if new_node is None or not isinstance(new_node, Expression):
 530            return new_node
 531        if new_node is not node:
 532            new_node.parent = node.parent
 533            return new_node
 534
 535        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 536        return new_node
 537
 538    def replace(self, expression):
 539        """
 540        Swap out this expression with a new expression.
 541
 542        For example::
 543
 544            >>> tree = Select().select("x").from_("tbl")
 545            >>> tree.find(Column).replace(Column(this="y"))
 546            (COLUMN this: y)
 547            >>> tree.sql()
 548            'SELECT y FROM tbl'
 549
 550        Args:
 551            expression (Expression|None): new node
 552
 553        Returns:
 554            The new expression or expressions.
 555        """
 556        if not self.parent:
 557            return expression
 558
 559        parent = self.parent
 560        self.parent = None
 561
 562        replace_children(parent, lambda child: expression if child is self else child)
 563        return expression
 564
 565    def pop(self):
 566        """
 567        Remove this expression from its AST.
 568
 569        Returns:
 570            The popped expression.
 571        """
 572        self.replace(None)
 573        return self
 574
 575    def assert_is(self, type_):
 576        """
 577        Assert that this `Expression` is an instance of `type_`.
 578
 579        If it is NOT an instance of `type_`, this raises an assertion error.
 580        Otherwise, this returns this expression.
 581
 582        Examples:
 583            This is useful for type security in chained expressions:
 584
 585            >>> import sqlglot
 586            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 587            'SELECT x, z FROM y'
 588        """
 589        assert isinstance(self, type_)
 590        return self
 591
 592    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 593        """
 594        Checks if this expression is valid (e.g. all mandatory args are set).
 595
 596        Args:
 597            args: a sequence of values that were used to instantiate a Func expression. This is used
 598                to check that the provided arguments don't exceed the function argument limit.
 599
 600        Returns:
 601            A list of error messages for all possible errors that were found.
 602        """
 603        errors: t.List[str] = []
 604
 605        for k in self.args:
 606            if k not in self.arg_types:
 607                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 608        for k, mandatory in self.arg_types.items():
 609            v = self.args.get(k)
 610            if mandatory and (v is None or (isinstance(v, list) and not v)):
 611                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 612
 613        if (
 614            args
 615            and isinstance(self, Func)
 616            and len(args) > len(self.arg_types)
 617            and not self.is_var_len_args
 618        ):
 619            errors.append(
 620                f"The number of provided arguments ({len(args)}) is greater than "
 621                f"the maximum number of supported arguments ({len(self.arg_types)})"
 622            )
 623
 624        return errors
 625
 626    def dump(self):
 627        """
 628        Dump this Expression to a JSON-serializable dict.
 629        """
 630        from sqlglot.serde import dump
 631
 632        return dump(self)
 633
 634    @classmethod
 635    def load(cls, obj):
 636        """
 637        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 638        """
 639        from sqlglot.serde import load
 640
 641        return load(obj)
 642
 643
 644IntoType = t.Union[
 645    str,
 646    t.Type[Expression],
 647    t.Collection[t.Union[str, t.Type[Expression]]],
 648]
 649ExpOrStr = t.Union[str, Expression]
 650
 651
 652class Condition(Expression):
 653    def and_(self, *expressions, dialect=None, copy=True, **opts):
 654        """
 655        AND this condition with one or multiple expressions.
 656
 657        Example:
 658            >>> condition("x=1").and_("y=1").sql()
 659            'x = 1 AND y = 1'
 660
 661        Args:
 662            *expressions (str | Expression): the SQL code strings to parse.
 663                If an `Expression` instance is passed, it will be used as-is.
 664            dialect (str): the dialect used to parse the input expression.
 665            copy (bool): whether or not to copy the involved expressions (only applies to Expressions).
 666            opts (kwargs): other options to use to parse the input expressions.
 667
 668        Returns:
 669            And: the new condition.
 670        """
 671        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 672
 673    def or_(self, *expressions, dialect=None, copy=True, **opts):
 674        """
 675        OR this condition with one or multiple expressions.
 676
 677        Example:
 678            >>> condition("x=1").or_("y=1").sql()
 679            'x = 1 OR y = 1'
 680
 681        Args:
 682            *expressions (str | Expression): the SQL code strings to parse.
 683                If an `Expression` instance is passed, it will be used as-is.
 684            dialect (str): the dialect used to parse the input expression.
 685            copy (bool): whether or not to copy the involved expressions (only applies to Expressions).
 686            opts (kwargs): other options to use to parse the input expressions.
 687
 688        Returns:
 689            Or: the new condition.
 690        """
 691        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 692
 693    def not_(self, copy=True):
 694        """
 695        Wrap this condition with NOT.
 696
 697        Example:
 698            >>> condition("x=1").not_().sql()
 699            'NOT x = 1'
 700
 701        Args:
 702            copy (bool): whether or not to copy this object.
 703
 704        Returns:
 705            Not: the new condition.
 706        """
 707        return not_(self, copy=copy)
 708
 709    def _binop(self, klass: t.Type[E], other: ExpOrStr, reverse=False) -> E:
 710        this = self.copy()
 711        other = convert(other, copy=True)
 712        if not isinstance(this, klass) and not isinstance(other, klass):
 713            this = _wrap(this, Binary)
 714            other = _wrap(other, Binary)
 715        if reverse:
 716            return klass(this=other, expression=this)
 717        return klass(this=this, expression=other)
 718
 719    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]):
 720        return Bracket(
 721            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 722        )
 723
 724    def isin(
 725        self, *expressions: t.Any, query: t.Optional[ExpOrStr] = None, copy=True, **opts
 726    ) -> In:
 727        return In(
 728            this=_maybe_copy(self, copy),
 729            expressions=[convert(e, copy=copy) for e in expressions],
 730            query=maybe_parse(query, copy=copy, **opts) if query else None,
 731        )
 732
 733    def between(self, low: t.Any, high: t.Any, copy=True, **opts) -> Between:
 734        return Between(
 735            this=_maybe_copy(self, copy),
 736            low=convert(low, copy=copy, **opts),
 737            high=convert(high, copy=copy, **opts),
 738        )
 739
 740    def like(self, other: ExpOrStr) -> Like:
 741        return self._binop(Like, other)
 742
 743    def ilike(self, other: ExpOrStr) -> ILike:
 744        return self._binop(ILike, other)
 745
 746    def eq(self, other: ExpOrStr) -> EQ:
 747        return self._binop(EQ, other)
 748
 749    def neq(self, other: ExpOrStr) -> NEQ:
 750        return self._binop(NEQ, other)
 751
 752    def rlike(self, other: ExpOrStr) -> RegexpLike:
 753        return self._binop(RegexpLike, other)
 754
 755    def __lt__(self, other: ExpOrStr) -> LT:
 756        return self._binop(LT, other)
 757
 758    def __le__(self, other: ExpOrStr) -> LTE:
 759        return self._binop(LTE, other)
 760
 761    def __gt__(self, other: ExpOrStr) -> GT:
 762        return self._binop(GT, other)
 763
 764    def __ge__(self, other: ExpOrStr) -> GTE:
 765        return self._binop(GTE, other)
 766
 767    def __add__(self, other: ExpOrStr) -> Add:
 768        return self._binop(Add, other)
 769
 770    def __radd__(self, other: ExpOrStr) -> Add:
 771        return self._binop(Add, other, reverse=True)
 772
 773    def __sub__(self, other: ExpOrStr) -> Sub:
 774        return self._binop(Sub, other)
 775
 776    def __rsub__(self, other: ExpOrStr) -> Sub:
 777        return self._binop(Sub, other, reverse=True)
 778
 779    def __mul__(self, other: ExpOrStr) -> Mul:
 780        return self._binop(Mul, other)
 781
 782    def __rmul__(self, other: ExpOrStr) -> Mul:
 783        return self._binop(Mul, other, reverse=True)
 784
 785    def __truediv__(self, other: ExpOrStr) -> Div:
 786        return self._binop(Div, other)
 787
 788    def __rtruediv__(self, other: ExpOrStr) -> Div:
 789        return self._binop(Div, other, reverse=True)
 790
 791    def __floordiv__(self, other: ExpOrStr) -> IntDiv:
 792        return self._binop(IntDiv, other)
 793
 794    def __rfloordiv__(self, other: ExpOrStr) -> IntDiv:
 795        return self._binop(IntDiv, other, reverse=True)
 796
 797    def __mod__(self, other: ExpOrStr) -> Mod:
 798        return self._binop(Mod, other)
 799
 800    def __rmod__(self, other: ExpOrStr) -> Mod:
 801        return self._binop(Mod, other, reverse=True)
 802
 803    def __pow__(self, other: ExpOrStr) -> Pow:
 804        return self._binop(Pow, other)
 805
 806    def __rpow__(self, other: ExpOrStr) -> Pow:
 807        return self._binop(Pow, other, reverse=True)
 808
 809    def __and__(self, other: ExpOrStr) -> And:
 810        return self._binop(And, other)
 811
 812    def __rand__(self, other: ExpOrStr) -> And:
 813        return self._binop(And, other, reverse=True)
 814
 815    def __or__(self, other: ExpOrStr) -> Or:
 816        return self._binop(Or, other)
 817
 818    def __ror__(self, other: ExpOrStr) -> Or:
 819        return self._binop(Or, other, reverse=True)
 820
 821    def __neg__(self) -> Neg:
 822        return Neg(this=_wrap(self.copy(), Binary))
 823
 824    def __invert__(self) -> Not:
 825        return not_(self.copy())
 826
 827
 828class Predicate(Condition):
 829    """Relationships like x = y, x > 1, x >= y."""
 830
 831
 832class DerivedTable(Expression):
 833    @property
 834    def alias_column_names(self):
 835        table_alias = self.args.get("alias")
 836        if not table_alias:
 837            return []
 838        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 839        return [c.name for c in column_list]
 840
 841    @property
 842    def selects(self):
 843        alias = self.args.get("alias")
 844
 845        if alias:
 846            return alias.columns
 847        return []
 848
 849    @property
 850    def named_selects(self):
 851        return [select.output_name for select in self.selects]
 852
 853
 854class Unionable(Expression):
 855    def union(self, expression, distinct=True, dialect=None, **opts):
 856        """
 857        Builds a UNION expression.
 858
 859        Example:
 860            >>> import sqlglot
 861            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 862            'SELECT * FROM foo UNION SELECT * FROM bla'
 863
 864        Args:
 865            expression (str | Expression): the SQL code string.
 866                If an `Expression` instance is passed, it will be used as-is.
 867            distinct (bool): set the DISTINCT flag if and only if this is true.
 868            dialect (str): the dialect used to parse the input expression.
 869            opts (kwargs): other options to use to parse the input expressions.
 870        Returns:
 871            Union: the Union expression.
 872        """
 873        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 874
 875    def intersect(self, expression, distinct=True, dialect=None, **opts):
 876        """
 877        Builds an INTERSECT expression.
 878
 879        Example:
 880            >>> import sqlglot
 881            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 882            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 883
 884        Args:
 885            expression (str | Expression): the SQL code string.
 886                If an `Expression` instance is passed, it will be used as-is.
 887            distinct (bool): set the DISTINCT flag if and only if this is true.
 888            dialect (str): the dialect used to parse the input expression.
 889            opts (kwargs): other options to use to parse the input expressions.
 890        Returns:
 891            Intersect: the Intersect expression
 892        """
 893        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 894
 895    def except_(self, expression, distinct=True, dialect=None, **opts):
 896        """
 897        Builds an EXCEPT expression.
 898
 899        Example:
 900            >>> import sqlglot
 901            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 902            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 903
 904        Args:
 905            expression (str | Expression): the SQL code string.
 906                If an `Expression` instance is passed, it will be used as-is.
 907            distinct (bool): set the DISTINCT flag if and only if this is true.
 908            dialect (str): the dialect used to parse the input expression.
 909            opts (kwargs): other options to use to parse the input expressions.
 910        Returns:
 911            Except: the Except expression
 912        """
 913        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 914
 915
 916class UDTF(DerivedTable, Unionable):
 917    pass
 918
 919
 920class Cache(Expression):
 921    arg_types = {
 922        "with": False,
 923        "this": True,
 924        "lazy": False,
 925        "options": False,
 926        "expression": False,
 927    }
 928
 929
 930class Uncache(Expression):
 931    arg_types = {"this": True, "exists": False}
 932
 933
 934class Create(Expression):
 935    arg_types = {
 936        "with": False,
 937        "this": True,
 938        "kind": True,
 939        "expression": False,
 940        "exists": False,
 941        "properties": False,
 942        "replace": False,
 943        "unique": False,
 944        "indexes": False,
 945        "no_schema_binding": False,
 946        "begin": False,
 947    }
 948
 949
 950class Describe(Expression):
 951    arg_types = {"this": True, "kind": False}
 952
 953
 954class Pragma(Expression):
 955    pass
 956
 957
 958class Set(Expression):
 959    arg_types = {"expressions": False}
 960
 961
 962class SetItem(Expression):
 963    arg_types = {
 964        "this": False,
 965        "expressions": False,
 966        "kind": False,
 967        "collate": False,  # MySQL SET NAMES statement
 968        "global": False,
 969    }
 970
 971
 972class Show(Expression):
 973    arg_types = {
 974        "this": True,
 975        "target": False,
 976        "offset": False,
 977        "limit": False,
 978        "like": False,
 979        "where": False,
 980        "db": False,
 981        "full": False,
 982        "mutex": False,
 983        "query": False,
 984        "channel": False,
 985        "global": False,
 986        "log": False,
 987        "position": False,
 988        "types": False,
 989    }
 990
 991
 992class UserDefinedFunction(Expression):
 993    arg_types = {"this": True, "expressions": False, "wrapped": False}
 994
 995
 996class CharacterSet(Expression):
 997    arg_types = {"this": True, "default": False}
 998
 999
1000class With(Expression):
1001    arg_types = {"expressions": True, "recursive": False}
1002
1003    @property
1004    def recursive(self) -> bool:
1005        return bool(self.args.get("recursive"))
1006
1007
1008class WithinGroup(Expression):
1009    arg_types = {"this": True, "expression": False}
1010
1011
1012class CTE(DerivedTable):
1013    arg_types = {"this": True, "alias": True}
1014
1015
1016class TableAlias(Expression):
1017    arg_types = {"this": False, "columns": False}
1018
1019    @property
1020    def columns(self):
1021        return self.args.get("columns") or []
1022
1023
1024class BitString(Condition):
1025    pass
1026
1027
1028class HexString(Condition):
1029    pass
1030
1031
1032class ByteString(Condition):
1033    pass
1034
1035
1036class Column(Condition):
1037    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1038
1039    @property
1040    def table(self) -> str:
1041        return self.text("table")
1042
1043    @property
1044    def db(self) -> str:
1045        return self.text("db")
1046
1047    @property
1048    def catalog(self) -> str:
1049        return self.text("catalog")
1050
1051    @property
1052    def output_name(self) -> str:
1053        return self.name
1054
1055    @property
1056    def parts(self) -> t.List[Identifier]:
1057        """Return the parts of a column in order catalog, db, table, name."""
1058        return [part for part in reversed(list(self.args.values())) if part]
1059
1060    def to_dot(self) -> Dot:
1061        """Converts the column into a dot expression."""
1062        parts = self.parts
1063        parent = self.parent
1064
1065        while parent:
1066            if isinstance(parent, Dot):
1067                parts.append(parent.expression)
1068            parent = parent.parent
1069
1070        return Dot.build(parts)
1071
1072
1073class ColumnPosition(Expression):
1074    arg_types = {"this": False, "position": True}
1075
1076
1077class ColumnDef(Expression):
1078    arg_types = {
1079        "this": True,
1080        "kind": False,
1081        "constraints": False,
1082        "exists": False,
1083        "position": False,
1084    }
1085
1086
1087class AlterColumn(Expression):
1088    arg_types = {
1089        "this": True,
1090        "dtype": False,
1091        "collate": False,
1092        "using": False,
1093        "default": False,
1094        "drop": False,
1095    }
1096
1097
1098class RenameTable(Expression):
1099    pass
1100
1101
1102class SetTag(Expression):
1103    arg_types = {"expressions": True, "unset": False}
1104
1105
1106class Comment(Expression):
1107    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1108
1109
1110class ColumnConstraint(Expression):
1111    arg_types = {"this": False, "kind": True}
1112
1113
1114class ColumnConstraintKind(Expression):
1115    pass
1116
1117
1118class AutoIncrementColumnConstraint(ColumnConstraintKind):
1119    pass
1120
1121
1122class CaseSpecificColumnConstraint(ColumnConstraintKind):
1123    arg_types = {"not_": True}
1124
1125
1126class CharacterSetColumnConstraint(ColumnConstraintKind):
1127    arg_types = {"this": True}
1128
1129
1130class CheckColumnConstraint(ColumnConstraintKind):
1131    pass
1132
1133
1134class CollateColumnConstraint(ColumnConstraintKind):
1135    pass
1136
1137
1138class CommentColumnConstraint(ColumnConstraintKind):
1139    pass
1140
1141
1142class CompressColumnConstraint(ColumnConstraintKind):
1143    pass
1144
1145
1146class DateFormatColumnConstraint(ColumnConstraintKind):
1147    arg_types = {"this": True}
1148
1149
1150class DefaultColumnConstraint(ColumnConstraintKind):
1151    pass
1152
1153
1154class EncodeColumnConstraint(ColumnConstraintKind):
1155    pass
1156
1157
1158class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1159    # this: True -> ALWAYS, this: False -> BY DEFAULT
1160    arg_types = {
1161        "this": False,
1162        "start": False,
1163        "increment": False,
1164        "minvalue": False,
1165        "maxvalue": False,
1166        "cycle": False,
1167    }
1168
1169
1170class InlineLengthColumnConstraint(ColumnConstraintKind):
1171    pass
1172
1173
1174class NotNullColumnConstraint(ColumnConstraintKind):
1175    arg_types = {"allow_null": False}
1176
1177
1178# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1179class OnUpdateColumnConstraint(ColumnConstraintKind):
1180    pass
1181
1182
1183class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1184    arg_types = {"desc": False}
1185
1186
1187class TitleColumnConstraint(ColumnConstraintKind):
1188    pass
1189
1190
1191class UniqueColumnConstraint(ColumnConstraintKind):
1192    arg_types: t.Dict[str, t.Any] = {}
1193
1194
1195class UppercaseColumnConstraint(ColumnConstraintKind):
1196    arg_types: t.Dict[str, t.Any] = {}
1197
1198
1199class PathColumnConstraint(ColumnConstraintKind):
1200    pass
1201
1202
1203class Constraint(Expression):
1204    arg_types = {"this": True, "expressions": True}
1205
1206
1207class Delete(Expression):
1208    arg_types = {"with": False, "this": False, "using": False, "where": False, "returning": False}
1209
1210    def delete(
1211        self,
1212        table: ExpOrStr,
1213        dialect: DialectType = None,
1214        copy: bool = True,
1215        **opts,
1216    ) -> Delete:
1217        """
1218        Create a DELETE expression or replace the table on an existing DELETE expression.
1219
1220        Example:
1221            >>> delete("tbl").sql()
1222            'DELETE FROM tbl'
1223
1224        Args:
1225            table: the table from which to delete.
1226            dialect: the dialect used to parse the input expression.
1227            copy: if `False`, modify this expression instance in-place.
1228            opts: other options to use to parse the input expressions.
1229
1230        Returns:
1231            Delete: the modified expression.
1232        """
1233        return _apply_builder(
1234            expression=table,
1235            instance=self,
1236            arg="this",
1237            dialect=dialect,
1238            into=Table,
1239            copy=copy,
1240            **opts,
1241        )
1242
1243    def where(
1244        self,
1245        *expressions: ExpOrStr,
1246        append: bool = True,
1247        dialect: DialectType = None,
1248        copy: bool = True,
1249        **opts,
1250    ) -> Delete:
1251        """
1252        Append to or set the WHERE expressions.
1253
1254        Example:
1255            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1256            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1257
1258        Args:
1259            *expressions: the SQL code strings to parse.
1260                If an `Expression` instance is passed, it will be used as-is.
1261                Multiple expressions are combined with an AND operator.
1262            append: if `True`, AND the new expressions to any existing expression.
1263                Otherwise, this resets the expression.
1264            dialect: the dialect used to parse the input expressions.
1265            copy: if `False`, modify this expression instance in-place.
1266            opts: other options to use to parse the input expressions.
1267
1268        Returns:
1269            Delete: the modified expression.
1270        """
1271        return _apply_conjunction_builder(
1272            *expressions,
1273            instance=self,
1274            arg="where",
1275            append=append,
1276            into=Where,
1277            dialect=dialect,
1278            copy=copy,
1279            **opts,
1280        )
1281
1282    def returning(
1283        self,
1284        expression: ExpOrStr,
1285        dialect: DialectType = None,
1286        copy: bool = True,
1287        **opts,
1288    ) -> Delete:
1289        """
1290        Set the RETURNING expression. Not supported by all dialects.
1291
1292        Example:
1293            >>> delete("tbl").returning("*", dialect="postgres").sql()
1294            'DELETE FROM tbl RETURNING *'
1295
1296        Args:
1297            expression: the SQL code strings to parse.
1298                If an `Expression` instance is passed, it will be used as-is.
1299            dialect: the dialect used to parse the input expressions.
1300            copy: if `False`, modify this expression instance in-place.
1301            opts: other options to use to parse the input expressions.
1302
1303        Returns:
1304            Delete: the modified expression.
1305        """
1306        return _apply_builder(
1307            expression=expression,
1308            instance=self,
1309            arg="returning",
1310            prefix="RETURNING",
1311            dialect=dialect,
1312            copy=copy,
1313            into=Returning,
1314            **opts,
1315        )
1316
1317
1318class Drop(Expression):
1319    arg_types = {
1320        "this": False,
1321        "kind": False,
1322        "exists": False,
1323        "temporary": False,
1324        "materialized": False,
1325        "cascade": False,
1326        "constraints": False,
1327        "purge": False,
1328    }
1329
1330
1331class Filter(Expression):
1332    arg_types = {"this": True, "expression": True}
1333
1334
1335class Check(Expression):
1336    pass
1337
1338
1339class Directory(Expression):
1340    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1341    arg_types = {"this": True, "local": False, "row_format": False}
1342
1343
1344class ForeignKey(Expression):
1345    arg_types = {
1346        "expressions": True,
1347        "reference": False,
1348        "delete": False,
1349        "update": False,
1350    }
1351
1352
1353class PrimaryKey(Expression):
1354    arg_types = {"expressions": True, "options": False}
1355
1356
1357class Unique(Expression):
1358    arg_types = {"expressions": True}
1359
1360
1361# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1362# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1363class Into(Expression):
1364    arg_types = {"this": True, "temporary": False, "unlogged": False}
1365
1366
1367class From(Expression):
1368    arg_types = {"expressions": True}
1369
1370
1371class Having(Expression):
1372    pass
1373
1374
1375class Hint(Expression):
1376    arg_types = {"expressions": True}
1377
1378
1379class JoinHint(Expression):
1380    arg_types = {"this": True, "expressions": True}
1381
1382
1383class Identifier(Expression):
1384    arg_types = {"this": True, "quoted": False}
1385
1386    @property
1387    def quoted(self):
1388        return bool(self.args.get("quoted"))
1389
1390    @property
1391    def hashable_args(self) -> t.Any:
1392        if self.quoted and any(char.isupper() for char in self.this):
1393            return (self.this, self.quoted)
1394        return self.this.lower()
1395
1396    @property
1397    def output_name(self):
1398        return self.name
1399
1400
1401class Index(Expression):
1402    arg_types = {
1403        "this": False,
1404        "table": False,
1405        "where": False,
1406        "columns": False,
1407        "unique": False,
1408        "primary": False,
1409        "amp": False,  # teradata
1410    }
1411
1412
1413class Insert(Expression):
1414    arg_types = {
1415        "with": False,
1416        "this": True,
1417        "expression": False,
1418        "conflict": False,
1419        "returning": False,
1420        "overwrite": False,
1421        "exists": False,
1422        "partition": False,
1423        "alternative": False,
1424    }
1425
1426
1427class OnConflict(Expression):
1428    arg_types = {
1429        "duplicate": False,
1430        "expressions": False,
1431        "nothing": False,
1432        "key": False,
1433        "constraint": False,
1434    }
1435
1436
1437class Returning(Expression):
1438    arg_types = {"expressions": True}
1439
1440
1441# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1442class Introducer(Expression):
1443    arg_types = {"this": True, "expression": True}
1444
1445
1446# national char, like n'utf8'
1447class National(Expression):
1448    pass
1449
1450
1451class LoadData(Expression):
1452    arg_types = {
1453        "this": True,
1454        "local": False,
1455        "overwrite": False,
1456        "inpath": True,
1457        "partition": False,
1458        "input_format": False,
1459        "serde": False,
1460    }
1461
1462
1463class Partition(Expression):
1464    arg_types = {"expressions": True}
1465
1466
1467class Fetch(Expression):
1468    arg_types = {
1469        "direction": False,
1470        "count": False,
1471        "percent": False,
1472        "with_ties": False,
1473    }
1474
1475
1476class Group(Expression):
1477    arg_types = {
1478        "expressions": False,
1479        "grouping_sets": False,
1480        "cube": False,
1481        "rollup": False,
1482    }
1483
1484
1485class Lambda(Expression):
1486    arg_types = {"this": True, "expressions": True}
1487
1488
1489class Limit(Expression):
1490    arg_types = {"this": False, "expression": True}
1491
1492
1493class Literal(Condition):
1494    arg_types = {"this": True, "is_string": True}
1495
1496    @property
1497    def hashable_args(self) -> t.Any:
1498        return (self.this, self.args.get("is_string"))
1499
1500    @classmethod
1501    def number(cls, number) -> Literal:
1502        return cls(this=str(number), is_string=False)
1503
1504    @classmethod
1505    def string(cls, string) -> Literal:
1506        return cls(this=str(string), is_string=True)
1507
1508    @property
1509    def output_name(self):
1510        return self.name
1511
1512
1513class Join(Expression):
1514    arg_types = {
1515        "this": True,
1516        "on": False,
1517        "side": False,
1518        "kind": False,
1519        "using": False,
1520        "natural": False,
1521        "hint": False,
1522    }
1523
1524    @property
1525    def kind(self):
1526        return self.text("kind").upper()
1527
1528    @property
1529    def side(self):
1530        return self.text("side").upper()
1531
1532    @property
1533    def hint(self):
1534        return self.text("hint").upper()
1535
1536    @property
1537    def alias_or_name(self):
1538        return self.this.alias_or_name
1539
1540    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1541        """
1542        Append to or set the ON expressions.
1543
1544        Example:
1545            >>> import sqlglot
1546            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1547            'JOIN x ON y = 1'
1548
1549        Args:
1550            *expressions (str | Expression): the SQL code strings to parse.
1551                If an `Expression` instance is passed, it will be used as-is.
1552                Multiple expressions are combined with an AND operator.
1553            append (bool): if `True`, AND the new expressions to any existing expression.
1554                Otherwise, this resets the expression.
1555            dialect (str): the dialect used to parse the input expressions.
1556            copy (bool): if `False`, modify this expression instance in-place.
1557            opts (kwargs): other options to use to parse the input expressions.
1558
1559        Returns:
1560            Join: the modified join expression.
1561        """
1562        join = _apply_conjunction_builder(
1563            *expressions,
1564            instance=self,
1565            arg="on",
1566            append=append,
1567            dialect=dialect,
1568            copy=copy,
1569            **opts,
1570        )
1571
1572        if join.kind == "CROSS":
1573            join.set("kind", None)
1574
1575        return join
1576
1577    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1578        """
1579        Append to or set the USING expressions.
1580
1581        Example:
1582            >>> import sqlglot
1583            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1584            'JOIN x USING (foo, bla)'
1585
1586        Args:
1587            *expressions (str | Expression): the SQL code strings to parse.
1588                If an `Expression` instance is passed, it will be used as-is.
1589            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1590                Otherwise, this resets the expression.
1591            dialect (str): the dialect used to parse the input expressions.
1592            copy (bool): if `False`, modify this expression instance in-place.
1593            opts (kwargs): other options to use to parse the input expressions.
1594
1595        Returns:
1596            Join: the modified join expression.
1597        """
1598        join = _apply_list_builder(
1599            *expressions,
1600            instance=self,
1601            arg="using",
1602            append=append,
1603            dialect=dialect,
1604            copy=copy,
1605            **opts,
1606        )
1607
1608        if join.kind == "CROSS":
1609            join.set("kind", None)
1610
1611        return join
1612
1613
1614class Lateral(UDTF):
1615    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1616
1617
1618class MatchRecognize(Expression):
1619    arg_types = {
1620        "partition_by": False,
1621        "order": False,
1622        "measures": False,
1623        "rows": False,
1624        "after": False,
1625        "pattern": False,
1626        "define": False,
1627        "alias": False,
1628    }
1629
1630
1631# Clickhouse FROM FINAL modifier
1632# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1633class Final(Expression):
1634    pass
1635
1636
1637class Offset(Expression):
1638    arg_types = {"this": False, "expression": True}
1639
1640
1641class Order(Expression):
1642    arg_types = {"this": False, "expressions": True}
1643
1644
1645# hive specific sorts
1646# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1647class Cluster(Order):
1648    pass
1649
1650
1651class Distribute(Order):
1652    pass
1653
1654
1655class Sort(Order):
1656    pass
1657
1658
1659class Ordered(Expression):
1660    arg_types = {"this": True, "desc": True, "nulls_first": True}
1661
1662
1663class Property(Expression):
1664    arg_types = {"this": True, "value": True}
1665
1666
1667class AfterJournalProperty(Property):
1668    arg_types = {"no": True, "dual": False, "local": False}
1669
1670
1671class AlgorithmProperty(Property):
1672    arg_types = {"this": True}
1673
1674
1675class AutoIncrementProperty(Property):
1676    arg_types = {"this": True}
1677
1678
1679class BlockCompressionProperty(Property):
1680    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1681
1682
1683class CharacterSetProperty(Property):
1684    arg_types = {"this": True, "default": True}
1685
1686
1687class ChecksumProperty(Property):
1688    arg_types = {"on": False, "default": False}
1689
1690
1691class CollateProperty(Property):
1692    arg_types = {"this": True}
1693
1694
1695class DataBlocksizeProperty(Property):
1696    arg_types = {"size": False, "units": False, "min": False, "default": False}
1697
1698
1699class DefinerProperty(Property):
1700    arg_types = {"this": True}
1701
1702
1703class DistKeyProperty(Property):
1704    arg_types = {"this": True}
1705
1706
1707class DistStyleProperty(Property):
1708    arg_types = {"this": True}
1709
1710
1711class EngineProperty(Property):
1712    arg_types = {"this": True}
1713
1714
1715class ExecuteAsProperty(Property):
1716    arg_types = {"this": True}
1717
1718
1719class ExternalProperty(Property):
1720    arg_types = {"this": False}
1721
1722
1723class FallbackProperty(Property):
1724    arg_types = {"no": True, "protection": False}
1725
1726
1727class FileFormatProperty(Property):
1728    arg_types = {"this": True}
1729
1730
1731class FreespaceProperty(Property):
1732    arg_types = {"this": True, "percent": False}
1733
1734
1735class InputOutputFormat(Expression):
1736    arg_types = {"input_format": False, "output_format": False}
1737
1738
1739class IsolatedLoadingProperty(Property):
1740    arg_types = {
1741        "no": True,
1742        "concurrent": True,
1743        "for_all": True,
1744        "for_insert": True,
1745        "for_none": True,
1746    }
1747
1748
1749class JournalProperty(Property):
1750    arg_types = {"no": True, "dual": False, "before": False}
1751
1752
1753class LanguageProperty(Property):
1754    arg_types = {"this": True}
1755
1756
1757class LikeProperty(Property):
1758    arg_types = {"this": True, "expressions": False}
1759
1760
1761class LocationProperty(Property):
1762    arg_types = {"this": True}
1763
1764
1765class LockingProperty(Property):
1766    arg_types = {
1767        "this": False,
1768        "kind": True,
1769        "for_or_in": True,
1770        "lock_type": True,
1771        "override": False,
1772    }
1773
1774
1775class LogProperty(Property):
1776    arg_types = {"no": True}
1777
1778
1779class MaterializedProperty(Property):
1780    arg_types = {"this": False}
1781
1782
1783class MergeBlockRatioProperty(Property):
1784    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1785
1786
1787class NoPrimaryIndexProperty(Property):
1788    arg_types = {"this": False}
1789
1790
1791class OnCommitProperty(Property):
1792    arg_type = {"this": False}
1793
1794
1795class PartitionedByProperty(Property):
1796    arg_types = {"this": True}
1797
1798
1799class ReturnsProperty(Property):
1800    arg_types = {"this": True, "is_table": False, "table": False}
1801
1802
1803class RowFormatProperty(Property):
1804    arg_types = {"this": True}
1805
1806
1807class RowFormatDelimitedProperty(Property):
1808    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1809    arg_types = {
1810        "fields": False,
1811        "escaped": False,
1812        "collection_items": False,
1813        "map_keys": False,
1814        "lines": False,
1815        "null": False,
1816        "serde": False,
1817    }
1818
1819
1820class RowFormatSerdeProperty(Property):
1821    arg_types = {"this": True}
1822
1823
1824class SchemaCommentProperty(Property):
1825    arg_types = {"this": True}
1826
1827
1828class SerdeProperties(Property):
1829    arg_types = {"expressions": True}
1830
1831
1832class SetProperty(Property):
1833    arg_types = {"multi": True}
1834
1835
1836class SortKeyProperty(Property):
1837    arg_types = {"this": True, "compound": False}
1838
1839
1840class SqlSecurityProperty(Property):
1841    arg_types = {"definer": True}
1842
1843
1844class StabilityProperty(Property):
1845    arg_types = {"this": True}
1846
1847
1848class TableFormatProperty(Property):
1849    arg_types = {"this": True}
1850
1851
1852class TemporaryProperty(Property):
1853    arg_types = {"global_": True}
1854
1855
1856class TransientProperty(Property):
1857    arg_types = {"this": False}
1858
1859
1860class VolatileProperty(Property):
1861    arg_types = {"this": False}
1862
1863
1864class WithDataProperty(Property):
1865    arg_types = {"no": True, "statistics": False}
1866
1867
1868class WithJournalTableProperty(Property):
1869    arg_types = {"this": True}
1870
1871
1872class Properties(Expression):
1873    arg_types = {"expressions": True}
1874
1875    NAME_TO_PROPERTY = {
1876        "ALGORITHM": AlgorithmProperty,
1877        "AUTO_INCREMENT": AutoIncrementProperty,
1878        "CHARACTER SET": CharacterSetProperty,
1879        "COLLATE": CollateProperty,
1880        "COMMENT": SchemaCommentProperty,
1881        "DEFINER": DefinerProperty,
1882        "DISTKEY": DistKeyProperty,
1883        "DISTSTYLE": DistStyleProperty,
1884        "ENGINE": EngineProperty,
1885        "EXECUTE AS": ExecuteAsProperty,
1886        "FORMAT": FileFormatProperty,
1887        "LANGUAGE": LanguageProperty,
1888        "LOCATION": LocationProperty,
1889        "PARTITIONED_BY": PartitionedByProperty,
1890        "RETURNS": ReturnsProperty,
1891        "ROW_FORMAT": RowFormatProperty,
1892        "SORTKEY": SortKeyProperty,
1893        "TABLE_FORMAT": TableFormatProperty,
1894    }
1895
1896    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1897
1898    # CREATE property locations
1899    # Form: schema specified
1900    #   create [POST_CREATE]
1901    #     table a [POST_NAME]
1902    #     (b int) [POST_SCHEMA]
1903    #     with ([POST_WITH])
1904    #     index (b) [POST_INDEX]
1905    #
1906    # Form: alias selection
1907    #   create [POST_CREATE]
1908    #     table a [POST_NAME]
1909    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1910    #     index (c) [POST_INDEX]
1911    class Location(AutoName):
1912        POST_CREATE = auto()
1913        POST_NAME = auto()
1914        POST_SCHEMA = auto()
1915        POST_WITH = auto()
1916        POST_ALIAS = auto()
1917        POST_EXPRESSION = auto()
1918        POST_INDEX = auto()
1919        UNSUPPORTED = auto()
1920
1921    @classmethod
1922    def from_dict(cls, properties_dict) -> Properties:
1923        expressions = []
1924        for key, value in properties_dict.items():
1925            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1926            if property_cls:
1927                expressions.append(property_cls(this=convert(value)))
1928            else:
1929                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1930
1931        return cls(expressions=expressions)
1932
1933
1934class Qualify(Expression):
1935    pass
1936
1937
1938# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1939class Return(Expression):
1940    pass
1941
1942
1943class Reference(Expression):
1944    arg_types = {"this": True, "expressions": False, "options": False}
1945
1946
1947class Tuple(Expression):
1948    arg_types = {"expressions": False}
1949
1950
1951class Subqueryable(Unionable):
1952    def subquery(self, alias=None, copy=True) -> Subquery:
1953        """
1954        Convert this expression to an aliased expression that can be used as a Subquery.
1955
1956        Example:
1957            >>> subquery = Select().select("x").from_("tbl").subquery()
1958            >>> Select().select("x").from_(subquery).sql()
1959            'SELECT x FROM (SELECT x FROM tbl)'
1960
1961        Args:
1962            alias (str | Identifier): an optional alias for the subquery
1963            copy (bool): if `False`, modify this expression instance in-place.
1964
1965        Returns:
1966            Alias: the subquery
1967        """
1968        instance = _maybe_copy(self, copy)
1969        return Subquery(
1970            this=instance,
1971            alias=TableAlias(this=to_identifier(alias)) if alias else None,
1972        )
1973
1974    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1975        raise NotImplementedError
1976
1977    @property
1978    def ctes(self):
1979        with_ = self.args.get("with")
1980        if not with_:
1981            return []
1982        return with_.expressions
1983
1984    @property
1985    def selects(self):
1986        raise NotImplementedError("Subqueryable objects must implement `selects`")
1987
1988    @property
1989    def named_selects(self):
1990        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1991
1992    def with_(
1993        self,
1994        alias,
1995        as_,
1996        recursive=None,
1997        append=True,
1998        dialect=None,
1999        copy=True,
2000        **opts,
2001    ):
2002        """
2003        Append to or set the common table expressions.
2004
2005        Example:
2006            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2007            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2008
2009        Args:
2010            alias (str | Expression): the SQL code string to parse as the table name.
2011                If an `Expression` instance is passed, this is used as-is.
2012            as_ (str | Expression): the SQL code string to parse as the table expression.
2013                If an `Expression` instance is passed, it will be used as-is.
2014            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
2015            append (bool): if `True`, add to any existing expressions.
2016                Otherwise, this resets the expressions.
2017            dialect (str): the dialect used to parse the input expression.
2018            copy (bool): if `False`, modify this expression instance in-place.
2019            opts (kwargs): other options to use to parse the input expressions.
2020
2021        Returns:
2022            Select: the modified expression.
2023        """
2024        alias_expression = maybe_parse(
2025            alias,
2026            dialect=dialect,
2027            into=TableAlias,
2028            **opts,
2029        )
2030        as_expression = maybe_parse(
2031            as_,
2032            dialect=dialect,
2033            **opts,
2034        )
2035        cte = CTE(
2036            this=as_expression,
2037            alias=alias_expression,
2038        )
2039        return _apply_child_list_builder(
2040            cte,
2041            instance=self,
2042            arg="with",
2043            append=append,
2044            copy=copy,
2045            into=With,
2046            properties={"recursive": recursive or False},
2047        )
2048
2049
2050QUERY_MODIFIERS = {
2051    "match": False,
2052    "laterals": False,
2053    "joins": False,
2054    "pivots": False,
2055    "where": False,
2056    "group": False,
2057    "having": False,
2058    "qualify": False,
2059    "windows": False,
2060    "distribute": False,
2061    "sort": False,
2062    "cluster": False,
2063    "order": False,
2064    "limit": False,
2065    "offset": False,
2066    "lock": False,
2067    "sample": False,
2068}
2069
2070
2071class Table(Expression):
2072    arg_types = {
2073        "this": True,
2074        "alias": False,
2075        "db": False,
2076        "catalog": False,
2077        "laterals": False,
2078        "joins": False,
2079        "pivots": False,
2080        "hints": False,
2081        "system_time": False,
2082    }
2083
2084    @property
2085    def db(self) -> str:
2086        return self.text("db")
2087
2088    @property
2089    def catalog(self) -> str:
2090        return self.text("catalog")
2091
2092
2093# See the TSQL "Querying data in a system-versioned temporal table" page
2094class SystemTime(Expression):
2095    arg_types = {
2096        "this": False,
2097        "expression": False,
2098        "kind": True,
2099    }
2100
2101
2102class Union(Subqueryable):
2103    arg_types = {
2104        "with": False,
2105        "this": True,
2106        "expression": True,
2107        "distinct": False,
2108        **QUERY_MODIFIERS,
2109    }
2110
2111    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2112        """
2113        Set the LIMIT expression.
2114
2115        Example:
2116            >>> select("1").union(select("1")).limit(1).sql()
2117            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2118
2119        Args:
2120            expression (str | int | Expression): the SQL code string to parse.
2121                This can also be an integer.
2122                If a `Limit` instance is passed, this is used as-is.
2123                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2124            dialect (str): the dialect used to parse the input expression.
2125            copy (bool): if `False`, modify this expression instance in-place.
2126            opts (kwargs): other options to use to parse the input expressions.
2127
2128        Returns:
2129            Select: The limited subqueryable.
2130        """
2131        return (
2132            select("*")
2133            .from_(self.subquery(alias="_l_0", copy=copy))
2134            .limit(expression, dialect=dialect, copy=False, **opts)
2135        )
2136
2137    def select(
2138        self,
2139        *expressions: ExpOrStr,
2140        append: bool = True,
2141        dialect: DialectType = None,
2142        copy: bool = True,
2143        **opts,
2144    ) -> Union:
2145        """Append to or set the SELECT of the union recursively.
2146
2147        Example:
2148            >>> from sqlglot import parse_one
2149            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2150            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2151
2152        Args:
2153            *expressions: the SQL code strings to parse.
2154                If an `Expression` instance is passed, it will be used as-is.
2155            append: if `True`, add to any existing expressions.
2156                Otherwise, this resets the expressions.
2157            dialect: the dialect used to parse the input expressions.
2158            copy: if `False`, modify this expression instance in-place.
2159            opts: other options to use to parse the input expressions.
2160
2161        Returns:
2162            Union: the modified expression.
2163        """
2164        this = self.copy() if copy else self
2165        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2166        this.expression.unnest().select(
2167            *expressions, append=append, dialect=dialect, copy=False, **opts
2168        )
2169        return this
2170
2171    @property
2172    def named_selects(self):
2173        return self.this.unnest().named_selects
2174
2175    @property
2176    def is_star(self) -> bool:
2177        return self.this.is_star or self.expression.is_star
2178
2179    @property
2180    def selects(self):
2181        return self.this.unnest().selects
2182
2183    @property
2184    def left(self):
2185        return self.this
2186
2187    @property
2188    def right(self):
2189        return self.expression
2190
2191
2192class Except(Union):
2193    pass
2194
2195
2196class Intersect(Union):
2197    pass
2198
2199
2200class Unnest(UDTF):
2201    arg_types = {
2202        "expressions": True,
2203        "ordinality": False,
2204        "alias": False,
2205        "offset": False,
2206    }
2207
2208
2209class Update(Expression):
2210    arg_types = {
2211        "with": False,
2212        "this": False,
2213        "expressions": True,
2214        "from": False,
2215        "where": False,
2216        "returning": False,
2217    }
2218
2219
2220class Values(UDTF):
2221    arg_types = {
2222        "expressions": True,
2223        "ordinality": False,
2224        "alias": False,
2225    }
2226
2227
2228class Var(Expression):
2229    pass
2230
2231
2232class Schema(Expression):
2233    arg_types = {"this": False, "expressions": False}
2234
2235
2236# Used to represent the FOR UPDATE and FOR SHARE locking read types.
2237# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
2238class Lock(Expression):
2239    arg_types = {"update": True}
2240
2241
2242class Select(Subqueryable):
2243    arg_types = {
2244        "with": False,
2245        "kind": False,
2246        "expressions": False,
2247        "hint": False,
2248        "distinct": False,
2249        "into": False,
2250        "from": False,
2251        **QUERY_MODIFIERS,
2252    }
2253
2254    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2255        """
2256        Set the FROM expression.
2257
2258        Example:
2259            >>> Select().from_("tbl").select("x").sql()
2260            'SELECT x FROM tbl'
2261
2262        Args:
2263            *expressions (str | Expression): the SQL code strings to parse.
2264                If a `From` instance is passed, this is used as-is.
2265                If another `Expression` instance is passed, it will be wrapped in a `From`.
2266            append (bool): if `True`, add to any existing expressions.
2267                Otherwise, this flattens all the `From` expression into a single expression.
2268            dialect (str): the dialect used to parse the input expression.
2269            copy (bool): if `False`, modify this expression instance in-place.
2270            opts (kwargs): other options to use to parse the input expressions.
2271
2272        Returns:
2273            Select: the modified expression.
2274        """
2275        return _apply_child_list_builder(
2276            *expressions,
2277            instance=self,
2278            arg="from",
2279            append=append,
2280            copy=copy,
2281            prefix="FROM",
2282            into=From,
2283            dialect=dialect,
2284            **opts,
2285        )
2286
2287    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2288        """
2289        Set the GROUP BY expression.
2290
2291        Example:
2292            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2293            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2294
2295        Args:
2296            *expressions (str | Expression): the SQL code strings to parse.
2297                If a `Group` instance is passed, this is used as-is.
2298                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2299                If nothing is passed in then a group by is not applied to the expression
2300            append (bool): if `True`, add to any existing expressions.
2301                Otherwise, this flattens all the `Group` expression into a single expression.
2302            dialect (str): the dialect used to parse the input expression.
2303            copy (bool): if `False`, modify this expression instance in-place.
2304            opts (kwargs): other options to use to parse the input expressions.
2305
2306        Returns:
2307            Select: the modified expression.
2308        """
2309        if not expressions:
2310            return self if not copy else self.copy()
2311        return _apply_child_list_builder(
2312            *expressions,
2313            instance=self,
2314            arg="group",
2315            append=append,
2316            copy=copy,
2317            prefix="GROUP BY",
2318            into=Group,
2319            dialect=dialect,
2320            **opts,
2321        )
2322
2323    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2324        """
2325        Set the ORDER BY expression.
2326
2327        Example:
2328            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2329            'SELECT x FROM tbl ORDER BY x DESC'
2330
2331        Args:
2332            *expressions (str | Expression): the SQL code strings to parse.
2333                If a `Group` instance is passed, this is used as-is.
2334                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2335            append (bool): if `True`, add to any existing expressions.
2336                Otherwise, this flattens all the `Order` expression into a single expression.
2337            dialect (str): the dialect used to parse the input expression.
2338            copy (bool): if `False`, modify this expression instance in-place.
2339            opts (kwargs): other options to use to parse the input expressions.
2340
2341        Returns:
2342            Select: the modified expression.
2343        """
2344        return _apply_child_list_builder(
2345            *expressions,
2346            instance=self,
2347            arg="order",
2348            append=append,
2349            copy=copy,
2350            prefix="ORDER BY",
2351            into=Order,
2352            dialect=dialect,
2353            **opts,
2354        )
2355
2356    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2357        """
2358        Set the SORT BY expression.
2359
2360        Example:
2361            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2362            'SELECT x FROM tbl SORT BY x DESC'
2363
2364        Args:
2365            *expressions (str | Expression): the SQL code strings to parse.
2366                If a `Group` instance is passed, this is used as-is.
2367                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2368            append (bool): if `True`, add to any existing expressions.
2369                Otherwise, this flattens all the `Order` expression into a single expression.
2370            dialect (str): the dialect used to parse the input expression.
2371            copy (bool): if `False`, modify this expression instance in-place.
2372            opts (kwargs): other options to use to parse the input expressions.
2373
2374        Returns:
2375            Select: the modified expression.
2376        """
2377        return _apply_child_list_builder(
2378            *expressions,
2379            instance=self,
2380            arg="sort",
2381            append=append,
2382            copy=copy,
2383            prefix="SORT BY",
2384            into=Sort,
2385            dialect=dialect,
2386            **opts,
2387        )
2388
2389    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2390        """
2391        Set the CLUSTER BY expression.
2392
2393        Example:
2394            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2395            'SELECT x FROM tbl CLUSTER BY x DESC'
2396
2397        Args:
2398            *expressions (str | Expression): the SQL code strings to parse.
2399                If a `Group` instance is passed, this is used as-is.
2400                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2401            append (bool): if `True`, add to any existing expressions.
2402                Otherwise, this flattens all the `Order` expression into a single expression.
2403            dialect (str): the dialect used to parse the input expression.
2404            copy (bool): if `False`, modify this expression instance in-place.
2405            opts (kwargs): other options to use to parse the input expressions.
2406
2407        Returns:
2408            Select: the modified expression.
2409        """
2410        return _apply_child_list_builder(
2411            *expressions,
2412            instance=self,
2413            arg="cluster",
2414            append=append,
2415            copy=copy,
2416            prefix="CLUSTER BY",
2417            into=Cluster,
2418            dialect=dialect,
2419            **opts,
2420        )
2421
2422    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2423        """
2424        Set the LIMIT expression.
2425
2426        Example:
2427            >>> Select().from_("tbl").select("x").limit(10).sql()
2428            'SELECT x FROM tbl LIMIT 10'
2429
2430        Args:
2431            expression (str | int | Expression): the SQL code string to parse.
2432                This can also be an integer.
2433                If a `Limit` instance is passed, this is used as-is.
2434                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2435            dialect (str): the dialect used to parse the input expression.
2436            copy (bool): if `False`, modify this expression instance in-place.
2437            opts (kwargs): other options to use to parse the input expressions.
2438
2439        Returns:
2440            Select: the modified expression.
2441        """
2442        return _apply_builder(
2443            expression=expression,
2444            instance=self,
2445            arg="limit",
2446            into=Limit,
2447            prefix="LIMIT",
2448            dialect=dialect,
2449            copy=copy,
2450            **opts,
2451        )
2452
2453    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2454        """
2455        Set the OFFSET expression.
2456
2457        Example:
2458            >>> Select().from_("tbl").select("x").offset(10).sql()
2459            'SELECT x FROM tbl OFFSET 10'
2460
2461        Args:
2462            expression (str | int | Expression): the SQL code string to parse.
2463                This can also be an integer.
2464                If a `Offset` instance is passed, this is used as-is.
2465                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2466            dialect (str): the dialect used to parse the input expression.
2467            copy (bool): if `False`, modify this expression instance in-place.
2468            opts (kwargs): other options to use to parse the input expressions.
2469
2470        Returns:
2471            Select: the modified expression.
2472        """
2473        return _apply_builder(
2474            expression=expression,
2475            instance=self,
2476            arg="offset",
2477            into=Offset,
2478            prefix="OFFSET",
2479            dialect=dialect,
2480            copy=copy,
2481            **opts,
2482        )
2483
2484    def select(
2485        self,
2486        *expressions: ExpOrStr,
2487        append: bool = True,
2488        dialect: DialectType = None,
2489        copy: bool = True,
2490        **opts,
2491    ) -> Select:
2492        """
2493        Append to or set the SELECT expressions.
2494
2495        Example:
2496            >>> Select().select("x", "y").sql()
2497            'SELECT x, y'
2498
2499        Args:
2500            *expressions: the SQL code strings to parse.
2501                If an `Expression` instance is passed, it will be used as-is.
2502            append: if `True`, add to any existing expressions.
2503                Otherwise, this resets the expressions.
2504            dialect: the dialect used to parse the input expressions.
2505            copy: if `False`, modify this expression instance in-place.
2506            opts: other options to use to parse the input expressions.
2507
2508        Returns:
2509            Select: the modified expression.
2510        """
2511        return _apply_list_builder(
2512            *expressions,
2513            instance=self,
2514            arg="expressions",
2515            append=append,
2516            dialect=dialect,
2517            copy=copy,
2518            **opts,
2519        )
2520
2521    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2522        """
2523        Append to or set the LATERAL expressions.
2524
2525        Example:
2526            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2527            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2528
2529        Args:
2530            *expressions (str | Expression): the SQL code strings to parse.
2531                If an `Expression` instance is passed, it will be used as-is.
2532            append (bool): if `True`, add to any existing expressions.
2533                Otherwise, this resets the expressions.
2534            dialect (str): the dialect used to parse the input expressions.
2535            copy (bool): if `False`, modify this expression instance in-place.
2536            opts (kwargs): other options to use to parse the input expressions.
2537
2538        Returns:
2539            Select: the modified expression.
2540        """
2541        return _apply_list_builder(
2542            *expressions,
2543            instance=self,
2544            arg="laterals",
2545            append=append,
2546            into=Lateral,
2547            prefix="LATERAL VIEW",
2548            dialect=dialect,
2549            copy=copy,
2550            **opts,
2551        )
2552
2553    def join(
2554        self,
2555        expression,
2556        on=None,
2557        using=None,
2558        append=True,
2559        join_type=None,
2560        join_alias=None,
2561        dialect=None,
2562        copy=True,
2563        **opts,
2564    ) -> Select:
2565        """
2566        Append to or set the JOIN expressions.
2567
2568        Example:
2569            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2570            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2571
2572            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2573            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2574
2575            Use `join_type` to change the type of join:
2576
2577            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2578            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2579
2580        Args:
2581            expression (str | Expression): the SQL code string to parse.
2582                If an `Expression` instance is passed, it will be used as-is.
2583            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2584                If an `Expression` instance is passed, it will be used as-is.
2585            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2586                If an `Expression` instance is passed, it will be used as-is.
2587            append (bool): if `True`, add to any existing expressions.
2588                Otherwise, this resets the expressions.
2589            join_type (str): If set, alter the parsed join type
2590            dialect (str): the dialect used to parse the input expressions.
2591            copy (bool): if `False`, modify this expression instance in-place.
2592            opts (kwargs): other options to use to parse the input expressions.
2593
2594        Returns:
2595            Select: the modified expression.
2596        """
2597        parse_args = {"dialect": dialect, **opts}
2598
2599        try:
2600            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2601        except ParseError:
2602            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2603
2604        join = expression if isinstance(expression, Join) else Join(this=expression)
2605
2606        if isinstance(join.this, Select):
2607            join.this.replace(join.this.subquery())
2608
2609        if join_type:
2610            natural: t.Optional[Token]
2611            side: t.Optional[Token]
2612            kind: t.Optional[Token]
2613
2614            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2615
2616            if natural:
2617                join.set("natural", True)
2618            if side:
2619                join.set("side", side.text)
2620            if kind:
2621                join.set("kind", kind.text)
2622
2623        if on:
2624            on = and_(*ensure_collection(on), dialect=dialect, copy=copy, **opts)
2625            join.set("on", on)
2626
2627        if using:
2628            join = _apply_list_builder(
2629                *ensure_collection(using),
2630                instance=join,
2631                arg="using",
2632                append=append,
2633                copy=copy,
2634                **opts,
2635            )
2636
2637        if join_alias:
2638            join.set("this", alias_(join.this, join_alias, table=True))
2639        return _apply_list_builder(
2640            join,
2641            instance=self,
2642            arg="joins",
2643            append=append,
2644            copy=copy,
2645            **opts,
2646        )
2647
2648    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2649        """
2650        Append to or set the WHERE expressions.
2651
2652        Example:
2653            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2654            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2655
2656        Args:
2657            *expressions (str | Expression): the SQL code strings to parse.
2658                If an `Expression` instance is passed, it will be used as-is.
2659                Multiple expressions are combined with an AND operator.
2660            append (bool): if `True`, AND the new expressions to any existing expression.
2661                Otherwise, this resets the expression.
2662            dialect (str): the dialect used to parse the input expressions.
2663            copy (bool): if `False`, modify this expression instance in-place.
2664            opts (kwargs): other options to use to parse the input expressions.
2665
2666        Returns:
2667            Select: the modified expression.
2668        """
2669        return _apply_conjunction_builder(
2670            *expressions,
2671            instance=self,
2672            arg="where",
2673            append=append,
2674            into=Where,
2675            dialect=dialect,
2676            copy=copy,
2677            **opts,
2678        )
2679
2680    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2681        """
2682        Append to or set the HAVING expressions.
2683
2684        Example:
2685            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2686            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2687
2688        Args:
2689            *expressions (str | Expression): the SQL code strings to parse.
2690                If an `Expression` instance is passed, it will be used as-is.
2691                Multiple expressions are combined with an AND operator.
2692            append (bool): if `True`, AND the new expressions to any existing expression.
2693                Otherwise, this resets the expression.
2694            dialect (str): the dialect used to parse the input expressions.
2695            copy (bool): if `False`, modify this expression instance in-place.
2696            opts (kwargs): other options to use to parse the input expressions.
2697
2698        Returns:
2699            Select: the modified expression.
2700        """
2701        return _apply_conjunction_builder(
2702            *expressions,
2703            instance=self,
2704            arg="having",
2705            append=append,
2706            into=Having,
2707            dialect=dialect,
2708            copy=copy,
2709            **opts,
2710        )
2711
2712    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2713        return _apply_list_builder(
2714            *expressions,
2715            instance=self,
2716            arg="windows",
2717            append=append,
2718            into=Window,
2719            dialect=dialect,
2720            copy=copy,
2721            **opts,
2722        )
2723
2724    def qualify(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2725        return _apply_conjunction_builder(
2726            *expressions,
2727            instance=self,
2728            arg="qualify",
2729            append=append,
2730            into=Qualify,
2731            dialect=dialect,
2732            copy=copy,
2733            **opts,
2734        )
2735
2736    def distinct(self, *ons: ExpOrStr, distinct: bool = True, copy: bool = True) -> Select:
2737        """
2738        Set the OFFSET expression.
2739
2740        Example:
2741            >>> Select().from_("tbl").select("x").distinct().sql()
2742            'SELECT DISTINCT x FROM tbl'
2743
2744        Args:
2745            ons: the expressions to distinct on
2746            distinct: whether the Select should be distinct
2747            copy: if `False`, modify this expression instance in-place.
2748
2749        Returns:
2750            Select: the modified expression.
2751        """
2752        instance = _maybe_copy(self, copy)
2753        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons]) if ons else None
2754        instance.set("distinct", Distinct(on=on) if distinct else None)
2755        return instance
2756
2757    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2758        """
2759        Convert this expression to a CREATE TABLE AS statement.
2760
2761        Example:
2762            >>> Select().select("*").from_("tbl").ctas("x").sql()
2763            'CREATE TABLE x AS SELECT * FROM tbl'
2764
2765        Args:
2766            table (str | Expression): the SQL code string to parse as the table name.
2767                If another `Expression` instance is passed, it will be used as-is.
2768            properties (dict): an optional mapping of table properties
2769            dialect (str): the dialect used to parse the input table.
2770            copy (bool): if `False`, modify this expression instance in-place.
2771            opts (kwargs): other options to use to parse the input table.
2772
2773        Returns:
2774            Create: the CREATE TABLE AS expression
2775        """
2776        instance = _maybe_copy(self, copy)
2777        table_expression = maybe_parse(
2778            table,
2779            into=Table,
2780            dialect=dialect,
2781            **opts,
2782        )
2783        properties_expression = None
2784        if properties:
2785            properties_expression = Properties.from_dict(properties)
2786
2787        return Create(
2788            this=table_expression,
2789            kind="table",
2790            expression=instance,
2791            properties=properties_expression,
2792        )
2793
2794    def lock(self, update: bool = True, copy: bool = True) -> Select:
2795        """
2796        Set the locking read mode for this expression.
2797
2798        Examples:
2799            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2800            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2801
2802            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2803            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2804
2805        Args:
2806            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2807            copy: if `False`, modify this expression instance in-place.
2808
2809        Returns:
2810            The modified expression.
2811        """
2812
2813        inst = _maybe_copy(self, copy)
2814        inst.set("lock", Lock(update=update))
2815
2816        return inst
2817
2818    @property
2819    def named_selects(self) -> t.List[str]:
2820        return [e.output_name for e in self.expressions if e.alias_or_name]
2821
2822    @property
2823    def is_star(self) -> bool:
2824        return any(expression.is_star for expression in self.expressions)
2825
2826    @property
2827    def selects(self) -> t.List[Expression]:
2828        return self.expressions
2829
2830
2831class Subquery(DerivedTable, Unionable):
2832    arg_types = {
2833        "this": True,
2834        "alias": False,
2835        "with": False,
2836        **QUERY_MODIFIERS,
2837    }
2838
2839    def unnest(self):
2840        """
2841        Returns the first non subquery.
2842        """
2843        expression = self
2844        while isinstance(expression, Subquery):
2845            expression = expression.this
2846        return expression
2847
2848    @property
2849    def is_star(self) -> bool:
2850        return self.this.is_star
2851
2852    @property
2853    def output_name(self):
2854        return self.alias
2855
2856
2857class TableSample(Expression):
2858    arg_types = {
2859        "this": False,
2860        "method": False,
2861        "bucket_numerator": False,
2862        "bucket_denominator": False,
2863        "bucket_field": False,
2864        "percent": False,
2865        "rows": False,
2866        "size": False,
2867        "seed": False,
2868        "kind": False,
2869    }
2870
2871
2872class Tag(Expression):
2873    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2874
2875    arg_types = {
2876        "this": False,
2877        "prefix": False,
2878        "postfix": False,
2879    }
2880
2881
2882class Pivot(Expression):
2883    arg_types = {
2884        "this": False,
2885        "alias": False,
2886        "expressions": True,
2887        "field": True,
2888        "unpivot": True,
2889        "columns": False,
2890    }
2891
2892
2893class Window(Expression):
2894    arg_types = {
2895        "this": True,
2896        "partition_by": False,
2897        "order": False,
2898        "spec": False,
2899        "alias": False,
2900        "over": False,
2901        "first": False,
2902    }
2903
2904
2905class WindowSpec(Expression):
2906    arg_types = {
2907        "kind": False,
2908        "start": False,
2909        "start_side": False,
2910        "end": False,
2911        "end_side": False,
2912    }
2913
2914
2915class Where(Expression):
2916    pass
2917
2918
2919class Star(Expression):
2920    arg_types = {"except": False, "replace": False}
2921
2922    @property
2923    def name(self) -> str:
2924        return "*"
2925
2926    @property
2927    def output_name(self):
2928        return self.name
2929
2930
2931class Parameter(Expression):
2932    arg_types = {"this": True, "wrapped": False}
2933
2934
2935class SessionParameter(Expression):
2936    arg_types = {"this": True, "kind": False}
2937
2938
2939class Placeholder(Expression):
2940    arg_types = {"this": False}
2941
2942
2943class Null(Condition):
2944    arg_types: t.Dict[str, t.Any] = {}
2945
2946    @property
2947    def name(self) -> str:
2948        return "NULL"
2949
2950
2951class Boolean(Condition):
2952    pass
2953
2954
2955class DataType(Expression):
2956    arg_types = {
2957        "this": True,
2958        "expressions": False,
2959        "nested": False,
2960        "values": False,
2961        "prefix": False,
2962    }
2963
2964    class Type(AutoName):
2965        CHAR = auto()
2966        NCHAR = auto()
2967        VARCHAR = auto()
2968        NVARCHAR = auto()
2969        TEXT = auto()
2970        MEDIUMTEXT = auto()
2971        LONGTEXT = auto()
2972        MEDIUMBLOB = auto()
2973        LONGBLOB = auto()
2974        BINARY = auto()
2975        VARBINARY = auto()
2976        INT = auto()
2977        UINT = auto()
2978        TINYINT = auto()
2979        UTINYINT = auto()
2980        SMALLINT = auto()
2981        USMALLINT = auto()
2982        BIGINT = auto()
2983        UBIGINT = auto()
2984        FLOAT = auto()
2985        DOUBLE = auto()
2986        DECIMAL = auto()
2987        BIGDECIMAL = auto()
2988        BIT = auto()
2989        BOOLEAN = auto()
2990        JSON = auto()
2991        JSONB = auto()
2992        INTERVAL = auto()
2993        TIME = auto()
2994        TIMESTAMP = auto()
2995        TIMESTAMPTZ = auto()
2996        TIMESTAMPLTZ = auto()
2997        DATE = auto()
2998        DATETIME = auto()
2999        ARRAY = auto()
3000        MAP = auto()
3001        UUID = auto()
3002        GEOGRAPHY = auto()
3003        GEOMETRY = auto()
3004        STRUCT = auto()
3005        NULLABLE = auto()
3006        HLLSKETCH = auto()
3007        HSTORE = auto()
3008        SUPER = auto()
3009        SERIAL = auto()
3010        SMALLSERIAL = auto()
3011        BIGSERIAL = auto()
3012        XML = auto()
3013        UNIQUEIDENTIFIER = auto()
3014        MONEY = auto()
3015        SMALLMONEY = auto()
3016        ROWVERSION = auto()
3017        IMAGE = auto()
3018        VARIANT = auto()
3019        OBJECT = auto()
3020        INET = auto()
3021        NULL = auto()
3022        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3023
3024    TEXT_TYPES = {
3025        Type.CHAR,
3026        Type.NCHAR,
3027        Type.VARCHAR,
3028        Type.NVARCHAR,
3029        Type.TEXT,
3030    }
3031
3032    INTEGER_TYPES = {
3033        Type.INT,
3034        Type.TINYINT,
3035        Type.SMALLINT,
3036        Type.BIGINT,
3037    }
3038
3039    FLOAT_TYPES = {
3040        Type.FLOAT,
3041        Type.DOUBLE,
3042    }
3043
3044    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
3045
3046    TEMPORAL_TYPES = {
3047        Type.TIMESTAMP,
3048        Type.TIMESTAMPTZ,
3049        Type.TIMESTAMPLTZ,
3050        Type.DATE,
3051        Type.DATETIME,
3052    }
3053
3054    @classmethod
3055    def build(
3056        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
3057    ) -> DataType:
3058        from sqlglot import parse_one
3059
3060        if isinstance(dtype, str):
3061            if dtype.upper() in cls.Type.__members__:
3062                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
3063            else:
3064                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3065            if data_type_exp is None:
3066                raise ValueError(f"Unparsable data type value: {dtype}")
3067        elif isinstance(dtype, DataType.Type):
3068            data_type_exp = DataType(this=dtype)
3069        elif isinstance(dtype, DataType):
3070            return dtype
3071        else:
3072            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3073        return DataType(**{**data_type_exp.args, **kwargs})
3074
3075    def is_type(self, dtype: DataType.Type) -> bool:
3076        return self.this == dtype
3077
3078
3079# https://www.postgresql.org/docs/15/datatype-pseudo.html
3080class PseudoType(Expression):
3081    pass
3082
3083
3084class StructKwarg(Expression):
3085    arg_types = {"this": True, "expression": True}
3086
3087
3088# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3089class SubqueryPredicate(Predicate):
3090    pass
3091
3092
3093class All(SubqueryPredicate):
3094    pass
3095
3096
3097class Any(SubqueryPredicate):
3098    pass
3099
3100
3101class Exists(SubqueryPredicate):
3102    pass
3103
3104
3105# Commands to interact with the databases or engines. For most of the command
3106# expressions we parse whatever comes after the command's name as a string.
3107class Command(Expression):
3108    arg_types = {"this": True, "expression": False}
3109
3110
3111class Transaction(Expression):
3112    arg_types = {"this": False, "modes": False}
3113
3114
3115class Commit(Expression):
3116    arg_types = {"chain": False}
3117
3118
3119class Rollback(Expression):
3120    arg_types = {"savepoint": False}
3121
3122
3123class AlterTable(Expression):
3124    arg_types = {"this": True, "actions": True, "exists": False}
3125
3126
3127class AddConstraint(Expression):
3128    arg_types = {"this": False, "expression": False, "enforced": False}
3129
3130
3131class DropPartition(Expression):
3132    arg_types = {"expressions": True, "exists": False}
3133
3134
3135# Binary expressions like (ADD a b)
3136class Binary(Condition):
3137    arg_types = {"this": True, "expression": True}
3138
3139    @property
3140    def left(self):
3141        return self.this
3142
3143    @property
3144    def right(self):
3145        return self.expression
3146
3147
3148class Add(Binary):
3149    pass
3150
3151
3152class Connector(Binary):
3153    pass
3154
3155
3156class And(Connector):
3157    pass
3158
3159
3160class Or(Connector):
3161    pass
3162
3163
3164class BitwiseAnd(Binary):
3165    pass
3166
3167
3168class BitwiseLeftShift(Binary):
3169    pass
3170
3171
3172class BitwiseOr(Binary):
3173    pass
3174
3175
3176class BitwiseRightShift(Binary):
3177    pass
3178
3179
3180class BitwiseXor(Binary):
3181    pass
3182
3183
3184class Div(Binary):
3185    pass
3186
3187
3188class Overlaps(Binary):
3189    pass
3190
3191
3192class Dot(Binary):
3193    @property
3194    def name(self) -> str:
3195        return self.expression.name
3196
3197    @classmethod
3198    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3199        """Build a Dot object with a sequence of expressions."""
3200        if len(expressions) < 2:
3201            raise ValueError(f"Dot requires >= 2 expressions.")
3202
3203        a, b, *expressions = expressions
3204        dot = Dot(this=a, expression=b)
3205
3206        for expression in expressions:
3207            dot = Dot(this=dot, expression=expression)
3208
3209        return dot
3210
3211
3212class DPipe(Binary):
3213    pass
3214
3215
3216class EQ(Binary, Predicate):
3217    pass
3218
3219
3220class NullSafeEQ(Binary, Predicate):
3221    pass
3222
3223
3224class NullSafeNEQ(Binary, Predicate):
3225    pass
3226
3227
3228class Distance(Binary):
3229    pass
3230
3231
3232class Escape(Binary):
3233    pass
3234
3235
3236class Glob(Binary, Predicate):
3237    pass
3238
3239
3240class GT(Binary, Predicate):
3241    pass
3242
3243
3244class GTE(Binary, Predicate):
3245    pass
3246
3247
3248class ILike(Binary, Predicate):
3249    pass
3250
3251
3252class ILikeAny(Binary, Predicate):
3253    pass
3254
3255
3256class IntDiv(Binary):
3257    pass
3258
3259
3260class Is(Binary, Predicate):
3261    pass
3262
3263
3264class Kwarg(Binary):
3265    """Kwarg in special functions like func(kwarg => y)."""
3266
3267
3268class Like(Binary, Predicate):
3269    pass
3270
3271
3272class LikeAny(Binary, Predicate):
3273    pass
3274
3275
3276class LT(Binary, Predicate):
3277    pass
3278
3279
3280class LTE(Binary, Predicate):
3281    pass
3282
3283
3284class Mod(Binary):
3285    pass
3286
3287
3288class Mul(Binary):
3289    pass
3290
3291
3292class NEQ(Binary, Predicate):
3293    pass
3294
3295
3296class SimilarTo(Binary, Predicate):
3297    pass
3298
3299
3300class Slice(Binary):
3301    arg_types = {"this": False, "expression": False}
3302
3303
3304class Sub(Binary):
3305    pass
3306
3307
3308class ArrayOverlaps(Binary):
3309    pass
3310
3311
3312# Unary Expressions
3313# (NOT a)
3314class Unary(Condition):
3315    pass
3316
3317
3318class BitwiseNot(Unary):
3319    pass
3320
3321
3322class Not(Unary):
3323    pass
3324
3325
3326class Paren(Unary):
3327    arg_types = {"this": True, "with": False}
3328
3329
3330class Neg(Unary):
3331    pass
3332
3333
3334class Alias(Expression):
3335    arg_types = {"this": True, "alias": False}
3336
3337    @property
3338    def output_name(self):
3339        return self.alias
3340
3341
3342class Aliases(Expression):
3343    arg_types = {"this": True, "expressions": True}
3344
3345    @property
3346    def aliases(self):
3347        return self.expressions
3348
3349
3350class AtTimeZone(Expression):
3351    arg_types = {"this": True, "zone": True}
3352
3353
3354class Between(Predicate):
3355    arg_types = {"this": True, "low": True, "high": True}
3356
3357
3358class Bracket(Condition):
3359    arg_types = {"this": True, "expressions": True}
3360
3361
3362class Distinct(Expression):
3363    arg_types = {"expressions": False, "on": False}
3364
3365
3366class In(Predicate):
3367    arg_types = {
3368        "this": True,
3369        "expressions": False,
3370        "query": False,
3371        "unnest": False,
3372        "field": False,
3373        "is_global": False,
3374    }
3375
3376
3377class TimeUnit(Expression):
3378    """Automatically converts unit arg into a var."""
3379
3380    arg_types = {"unit": False}
3381
3382    def __init__(self, **args):
3383        unit = args.get("unit")
3384        if isinstance(unit, (Column, Literal)):
3385            args["unit"] = Var(this=unit.name)
3386        elif isinstance(unit, Week):
3387            unit.set("this", Var(this=unit.this.name))
3388        super().__init__(**args)
3389
3390
3391class Interval(TimeUnit):
3392    arg_types = {"this": False, "unit": False}
3393
3394
3395class IgnoreNulls(Expression):
3396    pass
3397
3398
3399class RespectNulls(Expression):
3400    pass
3401
3402
3403# Functions
3404class Func(Condition):
3405    """
3406    The base class for all function expressions.
3407
3408    Attributes:
3409        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3410            treated as a variable length argument and the argument's value will be stored as a list.
3411        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3412            for this function expression. These values are used to map this node to a name during parsing
3413            as well as to provide the function's name during SQL string generation. By default the SQL
3414            name is set to the expression's class name transformed to snake case.
3415    """
3416
3417    is_var_len_args = False
3418
3419    @classmethod
3420    def from_arg_list(cls, args):
3421        if cls.is_var_len_args:
3422            all_arg_keys = list(cls.arg_types)
3423            # If this function supports variable length argument treat the last argument as such.
3424            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3425            num_non_var = len(non_var_len_arg_keys)
3426
3427            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3428            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3429        else:
3430            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3431
3432        return cls(**args_dict)
3433
3434    @classmethod
3435    def sql_names(cls):
3436        if cls is Func:
3437            raise NotImplementedError(
3438                "SQL name is only supported by concrete function implementations"
3439            )
3440        if "_sql_names" not in cls.__dict__:
3441            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3442        return cls._sql_names
3443
3444    @classmethod
3445    def sql_name(cls):
3446        return cls.sql_names()[0]
3447
3448    @classmethod
3449    def default_parser_mappings(cls):
3450        return {name: cls.from_arg_list for name in cls.sql_names()}
3451
3452
3453class AggFunc(Func):
3454    pass
3455
3456
3457class Abs(Func):
3458    pass
3459
3460
3461class Anonymous(Func):
3462    arg_types = {"this": True, "expressions": False}
3463    is_var_len_args = True
3464
3465
3466# https://docs.snowflake.com/en/sql-reference/functions/hll
3467# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
3468class Hll(AggFunc):
3469    arg_types = {"this": True, "expressions": False}
3470    is_var_len_args = True
3471
3472
3473class ApproxDistinct(AggFunc):
3474    arg_types = {"this": True, "accuracy": False}
3475
3476
3477class Array(Func):
3478    arg_types = {"expressions": False}
3479    is_var_len_args = True
3480
3481
3482# https://docs.snowflake.com/en/sql-reference/functions/to_char
3483class ToChar(Func):
3484    arg_types = {"this": True, "format": False}
3485
3486
3487class GenerateSeries(Func):
3488    arg_types = {"start": True, "end": True, "step": False}
3489
3490
3491class ArrayAgg(AggFunc):
3492    pass
3493
3494
3495class ArrayAll(Func):
3496    arg_types = {"this": True, "expression": True}
3497
3498
3499class ArrayAny(Func):
3500    arg_types = {"this": True, "expression": True}
3501
3502
3503class ArrayConcat(Func):
3504    arg_types = {"this": True, "expressions": False}
3505    is_var_len_args = True
3506
3507
3508class ArrayContains(Binary, Func):
3509    pass
3510
3511
3512class ArrayContained(Binary):
3513    pass
3514
3515
3516class ArrayFilter(Func):
3517    arg_types = {"this": True, "expression": True}
3518    _sql_names = ["FILTER", "ARRAY_FILTER"]
3519
3520
3521class ArrayJoin(Func):
3522    arg_types = {"this": True, "expression": True, "null": False}
3523
3524
3525class ArraySize(Func):
3526    arg_types = {"this": True, "expression": False}
3527
3528
3529class ArraySort(Func):
3530    arg_types = {"this": True, "expression": False}
3531
3532
3533class ArraySum(Func):
3534    pass
3535
3536
3537class ArrayUnionAgg(AggFunc):
3538    pass
3539
3540
3541class Avg(AggFunc):
3542    pass
3543
3544
3545class AnyValue(AggFunc):
3546    pass
3547
3548
3549class Case(Func):
3550    arg_types = {"this": False, "ifs": True, "default": False}
3551
3552    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
3553        instance = _maybe_copy(self, copy)
3554        instance.append(
3555            "ifs",
3556            If(
3557                this=maybe_parse(condition, copy=copy, **opts),
3558                true=maybe_parse(then, copy=copy, **opts),
3559            ),
3560        )
3561        return instance
3562
3563    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
3564        instance = _maybe_copy(self, copy)
3565        instance.set("default", maybe_parse(condition, copy=copy, **opts))
3566        return instance
3567
3568
3569class Cast(Func):
3570    arg_types = {"this": True, "to": True}
3571
3572    @property
3573    def name(self) -> str:
3574        return self.this.name
3575
3576    @property
3577    def to(self):
3578        return self.args["to"]
3579
3580    @property
3581    def output_name(self):
3582        return self.name
3583
3584    def is_type(self, dtype: DataType.Type) -> bool:
3585        return self.to.is_type(dtype)
3586
3587
3588class Collate(Binary):
3589    pass
3590
3591
3592class TryCast(Cast):
3593    pass
3594
3595
3596class Ceil(Func):
3597    arg_types = {"this": True, "decimals": False}
3598    _sql_names = ["CEIL", "CEILING"]
3599
3600
3601class Coalesce(Func):
3602    arg_types = {"this": True, "expressions": False}
3603    is_var_len_args = True
3604
3605
3606class Concat(Func):
3607    arg_types = {"expressions": True}
3608    is_var_len_args = True
3609
3610
3611class ConcatWs(Concat):
3612    _sql_names = ["CONCAT_WS"]
3613
3614
3615class Count(AggFunc):
3616    arg_types = {"this": False}
3617
3618
3619class CountIf(AggFunc):
3620    pass
3621
3622
3623class CurrentDate(Func):
3624    arg_types = {"this": False}
3625
3626
3627class CurrentDatetime(Func):
3628    arg_types = {"this": False}
3629
3630
3631class CurrentTime(Func):
3632    arg_types = {"this": False}
3633
3634
3635class CurrentTimestamp(Func):
3636    arg_types = {"this": False}
3637
3638
3639class CurrentUser(Func):
3640    arg_types = {"this": False}
3641
3642
3643class DateAdd(Func, TimeUnit):
3644    arg_types = {"this": True, "expression": True, "unit": False}
3645
3646
3647class DateSub(Func, TimeUnit):
3648    arg_types = {"this": True, "expression": True, "unit": False}
3649
3650
3651class DateDiff(Func, TimeUnit):
3652    _sql_names = ["DATEDIFF", "DATE_DIFF"]
3653    arg_types = {"this": True, "expression": True, "unit": False}
3654
3655
3656class DateTrunc(Func):
3657    arg_types = {"unit": True, "this": True, "zone": False}
3658
3659
3660class DatetimeAdd(Func, TimeUnit):
3661    arg_types = {"this": True, "expression": True, "unit": False}
3662
3663
3664class DatetimeSub(Func, TimeUnit):
3665    arg_types = {"this": True, "expression": True, "unit": False}
3666
3667
3668class DatetimeDiff(Func, TimeUnit):
3669    arg_types = {"this": True, "expression": True, "unit": False}
3670
3671
3672class DatetimeTrunc(Func, TimeUnit):
3673    arg_types = {"this": True, "unit": True, "zone": False}
3674
3675
3676class DayOfWeek(Func):
3677    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3678
3679
3680class DayOfMonth(Func):
3681    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3682
3683
3684class DayOfYear(Func):
3685    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3686
3687
3688class WeekOfYear(Func):
3689    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3690
3691
3692class LastDateOfMonth(Func):
3693    pass
3694
3695
3696class Extract(Func):
3697    arg_types = {"this": True, "expression": True}
3698
3699
3700class TimestampAdd(Func, TimeUnit):
3701    arg_types = {"this": True, "expression": True, "unit": False}
3702
3703
3704class TimestampSub(Func, TimeUnit):
3705    arg_types = {"this": True, "expression": True, "unit": False}
3706
3707
3708class TimestampDiff(Func, TimeUnit):
3709    arg_types = {"this": True, "expression": True, "unit": False}
3710
3711
3712class TimestampTrunc(Func, TimeUnit):
3713    arg_types = {"this": True, "unit": True, "zone": False}
3714
3715
3716class TimeAdd(Func, TimeUnit):
3717    arg_types = {"this": True, "expression": True, "unit": False}
3718
3719
3720class TimeSub(Func, TimeUnit):
3721    arg_types = {"this": True, "expression": True, "unit": False}
3722
3723
3724class TimeDiff(Func, TimeUnit):
3725    arg_types = {"this": True, "expression": True, "unit": False}
3726
3727
3728class TimeTrunc(Func, TimeUnit):
3729    arg_types = {"this": True, "unit": True, "zone": False}
3730
3731
3732class DateFromParts(Func):
3733    _sql_names = ["DATEFROMPARTS"]
3734    arg_types = {"year": True, "month": True, "day": True}
3735
3736
3737class DateStrToDate(Func):
3738    pass
3739
3740
3741class DateToDateStr(Func):
3742    pass
3743
3744
3745class DateToDi(Func):
3746    pass
3747
3748
3749class Day(Func):
3750    pass
3751
3752
3753class Decode(Func):
3754    arg_types = {"this": True, "charset": True, "replace": False}
3755
3756
3757class DiToDate(Func):
3758    pass
3759
3760
3761class Encode(Func):
3762    arg_types = {"this": True, "charset": True}
3763
3764
3765class Exp(Func):
3766    pass
3767
3768
3769class Explode(Func):
3770    pass
3771
3772
3773class ExponentialTimeDecayedAvg(AggFunc):
3774    arg_types = {"this": True, "time": False, "decay": False}
3775
3776
3777class Floor(Func):
3778    arg_types = {"this": True, "decimals": False}
3779
3780
3781class Greatest(Func):
3782    arg_types = {"this": True, "expressions": False}
3783    is_var_len_args = True
3784
3785
3786class GroupConcat(Func):
3787    arg_types = {"this": True, "separator": False}
3788
3789
3790class GroupUniqArray(AggFunc):
3791    arg_types = {"this": True, "size": False}
3792
3793
3794class Hex(Func):
3795    pass
3796
3797
3798class Histogram(AggFunc):
3799    arg_types = {"this": True, "bins": False}
3800
3801
3802class If(Func):
3803    arg_types = {"this": True, "true": True, "false": False}
3804
3805
3806class IfNull(Func):
3807    arg_types = {"this": True, "expression": False}
3808    _sql_names = ["IFNULL", "NVL"]
3809
3810
3811class Initcap(Func):
3812    pass
3813
3814
3815class JSONKeyValue(Expression):
3816    arg_types = {"this": True, "expression": True}
3817
3818
3819class JSONObject(Func):
3820    arg_types = {
3821        "expressions": False,
3822        "null_handling": False,
3823        "unique_keys": False,
3824        "return_type": False,
3825        "format_json": False,
3826        "encoding": False,
3827    }
3828
3829
3830class JSONBContains(Binary):
3831    _sql_names = ["JSONB_CONTAINS"]
3832
3833
3834class JSONExtract(Binary, Func):
3835    _sql_names = ["JSON_EXTRACT"]
3836
3837
3838class JSONExtractScalar(JSONExtract):
3839    _sql_names = ["JSON_EXTRACT_SCALAR"]
3840
3841
3842class JSONBExtract(JSONExtract):
3843    _sql_names = ["JSONB_EXTRACT"]
3844
3845
3846class JSONBExtractScalar(JSONExtract):
3847    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3848
3849
3850class JSONFormat(Func):
3851    arg_types = {"this": False, "options": False}
3852    _sql_names = ["JSON_FORMAT"]
3853
3854
3855class Least(Func):
3856    arg_types = {"expressions": False}
3857    is_var_len_args = True
3858
3859
3860class Length(Func):
3861    pass
3862
3863
3864class Levenshtein(Func):
3865    arg_types = {
3866        "this": True,
3867        "expression": False,
3868        "ins_cost": False,
3869        "del_cost": False,
3870        "sub_cost": False,
3871    }
3872
3873
3874class Ln(Func):
3875    pass
3876
3877
3878class Log(Func):
3879    arg_types = {"this": True, "expression": False}
3880
3881
3882class Log2(Func):
3883    pass
3884
3885
3886class Log10(Func):
3887    pass
3888
3889
3890class LogicalOr(AggFunc):
3891    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
3892
3893
3894class LogicalAnd(AggFunc):
3895    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
3896
3897
3898class Lower(Func):
3899    _sql_names = ["LOWER", "LCASE"]
3900
3901
3902class Map(Func):
3903    arg_types = {"keys": False, "values": False}
3904
3905
3906class StarMap(Func):
3907    pass
3908
3909
3910class VarMap(Func):
3911    arg_types = {"keys": True, "values": True}
3912    is_var_len_args = True
3913
3914
3915# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
3916class MatchAgainst(Func):
3917    arg_types = {"this": True, "expressions": True, "modifier": False}
3918
3919
3920class Max(AggFunc):
3921    arg_types = {"this": True, "expressions": False}
3922    is_var_len_args = True
3923
3924
3925class MD5(Func):
3926    _sql_names = ["MD5"]
3927
3928
3929class Min(AggFunc):
3930    arg_types = {"this": True, "expressions": False}
3931    is_var_len_args = True
3932
3933
3934class Month(Func):
3935    pass
3936
3937
3938class Nvl2(Func):
3939    arg_types = {"this": True, "true": True, "false": False}
3940
3941
3942class Posexplode(Func):
3943    pass
3944
3945
3946class Pow(Binary, Func):
3947    _sql_names = ["POWER", "POW"]
3948
3949
3950class PercentileCont(AggFunc):
3951    arg_types = {"this": True, "expression": False}
3952
3953
3954class PercentileDisc(AggFunc):
3955    arg_types = {"this": True, "expression": False}
3956
3957
3958class Quantile(AggFunc):
3959    arg_types = {"this": True, "quantile": True}
3960
3961
3962# Clickhouse-specific:
3963# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3964class Quantiles(AggFunc):
3965    arg_types = {"parameters": True, "expressions": True}
3966    is_var_len_args = True
3967
3968
3969class QuantileIf(AggFunc):
3970    arg_types = {"parameters": True, "expressions": True}
3971
3972
3973class ApproxQuantile(Quantile):
3974    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3975
3976
3977class RangeN(Func):
3978    arg_types = {"this": True, "expressions": True, "each": False}
3979
3980
3981class ReadCSV(Func):
3982    _sql_names = ["READ_CSV"]
3983    is_var_len_args = True
3984    arg_types = {"this": True, "expressions": False}
3985
3986
3987class Reduce(Func):
3988    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3989
3990
3991class RegexpExtract(Func):
3992    arg_types = {
3993        "this": True,
3994        "expression": True,
3995        "position": False,
3996        "occurrence": False,
3997        "group": False,
3998    }
3999
4000
4001class RegexpLike(Func):
4002    arg_types = {"this": True, "expression": True, "flag": False}
4003
4004
4005class RegexpILike(Func):
4006    arg_types = {"this": True, "expression": True, "flag": False}
4007
4008
4009# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4010# limit is the number of times a pattern is applied
4011class RegexpSplit(Func):
4012    arg_types = {"this": True, "expression": True, "limit": False}
4013
4014
4015class Repeat(Func):
4016    arg_types = {"this": True, "times": True}
4017
4018
4019class Round(Func):
4020    arg_types = {"this": True, "decimals": False}
4021
4022
4023class RowNumber(Func):
4024    arg_types: t.Dict[str, t.Any] = {}
4025
4026
4027class SafeDivide(Func):
4028    arg_types = {"this": True, "expression": True}
4029
4030
4031class SetAgg(AggFunc):
4032    pass
4033
4034
4035class SHA(Func):
4036    _sql_names = ["SHA", "SHA1"]
4037
4038
4039class SHA2(Func):
4040    _sql_names = ["SHA2"]
4041    arg_types = {"this": True, "length": False}
4042
4043
4044class SortArray(Func):
4045    arg_types = {"this": True, "asc": False}
4046
4047
4048class Split(Func):
4049    arg_types = {"this": True, "expression": True, "limit": False}
4050
4051
4052# Start may be omitted in the case of postgres
4053# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4054class Substring(Func):
4055    arg_types = {"this": True, "start": False, "length": False}
4056
4057
4058class StrPosition(Func):
4059    arg_types = {
4060        "this": True,
4061        "substr": True,
4062        "position": False,
4063        "instance": False,
4064    }
4065
4066
4067class StrToDate(Func):
4068    arg_types = {"this": True, "format": True}
4069
4070
4071class StrToTime(Func):
4072    arg_types = {"this": True, "format": True}
4073
4074
4075# Spark allows unix_timestamp()
4076# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4077class StrToUnix(Func):
4078    arg_types = {"this": False, "format": False}
4079
4080
4081class NumberToStr(Func):
4082    arg_types = {"this": True, "format": True}
4083
4084
4085class Struct(Func):
4086    arg_types = {"expressions": True}
4087    is_var_len_args = True
4088
4089
4090class StructExtract(Func):
4091    arg_types = {"this": True, "expression": True}
4092
4093
4094class Sum(AggFunc):
4095    pass
4096
4097
4098class Sqrt(Func):
4099    pass
4100
4101
4102class Stddev(AggFunc):
4103    pass
4104
4105
4106class StddevPop(AggFunc):
4107    pass
4108
4109
4110class StddevSamp(AggFunc):
4111    pass
4112
4113
4114class TimeToStr(Func):
4115    arg_types = {"this": True, "format": True}
4116
4117
4118class TimeToTimeStr(Func):
4119    pass
4120
4121
4122class TimeToUnix(Func):
4123    pass
4124
4125
4126class TimeStrToDate(Func):
4127    pass
4128
4129
4130class TimeStrToTime(Func):
4131    pass
4132
4133
4134class TimeStrToUnix(Func):
4135    pass
4136
4137
4138class Trim(Func):
4139    arg_types = {
4140        "this": True,
4141        "expression": False,
4142        "position": False,
4143        "collation": False,
4144    }
4145
4146
4147class TsOrDsAdd(Func, TimeUnit):
4148    arg_types = {"this": True, "expression": True, "unit": False}
4149
4150
4151class TsOrDsToDateStr(Func):
4152    pass
4153
4154
4155class TsOrDsToDate(Func):
4156    arg_types = {"this": True, "format": False}
4157
4158
4159class TsOrDiToDi(Func):
4160    pass
4161
4162
4163class Unhex(Func):
4164    pass
4165
4166
4167class UnixToStr(Func):
4168    arg_types = {"this": True, "format": False}
4169
4170
4171# https://prestodb.io/docs/current/functions/datetime.html
4172# presto has weird zone/hours/minutes
4173class UnixToTime(Func):
4174    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
4175
4176    SECONDS = Literal.string("seconds")
4177    MILLIS = Literal.string("millis")
4178    MICROS = Literal.string("micros")
4179
4180
4181class UnixToTimeStr(Func):
4182    pass
4183
4184
4185class Upper(Func):
4186    _sql_names = ["UPPER", "UCASE"]
4187
4188
4189class Variance(AggFunc):
4190    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4191
4192
4193class VariancePop(AggFunc):
4194    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4195
4196
4197class Week(Func):
4198    arg_types = {"this": True, "mode": False}
4199
4200
4201class XMLTable(Func):
4202    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4203
4204
4205class Year(Func):
4206    pass
4207
4208
4209class Use(Expression):
4210    arg_types = {"this": True, "kind": False}
4211
4212
4213class Merge(Expression):
4214    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4215
4216
4217class When(Func):
4218    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4219
4220
4221# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
4222# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
4223class NextValueFor(Func):
4224    arg_types = {"this": True, "order": False}
4225
4226
4227def _norm_arg(arg):
4228    return arg.lower() if type(arg) is str else arg
4229
4230
4231ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4232
4233
4234# Helpers
4235@t.overload
4236def maybe_parse(
4237    sql_or_expression: ExpOrStr,
4238    *,
4239    into: t.Type[E],
4240    dialect: DialectType = None,
4241    prefix: t.Optional[str] = None,
4242    copy: bool = False,
4243    **opts,
4244) -> E:
4245    ...
4246
4247
4248@t.overload
4249def maybe_parse(
4250    sql_or_expression: str | E,
4251    *,
4252    into: t.Optional[IntoType] = None,
4253    dialect: DialectType = None,
4254    prefix: t.Optional[str] = None,
4255    copy: bool = False,
4256    **opts,
4257) -> E:
4258    ...
4259
4260
4261def maybe_parse(
4262    sql_or_expression: ExpOrStr,
4263    *,
4264    into: t.Optional[IntoType] = None,
4265    dialect: DialectType = None,
4266    prefix: t.Optional[str] = None,
4267    copy: bool = False,
4268    **opts,
4269) -> Expression:
4270    """Gracefully handle a possible string or expression.
4271
4272    Example:
4273        >>> maybe_parse("1")
4274        (LITERAL this: 1, is_string: False)
4275        >>> maybe_parse(to_identifier("x"))
4276        (IDENTIFIER this: x, quoted: False)
4277
4278    Args:
4279        sql_or_expression: the SQL code string or an expression
4280        into: the SQLGlot Expression to parse into
4281        dialect: the dialect used to parse the input expressions (in the case that an
4282            input expression is a SQL string).
4283        prefix: a string to prefix the sql with before it gets parsed
4284            (automatically includes a space)
4285        copy: whether or not to copy the expression.
4286        **opts: other options to use to parse the input expressions (again, in the case
4287            that an input expression is a SQL string).
4288
4289    Returns:
4290        Expression: the parsed or given expression.
4291    """
4292    if isinstance(sql_or_expression, Expression):
4293        if copy:
4294            return sql_or_expression.copy()
4295        return sql_or_expression
4296
4297    import sqlglot
4298
4299    sql = str(sql_or_expression)
4300    if prefix:
4301        sql = f"{prefix} {sql}"
4302    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
4303
4304
4305def _maybe_copy(instance, copy=True):
4306    return instance.copy() if copy else instance
4307
4308
4309def _is_wrong_expression(expression, into):
4310    return isinstance(expression, Expression) and not isinstance(expression, into)
4311
4312
4313def _apply_builder(
4314    expression,
4315    instance,
4316    arg,
4317    copy=True,
4318    prefix=None,
4319    into=None,
4320    dialect=None,
4321    **opts,
4322):
4323    if _is_wrong_expression(expression, into):
4324        expression = into(this=expression)
4325    instance = _maybe_copy(instance, copy)
4326    expression = maybe_parse(
4327        sql_or_expression=expression,
4328        prefix=prefix,
4329        into=into,
4330        dialect=dialect,
4331        **opts,
4332    )
4333    instance.set(arg, expression)
4334    return instance
4335
4336
4337def _apply_child_list_builder(
4338    *expressions,
4339    instance,
4340    arg,
4341    append=True,
4342    copy=True,
4343    prefix=None,
4344    into=None,
4345    dialect=None,
4346    properties=None,
4347    **opts,
4348):
4349    instance = _maybe_copy(instance, copy)
4350    parsed = []
4351    for expression in expressions:
4352        if _is_wrong_expression(expression, into):
4353            expression = into(expressions=[expression])
4354        expression = maybe_parse(
4355            expression,
4356            into=into,
4357            dialect=dialect,
4358            prefix=prefix,
4359            **opts,
4360        )
4361        parsed.extend(expression.expressions)
4362
4363    existing = instance.args.get(arg)
4364    if append and existing:
4365        parsed = existing.expressions + parsed
4366
4367    child = into(expressions=parsed)
4368    for k, v in (properties or {}).items():
4369        child.set(k, v)
4370    instance.set(arg, child)
4371    return instance
4372
4373
4374def _apply_list_builder(
4375    *expressions,
4376    instance,
4377    arg,
4378    append=True,
4379    copy=True,
4380    prefix=None,
4381    into=None,
4382    dialect=None,
4383    **opts,
4384):
4385    inst = _maybe_copy(instance, copy)
4386
4387    expressions = [
4388        maybe_parse(
4389            sql_or_expression=expression,
4390            into=into,
4391            prefix=prefix,
4392            dialect=dialect,
4393            **opts,
4394        )
4395        for expression in expressions
4396    ]
4397
4398    existing_expressions = inst.args.get(arg)
4399    if append and existing_expressions:
4400        expressions = existing_expressions + expressions
4401
4402    inst.set(arg, expressions)
4403    return inst
4404
4405
4406def _apply_conjunction_builder(
4407    *expressions,
4408    instance,
4409    arg,
4410    into=None,
4411    append=True,
4412    copy=True,
4413    dialect=None,
4414    **opts,
4415):
4416    expressions = [exp for exp in expressions if exp is not None and exp != ""]
4417    if not expressions:
4418        return instance
4419
4420    inst = _maybe_copy(instance, copy)
4421
4422    existing = inst.args.get(arg)
4423    if append and existing is not None:
4424        expressions = [existing.this if into else existing] + list(expressions)
4425
4426    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
4427
4428    inst.set(arg, into(this=node) if into else node)
4429    return inst
4430
4431
4432def _combine(expressions, operator, dialect=None, copy=True, **opts):
4433    expressions = [
4434        condition(expression, dialect=dialect, copy=copy, **opts) for expression in expressions
4435    ]
4436    this = expressions[0]
4437    if expressions[1:]:
4438        this = _wrap(this, Connector)
4439    for expression in expressions[1:]:
4440        this = operator(this=this, expression=_wrap(expression, Connector))
4441    return this
4442
4443
4444def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
4445    if isinstance(expression, kind):
4446        return Paren(this=expression)
4447    return expression
4448
4449
4450def union(left, right, distinct=True, dialect=None, **opts):
4451    """
4452    Initializes a syntax tree from one UNION expression.
4453
4454    Example:
4455        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
4456        'SELECT * FROM foo UNION SELECT * FROM bla'
4457
4458    Args:
4459        left (str | Expression): the SQL code string corresponding to the left-hand side.
4460            If an `Expression` instance is passed, it will be used as-is.
4461        right (str | Expression): the SQL code string corresponding to the right-hand side.
4462            If an `Expression` instance is passed, it will be used as-is.
4463        distinct (bool): set the DISTINCT flag if and only if this is true.
4464        dialect (str): the dialect used to parse the input expression.
4465        opts (kwargs): other options to use to parse the input expressions.
4466    Returns:
4467        Union: the syntax tree for the UNION expression.
4468    """
4469    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4470    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4471
4472    return Union(this=left, expression=right, distinct=distinct)
4473
4474
4475def intersect(left, right, distinct=True, dialect=None, **opts):
4476    """
4477    Initializes a syntax tree from one INTERSECT expression.
4478
4479    Example:
4480        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4481        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4482
4483    Args:
4484        left (str | Expression): the SQL code string corresponding to the left-hand side.
4485            If an `Expression` instance is passed, it will be used as-is.
4486        right (str | Expression): the SQL code string corresponding to the right-hand side.
4487            If an `Expression` instance is passed, it will be used as-is.
4488        distinct (bool): set the DISTINCT flag if and only if this is true.
4489        dialect (str): the dialect used to parse the input expression.
4490        opts (kwargs): other options to use to parse the input expressions.
4491    Returns:
4492        Intersect: the syntax tree for the INTERSECT expression.
4493    """
4494    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4495    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4496
4497    return Intersect(this=left, expression=right, distinct=distinct)
4498
4499
4500def except_(left, right, distinct=True, dialect=None, **opts):
4501    """
4502    Initializes a syntax tree from one EXCEPT expression.
4503
4504    Example:
4505        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4506        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4507
4508    Args:
4509        left (str | Expression): the SQL code string corresponding to the left-hand side.
4510            If an `Expression` instance is passed, it will be used as-is.
4511        right (str | Expression): the SQL code string corresponding to the right-hand side.
4512            If an `Expression` instance is passed, it will be used as-is.
4513        distinct (bool): set the DISTINCT flag if and only if this is true.
4514        dialect (str): the dialect used to parse the input expression.
4515        opts (kwargs): other options to use to parse the input expressions.
4516    Returns:
4517        Except: the syntax tree for the EXCEPT statement.
4518    """
4519    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4520    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4521
4522    return Except(this=left, expression=right, distinct=distinct)
4523
4524
4525def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
4526    """
4527    Initializes a syntax tree from one or multiple SELECT expressions.
4528
4529    Example:
4530        >>> select("col1", "col2").from_("tbl").sql()
4531        'SELECT col1, col2 FROM tbl'
4532
4533    Args:
4534        *expressions: the SQL code string to parse as the expressions of a
4535            SELECT statement. If an Expression instance is passed, this is used as-is.
4536        dialect: the dialect used to parse the input expressions (in the case that an
4537            input expression is a SQL string).
4538        **opts: other options to use to parse the input expressions (again, in the case
4539            that an input expression is a SQL string).
4540
4541    Returns:
4542        Select: the syntax tree for the SELECT statement.
4543    """
4544    return Select().select(*expressions, dialect=dialect, **opts)
4545
4546
4547def from_(*expressions, dialect=None, **opts) -> Select:
4548    """
4549    Initializes a syntax tree from a FROM expression.
4550
4551    Example:
4552        >>> from_("tbl").select("col1", "col2").sql()
4553        'SELECT col1, col2 FROM tbl'
4554
4555    Args:
4556        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4557            SELECT statement. If an Expression instance is passed, this is used as-is.
4558        dialect (str): the dialect used to parse the input expression (in the case that the
4559            input expression is a SQL string).
4560        **opts: other options to use to parse the input expressions (again, in the case
4561            that the input expression is a SQL string).
4562
4563    Returns:
4564        Select: the syntax tree for the SELECT statement.
4565    """
4566    return Select().from_(*expressions, dialect=dialect, **opts)
4567
4568
4569def update(
4570    table: str | Table,
4571    properties: dict,
4572    where: t.Optional[ExpOrStr] = None,
4573    from_: t.Optional[ExpOrStr] = None,
4574    dialect: DialectType = None,
4575    **opts,
4576) -> Update:
4577    """
4578    Creates an update statement.
4579
4580    Example:
4581        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4582        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4583
4584    Args:
4585        *properties: dictionary of properties to set which are
4586            auto converted to sql objects eg None -> NULL
4587        where: sql conditional parsed into a WHERE statement
4588        from_: sql statement parsed into a FROM statement
4589        dialect: the dialect used to parse the input expressions.
4590        **opts: other options to use to parse the input expressions.
4591
4592    Returns:
4593        Update: the syntax tree for the UPDATE statement.
4594    """
4595    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4596    update_expr.set(
4597        "expressions",
4598        [
4599            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4600            for k, v in properties.items()
4601        ],
4602    )
4603    if from_:
4604        update_expr.set(
4605            "from",
4606            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4607        )
4608    if isinstance(where, Condition):
4609        where = Where(this=where)
4610    if where:
4611        update_expr.set(
4612            "where",
4613            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4614        )
4615    return update_expr
4616
4617
4618def delete(
4619    table: ExpOrStr,
4620    where: t.Optional[ExpOrStr] = None,
4621    returning: t.Optional[ExpOrStr] = None,
4622    dialect: DialectType = None,
4623    **opts,
4624) -> Delete:
4625    """
4626    Builds a delete statement.
4627
4628    Example:
4629        >>> delete("my_table", where="id > 1").sql()
4630        'DELETE FROM my_table WHERE id > 1'
4631
4632    Args:
4633        where: sql conditional parsed into a WHERE statement
4634        returning: sql conditional parsed into a RETURNING statement
4635        dialect: the dialect used to parse the input expressions.
4636        **opts: other options to use to parse the input expressions.
4637
4638    Returns:
4639        Delete: the syntax tree for the DELETE statement.
4640    """
4641    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
4642    if where:
4643        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
4644    if returning:
4645        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
4646    return delete_expr
4647
4648
4649def condition(expression, dialect=None, copy=True, **opts) -> Condition:
4650    """
4651    Initialize a logical condition expression.
4652
4653    Example:
4654        >>> condition("x=1").sql()
4655        'x = 1'
4656
4657        This is helpful for composing larger logical syntax trees:
4658        >>> where = condition("x=1")
4659        >>> where = where.and_("y=1")
4660        >>> Select().from_("tbl").select("*").where(where).sql()
4661        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4662
4663    Args:
4664        *expression (str | Expression): the SQL code string to parse.
4665            If an Expression instance is passed, this is used as-is.
4666        dialect (str): the dialect used to parse the input expression (in the case that the
4667            input expression is a SQL string).
4668        copy (bool): Whether or not to copy `expression` (only applies to expressions).
4669        **opts: other options to use to parse the input expressions (again, in the case
4670            that the input expression is a SQL string).
4671
4672    Returns:
4673        Condition: the expression
4674    """
4675    return maybe_parse(  # type: ignore
4676        expression,
4677        into=Condition,
4678        dialect=dialect,
4679        copy=copy,
4680        **opts,
4681    )
4682
4683
4684def and_(*expressions, dialect=None, copy=True, **opts) -> And:
4685    """
4686    Combine multiple conditions with an AND logical operator.
4687
4688    Example:
4689        >>> and_("x=1", and_("y=1", "z=1")).sql()
4690        'x = 1 AND (y = 1 AND z = 1)'
4691
4692    Args:
4693        *expressions (str | Expression): the SQL code strings to parse.
4694            If an Expression instance is passed, this is used as-is.
4695        dialect (str): the dialect used to parse the input expression.
4696        copy (bool): whether or not to copy `expressions` (only applies to Expressions).
4697        **opts: other options to use to parse the input expressions.
4698
4699    Returns:
4700        And: the new condition
4701    """
4702    return _combine(expressions, And, dialect, copy=copy, **opts)
4703
4704
4705def or_(*expressions, dialect=None, copy=True, **opts) -> Or:
4706    """
4707    Combine multiple conditions with an OR logical operator.
4708
4709    Example:
4710        >>> or_("x=1", or_("y=1", "z=1")).sql()
4711        'x = 1 OR (y = 1 OR z = 1)'
4712
4713    Args:
4714        *expressions (str | Expression): the SQL code strings to parse.
4715            If an Expression instance is passed, this is used as-is.
4716        dialect (str): the dialect used to parse the input expression.
4717        copy (bool): whether or not to copy `expressions` (only applies to Expressions).
4718        **opts: other options to use to parse the input expressions.
4719
4720    Returns:
4721        Or: the new condition
4722    """
4723    return _combine(expressions, Or, dialect, copy=copy, **opts)
4724
4725
4726def not_(expression, dialect=None, copy=True, **opts) -> Not:
4727    """
4728    Wrap a condition with a NOT operator.
4729
4730    Example:
4731        >>> not_("this_suit='black'").sql()
4732        "NOT this_suit = 'black'"
4733
4734    Args:
4735        expression (str | Expression): the SQL code strings to parse.
4736            If an Expression instance is passed, this is used as-is.
4737        dialect (str): the dialect used to parse the input expression.
4738        **opts: other options to use to parse the input expressions.
4739
4740    Returns:
4741        Not: the new condition
4742    """
4743    this = condition(
4744        expression,
4745        dialect=dialect,
4746        copy=copy,
4747        **opts,
4748    )
4749    return Not(this=_wrap(this, Connector))
4750
4751
4752def paren(expression, copy=True) -> Paren:
4753    return Paren(this=_maybe_copy(expression, copy))
4754
4755
4756SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4757
4758
4759@t.overload
4760def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4761    ...
4762
4763
4764@t.overload
4765def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4766    ...
4767
4768
4769def to_identifier(name, quoted=None):
4770    """Builds an identifier.
4771
4772    Args:
4773        name: The name to turn into an identifier.
4774        quoted: Whether or not force quote the identifier.
4775
4776    Returns:
4777        The identifier ast node.
4778    """
4779
4780    if name is None:
4781        return None
4782
4783    if isinstance(name, Identifier):
4784        identifier = name
4785    elif isinstance(name, str):
4786        identifier = Identifier(
4787            this=name,
4788            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
4789        )
4790    else:
4791        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4792    return identifier
4793
4794
4795INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4796
4797
4798def to_interval(interval: str | Literal) -> Interval:
4799    """Builds an interval expression from a string like '1 day' or '5 months'."""
4800    if isinstance(interval, Literal):
4801        if not interval.is_string:
4802            raise ValueError("Invalid interval string.")
4803
4804        interval = interval.this
4805
4806    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4807
4808    if not interval_parts:
4809        raise ValueError("Invalid interval string.")
4810
4811    return Interval(
4812        this=Literal.string(interval_parts.group(1)),
4813        unit=Var(this=interval_parts.group(2)),
4814    )
4815
4816
4817@t.overload
4818def to_table(sql_path: str | Table, **kwargs) -> Table:
4819    ...
4820
4821
4822@t.overload
4823def to_table(sql_path: None, **kwargs) -> None:
4824    ...
4825
4826
4827def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4828    """
4829    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4830    If a table is passed in then that table is returned.
4831
4832    Args:
4833        sql_path: a `[catalog].[schema].[table]` string.
4834
4835    Returns:
4836        A table expression.
4837    """
4838    if sql_path is None or isinstance(sql_path, Table):
4839        return sql_path
4840    if not isinstance(sql_path, str):
4841        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4842
4843    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4844    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4845
4846
4847def to_column(sql_path: str | Column, **kwargs) -> Column:
4848    """
4849    Create a column from a `[table].[column]` sql path. Schema is optional.
4850
4851    If a column is passed in then that column is returned.
4852
4853    Args:
4854        sql_path: `[table].[column]` string
4855    Returns:
4856        Table: A column expression
4857    """
4858    if sql_path is None or isinstance(sql_path, Column):
4859        return sql_path
4860    if not isinstance(sql_path, str):
4861        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4862    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
4863
4864
4865def alias_(
4866    expression: ExpOrStr,
4867    alias: str | Identifier,
4868    table: bool | t.Sequence[str | Identifier] = False,
4869    quoted: t.Optional[bool] = None,
4870    dialect: DialectType = None,
4871    **opts,
4872):
4873    """Create an Alias expression.
4874
4875    Example:
4876        >>> alias_('foo', 'bar').sql()
4877        'foo AS bar'
4878
4879        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4880        '(SELECT 1, 2) AS bar(a, b)'
4881
4882    Args:
4883        expression: the SQL code strings to parse.
4884            If an Expression instance is passed, this is used as-is.
4885        alias: the alias name to use. If the name has
4886            special characters it is quoted.
4887        table: Whether or not to create a table alias, can also be a list of columns.
4888        quoted: whether or not to quote the alias
4889        dialect: the dialect used to parse the input expression.
4890        **opts: other options to use to parse the input expressions.
4891
4892    Returns:
4893        Alias: the aliased expression
4894    """
4895    exp = maybe_parse(expression, dialect=dialect, **opts)
4896    alias = to_identifier(alias, quoted=quoted)
4897
4898    if table:
4899        table_alias = TableAlias(this=alias)
4900
4901        exp = exp.copy() if isinstance(expression, Expression) else exp
4902        exp.set("alias", table_alias)
4903
4904        if not isinstance(table, bool):
4905            for column in table:
4906                table_alias.append("columns", to_identifier(column, quoted=quoted))
4907
4908        return exp
4909
4910    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4911    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4912    # for the complete Window expression.
4913    #
4914    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4915
4916    if "alias" in exp.arg_types and not isinstance(exp, Window):
4917        exp = exp.copy()
4918        exp.set("alias", alias)
4919        return exp
4920    return Alias(this=exp, alias=alias)
4921
4922
4923def subquery(expression, alias=None, dialect=None, **opts):
4924    """
4925    Build a subquery expression.
4926
4927    Example:
4928        >>> subquery('select x from tbl', 'bar').select('x').sql()
4929        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4930
4931    Args:
4932        expression (str | Expression): the SQL code strings to parse.
4933            If an Expression instance is passed, this is used as-is.
4934        alias (str | Expression): the alias name to use.
4935        dialect (str): the dialect used to parse the input expression.
4936        **opts: other options to use to parse the input expressions.
4937
4938    Returns:
4939        Select: a new select with the subquery expression included
4940    """
4941
4942    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4943    return Select().from_(expression, dialect=dialect, **opts)
4944
4945
4946def column(
4947    col: str | Identifier,
4948    table: t.Optional[str | Identifier] = None,
4949    db: t.Optional[str | Identifier] = None,
4950    catalog: t.Optional[str | Identifier] = None,
4951    quoted: t.Optional[bool] = None,
4952) -> Column:
4953    """
4954    Build a Column.
4955
4956    Args:
4957        col: column name
4958        table: table name
4959        db: db name
4960        catalog: catalog name
4961        quoted: whether or not to force quote each part
4962    Returns:
4963        Column: column instance
4964    """
4965    return Column(
4966        this=to_identifier(col, quoted=quoted),
4967        table=to_identifier(table, quoted=quoted),
4968        db=to_identifier(db, quoted=quoted),
4969        catalog=to_identifier(catalog, quoted=quoted),
4970    )
4971
4972
4973def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
4974    """Cast an expression to a data type.
4975
4976    Example:
4977        >>> cast('x + 1', 'int').sql()
4978        'CAST(x + 1 AS INT)'
4979
4980    Args:
4981        expression: The expression to cast.
4982        to: The datatype to cast to.
4983
4984    Returns:
4985        A cast node.
4986    """
4987    expression = maybe_parse(expression, **opts)
4988    return Cast(this=expression, to=DataType.build(to, **opts))
4989
4990
4991def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4992    """Build a Table.
4993
4994    Args:
4995        table (str | Expression): column name
4996        db (str | Expression): db name
4997        catalog (str | Expression): catalog name
4998
4999    Returns:
5000        Table: table instance
5001    """
5002    return Table(
5003        this=to_identifier(table, quoted=quoted),
5004        db=to_identifier(db, quoted=quoted),
5005        catalog=to_identifier(catalog, quoted=quoted),
5006        alias=TableAlias(this=to_identifier(alias)) if alias else None,
5007    )
5008
5009
5010def values(
5011    values: t.Iterable[t.Tuple[t.Any, ...]],
5012    alias: t.Optional[str] = None,
5013    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
5014) -> Values:
5015    """Build VALUES statement.
5016
5017    Example:
5018        >>> values([(1, '2')]).sql()
5019        "VALUES (1, '2')"
5020
5021    Args:
5022        values: values statements that will be converted to SQL
5023        alias: optional alias
5024        columns: Optional list of ordered column names or ordered dictionary of column names to types.
5025         If either are provided then an alias is also required.
5026
5027    Returns:
5028        Values: the Values expression object
5029    """
5030    if columns and not alias:
5031        raise ValueError("Alias is required when providing columns")
5032
5033    return Values(
5034        expressions=[convert(tup) for tup in values],
5035        alias=(
5036            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
5037            if columns
5038            else (TableAlias(this=to_identifier(alias)) if alias else None)
5039        ),
5040    )
5041
5042
5043def var(name: t.Optional[ExpOrStr]) -> Var:
5044    """Build a SQL variable.
5045
5046    Example:
5047        >>> repr(var('x'))
5048        '(VAR this: x)'
5049
5050        >>> repr(var(column('x', table='y')))
5051        '(VAR this: x)'
5052
5053    Args:
5054        name: The name of the var or an expression who's name will become the var.
5055
5056    Returns:
5057        The new variable node.
5058    """
5059    if not name:
5060        raise ValueError("Cannot convert empty name into var.")
5061
5062    if isinstance(name, Expression):
5063        name = name.name
5064    return Var(this=name)
5065
5066
5067def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
5068    """Build ALTER TABLE... RENAME... expression
5069
5070    Args:
5071        old_name: The old name of the table
5072        new_name: The new name of the table
5073
5074    Returns:
5075        Alter table expression
5076    """
5077    old_table = to_table(old_name)
5078    new_table = to_table(new_name)
5079    return AlterTable(
5080        this=old_table,
5081        actions=[
5082            RenameTable(this=new_table),
5083        ],
5084    )
5085
5086
5087def convert(value: t.Any, copy: bool = False) -> Expression:
5088    """Convert a python value into an expression object.
5089
5090    Raises an error if a conversion is not possible.
5091
5092    Args:
5093        value: A python object.
5094        copy: Whether or not to copy `value` (only applies to Expressions and collections).
5095
5096    Returns:
5097        Expression: the equivalent expression object.
5098    """
5099    if isinstance(value, Expression):
5100        return _maybe_copy(value, copy)
5101    if isinstance(value, str):
5102        return Literal.string(value)
5103    if isinstance(value, bool):
5104        return Boolean(this=value)
5105    if value is None or (isinstance(value, float) and math.isnan(value)):
5106        return NULL
5107    if isinstance(value, numbers.Number):
5108        return Literal.number(value)
5109    if isinstance(value, datetime.datetime):
5110        datetime_literal = Literal.string(
5111            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
5112        )
5113        return TimeStrToTime(this=datetime_literal)
5114    if isinstance(value, datetime.date):
5115        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
5116        return DateStrToDate(this=date_literal)
5117    if isinstance(value, tuple):
5118        return Tuple(expressions=[convert(v, copy=copy) for v in value])
5119    if isinstance(value, list):
5120        return Array(expressions=[convert(v, copy=copy) for v in value])
5121    if isinstance(value, dict):
5122        return Map(
5123            keys=[convert(k, copy=copy) for k in value],
5124            values=[convert(v, copy=copy) for v in value.values()],
5125        )
5126    raise ValueError(f"Cannot convert {value}")
5127
5128
5129def replace_children(expression, fun, *args, **kwargs):
5130    """
5131    Replace children of an expression with the result of a lambda fun(child) -> exp.
5132    """
5133    for k, v in expression.args.items():
5134        is_list_arg = type(v) is list
5135
5136        child_nodes = v if is_list_arg else [v]
5137        new_child_nodes = []
5138
5139        for cn in child_nodes:
5140            if isinstance(cn, Expression):
5141                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
5142                    new_child_nodes.append(child_node)
5143                    child_node.parent = expression
5144                    child_node.arg_key = k
5145            else:
5146                new_child_nodes.append(cn)
5147
5148        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
5149
5150
5151def column_table_names(expression):
5152    """
5153    Return all table names referenced through columns in an expression.
5154
5155    Example:
5156        >>> import sqlglot
5157        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
5158        ['c', 'a']
5159
5160    Args:
5161        expression (sqlglot.Expression): expression to find table names
5162
5163    Returns:
5164        list: A list of unique names
5165    """
5166    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
5167
5168
5169def table_name(table) -> str:
5170    """Get the full name of a table as a string.
5171
5172    Args:
5173        table (exp.Table | str): table expression node or string.
5174
5175    Examples:
5176        >>> from sqlglot import exp, parse_one
5177        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
5178        'a.b.c'
5179
5180    Returns:
5181        The table name.
5182    """
5183
5184    table = maybe_parse(table, into=Table)
5185
5186    if not table:
5187        raise ValueError(f"Cannot parse {table}")
5188
5189    return ".".join(
5190        part
5191        for part in (
5192            table.text("catalog"),
5193            table.text("db"),
5194            table.name,
5195        )
5196        if part
5197    )
5198
5199
5200def replace_tables(expression, mapping):
5201    """Replace all tables in expression according to the mapping.
5202
5203    Args:
5204        expression (sqlglot.Expression): expression node to be transformed and replaced.
5205        mapping (Dict[str, str]): mapping of table names.
5206
5207    Examples:
5208        >>> from sqlglot import exp, parse_one
5209        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
5210        'SELECT * FROM c'
5211
5212    Returns:
5213        The mapped expression.
5214    """
5215
5216    def _replace_tables(node):
5217        if isinstance(node, Table):
5218            new_name = mapping.get(table_name(node))
5219            if new_name:
5220                return to_table(
5221                    new_name,
5222                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
5223                )
5224        return node
5225
5226    return expression.transform(_replace_tables)
5227
5228
5229def replace_placeholders(expression, *args, **kwargs):
5230    """Replace placeholders in an expression.
5231
5232    Args:
5233        expression (sqlglot.Expression): expression node to be transformed and replaced.
5234        args: positional names that will substitute unnamed placeholders in the given order.
5235        kwargs: keyword arguments that will substitute named placeholders.
5236
5237    Examples:
5238        >>> from sqlglot import exp, parse_one
5239        >>> replace_placeholders(
5240        ...     parse_one("select * from :tbl where ? = ?"),
5241        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
5242        ... ).sql()
5243        "SELECT * FROM foo WHERE str_col = 'b'"
5244
5245    Returns:
5246        The mapped expression.
5247    """
5248
5249    def _replace_placeholders(node, args, **kwargs):
5250        if isinstance(node, Placeholder):
5251            if node.name:
5252                new_name = kwargs.get(node.name)
5253                if new_name:
5254                    return convert(new_name)
5255            else:
5256                try:
5257                    return convert(next(args))
5258                except StopIteration:
5259                    pass
5260        return node
5261
5262    return expression.transform(_replace_placeholders, iter(args), **kwargs)
5263
5264
5265def expand(
5266    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
5267) -> Expression:
5268    """Transforms an expression by expanding all referenced sources into subqueries.
5269
5270    Examples:
5271        >>> from sqlglot import parse_one
5272        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
5273        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
5274
5275        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
5276        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
5277
5278    Args:
5279        expression: The expression to expand.
5280        sources: A dictionary of name to Subqueryables.
5281        copy: Whether or not to copy the expression during transformation. Defaults to True.
5282
5283    Returns:
5284        The transformed expression.
5285    """
5286
5287    def _expand(node: Expression):
5288        if isinstance(node, Table):
5289            name = table_name(node)
5290            source = sources.get(name)
5291            if source:
5292                subquery = source.subquery(node.alias or name)
5293                subquery.comments = [f"source: {name}"]
5294                return subquery.transform(_expand, copy=False)
5295        return node
5296
5297    return expression.transform(_expand, copy=copy)
5298
5299
5300def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
5301    """
5302    Returns a Func expression.
5303
5304    Examples:
5305        >>> func("abs", 5).sql()
5306        'ABS(5)'
5307
5308        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
5309        'CAST(5 AS DOUBLE)'
5310
5311    Args:
5312        name: the name of the function to build.
5313        args: the args used to instantiate the function of interest.
5314        dialect: the source dialect.
5315        kwargs: the kwargs used to instantiate the function of interest.
5316
5317    Note:
5318        The arguments `args` and `kwargs` are mutually exclusive.
5319
5320    Returns:
5321        An instance of the function of interest, or an anonymous function, if `name` doesn't
5322        correspond to an existing `sqlglot.expressions.Func` class.
5323    """
5324    if args and kwargs:
5325        raise ValueError("Can't use both args and kwargs to instantiate a function.")
5326
5327    from sqlglot.dialects.dialect import Dialect
5328
5329    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
5330    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
5331
5332    parser = Dialect.get_or_raise(dialect)().parser()
5333    from_args_list = parser.FUNCTIONS.get(name.upper())
5334
5335    if from_args_list:
5336        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
5337    else:
5338        kwargs = kwargs or {"expressions": converted}
5339        function = Anonymous(this=name, **kwargs)
5340
5341    for error_message in function.error_messages(converted):
5342        raise ValueError(error_message)
5343
5344    return function
5345
5346
5347def true():
5348    """
5349    Returns a true Boolean expression.
5350    """
5351    return Boolean(this=True)
5352
5353
5354def false():
5355    """
5356    Returns a false Boolean expression.
5357    """
5358    return Boolean(this=False)
5359
5360
5361def null():
5362    """
5363    Returns a Null expression.
5364    """
5365    return Null()
5366
5367
5368# TODO: deprecate this
5369TRUE = Boolean(this=True)
5370FALSE = Boolean(this=False)
5371NULL = Null()