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