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