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