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