select.c 172 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395339633973398339934003401340234033404340534063407340834093410341134123413341434153416341734183419342034213422342334243425342634273428342934303431343234333434343534363437343834393440344134423443344434453446344734483449345034513452345334543455345634573458345934603461346234633464346534663467346834693470347134723473347434753476347734783479348034813482348334843485348634873488348934903491349234933494349534963497349834993500350135023503350435053506350735083509351035113512351335143515351635173518351935203521352235233524352535263527352835293530353135323533353435353536353735383539354035413542354335443545354635473548354935503551355235533554355535563557355835593560356135623563356435653566356735683569357035713572357335743575357635773578357935803581358235833584358535863587358835893590359135923593359435953596359735983599360036013602360336043605360636073608360936103611361236133614361536163617361836193620362136223623362436253626362736283629363036313632363336343635363636373638363936403641364236433644364536463647364836493650365136523653365436553656365736583659366036613662366336643665366636673668366936703671367236733674367536763677367836793680368136823683368436853686368736883689369036913692369336943695369636973698369937003701370237033704370537063707370837093710371137123713371437153716371737183719372037213722372337243725372637273728372937303731373237333734373537363737373837393740374137423743374437453746374737483749375037513752375337543755375637573758375937603761376237633764376537663767376837693770377137723773377437753776377737783779378037813782378337843785378637873788378937903791379237933794379537963797379837993800380138023803380438053806380738083809381038113812381338143815381638173818381938203821382238233824382538263827382838293830383138323833383438353836383738383839384038413842384338443845384638473848384938503851385238533854385538563857385838593860386138623863386438653866386738683869387038713872387338743875387638773878387938803881388238833884388538863887388838893890389138923893389438953896389738983899390039013902390339043905390639073908390939103911391239133914391539163917391839193920392139223923392439253926392739283929393039313932393339343935393639373938393939403941394239433944394539463947394839493950395139523953395439553956395739583959396039613962396339643965396639673968396939703971397239733974397539763977397839793980398139823983398439853986398739883989399039913992399339943995399639973998399940004001400240034004400540064007400840094010401140124013401440154016401740184019402040214022402340244025402640274028402940304031403240334034403540364037403840394040404140424043404440454046404740484049405040514052405340544055405640574058405940604061406240634064406540664067406840694070407140724073407440754076407740784079408040814082408340844085408640874088408940904091409240934094409540964097409840994100410141024103410441054106410741084109411041114112411341144115411641174118411941204121412241234124412541264127412841294130413141324133413441354136413741384139414041414142414341444145414641474148414941504151415241534154415541564157415841594160416141624163416441654166416741684169417041714172417341744175417641774178417941804181418241834184418541864187418841894190419141924193419441954196419741984199420042014202420342044205420642074208420942104211421242134214421542164217421842194220422142224223422442254226422742284229423042314232423342344235423642374238423942404241424242434244424542464247424842494250425142524253425442554256425742584259426042614262426342644265426642674268426942704271427242734274427542764277427842794280428142824283428442854286428742884289429042914292429342944295429642974298429943004301430243034304430543064307430843094310431143124313431443154316431743184319432043214322432343244325432643274328432943304331433243334334433543364337433843394340434143424343434443454346434743484349435043514352435343544355435643574358435943604361436243634364436543664367436843694370437143724373437443754376437743784379438043814382438343844385438643874388438943904391439243934394439543964397439843994400440144024403440444054406440744084409441044114412441344144415441644174418441944204421442244234424442544264427442844294430443144324433443444354436443744384439444044414442444344444445444644474448444944504451445244534454445544564457445844594460446144624463446444654466446744684469447044714472447344744475447644774478447944804481448244834484448544864487448844894490449144924493449444954496449744984499450045014502450345044505450645074508450945104511451245134514451545164517451845194520452145224523452445254526452745284529453045314532453345344535453645374538453945404541454245434544454545464547454845494550455145524553455445554556455745584559456045614562456345644565456645674568456945704571457245734574457545764577457845794580458145824583458445854586458745884589459045914592459345944595459645974598459946004601460246034604460546064607460846094610461146124613461446154616461746184619462046214622462346244625462646274628462946304631463246334634463546364637463846394640464146424643464446454646464746484649465046514652465346544655465646574658465946604661466246634664466546664667466846694670467146724673467446754676467746784679468046814682468346844685468646874688468946904691469246934694469546964697469846994700470147024703470447054706470747084709471047114712471347144715471647174718471947204721472247234724472547264727472847294730473147324733473447354736473747384739474047414742474347444745474647474748474947504751475247534754475547564757475847594760476147624763476447654766476747684769477047714772477347744775477647774778477947804781478247834784478547864787478847894790479147924793479447954796479747984799480048014802480348044805480648074808480948104811481248134814481548164817481848194820482148224823482448254826482748284829483048314832483348344835483648374838483948404841484248434844484548464847484848494850485148524853485448554856485748584859486048614862486348644865486648674868486948704871487248734874487548764877
  1. /*
  2. ** 2001 September 15
  3. **
  4. ** The author disclaims copyright to this source code. In place of
  5. ** a legal notice, here is a blessing:
  6. **
  7. ** May you do good and not evil.
  8. ** May you find forgiveness for yourself and forgive others.
  9. ** May you share freely, never taking more than you give.
  10. **
  11. *************************************************************************
  12. ** This file contains C code routines that are called by the parser
  13. ** to handle SELECT statements in SQLite.
  14. */
  15. #include "sqliteInt.h"
  16. /*
  17. ** Delete all the content of a Select structure but do not deallocate
  18. ** the select structure itself.
  19. */
  20. static void clearSelect(sqlite3 *db, Select *p){
  21. sqlite3ExprListDelete(db, p->pEList);
  22. sqlite3SrcListDelete(db, p->pSrc);
  23. sqlite3ExprDelete(db, p->pWhere);
  24. sqlite3ExprListDelete(db, p->pGroupBy);
  25. sqlite3ExprDelete(db, p->pHaving);
  26. sqlite3ExprListDelete(db, p->pOrderBy);
  27. sqlite3SelectDelete(db, p->pPrior);
  28. sqlite3ExprDelete(db, p->pLimit);
  29. sqlite3ExprDelete(db, p->pOffset);
  30. }
  31. /*
  32. ** Initialize a SelectDest structure.
  33. */
  34. void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
  35. pDest->eDest = (u8)eDest;
  36. pDest->iSDParm = iParm;
  37. pDest->affSdst = 0;
  38. pDest->iSdst = 0;
  39. pDest->nSdst = 0;
  40. }
  41. /*
  42. ** Allocate a new Select structure and return a pointer to that
  43. ** structure.
  44. */
  45. Select *sqlite3SelectNew(
  46. Parse *pParse, /* Parsing context */
  47. ExprList *pEList, /* which columns to include in the result */
  48. SrcList *pSrc, /* the FROM clause -- which tables to scan */
  49. Expr *pWhere, /* the WHERE clause */
  50. ExprList *pGroupBy, /* the GROUP BY clause */
  51. Expr *pHaving, /* the HAVING clause */
  52. ExprList *pOrderBy, /* the ORDER BY clause */
  53. u16 selFlags, /* Flag parameters, such as SF_Distinct */
  54. Expr *pLimit, /* LIMIT value. NULL means not used */
  55. Expr *pOffset /* OFFSET value. NULL means no offset */
  56. ){
  57. Select *pNew;
  58. Select standin;
  59. sqlite3 *db = pParse->db;
  60. pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
  61. assert( db->mallocFailed || !pOffset || pLimit ); /* OFFSET implies LIMIT */
  62. if( pNew==0 ){
  63. assert( db->mallocFailed );
  64. pNew = &standin;
  65. memset(pNew, 0, sizeof(*pNew));
  66. }
  67. if( pEList==0 ){
  68. pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db,TK_ALL,0));
  69. }
  70. pNew->pEList = pEList;
  71. if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc));
  72. pNew->pSrc = pSrc;
  73. pNew->pWhere = pWhere;
  74. pNew->pGroupBy = pGroupBy;
  75. pNew->pHaving = pHaving;
  76. pNew->pOrderBy = pOrderBy;
  77. pNew->selFlags = selFlags;
  78. pNew->op = TK_SELECT;
  79. pNew->pLimit = pLimit;
  80. pNew->pOffset = pOffset;
  81. assert( pOffset==0 || pLimit!=0 );
  82. pNew->addrOpenEphm[0] = -1;
  83. pNew->addrOpenEphm[1] = -1;
  84. pNew->addrOpenEphm[2] = -1;
  85. if( db->mallocFailed ) {
  86. clearSelect(db, pNew);
  87. if( pNew!=&standin ) sqlite3DbFree(db, pNew);
  88. pNew = 0;
  89. }else{
  90. assert( pNew->pSrc!=0 || pParse->nErr>0 );
  91. }
  92. assert( pNew!=&standin );
  93. return pNew;
  94. }
  95. /*
  96. ** Delete the given Select structure and all of its substructures.
  97. */
  98. void sqlite3SelectDelete(sqlite3 *db, Select *p){
  99. if( p ){
  100. clearSelect(db, p);
  101. sqlite3DbFree(db, p);
  102. }
  103. }
  104. /*
  105. ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the
  106. ** type of join. Return an integer constant that expresses that type
  107. ** in terms of the following bit values:
  108. **
  109. ** JT_INNER
  110. ** JT_CROSS
  111. ** JT_OUTER
  112. ** JT_NATURAL
  113. ** JT_LEFT
  114. ** JT_RIGHT
  115. **
  116. ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
  117. **
  118. ** If an illegal or unsupported join type is seen, then still return
  119. ** a join type, but put an error in the pParse structure.
  120. */
  121. int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
  122. int jointype = 0;
  123. Token *apAll[3];
  124. Token *p;
  125. /* 0123456789 123456789 123456789 123 */
  126. static const char zKeyText[] = "naturaleftouterightfullinnercross";
  127. static const struct {
  128. u8 i; /* Beginning of keyword text in zKeyText[] */
  129. u8 nChar; /* Length of the keyword in characters */
  130. u8 code; /* Join type mask */
  131. } aKeyword[] = {
  132. /* natural */ { 0, 7, JT_NATURAL },
  133. /* left */ { 6, 4, JT_LEFT|JT_OUTER },
  134. /* outer */ { 10, 5, JT_OUTER },
  135. /* right */ { 14, 5, JT_RIGHT|JT_OUTER },
  136. /* full */ { 19, 4, JT_LEFT|JT_RIGHT|JT_OUTER },
  137. /* inner */ { 23, 5, JT_INNER },
  138. /* cross */ { 28, 5, JT_INNER|JT_CROSS },
  139. };
  140. int i, j;
  141. apAll[0] = pA;
  142. apAll[1] = pB;
  143. apAll[2] = pC;
  144. for(i=0; i<3 && apAll[i]; i++){
  145. p = apAll[i];
  146. for(j=0; j<ArraySize(aKeyword); j++){
  147. if( p->n==aKeyword[j].nChar
  148. && sqlite3StrNICmp((char*)p->z, &zKeyText[aKeyword[j].i], p->n)==0 ){
  149. jointype |= aKeyword[j].code;
  150. break;
  151. }
  152. }
  153. testcase( j==0 || j==1 || j==2 || j==3 || j==4 || j==5 || j==6 );
  154. if( j>=ArraySize(aKeyword) ){
  155. jointype |= JT_ERROR;
  156. break;
  157. }
  158. }
  159. if(
  160. (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||
  161. (jointype & JT_ERROR)!=0
  162. ){
  163. const char *zSp = " ";
  164. assert( pB!=0 );
  165. if( pC==0 ){ zSp++; }
  166. sqlite3ErrorMsg(pParse, "unknown or unsupported join type: "
  167. "%T %T%s%T", pA, pB, zSp, pC);
  168. jointype = JT_INNER;
  169. }else if( (jointype & JT_OUTER)!=0
  170. && (jointype & (JT_LEFT|JT_RIGHT))!=JT_LEFT ){
  171. sqlite3ErrorMsg(pParse,
  172. "RIGHT and FULL OUTER JOINs are not currently supported");
  173. jointype = JT_INNER;
  174. }
  175. return jointype;
  176. }
  177. /*
  178. ** Return the index of a column in a table. Return -1 if the column
  179. ** is not contained in the table.
  180. */
  181. static int columnIndex(Table *pTab, const char *zCol){
  182. int i;
  183. for(i=0; i<pTab->nCol; i++){
  184. if( sqlite3StrICmp(pTab->aCol[i].zName, zCol)==0 ) return i;
  185. }
  186. return -1;
  187. }
  188. /*
  189. ** Search the first N tables in pSrc, from left to right, looking for a
  190. ** table that has a column named zCol.
  191. **
  192. ** When found, set *piTab and *piCol to the table index and column index
  193. ** of the matching column and return TRUE.
  194. **
  195. ** If not found, return FALSE.
  196. */
  197. static int tableAndColumnIndex(
  198. SrcList *pSrc, /* Array of tables to search */
  199. int N, /* Number of tables in pSrc->a[] to search */
  200. const char *zCol, /* Name of the column we are looking for */
  201. int *piTab, /* Write index of pSrc->a[] here */
  202. int *piCol /* Write index of pSrc->a[*piTab].pTab->aCol[] here */
  203. ){
  204. int i; /* For looping over tables in pSrc */
  205. int iCol; /* Index of column matching zCol */
  206. assert( (piTab==0)==(piCol==0) ); /* Both or neither are NULL */
  207. for(i=0; i<N; i++){
  208. iCol = columnIndex(pSrc->a[i].pTab, zCol);
  209. if( iCol>=0 ){
  210. if( piTab ){
  211. *piTab = i;
  212. *piCol = iCol;
  213. }
  214. return 1;
  215. }
  216. }
  217. return 0;
  218. }
  219. /*
  220. ** This function is used to add terms implied by JOIN syntax to the
  221. ** WHERE clause expression of a SELECT statement. The new term, which
  222. ** is ANDed with the existing WHERE clause, is of the form:
  223. **
  224. ** (tab1.col1 = tab2.col2)
  225. **
  226. ** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
  227. ** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
  228. ** column iColRight of tab2.
  229. */
  230. static void addWhereTerm(
  231. Parse *pParse, /* Parsing context */
  232. SrcList *pSrc, /* List of tables in FROM clause */
  233. int iLeft, /* Index of first table to join in pSrc */
  234. int iColLeft, /* Index of column in first table */
  235. int iRight, /* Index of second table in pSrc */
  236. int iColRight, /* Index of column in second table */
  237. int isOuterJoin, /* True if this is an OUTER join */
  238. Expr **ppWhere /* IN/OUT: The WHERE clause to add to */
  239. ){
  240. sqlite3 *db = pParse->db;
  241. Expr *pE1;
  242. Expr *pE2;
  243. Expr *pEq;
  244. assert( iLeft<iRight );
  245. assert( pSrc->nSrc>iRight );
  246. assert( pSrc->a[iLeft].pTab );
  247. assert( pSrc->a[iRight].pTab );
  248. pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iColLeft);
  249. pE2 = sqlite3CreateColumnExpr(db, pSrc, iRight, iColRight);
  250. pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2, 0);
  251. if( pEq && isOuterJoin ){
  252. ExprSetProperty(pEq, EP_FromJoin);
  253. assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
  254. ExprSetVVAProperty(pEq, EP_NoReduce);
  255. pEq->iRightJoinTable = (i16)pE2->iTable;
  256. }
  257. *ppWhere = sqlite3ExprAnd(db, *ppWhere, pEq);
  258. }
  259. /*
  260. ** Set the EP_FromJoin property on all terms of the given expression.
  261. ** And set the Expr.iRightJoinTable to iTable for every term in the
  262. ** expression.
  263. **
  264. ** The EP_FromJoin property is used on terms of an expression to tell
  265. ** the LEFT OUTER JOIN processing logic that this term is part of the
  266. ** join restriction specified in the ON or USING clause and not a part
  267. ** of the more general WHERE clause. These terms are moved over to the
  268. ** WHERE clause during join processing but we need to remember that they
  269. ** originated in the ON or USING clause.
  270. **
  271. ** The Expr.iRightJoinTable tells the WHERE clause processing that the
  272. ** expression depends on table iRightJoinTable even if that table is not
  273. ** explicitly mentioned in the expression. That information is needed
  274. ** for cases like this:
  275. **
  276. ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
  277. **
  278. ** The where clause needs to defer the handling of the t1.x=5
  279. ** term until after the t2 loop of the join. In that way, a
  280. ** NULL t2 row will be inserted whenever t1.x!=5. If we do not
  281. ** defer the handling of t1.x=5, it will be processed immediately
  282. ** after the t1 loop and rows with t1.x!=5 will never appear in
  283. ** the output, which is incorrect.
  284. */
  285. static void setJoinExpr(Expr *p, int iTable){
  286. while( p ){
  287. ExprSetProperty(p, EP_FromJoin);
  288. assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
  289. ExprSetVVAProperty(p, EP_NoReduce);
  290. p->iRightJoinTable = (i16)iTable;
  291. setJoinExpr(p->pLeft, iTable);
  292. p = p->pRight;
  293. }
  294. }
  295. /*
  296. ** This routine processes the join information for a SELECT statement.
  297. ** ON and USING clauses are converted into extra terms of the WHERE clause.
  298. ** NATURAL joins also create extra WHERE clause terms.
  299. **
  300. ** The terms of a FROM clause are contained in the Select.pSrc structure.
  301. ** The left most table is the first entry in Select.pSrc. The right-most
  302. ** table is the last entry. The join operator is held in the entry to
  303. ** the left. Thus entry 0 contains the join operator for the join between
  304. ** entries 0 and 1. Any ON or USING clauses associated with the join are
  305. ** also attached to the left entry.
  306. **
  307. ** This routine returns the number of errors encountered.
  308. */
  309. static int sqliteProcessJoin(Parse *pParse, Select *p){
  310. SrcList *pSrc; /* All tables in the FROM clause */
  311. int i, j; /* Loop counters */
  312. struct SrcList_item *pLeft; /* Left table being joined */
  313. struct SrcList_item *pRight; /* Right table being joined */
  314. pSrc = p->pSrc;
  315. pLeft = &pSrc->a[0];
  316. pRight = &pLeft[1];
  317. for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){
  318. Table *pLeftTab = pLeft->pTab;
  319. Table *pRightTab = pRight->pTab;
  320. int isOuter;
  321. if( NEVER(pLeftTab==0 || pRightTab==0) ) continue;
  322. isOuter = (pRight->jointype & JT_OUTER)!=0;
  323. /* When the NATURAL keyword is present, add WHERE clause terms for
  324. ** every column that the two tables have in common.
  325. */
  326. if( pRight->jointype & JT_NATURAL ){
  327. if( pRight->pOn || pRight->pUsing ){
  328. sqlite3ErrorMsg(pParse, "a NATURAL join may not have "
  329. "an ON or USING clause", 0);
  330. return 1;
  331. }
  332. for(j=0; j<pRightTab->nCol; j++){
  333. char *zName; /* Name of column in the right table */
  334. int iLeft; /* Matching left table */
  335. int iLeftCol; /* Matching column in the left table */
  336. zName = pRightTab->aCol[j].zName;
  337. if( tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol) ){
  338. addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, j,
  339. isOuter, &p->pWhere);
  340. }
  341. }
  342. }
  343. /* Disallow both ON and USING clauses in the same join
  344. */
  345. if( pRight->pOn && pRight->pUsing ){
  346. sqlite3ErrorMsg(pParse, "cannot have both ON and USING "
  347. "clauses in the same join");
  348. return 1;
  349. }
  350. /* Add the ON clause to the end of the WHERE clause, connected by
  351. ** an AND operator.
  352. */
  353. if( pRight->pOn ){
  354. if( isOuter ) setJoinExpr(pRight->pOn, pRight->iCursor);
  355. p->pWhere = sqlite3ExprAnd(pParse->db, p->pWhere, pRight->pOn);
  356. pRight->pOn = 0;
  357. }
  358. /* Create extra terms on the WHERE clause for each column named
  359. ** in the USING clause. Example: If the two tables to be joined are
  360. ** A and B and the USING clause names X, Y, and Z, then add this
  361. ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
  362. ** Report an error if any column mentioned in the USING clause is
  363. ** not contained in both tables to be joined.
  364. */
  365. if( pRight->pUsing ){
  366. IdList *pList = pRight->pUsing;
  367. for(j=0; j<pList->nId; j++){
  368. char *zName; /* Name of the term in the USING clause */
  369. int iLeft; /* Table on the left with matching column name */
  370. int iLeftCol; /* Column number of matching column on the left */
  371. int iRightCol; /* Column number of matching column on the right */
  372. zName = pList->a[j].zName;
  373. iRightCol = columnIndex(pRightTab, zName);
  374. if( iRightCol<0
  375. || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol)
  376. ){
  377. sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
  378. "not present in both tables", zName);
  379. return 1;
  380. }
  381. addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
  382. isOuter, &p->pWhere);
  383. }
  384. }
  385. }
  386. return 0;
  387. }
  388. /*
  389. ** Insert code into "v" that will push the record on the top of the
  390. ** stack into the sorter.
  391. */
  392. static void pushOntoSorter(
  393. Parse *pParse, /* Parser context */
  394. ExprList *pOrderBy, /* The ORDER BY clause */
  395. Select *pSelect, /* The whole SELECT statement */
  396. int regData /* Register holding data to be sorted */
  397. ){
  398. Vdbe *v = pParse->pVdbe;
  399. int nExpr = pOrderBy->nExpr;
  400. int regBase = sqlite3GetTempRange(pParse, nExpr+2);
  401. int regRecord = sqlite3GetTempReg(pParse);
  402. int op;
  403. sqlite3ExprCacheClear(pParse);
  404. sqlite3ExprCodeExprList(pParse, pOrderBy, regBase, 0);
  405. sqlite3VdbeAddOp2(v, OP_Sequence, pOrderBy->iECursor, regBase+nExpr);
  406. sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+1, 1);
  407. sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nExpr + 2, regRecord);
  408. if( pSelect->selFlags & SF_UseSorter ){
  409. op = OP_SorterInsert;
  410. }else{
  411. op = OP_IdxInsert;
  412. }
  413. sqlite3VdbeAddOp2(v, op, pOrderBy->iECursor, regRecord);
  414. sqlite3ReleaseTempReg(pParse, regRecord);
  415. sqlite3ReleaseTempRange(pParse, regBase, nExpr+2);
  416. if( pSelect->iLimit ){
  417. int addr1, addr2;
  418. int iLimit;
  419. if( pSelect->iOffset ){
  420. iLimit = pSelect->iOffset+1;
  421. }else{
  422. iLimit = pSelect->iLimit;
  423. }
  424. addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit);
  425. sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
  426. addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
  427. sqlite3VdbeJumpHere(v, addr1);
  428. sqlite3VdbeAddOp1(v, OP_Last, pOrderBy->iECursor);
  429. sqlite3VdbeAddOp1(v, OP_Delete, pOrderBy->iECursor);
  430. sqlite3VdbeJumpHere(v, addr2);
  431. }
  432. }
  433. /*
  434. ** Add code to implement the OFFSET
  435. */
  436. static void codeOffset(
  437. Vdbe *v, /* Generate code into this VM */
  438. Select *p, /* The SELECT statement being coded */
  439. int iContinue /* Jump here to skip the current record */
  440. ){
  441. if( p->iOffset && iContinue!=0 ){
  442. int addr;
  443. sqlite3VdbeAddOp2(v, OP_AddImm, p->iOffset, -1);
  444. addr = sqlite3VdbeAddOp1(v, OP_IfNeg, p->iOffset);
  445. sqlite3VdbeAddOp2(v, OP_Goto, 0, iContinue);
  446. VdbeComment((v, "skip OFFSET records"));
  447. sqlite3VdbeJumpHere(v, addr);
  448. }
  449. }
  450. /*
  451. ** Add code that will check to make sure the N registers starting at iMem
  452. ** form a distinct entry. iTab is a sorting index that holds previously
  453. ** seen combinations of the N values. A new entry is made in iTab
  454. ** if the current N values are new.
  455. **
  456. ** A jump to addrRepeat is made and the N+1 values are popped from the
  457. ** stack if the top N elements are not distinct.
  458. */
  459. static void codeDistinct(
  460. Parse *pParse, /* Parsing and code generating context */
  461. int iTab, /* A sorting index used to test for distinctness */
  462. int addrRepeat, /* Jump to here if not distinct */
  463. int N, /* Number of elements */
  464. int iMem /* First element */
  465. ){
  466. Vdbe *v;
  467. int r1;
  468. v = pParse->pVdbe;
  469. r1 = sqlite3GetTempReg(pParse);
  470. sqlite3VdbeAddOp4Int(v, OP_Found, iTab, addrRepeat, iMem, N);
  471. sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, N, r1);
  472. sqlite3VdbeAddOp2(v, OP_IdxInsert, iTab, r1);
  473. sqlite3ReleaseTempReg(pParse, r1);
  474. }
  475. #ifndef SQLITE_OMIT_SUBQUERY
  476. /*
  477. ** Generate an error message when a SELECT is used within a subexpression
  478. ** (example: "a IN (SELECT * FROM table)") but it has more than 1 result
  479. ** column. We do this in a subroutine because the error used to occur
  480. ** in multiple places. (The error only occurs in one place now, but we
  481. ** retain the subroutine to minimize code disruption.)
  482. */
  483. static int checkForMultiColumnSelectError(
  484. Parse *pParse, /* Parse context. */
  485. SelectDest *pDest, /* Destination of SELECT results */
  486. int nExpr /* Number of result columns returned by SELECT */
  487. ){
  488. int eDest = pDest->eDest;
  489. if( nExpr>1 && (eDest==SRT_Mem || eDest==SRT_Set) ){
  490. sqlite3ErrorMsg(pParse, "only a single result allowed for "
  491. "a SELECT that is part of an expression");
  492. return 1;
  493. }else{
  494. return 0;
  495. }
  496. }
  497. #endif
  498. /*
  499. ** An instance of the following object is used to record information about
  500. ** how to process the DISTINCT keyword, to simplify passing that information
  501. ** into the selectInnerLoop() routine.
  502. */
  503. typedef struct DistinctCtx DistinctCtx;
  504. struct DistinctCtx {
  505. u8 isTnct; /* True if the DISTINCT keyword is present */
  506. u8 eTnctType; /* One of the WHERE_DISTINCT_* operators */
  507. int tabTnct; /* Ephemeral table used for DISTINCT processing */
  508. int addrTnct; /* Address of OP_OpenEphemeral opcode for tabTnct */
  509. };
  510. /*
  511. ** This routine generates the code for the inside of the inner loop
  512. ** of a SELECT.
  513. **
  514. ** If srcTab and nColumn are both zero, then the pEList expressions
  515. ** are evaluated in order to get the data for this row. If nColumn>0
  516. ** then data is pulled from srcTab and pEList is used only to get the
  517. ** datatypes for each column.
  518. */
  519. static void selectInnerLoop(
  520. Parse *pParse, /* The parser context */
  521. Select *p, /* The complete select statement being coded */
  522. ExprList *pEList, /* List of values being extracted */
  523. int srcTab, /* Pull data from this table */
  524. int nColumn, /* Number of columns in the source table */
  525. ExprList *pOrderBy, /* If not NULL, sort results using this key */
  526. DistinctCtx *pDistinct, /* If not NULL, info on how to process DISTINCT */
  527. SelectDest *pDest, /* How to dispose of the results */
  528. int iContinue, /* Jump here to continue with next row */
  529. int iBreak /* Jump here to break out of the inner loop */
  530. ){
  531. Vdbe *v = pParse->pVdbe;
  532. int i;
  533. int hasDistinct; /* True if the DISTINCT keyword is present */
  534. int regResult; /* Start of memory holding result set */
  535. int eDest = pDest->eDest; /* How to dispose of results */
  536. int iParm = pDest->iSDParm; /* First argument to disposal method */
  537. int nResultCol; /* Number of result columns */
  538. assert( v );
  539. if( NEVER(v==0) ) return;
  540. assert( pEList!=0 );
  541. hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP;
  542. if( pOrderBy==0 && !hasDistinct ){
  543. codeOffset(v, p, iContinue);
  544. }
  545. /* Pull the requested columns.
  546. */
  547. if( nColumn>0 ){
  548. nResultCol = nColumn;
  549. }else{
  550. nResultCol = pEList->nExpr;
  551. }
  552. if( pDest->iSdst==0 ){
  553. pDest->iSdst = pParse->nMem+1;
  554. pDest->nSdst = nResultCol;
  555. pParse->nMem += nResultCol;
  556. }else{
  557. assert( pDest->nSdst==nResultCol );
  558. }
  559. regResult = pDest->iSdst;
  560. if( nColumn>0 ){
  561. for(i=0; i<nColumn; i++){
  562. sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, regResult+i);
  563. }
  564. }else if( eDest!=SRT_Exists ){
  565. /* If the destination is an EXISTS(...) expression, the actual
  566. ** values returned by the SELECT are not required.
  567. */
  568. sqlite3ExprCacheClear(pParse);
  569. sqlite3ExprCodeExprList(pParse, pEList, regResult, eDest==SRT_Output);
  570. }
  571. nColumn = nResultCol;
  572. /* If the DISTINCT keyword was present on the SELECT statement
  573. ** and this row has been seen before, then do not make this row
  574. ** part of the result.
  575. */
  576. if( hasDistinct ){
  577. assert( pEList!=0 );
  578. assert( pEList->nExpr==nColumn );
  579. switch( pDistinct->eTnctType ){
  580. case WHERE_DISTINCT_ORDERED: {
  581. VdbeOp *pOp; /* No longer required OpenEphemeral instr. */
  582. int iJump; /* Jump destination */
  583. int regPrev; /* Previous row content */
  584. /* Allocate space for the previous row */
  585. regPrev = pParse->nMem+1;
  586. pParse->nMem += nColumn;
  587. /* Change the OP_OpenEphemeral coded earlier to an OP_Null
  588. ** sets the MEM_Cleared bit on the first register of the
  589. ** previous value. This will cause the OP_Ne below to always
  590. ** fail on the first iteration of the loop even if the first
  591. ** row is all NULLs.
  592. */
  593. sqlite3VdbeChangeToNoop(v, pDistinct->addrTnct);
  594. pOp = sqlite3VdbeGetOp(v, pDistinct->addrTnct);
  595. pOp->opcode = OP_Null;
  596. pOp->p1 = 1;
  597. pOp->p2 = regPrev;
  598. iJump = sqlite3VdbeCurrentAddr(v) + nColumn;
  599. for(i=0; i<nColumn; i++){
  600. CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[i].pExpr);
  601. if( i<nColumn-1 ){
  602. sqlite3VdbeAddOp3(v, OP_Ne, regResult+i, iJump, regPrev+i);
  603. }else{
  604. sqlite3VdbeAddOp3(v, OP_Eq, regResult+i, iContinue, regPrev+i);
  605. }
  606. sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ);
  607. sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
  608. }
  609. assert( sqlite3VdbeCurrentAddr(v)==iJump );
  610. sqlite3VdbeAddOp3(v, OP_Copy, regResult, regPrev, nColumn-1);
  611. break;
  612. }
  613. case WHERE_DISTINCT_UNIQUE: {
  614. sqlite3VdbeChangeToNoop(v, pDistinct->addrTnct);
  615. break;
  616. }
  617. default: {
  618. assert( pDistinct->eTnctType==WHERE_DISTINCT_UNORDERED );
  619. codeDistinct(pParse, pDistinct->tabTnct, iContinue, nColumn, regResult);
  620. break;
  621. }
  622. }
  623. if( pOrderBy==0 ){
  624. codeOffset(v, p, iContinue);
  625. }
  626. }
  627. switch( eDest ){
  628. /* In this mode, write each query result to the key of the temporary
  629. ** table iParm.
  630. */
  631. #ifndef SQLITE_OMIT_COMPOUND_SELECT
  632. case SRT_Union: {
  633. int r1;
  634. r1 = sqlite3GetTempReg(pParse);
  635. sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
  636. sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
  637. sqlite3ReleaseTempReg(pParse, r1);
  638. break;
  639. }
  640. /* Construct a record from the query result, but instead of
  641. ** saving that record, use it as a key to delete elements from
  642. ** the temporary table iParm.
  643. */
  644. case SRT_Except: {
  645. sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn);
  646. break;
  647. }
  648. #endif
  649. /* Store the result as data using a unique key.
  650. */
  651. case SRT_Table:
  652. case SRT_EphemTab: {
  653. int r1 = sqlite3GetTempReg(pParse);
  654. testcase( eDest==SRT_Table );
  655. testcase( eDest==SRT_EphemTab );
  656. sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
  657. if( pOrderBy ){
  658. pushOntoSorter(pParse, pOrderBy, p, r1);
  659. }else{
  660. int r2 = sqlite3GetTempReg(pParse);
  661. sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
  662. sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
  663. sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
  664. sqlite3ReleaseTempReg(pParse, r2);
  665. }
  666. sqlite3ReleaseTempReg(pParse, r1);
  667. break;
  668. }
  669. #ifndef SQLITE_OMIT_SUBQUERY
  670. /* If we are creating a set for an "expr IN (SELECT ...)" construct,
  671. ** then there should be a single item on the stack. Write this
  672. ** item into the set table with bogus data.
  673. */
  674. case SRT_Set: {
  675. assert( nColumn==1 );
  676. pDest->affSdst =
  677. sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affSdst);
  678. if( pOrderBy ){
  679. /* At first glance you would think we could optimize out the
  680. ** ORDER BY in this case since the order of entries in the set
  681. ** does not matter. But there might be a LIMIT clause, in which
  682. ** case the order does matter */
  683. pushOntoSorter(pParse, pOrderBy, p, regResult);
  684. }else{
  685. int r1 = sqlite3GetTempReg(pParse);
  686. sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult,1,r1, &pDest->affSdst, 1);
  687. sqlite3ExprCacheAffinityChange(pParse, regResult, 1);
  688. sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
  689. sqlite3ReleaseTempReg(pParse, r1);
  690. }
  691. break;
  692. }
  693. /* If any row exist in the result set, record that fact and abort.
  694. */
  695. case SRT_Exists: {
  696. sqlite3VdbeAddOp2(v, OP_Integer, 1, iParm);
  697. /* The LIMIT clause will terminate the loop for us */
  698. break;
  699. }
  700. /* If this is a scalar select that is part of an expression, then
  701. ** store the results in the appropriate memory cell and break out
  702. ** of the scan loop.
  703. */
  704. case SRT_Mem: {
  705. assert( nColumn==1 );
  706. if( pOrderBy ){
  707. pushOntoSorter(pParse, pOrderBy, p, regResult);
  708. }else{
  709. sqlite3ExprCodeMove(pParse, regResult, iParm, 1);
  710. /* The LIMIT clause will jump out of the loop for us */
  711. }
  712. break;
  713. }
  714. #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
  715. /* Send the data to the callback function or to a subroutine. In the
  716. ** case of a subroutine, the subroutine itself is responsible for
  717. ** popping the data from the stack.
  718. */
  719. case SRT_Coroutine:
  720. case SRT_Output: {
  721. testcase( eDest==SRT_Coroutine );
  722. testcase( eDest==SRT_Output );
  723. if( pOrderBy ){
  724. int r1 = sqlite3GetTempReg(pParse);
  725. sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
  726. pushOntoSorter(pParse, pOrderBy, p, r1);
  727. sqlite3ReleaseTempReg(pParse, r1);
  728. }else if( eDest==SRT_Coroutine ){
  729. sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
  730. }else{
  731. sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nColumn);
  732. sqlite3ExprCacheAffinityChange(pParse, regResult, nColumn);
  733. }
  734. break;
  735. }
  736. #if !defined(SQLITE_OMIT_TRIGGER)
  737. /* Discard the results. This is used for SELECT statements inside
  738. ** the body of a TRIGGER. The purpose of such selects is to call
  739. ** user-defined functions that have side effects. We do not care
  740. ** about the actual results of the select.
  741. */
  742. default: {
  743. assert( eDest==SRT_Discard );
  744. break;
  745. }
  746. #endif
  747. }
  748. /* Jump to the end of the loop if the LIMIT is reached. Except, if
  749. ** there is a sorter, in which case the sorter has already limited
  750. ** the output for us.
  751. */
  752. if( pOrderBy==0 && p->iLimit ){
  753. sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
  754. }
  755. }
  756. /*
  757. ** Allocate a KeyInfo object sufficient for an index of N columns.
  758. **
  759. ** Actually, always allocate one extra column for the rowid at the end
  760. ** of the index. So the KeyInfo returned will have space sufficient for
  761. ** N+1 columns.
  762. */
  763. KeyInfo *sqlite3KeyInfoAlloc(sqlite3 *db, int N){
  764. KeyInfo *p = sqlite3DbMallocZero(db,
  765. sizeof(KeyInfo) + (N+1)*(sizeof(CollSeq*)+1));
  766. if( p ){
  767. p->aSortOrder = (u8*)&p->aColl[N+1];
  768. p->nField = (u16)N;
  769. p->enc = ENC(db);
  770. p->db = db;
  771. }
  772. return p;
  773. }
  774. /*
  775. ** Given an expression list, generate a KeyInfo structure that records
  776. ** the collating sequence for each expression in that expression list.
  777. **
  778. ** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
  779. ** KeyInfo structure is appropriate for initializing a virtual index to
  780. ** implement that clause. If the ExprList is the result set of a SELECT
  781. ** then the KeyInfo structure is appropriate for initializing a virtual
  782. ** index to implement a DISTINCT test.
  783. **
  784. ** Space to hold the KeyInfo structure is obtain from malloc. The calling
  785. ** function is responsible for seeing that this structure is eventually
  786. ** freed. Add the KeyInfo structure to the P4 field of an opcode using
  787. ** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
  788. */
  789. static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList){
  790. int nExpr;
  791. KeyInfo *pInfo;
  792. struct ExprList_item *pItem;
  793. sqlite3 *db = pParse->db;
  794. int i;
  795. nExpr = pList->nExpr;
  796. pInfo = sqlite3KeyInfoAlloc(db, nExpr);
  797. if( pInfo ){
  798. for(i=0, pItem=pList->a; i<nExpr; i++, pItem++){
  799. CollSeq *pColl;
  800. pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
  801. if( !pColl ) pColl = db->pDfltColl;
  802. pInfo->aColl[i] = pColl;
  803. pInfo->aSortOrder[i] = pItem->sortOrder;
  804. }
  805. }
  806. return pInfo;
  807. }
  808. #ifndef SQLITE_OMIT_COMPOUND_SELECT
  809. /*
  810. ** Name of the connection operator, used for error messages.
  811. */
  812. static const char *selectOpName(int id){
  813. char *z;
  814. switch( id ){
  815. case TK_ALL: z = "UNION ALL"; break;
  816. case TK_INTERSECT: z = "INTERSECT"; break;
  817. case TK_EXCEPT: z = "EXCEPT"; break;
  818. default: z = "UNION"; break;
  819. }
  820. return z;
  821. }
  822. #endif /* SQLITE_OMIT_COMPOUND_SELECT */
  823. #ifndef SQLITE_OMIT_EXPLAIN
  824. /*
  825. ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
  826. ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
  827. ** where the caption is of the form:
  828. **
  829. ** "USE TEMP B-TREE FOR xxx"
  830. **
  831. ** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which
  832. ** is determined by the zUsage argument.
  833. */
  834. static void explainTempTable(Parse *pParse, const char *zUsage){
  835. if( pParse->explain==2 ){
  836. Vdbe *v = pParse->pVdbe;
  837. char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage);
  838. sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
  839. }
  840. }
  841. /*
  842. ** Assign expression b to lvalue a. A second, no-op, version of this macro
  843. ** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
  844. ** in sqlite3Select() to assign values to structure member variables that
  845. ** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
  846. ** code with #ifndef directives.
  847. */
  848. # define explainSetInteger(a, b) a = b
  849. #else
  850. /* No-op versions of the explainXXX() functions and macros. */
  851. # define explainTempTable(y,z)
  852. # define explainSetInteger(y,z)
  853. #endif
  854. #if !defined(SQLITE_OMIT_EXPLAIN) && !defined(SQLITE_OMIT_COMPOUND_SELECT)
  855. /*
  856. ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
  857. ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
  858. ** where the caption is of one of the two forms:
  859. **
  860. ** "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
  861. ** "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
  862. **
  863. ** where iSub1 and iSub2 are the integers passed as the corresponding
  864. ** function parameters, and op is the text representation of the parameter
  865. ** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
  866. ** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is
  867. ** false, or the second form if it is true.
  868. */
  869. static void explainComposite(
  870. Parse *pParse, /* Parse context */
  871. int op, /* One of TK_UNION, TK_EXCEPT etc. */
  872. int iSub1, /* Subquery id 1 */
  873. int iSub2, /* Subquery id 2 */
  874. int bUseTmp /* True if a temp table was used */
  875. ){
  876. assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL );
  877. if( pParse->explain==2 ){
  878. Vdbe *v = pParse->pVdbe;
  879. char *zMsg = sqlite3MPrintf(
  880. pParse->db, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
  881. bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op)
  882. );
  883. sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
  884. }
  885. }
  886. #else
  887. /* No-op versions of the explainXXX() functions and macros. */
  888. # define explainComposite(v,w,x,y,z)
  889. #endif
  890. /*
  891. ** If the inner loop was generated using a non-null pOrderBy argument,
  892. ** then the results were placed in a sorter. After the loop is terminated
  893. ** we need to run the sorter and output the results. The following
  894. ** routine generates the code needed to do that.
  895. */
  896. static void generateSortTail(
  897. Parse *pParse, /* Parsing context */
  898. Select *p, /* The SELECT statement */
  899. Vdbe *v, /* Generate code into this VDBE */
  900. int nColumn, /* Number of columns of data */
  901. SelectDest *pDest /* Write the sorted results here */
  902. ){
  903. int addrBreak = sqlite3VdbeMakeLabel(v); /* Jump here to exit loop */
  904. int addrContinue = sqlite3VdbeMakeLabel(v); /* Jump here for next cycle */
  905. int addr;
  906. int iTab;
  907. int pseudoTab = 0;
  908. ExprList *pOrderBy = p->pOrderBy;
  909. int eDest = pDest->eDest;
  910. int iParm = pDest->iSDParm;
  911. int regRow;
  912. int regRowid;
  913. iTab = pOrderBy->iECursor;
  914. regRow = sqlite3GetTempReg(pParse);
  915. if( eDest==SRT_Output || eDest==SRT_Coroutine ){
  916. pseudoTab = pParse->nTab++;
  917. sqlite3VdbeAddOp3(v, OP_OpenPseudo, pseudoTab, regRow, nColumn);
  918. regRowid = 0;
  919. }else{
  920. regRowid = sqlite3GetTempReg(pParse);
  921. }
  922. if( p->selFlags & SF_UseSorter ){
  923. int regSortOut = ++pParse->nMem;
  924. int ptab2 = pParse->nTab++;
  925. sqlite3VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2);
  926. addr = 1 + sqlite3VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
  927. codeOffset(v, p, addrContinue);
  928. sqlite3VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);
  929. sqlite3VdbeAddOp3(v, OP_Column, ptab2, pOrderBy->nExpr+1, regRow);
  930. sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE);
  931. }else{
  932. addr = 1 + sqlite3VdbeAddOp2(v, OP_Sort, iTab, addrBreak);
  933. codeOffset(v, p, addrContinue);
  934. sqlite3VdbeAddOp3(v, OP_Column, iTab, pOrderBy->nExpr+1, regRow);
  935. }
  936. switch( eDest ){
  937. case SRT_Table:
  938. case SRT_EphemTab: {
  939. testcase( eDest==SRT_Table );
  940. testcase( eDest==SRT_EphemTab );
  941. sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
  942. sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow, regRowid);
  943. sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
  944. break;
  945. }
  946. #ifndef SQLITE_OMIT_SUBQUERY
  947. case SRT_Set: {
  948. assert( nColumn==1 );
  949. sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, 1, regRowid,
  950. &pDest->affSdst, 1);
  951. sqlite3ExprCacheAffinityChange(pParse, regRow, 1);
  952. sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid);
  953. break;
  954. }
  955. case SRT_Mem: {
  956. assert( nColumn==1 );
  957. sqlite3ExprCodeMove(pParse, regRow, iParm, 1);
  958. /* The LIMIT clause will terminate the loop for us */
  959. break;
  960. }
  961. #endif
  962. default: {
  963. int i;
  964. assert( eDest==SRT_Output || eDest==SRT_Coroutine );
  965. testcase( eDest==SRT_Output );
  966. testcase( eDest==SRT_Coroutine );
  967. for(i=0; i<nColumn; i++){
  968. assert( regRow!=pDest->iSdst+i );
  969. sqlite3VdbeAddOp3(v, OP_Column, pseudoTab, i, pDest->iSdst+i);
  970. if( i==0 ){
  971. sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE);
  972. }
  973. }
  974. if( eDest==SRT_Output ){
  975. sqlite3VdbeAddOp2(v, OP_ResultRow, pDest->iSdst, nColumn);
  976. sqlite3ExprCacheAffinityChange(pParse, pDest->iSdst, nColumn);
  977. }else{
  978. sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
  979. }
  980. break;
  981. }
  982. }
  983. sqlite3ReleaseTempReg(pParse, regRow);
  984. sqlite3ReleaseTempReg(pParse, regRowid);
  985. /* The bottom of the loop
  986. */
  987. sqlite3VdbeResolveLabel(v, addrContinue);
  988. if( p->selFlags & SF_UseSorter ){
  989. sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr);
  990. }else{
  991. sqlite3VdbeAddOp2(v, OP_Next, iTab, addr);
  992. }
  993. sqlite3VdbeResolveLabel(v, addrBreak);
  994. if( eDest==SRT_Output || eDest==SRT_Coroutine ){
  995. sqlite3VdbeAddOp2(v, OP_Close, pseudoTab, 0);
  996. }
  997. }
  998. /*
  999. ** Return a pointer to a string containing the 'declaration type' of the
  1000. ** expression pExpr. The string may be treated as static by the caller.
  1001. **
  1002. ** Also try to estimate the size of the returned value and return that
  1003. ** result in *pEstWidth.
  1004. **
  1005. ** The declaration type is the exact datatype definition extracted from the
  1006. ** original CREATE TABLE statement if the expression is a column. The
  1007. ** declaration type for a ROWID field is INTEGER. Exactly when an expression
  1008. ** is considered a column can be complex in the presence of subqueries. The
  1009. ** result-set expression in all of the following SELECT statements is
  1010. ** considered a column by this function.
  1011. **
  1012. ** SELECT col FROM tbl;
  1013. ** SELECT (SELECT col FROM tbl;
  1014. ** SELECT (SELECT col FROM tbl);
  1015. ** SELECT abc FROM (SELECT col AS abc FROM tbl);
  1016. **
  1017. ** The declaration type for any expression other than a column is NULL.
  1018. **
  1019. ** This routine has either 3 or 6 parameters depending on whether or not
  1020. ** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used.
  1021. */
  1022. #ifdef SQLITE_ENABLE_COLUMN_METADATA
  1023. # define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,C,D,E,F)
  1024. static const char *columnTypeImpl(
  1025. NameContext *pNC,
  1026. Expr *pExpr,
  1027. const char **pzOrigDb,
  1028. const char **pzOrigTab,
  1029. const char **pzOrigCol,
  1030. u8 *pEstWidth
  1031. ){
  1032. char const *zOrigDb = 0;
  1033. char const *zOrigTab = 0;
  1034. char const *zOrigCol = 0;
  1035. #else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */
  1036. # define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,F)
  1037. static const char *columnTypeImpl(
  1038. NameContext *pNC,
  1039. Expr *pExpr,
  1040. u8 *pEstWidth
  1041. ){
  1042. #endif /* !defined(SQLITE_ENABLE_COLUMN_METADATA) */
  1043. char const *zType = 0;
  1044. int j;
  1045. u8 estWidth = 1;
  1046. if( NEVER(pExpr==0) || pNC->pSrcList==0 ) return 0;
  1047. switch( pExpr->op ){
  1048. case TK_AGG_COLUMN:
  1049. case TK_COLUMN: {
  1050. /* The expression is a column. Locate the table the column is being
  1051. ** extracted from in NameContext.pSrcList. This table may be real
  1052. ** database table or a subquery.
  1053. */
  1054. Table *pTab = 0; /* Table structure column is extracted from */
  1055. Select *pS = 0; /* Select the column is extracted from */
  1056. int iCol = pExpr->iColumn; /* Index of column in pTab */
  1057. testcase( pExpr->op==TK_AGG_COLUMN );
  1058. testcase( pExpr->op==TK_COLUMN );
  1059. while( pNC && !pTab ){
  1060. SrcList *pTabList = pNC->pSrcList;
  1061. for(j=0;j<pTabList->nSrc && pTabList->a[j].iCursor!=pExpr->iTable;j++);
  1062. if( j<pTabList->nSrc ){
  1063. pTab = pTabList->a[j].pTab;
  1064. pS = pTabList->a[j].pSelect;
  1065. }else{
  1066. pNC = pNC->pNext;
  1067. }
  1068. }
  1069. if( pTab==0 ){
  1070. /* At one time, code such as "SELECT new.x" within a trigger would
  1071. ** cause this condition to run. Since then, we have restructured how
  1072. ** trigger code is generated and so this condition is no longer
  1073. ** possible. However, it can still be true for statements like
  1074. ** the following:
  1075. **
  1076. ** CREATE TABLE t1(col INTEGER);
  1077. ** SELECT (SELECT t1.col) FROM FROM t1;
  1078. **
  1079. ** when columnType() is called on the expression "t1.col" in the
  1080. ** sub-select. In this case, set the column type to NULL, even
  1081. ** though it should really be "INTEGER".
  1082. **
  1083. ** This is not a problem, as the column type of "t1.col" is never
  1084. ** used. When columnType() is called on the expression
  1085. ** "(SELECT t1.col)", the correct type is returned (see the TK_SELECT
  1086. ** branch below. */
  1087. break;
  1088. }
  1089. assert( pTab && pExpr->pTab==pTab );
  1090. if( pS ){
  1091. /* The "table" is actually a sub-select or a view in the FROM clause
  1092. ** of the SELECT statement. Return the declaration type and origin
  1093. ** data for the result-set column of the sub-select.
  1094. */
  1095. if( iCol>=0 && ALWAYS(iCol<pS->pEList->nExpr) ){
  1096. /* If iCol is less than zero, then the expression requests the
  1097. ** rowid of the sub-select or view. This expression is legal (see
  1098. ** test case misc2.2.2) - it always evaluates to NULL.
  1099. */
  1100. NameContext sNC;
  1101. Expr *p = pS->pEList->a[iCol].pExpr;
  1102. sNC.pSrcList = pS->pSrc;
  1103. sNC.pNext = pNC;
  1104. sNC.pParse = pNC->pParse;
  1105. zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol, &estWidth);
  1106. }
  1107. }else if( ALWAYS(pTab->pSchema) ){
  1108. /* A real table */
  1109. assert( !pS );
  1110. if( iCol<0 ) iCol = pTab->iPKey;
  1111. assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
  1112. #ifdef SQLITE_ENABLE_COLUMN_METADATA
  1113. if( iCol<0 ){
  1114. zType = "INTEGER";
  1115. zOrigCol = "rowid";
  1116. }else{
  1117. zType = pTab->aCol[iCol].zType;
  1118. zOrigCol = pTab->aCol[iCol].zName;
  1119. estWidth = pTab->aCol[iCol].szEst;
  1120. }
  1121. zOrigTab = pTab->zName;
  1122. if( pNC->pParse ){
  1123. int iDb = sqlite3SchemaToIndex(pNC->pParse->db, pTab->pSchema);
  1124. zOrigDb = pNC->pParse->db->aDb[iDb].zName;
  1125. }
  1126. #else
  1127. if( iCol<0 ){
  1128. zType = "INTEGER";
  1129. }else{
  1130. zType = pTab->aCol[iCol].zType;
  1131. estWidth = pTab->aCol[iCol].szEst;
  1132. }
  1133. #endif
  1134. }
  1135. break;
  1136. }
  1137. #ifndef SQLITE_OMIT_SUBQUERY
  1138. case TK_SELECT: {
  1139. /* The expression is a sub-select. Return the declaration type and
  1140. ** origin info for the single column in the result set of the SELECT
  1141. ** statement.
  1142. */
  1143. NameContext sNC;
  1144. Select *pS = pExpr->x.pSelect;
  1145. Expr *p = pS->pEList->a[0].pExpr;
  1146. assert( ExprHasProperty(pExpr, EP_xIsSelect) );
  1147. sNC.pSrcList = pS->pSrc;
  1148. sNC.pNext = pNC;
  1149. sNC.pParse = pNC->pParse;
  1150. zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &estWidth);
  1151. break;
  1152. }
  1153. #endif
  1154. }
  1155. #ifdef SQLITE_ENABLE_COLUMN_METADATA
  1156. if( pzOrigDb ){
  1157. assert( pzOrigTab && pzOrigCol );
  1158. *pzOrigDb = zOrigDb;
  1159. *pzOrigTab = zOrigTab;
  1160. *pzOrigCol = zOrigCol;
  1161. }
  1162. #endif
  1163. if( pEstWidth ) *pEstWidth = estWidth;
  1164. return zType;
  1165. }
  1166. /*
  1167. ** Generate code that will tell the VDBE the declaration types of columns
  1168. ** in the result set.
  1169. */
  1170. static void generateColumnTypes(
  1171. Parse *pParse, /* Parser context */
  1172. SrcList *pTabList, /* List of tables */
  1173. ExprList *pEList /* Expressions defining the result set */
  1174. ){
  1175. #ifndef SQLITE_OMIT_DECLTYPE
  1176. Vdbe *v = pParse->pVdbe;
  1177. int i;
  1178. NameContext sNC;
  1179. sNC.pSrcList = pTabList;
  1180. sNC.pParse = pParse;
  1181. for(i=0; i<pEList->nExpr; i++){
  1182. Expr *p = pEList->a[i].pExpr;
  1183. const char *zType;
  1184. #ifdef SQLITE_ENABLE_COLUMN_METADATA
  1185. const char *zOrigDb = 0;
  1186. const char *zOrigTab = 0;
  1187. const char *zOrigCol = 0;
  1188. zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, 0);
  1189. /* The vdbe must make its own copy of the column-type and other
  1190. ** column specific strings, in case the schema is reset before this
  1191. ** virtual machine is deleted.
  1192. */
  1193. sqlite3VdbeSetColName(v, i, COLNAME_DATABASE, zOrigDb, SQLITE_TRANSIENT);
  1194. sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, SQLITE_TRANSIENT);
  1195. sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, SQLITE_TRANSIENT);
  1196. #else
  1197. zType = columnType(&sNC, p, 0, 0, 0, 0);
  1198. #endif
  1199. sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT);
  1200. }
  1201. #endif /* !defined(SQLITE_OMIT_DECLTYPE) */
  1202. }
  1203. /*
  1204. ** Generate code that will tell the VDBE the names of columns
  1205. ** in the result set. This information is used to provide the
  1206. ** azCol[] values in the callback.
  1207. */
  1208. static void generateColumnNames(
  1209. Parse *pParse, /* Parser context */
  1210. SrcList *pTabList, /* List of tables */
  1211. ExprList *pEList /* Expressions defining the result set */
  1212. ){
  1213. Vdbe *v = pParse->pVdbe;
  1214. int i, j;
  1215. sqlite3 *db = pParse->db;
  1216. int fullNames, shortNames;
  1217. #ifndef SQLITE_OMIT_EXPLAIN
  1218. /* If this is an EXPLAIN, skip this step */
  1219. if( pParse->explain ){
  1220. return;
  1221. }
  1222. #endif
  1223. if( pParse->colNamesSet || NEVER(v==0) || db->mallocFailed ) return;
  1224. pParse->colNamesSet = 1;
  1225. fullNames = (db->flags & SQLITE_FullColNames)!=0;
  1226. shortNames = (db->flags & SQLITE_ShortColNames)!=0;
  1227. sqlite3VdbeSetNumCols(v, pEList->nExpr);
  1228. for(i=0; i<pEList->nExpr; i++){
  1229. Expr *p;
  1230. p = pEList->a[i].pExpr;
  1231. if( NEVER(p==0) ) continue;
  1232. if( pEList->a[i].zName ){
  1233. char *zName = pEList->a[i].zName;
  1234. sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT);
  1235. }else if( (p->op==TK_COLUMN || p->op==TK_AGG_COLUMN) && pTabList ){
  1236. Table *pTab;
  1237. char *zCol;
  1238. int iCol = p->iColumn;
  1239. for(j=0; ALWAYS(j<pTabList->nSrc); j++){
  1240. if( pTabList->a[j].iCursor==p->iTable ) break;
  1241. }
  1242. assert( j<pTabList->nSrc );
  1243. pTab = pTabList->a[j].pTab;
  1244. if( iCol<0 ) iCol = pTab->iPKey;
  1245. assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
  1246. if( iCol<0 ){
  1247. zCol = "rowid";
  1248. }else{
  1249. zCol = pTab->aCol[iCol].zName;
  1250. }
  1251. if( !shortNames && !fullNames ){
  1252. sqlite3VdbeSetColName(v, i, COLNAME_NAME,
  1253. sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC);
  1254. }else if( fullNames ){
  1255. char *zName = 0;
  1256. zName = sqlite3MPrintf(db, "%s.%s", pTab->zName, zCol);
  1257. sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_DYNAMIC);
  1258. }else{
  1259. sqlite3VdbeSetColName(v, i, COLNAME_NAME, zCol, SQLITE_TRANSIENT);
  1260. }
  1261. }else{
  1262. sqlite3VdbeSetColName(v, i, COLNAME_NAME,
  1263. sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC);
  1264. }
  1265. }
  1266. generateColumnTypes(pParse, pTabList, pEList);
  1267. }
  1268. /*
  1269. ** Given a an expression list (which is really the list of expressions
  1270. ** that form the result set of a SELECT statement) compute appropriate
  1271. ** column names for a table that would hold the expression list.
  1272. **
  1273. ** All column names will be unique.
  1274. **
  1275. ** Only the column names are computed. Column.zType, Column.zColl,
  1276. ** and other fields of Column are zeroed.
  1277. **
  1278. ** Return SQLITE_OK on success. If a memory allocation error occurs,
  1279. ** store NULL in *paCol and 0 in *pnCol and return SQLITE_NOMEM.
  1280. */
  1281. static int selectColumnsFromExprList(
  1282. Parse *pParse, /* Parsing context */
  1283. ExprList *pEList, /* Expr list from which to derive column names */
  1284. i16 *pnCol, /* Write the number of columns here */
  1285. Column **paCol /* Write the new column list here */
  1286. ){
  1287. sqlite3 *db = pParse->db; /* Database connection */
  1288. int i, j; /* Loop counters */
  1289. int cnt; /* Index added to make the name unique */
  1290. Column *aCol, *pCol; /* For looping over result columns */
  1291. int nCol; /* Number of columns in the result set */
  1292. Expr *p; /* Expression for a single result column */
  1293. char *zName; /* Column name */
  1294. int nName; /* Size of name in zName[] */
  1295. if( pEList ){
  1296. nCol = pEList->nExpr;
  1297. aCol = sqlite3DbMallocZero(db, sizeof(aCol[0])*nCol);
  1298. testcase( aCol==0 );
  1299. }else{
  1300. nCol = 0;
  1301. aCol = 0;
  1302. }
  1303. *pnCol = nCol;
  1304. *paCol = aCol;
  1305. for(i=0, pCol=aCol; i<nCol; i++, pCol++){
  1306. /* Get an appropriate name for the column
  1307. */
  1308. p = sqlite3ExprSkipCollate(pEList->a[i].pExpr);
  1309. if( (zName = pEList->a[i].zName)!=0 ){
  1310. /* If the column contains an "AS <name>" phrase, use <name> as the name */
  1311. zName = sqlite3DbStrDup(db, zName);
  1312. }else{
  1313. Expr *pColExpr = p; /* The expression that is the result column name */
  1314. Table *pTab; /* Table associated with this expression */
  1315. while( pColExpr->op==TK_DOT ){
  1316. pColExpr = pColExpr->pRight;
  1317. assert( pColExpr!=0 );
  1318. }
  1319. if( pColExpr->op==TK_COLUMN && ALWAYS(pColExpr->pTab!=0) ){
  1320. /* For columns use the column name name */
  1321. int iCol = pColExpr->iColumn;
  1322. pTab = pColExpr->pTab;
  1323. if( iCol<0 ) iCol = pTab->iPKey;
  1324. zName = sqlite3MPrintf(db, "%s",
  1325. iCol>=0 ? pTab->aCol[iCol].zName : "rowid");
  1326. }else if( pColExpr->op==TK_ID ){
  1327. assert( !ExprHasProperty(pColExpr, EP_IntValue) );
  1328. zName = sqlite3MPrintf(db, "%s", pColExpr->u.zToken);
  1329. }else{
  1330. /* Use the original text of the column expression as its name */
  1331. zName = sqlite3MPrintf(db, "%s", pEList->a[i].zSpan);
  1332. }
  1333. }
  1334. if( db->mallocFailed ){
  1335. sqlite3DbFree(db, zName);
  1336. break;
  1337. }
  1338. /* Make sure the column name is unique. If the name is not unique,
  1339. ** append a integer to the name so that it becomes unique.
  1340. */
  1341. nName = sqlite3Strlen30(zName);
  1342. for(j=cnt=0; j<i; j++){
  1343. if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
  1344. char *zNewName;
  1345. int k;
  1346. for(k=nName-1; k>1 && sqlite3Isdigit(zName[k]); k--){}
  1347. if( zName[k]==':' ) nName = k;
  1348. zName[nName] = 0;
  1349. zNewName = sqlite3MPrintf(db, "%s:%d", zName, ++cnt);
  1350. sqlite3DbFree(db, zName);
  1351. zName = zNewName;
  1352. j = -1;
  1353. if( zName==0 ) break;
  1354. }
  1355. }
  1356. pCol->zName = zName;
  1357. }
  1358. if( db->mallocFailed ){
  1359. for(j=0; j<i; j++){
  1360. sqlite3DbFree(db, aCol[j].zName);
  1361. }
  1362. sqlite3DbFree(db, aCol);
  1363. *paCol = 0;
  1364. *pnCol = 0;
  1365. return SQLITE_NOMEM;
  1366. }
  1367. return SQLITE_OK;
  1368. }
  1369. /*
  1370. ** Add type and collation information to a column list based on
  1371. ** a SELECT statement.
  1372. **
  1373. ** The column list presumably came from selectColumnNamesFromExprList().
  1374. ** The column list has only names, not types or collations. This
  1375. ** routine goes through and adds the types and collations.
  1376. **
  1377. ** This routine requires that all identifiers in the SELECT
  1378. ** statement be resolved.
  1379. */
  1380. static void selectAddColumnTypeAndCollation(
  1381. Parse *pParse, /* Parsing contexts */
  1382. Table *pTab, /* Add column type information to this table */
  1383. Select *pSelect /* SELECT used to determine types and collations */
  1384. ){
  1385. sqlite3 *db = pParse->db;
  1386. NameContext sNC;
  1387. Column *pCol;
  1388. CollSeq *pColl;
  1389. int i;
  1390. Expr *p;
  1391. struct ExprList_item *a;
  1392. u64 szAll = 0;
  1393. assert( pSelect!=0 );
  1394. assert( (pSelect->selFlags & SF_Resolved)!=0 );
  1395. assert( pTab->nCol==pSelect->pEList->nExpr || db->mallocFailed );
  1396. if( db->mallocFailed ) return;
  1397. memset(&sNC, 0, sizeof(sNC));
  1398. sNC.pSrcList = pSelect->pSrc;
  1399. a = pSelect->pEList->a;
  1400. for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
  1401. p = a[i].pExpr;
  1402. pCol->zType = sqlite3DbStrDup(db, columnType(&sNC, p,0,0,0, &pCol->szEst));
  1403. szAll += pCol->szEst;
  1404. pCol->affinity = sqlite3ExprAffinity(p);
  1405. if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_NONE;
  1406. pColl = sqlite3ExprCollSeq(pParse, p);
  1407. if( pColl ){
  1408. pCol->zColl = sqlite3DbStrDup(db, pColl->zName);
  1409. }
  1410. }
  1411. pTab->szTabRow = sqlite3LogEst(szAll*4);
  1412. }
  1413. /*
  1414. ** Given a SELECT statement, generate a Table structure that describes
  1415. ** the result set of that SELECT.
  1416. */
  1417. Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){
  1418. Table *pTab;
  1419. sqlite3 *db = pParse->db;
  1420. int savedFlags;
  1421. savedFlags = db->flags;
  1422. db->flags &= ~SQLITE_FullColNames;
  1423. db->flags |= SQLITE_ShortColNames;
  1424. sqlite3SelectPrep(pParse, pSelect, 0);
  1425. if( pParse->nErr ) return 0;
  1426. while( pSelect->pPrior ) pSelect = pSelect->pPrior;
  1427. db->flags = savedFlags;
  1428. pTab = sqlite3DbMallocZero(db, sizeof(Table) );
  1429. if( pTab==0 ){
  1430. return 0;
  1431. }
  1432. /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  1433. ** is disabled */
  1434. assert( db->lookaside.bEnabled==0 );
  1435. pTab->nRef = 1;
  1436. pTab->zName = 0;
  1437. pTab->nRowEst = 1048576;
  1438. selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  1439. selectAddColumnTypeAndCollation(pParse, pTab, pSelect);
  1440. pTab->iPKey = -1;
  1441. if( db->mallocFailed ){
  1442. sqlite3DeleteTable(db, pTab);
  1443. return 0;
  1444. }
  1445. return pTab;
  1446. }
  1447. /*
  1448. ** Get a VDBE for the given parser context. Create a new one if necessary.
  1449. ** If an error occurs, return NULL and leave a message in pParse.
  1450. */
  1451. Vdbe *sqlite3GetVdbe(Parse *pParse){
  1452. Vdbe *v = pParse->pVdbe;
  1453. if( v==0 ){
  1454. v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db);
  1455. #ifndef SQLITE_OMIT_TRACE
  1456. if( v ){
  1457. sqlite3VdbeAddOp0(v, OP_Trace);
  1458. }
  1459. #endif
  1460. }
  1461. return v;
  1462. }
  1463. /*
  1464. ** Compute the iLimit and iOffset fields of the SELECT based on the
  1465. ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions
  1466. ** that appear in the original SQL statement after the LIMIT and OFFSET
  1467. ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset
  1468. ** are the integer memory register numbers for counters used to compute
  1469. ** the limit and offset. If there is no limit and/or offset, then
  1470. ** iLimit and iOffset are negative.
  1471. **
  1472. ** This routine changes the values of iLimit and iOffset only if
  1473. ** a limit or offset is defined by pLimit and pOffset. iLimit and
  1474. ** iOffset should have been preset to appropriate default values
  1475. ** (usually but not always -1) prior to calling this routine.
  1476. ** Only if pLimit!=0 or pOffset!=0 do the limit registers get
  1477. ** redefined. The UNION ALL operator uses this property to force
  1478. ** the reuse of the same limit and offset registers across multiple
  1479. ** SELECT statements.
  1480. */
  1481. static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){
  1482. Vdbe *v = 0;
  1483. int iLimit = 0;
  1484. int iOffset;
  1485. int addr1, n;
  1486. if( p->iLimit ) return;
  1487. /*
  1488. ** "LIMIT -1" always shows all rows. There is some
  1489. ** controversy about what the correct behavior should be.
  1490. ** The current implementation interprets "LIMIT 0" to mean
  1491. ** no rows.
  1492. */
  1493. sqlite3ExprCacheClear(pParse);
  1494. assert( p->pOffset==0 || p->pLimit!=0 );
  1495. if( p->pLimit ){
  1496. p->iLimit = iLimit = ++pParse->nMem;
  1497. v = sqlite3GetVdbe(pParse);
  1498. if( NEVER(v==0) ) return; /* VDBE should have already been allocated */
  1499. if( sqlite3ExprIsInteger(p->pLimit, &n) ){
  1500. sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
  1501. VdbeComment((v, "LIMIT counter"));
  1502. if( n==0 ){
  1503. sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
  1504. }else if( n>=0 && p->nSelectRow>(u64)n ){
  1505. p->nSelectRow = n;
  1506. }
  1507. }else{
  1508. sqlite3ExprCode(pParse, p->pLimit, iLimit);
  1509. sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit);
  1510. VdbeComment((v, "LIMIT counter"));
  1511. sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak);
  1512. }
  1513. if( p->pOffset ){
  1514. p->iOffset = iOffset = ++pParse->nMem;
  1515. pParse->nMem++; /* Allocate an extra register for limit+offset */
  1516. sqlite3ExprCode(pParse, p->pOffset, iOffset);
  1517. sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset);
  1518. VdbeComment((v, "OFFSET counter"));
  1519. addr1 = sqlite3VdbeAddOp1(v, OP_IfPos, iOffset);
  1520. sqlite3VdbeAddOp2(v, OP_Integer, 0, iOffset);
  1521. sqlite3VdbeJumpHere(v, addr1);
  1522. sqlite3VdbeAddOp3(v, OP_Add, iLimit, iOffset, iOffset+1);
  1523. VdbeComment((v, "LIMIT+OFFSET"));
  1524. addr1 = sqlite3VdbeAddOp1(v, OP_IfPos, iLimit);
  1525. sqlite3VdbeAddOp2(v, OP_Integer, -1, iOffset+1);
  1526. sqlite3VdbeJumpHere(v, addr1);
  1527. }
  1528. }
  1529. }
  1530. #ifndef SQLITE_OMIT_COMPOUND_SELECT
  1531. /*
  1532. ** Return the appropriate collating sequence for the iCol-th column of
  1533. ** the result set for the compound-select statement "p". Return NULL if
  1534. ** the column has no default collating sequence.
  1535. **
  1536. ** The collating sequence for the compound select is taken from the
  1537. ** left-most term of the select that has a collating sequence.
  1538. */
  1539. static CollSeq *multiSelectCollSeq(Parse *pParse, Select *p, int iCol){
  1540. CollSeq *pRet;
  1541. if( p->pPrior ){
  1542. pRet = multiSelectCollSeq(pParse, p->pPrior, iCol);
  1543. }else{
  1544. pRet = 0;
  1545. }
  1546. assert( iCol>=0 );
  1547. if( pRet==0 && iCol<p->pEList->nExpr ){
  1548. pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
  1549. }
  1550. return pRet;
  1551. }
  1552. #endif /* SQLITE_OMIT_COMPOUND_SELECT */
  1553. /* Forward reference */
  1554. static int multiSelectOrderBy(
  1555. Parse *pParse, /* Parsing context */
  1556. Select *p, /* The right-most of SELECTs to be coded */
  1557. SelectDest *pDest /* What to do with query results */
  1558. );
  1559. #ifndef SQLITE_OMIT_COMPOUND_SELECT
  1560. /*
  1561. ** This routine is called to process a compound query form from
  1562. ** two or more separate queries using UNION, UNION ALL, EXCEPT, or
  1563. ** INTERSECT
  1564. **
  1565. ** "p" points to the right-most of the two queries. the query on the
  1566. ** left is p->pPrior. The left query could also be a compound query
  1567. ** in which case this routine will be called recursively.
  1568. **
  1569. ** The results of the total query are to be written into a destination
  1570. ** of type eDest with parameter iParm.
  1571. **
  1572. ** Example 1: Consider a three-way compound SQL statement.
  1573. **
  1574. ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
  1575. **
  1576. ** This statement is parsed up as follows:
  1577. **
  1578. ** SELECT c FROM t3
  1579. ** |
  1580. ** `-----> SELECT b FROM t2
  1581. ** |
  1582. ** `------> SELECT a FROM t1
  1583. **
  1584. ** The arrows in the diagram above represent the Select.pPrior pointer.
  1585. ** So if this routine is called with p equal to the t3 query, then
  1586. ** pPrior will be the t2 query. p->op will be TK_UNION in this case.
  1587. **
  1588. ** Notice that because of the way SQLite parses compound SELECTs, the
  1589. ** individual selects always group from left to right.
  1590. */
  1591. static int multiSelect(
  1592. Parse *pParse, /* Parsing context */
  1593. Select *p, /* The right-most of SELECTs to be coded */
  1594. SelectDest *pDest /* What to do with query results */
  1595. ){
  1596. int rc = SQLITE_OK; /* Success code from a subroutine */
  1597. Select *pPrior; /* Another SELECT immediately to our left */
  1598. Vdbe *v; /* Generate code to this VDBE */
  1599. SelectDest dest; /* Alternative data destination */
  1600. Select *pDelete = 0; /* Chain of simple selects to delete */
  1601. sqlite3 *db; /* Database connection */
  1602. #ifndef SQLITE_OMIT_EXPLAIN
  1603. int iSub1; /* EQP id of left-hand query */
  1604. int iSub2; /* EQP id of right-hand query */
  1605. #endif
  1606. /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
  1607. ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  1608. */
  1609. assert( p && p->pPrior ); /* Calling function guarantees this much */
  1610. db = pParse->db;
  1611. pPrior = p->pPrior;
  1612. assert( pPrior->pRightmost!=pPrior );
  1613. assert( pPrior->pRightmost==p->pRightmost );
  1614. dest = *pDest;
  1615. if( pPrior->pOrderBy ){
  1616. sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
  1617. selectOpName(p->op));
  1618. rc = 1;
  1619. goto multi_select_end;
  1620. }
  1621. if( pPrior->pLimit ){
  1622. sqlite3ErrorMsg(pParse,"LIMIT clause should come after %s not before",
  1623. selectOpName(p->op));
  1624. rc = 1;
  1625. goto multi_select_end;
  1626. }
  1627. v = sqlite3GetVdbe(pParse);
  1628. assert( v!=0 ); /* The VDBE already created by calling function */
  1629. /* Create the destination temporary table if necessary
  1630. */
  1631. if( dest.eDest==SRT_EphemTab ){
  1632. assert( p->pEList );
  1633. sqlite3VdbeAddOp2(v, OP_OpenEphemeral, dest.iSDParm, p->pEList->nExpr);
  1634. sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
  1635. dest.eDest = SRT_Table;
  1636. }
  1637. /* Make sure all SELECTs in the statement have the same number of elements
  1638. ** in their result sets.
  1639. */
  1640. assert( p->pEList && pPrior->pEList );
  1641. if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
  1642. if( p->selFlags & SF_Values ){
  1643. sqlite3ErrorMsg(pParse, "all VALUES must have the same number of terms");
  1644. }else{
  1645. sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
  1646. " do not have the same number of result columns", selectOpName(p->op));
  1647. }
  1648. rc = 1;
  1649. goto multi_select_end;
  1650. }
  1651. /* Compound SELECTs that have an ORDER BY clause are handled separately.
  1652. */
  1653. if( p->pOrderBy ){
  1654. return multiSelectOrderBy(pParse, p, pDest);
  1655. }
  1656. /* Generate code for the left and right SELECT statements.
  1657. */
  1658. switch( p->op ){
  1659. case TK_ALL: {
  1660. int addr = 0;
  1661. int nLimit;
  1662. assert( !pPrior->pLimit );
  1663. pPrior->iLimit = p->iLimit;
  1664. pPrior->iOffset = p->iOffset;
  1665. pPrior->pLimit = p->pLimit;
  1666. pPrior->pOffset = p->pOffset;
  1667. explainSetInteger(iSub1, pParse->iNextSelectId);
  1668. rc = sqlite3Select(pParse, pPrior, &dest);
  1669. p->pLimit = 0;
  1670. p->pOffset = 0;
  1671. if( rc ){
  1672. goto multi_select_end;
  1673. }
  1674. p->pPrior = 0;
  1675. p->iLimit = pPrior->iLimit;
  1676. p->iOffset = pPrior->iOffset;
  1677. if( p->iLimit ){
  1678. addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
  1679. VdbeComment((v, "Jump ahead if LIMIT reached"));
  1680. }
  1681. explainSetInteger(iSub2, pParse->iNextSelectId);
  1682. rc = sqlite3Select(pParse, p, &dest);
  1683. testcase( rc!=SQLITE_OK );
  1684. pDelete = p->pPrior;
  1685. p->pPrior = pPrior;
  1686. p->nSelectRow += pPrior->nSelectRow;
  1687. if( pPrior->pLimit
  1688. && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit)
  1689. && nLimit>0 && p->nSelectRow > (u64)nLimit
  1690. ){
  1691. p->nSelectRow = nLimit;
  1692. }
  1693. if( addr ){
  1694. sqlite3VdbeJumpHere(v, addr);
  1695. }
  1696. break;
  1697. }
  1698. case TK_EXCEPT:
  1699. case TK_UNION: {
  1700. int unionTab; /* Cursor number of the temporary table holding result */
  1701. u8 op = 0; /* One of the SRT_ operations to apply to self */
  1702. int priorOp; /* The SRT_ operation to apply to prior selects */
  1703. Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */
  1704. int addr;
  1705. SelectDest uniondest;
  1706. testcase( p->op==TK_EXCEPT );
  1707. testcase( p->op==TK_UNION );
  1708. priorOp = SRT_Union;
  1709. if( dest.eDest==priorOp && ALWAYS(!p->pLimit &&!p->pOffset) ){
  1710. /* We can reuse a temporary table generated by a SELECT to our
  1711. ** right.
  1712. */
  1713. assert( p->pRightmost!=p ); /* Can only happen for leftward elements
  1714. ** of a 3-way or more compound */
  1715. assert( p->pLimit==0 ); /* Not allowed on leftward elements */
  1716. assert( p->pOffset==0 ); /* Not allowed on leftward elements */
  1717. unionTab = dest.iSDParm;
  1718. }else{
  1719. /* We will need to create our own temporary table to hold the
  1720. ** intermediate results.
  1721. */
  1722. unionTab = pParse->nTab++;
  1723. assert( p->pOrderBy==0 );
  1724. addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, unionTab, 0);
  1725. assert( p->addrOpenEphm[0] == -1 );
  1726. p->addrOpenEphm[0] = addr;
  1727. p->pRightmost->selFlags |= SF_UsesEphemeral;
  1728. assert( p->pEList );
  1729. }
  1730. /* Code the SELECT statements to our left
  1731. */
  1732. assert( !pPrior->pOrderBy );
  1733. sqlite3SelectDestInit(&uniondest, priorOp, unionTab);
  1734. explainSetInteger(iSub1, pParse->iNextSelectId);
  1735. rc = sqlite3Select(pParse, pPrior, &uniondest);
  1736. if( rc ){
  1737. goto multi_select_end;
  1738. }
  1739. /* Code the current SELECT statement
  1740. */
  1741. if( p->op==TK_EXCEPT ){
  1742. op = SRT_Except;
  1743. }else{
  1744. assert( p->op==TK_UNION );
  1745. op = SRT_Union;
  1746. }
  1747. p->pPrior = 0;
  1748. pLimit = p->pLimit;
  1749. p->pLimit = 0;
  1750. pOffset = p->pOffset;
  1751. p->pOffset = 0;
  1752. uniondest.eDest = op;
  1753. explainSetInteger(iSub2, pParse->iNextSelectId);
  1754. rc = sqlite3Select(pParse, p, &uniondest);
  1755. testcase( rc!=SQLITE_OK );
  1756. /* Query flattening in sqlite3Select() might refill p->pOrderBy.
  1757. ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
  1758. sqlite3ExprListDelete(db, p->pOrderBy);
  1759. pDelete = p->pPrior;
  1760. p->pPrior = pPrior;
  1761. p->pOrderBy = 0;
  1762. if( p->op==TK_UNION ) p->nSelectRow += pPrior->nSelectRow;
  1763. sqlite3ExprDelete(db, p->pLimit);
  1764. p->pLimit = pLimit;
  1765. p->pOffset = pOffset;
  1766. p->iLimit = 0;
  1767. p->iOffset = 0;
  1768. /* Convert the data in the temporary table into whatever form
  1769. ** it is that we currently need.
  1770. */
  1771. assert( unionTab==dest.iSDParm || dest.eDest!=priorOp );
  1772. if( dest.eDest!=priorOp ){
  1773. int iCont, iBreak, iStart;
  1774. assert( p->pEList );
  1775. if( dest.eDest==SRT_Output ){
  1776. Select *pFirst = p;
  1777. while( pFirst->pPrior ) pFirst = pFirst->pPrior;
  1778. generateColumnNames(pParse, 0, pFirst->pEList);
  1779. }
  1780. iBreak = sqlite3VdbeMakeLabel(v);
  1781. iCont = sqlite3VdbeMakeLabel(v);
  1782. computeLimitRegisters(pParse, p, iBreak);
  1783. sqlite3VdbeAddOp2(v, OP_Rewind, unionTab, iBreak);
  1784. iStart = sqlite3VdbeCurrentAddr(v);
  1785. selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
  1786. 0, 0, &dest, iCont, iBreak);
  1787. sqlite3VdbeResolveLabel(v, iCont);
  1788. sqlite3VdbeAddOp2(v, OP_Next, unionTab, iStart);
  1789. sqlite3VdbeResolveLabel(v, iBreak);
  1790. sqlite3VdbeAddOp2(v, OP_Close, unionTab, 0);
  1791. }
  1792. break;
  1793. }
  1794. default: assert( p->op==TK_INTERSECT ); {
  1795. int tab1, tab2;
  1796. int iCont, iBreak, iStart;
  1797. Expr *pLimit, *pOffset;
  1798. int addr;
  1799. SelectDest intersectdest;
  1800. int r1;
  1801. /* INTERSECT is different from the others since it requires
  1802. ** two temporary tables. Hence it has its own case. Begin
  1803. ** by allocating the tables we will need.
  1804. */
  1805. tab1 = pParse->nTab++;
  1806. tab2 = pParse->nTab++;
  1807. assert( p->pOrderBy==0 );
  1808. addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab1, 0);
  1809. assert( p->addrOpenEphm[0] == -1 );
  1810. p->addrOpenEphm[0] = addr;
  1811. p->pRightmost->selFlags |= SF_UsesEphemeral;
  1812. assert( p->pEList );
  1813. /* Code the SELECTs to our left into temporary table "tab1".
  1814. */
  1815. sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1);
  1816. explainSetInteger(iSub1, pParse->iNextSelectId);
  1817. rc = sqlite3Select(pParse, pPrior, &intersectdest);
  1818. if( rc ){
  1819. goto multi_select_end;
  1820. }
  1821. /* Code the current SELECT into temporary table "tab2"
  1822. */
  1823. addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab2, 0);
  1824. assert( p->addrOpenEphm[1] == -1 );
  1825. p->addrOpenEphm[1] = addr;
  1826. p->pPrior = 0;
  1827. pLimit = p->pLimit;
  1828. p->pLimit = 0;
  1829. pOffset = p->pOffset;
  1830. p->pOffset = 0;
  1831. intersectdest.iSDParm = tab2;
  1832. explainSetInteger(iSub2, pParse->iNextSelectId);
  1833. rc = sqlite3Select(pParse, p, &intersectdest);
  1834. testcase( rc!=SQLITE_OK );
  1835. pDelete = p->pPrior;
  1836. p->pPrior = pPrior;
  1837. if( p->nSelectRow>pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow;
  1838. sqlite3ExprDelete(db, p->pLimit);
  1839. p->pLimit = pLimit;
  1840. p->pOffset = pOffset;
  1841. /* Generate code to take the intersection of the two temporary
  1842. ** tables.
  1843. */
  1844. assert( p->pEList );
  1845. if( dest.eDest==SRT_Output ){
  1846. Select *pFirst = p;
  1847. while( pFirst->pPrior ) pFirst = pFirst->pPrior;
  1848. generateColumnNames(pParse, 0, pFirst->pEList);
  1849. }
  1850. iBreak = sqlite3VdbeMakeLabel(v);
  1851. iCont = sqlite3VdbeMakeLabel(v);
  1852. computeLimitRegisters(pParse, p, iBreak);
  1853. sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak);
  1854. r1 = sqlite3GetTempReg(pParse);
  1855. iStart = sqlite3VdbeAddOp2(v, OP_RowKey, tab1, r1);
  1856. sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0);
  1857. sqlite3ReleaseTempReg(pParse, r1);
  1858. selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
  1859. 0, 0, &dest, iCont, iBreak);
  1860. sqlite3VdbeResolveLabel(v, iCont);
  1861. sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart);
  1862. sqlite3VdbeResolveLabel(v, iBreak);
  1863. sqlite3VdbeAddOp2(v, OP_Close, tab2, 0);
  1864. sqlite3VdbeAddOp2(v, OP_Close, tab1, 0);
  1865. break;
  1866. }
  1867. }
  1868. explainComposite(pParse, p->op, iSub1, iSub2, p->op!=TK_ALL);
  1869. /* Compute collating sequences used by
  1870. ** temporary tables needed to implement the compound select.
  1871. ** Attach the KeyInfo structure to all temporary tables.
  1872. **
  1873. ** This section is run by the right-most SELECT statement only.
  1874. ** SELECT statements to the left always skip this part. The right-most
  1875. ** SELECT might also skip this part if it has no ORDER BY clause and
  1876. ** no temp tables are required.
  1877. */
  1878. if( p->selFlags & SF_UsesEphemeral ){
  1879. int i; /* Loop counter */
  1880. KeyInfo *pKeyInfo; /* Collating sequence for the result set */
  1881. Select *pLoop; /* For looping through SELECT statements */
  1882. CollSeq **apColl; /* For looping through pKeyInfo->aColl[] */
  1883. int nCol; /* Number of columns in result set */
  1884. assert( p->pRightmost==p );
  1885. nCol = p->pEList->nExpr;
  1886. pKeyInfo = sqlite3KeyInfoAlloc(db, nCol);
  1887. if( !pKeyInfo ){
  1888. rc = SQLITE_NOMEM;
  1889. goto multi_select_end;
  1890. }
  1891. for(i=0, apColl=pKeyInfo->aColl; i<nCol; i++, apColl++){
  1892. *apColl = multiSelectCollSeq(pParse, p, i);
  1893. if( 0==*apColl ){
  1894. *apColl = db->pDfltColl;
  1895. }
  1896. }
  1897. for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
  1898. for(i=0; i<2; i++){
  1899. int addr = pLoop->addrOpenEphm[i];
  1900. if( addr<0 ){
  1901. /* If [0] is unused then [1] is also unused. So we can
  1902. ** always safely abort as soon as the first unused slot is found */
  1903. assert( pLoop->addrOpenEphm[1]<0 );
  1904. break;
  1905. }
  1906. sqlite3VdbeChangeP2(v, addr, nCol);
  1907. sqlite3VdbeChangeP4(v, addr, (char*)pKeyInfo, P4_KEYINFO);
  1908. pLoop->addrOpenEphm[i] = -1;
  1909. }
  1910. }
  1911. sqlite3DbFree(db, pKeyInfo);
  1912. }
  1913. multi_select_end:
  1914. pDest->iSdst = dest.iSdst;
  1915. pDest->nSdst = dest.nSdst;
  1916. sqlite3SelectDelete(db, pDelete);
  1917. return rc;
  1918. }
  1919. #endif /* SQLITE_OMIT_COMPOUND_SELECT */
  1920. /*
  1921. ** Code an output subroutine for a coroutine implementation of a
  1922. ** SELECT statment.
  1923. **
  1924. ** The data to be output is contained in pIn->iSdst. There are
  1925. ** pIn->nSdst columns to be output. pDest is where the output should
  1926. ** be sent.
  1927. **
  1928. ** regReturn is the number of the register holding the subroutine
  1929. ** return address.
  1930. **
  1931. ** If regPrev>0 then it is the first register in a vector that
  1932. ** records the previous output. mem[regPrev] is a flag that is false
  1933. ** if there has been no previous output. If regPrev>0 then code is
  1934. ** generated to suppress duplicates. pKeyInfo is used for comparing
  1935. ** keys.
  1936. **
  1937. ** If the LIMIT found in p->iLimit is reached, jump immediately to
  1938. ** iBreak.
  1939. */
  1940. static int generateOutputSubroutine(
  1941. Parse *pParse, /* Parsing context */
  1942. Select *p, /* The SELECT statement */
  1943. SelectDest *pIn, /* Coroutine supplying data */
  1944. SelectDest *pDest, /* Where to send the data */
  1945. int regReturn, /* The return address register */
  1946. int regPrev, /* Previous result register. No uniqueness if 0 */
  1947. KeyInfo *pKeyInfo, /* For comparing with previous entry */
  1948. int p4type, /* The p4 type for pKeyInfo */
  1949. int iBreak /* Jump here if we hit the LIMIT */
  1950. ){
  1951. Vdbe *v = pParse->pVdbe;
  1952. int iContinue;
  1953. int addr;
  1954. addr = sqlite3VdbeCurrentAddr(v);
  1955. iContinue = sqlite3VdbeMakeLabel(v);
  1956. /* Suppress duplicates for UNION, EXCEPT, and INTERSECT
  1957. */
  1958. if( regPrev ){
  1959. int j1, j2;
  1960. j1 = sqlite3VdbeAddOp1(v, OP_IfNot, regPrev);
  1961. j2 = sqlite3VdbeAddOp4(v, OP_Compare, pIn->iSdst, regPrev+1, pIn->nSdst,
  1962. (char*)pKeyInfo, p4type);
  1963. sqlite3VdbeAddOp3(v, OP_Jump, j2+2, iContinue, j2+2);
  1964. sqlite3VdbeJumpHere(v, j1);
  1965. sqlite3VdbeAddOp3(v, OP_Copy, pIn->iSdst, regPrev+1, pIn->nSdst-1);
  1966. sqlite3VdbeAddOp2(v, OP_Integer, 1, regPrev);
  1967. }
  1968. if( pParse->db->mallocFailed ) return 0;
  1969. /* Suppress the first OFFSET entries if there is an OFFSET clause
  1970. */
  1971. codeOffset(v, p, iContinue);
  1972. switch( pDest->eDest ){
  1973. /* Store the result as data using a unique key.
  1974. */
  1975. case SRT_Table:
  1976. case SRT_EphemTab: {
  1977. int r1 = sqlite3GetTempReg(pParse);
  1978. int r2 = sqlite3GetTempReg(pParse);
  1979. testcase( pDest->eDest==SRT_Table );
  1980. testcase( pDest->eDest==SRT_EphemTab );
  1981. sqlite3VdbeAddOp3(v, OP_MakeRecord, pIn->iSdst, pIn->nSdst, r1);
  1982. sqlite3VdbeAddOp2(v, OP_NewRowid, pDest->iSDParm, r2);
  1983. sqlite3VdbeAddOp3(v, OP_Insert, pDest->iSDParm, r1, r2);
  1984. sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
  1985. sqlite3ReleaseTempReg(pParse, r2);
  1986. sqlite3ReleaseTempReg(pParse, r1);
  1987. break;
  1988. }
  1989. #ifndef SQLITE_OMIT_SUBQUERY
  1990. /* If we are creating a set for an "expr IN (SELECT ...)" construct,
  1991. ** then there should be a single item on the stack. Write this
  1992. ** item into the set table with bogus data.
  1993. */
  1994. case SRT_Set: {
  1995. int r1;
  1996. assert( pIn->nSdst==1 );
  1997. pDest->affSdst =
  1998. sqlite3CompareAffinity(p->pEList->a[0].pExpr, pDest->affSdst);
  1999. r1 = sqlite3GetTempReg(pParse);
  2000. sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iSdst, 1, r1, &pDest->affSdst,1);
  2001. sqlite3ExprCacheAffinityChange(pParse, pIn->iSdst, 1);
  2002. sqlite3VdbeAddOp2(v, OP_IdxInsert, pDest->iSDParm, r1);
  2003. sqlite3ReleaseTempReg(pParse, r1);
  2004. break;
  2005. }
  2006. #if 0 /* Never occurs on an ORDER BY query */
  2007. /* If any row exist in the result set, record that fact and abort.
  2008. */
  2009. case SRT_Exists: {
  2010. sqlite3VdbeAddOp2(v, OP_Integer, 1, pDest->iSDParm);
  2011. /* The LIMIT clause will terminate the loop for us */
  2012. break;
  2013. }
  2014. #endif
  2015. /* If this is a scalar select that is part of an expression, then
  2016. ** store the results in the appropriate memory cell and break out
  2017. ** of the scan loop.
  2018. */
  2019. case SRT_Mem: {
  2020. assert( pIn->nSdst==1 );
  2021. sqlite3ExprCodeMove(pParse, pIn->iSdst, pDest->iSDParm, 1);
  2022. /* The LIMIT clause will jump out of the loop for us */
  2023. break;
  2024. }
  2025. #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
  2026. /* The results are stored in a sequence of registers
  2027. ** starting at pDest->iSdst. Then the co-routine yields.
  2028. */
  2029. case SRT_Coroutine: {
  2030. if( pDest->iSdst==0 ){
  2031. pDest->iSdst = sqlite3GetTempRange(pParse, pIn->nSdst);
  2032. pDest->nSdst = pIn->nSdst;
  2033. }
  2034. sqlite3ExprCodeMove(pParse, pIn->iSdst, pDest->iSdst, pDest->nSdst);
  2035. sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
  2036. break;
  2037. }
  2038. /* If none of the above, then the result destination must be
  2039. ** SRT_Output. This routine is never called with any other
  2040. ** destination other than the ones handled above or SRT_Output.
  2041. **
  2042. ** For SRT_Output, results are stored in a sequence of registers.
  2043. ** Then the OP_ResultRow opcode is used to cause sqlite3_step() to
  2044. ** return the next row of result.
  2045. */
  2046. default: {
  2047. assert( pDest->eDest==SRT_Output );
  2048. sqlite3VdbeAddOp2(v, OP_ResultRow, pIn->iSdst, pIn->nSdst);
  2049. sqlite3ExprCacheAffinityChange(pParse, pIn->iSdst, pIn->nSdst);
  2050. break;
  2051. }
  2052. }
  2053. /* Jump to the end of the loop if the LIMIT is reached.
  2054. */
  2055. if( p->iLimit ){
  2056. sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
  2057. }
  2058. /* Generate the subroutine return
  2059. */
  2060. sqlite3VdbeResolveLabel(v, iContinue);
  2061. sqlite3VdbeAddOp1(v, OP_Return, regReturn);
  2062. return addr;
  2063. }
  2064. /*
  2065. ** Alternative compound select code generator for cases when there
  2066. ** is an ORDER BY clause.
  2067. **
  2068. ** We assume a query of the following form:
  2069. **
  2070. ** <selectA> <operator> <selectB> ORDER BY <orderbylist>
  2071. **
  2072. ** <operator> is one of UNION ALL, UNION, EXCEPT, or INTERSECT. The idea
  2073. ** is to code both <selectA> and <selectB> with the ORDER BY clause as
  2074. ** co-routines. Then run the co-routines in parallel and merge the results
  2075. ** into the output. In addition to the two coroutines (called selectA and
  2076. ** selectB) there are 7 subroutines:
  2077. **
  2078. ** outA: Move the output of the selectA coroutine into the output
  2079. ** of the compound query.
  2080. **
  2081. ** outB: Move the output of the selectB coroutine into the output
  2082. ** of the compound query. (Only generated for UNION and
  2083. ** UNION ALL. EXCEPT and INSERTSECT never output a row that
  2084. ** appears only in B.)
  2085. **
  2086. ** AltB: Called when there is data from both coroutines and A<B.
  2087. **
  2088. ** AeqB: Called when there is data from both coroutines and A==B.
  2089. **
  2090. ** AgtB: Called when there is data from both coroutines and A>B.
  2091. **
  2092. ** EofA: Called when data is exhausted from selectA.
  2093. **
  2094. ** EofB: Called when data is exhausted from selectB.
  2095. **
  2096. ** The implementation of the latter five subroutines depend on which
  2097. ** <operator> is used:
  2098. **
  2099. **
  2100. ** UNION ALL UNION EXCEPT INTERSECT
  2101. ** ------------- ----------------- -------------- -----------------
  2102. ** AltB: outA, nextA outA, nextA outA, nextA nextA
  2103. **
  2104. ** AeqB: outA, nextA nextA nextA outA, nextA
  2105. **
  2106. ** AgtB: outB, nextB outB, nextB nextB nextB
  2107. **
  2108. ** EofA: outB, nextB outB, nextB halt halt
  2109. **
  2110. ** EofB: outA, nextA outA, nextA outA, nextA halt
  2111. **
  2112. ** In the AltB, AeqB, and AgtB subroutines, an EOF on A following nextA
  2113. ** causes an immediate jump to EofA and an EOF on B following nextB causes
  2114. ** an immediate jump to EofB. Within EofA and EofB, and EOF on entry or
  2115. ** following nextX causes a jump to the end of the select processing.
  2116. **
  2117. ** Duplicate removal in the UNION, EXCEPT, and INTERSECT cases is handled
  2118. ** within the output subroutine. The regPrev register set holds the previously
  2119. ** output value. A comparison is made against this value and the output
  2120. ** is skipped if the next results would be the same as the previous.
  2121. **
  2122. ** The implementation plan is to implement the two coroutines and seven
  2123. ** subroutines first, then put the control logic at the bottom. Like this:
  2124. **
  2125. ** goto Init
  2126. ** coA: coroutine for left query (A)
  2127. ** coB: coroutine for right query (B)
  2128. ** outA: output one row of A
  2129. ** outB: output one row of B (UNION and UNION ALL only)
  2130. ** EofA: ...
  2131. ** EofB: ...
  2132. ** AltB: ...
  2133. ** AeqB: ...
  2134. ** AgtB: ...
  2135. ** Init: initialize coroutine registers
  2136. ** yield coA
  2137. ** if eof(A) goto EofA
  2138. ** yield coB
  2139. ** if eof(B) goto EofB
  2140. ** Cmpr: Compare A, B
  2141. ** Jump AltB, AeqB, AgtB
  2142. ** End: ...
  2143. **
  2144. ** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
  2145. ** actually called using Gosub and they do not Return. EofA and EofB loop
  2146. ** until all data is exhausted then jump to the "end" labe. AltB, AeqB,
  2147. ** and AgtB jump to either L2 or to one of EofA or EofB.
  2148. */
  2149. #ifndef SQLITE_OMIT_COMPOUND_SELECT
  2150. static int multiSelectOrderBy(
  2151. Parse *pParse, /* Parsing context */
  2152. Select *p, /* The right-most of SELECTs to be coded */
  2153. SelectDest *pDest /* What to do with query results */
  2154. ){
  2155. int i, j; /* Loop counters */
  2156. Select *pPrior; /* Another SELECT immediately to our left */
  2157. Vdbe *v; /* Generate code to this VDBE */
  2158. SelectDest destA; /* Destination for coroutine A */
  2159. SelectDest destB; /* Destination for coroutine B */
  2160. int regAddrA; /* Address register for select-A coroutine */
  2161. int regEofA; /* Flag to indicate when select-A is complete */
  2162. int regAddrB; /* Address register for select-B coroutine */
  2163. int regEofB; /* Flag to indicate when select-B is complete */
  2164. int addrSelectA; /* Address of the select-A coroutine */
  2165. int addrSelectB; /* Address of the select-B coroutine */
  2166. int regOutA; /* Address register for the output-A subroutine */
  2167. int regOutB; /* Address register for the output-B subroutine */
  2168. int addrOutA; /* Address of the output-A subroutine */
  2169. int addrOutB = 0; /* Address of the output-B subroutine */
  2170. int addrEofA; /* Address of the select-A-exhausted subroutine */
  2171. int addrEofB; /* Address of the select-B-exhausted subroutine */
  2172. int addrAltB; /* Address of the A<B subroutine */
  2173. int addrAeqB; /* Address of the A==B subroutine */
  2174. int addrAgtB; /* Address of the A>B subroutine */
  2175. int regLimitA; /* Limit register for select-A */
  2176. int regLimitB; /* Limit register for select-A */
  2177. int regPrev; /* A range of registers to hold previous output */
  2178. int savedLimit; /* Saved value of p->iLimit */
  2179. int savedOffset; /* Saved value of p->iOffset */
  2180. int labelCmpr; /* Label for the start of the merge algorithm */
  2181. int labelEnd; /* Label for the end of the overall SELECT stmt */
  2182. int j1; /* Jump instructions that get retargetted */
  2183. int op; /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
  2184. KeyInfo *pKeyDup = 0; /* Comparison information for duplicate removal */
  2185. KeyInfo *pKeyMerge; /* Comparison information for merging rows */
  2186. sqlite3 *db; /* Database connection */
  2187. ExprList *pOrderBy; /* The ORDER BY clause */
  2188. int nOrderBy; /* Number of terms in the ORDER BY clause */
  2189. int *aPermute; /* Mapping from ORDER BY terms to result set columns */
  2190. #ifndef SQLITE_OMIT_EXPLAIN
  2191. int iSub1; /* EQP id of left-hand query */
  2192. int iSub2; /* EQP id of right-hand query */
  2193. #endif
  2194. assert( p->pOrderBy!=0 );
  2195. assert( pKeyDup==0 ); /* "Managed" code needs this. Ticket #3382. */
  2196. db = pParse->db;
  2197. v = pParse->pVdbe;
  2198. assert( v!=0 ); /* Already thrown the error if VDBE alloc failed */
  2199. labelEnd = sqlite3VdbeMakeLabel(v);
  2200. labelCmpr = sqlite3VdbeMakeLabel(v);
  2201. /* Patch up the ORDER BY clause
  2202. */
  2203. op = p->op;
  2204. pPrior = p->pPrior;
  2205. assert( pPrior->pOrderBy==0 );
  2206. pOrderBy = p->pOrderBy;
  2207. assert( pOrderBy );
  2208. nOrderBy = pOrderBy->nExpr;
  2209. /* For operators other than UNION ALL we have to make sure that
  2210. ** the ORDER BY clause covers every term of the result set. Add
  2211. ** terms to the ORDER BY clause as necessary.
  2212. */
  2213. if( op!=TK_ALL ){
  2214. for(i=1; db->mallocFailed==0 && i<=p->pEList->nExpr; i++){
  2215. struct ExprList_item *pItem;
  2216. for(j=0, pItem=pOrderBy->a; j<nOrderBy; j++, pItem++){
  2217. assert( pItem->iOrderByCol>0 );
  2218. if( pItem->iOrderByCol==i ) break;
  2219. }
  2220. if( j==nOrderBy ){
  2221. Expr *pNew = sqlite3Expr(db, TK_INTEGER, 0);
  2222. if( pNew==0 ) return SQLITE_NOMEM;
  2223. pNew->flags |= EP_IntValue;
  2224. pNew->u.iValue = i;
  2225. pOrderBy = sqlite3ExprListAppend(pParse, pOrderBy, pNew);
  2226. if( pOrderBy ) pOrderBy->a[nOrderBy++].iOrderByCol = (u16)i;
  2227. }
  2228. }
  2229. }
  2230. /* Compute the comparison permutation and keyinfo that is used with
  2231. ** the permutation used to determine if the next
  2232. ** row of results comes from selectA or selectB. Also add explicit
  2233. ** collations to the ORDER BY clause terms so that when the subqueries
  2234. ** to the right and the left are evaluated, they use the correct
  2235. ** collation.
  2236. */
  2237. aPermute = sqlite3DbMallocRaw(db, sizeof(int)*nOrderBy);
  2238. if( aPermute ){
  2239. struct ExprList_item *pItem;
  2240. for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
  2241. assert( pItem->iOrderByCol>0 && pItem->iOrderByCol<=p->pEList->nExpr );
  2242. aPermute[i] = pItem->iOrderByCol - 1;
  2243. }
  2244. pKeyMerge = sqlite3KeyInfoAlloc(db, nOrderBy);
  2245. if( pKeyMerge ){
  2246. for(i=0; i<nOrderBy; i++){
  2247. CollSeq *pColl;
  2248. Expr *pTerm = pOrderBy->a[i].pExpr;
  2249. if( pTerm->flags & EP_Collate ){
  2250. pColl = sqlite3ExprCollSeq(pParse, pTerm);
  2251. }else{
  2252. pColl = multiSelectCollSeq(pParse, p, aPermute[i]);
  2253. if( pColl==0 ) pColl = db->pDfltColl;
  2254. pOrderBy->a[i].pExpr =
  2255. sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
  2256. }
  2257. pKeyMerge->aColl[i] = pColl;
  2258. pKeyMerge->aSortOrder[i] = pOrderBy->a[i].sortOrder;
  2259. }
  2260. }
  2261. }else{
  2262. pKeyMerge = 0;
  2263. }
  2264. /* Reattach the ORDER BY clause to the query.
  2265. */
  2266. p->pOrderBy = pOrderBy;
  2267. pPrior->pOrderBy = sqlite3ExprListDup(pParse->db, pOrderBy, 0);
  2268. /* Allocate a range of temporary registers and the KeyInfo needed
  2269. ** for the logic that removes duplicate result rows when the
  2270. ** operator is UNION, EXCEPT, or INTERSECT (but not UNION ALL).
  2271. */
  2272. if( op==TK_ALL ){
  2273. regPrev = 0;
  2274. }else{
  2275. int nExpr = p->pEList->nExpr;
  2276. assert( nOrderBy>=nExpr || db->mallocFailed );
  2277. regPrev = pParse->nMem+1;
  2278. pParse->nMem += nExpr+1;
  2279. sqlite3VdbeAddOp2(v, OP_Integer, 0, regPrev);
  2280. pKeyDup = sqlite3KeyInfoAlloc(db, nExpr);
  2281. if( pKeyDup ){
  2282. for(i=0; i<nExpr; i++){
  2283. pKeyDup->aColl[i] = multiSelectCollSeq(pParse, p, i);
  2284. pKeyDup->aSortOrder[i] = 0;
  2285. }
  2286. }
  2287. }
  2288. /* Separate the left and the right query from one another
  2289. */
  2290. p->pPrior = 0;
  2291. sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER");
  2292. if( pPrior->pPrior==0 ){
  2293. sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER");
  2294. }
  2295. /* Compute the limit registers */
  2296. computeLimitRegisters(pParse, p, labelEnd);
  2297. if( p->iLimit && op==TK_ALL ){
  2298. regLimitA = ++pParse->nMem;
  2299. regLimitB = ++pParse->nMem;
  2300. sqlite3VdbeAddOp2(v, OP_Copy, p->iOffset ? p->iOffset+1 : p->iLimit,
  2301. regLimitA);
  2302. sqlite3VdbeAddOp2(v, OP_Copy, regLimitA, regLimitB);
  2303. }else{
  2304. regLimitA = regLimitB = 0;
  2305. }
  2306. sqlite3ExprDelete(db, p->pLimit);
  2307. p->pLimit = 0;
  2308. sqlite3ExprDelete(db, p->pOffset);
  2309. p->pOffset = 0;
  2310. regAddrA = ++pParse->nMem;
  2311. regEofA = ++pParse->nMem;
  2312. regAddrB = ++pParse->nMem;
  2313. regEofB = ++pParse->nMem;
  2314. regOutA = ++pParse->nMem;
  2315. regOutB = ++pParse->nMem;
  2316. sqlite3SelectDestInit(&destA, SRT_Coroutine, regAddrA);
  2317. sqlite3SelectDestInit(&destB, SRT_Coroutine, regAddrB);
  2318. /* Jump past the various subroutines and coroutines to the main
  2319. ** merge loop
  2320. */
  2321. j1 = sqlite3VdbeAddOp0(v, OP_Goto);
  2322. addrSelectA = sqlite3VdbeCurrentAddr(v);
  2323. /* Generate a coroutine to evaluate the SELECT statement to the
  2324. ** left of the compound operator - the "A" select.
  2325. */
  2326. VdbeNoopComment((v, "Begin coroutine for left SELECT"));
  2327. pPrior->iLimit = regLimitA;
  2328. explainSetInteger(iSub1, pParse->iNextSelectId);
  2329. sqlite3Select(pParse, pPrior, &destA);
  2330. sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofA);
  2331. sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2332. VdbeNoopComment((v, "End coroutine for left SELECT"));
  2333. /* Generate a coroutine to evaluate the SELECT statement on
  2334. ** the right - the "B" select
  2335. */
  2336. addrSelectB = sqlite3VdbeCurrentAddr(v);
  2337. VdbeNoopComment((v, "Begin coroutine for right SELECT"));
  2338. savedLimit = p->iLimit;
  2339. savedOffset = p->iOffset;
  2340. p->iLimit = regLimitB;
  2341. p->iOffset = 0;
  2342. explainSetInteger(iSub2, pParse->iNextSelectId);
  2343. sqlite3Select(pParse, p, &destB);
  2344. p->iLimit = savedLimit;
  2345. p->iOffset = savedOffset;
  2346. sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofB);
  2347. sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
  2348. VdbeNoopComment((v, "End coroutine for right SELECT"));
  2349. /* Generate a subroutine that outputs the current row of the A
  2350. ** select as the next output row of the compound select.
  2351. */
  2352. VdbeNoopComment((v, "Output routine for A"));
  2353. addrOutA = generateOutputSubroutine(pParse,
  2354. p, &destA, pDest, regOutA,
  2355. regPrev, pKeyDup, P4_KEYINFO_HANDOFF, labelEnd);
  2356. /* Generate a subroutine that outputs the current row of the B
  2357. ** select as the next output row of the compound select.
  2358. */
  2359. if( op==TK_ALL || op==TK_UNION ){
  2360. VdbeNoopComment((v, "Output routine for B"));
  2361. addrOutB = generateOutputSubroutine(pParse,
  2362. p, &destB, pDest, regOutB,
  2363. regPrev, pKeyDup, P4_KEYINFO_STATIC, labelEnd);
  2364. }
  2365. /* Generate a subroutine to run when the results from select A
  2366. ** are exhausted and only data in select B remains.
  2367. */
  2368. VdbeNoopComment((v, "eof-A subroutine"));
  2369. if( op==TK_EXCEPT || op==TK_INTERSECT ){
  2370. addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd);
  2371. }else{
  2372. addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd);
  2373. sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB);
  2374. sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
  2375. sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA);
  2376. p->nSelectRow += pPrior->nSelectRow;
  2377. }
  2378. /* Generate a subroutine to run when the results from select B
  2379. ** are exhausted and only data in select A remains.
  2380. */
  2381. if( op==TK_INTERSECT ){
  2382. addrEofB = addrEofA;
  2383. if( p->nSelectRow > pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow;
  2384. }else{
  2385. VdbeNoopComment((v, "eof-B subroutine"));
  2386. addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd);
  2387. sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA);
  2388. sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2389. sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB);
  2390. }
  2391. /* Generate code to handle the case of A<B
  2392. */
  2393. VdbeNoopComment((v, "A-lt-B subroutine"));
  2394. addrAltB = sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA);
  2395. sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2396. sqlite3VdbeAddOp2(v, OP_If, regEofA, addrEofA);
  2397. sqlite3VdbeAddOp2(v, OP_Goto, 0, labelCmpr);
  2398. /* Generate code to handle the case of A==B
  2399. */
  2400. if( op==TK_ALL ){
  2401. addrAeqB = addrAltB;
  2402. }else if( op==TK_INTERSECT ){
  2403. addrAeqB = addrAltB;
  2404. addrAltB++;
  2405. }else{
  2406. VdbeNoopComment((v, "A-eq-B subroutine"));
  2407. addrAeqB =
  2408. sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2409. sqlite3VdbeAddOp2(v, OP_If, regEofA, addrEofA);
  2410. sqlite3VdbeAddOp2(v, OP_Goto, 0, labelCmpr);
  2411. }
  2412. /* Generate code to handle the case of A>B
  2413. */
  2414. VdbeNoopComment((v, "A-gt-B subroutine"));
  2415. addrAgtB = sqlite3VdbeCurrentAddr(v);
  2416. if( op==TK_ALL || op==TK_UNION ){
  2417. sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB);
  2418. }
  2419. sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
  2420. sqlite3VdbeAddOp2(v, OP_If, regEofB, addrEofB);
  2421. sqlite3VdbeAddOp2(v, OP_Goto, 0, labelCmpr);
  2422. /* This code runs once to initialize everything.
  2423. */
  2424. sqlite3VdbeJumpHere(v, j1);
  2425. sqlite3VdbeAddOp2(v, OP_Integer, 0, regEofA);
  2426. sqlite3VdbeAddOp2(v, OP_Integer, 0, regEofB);
  2427. sqlite3VdbeAddOp2(v, OP_Gosub, regAddrA, addrSelectA);
  2428. sqlite3VdbeAddOp2(v, OP_Gosub, regAddrB, addrSelectB);
  2429. sqlite3VdbeAddOp2(v, OP_If, regEofA, addrEofA);
  2430. sqlite3VdbeAddOp2(v, OP_If, regEofB, addrEofB);
  2431. /* Implement the main merge loop
  2432. */
  2433. sqlite3VdbeResolveLabel(v, labelCmpr);
  2434. sqlite3VdbeAddOp4(v, OP_Permutation, 0, 0, 0, (char*)aPermute, P4_INTARRAY);
  2435. sqlite3VdbeAddOp4(v, OP_Compare, destA.iSdst, destB.iSdst, nOrderBy,
  2436. (char*)pKeyMerge, P4_KEYINFO_HANDOFF);
  2437. sqlite3VdbeChangeP5(v, OPFLAG_PERMUTE);
  2438. sqlite3VdbeAddOp3(v, OP_Jump, addrAltB, addrAeqB, addrAgtB);
  2439. /* Jump to the this point in order to terminate the query.
  2440. */
  2441. sqlite3VdbeResolveLabel(v, labelEnd);
  2442. /* Set the number of output columns
  2443. */
  2444. if( pDest->eDest==SRT_Output ){
  2445. Select *pFirst = pPrior;
  2446. while( pFirst->pPrior ) pFirst = pFirst->pPrior;
  2447. generateColumnNames(pParse, 0, pFirst->pEList);
  2448. }
  2449. /* Reassembly the compound query so that it will be freed correctly
  2450. ** by the calling function */
  2451. if( p->pPrior ){
  2452. sqlite3SelectDelete(db, p->pPrior);
  2453. }
  2454. p->pPrior = pPrior;
  2455. /*** TBD: Insert subroutine calls to close cursors on incomplete
  2456. **** subqueries ****/
  2457. explainComposite(pParse, p->op, iSub1, iSub2, 0);
  2458. return SQLITE_OK;
  2459. }
  2460. #endif
  2461. #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  2462. /* Forward Declarations */
  2463. static void substExprList(sqlite3*, ExprList*, int, ExprList*);
  2464. static void substSelect(sqlite3*, Select *, int, ExprList *);
  2465. /*
  2466. ** Scan through the expression pExpr. Replace every reference to
  2467. ** a column in table number iTable with a copy of the iColumn-th
  2468. ** entry in pEList. (But leave references to the ROWID column
  2469. ** unchanged.)
  2470. **
  2471. ** This routine is part of the flattening procedure. A subquery
  2472. ** whose result set is defined by pEList appears as entry in the
  2473. ** FROM clause of a SELECT such that the VDBE cursor assigned to that
  2474. ** FORM clause entry is iTable. This routine make the necessary
  2475. ** changes to pExpr so that it refers directly to the source table
  2476. ** of the subquery rather the result set of the subquery.
  2477. */
  2478. static Expr *substExpr(
  2479. sqlite3 *db, /* Report malloc errors to this connection */
  2480. Expr *pExpr, /* Expr in which substitution occurs */
  2481. int iTable, /* Table to be substituted */
  2482. ExprList *pEList /* Substitute expressions */
  2483. ){
  2484. if( pExpr==0 ) return 0;
  2485. if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
  2486. if( pExpr->iColumn<0 ){
  2487. pExpr->op = TK_NULL;
  2488. }else{
  2489. Expr *pNew;
  2490. assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
  2491. assert( pExpr->pLeft==0 && pExpr->pRight==0 );
  2492. pNew = sqlite3ExprDup(db, pEList->a[pExpr->iColumn].pExpr, 0);
  2493. sqlite3ExprDelete(db, pExpr);
  2494. pExpr = pNew;
  2495. }
  2496. }else{
  2497. pExpr->pLeft = substExpr(db, pExpr->pLeft, iTable, pEList);
  2498. pExpr->pRight = substExpr(db, pExpr->pRight, iTable, pEList);
  2499. if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  2500. substSelect(db, pExpr->x.pSelect, iTable, pEList);
  2501. }else{
  2502. substExprList(db, pExpr->x.pList, iTable, pEList);
  2503. }
  2504. }
  2505. return pExpr;
  2506. }
  2507. static void substExprList(
  2508. sqlite3 *db, /* Report malloc errors here */
  2509. ExprList *pList, /* List to scan and in which to make substitutes */
  2510. int iTable, /* Table to be substituted */
  2511. ExprList *pEList /* Substitute values */
  2512. ){
  2513. int i;
  2514. if( pList==0 ) return;
  2515. for(i=0; i<pList->nExpr; i++){
  2516. pList->a[i].pExpr = substExpr(db, pList->a[i].pExpr, iTable, pEList);
  2517. }
  2518. }
  2519. static void substSelect(
  2520. sqlite3 *db, /* Report malloc errors here */
  2521. Select *p, /* SELECT statement in which to make substitutions */
  2522. int iTable, /* Table to be replaced */
  2523. ExprList *pEList /* Substitute values */
  2524. ){
  2525. SrcList *pSrc;
  2526. struct SrcList_item *pItem;
  2527. int i;
  2528. if( !p ) return;
  2529. substExprList(db, p->pEList, iTable, pEList);
  2530. substExprList(db, p->pGroupBy, iTable, pEList);
  2531. substExprList(db, p->pOrderBy, iTable, pEList);
  2532. p->pHaving = substExpr(db, p->pHaving, iTable, pEList);
  2533. p->pWhere = substExpr(db, p->pWhere, iTable, pEList);
  2534. substSelect(db, p->pPrior, iTable, pEList);
  2535. pSrc = p->pSrc;
  2536. assert( pSrc ); /* Even for (SELECT 1) we have: pSrc!=0 but pSrc->nSrc==0 */
  2537. if( ALWAYS(pSrc) ){
  2538. for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){
  2539. substSelect(db, pItem->pSelect, iTable, pEList);
  2540. }
  2541. }
  2542. }
  2543. #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  2544. #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  2545. /*
  2546. ** This routine attempts to flatten subqueries as a performance optimization.
  2547. ** This routine returns 1 if it makes changes and 0 if no flattening occurs.
  2548. **
  2549. ** To understand the concept of flattening, consider the following
  2550. ** query:
  2551. **
  2552. ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
  2553. **
  2554. ** The default way of implementing this query is to execute the
  2555. ** subquery first and store the results in a temporary table, then
  2556. ** run the outer query on that temporary table. This requires two
  2557. ** passes over the data. Furthermore, because the temporary table
  2558. ** has no indices, the WHERE clause on the outer query cannot be
  2559. ** optimized.
  2560. **
  2561. ** This routine attempts to rewrite queries such as the above into
  2562. ** a single flat select, like this:
  2563. **
  2564. ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
  2565. **
  2566. ** The code generated for this simpification gives the same result
  2567. ** but only has to scan the data once. And because indices might
  2568. ** exist on the table t1, a complete scan of the data might be
  2569. ** avoided.
  2570. **
  2571. ** Flattening is only attempted if all of the following are true:
  2572. **
  2573. ** (1) The subquery and the outer query do not both use aggregates.
  2574. **
  2575. ** (2) The subquery is not an aggregate or the outer query is not a join.
  2576. **
  2577. ** (3) The subquery is not the right operand of a left outer join
  2578. ** (Originally ticket #306. Strengthened by ticket #3300)
  2579. **
  2580. ** (4) The subquery is not DISTINCT.
  2581. **
  2582. ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT
  2583. ** sub-queries that were excluded from this optimization. Restriction
  2584. ** (4) has since been expanded to exclude all DISTINCT subqueries.
  2585. **
  2586. ** (6) The subquery does not use aggregates or the outer query is not
  2587. ** DISTINCT.
  2588. **
  2589. ** (7) The subquery has a FROM clause. TODO: For subqueries without
  2590. ** A FROM clause, consider adding a FROM close with the special
  2591. ** table sqlite_once that consists of a single row containing a
  2592. ** single NULL.
  2593. **
  2594. ** (8) The subquery does not use LIMIT or the outer query is not a join.
  2595. **
  2596. ** (9) The subquery does not use LIMIT or the outer query does not use
  2597. ** aggregates.
  2598. **
  2599. ** (10) The subquery does not use aggregates or the outer query does not
  2600. ** use LIMIT.
  2601. **
  2602. ** (11) The subquery and the outer query do not both have ORDER BY clauses.
  2603. **
  2604. ** (**) Not implemented. Subsumed into restriction (3). Was previously
  2605. ** a separate restriction deriving from ticket #350.
  2606. **
  2607. ** (13) The subquery and outer query do not both use LIMIT.
  2608. **
  2609. ** (14) The subquery does not use OFFSET.
  2610. **
  2611. ** (15) The outer query is not part of a compound select or the
  2612. ** subquery does not have a LIMIT clause.
  2613. ** (See ticket #2339 and ticket [02a8e81d44]).
  2614. **
  2615. ** (16) The outer query is not an aggregate or the subquery does
  2616. ** not contain ORDER BY. (Ticket #2942) This used to not matter
  2617. ** until we introduced the group_concat() function.
  2618. **
  2619. ** (17) The sub-query is not a compound select, or it is a UNION ALL
  2620. ** compound clause made up entirely of non-aggregate queries, and
  2621. ** the parent query:
  2622. **
  2623. ** * is not itself part of a compound select,
  2624. ** * is not an aggregate or DISTINCT query, and
  2625. ** * is not a join
  2626. **
  2627. ** The parent and sub-query may contain WHERE clauses. Subject to
  2628. ** rules (11), (13) and (14), they may also contain ORDER BY,
  2629. ** LIMIT and OFFSET clauses. The subquery cannot use any compound
  2630. ** operator other than UNION ALL because all the other compound
  2631. ** operators have an implied DISTINCT which is disallowed by
  2632. ** restriction (4).
  2633. **
  2634. ** Also, each component of the sub-query must return the same number
  2635. ** of result columns. This is actually a requirement for any compound
  2636. ** SELECT statement, but all the code here does is make sure that no
  2637. ** such (illegal) sub-query is flattened. The caller will detect the
  2638. ** syntax error and return a detailed message.
  2639. **
  2640. ** (18) If the sub-query is a compound select, then all terms of the
  2641. ** ORDER by clause of the parent must be simple references to
  2642. ** columns of the sub-query.
  2643. **
  2644. ** (19) The subquery does not use LIMIT or the outer query does not
  2645. ** have a WHERE clause.
  2646. **
  2647. ** (20) If the sub-query is a compound select, then it must not use
  2648. ** an ORDER BY clause. Ticket #3773. We could relax this constraint
  2649. ** somewhat by saying that the terms of the ORDER BY clause must
  2650. ** appear as unmodified result columns in the outer query. But we
  2651. ** have other optimizations in mind to deal with that case.
  2652. **
  2653. ** (21) The subquery does not use LIMIT or the outer query is not
  2654. ** DISTINCT. (See ticket [752e1646fc]).
  2655. **
  2656. ** In this routine, the "p" parameter is a pointer to the outer query.
  2657. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
  2658. ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
  2659. **
  2660. ** If flattening is not attempted, this routine is a no-op and returns 0.
  2661. ** If flattening is attempted this routine returns 1.
  2662. **
  2663. ** All of the expression analysis must occur on both the outer query and
  2664. ** the subquery before this routine runs.
  2665. */
  2666. static int flattenSubquery(
  2667. Parse *pParse, /* Parsing context */
  2668. Select *p, /* The parent or outer SELECT statement */
  2669. int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
  2670. int isAgg, /* True if outer SELECT uses aggregate functions */
  2671. int subqueryIsAgg /* True if the subquery uses aggregate functions */
  2672. ){
  2673. const char *zSavedAuthContext = pParse->zAuthContext;
  2674. Select *pParent;
  2675. Select *pSub; /* The inner query or "subquery" */
  2676. Select *pSub1; /* Pointer to the rightmost select in sub-query */
  2677. SrcList *pSrc; /* The FROM clause of the outer query */
  2678. SrcList *pSubSrc; /* The FROM clause of the subquery */
  2679. ExprList *pList; /* The result set of the outer query */
  2680. int iParent; /* VDBE cursor number of the pSub result set temp table */
  2681. int i; /* Loop counter */
  2682. Expr *pWhere; /* The WHERE clause */
  2683. struct SrcList_item *pSubitem; /* The subquery */
  2684. sqlite3 *db = pParse->db;
  2685. /* Check to see if flattening is permitted. Return 0 if not.
  2686. */
  2687. assert( p!=0 );
  2688. assert( p->pPrior==0 ); /* Unable to flatten compound queries */
  2689. if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
  2690. pSrc = p->pSrc;
  2691. assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  2692. pSubitem = &pSrc->a[iFrom];
  2693. iParent = pSubitem->iCursor;
  2694. pSub = pSubitem->pSelect;
  2695. assert( pSub!=0 );
  2696. if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */
  2697. if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; /* Restriction (2) */
  2698. pSubSrc = pSub->pSrc;
  2699. assert( pSubSrc );
  2700. /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
  2701. ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
  2702. ** because they could be computed at compile-time. But when LIMIT and OFFSET
  2703. ** became arbitrary expressions, we were forced to add restrictions (13)
  2704. ** and (14). */
  2705. if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */
  2706. if( pSub->pOffset ) return 0; /* Restriction (14) */
  2707. if( p->pRightmost && pSub->pLimit ){
  2708. return 0; /* Restriction (15) */
  2709. }
  2710. if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
  2711. if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (5) */
  2712. if( pSub->pLimit && (pSrc->nSrc>1 || isAgg) ){
  2713. return 0; /* Restrictions (8)(9) */
  2714. }
  2715. if( (p->selFlags & SF_Distinct)!=0 && subqueryIsAgg ){
  2716. return 0; /* Restriction (6) */
  2717. }
  2718. if( p->pOrderBy && pSub->pOrderBy ){
  2719. return 0; /* Restriction (11) */
  2720. }
  2721. if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */
  2722. if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */
  2723. if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){
  2724. return 0; /* Restriction (21) */
  2725. }
  2726. /* OBSOLETE COMMENT 1:
  2727. ** Restriction 3: If the subquery is a join, make sure the subquery is
  2728. ** not used as the right operand of an outer join. Examples of why this
  2729. ** is not allowed:
  2730. **
  2731. ** t1 LEFT OUTER JOIN (t2 JOIN t3)
  2732. **
  2733. ** If we flatten the above, we would get
  2734. **
  2735. ** (t1 LEFT OUTER JOIN t2) JOIN t3
  2736. **
  2737. ** which is not at all the same thing.
  2738. **
  2739. ** OBSOLETE COMMENT 2:
  2740. ** Restriction 12: If the subquery is the right operand of a left outer
  2741. ** join, make sure the subquery has no WHERE clause.
  2742. ** An examples of why this is not allowed:
  2743. **
  2744. ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
  2745. **
  2746. ** If we flatten the above, we would get
  2747. **
  2748. ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
  2749. **
  2750. ** But the t2.x>0 test will always fail on a NULL row of t2, which
  2751. ** effectively converts the OUTER JOIN into an INNER JOIN.
  2752. **
  2753. ** THIS OVERRIDES OBSOLETE COMMENTS 1 AND 2 ABOVE:
  2754. ** Ticket #3300 shows that flattening the right term of a LEFT JOIN
  2755. ** is fraught with danger. Best to avoid the whole thing. If the
  2756. ** subquery is the right term of a LEFT JOIN, then do not flatten.
  2757. */
  2758. if( (pSubitem->jointype & JT_OUTER)!=0 ){
  2759. return 0;
  2760. }
  2761. /* Restriction 17: If the sub-query is a compound SELECT, then it must
  2762. ** use only the UNION ALL operator. And none of the simple select queries
  2763. ** that make up the compound SELECT are allowed to be aggregate or distinct
  2764. ** queries.
  2765. */
  2766. if( pSub->pPrior ){
  2767. if( pSub->pOrderBy ){
  2768. return 0; /* Restriction 20 */
  2769. }
  2770. if( isAgg || (p->selFlags & SF_Distinct)!=0 || pSrc->nSrc!=1 ){
  2771. return 0;
  2772. }
  2773. for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
  2774. testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
  2775. testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
  2776. assert( pSub->pSrc!=0 );
  2777. if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0
  2778. || (pSub1->pPrior && pSub1->op!=TK_ALL)
  2779. || pSub1->pSrc->nSrc<1
  2780. || pSub->pEList->nExpr!=pSub1->pEList->nExpr
  2781. ){
  2782. return 0;
  2783. }
  2784. testcase( pSub1->pSrc->nSrc>1 );
  2785. }
  2786. /* Restriction 18. */
  2787. if( p->pOrderBy ){
  2788. int ii;
  2789. for(ii=0; ii<p->pOrderBy->nExpr; ii++){
  2790. if( p->pOrderBy->a[ii].iOrderByCol==0 ) return 0;
  2791. }
  2792. }
  2793. }
  2794. /***** If we reach this point, flattening is permitted. *****/
  2795. /* Authorize the subquery */
  2796. pParse->zAuthContext = pSubitem->zName;
  2797. TESTONLY(i =) sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0);
  2798. testcase( i==SQLITE_DENY );
  2799. pParse->zAuthContext = zSavedAuthContext;
  2800. /* If the sub-query is a compound SELECT statement, then (by restrictions
  2801. ** 17 and 18 above) it must be a UNION ALL and the parent query must
  2802. ** be of the form:
  2803. **
  2804. ** SELECT <expr-list> FROM (<sub-query>) <where-clause>
  2805. **
  2806. ** followed by any ORDER BY, LIMIT and/or OFFSET clauses. This block
  2807. ** creates N-1 copies of the parent query without any ORDER BY, LIMIT or
  2808. ** OFFSET clauses and joins them to the left-hand-side of the original
  2809. ** using UNION ALL operators. In this case N is the number of simple
  2810. ** select statements in the compound sub-query.
  2811. **
  2812. ** Example:
  2813. **
  2814. ** SELECT a+1 FROM (
  2815. ** SELECT x FROM tab
  2816. ** UNION ALL
  2817. ** SELECT y FROM tab
  2818. ** UNION ALL
  2819. ** SELECT abs(z*2) FROM tab2
  2820. ** ) WHERE a!=5 ORDER BY 1
  2821. **
  2822. ** Transformed into:
  2823. **
  2824. ** SELECT x+1 FROM tab WHERE x+1!=5
  2825. ** UNION ALL
  2826. ** SELECT y+1 FROM tab WHERE y+1!=5
  2827. ** UNION ALL
  2828. ** SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
  2829. ** ORDER BY 1
  2830. **
  2831. ** We call this the "compound-subquery flattening".
  2832. */
  2833. for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
  2834. Select *pNew;
  2835. ExprList *pOrderBy = p->pOrderBy;
  2836. Expr *pLimit = p->pLimit;
  2837. Expr *pOffset = p->pOffset;
  2838. Select *pPrior = p->pPrior;
  2839. p->pOrderBy = 0;
  2840. p->pSrc = 0;
  2841. p->pPrior = 0;
  2842. p->pLimit = 0;
  2843. p->pOffset = 0;
  2844. pNew = sqlite3SelectDup(db, p, 0);
  2845. p->pOffset = pOffset;
  2846. p->pLimit = pLimit;
  2847. p->pOrderBy = pOrderBy;
  2848. p->pSrc = pSrc;
  2849. p->op = TK_ALL;
  2850. p->pRightmost = 0;
  2851. if( pNew==0 ){
  2852. pNew = pPrior;
  2853. }else{
  2854. pNew->pPrior = pPrior;
  2855. pNew->pRightmost = 0;
  2856. }
  2857. p->pPrior = pNew;
  2858. if( db->mallocFailed ) return 1;
  2859. }
  2860. /* Begin flattening the iFrom-th entry of the FROM clause
  2861. ** in the outer query.
  2862. */
  2863. pSub = pSub1 = pSubitem->pSelect;
  2864. /* Delete the transient table structure associated with the
  2865. ** subquery
  2866. */
  2867. sqlite3DbFree(db, pSubitem->zDatabase);
  2868. sqlite3DbFree(db, pSubitem->zName);
  2869. sqlite3DbFree(db, pSubitem->zAlias);
  2870. pSubitem->zDatabase = 0;
  2871. pSubitem->zName = 0;
  2872. pSubitem->zAlias = 0;
  2873. pSubitem->pSelect = 0;
  2874. /* Defer deleting the Table object associated with the
  2875. ** subquery until code generation is
  2876. ** complete, since there may still exist Expr.pTab entries that
  2877. ** refer to the subquery even after flattening. Ticket #3346.
  2878. **
  2879. ** pSubitem->pTab is always non-NULL by test restrictions and tests above.
  2880. */
  2881. if( ALWAYS(pSubitem->pTab!=0) ){
  2882. Table *pTabToDel = pSubitem->pTab;
  2883. if( pTabToDel->nRef==1 ){
  2884. Parse *pToplevel = sqlite3ParseToplevel(pParse);
  2885. pTabToDel->pNextZombie = pToplevel->pZombieTab;
  2886. pToplevel->pZombieTab = pTabToDel;
  2887. }else{
  2888. pTabToDel->nRef--;
  2889. }
  2890. pSubitem->pTab = 0;
  2891. }
  2892. /* The following loop runs once for each term in a compound-subquery
  2893. ** flattening (as described above). If we are doing a different kind
  2894. ** of flattening - a flattening other than a compound-subquery flattening -
  2895. ** then this loop only runs once.
  2896. **
  2897. ** This loop moves all of the FROM elements of the subquery into the
  2898. ** the FROM clause of the outer query. Before doing this, remember
  2899. ** the cursor number for the original outer query FROM element in
  2900. ** iParent. The iParent cursor will never be used. Subsequent code
  2901. ** will scan expressions looking for iParent references and replace
  2902. ** those references with expressions that resolve to the subquery FROM
  2903. ** elements we are now copying in.
  2904. */
  2905. for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){
  2906. int nSubSrc;
  2907. u8 jointype = 0;
  2908. pSubSrc = pSub->pSrc; /* FROM clause of subquery */
  2909. nSubSrc = pSubSrc->nSrc; /* Number of terms in subquery FROM clause */
  2910. pSrc = pParent->pSrc; /* FROM clause of the outer query */
  2911. if( pSrc ){
  2912. assert( pParent==p ); /* First time through the loop */
  2913. jointype = pSubitem->jointype;
  2914. }else{
  2915. assert( pParent!=p ); /* 2nd and subsequent times through the loop */
  2916. pSrc = pParent->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
  2917. if( pSrc==0 ){
  2918. assert( db->mallocFailed );
  2919. break;
  2920. }
  2921. }
  2922. /* The subquery uses a single slot of the FROM clause of the outer
  2923. ** query. If the subquery has more than one element in its FROM clause,
  2924. ** then expand the outer query to make space for it to hold all elements
  2925. ** of the subquery.
  2926. **
  2927. ** Example:
  2928. **
  2929. ** SELECT * FROM tabA, (SELECT * FROM sub1, sub2), tabB;
  2930. **
  2931. ** The outer query has 3 slots in its FROM clause. One slot of the
  2932. ** outer query (the middle slot) is used by the subquery. The next
  2933. ** block of code will expand the out query to 4 slots. The middle
  2934. ** slot is expanded to two slots in order to make space for the
  2935. ** two elements in the FROM clause of the subquery.
  2936. */
  2937. if( nSubSrc>1 ){
  2938. pParent->pSrc = pSrc = sqlite3SrcListEnlarge(db, pSrc, nSubSrc-1,iFrom+1);
  2939. if( db->mallocFailed ){
  2940. break;
  2941. }
  2942. }
  2943. /* Transfer the FROM clause terms from the subquery into the
  2944. ** outer query.
  2945. */
  2946. for(i=0; i<nSubSrc; i++){
  2947. sqlite3IdListDelete(db, pSrc->a[i+iFrom].pUsing);
  2948. pSrc->a[i+iFrom] = pSubSrc->a[i];
  2949. memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
  2950. }
  2951. pSrc->a[iFrom].jointype = jointype;
  2952. /* Now begin substituting subquery result set expressions for
  2953. ** references to the iParent in the outer query.
  2954. **
  2955. ** Example:
  2956. **
  2957. ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
  2958. ** \ \_____________ subquery __________/ /
  2959. ** \_____________________ outer query ______________________________/
  2960. **
  2961. ** We look at every expression in the outer query and every place we see
  2962. ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
  2963. */
  2964. pList = pParent->pEList;
  2965. for(i=0; i<pList->nExpr; i++){
  2966. if( pList->a[i].zName==0 ){
  2967. char *zName = sqlite3DbStrDup(db, pList->a[i].zSpan);
  2968. sqlite3Dequote(zName);
  2969. pList->a[i].zName = zName;
  2970. }
  2971. }
  2972. substExprList(db, pParent->pEList, iParent, pSub->pEList);
  2973. if( isAgg ){
  2974. substExprList(db, pParent->pGroupBy, iParent, pSub->pEList);
  2975. pParent->pHaving = substExpr(db, pParent->pHaving, iParent, pSub->pEList);
  2976. }
  2977. if( pSub->pOrderBy ){
  2978. assert( pParent->pOrderBy==0 );
  2979. pParent->pOrderBy = pSub->pOrderBy;
  2980. pSub->pOrderBy = 0;
  2981. }else if( pParent->pOrderBy ){
  2982. substExprList(db, pParent->pOrderBy, iParent, pSub->pEList);
  2983. }
  2984. if( pSub->pWhere ){
  2985. pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
  2986. }else{
  2987. pWhere = 0;
  2988. }
  2989. if( subqueryIsAgg ){
  2990. assert( pParent->pHaving==0 );
  2991. pParent->pHaving = pParent->pWhere;
  2992. pParent->pWhere = pWhere;
  2993. pParent->pHaving = substExpr(db, pParent->pHaving, iParent, pSub->pEList);
  2994. pParent->pHaving = sqlite3ExprAnd(db, pParent->pHaving,
  2995. sqlite3ExprDup(db, pSub->pHaving, 0));
  2996. assert( pParent->pGroupBy==0 );
  2997. pParent->pGroupBy = sqlite3ExprListDup(db, pSub->pGroupBy, 0);
  2998. }else{
  2999. pParent->pWhere = substExpr(db, pParent->pWhere, iParent, pSub->pEList);
  3000. pParent->pWhere = sqlite3ExprAnd(db, pParent->pWhere, pWhere);
  3001. }
  3002. /* The flattened query is distinct if either the inner or the
  3003. ** outer query is distinct.
  3004. */
  3005. pParent->selFlags |= pSub->selFlags & SF_Distinct;
  3006. /*
  3007. ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
  3008. **
  3009. ** One is tempted to try to add a and b to combine the limits. But this
  3010. ** does not work if either limit is negative.
  3011. */
  3012. if( pSub->pLimit ){
  3013. pParent->pLimit = pSub->pLimit;
  3014. pSub->pLimit = 0;
  3015. }
  3016. }
  3017. /* Finially, delete what is left of the subquery and return
  3018. ** success.
  3019. */
  3020. sqlite3SelectDelete(db, pSub1);
  3021. return 1;
  3022. }
  3023. #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  3024. /*
  3025. ** Based on the contents of the AggInfo structure indicated by the first
  3026. ** argument, this function checks if the following are true:
  3027. **
  3028. ** * the query contains just a single aggregate function,
  3029. ** * the aggregate function is either min() or max(), and
  3030. ** * the argument to the aggregate function is a column value.
  3031. **
  3032. ** If all of the above are true, then WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX
  3033. ** is returned as appropriate. Also, *ppMinMax is set to point to the
  3034. ** list of arguments passed to the aggregate before returning.
  3035. **
  3036. ** Or, if the conditions above are not met, *ppMinMax is set to 0 and
  3037. ** WHERE_ORDERBY_NORMAL is returned.
  3038. */
  3039. static u8 minMaxQuery(AggInfo *pAggInfo, ExprList **ppMinMax){
  3040. int eRet = WHERE_ORDERBY_NORMAL; /* Return value */
  3041. *ppMinMax = 0;
  3042. if( pAggInfo->nFunc==1 ){
  3043. Expr *pExpr = pAggInfo->aFunc[0].pExpr; /* Aggregate function */
  3044. ExprList *pEList = pExpr->x.pList; /* Arguments to agg function */
  3045. assert( pExpr->op==TK_AGG_FUNCTION );
  3046. if( pEList && pEList->nExpr==1 && pEList->a[0].pExpr->op==TK_AGG_COLUMN ){
  3047. const char *zFunc = pExpr->u.zToken;
  3048. if( sqlite3StrICmp(zFunc, "min")==0 ){
  3049. eRet = WHERE_ORDERBY_MIN;
  3050. *ppMinMax = pEList;
  3051. }else if( sqlite3StrICmp(zFunc, "max")==0 ){
  3052. eRet = WHERE_ORDERBY_MAX;
  3053. *ppMinMax = pEList;
  3054. }
  3055. }
  3056. }
  3057. assert( *ppMinMax==0 || (*ppMinMax)->nExpr==1 );
  3058. return eRet;
  3059. }
  3060. /*
  3061. ** The select statement passed as the first argument is an aggregate query.
  3062. ** The second argment is the associated aggregate-info object. This
  3063. ** function tests if the SELECT is of the form:
  3064. **
  3065. ** SELECT count(*) FROM <tbl>
  3066. **
  3067. ** where table is a database table, not a sub-select or view. If the query
  3068. ** does match this pattern, then a pointer to the Table object representing
  3069. ** <tbl> is returned. Otherwise, 0 is returned.
  3070. */
  3071. static Table *isSimpleCount(Select *p, AggInfo *pAggInfo){
  3072. Table *pTab;
  3073. Expr *pExpr;
  3074. assert( !p->pGroupBy );
  3075. if( p->pWhere || p->pEList->nExpr!=1
  3076. || p->pSrc->nSrc!=1 || p->pSrc->a[0].pSelect
  3077. ){
  3078. return 0;
  3079. }
  3080. pTab = p->pSrc->a[0].pTab;
  3081. pExpr = p->pEList->a[0].pExpr;
  3082. assert( pTab && !pTab->pSelect && pExpr );
  3083. if( IsVirtual(pTab) ) return 0;
  3084. if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  3085. if( NEVER(pAggInfo->nFunc==0) ) return 0;
  3086. if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0;
  3087. if( pExpr->flags&EP_Distinct ) return 0;
  3088. return pTab;
  3089. }
  3090. /*
  3091. ** If the source-list item passed as an argument was augmented with an
  3092. ** INDEXED BY clause, then try to locate the specified index. If there
  3093. ** was such a clause and the named index cannot be found, return
  3094. ** SQLITE_ERROR and leave an error in pParse. Otherwise, populate
  3095. ** pFrom->pIndex and return SQLITE_OK.
  3096. */
  3097. int sqlite3IndexedByLookup(Parse *pParse, struct SrcList_item *pFrom){
  3098. if( pFrom->pTab && pFrom->zIndex ){
  3099. Table *pTab = pFrom->pTab;
  3100. char *zIndex = pFrom->zIndex;
  3101. Index *pIdx;
  3102. for(pIdx=pTab->pIndex;
  3103. pIdx && sqlite3StrICmp(pIdx->zName, zIndex);
  3104. pIdx=pIdx->pNext
  3105. );
  3106. if( !pIdx ){
  3107. sqlite3ErrorMsg(pParse, "no such index: %s", zIndex, 0);
  3108. pParse->checkSchema = 1;
  3109. return SQLITE_ERROR;
  3110. }
  3111. pFrom->pIndex = pIdx;
  3112. }
  3113. return SQLITE_OK;
  3114. }
  3115. /*
  3116. ** Detect compound SELECT statements that use an ORDER BY clause with
  3117. ** an alternative collating sequence.
  3118. **
  3119. ** SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ...
  3120. **
  3121. ** These are rewritten as a subquery:
  3122. **
  3123. ** SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2)
  3124. ** ORDER BY ... COLLATE ...
  3125. **
  3126. ** This transformation is necessary because the multiSelectOrderBy() routine
  3127. ** above that generates the code for a compound SELECT with an ORDER BY clause
  3128. ** uses a merge algorithm that requires the same collating sequence on the
  3129. ** result columns as on the ORDER BY clause. See ticket
  3130. ** http://www.sqlite.org/src/info/6709574d2a
  3131. **
  3132. ** This transformation is only needed for EXCEPT, INTERSECT, and UNION.
  3133. ** The UNION ALL operator works fine with multiSelectOrderBy() even when
  3134. ** there are COLLATE terms in the ORDER BY.
  3135. */
  3136. static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){
  3137. int i;
  3138. Select *pNew;
  3139. Select *pX;
  3140. sqlite3 *db;
  3141. struct ExprList_item *a;
  3142. SrcList *pNewSrc;
  3143. Parse *pParse;
  3144. Token dummy;
  3145. if( p->pPrior==0 ) return WRC_Continue;
  3146. if( p->pOrderBy==0 ) return WRC_Continue;
  3147. for(pX=p; pX && (pX->op==TK_ALL || pX->op==TK_SELECT); pX=pX->pPrior){}
  3148. if( pX==0 ) return WRC_Continue;
  3149. a = p->pOrderBy->a;
  3150. for(i=p->pOrderBy->nExpr-1; i>=0; i--){
  3151. if( a[i].pExpr->flags & EP_Collate ) break;
  3152. }
  3153. if( i<0 ) return WRC_Continue;
  3154. /* If we reach this point, that means the transformation is required. */
  3155. pParse = pWalker->pParse;
  3156. db = pParse->db;
  3157. pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
  3158. if( pNew==0 ) return WRC_Abort;
  3159. memset(&dummy, 0, sizeof(dummy));
  3160. pNewSrc = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&dummy,pNew,0,0);
  3161. if( pNewSrc==0 ) return WRC_Abort;
  3162. *pNew = *p;
  3163. p->pSrc = pNewSrc;
  3164. p->pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ALL, 0));
  3165. p->op = TK_SELECT;
  3166. p->pWhere = 0;
  3167. pNew->pGroupBy = 0;
  3168. pNew->pHaving = 0;
  3169. pNew->pOrderBy = 0;
  3170. p->pPrior = 0;
  3171. pNew->pLimit = 0;
  3172. pNew->pOffset = 0;
  3173. return WRC_Continue;
  3174. }
  3175. /*
  3176. ** This routine is a Walker callback for "expanding" a SELECT statement.
  3177. ** "Expanding" means to do the following:
  3178. **
  3179. ** (1) Make sure VDBE cursor numbers have been assigned to every
  3180. ** element of the FROM clause.
  3181. **
  3182. ** (2) Fill in the pTabList->a[].pTab fields in the SrcList that
  3183. ** defines FROM clause. When views appear in the FROM clause,
  3184. ** fill pTabList->a[].pSelect with a copy of the SELECT statement
  3185. ** that implements the view. A copy is made of the view's SELECT
  3186. ** statement so that we can freely modify or delete that statement
  3187. ** without worrying about messing up the presistent representation
  3188. ** of the view.
  3189. **
  3190. ** (3) Add terms to the WHERE clause to accomodate the NATURAL keyword
  3191. ** on joins and the ON and USING clause of joins.
  3192. **
  3193. ** (4) Scan the list of columns in the result set (pEList) looking
  3194. ** for instances of the "*" operator or the TABLE.* operator.
  3195. ** If found, expand each "*" to be every column in every table
  3196. ** and TABLE.* to be every column in TABLE.
  3197. **
  3198. */
  3199. static int selectExpander(Walker *pWalker, Select *p){
  3200. Parse *pParse = pWalker->pParse;
  3201. int i, j, k;
  3202. SrcList *pTabList;
  3203. ExprList *pEList;
  3204. struct SrcList_item *pFrom;
  3205. sqlite3 *db = pParse->db;
  3206. Expr *pE, *pRight, *pExpr;
  3207. u16 selFlags = p->selFlags;
  3208. p->selFlags |= SF_Expanded;
  3209. if( db->mallocFailed ){
  3210. return WRC_Abort;
  3211. }
  3212. if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){
  3213. return WRC_Prune;
  3214. }
  3215. pTabList = p->pSrc;
  3216. pEList = p->pEList;
  3217. /* Make sure cursor numbers have been assigned to all entries in
  3218. ** the FROM clause of the SELECT statement.
  3219. */
  3220. sqlite3SrcListAssignCursors(pParse, pTabList);
  3221. /* Look up every table named in the FROM clause of the select. If
  3222. ** an entry of the FROM clause is a subquery instead of a table or view,
  3223. ** then create a transient table structure to describe the subquery.
  3224. */
  3225. for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3226. Table *pTab;
  3227. if( pFrom->pTab!=0 ){
  3228. /* This statement has already been prepared. There is no need
  3229. ** to go further. */
  3230. assert( i==0 );
  3231. return WRC_Prune;
  3232. }
  3233. if( pFrom->zName==0 ){
  3234. #ifndef SQLITE_OMIT_SUBQUERY
  3235. Select *pSel = pFrom->pSelect;
  3236. /* A sub-query in the FROM clause of a SELECT */
  3237. assert( pSel!=0 );
  3238. assert( pFrom->pTab==0 );
  3239. sqlite3WalkSelect(pWalker, pSel);
  3240. pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
  3241. if( pTab==0 ) return WRC_Abort;
  3242. pTab->nRef = 1;
  3243. pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
  3244. while( pSel->pPrior ){ pSel = pSel->pPrior; }
  3245. selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
  3246. pTab->iPKey = -1;
  3247. pTab->nRowEst = 1048576;
  3248. pTab->tabFlags |= TF_Ephemeral;
  3249. #endif
  3250. }else{
  3251. /* An ordinary table or view name in the FROM clause */
  3252. assert( pFrom->pTab==0 );
  3253. pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom);
  3254. if( pTab==0 ) return WRC_Abort;
  3255. if( pTab->nRef==0xffff ){
  3256. sqlite3ErrorMsg(pParse, "too many references to \"%s\": max 65535",
  3257. pTab->zName);
  3258. pFrom->pTab = 0;
  3259. return WRC_Abort;
  3260. }
  3261. pTab->nRef++;
  3262. #if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE)
  3263. if( pTab->pSelect || IsVirtual(pTab) ){
  3264. /* We reach here if the named table is a really a view */
  3265. if( sqlite3ViewGetColumnNames(pParse, pTab) ) return WRC_Abort;
  3266. assert( pFrom->pSelect==0 );
  3267. pFrom->pSelect = sqlite3SelectDup(db, pTab->pSelect, 0);
  3268. sqlite3WalkSelect(pWalker, pFrom->pSelect);
  3269. }
  3270. #endif
  3271. }
  3272. /* Locate the index named by the INDEXED BY clause, if any. */
  3273. if( sqlite3IndexedByLookup(pParse, pFrom) ){
  3274. return WRC_Abort;
  3275. }
  3276. }
  3277. /* Process NATURAL keywords, and ON and USING clauses of joins.
  3278. */
  3279. if( db->mallocFailed || sqliteProcessJoin(pParse, p) ){
  3280. return WRC_Abort;
  3281. }
  3282. /* For every "*" that occurs in the column list, insert the names of
  3283. ** all columns in all tables. And for every TABLE.* insert the names
  3284. ** of all columns in TABLE. The parser inserted a special expression
  3285. ** with the TK_ALL operator for each "*" that it found in the column list.
  3286. ** The following code just has to locate the TK_ALL expressions and expand
  3287. ** each one to the list of all columns in all tables.
  3288. **
  3289. ** The first loop just checks to see if there are any "*" operators
  3290. ** that need expanding.
  3291. */
  3292. for(k=0; k<pEList->nExpr; k++){
  3293. pE = pEList->a[k].pExpr;
  3294. if( pE->op==TK_ALL ) break;
  3295. assert( pE->op!=TK_DOT || pE->pRight!=0 );
  3296. assert( pE->op!=TK_DOT || (pE->pLeft!=0 && pE->pLeft->op==TK_ID) );
  3297. if( pE->op==TK_DOT && pE->pRight->op==TK_ALL ) break;
  3298. }
  3299. if( k<pEList->nExpr ){
  3300. /*
  3301. ** If we get here it means the result set contains one or more "*"
  3302. ** operators that need to be expanded. Loop through each expression
  3303. ** in the result set and expand them one by one.
  3304. */
  3305. struct ExprList_item *a = pEList->a;
  3306. ExprList *pNew = 0;
  3307. int flags = pParse->db->flags;
  3308. int longNames = (flags & SQLITE_FullColNames)!=0
  3309. && (flags & SQLITE_ShortColNames)==0;
  3310. /* When processing FROM-clause subqueries, it is always the case
  3311. ** that full_column_names=OFF and short_column_names=ON. The
  3312. ** sqlite3ResultSetOfSelect() routine makes it so. */
  3313. assert( (p->selFlags & SF_NestedFrom)==0
  3314. || ((flags & SQLITE_FullColNames)==0 &&
  3315. (flags & SQLITE_ShortColNames)!=0) );
  3316. for(k=0; k<pEList->nExpr; k++){
  3317. pE = a[k].pExpr;
  3318. pRight = pE->pRight;
  3319. assert( pE->op!=TK_DOT || pRight!=0 );
  3320. if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pRight->op!=TK_ALL) ){
  3321. /* This particular expression does not need to be expanded.
  3322. */
  3323. pNew = sqlite3ExprListAppend(pParse, pNew, a[k].pExpr);
  3324. if( pNew ){
  3325. pNew->a[pNew->nExpr-1].zName = a[k].zName;
  3326. pNew->a[pNew->nExpr-1].zSpan = a[k].zSpan;
  3327. a[k].zName = 0;
  3328. a[k].zSpan = 0;
  3329. }
  3330. a[k].pExpr = 0;
  3331. }else{
  3332. /* This expression is a "*" or a "TABLE.*" and needs to be
  3333. ** expanded. */
  3334. int tableSeen = 0; /* Set to 1 when TABLE matches */
  3335. char *zTName = 0; /* text of name of TABLE */
  3336. if( pE->op==TK_DOT ){
  3337. assert( pE->pLeft!=0 );
  3338. assert( !ExprHasProperty(pE->pLeft, EP_IntValue) );
  3339. zTName = pE->pLeft->u.zToken;
  3340. }
  3341. for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3342. Table *pTab = pFrom->pTab;
  3343. Select *pSub = pFrom->pSelect;
  3344. char *zTabName = pFrom->zAlias;
  3345. const char *zSchemaName = 0;
  3346. int iDb;
  3347. if( zTabName==0 ){
  3348. zTabName = pTab->zName;
  3349. }
  3350. if( db->mallocFailed ) break;
  3351. if( pSub==0 || (pSub->selFlags & SF_NestedFrom)==0 ){
  3352. pSub = 0;
  3353. if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
  3354. continue;
  3355. }
  3356. iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  3357. zSchemaName = iDb>=0 ? db->aDb[iDb].zName : "*";
  3358. }
  3359. for(j=0; j<pTab->nCol; j++){
  3360. char *zName = pTab->aCol[j].zName;
  3361. char *zColname; /* The computed column name */
  3362. char *zToFree; /* Malloced string that needs to be freed */
  3363. Token sColname; /* Computed column name as a token */
  3364. assert( zName );
  3365. if( zTName && pSub
  3366. && sqlite3MatchSpanName(pSub->pEList->a[j].zSpan, 0, zTName, 0)==0
  3367. ){
  3368. continue;
  3369. }
  3370. /* If a column is marked as 'hidden' (currently only possible
  3371. ** for virtual tables), do not include it in the expanded
  3372. ** result-set list.
  3373. */
  3374. if( IsHiddenColumn(&pTab->aCol[j]) ){
  3375. assert(IsVirtual(pTab));
  3376. continue;
  3377. }
  3378. tableSeen = 1;
  3379. if( i>0 && zTName==0 ){
  3380. if( (pFrom->jointype & JT_NATURAL)!=0
  3381. && tableAndColumnIndex(pTabList, i, zName, 0, 0)
  3382. ){
  3383. /* In a NATURAL join, omit the join columns from the
  3384. ** table to the right of the join */
  3385. continue;
  3386. }
  3387. if( sqlite3IdListIndex(pFrom->pUsing, zName)>=0 ){
  3388. /* In a join with a USING clause, omit columns in the
  3389. ** using clause from the table on the right. */
  3390. continue;
  3391. }
  3392. }
  3393. pRight = sqlite3Expr(db, TK_ID, zName);
  3394. zColname = zName;
  3395. zToFree = 0;
  3396. if( longNames || pTabList->nSrc>1 ){
  3397. Expr *pLeft;
  3398. pLeft = sqlite3Expr(db, TK_ID, zTabName);
  3399. pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pRight, 0);
  3400. if( zSchemaName ){
  3401. pLeft = sqlite3Expr(db, TK_ID, zSchemaName);
  3402. pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pExpr, 0);
  3403. }
  3404. if( longNames ){
  3405. zColname = sqlite3MPrintf(db, "%s.%s", zTabName, zName);
  3406. zToFree = zColname;
  3407. }
  3408. }else{
  3409. pExpr = pRight;
  3410. }
  3411. pNew = sqlite3ExprListAppend(pParse, pNew, pExpr);
  3412. sColname.z = zColname;
  3413. sColname.n = sqlite3Strlen30(zColname);
  3414. sqlite3ExprListSetName(pParse, pNew, &sColname, 0);
  3415. if( pNew && (p->selFlags & SF_NestedFrom)!=0 ){
  3416. struct ExprList_item *pX = &pNew->a[pNew->nExpr-1];
  3417. if( pSub ){
  3418. pX->zSpan = sqlite3DbStrDup(db, pSub->pEList->a[j].zSpan);
  3419. testcase( pX->zSpan==0 );
  3420. }else{
  3421. pX->zSpan = sqlite3MPrintf(db, "%s.%s.%s",
  3422. zSchemaName, zTabName, zColname);
  3423. testcase( pX->zSpan==0 );
  3424. }
  3425. pX->bSpanIsTab = 1;
  3426. }
  3427. sqlite3DbFree(db, zToFree);
  3428. }
  3429. }
  3430. if( !tableSeen ){
  3431. if( zTName ){
  3432. sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
  3433. }else{
  3434. sqlite3ErrorMsg(pParse, "no tables specified");
  3435. }
  3436. }
  3437. }
  3438. }
  3439. sqlite3ExprListDelete(db, pEList);
  3440. p->pEList = pNew;
  3441. }
  3442. #if SQLITE_MAX_COLUMN
  3443. if( p->pEList && p->pEList->nExpr>db->aLimit[SQLITE_LIMIT_COLUMN] ){
  3444. sqlite3ErrorMsg(pParse, "too many columns in result set");
  3445. }
  3446. #endif
  3447. return WRC_Continue;
  3448. }
  3449. /*
  3450. ** No-op routine for the parse-tree walker.
  3451. **
  3452. ** When this routine is the Walker.xExprCallback then expression trees
  3453. ** are walked without any actions being taken at each node. Presumably,
  3454. ** when this routine is used for Walker.xExprCallback then
  3455. ** Walker.xSelectCallback is set to do something useful for every
  3456. ** subquery in the parser tree.
  3457. */
  3458. static int exprWalkNoop(Walker *NotUsed, Expr *NotUsed2){
  3459. UNUSED_PARAMETER2(NotUsed, NotUsed2);
  3460. return WRC_Continue;
  3461. }
  3462. /*
  3463. ** This routine "expands" a SELECT statement and all of its subqueries.
  3464. ** For additional information on what it means to "expand" a SELECT
  3465. ** statement, see the comment on the selectExpand worker callback above.
  3466. **
  3467. ** Expanding a SELECT statement is the first step in processing a
  3468. ** SELECT statement. The SELECT statement must be expanded before
  3469. ** name resolution is performed.
  3470. **
  3471. ** If anything goes wrong, an error message is written into pParse.
  3472. ** The calling function can detect the problem by looking at pParse->nErr
  3473. ** and/or pParse->db->mallocFailed.
  3474. */
  3475. static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){
  3476. Walker w;
  3477. memset(&w, 0, sizeof(w));
  3478. w.xExprCallback = exprWalkNoop;
  3479. w.pParse = pParse;
  3480. if( pParse->hasCompound ){
  3481. w.xSelectCallback = convertCompoundSelectToSubquery;
  3482. sqlite3WalkSelect(&w, pSelect);
  3483. }
  3484. w.xSelectCallback = selectExpander;
  3485. sqlite3WalkSelect(&w, pSelect);
  3486. }
  3487. #ifndef SQLITE_OMIT_SUBQUERY
  3488. /*
  3489. ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
  3490. ** interface.
  3491. **
  3492. ** For each FROM-clause subquery, add Column.zType and Column.zColl
  3493. ** information to the Table structure that represents the result set
  3494. ** of that subquery.
  3495. **
  3496. ** The Table structure that represents the result set was constructed
  3497. ** by selectExpander() but the type and collation information was omitted
  3498. ** at that point because identifiers had not yet been resolved. This
  3499. ** routine is called after identifier resolution.
  3500. */
  3501. static int selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
  3502. Parse *pParse;
  3503. int i;
  3504. SrcList *pTabList;
  3505. struct SrcList_item *pFrom;
  3506. assert( p->selFlags & SF_Resolved );
  3507. if( (p->selFlags & SF_HasTypeInfo)==0 ){
  3508. p->selFlags |= SF_HasTypeInfo;
  3509. pParse = pWalker->pParse;
  3510. pTabList = p->pSrc;
  3511. for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3512. Table *pTab = pFrom->pTab;
  3513. if( ALWAYS(pTab!=0) && (pTab->tabFlags & TF_Ephemeral)!=0 ){
  3514. /* A sub-query in the FROM clause of a SELECT */
  3515. Select *pSel = pFrom->pSelect;
  3516. assert( pSel );
  3517. while( pSel->pPrior ) pSel = pSel->pPrior;
  3518. selectAddColumnTypeAndCollation(pParse, pTab, pSel);
  3519. }
  3520. }
  3521. }
  3522. return WRC_Continue;
  3523. }
  3524. #endif
  3525. /*
  3526. ** This routine adds datatype and collating sequence information to
  3527. ** the Table structures of all FROM-clause subqueries in a
  3528. ** SELECT statement.
  3529. **
  3530. ** Use this routine after name resolution.
  3531. */
  3532. static void sqlite3SelectAddTypeInfo(Parse *pParse, Select *pSelect){
  3533. #ifndef SQLITE_OMIT_SUBQUERY
  3534. Walker w;
  3535. memset(&w, 0, sizeof(w));
  3536. w.xSelectCallback = selectAddSubqueryTypeInfo;
  3537. w.xExprCallback = exprWalkNoop;
  3538. w.pParse = pParse;
  3539. w.bSelectDepthFirst = 1;
  3540. sqlite3WalkSelect(&w, pSelect);
  3541. #endif
  3542. }
  3543. /*
  3544. ** This routine sets up a SELECT statement for processing. The
  3545. ** following is accomplished:
  3546. **
  3547. ** * VDBE Cursor numbers are assigned to all FROM-clause terms.
  3548. ** * Ephemeral Table objects are created for all FROM-clause subqueries.
  3549. ** * ON and USING clauses are shifted into WHERE statements
  3550. ** * Wildcards "*" and "TABLE.*" in result sets are expanded.
  3551. ** * Identifiers in expression are matched to tables.
  3552. **
  3553. ** This routine acts recursively on all subqueries within the SELECT.
  3554. */
  3555. void sqlite3SelectPrep(
  3556. Parse *pParse, /* The parser context */
  3557. Select *p, /* The SELECT statement being coded. */
  3558. NameContext *pOuterNC /* Name context for container */
  3559. ){
  3560. sqlite3 *db;
  3561. if( NEVER(p==0) ) return;
  3562. db = pParse->db;
  3563. if( db->mallocFailed ) return;
  3564. if( p->selFlags & SF_HasTypeInfo ) return;
  3565. sqlite3SelectExpand(pParse, p);
  3566. if( pParse->nErr || db->mallocFailed ) return;
  3567. sqlite3ResolveSelectNames(pParse, p, pOuterNC);
  3568. if( pParse->nErr || db->mallocFailed ) return;
  3569. sqlite3SelectAddTypeInfo(pParse, p);
  3570. }
  3571. /*
  3572. ** Reset the aggregate accumulator.
  3573. **
  3574. ** The aggregate accumulator is a set of memory cells that hold
  3575. ** intermediate results while calculating an aggregate. This
  3576. ** routine generates code that stores NULLs in all of those memory
  3577. ** cells.
  3578. */
  3579. static void resetAccumulator(Parse *pParse, AggInfo *pAggInfo){
  3580. Vdbe *v = pParse->pVdbe;
  3581. int i;
  3582. struct AggInfo_func *pFunc;
  3583. if( pAggInfo->nFunc+pAggInfo->nColumn==0 ){
  3584. return;
  3585. }
  3586. for(i=0; i<pAggInfo->nColumn; i++){
  3587. sqlite3VdbeAddOp2(v, OP_Null, 0, pAggInfo->aCol[i].iMem);
  3588. }
  3589. for(pFunc=pAggInfo->aFunc, i=0; i<pAggInfo->nFunc; i++, pFunc++){
  3590. sqlite3VdbeAddOp2(v, OP_Null, 0, pFunc->iMem);
  3591. if( pFunc->iDistinct>=0 ){
  3592. Expr *pE = pFunc->pExpr;
  3593. assert( !ExprHasProperty(pE, EP_xIsSelect) );
  3594. if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
  3595. sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
  3596. "argument");
  3597. pFunc->iDistinct = -1;
  3598. }else{
  3599. KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList);
  3600. sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
  3601. (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  3602. }
  3603. }
  3604. }
  3605. }
  3606. /*
  3607. ** Invoke the OP_AggFinalize opcode for every aggregate function
  3608. ** in the AggInfo structure.
  3609. */
  3610. static void finalizeAggFunctions(Parse *pParse, AggInfo *pAggInfo){
  3611. Vdbe *v = pParse->pVdbe;
  3612. int i;
  3613. struct AggInfo_func *pF;
  3614. for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
  3615. ExprList *pList = pF->pExpr->x.pList;
  3616. assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
  3617. sqlite3VdbeAddOp4(v, OP_AggFinal, pF->iMem, pList ? pList->nExpr : 0, 0,
  3618. (void*)pF->pFunc, P4_FUNCDEF);
  3619. }
  3620. }
  3621. /*
  3622. ** Update the accumulator memory cells for an aggregate based on
  3623. ** the current cursor position.
  3624. */
  3625. static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){
  3626. Vdbe *v = pParse->pVdbe;
  3627. int i;
  3628. int regHit = 0;
  3629. int addrHitTest = 0;
  3630. struct AggInfo_func *pF;
  3631. struct AggInfo_col *pC;
  3632. pAggInfo->directMode = 1;
  3633. sqlite3ExprCacheClear(pParse);
  3634. for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
  3635. int nArg;
  3636. int addrNext = 0;
  3637. int regAgg;
  3638. ExprList *pList = pF->pExpr->x.pList;
  3639. assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
  3640. if( pList ){
  3641. nArg = pList->nExpr;
  3642. regAgg = sqlite3GetTempRange(pParse, nArg);
  3643. sqlite3ExprCodeExprList(pParse, pList, regAgg, 1);
  3644. }else{
  3645. nArg = 0;
  3646. regAgg = 0;
  3647. }
  3648. if( pF->iDistinct>=0 ){
  3649. addrNext = sqlite3VdbeMakeLabel(v);
  3650. assert( nArg==1 );
  3651. codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
  3652. }
  3653. if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  3654. CollSeq *pColl = 0;
  3655. struct ExprList_item *pItem;
  3656. int j;
  3657. assert( pList!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */
  3658. for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){
  3659. pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
  3660. }
  3661. if( !pColl ){
  3662. pColl = pParse->db->pDfltColl;
  3663. }
  3664. if( regHit==0 && pAggInfo->nAccumulator ) regHit = ++pParse->nMem;
  3665. sqlite3VdbeAddOp4(v, OP_CollSeq, regHit, 0, 0, (char *)pColl, P4_COLLSEQ);
  3666. }
  3667. sqlite3VdbeAddOp4(v, OP_AggStep, 0, regAgg, pF->iMem,
  3668. (void*)pF->pFunc, P4_FUNCDEF);
  3669. sqlite3VdbeChangeP5(v, (u8)nArg);
  3670. sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg);
  3671. sqlite3ReleaseTempRange(pParse, regAgg, nArg);
  3672. if( addrNext ){
  3673. sqlite3VdbeResolveLabel(v, addrNext);
  3674. sqlite3ExprCacheClear(pParse);
  3675. }
  3676. }
  3677. /* Before populating the accumulator registers, clear the column cache.
  3678. ** Otherwise, if any of the required column values are already present
  3679. ** in registers, sqlite3ExprCode() may use OP_SCopy to copy the value
  3680. ** to pC->iMem. But by the time the value is used, the original register
  3681. ** may have been used, invalidating the underlying buffer holding the
  3682. ** text or blob value. See ticket [883034dcb5].
  3683. **
  3684. ** Another solution would be to change the OP_SCopy used to copy cached
  3685. ** values to an OP_Copy.
  3686. */
  3687. if( regHit ){
  3688. addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit);
  3689. }
  3690. sqlite3ExprCacheClear(pParse);
  3691. for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
  3692. sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
  3693. }
  3694. pAggInfo->directMode = 0;
  3695. sqlite3ExprCacheClear(pParse);
  3696. if( addrHitTest ){
  3697. sqlite3VdbeJumpHere(v, addrHitTest);
  3698. }
  3699. }
  3700. /*
  3701. ** Add a single OP_Explain instruction to the VDBE to explain a simple
  3702. ** count(*) query ("SELECT count(*) FROM pTab").
  3703. */
  3704. #ifndef SQLITE_OMIT_EXPLAIN
  3705. static void explainSimpleCount(
  3706. Parse *pParse, /* Parse context */
  3707. Table *pTab, /* Table being queried */
  3708. Index *pIdx /* Index used to optimize scan, or NULL */
  3709. ){
  3710. if( pParse->explain==2 ){
  3711. char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s",
  3712. pTab->zName,
  3713. pIdx ? " USING COVERING INDEX " : "",
  3714. pIdx ? pIdx->zName : ""
  3715. );
  3716. sqlite3VdbeAddOp4(
  3717. pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
  3718. );
  3719. }
  3720. }
  3721. #else
  3722. # define explainSimpleCount(a,b,c)
  3723. #endif
  3724. /*
  3725. ** Generate code for the SELECT statement given in the p argument.
  3726. **
  3727. ** The results are distributed in various ways depending on the
  3728. ** contents of the SelectDest structure pointed to by argument pDest
  3729. ** as follows:
  3730. **
  3731. ** pDest->eDest Result
  3732. ** ------------ -------------------------------------------
  3733. ** SRT_Output Generate a row of output (using the OP_ResultRow
  3734. ** opcode) for each row in the result set.
  3735. **
  3736. ** SRT_Mem Only valid if the result is a single column.
  3737. ** Store the first column of the first result row
  3738. ** in register pDest->iSDParm then abandon the rest
  3739. ** of the query. This destination implies "LIMIT 1".
  3740. **
  3741. ** SRT_Set The result must be a single column. Store each
  3742. ** row of result as the key in table pDest->iSDParm.
  3743. ** Apply the affinity pDest->affSdst before storing
  3744. ** results. Used to implement "IN (SELECT ...)".
  3745. **
  3746. ** SRT_Union Store results as a key in a temporary table
  3747. ** identified by pDest->iSDParm.
  3748. **
  3749. ** SRT_Except Remove results from the temporary table pDest->iSDParm.
  3750. **
  3751. ** SRT_Table Store results in temporary table pDest->iSDParm.
  3752. ** This is like SRT_EphemTab except that the table
  3753. ** is assumed to already be open.
  3754. **
  3755. ** SRT_EphemTab Create an temporary table pDest->iSDParm and store
  3756. ** the result there. The cursor is left open after
  3757. ** returning. This is like SRT_Table except that
  3758. ** this destination uses OP_OpenEphemeral to create
  3759. ** the table first.
  3760. **
  3761. ** SRT_Coroutine Generate a co-routine that returns a new row of
  3762. ** results each time it is invoked. The entry point
  3763. ** of the co-routine is stored in register pDest->iSDParm.
  3764. **
  3765. ** SRT_Exists Store a 1 in memory cell pDest->iSDParm if the result
  3766. ** set is not empty.
  3767. **
  3768. ** SRT_Discard Throw the results away. This is used by SELECT
  3769. ** statements within triggers whose only purpose is
  3770. ** the side-effects of functions.
  3771. **
  3772. ** This routine returns the number of errors. If any errors are
  3773. ** encountered, then an appropriate error message is left in
  3774. ** pParse->zErrMsg.
  3775. **
  3776. ** This routine does NOT free the Select structure passed in. The
  3777. ** calling function needs to do that.
  3778. */
  3779. int sqlite3Select(
  3780. Parse *pParse, /* The parser context */
  3781. Select *p, /* The SELECT statement being coded. */
  3782. SelectDest *pDest /* What to do with the query results */
  3783. ){
  3784. int i, j; /* Loop counters */
  3785. WhereInfo *pWInfo; /* Return from sqlite3WhereBegin() */
  3786. Vdbe *v; /* The virtual machine under construction */
  3787. int isAgg; /* True for select lists like "count(*)" */
  3788. ExprList *pEList; /* List of columns to extract. */
  3789. SrcList *pTabList; /* List of tables to select from */
  3790. Expr *pWhere; /* The WHERE clause. May be NULL */
  3791. ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */
  3792. ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */
  3793. Expr *pHaving; /* The HAVING clause. May be NULL */
  3794. int rc = 1; /* Value to return from this function */
  3795. int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */
  3796. DistinctCtx sDistinct; /* Info on how to code the DISTINCT keyword */
  3797. AggInfo sAggInfo; /* Information used by aggregate queries */
  3798. int iEnd; /* Address of the end of the query */
  3799. sqlite3 *db; /* The database connection */
  3800. #ifndef SQLITE_OMIT_EXPLAIN
  3801. int iRestoreSelectId = pParse->iSelectId;
  3802. pParse->iSelectId = pParse->iNextSelectId++;
  3803. #endif
  3804. db = pParse->db;
  3805. if( p==0 || db->mallocFailed || pParse->nErr ){
  3806. return 1;
  3807. }
  3808. if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  3809. memset(&sAggInfo, 0, sizeof(sAggInfo));
  3810. if( IgnorableOrderby(pDest) ){
  3811. assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union ||
  3812. pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
  3813. /* If ORDER BY makes no difference in the output then neither does
  3814. ** DISTINCT so it can be removed too. */
  3815. sqlite3ExprListDelete(db, p->pOrderBy);
  3816. p->pOrderBy = 0;
  3817. p->selFlags &= ~SF_Distinct;
  3818. }
  3819. sqlite3SelectPrep(pParse, p, 0);
  3820. pOrderBy = p->pOrderBy;
  3821. pTabList = p->pSrc;
  3822. pEList = p->pEList;
  3823. if( pParse->nErr || db->mallocFailed ){
  3824. goto select_end;
  3825. }
  3826. isAgg = (p->selFlags & SF_Aggregate)!=0;
  3827. assert( pEList!=0 );
  3828. /* Begin generating code.
  3829. */
  3830. v = sqlite3GetVdbe(pParse);
  3831. if( v==0 ) goto select_end;
  3832. /* If writing to memory or generating a set
  3833. ** only a single column may be output.
  3834. */
  3835. #ifndef SQLITE_OMIT_SUBQUERY
  3836. if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
  3837. goto select_end;
  3838. }
  3839. #endif
  3840. /* Generate code for all sub-queries in the FROM clause
  3841. */
  3842. #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3843. for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
  3844. struct SrcList_item *pItem = &pTabList->a[i];
  3845. SelectDest dest;
  3846. Select *pSub = pItem->pSelect;
  3847. int isAggSub;
  3848. if( pSub==0 ) continue;
  3849. /* Sometimes the code for a subquery will be generated more than
  3850. ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
  3851. ** for example. In that case, do not regenerate the code to manifest
  3852. ** a view or the co-routine to implement a view. The first instance
  3853. ** is sufficient, though the subroutine to manifest the view does need
  3854. ** to be invoked again. */
  3855. if( pItem->addrFillSub ){
  3856. if( pItem->viaCoroutine==0 ){
  3857. sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
  3858. }
  3859. continue;
  3860. }
  3861. /* Increment Parse.nHeight by the height of the largest expression
  3862. ** tree referred to by this, the parent select. The child select
  3863. ** may contain expression trees of at most
  3864. ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
  3865. ** more conservative than necessary, but much easier than enforcing
  3866. ** an exact limit.
  3867. */
  3868. pParse->nHeight += sqlite3SelectExprHeight(p);
  3869. isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
  3870. if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
  3871. /* This subquery can be absorbed into its parent. */
  3872. if( isAggSub ){
  3873. isAgg = 1;
  3874. p->selFlags |= SF_Aggregate;
  3875. }
  3876. i = -1;
  3877. }else if( pTabList->nSrc==1 && (p->selFlags & SF_Materialize)==0
  3878. && OptimizationEnabled(db, SQLITE_SubqCoroutine)
  3879. ){
  3880. /* Implement a co-routine that will return a single row of the result
  3881. ** set on each invocation.
  3882. */
  3883. int addrTop;
  3884. int addrEof;
  3885. pItem->regReturn = ++pParse->nMem;
  3886. addrEof = ++pParse->nMem;
  3887. /* Before coding the OP_Goto to jump to the start of the main routine,
  3888. ** ensure that the jump to the verify-schema routine has already
  3889. ** been coded. Otherwise, the verify-schema would likely be coded as
  3890. ** part of the co-routine. If the main routine then accessed the
  3891. ** database before invoking the co-routine for the first time (for
  3892. ** example to initialize a LIMIT register from a sub-select), it would
  3893. ** be doing so without having verified the schema version and obtained
  3894. ** the required db locks. See ticket d6b36be38. */
  3895. sqlite3CodeVerifySchema(pParse, -1);
  3896. sqlite3VdbeAddOp0(v, OP_Goto);
  3897. addrTop = sqlite3VdbeAddOp1(v, OP_OpenPseudo, pItem->iCursor);
  3898. sqlite3VdbeChangeP5(v, 1);
  3899. VdbeComment((v, "coroutine for %s", pItem->pTab->zName));
  3900. pItem->addrFillSub = addrTop;
  3901. sqlite3VdbeAddOp2(v, OP_Integer, 0, addrEof);
  3902. sqlite3VdbeChangeP5(v, 1);
  3903. sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
  3904. explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  3905. sqlite3Select(pParse, pSub, &dest);
  3906. pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
  3907. pItem->viaCoroutine = 1;
  3908. sqlite3VdbeChangeP2(v, addrTop, dest.iSdst);
  3909. sqlite3VdbeChangeP3(v, addrTop, dest.nSdst);
  3910. sqlite3VdbeAddOp2(v, OP_Integer, 1, addrEof);
  3911. sqlite3VdbeAddOp1(v, OP_Yield, pItem->regReturn);
  3912. VdbeComment((v, "end %s", pItem->pTab->zName));
  3913. sqlite3VdbeJumpHere(v, addrTop-1);
  3914. sqlite3ClearTempRegCache(pParse);
  3915. }else{
  3916. /* Generate a subroutine that will fill an ephemeral table with
  3917. ** the content of this subquery. pItem->addrFillSub will point
  3918. ** to the address of the generated subroutine. pItem->regReturn
  3919. ** is a register allocated to hold the subroutine return address
  3920. */
  3921. int topAddr;
  3922. int onceAddr = 0;
  3923. int retAddr;
  3924. assert( pItem->addrFillSub==0 );
  3925. pItem->regReturn = ++pParse->nMem;
  3926. topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
  3927. pItem->addrFillSub = topAddr+1;
  3928. VdbeNoopComment((v, "materialize %s", pItem->pTab->zName));
  3929. if( pItem->isCorrelated==0 ){
  3930. /* If the subquery is not correlated and if we are not inside of
  3931. ** a trigger, then we only need to compute the value of the subquery
  3932. ** once. */
  3933. onceAddr = sqlite3CodeOnce(pParse);
  3934. }
  3935. sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  3936. explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  3937. sqlite3Select(pParse, pSub, &dest);
  3938. pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
  3939. if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
  3940. retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
  3941. VdbeComment((v, "end %s", pItem->pTab->zName));
  3942. sqlite3VdbeChangeP1(v, topAddr, retAddr);
  3943. sqlite3ClearTempRegCache(pParse);
  3944. }
  3945. if( /*pParse->nErr ||*/ db->mallocFailed ){
  3946. goto select_end;
  3947. }
  3948. pParse->nHeight -= sqlite3SelectExprHeight(p);
  3949. pTabList = p->pSrc;
  3950. if( !IgnorableOrderby(pDest) ){
  3951. pOrderBy = p->pOrderBy;
  3952. }
  3953. }
  3954. pEList = p->pEList;
  3955. #endif
  3956. pWhere = p->pWhere;
  3957. pGroupBy = p->pGroupBy;
  3958. pHaving = p->pHaving;
  3959. sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0;
  3960. #ifndef SQLITE_OMIT_COMPOUND_SELECT
  3961. /* If there is are a sequence of queries, do the earlier ones first.
  3962. */
  3963. if( p->pPrior ){
  3964. if( p->pRightmost==0 ){
  3965. Select *pLoop, *pRight = 0;
  3966. int cnt = 0;
  3967. int mxSelect;
  3968. for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){
  3969. pLoop->pRightmost = p;
  3970. pLoop->pNext = pRight;
  3971. pRight = pLoop;
  3972. }
  3973. mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
  3974. if( mxSelect && cnt>mxSelect ){
  3975. sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
  3976. goto select_end;
  3977. }
  3978. }
  3979. rc = multiSelect(pParse, p, pDest);
  3980. explainSetInteger(pParse->iSelectId, iRestoreSelectId);
  3981. return rc;
  3982. }
  3983. #endif
  3984. /* If there is both a GROUP BY and an ORDER BY clause and they are
  3985. ** identical, then disable the ORDER BY clause since the GROUP BY
  3986. ** will cause elements to come out in the correct order. This is
  3987. ** an optimization - the correct answer should result regardless.
  3988. ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  3989. ** to disable this optimization for testing purposes.
  3990. */
  3991. if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
  3992. && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
  3993. pOrderBy = 0;
  3994. }
  3995. /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and
  3996. ** if the select-list is the same as the ORDER BY list, then this query
  3997. ** can be rewritten as a GROUP BY. In other words, this:
  3998. **
  3999. ** SELECT DISTINCT xyz FROM ... ORDER BY xyz
  4000. **
  4001. ** is transformed to:
  4002. **
  4003. ** SELECT xyz FROM ... GROUP BY xyz
  4004. **
  4005. ** The second form is preferred as a single index (or temp-table) may be
  4006. ** used for both the ORDER BY and DISTINCT processing. As originally
  4007. ** written the query must use a temp-table for at least one of the ORDER
  4008. ** BY and DISTINCT, and an index or separate temp-table for the other.
  4009. */
  4010. if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct
  4011. && sqlite3ExprListCompare(pOrderBy, p->pEList, -1)==0
  4012. ){
  4013. p->selFlags &= ~SF_Distinct;
  4014. p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
  4015. pGroupBy = p->pGroupBy;
  4016. pOrderBy = 0;
  4017. /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
  4018. ** the sDistinct.isTnct is still set. Hence, isTnct represents the
  4019. ** original setting of the SF_Distinct flag, not the current setting */
  4020. assert( sDistinct.isTnct );
  4021. }
  4022. /* If there is an ORDER BY clause, then this sorting
  4023. ** index might end up being unused if the data can be
  4024. ** extracted in pre-sorted order. If that is the case, then the
  4025. ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
  4026. ** we figure out that the sorting index is not needed. The addrSortIndex
  4027. ** variable is used to facilitate that change.
  4028. */
  4029. if( pOrderBy ){
  4030. KeyInfo *pKeyInfo;
  4031. pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);
  4032. pOrderBy->iECursor = pParse->nTab++;
  4033. p->addrOpenEphm[2] = addrSortIndex =
  4034. sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
  4035. pOrderBy->iECursor, pOrderBy->nExpr+2, 0,
  4036. (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  4037. }else{
  4038. addrSortIndex = -1;
  4039. }
  4040. /* If the output is destined for a temporary table, open that table.
  4041. */
  4042. if( pDest->eDest==SRT_EphemTab ){
  4043. sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr);
  4044. }
  4045. /* Set the limiter.
  4046. */
  4047. iEnd = sqlite3VdbeMakeLabel(v);
  4048. p->nSelectRow = LARGEST_INT64;
  4049. computeLimitRegisters(pParse, p, iEnd);
  4050. if( p->iLimit==0 && addrSortIndex>=0 ){
  4051. sqlite3VdbeGetOp(v, addrSortIndex)->opcode = OP_SorterOpen;
  4052. p->selFlags |= SF_UseSorter;
  4053. }
  4054. /* Open a virtual index to use for the distinct set.
  4055. */
  4056. if( p->selFlags & SF_Distinct ){
  4057. sDistinct.tabTnct = pParse->nTab++;
  4058. sDistinct.addrTnct = sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
  4059. sDistinct.tabTnct, 0, 0,
  4060. (char*)keyInfoFromExprList(pParse, p->pEList),
  4061. P4_KEYINFO_HANDOFF);
  4062. sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
  4063. sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  4064. }else{
  4065. sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  4066. }
  4067. if( !isAgg && pGroupBy==0 ){
  4068. /* No aggregate functions and no GROUP BY clause */
  4069. u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);
  4070. /* Begin the database scan. */
  4071. pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList,
  4072. wctrlFlags, 0);
  4073. if( pWInfo==0 ) goto select_end;
  4074. if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
  4075. p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
  4076. }
  4077. if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
  4078. sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo);
  4079. }
  4080. if( pOrderBy && sqlite3WhereIsOrdered(pWInfo) ) pOrderBy = 0;
  4081. /* If sorting index that was created by a prior OP_OpenEphemeral
  4082. ** instruction ended up not being needed, then change the OP_OpenEphemeral
  4083. ** into an OP_Noop.
  4084. */
  4085. if( addrSortIndex>=0 && pOrderBy==0 ){
  4086. sqlite3VdbeChangeToNoop(v, addrSortIndex);
  4087. p->addrOpenEphm[2] = -1;
  4088. }
  4089. /* Use the standard inner loop. */
  4090. selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, &sDistinct, pDest,
  4091. sqlite3WhereContinueLabel(pWInfo),
  4092. sqlite3WhereBreakLabel(pWInfo));
  4093. /* End the database scan loop.
  4094. */
  4095. sqlite3WhereEnd(pWInfo);
  4096. }else{
  4097. /* This case when there exist aggregate functions or a GROUP BY clause
  4098. ** or both */
  4099. NameContext sNC; /* Name context for processing aggregate information */
  4100. int iAMem; /* First Mem address for storing current GROUP BY */
  4101. int iBMem; /* First Mem address for previous GROUP BY */
  4102. int iUseFlag; /* Mem address holding flag indicating that at least
  4103. ** one row of the input to the aggregator has been
  4104. ** processed */
  4105. int iAbortFlag; /* Mem address which causes query abort if positive */
  4106. int groupBySort; /* Rows come from source in GROUP BY order */
  4107. int addrEnd; /* End of processing for this SELECT */
  4108. int sortPTab = 0; /* Pseudotable used to decode sorting results */
  4109. int sortOut = 0; /* Output register from the sorter */
  4110. /* Remove any and all aliases between the result set and the
  4111. ** GROUP BY clause.
  4112. */
  4113. if( pGroupBy ){
  4114. int k; /* Loop counter */
  4115. struct ExprList_item *pItem; /* For looping over expression in a list */
  4116. for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){
  4117. pItem->iAlias = 0;
  4118. }
  4119. for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
  4120. pItem->iAlias = 0;
  4121. }
  4122. if( p->nSelectRow>100 ) p->nSelectRow = 100;
  4123. }else{
  4124. p->nSelectRow = 1;
  4125. }
  4126. /* Create a label to jump to when we want to abort the query */
  4127. addrEnd = sqlite3VdbeMakeLabel(v);
  4128. /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
  4129. ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
  4130. ** SELECT statement.
  4131. */
  4132. memset(&sNC, 0, sizeof(sNC));
  4133. sNC.pParse = pParse;
  4134. sNC.pSrcList = pTabList;
  4135. sNC.pAggInfo = &sAggInfo;
  4136. sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0;
  4137. sAggInfo.pGroupBy = pGroupBy;
  4138. sqlite3ExprAnalyzeAggList(&sNC, pEList);
  4139. sqlite3ExprAnalyzeAggList(&sNC, pOrderBy);
  4140. if( pHaving ){
  4141. sqlite3ExprAnalyzeAggregates(&sNC, pHaving);
  4142. }
  4143. sAggInfo.nAccumulator = sAggInfo.nColumn;
  4144. for(i=0; i<sAggInfo.nFunc; i++){
  4145. assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) );
  4146. sNC.ncFlags |= NC_InAggFunc;
  4147. sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList);
  4148. sNC.ncFlags &= ~NC_InAggFunc;
  4149. }
  4150. if( db->mallocFailed ) goto select_end;
  4151. /* Processing for aggregates with GROUP BY is very different and
  4152. ** much more complex than aggregates without a GROUP BY.
  4153. */
  4154. if( pGroupBy ){
  4155. KeyInfo *pKeyInfo; /* Keying information for the group by clause */
  4156. int j1; /* A-vs-B comparision jump */
  4157. int addrOutputRow; /* Start of subroutine that outputs a result row */
  4158. int regOutputRow; /* Return address register for output subroutine */
  4159. int addrSetAbort; /* Set the abort flag and return */
  4160. int addrTopOfLoop; /* Top of the input loop */
  4161. int addrSortingIdx; /* The OP_OpenEphemeral for the sorting index */
  4162. int addrReset; /* Subroutine for resetting the accumulator */
  4163. int regReset; /* Return address register for reset subroutine */
  4164. /* If there is a GROUP BY clause we might need a sorting index to
  4165. ** implement it. Allocate that sorting index now. If it turns out
  4166. ** that we do not need it after all, the OP_SorterOpen instruction
  4167. ** will be converted into a Noop.
  4168. */
  4169. sAggInfo.sortingIdx = pParse->nTab++;
  4170. pKeyInfo = keyInfoFromExprList(pParse, pGroupBy);
  4171. addrSortingIdx = sqlite3VdbeAddOp4(v, OP_SorterOpen,
  4172. sAggInfo.sortingIdx, sAggInfo.nSortingColumn,
  4173. 0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  4174. /* Initialize memory locations used by GROUP BY aggregate processing
  4175. */
  4176. iUseFlag = ++pParse->nMem;
  4177. iAbortFlag = ++pParse->nMem;
  4178. regOutputRow = ++pParse->nMem;
  4179. addrOutputRow = sqlite3VdbeMakeLabel(v);
  4180. regReset = ++pParse->nMem;
  4181. addrReset = sqlite3VdbeMakeLabel(v);
  4182. iAMem = pParse->nMem + 1;
  4183. pParse->nMem += pGroupBy->nExpr;
  4184. iBMem = pParse->nMem + 1;
  4185. pParse->nMem += pGroupBy->nExpr;
  4186. sqlite3VdbeAddOp2(v, OP_Integer, 0, iAbortFlag);
  4187. VdbeComment((v, "clear abort flag"));
  4188. sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag);
  4189. VdbeComment((v, "indicate accumulator empty"));
  4190. sqlite3VdbeAddOp3(v, OP_Null, 0, iAMem, iAMem+pGroupBy->nExpr-1);
  4191. /* Begin a loop that will extract all source rows in GROUP BY order.
  4192. ** This might involve two separate loops with an OP_Sort in between, or
  4193. ** it might be a single loop that uses an index to extract information
  4194. ** in the right order to begin with.
  4195. */
  4196. sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  4197. pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
  4198. WHERE_GROUPBY, 0);
  4199. if( pWInfo==0 ) goto select_end;
  4200. if( sqlite3WhereIsOrdered(pWInfo) ){
  4201. /* The optimizer is able to deliver rows in group by order so
  4202. ** we do not have to sort. The OP_OpenEphemeral table will be
  4203. ** cancelled later because we still need to use the pKeyInfo
  4204. */
  4205. groupBySort = 0;
  4206. }else{
  4207. /* Rows are coming out in undetermined order. We have to push
  4208. ** each row into a sorting index, terminate the first loop,
  4209. ** then loop over the sorting index in order to get the output
  4210. ** in sorted order
  4211. */
  4212. int regBase;
  4213. int regRecord;
  4214. int nCol;
  4215. int nGroupBy;
  4216. explainTempTable(pParse,
  4217. (sDistinct.isTnct && (p->selFlags&SF_Distinct)==0) ?
  4218. "DISTINCT" : "GROUP BY");
  4219. groupBySort = 1;
  4220. nGroupBy = pGroupBy->nExpr;
  4221. nCol = nGroupBy + 1;
  4222. j = nGroupBy+1;
  4223. for(i=0; i<sAggInfo.nColumn; i++){
  4224. if( sAggInfo.aCol[i].iSorterColumn>=j ){
  4225. nCol++;
  4226. j++;
  4227. }
  4228. }
  4229. regBase = sqlite3GetTempRange(pParse, nCol);
  4230. sqlite3ExprCacheClear(pParse);
  4231. sqlite3ExprCodeExprList(pParse, pGroupBy, regBase, 0);
  4232. sqlite3VdbeAddOp2(v, OP_Sequence, sAggInfo.sortingIdx,regBase+nGroupBy);
  4233. j = nGroupBy+1;
  4234. for(i=0; i<sAggInfo.nColumn; i++){
  4235. struct AggInfo_col *pCol = &sAggInfo.aCol[i];
  4236. if( pCol->iSorterColumn>=j ){
  4237. int r1 = j + regBase;
  4238. int r2;
  4239. r2 = sqlite3ExprCodeGetColumn(pParse,
  4240. pCol->pTab, pCol->iColumn, pCol->iTable, r1, 0);
  4241. if( r1!=r2 ){
  4242. sqlite3VdbeAddOp2(v, OP_SCopy, r2, r1);
  4243. }
  4244. j++;
  4245. }
  4246. }
  4247. regRecord = sqlite3GetTempReg(pParse);
  4248. sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regRecord);
  4249. sqlite3VdbeAddOp2(v, OP_SorterInsert, sAggInfo.sortingIdx, regRecord);
  4250. sqlite3ReleaseTempReg(pParse, regRecord);
  4251. sqlite3ReleaseTempRange(pParse, regBase, nCol);
  4252. sqlite3WhereEnd(pWInfo);
  4253. sAggInfo.sortingIdxPTab = sortPTab = pParse->nTab++;
  4254. sortOut = sqlite3GetTempReg(pParse);
  4255. sqlite3VdbeAddOp3(v, OP_OpenPseudo, sortPTab, sortOut, nCol);
  4256. sqlite3VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd);
  4257. VdbeComment((v, "GROUP BY sort"));
  4258. sAggInfo.useSortingIdx = 1;
  4259. sqlite3ExprCacheClear(pParse);
  4260. }
  4261. /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
  4262. ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
  4263. ** Then compare the current GROUP BY terms against the GROUP BY terms
  4264. ** from the previous row currently stored in a0, a1, a2...
  4265. */
  4266. addrTopOfLoop = sqlite3VdbeCurrentAddr(v);
  4267. sqlite3ExprCacheClear(pParse);
  4268. if( groupBySort ){
  4269. sqlite3VdbeAddOp2(v, OP_SorterData, sAggInfo.sortingIdx, sortOut);
  4270. }
  4271. for(j=0; j<pGroupBy->nExpr; j++){
  4272. if( groupBySort ){
  4273. sqlite3VdbeAddOp3(v, OP_Column, sortPTab, j, iBMem+j);
  4274. if( j==0 ) sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE);
  4275. }else{
  4276. sAggInfo.directMode = 1;
  4277. sqlite3ExprCode(pParse, pGroupBy->a[j].pExpr, iBMem+j);
  4278. }
  4279. }
  4280. sqlite3VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr,
  4281. (char*)pKeyInfo, P4_KEYINFO);
  4282. j1 = sqlite3VdbeCurrentAddr(v);
  4283. sqlite3VdbeAddOp3(v, OP_Jump, j1+1, 0, j1+1);
  4284. /* Generate code that runs whenever the GROUP BY changes.
  4285. ** Changes in the GROUP BY are detected by the previous code
  4286. ** block. If there were no changes, this block is skipped.
  4287. **
  4288. ** This code copies current group by terms in b0,b1,b2,...
  4289. ** over to a0,a1,a2. It then calls the output subroutine
  4290. ** and resets the aggregate accumulator registers in preparation
  4291. ** for the next GROUP BY batch.
  4292. */
  4293. sqlite3ExprCodeMove(pParse, iBMem, iAMem, pGroupBy->nExpr);
  4294. sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow);
  4295. VdbeComment((v, "output one row"));
  4296. sqlite3VdbeAddOp2(v, OP_IfPos, iAbortFlag, addrEnd);
  4297. VdbeComment((v, "check abort flag"));
  4298. sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  4299. VdbeComment((v, "reset accumulator"));
  4300. /* Update the aggregate accumulators based on the content of
  4301. ** the current row
  4302. */
  4303. sqlite3VdbeJumpHere(v, j1);
  4304. updateAccumulator(pParse, &sAggInfo);
  4305. sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag);
  4306. VdbeComment((v, "indicate data in accumulator"));
  4307. /* End of the loop
  4308. */
  4309. if( groupBySort ){
  4310. sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop);
  4311. }else{
  4312. sqlite3WhereEnd(pWInfo);
  4313. sqlite3VdbeChangeToNoop(v, addrSortingIdx);
  4314. }
  4315. /* Output the final row of result
  4316. */
  4317. sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow);
  4318. VdbeComment((v, "output final row"));
  4319. /* Jump over the subroutines
  4320. */
  4321. sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEnd);
  4322. /* Generate a subroutine that outputs a single row of the result
  4323. ** set. This subroutine first looks at the iUseFlag. If iUseFlag
  4324. ** is less than or equal to zero, the subroutine is a no-op. If
  4325. ** the processing calls for the query to abort, this subroutine
  4326. ** increments the iAbortFlag memory location before returning in
  4327. ** order to signal the caller to abort.
  4328. */
  4329. addrSetAbort = sqlite3VdbeCurrentAddr(v);
  4330. sqlite3VdbeAddOp2(v, OP_Integer, 1, iAbortFlag);
  4331. VdbeComment((v, "set abort flag"));
  4332. sqlite3VdbeAddOp1(v, OP_Return, regOutputRow);
  4333. sqlite3VdbeResolveLabel(v, addrOutputRow);
  4334. addrOutputRow = sqlite3VdbeCurrentAddr(v);
  4335. sqlite3VdbeAddOp2(v, OP_IfPos, iUseFlag, addrOutputRow+2);
  4336. VdbeComment((v, "Groupby result generator entry point"));
  4337. sqlite3VdbeAddOp1(v, OP_Return, regOutputRow);
  4338. finalizeAggFunctions(pParse, &sAggInfo);
  4339. sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, SQLITE_JUMPIFNULL);
  4340. selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy,
  4341. &sDistinct, pDest,
  4342. addrOutputRow+1, addrSetAbort);
  4343. sqlite3VdbeAddOp1(v, OP_Return, regOutputRow);
  4344. VdbeComment((v, "end groupby result generator"));
  4345. /* Generate a subroutine that will reset the group-by accumulator
  4346. */
  4347. sqlite3VdbeResolveLabel(v, addrReset);
  4348. resetAccumulator(pParse, &sAggInfo);
  4349. sqlite3VdbeAddOp1(v, OP_Return, regReset);
  4350. } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */
  4351. else {
  4352. ExprList *pDel = 0;
  4353. #ifndef SQLITE_OMIT_BTREECOUNT
  4354. Table *pTab;
  4355. if( (pTab = isSimpleCount(p, &sAggInfo))!=0 ){
  4356. /* If isSimpleCount() returns a pointer to a Table structure, then
  4357. ** the SQL statement is of the form:
  4358. **
  4359. ** SELECT count(*) FROM <tbl>
  4360. **
  4361. ** where the Table structure returned represents table <tbl>.
  4362. **
  4363. ** This statement is so common that it is optimized specially. The
  4364. ** OP_Count instruction is executed either on the intkey table that
  4365. ** contains the data for table <tbl> or on one of its indexes. It
  4366. ** is better to execute the op on an index, as indexes are almost
  4367. ** always spread across less pages than their corresponding tables.
  4368. */
  4369. const int iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
  4370. const int iCsr = pParse->nTab++; /* Cursor to scan b-tree */
  4371. Index *pIdx; /* Iterator variable */
  4372. KeyInfo *pKeyInfo = 0; /* Keyinfo for scanned index */
  4373. Index *pBest = 0; /* Best index found so far */
  4374. int iRoot = pTab->tnum; /* Root page of scanned b-tree */
  4375. sqlite3CodeVerifySchema(pParse, iDb);
  4376. sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  4377. /* Search for the index that has the lowest scan cost.
  4378. **
  4379. ** (2011-04-15) Do not do a full scan of an unordered index.
  4380. **
  4381. ** (2013-10-03) Do not count the entires in a partial index.
  4382. **
  4383. ** In practice the KeyInfo structure will not be used. It is only
  4384. ** passed to keep OP_OpenRead happy.
  4385. */
  4386. for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  4387. if( pIdx->bUnordered==0
  4388. && pIdx->szIdxRow<pTab->szTabRow
  4389. && pIdx->pPartIdxWhere==0
  4390. && (!pBest || pIdx->szIdxRow<pBest->szIdxRow)
  4391. ){
  4392. pBest = pIdx;
  4393. }
  4394. }
  4395. if( pBest ){
  4396. iRoot = pBest->tnum;
  4397. pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest);
  4398. }
  4399. /* Open a read-only cursor, execute the OP_Count, close the cursor. */
  4400. sqlite3VdbeAddOp3(v, OP_OpenRead, iCsr, iRoot, iDb);
  4401. if( pKeyInfo ){
  4402. sqlite3VdbeChangeP4(v, -1, (char *)pKeyInfo, P4_KEYINFO_HANDOFF);
  4403. }
  4404. sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem);
  4405. sqlite3VdbeAddOp1(v, OP_Close, iCsr);
  4406. explainSimpleCount(pParse, pTab, pBest);
  4407. }else
  4408. #endif /* SQLITE_OMIT_BTREECOUNT */
  4409. {
  4410. /* Check if the query is of one of the following forms:
  4411. **
  4412. ** SELECT min(x) FROM ...
  4413. ** SELECT max(x) FROM ...
  4414. **
  4415. ** If it is, then ask the code in where.c to attempt to sort results
  4416. ** as if there was an "ORDER ON x" or "ORDER ON x DESC" clause.
  4417. ** If where.c is able to produce results sorted in this order, then
  4418. ** add vdbe code to break out of the processing loop after the
  4419. ** first iteration (since the first iteration of the loop is
  4420. ** guaranteed to operate on the row with the minimum or maximum
  4421. ** value of x, the only row required).
  4422. **
  4423. ** A special flag must be passed to sqlite3WhereBegin() to slightly
  4424. ** modify behavior as follows:
  4425. **
  4426. ** + If the query is a "SELECT min(x)", then the loop coded by
  4427. ** where.c should not iterate over any values with a NULL value
  4428. ** for x.
  4429. **
  4430. ** + The optimizer code in where.c (the thing that decides which
  4431. ** index or indices to use) should place a different priority on
  4432. ** satisfying the 'ORDER BY' clause than it does in other cases.
  4433. ** Refer to code and comments in where.c for details.
  4434. */
  4435. ExprList *pMinMax = 0;
  4436. u8 flag = WHERE_ORDERBY_NORMAL;
  4437. assert( p->pGroupBy==0 );
  4438. assert( flag==0 );
  4439. if( p->pHaving==0 ){
  4440. flag = minMaxQuery(&sAggInfo, &pMinMax);
  4441. }
  4442. assert( flag==0 || (pMinMax!=0 && pMinMax->nExpr==1) );
  4443. if( flag ){
  4444. pMinMax = sqlite3ExprListDup(db, pMinMax, 0);
  4445. pDel = pMinMax;
  4446. if( pMinMax && !db->mallocFailed ){
  4447. pMinMax->a[0].sortOrder = flag!=WHERE_ORDERBY_MIN ?1:0;
  4448. pMinMax->a[0].pExpr->op = TK_COLUMN;
  4449. }
  4450. }
  4451. /* This case runs if the aggregate has no GROUP BY clause. The
  4452. ** processing is much simpler since there is only a single row
  4453. ** of output.
  4454. */
  4455. resetAccumulator(pParse, &sAggInfo);
  4456. pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0);
  4457. if( pWInfo==0 ){
  4458. sqlite3ExprListDelete(db, pDel);
  4459. goto select_end;
  4460. }
  4461. updateAccumulator(pParse, &sAggInfo);
  4462. assert( pMinMax==0 || pMinMax->nExpr==1 );
  4463. if( sqlite3WhereIsOrdered(pWInfo) ){
  4464. sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3WhereBreakLabel(pWInfo));
  4465. VdbeComment((v, "%s() by index",
  4466. (flag==WHERE_ORDERBY_MIN?"min":"max")));
  4467. }
  4468. sqlite3WhereEnd(pWInfo);
  4469. finalizeAggFunctions(pParse, &sAggInfo);
  4470. }
  4471. pOrderBy = 0;
  4472. sqlite3ExprIfFalse(pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL);
  4473. selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, 0,
  4474. pDest, addrEnd, addrEnd);
  4475. sqlite3ExprListDelete(db, pDel);
  4476. }
  4477. sqlite3VdbeResolveLabel(v, addrEnd);
  4478. } /* endif aggregate query */
  4479. if( sDistinct.eTnctType==WHERE_DISTINCT_UNORDERED ){
  4480. explainTempTable(pParse, "DISTINCT");
  4481. }
  4482. /* If there is an ORDER BY clause, then we need to sort the results
  4483. ** and send them to the callback one by one.
  4484. */
  4485. if( pOrderBy ){
  4486. explainTempTable(pParse, "ORDER BY");
  4487. generateSortTail(pParse, p, v, pEList->nExpr, pDest);
  4488. }
  4489. /* Jump here to skip this query
  4490. */
  4491. sqlite3VdbeResolveLabel(v, iEnd);
  4492. /* The SELECT was successfully coded. Set the return code to 0
  4493. ** to indicate no errors.
  4494. */
  4495. rc = 0;
  4496. /* Control jumps to here if an error is encountered above, or upon
  4497. ** successful coding of the SELECT.
  4498. */
  4499. select_end:
  4500. explainSetInteger(pParse->iSelectId, iRestoreSelectId);
  4501. /* Identify column names if results of the SELECT are to be output.
  4502. */
  4503. if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){
  4504. generateColumnNames(pParse, pTabList, pEList);
  4505. }
  4506. sqlite3DbFree(db, sAggInfo.aCol);
  4507. sqlite3DbFree(db, sAggInfo.aFunc);
  4508. return rc;
  4509. }
  4510. #if defined(SQLITE_ENABLE_TREE_EXPLAIN)
  4511. /*
  4512. ** Generate a human-readable description of a the Select object.
  4513. */
  4514. static void explainOneSelect(Vdbe *pVdbe, Select *p){
  4515. sqlite3ExplainPrintf(pVdbe, "SELECT ");
  4516. if( p->selFlags & (SF_Distinct|SF_Aggregate) ){
  4517. if( p->selFlags & SF_Distinct ){
  4518. sqlite3ExplainPrintf(pVdbe, "DISTINCT ");
  4519. }
  4520. if( p->selFlags & SF_Aggregate ){
  4521. sqlite3ExplainPrintf(pVdbe, "agg_flag ");
  4522. }
  4523. sqlite3ExplainNL(pVdbe);
  4524. sqlite3ExplainPrintf(pVdbe, " ");
  4525. }
  4526. sqlite3ExplainExprList(pVdbe, p->pEList);
  4527. sqlite3ExplainNL(pVdbe);
  4528. if( p->pSrc && p->pSrc->nSrc ){
  4529. int i;
  4530. sqlite3ExplainPrintf(pVdbe, "FROM ");
  4531. sqlite3ExplainPush(pVdbe);
  4532. for(i=0; i<p->pSrc->nSrc; i++){
  4533. struct SrcList_item *pItem = &p->pSrc->a[i];
  4534. sqlite3ExplainPrintf(pVdbe, "{%d,*} = ", pItem->iCursor);
  4535. if( pItem->pSelect ){
  4536. sqlite3ExplainSelect(pVdbe, pItem->pSelect);
  4537. if( pItem->pTab ){
  4538. sqlite3ExplainPrintf(pVdbe, " (tabname=%s)", pItem->pTab->zName);
  4539. }
  4540. }else if( pItem->zName ){
  4541. sqlite3ExplainPrintf(pVdbe, "%s", pItem->zName);
  4542. }
  4543. if( pItem->zAlias ){
  4544. sqlite3ExplainPrintf(pVdbe, " (AS %s)", pItem->zAlias);
  4545. }
  4546. if( pItem->jointype & JT_LEFT ){
  4547. sqlite3ExplainPrintf(pVdbe, " LEFT-JOIN");
  4548. }
  4549. sqlite3ExplainNL(pVdbe);
  4550. }
  4551. sqlite3ExplainPop(pVdbe);
  4552. }
  4553. if( p->pWhere ){
  4554. sqlite3ExplainPrintf(pVdbe, "WHERE ");
  4555. sqlite3ExplainExpr(pVdbe, p->pWhere);
  4556. sqlite3ExplainNL(pVdbe);
  4557. }
  4558. if( p->pGroupBy ){
  4559. sqlite3ExplainPrintf(pVdbe, "GROUPBY ");
  4560. sqlite3ExplainExprList(pVdbe, p->pGroupBy);
  4561. sqlite3ExplainNL(pVdbe);
  4562. }
  4563. if( p->pHaving ){
  4564. sqlite3ExplainPrintf(pVdbe, "HAVING ");
  4565. sqlite3ExplainExpr(pVdbe, p->pHaving);
  4566. sqlite3ExplainNL(pVdbe);
  4567. }
  4568. if( p->pOrderBy ){
  4569. sqlite3ExplainPrintf(pVdbe, "ORDERBY ");
  4570. sqlite3ExplainExprList(pVdbe, p->pOrderBy);
  4571. sqlite3ExplainNL(pVdbe);
  4572. }
  4573. if( p->pLimit ){
  4574. sqlite3ExplainPrintf(pVdbe, "LIMIT ");
  4575. sqlite3ExplainExpr(pVdbe, p->pLimit);
  4576. sqlite3ExplainNL(pVdbe);
  4577. }
  4578. if( p->pOffset ){
  4579. sqlite3ExplainPrintf(pVdbe, "OFFSET ");
  4580. sqlite3ExplainExpr(pVdbe, p->pOffset);
  4581. sqlite3ExplainNL(pVdbe);
  4582. }
  4583. }
  4584. void sqlite3ExplainSelect(Vdbe *pVdbe, Select *p){
  4585. if( p==0 ){
  4586. sqlite3ExplainPrintf(pVdbe, "(null-select)");
  4587. return;
  4588. }
  4589. while( p->pPrior ){
  4590. p->pPrior->pNext = p;
  4591. p = p->pPrior;
  4592. }
  4593. sqlite3ExplainPush(pVdbe);
  4594. while( p ){
  4595. explainOneSelect(pVdbe, p);
  4596. p = p->pNext;
  4597. if( p==0 ) break;
  4598. sqlite3ExplainNL(pVdbe);
  4599. sqlite3ExplainPrintf(pVdbe, "%s\n", selectOpName(p->op));
  4600. }
  4601. sqlite3ExplainPrintf(pVdbe, "END");
  4602. sqlite3ExplainPop(pVdbe);
  4603. }
  4604. /* End of the structure debug printing code
  4605. *****************************************************************************/
  4606. #endif /* defined(SQLITE_ENABLE_TREE_EXPLAIN) */