U a;@sddlZddlmZddlmZddlmZddlmZddlmZddlmZdd lm Z dd l m Z dd l m Z dd l mZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlm Z d d lm!Z!d d!lm"Z"d d"lm#Z#d d#lm$Z$d d$lm%Z%d d%l&m'Z'd d&l&m(Z(Gd'd(d(ej)Z*Gd)d*d*ej)Z+Gd+d,d,ej,Z-Gd-d.d.ej)Z.Gd/d0d0ej)Z/Gd1d2d2ej)Z0Gd3d4d4eeej)Z1Gd5d6d6ej)Z2Gd7d8d8ej)Z3Gd9d:d:ej)Z4Gd;d<dd>ej)Z6Gd?d@d@ej)Z7GdAdBdBeej)Z8GdCdDdDej)Z9dS)EN)AssertsCompiledSQL)AssertsExecutionResults)config)fixtures) assert_raises)eq_)in_) CursorSQL)Column)Table) bindparam)case)column)Computed)exists)false) ForeignKey)func)Identity)Integer)literal)literal_column)null)select)String)table)testing)text)true)tuple_)union)util)values) DatabaseError)ProgrammingErrorc@s@eZdZdZeddZeddZddZej j dd Z d S) CollateTestTcCs&td|tdtddtdtddS)N some_tableidT primary_keydatadr r rrclsmetadatar2eC:\Users\vtejo\AppData\Local\Temp\pip-unpacked-wheel-nyjtotrf\sqlalchemy\testing\suite\test_select.py define_tables+s   zCollateTest.define_tablescCs(||jjddddddgdS)N collate data1r)r,r collate data2executetablesr(insertr0 connectionr2r2r3 insert_data4s  zCollateTest.insert_datac Cs.tj}t|||W5QRXdSNrdbconnectrr:fetchallselfrresultconnr2r2r3_assert_result>s zCollateTest._assert_resultcCsBtjtj}|t|jj|jjj j | ddgdS)N)r5r6)rr8) rrequiresZget_order_by_collationrrIrr;r(order_bycr,ZcollateZasc)rFZ collationr2r2r3test_collate_order_byBs z!CollateTest.test_collate_order_byN) __name__ __module__ __qualname__ __backend__ classmethodr4r?rIrrJZorder_by_collationrMr2r2r2r3r'(s  r'c@sleZdZdZdZeddZeddZddZd d Z d d Z d dZ ddZ ddZ ejjddZdS)OrderByLabelTestzTest the dialect sends appropriate ORDER BY expressions when labels are used. This essentially exercises the "supports_simple_order_by_label" setting. Tc CsBtd|tdtddtdttdttdtdtd tddS) Nr(r)Tr*xyq2pr.r/r2r2r3r4Ys   zOrderByLabelTest.define_tablesc CsB||jjddddddddddddddd d d dgdS) Nr5rZq1Zp3)r)rTrUrVrXr Zq2p2Zq3p1r9r=r2r2r3r?es zOrderByLabelTest.insert_datac Cs.tj}t|||W5QRXdSr@rArEr2r2r3rIps zOrderByLabelTest._assert_resultcCs6|jj}|jjd}|t||dddgdS)Nlxr5rr )r;r(rLrTlabelrIrrKrFrr\r2r2r3 test_plaintszOrderByLabelTest.test_plaincCs>|jj}|jj|jjd}|t||dddgdS)Nr\r_) r;r(rLrTrUr`rIrrKrar2r2r3test_composed_intysz"OrderByLabelTest.test_composed_intc Cs|jj}|jj|jjd}t|jj|jj d}| t || || dtdfdtdfdtdfgdS) Nr\lyr Zq1p3rdZq2p2rfZq3p1)r;r(rLrTrUr`rlowerrVrXrIrrKdescr#u)rFrr\rhr2r2r3test_composed_multiple~s&z'OrderByLabelTest.test_composed_multiplecCs:|jj}|jjd}|t||dddgdS)Nr\r_r^r]) r;r(rLrTr`rIrrKrjrar2r2r3test_plain_descsz OrderByLabelTest.test_plain_desccCsB|jj}|jj|jjd}|t|| dddgdS)Nr\rercr_) r;r(rLrTrUr`rIrrKrjrar2r2r3test_composed_int_descsz'OrderByLabelTest.test_composed_int_desccCsT|jj}|jj|jjd}tt|jj | | |}| |dddgdS)Nr\)r5r )r5rd)r5rf) r;r(rLrTrUr`rrcountr)Zgroup_byrKrI)rFrexprstmtr2r2r3test_group_by_composeds z'OrderByLabelTest.test_group_by_composedN)rNrOrP__doc__rQrRr4r?rIrbrgrlrmrnrrJZgroup_by_complex_expressionrrr2r2r2r3rSNs   rSc@seZdZdZdZddZdS)ValuesExpressionTest)Ztable_value_constructorTcCsHttdttdtdddddg}t|t|dddgdS)Nr)nameZ my_values)ru)r5Zname1)rZname2)r Zname3) r$rrrr,rr:rall)rFr>Z value_exprr2r2r3 test_tuplessz ValuesExpressionTest.test_tuplesN)rNrOrP __requires__rQrwr2r2r2r3rtsrtc@s0eZdZdZeddZeddZdIdd ZdJd d Zd d Z ddZ e j j ddZe j jddZe jdddgdddgdddgdde j jddZe j j ddZe j jddZe j jd d!Ze j jd"d#Ze j j d$d%Ze j jd&d'Ze j jd(d)Ze j jd*d+Ze j j d,d-Ze j jd.d/Ze j jd0d1Z e j jd2d3Z!e j j d4d5Z"e j jd6d7Z#e j jd8d9Z$e j j%d:d;Z&e j j%e j j'dd?Z)e j j*d@dAZ+e j j*e j j'dBdCZ,e j j%e j j*dDdEZ-e j j%e j j*e j j'dFdGZ.dHS)KFetchLimitOffsetTestTcCs*td|tdtddtdttdtdSNr(r)Tr*rTrUr r rr/r2r2r3r4s z"FetchLimitOffsetTest.define_tablesc CsJ||jjddddddddddddddddddddgdS)Nr5rr)rTrUr rZrdr9r=r2r2r3r?s      z FetchLimitOffsetTest.insert_datar2FcCsT|r:|||}tt|t|tt|t|nt||||dSr@)r:rDrlenset)rFr>rrGparamsset_Z query_resr2r2r3rIs z#FetchLimitOffsetTest._assert_resultcCs(tjjdd}t||||dS)NT)Zclose_with_result)rrBrCrZexec_driver_sqlrDrFrrGrrHr2r2r3_assert_result_strsz'FetchLimitOffsetTest._assert_result_strcCsP|jj}t||jj}|||dddg|||ddddgdSNrr5r5rrrr r r r rZ)r;r(rrKrLr)rIlimit)rFr>rrqr2r2r3test_simple_limitsz&FetchLimitOffsetTest.test_simple_limitcCsL|jj}t|jjd}tt|t|}| ||dgdS)Nr5r]) r;r(rrLr)rZscalar_subqueryr"ZsubqueryrI)rFr>rrqrkr2r2r3 test_limit_render_multiple_timessz5FetchLimitOffsetTest.test_limit_render_multiple_timescCsZ|jj}||t||jjdddg||t||jjddddgdSrr;r(rIrrKrLr)fetchrFr>rr2r2r3test_simple_fetchsz&FetchLimitOffsetTest.test_simple_fetchcCsZ|jj}||t||jjddddg||t||jjdddgdS)NrrrZrZrdrdrZr}r r;r(rIrrKrLr)offsetrr2r2r3test_simple_offsetsz'FetchLimitOffsetTest.test_simple_offset)rrrr5)r r)r r5cases)argnamescCsl|jj}|jid}dddddg}|D]@\}}||||}||t||jj| ||q&dS)N)Zcompiled_cacherrrrr) r;r(Zexecution_optionsrIrrKrLr)rr)rFr>rrZ assert_datarrexpectedr2r2r3test_simple_limit_offset s  z-FetchLimitOffsetTest.test_simple_limit_offsetcCsf|jj}||t||jjddddg||t||jjdddddgdS)Nrr5rrr rr r;r(rIrrKrLr)rrrr2r2r3test_simple_fetch_offset!sz-FetchLimitOffsetTest.test_simple_fetch_offsetcCs2|jj}|j|t|ddddddgdddS) N rrrrrTr)r;r(rIrrrr2r2r3test_fetch_offset_no_order0s  z/FetchLimitOffsetTest.test_fetch_offset_no_orderc Csb|jj}||t||jjddddddg||t||jjdddddgdS)Nrrrrrrr5rrr2r2r3test_simple_offset_zero:s  z,FetchLimitOffsetTest.test_simple_offset_zerocCsX|jj}t||jjdd}|jt j j ddid}t |}| |ddgdS z7test that 'literal binds' mode works - no bound params.rr5 literal_bindsT)dialectcompile_kwargsrrN)r;r(rrKrLr)rrcompilerrBrstrrrFrrqZsqlr2r2r3test_limit_offset_nobindsIsz.FetchLimitOffsetTest.test_limit_offset_nobindscCsX|jj}t||jjdd}|jt j j ddid}t |}| |ddgdSr)r;r(rrKrLr)rrrrrBrrrrr2r2r3test_fetch_offset_nobindsVsz.FetchLimitOffsetTest.test_fetch_offset_nobindscCsr|jj}|j|t||jjtdddgddid|j|t||jjtddddgddiddS)Nlrrrrrr ) r;r(rIrrKrLr)rrrr2r2r3test_bound_limitcsz%FetchLimitOffsetTest.test_bound_limitcCsv|jj}|j|t||jjtddddgddid|j|t||jjtdddddgddiddS) Norrrrrrr5) r;r(rIrrKrLr)rrrr2r2r3test_bound_offsetts z&FetchLimitOffsetTest.test_bound_offsetcCs|jj}|j|t||jjtd tdddgdddd|j|t||jjtd tddd d gd ddddS) Nrrrrrr5)rrrrrr ) r;r(rIrrKrLr)rrrrr2r2r3test_bound_limit_offsets2 z,FetchLimitOffsetTest.test_bound_limit_offsetcCs|jj}|j|t||jjtd tdddgdddd|j|t||jjtd tddd d gd ddddS) Nfrrrrr5)rrrrrr ) r;r(rIrrKrLr)rrrrr2r2r3test_bound_fetch_offsets2 z,FetchLimitOffsetTest.test_bound_fetch_offsetcCs>|jj}||t||jjtdtdddgdS)N12rr) r;r(rIrrKrLr)rrrr2r2r3test_expr_offsetsz%FetchLimitOffsetTest.test_expr_offsetcCs@|jj}||t||jjtdtddddgdS)Nrrrrr) r;r(rIrrKrLr)rrrr2r2r3test_expr_limitsz$FetchLimitOffsetTest.test_expr_limitcCsP|jj}||t||jjtdtd tdtdddgdSNrrr r;r(rIrrKrLr)rrrrr2r2r3test_expr_limit_offsetsz+FetchLimitOffsetTest.test_expr_limit_offsetcCsP|jj}||t||jjtdtd tdtdddgdSr) r;r(rIrrKrLr)rrrrr2r2r3test_expr_fetch_offsetsz+FetchLimitOffsetTest.test_expr_fetch_offsetcCs~|jj}||t||jjdt dt dddg||t||jjdt dt ddddgdS)Nrrrrr r) r;r(rIrrKrLr)rrrrr2r2r3test_simple_limit_expr_offsets. z2FetchLimitOffsetTest.test_simple_limit_expr_offsetcCs||jj}||t||jjtdtd dddg||t||jjtdtd dddgdS)Nrrrrr5rrrr2r2r3test_expr_limit_simple_offsets. z2FetchLimitOffsetTest.test_expr_limit_simple_offsetcCsr|jj}|j|t||jjjdddddgdd|j|t||jjjddddddgdddS) Nr5T with_tiesrrrr r r;r(rIrrKrLrTrjrrr2r2r3test_simple_fetch_ties sz+FetchLimitOffsetTest.test_simple_fetch_tiescCs^|jj}|t||jjjdddd }t |ddt t |t dddgdS)NrTrrrrr r;r(r:rrKrLrTrrrDrrrFr>rfar2r2r3test_fetch_offset_tiessz+FetchLimitOffsetTest.test_fetch_offset_tiescCsl|jj}||t||jjjddddddg||t||jjjdddddd gdS) NrTrr5rrr rr) r;r(rIrrKrLrTrrrr2r2r3#test_fetch_offset_ties_exact_number)s2 z8FetchLimitOffsetTest.test_fetch_offset_ties_exact_numbercCs4|jj}||t||jjjddddgdS)NTpercentrrrr2r2r3test_simple_fetch_percent?s z.FetchLimitOffsetTest.test_simple_fetch_percentcCs<|jj}||t||jjjddddddgdS)N(Trr5rrrrr2r2r3test_fetch_offset_percentHsz.FetchLimitOffsetTest.test_fetch_offset_percentcCs@|jj}|j|t||jjjddddddgdddS)NrTrrrrrrrr2r2r3test_simple_fetch_percent_tiesUs z3FetchLimitOffsetTest.test_simple_fetch_percent_tiescCs`|jj}|t||jjjddddd }t |ddt t |t dddgdS) NrTrrrrrrrrr2r2r3test_fetch_offset_percent_tiesbsz3FetchLimitOffsetTest.test_fetch_offset_percent_tiesN)r2F)r2)/rNrOrPrQrRr4r?rIrrrrrJZ fetch_firstrrr combinationsrrZfetch_no_order_byrrrrZbound_limit_offsetrrrrZsql_expression_limit_offsetrrrrrrZ fetch_tiesrZfetch_offset_with_optionsrrZ fetch_percentrrrrr2r2r2r3rys                          ryc@sZeZdZdZdddZeddZeddZd d Zd d Z d dZ ddZ ddZ dS)JoinTestTr2c Cs0tj}t||||W5QRXdSr@rArr2r2r3rIts zJoinTest._assert_resultc Cs@td|tdtddtd|tdtddtdtddd dS) Nar)Tr*ba_idza.idFZnullable)r r rrr/r2r2r3r4xs zJoinTest.define_tablesc Csj||jjddiddiddiddiddig||jjddddddddddddgdS)Nr)r5rr rZrd)r)r)r:r;rr<rr=r2r2r3r?s   zJoinTest.insert_datacCsN|dd\}}t|||||jj|jj}||ddddgdSNrr)r5r5r5)r5rr5)rrZr)r rdr r;r select_fromjoinrKrLr)rIrFrrrqr2r2r3test_inner_join_fks&zJoinTest.test_inner_join_fkc Csn|dd\}}t||||t|jj|jj}||ddt ddddd gd d d d gDdS)NrrcSs g|]\\}\}}|||fqSr2r2).0rrrLr2r2r3 s z1JoinTest.test_inner_join_true..r]r^r_rZrc)r5r5r)rZr)rdr ) r;rrrr rKrLr)rI itertoolsproductrr2r2r3test_inner_join_trues"    zJoinTest.test_inner_join_truecCsJ|dd\}}t||||t|jj|jj}||gdS)Nrr) r;rrrrrKrLr)rIrr2r2r3test_inner_join_falses  zJoinTest.test_inner_join_falsecCsT|dd\}}t||||t|jj|jj}||dddddgdS)Nrr)r5NN)rNN)r NN)rZNN)rdNN) r;rrZ outerjoinrrKrLr)rIrr2r2r3test_outer_join_falses"  zJoinTest.test_outer_join_falsecCsN|dd\}}t|||||jj|jj}||ddddgdSrrrr2r2r3test_outer_join_fks&zJoinTest.test_outer_join_fkN)r2) rNrOrPrQrIrRr4r?rrrrrr2r2r2r3rqs    rc@seZdZdZeddZeddZdddZd d Zd d Z e j j e j j d dZe j jddZddZe j j ddZddZdS)CompoundSelectTestTcCs*td|tdtddtdttdtdSrzr{r/r2r2r3r4s z CompoundSelectTest.define_tablesc Cs@||jjddddddddddddddddgdS)Nr5rr|r rZrdr9r=r2r2r3r?s     zCompoundSelectTest.insert_datar2c Cs0tj}t||||W5QRXdSr@rArr2r2r3rIs z!CompoundSelectTest._assert_resultcCs\|jj}t||jjdk}t||jjdk}t||}|||j jddgdSNrr rr) r;r(rwhererLr)r"rIrKselected_columnsrFrs1s2u1r2r2r3test_plain_unions  z#CompoundSelectTest.test_plain_unioncCsd|jj}t||jjdk}t||jjdk}t||}|| |j jddgdSr) r;r(rrrLr)r"aliasrIrKrrr2r2r3test_select_from_plain_unions z/CompoundSelectTest.test_select_from_plain_unioncCs|jj}t||jjdkd|jj}t||jjdkd|jj}t||d}| ||j jddgdSNrr5r rr) r;r(rrrLr)rrKr"rIrrr2r2r3&test_limit_offset_selectable_in_unionss&& z9CompoundSelectTest.test_limit_offset_selectable_in_unionscCsv|jj}t||jjdk|jj}t||jjdk|jj}t||d}| ||j jddgdSr) r;r(rrrLr)rKr"rrIrrr2r2r3"test_order_by_selectable_in_unionss   z5CompoundSelectTest.test_order_by_selectable_in_unionscCsj|jj}t||jjdk}t||jjdk}t||d}| | |j jddgdSr) r;r(rrrLr)distinctr"rrIrKrrr2r2r3"test_distinct_selectable_in_unionss z5CompoundSelectTest.test_distinct_selectable_in_unionscCs|jj}t||jjdkd|jj}t||jjdkd|jj}t|| }| |d|jjddgdSr) r;r(rrrLr)rrKr"rrIrr2r2r3&test_limit_offset_in_unions_from_alias's&&z9CompoundSelectTest.test_limit_offset_in_unions_from_aliascCs|jj}t||jjdkd|jj}t||jjdkd|jj}t ||d}| ||j jddgdSr) r;r(rrrLr)rrKrr"rIrrr2r2r3.test_limit_offset_aliased_selectable_in_unions3s,       zACompoundSelectTest.test_limit_offset_aliased_selectable_in_unionsN)r2)rNrOrPrQrRr4r?rIrrrrJZorder_by_col_from_unionZ/parens_in_union_contained_select_w_limit_offsetrZ0parens_in_union_contained_select_wo_limit_offsetrrrrr2r2r2r3rs"        rc@sleZdZdZdZeddZeddZddZd d Z d d Z d dZ e j jddZe j jddZdS)PostCompileParamsTestT)Zstandard_cursor_sqlc Cs6td|tdtddtdttdttdtddS Nr(r)Tr*rTrUzrWr.r/r2r2r3r4Ss  z#PostCompileParamsTest.define_tablesc CsH||jjdddddddddddddddddd d dgdS Nr5rz1)r)rTrUrr z2rZz3rdz4r9r=r2r2r3r?^s     z!PostCompileParamsTest.insert_datacCs<|jj}t|jj|jjtdddk}||didS)NrVTliteral_executezISELECT some_table.id FROM some_table WHERE some_table.x = [POSTCOMPILE_q] r;r(rrLr)rrTrassert_compilerFrrqr2r2r3 test_compilejs z"PostCompileParamsTest.test_compilecCsB|jj}t|jj|jjtddddk}|j|didddS)NrVrTrz