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