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