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