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