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
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    seq_get,
  30    split_num_words,
  31    subclasses,
  32)
  33from sqlglot.tokens import Token
  34
  35if t.TYPE_CHECKING:
  36    from sqlglot.dialects.dialect import DialectType
  37
  38E = t.TypeVar("E", bound="Expression")
  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
  55class Expression(metaclass=_Expression):
  56    """
  57    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  58    context, such as its child expressions, their names (arg keys), and whether a given child expression
  59    is optional or not.
  60
  61    Attributes:
  62        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  63            and representing expressions as strings.
  64        arg_types: determines what arguments (child nodes) are supported by an expression. It
  65            maps arg keys to booleans that indicate whether the corresponding args are optional.
  66
  67    Example:
  68        >>> class Foo(Expression):
  69        ...     arg_types = {"this": True, "expression": False}
  70
  71        The above definition informs us that Foo is an Expression that requires an argument called
  72        "this" and may also optionally receive an argument called "expression".
  73
  74    Args:
  75        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  76        parent: a reference to the parent expression (or None, in case of root expressions).
  77        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  78            uses to refer to it.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96
  97        for arg_key, value in self.args.items():
  98            self._set_parent(arg_key, value)
  99
 100    def __eq__(self, other) -> bool:
 101        return type(self) is type(other) and _norm_args(self) == _norm_args(other)
 102
 103    def __hash__(self) -> int:
 104        return hash(
 105            (
 106                self.key,
 107                tuple(
 108                    (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items()
 109                ),
 110            )
 111        )
 112
 113    @property
 114    def this(self):
 115        """
 116        Retrieves the argument with key "this".
 117        """
 118        return self.args.get("this")
 119
 120    @property
 121    def expression(self):
 122        """
 123        Retrieves the argument with key "expression".
 124        """
 125        return self.args.get("expression")
 126
 127    @property
 128    def expressions(self):
 129        """
 130        Retrieves the argument with key "expressions".
 131        """
 132        return self.args.get("expressions") or []
 133
 134    def text(self, key) -> str:
 135        """
 136        Returns a textual representation of the argument corresponding to "key". This can only be used
 137        for args that are strings or leaf Expression instances, such as identifiers and literals.
 138        """
 139        field = self.args.get(key)
 140        if isinstance(field, str):
 141            return field
 142        if isinstance(field, (Identifier, Literal, Var)):
 143            return field.this
 144        if isinstance(field, (Star, Null)):
 145            return field.name
 146        return ""
 147
 148    @property
 149    def is_string(self) -> bool:
 150        """
 151        Checks whether a Literal expression is a string.
 152        """
 153        return isinstance(self, Literal) and self.args["is_string"]
 154
 155    @property
 156    def is_number(self) -> bool:
 157        """
 158        Checks whether a Literal expression is a number.
 159        """
 160        return isinstance(self, Literal) and not self.args["is_string"]
 161
 162    @property
 163    def is_int(self) -> bool:
 164        """
 165        Checks whether a Literal expression is an integer.
 166        """
 167        if self.is_number:
 168            try:
 169                int(self.name)
 170                return True
 171            except ValueError:
 172                pass
 173        return False
 174
 175    @property
 176    def is_star(self) -> bool:
 177        """Checks whether an expression is a star."""
 178        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 179
 180    @property
 181    def alias(self) -> str:
 182        """
 183        Returns the alias of the expression, or an empty string if it's not aliased.
 184        """
 185        if isinstance(self.args.get("alias"), TableAlias):
 186            return self.args["alias"].name
 187        return self.text("alias")
 188
 189    @property
 190    def name(self) -> str:
 191        return self.text("this")
 192
 193    @property
 194    def alias_or_name(self):
 195        return self.alias or self.name
 196
 197    @property
 198    def output_name(self):
 199        """
 200        Name of the output column if this expression is a selection.
 201
 202        If the Expression has no output name, an empty string is returned.
 203
 204        Example:
 205            >>> from sqlglot import parse_one
 206            >>> parse_one("SELECT a").expressions[0].output_name
 207            'a'
 208            >>> parse_one("SELECT b AS c").expressions[0].output_name
 209            'c'
 210            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 211            ''
 212        """
 213        return ""
 214
 215    @property
 216    def type(self) -> t.Optional[DataType]:
 217        return self._type
 218
 219    @type.setter
 220    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 221        if dtype and not isinstance(dtype, DataType):
 222            dtype = DataType.build(dtype)
 223        self._type = dtype  # type: ignore
 224
 225    @property
 226    def meta(self) -> t.Dict[str, t.Any]:
 227        if self._meta is None:
 228            self._meta = {}
 229        return self._meta
 230
 231    def __deepcopy__(self, memo):
 232        copy = self.__class__(**deepcopy(self.args))
 233        if self.comments is not None:
 234            copy.comments = deepcopy(self.comments)
 235
 236        if self._type is not None:
 237            copy._type = self._type.copy()
 238
 239        if self._meta is not None:
 240            copy._meta = deepcopy(self._meta)
 241
 242        return copy
 243
 244    def copy(self):
 245        """
 246        Returns a deep copy of the expression.
 247        """
 248        new = deepcopy(self)
 249        new.parent = self.parent
 250        for item, parent, _ in new.bfs():
 251            if isinstance(item, Expression) and parent:
 252                item.parent = parent
 253        return new
 254
 255    def append(self, arg_key, value):
 256        """
 257        Appends value to arg_key if it's a list or sets it as a new list.
 258
 259        Args:
 260            arg_key (str): name of the list expression arg
 261            value (Any): value to append to the list
 262        """
 263        if not isinstance(self.args.get(arg_key), list):
 264            self.args[arg_key] = []
 265        self.args[arg_key].append(value)
 266        self._set_parent(arg_key, value)
 267
 268    def set(self, arg_key, value):
 269        """
 270        Sets `arg_key` to `value`.
 271
 272        Args:
 273            arg_key (str): name of the expression arg.
 274            value: value to set the arg to.
 275        """
 276        self.args[arg_key] = value
 277        self._set_parent(arg_key, value)
 278
 279    def _set_parent(self, arg_key, value):
 280        if isinstance(value, Expression):
 281            value.parent = self
 282            value.arg_key = arg_key
 283        elif isinstance(value, list):
 284            for v in value:
 285                if isinstance(v, Expression):
 286                    v.parent = self
 287                    v.arg_key = arg_key
 288
 289    @property
 290    def depth(self):
 291        """
 292        Returns the depth of this tree.
 293        """
 294        if self.parent:
 295            return self.parent.depth + 1
 296        return 0
 297
 298    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 299        """
 300        Returns the first node in this tree which matches at least one of
 301        the specified types.
 302
 303        Args:
 304            expression_types (type): the expression type(s) to match.
 305
 306        Returns:
 307            The node which matches the criteria or None if no such node was found.
 308        """
 309        return next(self.find_all(*expression_types, bfs=bfs), None)
 310
 311    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 312        """
 313        Returns a generator object which visits all nodes in this tree and only
 314        yields those that match at least one of the specified expression types.
 315
 316        Args:
 317            expression_types (type): the expression type(s) to match.
 318
 319        Returns:
 320            The generator object.
 321        """
 322        for expression, _, _ in self.walk(bfs=bfs):
 323            if isinstance(expression, expression_types):
 324                yield expression
 325
 326    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 327        """
 328        Returns a nearest parent matching expression_types.
 329
 330        Args:
 331            expression_types (type): the expression type(s) to match.
 332
 333        Returns:
 334            The parent node.
 335        """
 336        ancestor = self.parent
 337        while ancestor and not isinstance(ancestor, expression_types):
 338            ancestor = ancestor.parent
 339        # ignore type because mypy doesn't know that we're checking type in the loop
 340        return ancestor  # type: ignore[return-value]
 341
 342    @property
 343    def parent_select(self):
 344        """
 345        Returns the parent select statement.
 346        """
 347        return self.find_ancestor(Select)
 348
 349    def root(self) -> Expression:
 350        """
 351        Returns the root expression of this tree.
 352        """
 353        expression = self
 354        while expression.parent:
 355            expression = expression.parent
 356        return expression
 357
 358    def walk(self, bfs=True, prune=None):
 359        """
 360        Returns a generator object which visits all nodes in this tree.
 361
 362        Args:
 363            bfs (bool): if set to True the BFS traversal order will be applied,
 364                otherwise the DFS traversal will be used instead.
 365            prune ((node, parent, arg_key) -> bool): callable that returns True if
 366                the generator should stop traversing this branch of the tree.
 367
 368        Returns:
 369            the generator object.
 370        """
 371        if bfs:
 372            yield from self.bfs(prune=prune)
 373        else:
 374            yield from self.dfs(prune=prune)
 375
 376    def dfs(self, parent=None, key=None, prune=None):
 377        """
 378        Returns a generator object which visits all nodes in this tree in
 379        the DFS (Depth-first) order.
 380
 381        Returns:
 382            The generator object.
 383        """
 384        parent = parent or self.parent
 385        yield self, parent, key
 386        if prune and prune(self, parent, key):
 387            return
 388
 389        for k, v in self.args.items():
 390            for node in ensure_collection(v):
 391                if isinstance(node, Expression):
 392                    yield from node.dfs(self, k, prune)
 393
 394    def bfs(self, prune=None):
 395        """
 396        Returns a generator object which visits all nodes in this tree in
 397        the BFS (Breadth-first) order.
 398
 399        Returns:
 400            The generator object.
 401        """
 402        queue = deque([(self, self.parent, None)])
 403
 404        while queue:
 405            item, parent, key = queue.popleft()
 406
 407            yield item, parent, key
 408            if prune and prune(item, parent, key):
 409                continue
 410
 411            if isinstance(item, Expression):
 412                for k, v in item.args.items():
 413                    for node in ensure_collection(v):
 414                        if isinstance(node, Expression):
 415                            queue.append((node, item, k))
 416
 417    def unnest(self):
 418        """
 419        Returns the first non parenthesis child or self.
 420        """
 421        expression = self
 422        while isinstance(expression, Paren):
 423            expression = expression.this
 424        return expression
 425
 426    def unalias(self):
 427        """
 428        Returns the inner expression if this is an Alias.
 429        """
 430        if isinstance(self, Alias):
 431            return self.this
 432        return self
 433
 434    def unnest_operands(self):
 435        """
 436        Returns unnested operands as a tuple.
 437        """
 438        return tuple(arg.unnest() for arg in self.args.values() if arg)
 439
 440    def flatten(self, unnest=True):
 441        """
 442        Returns a generator which yields child nodes who's parents are the same class.
 443
 444        A AND B AND C -> [A, B, C]
 445        """
 446        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)):
 447            if not isinstance(node, self.__class__):
 448                yield node.unnest() if unnest else node
 449
 450    def __str__(self):
 451        return self.sql()
 452
 453    def __repr__(self):
 454        return self._to_s()
 455
 456    def sql(self, dialect: DialectType = None, **opts) -> str:
 457        """
 458        Returns SQL string representation of this tree.
 459
 460        Args:
 461            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 462            opts: other `sqlglot.generator.Generator` options.
 463
 464        Returns:
 465            The SQL string.
 466        """
 467        from sqlglot.dialects import Dialect
 468
 469        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 470
 471    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 472        indent = "" if not level else "\n"
 473        indent += "".join(["  "] * level)
 474        left = f"({self.key.upper()} "
 475
 476        args: t.Dict[str, t.Any] = {
 477            k: ", ".join(
 478                v._to_s(hide_missing=hide_missing, level=level + 1)
 479                if hasattr(v, "_to_s")
 480                else str(v)
 481                for v in ensure_collection(vs)
 482                if v is not None
 483            )
 484            for k, vs in self.args.items()
 485        }
 486        args["comments"] = self.comments
 487        args["type"] = self.type
 488        args = {k: v for k, v in args.items() if v or not hide_missing}
 489
 490        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 491        right += ")"
 492
 493        return indent + left + right
 494
 495    def transform(self, fun, *args, copy=True, **kwargs):
 496        """
 497        Recursively visits all tree nodes (excluding already transformed ones)
 498        and applies the given transformation function to each node.
 499
 500        Args:
 501            fun (function): a function which takes a node as an argument and returns a
 502                new transformed node or the same node without modifications. If the function
 503                returns None, then the corresponding node will be removed from the syntax tree.
 504            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 505                modified in place.
 506
 507        Returns:
 508            The transformed tree.
 509        """
 510        node = self.copy() if copy else self
 511        new_node = fun(node, *args, **kwargs)
 512
 513        if new_node is None or not isinstance(new_node, Expression):
 514            return new_node
 515        if new_node is not node:
 516            new_node.parent = node.parent
 517            return new_node
 518
 519        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 520        return new_node
 521
 522    def replace(self, expression):
 523        """
 524        Swap out this expression with a new expression.
 525
 526        For example::
 527
 528            >>> tree = Select().select("x").from_("tbl")
 529            >>> tree.find(Column).replace(Column(this="y"))
 530            (COLUMN this: y)
 531            >>> tree.sql()
 532            'SELECT y FROM tbl'
 533
 534        Args:
 535            expression (Expression|None): new node
 536
 537        Returns:
 538            The new expression or expressions.
 539        """
 540        if not self.parent:
 541            return expression
 542
 543        parent = self.parent
 544        self.parent = None
 545
 546        replace_children(parent, lambda child: expression if child is self else child)
 547        return expression
 548
 549    def pop(self):
 550        """
 551        Remove this expression from its AST.
 552
 553        Returns:
 554            The popped expression.
 555        """
 556        self.replace(None)
 557        return self
 558
 559    def assert_is(self, type_):
 560        """
 561        Assert that this `Expression` is an instance of `type_`.
 562
 563        If it is NOT an instance of `type_`, this raises an assertion error.
 564        Otherwise, this returns this expression.
 565
 566        Examples:
 567            This is useful for type security in chained expressions:
 568
 569            >>> import sqlglot
 570            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 571            'SELECT x, z FROM y'
 572        """
 573        assert isinstance(self, type_)
 574        return self
 575
 576    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 577        """
 578        Checks if this expression is valid (e.g. all mandatory args are set).
 579
 580        Args:
 581            args: a sequence of values that were used to instantiate a Func expression. This is used
 582                to check that the provided arguments don't exceed the function argument limit.
 583
 584        Returns:
 585            A list of error messages for all possible errors that were found.
 586        """
 587        errors: t.List[str] = []
 588
 589        for k in self.args:
 590            if k not in self.arg_types:
 591                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 592        for k, mandatory in self.arg_types.items():
 593            v = self.args.get(k)
 594            if mandatory and (v is None or (isinstance(v, list) and not v)):
 595                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 596
 597        if (
 598            args
 599            and isinstance(self, Func)
 600            and len(args) > len(self.arg_types)
 601            and not self.is_var_len_args
 602        ):
 603            errors.append(
 604                f"The number of provided arguments ({len(args)}) is greater than "
 605                f"the maximum number of supported arguments ({len(self.arg_types)})"
 606            )
 607
 608        return errors
 609
 610    def dump(self):
 611        """
 612        Dump this Expression to a JSON-serializable dict.
 613        """
 614        from sqlglot.serde import dump
 615
 616        return dump(self)
 617
 618    @classmethod
 619    def load(cls, obj):
 620        """
 621        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 622        """
 623        from sqlglot.serde import load
 624
 625        return load(obj)
 626
 627
 628IntoType = t.Union[
 629    str,
 630    t.Type[Expression],
 631    t.Collection[t.Union[str, t.Type[Expression]]],
 632]
 633ExpOrStr = t.Union[str, Expression]
 634
 635
 636class Condition(Expression):
 637    def and_(self, *expressions, dialect=None, **opts):
 638        """
 639        AND this condition with one or multiple expressions.
 640
 641        Example:
 642            >>> condition("x=1").and_("y=1").sql()
 643            'x = 1 AND y = 1'
 644
 645        Args:
 646            *expressions (str | Expression): the SQL code strings to parse.
 647                If an `Expression` instance is passed, it will be used as-is.
 648            dialect (str): the dialect used to parse the input expression.
 649            opts (kwargs): other options to use to parse the input expressions.
 650
 651        Returns:
 652            And: the new condition.
 653        """
 654        return and_(self, *expressions, dialect=dialect, **opts)
 655
 656    def or_(self, *expressions, dialect=None, **opts):
 657        """
 658        OR this condition with one or multiple expressions.
 659
 660        Example:
 661            >>> condition("x=1").or_("y=1").sql()
 662            'x = 1 OR y = 1'
 663
 664        Args:
 665            *expressions (str | Expression): the SQL code strings to parse.
 666                If an `Expression` instance is passed, it will be used as-is.
 667            dialect (str): the dialect used to parse the input expression.
 668            opts (kwargs): other options to use to parse the input expressions.
 669
 670        Returns:
 671            Or: the new condition.
 672        """
 673        return or_(self, *expressions, dialect=dialect, **opts)
 674
 675    def not_(self):
 676        """
 677        Wrap this condition with NOT.
 678
 679        Example:
 680            >>> condition("x=1").not_().sql()
 681            'NOT x = 1'
 682
 683        Returns:
 684            Not: the new condition.
 685        """
 686        return not_(self)
 687
 688
 689class Predicate(Condition):
 690    """Relationships like x = y, x > 1, x >= y."""
 691
 692
 693class DerivedTable(Expression):
 694    @property
 695    def alias_column_names(self):
 696        table_alias = self.args.get("alias")
 697        if not table_alias:
 698            return []
 699        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 700        return [c.name for c in column_list]
 701
 702    @property
 703    def selects(self):
 704        alias = self.args.get("alias")
 705
 706        if alias:
 707            return alias.columns
 708        return []
 709
 710    @property
 711    def named_selects(self):
 712        return [select.output_name for select in self.selects]
 713
 714
 715class Unionable(Expression):
 716    def union(self, expression, distinct=True, dialect=None, **opts):
 717        """
 718        Builds a UNION expression.
 719
 720        Example:
 721            >>> import sqlglot
 722            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 723            'SELECT * FROM foo UNION SELECT * FROM bla'
 724
 725        Args:
 726            expression (str | Expression): the SQL code string.
 727                If an `Expression` instance is passed, it will be used as-is.
 728            distinct (bool): set the DISTINCT flag if and only if this is true.
 729            dialect (str): the dialect used to parse the input expression.
 730            opts (kwargs): other options to use to parse the input expressions.
 731        Returns:
 732            Union: the Union expression.
 733        """
 734        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 735
 736    def intersect(self, expression, distinct=True, dialect=None, **opts):
 737        """
 738        Builds an INTERSECT expression.
 739
 740        Example:
 741            >>> import sqlglot
 742            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 743            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 744
 745        Args:
 746            expression (str | Expression): the SQL code string.
 747                If an `Expression` instance is passed, it will be used as-is.
 748            distinct (bool): set the DISTINCT flag if and only if this is true.
 749            dialect (str): the dialect used to parse the input expression.
 750            opts (kwargs): other options to use to parse the input expressions.
 751        Returns:
 752            Intersect: the Intersect expression
 753        """
 754        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 755
 756    def except_(self, expression, distinct=True, dialect=None, **opts):
 757        """
 758        Builds an EXCEPT expression.
 759
 760        Example:
 761            >>> import sqlglot
 762            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 763            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 764
 765        Args:
 766            expression (str | Expression): the SQL code string.
 767                If an `Expression` instance is passed, it will be used as-is.
 768            distinct (bool): set the DISTINCT flag if and only if this is true.
 769            dialect (str): the dialect used to parse the input expression.
 770            opts (kwargs): other options to use to parse the input expressions.
 771        Returns:
 772            Except: the Except expression
 773        """
 774        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 775
 776
 777class UDTF(DerivedTable, Unionable):
 778    pass
 779
 780
 781class Cache(Expression):
 782    arg_types = {
 783        "with": False,
 784        "this": True,
 785        "lazy": False,
 786        "options": False,
 787        "expression": False,
 788    }
 789
 790
 791class Uncache(Expression):
 792    arg_types = {"this": True, "exists": False}
 793
 794
 795class Create(Expression):
 796    arg_types = {
 797        "with": False,
 798        "this": True,
 799        "kind": True,
 800        "expression": False,
 801        "exists": False,
 802        "properties": False,
 803        "replace": False,
 804        "unique": False,
 805        "volatile": False,
 806        "indexes": False,
 807        "no_schema_binding": False,
 808        "begin": False,
 809    }
 810
 811
 812class Describe(Expression):
 813    arg_types = {"this": True, "kind": False}
 814
 815
 816class Set(Expression):
 817    arg_types = {"expressions": False}
 818
 819
 820class SetItem(Expression):
 821    arg_types = {
 822        "this": False,
 823        "expressions": False,
 824        "kind": False,
 825        "collate": False,  # MySQL SET NAMES statement
 826        "global": False,
 827    }
 828
 829
 830class Show(Expression):
 831    arg_types = {
 832        "this": True,
 833        "target": False,
 834        "offset": False,
 835        "limit": False,
 836        "like": False,
 837        "where": False,
 838        "db": False,
 839        "full": False,
 840        "mutex": False,
 841        "query": False,
 842        "channel": False,
 843        "global": False,
 844        "log": False,
 845        "position": False,
 846        "types": False,
 847    }
 848
 849
 850class UserDefinedFunction(Expression):
 851    arg_types = {"this": True, "expressions": False, "wrapped": False}
 852
 853
 854class CharacterSet(Expression):
 855    arg_types = {"this": True, "default": False}
 856
 857
 858class With(Expression):
 859    arg_types = {"expressions": True, "recursive": False}
 860
 861    @property
 862    def recursive(self) -> bool:
 863        return bool(self.args.get("recursive"))
 864
 865
 866class WithinGroup(Expression):
 867    arg_types = {"this": True, "expression": False}
 868
 869
 870class CTE(DerivedTable):
 871    arg_types = {"this": True, "alias": True}
 872
 873
 874class TableAlias(Expression):
 875    arg_types = {"this": False, "columns": False}
 876
 877    @property
 878    def columns(self):
 879        return self.args.get("columns") or []
 880
 881
 882class BitString(Condition):
 883    pass
 884
 885
 886class HexString(Condition):
 887    pass
 888
 889
 890class ByteString(Condition):
 891    pass
 892
 893
 894class Column(Condition):
 895    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 896
 897    @property
 898    def table(self) -> str:
 899        return self.text("table")
 900
 901    @property
 902    def db(self) -> str:
 903        return self.text("db")
 904
 905    @property
 906    def catalog(self) -> str:
 907        return self.text("catalog")
 908
 909    @property
 910    def output_name(self) -> str:
 911        return self.name
 912
 913
 914class ColumnDef(Expression):
 915    arg_types = {
 916        "this": True,
 917        "kind": False,
 918        "constraints": False,
 919        "exists": False,
 920    }
 921
 922
 923class AlterColumn(Expression):
 924    arg_types = {
 925        "this": True,
 926        "dtype": False,
 927        "collate": False,
 928        "using": False,
 929        "default": False,
 930        "drop": False,
 931    }
 932
 933
 934class RenameTable(Expression):
 935    pass
 936
 937
 938class SetTag(Expression):
 939    arg_types = {"expressions": True, "unset": False}
 940
 941
 942class Comment(Expression):
 943    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
 944
 945
 946class ColumnConstraint(Expression):
 947    arg_types = {"this": False, "kind": True}
 948
 949
 950class ColumnConstraintKind(Expression):
 951    pass
 952
 953
 954class AutoIncrementColumnConstraint(ColumnConstraintKind):
 955    pass
 956
 957
 958class CaseSpecificColumnConstraint(ColumnConstraintKind):
 959    arg_types = {"not_": True}
 960
 961
 962class CharacterSetColumnConstraint(ColumnConstraintKind):
 963    arg_types = {"this": True}
 964
 965
 966class CheckColumnConstraint(ColumnConstraintKind):
 967    pass
 968
 969
 970class CollateColumnConstraint(ColumnConstraintKind):
 971    pass
 972
 973
 974class CommentColumnConstraint(ColumnConstraintKind):
 975    pass
 976
 977
 978class CompressColumnConstraint(ColumnConstraintKind):
 979    pass
 980
 981
 982class DateFormatColumnConstraint(ColumnConstraintKind):
 983    arg_types = {"this": True}
 984
 985
 986class DefaultColumnConstraint(ColumnConstraintKind):
 987    pass
 988
 989
 990class EncodeColumnConstraint(ColumnConstraintKind):
 991    pass
 992
 993
 994class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
 995    # this: True -> ALWAYS, this: False -> BY DEFAULT
 996    arg_types = {
 997        "this": False,
 998        "start": False,
 999        "increment": False,
1000        "minvalue": False,
1001        "maxvalue": False,
1002        "cycle": False,
1003    }
1004
1005
1006class InlineLengthColumnConstraint(ColumnConstraintKind):
1007    pass
1008
1009
1010class NotNullColumnConstraint(ColumnConstraintKind):
1011    arg_types = {"allow_null": False}
1012
1013
1014class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1015    arg_types = {"desc": False}
1016
1017
1018class TitleColumnConstraint(ColumnConstraintKind):
1019    pass
1020
1021
1022class UniqueColumnConstraint(ColumnConstraintKind):
1023    arg_types: t.Dict[str, t.Any] = {}
1024
1025
1026class UppercaseColumnConstraint(ColumnConstraintKind):
1027    arg_types: t.Dict[str, t.Any] = {}
1028
1029
1030class PathColumnConstraint(ColumnConstraintKind):
1031    pass
1032
1033
1034class Constraint(Expression):
1035    arg_types = {"this": True, "expressions": True}
1036
1037
1038class Delete(Expression):
1039    arg_types = {"with": False, "this": False, "using": False, "where": False, "returning": False}
1040
1041    def delete(
1042        self,
1043        table: ExpOrStr,
1044        dialect: DialectType = None,
1045        copy: bool = True,
1046        **opts,
1047    ) -> Delete:
1048        """
1049        Create a DELETE expression or replace the table on an existing DELETE expression.
1050
1051        Example:
1052            >>> delete("tbl").sql()
1053            'DELETE FROM tbl'
1054
1055        Args:
1056            table: the table from which to delete.
1057            dialect: the dialect used to parse the input expression.
1058            copy: if `False`, modify this expression instance in-place.
1059            opts: other options to use to parse the input expressions.
1060
1061        Returns:
1062            Delete: the modified expression.
1063        """
1064        return _apply_builder(
1065            expression=table,
1066            instance=self,
1067            arg="this",
1068            dialect=dialect,
1069            into=Table,
1070            copy=copy,
1071            **opts,
1072        )
1073
1074    def where(
1075        self,
1076        *expressions: ExpOrStr,
1077        append: bool = True,
1078        dialect: DialectType = None,
1079        copy: bool = True,
1080        **opts,
1081    ) -> Delete:
1082        """
1083        Append to or set the WHERE expressions.
1084
1085        Example:
1086            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1087            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1088
1089        Args:
1090            *expressions: the SQL code strings to parse.
1091                If an `Expression` instance is passed, it will be used as-is.
1092                Multiple expressions are combined with an AND operator.
1093            append: if `True`, AND the new expressions to any existing expression.
1094                Otherwise, this resets the expression.
1095            dialect: the dialect used to parse the input expressions.
1096            copy: if `False`, modify this expression instance in-place.
1097            opts: other options to use to parse the input expressions.
1098
1099        Returns:
1100            Delete: the modified expression.
1101        """
1102        return _apply_conjunction_builder(
1103            *expressions,
1104            instance=self,
1105            arg="where",
1106            append=append,
1107            into=Where,
1108            dialect=dialect,
1109            copy=copy,
1110            **opts,
1111        )
1112
1113    def returning(
1114        self,
1115        expression: ExpOrStr,
1116        dialect: DialectType = None,
1117        copy: bool = True,
1118        **opts,
1119    ) -> Delete:
1120        """
1121        Set the RETURNING expression. Not supported by all dialects.
1122
1123        Example:
1124            >>> delete("tbl").returning("*", dialect="postgres").sql()
1125            'DELETE FROM tbl RETURNING *'
1126
1127        Args:
1128            expression: the SQL code strings to parse.
1129                If an `Expression` instance is passed, it will be used as-is.
1130            dialect: the dialect used to parse the input expressions.
1131            copy: if `False`, modify this expression instance in-place.
1132            opts: other options to use to parse the input expressions.
1133
1134        Returns:
1135            Delete: the modified expression.
1136        """
1137        return _apply_builder(
1138            expression=expression,
1139            instance=self,
1140            arg="returning",
1141            prefix="RETURNING",
1142            dialect=dialect,
1143            copy=copy,
1144            into=Returning,
1145            **opts,
1146        )
1147
1148
1149class Drop(Expression):
1150    arg_types = {
1151        "this": False,
1152        "kind": False,
1153        "exists": False,
1154        "temporary": False,
1155        "materialized": False,
1156        "cascade": False,
1157    }
1158
1159
1160class Filter(Expression):
1161    arg_types = {"this": True, "expression": True}
1162
1163
1164class Check(Expression):
1165    pass
1166
1167
1168class Directory(Expression):
1169    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1170    arg_types = {"this": True, "local": False, "row_format": False}
1171
1172
1173class ForeignKey(Expression):
1174    arg_types = {
1175        "expressions": True,
1176        "reference": False,
1177        "delete": False,
1178        "update": False,
1179    }
1180
1181
1182class PrimaryKey(Expression):
1183    arg_types = {"expressions": True, "options": False}
1184
1185
1186class Unique(Expression):
1187    arg_types = {"expressions": True}
1188
1189
1190# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1191# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1192class Into(Expression):
1193    arg_types = {"this": True, "temporary": False, "unlogged": False}
1194
1195
1196class From(Expression):
1197    arg_types = {"expressions": True}
1198
1199
1200class Having(Expression):
1201    pass
1202
1203
1204class Hint(Expression):
1205    arg_types = {"expressions": True}
1206
1207
1208class JoinHint(Expression):
1209    arg_types = {"this": True, "expressions": True}
1210
1211
1212class Identifier(Expression):
1213    arg_types = {"this": True, "quoted": False}
1214
1215    @property
1216    def quoted(self):
1217        return bool(self.args.get("quoted"))
1218
1219    def __eq__(self, other):
1220        return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this)
1221
1222    def __hash__(self):
1223        return hash((self.key, self.this.lower()))
1224
1225    @property
1226    def output_name(self):
1227        return self.name
1228
1229
1230class Index(Expression):
1231    arg_types = {
1232        "this": False,
1233        "table": False,
1234        "where": False,
1235        "columns": False,
1236        "unique": False,
1237        "primary": False,
1238        "amp": False,  # teradata
1239    }
1240
1241
1242class Insert(Expression):
1243    arg_types = {
1244        "with": False,
1245        "this": True,
1246        "expression": False,
1247        "returning": False,
1248        "overwrite": False,
1249        "exists": False,
1250        "partition": False,
1251        "alternative": False,
1252    }
1253
1254
1255class Returning(Expression):
1256    arg_types = {"expressions": True}
1257
1258
1259# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1260class Introducer(Expression):
1261    arg_types = {"this": True, "expression": True}
1262
1263
1264# national char, like n'utf8'
1265class National(Expression):
1266    pass
1267
1268
1269class LoadData(Expression):
1270    arg_types = {
1271        "this": True,
1272        "local": False,
1273        "overwrite": False,
1274        "inpath": True,
1275        "partition": False,
1276        "input_format": False,
1277        "serde": False,
1278    }
1279
1280
1281class Partition(Expression):
1282    arg_types = {"expressions": True}
1283
1284
1285class Fetch(Expression):
1286    arg_types = {"direction": False, "count": False}
1287
1288
1289class Group(Expression):
1290    arg_types = {
1291        "expressions": False,
1292        "grouping_sets": False,
1293        "cube": False,
1294        "rollup": False,
1295    }
1296
1297
1298class Lambda(Expression):
1299    arg_types = {"this": True, "expressions": True}
1300
1301
1302class Limit(Expression):
1303    arg_types = {"this": False, "expression": True}
1304
1305
1306class Literal(Condition):
1307    arg_types = {"this": True, "is_string": True}
1308
1309    def __eq__(self, other):
1310        return (
1311            isinstance(other, Literal)
1312            and self.this == other.this
1313            and self.args["is_string"] == other.args["is_string"]
1314        )
1315
1316    def __hash__(self):
1317        return hash((self.key, self.this, self.args["is_string"]))
1318
1319    @classmethod
1320    def number(cls, number) -> Literal:
1321        return cls(this=str(number), is_string=False)
1322
1323    @classmethod
1324    def string(cls, string) -> Literal:
1325        return cls(this=str(string), is_string=True)
1326
1327    @property
1328    def output_name(self):
1329        return self.name
1330
1331
1332class Join(Expression):
1333    arg_types = {
1334        "this": True,
1335        "on": False,
1336        "side": False,
1337        "kind": False,
1338        "using": False,
1339        "natural": False,
1340    }
1341
1342    @property
1343    def kind(self):
1344        return self.text("kind").upper()
1345
1346    @property
1347    def side(self):
1348        return self.text("side").upper()
1349
1350    @property
1351    def alias_or_name(self):
1352        return self.this.alias_or_name
1353
1354    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1355        """
1356        Append to or set the ON expressions.
1357
1358        Example:
1359            >>> import sqlglot
1360            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1361            'JOIN x ON y = 1'
1362
1363        Args:
1364            *expressions (str | Expression): the SQL code strings to parse.
1365                If an `Expression` instance is passed, it will be used as-is.
1366                Multiple expressions are combined with an AND operator.
1367            append (bool): if `True`, AND the new expressions to any existing expression.
1368                Otherwise, this resets the expression.
1369            dialect (str): the dialect used to parse the input expressions.
1370            copy (bool): if `False`, modify this expression instance in-place.
1371            opts (kwargs): other options to use to parse the input expressions.
1372
1373        Returns:
1374            Join: the modified join expression.
1375        """
1376        join = _apply_conjunction_builder(
1377            *expressions,
1378            instance=self,
1379            arg="on",
1380            append=append,
1381            dialect=dialect,
1382            copy=copy,
1383            **opts,
1384        )
1385
1386        if join.kind == "CROSS":
1387            join.set("kind", None)
1388
1389        return join
1390
1391    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1392        """
1393        Append to or set the USING expressions.
1394
1395        Example:
1396            >>> import sqlglot
1397            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1398            'JOIN x USING (foo, bla)'
1399
1400        Args:
1401            *expressions (str | Expression): the SQL code strings to parse.
1402                If an `Expression` instance is passed, it will be used as-is.
1403            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1404                Otherwise, this resets the expression.
1405            dialect (str): the dialect used to parse the input expressions.
1406            copy (bool): if `False`, modify this expression instance in-place.
1407            opts (kwargs): other options to use to parse the input expressions.
1408
1409        Returns:
1410            Join: the modified join expression.
1411        """
1412        join = _apply_list_builder(
1413            *expressions,
1414            instance=self,
1415            arg="using",
1416            append=append,
1417            dialect=dialect,
1418            copy=copy,
1419            **opts,
1420        )
1421
1422        if join.kind == "CROSS":
1423            join.set("kind", None)
1424
1425        return join
1426
1427
1428class Lateral(UDTF):
1429    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1430
1431
1432class MatchRecognize(Expression):
1433    arg_types = {
1434        "partition_by": False,
1435        "order": False,
1436        "measures": False,
1437        "rows": False,
1438        "after": False,
1439        "pattern": False,
1440        "define": False,
1441    }
1442
1443
1444# Clickhouse FROM FINAL modifier
1445# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1446class Final(Expression):
1447    pass
1448
1449
1450class Offset(Expression):
1451    arg_types = {"this": False, "expression": True}
1452
1453
1454class Order(Expression):
1455    arg_types = {"this": False, "expressions": True}
1456
1457
1458# hive specific sorts
1459# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1460class Cluster(Order):
1461    pass
1462
1463
1464class Distribute(Order):
1465    pass
1466
1467
1468class Sort(Order):
1469    pass
1470
1471
1472class Ordered(Expression):
1473    arg_types = {"this": True, "desc": True, "nulls_first": True}
1474
1475
1476class Property(Expression):
1477    arg_types = {"this": True, "value": True}
1478
1479
1480class AfterJournalProperty(Property):
1481    arg_types = {"no": True, "dual": False, "local": False}
1482
1483
1484class AlgorithmProperty(Property):
1485    arg_types = {"this": True}
1486
1487
1488class AutoIncrementProperty(Property):
1489    arg_types = {"this": True}
1490
1491
1492class BlockCompressionProperty(Property):
1493    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1494
1495
1496class CharacterSetProperty(Property):
1497    arg_types = {"this": True, "default": True}
1498
1499
1500class ChecksumProperty(Property):
1501    arg_types = {"on": False, "default": False}
1502
1503
1504class CollateProperty(Property):
1505    arg_types = {"this": True}
1506
1507
1508class DataBlocksizeProperty(Property):
1509    arg_types = {"size": False, "units": False, "min": False, "default": False}
1510
1511
1512class DefinerProperty(Property):
1513    arg_types = {"this": True}
1514
1515
1516class DistKeyProperty(Property):
1517    arg_types = {"this": True}
1518
1519
1520class DistStyleProperty(Property):
1521    arg_types = {"this": True}
1522
1523
1524class EngineProperty(Property):
1525    arg_types = {"this": True}
1526
1527
1528class ExecuteAsProperty(Property):
1529    arg_types = {"this": True}
1530
1531
1532class ExternalProperty(Property):
1533    arg_types = {"this": False}
1534
1535
1536class FallbackProperty(Property):
1537    arg_types = {"no": True, "protection": False}
1538
1539
1540class FileFormatProperty(Property):
1541    arg_types = {"this": True}
1542
1543
1544class FreespaceProperty(Property):
1545    arg_types = {"this": True, "percent": False}
1546
1547
1548class IsolatedLoadingProperty(Property):
1549    arg_types = {
1550        "no": True,
1551        "concurrent": True,
1552        "for_all": True,
1553        "for_insert": True,
1554        "for_none": True,
1555    }
1556
1557
1558class JournalProperty(Property):
1559    arg_types = {"no": True, "dual": False, "before": False}
1560
1561
1562class LanguageProperty(Property):
1563    arg_types = {"this": True}
1564
1565
1566class LikeProperty(Property):
1567    arg_types = {"this": True, "expressions": False}
1568
1569
1570class LocationProperty(Property):
1571    arg_types = {"this": True}
1572
1573
1574class LockingProperty(Property):
1575    arg_types = {
1576        "this": False,
1577        "kind": True,
1578        "for_or_in": True,
1579        "lock_type": True,
1580        "override": False,
1581    }
1582
1583
1584class LogProperty(Property):
1585    arg_types = {"no": True}
1586
1587
1588class MaterializedProperty(Property):
1589    arg_types = {"this": False}
1590
1591
1592class MergeBlockRatioProperty(Property):
1593    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1594
1595
1596class NoPrimaryIndexProperty(Property):
1597    arg_types = {"this": False}
1598
1599
1600class OnCommitProperty(Property):
1601    arg_type = {"this": False}
1602
1603
1604class PartitionedByProperty(Property):
1605    arg_types = {"this": True}
1606
1607
1608class ReturnsProperty(Property):
1609    arg_types = {"this": True, "is_table": False, "table": False}
1610
1611
1612class RowFormatDelimitedProperty(Property):
1613    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1614    arg_types = {
1615        "fields": False,
1616        "escaped": False,
1617        "collection_items": False,
1618        "map_keys": False,
1619        "lines": False,
1620        "null": False,
1621        "serde": False,
1622    }
1623
1624
1625class RowFormatSerdeProperty(Property):
1626    arg_types = {"this": True}
1627
1628
1629class SchemaCommentProperty(Property):
1630    arg_types = {"this": True}
1631
1632
1633class SerdeProperties(Property):
1634    arg_types = {"expressions": True}
1635
1636
1637class SetProperty(Property):
1638    arg_types = {"multi": True}
1639
1640
1641class SortKeyProperty(Property):
1642    arg_types = {"this": True, "compound": False}
1643
1644
1645class SqlSecurityProperty(Property):
1646    arg_types = {"definer": True}
1647
1648
1649class TableFormatProperty(Property):
1650    arg_types = {"this": True}
1651
1652
1653class TemporaryProperty(Property):
1654    arg_types = {"global_": True}
1655
1656
1657class TransientProperty(Property):
1658    arg_types = {"this": False}
1659
1660
1661class VolatilityProperty(Property):
1662    arg_types = {"this": True}
1663
1664
1665class WithDataProperty(Property):
1666    arg_types = {"no": True, "statistics": False}
1667
1668
1669class WithJournalTableProperty(Property):
1670    arg_types = {"this": True}
1671
1672
1673class Properties(Expression):
1674    arg_types = {"expressions": True}
1675
1676    NAME_TO_PROPERTY = {
1677        "ALGORITHM": AlgorithmProperty,
1678        "AUTO_INCREMENT": AutoIncrementProperty,
1679        "CHARACTER SET": CharacterSetProperty,
1680        "COLLATE": CollateProperty,
1681        "COMMENT": SchemaCommentProperty,
1682        "DEFINER": DefinerProperty,
1683        "DISTKEY": DistKeyProperty,
1684        "DISTSTYLE": DistStyleProperty,
1685        "ENGINE": EngineProperty,
1686        "EXECUTE AS": ExecuteAsProperty,
1687        "FORMAT": FileFormatProperty,
1688        "LANGUAGE": LanguageProperty,
1689        "LOCATION": LocationProperty,
1690        "PARTITIONED_BY": PartitionedByProperty,
1691        "RETURNS": ReturnsProperty,
1692        "SORTKEY": SortKeyProperty,
1693        "TABLE_FORMAT": TableFormatProperty,
1694    }
1695
1696    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1697
1698    # CREATE property locations
1699    # Form: schema specified
1700    #   create [POST_CREATE]
1701    #     table a [POST_NAME]
1702    #     (b int) [POST_SCHEMA]
1703    #     with ([POST_WITH])
1704    #     index (b) [POST_INDEX]
1705    #
1706    # Form: alias selection
1707    #   create [POST_CREATE]
1708    #     table a [POST_NAME]
1709    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1710    #     index (c) [POST_INDEX]
1711    class Location(AutoName):
1712        POST_CREATE = auto()
1713        POST_NAME = auto()
1714        POST_SCHEMA = auto()
1715        POST_WITH = auto()
1716        POST_ALIAS = auto()
1717        POST_EXPRESSION = auto()
1718        POST_INDEX = auto()
1719        UNSUPPORTED = auto()
1720
1721    @classmethod
1722    def from_dict(cls, properties_dict) -> Properties:
1723        expressions = []
1724        for key, value in properties_dict.items():
1725            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1726            if property_cls:
1727                expressions.append(property_cls(this=convert(value)))
1728            else:
1729                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1730
1731        return cls(expressions=expressions)
1732
1733
1734class Qualify(Expression):
1735    pass
1736
1737
1738# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1739class Return(Expression):
1740    pass
1741
1742
1743class Reference(Expression):
1744    arg_types = {"this": True, "expressions": False, "options": False}
1745
1746
1747class Tuple(Expression):
1748    arg_types = {"expressions": False}
1749
1750
1751class Subqueryable(Unionable):
1752    def subquery(self, alias=None, copy=True) -> Subquery:
1753        """
1754        Convert this expression to an aliased expression that can be used as a Subquery.
1755
1756        Example:
1757            >>> subquery = Select().select("x").from_("tbl").subquery()
1758            >>> Select().select("x").from_(subquery).sql()
1759            'SELECT x FROM (SELECT x FROM tbl)'
1760
1761        Args:
1762            alias (str | Identifier): an optional alias for the subquery
1763            copy (bool): if `False`, modify this expression instance in-place.
1764
1765        Returns:
1766            Alias: the subquery
1767        """
1768        instance = _maybe_copy(self, copy)
1769        return Subquery(
1770            this=instance,
1771            alias=TableAlias(this=to_identifier(alias)),
1772        )
1773
1774    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1775        raise NotImplementedError
1776
1777    @property
1778    def ctes(self):
1779        with_ = self.args.get("with")
1780        if not with_:
1781            return []
1782        return with_.expressions
1783
1784    @property
1785    def selects(self):
1786        raise NotImplementedError("Subqueryable objects must implement `selects`")
1787
1788    @property
1789    def named_selects(self):
1790        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1791
1792    def with_(
1793        self,
1794        alias,
1795        as_,
1796        recursive=None,
1797        append=True,
1798        dialect=None,
1799        copy=True,
1800        **opts,
1801    ):
1802        """
1803        Append to or set the common table expressions.
1804
1805        Example:
1806            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1807            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1808
1809        Args:
1810            alias (str | Expression): the SQL code string to parse as the table name.
1811                If an `Expression` instance is passed, this is used as-is.
1812            as_ (str | Expression): the SQL code string to parse as the table expression.
1813                If an `Expression` instance is passed, it will be used as-is.
1814            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1815            append (bool): if `True`, add to any existing expressions.
1816                Otherwise, this resets the expressions.
1817            dialect (str): the dialect used to parse the input expression.
1818            copy (bool): if `False`, modify this expression instance in-place.
1819            opts (kwargs): other options to use to parse the input expressions.
1820
1821        Returns:
1822            Select: the modified expression.
1823        """
1824        alias_expression = maybe_parse(
1825            alias,
1826            dialect=dialect,
1827            into=TableAlias,
1828            **opts,
1829        )
1830        as_expression = maybe_parse(
1831            as_,
1832            dialect=dialect,
1833            **opts,
1834        )
1835        cte = CTE(
1836            this=as_expression,
1837            alias=alias_expression,
1838        )
1839        return _apply_child_list_builder(
1840            cte,
1841            instance=self,
1842            arg="with",
1843            append=append,
1844            copy=copy,
1845            into=With,
1846            properties={"recursive": recursive or False},
1847        )
1848
1849
1850QUERY_MODIFIERS = {
1851    "match": False,
1852    "laterals": False,
1853    "joins": False,
1854    "pivots": False,
1855    "where": False,
1856    "group": False,
1857    "having": False,
1858    "qualify": False,
1859    "windows": False,
1860    "distribute": False,
1861    "sort": False,
1862    "cluster": False,
1863    "order": False,
1864    "limit": False,
1865    "offset": False,
1866    "lock": False,
1867    "sample": False,
1868}
1869
1870
1871class Table(Expression):
1872    arg_types = {
1873        "this": True,
1874        "alias": False,
1875        "db": False,
1876        "catalog": False,
1877        "laterals": False,
1878        "joins": False,
1879        "pivots": False,
1880        "hints": False,
1881        "system_time": False,
1882    }
1883
1884    @property
1885    def db(self) -> str:
1886        return self.text("db")
1887
1888    @property
1889    def catalog(self) -> str:
1890        return self.text("catalog")
1891
1892
1893# See the TSQL "Querying data in a system-versioned temporal table" page
1894class SystemTime(Expression):
1895    arg_types = {
1896        "this": False,
1897        "expression": False,
1898        "kind": True,
1899    }
1900
1901
1902class Union(Subqueryable):
1903    arg_types = {
1904        "with": False,
1905        "this": True,
1906        "expression": True,
1907        "distinct": False,
1908        **QUERY_MODIFIERS,
1909    }
1910
1911    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1912        """
1913        Set the LIMIT expression.
1914
1915        Example:
1916            >>> select("1").union(select("1")).limit(1).sql()
1917            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1918
1919        Args:
1920            expression (str | int | Expression): the SQL code string to parse.
1921                This can also be an integer.
1922                If a `Limit` instance is passed, this is used as-is.
1923                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1924            dialect (str): the dialect used to parse the input expression.
1925            copy (bool): if `False`, modify this expression instance in-place.
1926            opts (kwargs): other options to use to parse the input expressions.
1927
1928        Returns:
1929            Select: The limited subqueryable.
1930        """
1931        return (
1932            select("*")
1933            .from_(self.subquery(alias="_l_0", copy=copy))
1934            .limit(expression, dialect=dialect, copy=False, **opts)
1935        )
1936
1937    def select(
1938        self,
1939        *expressions: ExpOrStr,
1940        append: bool = True,
1941        dialect: DialectType = None,
1942        copy: bool = True,
1943        **opts,
1944    ) -> Union:
1945        """Append to or set the SELECT of the union recursively.
1946
1947        Example:
1948            >>> from sqlglot import parse_one
1949            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1950            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1951
1952        Args:
1953            *expressions: the SQL code strings to parse.
1954                If an `Expression` instance is passed, it will be used as-is.
1955            append: if `True`, add to any existing expressions.
1956                Otherwise, this resets the expressions.
1957            dialect: the dialect used to parse the input expressions.
1958            copy: if `False`, modify this expression instance in-place.
1959            opts: other options to use to parse the input expressions.
1960
1961        Returns:
1962            Union: the modified expression.
1963        """
1964        this = self.copy() if copy else self
1965        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1966        this.expression.unnest().select(
1967            *expressions, append=append, dialect=dialect, copy=False, **opts
1968        )
1969        return this
1970
1971    @property
1972    def named_selects(self):
1973        return self.this.unnest().named_selects
1974
1975    @property
1976    def is_star(self) -> bool:
1977        return self.this.is_star or self.expression.is_star
1978
1979    @property
1980    def selects(self):
1981        return self.this.unnest().selects
1982
1983    @property
1984    def left(self):
1985        return self.this
1986
1987    @property
1988    def right(self):
1989        return self.expression
1990
1991
1992class Except(Union):
1993    pass
1994
1995
1996class Intersect(Union):
1997    pass
1998
1999
2000class Unnest(UDTF):
2001    arg_types = {
2002        "expressions": True,
2003        "ordinality": False,
2004        "alias": False,
2005        "offset": False,
2006    }
2007
2008
2009class Update(Expression):
2010    arg_types = {
2011        "with": False,
2012        "this": False,
2013        "expressions": True,
2014        "from": False,
2015        "where": False,
2016        "returning": False,
2017    }
2018
2019
2020class Values(UDTF):
2021    arg_types = {
2022        "expressions": True,
2023        "ordinality": False,
2024        "alias": False,
2025    }
2026
2027
2028class Var(Expression):
2029    pass
2030
2031
2032class Schema(Expression):
2033    arg_types = {"this": False, "expressions": False}
2034
2035
2036# Used to represent the FOR UPDATE and FOR SHARE locking read types.
2037# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
2038class Lock(Expression):
2039    arg_types = {"update": True}
2040
2041
2042class Select(Subqueryable):
2043    arg_types = {
2044        "with": False,
2045        "expressions": False,
2046        "hint": False,
2047        "distinct": False,
2048        "into": False,
2049        "from": False,
2050        **QUERY_MODIFIERS,
2051    }
2052
2053    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2054        """
2055        Set the FROM expression.
2056
2057        Example:
2058            >>> Select().from_("tbl").select("x").sql()
2059            'SELECT x FROM tbl'
2060
2061        Args:
2062            *expressions (str | Expression): the SQL code strings to parse.
2063                If a `From` instance is passed, this is used as-is.
2064                If another `Expression` instance is passed, it will be wrapped in a `From`.
2065            append (bool): if `True`, add to any existing expressions.
2066                Otherwise, this flattens all the `From` expression into a single expression.
2067            dialect (str): the dialect used to parse the input expression.
2068            copy (bool): if `False`, modify this expression instance in-place.
2069            opts (kwargs): other options to use to parse the input expressions.
2070
2071        Returns:
2072            Select: the modified expression.
2073        """
2074        return _apply_child_list_builder(
2075            *expressions,
2076            instance=self,
2077            arg="from",
2078            append=append,
2079            copy=copy,
2080            prefix="FROM",
2081            into=From,
2082            dialect=dialect,
2083            **opts,
2084        )
2085
2086    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2087        """
2088        Set the GROUP BY expression.
2089
2090        Example:
2091            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2092            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2093
2094        Args:
2095            *expressions (str | Expression): the SQL code strings to parse.
2096                If a `Group` instance is passed, this is used as-is.
2097                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2098                If nothing is passed in then a group by is not applied to the expression
2099            append (bool): if `True`, add to any existing expressions.
2100                Otherwise, this flattens all the `Group` expression into a single expression.
2101            dialect (str): the dialect used to parse the input expression.
2102            copy (bool): if `False`, modify this expression instance in-place.
2103            opts (kwargs): other options to use to parse the input expressions.
2104
2105        Returns:
2106            Select: the modified expression.
2107        """
2108        if not expressions:
2109            return self if not copy else self.copy()
2110        return _apply_child_list_builder(
2111            *expressions,
2112            instance=self,
2113            arg="group",
2114            append=append,
2115            copy=copy,
2116            prefix="GROUP BY",
2117            into=Group,
2118            dialect=dialect,
2119            **opts,
2120        )
2121
2122    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2123        """
2124        Set the ORDER BY expression.
2125
2126        Example:
2127            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2128            'SELECT x FROM tbl ORDER BY x DESC'
2129
2130        Args:
2131            *expressions (str | Expression): the SQL code strings to parse.
2132                If a `Group` instance is passed, this is used as-is.
2133                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2134            append (bool): if `True`, add to any existing expressions.
2135                Otherwise, this flattens all the `Order` expression into a single expression.
2136            dialect (str): the dialect used to parse the input expression.
2137            copy (bool): if `False`, modify this expression instance in-place.
2138            opts (kwargs): other options to use to parse the input expressions.
2139
2140        Returns:
2141            Select: the modified expression.
2142        """
2143        return _apply_child_list_builder(
2144            *expressions,
2145            instance=self,
2146            arg="order",
2147            append=append,
2148            copy=copy,
2149            prefix="ORDER BY",
2150            into=Order,
2151            dialect=dialect,
2152            **opts,
2153        )
2154
2155    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2156        """
2157        Set the SORT BY expression.
2158
2159        Example:
2160            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2161            'SELECT x FROM tbl SORT BY x DESC'
2162
2163        Args:
2164            *expressions (str | Expression): the SQL code strings to parse.
2165                If a `Group` instance is passed, this is used as-is.
2166                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2167            append (bool): if `True`, add to any existing expressions.
2168                Otherwise, this flattens all the `Order` expression into a single expression.
2169            dialect (str): the dialect used to parse the input expression.
2170            copy (bool): if `False`, modify this expression instance in-place.
2171            opts (kwargs): other options to use to parse the input expressions.
2172
2173        Returns:
2174            Select: the modified expression.
2175        """
2176        return _apply_child_list_builder(
2177            *expressions,
2178            instance=self,
2179            arg="sort",
2180            append=append,
2181            copy=copy,
2182            prefix="SORT BY",
2183            into=Sort,
2184            dialect=dialect,
2185            **opts,
2186        )
2187
2188    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2189        """
2190        Set the CLUSTER BY expression.
2191
2192        Example:
2193            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2194            'SELECT x FROM tbl CLUSTER BY x DESC'
2195
2196        Args:
2197            *expressions (str | Expression): the SQL code strings to parse.
2198                If a `Group` instance is passed, this is used as-is.
2199                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2200            append (bool): if `True`, add to any existing expressions.
2201                Otherwise, this flattens all the `Order` expression into a single expression.
2202            dialect (str): the dialect used to parse the input expression.
2203            copy (bool): if `False`, modify this expression instance in-place.
2204            opts (kwargs): other options to use to parse the input expressions.
2205
2206        Returns:
2207            Select: the modified expression.
2208        """
2209        return _apply_child_list_builder(
2210            *expressions,
2211            instance=self,
2212            arg="cluster",
2213            append=append,
2214            copy=copy,
2215            prefix="CLUSTER BY",
2216            into=Cluster,
2217            dialect=dialect,
2218            **opts,
2219        )
2220
2221    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2222        """
2223        Set the LIMIT expression.
2224
2225        Example:
2226            >>> Select().from_("tbl").select("x").limit(10).sql()
2227            'SELECT x FROM tbl LIMIT 10'
2228
2229        Args:
2230            expression (str | int | Expression): the SQL code string to parse.
2231                This can also be an integer.
2232                If a `Limit` instance is passed, this is used as-is.
2233                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2234            dialect (str): the dialect used to parse the input expression.
2235            copy (bool): if `False`, modify this expression instance in-place.
2236            opts (kwargs): other options to use to parse the input expressions.
2237
2238        Returns:
2239            Select: the modified expression.
2240        """
2241        return _apply_builder(
2242            expression=expression,
2243            instance=self,
2244            arg="limit",
2245            into=Limit,
2246            prefix="LIMIT",
2247            dialect=dialect,
2248            copy=copy,
2249            **opts,
2250        )
2251
2252    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2253        """
2254        Set the OFFSET expression.
2255
2256        Example:
2257            >>> Select().from_("tbl").select("x").offset(10).sql()
2258            'SELECT x FROM tbl OFFSET 10'
2259
2260        Args:
2261            expression (str | int | Expression): the SQL code string to parse.
2262                This can also be an integer.
2263                If a `Offset` instance is passed, this is used as-is.
2264                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2265            dialect (str): the dialect used to parse the input expression.
2266            copy (bool): if `False`, modify this expression instance in-place.
2267            opts (kwargs): other options to use to parse the input expressions.
2268
2269        Returns:
2270            Select: the modified expression.
2271        """
2272        return _apply_builder(
2273            expression=expression,
2274            instance=self,
2275            arg="offset",
2276            into=Offset,
2277            prefix="OFFSET",
2278            dialect=dialect,
2279            copy=copy,
2280            **opts,
2281        )
2282
2283    def select(
2284        self,
2285        *expressions: ExpOrStr,
2286        append: bool = True,
2287        dialect: DialectType = None,
2288        copy: bool = True,
2289        **opts,
2290    ) -> Select:
2291        """
2292        Append to or set the SELECT expressions.
2293
2294        Example:
2295            >>> Select().select("x", "y").sql()
2296            'SELECT x, y'
2297
2298        Args:
2299            *expressions: the SQL code strings to parse.
2300                If an `Expression` instance is passed, it will be used as-is.
2301            append: if `True`, add to any existing expressions.
2302                Otherwise, this resets the expressions.
2303            dialect: the dialect used to parse the input expressions.
2304            copy: if `False`, modify this expression instance in-place.
2305            opts: other options to use to parse the input expressions.
2306
2307        Returns:
2308            Select: the modified expression.
2309        """
2310        return _apply_list_builder(
2311            *expressions,
2312            instance=self,
2313            arg="expressions",
2314            append=append,
2315            dialect=dialect,
2316            copy=copy,
2317            **opts,
2318        )
2319
2320    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2321        """
2322        Append to or set the LATERAL expressions.
2323
2324        Example:
2325            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2326            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2327
2328        Args:
2329            *expressions (str | Expression): the SQL code strings to parse.
2330                If an `Expression` instance is passed, it will be used as-is.
2331            append (bool): if `True`, add to any existing expressions.
2332                Otherwise, this resets the expressions.
2333            dialect (str): the dialect used to parse the input expressions.
2334            copy (bool): if `False`, modify this expression instance in-place.
2335            opts (kwargs): other options to use to parse the input expressions.
2336
2337        Returns:
2338            Select: the modified expression.
2339        """
2340        return _apply_list_builder(
2341            *expressions,
2342            instance=self,
2343            arg="laterals",
2344            append=append,
2345            into=Lateral,
2346            prefix="LATERAL VIEW",
2347            dialect=dialect,
2348            copy=copy,
2349            **opts,
2350        )
2351
2352    def join(
2353        self,
2354        expression,
2355        on=None,
2356        using=None,
2357        append=True,
2358        join_type=None,
2359        join_alias=None,
2360        dialect=None,
2361        copy=True,
2362        **opts,
2363    ) -> Select:
2364        """
2365        Append to or set the JOIN expressions.
2366
2367        Example:
2368            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2369            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2370
2371            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2372            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2373
2374            Use `join_type` to change the type of join:
2375
2376            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2377            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2378
2379        Args:
2380            expression (str | Expression): the SQL code string to parse.
2381                If an `Expression` instance is passed, it will be used as-is.
2382            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2383                If an `Expression` instance is passed, it will be used as-is.
2384            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2385                If an `Expression` instance is passed, it will be used as-is.
2386            append (bool): if `True`, add to any existing expressions.
2387                Otherwise, this resets the expressions.
2388            join_type (str): If set, alter the parsed join type
2389            dialect (str): the dialect used to parse the input expressions.
2390            copy (bool): if `False`, modify this expression instance in-place.
2391            opts (kwargs): other options to use to parse the input expressions.
2392
2393        Returns:
2394            Select: the modified expression.
2395        """
2396        parse_args = {"dialect": dialect, **opts}
2397
2398        try:
2399            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2400        except ParseError:
2401            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2402
2403        join = expression if isinstance(expression, Join) else Join(this=expression)
2404
2405        if isinstance(join.this, Select):
2406            join.this.replace(join.this.subquery())
2407
2408        if join_type:
2409            natural: t.Optional[Token]
2410            side: t.Optional[Token]
2411            kind: t.Optional[Token]
2412
2413            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2414
2415            if natural:
2416                join.set("natural", True)
2417            if side:
2418                join.set("side", side.text)
2419            if kind:
2420                join.set("kind", kind.text)
2421
2422        if on:
2423            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2424            join.set("on", on)
2425
2426        if using:
2427            join = _apply_list_builder(
2428                *ensure_collection(using),
2429                instance=join,
2430                arg="using",
2431                append=append,
2432                copy=copy,
2433                **opts,
2434            )
2435
2436        if join_alias:
2437            join.set("this", alias_(join.this, join_alias, table=True))
2438        return _apply_list_builder(
2439            join,
2440            instance=self,
2441            arg="joins",
2442            append=append,
2443            copy=copy,
2444            **opts,
2445        )
2446
2447    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2448        """
2449        Append to or set the WHERE expressions.
2450
2451        Example:
2452            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2453            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2454
2455        Args:
2456            *expressions (str | Expression): the SQL code strings to parse.
2457                If an `Expression` instance is passed, it will be used as-is.
2458                Multiple expressions are combined with an AND operator.
2459            append (bool): if `True`, AND the new expressions to any existing expression.
2460                Otherwise, this resets the expression.
2461            dialect (str): the dialect used to parse the input expressions.
2462            copy (bool): if `False`, modify this expression instance in-place.
2463            opts (kwargs): other options to use to parse the input expressions.
2464
2465        Returns:
2466            Select: the modified expression.
2467        """
2468        return _apply_conjunction_builder(
2469            *expressions,
2470            instance=self,
2471            arg="where",
2472            append=append,
2473            into=Where,
2474            dialect=dialect,
2475            copy=copy,
2476            **opts,
2477        )
2478
2479    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2480        """
2481        Append to or set the HAVING expressions.
2482
2483        Example:
2484            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2485            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2486
2487        Args:
2488            *expressions (str | Expression): the SQL code strings to parse.
2489                If an `Expression` instance is passed, it will be used as-is.
2490                Multiple expressions are combined with an AND operator.
2491            append (bool): if `True`, AND the new expressions to any existing expression.
2492                Otherwise, this resets the expression.
2493            dialect (str): the dialect used to parse the input expressions.
2494            copy (bool): if `False`, modify this expression instance in-place.
2495            opts (kwargs): other options to use to parse the input expressions.
2496
2497        Returns:
2498            Select: the modified expression.
2499        """
2500        return _apply_conjunction_builder(
2501            *expressions,
2502            instance=self,
2503            arg="having",
2504            append=append,
2505            into=Having,
2506            dialect=dialect,
2507            copy=copy,
2508            **opts,
2509        )
2510
2511    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2512        return _apply_list_builder(
2513            *expressions,
2514            instance=self,
2515            arg="windows",
2516            append=append,
2517            into=Window,
2518            dialect=dialect,
2519            copy=copy,
2520            **opts,
2521        )
2522
2523    def qualify(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2524        return _apply_conjunction_builder(
2525            *expressions,
2526            instance=self,
2527            arg="qualify",
2528            append=append,
2529            into=Qualify,
2530            dialect=dialect,
2531            copy=copy,
2532            **opts,
2533        )
2534
2535    def distinct(self, distinct=True, copy=True) -> Select:
2536        """
2537        Set the OFFSET expression.
2538
2539        Example:
2540            >>> Select().from_("tbl").select("x").distinct().sql()
2541            'SELECT DISTINCT x FROM tbl'
2542
2543        Args:
2544            distinct (bool): whether the Select should be distinct
2545            copy (bool): if `False`, modify this expression instance in-place.
2546
2547        Returns:
2548            Select: the modified expression.
2549        """
2550        instance = _maybe_copy(self, copy)
2551        instance.set("distinct", Distinct() if distinct else None)
2552        return instance
2553
2554    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2555        """
2556        Convert this expression to a CREATE TABLE AS statement.
2557
2558        Example:
2559            >>> Select().select("*").from_("tbl").ctas("x").sql()
2560            'CREATE TABLE x AS SELECT * FROM tbl'
2561
2562        Args:
2563            table (str | Expression): the SQL code string to parse as the table name.
2564                If another `Expression` instance is passed, it will be used as-is.
2565            properties (dict): an optional mapping of table properties
2566            dialect (str): the dialect used to parse the input table.
2567            copy (bool): if `False`, modify this expression instance in-place.
2568            opts (kwargs): other options to use to parse the input table.
2569
2570        Returns:
2571            Create: the CREATE TABLE AS expression
2572        """
2573        instance = _maybe_copy(self, copy)
2574        table_expression = maybe_parse(
2575            table,
2576            into=Table,
2577            dialect=dialect,
2578            **opts,
2579        )
2580        properties_expression = None
2581        if properties:
2582            properties_expression = Properties.from_dict(properties)
2583
2584        return Create(
2585            this=table_expression,
2586            kind="table",
2587            expression=instance,
2588            properties=properties_expression,
2589        )
2590
2591    def lock(self, update: bool = True, copy: bool = True) -> Select:
2592        """
2593        Set the locking read mode for this expression.
2594
2595        Examples:
2596            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2597            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2598
2599            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2600            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2601
2602        Args:
2603            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2604            copy: if `False`, modify this expression instance in-place.
2605
2606        Returns:
2607            The modified expression.
2608        """
2609
2610        inst = _maybe_copy(self, copy)
2611        inst.set("lock", Lock(update=update))
2612
2613        return inst
2614
2615    @property
2616    def named_selects(self) -> t.List[str]:
2617        return [e.output_name for e in self.expressions if e.alias_or_name]
2618
2619    @property
2620    def is_star(self) -> bool:
2621        return any(expression.is_star for expression in self.expressions)
2622
2623    @property
2624    def selects(self) -> t.List[Expression]:
2625        return self.expressions
2626
2627
2628class Subquery(DerivedTable, Unionable):
2629    arg_types = {
2630        "this": True,
2631        "alias": False,
2632        "with": False,
2633        **QUERY_MODIFIERS,
2634    }
2635
2636    def unnest(self):
2637        """
2638        Returns the first non subquery.
2639        """
2640        expression = self
2641        while isinstance(expression, Subquery):
2642            expression = expression.this
2643        return expression
2644
2645    @property
2646    def is_star(self) -> bool:
2647        return self.this.is_star
2648
2649    @property
2650    def output_name(self):
2651        return self.alias
2652
2653
2654class TableSample(Expression):
2655    arg_types = {
2656        "this": False,
2657        "method": False,
2658        "bucket_numerator": False,
2659        "bucket_denominator": False,
2660        "bucket_field": False,
2661        "percent": False,
2662        "rows": False,
2663        "size": False,
2664        "seed": False,
2665        "kind": False,
2666    }
2667
2668
2669class Tag(Expression):
2670    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2671
2672    arg_types = {
2673        "this": False,
2674        "prefix": False,
2675        "postfix": False,
2676    }
2677
2678
2679class Pivot(Expression):
2680    arg_types = {
2681        "this": False,
2682        "alias": False,
2683        "expressions": True,
2684        "field": True,
2685        "unpivot": True,
2686    }
2687
2688
2689class Window(Expression):
2690    arg_types = {
2691        "this": True,
2692        "partition_by": False,
2693        "order": False,
2694        "spec": False,
2695        "alias": False,
2696    }
2697
2698
2699class WindowSpec(Expression):
2700    arg_types = {
2701        "kind": False,
2702        "start": False,
2703        "start_side": False,
2704        "end": False,
2705        "end_side": False,
2706    }
2707
2708
2709class Where(Expression):
2710    pass
2711
2712
2713class Star(Expression):
2714    arg_types = {"except": False, "replace": False}
2715
2716    @property
2717    def name(self) -> str:
2718        return "*"
2719
2720    @property
2721    def output_name(self):
2722        return self.name
2723
2724
2725class Parameter(Expression):
2726    arg_types = {"this": True, "wrapped": False}
2727
2728
2729class SessionParameter(Expression):
2730    arg_types = {"this": True, "kind": False}
2731
2732
2733class Placeholder(Expression):
2734    arg_types = {"this": False}
2735
2736
2737class Null(Condition):
2738    arg_types: t.Dict[str, t.Any] = {}
2739
2740    @property
2741    def name(self) -> str:
2742        return "NULL"
2743
2744
2745class Boolean(Condition):
2746    pass
2747
2748
2749class DataType(Expression):
2750    arg_types = {
2751        "this": True,
2752        "expressions": False,
2753        "nested": False,
2754        "values": False,
2755        "prefix": False,
2756    }
2757
2758    class Type(AutoName):
2759        CHAR = auto()
2760        NCHAR = auto()
2761        VARCHAR = auto()
2762        NVARCHAR = auto()
2763        TEXT = auto()
2764        MEDIUMTEXT = auto()
2765        LONGTEXT = auto()
2766        MEDIUMBLOB = auto()
2767        LONGBLOB = auto()
2768        BINARY = auto()
2769        VARBINARY = auto()
2770        INT = auto()
2771        UINT = auto()
2772        TINYINT = auto()
2773        UTINYINT = auto()
2774        SMALLINT = auto()
2775        USMALLINT = auto()
2776        BIGINT = auto()
2777        UBIGINT = auto()
2778        FLOAT = auto()
2779        DOUBLE = auto()
2780        DECIMAL = auto()
2781        BIT = auto()
2782        BOOLEAN = auto()
2783        JSON = auto()
2784        JSONB = auto()
2785        INTERVAL = auto()
2786        TIME = auto()
2787        TIMESTAMP = auto()
2788        TIMESTAMPTZ = auto()
2789        TIMESTAMPLTZ = auto()
2790        DATE = auto()
2791        DATETIME = auto()
2792        ARRAY = auto()
2793        MAP = auto()
2794        UUID = auto()
2795        GEOGRAPHY = auto()
2796        GEOMETRY = auto()
2797        STRUCT = auto()
2798        NULLABLE = auto()
2799        HLLSKETCH = auto()
2800        HSTORE = auto()
2801        SUPER = auto()
2802        SERIAL = auto()
2803        SMALLSERIAL = auto()
2804        BIGSERIAL = auto()
2805        XML = auto()
2806        UNIQUEIDENTIFIER = auto()
2807        MONEY = auto()
2808        SMALLMONEY = auto()
2809        ROWVERSION = auto()
2810        IMAGE = auto()
2811        VARIANT = auto()
2812        OBJECT = auto()
2813        INET = auto()
2814        NULL = auto()
2815        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2816
2817    TEXT_TYPES = {
2818        Type.CHAR,
2819        Type.NCHAR,
2820        Type.VARCHAR,
2821        Type.NVARCHAR,
2822        Type.TEXT,
2823    }
2824
2825    INTEGER_TYPES = {
2826        Type.INT,
2827        Type.TINYINT,
2828        Type.SMALLINT,
2829        Type.BIGINT,
2830    }
2831
2832    FLOAT_TYPES = {
2833        Type.FLOAT,
2834        Type.DOUBLE,
2835    }
2836
2837    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2838
2839    TEMPORAL_TYPES = {
2840        Type.TIMESTAMP,
2841        Type.TIMESTAMPTZ,
2842        Type.TIMESTAMPLTZ,
2843        Type.DATE,
2844        Type.DATETIME,
2845    }
2846
2847    @classmethod
2848    def build(
2849        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2850    ) -> DataType:
2851        from sqlglot import parse_one
2852
2853        if isinstance(dtype, str):
2854            if dtype.upper() in cls.Type.__members__:
2855                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2856            else:
2857                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2858            if data_type_exp is None:
2859                raise ValueError(f"Unparsable data type value: {dtype}")
2860        elif isinstance(dtype, DataType.Type):
2861            data_type_exp = DataType(this=dtype)
2862        elif isinstance(dtype, DataType):
2863            return dtype
2864        else:
2865            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2866        return DataType(**{**data_type_exp.args, **kwargs})
2867
2868    def is_type(self, dtype: DataType.Type) -> bool:
2869        return self.this == dtype
2870
2871
2872# https://www.postgresql.org/docs/15/datatype-pseudo.html
2873class PseudoType(Expression):
2874    pass
2875
2876
2877class StructKwarg(Expression):
2878    arg_types = {"this": True, "expression": True}
2879
2880
2881# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2882class SubqueryPredicate(Predicate):
2883    pass
2884
2885
2886class All(SubqueryPredicate):
2887    pass
2888
2889
2890class Any(SubqueryPredicate):
2891    pass
2892
2893
2894class Exists(SubqueryPredicate):
2895    pass
2896
2897
2898# Commands to interact with the databases or engines. For most of the command
2899# expressions we parse whatever comes after the command's name as a string.
2900class Command(Expression):
2901    arg_types = {"this": True, "expression": False}
2902
2903
2904class Transaction(Expression):
2905    arg_types = {"this": False, "modes": False}
2906
2907
2908class Commit(Expression):
2909    arg_types = {"chain": False}
2910
2911
2912class Rollback(Expression):
2913    arg_types = {"savepoint": False}
2914
2915
2916class AlterTable(Expression):
2917    arg_types = {"this": True, "actions": True, "exists": False}
2918
2919
2920class AddConstraint(Expression):
2921    arg_types = {"this": False, "expression": False, "enforced": False}
2922
2923
2924class DropPartition(Expression):
2925    arg_types = {"expressions": True, "exists": False}
2926
2927
2928# Binary expressions like (ADD a b)
2929class Binary(Expression):
2930    arg_types = {"this": True, "expression": True}
2931
2932    @property
2933    def left(self):
2934        return self.this
2935
2936    @property
2937    def right(self):
2938        return self.expression
2939
2940
2941class Add(Binary):
2942    pass
2943
2944
2945class Connector(Binary, Condition):
2946    pass
2947
2948
2949class And(Connector):
2950    pass
2951
2952
2953class Or(Connector):
2954    pass
2955
2956
2957class BitwiseAnd(Binary):
2958    pass
2959
2960
2961class BitwiseLeftShift(Binary):
2962    pass
2963
2964
2965class BitwiseOr(Binary):
2966    pass
2967
2968
2969class BitwiseRightShift(Binary):
2970    pass
2971
2972
2973class BitwiseXor(Binary):
2974    pass
2975
2976
2977class Div(Binary):
2978    pass
2979
2980
2981class Overlaps(Binary):
2982    pass
2983
2984
2985class Dot(Binary):
2986    @property
2987    def name(self) -> str:
2988        return self.expression.name
2989
2990
2991class DPipe(Binary):
2992    pass
2993
2994
2995class EQ(Binary, Predicate):
2996    pass
2997
2998
2999class NullSafeEQ(Binary, Predicate):
3000    pass
3001
3002
3003class NullSafeNEQ(Binary, Predicate):
3004    pass
3005
3006
3007class Distance(Binary):
3008    pass
3009
3010
3011class Escape(Binary):
3012    pass
3013
3014
3015class Glob(Binary, Predicate):
3016    pass
3017
3018
3019class GT(Binary, Predicate):
3020    pass
3021
3022
3023class GTE(Binary, Predicate):
3024    pass
3025
3026
3027class ILike(Binary, Predicate):
3028    pass
3029
3030
3031class ILikeAny(Binary, Predicate):
3032    pass
3033
3034
3035class IntDiv(Binary):
3036    pass
3037
3038
3039class Is(Binary, Predicate):
3040    pass
3041
3042
3043class Kwarg(Binary):
3044    """Kwarg in special functions like func(kwarg => y)."""
3045
3046
3047class Like(Binary, Predicate):
3048    pass
3049
3050
3051class LikeAny(Binary, Predicate):
3052    pass
3053
3054
3055class LT(Binary, Predicate):
3056    pass
3057
3058
3059class LTE(Binary, Predicate):
3060    pass
3061
3062
3063class Mod(Binary):
3064    pass
3065
3066
3067class Mul(Binary):
3068    pass
3069
3070
3071class NEQ(Binary, Predicate):
3072    pass
3073
3074
3075class SimilarTo(Binary, Predicate):
3076    pass
3077
3078
3079class Slice(Binary):
3080    arg_types = {"this": False, "expression": False}
3081
3082
3083class Sub(Binary):
3084    pass
3085
3086
3087class ArrayOverlaps(Binary):
3088    pass
3089
3090
3091# Unary Expressions
3092# (NOT a)
3093class Unary(Expression):
3094    pass
3095
3096
3097class BitwiseNot(Unary):
3098    pass
3099
3100
3101class Not(Unary, Condition):
3102    pass
3103
3104
3105class Paren(Unary, Condition):
3106    arg_types = {"this": True, "with": False}
3107
3108
3109class Neg(Unary):
3110    pass
3111
3112
3113# Special Functions
3114class Alias(Expression):
3115    arg_types = {"this": True, "alias": False}
3116
3117    @property
3118    def output_name(self):
3119        return self.alias
3120
3121
3122class Aliases(Expression):
3123    arg_types = {"this": True, "expressions": True}
3124
3125    @property
3126    def aliases(self):
3127        return self.expressions
3128
3129
3130class AtTimeZone(Expression):
3131    arg_types = {"this": True, "zone": True}
3132
3133
3134class Between(Predicate):
3135    arg_types = {"this": True, "low": True, "high": True}
3136
3137
3138class Bracket(Condition):
3139    arg_types = {"this": True, "expressions": True}
3140
3141
3142class Distinct(Expression):
3143    arg_types = {"expressions": False, "on": False}
3144
3145
3146class In(Predicate):
3147    arg_types = {
3148        "this": True,
3149        "expressions": False,
3150        "query": False,
3151        "unnest": False,
3152        "field": False,
3153        "is_global": False,
3154    }
3155
3156
3157class TimeUnit(Expression):
3158    """Automatically converts unit arg into a var."""
3159
3160    arg_types = {"unit": False}
3161
3162    def __init__(self, **args):
3163        unit = args.get("unit")
3164        if isinstance(unit, (Column, Literal)):
3165            args["unit"] = Var(this=unit.name)
3166        elif isinstance(unit, Week):
3167            unit.set("this", Var(this=unit.this.name))
3168        super().__init__(**args)
3169
3170
3171class Interval(TimeUnit):
3172    arg_types = {"this": False, "unit": False}
3173
3174
3175class IgnoreNulls(Expression):
3176    pass
3177
3178
3179class RespectNulls(Expression):
3180    pass
3181
3182
3183# Functions
3184class Func(Condition):
3185    """
3186    The base class for all function expressions.
3187
3188    Attributes:
3189        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3190            treated as a variable length argument and the argument's value will be stored as a list.
3191        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3192            for this function expression. These values are used to map this node to a name during parsing
3193            as well as to provide the function's name during SQL string generation. By default the SQL
3194            name is set to the expression's class name transformed to snake case.
3195    """
3196
3197    is_var_len_args = False
3198
3199    @classmethod
3200    def from_arg_list(cls, args):
3201        if cls.is_var_len_args:
3202            all_arg_keys = list(cls.arg_types)
3203            # If this function supports variable length argument treat the last argument as such.
3204            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3205            num_non_var = len(non_var_len_arg_keys)
3206
3207            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3208            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3209        else:
3210            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3211
3212        return cls(**args_dict)
3213
3214    @classmethod
3215    def sql_names(cls):
3216        if cls is Func:
3217            raise NotImplementedError(
3218                "SQL name is only supported by concrete function implementations"
3219            )
3220        if "_sql_names" not in cls.__dict__:
3221            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3222        return cls._sql_names
3223
3224    @classmethod
3225    def sql_name(cls):
3226        return cls.sql_names()[0]
3227
3228    @classmethod
3229    def default_parser_mappings(cls):
3230        return {name: cls.from_arg_list for name in cls.sql_names()}
3231
3232
3233class AggFunc(Func):
3234    pass
3235
3236
3237class Abs(Func):
3238    pass
3239
3240
3241class Anonymous(Func):
3242    arg_types = {"this": True, "expressions": False}
3243    is_var_len_args = True
3244
3245
3246class ApproxDistinct(AggFunc):
3247    arg_types = {"this": True, "accuracy": False}
3248
3249
3250class Array(Func):
3251    arg_types = {"expressions": False}
3252    is_var_len_args = True
3253
3254
3255# https://docs.snowflake.com/en/sql-reference/functions/to_char
3256class ToChar(Func):
3257    arg_types = {"this": True, "format": False}
3258
3259
3260class GenerateSeries(Func):
3261    arg_types = {"start": True, "end": True, "step": False}
3262
3263
3264class ArrayAgg(AggFunc):
3265    pass
3266
3267
3268class ArrayAll(Func):
3269    arg_types = {"this": True, "expression": True}
3270
3271
3272class ArrayAny(Func):
3273    arg_types = {"this": True, "expression": True}
3274
3275
3276class ArrayConcat(Func):
3277    arg_types = {"this": True, "expressions": False}
3278    is_var_len_args = True
3279
3280
3281class ArrayContains(Binary, Func):
3282    pass
3283
3284
3285class ArrayContained(Binary):
3286    pass
3287
3288
3289class ArrayFilter(Func):
3290    arg_types = {"this": True, "expression": True}
3291    _sql_names = ["FILTER", "ARRAY_FILTER"]
3292
3293
3294class ArrayJoin(Func):
3295    arg_types = {"this": True, "expression": True, "null": False}
3296
3297
3298class ArraySize(Func):
3299    arg_types = {"this": True, "expression": False}
3300
3301
3302class ArraySort(Func):
3303    arg_types = {"this": True, "expression": False}
3304
3305
3306class ArraySum(Func):
3307    pass
3308
3309
3310class ArrayUnionAgg(AggFunc):
3311    pass
3312
3313
3314class Avg(AggFunc):
3315    pass
3316
3317
3318class AnyValue(AggFunc):
3319    pass
3320
3321
3322class Case(Func):
3323    arg_types = {"this": False, "ifs": True, "default": False}
3324
3325
3326class Cast(Func):
3327    arg_types = {"this": True, "to": True}
3328
3329    @property
3330    def name(self) -> str:
3331        return self.this.name
3332
3333    @property
3334    def to(self):
3335        return self.args["to"]
3336
3337    @property
3338    def output_name(self):
3339        return self.name
3340
3341    def is_type(self, dtype: DataType.Type) -> bool:
3342        return self.to.is_type(dtype)
3343
3344
3345class Collate(Binary):
3346    pass
3347
3348
3349class TryCast(Cast):
3350    pass
3351
3352
3353class Ceil(Func):
3354    arg_types = {"this": True, "decimals": False}
3355    _sql_names = ["CEIL", "CEILING"]
3356
3357
3358class Coalesce(Func):
3359    arg_types = {"this": True, "expressions": False}
3360    is_var_len_args = True
3361
3362
3363class Concat(Func):
3364    arg_types = {"expressions": True}
3365    is_var_len_args = True
3366
3367
3368class ConcatWs(Concat):
3369    _sql_names = ["CONCAT_WS"]
3370
3371
3372class Count(AggFunc):
3373    arg_types = {"this": False}
3374
3375
3376class CountIf(AggFunc):
3377    pass
3378
3379
3380class CurrentDate(Func):
3381    arg_types = {"this": False}
3382
3383
3384class CurrentDatetime(Func):
3385    arg_types = {"this": False}
3386
3387
3388class CurrentTime(Func):
3389    arg_types = {"this": False}
3390
3391
3392class CurrentTimestamp(Func):
3393    arg_types = {"this": False}
3394
3395
3396class DateAdd(Func, TimeUnit):
3397    arg_types = {"this": True, "expression": True, "unit": False}
3398
3399
3400class DateSub(Func, TimeUnit):
3401    arg_types = {"this": True, "expression": True, "unit": False}
3402
3403
3404class DateDiff(Func, TimeUnit):
3405    _sql_names = ["DATEDIFF", "DATE_DIFF"]
3406    arg_types = {"this": True, "expression": True, "unit": False}
3407
3408
3409class DateTrunc(Func):
3410    arg_types = {"unit": True, "this": True, "zone": False}
3411
3412
3413class DatetimeAdd(Func, TimeUnit):
3414    arg_types = {"this": True, "expression": True, "unit": False}
3415
3416
3417class DatetimeSub(Func, TimeUnit):
3418    arg_types = {"this": True, "expression": True, "unit": False}
3419
3420
3421class DatetimeDiff(Func, TimeUnit):
3422    arg_types = {"this": True, "expression": True, "unit": False}
3423
3424
3425class DatetimeTrunc(Func, TimeUnit):
3426    arg_types = {"this": True, "unit": True, "zone": False}
3427
3428
3429class DayOfWeek(Func):
3430    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3431
3432
3433class DayOfMonth(Func):
3434    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3435
3436
3437class DayOfYear(Func):
3438    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3439
3440
3441class WeekOfYear(Func):
3442    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3443
3444
3445class LastDateOfMonth(Func):
3446    pass
3447
3448
3449class Extract(Func):
3450    arg_types = {"this": True, "expression": True}
3451
3452
3453class TimestampAdd(Func, TimeUnit):
3454    arg_types = {"this": True, "expression": True, "unit": False}
3455
3456
3457class TimestampSub(Func, TimeUnit):
3458    arg_types = {"this": True, "expression": True, "unit": False}
3459
3460
3461class TimestampDiff(Func, TimeUnit):
3462    arg_types = {"this": True, "expression": True, "unit": False}
3463
3464
3465class TimestampTrunc(Func, TimeUnit):
3466    arg_types = {"this": True, "unit": True, "zone": False}
3467
3468
3469class TimeAdd(Func, TimeUnit):
3470    arg_types = {"this": True, "expression": True, "unit": False}
3471
3472
3473class TimeSub(Func, TimeUnit):
3474    arg_types = {"this": True, "expression": True, "unit": False}
3475
3476
3477class TimeDiff(Func, TimeUnit):
3478    arg_types = {"this": True, "expression": True, "unit": False}
3479
3480
3481class TimeTrunc(Func, TimeUnit):
3482    arg_types = {"this": True, "unit": True, "zone": False}
3483
3484
3485class DateFromParts(Func):
3486    _sql_names = ["DATEFROMPARTS"]
3487    arg_types = {"year": True, "month": True, "day": True}
3488
3489
3490class DateStrToDate(Func):
3491    pass
3492
3493
3494class DateToDateStr(Func):
3495    pass
3496
3497
3498class DateToDi(Func):
3499    pass
3500
3501
3502class Day(Func):
3503    pass
3504
3505
3506class Decode(Func):
3507    arg_types = {"this": True, "charset": True, "replace": False}
3508
3509
3510class DiToDate(Func):
3511    pass
3512
3513
3514class Encode(Func):
3515    arg_types = {"this": True, "charset": True}
3516
3517
3518class Exp(Func):
3519    pass
3520
3521
3522class Explode(Func):
3523    pass
3524
3525
3526class Floor(Func):
3527    arg_types = {"this": True, "decimals": False}
3528
3529
3530class Greatest(Func):
3531    arg_types = {"this": True, "expressions": False}
3532    is_var_len_args = True
3533
3534
3535class GroupConcat(Func):
3536    arg_types = {"this": True, "separator": False}
3537
3538
3539class Hex(Func):
3540    pass
3541
3542
3543class If(Func):
3544    arg_types = {"this": True, "true": True, "false": False}
3545
3546
3547class IfNull(Func):
3548    arg_types = {"this": True, "expression": False}
3549    _sql_names = ["IFNULL", "NVL"]
3550
3551
3552class Initcap(Func):
3553    pass
3554
3555
3556class JSONBContains(Binary):
3557    _sql_names = ["JSONB_CONTAINS"]
3558
3559
3560class JSONExtract(Binary, Func):
3561    _sql_names = ["JSON_EXTRACT"]
3562
3563
3564class JSONExtractScalar(JSONExtract):
3565    _sql_names = ["JSON_EXTRACT_SCALAR"]
3566
3567
3568class JSONBExtract(JSONExtract):
3569    _sql_names = ["JSONB_EXTRACT"]
3570
3571
3572class JSONBExtractScalar(JSONExtract):
3573    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3574
3575
3576class Least(Func):
3577    arg_types = {"expressions": False}
3578    is_var_len_args = True
3579
3580
3581class Length(Func):
3582    pass
3583
3584
3585class Levenshtein(Func):
3586    arg_types = {
3587        "this": True,
3588        "expression": False,
3589        "ins_cost": False,
3590        "del_cost": False,
3591        "sub_cost": False,
3592    }
3593
3594
3595class Ln(Func):
3596    pass
3597
3598
3599class Log(Func):
3600    arg_types = {"this": True, "expression": False}
3601
3602
3603class Log2(Func):
3604    pass
3605
3606
3607class Log10(Func):
3608    pass
3609
3610
3611class LogicalOr(AggFunc):
3612    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
3613
3614
3615class LogicalAnd(AggFunc):
3616    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
3617
3618
3619class Lower(Func):
3620    _sql_names = ["LOWER", "LCASE"]
3621
3622
3623class Map(Func):
3624    arg_types = {"keys": False, "values": False}
3625
3626
3627class VarMap(Func):
3628    arg_types = {"keys": True, "values": True}
3629    is_var_len_args = True
3630
3631
3632class Matches(Func):
3633    """Oracle/Snowflake decode.
3634    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3635    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3636    """
3637
3638    arg_types = {"this": True, "expressions": True}
3639    is_var_len_args = True
3640
3641
3642class Max(AggFunc):
3643    arg_types = {"this": True, "expressions": False}
3644    is_var_len_args = True
3645
3646
3647class Min(AggFunc):
3648    arg_types = {"this": True, "expressions": False}
3649    is_var_len_args = True
3650
3651
3652class Month(Func):
3653    pass
3654
3655
3656class Nvl2(Func):
3657    arg_types = {"this": True, "true": True, "false": False}
3658
3659
3660class Posexplode(Func):
3661    pass
3662
3663
3664class Pow(Binary, Func):
3665    _sql_names = ["POWER", "POW"]
3666
3667
3668class PercentileCont(AggFunc):
3669    pass
3670
3671
3672class PercentileDisc(AggFunc):
3673    pass
3674
3675
3676class Quantile(AggFunc):
3677    arg_types = {"this": True, "quantile": True}
3678
3679
3680# Clickhouse-specific:
3681# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3682class Quantiles(AggFunc):
3683    arg_types = {"parameters": True, "expressions": True}
3684    is_var_len_args = True
3685
3686
3687class QuantileIf(AggFunc):
3688    arg_types = {"parameters": True, "expressions": True}
3689
3690
3691class ApproxQuantile(Quantile):
3692    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3693
3694
3695class RangeN(Func):
3696    arg_types = {"this": True, "expressions": True, "each": False}
3697
3698
3699class ReadCSV(Func):
3700    _sql_names = ["READ_CSV"]
3701    is_var_len_args = True
3702    arg_types = {"this": True, "expressions": False}
3703
3704
3705class Reduce(Func):
3706    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3707
3708
3709class RegexpExtract(Func):
3710    arg_types = {
3711        "this": True,
3712        "expression": True,
3713        "position": False,
3714        "occurrence": False,
3715        "group": False,
3716    }
3717
3718
3719class RegexpLike(Func):
3720    arg_types = {"this": True, "expression": True, "flag": False}
3721
3722
3723class RegexpILike(Func):
3724    arg_types = {"this": True, "expression": True, "flag": False}
3725
3726
3727class RegexpSplit(Func):
3728    arg_types = {"this": True, "expression": True}
3729
3730
3731class Repeat(Func):
3732    arg_types = {"this": True, "times": True}
3733
3734
3735class Round(Func):
3736    arg_types = {"this": True, "decimals": False}
3737
3738
3739class RowNumber(Func):
3740    arg_types: t.Dict[str, t.Any] = {}
3741
3742
3743class SafeDivide(Func):
3744    arg_types = {"this": True, "expression": True}
3745
3746
3747class SetAgg(AggFunc):
3748    pass
3749
3750
3751class SortArray(Func):
3752    arg_types = {"this": True, "asc": False}
3753
3754
3755class Split(Func):
3756    arg_types = {"this": True, "expression": True, "limit": False}
3757
3758
3759# Start may be omitted in the case of postgres
3760# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3761class Substring(Func):
3762    arg_types = {"this": True, "start": False, "length": False}
3763
3764
3765class StrPosition(Func):
3766    arg_types = {
3767        "this": True,
3768        "substr": True,
3769        "position": False,
3770        "instance": False,
3771    }
3772
3773
3774class StrToDate(Func):
3775    arg_types = {"this": True, "format": True}
3776
3777
3778class StrToTime(Func):
3779    arg_types = {"this": True, "format": True}
3780
3781
3782# Spark allows unix_timestamp()
3783# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3784class StrToUnix(Func):
3785    arg_types = {"this": False, "format": False}
3786
3787
3788class NumberToStr(Func):
3789    arg_types = {"this": True, "format": True}
3790
3791
3792class Struct(Func):
3793    arg_types = {"expressions": True}
3794    is_var_len_args = True
3795
3796
3797class StructExtract(Func):
3798    arg_types = {"this": True, "expression": True}
3799
3800
3801class Sum(AggFunc):
3802    pass
3803
3804
3805class Sqrt(Func):
3806    pass
3807
3808
3809class Stddev(AggFunc):
3810    pass
3811
3812
3813class StddevPop(AggFunc):
3814    pass
3815
3816
3817class StddevSamp(AggFunc):
3818    pass
3819
3820
3821class TimeToStr(Func):
3822    arg_types = {"this": True, "format": True}
3823
3824
3825class TimeToTimeStr(Func):
3826    pass
3827
3828
3829class TimeToUnix(Func):
3830    pass
3831
3832
3833class TimeStrToDate(Func):
3834    pass
3835
3836
3837class TimeStrToTime(Func):
3838    pass
3839
3840
3841class TimeStrToUnix(Func):
3842    pass
3843
3844
3845class Trim(Func):
3846    arg_types = {
3847        "this": True,
3848        "expression": False,
3849        "position": False,
3850        "collation": False,
3851    }
3852
3853
3854class TsOrDsAdd(Func, TimeUnit):
3855    arg_types = {"this": True, "expression": True, "unit": False}
3856
3857
3858class TsOrDsToDateStr(Func):
3859    pass
3860
3861
3862class TsOrDsToDate(Func):
3863    arg_types = {"this": True, "format": False}
3864
3865
3866class TsOrDiToDi(Func):
3867    pass
3868
3869
3870class Unhex(Func):
3871    pass
3872
3873
3874class UnixToStr(Func):
3875    arg_types = {"this": True, "format": False}
3876
3877
3878# https://prestodb.io/docs/current/functions/datetime.html
3879# presto has weird zone/hours/minutes
3880class UnixToTime(Func):
3881    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3882
3883    SECONDS = Literal.string("seconds")
3884    MILLIS = Literal.string("millis")
3885    MICROS = Literal.string("micros")
3886
3887
3888class UnixToTimeStr(Func):
3889    pass
3890
3891
3892class Upper(Func):
3893    _sql_names = ["UPPER", "UCASE"]
3894
3895
3896class Variance(AggFunc):
3897    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3898
3899
3900class VariancePop(AggFunc):
3901    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3902
3903
3904class Week(Func):
3905    arg_types = {"this": True, "mode": False}
3906
3907
3908class XMLTable(Func):
3909    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3910
3911
3912class Year(Func):
3913    pass
3914
3915
3916class Use(Expression):
3917    arg_types = {"this": True, "kind": False}
3918
3919
3920class Merge(Expression):
3921    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3922
3923
3924class When(Func):
3925    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
3926
3927
3928def _norm_args(expression):
3929    args = {}
3930
3931    for k, arg in expression.args.items():
3932        if isinstance(arg, list):
3933            arg = [_norm_arg(a) for a in arg]
3934            if not arg:
3935                arg = None
3936        else:
3937            arg = _norm_arg(arg)
3938
3939        if arg is not None and arg is not False:
3940            args[k] = arg
3941
3942    return args
3943
3944
3945def _norm_arg(arg):
3946    return arg.lower() if isinstance(arg, str) else arg
3947
3948
3949ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3950
3951
3952# Helpers
3953def maybe_parse(
3954    sql_or_expression: ExpOrStr,
3955    *,
3956    into: t.Optional[IntoType] = None,
3957    dialect: DialectType = None,
3958    prefix: t.Optional[str] = None,
3959    copy: bool = False,
3960    **opts,
3961) -> Expression:
3962    """Gracefully handle a possible string or expression.
3963
3964    Example:
3965        >>> maybe_parse("1")
3966        (LITERAL this: 1, is_string: False)
3967        >>> maybe_parse(to_identifier("x"))
3968        (IDENTIFIER this: x, quoted: False)
3969
3970    Args:
3971        sql_or_expression: the SQL code string or an expression
3972        into: the SQLGlot Expression to parse into
3973        dialect: the dialect used to parse the input expressions (in the case that an
3974            input expression is a SQL string).
3975        prefix: a string to prefix the sql with before it gets parsed
3976            (automatically includes a space)
3977        copy: whether or not to copy the expression.
3978        **opts: other options to use to parse the input expressions (again, in the case
3979            that an input expression is a SQL string).
3980
3981    Returns:
3982        Expression: the parsed or given expression.
3983    """
3984    if isinstance(sql_or_expression, Expression):
3985        if copy:
3986            return sql_or_expression.copy()
3987        return sql_or_expression
3988
3989    import sqlglot
3990
3991    sql = str(sql_or_expression)
3992    if prefix:
3993        sql = f"{prefix} {sql}"
3994    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
3995
3996
3997def _maybe_copy(instance, copy=True):
3998    return instance.copy() if copy else instance
3999
4000
4001def _is_wrong_expression(expression, into):
4002    return isinstance(expression, Expression) and not isinstance(expression, into)
4003
4004
4005def _apply_builder(
4006    expression,
4007    instance,
4008    arg,
4009    copy=True,
4010    prefix=None,
4011    into=None,
4012    dialect=None,
4013    **opts,
4014):
4015    if _is_wrong_expression(expression, into):
4016        expression = into(this=expression)
4017    instance = _maybe_copy(instance, copy)
4018    expression = maybe_parse(
4019        sql_or_expression=expression,
4020        prefix=prefix,
4021        into=into,
4022        dialect=dialect,
4023        **opts,
4024    )
4025    instance.set(arg, expression)
4026    return instance
4027
4028
4029def _apply_child_list_builder(
4030    *expressions,
4031    instance,
4032    arg,
4033    append=True,
4034    copy=True,
4035    prefix=None,
4036    into=None,
4037    dialect=None,
4038    properties=None,
4039    **opts,
4040):
4041    instance = _maybe_copy(instance, copy)
4042    parsed = []
4043    for expression in expressions:
4044        if _is_wrong_expression(expression, into):
4045            expression = into(expressions=[expression])
4046        expression = maybe_parse(
4047            expression,
4048            into=into,
4049            dialect=dialect,
4050            prefix=prefix,
4051            **opts,
4052        )
4053        parsed.extend(expression.expressions)
4054
4055    existing = instance.args.get(arg)
4056    if append and existing:
4057        parsed = existing.expressions + parsed
4058
4059    child = into(expressions=parsed)
4060    for k, v in (properties or {}).items():
4061        child.set(k, v)
4062    instance.set(arg, child)
4063    return instance
4064
4065
4066def _apply_list_builder(
4067    *expressions,
4068    instance,
4069    arg,
4070    append=True,
4071    copy=True,
4072    prefix=None,
4073    into=None,
4074    dialect=None,
4075    **opts,
4076):
4077    inst = _maybe_copy(instance, copy)
4078
4079    expressions = [
4080        maybe_parse(
4081            sql_or_expression=expression,
4082            into=into,
4083            prefix=prefix,
4084            dialect=dialect,
4085            **opts,
4086        )
4087        for expression in expressions
4088    ]
4089
4090    existing_expressions = inst.args.get(arg)
4091    if append and existing_expressions:
4092        expressions = existing_expressions + expressions
4093
4094    inst.set(arg, expressions)
4095    return inst
4096
4097
4098def _apply_conjunction_builder(
4099    *expressions,
4100    instance,
4101    arg,
4102    into=None,
4103    append=True,
4104    copy=True,
4105    dialect=None,
4106    **opts,
4107):
4108    expressions = [exp for exp in expressions if exp is not None and exp != ""]
4109    if not expressions:
4110        return instance
4111
4112    inst = _maybe_copy(instance, copy)
4113
4114    existing = inst.args.get(arg)
4115    if append and existing is not None:
4116        expressions = [existing.this if into else existing] + list(expressions)
4117
4118    node = and_(*expressions, dialect=dialect, **opts)
4119
4120    inst.set(arg, into(this=node) if into else node)
4121    return inst
4122
4123
4124def _combine(expressions, operator, dialect=None, **opts):
4125    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
4126    this = expressions[0]
4127    if expressions[1:]:
4128        this = _wrap_operator(this)
4129    for expression in expressions[1:]:
4130        this = operator(this=this, expression=_wrap_operator(expression))
4131    return this
4132
4133
4134def _wrap_operator(expression):
4135    if isinstance(expression, (And, Or, Not)):
4136        expression = Paren(this=expression)
4137    return expression
4138
4139
4140def union(left, right, distinct=True, dialect=None, **opts):
4141    """
4142    Initializes a syntax tree from one UNION expression.
4143
4144    Example:
4145        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
4146        'SELECT * FROM foo UNION SELECT * FROM bla'
4147
4148    Args:
4149        left (str | Expression): the SQL code string corresponding to the left-hand side.
4150            If an `Expression` instance is passed, it will be used as-is.
4151        right (str | Expression): the SQL code string corresponding to the right-hand side.
4152            If an `Expression` instance is passed, it will be used as-is.
4153        distinct (bool): set the DISTINCT flag if and only if this is true.
4154        dialect (str): the dialect used to parse the input expression.
4155        opts (kwargs): other options to use to parse the input expressions.
4156    Returns:
4157        Union: the syntax tree for the UNION expression.
4158    """
4159    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4160    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4161
4162    return Union(this=left, expression=right, distinct=distinct)
4163
4164
4165def intersect(left, right, distinct=True, dialect=None, **opts):
4166    """
4167    Initializes a syntax tree from one INTERSECT expression.
4168
4169    Example:
4170        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4171        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4172
4173    Args:
4174        left (str | Expression): the SQL code string corresponding to the left-hand side.
4175            If an `Expression` instance is passed, it will be used as-is.
4176        right (str | Expression): the SQL code string corresponding to the right-hand side.
4177            If an `Expression` instance is passed, it will be used as-is.
4178        distinct (bool): set the DISTINCT flag if and only if this is true.
4179        dialect (str): the dialect used to parse the input expression.
4180        opts (kwargs): other options to use to parse the input expressions.
4181    Returns:
4182        Intersect: the syntax tree for the INTERSECT expression.
4183    """
4184    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4185    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4186
4187    return Intersect(this=left, expression=right, distinct=distinct)
4188
4189
4190def except_(left, right, distinct=True, dialect=None, **opts):
4191    """
4192    Initializes a syntax tree from one EXCEPT expression.
4193
4194    Example:
4195        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4196        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4197
4198    Args:
4199        left (str | Expression): the SQL code string corresponding to the left-hand side.
4200            If an `Expression` instance is passed, it will be used as-is.
4201        right (str | Expression): the SQL code string corresponding to the right-hand side.
4202            If an `Expression` instance is passed, it will be used as-is.
4203        distinct (bool): set the DISTINCT flag if and only if this is true.
4204        dialect (str): the dialect used to parse the input expression.
4205        opts (kwargs): other options to use to parse the input expressions.
4206    Returns:
4207        Except: the syntax tree for the EXCEPT statement.
4208    """
4209    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4210    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4211
4212    return Except(this=left, expression=right, distinct=distinct)
4213
4214
4215def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
4216    """
4217    Initializes a syntax tree from one or multiple SELECT expressions.
4218
4219    Example:
4220        >>> select("col1", "col2").from_("tbl").sql()
4221        'SELECT col1, col2 FROM tbl'
4222
4223    Args:
4224        *expressions: the SQL code string to parse as the expressions of a
4225            SELECT statement. If an Expression instance is passed, this is used as-is.
4226        dialect: the dialect used to parse the input expressions (in the case that an
4227            input expression is a SQL string).
4228        **opts: other options to use to parse the input expressions (again, in the case
4229            that an input expression is a SQL string).
4230
4231    Returns:
4232        Select: the syntax tree for the SELECT statement.
4233    """
4234    return Select().select(*expressions, dialect=dialect, **opts)
4235
4236
4237def from_(*expressions, dialect=None, **opts) -> Select:
4238    """
4239    Initializes a syntax tree from a FROM expression.
4240
4241    Example:
4242        >>> from_("tbl").select("col1", "col2").sql()
4243        'SELECT col1, col2 FROM tbl'
4244
4245    Args:
4246        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4247            SELECT statement. If an Expression instance is passed, this is used as-is.
4248        dialect (str): the dialect used to parse the input expression (in the case that the
4249            input expression is a SQL string).
4250        **opts: other options to use to parse the input expressions (again, in the case
4251            that the input expression is a SQL string).
4252
4253    Returns:
4254        Select: the syntax tree for the SELECT statement.
4255    """
4256    return Select().from_(*expressions, dialect=dialect, **opts)
4257
4258
4259def update(
4260    table: str | Table,
4261    properties: dict,
4262    where: t.Optional[ExpOrStr] = None,
4263    from_: t.Optional[ExpOrStr] = None,
4264    dialect: DialectType = None,
4265    **opts,
4266) -> Update:
4267    """
4268    Creates an update statement.
4269
4270    Example:
4271        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4272        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4273
4274    Args:
4275        *properties: dictionary of properties to set which are
4276            auto converted to sql objects eg None -> NULL
4277        where: sql conditional parsed into a WHERE statement
4278        from_: sql statement parsed into a FROM statement
4279        dialect: the dialect used to parse the input expressions.
4280        **opts: other options to use to parse the input expressions.
4281
4282    Returns:
4283        Update: the syntax tree for the UPDATE statement.
4284    """
4285    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4286    update_expr.set(
4287        "expressions",
4288        [
4289            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4290            for k, v in properties.items()
4291        ],
4292    )
4293    if from_:
4294        update_expr.set(
4295            "from",
4296            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4297        )
4298    if isinstance(where, Condition):
4299        where = Where(this=where)
4300    if where:
4301        update_expr.set(
4302            "where",
4303            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4304        )
4305    return update_expr
4306
4307
4308def delete(
4309    table: ExpOrStr,
4310    where: t.Optional[ExpOrStr] = None,
4311    returning: t.Optional[ExpOrStr] = None,
4312    dialect: DialectType = None,
4313    **opts,
4314) -> Delete:
4315    """
4316    Builds a delete statement.
4317
4318    Example:
4319        >>> delete("my_table", where="id > 1").sql()
4320        'DELETE FROM my_table WHERE id > 1'
4321
4322    Args:
4323        where: sql conditional parsed into a WHERE statement
4324        returning: sql conditional parsed into a RETURNING statement
4325        dialect: the dialect used to parse the input expressions.
4326        **opts: other options to use to parse the input expressions.
4327
4328    Returns:
4329        Delete: the syntax tree for the DELETE statement.
4330    """
4331    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
4332    if where:
4333        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
4334    if returning:
4335        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
4336    return delete_expr
4337
4338
4339def condition(expression, dialect=None, **opts) -> Condition:
4340    """
4341    Initialize a logical condition expression.
4342
4343    Example:
4344        >>> condition("x=1").sql()
4345        'x = 1'
4346
4347        This is helpful for composing larger logical syntax trees:
4348        >>> where = condition("x=1")
4349        >>> where = where.and_("y=1")
4350        >>> Select().from_("tbl").select("*").where(where).sql()
4351        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4352
4353    Args:
4354        *expression (str | Expression): the SQL code string to parse.
4355            If an Expression instance is passed, this is used as-is.
4356        dialect (str): the dialect used to parse the input expression (in the case that the
4357            input expression is a SQL string).
4358        **opts: other options to use to parse the input expressions (again, in the case
4359            that the input expression is a SQL string).
4360
4361    Returns:
4362        Condition: the expression
4363    """
4364    return maybe_parse(  # type: ignore
4365        expression,
4366        into=Condition,
4367        dialect=dialect,
4368        **opts,
4369    )
4370
4371
4372def and_(*expressions, dialect=None, **opts) -> And:
4373    """
4374    Combine multiple conditions with an AND logical operator.
4375
4376    Example:
4377        >>> and_("x=1", and_("y=1", "z=1")).sql()
4378        'x = 1 AND (y = 1 AND z = 1)'
4379
4380    Args:
4381        *expressions (str | Expression): the SQL code strings to parse.
4382            If an Expression instance is passed, this is used as-is.
4383        dialect (str): the dialect used to parse the input expression.
4384        **opts: other options to use to parse the input expressions.
4385
4386    Returns:
4387        And: the new condition
4388    """
4389    return _combine(expressions, And, dialect, **opts)
4390
4391
4392def or_(*expressions, dialect=None, **opts) -> Or:
4393    """
4394    Combine multiple conditions with an OR logical operator.
4395
4396    Example:
4397        >>> or_("x=1", or_("y=1", "z=1")).sql()
4398        'x = 1 OR (y = 1 OR z = 1)'
4399
4400    Args:
4401        *expressions (str | Expression): the SQL code strings to parse.
4402            If an Expression instance is passed, this is used as-is.
4403        dialect (str): the dialect used to parse the input expression.
4404        **opts: other options to use to parse the input expressions.
4405
4406    Returns:
4407        Or: the new condition
4408    """
4409    return _combine(expressions, Or, dialect, **opts)
4410
4411
4412def not_(expression, dialect=None, **opts) -> Not:
4413    """
4414    Wrap a condition with a NOT operator.
4415
4416    Example:
4417        >>> not_("this_suit='black'").sql()
4418        "NOT this_suit = 'black'"
4419
4420    Args:
4421        expression (str | Expression): the SQL code strings to parse.
4422            If an Expression instance is passed, this is used as-is.
4423        dialect (str): the dialect used to parse the input expression.
4424        **opts: other options to use to parse the input expressions.
4425
4426    Returns:
4427        Not: the new condition
4428    """
4429    this = condition(
4430        expression,
4431        dialect=dialect,
4432        **opts,
4433    )
4434    return Not(this=_wrap_operator(this))
4435
4436
4437def paren(expression) -> Paren:
4438    return Paren(this=expression)
4439
4440
4441SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4442
4443
4444@t.overload
4445def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4446    ...
4447
4448
4449@t.overload
4450def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4451    ...
4452
4453
4454def to_identifier(name, quoted=None):
4455    """Builds an identifier.
4456
4457    Args:
4458        name: The name to turn into an identifier.
4459        quoted: Whether or not force quote the identifier.
4460
4461    Returns:
4462        The identifier ast node.
4463    """
4464
4465    if name is None:
4466        return None
4467
4468    if isinstance(name, Identifier):
4469        identifier = name
4470    elif isinstance(name, str):
4471        identifier = Identifier(
4472            this=name,
4473            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4474        )
4475    else:
4476        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4477    return identifier
4478
4479
4480INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4481
4482
4483def to_interval(interval: str | Literal) -> Interval:
4484    """Builds an interval expression from a string like '1 day' or '5 months'."""
4485    if isinstance(interval, Literal):
4486        if not interval.is_string:
4487            raise ValueError("Invalid interval string.")
4488
4489        interval = interval.this
4490
4491    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4492
4493    if not interval_parts:
4494        raise ValueError("Invalid interval string.")
4495
4496    return Interval(
4497        this=Literal.string(interval_parts.group(1)),
4498        unit=Var(this=interval_parts.group(2)),
4499    )
4500
4501
4502@t.overload
4503def to_table(sql_path: str | Table, **kwargs) -> Table:
4504    ...
4505
4506
4507@t.overload
4508def to_table(sql_path: None, **kwargs) -> None:
4509    ...
4510
4511
4512def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4513    """
4514    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4515    If a table is passed in then that table is returned.
4516
4517    Args:
4518        sql_path: a `[catalog].[schema].[table]` string.
4519
4520    Returns:
4521        A table expression.
4522    """
4523    if sql_path is None or isinstance(sql_path, Table):
4524        return sql_path
4525    if not isinstance(sql_path, str):
4526        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4527
4528    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4529    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4530
4531
4532def to_column(sql_path: str | Column, **kwargs) -> Column:
4533    """
4534    Create a column from a `[table].[column]` sql path. Schema is optional.
4535
4536    If a column is passed in then that column is returned.
4537
4538    Args:
4539        sql_path: `[table].[column]` string
4540    Returns:
4541        Table: A column expression
4542    """
4543    if sql_path is None or isinstance(sql_path, Column):
4544        return sql_path
4545    if not isinstance(sql_path, str):
4546        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4547    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4548    return Column(this=column_name, table=table_name, **kwargs)
4549
4550
4551def alias_(
4552    expression: ExpOrStr,
4553    alias: str | Identifier,
4554    table: bool | t.Sequence[str | Identifier] = False,
4555    quoted: t.Optional[bool] = None,
4556    dialect: DialectType = None,
4557    **opts,
4558):
4559    """Create an Alias expression.
4560
4561    Example:
4562        >>> alias_('foo', 'bar').sql()
4563        'foo AS bar'
4564
4565        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4566        '(SELECT 1, 2) AS bar(a, b)'
4567
4568    Args:
4569        expression: the SQL code strings to parse.
4570            If an Expression instance is passed, this is used as-is.
4571        alias: the alias name to use. If the name has
4572            special characters it is quoted.
4573        table: Whether or not to create a table alias, can also be a list of columns.
4574        quoted: whether or not to quote the alias
4575        dialect: the dialect used to parse the input expression.
4576        **opts: other options to use to parse the input expressions.
4577
4578    Returns:
4579        Alias: the aliased expression
4580    """
4581    exp = maybe_parse(expression, dialect=dialect, **opts)
4582    alias = to_identifier(alias, quoted=quoted)
4583
4584    if table:
4585        table_alias = TableAlias(this=alias)
4586        exp.set("alias", table_alias)
4587
4588        if not isinstance(table, bool):
4589            for column in table:
4590                table_alias.append("columns", to_identifier(column, quoted=quoted))
4591
4592        return exp
4593
4594    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4595    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4596    # for the complete Window expression.
4597    #
4598    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4599
4600    if "alias" in exp.arg_types and not isinstance(exp, Window):
4601        exp = exp.copy()
4602        exp.set("alias", alias)
4603        return exp
4604    return Alias(this=exp, alias=alias)
4605
4606
4607def subquery(expression, alias=None, dialect=None, **opts):
4608    """
4609    Build a subquery expression.
4610
4611    Example:
4612        >>> subquery('select x from tbl', 'bar').select('x').sql()
4613        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4614
4615    Args:
4616        expression (str | Expression): the SQL code strings to parse.
4617            If an Expression instance is passed, this is used as-is.
4618        alias (str | Expression): the alias name to use.
4619        dialect (str): the dialect used to parse the input expression.
4620        **opts: other options to use to parse the input expressions.
4621
4622    Returns:
4623        Select: a new select with the subquery expression included
4624    """
4625
4626    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4627    return Select().from_(expression, dialect=dialect, **opts)
4628
4629
4630def column(
4631    col: str | Identifier,
4632    table: t.Optional[str | Identifier] = None,
4633    schema: t.Optional[str | Identifier] = None,
4634    quoted: t.Optional[bool] = None,
4635) -> Column:
4636    """
4637    Build a Column.
4638
4639    Args:
4640        col: column name
4641        table: table name
4642        schema: schema name
4643        quoted: whether or not to force quote each part
4644    Returns:
4645        Column: column instance
4646    """
4647    return Column(
4648        this=to_identifier(col, quoted=quoted),
4649        table=to_identifier(table, quoted=quoted),
4650        schema=to_identifier(schema, quoted=quoted),
4651    )
4652
4653
4654def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
4655    """Cast an expression to a data type.
4656
4657    Example:
4658        >>> cast('x + 1', 'int').sql()
4659        'CAST(x + 1 AS INT)'
4660
4661    Args:
4662        expression: The expression to cast.
4663        to: The datatype to cast to.
4664
4665    Returns:
4666        A cast node.
4667    """
4668    expression = maybe_parse(expression, **opts)
4669    return Cast(this=expression, to=DataType.build(to, **opts))
4670
4671
4672def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4673    """Build a Table.
4674
4675    Args:
4676        table (str | Expression): column name
4677        db (str | Expression): db name
4678        catalog (str | Expression): catalog name
4679
4680    Returns:
4681        Table: table instance
4682    """
4683    return Table(
4684        this=to_identifier(table, quoted=quoted),
4685        db=to_identifier(db, quoted=quoted),
4686        catalog=to_identifier(catalog, quoted=quoted),
4687        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4688    )
4689
4690
4691def values(
4692    values: t.Iterable[t.Tuple[t.Any, ...]],
4693    alias: t.Optional[str] = None,
4694    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4695) -> Values:
4696    """Build VALUES statement.
4697
4698    Example:
4699        >>> values([(1, '2')]).sql()
4700        "VALUES (1, '2')"
4701
4702    Args:
4703        values: values statements that will be converted to SQL
4704        alias: optional alias
4705        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4706         If either are provided then an alias is also required.
4707         If a dictionary is provided then the first column of the values will be casted to the expected type
4708         in order to help with type inference.
4709
4710    Returns:
4711        Values: the Values expression object
4712    """
4713    if columns and not alias:
4714        raise ValueError("Alias is required when providing columns")
4715    table_alias = (
4716        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4717        if columns
4718        else TableAlias(this=to_identifier(alias) if alias else None)
4719    )
4720    expressions = [convert(tup) for tup in values]
4721    if columns and isinstance(columns, dict):
4722        types = list(columns.values())
4723        expressions[0].set(
4724            "expressions",
4725            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4726        )
4727    return Values(
4728        expressions=expressions,
4729        alias=table_alias,
4730    )
4731
4732
4733def var(name: t.Optional[ExpOrStr]) -> Var:
4734    """Build a SQL variable.
4735
4736    Example:
4737        >>> repr(var('x'))
4738        '(VAR this: x)'
4739
4740        >>> repr(var(column('x', table='y')))
4741        '(VAR this: x)'
4742
4743    Args:
4744        name: The name of the var or an expression who's name will become the var.
4745
4746    Returns:
4747        The new variable node.
4748    """
4749    if not name:
4750        raise ValueError("Cannot convert empty name into var.")
4751
4752    if isinstance(name, Expression):
4753        name = name.name
4754    return Var(this=name)
4755
4756
4757def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4758    """Build ALTER TABLE... RENAME... expression
4759
4760    Args:
4761        old_name: The old name of the table
4762        new_name: The new name of the table
4763
4764    Returns:
4765        Alter table expression
4766    """
4767    old_table = to_table(old_name)
4768    new_table = to_table(new_name)
4769    return AlterTable(
4770        this=old_table,
4771        actions=[
4772            RenameTable(this=new_table),
4773        ],
4774    )
4775
4776
4777def convert(value) -> Expression:
4778    """Convert a python value into an expression object.
4779
4780    Raises an error if a conversion is not possible.
4781
4782    Args:
4783        value (Any): a python object
4784
4785    Returns:
4786        Expression: the equivalent expression object
4787    """
4788    if isinstance(value, Expression):
4789        return value
4790    if value is None:
4791        return NULL
4792    if isinstance(value, bool):
4793        return Boolean(this=value)
4794    if isinstance(value, str):
4795        return Literal.string(value)
4796    if isinstance(value, float) and math.isnan(value):
4797        return NULL
4798    if isinstance(value, numbers.Number):
4799        return Literal.number(value)
4800    if isinstance(value, tuple):
4801        return Tuple(expressions=[convert(v) for v in value])
4802    if isinstance(value, list):
4803        return Array(expressions=[convert(v) for v in value])
4804    if isinstance(value, dict):
4805        return Map(
4806            keys=[convert(k) for k in value],
4807            values=[convert(v) for v in value.values()],
4808        )
4809    if isinstance(value, datetime.datetime):
4810        datetime_literal = Literal.string(
4811            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4812        )
4813        return TimeStrToTime(this=datetime_literal)
4814    if isinstance(value, datetime.date):
4815        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4816        return DateStrToDate(this=date_literal)
4817    raise ValueError(f"Cannot convert {value}")
4818
4819
4820def replace_children(expression, fun, *args, **kwargs):
4821    """
4822    Replace children of an expression with the result of a lambda fun(child) -> exp.
4823    """
4824    for k, v in expression.args.items():
4825        is_list_arg = isinstance(v, list)
4826
4827        child_nodes = v if is_list_arg else [v]
4828        new_child_nodes = []
4829
4830        for cn in child_nodes:
4831            if isinstance(cn, Expression):
4832                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
4833                    new_child_nodes.append(child_node)
4834                    child_node.parent = expression
4835                    child_node.arg_key = k
4836            else:
4837                new_child_nodes.append(cn)
4838
4839        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4840
4841
4842def column_table_names(expression):
4843    """
4844    Return all table names referenced through columns in an expression.
4845
4846    Example:
4847        >>> import sqlglot
4848        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4849        ['c', 'a']
4850
4851    Args:
4852        expression (sqlglot.Expression): expression to find table names
4853
4854    Returns:
4855        list: A list of unique names
4856    """
4857    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4858
4859
4860def table_name(table) -> str:
4861    """Get the full name of a table as a string.
4862
4863    Args:
4864        table (exp.Table | str): table expression node or string.
4865
4866    Examples:
4867        >>> from sqlglot import exp, parse_one
4868        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4869        'a.b.c'
4870
4871    Returns:
4872        The table name.
4873    """
4874
4875    table = maybe_parse(table, into=Table)
4876
4877    if not table:
4878        raise ValueError(f"Cannot parse {table}")
4879
4880    return ".".join(
4881        part
4882        for part in (
4883            table.text("catalog"),
4884            table.text("db"),
4885            table.name,
4886        )
4887        if part
4888    )
4889
4890
4891def replace_tables(expression, mapping):
4892    """Replace all tables in expression according to the mapping.
4893
4894    Args:
4895        expression (sqlglot.Expression): expression node to be transformed and replaced.
4896        mapping (Dict[str, str]): mapping of table names.
4897
4898    Examples:
4899        >>> from sqlglot import exp, parse_one
4900        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4901        'SELECT * FROM c'
4902
4903    Returns:
4904        The mapped expression.
4905    """
4906
4907    def _replace_tables(node):
4908        if isinstance(node, Table):
4909            new_name = mapping.get(table_name(node))
4910            if new_name:
4911                return to_table(
4912                    new_name,
4913                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4914                )
4915        return node
4916
4917    return expression.transform(_replace_tables)
4918
4919
4920def replace_placeholders(expression, *args, **kwargs):
4921    """Replace placeholders in an expression.
4922
4923    Args:
4924        expression (sqlglot.Expression): expression node to be transformed and replaced.
4925        args: positional names that will substitute unnamed placeholders in the given order.
4926        kwargs: keyword arguments that will substitute named placeholders.
4927
4928    Examples:
4929        >>> from sqlglot import exp, parse_one
4930        >>> replace_placeholders(
4931        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4932        ... ).sql()
4933        'SELECT * FROM foo WHERE a = b'
4934
4935    Returns:
4936        The mapped expression.
4937    """
4938
4939    def _replace_placeholders(node, args, **kwargs):
4940        if isinstance(node, Placeholder):
4941            if node.name:
4942                new_name = kwargs.get(node.name)
4943                if new_name:
4944                    return to_identifier(new_name)
4945            else:
4946                try:
4947                    return to_identifier(next(args))
4948                except StopIteration:
4949                    pass
4950        return node
4951
4952    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4953
4954
4955def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4956    """Transforms an expression by expanding all referenced sources into subqueries.
4957
4958    Examples:
4959        >>> from sqlglot import parse_one
4960        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
4961        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
4962
4963    Args:
4964        expression: The expression to expand.
4965        sources: A dictionary of name to Subqueryables.
4966        copy: Whether or not to copy the expression during transformation. Defaults to True.
4967
4968    Returns:
4969        The transformed expression.
4970    """
4971
4972    def _expand(node: Expression):
4973        if isinstance(node, Table):
4974            name = table_name(node)
4975            source = sources.get(name)
4976            if source:
4977                subquery = source.subquery(node.alias or name)
4978                subquery.comments = [f"source: {name}"]
4979                return subquery
4980        return node
4981
4982    return expression.transform(_expand, copy=copy)
4983
4984
4985def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
4986    """
4987    Returns a Func expression.
4988
4989    Examples:
4990        >>> func("abs", 5).sql()
4991        'ABS(5)'
4992
4993        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
4994        'CAST(5 AS DOUBLE)'
4995
4996    Args:
4997        name: the name of the function to build.
4998        args: the args used to instantiate the function of interest.
4999        dialect: the source dialect.
5000        kwargs: the kwargs used to instantiate the function of interest.
5001
5002    Note:
5003        The arguments `args` and `kwargs` are mutually exclusive.
5004
5005    Returns:
5006        An instance of the function of interest, or an anonymous function, if `name` doesn't
5007        correspond to an existing `sqlglot.expressions.Func` class.
5008    """
5009    if args and kwargs:
5010        raise ValueError("Can't use both args and kwargs to instantiate a function.")
5011
5012    from sqlglot.dialects.dialect import Dialect
5013
5014    converted = [convert(arg) for arg in args]
5015    kwargs = {key: convert(value) for key, value in kwargs.items()}
5016
5017    parser = Dialect.get_or_raise(dialect)().parser()
5018    from_args_list = parser.FUNCTIONS.get(name.upper())
5019
5020    if from_args_list:
5021        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
5022    else:
5023        kwargs = kwargs or {"expressions": converted}
5024        function = Anonymous(this=name, **kwargs)
5025
5026    for error_message in function.error_messages(converted):
5027        raise ValueError(error_message)
5028
5029    return function
5030
5031
5032def true():
5033    """
5034    Returns a true Boolean expression.
5035    """
5036    return Boolean(this=True)
5037
5038
5039def false():
5040    """
5041    Returns a false Boolean expression.
5042    """
5043    return Boolean(this=False)
5044
5045
5046def null():
5047    """
5048    Returns a Null expression.
5049    """
5050    return Null()
5051
5052
5053# TODO: deprecate this
5054TRUE = Boolean(this=True)
5055FALSE = Boolean(this=False)
5056NULL = Null()