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