o 8Dihx@s.ddlmZddlZddlZddlZddlmZddlm Z m Z ddl m Z ddl mZddlmZmZmZedZe je je jfZGd d d e ZGd d d Zd3ddZd4ddZddZddZddZddZ d5d d!Z!d6d"d#Z"d$d%Z#d&d'Z$d(d)Z%d7d+d,Z&d8d-d.Z'd8d/d0Z(d1d2Z)dS)9) annotationsN) defaultdict)Enumauto)exp) OptimizeError)ensure_collection find_new_nameseq_getsqlglotc@s0eZdZeZeZeZeZeZeZ dS) ScopeTypeN) __name__ __module__ __qualname__rROOTSUBQUERY DERIVED_TABLECTEUNIONUDTFrrP/var/www/Datamplify/venv/lib/python3.10/site-packages/sqlglot/optimizer/scope.pyr s r c@seZdZdZdddejdddfddZddZ dTddZd d Z d d Z dUddZ d dddZ d dddZ ddZeddZeddZeddZeddZedd ZedVd#d$Zed%d&Zed'd(Zed)d*ZedWd,d-Zed.d/Zed0d1Zed2d3Zed4d5Zed6d7Zd8d9Zed:d;Z edd?Z"ed@dAZ#edBdCZ$edDdEZ%edFdGZ&dHdIZ'dJdKZ(dLdMZ)dNdOZ*dPdQZ+dRdSZ,dS)XScopea Selection scope. Attributes: expression (exp.Select|exp.SetOperation): Root expression of this scope sources (dict[str, exp.Table|Scope]): Mapping of source name to either a Table expression or another Scope instance. For example: SELECT * FROM x {"x": Table(this="x")} SELECT * FROM x AS y {"y": Table(this="x")} SELECT * FROM (SELECT ...) AS y {"y": Scope(...)} lateral_sources (dict[str, exp.Table|Scope]): Sources from laterals For example: SELECT c FROM x LATERAL VIEW EXPLODE (a) AS c; The LATERAL VIEW EXPLODE gets x as a source. cte_sources (dict[str, Scope]): Sources from CTES outer_columns (list[str]): If this is a derived table or CTE, and the outer query defines a column list for the alias of this scope, this is that list of columns. For example: SELECT * FROM (SELECT ...) AS y(col1, col2) The inner query would have `["col1", "col2"]` for its `outer_columns` parent (Scope): Parent scope scope_type (ScopeType): Type of this scope, relative to it's parent subquery_scopes (list[Scope]): List of all child scopes for subqueries cte_scopes (list[Scope]): List of all child scopes for CTEs derived_table_scopes (list[Scope]): List of all child scopes for derived_tables udtf_scopes (list[Scope]): List of all child scopes for user defined tabular functions table_scopes (list[Scope]): derived_table_scopes + udtf_scopes, in the order that they're defined union_scopes (list[Scope, Scope]): If this Scope is for a Union expression, this will be a list of the left and right child scopes. Nc Cs||_|pi|_|p i|_|pi|_|j|j|j|j|p#g|_||_||_g|_g|_ g|_ g|_ g|_ g|_ ||_|dSN) expressionsourceslateral_sources cte_sourcesupdate outer_columnsparent scope_typesubquery_scopesderived_table_scopes table_scopes cte_scopes union_scopes udtf_scopescan_be_correlated clear_cache) selfrrrr r!rrr(rrr__init__;s"     zScope.__init__cCsdd|_d|_d|_d|_d|_d|_d|_d|_d|_d|_ d|_ d|_ d|_ d|_ d|_d|_dS)NF) _collected _raw_columns_table_columns_stars_derived_tables_udtfs_tables_ctes _subqueries_selected_sources_columns_external_columns _join_hints_pivots _references_semi_anti_join_tablesr*rrrr)Xs  zScope.clear_cachec KsZtd||r |nd||i|j|pi|r|nd|jp'|tjtjfvd|S)z3Branch from the current scope to a new, inner scopeN)rrr r!rrr(r)runnestcopyrr(r rr)r*rr!rrrkwargsrrrbranchjs z Scope.branchcCsg|_g|_g|_g|_g|_g|_g|_g|_g|_t |_ |j ddD]}||j ur-q%t |tjr=|jr=|j|q%t |tjrXt |jtjrQ|j|q%|j|q%t |tjrt |jtjs|j}t |tjrx|jrx|j |j|j|q%t |tjr|j|q%t |tjr|j|q%t |tjr|j|q%t|rt|r|j|q%t |tj r|j|q%t |tj!r|j|q%d|_"dS)NFbfsT)#r2r3r4r0r1r-r.r/r8setr;walkr isinstancerDotis_starappendColumnthisStarTabler JoinHintJoinis_semi_or_anti_joinadd alias_or_namerr_is_derived_table_is_from_or_joinUNWRAPPED_QUERIES TableColumnr,)r*noder rrr_collectzsL         zScope._collectcCs|js |dSdSr)r,rWr<rrr_ensure_collecteds zScope._ensure_collectedTcCst|j|ddS)NrBprune) walk_in_scoper)r*rBrZrrrrDz Scope.walkrAcGt|j||dSNrA) find_in_scoperr*rBexpression_typesrrrfindr\z Scope.findcGr]r^)find_all_in_scoperr`rrrfind_allr\zScope.find_allcCs|||dS)z Replace `old` with `new`. This can be used instead of `exp.Expression.replace` to ensure the `Scope` is kept up-to-date. Args: old (exp.Expression): old node new (exp.Expression): new node N)replacer))r*oldnewrrrres z Scope.replacecC||jS)ze List of tables in this scope. Returns: list[exp.Table]: tables )rXr2r<rrrtablesz Scope.tablescCrh)z_ List of CTEs in this scope. Returns: list[exp.CTE]: ctes )rXr3r<rrrctesrjz Scope.ctescCrh)z List of derived tables in this scope. For example: SELECT * FROM (SELECT ...) <- that's a derived table Returns: list[exp.Subquery]: derived tables )rXr0r<rrrderived_tables zScope.derived_tablescCrh)z} List of "User Defined Tabular Functions" in this scope. Returns: list[exp.UDTF]: UDTFs )rXr1r<rrrudtfsrjz Scope.udtfscCrh)z List of subqueries in this scope. For example: SELECT * FROM x WHERE a IN (SELECT ...) <- that's a subquery Returns: list[exp.Select | exp.SetOperation]: subqueries )rXr4r<rrr subqueriesrmzScope.subqueriesreturnt.List[exp.Column | exp.Dot]cCrh)zK List of star expressions (columns or dots) in this scope. )rXr/r<rrrstarssz Scope.starsc Cs|jdur||j}ddt|j|jdd|jDD}t|j j }g|_||D]W}| t j t jt jt jt jt jt jt j}|r~|js~t|t j s~t|t jr[t|jt jr~t|t jt jfrst|jt jt jfs~|j|vs~t|t jr|jdks|j|q-|jS)z List of columns in this scope. Returns: list[exp.Column]: Column instances in this scope, plus any Columns that reference this scope from correlated subqueries. NcSsg|] }|jD]}|qqSr)external_columns).0scopecolumnrrr sz!Scope.columns..css|]}|jr|VqdSr)r()rtdtsrrr sz Scope.columns..except)r6rXr- itertoolschainr"r'r#rCr named_selects find_ancestorrSelectQualifyOrderHavingHintrLrKDistincttablerErJFuncr Window WithinGroupnamearg_keyrH)r*columnsrsr}rvancestorrrrrsX          z Scope.columnscCs|jdur ||jSr)r.rXr<rrr table_columns<s zScope.table_columnscCsh|jdur1i}|jD]#\}}||jvrq ||vrtd|||jvr-||j|f||<q ||_|jS)an Mapping of nodes and sources that are actually selected from in this scope. That is, all tables in a schema are selectable at any point. But a table only becomes a selected source if it's included in a FROM or JOIN clause. Returns: dict[str, (exp.Table|exp.Select, exp.Table|Scope)]: selected sources and nodes NzAlias already used: )r5 referencesr;rr)r*resultrrVrrrselected_sourcesCs   zScope.selected_sources$t.List[t.Tuple[str, exp.Expression]]cCsr|jdur6g|_|jD] }|j|j|fq t|j|jD]}|jt||j dr/|n| fq|jS)Npivots) r:rirHrQr{r|rlrn_get_source_aliasargsgetr=)r*rrrrrr_s  zScope.referencescsRjdur&tjtjrj\}}|j|j_jSfddjD_jS)z Columns that appear to reference sources in outer scopes. Returns: list[exp.Column]: Column instances that don't reference sources in the current scope. Ncs(g|]}|jjvr|jjvr|qSr)rrsemi_or_anti_join_tablesrtcr<rrrw~s   z*Scope.external_columns..)r7rErr SetOperationr&rsr)r*leftrightrr<rrsps   zScope.external_columnscCsdd|jDS)z Unqualified columns in the current scope. Returns: list[exp.Column]: Unqualified columns cSsg|]}|js|qSrrrrrrrwsz-Scope.unqualified_columns..rr<rrrunqualified_columnsszScope.unqualified_columnscCs|jdurgS|jS)z Hints that exist in the scope that reference tables Returns: list[exp.JoinHint]: Join hints that are referenced within the scope N)r8r<rrr join_hintss zScope.join_hintscCs|js dd|jD|_|jS)NcSs*g|]\}}|jdp gD]}|qqS)r)rr)rt_rVpivotrrrrws z Scope.pivots..)r9rr<rrrrs z Scope.pivotscCs |jptSr)r;rCr<rrrrs zScope.semi_or_anti_join_tablescsfdd|jDS)z Get all columns in the current scope for a particular source. Args: source_name (str): Name of the source Returns: list[exp.Column]: Column instances that reference `source_name` csg|] }|jkr|qSrr)rtrv source_namerrrwsz(Scope.source_columns..r)r*rrrrsource_columnss zScope.source_columnscC |jtjkS)z%Determine if this scope is a subquery)r!r rr<rrr is_subquery zScope.is_subquerycCr)z*Determine if this scope is a derived table)r!r rr<rrris_derived_tablerzScope.is_derived_tablecCr)z"Determine if this scope is a union)r!r rr<rrris_unionrzScope.is_unioncCr)z4Determine if this scope is a common table expression)r!r rr<rrris_cterz Scope.is_ctecCr)z#Determine if this is the root scope)r!r rr<rrris_rootrz Scope.is_rootcCr)z?Determine if this scope is a UDTF (User Defined Table Function))r!r rr<rrris_udtfrz Scope.is_udtfcCst|jo|jS)z0Determine if this scope is a correlated subquery)boolr(rsr<rrris_correlated_subqueryszScope.is_correlated_subquerycCs |j|pdg}||j|<dS)zRename a source in this scopeN)rpop)r*old_namenew_namerrrr rename_sourceszScope.rename_sourcecCs||j|<|dS)zAdd a source to this scopeN)rr))r*rsourcerrr add_sources  zScope.add_sourcecCs|j|d|dS)zRemove a source from this scopeN)rrr))r*rrrr remove_sources zScope.remove_sourcecCsd|jdS)NzScope<>)rsqlr<rrr__repr__szScope.__repr__ccsV|g}g}|r"|}|||t|j|j|j|j|st |EdHdS)z Traverse the scope tree from this node. Yields: Scope: scope instances in depth-first-search post-order N) rrHextendr{r|r%r&r$r"reversed)r*stackrrurrrtraverses   zScope.traversecCsFtdd}|D]}|jD]\}}|t|d7<qq |S)z Count the number of times each scope in this tree is referenced. Returns: dict[int, int]: Mapping of Scope instance ID to reference count cSsdS)Nrrrrrrsz!Scope.ref_count..)rrrvaluesid)r*scope_ref_countrurrrrr ref_counts  zScope.ref_count)NNNTN)rprq)rpr)-r rr__doc__r rr+r)r@rWrXrDrbrdrepropertyrirkrlrnrorrrrrrrsrrrrrrrrrrrrrrrrrrrrrrrs"  ,        6                rrexp.Expressionrp t.List[Scope]cCst|tr ttt|SgS)a Traverse an expression by its "scopes". "Scope" represents the current context of a Select statement. This is helpful for optimizing queries, where we need more information than the expression tree itself. For example, we might care about the source names within a subquery. Returns a list because a generator could result in incomplete properties which is confusing. Examples: >>> import sqlglot >>> expression = sqlglot.parse_one("SELECT a FROM (SELECT a FROM x) AS y") >>> scopes = traverse_scope(expression) >>> scopes[0].expression.sql(), list(scopes[0].sources) ('SELECT a FROM x', ['x']) >>> scopes[1].expression.sql(), list(scopes[1].sources) ('SELECT a FROM (SELECT a FROM x) AS y', ['y']) Args: expression: Expression to traverse Returns: A list of the created scope instances )rE TRAVERSABLESlist_traverse_scoperrrrrtraverse_scopes rt.Optional[Scope]cCstt|dS)z Build a scope tree. Args: expression: Expression to build the scope tree for. Returns: The root scope )r rrrrr build_scope0s rccs||j}t|tjrt|EdHnt|tjr(t|EdHt|EdHdSt|tjrA|j r9t|EdHnt |EdHnxt|tj rOt |EdHnjt|tj r]t|EdHn\t|tjrt|jtjr~t|EdHtt|j|jdEdHdSt|tjrt|EdHt|tjD]}t|jtjtjfstt||jdEdHqdStd|t|dS|VdS)N)rz'Cannot traverse scope %s with type '%s')rrErr_traverse_selectr_traverse_ctes_traverse_unionSubqueryr_traverse_subqueriesrL_traverse_tablesr_traverse_udtfsDDLQueryrrrDMLrcr rloggerwarningtype)rurqueryrrrr=s>        rccs0t|EdHt|EdHt|EdHdSr)rrr)rurrrrbsrccsd}|g}|jj|jjg}|r_|}|d}|j||jtjd}t|t j r>t |EdH| || |j|jgqt|D]}|VqB|rY|||g|_|}|Vn|}|sdSdS)Nr)rr!)rrrrr@rr rrErrrrHrrr&)ru prev_scopeunion_scope_stackexpression_stackr union_scope new_scoperrrrhs4     rccsi}|jD]G}|j}|jjd}|r+|jr+|j}t|tj r+|j |jt j d||<d}t |j |j||jt j dD]}|Vq;|rM|||<|j|q|j||j|dS)Nwithr!)rrr!)rkaliasrrr recursiverJrErrr@r rralias_column_namesr%rHrrr)rurctecte_namewith_union child_scoperrrrs2     r exp.SubqueryrcCs$t|tjot|jpt|jtjS)z We represent (tbl1 JOIN tbl2) as a Subquery, but it's not really a "derived table", as it doesn't introduce a new scope. If an alias is present, it shadows all names under the Subquery, so that's one exception to this rule. )rErrrrrJrTrrrrrRsrRcCs6|j}t|tjr|j}t|tjs t|tjtjfS)zU Determine if `expression` is the FROM or JOIN clause of a SELECT statement. )r rErrFromrN)rr rrrrSs   rSc cs2i}g}|jjd}|r||j|jjdpgD]}||jqt|jtjr3||j||jjdp=g|D]}t|tj rL|j}t|tjr|j }|j }||j vrx|j sx|jd}|rp|||dj<n|j |||<n||vr||t||<n|||<||jur|dd|jdpgDqAt|tjsqAt|tjr|} tj} |j} n4t|rd} tj} |j} |dd|jdpgDn||j|d d|jdpgDqAd} t|j|| |j| d D] } | V| |t|<q| r| | |j| qA|j |dS) Nfromjoinslateralsrrcs|]}|jVqdSrrJrtjoinrrrryz#_traverse_tables..csrrrrrrrryrcsrrrrrrrryr)rrr!)rrrrHrJrErrLrFinalrrQrdbrr DerivedTablerr r'rRrr#rr@rrr$r) rur expressionsfrom_rr table_namerrrr!scopesrrrrrst         "    rccsF|jD]}d}t|j|tjdD]}|V|}q|j|qdS)Nr)rorr@r rr"rH)rusubquerytoprrrrrs rccst|jtjr |jj}nt|jtjr|jjg}ng}i}|D](}t|rHd}t|j |t j |j dD] }|V|}||t |<q4|j|q |j|dS)N)r!r)rErrUnnestrLateralrJrRrr@r rrrr"rHrr)rurrrrrrrrr's.   rTc#sd|j|fdddD]Y}d|V||urqt|tjsDt|jtjtjtjfr1t|sDt|jtj r>t|tj sDt|tj rhdt|tjtj frhdD]}|j |pZgD] }t||dEdHq[qQqdS) a3 Returns a generator object which visits all nodes in the syntrax tree, stopping at nodes that start child scopes. Args: expression (exp.Expression): bfs (bool): if set to True the BFS traversal order will be applied, otherwise the DFS traversal will be used instead. prune ((node, parent, arg_key) -> bool): callable that returns True if the generator should stop traversing this branch of the tree. Yields: tuple[exp.Expression, Optional[exp.Expression], str]: node, parent, arg key Fcspo|Srr)ncrossed_scope_boundaryrZrrrWszwalk_in_scope..rYT)rrrrAN)rDrErrr rrNrrRrrrTrrr[)rrBrZrVkeyargrrrr[Cs:      r[ccs0t||dD]}t|tt|r|VqdS)a Returns a generator object which visits all nodes in this scope and only yields those that match at least one of the specified expression types. This does NOT traverse into subscopes. Args: expression (exp.Expression): expression_types (tuple[type]|type): the expression type(s) to match. bfs (bool): True to use breadth-first search, False to use depth-first. Yields: exp.Expression: nodes rAN)r[rEtuplerrrarBrrrrcrs rccCstt|||ddS)a Returns the first node in this scope which matches at least one of the specified types. This does NOT traverse into subscopes. Args: expression (exp.Expression): expression_types (tuple[type]|type): the expression type(s) to match. bfs (bool): True to use breadth-first search, False to use depth-first. Returns: exp.Expression: the node which matches the criteria or None if no node matching the criteria was found. rAN)nextrcr rrrr_sr_cCs@|jd}|j}|st|tjrt|jdkr|jdj}|S)Nrrr) rrrrEr TableAliaslenrr)r alias_arg alias_namerrrrs  r)rrrpr)rrrpr)rrrpr)rrrprr)T)* __future__rr{loggingtypingt collectionsrenumrrr rsqlglot.errorsrsqlglot.helperrr r getLoggerrrrrrr rrrrrrrrRrSrrrr[rcr_rrrrrs>       y  %# $  W  /