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