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