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 typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56TABLE_PARTS = ("this", "db", "catalog")
  57
  58
  59class Expression(metaclass=_Expression):
  60    """
  61    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  62    context, such as its child expressions, their names (arg keys), and whether a given child expression
  63    is optional or not.
  64
  65    Attributes:
  66        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  67            and representing expressions as strings.
  68        arg_types: determines what arguments (child nodes) are supported by an expression. It
  69            maps arg keys to booleans that indicate whether the corresponding args are optional.
  70        parent: a reference to the parent expression (or None, in case of root expressions).
  71        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  72            uses to refer to it.
  73        comments: a list of comments that are associated with a given expression. This is used in
  74            order to preserve comments when transpiling SQL code.
  75        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  76            optimizer, in order to enable some transformations that require type information.
  77        meta: a dictionary that can be used to store useful metadata for a given expression.
  78
  79    Example:
  80        >>> class Foo(Expression):
  81        ...     arg_types = {"this": True, "expression": False}
  82
  83        The above definition informs us that Foo is an Expression that requires an argument called
  84        "this" and may also optionally receive an argument called "expression".
  85
  86    Args:
  87        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  88    """
  89
  90    key = "expression"
  91    arg_types = {"this": True}
  92    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  93
  94    def __init__(self, **args: t.Any):
  95        self.args: t.Dict[str, t.Any] = args
  96        self.parent: t.Optional[Expression] = None
  97        self.arg_key: t.Optional[str] = None
  98        self.comments: t.Optional[t.List[str]] = None
  99        self._type: t.Optional[DataType] = None
 100        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 101        self._hash: t.Optional[int] = None
 102
 103        for arg_key, value in self.args.items():
 104            self._set_parent(arg_key, value)
 105
 106    def __eq__(self, other) -> bool:
 107        return type(self) is type(other) and hash(self) == hash(other)
 108
 109    @property
 110    def hashable_args(self) -> t.Any:
 111        return frozenset(
 112            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 113            for k, v in self.args.items()
 114            if not (v is None or v is False or (type(v) is list and not v))
 115        )
 116
 117    def __hash__(self) -> int:
 118        if self._hash is not None:
 119            return self._hash
 120
 121        return hash((self.__class__, self.hashable_args))
 122
 123    @property
 124    def this(self) -> t.Any:
 125        """
 126        Retrieves the argument with key "this".
 127        """
 128        return self.args.get("this")
 129
 130    @property
 131    def expression(self) -> t.Any:
 132        """
 133        Retrieves the argument with key "expression".
 134        """
 135        return self.args.get("expression")
 136
 137    @property
 138    def expressions(self) -> t.List[t.Any]:
 139        """
 140        Retrieves the argument with key "expressions".
 141        """
 142        return self.args.get("expressions") or []
 143
 144    def text(self, key) -> str:
 145        """
 146        Returns a textual representation of the argument corresponding to "key". This can only be used
 147        for args that are strings or leaf Expression instances, such as identifiers and literals.
 148        """
 149        field = self.args.get(key)
 150        if isinstance(field, str):
 151            return field
 152        if isinstance(field, (Identifier, Literal, Var)):
 153            return field.this
 154        if isinstance(field, (Star, Null)):
 155            return field.name
 156        return ""
 157
 158    @property
 159    def is_string(self) -> bool:
 160        """
 161        Checks whether a Literal expression is a string.
 162        """
 163        return isinstance(self, Literal) and self.args["is_string"]
 164
 165    @property
 166    def is_number(self) -> bool:
 167        """
 168        Checks whether a Literal expression is a number.
 169        """
 170        return isinstance(self, Literal) and not self.args["is_string"]
 171
 172    @property
 173    def is_int(self) -> bool:
 174        """
 175        Checks whether a Literal expression is an integer.
 176        """
 177        if self.is_number:
 178            try:
 179                int(self.name)
 180                return True
 181            except ValueError:
 182                pass
 183        return False
 184
 185    @property
 186    def is_star(self) -> bool:
 187        """Checks whether an expression is a star."""
 188        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 189
 190    @property
 191    def alias(self) -> str:
 192        """
 193        Returns the alias of the expression, or an empty string if it's not aliased.
 194        """
 195        if isinstance(self.args.get("alias"), TableAlias):
 196            return self.args["alias"].name
 197        return self.text("alias")
 198
 199    @property
 200    def alias_column_names(self) -> t.List[str]:
 201        table_alias = self.args.get("alias")
 202        if not table_alias:
 203            return []
 204        return [c.name for c in table_alias.args.get("columns") or []]
 205
 206    @property
 207    def name(self) -> str:
 208        return self.text("this")
 209
 210    @property
 211    def alias_or_name(self) -> str:
 212        return self.alias or self.name
 213
 214    @property
 215    def output_name(self) -> str:
 216        """
 217        Name of the output column if this expression is a selection.
 218
 219        If the Expression has no output name, an empty string is returned.
 220
 221        Example:
 222            >>> from sqlglot import parse_one
 223            >>> parse_one("SELECT a").expressions[0].output_name
 224            'a'
 225            >>> parse_one("SELECT b AS c").expressions[0].output_name
 226            'c'
 227            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 228            ''
 229        """
 230        return ""
 231
 232    @property
 233    def type(self) -> t.Optional[DataType]:
 234        return self._type
 235
 236    @type.setter
 237    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 238        if dtype and not isinstance(dtype, DataType):
 239            dtype = DataType.build(dtype)
 240        self._type = dtype  # type: ignore
 241
 242    def is_type(self, *dtypes) -> bool:
 243        return self.type is not None and self.type.is_type(*dtypes)
 244
 245    @property
 246    def meta(self) -> t.Dict[str, t.Any]:
 247        if self._meta is None:
 248            self._meta = {}
 249        return self._meta
 250
 251    def __deepcopy__(self, memo):
 252        copy = self.__class__(**deepcopy(self.args))
 253        if self.comments is not None:
 254            copy.comments = deepcopy(self.comments)
 255
 256        if self._type is not None:
 257            copy._type = self._type.copy()
 258
 259        if self._meta is not None:
 260            copy._meta = deepcopy(self._meta)
 261
 262        return copy
 263
 264    def copy(self):
 265        """
 266        Returns a deep copy of the expression.
 267        """
 268        new = deepcopy(self)
 269        new.parent = self.parent
 270        return new
 271
 272    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 273        if self.comments is None:
 274            self.comments = []
 275        if comments:
 276            for comment in comments:
 277                _, *meta = comment.split(SQLGLOT_META)
 278                if meta:
 279                    for kv in "".join(meta).split(","):
 280                        k, *v = kv.split("=")
 281                        value = v[0].strip() if v else True
 282                        self.meta[k.strip()] = value
 283                self.comments.append(comment)
 284
 285    def append(self, arg_key: str, value: t.Any) -> None:
 286        """
 287        Appends value to arg_key if it's a list or sets it as a new list.
 288
 289        Args:
 290            arg_key (str): name of the list expression arg
 291            value (Any): value to append to the list
 292        """
 293        if not isinstance(self.args.get(arg_key), list):
 294            self.args[arg_key] = []
 295        self.args[arg_key].append(value)
 296        self._set_parent(arg_key, value)
 297
 298    def set(self, arg_key: str, value: t.Any) -> None:
 299        """
 300        Sets arg_key to value.
 301
 302        Args:
 303            arg_key: name of the expression arg.
 304            value: value to set the arg to.
 305        """
 306        if value is None:
 307            self.args.pop(arg_key, None)
 308            return
 309
 310        self.args[arg_key] = value
 311        self._set_parent(arg_key, value)
 312
 313    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 314        if hasattr(value, "parent"):
 315            value.parent = self
 316            value.arg_key = arg_key
 317        elif type(value) is list:
 318            for v in value:
 319                if hasattr(v, "parent"):
 320                    v.parent = self
 321                    v.arg_key = arg_key
 322
 323    @property
 324    def depth(self) -> int:
 325        """
 326        Returns the depth of this tree.
 327        """
 328        if self.parent:
 329            return self.parent.depth + 1
 330        return 0
 331
 332    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 333        """Yields the key and expression for all arguments, exploding list args."""
 334        for k, vs in self.args.items():
 335            if type(vs) is list:
 336                for v in vs:
 337                    if hasattr(v, "parent"):
 338                        yield k, v
 339            else:
 340                if hasattr(vs, "parent"):
 341                    yield k, vs
 342
 343    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 344        """
 345        Returns the first node in this tree which matches at least one of
 346        the specified types.
 347
 348        Args:
 349            expression_types: the expression type(s) to match.
 350            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 351
 352        Returns:
 353            The node which matches the criteria or None if no such node was found.
 354        """
 355        return next(self.find_all(*expression_types, bfs=bfs), None)
 356
 357    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 358        """
 359        Returns a generator object which visits all nodes in this tree and only
 360        yields those that match at least one of the specified expression types.
 361
 362        Args:
 363            expression_types: the expression type(s) to match.
 364            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 365
 366        Returns:
 367            The generator object.
 368        """
 369        for expression, *_ in self.walk(bfs=bfs):
 370            if isinstance(expression, expression_types):
 371                yield expression
 372
 373    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 374        """
 375        Returns a nearest parent matching expression_types.
 376
 377        Args:
 378            expression_types: the expression type(s) to match.
 379
 380        Returns:
 381            The parent node.
 382        """
 383        ancestor = self.parent
 384        while ancestor and not isinstance(ancestor, expression_types):
 385            ancestor = ancestor.parent
 386        return t.cast(E, ancestor)
 387
 388    @property
 389    def parent_select(self) -> t.Optional[Select]:
 390        """
 391        Returns the parent select statement.
 392        """
 393        return self.find_ancestor(Select)
 394
 395    @property
 396    def same_parent(self) -> bool:
 397        """Returns if the parent is the same class as itself."""
 398        return type(self.parent) is self.__class__
 399
 400    def root(self) -> Expression:
 401        """
 402        Returns the root expression of this tree.
 403        """
 404        expression = self
 405        while expression.parent:
 406            expression = expression.parent
 407        return expression
 408
 409    def walk(self, bfs=True, prune=None):
 410        """
 411        Returns a generator object which visits all nodes in this tree.
 412
 413        Args:
 414            bfs (bool): if set to True the BFS traversal order will be applied,
 415                otherwise the DFS traversal will be used instead.
 416            prune ((node, parent, arg_key) -> bool): callable that returns True if
 417                the generator should stop traversing this branch of the tree.
 418
 419        Returns:
 420            the generator object.
 421        """
 422        if bfs:
 423            yield from self.bfs(prune=prune)
 424        else:
 425            yield from self.dfs(prune=prune)
 426
 427    def dfs(self, parent=None, key=None, prune=None):
 428        """
 429        Returns a generator object which visits all nodes in this tree in
 430        the DFS (Depth-first) order.
 431
 432        Returns:
 433            The generator object.
 434        """
 435        parent = parent or self.parent
 436        yield self, parent, key
 437        if prune and prune(self, parent, key):
 438            return
 439
 440        for k, v in self.iter_expressions():
 441            yield from v.dfs(self, k, prune)
 442
 443    def bfs(self, prune=None):
 444        """
 445        Returns a generator object which visits all nodes in this tree in
 446        the BFS (Breadth-first) order.
 447
 448        Returns:
 449            The generator object.
 450        """
 451        queue = deque([(self, self.parent, None)])
 452
 453        while queue:
 454            item, parent, key = queue.popleft()
 455
 456            yield item, parent, key
 457            if prune and prune(item, parent, key):
 458                continue
 459
 460            for k, v in item.iter_expressions():
 461                queue.append((v, item, k))
 462
 463    def unnest(self):
 464        """
 465        Returns the first non parenthesis child or self.
 466        """
 467        expression = self
 468        while type(expression) is Paren:
 469            expression = expression.this
 470        return expression
 471
 472    def unalias(self):
 473        """
 474        Returns the inner expression if this is an Alias.
 475        """
 476        if isinstance(self, Alias):
 477            return self.this
 478        return self
 479
 480    def unnest_operands(self):
 481        """
 482        Returns unnested operands as a tuple.
 483        """
 484        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 485
 486    def flatten(self, unnest=True):
 487        """
 488        Returns a generator which yields child nodes who's parents are the same class.
 489
 490        A AND B AND C -> [A, B, C]
 491        """
 492        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 493            if not type(node) is self.__class__:
 494                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 495
 496    def __str__(self) -> str:
 497        return self.sql()
 498
 499    def __repr__(self) -> str:
 500        return self._to_s()
 501
 502    def sql(self, dialect: DialectType = None, **opts) -> str:
 503        """
 504        Returns SQL string representation of this tree.
 505
 506        Args:
 507            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 508            opts: other `sqlglot.generator.Generator` options.
 509
 510        Returns:
 511            The SQL string.
 512        """
 513        from sqlglot.dialects import Dialect
 514
 515        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 516
 517    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 518        indent = "" if not level else "\n"
 519        indent += "".join(["  "] * level)
 520        left = f"({self.key.upper()} "
 521
 522        args: t.Dict[str, t.Any] = {
 523            k: ", ".join(
 524                v._to_s(hide_missing=hide_missing, level=level + 1)
 525                if hasattr(v, "_to_s")
 526                else str(v)
 527                for v in ensure_list(vs)
 528                if v is not None
 529            )
 530            for k, vs in self.args.items()
 531        }
 532        args["comments"] = self.comments
 533        args["type"] = self.type
 534        args = {k: v for k, v in args.items() if v or not hide_missing}
 535
 536        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 537        right += ")"
 538
 539        return indent + left + right
 540
 541    def transform(self, fun, *args, copy=True, **kwargs):
 542        """
 543        Recursively visits all tree nodes (excluding already transformed ones)
 544        and applies the given transformation function to each node.
 545
 546        Args:
 547            fun (function): a function which takes a node as an argument and returns a
 548                new transformed node or the same node without modifications. If the function
 549                returns None, then the corresponding node will be removed from the syntax tree.
 550            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 551                modified in place.
 552
 553        Returns:
 554            The transformed tree.
 555        """
 556        node = self.copy() if copy else self
 557        new_node = fun(node, *args, **kwargs)
 558
 559        if new_node is None or not isinstance(new_node, Expression):
 560            return new_node
 561        if new_node is not node:
 562            new_node.parent = node.parent
 563            return new_node
 564
 565        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 566        return new_node
 567
 568    @t.overload
 569    def replace(self, expression: E) -> E:
 570        ...
 571
 572    @t.overload
 573    def replace(self, expression: None) -> None:
 574        ...
 575
 576    def replace(self, expression):
 577        """
 578        Swap out this expression with a new expression.
 579
 580        For example::
 581
 582            >>> tree = Select().select("x").from_("tbl")
 583            >>> tree.find(Column).replace(Column(this="y"))
 584            (COLUMN this: y)
 585            >>> tree.sql()
 586            'SELECT y FROM tbl'
 587
 588        Args:
 589            expression: new node
 590
 591        Returns:
 592            The new expression or expressions.
 593        """
 594        if not self.parent:
 595            return expression
 596
 597        parent = self.parent
 598        self.parent = None
 599
 600        replace_children(parent, lambda child: expression if child is self else child)
 601        return expression
 602
 603    def pop(self: E) -> E:
 604        """
 605        Remove this expression from its AST.
 606
 607        Returns:
 608            The popped expression.
 609        """
 610        self.replace(None)
 611        return self
 612
 613    def assert_is(self, type_: t.Type[E]) -> E:
 614        """
 615        Assert that this `Expression` is an instance of `type_`.
 616
 617        If it is NOT an instance of `type_`, this raises an assertion error.
 618        Otherwise, this returns this expression.
 619
 620        Examples:
 621            This is useful for type security in chained expressions:
 622
 623            >>> import sqlglot
 624            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 625            'SELECT x, z FROM y'
 626        """
 627        assert isinstance(self, type_)
 628        return self
 629
 630    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 631        """
 632        Checks if this expression is valid (e.g. all mandatory args are set).
 633
 634        Args:
 635            args: a sequence of values that were used to instantiate a Func expression. This is used
 636                to check that the provided arguments don't exceed the function argument limit.
 637
 638        Returns:
 639            A list of error messages for all possible errors that were found.
 640        """
 641        errors: t.List[str] = []
 642
 643        for k in self.args:
 644            if k not in self.arg_types:
 645                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 646        for k, mandatory in self.arg_types.items():
 647            v = self.args.get(k)
 648            if mandatory and (v is None or (isinstance(v, list) and not v)):
 649                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 650
 651        if (
 652            args
 653            and isinstance(self, Func)
 654            and len(args) > len(self.arg_types)
 655            and not self.is_var_len_args
 656        ):
 657            errors.append(
 658                f"The number of provided arguments ({len(args)}) is greater than "
 659                f"the maximum number of supported arguments ({len(self.arg_types)})"
 660            )
 661
 662        return errors
 663
 664    def dump(self):
 665        """
 666        Dump this Expression to a JSON-serializable dict.
 667        """
 668        from sqlglot.serde import dump
 669
 670        return dump(self)
 671
 672    @classmethod
 673    def load(cls, obj):
 674        """
 675        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 676        """
 677        from sqlglot.serde import load
 678
 679        return load(obj)
 680
 681    def and_(
 682        self,
 683        *expressions: t.Optional[ExpOrStr],
 684        dialect: DialectType = None,
 685        copy: bool = True,
 686        **opts,
 687    ) -> Condition:
 688        """
 689        AND this condition with one or multiple expressions.
 690
 691        Example:
 692            >>> condition("x=1").and_("y=1").sql()
 693            'x = 1 AND y = 1'
 694
 695        Args:
 696            *expressions: the SQL code strings to parse.
 697                If an `Expression` instance is passed, it will be used as-is.
 698            dialect: the dialect used to parse the input expression.
 699            copy: whether or not to copy the involved expressions (only applies to Expressions).
 700            opts: other options to use to parse the input expressions.
 701
 702        Returns:
 703            The new And condition.
 704        """
 705        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 706
 707    def or_(
 708        self,
 709        *expressions: t.Optional[ExpOrStr],
 710        dialect: DialectType = None,
 711        copy: bool = True,
 712        **opts,
 713    ) -> Condition:
 714        """
 715        OR this condition with one or multiple expressions.
 716
 717        Example:
 718            >>> condition("x=1").or_("y=1").sql()
 719            'x = 1 OR y = 1'
 720
 721        Args:
 722            *expressions: the SQL code strings to parse.
 723                If an `Expression` instance is passed, it will be used as-is.
 724            dialect: the dialect used to parse the input expression.
 725            copy: whether or not to copy the involved expressions (only applies to Expressions).
 726            opts: other options to use to parse the input expressions.
 727
 728        Returns:
 729            The new Or condition.
 730        """
 731        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 732
 733    def not_(self, copy: bool = True):
 734        """
 735        Wrap this condition with NOT.
 736
 737        Example:
 738            >>> condition("x=1").not_().sql()
 739            'NOT x = 1'
 740
 741        Args:
 742            copy: whether or not to copy this object.
 743
 744        Returns:
 745            The new Not instance.
 746        """
 747        return not_(self, copy=copy)
 748
 749    def as_(
 750        self,
 751        alias: str | Identifier,
 752        quoted: t.Optional[bool] = None,
 753        dialect: DialectType = None,
 754        copy: bool = True,
 755        **opts,
 756    ) -> Alias:
 757        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 758
 759    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 760        this = self.copy()
 761        other = convert(other, copy=True)
 762        if not isinstance(this, klass) and not isinstance(other, klass):
 763            this = _wrap(this, Binary)
 764            other = _wrap(other, Binary)
 765        if reverse:
 766            return klass(this=other, expression=this)
 767        return klass(this=this, expression=other)
 768
 769    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 770        return Bracket(
 771            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 772        )
 773
 774    def __iter__(self) -> t.Iterator:
 775        if "expressions" in self.arg_types:
 776            return iter(self.args.get("expressions") or [])
 777        # We define this because __getitem__ converts Expression into an iterable, which is
 778        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 779        # See: https://peps.python.org/pep-0234/
 780        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 781
 782    def isin(
 783        self,
 784        *expressions: t.Any,
 785        query: t.Optional[ExpOrStr] = None,
 786        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 787        copy: bool = True,
 788        **opts,
 789    ) -> In:
 790        return In(
 791            this=maybe_copy(self, copy),
 792            expressions=[convert(e, copy=copy) for e in expressions],
 793            query=maybe_parse(query, copy=copy, **opts) if query else None,
 794            unnest=Unnest(
 795                expressions=[
 796                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 797                ]
 798            )
 799            if unnest
 800            else None,
 801        )
 802
 803    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 804        return Between(
 805            this=maybe_copy(self, copy),
 806            low=convert(low, copy=copy, **opts),
 807            high=convert(high, copy=copy, **opts),
 808        )
 809
 810    def is_(self, other: ExpOrStr) -> Is:
 811        return self._binop(Is, other)
 812
 813    def like(self, other: ExpOrStr) -> Like:
 814        return self._binop(Like, other)
 815
 816    def ilike(self, other: ExpOrStr) -> ILike:
 817        return self._binop(ILike, other)
 818
 819    def eq(self, other: t.Any) -> EQ:
 820        return self._binop(EQ, other)
 821
 822    def neq(self, other: t.Any) -> NEQ:
 823        return self._binop(NEQ, other)
 824
 825    def rlike(self, other: ExpOrStr) -> RegexpLike:
 826        return self._binop(RegexpLike, other)
 827
 828    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 829        div = self._binop(Div, other)
 830        div.args["typed"] = typed
 831        div.args["safe"] = safe
 832        return div
 833
 834    def __lt__(self, other: t.Any) -> LT:
 835        return self._binop(LT, other)
 836
 837    def __le__(self, other: t.Any) -> LTE:
 838        return self._binop(LTE, other)
 839
 840    def __gt__(self, other: t.Any) -> GT:
 841        return self._binop(GT, other)
 842
 843    def __ge__(self, other: t.Any) -> GTE:
 844        return self._binop(GTE, other)
 845
 846    def __add__(self, other: t.Any) -> Add:
 847        return self._binop(Add, other)
 848
 849    def __radd__(self, other: t.Any) -> Add:
 850        return self._binop(Add, other, reverse=True)
 851
 852    def __sub__(self, other: t.Any) -> Sub:
 853        return self._binop(Sub, other)
 854
 855    def __rsub__(self, other: t.Any) -> Sub:
 856        return self._binop(Sub, other, reverse=True)
 857
 858    def __mul__(self, other: t.Any) -> Mul:
 859        return self._binop(Mul, other)
 860
 861    def __rmul__(self, other: t.Any) -> Mul:
 862        return self._binop(Mul, other, reverse=True)
 863
 864    def __truediv__(self, other: t.Any) -> Div:
 865        return self._binop(Div, other)
 866
 867    def __rtruediv__(self, other: t.Any) -> Div:
 868        return self._binop(Div, other, reverse=True)
 869
 870    def __floordiv__(self, other: t.Any) -> IntDiv:
 871        return self._binop(IntDiv, other)
 872
 873    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 874        return self._binop(IntDiv, other, reverse=True)
 875
 876    def __mod__(self, other: t.Any) -> Mod:
 877        return self._binop(Mod, other)
 878
 879    def __rmod__(self, other: t.Any) -> Mod:
 880        return self._binop(Mod, other, reverse=True)
 881
 882    def __pow__(self, other: t.Any) -> Pow:
 883        return self._binop(Pow, other)
 884
 885    def __rpow__(self, other: t.Any) -> Pow:
 886        return self._binop(Pow, other, reverse=True)
 887
 888    def __and__(self, other: t.Any) -> And:
 889        return self._binop(And, other)
 890
 891    def __rand__(self, other: t.Any) -> And:
 892        return self._binop(And, other, reverse=True)
 893
 894    def __or__(self, other: t.Any) -> Or:
 895        return self._binop(Or, other)
 896
 897    def __ror__(self, other: t.Any) -> Or:
 898        return self._binop(Or, other, reverse=True)
 899
 900    def __neg__(self) -> Neg:
 901        return Neg(this=_wrap(self.copy(), Binary))
 902
 903    def __invert__(self) -> Not:
 904        return not_(self.copy())
 905
 906
 907IntoType = t.Union[
 908    str,
 909    t.Type[Expression],
 910    t.Collection[t.Union[str, t.Type[Expression]]],
 911]
 912ExpOrStr = t.Union[str, Expression]
 913
 914
 915class Condition(Expression):
 916    """Logical conditions like x AND y, or simply x"""
 917
 918
 919class Predicate(Condition):
 920    """Relationships like x = y, x > 1, x >= y."""
 921
 922
 923class DerivedTable(Expression):
 924    @property
 925    def selects(self) -> t.List[Expression]:
 926        return self.this.selects if isinstance(self.this, Subqueryable) else []
 927
 928    @property
 929    def named_selects(self) -> t.List[str]:
 930        return [select.output_name for select in self.selects]
 931
 932
 933class Unionable(Expression):
 934    def union(
 935        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 936    ) -> Unionable:
 937        """
 938        Builds a UNION expression.
 939
 940        Example:
 941            >>> import sqlglot
 942            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 943            'SELECT * FROM foo UNION SELECT * FROM bla'
 944
 945        Args:
 946            expression: the SQL code string.
 947                If an `Expression` instance is passed, it will be used as-is.
 948            distinct: set the DISTINCT flag if and only if this is true.
 949            dialect: the dialect used to parse the input expression.
 950            opts: other options to use to parse the input expressions.
 951
 952        Returns:
 953            The new Union expression.
 954        """
 955        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 956
 957    def intersect(
 958        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 959    ) -> Unionable:
 960        """
 961        Builds an INTERSECT expression.
 962
 963        Example:
 964            >>> import sqlglot
 965            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 966            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 967
 968        Args:
 969            expression: the SQL code string.
 970                If an `Expression` instance is passed, it will be used as-is.
 971            distinct: set the DISTINCT flag if and only if this is true.
 972            dialect: the dialect used to parse the input expression.
 973            opts: other options to use to parse the input expressions.
 974
 975        Returns:
 976            The new Intersect expression.
 977        """
 978        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 979
 980    def except_(
 981        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 982    ) -> Unionable:
 983        """
 984        Builds an EXCEPT expression.
 985
 986        Example:
 987            >>> import sqlglot
 988            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 989            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 990
 991        Args:
 992            expression: the SQL code string.
 993                If an `Expression` instance is passed, it will be used as-is.
 994            distinct: set the DISTINCT flag if and only if this is true.
 995            dialect: the dialect used to parse the input expression.
 996            opts: other options to use to parse the input expressions.
 997
 998        Returns:
 999            The new Except expression.
1000        """
1001        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1002
1003
1004class UDTF(DerivedTable, Unionable):
1005    @property
1006    def selects(self) -> t.List[Expression]:
1007        alias = self.args.get("alias")
1008        return alias.columns if alias else []
1009
1010
1011class Cache(Expression):
1012    arg_types = {
1013        "this": True,
1014        "lazy": False,
1015        "options": False,
1016        "expression": False,
1017    }
1018
1019
1020class Uncache(Expression):
1021    arg_types = {"this": True, "exists": False}
1022
1023
1024class Refresh(Expression):
1025    pass
1026
1027
1028class DDL(Expression):
1029    @property
1030    def ctes(self):
1031        with_ = self.args.get("with")
1032        if not with_:
1033            return []
1034        return with_.expressions
1035
1036    @property
1037    def named_selects(self) -> t.List[str]:
1038        if isinstance(self.expression, Subqueryable):
1039            return self.expression.named_selects
1040        return []
1041
1042    @property
1043    def selects(self) -> t.List[Expression]:
1044        if isinstance(self.expression, Subqueryable):
1045            return self.expression.selects
1046        return []
1047
1048
1049class DML(Expression):
1050    def returning(
1051        self,
1052        expression: ExpOrStr,
1053        dialect: DialectType = None,
1054        copy: bool = True,
1055        **opts,
1056    ) -> DML:
1057        """
1058        Set the RETURNING expression. Not supported by all dialects.
1059
1060        Example:
1061            >>> delete("tbl").returning("*", dialect="postgres").sql()
1062            'DELETE FROM tbl RETURNING *'
1063
1064        Args:
1065            expression: the SQL code strings to parse.
1066                If an `Expression` instance is passed, it will be used as-is.
1067            dialect: the dialect used to parse the input expressions.
1068            copy: if `False`, modify this expression instance in-place.
1069            opts: other options to use to parse the input expressions.
1070
1071        Returns:
1072            Delete: the modified expression.
1073        """
1074        return _apply_builder(
1075            expression=expression,
1076            instance=self,
1077            arg="returning",
1078            prefix="RETURNING",
1079            dialect=dialect,
1080            copy=copy,
1081            into=Returning,
1082            **opts,
1083        )
1084
1085
1086class Create(DDL):
1087    arg_types = {
1088        "with": False,
1089        "this": True,
1090        "kind": True,
1091        "expression": False,
1092        "exists": False,
1093        "properties": False,
1094        "replace": False,
1095        "unique": False,
1096        "indexes": False,
1097        "no_schema_binding": False,
1098        "begin": False,
1099        "end": False,
1100        "clone": False,
1101    }
1102
1103
1104# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1105# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1106# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1107class Clone(Expression):
1108    arg_types = {
1109        "this": True,
1110        "when": False,
1111        "kind": False,
1112        "shallow": False,
1113        "expression": False,
1114        "copy": False,
1115    }
1116
1117
1118class Describe(Expression):
1119    arg_types = {"this": True, "kind": False, "expressions": False}
1120
1121
1122class Kill(Expression):
1123    arg_types = {"this": True, "kind": False}
1124
1125
1126class Pragma(Expression):
1127    pass
1128
1129
1130class Set(Expression):
1131    arg_types = {"expressions": False, "unset": False, "tag": False}
1132
1133
1134class SetItem(Expression):
1135    arg_types = {
1136        "this": False,
1137        "expressions": False,
1138        "kind": False,
1139        "collate": False,  # MySQL SET NAMES statement
1140        "global": False,
1141    }
1142
1143
1144class Show(Expression):
1145    arg_types = {
1146        "this": True,
1147        "target": False,
1148        "offset": False,
1149        "limit": False,
1150        "like": False,
1151        "where": False,
1152        "db": False,
1153        "scope": False,
1154        "scope_kind": False,
1155        "full": False,
1156        "mutex": False,
1157        "query": False,
1158        "channel": False,
1159        "global": False,
1160        "log": False,
1161        "position": False,
1162        "types": False,
1163    }
1164
1165
1166class UserDefinedFunction(Expression):
1167    arg_types = {"this": True, "expressions": False, "wrapped": False}
1168
1169
1170class CharacterSet(Expression):
1171    arg_types = {"this": True, "default": False}
1172
1173
1174class With(Expression):
1175    arg_types = {"expressions": True, "recursive": False}
1176
1177    @property
1178    def recursive(self) -> bool:
1179        return bool(self.args.get("recursive"))
1180
1181
1182class WithinGroup(Expression):
1183    arg_types = {"this": True, "expression": False}
1184
1185
1186# clickhouse supports scalar ctes
1187# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1188class CTE(DerivedTable):
1189    arg_types = {"this": True, "alias": True, "scalar": False}
1190
1191
1192class TableAlias(Expression):
1193    arg_types = {"this": False, "columns": False}
1194
1195    @property
1196    def columns(self):
1197        return self.args.get("columns") or []
1198
1199
1200class BitString(Condition):
1201    pass
1202
1203
1204class HexString(Condition):
1205    pass
1206
1207
1208class ByteString(Condition):
1209    pass
1210
1211
1212class RawString(Condition):
1213    pass
1214
1215
1216class Column(Condition):
1217    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1218
1219    @property
1220    def table(self) -> str:
1221        return self.text("table")
1222
1223    @property
1224    def db(self) -> str:
1225        return self.text("db")
1226
1227    @property
1228    def catalog(self) -> str:
1229        return self.text("catalog")
1230
1231    @property
1232    def output_name(self) -> str:
1233        return self.name
1234
1235    @property
1236    def parts(self) -> t.List[Identifier]:
1237        """Return the parts of a column in order catalog, db, table, name."""
1238        return [
1239            t.cast(Identifier, self.args[part])
1240            for part in ("catalog", "db", "table", "this")
1241            if self.args.get(part)
1242        ]
1243
1244    def to_dot(self) -> Dot | Identifier:
1245        """Converts the column into a dot expression."""
1246        parts = self.parts
1247        parent = self.parent
1248
1249        while parent:
1250            if isinstance(parent, Dot):
1251                parts.append(parent.expression)
1252            parent = parent.parent
1253
1254        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1255
1256
1257class ColumnPosition(Expression):
1258    arg_types = {"this": False, "position": True}
1259
1260
1261class ColumnDef(Expression):
1262    arg_types = {
1263        "this": True,
1264        "kind": False,
1265        "constraints": False,
1266        "exists": False,
1267        "position": False,
1268    }
1269
1270    @property
1271    def constraints(self) -> t.List[ColumnConstraint]:
1272        return self.args.get("constraints") or []
1273
1274
1275class AlterColumn(Expression):
1276    arg_types = {
1277        "this": True,
1278        "dtype": False,
1279        "collate": False,
1280        "using": False,
1281        "default": False,
1282        "drop": False,
1283    }
1284
1285
1286class RenameTable(Expression):
1287    pass
1288
1289
1290class SwapTable(Expression):
1291    pass
1292
1293
1294class Comment(Expression):
1295    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1296
1297
1298class Comprehension(Expression):
1299    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1300
1301
1302# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1303class MergeTreeTTLAction(Expression):
1304    arg_types = {
1305        "this": True,
1306        "delete": False,
1307        "recompress": False,
1308        "to_disk": False,
1309        "to_volume": False,
1310    }
1311
1312
1313# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1314class MergeTreeTTL(Expression):
1315    arg_types = {
1316        "expressions": True,
1317        "where": False,
1318        "group": False,
1319        "aggregates": False,
1320    }
1321
1322
1323# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1324class IndexConstraintOption(Expression):
1325    arg_types = {
1326        "key_block_size": False,
1327        "using": False,
1328        "parser": False,
1329        "comment": False,
1330        "visible": False,
1331        "engine_attr": False,
1332        "secondary_engine_attr": False,
1333    }
1334
1335
1336class ColumnConstraint(Expression):
1337    arg_types = {"this": False, "kind": True}
1338
1339    @property
1340    def kind(self) -> ColumnConstraintKind:
1341        return self.args["kind"]
1342
1343
1344class ColumnConstraintKind(Expression):
1345    pass
1346
1347
1348class AutoIncrementColumnConstraint(ColumnConstraintKind):
1349    pass
1350
1351
1352class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1353    arg_types = {"this": True, "expression": True}
1354
1355
1356class CaseSpecificColumnConstraint(ColumnConstraintKind):
1357    arg_types = {"not_": True}
1358
1359
1360class CharacterSetColumnConstraint(ColumnConstraintKind):
1361    arg_types = {"this": True}
1362
1363
1364class CheckColumnConstraint(ColumnConstraintKind):
1365    pass
1366
1367
1368class ClusteredColumnConstraint(ColumnConstraintKind):
1369    pass
1370
1371
1372class CollateColumnConstraint(ColumnConstraintKind):
1373    pass
1374
1375
1376class CommentColumnConstraint(ColumnConstraintKind):
1377    pass
1378
1379
1380class CompressColumnConstraint(ColumnConstraintKind):
1381    pass
1382
1383
1384class DateFormatColumnConstraint(ColumnConstraintKind):
1385    arg_types = {"this": True}
1386
1387
1388class DefaultColumnConstraint(ColumnConstraintKind):
1389    pass
1390
1391
1392class EncodeColumnConstraint(ColumnConstraintKind):
1393    pass
1394
1395
1396class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1397    # this: True -> ALWAYS, this: False -> BY DEFAULT
1398    arg_types = {
1399        "this": False,
1400        "expression": False,
1401        "on_null": False,
1402        "start": False,
1403        "increment": False,
1404        "minvalue": False,
1405        "maxvalue": False,
1406        "cycle": False,
1407    }
1408
1409
1410class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1411    arg_types = {"start": True, "hidden": False}
1412
1413
1414# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1415class IndexColumnConstraint(ColumnConstraintKind):
1416    arg_types = {
1417        "this": False,
1418        "schema": True,
1419        "kind": False,
1420        "index_type": False,
1421        "options": False,
1422    }
1423
1424
1425class InlineLengthColumnConstraint(ColumnConstraintKind):
1426    pass
1427
1428
1429class NonClusteredColumnConstraint(ColumnConstraintKind):
1430    pass
1431
1432
1433class NotForReplicationColumnConstraint(ColumnConstraintKind):
1434    arg_types = {}
1435
1436
1437class NotNullColumnConstraint(ColumnConstraintKind):
1438    arg_types = {"allow_null": False}
1439
1440
1441# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1442class OnUpdateColumnConstraint(ColumnConstraintKind):
1443    pass
1444
1445
1446class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1447    arg_types = {"desc": False}
1448
1449
1450class TitleColumnConstraint(ColumnConstraintKind):
1451    pass
1452
1453
1454class UniqueColumnConstraint(ColumnConstraintKind):
1455    arg_types = {"this": False, "index_type": False}
1456
1457
1458class UppercaseColumnConstraint(ColumnConstraintKind):
1459    arg_types: t.Dict[str, t.Any] = {}
1460
1461
1462class PathColumnConstraint(ColumnConstraintKind):
1463    pass
1464
1465
1466# computed column expression
1467# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1468class ComputedColumnConstraint(ColumnConstraintKind):
1469    arg_types = {"this": True, "persisted": False, "not_null": False}
1470
1471
1472class Constraint(Expression):
1473    arg_types = {"this": True, "expressions": True}
1474
1475
1476class Delete(DML):
1477    arg_types = {
1478        "with": False,
1479        "this": False,
1480        "using": False,
1481        "where": False,
1482        "returning": False,
1483        "limit": False,
1484        "tables": False,  # Multiple-Table Syntax (MySQL)
1485    }
1486
1487    def delete(
1488        self,
1489        table: ExpOrStr,
1490        dialect: DialectType = None,
1491        copy: bool = True,
1492        **opts,
1493    ) -> Delete:
1494        """
1495        Create a DELETE expression or replace the table on an existing DELETE expression.
1496
1497        Example:
1498            >>> delete("tbl").sql()
1499            'DELETE FROM tbl'
1500
1501        Args:
1502            table: the table from which to delete.
1503            dialect: the dialect used to parse the input expression.
1504            copy: if `False`, modify this expression instance in-place.
1505            opts: other options to use to parse the input expressions.
1506
1507        Returns:
1508            Delete: the modified expression.
1509        """
1510        return _apply_builder(
1511            expression=table,
1512            instance=self,
1513            arg="this",
1514            dialect=dialect,
1515            into=Table,
1516            copy=copy,
1517            **opts,
1518        )
1519
1520    def where(
1521        self,
1522        *expressions: t.Optional[ExpOrStr],
1523        append: bool = True,
1524        dialect: DialectType = None,
1525        copy: bool = True,
1526        **opts,
1527    ) -> Delete:
1528        """
1529        Append to or set the WHERE expressions.
1530
1531        Example:
1532            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1533            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1534
1535        Args:
1536            *expressions: the SQL code strings to parse.
1537                If an `Expression` instance is passed, it will be used as-is.
1538                Multiple expressions are combined with an AND operator.
1539            append: if `True`, AND the new expressions to any existing expression.
1540                Otherwise, this resets the expression.
1541            dialect: the dialect used to parse the input expressions.
1542            copy: if `False`, modify this expression instance in-place.
1543            opts: other options to use to parse the input expressions.
1544
1545        Returns:
1546            Delete: the modified expression.
1547        """
1548        return _apply_conjunction_builder(
1549            *expressions,
1550            instance=self,
1551            arg="where",
1552            append=append,
1553            into=Where,
1554            dialect=dialect,
1555            copy=copy,
1556            **opts,
1557        )
1558
1559
1560class Drop(Expression):
1561    arg_types = {
1562        "this": False,
1563        "kind": False,
1564        "exists": False,
1565        "temporary": False,
1566        "materialized": False,
1567        "cascade": False,
1568        "constraints": False,
1569        "purge": False,
1570    }
1571
1572
1573class Filter(Expression):
1574    arg_types = {"this": True, "expression": True}
1575
1576
1577class Check(Expression):
1578    pass
1579
1580
1581# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1582class Connect(Expression):
1583    arg_types = {"start": False, "connect": True}
1584
1585
1586class Prior(Expression):
1587    pass
1588
1589
1590class Directory(Expression):
1591    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1592    arg_types = {"this": True, "local": False, "row_format": False}
1593
1594
1595class ForeignKey(Expression):
1596    arg_types = {
1597        "expressions": True,
1598        "reference": False,
1599        "delete": False,
1600        "update": False,
1601    }
1602
1603
1604class ColumnPrefix(Expression):
1605    arg_types = {"this": True, "expression": True}
1606
1607
1608class PrimaryKey(Expression):
1609    arg_types = {"expressions": True, "options": False}
1610
1611
1612# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1613# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1614class Into(Expression):
1615    arg_types = {"this": True, "temporary": False, "unlogged": False}
1616
1617
1618class From(Expression):
1619    @property
1620    def name(self) -> str:
1621        return self.this.name
1622
1623    @property
1624    def alias_or_name(self) -> str:
1625        return self.this.alias_or_name
1626
1627
1628class Having(Expression):
1629    pass
1630
1631
1632class Hint(Expression):
1633    arg_types = {"expressions": True}
1634
1635
1636class JoinHint(Expression):
1637    arg_types = {"this": True, "expressions": True}
1638
1639
1640class Identifier(Expression):
1641    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1642
1643    @property
1644    def quoted(self) -> bool:
1645        return bool(self.args.get("quoted"))
1646
1647    @property
1648    def hashable_args(self) -> t.Any:
1649        return (self.this, self.quoted)
1650
1651    @property
1652    def output_name(self) -> str:
1653        return self.name
1654
1655
1656# https://www.postgresql.org/docs/current/indexes-opclass.html
1657class Opclass(Expression):
1658    arg_types = {"this": True, "expression": True}
1659
1660
1661class Index(Expression):
1662    arg_types = {
1663        "this": False,
1664        "table": False,
1665        "using": False,
1666        "where": False,
1667        "columns": False,
1668        "unique": False,
1669        "primary": False,
1670        "amp": False,  # teradata
1671        "partition_by": False,  # teradata
1672        "where": False,  # postgres partial indexes
1673    }
1674
1675
1676class Insert(DDL, DML):
1677    arg_types = {
1678        "with": False,
1679        "this": True,
1680        "expression": False,
1681        "conflict": False,
1682        "returning": False,
1683        "overwrite": False,
1684        "exists": False,
1685        "partition": False,
1686        "alternative": False,
1687        "where": False,
1688        "ignore": False,
1689        "by_name": False,
1690    }
1691
1692    def with_(
1693        self,
1694        alias: ExpOrStr,
1695        as_: ExpOrStr,
1696        recursive: t.Optional[bool] = None,
1697        append: bool = True,
1698        dialect: DialectType = None,
1699        copy: bool = True,
1700        **opts,
1701    ) -> Insert:
1702        """
1703        Append to or set the common table expressions.
1704
1705        Example:
1706            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1707            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1708
1709        Args:
1710            alias: the SQL code string to parse as the table name.
1711                If an `Expression` instance is passed, this is used as-is.
1712            as_: the SQL code string to parse as the table expression.
1713                If an `Expression` instance is passed, it will be used as-is.
1714            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1715            append: if `True`, add to any existing expressions.
1716                Otherwise, this resets the expressions.
1717            dialect: the dialect used to parse the input expression.
1718            copy: if `False`, modify this expression instance in-place.
1719            opts: other options to use to parse the input expressions.
1720
1721        Returns:
1722            The modified expression.
1723        """
1724        return _apply_cte_builder(
1725            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1726        )
1727
1728
1729class OnConflict(Expression):
1730    arg_types = {
1731        "duplicate": False,
1732        "expressions": False,
1733        "nothing": False,
1734        "key": False,
1735        "constraint": False,
1736    }
1737
1738
1739class Returning(Expression):
1740    arg_types = {"expressions": True, "into": False}
1741
1742
1743# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1744class Introducer(Expression):
1745    arg_types = {"this": True, "expression": True}
1746
1747
1748# national char, like n'utf8'
1749class National(Expression):
1750    pass
1751
1752
1753class LoadData(Expression):
1754    arg_types = {
1755        "this": True,
1756        "local": False,
1757        "overwrite": False,
1758        "inpath": True,
1759        "partition": False,
1760        "input_format": False,
1761        "serde": False,
1762    }
1763
1764
1765class Partition(Expression):
1766    arg_types = {"expressions": True}
1767
1768
1769class Fetch(Expression):
1770    arg_types = {
1771        "direction": False,
1772        "count": False,
1773        "percent": False,
1774        "with_ties": False,
1775    }
1776
1777
1778class Group(Expression):
1779    arg_types = {
1780        "expressions": False,
1781        "grouping_sets": False,
1782        "cube": False,
1783        "rollup": False,
1784        "totals": False,
1785        "all": False,
1786    }
1787
1788
1789class Lambda(Expression):
1790    arg_types = {"this": True, "expressions": True}
1791
1792
1793class Limit(Expression):
1794    arg_types = {"this": False, "expression": True, "offset": False}
1795
1796
1797class Literal(Condition):
1798    arg_types = {"this": True, "is_string": True}
1799
1800    @property
1801    def hashable_args(self) -> t.Any:
1802        return (self.this, self.args.get("is_string"))
1803
1804    @classmethod
1805    def number(cls, number) -> Literal:
1806        return cls(this=str(number), is_string=False)
1807
1808    @classmethod
1809    def string(cls, string) -> Literal:
1810        return cls(this=str(string), is_string=True)
1811
1812    @property
1813    def output_name(self) -> str:
1814        return self.name
1815
1816
1817class Join(Expression):
1818    arg_types = {
1819        "this": True,
1820        "on": False,
1821        "side": False,
1822        "kind": False,
1823        "using": False,
1824        "method": False,
1825        "global": False,
1826        "hint": False,
1827    }
1828
1829    @property
1830    def method(self) -> str:
1831        return self.text("method").upper()
1832
1833    @property
1834    def kind(self) -> str:
1835        return self.text("kind").upper()
1836
1837    @property
1838    def side(self) -> str:
1839        return self.text("side").upper()
1840
1841    @property
1842    def hint(self) -> str:
1843        return self.text("hint").upper()
1844
1845    @property
1846    def alias_or_name(self) -> str:
1847        return self.this.alias_or_name
1848
1849    def on(
1850        self,
1851        *expressions: t.Optional[ExpOrStr],
1852        append: bool = True,
1853        dialect: DialectType = None,
1854        copy: bool = True,
1855        **opts,
1856    ) -> Join:
1857        """
1858        Append to or set the ON expressions.
1859
1860        Example:
1861            >>> import sqlglot
1862            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1863            'JOIN x ON y = 1'
1864
1865        Args:
1866            *expressions: the SQL code strings to parse.
1867                If an `Expression` instance is passed, it will be used as-is.
1868                Multiple expressions are combined with an AND operator.
1869            append: if `True`, AND the new expressions to any existing expression.
1870                Otherwise, this resets the expression.
1871            dialect: the dialect used to parse the input expressions.
1872            copy: if `False`, modify this expression instance in-place.
1873            opts: other options to use to parse the input expressions.
1874
1875        Returns:
1876            The modified Join expression.
1877        """
1878        join = _apply_conjunction_builder(
1879            *expressions,
1880            instance=self,
1881            arg="on",
1882            append=append,
1883            dialect=dialect,
1884            copy=copy,
1885            **opts,
1886        )
1887
1888        if join.kind == "CROSS":
1889            join.set("kind", None)
1890
1891        return join
1892
1893    def using(
1894        self,
1895        *expressions: t.Optional[ExpOrStr],
1896        append: bool = True,
1897        dialect: DialectType = None,
1898        copy: bool = True,
1899        **opts,
1900    ) -> Join:
1901        """
1902        Append to or set the USING expressions.
1903
1904        Example:
1905            >>> import sqlglot
1906            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1907            'JOIN x USING (foo, bla)'
1908
1909        Args:
1910            *expressions: the SQL code strings to parse.
1911                If an `Expression` instance is passed, it will be used as-is.
1912            append: if `True`, concatenate the new expressions to the existing "using" list.
1913                Otherwise, this resets the expression.
1914            dialect: the dialect used to parse the input expressions.
1915            copy: if `False`, modify this expression instance in-place.
1916            opts: other options to use to parse the input expressions.
1917
1918        Returns:
1919            The modified Join expression.
1920        """
1921        join = _apply_list_builder(
1922            *expressions,
1923            instance=self,
1924            arg="using",
1925            append=append,
1926            dialect=dialect,
1927            copy=copy,
1928            **opts,
1929        )
1930
1931        if join.kind == "CROSS":
1932            join.set("kind", None)
1933
1934        return join
1935
1936
1937class Lateral(UDTF):
1938    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1939
1940
1941class MatchRecognize(Expression):
1942    arg_types = {
1943        "partition_by": False,
1944        "order": False,
1945        "measures": False,
1946        "rows": False,
1947        "after": False,
1948        "pattern": False,
1949        "define": False,
1950        "alias": False,
1951    }
1952
1953
1954# Clickhouse FROM FINAL modifier
1955# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1956class Final(Expression):
1957    pass
1958
1959
1960class Offset(Expression):
1961    arg_types = {"this": False, "expression": True}
1962
1963
1964class Order(Expression):
1965    arg_types = {"this": False, "expressions": True}
1966
1967
1968# hive specific sorts
1969# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1970class Cluster(Order):
1971    pass
1972
1973
1974class Distribute(Order):
1975    pass
1976
1977
1978class Sort(Order):
1979    pass
1980
1981
1982class Ordered(Expression):
1983    arg_types = {"this": True, "desc": False, "nulls_first": True}
1984
1985
1986class Property(Expression):
1987    arg_types = {"this": True, "value": True}
1988
1989
1990class AlgorithmProperty(Property):
1991    arg_types = {"this": True}
1992
1993
1994class AutoIncrementProperty(Property):
1995    arg_types = {"this": True}
1996
1997
1998class BlockCompressionProperty(Property):
1999    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
2000
2001
2002class CharacterSetProperty(Property):
2003    arg_types = {"this": True, "default": True}
2004
2005
2006class ChecksumProperty(Property):
2007    arg_types = {"on": False, "default": False}
2008
2009
2010class CollateProperty(Property):
2011    arg_types = {"this": True, "default": False}
2012
2013
2014class CopyGrantsProperty(Property):
2015    arg_types = {}
2016
2017
2018class DataBlocksizeProperty(Property):
2019    arg_types = {
2020        "size": False,
2021        "units": False,
2022        "minimum": False,
2023        "maximum": False,
2024        "default": False,
2025    }
2026
2027
2028class DefinerProperty(Property):
2029    arg_types = {"this": True}
2030
2031
2032class DistKeyProperty(Property):
2033    arg_types = {"this": True}
2034
2035
2036class DistStyleProperty(Property):
2037    arg_types = {"this": True}
2038
2039
2040class EngineProperty(Property):
2041    arg_types = {"this": True}
2042
2043
2044class HeapProperty(Property):
2045    arg_types = {}
2046
2047
2048class ToTableProperty(Property):
2049    arg_types = {"this": True}
2050
2051
2052class ExecuteAsProperty(Property):
2053    arg_types = {"this": True}
2054
2055
2056class ExternalProperty(Property):
2057    arg_types = {"this": False}
2058
2059
2060class FallbackProperty(Property):
2061    arg_types = {"no": True, "protection": False}
2062
2063
2064class FileFormatProperty(Property):
2065    arg_types = {"this": True}
2066
2067
2068class FreespaceProperty(Property):
2069    arg_types = {"this": True, "percent": False}
2070
2071
2072class InputModelProperty(Property):
2073    arg_types = {"this": True}
2074
2075
2076class OutputModelProperty(Property):
2077    arg_types = {"this": True}
2078
2079
2080class IsolatedLoadingProperty(Property):
2081    arg_types = {
2082        "no": True,
2083        "concurrent": True,
2084        "for_all": True,
2085        "for_insert": True,
2086        "for_none": True,
2087    }
2088
2089
2090class JournalProperty(Property):
2091    arg_types = {
2092        "no": False,
2093        "dual": False,
2094        "before": False,
2095        "local": False,
2096        "after": False,
2097    }
2098
2099
2100class LanguageProperty(Property):
2101    arg_types = {"this": True}
2102
2103
2104# spark ddl
2105class ClusteredByProperty(Property):
2106    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2107
2108
2109class DictProperty(Property):
2110    arg_types = {"this": True, "kind": True, "settings": False}
2111
2112
2113class DictSubProperty(Property):
2114    pass
2115
2116
2117class DictRange(Property):
2118    arg_types = {"this": True, "min": True, "max": True}
2119
2120
2121# Clickhouse CREATE ... ON CLUSTER modifier
2122# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2123class OnCluster(Property):
2124    arg_types = {"this": True}
2125
2126
2127class LikeProperty(Property):
2128    arg_types = {"this": True, "expressions": False}
2129
2130
2131class LocationProperty(Property):
2132    arg_types = {"this": True}
2133
2134
2135class LockingProperty(Property):
2136    arg_types = {
2137        "this": False,
2138        "kind": True,
2139        "for_or_in": False,
2140        "lock_type": True,
2141        "override": False,
2142    }
2143
2144
2145class LogProperty(Property):
2146    arg_types = {"no": True}
2147
2148
2149class MaterializedProperty(Property):
2150    arg_types = {"this": False}
2151
2152
2153class MergeBlockRatioProperty(Property):
2154    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2155
2156
2157class NoPrimaryIndexProperty(Property):
2158    arg_types = {}
2159
2160
2161class OnProperty(Property):
2162    arg_types = {"this": True}
2163
2164
2165class OnCommitProperty(Property):
2166    arg_types = {"delete": False}
2167
2168
2169class PartitionedByProperty(Property):
2170    arg_types = {"this": True}
2171
2172
2173# https://www.postgresql.org/docs/current/sql-createtable.html
2174class PartitionBoundSpec(Expression):
2175    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2176    arg_types = {
2177        "this": False,
2178        "expression": False,
2179        "from_expressions": False,
2180        "to_expressions": False,
2181    }
2182
2183
2184class PartitionedOfProperty(Property):
2185    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2186    arg_types = {"this": True, "expression": True}
2187
2188
2189class RemoteWithConnectionModelProperty(Property):
2190    arg_types = {"this": True}
2191
2192
2193class ReturnsProperty(Property):
2194    arg_types = {"this": True, "is_table": False, "table": False}
2195
2196
2197class RowFormatProperty(Property):
2198    arg_types = {"this": True}
2199
2200
2201class RowFormatDelimitedProperty(Property):
2202    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2203    arg_types = {
2204        "fields": False,
2205        "escaped": False,
2206        "collection_items": False,
2207        "map_keys": False,
2208        "lines": False,
2209        "null": False,
2210        "serde": False,
2211    }
2212
2213
2214class RowFormatSerdeProperty(Property):
2215    arg_types = {"this": True, "serde_properties": False}
2216
2217
2218# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2219class QueryTransform(Expression):
2220    arg_types = {
2221        "expressions": True,
2222        "command_script": True,
2223        "schema": False,
2224        "row_format_before": False,
2225        "record_writer": False,
2226        "row_format_after": False,
2227        "record_reader": False,
2228    }
2229
2230
2231class SampleProperty(Property):
2232    arg_types = {"this": True}
2233
2234
2235class SchemaCommentProperty(Property):
2236    arg_types = {"this": True}
2237
2238
2239class SerdeProperties(Property):
2240    arg_types = {"expressions": True}
2241
2242
2243class SetProperty(Property):
2244    arg_types = {"multi": True}
2245
2246
2247class SettingsProperty(Property):
2248    arg_types = {"expressions": True}
2249
2250
2251class SortKeyProperty(Property):
2252    arg_types = {"this": True, "compound": False}
2253
2254
2255class SqlSecurityProperty(Property):
2256    arg_types = {"definer": True}
2257
2258
2259class StabilityProperty(Property):
2260    arg_types = {"this": True}
2261
2262
2263class TemporaryProperty(Property):
2264    arg_types = {}
2265
2266
2267class TransformModelProperty(Property):
2268    arg_types = {"expressions": True}
2269
2270
2271class TransientProperty(Property):
2272    arg_types = {"this": False}
2273
2274
2275class VolatileProperty(Property):
2276    arg_types = {"this": False}
2277
2278
2279class WithDataProperty(Property):
2280    arg_types = {"no": True, "statistics": False}
2281
2282
2283class WithJournalTableProperty(Property):
2284    arg_types = {"this": True}
2285
2286
2287class WithSystemVersioningProperty(Property):
2288    # this -> history table name, expression -> data consistency check
2289    arg_types = {"this": False, "expression": False}
2290
2291
2292class Properties(Expression):
2293    arg_types = {"expressions": True}
2294
2295    NAME_TO_PROPERTY = {
2296        "ALGORITHM": AlgorithmProperty,
2297        "AUTO_INCREMENT": AutoIncrementProperty,
2298        "CHARACTER SET": CharacterSetProperty,
2299        "CLUSTERED_BY": ClusteredByProperty,
2300        "COLLATE": CollateProperty,
2301        "COMMENT": SchemaCommentProperty,
2302        "DEFINER": DefinerProperty,
2303        "DISTKEY": DistKeyProperty,
2304        "DISTSTYLE": DistStyleProperty,
2305        "ENGINE": EngineProperty,
2306        "EXECUTE AS": ExecuteAsProperty,
2307        "FORMAT": FileFormatProperty,
2308        "LANGUAGE": LanguageProperty,
2309        "LOCATION": LocationProperty,
2310        "PARTITIONED_BY": PartitionedByProperty,
2311        "RETURNS": ReturnsProperty,
2312        "ROW_FORMAT": RowFormatProperty,
2313        "SORTKEY": SortKeyProperty,
2314    }
2315
2316    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2317
2318    # CREATE property locations
2319    # Form: schema specified
2320    #   create [POST_CREATE]
2321    #     table a [POST_NAME]
2322    #     (b int) [POST_SCHEMA]
2323    #     with ([POST_WITH])
2324    #     index (b) [POST_INDEX]
2325    #
2326    # Form: alias selection
2327    #   create [POST_CREATE]
2328    #     table a [POST_NAME]
2329    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2330    #     index (c) [POST_INDEX]
2331    class Location(AutoName):
2332        POST_CREATE = auto()
2333        POST_NAME = auto()
2334        POST_SCHEMA = auto()
2335        POST_WITH = auto()
2336        POST_ALIAS = auto()
2337        POST_EXPRESSION = auto()
2338        POST_INDEX = auto()
2339        UNSUPPORTED = auto()
2340
2341    @classmethod
2342    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2343        expressions = []
2344        for key, value in properties_dict.items():
2345            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2346            if property_cls:
2347                expressions.append(property_cls(this=convert(value)))
2348            else:
2349                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2350
2351        return cls(expressions=expressions)
2352
2353
2354class Qualify(Expression):
2355    pass
2356
2357
2358class InputOutputFormat(Expression):
2359    arg_types = {"input_format": False, "output_format": False}
2360
2361
2362# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2363class Return(Expression):
2364    pass
2365
2366
2367class Reference(Expression):
2368    arg_types = {"this": True, "expressions": False, "options": False}
2369
2370
2371class Tuple(Expression):
2372    arg_types = {"expressions": False}
2373
2374    def isin(
2375        self,
2376        *expressions: t.Any,
2377        query: t.Optional[ExpOrStr] = None,
2378        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2379        copy: bool = True,
2380        **opts,
2381    ) -> In:
2382        return In(
2383            this=maybe_copy(self, copy),
2384            expressions=[convert(e, copy=copy) for e in expressions],
2385            query=maybe_parse(query, copy=copy, **opts) if query else None,
2386            unnest=Unnest(
2387                expressions=[
2388                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2389                ]
2390            )
2391            if unnest
2392            else None,
2393        )
2394
2395
2396class Subqueryable(Unionable):
2397    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2398        """
2399        Convert this expression to an aliased expression that can be used as a Subquery.
2400
2401        Example:
2402            >>> subquery = Select().select("x").from_("tbl").subquery()
2403            >>> Select().select("x").from_(subquery).sql()
2404            'SELECT x FROM (SELECT x FROM tbl)'
2405
2406        Args:
2407            alias (str | Identifier): an optional alias for the subquery
2408            copy (bool): if `False`, modify this expression instance in-place.
2409
2410        Returns:
2411            Alias: the subquery
2412        """
2413        instance = maybe_copy(self, copy)
2414        if not isinstance(alias, Expression):
2415            alias = TableAlias(this=to_identifier(alias)) if alias else None
2416
2417        return Subquery(this=instance, alias=alias)
2418
2419    def limit(
2420        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2421    ) -> Select:
2422        raise NotImplementedError
2423
2424    @property
2425    def ctes(self):
2426        with_ = self.args.get("with")
2427        if not with_:
2428            return []
2429        return with_.expressions
2430
2431    @property
2432    def selects(self) -> t.List[Expression]:
2433        raise NotImplementedError("Subqueryable objects must implement `selects`")
2434
2435    @property
2436    def named_selects(self) -> t.List[str]:
2437        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2438
2439    def select(
2440        self,
2441        *expressions: t.Optional[ExpOrStr],
2442        append: bool = True,
2443        dialect: DialectType = None,
2444        copy: bool = True,
2445        **opts,
2446    ) -> Subqueryable:
2447        raise NotImplementedError("Subqueryable objects must implement `select`")
2448
2449    def with_(
2450        self,
2451        alias: ExpOrStr,
2452        as_: ExpOrStr,
2453        recursive: t.Optional[bool] = None,
2454        append: bool = True,
2455        dialect: DialectType = None,
2456        copy: bool = True,
2457        **opts,
2458    ) -> Subqueryable:
2459        """
2460        Append to or set the common table expressions.
2461
2462        Example:
2463            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2464            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2465
2466        Args:
2467            alias: the SQL code string to parse as the table name.
2468                If an `Expression` instance is passed, this is used as-is.
2469            as_: the SQL code string to parse as the table expression.
2470                If an `Expression` instance is passed, it will be used as-is.
2471            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2472            append: if `True`, add to any existing expressions.
2473                Otherwise, this resets the expressions.
2474            dialect: the dialect used to parse the input expression.
2475            copy: if `False`, modify this expression instance in-place.
2476            opts: other options to use to parse the input expressions.
2477
2478        Returns:
2479            The modified expression.
2480        """
2481        return _apply_cte_builder(
2482            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2483        )
2484
2485
2486QUERY_MODIFIERS = {
2487    "match": False,
2488    "laterals": False,
2489    "joins": False,
2490    "connect": False,
2491    "pivots": False,
2492    "where": False,
2493    "group": False,
2494    "having": False,
2495    "qualify": False,
2496    "windows": False,
2497    "distribute": False,
2498    "sort": False,
2499    "cluster": False,
2500    "order": False,
2501    "limit": False,
2502    "offset": False,
2503    "locks": False,
2504    "sample": False,
2505    "settings": False,
2506    "format": False,
2507}
2508
2509
2510# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2511class WithTableHint(Expression):
2512    arg_types = {"expressions": True}
2513
2514
2515# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2516class IndexTableHint(Expression):
2517    arg_types = {"this": True, "expressions": False, "target": False}
2518
2519
2520class Table(Expression):
2521    arg_types = {
2522        "this": True,
2523        "alias": False,
2524        "db": False,
2525        "catalog": False,
2526        "laterals": False,
2527        "joins": False,
2528        "pivots": False,
2529        "hints": False,
2530        "system_time": False,
2531        "version": False,
2532        "format": False,
2533        "pattern": False,
2534        "index": False,
2535        "ordinality": False,
2536    }
2537
2538    @property
2539    def name(self) -> str:
2540        if isinstance(self.this, Func):
2541            return ""
2542        return self.this.name
2543
2544    @property
2545    def db(self) -> str:
2546        return self.text("db")
2547
2548    @property
2549    def catalog(self) -> str:
2550        return self.text("catalog")
2551
2552    @property
2553    def selects(self) -> t.List[Expression]:
2554        return []
2555
2556    @property
2557    def named_selects(self) -> t.List[str]:
2558        return []
2559
2560    @property
2561    def parts(self) -> t.List[Expression]:
2562        """Return the parts of a table in order catalog, db, table."""
2563        parts: t.List[Expression] = []
2564
2565        for arg in ("catalog", "db", "this"):
2566            part = self.args.get(arg)
2567
2568            if isinstance(part, Dot):
2569                parts.extend(part.flatten())
2570            elif isinstance(part, Expression):
2571                parts.append(part)
2572
2573        return parts
2574
2575
2576class Union(Subqueryable):
2577    arg_types = {
2578        "with": False,
2579        "this": True,
2580        "expression": True,
2581        "distinct": False,
2582        "by_name": False,
2583        **QUERY_MODIFIERS,
2584    }
2585
2586    def limit(
2587        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2588    ) -> Select:
2589        """
2590        Set the LIMIT expression.
2591
2592        Example:
2593            >>> select("1").union(select("1")).limit(1).sql()
2594            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2595
2596        Args:
2597            expression: the SQL code string to parse.
2598                This can also be an integer.
2599                If a `Limit` instance is passed, this is used as-is.
2600                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2601            dialect: the dialect used to parse the input expression.
2602            copy: if `False`, modify this expression instance in-place.
2603            opts: other options to use to parse the input expressions.
2604
2605        Returns:
2606            The limited subqueryable.
2607        """
2608        return (
2609            select("*")
2610            .from_(self.subquery(alias="_l_0", copy=copy))
2611            .limit(expression, dialect=dialect, copy=False, **opts)
2612        )
2613
2614    def select(
2615        self,
2616        *expressions: t.Optional[ExpOrStr],
2617        append: bool = True,
2618        dialect: DialectType = None,
2619        copy: bool = True,
2620        **opts,
2621    ) -> Union:
2622        """Append to or set the SELECT of the union recursively.
2623
2624        Example:
2625            >>> from sqlglot import parse_one
2626            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2627            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2628
2629        Args:
2630            *expressions: the SQL code strings to parse.
2631                If an `Expression` instance is passed, it will be used as-is.
2632            append: if `True`, add to any existing expressions.
2633                Otherwise, this resets the expressions.
2634            dialect: the dialect used to parse the input expressions.
2635            copy: if `False`, modify this expression instance in-place.
2636            opts: other options to use to parse the input expressions.
2637
2638        Returns:
2639            Union: the modified expression.
2640        """
2641        this = self.copy() if copy else self
2642        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2643        this.expression.unnest().select(
2644            *expressions, append=append, dialect=dialect, copy=False, **opts
2645        )
2646        return this
2647
2648    @property
2649    def named_selects(self) -> t.List[str]:
2650        return self.this.unnest().named_selects
2651
2652    @property
2653    def is_star(self) -> bool:
2654        return self.this.is_star or self.expression.is_star
2655
2656    @property
2657    def selects(self) -> t.List[Expression]:
2658        return self.this.unnest().selects
2659
2660    @property
2661    def left(self) -> Expression:
2662        return self.this
2663
2664    @property
2665    def right(self) -> Expression:
2666        return self.expression
2667
2668
2669class Except(Union):
2670    pass
2671
2672
2673class Intersect(Union):
2674    pass
2675
2676
2677class Unnest(UDTF):
2678    arg_types = {
2679        "expressions": True,
2680        "alias": False,
2681        "offset": False,
2682    }
2683
2684
2685class Update(Expression):
2686    arg_types = {
2687        "with": False,
2688        "this": False,
2689        "expressions": True,
2690        "from": False,
2691        "where": False,
2692        "returning": False,
2693        "order": False,
2694        "limit": False,
2695    }
2696
2697
2698class Values(UDTF):
2699    arg_types = {"expressions": True, "alias": False}
2700
2701
2702class Var(Expression):
2703    pass
2704
2705
2706class Version(Expression):
2707    """
2708    Time travel, iceberg, bigquery etc
2709    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2710    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2711    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2712    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2713    this is either TIMESTAMP or VERSION
2714    kind is ("AS OF", "BETWEEN")
2715    """
2716
2717    arg_types = {"this": True, "kind": True, "expression": False}
2718
2719
2720class Schema(Expression):
2721    arg_types = {"this": False, "expressions": False}
2722
2723
2724# https://dev.mysql.com/doc/refman/8.0/en/select.html
2725# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2726class Lock(Expression):
2727    arg_types = {"update": True, "expressions": False, "wait": False}
2728
2729
2730class Select(Subqueryable):
2731    arg_types = {
2732        "with": False,
2733        "kind": False,
2734        "expressions": False,
2735        "hint": False,
2736        "distinct": False,
2737        "into": False,
2738        "from": False,
2739        **QUERY_MODIFIERS,
2740    }
2741
2742    def from_(
2743        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2744    ) -> Select:
2745        """
2746        Set the FROM expression.
2747
2748        Example:
2749            >>> Select().from_("tbl").select("x").sql()
2750            'SELECT x FROM tbl'
2751
2752        Args:
2753            expression : the SQL code strings to parse.
2754                If a `From` instance is passed, this is used as-is.
2755                If another `Expression` instance is passed, it will be wrapped in a `From`.
2756            dialect: the dialect used to parse the input expression.
2757            copy: if `False`, modify this expression instance in-place.
2758            opts: other options to use to parse the input expressions.
2759
2760        Returns:
2761            The modified Select expression.
2762        """
2763        return _apply_builder(
2764            expression=expression,
2765            instance=self,
2766            arg="from",
2767            into=From,
2768            prefix="FROM",
2769            dialect=dialect,
2770            copy=copy,
2771            **opts,
2772        )
2773
2774    def group_by(
2775        self,
2776        *expressions: t.Optional[ExpOrStr],
2777        append: bool = True,
2778        dialect: DialectType = None,
2779        copy: bool = True,
2780        **opts,
2781    ) -> Select:
2782        """
2783        Set the GROUP BY expression.
2784
2785        Example:
2786            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2787            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2788
2789        Args:
2790            *expressions: the SQL code strings to parse.
2791                If a `Group` instance is passed, this is used as-is.
2792                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2793                If nothing is passed in then a group by is not applied to the expression
2794            append: if `True`, add to any existing expressions.
2795                Otherwise, this flattens all the `Group` expression into a single expression.
2796            dialect: the dialect used to parse the input expression.
2797            copy: if `False`, modify this expression instance in-place.
2798            opts: other options to use to parse the input expressions.
2799
2800        Returns:
2801            The modified Select expression.
2802        """
2803        if not expressions:
2804            return self if not copy else self.copy()
2805
2806        return _apply_child_list_builder(
2807            *expressions,
2808            instance=self,
2809            arg="group",
2810            append=append,
2811            copy=copy,
2812            prefix="GROUP BY",
2813            into=Group,
2814            dialect=dialect,
2815            **opts,
2816        )
2817
2818    def order_by(
2819        self,
2820        *expressions: t.Optional[ExpOrStr],
2821        append: bool = True,
2822        dialect: DialectType = None,
2823        copy: bool = True,
2824        **opts,
2825    ) -> Select:
2826        """
2827        Set the ORDER BY expression.
2828
2829        Example:
2830            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2831            'SELECT x FROM tbl ORDER BY x DESC'
2832
2833        Args:
2834            *expressions: the SQL code strings to parse.
2835                If a `Group` instance is passed, this is used as-is.
2836                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2837            append: if `True`, add to any existing expressions.
2838                Otherwise, this flattens all the `Order` expression into a single expression.
2839            dialect: the dialect used to parse the input expression.
2840            copy: if `False`, modify this expression instance in-place.
2841            opts: other options to use to parse the input expressions.
2842
2843        Returns:
2844            The modified Select expression.
2845        """
2846        return _apply_child_list_builder(
2847            *expressions,
2848            instance=self,
2849            arg="order",
2850            append=append,
2851            copy=copy,
2852            prefix="ORDER BY",
2853            into=Order,
2854            dialect=dialect,
2855            **opts,
2856        )
2857
2858    def sort_by(
2859        self,
2860        *expressions: t.Optional[ExpOrStr],
2861        append: bool = True,
2862        dialect: DialectType = None,
2863        copy: bool = True,
2864        **opts,
2865    ) -> Select:
2866        """
2867        Set the SORT BY expression.
2868
2869        Example:
2870            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2871            'SELECT x FROM tbl SORT BY x DESC'
2872
2873        Args:
2874            *expressions: the SQL code strings to parse.
2875                If a `Group` instance is passed, this is used as-is.
2876                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2877            append: if `True`, add to any existing expressions.
2878                Otherwise, this flattens all the `Order` expression into a single expression.
2879            dialect: the dialect used to parse the input expression.
2880            copy: if `False`, modify this expression instance in-place.
2881            opts: other options to use to parse the input expressions.
2882
2883        Returns:
2884            The modified Select expression.
2885        """
2886        return _apply_child_list_builder(
2887            *expressions,
2888            instance=self,
2889            arg="sort",
2890            append=append,
2891            copy=copy,
2892            prefix="SORT BY",
2893            into=Sort,
2894            dialect=dialect,
2895            **opts,
2896        )
2897
2898    def cluster_by(
2899        self,
2900        *expressions: t.Optional[ExpOrStr],
2901        append: bool = True,
2902        dialect: DialectType = None,
2903        copy: bool = True,
2904        **opts,
2905    ) -> Select:
2906        """
2907        Set the CLUSTER BY expression.
2908
2909        Example:
2910            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2911            'SELECT x FROM tbl CLUSTER BY x DESC'
2912
2913        Args:
2914            *expressions: the SQL code strings to parse.
2915                If a `Group` instance is passed, this is used as-is.
2916                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2917            append: if `True`, add to any existing expressions.
2918                Otherwise, this flattens all the `Order` expression into a single expression.
2919            dialect: the dialect used to parse the input expression.
2920            copy: if `False`, modify this expression instance in-place.
2921            opts: other options to use to parse the input expressions.
2922
2923        Returns:
2924            The modified Select expression.
2925        """
2926        return _apply_child_list_builder(
2927            *expressions,
2928            instance=self,
2929            arg="cluster",
2930            append=append,
2931            copy=copy,
2932            prefix="CLUSTER BY",
2933            into=Cluster,
2934            dialect=dialect,
2935            **opts,
2936        )
2937
2938    def limit(
2939        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2940    ) -> Select:
2941        """
2942        Set the LIMIT expression.
2943
2944        Example:
2945            >>> Select().from_("tbl").select("x").limit(10).sql()
2946            'SELECT x FROM tbl LIMIT 10'
2947
2948        Args:
2949            expression: the SQL code string to parse.
2950                This can also be an integer.
2951                If a `Limit` instance is passed, this is used as-is.
2952                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2953            dialect: the dialect used to parse the input expression.
2954            copy: if `False`, modify this expression instance in-place.
2955            opts: other options to use to parse the input expressions.
2956
2957        Returns:
2958            Select: the modified expression.
2959        """
2960        return _apply_builder(
2961            expression=expression,
2962            instance=self,
2963            arg="limit",
2964            into=Limit,
2965            prefix="LIMIT",
2966            dialect=dialect,
2967            copy=copy,
2968            into_arg="expression",
2969            **opts,
2970        )
2971
2972    def offset(
2973        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2974    ) -> Select:
2975        """
2976        Set the OFFSET expression.
2977
2978        Example:
2979            >>> Select().from_("tbl").select("x").offset(10).sql()
2980            'SELECT x FROM tbl OFFSET 10'
2981
2982        Args:
2983            expression: the SQL code string to parse.
2984                This can also be an integer.
2985                If a `Offset` instance is passed, this is used as-is.
2986                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2987            dialect: the dialect used to parse the input expression.
2988            copy: if `False`, modify this expression instance in-place.
2989            opts: other options to use to parse the input expressions.
2990
2991        Returns:
2992            The modified Select expression.
2993        """
2994        return _apply_builder(
2995            expression=expression,
2996            instance=self,
2997            arg="offset",
2998            into=Offset,
2999            prefix="OFFSET",
3000            dialect=dialect,
3001            copy=copy,
3002            into_arg="expression",
3003            **opts,
3004        )
3005
3006    def select(
3007        self,
3008        *expressions: t.Optional[ExpOrStr],
3009        append: bool = True,
3010        dialect: DialectType = None,
3011        copy: bool = True,
3012        **opts,
3013    ) -> Select:
3014        """
3015        Append to or set the SELECT expressions.
3016
3017        Example:
3018            >>> Select().select("x", "y").sql()
3019            'SELECT x, y'
3020
3021        Args:
3022            *expressions: the SQL code strings to parse.
3023                If an `Expression` instance is passed, it will be used as-is.
3024            append: if `True`, add to any existing expressions.
3025                Otherwise, this resets the expressions.
3026            dialect: the dialect used to parse the input expressions.
3027            copy: if `False`, modify this expression instance in-place.
3028            opts: other options to use to parse the input expressions.
3029
3030        Returns:
3031            The modified Select expression.
3032        """
3033        return _apply_list_builder(
3034            *expressions,
3035            instance=self,
3036            arg="expressions",
3037            append=append,
3038            dialect=dialect,
3039            copy=copy,
3040            **opts,
3041        )
3042
3043    def lateral(
3044        self,
3045        *expressions: t.Optional[ExpOrStr],
3046        append: bool = True,
3047        dialect: DialectType = None,
3048        copy: bool = True,
3049        **opts,
3050    ) -> Select:
3051        """
3052        Append to or set the LATERAL expressions.
3053
3054        Example:
3055            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3056            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3057
3058        Args:
3059            *expressions: the SQL code strings to parse.
3060                If an `Expression` instance is passed, it will be used as-is.
3061            append: if `True`, add to any existing expressions.
3062                Otherwise, this resets the expressions.
3063            dialect: the dialect used to parse the input expressions.
3064            copy: if `False`, modify this expression instance in-place.
3065            opts: other options to use to parse the input expressions.
3066
3067        Returns:
3068            The modified Select expression.
3069        """
3070        return _apply_list_builder(
3071            *expressions,
3072            instance=self,
3073            arg="laterals",
3074            append=append,
3075            into=Lateral,
3076            prefix="LATERAL VIEW",
3077            dialect=dialect,
3078            copy=copy,
3079            **opts,
3080        )
3081
3082    def join(
3083        self,
3084        expression: ExpOrStr,
3085        on: t.Optional[ExpOrStr] = None,
3086        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3087        append: bool = True,
3088        join_type: t.Optional[str] = None,
3089        join_alias: t.Optional[Identifier | str] = None,
3090        dialect: DialectType = None,
3091        copy: bool = True,
3092        **opts,
3093    ) -> Select:
3094        """
3095        Append to or set the JOIN expressions.
3096
3097        Example:
3098            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3099            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3100
3101            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3102            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3103
3104            Use `join_type` to change the type of join:
3105
3106            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3107            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3108
3109        Args:
3110            expression: the SQL code string to parse.
3111                If an `Expression` instance is passed, it will be used as-is.
3112            on: optionally specify the join "on" criteria as a SQL string.
3113                If an `Expression` instance is passed, it will be used as-is.
3114            using: optionally specify the join "using" criteria as a SQL string.
3115                If an `Expression` instance is passed, it will be used as-is.
3116            append: if `True`, add to any existing expressions.
3117                Otherwise, this resets the expressions.
3118            join_type: if set, alter the parsed join type.
3119            join_alias: an optional alias for the joined source.
3120            dialect: the dialect used to parse the input expressions.
3121            copy: if `False`, modify this expression instance in-place.
3122            opts: other options to use to parse the input expressions.
3123
3124        Returns:
3125            Select: the modified expression.
3126        """
3127        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3128
3129        try:
3130            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3131        except ParseError:
3132            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3133
3134        join = expression if isinstance(expression, Join) else Join(this=expression)
3135
3136        if isinstance(join.this, Select):
3137            join.this.replace(join.this.subquery())
3138
3139        if join_type:
3140            method: t.Optional[Token]
3141            side: t.Optional[Token]
3142            kind: t.Optional[Token]
3143
3144            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3145
3146            if method:
3147                join.set("method", method.text)
3148            if side:
3149                join.set("side", side.text)
3150            if kind:
3151                join.set("kind", kind.text)
3152
3153        if on:
3154            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3155            join.set("on", on)
3156
3157        if using:
3158            join = _apply_list_builder(
3159                *ensure_list(using),
3160                instance=join,
3161                arg="using",
3162                append=append,
3163                copy=copy,
3164                into=Identifier,
3165                **opts,
3166            )
3167
3168        if join_alias:
3169            join.set("this", alias_(join.this, join_alias, table=True))
3170
3171        return _apply_list_builder(
3172            join,
3173            instance=self,
3174            arg="joins",
3175            append=append,
3176            copy=copy,
3177            **opts,
3178        )
3179
3180    def where(
3181        self,
3182        *expressions: t.Optional[ExpOrStr],
3183        append: bool = True,
3184        dialect: DialectType = None,
3185        copy: bool = True,
3186        **opts,
3187    ) -> Select:
3188        """
3189        Append to or set the WHERE expressions.
3190
3191        Example:
3192            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3193            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3194
3195        Args:
3196            *expressions: the SQL code strings to parse.
3197                If an `Expression` instance is passed, it will be used as-is.
3198                Multiple expressions are combined with an AND operator.
3199            append: if `True`, AND the new expressions to any existing expression.
3200                Otherwise, this resets the expression.
3201            dialect: the dialect used to parse the input expressions.
3202            copy: if `False`, modify this expression instance in-place.
3203            opts: other options to use to parse the input expressions.
3204
3205        Returns:
3206            Select: the modified expression.
3207        """
3208        return _apply_conjunction_builder(
3209            *expressions,
3210            instance=self,
3211            arg="where",
3212            append=append,
3213            into=Where,
3214            dialect=dialect,
3215            copy=copy,
3216            **opts,
3217        )
3218
3219    def having(
3220        self,
3221        *expressions: t.Optional[ExpOrStr],
3222        append: bool = True,
3223        dialect: DialectType = None,
3224        copy: bool = True,
3225        **opts,
3226    ) -> Select:
3227        """
3228        Append to or set the HAVING expressions.
3229
3230        Example:
3231            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3232            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3233
3234        Args:
3235            *expressions: the SQL code strings to parse.
3236                If an `Expression` instance is passed, it will be used as-is.
3237                Multiple expressions are combined with an AND operator.
3238            append: if `True`, AND the new expressions to any existing expression.
3239                Otherwise, this resets the expression.
3240            dialect: the dialect used to parse the input expressions.
3241            copy: if `False`, modify this expression instance in-place.
3242            opts: other options to use to parse the input expressions.
3243
3244        Returns:
3245            The modified Select expression.
3246        """
3247        return _apply_conjunction_builder(
3248            *expressions,
3249            instance=self,
3250            arg="having",
3251            append=append,
3252            into=Having,
3253            dialect=dialect,
3254            copy=copy,
3255            **opts,
3256        )
3257
3258    def window(
3259        self,
3260        *expressions: t.Optional[ExpOrStr],
3261        append: bool = True,
3262        dialect: DialectType = None,
3263        copy: bool = True,
3264        **opts,
3265    ) -> Select:
3266        return _apply_list_builder(
3267            *expressions,
3268            instance=self,
3269            arg="windows",
3270            append=append,
3271            into=Window,
3272            dialect=dialect,
3273            copy=copy,
3274            **opts,
3275        )
3276
3277    def qualify(
3278        self,
3279        *expressions: t.Optional[ExpOrStr],
3280        append: bool = True,
3281        dialect: DialectType = None,
3282        copy: bool = True,
3283        **opts,
3284    ) -> Select:
3285        return _apply_conjunction_builder(
3286            *expressions,
3287            instance=self,
3288            arg="qualify",
3289            append=append,
3290            into=Qualify,
3291            dialect=dialect,
3292            copy=copy,
3293            **opts,
3294        )
3295
3296    def distinct(
3297        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3298    ) -> Select:
3299        """
3300        Set the OFFSET expression.
3301
3302        Example:
3303            >>> Select().from_("tbl").select("x").distinct().sql()
3304            'SELECT DISTINCT x FROM tbl'
3305
3306        Args:
3307            ons: the expressions to distinct on
3308            distinct: whether the Select should be distinct
3309            copy: if `False`, modify this expression instance in-place.
3310
3311        Returns:
3312            Select: the modified expression.
3313        """
3314        instance = maybe_copy(self, copy)
3315        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3316        instance.set("distinct", Distinct(on=on) if distinct else None)
3317        return instance
3318
3319    def ctas(
3320        self,
3321        table: ExpOrStr,
3322        properties: t.Optional[t.Dict] = None,
3323        dialect: DialectType = None,
3324        copy: bool = True,
3325        **opts,
3326    ) -> Create:
3327        """
3328        Convert this expression to a CREATE TABLE AS statement.
3329
3330        Example:
3331            >>> Select().select("*").from_("tbl").ctas("x").sql()
3332            'CREATE TABLE x AS SELECT * FROM tbl'
3333
3334        Args:
3335            table: the SQL code string to parse as the table name.
3336                If another `Expression` instance is passed, it will be used as-is.
3337            properties: an optional mapping of table properties
3338            dialect: the dialect used to parse the input table.
3339            copy: if `False`, modify this expression instance in-place.
3340            opts: other options to use to parse the input table.
3341
3342        Returns:
3343            The new Create expression.
3344        """
3345        instance = maybe_copy(self, copy)
3346        table_expression = maybe_parse(
3347            table,
3348            into=Table,
3349            dialect=dialect,
3350            **opts,
3351        )
3352        properties_expression = None
3353        if properties:
3354            properties_expression = Properties.from_dict(properties)
3355
3356        return Create(
3357            this=table_expression,
3358            kind="table",
3359            expression=instance,
3360            properties=properties_expression,
3361        )
3362
3363    def lock(self, update: bool = True, copy: bool = True) -> Select:
3364        """
3365        Set the locking read mode for this expression.
3366
3367        Examples:
3368            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3369            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3370
3371            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3372            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3373
3374        Args:
3375            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3376            copy: if `False`, modify this expression instance in-place.
3377
3378        Returns:
3379            The modified expression.
3380        """
3381        inst = maybe_copy(self, copy)
3382        inst.set("locks", [Lock(update=update)])
3383
3384        return inst
3385
3386    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3387        """
3388        Set hints for this expression.
3389
3390        Examples:
3391            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3392            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3393
3394        Args:
3395            hints: The SQL code strings to parse as the hints.
3396                If an `Expression` instance is passed, it will be used as-is.
3397            dialect: The dialect used to parse the hints.
3398            copy: If `False`, modify this expression instance in-place.
3399
3400        Returns:
3401            The modified expression.
3402        """
3403        inst = maybe_copy(self, copy)
3404        inst.set(
3405            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3406        )
3407
3408        return inst
3409
3410    @property
3411    def named_selects(self) -> t.List[str]:
3412        return [e.output_name for e in self.expressions if e.alias_or_name]
3413
3414    @property
3415    def is_star(self) -> bool:
3416        return any(expression.is_star for expression in self.expressions)
3417
3418    @property
3419    def selects(self) -> t.List[Expression]:
3420        return self.expressions
3421
3422
3423class Subquery(DerivedTable, Unionable):
3424    arg_types = {
3425        "this": True,
3426        "alias": False,
3427        "with": False,
3428        **QUERY_MODIFIERS,
3429    }
3430
3431    def unnest(self):
3432        """
3433        Returns the first non subquery.
3434        """
3435        expression = self
3436        while isinstance(expression, Subquery):
3437            expression = expression.this
3438        return expression
3439
3440    def unwrap(self) -> Subquery:
3441        expression = self
3442        while expression.same_parent and expression.is_wrapper:
3443            expression = t.cast(Subquery, expression.parent)
3444        return expression
3445
3446    @property
3447    def is_wrapper(self) -> bool:
3448        """
3449        Whether this Subquery acts as a simple wrapper around another expression.
3450
3451        SELECT * FROM (((SELECT * FROM t)))
3452                      ^
3453                      This corresponds to a "wrapper" Subquery node
3454        """
3455        return all(v is None for k, v in self.args.items() if k != "this")
3456
3457    @property
3458    def is_star(self) -> bool:
3459        return self.this.is_star
3460
3461    @property
3462    def output_name(self) -> str:
3463        return self.alias
3464
3465
3466class TableSample(Expression):
3467    arg_types = {
3468        "this": False,
3469        "expressions": False,
3470        "method": False,
3471        "bucket_numerator": False,
3472        "bucket_denominator": False,
3473        "bucket_field": False,
3474        "percent": False,
3475        "rows": False,
3476        "size": False,
3477        "seed": False,
3478        "kind": False,
3479    }
3480
3481
3482class Tag(Expression):
3483    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3484
3485    arg_types = {
3486        "this": False,
3487        "prefix": False,
3488        "postfix": False,
3489    }
3490
3491
3492# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3493# https://duckdb.org/docs/sql/statements/pivot
3494class Pivot(Expression):
3495    arg_types = {
3496        "this": False,
3497        "alias": False,
3498        "expressions": False,
3499        "field": False,
3500        "unpivot": False,
3501        "using": False,
3502        "group": False,
3503        "columns": False,
3504        "include_nulls": False,
3505    }
3506
3507
3508class Window(Condition):
3509    arg_types = {
3510        "this": True,
3511        "partition_by": False,
3512        "order": False,
3513        "spec": False,
3514        "alias": False,
3515        "over": False,
3516        "first": False,
3517    }
3518
3519
3520class WindowSpec(Expression):
3521    arg_types = {
3522        "kind": False,
3523        "start": False,
3524        "start_side": False,
3525        "end": False,
3526        "end_side": False,
3527    }
3528
3529
3530class Where(Expression):
3531    pass
3532
3533
3534class Star(Expression):
3535    arg_types = {"except": False, "replace": False}
3536
3537    @property
3538    def name(self) -> str:
3539        return "*"
3540
3541    @property
3542    def output_name(self) -> str:
3543        return self.name
3544
3545
3546class Parameter(Condition):
3547    arg_types = {"this": True, "expression": False}
3548
3549
3550class SessionParameter(Condition):
3551    arg_types = {"this": True, "kind": False}
3552
3553
3554class Placeholder(Condition):
3555    arg_types = {"this": False, "kind": False}
3556
3557
3558class Null(Condition):
3559    arg_types: t.Dict[str, t.Any] = {}
3560
3561    @property
3562    def name(self) -> str:
3563        return "NULL"
3564
3565
3566class Boolean(Condition):
3567    pass
3568
3569
3570class DataTypeParam(Expression):
3571    arg_types = {"this": True, "expression": False}
3572
3573
3574class DataType(Expression):
3575    arg_types = {
3576        "this": True,
3577        "expressions": False,
3578        "nested": False,
3579        "values": False,
3580        "prefix": False,
3581        "kind": False,
3582    }
3583
3584    class Type(AutoName):
3585        ARRAY = auto()
3586        BIGDECIMAL = auto()
3587        BIGINT = auto()
3588        BIGSERIAL = auto()
3589        BINARY = auto()
3590        BIT = auto()
3591        BOOLEAN = auto()
3592        CHAR = auto()
3593        DATE = auto()
3594        DATEMULTIRANGE = auto()
3595        DATERANGE = auto()
3596        DATETIME = auto()
3597        DATETIME64 = auto()
3598        DECIMAL = auto()
3599        DOUBLE = auto()
3600        ENUM = auto()
3601        ENUM8 = auto()
3602        ENUM16 = auto()
3603        FIXEDSTRING = auto()
3604        FLOAT = auto()
3605        GEOGRAPHY = auto()
3606        GEOMETRY = auto()
3607        HLLSKETCH = auto()
3608        HSTORE = auto()
3609        IMAGE = auto()
3610        INET = auto()
3611        INT = auto()
3612        INT128 = auto()
3613        INT256 = auto()
3614        INT4MULTIRANGE = auto()
3615        INT4RANGE = auto()
3616        INT8MULTIRANGE = auto()
3617        INT8RANGE = auto()
3618        INTERVAL = auto()
3619        IPADDRESS = auto()
3620        IPPREFIX = auto()
3621        JSON = auto()
3622        JSONB = auto()
3623        LONGBLOB = auto()
3624        LONGTEXT = auto()
3625        LOWCARDINALITY = auto()
3626        MAP = auto()
3627        MEDIUMBLOB = auto()
3628        MEDIUMINT = auto()
3629        MEDIUMTEXT = auto()
3630        MONEY = auto()
3631        NCHAR = auto()
3632        NESTED = auto()
3633        NULL = auto()
3634        NULLABLE = auto()
3635        NUMMULTIRANGE = auto()
3636        NUMRANGE = auto()
3637        NVARCHAR = auto()
3638        OBJECT = auto()
3639        ROWVERSION = auto()
3640        SERIAL = auto()
3641        SET = auto()
3642        SMALLINT = auto()
3643        SMALLMONEY = auto()
3644        SMALLSERIAL = auto()
3645        STRUCT = auto()
3646        SUPER = auto()
3647        TEXT = auto()
3648        TINYBLOB = auto()
3649        TINYTEXT = auto()
3650        TIME = auto()
3651        TIMETZ = auto()
3652        TIMESTAMP = auto()
3653        TIMESTAMPLTZ = auto()
3654        TIMESTAMPTZ = auto()
3655        TIMESTAMP_S = auto()
3656        TIMESTAMP_MS = auto()
3657        TIMESTAMP_NS = auto()
3658        TINYINT = auto()
3659        TSMULTIRANGE = auto()
3660        TSRANGE = auto()
3661        TSTZMULTIRANGE = auto()
3662        TSTZRANGE = auto()
3663        UBIGINT = auto()
3664        UINT = auto()
3665        UINT128 = auto()
3666        UINT256 = auto()
3667        UMEDIUMINT = auto()
3668        UDECIMAL = auto()
3669        UNIQUEIDENTIFIER = auto()
3670        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3671        USERDEFINED = "USER-DEFINED"
3672        USMALLINT = auto()
3673        UTINYINT = auto()
3674        UUID = auto()
3675        VARBINARY = auto()
3676        VARCHAR = auto()
3677        VARIANT = auto()
3678        XML = auto()
3679        YEAR = auto()
3680
3681    TEXT_TYPES = {
3682        Type.CHAR,
3683        Type.NCHAR,
3684        Type.VARCHAR,
3685        Type.NVARCHAR,
3686        Type.TEXT,
3687    }
3688
3689    INTEGER_TYPES = {
3690        Type.INT,
3691        Type.TINYINT,
3692        Type.SMALLINT,
3693        Type.BIGINT,
3694        Type.INT128,
3695        Type.INT256,
3696        Type.BIT,
3697    }
3698
3699    FLOAT_TYPES = {
3700        Type.FLOAT,
3701        Type.DOUBLE,
3702    }
3703
3704    NUMERIC_TYPES = {
3705        *INTEGER_TYPES,
3706        *FLOAT_TYPES,
3707    }
3708
3709    TEMPORAL_TYPES = {
3710        Type.TIME,
3711        Type.TIMETZ,
3712        Type.TIMESTAMP,
3713        Type.TIMESTAMPTZ,
3714        Type.TIMESTAMPLTZ,
3715        Type.TIMESTAMP_S,
3716        Type.TIMESTAMP_MS,
3717        Type.TIMESTAMP_NS,
3718        Type.DATE,
3719        Type.DATETIME,
3720        Type.DATETIME64,
3721    }
3722
3723    @classmethod
3724    def build(
3725        cls,
3726        dtype: DATA_TYPE,
3727        dialect: DialectType = None,
3728        udt: bool = False,
3729        **kwargs,
3730    ) -> DataType:
3731        """
3732        Constructs a DataType object.
3733
3734        Args:
3735            dtype: the data type of interest.
3736            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3737            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3738                DataType, thus creating a user-defined type.
3739            kawrgs: additional arguments to pass in the constructor of DataType.
3740
3741        Returns:
3742            The constructed DataType object.
3743        """
3744        from sqlglot import parse_one
3745
3746        if isinstance(dtype, str):
3747            if dtype.upper() == "UNKNOWN":
3748                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3749
3750            try:
3751                data_type_exp = parse_one(
3752                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3753                )
3754            except ParseError:
3755                if udt:
3756                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3757                raise
3758        elif isinstance(dtype, DataType.Type):
3759            data_type_exp = DataType(this=dtype)
3760        elif isinstance(dtype, DataType):
3761            return dtype
3762        else:
3763            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3764
3765        return DataType(**{**data_type_exp.args, **kwargs})
3766
3767    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3768        """
3769        Checks whether this DataType matches one of the provided data types. Nested types or precision
3770        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3771
3772        Args:
3773            dtypes: the data types to compare this DataType to.
3774
3775        Returns:
3776            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3777        """
3778        for dtype in dtypes:
3779            other = DataType.build(dtype, udt=True)
3780
3781            if (
3782                other.expressions
3783                or self.this == DataType.Type.USERDEFINED
3784                or other.this == DataType.Type.USERDEFINED
3785            ):
3786                matches = self == other
3787            else:
3788                matches = self.this == other.this
3789
3790            if matches:
3791                return True
3792        return False
3793
3794
3795DATA_TYPE = t.Union[str, DataType, DataType.Type]
3796
3797
3798# https://www.postgresql.org/docs/15/datatype-pseudo.html
3799class PseudoType(DataType):
3800    arg_types = {"this": True}
3801
3802
3803# https://www.postgresql.org/docs/15/datatype-oid.html
3804class ObjectIdentifier(DataType):
3805    arg_types = {"this": True}
3806
3807
3808# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3809class SubqueryPredicate(Predicate):
3810    pass
3811
3812
3813class All(SubqueryPredicate):
3814    pass
3815
3816
3817class Any(SubqueryPredicate):
3818    pass
3819
3820
3821class Exists(SubqueryPredicate):
3822    pass
3823
3824
3825# Commands to interact with the databases or engines. For most of the command
3826# expressions we parse whatever comes after the command's name as a string.
3827class Command(Expression):
3828    arg_types = {"this": True, "expression": False}
3829
3830
3831class Transaction(Expression):
3832    arg_types = {"this": False, "modes": False, "mark": False}
3833
3834
3835class Commit(Expression):
3836    arg_types = {"chain": False, "this": False, "durability": False}
3837
3838
3839class Rollback(Expression):
3840    arg_types = {"savepoint": False, "this": False}
3841
3842
3843class AlterTable(Expression):
3844    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3845
3846
3847class AddConstraint(Expression):
3848    arg_types = {"this": False, "expression": False, "enforced": False}
3849
3850
3851class DropPartition(Expression):
3852    arg_types = {"expressions": True, "exists": False}
3853
3854
3855# Binary expressions like (ADD a b)
3856class Binary(Condition):
3857    arg_types = {"this": True, "expression": True}
3858
3859    @property
3860    def left(self) -> Expression:
3861        return self.this
3862
3863    @property
3864    def right(self) -> Expression:
3865        return self.expression
3866
3867
3868class Add(Binary):
3869    pass
3870
3871
3872class Connector(Binary):
3873    pass
3874
3875
3876class And(Connector):
3877    pass
3878
3879
3880class Or(Connector):
3881    pass
3882
3883
3884class BitwiseAnd(Binary):
3885    pass
3886
3887
3888class BitwiseLeftShift(Binary):
3889    pass
3890
3891
3892class BitwiseOr(Binary):
3893    pass
3894
3895
3896class BitwiseRightShift(Binary):
3897    pass
3898
3899
3900class BitwiseXor(Binary):
3901    pass
3902
3903
3904class Div(Binary):
3905    arg_types = {"this":