Expressions
Every AST node in SQLGlot is represented by a subclass of Expression
.
This module contains the implementation of all supported Expression
types. Additionally,
it exposes a number of helper functions, which are mainly used to programmatically build
SQL expressions, such as sqlglot.expressions.select
.
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 14import datetime 15import math 16import numbers 17import re 18import textwrap 19import typing as t 20from collections import deque 21from copy import deepcopy 22from decimal import Decimal 23from enum import auto 24from functools import reduce 25 26from sqlglot.errors import ErrorLevel, ParseError 27from sqlglot.helper import ( 28 AutoName, 29 camel_to_snake_case, 30 ensure_collection, 31 ensure_list, 32 seq_get, 33 subclasses, 34 to_bool, 35) 36from sqlglot.tokens import Token, TokenError 37 38if t.TYPE_CHECKING: 39 from typing_extensions import Self 40 from sqlglot._typing import E, Lit 41 from sqlglot.dialects.dialect import DialectType 42 43 Q = t.TypeVar("Q", bound="Query") 44 S = t.TypeVar("S", bound="SetOperation") 45 46 47class _Expression(type): 48 def __new__(cls, clsname, bases, attrs): 49 klass = super().__new__(cls, clsname, bases, attrs) 50 51 # When an Expression class is created, its key is automatically set to be 52 # the lowercase version of the class' name. 53 klass.key = clsname.lower() 54 55 # This is so that docstrings are not inherited in pdoc 56 klass.__doc__ = klass.__doc__ or "" 57 58 return klass 59 60 61SQLGLOT_META = "sqlglot.meta" 62SQLGLOT_ANONYMOUS = "sqlglot.anonymous" 63TABLE_PARTS = ("this", "db", "catalog") 64COLUMN_PARTS = ("this", "table", "db", "catalog") 65 66 67class Expression(metaclass=_Expression): 68 """ 69 The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary 70 context, such as its child expressions, their names (arg keys), and whether a given child expression 71 is optional or not. 72 73 Attributes: 74 key: a unique key for each class in the Expression hierarchy. This is useful for hashing 75 and representing expressions as strings. 76 arg_types: determines the arguments (child nodes) supported by an expression. It maps 77 arg keys to booleans that indicate whether the corresponding args are optional. 78 parent: a reference to the parent expression (or None, in case of root expressions). 79 arg_key: the arg key an expression is associated with, i.e. the name its parent expression 80 uses to refer to it. 81 index: the index of an expression if it is inside of a list argument in its parent. 82 comments: a list of comments that are associated with a given expression. This is used in 83 order to preserve comments when transpiling SQL code. 84 type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the 85 optimizer, in order to enable some transformations that require type information. 86 meta: a dictionary that can be used to store useful metadata for a given expression. 87 88 Example: 89 >>> class Foo(Expression): 90 ... arg_types = {"this": True, "expression": False} 91 92 The above definition informs us that Foo is an Expression that requires an argument called 93 "this" and may also optionally receive an argument called "expression". 94 95 Args: 96 args: a mapping used for retrieving the arguments of an expression, given their arg keys. 97 """ 98 99 key = "expression" 100 arg_types = {"this": True} 101 __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash") 102 103 def __init__(self, **args: t.Any): 104 self.args: t.Dict[str, t.Any] = args 105 self.parent: t.Optional[Expression] = None 106 self.arg_key: t.Optional[str] = None 107 self.index: t.Optional[int] = None 108 self.comments: t.Optional[t.List[str]] = None 109 self._type: t.Optional[DataType] = None 110 self._meta: t.Optional[t.Dict[str, t.Any]] = None 111 self._hash: t.Optional[int] = None 112 113 for arg_key, value in self.args.items(): 114 self._set_parent(arg_key, value) 115 116 def __eq__(self, other) -> bool: 117 return type(self) is type(other) and hash(self) == hash(other) 118 119 @property 120 def hashable_args(self) -> t.Any: 121 return frozenset( 122 (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v)) 123 for k, v in self.args.items() 124 if not (v is None or v is False or (type(v) is list and not v)) 125 ) 126 127 def __hash__(self) -> int: 128 if self._hash is not None: 129 return self._hash 130 131 return hash((self.__class__, self.hashable_args)) 132 133 @property 134 def this(self) -> t.Any: 135 """ 136 Retrieves the argument with key "this". 137 """ 138 return self.args.get("this") 139 140 @property 141 def expression(self) -> t.Any: 142 """ 143 Retrieves the argument with key "expression". 144 """ 145 return self.args.get("expression") 146 147 @property 148 def expressions(self) -> t.List[t.Any]: 149 """ 150 Retrieves the argument with key "expressions". 151 """ 152 return self.args.get("expressions") or [] 153 154 def text(self, key) -> str: 155 """ 156 Returns a textual representation of the argument corresponding to "key". This can only be used 157 for args that are strings or leaf Expression instances, such as identifiers and literals. 158 """ 159 field = self.args.get(key) 160 if isinstance(field, str): 161 return field 162 if isinstance(field, (Identifier, Literal, Var)): 163 return field.this 164 if isinstance(field, (Star, Null)): 165 return field.name 166 return "" 167 168 @property 169 def is_string(self) -> bool: 170 """ 171 Checks whether a Literal expression is a string. 172 """ 173 return isinstance(self, Literal) and self.args["is_string"] 174 175 @property 176 def is_number(self) -> bool: 177 """ 178 Checks whether a Literal expression is a number. 179 """ 180 return (isinstance(self, Literal) and not self.args["is_string"]) or ( 181 isinstance(self, Neg) and self.this.is_number 182 ) 183 184 def to_py(self) -> t.Any: 185 """ 186 Returns a Python object equivalent of the SQL node. 187 """ 188 raise ValueError(f"{self} cannot be converted to a Python object.") 189 190 @property 191 def is_int(self) -> bool: 192 """ 193 Checks whether an expression is an integer. 194 """ 195 return self.is_number and isinstance(self.to_py(), int) 196 197 @property 198 def is_star(self) -> bool: 199 """Checks whether an expression is a star.""" 200 return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star)) 201 202 @property 203 def alias(self) -> str: 204 """ 205 Returns the alias of the expression, or an empty string if it's not aliased. 206 """ 207 if isinstance(self.args.get("alias"), TableAlias): 208 return self.args["alias"].name 209 return self.text("alias") 210 211 @property 212 def alias_column_names(self) -> t.List[str]: 213 table_alias = self.args.get("alias") 214 if not table_alias: 215 return [] 216 return [c.name for c in table_alias.args.get("columns") or []] 217 218 @property 219 def name(self) -> str: 220 return self.text("this") 221 222 @property 223 def alias_or_name(self) -> str: 224 return self.alias or self.name 225 226 @property 227 def output_name(self) -> str: 228 """ 229 Name of the output column if this expression is a selection. 230 231 If the Expression has no output name, an empty string is returned. 232 233 Example: 234 >>> from sqlglot import parse_one 235 >>> parse_one("SELECT a").expressions[0].output_name 236 'a' 237 >>> parse_one("SELECT b AS c").expressions[0].output_name 238 'c' 239 >>> parse_one("SELECT 1 + 2").expressions[0].output_name 240 '' 241 """ 242 return "" 243 244 @property 245 def type(self) -> t.Optional[DataType]: 246 return self._type 247 248 @type.setter 249 def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None: 250 if dtype and not isinstance(dtype, DataType): 251 dtype = DataType.build(dtype) 252 self._type = dtype # type: ignore 253 254 def is_type(self, *dtypes) -> bool: 255 return self.type is not None and self.type.is_type(*dtypes) 256 257 def is_leaf(self) -> bool: 258 return not any(isinstance(v, (Expression, list)) for v in self.args.values()) 259 260 @property 261 def meta(self) -> t.Dict[str, t.Any]: 262 if self._meta is None: 263 self._meta = {} 264 return self._meta 265 266 def __deepcopy__(self, memo): 267 root = self.__class__() 268 stack = [(self, root)] 269 270 while stack: 271 node, copy = stack.pop() 272 273 if node.comments is not None: 274 copy.comments = deepcopy(node.comments) 275 if node._type is not None: 276 copy._type = deepcopy(node._type) 277 if node._meta is not None: 278 copy._meta = deepcopy(node._meta) 279 if node._hash is not None: 280 copy._hash = node._hash 281 282 for k, vs in node.args.items(): 283 if hasattr(vs, "parent"): 284 stack.append((vs, vs.__class__())) 285 copy.set(k, stack[-1][-1]) 286 elif type(vs) is list: 287 copy.args[k] = [] 288 289 for v in vs: 290 if hasattr(v, "parent"): 291 stack.append((v, v.__class__())) 292 copy.append(k, stack[-1][-1]) 293 else: 294 copy.append(k, v) 295 else: 296 copy.args[k] = vs 297 298 return root 299 300 def copy(self) -> Self: 301 """ 302 Returns a deep copy of the expression. 303 """ 304 return deepcopy(self) 305 306 def add_comments(self, comments: t.Optional[t.List[str]] = None, prepend: bool = False) -> None: 307 if self.comments is None: 308 self.comments = [] 309 310 if comments: 311 for comment in comments: 312 _, *meta = comment.split(SQLGLOT_META) 313 if meta: 314 for kv in "".join(meta).split(","): 315 k, *v = kv.split("=") 316 value = v[0].strip() if v else True 317 self.meta[k.strip()] = to_bool(value) 318 319 if not prepend: 320 self.comments.append(comment) 321 322 if prepend: 323 self.comments = comments + self.comments 324 325 def pop_comments(self) -> t.List[str]: 326 comments = self.comments or [] 327 self.comments = None 328 return comments 329 330 def append(self, arg_key: str, value: t.Any) -> None: 331 """ 332 Appends value to arg_key if it's a list or sets it as a new list. 333 334 Args: 335 arg_key (str): name of the list expression arg 336 value (Any): value to append to the list 337 """ 338 if type(self.args.get(arg_key)) is not list: 339 self.args[arg_key] = [] 340 self._set_parent(arg_key, value) 341 values = self.args[arg_key] 342 if hasattr(value, "parent"): 343 value.index = len(values) 344 values.append(value) 345 346 def set( 347 self, 348 arg_key: str, 349 value: t.Any, 350 index: t.Optional[int] = None, 351 overwrite: bool = True, 352 ) -> None: 353 """ 354 Sets arg_key to value. 355 356 Args: 357 arg_key: name of the expression arg. 358 value: value to set the arg to. 359 index: if the arg is a list, this specifies what position to add the value in it. 360 overwrite: assuming an index is given, this determines whether to overwrite the 361 list entry instead of only inserting a new value (i.e., like list.insert). 362 """ 363 if index is not None: 364 expressions = self.args.get(arg_key) or [] 365 366 if seq_get(expressions, index) is None: 367 return 368 if value is None: 369 expressions.pop(index) 370 for v in expressions[index:]: 371 v.index = v.index - 1 372 return 373 374 if isinstance(value, list): 375 expressions.pop(index) 376 expressions[index:index] = value 377 elif overwrite: 378 expressions[index] = value 379 else: 380 expressions.insert(index, value) 381 382 value = expressions 383 elif value is None: 384 self.args.pop(arg_key, None) 385 return 386 387 self.args[arg_key] = value 388 self._set_parent(arg_key, value, index) 389 390 def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None: 391 if hasattr(value, "parent"): 392 value.parent = self 393 value.arg_key = arg_key 394 value.index = index 395 elif type(value) is list: 396 for index, v in enumerate(value): 397 if hasattr(v, "parent"): 398 v.parent = self 399 v.arg_key = arg_key 400 v.index = index 401 402 @property 403 def depth(self) -> int: 404 """ 405 Returns the depth of this tree. 406 """ 407 if self.parent: 408 return self.parent.depth + 1 409 return 0 410 411 def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]: 412 """Yields the key and expression for all arguments, exploding list args.""" 413 # remove tuple when python 3.7 is deprecated 414 for vs in reversed(tuple(self.args.values())) if reverse else self.args.values(): # type: ignore 415 if type(vs) is list: 416 for v in reversed(vs) if reverse else vs: # type: ignore 417 if hasattr(v, "parent"): 418 yield v 419 else: 420 if hasattr(vs, "parent"): 421 yield vs 422 423 def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]: 424 """ 425 Returns the first node in this tree which matches at least one of 426 the specified types. 427 428 Args: 429 expression_types: the expression type(s) to match. 430 bfs: whether to search the AST using the BFS algorithm (DFS is used if false). 431 432 Returns: 433 The node which matches the criteria or None if no such node was found. 434 """ 435 return next(self.find_all(*expression_types, bfs=bfs), None) 436 437 def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]: 438 """ 439 Returns a generator object which visits all nodes in this tree and only 440 yields those that match at least one of the specified expression types. 441 442 Args: 443 expression_types: the expression type(s) to match. 444 bfs: whether to search the AST using the BFS algorithm (DFS is used if false). 445 446 Returns: 447 The generator object. 448 """ 449 for expression in self.walk(bfs=bfs): 450 if isinstance(expression, expression_types): 451 yield expression 452 453 def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]: 454 """ 455 Returns a nearest parent matching expression_types. 456 457 Args: 458 expression_types: the expression type(s) to match. 459 460 Returns: 461 The parent node. 462 """ 463 ancestor = self.parent 464 while ancestor and not isinstance(ancestor, expression_types): 465 ancestor = ancestor.parent 466 return ancestor # type: ignore 467 468 @property 469 def parent_select(self) -> t.Optional[Select]: 470 """ 471 Returns the parent select statement. 472 """ 473 return self.find_ancestor(Select) 474 475 @property 476 def same_parent(self) -> bool: 477 """Returns if the parent is the same class as itself.""" 478 return type(self.parent) is self.__class__ 479 480 def root(self) -> Expression: 481 """ 482 Returns the root expression of this tree. 483 """ 484 expression = self 485 while expression.parent: 486 expression = expression.parent 487 return expression 488 489 def walk( 490 self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None 491 ) -> t.Iterator[Expression]: 492 """ 493 Returns a generator object which visits all nodes in this tree. 494 495 Args: 496 bfs: if set to True the BFS traversal order will be applied, 497 otherwise the DFS traversal will be used instead. 498 prune: callable that returns True if the generator should stop traversing 499 this branch of the tree. 500 501 Returns: 502 the generator object. 503 """ 504 if bfs: 505 yield from self.bfs(prune=prune) 506 else: 507 yield from self.dfs(prune=prune) 508 509 def dfs( 510 self, prune: t.Optional[t.Callable[[Expression], bool]] = None 511 ) -> t.Iterator[Expression]: 512 """ 513 Returns a generator object which visits all nodes in this tree in 514 the DFS (Depth-first) order. 515 516 Returns: 517 The generator object. 518 """ 519 stack = [self] 520 521 while stack: 522 node = stack.pop() 523 524 yield node 525 526 if prune and prune(node): 527 continue 528 529 for v in node.iter_expressions(reverse=True): 530 stack.append(v) 531 532 def bfs( 533 self, prune: t.Optional[t.Callable[[Expression], bool]] = None 534 ) -> t.Iterator[Expression]: 535 """ 536 Returns a generator object which visits all nodes in this tree in 537 the BFS (Breadth-first) order. 538 539 Returns: 540 The generator object. 541 """ 542 queue = deque([self]) 543 544 while queue: 545 node = queue.popleft() 546 547 yield node 548 549 if prune and prune(node): 550 continue 551 552 for v in node.iter_expressions(): 553 queue.append(v) 554 555 def unnest(self): 556 """ 557 Returns the first non parenthesis child or self. 558 """ 559 expression = self 560 while type(expression) is Paren: 561 expression = expression.this 562 return expression 563 564 def unalias(self): 565 """ 566 Returns the inner expression if this is an Alias. 567 """ 568 if isinstance(self, Alias): 569 return self.this 570 return self 571 572 def unnest_operands(self): 573 """ 574 Returns unnested operands as a tuple. 575 """ 576 return tuple(arg.unnest() for arg in self.iter_expressions()) 577 578 def flatten(self, unnest=True): 579 """ 580 Returns a generator which yields child nodes whose parents are the same class. 581 582 A AND B AND C -> [A, B, C] 583 """ 584 for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__): 585 if type(node) is not self.__class__: 586 yield node.unnest() if unnest and not isinstance(node, Subquery) else node 587 588 def __str__(self) -> str: 589 return self.sql() 590 591 def __repr__(self) -> str: 592 return _to_s(self) 593 594 def to_s(self) -> str: 595 """ 596 Same as __repr__, but includes additional information which can be useful 597 for debugging, like empty or missing args and the AST nodes' object IDs. 598 """ 599 return _to_s(self, verbose=True) 600 601 def sql(self, dialect: DialectType = None, **opts) -> str: 602 """ 603 Returns SQL string representation of this tree. 604 605 Args: 606 dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql"). 607 opts: other `sqlglot.generator.Generator` options. 608 609 Returns: 610 The SQL string. 611 """ 612 from sqlglot.dialects import Dialect 613 614 return Dialect.get_or_raise(dialect).generate(self, **opts) 615 616 def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression: 617 """ 618 Visits all tree nodes (excluding already transformed ones) 619 and applies the given transformation function to each node. 620 621 Args: 622 fun: a function which takes a node as an argument and returns a 623 new transformed node or the same node without modifications. If the function 624 returns None, then the corresponding node will be removed from the syntax tree. 625 copy: if set to True a new tree instance is constructed, otherwise the tree is 626 modified in place. 627 628 Returns: 629 The transformed tree. 630 """ 631 root = None 632 new_node = None 633 634 for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node): 635 parent, arg_key, index = node.parent, node.arg_key, node.index 636 new_node = fun(node, *args, **kwargs) 637 638 if not root: 639 root = new_node 640 elif new_node is not node: 641 parent.set(arg_key, new_node, index) 642 643 assert root 644 return root.assert_is(Expression) 645 646 @t.overload 647 def replace(self, expression: E) -> E: ... 648 649 @t.overload 650 def replace(self, expression: None) -> None: ... 651 652 def replace(self, expression): 653 """ 654 Swap out this expression with a new expression. 655 656 For example:: 657 658 >>> tree = Select().select("x").from_("tbl") 659 >>> tree.find(Column).replace(column("y")) 660 Column( 661 this=Identifier(this=y, quoted=False)) 662 >>> tree.sql() 663 'SELECT y FROM tbl' 664 665 Args: 666 expression: new node 667 668 Returns: 669 The new expression or expressions. 670 """ 671 parent = self.parent 672 673 if not parent or parent is expression: 674 return expression 675 676 key = self.arg_key 677 value = parent.args.get(key) 678 679 if type(expression) is list and isinstance(value, Expression): 680 # We are trying to replace an Expression with a list, so it's assumed that 681 # the intention was to really replace the parent of this expression. 682 value.parent.replace(expression) 683 else: 684 parent.set(key, expression, self.index) 685 686 if expression is not self: 687 self.parent = None 688 self.arg_key = None 689 self.index = None 690 691 return expression 692 693 def pop(self: E) -> E: 694 """ 695 Remove this expression from its AST. 696 697 Returns: 698 The popped expression. 699 """ 700 self.replace(None) 701 return self 702 703 def assert_is(self, type_: t.Type[E]) -> E: 704 """ 705 Assert that this `Expression` is an instance of `type_`. 706 707 If it is NOT an instance of `type_`, this raises an assertion error. 708 Otherwise, this returns this expression. 709 710 Examples: 711 This is useful for type security in chained expressions: 712 713 >>> import sqlglot 714 >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql() 715 'SELECT x, z FROM y' 716 """ 717 if not isinstance(self, type_): 718 raise AssertionError(f"{self} is not {type_}.") 719 return self 720 721 def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]: 722 """ 723 Checks if this expression is valid (e.g. all mandatory args are set). 724 725 Args: 726 args: a sequence of values that were used to instantiate a Func expression. This is used 727 to check that the provided arguments don't exceed the function argument limit. 728 729 Returns: 730 A list of error messages for all possible errors that were found. 731 """ 732 errors: t.List[str] = [] 733 734 for k in self.args: 735 if k not in self.arg_types: 736 errors.append(f"Unexpected keyword: '{k}' for {self.__class__}") 737 for k, mandatory in self.arg_types.items(): 738 v = self.args.get(k) 739 if mandatory and (v is None or (isinstance(v, list) and not v)): 740 errors.append(f"Required keyword: '{k}' missing for {self.__class__}") 741 742 if ( 743 args 744 and isinstance(self, Func) 745 and len(args) > len(self.arg_types) 746 and not self.is_var_len_args 747 ): 748 errors.append( 749 f"The number of provided arguments ({len(args)}) is greater than " 750 f"the maximum number of supported arguments ({len(self.arg_types)})" 751 ) 752 753 return errors 754 755 def dump(self): 756 """ 757 Dump this Expression to a JSON-serializable dict. 758 """ 759 from sqlglot.serde import dump 760 761 return dump(self) 762 763 @classmethod 764 def load(cls, obj): 765 """ 766 Load a dict (as returned by `Expression.dump`) into an Expression instance. 767 """ 768 from sqlglot.serde import load 769 770 return load(obj) 771 772 def and_( 773 self, 774 *expressions: t.Optional[ExpOrStr], 775 dialect: DialectType = None, 776 copy: bool = True, 777 wrap: bool = True, 778 **opts, 779 ) -> Condition: 780 """ 781 AND this condition with one or multiple expressions. 782 783 Example: 784 >>> condition("x=1").and_("y=1").sql() 785 'x = 1 AND y = 1' 786 787 Args: 788 *expressions: the SQL code strings to parse. 789 If an `Expression` instance is passed, it will be used as-is. 790 dialect: the dialect used to parse the input expression. 791 copy: whether to copy the involved expressions (only applies to Expressions). 792 wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid 793 precedence issues, but can be turned off when the produced AST is too deep and 794 causes recursion-related issues. 795 opts: other options to use to parse the input expressions. 796 797 Returns: 798 The new And condition. 799 """ 800 return and_(self, *expressions, dialect=dialect, copy=copy, wrap=wrap, **opts) 801 802 def or_( 803 self, 804 *expressions: t.Optional[ExpOrStr], 805 dialect: DialectType = None, 806 copy: bool = True, 807 wrap: bool = True, 808 **opts, 809 ) -> Condition: 810 """ 811 OR this condition with one or multiple expressions. 812 813 Example: 814 >>> condition("x=1").or_("y=1").sql() 815 'x = 1 OR y = 1' 816 817 Args: 818 *expressions: the SQL code strings to parse. 819 If an `Expression` instance is passed, it will be used as-is. 820 dialect: the dialect used to parse the input expression. 821 copy: whether to copy the involved expressions (only applies to Expressions). 822 wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid 823 precedence issues, but can be turned off when the produced AST is too deep and 824 causes recursion-related issues. 825 opts: other options to use to parse the input expressions. 826 827 Returns: 828 The new Or condition. 829 """ 830 return or_(self, *expressions, dialect=dialect, copy=copy, wrap=wrap, **opts) 831 832 def not_(self, copy: bool = True): 833 """ 834 Wrap this condition with NOT. 835 836 Example: 837 >>> condition("x=1").not_().sql() 838 'NOT x = 1' 839 840 Args: 841 copy: whether to copy this object. 842 843 Returns: 844 The new Not instance. 845 """ 846 return not_(self, copy=copy) 847 848 def as_( 849 self, 850 alias: str | Identifier, 851 quoted: t.Optional[bool] = None, 852 dialect: DialectType = None, 853 copy: bool = True, 854 **opts, 855 ) -> Alias: 856 return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts) 857 858 def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E: 859 this = self.copy() 860 other = convert(other, copy=True) 861 if not isinstance(this, klass) and not isinstance(other, klass): 862 this = _wrap(this, Binary) 863 other = _wrap(other, Binary) 864 if reverse: 865 return klass(this=other, expression=this) 866 return klass(this=this, expression=other) 867 868 def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket: 869 return Bracket( 870 this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)] 871 ) 872 873 def __iter__(self) -> t.Iterator: 874 if "expressions" in self.arg_types: 875 return iter(self.args.get("expressions") or []) 876 # We define this because __getitem__ converts Expression into an iterable, which is 877 # problematic because one can hit infinite loops if they do "for x in some_expr: ..." 878 # See: https://peps.python.org/pep-0234/ 879 raise TypeError(f"'{self.__class__.__name__}' object is not iterable") 880 881 def isin( 882 self, 883 *expressions: t.Any, 884 query: t.Optional[ExpOrStr] = None, 885 unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None, 886 copy: bool = True, 887 **opts, 888 ) -> In: 889 subquery = maybe_parse(query, copy=copy, **opts) if query else None 890 if subquery and not isinstance(subquery, Subquery): 891 subquery = subquery.subquery(copy=False) 892 893 return In( 894 this=maybe_copy(self, copy), 895 expressions=[convert(e, copy=copy) for e in expressions], 896 query=subquery, 897 unnest=( 898 Unnest( 899 expressions=[ 900 maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) 901 for e in ensure_list(unnest) 902 ] 903 ) 904 if unnest 905 else None 906 ), 907 ) 908 909 def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between: 910 return Between( 911 this=maybe_copy(self, copy), 912 low=convert(low, copy=copy, **opts), 913 high=convert(high, copy=copy, **opts), 914 ) 915 916 def is_(self, other: ExpOrStr) -> Is: 917 return self._binop(Is, other) 918 919 def like(self, other: ExpOrStr) -> Like: 920 return self._binop(Like, other) 921 922 def ilike(self, other: ExpOrStr) -> ILike: 923 return self._binop(ILike, other) 924 925 def eq(self, other: t.Any) -> EQ: 926 return self._binop(EQ, other) 927 928 def neq(self, other: t.Any) -> NEQ: 929 return self._binop(NEQ, other) 930 931 def rlike(self, other: ExpOrStr) -> RegexpLike: 932 return self._binop(RegexpLike, other) 933 934 def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div: 935 div = self._binop(Div, other) 936 div.args["typed"] = typed 937 div.args["safe"] = safe 938 return div 939 940 def asc(self, nulls_first: bool = True) -> Ordered: 941 return Ordered(this=self.copy(), nulls_first=nulls_first) 942 943 def desc(self, nulls_first: bool = False) -> Ordered: 944 return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first) 945 946 def __lt__(self, other: t.Any) -> LT: 947 return self._binop(LT, other) 948 949 def __le__(self, other: t.Any) -> LTE: 950 return self._binop(LTE, other) 951 952 def __gt__(self, other: t.Any) -> GT: 953 return self._binop(GT, other) 954 955 def __ge__(self, other: t.Any) -> GTE: 956 return self._binop(GTE, other) 957 958 def __add__(self, other: t.Any) -> Add: 959 return self._binop(Add, other) 960 961 def __radd__(self, other: t.Any) -> Add: 962 return self._binop(Add, other, reverse=True) 963 964 def __sub__(self, other: t.Any) -> Sub: 965 return self._binop(Sub, other) 966 967 def __rsub__(self, other: t.Any) -> Sub: 968 return self._binop(Sub, other, reverse=True) 969 970 def __mul__(self, other: t.Any) -> Mul: 971 return self._binop(Mul, other) 972 973 def __rmul__(self, other: t.Any) -> Mul: 974 return self._binop(Mul, other, reverse=True) 975 976 def __truediv__(self, other: t.Any) -> Div: 977 return self._binop(Div, other) 978 979 def __rtruediv__(self, other: t.Any) -> Div: 980 return self._binop(Div, other, reverse=True) 981 982 def __floordiv__(self, other: t.Any) -> IntDiv: 983 return self._binop(IntDiv, other) 984 985 def __rfloordiv__(self, other: t.Any) -> IntDiv: 986 return self._binop(IntDiv, other, reverse=True) 987 988 def __mod__(self, other: t.Any) -> Mod: 989 return self._binop(Mod, other) 990 991 def __rmod__(self, other: t.Any) -> Mod: 992 return self._binop(Mod, other, reverse=True) 993 994 def __pow__(self, other: t.Any) -> Pow: 995 return self._binop(Pow, other) 996 997 def __rpow__(self, other: t.Any) -> Pow: 998 return self._binop(Pow, other, reverse=True) 999 1000 def __and__(self, other: t.Any) -> And: 1001 return self._binop(And, other) 1002 1003 def __rand__(self, other: t.Any) -> And: 1004 return self._binop(And, other, reverse=True) 1005 1006 def __or__(self, other: t.Any) -> Or: 1007 return self._binop(Or, other) 1008 1009 def __ror__(self, other: t.Any) -> Or: 1010 return self._binop(Or, other, reverse=True) 1011 1012 def __neg__(self) -> Neg: 1013 return Neg(this=_wrap(self.copy(), Binary)) 1014 1015 def __invert__(self) -> Not: 1016 return not_(self.copy()) 1017 1018 1019IntoType = t.Union[ 1020 str, 1021 t.Type[Expression], 1022 t.Collection[t.Union[str, t.Type[Expression]]], 1023] 1024ExpOrStr = t.Union[str, Expression] 1025 1026 1027class Condition(Expression): 1028 """Logical conditions like x AND y, or simply x""" 1029 1030 1031class Predicate(Condition): 1032 """Relationships like x = y, x > 1, x >= y.""" 1033 1034 1035class DerivedTable(Expression): 1036 @property 1037 def selects(self) -> t.List[Expression]: 1038 return self.this.selects if isinstance(self.this, Query) else [] 1039 1040 @property 1041 def named_selects(self) -> t.List[str]: 1042 return [select.output_name for select in self.selects] 1043 1044 1045class Query(Expression): 1046 def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery: 1047 """ 1048 Returns a `Subquery` that wraps around this query. 1049 1050 Example: 1051 >>> subquery = Select().select("x").from_("tbl").subquery() 1052 >>> Select().select("x").from_(subquery).sql() 1053 'SELECT x FROM (SELECT x FROM tbl)' 1054 1055 Args: 1056 alias: an optional alias for the subquery. 1057 copy: if `False`, modify this expression instance in-place. 1058 """ 1059 instance = maybe_copy(self, copy) 1060 if not isinstance(alias, Expression): 1061 alias = TableAlias(this=to_identifier(alias)) if alias else None 1062 1063 return Subquery(this=instance, alias=alias) 1064 1065 def limit( 1066 self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts 1067 ) -> Q: 1068 """ 1069 Adds a LIMIT clause to this query. 1070 1071 Example: 1072 >>> select("1").union(select("1")).limit(1).sql() 1073 'SELECT 1 UNION SELECT 1 LIMIT 1' 1074 1075 Args: 1076 expression: the SQL code string to parse. 1077 This can also be an integer. 1078 If a `Limit` instance is passed, it will be used as-is. 1079 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1080 dialect: the dialect used to parse the input expression. 1081 copy: if `False`, modify this expression instance in-place. 1082 opts: other options to use to parse the input expressions. 1083 1084 Returns: 1085 A limited Select expression. 1086 """ 1087 return _apply_builder( 1088 expression=expression, 1089 instance=self, 1090 arg="limit", 1091 into=Limit, 1092 prefix="LIMIT", 1093 dialect=dialect, 1094 copy=copy, 1095 into_arg="expression", 1096 **opts, 1097 ) 1098 1099 def offset( 1100 self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts 1101 ) -> Q: 1102 """ 1103 Set the OFFSET expression. 1104 1105 Example: 1106 >>> Select().from_("tbl").select("x").offset(10).sql() 1107 'SELECT x FROM tbl OFFSET 10' 1108 1109 Args: 1110 expression: the SQL code string to parse. 1111 This can also be an integer. 1112 If a `Offset` instance is passed, this is used as-is. 1113 If another `Expression` instance is passed, it will be wrapped in a `Offset`. 1114 dialect: the dialect used to parse the input expression. 1115 copy: if `False`, modify this expression instance in-place. 1116 opts: other options to use to parse the input expressions. 1117 1118 Returns: 1119 The modified Select expression. 1120 """ 1121 return _apply_builder( 1122 expression=expression, 1123 instance=self, 1124 arg="offset", 1125 into=Offset, 1126 prefix="OFFSET", 1127 dialect=dialect, 1128 copy=copy, 1129 into_arg="expression", 1130 **opts, 1131 ) 1132 1133 def order_by( 1134 self: Q, 1135 *expressions: t.Optional[ExpOrStr], 1136 append: bool = True, 1137 dialect: DialectType = None, 1138 copy: bool = True, 1139 **opts, 1140 ) -> Q: 1141 """ 1142 Set the ORDER BY expression. 1143 1144 Example: 1145 >>> Select().from_("tbl").select("x").order_by("x DESC").sql() 1146 'SELECT x FROM tbl ORDER BY x DESC' 1147 1148 Args: 1149 *expressions: the SQL code strings to parse. 1150 If a `Group` instance is passed, this is used as-is. 1151 If another `Expression` instance is passed, it will be wrapped in a `Order`. 1152 append: if `True`, add to any existing expressions. 1153 Otherwise, this flattens all the `Order` expression into a single expression. 1154 dialect: the dialect used to parse the input expression. 1155 copy: if `False`, modify this expression instance in-place. 1156 opts: other options to use to parse the input expressions. 1157 1158 Returns: 1159 The modified Select expression. 1160 """ 1161 return _apply_child_list_builder( 1162 *expressions, 1163 instance=self, 1164 arg="order", 1165 append=append, 1166 copy=copy, 1167 prefix="ORDER BY", 1168 into=Order, 1169 dialect=dialect, 1170 **opts, 1171 ) 1172 1173 @property 1174 def ctes(self) -> t.List[CTE]: 1175 """Returns a list of all the CTEs attached to this query.""" 1176 with_ = self.args.get("with") 1177 return with_.expressions if with_ else [] 1178 1179 @property 1180 def selects(self) -> t.List[Expression]: 1181 """Returns the query's projections.""" 1182 raise NotImplementedError("Query objects must implement `selects`") 1183 1184 @property 1185 def named_selects(self) -> t.List[str]: 1186 """Returns the output names of the query's projections.""" 1187 raise NotImplementedError("Query objects must implement `named_selects`") 1188 1189 def select( 1190 self: Q, 1191 *expressions: t.Optional[ExpOrStr], 1192 append: bool = True, 1193 dialect: DialectType = None, 1194 copy: bool = True, 1195 **opts, 1196 ) -> Q: 1197 """ 1198 Append to or set the SELECT expressions. 1199 1200 Example: 1201 >>> Select().select("x", "y").sql() 1202 'SELECT x, y' 1203 1204 Args: 1205 *expressions: the SQL code strings to parse. 1206 If an `Expression` instance is passed, it will be used as-is. 1207 append: if `True`, add to any existing expressions. 1208 Otherwise, this resets the expressions. 1209 dialect: the dialect used to parse the input expressions. 1210 copy: if `False`, modify this expression instance in-place. 1211 opts: other options to use to parse the input expressions. 1212 1213 Returns: 1214 The modified Query expression. 1215 """ 1216 raise NotImplementedError("Query objects must implement `select`") 1217 1218 def with_( 1219 self: Q, 1220 alias: ExpOrStr, 1221 as_: ExpOrStr, 1222 recursive: t.Optional[bool] = None, 1223 materialized: t.Optional[bool] = None, 1224 append: bool = True, 1225 dialect: DialectType = None, 1226 copy: bool = True, 1227 **opts, 1228 ) -> Q: 1229 """ 1230 Append to or set the common table expressions. 1231 1232 Example: 1233 >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql() 1234 'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2' 1235 1236 Args: 1237 alias: the SQL code string to parse as the table name. 1238 If an `Expression` instance is passed, this is used as-is. 1239 as_: the SQL code string to parse as the table expression. 1240 If an `Expression` instance is passed, it will be used as-is. 1241 recursive: set the RECURSIVE part of the expression. Defaults to `False`. 1242 materialized: set the MATERIALIZED part of the expression. 1243 append: if `True`, add to any existing expressions. 1244 Otherwise, this resets the expressions. 1245 dialect: the dialect used to parse the input expression. 1246 copy: if `False`, modify this expression instance in-place. 1247 opts: other options to use to parse the input expressions. 1248 1249 Returns: 1250 The modified expression. 1251 """ 1252 return _apply_cte_builder( 1253 self, 1254 alias, 1255 as_, 1256 recursive=recursive, 1257 materialized=materialized, 1258 append=append, 1259 dialect=dialect, 1260 copy=copy, 1261 **opts, 1262 ) 1263 1264 def union( 1265 self, *expressions: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts 1266 ) -> Union: 1267 """ 1268 Builds a UNION expression. 1269 1270 Example: 1271 >>> import sqlglot 1272 >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql() 1273 'SELECT * FROM foo UNION SELECT * FROM bla' 1274 1275 Args: 1276 expressions: the SQL code strings. 1277 If `Expression` instances are passed, they will be used as-is. 1278 distinct: set the DISTINCT flag if and only if this is true. 1279 dialect: the dialect used to parse the input expression. 1280 opts: other options to use to parse the input expressions. 1281 1282 Returns: 1283 The new Union expression. 1284 """ 1285 return union(self, *expressions, distinct=distinct, dialect=dialect, **opts) 1286 1287 def intersect( 1288 self, *expressions: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts 1289 ) -> Intersect: 1290 """ 1291 Builds an INTERSECT expression. 1292 1293 Example: 1294 >>> import sqlglot 1295 >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql() 1296 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 1297 1298 Args: 1299 expressions: the SQL code strings. 1300 If `Expression` instances are passed, they will be used as-is. 1301 distinct: set the DISTINCT flag if and only if this is true. 1302 dialect: the dialect used to parse the input expression. 1303 opts: other options to use to parse the input expressions. 1304 1305 Returns: 1306 The new Intersect expression. 1307 """ 1308 return intersect(self, *expressions, distinct=distinct, dialect=dialect, **opts) 1309 1310 def except_( 1311 self, *expressions: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts 1312 ) -> Except: 1313 """ 1314 Builds an EXCEPT expression. 1315 1316 Example: 1317 >>> import sqlglot 1318 >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql() 1319 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 1320 1321 Args: 1322 expressions: the SQL code strings. 1323 If `Expression` instance are passed, they will be used as-is. 1324 distinct: set the DISTINCT flag if and only if this is true. 1325 dialect: the dialect used to parse the input expression. 1326 opts: other options to use to parse the input expressions. 1327 1328 Returns: 1329 The new Except expression. 1330 """ 1331 return except_(self, *expressions, distinct=distinct, dialect=dialect, **opts) 1332 1333 1334class UDTF(DerivedTable): 1335 @property 1336 def selects(self) -> t.List[Expression]: 1337 alias = self.args.get("alias") 1338 return alias.columns if alias else [] 1339 1340 1341class Cache(Expression): 1342 arg_types = { 1343 "this": True, 1344 "lazy": False, 1345 "options": False, 1346 "expression": False, 1347 } 1348 1349 1350class Uncache(Expression): 1351 arg_types = {"this": True, "exists": False} 1352 1353 1354class Refresh(Expression): 1355 pass 1356 1357 1358class DDL(Expression): 1359 @property 1360 def ctes(self) -> t.List[CTE]: 1361 """Returns a list of all the CTEs attached to this statement.""" 1362 with_ = self.args.get("with") 1363 return with_.expressions if with_ else [] 1364 1365 @property 1366 def selects(self) -> t.List[Expression]: 1367 """If this statement contains a query (e.g. a CTAS), this returns the query's projections.""" 1368 return self.expression.selects if isinstance(self.expression, Query) else [] 1369 1370 @property 1371 def named_selects(self) -> t.List[str]: 1372 """ 1373 If this statement contains a query (e.g. a CTAS), this returns the output 1374 names of the query's projections. 1375 """ 1376 return self.expression.named_selects if isinstance(self.expression, Query) else [] 1377 1378 1379class DML(Expression): 1380 def returning( 1381 self, 1382 expression: ExpOrStr, 1383 dialect: DialectType = None, 1384 copy: bool = True, 1385 **opts, 1386 ) -> "Self": 1387 """ 1388 Set the RETURNING expression. Not supported by all dialects. 1389 1390 Example: 1391 >>> delete("tbl").returning("*", dialect="postgres").sql() 1392 'DELETE FROM tbl RETURNING *' 1393 1394 Args: 1395 expression: the SQL code strings to parse. 1396 If an `Expression` instance is passed, it will be used as-is. 1397 dialect: the dialect used to parse the input expressions. 1398 copy: if `False`, modify this expression instance in-place. 1399 opts: other options to use to parse the input expressions. 1400 1401 Returns: 1402 Delete: the modified expression. 1403 """ 1404 return _apply_builder( 1405 expression=expression, 1406 instance=self, 1407 arg="returning", 1408 prefix="RETURNING", 1409 dialect=dialect, 1410 copy=copy, 1411 into=Returning, 1412 **opts, 1413 ) 1414 1415 1416class Create(DDL): 1417 arg_types = { 1418 "with": False, 1419 "this": True, 1420 "kind": True, 1421 "expression": False, 1422 "exists": False, 1423 "properties": False, 1424 "replace": False, 1425 "refresh": False, 1426 "unique": False, 1427 "indexes": False, 1428 "no_schema_binding": False, 1429 "begin": False, 1430 "end": False, 1431 "clone": False, 1432 "concurrently": False, 1433 "clustered": False, 1434 } 1435 1436 @property 1437 def kind(self) -> t.Optional[str]: 1438 kind = self.args.get("kind") 1439 return kind and kind.upper() 1440 1441 1442class SequenceProperties(Expression): 1443 arg_types = { 1444 "increment": False, 1445 "minvalue": False, 1446 "maxvalue": False, 1447 "cache": False, 1448 "start": False, 1449 "owned": False, 1450 "options": False, 1451 } 1452 1453 1454class TruncateTable(Expression): 1455 arg_types = { 1456 "expressions": True, 1457 "is_database": False, 1458 "exists": False, 1459 "only": False, 1460 "cluster": False, 1461 "identity": False, 1462 "option": False, 1463 "partition": False, 1464 } 1465 1466 1467# https://docs.snowflake.com/en/sql-reference/sql/create-clone 1468# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement 1469# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy 1470class Clone(Expression): 1471 arg_types = {"this": True, "shallow": False, "copy": False} 1472 1473 1474class Describe(Expression): 1475 arg_types = { 1476 "this": True, 1477 "style": False, 1478 "kind": False, 1479 "expressions": False, 1480 "partition": False, 1481 "format": False, 1482 } 1483 1484 1485# https://duckdb.org/docs/sql/statements/attach.html#attach 1486class Attach(Expression): 1487 arg_types = {"this": True, "exists": False, "expressions": False} 1488 1489 1490# https://duckdb.org/docs/sql/statements/attach.html#detach 1491class Detach(Expression): 1492 arg_types = {"this": True, "exists": False} 1493 1494 1495# https://duckdb.org/docs/guides/meta/summarize.html 1496class Summarize(Expression): 1497 arg_types = {"this": True, "table": False} 1498 1499 1500class Kill(Expression): 1501 arg_types = {"this": True, "kind": False} 1502 1503 1504class Pragma(Expression): 1505 pass 1506 1507 1508class Declare(Expression): 1509 arg_types = {"expressions": True} 1510 1511 1512class DeclareItem(Expression): 1513 arg_types = {"this": True, "kind": True, "default": False} 1514 1515 1516class Set(Expression): 1517 arg_types = {"expressions": False, "unset": False, "tag": False} 1518 1519 1520class Heredoc(Expression): 1521 arg_types = {"this": True, "tag": False} 1522 1523 1524class SetItem(Expression): 1525 arg_types = { 1526 "this": False, 1527 "expressions": False, 1528 "kind": False, 1529 "collate": False, # MySQL SET NAMES statement 1530 "global": False, 1531 } 1532 1533 1534class Show(Expression): 1535 arg_types = { 1536 "this": True, 1537 "history": False, 1538 "terse": False, 1539 "target": False, 1540 "offset": False, 1541 "starts_with": False, 1542 "limit": False, 1543 "from": False, 1544 "like": False, 1545 "where": False, 1546 "db": False, 1547 "scope": False, 1548 "scope_kind": False, 1549 "full": False, 1550 "mutex": False, 1551 "query": False, 1552 "channel": False, 1553 "global": False, 1554 "log": False, 1555 "position": False, 1556 "types": False, 1557 } 1558 1559 1560class UserDefinedFunction(Expression): 1561 arg_types = {"this": True, "expressions": False, "wrapped": False} 1562 1563 1564class CharacterSet(Expression): 1565 arg_types = {"this": True, "default": False} 1566 1567 1568class With(Expression): 1569 arg_types = {"expressions": True, "recursive": False} 1570 1571 @property 1572 def recursive(self) -> bool: 1573 return bool(self.args.get("recursive")) 1574 1575 1576class WithinGroup(Expression): 1577 arg_types = {"this": True, "expression": False} 1578 1579 1580# clickhouse supports scalar ctes 1581# https://clickhouse.com/docs/en/sql-reference/statements/select/with 1582class CTE(DerivedTable): 1583 arg_types = { 1584 "this": True, 1585 "alias": True, 1586 "scalar": False, 1587 "materialized": False, 1588 } 1589 1590 1591class ProjectionDef(Expression): 1592 arg_types = {"this": True, "expression": True} 1593 1594 1595class TableAlias(Expression): 1596 arg_types = {"this": False, "columns": False} 1597 1598 @property 1599 def columns(self): 1600 return self.args.get("columns") or [] 1601 1602 1603class BitString(Condition): 1604 pass 1605 1606 1607class HexString(Condition): 1608 pass 1609 1610 1611class ByteString(Condition): 1612 pass 1613 1614 1615class RawString(Condition): 1616 pass 1617 1618 1619class UnicodeString(Condition): 1620 arg_types = {"this": True, "escape": False} 1621 1622 1623class Column(Condition): 1624 arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False} 1625 1626 @property 1627 def table(self) -> str: 1628 return self.text("table") 1629 1630 @property 1631 def db(self) -> str: 1632 return self.text("db") 1633 1634 @property 1635 def catalog(self) -> str: 1636 return self.text("catalog") 1637 1638 @property 1639 def output_name(self) -> str: 1640 return self.name 1641 1642 @property 1643 def parts(self) -> t.List[Identifier]: 1644 """Return the parts of a column in order catalog, db, table, name.""" 1645 return [ 1646 t.cast(Identifier, self.args[part]) 1647 for part in ("catalog", "db", "table", "this") 1648 if self.args.get(part) 1649 ] 1650 1651 def to_dot(self) -> Dot | Identifier: 1652 """Converts the column into a dot expression.""" 1653 parts = self.parts 1654 parent = self.parent 1655 1656 while parent: 1657 if isinstance(parent, Dot): 1658 parts.append(parent.expression) 1659 parent = parent.parent 1660 1661 return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0] 1662 1663 1664class ColumnPosition(Expression): 1665 arg_types = {"this": False, "position": True} 1666 1667 1668class ColumnDef(Expression): 1669 arg_types = { 1670 "this": True, 1671 "kind": False, 1672 "constraints": False, 1673 "exists": False, 1674 "position": False, 1675 } 1676 1677 @property 1678 def constraints(self) -> t.List[ColumnConstraint]: 1679 return self.args.get("constraints") or [] 1680 1681 @property 1682 def kind(self) -> t.Optional[DataType]: 1683 return self.args.get("kind") 1684 1685 1686class AlterColumn(Expression): 1687 arg_types = { 1688 "this": True, 1689 "dtype": False, 1690 "collate": False, 1691 "using": False, 1692 "default": False, 1693 "drop": False, 1694 "comment": False, 1695 "allow_null": False, 1696 } 1697 1698 1699# https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html 1700class AlterDistStyle(Expression): 1701 pass 1702 1703 1704class AlterSortKey(Expression): 1705 arg_types = {"this": False, "expressions": False, "compound": False} 1706 1707 1708class AlterSet(Expression): 1709 arg_types = { 1710 "expressions": False, 1711 "option": False, 1712 "tablespace": False, 1713 "access_method": False, 1714 "file_format": False, 1715 "copy_options": False, 1716 "tag": False, 1717 "location": False, 1718 "serde": False, 1719 } 1720 1721 1722class RenameColumn(Expression): 1723 arg_types = {"this": True, "to": True, "exists": False} 1724 1725 1726class AlterRename(Expression): 1727 pass 1728 1729 1730class SwapTable(Expression): 1731 pass 1732 1733 1734class Comment(Expression): 1735 arg_types = { 1736 "this": True, 1737 "kind": True, 1738 "expression": True, 1739 "exists": False, 1740 "materialized": False, 1741 } 1742 1743 1744class Comprehension(Expression): 1745 arg_types = {"this": True, "expression": True, "iterator": True, "condition": False} 1746 1747 1748# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl 1749class MergeTreeTTLAction(Expression): 1750 arg_types = { 1751 "this": True, 1752 "delete": False, 1753 "recompress": False, 1754 "to_disk": False, 1755 "to_volume": False, 1756 } 1757 1758 1759# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl 1760class MergeTreeTTL(Expression): 1761 arg_types = { 1762 "expressions": True, 1763 "where": False, 1764 "group": False, 1765 "aggregates": False, 1766 } 1767 1768 1769# https://dev.mysql.com/doc/refman/8.0/en/create-table.html 1770class IndexConstraintOption(Expression): 1771 arg_types = { 1772 "key_block_size": False, 1773 "using": False, 1774 "parser": False, 1775 "comment": False, 1776 "visible": False, 1777 "engine_attr": False, 1778 "secondary_engine_attr": False, 1779 } 1780 1781 1782class ColumnConstraint(Expression): 1783 arg_types = {"this": False, "kind": True} 1784 1785 @property 1786 def kind(self) -> ColumnConstraintKind: 1787 return self.args["kind"] 1788 1789 1790class ColumnConstraintKind(Expression): 1791 pass 1792 1793 1794class AutoIncrementColumnConstraint(ColumnConstraintKind): 1795 pass 1796 1797 1798class PeriodForSystemTimeConstraint(ColumnConstraintKind): 1799 arg_types = {"this": True, "expression": True} 1800 1801 1802class CaseSpecificColumnConstraint(ColumnConstraintKind): 1803 arg_types = {"not_": True} 1804 1805 1806class CharacterSetColumnConstraint(ColumnConstraintKind): 1807 arg_types = {"this": True} 1808 1809 1810class CheckColumnConstraint(ColumnConstraintKind): 1811 arg_types = {"this": True, "enforced": False} 1812 1813 1814class ClusteredColumnConstraint(ColumnConstraintKind): 1815 pass 1816 1817 1818class CollateColumnConstraint(ColumnConstraintKind): 1819 pass 1820 1821 1822class CommentColumnConstraint(ColumnConstraintKind): 1823 pass 1824 1825 1826class CompressColumnConstraint(ColumnConstraintKind): 1827 arg_types = {"this": False} 1828 1829 1830class DateFormatColumnConstraint(ColumnConstraintKind): 1831 arg_types = {"this": True} 1832 1833 1834class DefaultColumnConstraint(ColumnConstraintKind): 1835 pass 1836 1837 1838class EncodeColumnConstraint(ColumnConstraintKind): 1839 pass 1840 1841 1842# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE 1843class ExcludeColumnConstraint(ColumnConstraintKind): 1844 pass 1845 1846 1847class EphemeralColumnConstraint(ColumnConstraintKind): 1848 arg_types = {"this": False} 1849 1850 1851class WithOperator(Expression): 1852 arg_types = {"this": True, "op": True} 1853 1854 1855class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind): 1856 # this: True -> ALWAYS, this: False -> BY DEFAULT 1857 arg_types = { 1858 "this": False, 1859 "expression": False, 1860 "on_null": False, 1861 "start": False, 1862 "increment": False, 1863 "minvalue": False, 1864 "maxvalue": False, 1865 "cycle": False, 1866 } 1867 1868 1869class GeneratedAsRowColumnConstraint(ColumnConstraintKind): 1870 arg_types = {"start": False, "hidden": False} 1871 1872 1873# https://dev.mysql.com/doc/refman/8.0/en/create-table.html 1874# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646 1875class IndexColumnConstraint(ColumnConstraintKind): 1876 arg_types = { 1877 "this": False, 1878 "expressions": False, 1879 "kind": False, 1880 "index_type": False, 1881 "options": False, 1882 "expression": False, # Clickhouse 1883 "granularity": False, 1884 } 1885 1886 1887class InlineLengthColumnConstraint(ColumnConstraintKind): 1888 pass 1889 1890 1891class NonClusteredColumnConstraint(ColumnConstraintKind): 1892 pass 1893 1894 1895class NotForReplicationColumnConstraint(ColumnConstraintKind): 1896 arg_types = {} 1897 1898 1899# https://docs.snowflake.com/en/sql-reference/sql/create-table 1900class MaskingPolicyColumnConstraint(ColumnConstraintKind): 1901 arg_types = {"this": True, "expressions": False} 1902 1903 1904class NotNullColumnConstraint(ColumnConstraintKind): 1905 arg_types = {"allow_null": False} 1906 1907 1908# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html 1909class OnUpdateColumnConstraint(ColumnConstraintKind): 1910 pass 1911 1912 1913# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters 1914class TransformColumnConstraint(ColumnConstraintKind): 1915 pass 1916 1917 1918class PrimaryKeyColumnConstraint(ColumnConstraintKind): 1919 arg_types = {"desc": False} 1920 1921 1922class TitleColumnConstraint(ColumnConstraintKind): 1923 pass 1924 1925 1926class UniqueColumnConstraint(ColumnConstraintKind): 1927 arg_types = {"this": False, "index_type": False, "on_conflict": False, "nulls": False} 1928 1929 1930class UppercaseColumnConstraint(ColumnConstraintKind): 1931 arg_types: t.Dict[str, t.Any] = {} 1932 1933 1934# https://docs.risingwave.com/processing/watermarks#syntax 1935class WatermarkColumnConstraint(Expression): 1936 arg_types = {"this": True, "expression": True} 1937 1938 1939class PathColumnConstraint(ColumnConstraintKind): 1940 pass 1941 1942 1943# https://docs.snowflake.com/en/sql-reference/sql/create-table 1944class ProjectionPolicyColumnConstraint(ColumnConstraintKind): 1945 pass 1946 1947 1948# computed column expression 1949# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16 1950class ComputedColumnConstraint(ColumnConstraintKind): 1951 arg_types = {"this": True, "persisted": False, "not_null": False} 1952 1953 1954class Constraint(Expression): 1955 arg_types = {"this": True, "expressions": True} 1956 1957 1958class Delete(DML): 1959 arg_types = { 1960 "with": False, 1961 "this": False, 1962 "using": False, 1963 "where": False, 1964 "returning": False, 1965 "limit": False, 1966 "tables": False, # Multiple-Table Syntax (MySQL) 1967 "cluster": False, # Clickhouse 1968 } 1969 1970 def delete( 1971 self, 1972 table: ExpOrStr, 1973 dialect: DialectType = None, 1974 copy: bool = True, 1975 **opts, 1976 ) -> Delete: 1977 """ 1978 Create a DELETE expression or replace the table on an existing DELETE expression. 1979 1980 Example: 1981 >>> delete("tbl").sql() 1982 'DELETE FROM tbl' 1983 1984 Args: 1985 table: the table from which to delete. 1986 dialect: the dialect used to parse the input expression. 1987 copy: if `False`, modify this expression instance in-place. 1988 opts: other options to use to parse the input expressions. 1989 1990 Returns: 1991 Delete: the modified expression. 1992 """ 1993 return _apply_builder( 1994 expression=table, 1995 instance=self, 1996 arg="this", 1997 dialect=dialect, 1998 into=Table, 1999 copy=copy, 2000 **opts, 2001 ) 2002 2003 def where( 2004 self, 2005 *expressions: t.Optional[ExpOrStr], 2006 append: bool = True, 2007 dialect: DialectType = None, 2008 copy: bool = True, 2009 **opts, 2010 ) -> Delete: 2011 """ 2012 Append to or set the WHERE expressions. 2013 2014 Example: 2015 >>> delete("tbl").where("x = 'a' OR x < 'b'").sql() 2016 "DELETE FROM tbl WHERE x = 'a' OR x < 'b'" 2017 2018 Args: 2019 *expressions: the SQL code strings to parse. 2020 If an `Expression` instance is passed, it will be used as-is. 2021 Multiple expressions are combined with an AND operator. 2022 append: if `True`, AND the new expressions to any existing expression. 2023 Otherwise, this resets the expression. 2024 dialect: the dialect used to parse the input expressions. 2025 copy: if `False`, modify this expression instance in-place. 2026 opts: other options to use to parse the input expressions. 2027 2028 Returns: 2029 Delete: the modified expression. 2030 """ 2031 return _apply_conjunction_builder( 2032 *expressions, 2033 instance=self, 2034 arg="where", 2035 append=append, 2036 into=Where, 2037 dialect=dialect, 2038 copy=copy, 2039 **opts, 2040 ) 2041 2042 2043class Drop(Expression): 2044 arg_types = { 2045 "this": False, 2046 "kind": False, 2047 "expressions": False, 2048 "exists": False, 2049 "temporary": False, 2050 "materialized": False, 2051 "cascade": False, 2052 "constraints": False, 2053 "purge": False, 2054 "cluster": False, 2055 "concurrently": False, 2056 } 2057 2058 @property 2059 def kind(self) -> t.Optional[str]: 2060 kind = self.args.get("kind") 2061 return kind and kind.upper() 2062 2063 2064class Filter(Expression): 2065 arg_types = {"this": True, "expression": True} 2066 2067 2068class Check(Expression): 2069 pass 2070 2071 2072class Changes(Expression): 2073 arg_types = {"information": True, "at_before": False, "end": False} 2074 2075 2076# https://docs.snowflake.com/en/sql-reference/constructs/connect-by 2077class Connect(Expression): 2078 arg_types = {"start": False, "connect": True, "nocycle": False} 2079 2080 2081class CopyParameter(Expression): 2082 arg_types = {"this": True, "expression": False, "expressions": False} 2083 2084 2085class Copy(DML): 2086 arg_types = { 2087 "this": True, 2088 "kind": True, 2089 "files": True, 2090 "credentials": False, 2091 "format": False, 2092 "params": False, 2093 } 2094 2095 2096class Credentials(Expression): 2097 arg_types = { 2098 "credentials": False, 2099 "encryption": False, 2100 "storage": False, 2101 "iam_role": False, 2102 "region": False, 2103 } 2104 2105 2106class Prior(Expression): 2107 pass 2108 2109 2110class Directory(Expression): 2111 # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html 2112 arg_types = {"this": True, "local": False, "row_format": False} 2113 2114 2115class ForeignKey(Expression): 2116 arg_types = { 2117 "expressions": False, 2118 "reference": False, 2119 "delete": False, 2120 "update": False, 2121 } 2122 2123 2124class ColumnPrefix(Expression): 2125 arg_types = {"this": True, "expression": True} 2126 2127 2128class PrimaryKey(Expression): 2129 arg_types = {"expressions": True, "options": False} 2130 2131 2132# https://www.postgresql.org/docs/9.1/sql-selectinto.html 2133# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples 2134class Into(Expression): 2135 arg_types = { 2136 "this": False, 2137 "temporary": False, 2138 "unlogged": False, 2139 "bulk_collect": False, 2140 "expressions": False, 2141 } 2142 2143 2144class From(Expression): 2145 @property 2146 def name(self) -> str: 2147 return self.this.name 2148 2149 @property 2150 def alias_or_name(self) -> str: 2151 return self.this.alias_or_name 2152 2153 2154class Having(Expression): 2155 pass 2156 2157 2158class Hint(Expression): 2159 arg_types = {"expressions": True} 2160 2161 2162class JoinHint(Expression): 2163 arg_types = {"this": True, "expressions": True} 2164 2165 2166class Identifier(Expression): 2167 arg_types = {"this": True, "quoted": False, "global": False, "temporary": False} 2168 2169 @property 2170 def quoted(self) -> bool: 2171 return bool(self.args.get("quoted")) 2172 2173 @property 2174 def hashable_args(self) -> t.Any: 2175 return (self.this, self.quoted) 2176 2177 @property 2178 def output_name(self) -> str: 2179 return self.name 2180 2181 2182# https://www.postgresql.org/docs/current/indexes-opclass.html 2183class Opclass(Expression): 2184 arg_types = {"this": True, "expression": True} 2185 2186 2187class Index(Expression): 2188 arg_types = { 2189 "this": False, 2190 "table": False, 2191 "unique": False, 2192 "primary": False, 2193 "amp": False, # teradata 2194 "params": False, 2195 } 2196 2197 2198class IndexParameters(Expression): 2199 arg_types = { 2200 "using": False, 2201 "include": False, 2202 "columns": False, 2203 "with_storage": False, 2204 "partition_by": False, 2205 "tablespace": False, 2206 "where": False, 2207 "on": False, 2208 } 2209 2210 2211class Insert(DDL, DML): 2212 arg_types = { 2213 "hint": False, 2214 "with": False, 2215 "is_function": False, 2216 "this": False, 2217 "expression": False, 2218 "conflict": False, 2219 "returning": False, 2220 "overwrite": False, 2221 "exists": False, 2222 "alternative": False, 2223 "where": False, 2224 "ignore": False, 2225 "by_name": False, 2226 "stored": False, 2227 "partition": False, 2228 "settings": False, 2229 "source": False, 2230 } 2231 2232 def with_( 2233 self, 2234 alias: ExpOrStr, 2235 as_: ExpOrStr, 2236 recursive: t.Optional[bool] = None, 2237 materialized: t.Optional[bool] = None, 2238 append: bool = True, 2239 dialect: DialectType = None, 2240 copy: bool = True, 2241 **opts, 2242 ) -> Insert: 2243 """ 2244 Append to or set the common table expressions. 2245 2246 Example: 2247 >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql() 2248 'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte' 2249 2250 Args: 2251 alias: the SQL code string to parse as the table name. 2252 If an `Expression` instance is passed, this is used as-is. 2253 as_: the SQL code string to parse as the table expression. 2254 If an `Expression` instance is passed, it will be used as-is. 2255 recursive: set the RECURSIVE part of the expression. Defaults to `False`. 2256 materialized: set the MATERIALIZED part of the expression. 2257 append: if `True`, add to any existing expressions. 2258 Otherwise, this resets the expressions. 2259 dialect: the dialect used to parse the input expression. 2260 copy: if `False`, modify this expression instance in-place. 2261 opts: other options to use to parse the input expressions. 2262 2263 Returns: 2264 The modified expression. 2265 """ 2266 return _apply_cte_builder( 2267 self, 2268 alias, 2269 as_, 2270 recursive=recursive, 2271 materialized=materialized, 2272 append=append, 2273 dialect=dialect, 2274 copy=copy, 2275 **opts, 2276 ) 2277 2278 2279class ConditionalInsert(Expression): 2280 arg_types = {"this": True, "expression": False, "else_": False} 2281 2282 2283class MultitableInserts(Expression): 2284 arg_types = {"expressions": True, "kind": True, "source": True} 2285 2286 2287class OnConflict(Expression): 2288 arg_types = { 2289 "duplicate": False, 2290 "expressions": False, 2291 "action": False, 2292 "conflict_keys": False, 2293 "constraint": False, 2294 "where": False, 2295 } 2296 2297 2298class OnCondition(Expression): 2299 arg_types = {"error": False, "empty": False, "null": False} 2300 2301 2302class Returning(Expression): 2303 arg_types = {"expressions": True, "into": False} 2304 2305 2306# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html 2307class Introducer(Expression): 2308 arg_types = {"this": True, "expression": True} 2309 2310 2311# national char, like n'utf8' 2312class National(Expression): 2313 pass 2314 2315 2316class LoadData(Expression): 2317 arg_types = { 2318 "this": True, 2319 "local": False, 2320 "overwrite": False, 2321 "inpath": True, 2322 "partition": False, 2323 "input_format": False, 2324 "serde": False, 2325 } 2326 2327 2328class Partition(Expression): 2329 arg_types = {"expressions": True} 2330 2331 2332class PartitionRange(Expression): 2333 arg_types = {"this": True, "expression": True} 2334 2335 2336# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression 2337class PartitionId(Expression): 2338 pass 2339 2340 2341class Fetch(Expression): 2342 arg_types = { 2343 "direction": False, 2344 "count": False, 2345 "percent": False, 2346 "with_ties": False, 2347 } 2348 2349 2350class Grant(Expression): 2351 arg_types = { 2352 "privileges": True, 2353 "kind": False, 2354 "securable": True, 2355 "principals": True, 2356 "grant_option": False, 2357 } 2358 2359 2360class Group(Expression): 2361 arg_types = { 2362 "expressions": False, 2363 "grouping_sets": False, 2364 "cube": False, 2365 "rollup": False, 2366 "totals": False, 2367 "all": False, 2368 } 2369 2370 2371class Cube(Expression): 2372 arg_types = {"expressions": False} 2373 2374 2375class Rollup(Expression): 2376 arg_types = {"expressions": False} 2377 2378 2379class GroupingSets(Expression): 2380 arg_types = {"expressions": True} 2381 2382 2383class Lambda(Expression): 2384 arg_types = {"this": True, "expressions": True} 2385 2386 2387class Limit(Expression): 2388 arg_types = {"this": False, "expression": True, "offset": False, "expressions": False} 2389 2390 2391class Literal(Condition): 2392 arg_types = {"this": True, "is_string": True} 2393 2394 @property 2395 def hashable_args(self) -> t.Any: 2396 return (self.this, self.args.get("is_string")) 2397 2398 @classmethod 2399 def number(cls, number) -> Literal: 2400 return cls(this=str(number), is_string=False) 2401 2402 @classmethod 2403 def string(cls, string) -> Literal: 2404 return cls(this=str(string), is_string=True) 2405 2406 @property 2407 def output_name(self) -> str: 2408 return self.name 2409 2410 def to_py(self) -> int | str | Decimal: 2411 if self.is_number: 2412 try: 2413 return int(self.this) 2414 except ValueError: 2415 return Decimal(self.this) 2416 return self.this 2417 2418 2419class Join(Expression): 2420 arg_types = { 2421 "this": True, 2422 "on": False, 2423 "side": False, 2424 "kind": False, 2425 "using": False, 2426 "method": False, 2427 "global": False, 2428 "hint": False, 2429 "match_condition": False, # Snowflake 2430 "expressions": False, 2431 } 2432 2433 @property 2434 def method(self) -> str: 2435 return self.text("method").upper() 2436 2437 @property 2438 def kind(self) -> str: 2439 return self.text("kind").upper() 2440 2441 @property 2442 def side(self) -> str: 2443 return self.text("side").upper() 2444 2445 @property 2446 def hint(self) -> str: 2447 return self.text("hint").upper() 2448 2449 @property 2450 def alias_or_name(self) -> str: 2451 return self.this.alias_or_name 2452 2453 def on( 2454 self, 2455 *expressions: t.Optional[ExpOrStr], 2456 append: bool = True, 2457 dialect: DialectType = None, 2458 copy: bool = True, 2459 **opts, 2460 ) -> Join: 2461 """ 2462 Append to or set the ON expressions. 2463 2464 Example: 2465 >>> import sqlglot 2466 >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql() 2467 'JOIN x ON y = 1' 2468 2469 Args: 2470 *expressions: the SQL code strings to parse. 2471 If an `Expression` instance is passed, it will be used as-is. 2472 Multiple expressions are combined with an AND operator. 2473 append: if `True`, AND the new expressions to any existing expression. 2474 Otherwise, this resets the expression. 2475 dialect: the dialect used to parse the input expressions. 2476 copy: if `False`, modify this expression instance in-place. 2477 opts: other options to use to parse the input expressions. 2478 2479 Returns: 2480 The modified Join expression. 2481 """ 2482 join = _apply_conjunction_builder( 2483 *expressions, 2484 instance=self, 2485 arg="on", 2486 append=append, 2487 dialect=dialect, 2488 copy=copy, 2489 **opts, 2490 ) 2491 2492 if join.kind == "CROSS": 2493 join.set("kind", None) 2494 2495 return join 2496 2497 def using( 2498 self, 2499 *expressions: t.Optional[ExpOrStr], 2500 append: bool = True, 2501 dialect: DialectType = None, 2502 copy: bool = True, 2503 **opts, 2504 ) -> Join: 2505 """ 2506 Append to or set the USING expressions. 2507 2508 Example: 2509 >>> import sqlglot 2510 >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql() 2511 'JOIN x USING (foo, bla)' 2512 2513 Args: 2514 *expressions: the SQL code strings to parse. 2515 If an `Expression` instance is passed, it will be used as-is. 2516 append: if `True`, concatenate the new expressions to the existing "using" list. 2517 Otherwise, this resets the expression. 2518 dialect: the dialect used to parse the input expressions. 2519 copy: if `False`, modify this expression instance in-place. 2520 opts: other options to use to parse the input expressions. 2521 2522 Returns: 2523 The modified Join expression. 2524 """ 2525 join = _apply_list_builder( 2526 *expressions, 2527 instance=self, 2528 arg="using", 2529 append=append, 2530 dialect=dialect, 2531 copy=copy, 2532 **opts, 2533 ) 2534 2535 if join.kind == "CROSS": 2536 join.set("kind", None) 2537 2538 return join 2539 2540 2541class Lateral(UDTF): 2542 arg_types = { 2543 "this": True, 2544 "view": False, 2545 "outer": False, 2546 "alias": False, 2547 "cross_apply": False, # True -> CROSS APPLY, False -> OUTER APPLY 2548 } 2549 2550 2551class MatchRecognizeMeasure(Expression): 2552 arg_types = { 2553 "this": True, 2554 "window_frame": False, 2555 } 2556 2557 2558class MatchRecognize(Expression): 2559 arg_types = { 2560 "partition_by": False, 2561 "order": False, 2562 "measures": False, 2563 "rows": False, 2564 "after": False, 2565 "pattern": False, 2566 "define": False, 2567 "alias": False, 2568 } 2569 2570 2571# Clickhouse FROM FINAL modifier 2572# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier 2573class Final(Expression): 2574 pass 2575 2576 2577class Offset(Expression): 2578 arg_types = {"this": False, "expression": True, "expressions": False} 2579 2580 2581class Order(Expression): 2582 arg_types = {"this": False, "expressions": True, "siblings": False} 2583 2584 2585# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier 2586class WithFill(Expression): 2587 arg_types = { 2588 "from": False, 2589 "to": False, 2590 "step": False, 2591 "interpolate": False, 2592 } 2593 2594 2595# hive specific sorts 2596# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy 2597class Cluster(Order): 2598 pass 2599 2600 2601class Distribute(Order): 2602 pass 2603 2604 2605class Sort(Order): 2606 pass 2607 2608 2609class Ordered(Expression): 2610 arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False} 2611 2612 2613class Property(Expression): 2614 arg_types = {"this": True, "value": True} 2615 2616 2617class GrantPrivilege(Expression): 2618 arg_types = {"this": True, "expressions": False} 2619 2620 2621class GrantPrincipal(Expression): 2622 arg_types = {"this": True, "kind": False} 2623 2624 2625class AllowedValuesProperty(Expression): 2626 arg_types = {"expressions": True} 2627 2628 2629class AlgorithmProperty(Property): 2630 arg_types = {"this": True} 2631 2632 2633class AutoIncrementProperty(Property): 2634 arg_types = {"this": True} 2635 2636 2637# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html 2638class AutoRefreshProperty(Property): 2639 arg_types = {"this": True} 2640 2641 2642class BackupProperty(Property): 2643 arg_types = {"this": True} 2644 2645 2646class BlockCompressionProperty(Property): 2647 arg_types = { 2648 "autotemp": False, 2649 "always": False, 2650 "default": False, 2651 "manual": False, 2652 "never": False, 2653 } 2654 2655 2656class CharacterSetProperty(Property): 2657 arg_types = {"this": True, "default": True} 2658 2659 2660class ChecksumProperty(Property): 2661 arg_types = {"on": False, "default": False} 2662 2663 2664class CollateProperty(Property): 2665 arg_types = {"this": True, "default": False} 2666 2667 2668class CopyGrantsProperty(Property): 2669 arg_types = {} 2670 2671 2672class DataBlocksizeProperty(Property): 2673 arg_types = { 2674 "size": False, 2675 "units": False, 2676 "minimum": False, 2677 "maximum": False, 2678 "default": False, 2679 } 2680 2681 2682class DataDeletionProperty(Property): 2683 arg_types = {"on": True, "filter_col": False, "retention_period": False} 2684 2685 2686class DefinerProperty(Property): 2687 arg_types = {"this": True} 2688 2689 2690class DistKeyProperty(Property): 2691 arg_types = {"this": True} 2692 2693 2694# https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc 2695# https://doris.apache.org/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-TABLE?_highlight=create&_highlight=table#distribution_desc 2696class DistributedByProperty(Property): 2697 arg_types = {"expressions": False, "kind": True, "buckets": False, "order": False} 2698 2699 2700class DistStyleProperty(Property): 2701 arg_types = {"this": True} 2702 2703 2704class DuplicateKeyProperty(Property): 2705 arg_types = {"expressions": True} 2706 2707 2708class EngineProperty(Property): 2709 arg_types = {"this": True} 2710 2711 2712class HeapProperty(Property): 2713 arg_types = {} 2714 2715 2716class ToTableProperty(Property): 2717 arg_types = {"this": True} 2718 2719 2720class ExecuteAsProperty(Property): 2721 arg_types = {"this": True} 2722 2723 2724class ExternalProperty(Property): 2725 arg_types = {"this": False} 2726 2727 2728class FallbackProperty(Property): 2729 arg_types = {"no": True, "protection": False} 2730 2731 2732class FileFormatProperty(Property): 2733 arg_types = {"this": True} 2734 2735 2736class FreespaceProperty(Property): 2737 arg_types = {"this": True, "percent": False} 2738 2739 2740class GlobalProperty(Property): 2741 arg_types = {} 2742 2743 2744class IcebergProperty(Property): 2745 arg_types = {} 2746 2747 2748class InheritsProperty(Property): 2749 arg_types = {"expressions": True} 2750 2751 2752class InputModelProperty(Property): 2753 arg_types = {"this": True} 2754 2755 2756class OutputModelProperty(Property): 2757 arg_types = {"this": True} 2758 2759 2760class IsolatedLoadingProperty(Property): 2761 arg_types = {"no": False, "concurrent": False, "target": False} 2762 2763 2764class JournalProperty(Property): 2765 arg_types = { 2766 "no": False, 2767 "dual": False, 2768 "before": False, 2769 "local": False, 2770 "after": False, 2771 } 2772 2773 2774class LanguageProperty(Property): 2775 arg_types = {"this": True} 2776 2777 2778# spark ddl 2779class ClusteredByProperty(Property): 2780 arg_types = {"expressions": True, "sorted_by": False, "buckets": True} 2781 2782 2783class DictProperty(Property): 2784 arg_types = {"this": True, "kind": True, "settings": False} 2785 2786 2787class DictSubProperty(Property): 2788 pass 2789 2790 2791class DictRange(Property): 2792 arg_types = {"this": True, "min": True, "max": True} 2793 2794 2795class DynamicProperty(Property): 2796 arg_types = {} 2797 2798 2799# Clickhouse CREATE ... ON CLUSTER modifier 2800# https://clickhouse.com/docs/en/sql-reference/distributed-ddl 2801class OnCluster(Property): 2802 arg_types = {"this": True} 2803 2804 2805# Clickhouse EMPTY table "property" 2806class EmptyProperty(Property): 2807 arg_types = {} 2808 2809 2810class LikeProperty(Property): 2811 arg_types = {"this": True, "expressions": False} 2812 2813 2814class LocationProperty(Property): 2815 arg_types = {"this": True} 2816 2817 2818class LockProperty(Property): 2819 arg_types = {"this": True} 2820 2821 2822class LockingProperty(Property): 2823 arg_types = { 2824 "this": False, 2825 "kind": True, 2826 "for_or_in": False, 2827 "lock_type": True, 2828 "override": False, 2829 } 2830 2831 2832class LogProperty(Property): 2833 arg_types = {"no": True} 2834 2835 2836class MaterializedProperty(Property): 2837 arg_types = {"this": False} 2838 2839 2840class MergeBlockRatioProperty(Property): 2841 arg_types = {"this": False, "no": False, "default": False, "percent": False} 2842 2843 2844class NoPrimaryIndexProperty(Property): 2845 arg_types = {} 2846 2847 2848class OnProperty(Property): 2849 arg_types = {"this": True} 2850 2851 2852class OnCommitProperty(Property): 2853 arg_types = {"delete": False} 2854 2855 2856class PartitionedByProperty(Property): 2857 arg_types = {"this": True} 2858 2859 2860# https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/ 2861class PartitionByRangeProperty(Property): 2862 arg_types = {"partition_expressions": True, "create_expressions": True} 2863 2864 2865# https://docs.starrocks.io/docs/table_design/data_distribution/#range-partitioning 2866class PartitionByRangePropertyDynamic(Expression): 2867 arg_types = {"this": False, "start": True, "end": True, "every": True} 2868 2869 2870# https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/ 2871class UniqueKeyProperty(Property): 2872 arg_types = {"expressions": True} 2873 2874 2875# https://www.postgresql.org/docs/current/sql-createtable.html 2876class PartitionBoundSpec(Expression): 2877 # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...) 2878 arg_types = { 2879 "this": False, 2880 "expression": False, 2881 "from_expressions": False, 2882 "to_expressions": False, 2883 } 2884 2885 2886class PartitionedOfProperty(Property): 2887 # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT 2888 arg_types = {"this": True, "expression": True} 2889 2890 2891class StreamingTableProperty(Property): 2892 arg_types = {} 2893 2894 2895class RemoteWithConnectionModelProperty(Property): 2896 arg_types = {"this": True} 2897 2898 2899class ReturnsProperty(Property): 2900 arg_types = {"this": False, "is_table": False, "table": False, "null": False} 2901 2902 2903class StrictProperty(Property): 2904 arg_types = {} 2905 2906 2907class RowFormatProperty(Property): 2908 arg_types = {"this": True} 2909 2910 2911class RowFormatDelimitedProperty(Property): 2912 # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml 2913 arg_types = { 2914 "fields": False, 2915 "escaped": False, 2916 "collection_items": False, 2917 "map_keys": False, 2918 "lines": False, 2919 "null": False, 2920 "serde": False, 2921 } 2922 2923 2924class RowFormatSerdeProperty(Property): 2925 arg_types = {"this": True, "serde_properties": False} 2926 2927 2928# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html 2929class QueryTransform(Expression): 2930 arg_types = { 2931 "expressions": True, 2932 "command_script": True, 2933 "schema": False, 2934 "row_format_before": False, 2935 "record_writer": False, 2936 "row_format_after": False, 2937 "record_reader": False, 2938 } 2939 2940 2941class SampleProperty(Property): 2942 arg_types = {"this": True} 2943 2944 2945# https://prestodb.io/docs/current/sql/create-view.html#synopsis 2946class SecurityProperty(Property): 2947 arg_types = {"this": True} 2948 2949 2950class SchemaCommentProperty(Property): 2951 arg_types = {"this": True} 2952 2953 2954class SerdeProperties(Property): 2955 arg_types = {"expressions": True, "with": False} 2956 2957 2958class SetProperty(Property): 2959 arg_types = {"multi": True} 2960 2961 2962class SharingProperty(Property): 2963 arg_types = {"this": False} 2964 2965 2966class SetConfigProperty(Property): 2967 arg_types = {"this": True} 2968 2969 2970class SettingsProperty(Property): 2971 arg_types = {"expressions": True} 2972 2973 2974class SortKeyProperty(Property): 2975 arg_types = {"this": True, "compound": False} 2976 2977 2978class SqlReadWriteProperty(Property): 2979 arg_types = {"this": True} 2980 2981 2982class SqlSecurityProperty(Property): 2983 arg_types = {"definer": True} 2984 2985 2986class StabilityProperty(Property): 2987 arg_types = {"this": True} 2988 2989 2990class TemporaryProperty(Property): 2991 arg_types = {"this": False} 2992 2993 2994class SecureProperty(Property): 2995 arg_types = {} 2996 2997 2998# https://docs.snowflake.com/en/sql-reference/sql/create-table 2999class Tags(ColumnConstraintKind, Property): 3000 arg_types = {"expressions": True} 3001 3002 3003class TransformModelProperty(Property): 3004 arg_types = {"expressions": True} 3005 3006 3007class TransientProperty(Property): 3008 arg_types = {"this": False} 3009 3010 3011class UnloggedProperty(Property): 3012 arg_types = {} 3013 3014 3015# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16 3016class ViewAttributeProperty(Property): 3017 arg_types = {"this": True} 3018 3019 3020class VolatileProperty(Property): 3021 arg_types = {"this": False} 3022 3023 3024class WithDataProperty(Property): 3025 arg_types = {"no": True, "statistics": False} 3026 3027 3028class WithJournalTableProperty(Property): 3029 arg_types = {"this": True} 3030 3031 3032class WithSchemaBindingProperty(Property): 3033 arg_types = {"this": True} 3034 3035 3036class WithSystemVersioningProperty(Property): 3037 arg_types = { 3038 "on": False, 3039 "this": False, 3040 "data_consistency": False, 3041 "retention_period": False, 3042 "with": True, 3043 } 3044 3045 3046class WithProcedureOptions(Property): 3047 arg_types = {"expressions": True} 3048 3049 3050class EncodeProperty(Property): 3051 arg_types = {"this": True, "properties": False, "key": False} 3052 3053 3054class IncludeProperty(Property): 3055 arg_types = {"this": True, "alias": False, "column_def": False} 3056 3057 3058class Properties(Expression): 3059 arg_types = {"expressions": True} 3060 3061 NAME_TO_PROPERTY = { 3062 "ALGORITHM": AlgorithmProperty, 3063 "AUTO_INCREMENT": AutoIncrementProperty, 3064 "CHARACTER SET": CharacterSetProperty, 3065 "CLUSTERED_BY": ClusteredByProperty, 3066 "COLLATE": CollateProperty, 3067 "COMMENT": SchemaCommentProperty, 3068 "DEFINER": DefinerProperty, 3069 "DISTKEY": DistKeyProperty, 3070 "DISTRIBUTED_BY": DistributedByProperty, 3071 "DISTSTYLE": DistStyleProperty, 3072 "ENGINE": EngineProperty, 3073 "EXECUTE AS": ExecuteAsProperty, 3074 "FORMAT": FileFormatProperty, 3075 "LANGUAGE": LanguageProperty, 3076 "LOCATION": LocationProperty, 3077 "LOCK": LockProperty, 3078 "PARTITIONED_BY": PartitionedByProperty, 3079 "RETURNS": ReturnsProperty, 3080 "ROW_FORMAT": RowFormatProperty, 3081 "SORTKEY": SortKeyProperty, 3082 "ENCODE": EncodeProperty, 3083 "INCLUDE": IncludeProperty, 3084 } 3085 3086 PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()} 3087 3088 # CREATE property locations 3089 # Form: schema specified 3090 # create [POST_CREATE] 3091 # table a [POST_NAME] 3092 # (b int) [POST_SCHEMA] 3093 # with ([POST_WITH]) 3094 # index (b) [POST_INDEX] 3095 # 3096 # Form: alias selection 3097 # create [POST_CREATE] 3098 # table a [POST_NAME] 3099 # as [POST_ALIAS] (select * from b) [POST_EXPRESSION] 3100 # index (c) [POST_INDEX] 3101 class Location(AutoName): 3102 POST_CREATE = auto() 3103 POST_NAME = auto() 3104 POST_SCHEMA = auto() 3105 POST_WITH = auto() 3106 POST_ALIAS = auto() 3107 POST_EXPRESSION = auto() 3108 POST_INDEX = auto() 3109 UNSUPPORTED = auto() 3110 3111 @classmethod 3112 def from_dict(cls, properties_dict: t.Dict) -> Properties: 3113 expressions = [] 3114 for key, value in properties_dict.items(): 3115 property_cls = cls.NAME_TO_PROPERTY.get(key.upper()) 3116 if property_cls: 3117 expressions.append(property_cls(this=convert(value))) 3118 else: 3119 expressions.append(Property(this=Literal.string(key), value=convert(value))) 3120 3121 return cls(expressions=expressions) 3122 3123 3124class Qualify(Expression): 3125 pass 3126 3127 3128class InputOutputFormat(Expression): 3129 arg_types = {"input_format": False, "output_format": False} 3130 3131 3132# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql 3133class Return(Expression): 3134 pass 3135 3136 3137class Reference(Expression): 3138 arg_types = {"this": True, "expressions": False, "options": False} 3139 3140 3141class Tuple(Expression): 3142 arg_types = {"expressions": False} 3143 3144 def isin( 3145 self, 3146 *expressions: t.Any, 3147 query: t.Optional[ExpOrStr] = None, 3148 unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None, 3149 copy: bool = True, 3150 **opts, 3151 ) -> In: 3152 return In( 3153 this=maybe_copy(self, copy), 3154 expressions=[convert(e, copy=copy) for e in expressions], 3155 query=maybe_parse(query, copy=copy, **opts) if query else None, 3156 unnest=( 3157 Unnest( 3158 expressions=[ 3159 maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) 3160 for e in ensure_list(unnest) 3161 ] 3162 ) 3163 if unnest 3164 else None 3165 ), 3166 ) 3167 3168 3169QUERY_MODIFIERS = { 3170 "match": False, 3171 "laterals": False, 3172 "joins": False, 3173 "connect": False, 3174 "pivots": False, 3175 "prewhere": False, 3176 "where": False, 3177 "group": False, 3178 "having": False, 3179 "qualify": False, 3180 "windows": False, 3181 "distribute": False, 3182 "sort": False, 3183 "cluster": False, 3184 "order": False, 3185 "limit": False, 3186 "offset": False, 3187 "locks": False, 3188 "sample": False, 3189 "settings": False, 3190 "format": False, 3191 "options": False, 3192} 3193 3194 3195# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16 3196# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16 3197class QueryOption(Expression): 3198 arg_types = {"this": True, "expression": False} 3199 3200 3201# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16 3202class WithTableHint(Expression): 3203 arg_types = {"expressions": True} 3204 3205 3206# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html 3207class IndexTableHint(Expression): 3208 arg_types = {"this": True, "expressions": False, "target": False} 3209 3210 3211# https://docs.snowflake.com/en/sql-reference/constructs/at-before 3212class HistoricalData(Expression): 3213 arg_types = {"this": True, "kind": True, "expression": True} 3214 3215 3216class Table(Expression): 3217 arg_types = { 3218 "this": False, 3219 "alias": False, 3220 "db": False, 3221 "catalog": False, 3222 "laterals": False, 3223 "joins": False, 3224 "pivots": False, 3225 "hints": False, 3226 "system_time": False, 3227 "version": False, 3228 "format": False, 3229 "pattern": False, 3230 "ordinality": False, 3231 "when": False, 3232 "only": False, 3233 "partition": False, 3234 "changes": False, 3235 "rows_from": False, 3236 "sample": False, 3237 } 3238 3239 @property 3240 def name(self) -> str: 3241 if isinstance(self.this, Func): 3242 return "" 3243 return self.this.name 3244 3245 @property 3246 def db(self) -> str: 3247 return self.text("db") 3248 3249 @property 3250 def catalog(self) -> str: 3251 return self.text("catalog") 3252 3253 @property 3254 def selects(self) -> t.List[Expression]: 3255 return [] 3256 3257 @property 3258 def named_selects(self) -> t.List[str]: 3259 return [] 3260 3261 @property 3262 def parts(self) -> t.List[Expression]: 3263 """Return the parts of a table in order catalog, db, table.""" 3264 parts: t.List[Expression] = [] 3265 3266 for arg in ("catalog", "db", "this"): 3267 part = self.args.get(arg) 3268 3269 if isinstance(part, Dot): 3270 parts.extend(part.flatten()) 3271 elif isinstance(part, Expression): 3272 parts.append(part) 3273 3274 return parts 3275 3276 def to_column(self, copy: bool = True) -> Expression: 3277 parts = self.parts 3278 last_part = parts[-1] 3279 3280 if isinstance(last_part, Identifier): 3281 col: Expression = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy) # type: ignore 3282 else: 3283 # This branch will be reached if a function or array is wrapped in a `Table` 3284 col = last_part 3285 3286 alias = self.args.get("alias") 3287 if alias: 3288 col = alias_(col, alias.this, copy=copy) 3289 3290 return col 3291 3292 3293class SetOperation(Query): 3294 arg_types = { 3295 "with": False, 3296 "this": True, 3297 "expression": True, 3298 "distinct": False, 3299 "by_name": False, 3300 **QUERY_MODIFIERS, 3301 } 3302 3303 def select( 3304 self: S, 3305 *expressions: t.Optional[ExpOrStr], 3306 append: bool = True, 3307 dialect: DialectType = None, 3308 copy: bool = True, 3309 **opts, 3310 ) -> S: 3311 this = maybe_copy(self, copy) 3312 this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts) 3313 this.expression.unnest().select( 3314 *expressions, append=append, dialect=dialect, copy=False, **opts 3315 ) 3316 return this 3317 3318 @property 3319 def named_selects(self) -> t.List[str]: 3320 return self.this.unnest().named_selects 3321 3322 @property 3323 def is_star(self) -> bool: 3324 return self.this.is_star or self.expression.is_star 3325 3326 @property 3327