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