o 8Di̠@s|ddlmZddlZddlmZddlmZddl m Z m Z ej r-ddl mZddlmZdNd d ZdOddZdOddZdOddZdOddZdOddZdOddZdOddZ dPdQd!d"Z dRdSd&d'ZdOd(d)ZdOd*d+ZdOd,d-ZdOd.d/ZdOd0d1ZdOd2d3Z dTd5d6Z!dOd7d8Z"dOd9d:Z#dOd;d<Z$d=d>fdUd@dAZ%dOdBdCZ&dOdDdEZ'dOdFdGZ(dOdHdIZ)dOdJdKZ*dOdLdMZ+dS)V) annotationsN expressions)UnsupportedError) find_new_name name_sequence)E) Generator transforms4t.List[t.Callable[[exp.Expression], exp.Expression]]return,t.Callable[[Generator, exp.Expression], str]csdfdd }|S) a Creates a new transform by chaining a sequence of transformations and converts the resulting expression to SQL, using either the "_sql" method corresponding to the resulting expression, or the appropriate `Generator.TRANSFORMS` function (when applicable -- see below). Args: transforms: sequence of transform functions. These will be called in order. Returns: Function that can be used as a generator transform. expressionexp.Expressionr strc st|}zd|}ddD]}||}qWnty2}z |t|WYd}~nd}~wwt||jdd}|rB||S|jt|}|rl|t|urgt|t j r]| |St d|j jd|||St d|j jd)Nr_sqlzExpression type z3 requires a _sql method in order to be transformed.zUnsupported expression type .)typer unsupportedrgetattrkey TRANSFORMSget isinstanceexpFuncfunction_fallback_sql ValueError __class____name__)selfrexpression_type transformunsupported_error _sql_handlertransforms_handlerr K/var/www/Datamplify/venv/lib/python3.10/site-packages/sqlglot/transforms.py_to_sqls.      zpreprocess.._to_sqlN)rrr rr()r r*r(r'r) preprocesssr+rrc Cst|tjrd}g}|tjD]}t|jtjtjfr,t|j dks,t|j dtj s-q|j d}|j d}|j d}|j d}|rN|rNt|tj sOq|j d}t|tjr`|jdnd} t|d}td | tj|j|j d } t| d} d |rd |nd } t|| } t| | | t|dk}| j|dd}t| | }||| |tjtj|d| | gd|d7}q|r|j dpt}| dd| dg||j | d||S)Nrrstartendstepalias date_valuedatedate_addunit_generated_dates_F)distinctthistablewith recursiveTr)!rrSelectfind_allUnnestparentFromJoinlenrGenerateDateArrayargsrInterval TableAliascolumnscastfuncLiteralnumbernameselectas_from_whereunionreplacesubqueryappendalias_CTEWithset)rcountrecursive_ctesunnestgenerate_date_arrayr,r-r.r/ column_namer2 cast_date_addcte_name base_queryrecursive_query cte_querygenerate_dates_querywith_expressionr(r(r).unnest_generate_date_array_using_recursive_cte@sT           rgcCsP|j}t|tjr&t|tjr&tj|gd}|jr$tj|d|jgddS|S|S)z5Unnests GENERATE_SERIES or SEQUENCE table references.r_uF)r/r;copy)r9rrTableGenerateSeriesr@r/rW)rr9r]r(r(r)unnest_generate_seriesysrlcCszt|tjr;t|jtjr;ddt|jjddD}|jD]}t|tjr:|js:|j |vr:| tj | |j q|S)ap Replace references to select aliases in GROUP BY clauses. Example: >>> import sqlglot >>> sqlglot.parse_one("SELECT a AS b FROM x GROUP BY b").transform(unalias_group).sql() 'SELECT a AS b FROM x GROUP BY 1' Args: expression: the expression that will be transformed. Returns: The transformed expression. cSs$i|]\}}t|tjr|j|qSr()rrAliasr/).0ier(r(r) s  z!unalias_group..rr,)rrGrouprAr> enumeraterColumnr;rNrTrLrMr)raliased_selectsgroup_byr(r(r) unalias_groups   rxc Cst|tjr|jdrt|jdjdtjrt|jd}|jdjdj }tj t |d}|jd}|rD| d|n| dtj dd|Ddt||}|j|d d g}|h}|jd d D]B}|jrxtg}n7t|tjst||jpd }t|tjr|jjdnd } |tj||| d}||j||jdqltj|dd ij|jdd d d d jt|dd d S|S)a4 Convert SELECT DISTINCT ON statements to a subquery with a window function. This is useful for dialects that don't support SELECT DISTINCT ON but support window functions. Args: expression: the expression that will be transformed. Returns: The transformed expression. r7on _row_number)r9 partition_byordercSsg|]}|qSr(rirncr(r(r) z)eliminate_distinct_on..rFr}N_colquotedrr/ri_tr)rrr>rFrTupler named_selectspoprWindow RowNumberrZOrderrWrOselectsis_starStarrm output_namerur9rTaddrVrQrUrRcolumneq) rrow_number_window_alias distinct_colswindowr| new_selects taken_namesrOr/rr(r(r)eliminate_distinct_onsD        rc Cst|tjr|jdrt|j}|jD]}|js,t |d}| t ||| |qddd}tj tt||j}|jdj}d d |jD}|jrStjntjtjf}t||D]U} t| tjr|r| tjD]} || j} | r| | qpt |jd }|j t | |d d t|} t| jtjr| }q`| | q`| j|jvr|j | d d q`|j|jdd dd d j|d d S|S)a@ Convert SELECT statements that contain the QUALIFY clause into subqueries, filtered equivalently. The idea behind this transformation can be seen in Snowflake's documentation for QUALIFY: https://docs.snowflake.com/en/sql-reference/constructs/qualify Some dialects don't support window functions in the WHERE clause, so we need to include them as projections in the subquery, in order to refer to them in the outer filter using aliases. Also, if a column is referenced in the QUALIFY clause but is not selected, we need to include it too, otherwise we won't be able to refer to it in the outer query's WHERE clause. Finally, if a newly aliased projection is referenced in the QUALIFY clause, it will be replaced by the corresponding expression to avoid creating invalid column references. qualify_crOrr str | exp.ColumncSs>|j}|jdp |j}t|tjrtj||jddS|S)Nr/rr) alias_or_namerFrr9rr Identifierr)rOr identifierr(r(r)_select_alias_or_names  z0eliminate_qualify.._select_alias_or_namecSs"i|] }t|tjr|j|jqSr()rrrmr/r9)rnrOr(r(r)rqs  z%eliminate_qualify.._wFr}r)r/riN)rOrr r)rrr>rFrrZrrrrrTrWrrOlistmaprr9rrrur?rNrrAQualifyrirQrUrR) rtakenrOr/r outer_selectsqualify_filtersexpression_by_aliasselect_candidatesselect_candidaterexprr(r(r)eliminate_qualifysH            rcCs.|tjD]}|ddd|jDq|S)z Some dialects only allow the precision for parameterized types to be defined in the DDL and not in other expressions. This transforms removes the precision from parameterized types in expressions. rcSsg|] }t|tjs|qSr()rr DataTypeParamrnrpr(r(r)r&z8remove_precision_parameterized_types..)r?rDataTyperZr)rnoder(r(r)$remove_precision_parameterized_typess rcCsnddlm}t|tjr5dd||tjD}|r5|tjD]}|jd}|j dkr4|j |vr4| q|S)zYRemove references to unnest table aliases, added by the optimizer's qualify_columns step.rfind_all_in_scopecSs&h|]}t|jtjtjfr|jqSr()rrArrBrCr/)rnr]r(r(r) 1s z#unqualify_unnest..r9) sqlglot.optimizer.scoperrrr>r@r?rupartsarg_keyr9r)rrunnest_aliasesr leftmost_partr(r(r)unqualify_unnest,s    rTunnest_using_arrays_zipboolc s dfdd }dd d }t|tjr|jd }|rwt|jtjrw|j}|jd}|j}t|dk}||||^} } |rA|j ng} |jd} | r\| dt| tj rV| nt d| tj|||| | d|rrtj|j| dndd|jdp~g} t| D]}|j}t|tj}|r|jn|}t|tjr |r|jd}n|jd}|j}t|dk}||||}| ||r|j ng}|st|dvrtd|jd} | r| dt| tj r| nt dt||D]\}}|dtj||||ddtj|j|ddqq|S) z4Convert cross join unnest into lateral view explode.u exp.Unnest unnest_exprst.List[exp.Expression]has_multi_exprrr cs4|rstdtjd|dg}|d||S|S)Nz2Cannot transpile UNNEST with multiple input arrays ARRAYS_ZIPr9rr)rr AnonymousrZ)rrr zip_exprsrr(r)_unnest_zip_exprsEs  z,unnest_to_explode.._unnest_zip_exprst.Type[exp.Func]cSs"|jdr tjS|rtjStjS)Noffset)rFrr PosexplodeInlineExplode)rrr(r(r) _udtf_typeTs z%unnest_to_explode.._udtf_typefromr/rrrposr)r9rIN)r9r/joins)rzYCROSS JOIN UNNEST to LATERAL VIEW EXPLODE transformation requires explicit column aliaseslateralsr8T)r9viewr/)rrrrrrr r)rrrrr r)rrr>rFrr9r@rrDrIinsertr to_identifierrTrjrHrLateralremoverziprV)rrrrrQr]r/exprsrr9rrIrrjoin join_expr is_lateral alias_colsrprr(rr)unnest_to_explode?s~             r index_offsetint,t.Callable[[exp.Expression], exp.Expression]csdfdd }|S)z4Convert explode/posexplode projections into unnests.rrr c st|tjrddlm}t|j}dd||jD}d'd d }g}||d }tjtj tj tj d gd||d|gd}t |jD]~}|tj} | rd} d} t|tjrd|jd} |} n0t|tjr|jd} |jd} |tj|jddd} n|t|d} | tj} | sJt| tj} | j}t| tjr|d}|dd|ddtdtdtd|tdtj|dd|}t|tjr||j||d}| s||d} | r||d } | s||d } | dt | |jdj}tj!tj"||dtj"| |dtj"| |dd}| || rR|j#}|$|%| dtj!tj"||dtj"| |dtj"| |dd&| |d||sl|j'dre|j(|dd d!n|j)|ddtj*|+d"}|,||j(tjtj |+gt | d#|| gdd dd$dkr|d}|j-tj"||dtj"| |d.tj"||d|k/tj"| |d|ddqF|rtj0|d|ddd%}dkr|d}|j#dd&||S)(Nr)ScopecSsh|]\}}|qSr(r()rnrNr5r(r(r)rrzVexplode_projection_to_unnest.._explode_projection_to_unnest..names t.Set[str]rNrr cSst||}|||SN)rr)rrNr(r(r)new_names  zUexplode_projection_to_unnest.._explode_projection_to_unnest..new_namerrrrrhr:r6r/rFr}safeTrIF ARRAY_SIZECOALESCEcol)r9truerrCROSS)ri join_typer8)rr)rrirr-)rrrNrr r)1rrr>rrrZr referencesrWr@rkrLrMrrfindrrmrFAliasesaliasesrTr9r ExplodeOuterrKArrayrarrayrurrrIfrrrindexrPrrrQ ArraySizerirVrRor_and_Greatest)rrtaken_select_namestaken_source_namesrarrays series_aliasseriesrOexplode pos_alias explode_aliasr/ is_posexplode explode_argbracketunnest_source_aliasseries_table_aliasrrsizer-rr(r)_explode_projection_to_unnests                                   zCexplode_projection_to_unnest.._explode_projection_to_unnestNrrr rr()rr r(r r)explode_projection_to_unnestsrcCsdt|tjr0t|jtjs0|jr0|j}|d|jtj tj |dgd}tj||d}|S)z?Transforms percentiles by adding a WITHIN GROUP clause to them.r9r8rr9r) rr PERCENTILESrA WithinGrouprr9rrZrOrdered)rrr|r(r(r) add_within_group_for_percentilesCs   rcCs`t|tjr.t|jtjr.t|jtjr.|jj}ttj | tj j}| tj ||dS|S)zQTransforms percentiles by getting rid of their corresponding WITHIN GROUP clause.)r9quantile) rrrr9rrrtrJrrrTApproxQuantile)rr input_valuer(r(r)#remove_within_group_for_percentilesRs   rcspt|tjr6|jr6td|jD]%}|jdjs5|j}t|tj r$|j}|jd dfdd|j Dq|S)zVUses projection output names in recursive CTE definitions to define the CTEs' columns._c_r/rIcsg|] }t|jp qSr()rrr)rns next_namer(r)rmz2add_recursive_cte_column_names..) rrrYr=rrrFrIr9 SetOperationrZr)rctequeryr(rr)add_recursive_cte_column_names`s    r!cCsHt|tjtjfr"|jdkr"|jjtjj vr"|j tj d|S)z8Replace 'epoch' in casts by the equivalent date literal.epochz1970-01-01 00:00:00) rrCastTryCastrNlowertor9rTEMPORAL_TYPESrTrLstringrr(r(r)epoch_cast_to_tsss r*cCst|tjrF|jdp gD]7}|jd}|rE|jdvrEtd|j |}tj |d}|jdkr:|j dd}| |j |ddq|S) zIConvert SEMI and ANTI joins into equivalent forms that use EXIST instead.rry)SEMIANTI1r8r,Fr}) rrr>rFrkindrOrQr9rRExistsnot_r)rrryrUexistsr(r(r)eliminate_semi_and_anti_joinss     r2cst|tjrddt|jdpgD}t|dkr|}|dd|d\}}|jdj |j f|jd pItj fd d|jd D}|d d t d |jd |}|jd|d d| tj|d}|jdd|jddtj||dddS|S)z Converts a query with a FULL OUTER join to a union of identical queries that use LEFT/RIGHT OUTER joins instead. This transformation currently only works for queries that have a single FULL OUTER join. cSs"g|] \}}|jdkr||fqS)FULL)side)rnrrr(r(r)rs  z-eliminate_full_outer_join..rrlimitNrrryc s.g|]}t|dt|dqS)rr)rrrrnrtablesr(r)rs usingr4leftr-rightr8r<r|F)rir7)rrr>rtrFrrDrirZrrrOrQrRr/r0rrS)rfull_outer_joinsexpression_copyrfull_outer_joinjoin_conditionsanti_join_clauser(r7r)eliminate_full_outer_joins,       rArcCs|jd}|tjD]J}|j|urq |s!|}|d|q |jr*|dd| tj }||rL|j |}|j |j ||<|d|j q |d|j |j q |S)a Some dialects (e.g. Hive, T-SQL, Spark prior to version 3) only allow CTEs to be defined at the top-level, so for example queries like: SELECT * FROM (WITH t(c) AS (SELECT 1) SELECT * FROM t) AS subq are invalid in those dialects. This transformation can be used to ensure all CTEs are moved to the top level so that the final SQL code is valid from a syntax standpoint. TODO: handle name clashes whilst moving CTEs (it can get quite tricky & costly). r<r=Tr) rFrr?rrYrArrZr= find_ancestorrXrr)rtop_level_with inner_with parent_cteror(r(r)move_ctes_to_top_levels&      rFcCs2ddlm}d dd}|D]}|||q|S) zMConverts numeric values used in conditions into explicit boolean expressions.r) ensure_boolsrrr NonecSs\|js t|tjs|jtjjjgtjjRs t|tj r*|j s,| | ddSdSdS)Nr) is_numberrrSubqueryPredicateis_typerTypeUNKNOWN NUMERIC_TYPESrurrTneq)rr(r(r) _ensure_bools  z"ensure_bools.._ensure_boolN)rrr rH)sqlglot.optimizer.canonicalizerGwalk)rrGrPrr(r(r)rGs   rGcCs2|tjD]}|jddD]}|qq|S)Nr)r?rrurr)rrpartr(r(r)unqualify_columnss  rTcCs6t|tjsJ|tjD] }|jr|jq|Sr)rrCreater?UniqueColumnConstraintrAr)r constraintr(r(r)remove_unique_constraintss  rXcCs|Srr()rpr(r(r)srYtmp_storage_providercCslt|tjsJ|jd}tdd|r|jngD}|jdkr4|r4|jr0tjd|j |jdS||S|S)N propertiescss|] }t|tjVqdSr)rrTemporaryProperty)rnpropr(r(r) s  z:ctas_with_tmp_tables_to_create_tmp_view..TABLEzTEMPORARY VIEW)r.r9r) rrrUrFranyrr.rr9)rrZr[ temporaryr(r(r)'ctas_with_tmp_tables_to_create_tmp_views  rbcst|tjsJt|jtj}|jdv}|ra|ra|tj}|ra|jrat|jtjsa|j}dd|jjDfdd|jD| dfdd|jD| tjtjdd | d ||S) a In Hive, the PARTITIONED BY property acts as an extension of a table's schema. When the PARTITIONED BY value is an array of column names, they are transformed into a schema. The corresponding columns are removed from the create statement. >VIEWr_cSsh|]}|jqSr(rNupper)rnvr(r(r)r*sz8move_schema_columns_to_partitioned_by..csg|] }|jvr|qSr(rdr6)rIr(r)r+rz9move_schema_columns_to_partitioned_by..rcsg|]}|vr|qSr(r(r) partitionsr(r)r,rr8r9) rrrUr9Schemar.rPartitionedByPropertyrrZrT)r has_schemais_partitionabler]schemar()rIrgr)%move_schema_columns_to_partitioned_bys   rncCst|tjsJ|tj}|rH|jrHt|jtjrHtdd|jjDrHtj dd|jjDd}|j}|jjD]}| d|q9| d||S)z Spark 3 supports both "HIVEFORMAT" and "DATASOURCE" formats for CREATE TABLE. Currently, SQLGlot uses the DATASOURCE format for Spark 3. css"|] }t|tjo |jVqdSr)rr ColumnDefr.rr(r(r)r^?s z8move_partitioned_by_to_schema_columns..cSsg|]}t|jqSr()rrr9rr(r(r)rBrhz9move_partitioned_by_to_schema_columns..rrr9) rrrUrrjr9riallrrrVrZ)rr] prop_thisrmrpr(r(r)%move_partitioned_by_to_schema_columns3s$    rrcCs(t|tjr|ddd|jD|S)z:Converts struct arguments to aliases, e.g. STRUCT(1 AS y).rcSs,g|]}t|tjrt|j|jn|qSr()rr PropertyEQrWrr9rr(r(r)rQsz&struct_kv_to_alias..)rrStructrZrr)r(r(r)struct_kv_to_aliasLs rucCsddlm}ddlm}m}ddlm}t||D]L}|j}|j d}|j dg}|j r4Jd|rCt dd | tjDsDq||j}||sQJd |t} t|tjs^|gn|D]7} d d | tjD} td d | D} | s{qbt| dkrJd| D]} | ddq| | | qbdd|D}i}|j d}| D][\}}| ||j}tj|tj|dd||j<|D]=}t|jtjr|j |t|jtjs|j}|t|tj!r| |j"dur|j#n|j"qt|tj$r|qq|j|vr4|%|%}t|dks!Jdt|d}|dtj&||jd|rg|D]!\}}||vr[||j dj'kr[|j(sW|dd|||<q;|dt|)q|S)aNhttps://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3178 1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax. 2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view. The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query. You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator. A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator. A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression. A WHERE condition cannot compare any column marked with the (+) operator with a subquery. -- example with WHERE SELECT d.department_name, sum(e.salary) as total_salary FROM departments d, employees e WHERE e.department_id(+) = d.department_id group by department_name -- example of left correlation in select SELECT d.department_name, ( SELECT SUM(e.salary) FROM employees e WHERE e.department_id(+) = d.department_id) AS total_salary FROM departments d; -- example of left correlation in from SELECT d.department_name, t.total_salary FROM departments d, ( SELECT SUM(e.salary) AS total_salary FROM employees e WHERE e.department_id(+) = d.department_id ) t r)traverse_scope) normalize normalized) defaultdictrRrz$Correlated queries are not supportedcss|] }|jdVqdS) join_markNrFrr~r(r(r)r^sz'eliminate_join_marks..z Cannot normalize JOIN predicatescSsg|] }|jdr|qS)rzr{r6r(r(r)rrz(eliminate_join_marks..css|]}|jVqdSrr:r6r(r(r)r^srz4Cannot combine JOIN predicates from different tablesrzFcSsi|]}|j|qSr()r)rnrr(r(r)rqrz(eliminate_join_marks..rLEFT)r9ryr.Nz:Cannot determine which table to use in the new FROM clauser8r.r)*rrvsqlglot.optimizer.normalizerwrx collectionsryreversedrrFris_correlated_subqueryr`r?rrur9rrAndflattenrZrDrrVitemsrirCrrrAParenrTBinaryr:r;WherekeysrBrNr.values)rrvrwrxryscoper rRr joins_onscond join_colsleft_join_tabler old_joins new_joins query_fromr; predicates join_whatprAonly_old_joins new_from_namenjr(r(r)eliminate_join_marksZsz )           rcCst|tjrB|tjD]5}|j}t|tjrq |j}t|tjrAt d}| |tj | |gd}| tj ||dq |S)a= Transform ANY operator to Spark's EXISTS For example, - Postgres: SELECT * FROM tbl WHERE 5 > ANY(tbl.col) - Spark: SELECT * FROM tbl WHERE EXISTS(tbl.col, x -> x < 5) Both ANY and EXISTS accept queries but currently only array expressions are supported for this transformation xrr)rrr>r?Anyr9QueryrArrrTLambdarir/r])rany_exprr9binop lambda_arg lambda_exprr(r(r) any_to_existss     rcst|tjrC|jdrCddlm}|jd}|ddid fd d }|D] }||||j <q(||tj D]}||q<|S) zAEliminates the `WINDOW` query clause by inling each named window.windowsrrNrrr rHcsR|j}|s dS|dddD]}|j|}|r&|||qdS)Nr/)r{r|spec)rr/r%rZrFri)rinherited_windowrargwindow_expressionr(r)_inline_inherited_windows  z9eliminate_window_clause.._inline_inherited_window)rrr rH) rrr>rFrrrrZrNr%r)rrrrrr(rr)eliminate_window_clauses     r)r r r r r )T)rrrrr r)r)rrr r)rrr r)rrrZrr r), __future__rtypingrsqlglotrrsqlglot.errorsrsqlglot.helperrr TYPE_CHECKINGsqlglot._typingrsqlglot.generatorr r+rgrlrxrrrrrrrrr!r*r2rArFrGrTrXrbrnrrrurrrr(r(r(r)sN       1 9 ! 8 @  l       & '       y