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}
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        "overwrite": False,
1136        "exists": False,
1137        "partition": False,
1138        "alternative": False,
1139    }
1140
1141
1142# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1143class Introducer(Expression):
1144    arg_types = {"this": True, "expression": True}
1145
1146
1147# national char, like n'utf8'
1148class National(Expression):
1149    pass
1150
1151
1152class LoadData(Expression):
1153    arg_types = {
1154        "this": True,
1155        "local": False,
1156        "overwrite": False,
1157        "inpath": True,
1158        "partition": False,
1159        "input_format": False,
1160        "serde": False,
1161    }
1162
1163
1164class Partition(Expression):
1165    arg_types = {"expressions": True}
1166
1167
1168class Fetch(Expression):
1169    arg_types = {"direction": False, "count": False}
1170
1171
1172class Group(Expression):
1173    arg_types = {
1174        "expressions": False,
1175        "grouping_sets": False,
1176        "cube": False,
1177        "rollup": False,
1178    }
1179
1180
1181class Lambda(Expression):
1182    arg_types = {"this": True, "expressions": True}
1183
1184
1185class Limit(Expression):
1186    arg_types = {"this": False, "expression": True}
1187
1188
1189class Literal(Condition):
1190    arg_types = {"this": True, "is_string": True}
1191
1192    def __eq__(self, other):
1193        return (
1194            isinstance(other, Literal)
1195            and self.this == other.this
1196            and self.args["is_string"] == other.args["is_string"]
1197        )
1198
1199    def __hash__(self):
1200        return hash((self.key, self.this, self.args["is_string"]))
1201
1202    @classmethod
1203    def number(cls, number) -> Literal:
1204        return cls(this=str(number), is_string=False)
1205
1206    @classmethod
1207    def string(cls, string) -> Literal:
1208        return cls(this=str(string), is_string=True)
1209
1210    @property
1211    def output_name(self):
1212        return self.name
1213
1214
1215class Join(Expression):
1216    arg_types = {
1217        "this": True,
1218        "on": False,
1219        "side": False,
1220        "kind": False,
1221        "using": False,
1222        "natural": False,
1223    }
1224
1225    @property
1226    def kind(self):
1227        return self.text("kind").upper()
1228
1229    @property
1230    def side(self):
1231        return self.text("side").upper()
1232
1233    @property
1234    def alias_or_name(self):
1235        return self.this.alias_or_name
1236
1237    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1238        """
1239        Append to or set the ON expressions.
1240
1241        Example:
1242            >>> import sqlglot
1243            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1244            'JOIN x ON y = 1'
1245
1246        Args:
1247            *expressions (str | Expression): the SQL code strings to parse.
1248                If an `Expression` instance is passed, it will be used as-is.
1249                Multiple expressions are combined with an AND operator.
1250            append (bool): if `True`, AND the new expressions to any existing expression.
1251                Otherwise, this resets the expression.
1252            dialect (str): the dialect used to parse the input expressions.
1253            copy (bool): if `False`, modify this expression instance in-place.
1254            opts (kwargs): other options to use to parse the input expressions.
1255
1256        Returns:
1257            Join: the modified join expression.
1258        """
1259        join = _apply_conjunction_builder(
1260            *expressions,
1261            instance=self,
1262            arg="on",
1263            append=append,
1264            dialect=dialect,
1265            copy=copy,
1266            **opts,
1267        )
1268
1269        if join.kind == "CROSS":
1270            join.set("kind", None)
1271
1272        return join
1273
1274    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1275        """
1276        Append to or set the USING expressions.
1277
1278        Example:
1279            >>> import sqlglot
1280            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1281            'JOIN x USING (foo, bla)'
1282
1283        Args:
1284            *expressions (str | Expression): the SQL code strings to parse.
1285                If an `Expression` instance is passed, it will be used as-is.
1286            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1287                Otherwise, this resets the expression.
1288            dialect (str): the dialect used to parse the input expressions.
1289            copy (bool): if `False`, modify this expression instance in-place.
1290            opts (kwargs): other options to use to parse the input expressions.
1291
1292        Returns:
1293            Join: the modified join expression.
1294        """
1295        join = _apply_list_builder(
1296            *expressions,
1297            instance=self,
1298            arg="using",
1299            append=append,
1300            dialect=dialect,
1301            copy=copy,
1302            **opts,
1303        )
1304
1305        if join.kind == "CROSS":
1306            join.set("kind", None)
1307
1308        return join
1309
1310
1311class Lateral(UDTF):
1312    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1313
1314
1315class MatchRecognize(Expression):
1316    arg_types = {
1317        "partition_by": False,
1318        "order": False,
1319        "measures": False,
1320        "rows": False,
1321        "after": False,
1322        "pattern": False,
1323        "define": False,
1324    }
1325
1326
1327# Clickhouse FROM FINAL modifier
1328# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1329class Final(Expression):
1330    pass
1331
1332
1333class Offset(Expression):
1334    arg_types = {"this": False, "expression": True}
1335
1336
1337class Order(Expression):
1338    arg_types = {"this": False, "expressions": True}
1339
1340
1341# hive specific sorts
1342# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1343class Cluster(Order):
1344    pass
1345
1346
1347class Distribute(Order):
1348    pass
1349
1350
1351class Sort(Order):
1352    pass
1353
1354
1355class Ordered(Expression):
1356    arg_types = {"this": True, "desc": True, "nulls_first": True}
1357
1358
1359class Property(Expression):
1360    arg_types = {"this": True, "value": True}
1361
1362
1363class AfterJournalProperty(Property):
1364    arg_types = {"no": True, "dual": False, "local": False}
1365
1366
1367class AlgorithmProperty(Property):
1368    arg_types = {"this": True}
1369
1370
1371class AutoIncrementProperty(Property):
1372    arg_types = {"this": True}
1373
1374
1375class BlockCompressionProperty(Property):
1376    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1377
1378
1379class CharacterSetProperty(Property):
1380    arg_types = {"this": True, "default": True}
1381
1382
1383class ChecksumProperty(Property):
1384    arg_types = {"on": False, "default": False}
1385
1386
1387class CollateProperty(Property):
1388    arg_types = {"this": True}
1389
1390
1391class DataBlocksizeProperty(Property):
1392    arg_types = {"size": False, "units": False, "min": False, "default": False}
1393
1394
1395class DefinerProperty(Property):
1396    arg_types = {"this": True}
1397
1398
1399class DistKeyProperty(Property):
1400    arg_types = {"this": True}
1401
1402
1403class DistStyleProperty(Property):
1404    arg_types = {"this": True}
1405
1406
1407class EngineProperty(Property):
1408    arg_types = {"this": True}
1409
1410
1411class ExecuteAsProperty(Property):
1412    arg_types = {"this": True}
1413
1414
1415class ExternalProperty(Property):
1416    arg_types = {"this": False}
1417
1418
1419class FallbackProperty(Property):
1420    arg_types = {"no": True, "protection": False}
1421
1422
1423class FileFormatProperty(Property):
1424    arg_types = {"this": True}
1425
1426
1427class FreespaceProperty(Property):
1428    arg_types = {"this": True, "percent": False}
1429
1430
1431class IsolatedLoadingProperty(Property):
1432    arg_types = {
1433        "no": True,
1434        "concurrent": True,
1435        "for_all": True,
1436        "for_insert": True,
1437        "for_none": True,
1438    }
1439
1440
1441class JournalProperty(Property):
1442    arg_types = {"no": True, "dual": False, "before": False}
1443
1444
1445class LanguageProperty(Property):
1446    arg_types = {"this": True}
1447
1448
1449class LikeProperty(Property):
1450    arg_types = {"this": True, "expressions": False}
1451
1452
1453class LocationProperty(Property):
1454    arg_types = {"this": True}
1455
1456
1457class LockingProperty(Property):
1458    arg_types = {
1459        "this": False,
1460        "kind": True,
1461        "for_or_in": True,
1462        "lock_type": True,
1463        "override": False,
1464    }
1465
1466
1467class LogProperty(Property):
1468    arg_types = {"no": True}
1469
1470
1471class MaterializedProperty(Property):
1472    arg_types = {"this": False}
1473
1474
1475class MergeBlockRatioProperty(Property):
1476    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1477
1478
1479class NoPrimaryIndexProperty(Property):
1480    arg_types = {"this": False}
1481
1482
1483class OnCommitProperty(Property):
1484    arg_type = {"this": False}
1485
1486
1487class PartitionedByProperty(Property):
1488    arg_types = {"this": True}
1489
1490
1491class ReturnsProperty(Property):
1492    arg_types = {"this": True, "is_table": False, "table": False}
1493
1494
1495class RowFormatDelimitedProperty(Property):
1496    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1497    arg_types = {
1498        "fields": False,
1499        "escaped": False,
1500        "collection_items": False,
1501        "map_keys": False,
1502        "lines": False,
1503        "null": False,
1504        "serde": False,
1505    }
1506
1507
1508class RowFormatSerdeProperty(Property):
1509    arg_types = {"this": True}
1510
1511
1512class SchemaCommentProperty(Property):
1513    arg_types = {"this": True}
1514
1515
1516class SerdeProperties(Property):
1517    arg_types = {"expressions": True}
1518
1519
1520class SetProperty(Property):
1521    arg_types = {"multi": True}
1522
1523
1524class SortKeyProperty(Property):
1525    arg_types = {"this": True, "compound": False}
1526
1527
1528class SqlSecurityProperty(Property):
1529    arg_types = {"definer": True}
1530
1531
1532class TableFormatProperty(Property):
1533    arg_types = {"this": True}
1534
1535
1536class TemporaryProperty(Property):
1537    arg_types = {"global_": True}
1538
1539
1540class TransientProperty(Property):
1541    arg_types = {"this": False}
1542
1543
1544class VolatilityProperty(Property):
1545    arg_types = {"this": True}
1546
1547
1548class WithDataProperty(Property):
1549    arg_types = {"no": True, "statistics": False}
1550
1551
1552class WithJournalTableProperty(Property):
1553    arg_types = {"this": True}
1554
1555
1556class Properties(Expression):
1557    arg_types = {"expressions": True}
1558
1559    NAME_TO_PROPERTY = {
1560        "ALGORITHM": AlgorithmProperty,
1561        "AUTO_INCREMENT": AutoIncrementProperty,
1562        "CHARACTER SET": CharacterSetProperty,
1563        "COLLATE": CollateProperty,
1564        "COMMENT": SchemaCommentProperty,
1565        "DEFINER": DefinerProperty,
1566        "DISTKEY": DistKeyProperty,
1567        "DISTSTYLE": DistStyleProperty,
1568        "ENGINE": EngineProperty,
1569        "EXECUTE AS": ExecuteAsProperty,
1570        "FORMAT": FileFormatProperty,
1571        "LANGUAGE": LanguageProperty,
1572        "LOCATION": LocationProperty,
1573        "PARTITIONED_BY": PartitionedByProperty,
1574        "RETURNS": ReturnsProperty,
1575        "SORTKEY": SortKeyProperty,
1576        "TABLE_FORMAT": TableFormatProperty,
1577    }
1578
1579    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1580
1581    # CREATE property locations
1582    # Form: schema specified
1583    #   create [POST_CREATE]
1584    #     table a [POST_NAME]
1585    #     (b int) [POST_SCHEMA]
1586    #     with ([POST_WITH])
1587    #     index (b) [POST_INDEX]
1588    #
1589    # Form: alias selection
1590    #   create [POST_CREATE]
1591    #     table a [POST_NAME]
1592    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1593    #     index (c) [POST_INDEX]
1594    class Location(AutoName):
1595        POST_CREATE = auto()
1596        POST_NAME = auto()
1597        POST_SCHEMA = auto()
1598        POST_WITH = auto()
1599        POST_ALIAS = auto()
1600        POST_EXPRESSION = auto()
1601        POST_INDEX = auto()
1602        UNSUPPORTED = auto()
1603
1604    @classmethod
1605    def from_dict(cls, properties_dict) -> Properties:
1606        expressions = []
1607        for key, value in properties_dict.items():
1608            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1609            if property_cls:
1610                expressions.append(property_cls(this=convert(value)))
1611            else:
1612                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1613
1614        return cls(expressions=expressions)
1615
1616
1617class Qualify(Expression):
1618    pass
1619
1620
1621# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1622class Return(Expression):
1623    pass
1624
1625
1626class Reference(Expression):
1627    arg_types = {"this": True, "expressions": False, "options": False}
1628
1629
1630class Tuple(Expression):
1631    arg_types = {"expressions": False}
1632
1633
1634class Subqueryable(Unionable):
1635    def subquery(self, alias=None, copy=True) -> Subquery:
1636        """
1637        Convert this expression to an aliased expression that can be used as a Subquery.
1638
1639        Example:
1640            >>> subquery = Select().select("x").from_("tbl").subquery()
1641            >>> Select().select("x").from_(subquery).sql()
1642            'SELECT x FROM (SELECT x FROM tbl)'
1643
1644        Args:
1645            alias (str | Identifier): an optional alias for the subquery
1646            copy (bool): if `False`, modify this expression instance in-place.
1647
1648        Returns:
1649            Alias: the subquery
1650        """
1651        instance = _maybe_copy(self, copy)
1652        return Subquery(
1653            this=instance,
1654            alias=TableAlias(this=to_identifier(alias)),
1655        )
1656
1657    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1658        raise NotImplementedError
1659
1660    @property
1661    def ctes(self):
1662        with_ = self.args.get("with")
1663        if not with_:
1664            return []
1665        return with_.expressions
1666
1667    @property
1668    def selects(self):
1669        raise NotImplementedError("Subqueryable objects must implement `selects`")
1670
1671    @property
1672    def named_selects(self):
1673        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1674
1675    def with_(
1676        self,
1677        alias,
1678        as_,
1679        recursive=None,
1680        append=True,
1681        dialect=None,
1682        copy=True,
1683        **opts,
1684    ):
1685        """
1686        Append to or set the common table expressions.
1687
1688        Example:
1689            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1690            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1691
1692        Args:
1693            alias (str | Expression): the SQL code string to parse as the table name.
1694                If an `Expression` instance is passed, this is used as-is.
1695            as_ (str | Expression): the SQL code string to parse as the table expression.
1696                If an `Expression` instance is passed, it will be used as-is.
1697            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1698            append (bool): if `True`, add to any existing expressions.
1699                Otherwise, this resets the expressions.
1700            dialect (str): the dialect used to parse the input expression.
1701            copy (bool): if `False`, modify this expression instance in-place.
1702            opts (kwargs): other options to use to parse the input expressions.
1703
1704        Returns:
1705            Select: the modified expression.
1706        """
1707        alias_expression = maybe_parse(
1708            alias,
1709            dialect=dialect,
1710            into=TableAlias,
1711            **opts,
1712        )
1713        as_expression = maybe_parse(
1714            as_,
1715            dialect=dialect,
1716            **opts,
1717        )
1718        cte = CTE(
1719            this=as_expression,
1720            alias=alias_expression,
1721        )
1722        return _apply_child_list_builder(
1723            cte,
1724            instance=self,
1725            arg="with",
1726            append=append,
1727            copy=copy,
1728            into=With,
1729            properties={"recursive": recursive or False},
1730        )
1731
1732
1733QUERY_MODIFIERS = {
1734    "match": False,
1735    "laterals": False,
1736    "joins": False,
1737    "pivots": False,
1738    "where": False,
1739    "group": False,
1740    "having": False,
1741    "qualify": False,
1742    "windows": False,
1743    "distribute": False,
1744    "sort": False,
1745    "cluster": False,
1746    "order": False,
1747    "limit": False,
1748    "offset": False,
1749    "lock": False,
1750}
1751
1752
1753class Table(Expression):
1754    arg_types = {
1755        "this": True,
1756        "alias": False,
1757        "db": False,
1758        "catalog": False,
1759        "laterals": False,
1760        "joins": False,
1761        "pivots": False,
1762        "hints": False,
1763        "system_time": False,
1764    }
1765
1766    @property
1767    def db(self) -> str:
1768        return self.text("db")
1769
1770    @property
1771    def catalog(self) -> str:
1772        return self.text("catalog")
1773
1774
1775# See the TSQL "Querying data in a system-versioned temporal table" page
1776class SystemTime(Expression):
1777    arg_types = {
1778        "this": False,
1779        "expression": False,
1780        "kind": True,
1781    }
1782
1783
1784class Union(Subqueryable):
1785    arg_types = {
1786        "with": False,
1787        "this": True,
1788        "expression": True,
1789        "distinct": False,
1790        **QUERY_MODIFIERS,
1791    }
1792
1793    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1794        """
1795        Set the LIMIT expression.
1796
1797        Example:
1798            >>> select("1").union(select("1")).limit(1).sql()
1799            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1800
1801        Args:
1802            expression (str | int | Expression): the SQL code string to parse.
1803                This can also be an integer.
1804                If a `Limit` instance is passed, this is used as-is.
1805                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1806            dialect (str): the dialect used to parse the input expression.
1807            copy (bool): if `False`, modify this expression instance in-place.
1808            opts (kwargs): other options to use to parse the input expressions.
1809
1810        Returns:
1811            Select: The limited subqueryable.
1812        """
1813        return (
1814            select("*")
1815            .from_(self.subquery(alias="_l_0", copy=copy))
1816            .limit(expression, dialect=dialect, copy=False, **opts)
1817        )
1818
1819    def select(
1820        self,
1821        *expressions: str | Expression,
1822        append: bool = True,
1823        dialect: DialectType = None,
1824        copy: bool = True,
1825        **opts,
1826    ) -> Union:
1827        """Append to or set the SELECT of the union recursively.
1828
1829        Example:
1830            >>> from sqlglot import parse_one
1831            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1832            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1833
1834        Args:
1835            *expressions: the SQL code strings to parse.
1836                If an `Expression` instance is passed, it will be used as-is.
1837            append: if `True`, add to any existing expressions.
1838                Otherwise, this resets the expressions.
1839            dialect: the dialect used to parse the input expressions.
1840            copy: if `False`, modify this expression instance in-place.
1841            opts: other options to use to parse the input expressions.
1842
1843        Returns:
1844            Union: the modified expression.
1845        """
1846        this = self.copy() if copy else self
1847        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1848        this.expression.unnest().select(
1849            *expressions, append=append, dialect=dialect, copy=False, **opts
1850        )
1851        return this
1852
1853    @property
1854    def named_selects(self):
1855        return self.this.unnest().named_selects
1856
1857    @property
1858    def is_star(self) -> bool:
1859        return self.this.is_star or self.expression.is_star
1860
1861    @property
1862    def selects(self):
1863        return self.this.unnest().selects
1864
1865    @property
1866    def left(self):
1867        return self.this
1868
1869    @property
1870    def right(self):
1871        return self.expression
1872
1873
1874class Except(Union):
1875    pass
1876
1877
1878class Intersect(Union):
1879    pass
1880
1881
1882class Unnest(UDTF):
1883    arg_types = {
1884        "expressions": True,
1885        "ordinality": False,
1886        "alias": False,
1887        "offset": False,
1888    }
1889
1890
1891class Update(Expression):
1892    arg_types = {
1893        "with": False,
1894        "this": False,
1895        "expressions": True,
1896        "from": False,
1897        "where": False,
1898    }
1899
1900
1901class Values(UDTF):
1902    arg_types = {
1903        "expressions": True,
1904        "ordinality": False,
1905        "alias": False,
1906    }
1907
1908
1909class Var(Expression):
1910    pass
1911
1912
1913class Schema(Expression):
1914    arg_types = {"this": False, "expressions": False}
1915
1916
1917# Used to represent the FOR UPDATE and FOR SHARE locking read types.
1918# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
1919class Lock(Expression):
1920    arg_types = {"update": True}
1921
1922
1923class Select(Subqueryable):
1924    arg_types = {
1925        "with": False,
1926        "expressions": False,
1927        "hint": False,
1928        "distinct": False,
1929        "into": False,
1930        "from": False,
1931        **QUERY_MODIFIERS,
1932    }
1933
1934    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1935        """
1936        Set the FROM expression.
1937
1938        Example:
1939            >>> Select().from_("tbl").select("x").sql()
1940            'SELECT x FROM tbl'
1941
1942        Args:
1943            *expressions (str | Expression): the SQL code strings to parse.
1944                If a `From` instance is passed, this is used as-is.
1945                If another `Expression` instance is passed, it will be wrapped in a `From`.
1946            append (bool): if `True`, add to any existing expressions.
1947                Otherwise, this flattens all the `From` expression into a single expression.
1948            dialect (str): the dialect used to parse the input expression.
1949            copy (bool): if `False`, modify this expression instance in-place.
1950            opts (kwargs): other options to use to parse the input expressions.
1951
1952        Returns:
1953            Select: the modified expression.
1954        """
1955        return _apply_child_list_builder(
1956            *expressions,
1957            instance=self,
1958            arg="from",
1959            append=append,
1960            copy=copy,
1961            prefix="FROM",
1962            into=From,
1963            dialect=dialect,
1964            **opts,
1965        )
1966
1967    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1968        """
1969        Set the GROUP BY expression.
1970
1971        Example:
1972            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1973            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1974
1975        Args:
1976            *expressions (str | Expression): the SQL code strings to parse.
1977                If a `Group` instance is passed, this is used as-is.
1978                If another `Expression` instance is passed, it will be wrapped in a `Group`.
1979                If nothing is passed in then a group by is not applied to the expression
1980            append (bool): if `True`, add to any existing expressions.
1981                Otherwise, this flattens all the `Group` expression into a single expression.
1982            dialect (str): the dialect used to parse the input expression.
1983            copy (bool): if `False`, modify this expression instance in-place.
1984            opts (kwargs): other options to use to parse the input expressions.
1985
1986        Returns:
1987            Select: the modified expression.
1988        """
1989        if not expressions:
1990            return self if not copy else self.copy()
1991        return _apply_child_list_builder(
1992            *expressions,
1993            instance=self,
1994            arg="group",
1995            append=append,
1996            copy=copy,
1997            prefix="GROUP BY",
1998            into=Group,
1999            dialect=dialect,
2000            **opts,
2001        )
2002
2003    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2004        """
2005        Set the ORDER BY expression.
2006
2007        Example:
2008            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2009            'SELECT x FROM tbl ORDER BY x DESC'
2010
2011        Args:
2012            *expressions (str | Expression): the SQL code strings to parse.
2013                If a `Group` instance is passed, this is used as-is.
2014                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2015            append (bool): if `True`, add to any existing expressions.
2016                Otherwise, this flattens all the `Order` expression into a single expression.
2017            dialect (str): the dialect used to parse the input expression.
2018            copy (bool): if `False`, modify this expression instance in-place.
2019            opts (kwargs): other options to use to parse the input expressions.
2020
2021        Returns:
2022            Select: the modified expression.
2023        """
2024        return _apply_child_list_builder(
2025            *expressions,
2026            instance=self,
2027            arg="order",
2028            append=append,
2029            copy=copy,
2030            prefix="ORDER BY",
2031            into=Order,
2032            dialect=dialect,
2033            **opts,
2034        )
2035
2036    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2037        """
2038        Set the SORT BY expression.
2039
2040        Example:
2041            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2042            'SELECT x FROM tbl SORT BY x DESC'
2043
2044        Args:
2045            *expressions (str | Expression): the SQL code strings to parse.
2046                If a `Group` instance is passed, this is used as-is.
2047                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2048            append (bool): if `True`, add to any existing expressions.
2049                Otherwise, this flattens all the `Order` expression into a single expression.
2050            dialect (str): the dialect used to parse the input expression.
2051            copy (bool): if `False`, modify this expression instance in-place.
2052            opts (kwargs): other options to use to parse the input expressions.
2053
2054        Returns:
2055            Select: the modified expression.
2056        """
2057        return _apply_child_list_builder(
2058            *expressions,
2059            instance=self,
2060            arg="sort",
2061            append=append,
2062            copy=copy,
2063            prefix="SORT BY",
2064            into=Sort,
2065            dialect=dialect,
2066            **opts,
2067        )
2068
2069    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2070        """
2071        Set the CLUSTER BY expression.
2072
2073        Example:
2074            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2075            'SELECT x FROM tbl CLUSTER BY x DESC'
2076
2077        Args:
2078            *expressions (str | Expression): the SQL code strings to parse.
2079                If a `Group` instance is passed, this is used as-is.
2080                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2081            append (bool): if `True`, add to any existing expressions.
2082                Otherwise, this flattens all the `Order` expression into a single expression.
2083            dialect (str): the dialect used to parse the input expression.
2084            copy (bool): if `False`, modify this expression instance in-place.
2085            opts (kwargs): other options to use to parse the input expressions.
2086
2087        Returns:
2088            Select: the modified expression.
2089        """
2090        return _apply_child_list_builder(
2091            *expressions,
2092            instance=self,
2093            arg="cluster",
2094            append=append,
2095            copy=copy,
2096            prefix="CLUSTER BY",
2097            into=Cluster,
2098            dialect=dialect,
2099            **opts,
2100        )
2101
2102    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2103        """
2104        Set the LIMIT expression.
2105
2106        Example:
2107            >>> Select().from_("tbl").select("x").limit(10).sql()
2108            'SELECT x FROM tbl LIMIT 10'
2109
2110        Args:
2111            expression (str | int | Expression): the SQL code string to parse.
2112                This can also be an integer.
2113                If a `Limit` instance is passed, this is used as-is.
2114                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2115            dialect (str): the dialect used to parse the input expression.
2116            copy (bool): if `False`, modify this expression instance in-place.
2117            opts (kwargs): other options to use to parse the input expressions.
2118
2119        Returns:
2120            Select: the modified expression.
2121        """
2122        return _apply_builder(
2123            expression=expression,
2124            instance=self,
2125            arg="limit",
2126            into=Limit,
2127            prefix="LIMIT",
2128            dialect=dialect,
2129            copy=copy,
2130            **opts,
2131        )
2132
2133    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2134        """
2135        Set the OFFSET expression.
2136
2137        Example:
2138            >>> Select().from_("tbl").select("x").offset(10).sql()
2139            'SELECT x FROM tbl OFFSET 10'
2140
2141        Args:
2142            expression (str | int | Expression): the SQL code string to parse.
2143                This can also be an integer.
2144                If a `Offset` instance is passed, this is used as-is.
2145                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2146            dialect (str): the dialect used to parse the input expression.
2147            copy (bool): if `False`, modify this expression instance in-place.
2148            opts (kwargs): other options to use to parse the input expressions.
2149
2150        Returns:
2151            Select: the modified expression.
2152        """
2153        return _apply_builder(
2154            expression=expression,
2155            instance=self,
2156            arg="offset",
2157            into=Offset,
2158            prefix="OFFSET",
2159            dialect=dialect,
2160            copy=copy,
2161            **opts,
2162        )
2163
2164    def select(
2165        self,
2166        *expressions: str | Expression,
2167        append: bool = True,
2168        dialect: DialectType = None,
2169        copy: bool = True,
2170        **opts,
2171    ) -> Select:
2172        """
2173        Append to or set the SELECT expressions.
2174
2175        Example:
2176            >>> Select().select("x", "y").sql()
2177            'SELECT x, y'
2178
2179        Args:
2180            *expressions: the SQL code strings to parse.
2181                If an `Expression` instance is passed, it will be used as-is.
2182            append: if `True`, add to any existing expressions.
2183                Otherwise, this resets the expressions.
2184            dialect: the dialect used to parse the input expressions.
2185            copy: if `False`, modify this expression instance in-place.
2186            opts: other options to use to parse the input expressions.
2187
2188        Returns:
2189            Select: the modified expression.
2190        """
2191        return _apply_list_builder(
2192            *expressions,
2193            instance=self,
2194            arg="expressions",
2195            append=append,
2196            dialect=dialect,
2197            copy=copy,
2198            **opts,
2199        )
2200
2201    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2202        """
2203        Append to or set the LATERAL expressions.
2204
2205        Example:
2206            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2207            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2208
2209        Args:
2210            *expressions (str | Expression): the SQL code strings to parse.
2211                If an `Expression` instance is passed, it will be used as-is.
2212            append (bool): if `True`, add to any existing expressions.
2213                Otherwise, this resets the expressions.
2214            dialect (str): the dialect used to parse the input expressions.
2215            copy (bool): if `False`, modify this expression instance in-place.
2216            opts (kwargs): other options to use to parse the input expressions.
2217
2218        Returns:
2219            Select: the modified expression.
2220        """
2221        return _apply_list_builder(
2222            *expressions,
2223            instance=self,
2224            arg="laterals",
2225            append=append,
2226            into=Lateral,
2227            prefix="LATERAL VIEW",
2228            dialect=dialect,
2229            copy=copy,
2230            **opts,
2231        )
2232
2233    def join(
2234        self,
2235        expression,
2236        on=None,
2237        using=None,
2238        append=True,
2239        join_type=None,
2240        join_alias=None,
2241        dialect=None,
2242        copy=True,
2243        **opts,
2244    ) -> Select:
2245        """
2246        Append to or set the JOIN expressions.
2247
2248        Example:
2249            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2250            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2251
2252            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2253            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2254
2255            Use `join_type` to change the type of join:
2256
2257            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2258            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2259
2260        Args:
2261            expression (str | Expression): the SQL code string to parse.
2262                If an `Expression` instance is passed, it will be used as-is.
2263            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2264                If an `Expression` instance is passed, it will be used as-is.
2265            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2266                If an `Expression` instance is passed, it will be used as-is.
2267            append (bool): if `True`, add to any existing expressions.
2268                Otherwise, this resets the expressions.
2269            join_type (str): If set, alter the parsed join type
2270            dialect (str): the dialect used to parse the input expressions.
2271            copy (bool): if `False`, modify this expression instance in-place.
2272            opts (kwargs): other options to use to parse the input expressions.
2273
2274        Returns:
2275            Select: the modified expression.
2276        """
2277        parse_args = {"dialect": dialect, **opts}
2278
2279        try:
2280            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2281        except ParseError:
2282            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2283
2284        join = expression if isinstance(expression, Join) else Join(this=expression)
2285
2286        if isinstance(join.this, Select):
2287            join.this.replace(join.this.subquery())
2288
2289        if join_type:
2290            natural: t.Optional[Token]
2291            side: t.Optional[Token]
2292            kind: t.Optional[Token]
2293
2294            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2295
2296            if natural:
2297                join.set("natural", True)
2298            if side:
2299                join.set("side", side.text)
2300            if kind:
2301                join.set("kind", kind.text)
2302
2303        if on:
2304            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2305            join.set("on", on)
2306
2307        if using:
2308            join = _apply_list_builder(
2309                *ensure_collection(using),
2310                instance=join,
2311                arg="using",
2312                append=append,
2313                copy=copy,
2314                **opts,
2315            )
2316
2317        if join_alias:
2318            join.set("this", alias_(join.this, join_alias, table=True))
2319        return _apply_list_builder(
2320            join,
2321            instance=self,
2322            arg="joins",
2323            append=append,
2324            copy=copy,
2325            **opts,
2326        )
2327
2328    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2329        """
2330        Append to or set the WHERE expressions.
2331
2332        Example:
2333            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2334            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2335
2336        Args:
2337            *expressions (str | Expression): the SQL code strings to parse.
2338                If an `Expression` instance is passed, it will be used as-is.
2339                Multiple expressions are combined with an AND operator.
2340            append (bool): if `True`, AND the new expressions to any existing expression.
2341                Otherwise, this resets the expression.
2342            dialect (str): the dialect used to parse the input expressions.
2343            copy (bool): if `False`, modify this expression instance in-place.
2344            opts (kwargs): other options to use to parse the input expressions.
2345
2346        Returns:
2347            Select: the modified expression.
2348        """
2349        return _apply_conjunction_builder(
2350            *expressions,
2351            instance=self,
2352            arg="where",
2353            append=append,
2354            into=Where,
2355            dialect=dialect,
2356            copy=copy,
2357            **opts,
2358        )
2359
2360    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2361        """
2362        Append to or set the HAVING expressions.
2363
2364        Example:
2365            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2366            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2367
2368        Args:
2369            *expressions (str | Expression): the SQL code strings to parse.
2370                If an `Expression` instance is passed, it will be used as-is.
2371                Multiple expressions are combined with an AND operator.
2372            append (bool): if `True`, AND the new expressions to any existing expression.
2373                Otherwise, this resets the expression.
2374            dialect (str): the dialect used to parse the input expressions.
2375            copy (bool): if `False`, modify this expression instance in-place.
2376            opts (kwargs): other options to use to parse the input expressions.
2377
2378        Returns:
2379            Select: the modified expression.
2380        """
2381        return _apply_conjunction_builder(
2382            *expressions,
2383            instance=self,
2384            arg="having",
2385            append=append,
2386            into=Having,
2387            dialect=dialect,
2388            copy=copy,
2389            **opts,
2390        )
2391
2392    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2393        return _apply_list_builder(
2394            *expressions,
2395            instance=self,
2396            arg="windows",
2397            append=append,
2398            into=Window,
2399            dialect=dialect,
2400            copy=copy,
2401            **opts,
2402        )
2403
2404    def distinct(self, distinct=True, copy=True) -> Select:
2405        """
2406        Set the OFFSET expression.
2407
2408        Example:
2409            >>> Select().from_("tbl").select("x").distinct().sql()
2410            'SELECT DISTINCT x FROM tbl'
2411
2412        Args:
2413            distinct (bool): whether the Select should be distinct
2414            copy (bool): if `False`, modify this expression instance in-place.
2415
2416        Returns:
2417            Select: the modified expression.
2418        """
2419        instance = _maybe_copy(self, copy)
2420        instance.set("distinct", Distinct() if distinct else None)
2421        return instance
2422
2423    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2424        """
2425        Convert this expression to a CREATE TABLE AS statement.
2426
2427        Example:
2428            >>> Select().select("*").from_("tbl").ctas("x").sql()
2429            'CREATE TABLE x AS SELECT * FROM tbl'
2430
2431        Args:
2432            table (str | Expression): the SQL code string to parse as the table name.
2433                If another `Expression` instance is passed, it will be used as-is.
2434            properties (dict): an optional mapping of table properties
2435            dialect (str): the dialect used to parse the input table.
2436            copy (bool): if `False`, modify this expression instance in-place.
2437            opts (kwargs): other options to use to parse the input table.
2438
2439        Returns:
2440            Create: the CREATE TABLE AS expression
2441        """
2442        instance = _maybe_copy(self, copy)
2443        table_expression = maybe_parse(
2444            table,
2445            into=Table,
2446            dialect=dialect,
2447            **opts,
2448        )
2449        properties_expression = None
2450        if properties:
2451            properties_expression = Properties.from_dict(properties)
2452
2453        return Create(
2454            this=table_expression,
2455            kind="table",
2456            expression=instance,
2457            properties=properties_expression,
2458        )
2459
2460    def lock(self, update: bool = True, copy: bool = True) -> Select:
2461        """
2462        Set the locking read mode for this expression.
2463
2464        Examples:
2465            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2466            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2467
2468            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2469            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2470
2471        Args:
2472            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2473            copy: if `False`, modify this expression instance in-place.
2474
2475        Returns:
2476            The modified expression.
2477        """
2478
2479        inst = _maybe_copy(self, copy)
2480        inst.set("lock", Lock(update=update))
2481
2482        return inst
2483
2484    @property
2485    def named_selects(self) -> t.List[str]:
2486        return [e.output_name for e in self.expressions if e.alias_or_name]
2487
2488    @property
2489    def is_star(self) -> bool:
2490        return any(expression.is_star for expression in self.expressions)
2491
2492    @property
2493    def selects(self) -> t.List[Expression]:
2494        return self.expressions
2495
2496
2497class Subquery(DerivedTable, Unionable):
2498    arg_types = {
2499        "this": True,
2500        "alias": False,
2501        "with": False,
2502        **QUERY_MODIFIERS,
2503    }
2504
2505    def unnest(self):
2506        """
2507        Returns the first non subquery.
2508        """
2509        expression = self
2510        while isinstance(expression, Subquery):
2511            expression = expression.this
2512        return expression
2513
2514    @property
2515    def is_star(self) -> bool:
2516        return self.this.is_star
2517
2518    @property
2519    def output_name(self):
2520        return self.alias
2521
2522
2523class TableSample(Expression):
2524    arg_types = {
2525        "this": False,
2526        "method": False,
2527        "bucket_numerator": False,
2528        "bucket_denominator": False,
2529        "bucket_field": False,
2530        "percent": False,
2531        "rows": False,
2532        "size": False,
2533        "seed": False,
2534    }
2535
2536
2537class Tag(Expression):
2538    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2539
2540    arg_types = {
2541        "this": False,
2542        "prefix": False,
2543        "postfix": False,
2544    }
2545
2546
2547class Pivot(Expression):
2548    arg_types = {
2549        "this": False,
2550        "alias": False,
2551        "expressions": True,
2552        "field": True,
2553        "unpivot": True,
2554    }
2555
2556
2557class Window(Expression):
2558    arg_types = {
2559        "this": True,
2560        "partition_by": False,
2561        "order": False,
2562        "spec": False,
2563        "alias": False,
2564    }
2565
2566
2567class WindowSpec(Expression):
2568    arg_types = {
2569        "kind": False,
2570        "start": False,
2571        "start_side": False,
2572        "end": False,
2573        "end_side": False,
2574    }
2575
2576
2577class Where(Expression):
2578    pass
2579
2580
2581class Star(Expression):
2582    arg_types = {"except": False, "replace": False}
2583
2584    @property
2585    def name(self) -> str:
2586        return "*"
2587
2588    @property
2589    def output_name(self):
2590        return self.name
2591
2592
2593class Parameter(Expression):
2594    arg_types = {"this": True, "wrapped": False}
2595
2596
2597class SessionParameter(Expression):
2598    arg_types = {"this": True, "kind": False}
2599
2600
2601class Placeholder(Expression):
2602    arg_types = {"this": False}
2603
2604
2605class Null(Condition):
2606    arg_types: t.Dict[str, t.Any] = {}
2607
2608    @property
2609    def name(self) -> str:
2610        return "NULL"
2611
2612
2613class Boolean(Condition):
2614    pass
2615
2616
2617class DataType(Expression):
2618    arg_types = {
2619        "this": True,
2620        "expressions": False,
2621        "nested": False,
2622        "values": False,
2623        "prefix": False,
2624    }
2625
2626    class Type(AutoName):
2627        CHAR = auto()
2628        NCHAR = auto()
2629        VARCHAR = auto()
2630        NVARCHAR = auto()
2631        TEXT = auto()
2632        MEDIUMTEXT = auto()
2633        LONGTEXT = auto()
2634        MEDIUMBLOB = auto()
2635        LONGBLOB = auto()
2636        BINARY = auto()
2637        VARBINARY = auto()
2638        INT = auto()
2639        TINYINT = auto()
2640        SMALLINT = auto()
2641        BIGINT = auto()
2642        FLOAT = auto()
2643        DOUBLE = auto()
2644        DECIMAL = auto()
2645        BOOLEAN = auto()
2646        JSON = auto()
2647        JSONB = auto()
2648        INTERVAL = auto()
2649        TIME = auto()
2650        TIMESTAMP = auto()
2651        TIMESTAMPTZ = auto()
2652        TIMESTAMPLTZ = auto()
2653        DATE = auto()
2654        DATETIME = auto()
2655        ARRAY = auto()
2656        MAP = auto()
2657        UUID = auto()
2658        GEOGRAPHY = auto()
2659        GEOMETRY = auto()
2660        STRUCT = auto()
2661        NULLABLE = auto()
2662        HLLSKETCH = auto()
2663        HSTORE = auto()
2664        SUPER = auto()
2665        SERIAL = auto()
2666        SMALLSERIAL = auto()
2667        BIGSERIAL = auto()
2668        XML = auto()
2669        UNIQUEIDENTIFIER = auto()
2670        MONEY = auto()
2671        SMALLMONEY = auto()
2672        ROWVERSION = auto()
2673        IMAGE = auto()
2674        VARIANT = auto()
2675        OBJECT = auto()
2676        INET = auto()
2677        NULL = auto()
2678        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2679
2680    TEXT_TYPES = {
2681        Type.CHAR,
2682        Type.NCHAR,
2683        Type.VARCHAR,
2684        Type.NVARCHAR,
2685        Type.TEXT,
2686    }
2687
2688    INTEGER_TYPES = {
2689        Type.INT,
2690        Type.TINYINT,
2691        Type.SMALLINT,
2692        Type.BIGINT,
2693    }
2694
2695    FLOAT_TYPES = {
2696        Type.FLOAT,
2697        Type.DOUBLE,
2698    }
2699
2700    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2701
2702    TEMPORAL_TYPES = {
2703        Type.TIMESTAMP,
2704        Type.TIMESTAMPTZ,
2705        Type.TIMESTAMPLTZ,
2706        Type.DATE,
2707        Type.DATETIME,
2708    }
2709
2710    @classmethod
2711    def build(
2712        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2713    ) -> DataType:
2714        from sqlglot import parse_one
2715
2716        if isinstance(dtype, str):
2717            if dtype.upper() in cls.Type.__members__:
2718                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2719            else:
2720                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2721            if data_type_exp is None:
2722                raise ValueError(f"Unparsable data type value: {dtype}")
2723        elif isinstance(dtype, DataType.Type):
2724            data_type_exp = DataType(this=dtype)
2725        elif isinstance(dtype, DataType):
2726            return dtype
2727        else:
2728            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2729        return DataType(**{**data_type_exp.args, **kwargs})
2730
2731    def is_type(self, dtype: DataType.Type) -> bool:
2732        return self.this == dtype
2733
2734
2735# https://www.postgresql.org/docs/15/datatype-pseudo.html
2736class PseudoType(Expression):
2737    pass
2738
2739
2740class StructKwarg(Expression):
2741    arg_types = {"this": True, "expression": True}
2742
2743
2744# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2745class SubqueryPredicate(Predicate):
2746    pass
2747
2748
2749class All(SubqueryPredicate):
2750    pass
2751
2752
2753class Any(SubqueryPredicate):
2754    pass
2755
2756
2757class Exists(SubqueryPredicate):
2758    pass
2759
2760
2761# Commands to interact with the databases or engines. For most of the command
2762# expressions we parse whatever comes after the command's name as a string.
2763class Command(Expression):
2764    arg_types = {"this": True, "expression": False}
2765
2766
2767class Transaction(Expression):
2768    arg_types = {"this": False, "modes": False}
2769
2770
2771class Commit(Expression):
2772    arg_types = {"chain": False}
2773
2774
2775class Rollback(Expression):
2776    arg_types = {"savepoint": False}
2777
2778
2779class AlterTable(Expression):
2780    arg_types = {"this": True, "actions": True, "exists": False}
2781
2782
2783class AddConstraint(Expression):
2784    arg_types = {"this": False, "expression": False, "enforced": False}
2785
2786
2787class DropPartition(Expression):
2788    arg_types = {"expressions": True, "exists": False}
2789
2790
2791# Binary expressions like (ADD a b)
2792class Binary(Expression):
2793    arg_types = {"this": True, "expression": True}
2794
2795    @property
2796    def left(self):
2797        return self.this
2798
2799    @property
2800    def right(self):
2801        return self.expression
2802
2803
2804class Add(Binary):
2805    pass
2806
2807
2808class Connector(Binary, Condition):
2809    pass
2810
2811
2812class And(Connector):
2813    pass
2814
2815
2816class Or(Connector):
2817    pass
2818
2819
2820class BitwiseAnd(Binary):
2821    pass
2822
2823
2824class BitwiseLeftShift(Binary):
2825    pass
2826
2827
2828class BitwiseOr(Binary):
2829    pass
2830
2831
2832class BitwiseRightShift(Binary):
2833    pass
2834
2835
2836class BitwiseXor(Binary):
2837    pass
2838
2839
2840class Div(Binary):
2841    pass
2842
2843
2844class Overlaps(Binary):
2845    pass
2846
2847
2848class Dot(Binary):
2849    @property
2850    def name(self) -> str:
2851        return self.expression.name
2852
2853
2854class DPipe(Binary):
2855    pass
2856
2857
2858class EQ(Binary, Predicate):
2859    pass
2860
2861
2862class NullSafeEQ(Binary, Predicate):
2863    pass
2864
2865
2866class NullSafeNEQ(Binary, Predicate):
2867    pass
2868
2869
2870class Distance(Binary):
2871    pass
2872
2873
2874class Escape(Binary):
2875    pass
2876
2877
2878class Glob(Binary, Predicate):
2879    pass
2880
2881
2882class GT(Binary, Predicate):
2883    pass
2884
2885
2886class GTE(Binary, Predicate):
2887    pass
2888
2889
2890class ILike(Binary, Predicate):
2891    pass
2892
2893
2894class ILikeAny(Binary, Predicate):
2895    pass
2896
2897
2898class IntDiv(Binary):
2899    pass
2900
2901
2902class Is(Binary, Predicate):
2903    pass
2904
2905
2906class Kwarg(Binary):
2907    """Kwarg in special functions like func(kwarg => y)."""
2908
2909
2910class Like(Binary, Predicate):
2911    pass
2912
2913
2914class LikeAny(Binary, Predicate):
2915    pass
2916
2917
2918class LT(Binary, Predicate):
2919    pass
2920
2921
2922class LTE(Binary, Predicate):
2923    pass
2924
2925
2926class Mod(Binary):
2927    pass
2928
2929
2930class Mul(Binary):
2931    pass
2932
2933
2934class NEQ(Binary, Predicate):
2935    pass
2936
2937
2938class SimilarTo(Binary, Predicate):
2939    pass
2940
2941
2942class Slice(Binary):
2943    arg_types = {"this": False, "expression": False}
2944
2945
2946class Sub(Binary):
2947    pass
2948
2949
2950# Unary Expressions
2951# (NOT a)
2952class Unary(Expression):
2953    pass
2954
2955
2956class BitwiseNot(Unary):
2957    pass
2958
2959
2960class Not(Unary, Condition):
2961    pass
2962
2963
2964class Paren(Unary, Condition):
2965    arg_types = {"this": True, "with": False}
2966
2967
2968class Neg(Unary):
2969    pass
2970
2971
2972# Special Functions
2973class Alias(Expression):
2974    arg_types = {"this": True, "alias": False}
2975
2976    @property
2977    def output_name(self):
2978        return self.alias
2979
2980
2981class Aliases(Expression):
2982    arg_types = {"this": True, "expressions": True}
2983
2984    @property
2985    def aliases(self):
2986        return self.expressions
2987
2988
2989class AtTimeZone(Expression):
2990    arg_types = {"this": True, "zone": True}
2991
2992
2993class Between(Predicate):
2994    arg_types = {"this": True, "low": True, "high": True}
2995
2996
2997class Bracket(Condition):
2998    arg_types = {"this": True, "expressions": True}
2999
3000
3001class Distinct(Expression):
3002    arg_types = {"expressions": False, "on": False}
3003
3004
3005class In(Predicate):
3006    arg_types = {
3007        "this": True,
3008        "expressions": False,
3009        "query": False,
3010        "unnest": False,
3011        "field": False,
3012        "is_global": False,
3013    }
3014
3015
3016class TimeUnit(Expression):
3017    """Automatically converts unit arg into a var."""
3018
3019    arg_types = {"unit": False}
3020
3021    def __init__(self, **args):
3022        unit = args.get("unit")
3023        if isinstance(unit, Column):
3024            args["unit"] = Var(this=unit.name)
3025        elif isinstance(unit, Week):
3026            unit.set("this", Var(this=unit.this.name))
3027        super().__init__(**args)
3028
3029
3030class Interval(TimeUnit):
3031    arg_types = {"this": False, "unit": False}
3032
3033
3034class IgnoreNulls(Expression):
3035    pass
3036
3037
3038class RespectNulls(Expression):
3039    pass
3040
3041
3042# Functions
3043class Func(Condition):
3044    """
3045    The base class for all function expressions.
3046
3047    Attributes:
3048        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3049            treated as a variable length argument and the argument's value will be stored as a list.
3050        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3051            for this function expression. These values are used to map this node to a name during parsing
3052            as well as to provide the function's name during SQL string generation. By default the SQL
3053            name is set to the expression's class name transformed to snake case.
3054    """
3055
3056    is_var_len_args = False
3057
3058    @classmethod
3059    def from_arg_list(cls, args):
3060        if cls.is_var_len_args:
3061            all_arg_keys = list(cls.arg_types)
3062            # If this function supports variable length argument treat the last argument as such.
3063            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3064            num_non_var = len(non_var_len_arg_keys)
3065
3066            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3067            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3068        else:
3069            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3070
3071        return cls(**args_dict)
3072
3073    @classmethod
3074    def sql_names(cls):
3075        if cls is Func:
3076            raise NotImplementedError(
3077                "SQL name is only supported by concrete function implementations"
3078            )
3079        if "_sql_names" not in cls.__dict__:
3080            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3081        return cls._sql_names
3082
3083    @classmethod
3084    def sql_name(cls):
3085        return cls.sql_names()[0]
3086
3087    @classmethod
3088    def default_parser_mappings(cls):
3089        return {name: cls.from_arg_list for name in cls.sql_names()}
3090
3091
3092class AggFunc(Func):
3093    pass
3094
3095
3096class Abs(Func):
3097    pass
3098
3099
3100class Anonymous(Func):
3101    arg_types = {"this": True, "expressions": False}
3102    is_var_len_args = True
3103
3104
3105class ApproxDistinct(AggFunc):
3106    arg_types = {"this": True, "accuracy": False}
3107
3108
3109class Array(Func):
3110    arg_types = {"expressions": False}
3111    is_var_len_args = True
3112
3113
3114class GenerateSeries(Func):
3115    arg_types = {"start": True, "end": True, "step": False}
3116
3117
3118class ArrayAgg(AggFunc):
3119    pass
3120
3121
3122class ArrayAll(Func):
3123    arg_types = {"this": True, "expression": True}
3124
3125
3126class ArrayAny(Func):
3127    arg_types = {"this": True, "expression": True}
3128
3129
3130class ArrayConcat(Func):
3131    arg_types = {"this": True, "expressions": False}
3132    is_var_len_args = True
3133
3134
3135class ArrayContains(Func):
3136    arg_types = {"this": True, "expression": True}
3137
3138
3139class ArrayFilter(Func):
3140    arg_types = {"this": True, "expression": True}
3141    _sql_names = ["FILTER", "ARRAY_FILTER"]
3142
3143
3144class ArrayJoin(Func):
3145    arg_types = {"this": True, "expression": True, "null": False}
3146
3147
3148class ArraySize(Func):
3149    arg_types = {"this": True, "expression": False}
3150
3151
3152class ArraySort(Func):
3153    arg_types = {"this": True, "expression": False}
3154
3155
3156class ArraySum(Func):
3157    pass
3158
3159
3160class ArrayUnionAgg(AggFunc):
3161    pass
3162
3163
3164class Avg(AggFunc):
3165    pass
3166
3167
3168class AnyValue(AggFunc):
3169    pass
3170
3171
3172class Case(Func):
3173    arg_types = {"this": False, "ifs": True, "default": False}
3174
3175
3176class Cast(Func):
3177    arg_types = {"this": True, "to": True}
3178
3179    @property
3180    def name(self) -> str:
3181        return self.this.name
3182
3183    @property
3184    def to(self):
3185        return self.args["to"]
3186
3187    @property
3188    def output_name(self):
3189        return self.name
3190
3191    def is_type(self, dtype: DataType.Type) -> bool:
3192        return self.to.is_type(dtype)
3193
3194
3195class Collate(Binary):
3196    pass
3197
3198
3199class TryCast(Cast):
3200    pass
3201
3202
3203class Ceil(Func):
3204    arg_types = {"this": True, "decimals": False}
3205    _sql_names = ["CEIL", "CEILING"]
3206
3207
3208class Coalesce(Func):
3209    arg_types = {"this": True, "expressions": False}
3210    is_var_len_args = True
3211
3212
3213class Concat(Func):
3214    arg_types = {"expressions": True}
3215    is_var_len_args = True
3216
3217
3218class ConcatWs(Concat):
3219    _sql_names = ["CONCAT_WS"]
3220
3221
3222class Count(AggFunc):
3223    arg_types = {"this": False}
3224
3225
3226class CurrentDate(Func):
3227    arg_types = {"this": False}
3228
3229
3230class CurrentDatetime(Func):
3231    arg_types = {"this": False}
3232
3233
3234class CurrentTime(Func):
3235    arg_types = {"this": False}
3236
3237
3238class CurrentTimestamp(Func):
3239    arg_types = {"this": False}
3240
3241
3242class DateAdd(Func, TimeUnit):
3243    arg_types = {"this": True, "expression": True, "unit": False}
3244
3245
3246class DateSub(Func, TimeUnit):
3247    arg_types = {"this": True, "expression": True, "unit": False}
3248
3249
3250class DateDiff(Func, TimeUnit):
3251    arg_types = {"this": True, "expression": True, "unit": False}
3252
3253
3254class DateTrunc(Func):
3255    arg_types = {"unit": True, "this": True, "zone": False}
3256
3257
3258class DatetimeAdd(Func, TimeUnit):
3259    arg_types = {"this": True, "expression": True, "unit": False}
3260
3261
3262class DatetimeSub(Func, TimeUnit):
3263    arg_types = {"this": True, "expression": True, "unit": False}
3264
3265
3266class DatetimeDiff(Func, TimeUnit):
3267    arg_types = {"this": True, "expression": True, "unit": False}
3268
3269
3270class DatetimeTrunc(Func, TimeUnit):
3271    arg_types = {"this": True, "unit": True, "zone": False}
3272
3273
3274class DayOfWeek(Func):
3275    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3276
3277
3278class DayOfMonth(Func):
3279    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3280
3281
3282class DayOfYear(Func):
3283    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3284
3285
3286class WeekOfYear(Func):
3287    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3288
3289
3290class LastDateOfMonth(Func):
3291    pass
3292
3293
3294class Extract(Func):
3295    arg_types = {"this": True, "expression": True}
3296
3297
3298class TimestampAdd(Func, TimeUnit):
3299    arg_types = {"this": True, "expression": True, "unit": False}
3300
3301
3302class TimestampSub(Func, TimeUnit):
3303    arg_types = {"this": True, "expression": True, "unit": False}
3304
3305
3306class TimestampDiff(Func, TimeUnit):
3307    arg_types = {"this": True, "expression": True, "unit": False}
3308
3309
3310class TimestampTrunc(Func, TimeUnit):
3311    arg_types = {"this": True, "unit": True, "zone": False}
3312
3313
3314class TimeAdd(Func, TimeUnit):
3315    arg_types = {"this": True, "expression": True, "unit": False}
3316
3317
3318class TimeSub(Func, TimeUnit):
3319    arg_types = {"this": True, "expression": True, "unit": False}
3320
3321
3322class TimeDiff(Func, TimeUnit):
3323    arg_types = {"this": True, "expression": True, "unit": False}
3324
3325
3326class TimeTrunc(Func, TimeUnit):
3327    arg_types = {"this": True, "unit": True, "zone": False}
3328
3329
3330class DateFromParts(Func):
3331    _sql_names = ["DATEFROMPARTS"]
3332    arg_types = {"year": True, "month": True, "day": True}
3333
3334
3335class DateStrToDate(Func):
3336    pass
3337
3338
3339class DateToDateStr(Func):
3340    pass
3341
3342
3343class DateToDi(Func):
3344    pass
3345
3346
3347class Day(Func):
3348    pass
3349
3350
3351class Decode(Func):
3352    arg_types = {"this": True, "charset": True, "replace": False}
3353
3354
3355class DiToDate(Func):
3356    pass
3357
3358
3359class Encode(Func):
3360    arg_types = {"this": True, "charset": True}
3361
3362
3363class Exp(Func):
3364    pass
3365
3366
3367class Explode(Func):
3368    pass
3369
3370
3371class Floor(Func):
3372    arg_types = {"this": True, "decimals": False}
3373
3374
3375class Greatest(Func):
3376    arg_types = {"this": True, "expressions": False}
3377    is_var_len_args = True
3378
3379
3380class GroupConcat(Func):
3381    arg_types = {"this": True, "separator": False}
3382
3383
3384class Hex(Func):
3385    pass
3386
3387
3388class If(Func):
3389    arg_types = {"this": True, "true": True, "false": False}
3390
3391
3392class IfNull(Func):
3393    arg_types = {"this": True, "expression": False}
3394    _sql_names = ["IFNULL", "NVL"]
3395
3396
3397class Initcap(Func):
3398    pass
3399
3400
3401class JSONBContains(Binary):
3402    _sql_names = ["JSONB_CONTAINS"]
3403
3404
3405class JSONExtract(Binary, Func):
3406    _sql_names = ["JSON_EXTRACT"]
3407
3408
3409class JSONExtractScalar(JSONExtract):
3410    _sql_names = ["JSON_EXTRACT_SCALAR"]
3411
3412
3413class JSONBExtract(JSONExtract):
3414    _sql_names = ["JSONB_EXTRACT"]
3415
3416
3417class JSONBExtractScalar(JSONExtract):
3418    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3419
3420
3421class Least(Func):
3422    arg_types = {"this": True, "expressions": False}
3423    is_var_len_args = True
3424
3425
3426class Length(Func):
3427    pass
3428
3429
3430class Levenshtein(Func):
3431    arg_types = {
3432        "this": True,
3433        "expression": False,
3434        "ins_cost": False,
3435        "del_cost": False,
3436        "sub_cost": False,
3437    }
3438
3439
3440class Ln(Func):
3441    pass
3442
3443
3444class Log(Func):
3445    arg_types = {"this": True, "expression": False}
3446
3447
3448class Log2(Func):
3449    pass
3450
3451
3452class Log10(Func):
3453    pass
3454
3455
3456class LogicalOr(AggFunc):
3457    _sql_names = ["LOGICAL_OR", "BOOL_OR"]
3458
3459
3460class Lower(Func):
3461    _sql_names = ["LOWER", "LCASE"]
3462
3463
3464class Map(Func):
3465    arg_types = {"keys": False, "values": False}
3466
3467
3468class VarMap(Func):
3469    arg_types = {"keys": True, "values": True}
3470    is_var_len_args = True
3471
3472
3473class Matches(Func):
3474    """Oracle/Snowflake decode.
3475    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3476    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3477    """
3478
3479    arg_types = {"this": True, "expressions": True}
3480    is_var_len_args = True
3481
3482
3483class Max(AggFunc):
3484    arg_types = {"this": True, "expression": False}
3485
3486
3487class Min(AggFunc):
3488    arg_types = {"this": True, "expression": False}
3489
3490
3491class Month(Func):
3492    pass
3493
3494
3495class Nvl2(Func):
3496    arg_types = {"this": True, "true": True, "false": False}
3497
3498
3499class Posexplode(Func):
3500    pass
3501
3502
3503class Pow(Binary, Func):
3504    _sql_names = ["POWER", "POW"]
3505
3506
3507class PercentileCont(AggFunc):
3508    pass
3509
3510
3511class PercentileDisc(AggFunc):
3512    pass
3513
3514
3515class Quantile(AggFunc):
3516    arg_types = {"this": True, "quantile": True}
3517
3518
3519# Clickhouse-specific:
3520# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3521class Quantiles(AggFunc):
3522    arg_types = {"parameters": True, "expressions": True}
3523
3524
3525class QuantileIf(AggFunc):
3526    arg_types = {"parameters": True, "expressions": True}
3527
3528
3529class ApproxQuantile(Quantile):
3530    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3531
3532
3533class RangeN(Func):
3534    arg_types = {"this": True, "expressions": True, "each": False}
3535
3536
3537class ReadCSV(Func):
3538    _sql_names = ["READ_CSV"]
3539    is_var_len_args = True
3540    arg_types = {"this": True, "expressions": False}
3541
3542
3543class Reduce(Func):
3544    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3545
3546
3547class RegexpExtract(Func):
3548    arg_types = {
3549        "this": True,
3550        "expression": True,
3551        "position": False,
3552        "occurrence": False,
3553        "group": False,
3554    }
3555
3556
3557class RegexpLike(Func):
3558    arg_types = {"this": True, "expression": True, "flag": False}
3559
3560
3561class RegexpILike(Func):
3562    arg_types = {"this": True, "expression": True, "flag": False}
3563
3564
3565class RegexpSplit(Func):
3566    arg_types = {"this": True, "expression": True}
3567
3568
3569class Repeat(Func):
3570    arg_types = {"this": True, "times": True}
3571
3572
3573class Round(Func):
3574    arg_types = {"this": True, "decimals": False}
3575
3576
3577class RowNumber(Func):
3578    arg_types: t.Dict[str, t.Any] = {}
3579
3580
3581class SafeDivide(Func):
3582    arg_types = {"this": True, "expression": True}
3583
3584
3585class SetAgg(AggFunc):
3586    pass
3587
3588
3589class SortArray(Func):
3590    arg_types = {"this": True, "asc": False}
3591
3592
3593class Split(Func):
3594    arg_types = {"this": True, "expression": True, "limit": False}
3595
3596
3597# Start may be omitted in the case of postgres
3598# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3599class Substring(Func):
3600    arg_types = {"this": True, "start": False, "length": False}
3601
3602
3603class StrPosition(Func):
3604    arg_types = {
3605        "this": True,
3606        "substr": True,
3607        "position": False,
3608        "instance": False,
3609    }
3610
3611
3612class StrToDate(Func):
3613    arg_types = {"this": True, "format": True}
3614
3615
3616class StrToTime(Func):
3617    arg_types = {"this": True, "format": True}
3618
3619
3620# Spark allows unix_timestamp()
3621# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3622class StrToUnix(Func):
3623    arg_types = {"this": False, "format": False}
3624
3625
3626class NumberToStr(Func):
3627    arg_types = {"this": True, "format": True}
3628
3629
3630class Struct(Func):
3631    arg_types = {"expressions": True}
3632    is_var_len_args = True
3633
3634
3635class StructExtract(Func):
3636    arg_types = {"this": True, "expression": True}
3637
3638
3639class Sum(AggFunc):
3640    pass
3641
3642
3643class Sqrt(Func):
3644    pass
3645
3646
3647class Stddev(AggFunc):
3648    pass
3649
3650
3651class StddevPop(AggFunc):
3652    pass
3653
3654
3655class StddevSamp(AggFunc):
3656    pass
3657
3658
3659class TimeToStr(Func):
3660    arg_types = {"this": True, "format": True}
3661
3662
3663class TimeToTimeStr(Func):
3664    pass
3665
3666
3667class TimeToUnix(Func):
3668    pass
3669
3670
3671class TimeStrToDate(Func):
3672    pass
3673
3674
3675class TimeStrToTime(Func):
3676    pass
3677
3678
3679class TimeStrToUnix(Func):
3680    pass
3681
3682
3683class Trim(Func):
3684    arg_types = {
3685        "this": True,
3686        "expression": False,
3687        "position": False,
3688        "collation": False,
3689    }
3690
3691
3692class TsOrDsAdd(Func, TimeUnit):
3693    arg_types = {"this": True, "expression": True, "unit": False}
3694
3695
3696class TsOrDsToDateStr(Func):
3697    pass
3698
3699
3700class TsOrDsToDate(Func):
3701    arg_types = {"this": True, "format": False}
3702
3703
3704class TsOrDiToDi(Func):
3705    pass
3706
3707
3708class Unhex(Func):
3709    pass
3710
3711
3712class UnixToStr(Func):
3713    arg_types = {"this": True, "format": False}
3714
3715
3716# https://prestodb.io/docs/current/functions/datetime.html
3717# presto has weird zone/hours/minutes
3718class UnixToTime(Func):
3719    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3720
3721    SECONDS = Literal.string("seconds")
3722    MILLIS = Literal.string("millis")
3723    MICROS = Literal.string("micros")
3724
3725
3726class UnixToTimeStr(Func):
3727    pass
3728
3729
3730class Upper(Func):
3731    _sql_names = ["UPPER", "UCASE"]
3732
3733
3734class Variance(AggFunc):
3735    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3736
3737
3738class VariancePop(AggFunc):
3739    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3740
3741
3742class Week(Func):
3743    arg_types = {"this": True, "mode": False}
3744
3745
3746class XMLTable(Func):
3747    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3748
3749
3750class Year(Func):
3751    pass
3752
3753
3754class Use(Expression):
3755    arg_types = {"this": True, "kind": False}
3756
3757
3758class Merge(Expression):
3759    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3760
3761
3762class When(Func):
3763    arg_types = {"this": True, "then": True}
3764
3765
3766def _norm_args(expression):
3767    args = {}
3768
3769    for k, arg in expression.args.items():
3770        if isinstance(arg, list):
3771            arg = [_norm_arg(a) for a in arg]
3772            if not arg:
3773                arg = None
3774        else:
3775            arg = _norm_arg(arg)
3776
3777        if arg is not None and arg is not False:
3778            args[k] = arg
3779
3780    return args
3781
3782
3783def _norm_arg(arg):
3784    return arg.lower() if isinstance(arg, str) else arg
3785
3786
3787ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3788
3789
3790# Helpers
3791def maybe_parse(
3792    sql_or_expression: str | Expression,
3793    *,
3794    into: t.Optional[IntoType] = None,
3795    dialect: DialectType = None,
3796    prefix: t.Optional[str] = None,
3797    copy: bool = False,
3798    **opts,
3799) -> Expression:
3800    """Gracefully handle a possible string or expression.
3801
3802    Example:
3803        >>> maybe_parse("1")
3804        (LITERAL this: 1, is_string: False)
3805        >>> maybe_parse(to_identifier("x"))
3806        (IDENTIFIER this: x, quoted: False)
3807
3808    Args:
3809        sql_or_expression: the SQL code string or an expression
3810        into: the SQLGlot Expression to parse into
3811        dialect: the dialect used to parse the input expressions (in the case that an
3812            input expression is a SQL string).
3813        prefix: a string to prefix the sql with before it gets parsed
3814            (automatically includes a space)
3815        copy: whether or not to copy the expression.
3816        **opts: other options to use to parse the input expressions (again, in the case
3817            that an input expression is a SQL string).
3818
3819    Returns:
3820        Expression: the parsed or given expression.
3821    """
3822    if isinstance(sql_or_expression, Expression):
3823        if copy:
3824            return sql_or_expression.copy()
3825        return sql_or_expression
3826
3827    import sqlglot
3828
3829    sql = str(sql_or_expression)
3830    if prefix:
3831        sql = f"{prefix} {sql}"
3832    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
3833
3834
3835def _maybe_copy(instance, copy=True):
3836    return instance.copy() if copy else instance
3837
3838
3839def _is_wrong_expression(expression, into):
3840    return isinstance(expression, Expression) and not isinstance(expression, into)
3841
3842
3843def _apply_builder(
3844    expression,
3845    instance,
3846    arg,
3847    copy=True,
3848    prefix=None,
3849    into=None,
3850    dialect=None,
3851    **opts,
3852):
3853    if _is_wrong_expression(expression, into):
3854        expression = into(this=expression)
3855    instance = _maybe_copy(instance, copy)
3856    expression = maybe_parse(
3857        sql_or_expression=expression,
3858        prefix=prefix,
3859        into=into,
3860        dialect=dialect,
3861        **opts,
3862    )
3863    instance.set(arg, expression)
3864    return instance
3865
3866
3867def _apply_child_list_builder(
3868    *expressions,
3869    instance,
3870    arg,
3871    append=True,
3872    copy=True,
3873    prefix=None,
3874    into=None,
3875    dialect=None,
3876    properties=None,
3877    **opts,
3878):
3879    instance = _maybe_copy(instance, copy)
3880    parsed = []
3881    for expression in expressions:
3882        if _is_wrong_expression(expression, into):
3883            expression = into(expressions=[expression])
3884        expression = maybe_parse(
3885            expression,
3886            into=into,
3887            dialect=dialect,
3888            prefix=prefix,
3889            **opts,
3890        )
3891        parsed.extend(expression.expressions)
3892
3893    existing = instance.args.get(arg)
3894    if append and existing:
3895        parsed = existing.expressions + parsed
3896
3897    child = into(expressions=parsed)
3898    for k, v in (properties or {}).items():
3899        child.set(k, v)
3900    instance.set(arg, child)
3901    return instance
3902
3903
3904def _apply_list_builder(
3905    *expressions,
3906    instance,
3907    arg,
3908    append=True,
3909    copy=True,
3910    prefix=None,
3911    into=None,
3912    dialect=None,
3913    **opts,
3914):
3915    inst = _maybe_copy(instance, copy)
3916
3917    expressions = [
3918        maybe_parse(
3919            sql_or_expression=expression,
3920            into=into,
3921            prefix=prefix,
3922            dialect=dialect,
3923            **opts,
3924        )
3925        for expression in expressions
3926    ]
3927
3928    existing_expressions = inst.args.get(arg)
3929    if append and existing_expressions:
3930        expressions = existing_expressions + expressions
3931
3932    inst.set(arg, expressions)
3933    return inst
3934
3935
3936def _apply_conjunction_builder(
3937    *expressions,
3938    instance,
3939    arg,
3940    into=None,
3941    append=True,
3942    copy=True,
3943    dialect=None,
3944    **opts,
3945):
3946    expressions = [exp for exp in expressions if exp is not None and exp != ""]
3947    if not expressions:
3948        return instance
3949
3950    inst = _maybe_copy(instance, copy)
3951
3952    existing = inst.args.get(arg)
3953    if append and existing is not None:
3954        expressions = [existing.this if into else existing] + list(expressions)
3955
3956    node = and_(*expressions, dialect=dialect, **opts)
3957
3958    inst.set(arg, into(this=node) if into else node)
3959    return inst
3960
3961
3962def _combine(expressions, operator, dialect=None, **opts):
3963    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
3964    this = expressions[0]
3965    if expressions[1:]:
3966        this = _wrap_operator(this)
3967    for expression in expressions[1:]:
3968        this = operator(this=this, expression=_wrap_operator(expression))
3969    return this
3970
3971
3972def _wrap_operator(expression):
3973    if isinstance(expression, (And, Or, Not)):
3974        expression = Paren(this=expression)
3975    return expression
3976
3977
3978def union(left, right, distinct=True, dialect=None, **opts):
3979    """
3980    Initializes a syntax tree from one UNION expression.
3981
3982    Example:
3983        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
3984        'SELECT * FROM foo UNION SELECT * FROM bla'
3985
3986    Args:
3987        left (str | Expression): the SQL code string corresponding to the left-hand side.
3988            If an `Expression` instance is passed, it will be used as-is.
3989        right (str | Expression): the SQL code string corresponding to the right-hand side.
3990            If an `Expression` instance is passed, it will be used as-is.
3991        distinct (bool): set the DISTINCT flag if and only if this is true.
3992        dialect (str): the dialect used to parse the input expression.
3993        opts (kwargs): other options to use to parse the input expressions.
3994    Returns:
3995        Union: the syntax tree for the UNION expression.
3996    """
3997    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3998    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3999
4000    return Union(this=left, expression=right, distinct=distinct)
4001
4002
4003def intersect(left, right, distinct=True, dialect=None, **opts):
4004    """
4005    Initializes a syntax tree from one INTERSECT expression.
4006
4007    Example:
4008        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4009        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4010
4011    Args:
4012        left (str | Expression): the SQL code string corresponding to the left-hand side.
4013            If an `Expression` instance is passed, it will be used as-is.
4014        right (str | Expression): the SQL code string corresponding to the right-hand side.
4015            If an `Expression` instance is passed, it will be used as-is.
4016        distinct (bool): set the DISTINCT flag if and only if this is true.
4017        dialect (str): the dialect used to parse the input expression.
4018        opts (kwargs): other options to use to parse the input expressions.
4019    Returns:
4020        Intersect: the syntax tree for the INTERSECT expression.
4021    """
4022    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4023    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4024
4025    return Intersect(this=left, expression=right, distinct=distinct)
4026
4027
4028def except_(left, right, distinct=True, dialect=None, **opts):
4029    """
4030    Initializes a syntax tree from one EXCEPT expression.
4031
4032    Example:
4033        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4034        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4035
4036    Args:
4037        left (str | Expression): the SQL code string corresponding to the left-hand side.
4038            If an `Expression` instance is passed, it will be used as-is.
4039        right (str | Expression): the SQL code string corresponding to the right-hand side.
4040            If an `Expression` instance is passed, it will be used as-is.
4041        distinct (bool): set the DISTINCT flag if and only if this is true.
4042        dialect (str): the dialect used to parse the input expression.
4043        opts (kwargs): other options to use to parse the input expressions.
4044    Returns:
4045        Except: the syntax tree for the EXCEPT statement.
4046    """
4047    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4048    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4049
4050    return Except(this=left, expression=right, distinct=distinct)
4051
4052
4053def select(*expressions: str | Expression, dialect: DialectType = None, **opts) -> Select:
4054    """
4055    Initializes a syntax tree from one or multiple SELECT expressions.
4056
4057    Example:
4058        >>> select("col1", "col2").from_("tbl").sql()
4059        'SELECT col1, col2 FROM tbl'
4060
4061    Args:
4062        *expressions: the SQL code string to parse as the expressions of a
4063            SELECT statement. If an Expression instance is passed, this is used as-is.
4064        dialect: the dialect used to parse the input expressions (in the case that an
4065            input expression is a SQL string).
4066        **opts: other options to use to parse the input expressions (again, in the case
4067            that an input expression is a SQL string).
4068
4069    Returns:
4070        Select: the syntax tree for the SELECT statement.
4071    """
4072    return Select().select(*expressions, dialect=dialect, **opts)
4073
4074
4075def from_(*expressions, dialect=None, **opts) -> Select:
4076    """
4077    Initializes a syntax tree from a FROM expression.
4078
4079    Example:
4080        >>> from_("tbl").select("col1", "col2").sql()
4081        'SELECT col1, col2 FROM tbl'
4082
4083    Args:
4084        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4085            SELECT statement. If an Expression instance is passed, this is used as-is.
4086        dialect (str): the dialect used to parse the input expression (in the case that the
4087            input expression is a SQL string).
4088        **opts: other options to use to parse the input expressions (again, in the case
4089            that the input expression is a SQL string).
4090
4091    Returns:
4092        Select: the syntax tree for the SELECT statement.
4093    """
4094    return Select().from_(*expressions, dialect=dialect, **opts)
4095
4096
4097def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update:
4098    """
4099    Creates an update statement.
4100
4101    Example:
4102        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4103        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4104
4105    Args:
4106        *properties (Dict[str, Any]): dictionary of properties to set which are
4107            auto converted to sql objects eg None -> NULL
4108        where (str): sql conditional parsed into a WHERE statement
4109        from_ (str): sql statement parsed into a FROM statement
4110        dialect (str): the dialect used to parse the input expressions.
4111        **opts: other options to use to parse the input expressions.
4112
4113    Returns:
4114        Update: the syntax tree for the UPDATE statement.
4115    """
4116    update = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4117    update.set(
4118        "expressions",
4119        [
4120            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4121            for k, v in properties.items()
4122        ],
4123    )
4124    if from_:
4125        update.set(
4126            "from",
4127            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4128        )
4129    if isinstance(where, Condition):
4130        where = Where(this=where)
4131    if where:
4132        update.set(
4133            "where",
4134            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4135        )
4136    return update
4137
4138
4139def delete(table, where=None, dialect=None, **opts) -> Delete:
4140    """
4141    Builds a delete statement.
4142
4143    Example:
4144        >>> delete("my_table", where="id > 1").sql()
4145        'DELETE FROM my_table WHERE id > 1'
4146
4147    Args:
4148        where (str|Condition): sql conditional parsed into a WHERE statement
4149        dialect (str): the dialect used to parse the input expressions.
4150        **opts: other options to use to parse the input expressions.
4151
4152    Returns:
4153        Delete: the syntax tree for the DELETE statement.
4154    """
4155    return Delete(
4156        this=maybe_parse(table, into=Table, dialect=dialect, **opts),
4157        where=Where(this=where)
4158        if isinstance(where, Condition)
4159        else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4160    )
4161
4162
4163def condition(expression, dialect=None, **opts) -> Condition:
4164    """
4165    Initialize a logical condition expression.
4166
4167    Example:
4168        >>> condition("x=1").sql()
4169        'x = 1'
4170
4171        This is helpful for composing larger logical syntax trees:
4172        >>> where = condition("x=1")
4173        >>> where = where.and_("y=1")
4174        >>> Select().from_("tbl").select("*").where(where).sql()
4175        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4176
4177    Args:
4178        *expression (str | Expression): the SQL code string to parse.
4179            If an Expression instance is passed, this is used as-is.
4180        dialect (str): the dialect used to parse the input expression (in the case that the
4181            input expression is a SQL string).
4182        **opts: other options to use to parse the input expressions (again, in the case
4183            that the input expression is a SQL string).
4184
4185    Returns:
4186        Condition: the expression
4187    """
4188    return maybe_parse(  # type: ignore
4189        expression,
4190        into=Condition,
4191        dialect=dialect,
4192        **opts,
4193    )
4194
4195
4196def and_(*expressions, dialect=None, **opts) -> And:
4197    """
4198    Combine multiple conditions with an AND logical operator.
4199
4200    Example:
4201        >>> and_("x=1", and_("y=1", "z=1")).sql()
4202        'x = 1 AND (y = 1 AND z = 1)'
4203
4204    Args:
4205        *expressions (str | Expression): the SQL code strings to parse.
4206            If an Expression instance is passed, this is used as-is.
4207        dialect (str): the dialect used to parse the input expression.
4208        **opts: other options to use to parse the input expressions.
4209
4210    Returns:
4211        And: the new condition
4212    """
4213    return _combine(expressions, And, dialect, **opts)
4214
4215
4216def or_(*expressions, dialect=None, **opts) -> Or:
4217    """
4218    Combine multiple conditions with an OR logical operator.
4219
4220    Example:
4221        >>> or_("x=1", or_("y=1", "z=1")).sql()
4222        'x = 1 OR (y = 1 OR z = 1)'
4223
4224    Args:
4225        *expressions (str | Expression): the SQL code strings to parse.
4226            If an Expression instance is passed, this is used as-is.
4227        dialect (str): the dialect used to parse the input expression.
4228        **opts: other options to use to parse the input expressions.
4229
4230    Returns:
4231        Or: the new condition
4232    """
4233    return _combine(expressions, Or, dialect, **opts)
4234
4235
4236def not_(expression, dialect=None, **opts) -> Not:
4237    """
4238    Wrap a condition with a NOT operator.
4239
4240    Example:
4241        >>> not_("this_suit='black'").sql()
4242        "NOT this_suit = 'black'"
4243
4244    Args:
4245        expression (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        Not: the new condition
4252    """
4253    this = condition(
4254        expression,
4255        dialect=dialect,
4256        **opts,
4257    )
4258    return Not(this=_wrap_operator(this))
4259
4260
4261def paren(expression) -> Paren:
4262    return Paren(this=expression)
4263
4264
4265SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4266
4267
4268@t.overload
4269def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4270    ...
4271
4272
4273@t.overload
4274def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4275    ...
4276
4277
4278def to_identifier(name, quoted=None):
4279    """Builds an identifier.
4280
4281    Args:
4282        name: The name to turn into an identifier.
4283        quoted: Whether or not force quote the identifier.
4284
4285    Returns:
4286        The identifier ast node.
4287    """
4288
4289    if name is None:
4290        return None
4291
4292    if isinstance(name, Identifier):
4293        identifier = name
4294    elif isinstance(name, str):
4295        identifier = Identifier(
4296            this=name,
4297            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4298        )
4299    else:
4300        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4301    return identifier
4302
4303
4304INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4305
4306
4307def to_interval(interval: str | Literal) -> Interval:
4308    """Builds an interval expression from a string like '1 day' or '5 months'."""
4309    if isinstance(interval, Literal):
4310        if not interval.is_string:
4311            raise ValueError("Invalid interval string.")
4312
4313        interval = interval.this
4314
4315    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4316
4317    if not interval_parts:
4318        raise ValueError("Invalid interval string.")
4319
4320    return Interval(
4321        this=Literal.string(interval_parts.group(1)),
4322        unit=Var(this=interval_parts.group(2)),
4323    )
4324
4325
4326@t.overload
4327def to_table(sql_path: str | Table, **kwargs) -> Table:
4328    ...
4329
4330
4331@t.overload
4332def to_table(sql_path: None, **kwargs) -> None:
4333    ...
4334
4335
4336def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4337    """
4338    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4339    If a table is passed in then that table is returned.
4340
4341    Args:
4342        sql_path: a `[catalog].[schema].[table]` string.
4343
4344    Returns:
4345        A table expression.
4346    """
4347    if sql_path is None or isinstance(sql_path, Table):
4348        return sql_path
4349    if not isinstance(sql_path, str):
4350        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4351
4352    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4353    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4354
4355
4356def to_column(sql_path: str | Column, **kwargs) -> Column:
4357    """
4358    Create a column from a `[table].[column]` sql path. Schema is optional.
4359
4360    If a column is passed in then that column is returned.
4361
4362    Args:
4363        sql_path: `[table].[column]` string
4364    Returns:
4365        Table: A column expression
4366    """
4367    if sql_path is None or isinstance(sql_path, Column):
4368        return sql_path
4369    if not isinstance(sql_path, str):
4370        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4371    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4372    return Column(this=column_name, table=table_name, **kwargs)
4373
4374
4375def alias_(
4376    expression: str | Expression,
4377    alias: str | Identifier,
4378    table: bool | t.Sequence[str | Identifier] = False,
4379    quoted: t.Optional[bool] = None,
4380    dialect: DialectType = None,
4381    **opts,
4382):
4383    """Create an Alias expression.
4384
4385    Example:
4386        >>> alias_('foo', 'bar').sql()
4387        'foo AS bar'
4388
4389        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4390        '(SELECT 1, 2) AS bar(a, b)'
4391
4392    Args:
4393        expression: the SQL code strings to parse.
4394            If an Expression instance is passed, this is used as-is.
4395        alias: the alias name to use. If the name has
4396            special characters it is quoted.
4397        table: Whether or not to create a table alias, can also be a list of columns.
4398        quoted: whether or not to quote the alias
4399        dialect: the dialect used to parse the input expression.
4400        **opts: other options to use to parse the input expressions.
4401
4402    Returns:
4403        Alias: the aliased expression
4404    """
4405    exp = maybe_parse(expression, dialect=dialect, **opts)
4406    alias = to_identifier(alias, quoted=quoted)
4407
4408    if table:
4409        table_alias = TableAlias(this=alias)
4410        exp.set("alias", table_alias)
4411
4412        if not isinstance(table, bool):
4413            for column in table:
4414                table_alias.append("columns", to_identifier(column, quoted=quoted))
4415
4416        return exp
4417
4418    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4419    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4420    # for the complete Window expression.
4421    #
4422    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4423
4424    if "alias" in exp.arg_types and not isinstance(exp, Window):
4425        exp = exp.copy()
4426        exp.set("alias", alias)
4427        return exp
4428    return Alias(this=exp, alias=alias)
4429
4430
4431def subquery(expression, alias=None, dialect=None, **opts):
4432    """
4433    Build a subquery expression.
4434
4435    Example:
4436        >>> subquery('select x from tbl', 'bar').select('x').sql()
4437        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4438
4439    Args:
4440        expression (str | Expression): the SQL code strings to parse.
4441            If an Expression instance is passed, this is used as-is.
4442        alias (str | Expression): the alias name to use.
4443        dialect (str): the dialect used to parse the input expression.
4444        **opts: other options to use to parse the input expressions.
4445
4446    Returns:
4447        Select: a new select with the subquery expression included
4448    """
4449
4450    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4451    return Select().from_(expression, dialect=dialect, **opts)
4452
4453
4454def column(
4455    col: str | Identifier,
4456    table: t.Optional[str | Identifier] = None,
4457    schema: t.Optional[str | Identifier] = None,
4458    quoted: t.Optional[bool] = None,
4459) -> Column:
4460    """
4461    Build a Column.
4462
4463    Args:
4464        col: column name
4465        table: table name
4466        schema: schema name
4467        quoted: whether or not to force quote each part
4468    Returns:
4469        Column: column instance
4470    """
4471    return Column(
4472        this=to_identifier(col, quoted=quoted),
4473        table=to_identifier(table, quoted=quoted),
4474        schema=to_identifier(schema, quoted=quoted),
4475    )
4476
4477
4478def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast:
4479    """Cast an expression to a data type.
4480
4481    Example:
4482        >>> cast('x + 1', 'int').sql()
4483        'CAST(x + 1 AS INT)'
4484
4485    Args:
4486        expression: The expression to cast.
4487        to: The datatype to cast to.
4488
4489    Returns:
4490        A cast node.
4491    """
4492    expression = maybe_parse(expression, **opts)
4493    return Cast(this=expression, to=DataType.build(to, **opts))
4494
4495
4496def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4497    """Build a Table.
4498
4499    Args:
4500        table (str | Expression): column name
4501        db (str | Expression): db name
4502        catalog (str | Expression): catalog name
4503
4504    Returns:
4505        Table: table instance
4506    """
4507    return Table(
4508        this=to_identifier(table, quoted=quoted),
4509        db=to_identifier(db, quoted=quoted),
4510        catalog=to_identifier(catalog, quoted=quoted),
4511        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4512    )
4513
4514
4515def values(
4516    values: t.Iterable[t.Tuple[t.Any, ...]],
4517    alias: t.Optional[str] = None,
4518    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4519) -> Values:
4520    """Build VALUES statement.
4521
4522    Example:
4523        >>> values([(1, '2')]).sql()
4524        "VALUES (1, '2')"
4525
4526    Args:
4527        values: values statements that will be converted to SQL
4528        alias: optional alias
4529        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4530         If either are provided then an alias is also required.
4531         If a dictionary is provided then the first column of the values will be casted to the expected type
4532         in order to help with type inference.
4533
4534    Returns:
4535        Values: the Values expression object
4536    """
4537    if columns and not alias:
4538        raise ValueError("Alias is required when providing columns")
4539    table_alias = (
4540        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4541        if columns
4542        else TableAlias(this=to_identifier(alias) if alias else None)
4543    )
4544    expressions = [convert(tup) for tup in values]
4545    if columns and isinstance(columns, dict):
4546        types = list(columns.values())
4547        expressions[0].set(
4548            "expressions",
4549            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4550        )
4551    return Values(
4552        expressions=expressions,
4553        alias=table_alias,
4554    )
4555
4556
4557def var(name: t.Optional[str | Expression]) -> Var:
4558    """Build a SQL variable.
4559
4560    Example:
4561        >>> repr(var('x'))
4562        '(VAR this: x)'
4563
4564        >>> repr(var(column('x', table='y')))
4565        '(VAR this: x)'
4566
4567    Args:
4568        name: The name of the var or an expression who's name will become the var.
4569
4570    Returns:
4571        The new variable node.
4572    """
4573    if not name:
4574        raise ValueError(f"Cannot convert empty name into var.")
4575
4576    if isinstance(name, Expression):
4577        name = name.name
4578    return Var(this=name)
4579
4580
4581def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4582    """Build ALTER TABLE... RENAME... expression
4583
4584    Args:
4585        old_name: The old name of the table
4586        new_name: The new name of the table
4587
4588    Returns:
4589        Alter table expression
4590    """
4591    old_table = to_table(old_name)
4592    new_table = to_table(new_name)
4593    return AlterTable(
4594        this=old_table,
4595        actions=[
4596            RenameTable(this=new_table),
4597        ],
4598    )
4599
4600
4601def convert(value) -> Expression:
4602    """Convert a python value into an expression object.
4603
4604    Raises an error if a conversion is not possible.
4605
4606    Args:
4607        value (Any): a python object
4608
4609    Returns:
4610        Expression: the equivalent expression object
4611    """
4612    if isinstance(value, Expression):
4613        return value
4614    if value is None:
4615        return NULL
4616    if isinstance(value, bool):
4617        return Boolean(this=value)
4618    if isinstance(value, str):
4619        return Literal.string(value)
4620    if isinstance(value, float) and math.isnan(value):
4621        return NULL
4622    if isinstance(value, numbers.Number):
4623        return Literal.number(value)
4624    if isinstance(value, tuple):
4625        return Tuple(expressions=[convert(v) for v in value])
4626    if isinstance(value, list):
4627        return Array(expressions=[convert(v) for v in value])
4628    if isinstance(value, dict):
4629        return Map(
4630            keys=[convert(k) for k in value],
4631            values=[convert(v) for v in value.values()],
4632        )
4633    if isinstance(value, datetime.datetime):
4634        datetime_literal = Literal.string(
4635            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4636        )
4637        return TimeStrToTime(this=datetime_literal)
4638    if isinstance(value, datetime.date):
4639        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4640        return DateStrToDate(this=date_literal)
4641    raise ValueError(f"Cannot convert {value}")
4642
4643
4644def replace_children(expression, fun):
4645    """
4646    Replace children of an expression with the result of a lambda fun(child) -> exp.
4647    """
4648    for k, v in expression.args.items():
4649        is_list_arg = isinstance(v, list)
4650
4651        child_nodes = v if is_list_arg else [v]
4652        new_child_nodes = []
4653
4654        for cn in child_nodes:
4655            if isinstance(cn, Expression):
4656                for child_node in ensure_collection(fun(cn)):
4657                    new_child_nodes.append(child_node)
4658                    child_node.parent = expression
4659                    child_node.arg_key = k
4660            else:
4661                new_child_nodes.append(cn)
4662
4663        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4664
4665
4666def column_table_names(expression):
4667    """
4668    Return all table names referenced through columns in an expression.
4669
4670    Example:
4671        >>> import sqlglot
4672        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4673        ['c', 'a']
4674
4675    Args:
4676        expression (sqlglot.Expression): expression to find table names
4677
4678    Returns:
4679        list: A list of unique names
4680    """
4681    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4682
4683
4684def table_name(table) -> str:
4685    """Get the full name of a table as a string.
4686
4687    Args:
4688        table (exp.Table | str): table expression node or string.
4689
4690    Examples:
4691        >>> from sqlglot import exp, parse_one
4692        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4693        'a.b.c'
4694
4695    Returns:
4696        The table name.
4697    """
4698
4699    table = maybe_parse(table, into=Table)
4700
4701    if not table:
4702        raise ValueError(f"Cannot parse {table}")
4703
4704    return ".".join(
4705        part
4706        for part in (
4707            table.text("catalog"),
4708            table.text("db"),
4709            table.name,
4710        )
4711        if part
4712    )
4713
4714
4715def replace_tables(expression, mapping):
4716    """Replace all tables in expression according to the mapping.
4717
4718    Args:
4719        expression (sqlglot.Expression): expression node to be transformed and replaced.
4720        mapping (Dict[str, str]): mapping of table names.
4721
4722    Examples:
4723        >>> from sqlglot import exp, parse_one
4724        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4725        'SELECT * FROM c'
4726
4727    Returns:
4728        The mapped expression.
4729    """
4730
4731    def _replace_tables(node):
4732        if isinstance(node, Table):
4733            new_name = mapping.get(table_name(node))
4734            if new_name:
4735                return to_table(
4736                    new_name,
4737                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4738                )
4739        return node
4740
4741    return expression.transform(_replace_tables)
4742
4743
4744def replace_placeholders(expression, *args, **kwargs):
4745    """Replace placeholders in an expression.
4746
4747    Args:
4748        expression (sqlglot.Expression): expression node to be transformed and replaced.
4749        args: positional names that will substitute unnamed placeholders in the given order.
4750        kwargs: keyword arguments that will substitute named placeholders.
4751
4752    Examples:
4753        >>> from sqlglot import exp, parse_one
4754        >>> replace_placeholders(
4755        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4756        ... ).sql()
4757        'SELECT * FROM foo WHERE a = b'
4758
4759    Returns:
4760        The mapped expression.
4761    """
4762
4763    def _replace_placeholders(node, args, **kwargs):
4764        if isinstance(node, Placeholder):
4765            if node.name:
4766                new_name = kwargs.get(node.name)
4767                if new_name:
4768                    return to_identifier(new_name)
4769            else:
4770                try:
4771                    return to_identifier(next(args))
4772                except StopIteration:
4773                    pass
4774        return node
4775
4776    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4777
4778
4779def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4780    """Transforms an expression by expanding all referenced sources into subqueries.
4781
4782    Examples:
4783        >>> from sqlglot import parse_one
4784        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
4785        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
4786
4787    Args:
4788        expression: The expression to expand.
4789        sources: A dictionary of name to Subqueryables.
4790        copy: Whether or not to copy the expression during transformation. Defaults to True.
4791
4792    Returns:
4793        The transformed expression.
4794    """
4795
4796    def _expand(node: Expression):
4797        if isinstance(node, Table):
4798            name = table_name(node)
4799            source = sources.get(name)
4800            if source:
4801                subquery = source.subquery(node.alias or name)
4802                subquery.comments = [f"source: {name}"]
4803                return subquery
4804        return node
4805
4806    return expression.transform(_expand, copy=copy)
4807
4808
4809def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
4810    """
4811    Returns a Func expression.
4812
4813    Examples:
4814        >>> func("abs", 5).sql()
4815        'ABS(5)'
4816
4817        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
4818        'CAST(5 AS DOUBLE)'
4819
4820    Args:
4821        name: the name of the function to build.
4822        args: the args used to instantiate the function of interest.
4823        dialect: the source dialect.
4824        kwargs: the kwargs used to instantiate the function of interest.
4825
4826    Note:
4827        The arguments `args` and `kwargs` are mutually exclusive.
4828
4829    Returns:
4830        An instance of the function of interest, or an anonymous function, if `name` doesn't
4831        correspond to an existing `sqlglot.expressions.Func` class.
4832    """
4833    if args and kwargs:
4834        raise ValueError("Can't use both args and kwargs to instantiate a function.")
4835
4836    from sqlglot.dialects.dialect import Dialect
4837
4838    args = tuple(convert(arg) for arg in args)
4839    kwargs = {key: convert(value) for key, value in kwargs.items()}
4840
4841    parser = Dialect.get_or_raise(dialect)().parser()
4842    from_args_list = parser.FUNCTIONS.get(name.upper())
4843
4844    if from_args_list:
4845        function = from_args_list(args) if args else from_args_list.__self__(**kwargs)  # type: ignore
4846    else:
4847        kwargs = kwargs or {"expressions": args}
4848        function = Anonymous(this=name, **kwargs)
4849
4850    for error_message in function.error_messages(args):
4851        raise ValueError(error_message)
4852
4853    return function
4854
4855
4856def true():
4857    """
4858    Returns a true Boolean expression.
4859    """
4860    return Boolean(this=True)
4861
4862
4863def false():
4864    """
4865    Returns a false Boolean expression.
4866    """
4867    return Boolean(this=False)
4868
4869
4870def null():
4871    """
4872    Returns a Null expression.
4873    """
4874    return Null()
4875
4876
4877# TODO: deprecate this
4878TRUE = Boolean(this=True)
4879FALSE = Boolean(this=False)
4880NULL = Null()