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