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