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