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 ** $Id$
16 */
17 #include "sqliteInt.h"
18
19
20 /*
21 ** Allocate a new Select structure and return a pointer to that
22 ** structure.
23 */
sqliteSelectNew(ExprList * pEList,SrcList * pSrc,Expr * pWhere,ExprList * pGroupBy,Expr * pHaving,ExprList * pOrderBy,int isDistinct,int nLimit,int nOffset)24 Select *sqliteSelectNew(
25 ExprList *pEList, /* which columns to include in the result */
26 SrcList *pSrc, /* the FROM clause -- which tables to scan */
27 Expr *pWhere, /* the WHERE clause */
28 ExprList *pGroupBy, /* the GROUP BY clause */
29 Expr *pHaving, /* the HAVING clause */
30 ExprList *pOrderBy, /* the ORDER BY clause */
31 int isDistinct, /* true if the DISTINCT keyword is present */
32 int nLimit, /* LIMIT value. -1 means not used */
33 int nOffset /* OFFSET value. 0 means no offset */
34 ){
35 Select *pNew;
36 pNew = sqliteMalloc( sizeof(*pNew) );
37 if( pNew==0 ){
38 sqliteExprListDelete(pEList);
39 sqliteSrcListDelete(pSrc);
40 sqliteExprDelete(pWhere);
41 sqliteExprListDelete(pGroupBy);
42 sqliteExprDelete(pHaving);
43 sqliteExprListDelete(pOrderBy);
44 }else{
45 if( pEList==0 ){
46 pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
47 }
48 pNew->pEList = pEList;
49 pNew->pSrc = pSrc;
50 pNew->pWhere = pWhere;
51 pNew->pGroupBy = pGroupBy;
52 pNew->pHaving = pHaving;
53 pNew->pOrderBy = pOrderBy;
54 pNew->isDistinct = isDistinct;
55 pNew->op = TK_SELECT;
56 pNew->nLimit = nLimit;
57 pNew->nOffset = nOffset;
58 pNew->iLimit = -1;
59 pNew->iOffset = -1;
60 }
61 return pNew;
62 }
63
64 /*
65 ** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
66 ** type of join. Return an integer constant that expresses that type
67 ** in terms of the following bit values:
68 **
69 ** JT_INNER
70 ** JT_OUTER
71 ** JT_NATURAL
72 ** JT_LEFT
73 ** JT_RIGHT
74 **
75 ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
76 **
77 ** If an illegal or unsupported join type is seen, then still return
78 ** a join type, but put an error in the pParse structure.
79 */
sqliteJoinType(Parse * pParse,Token * pA,Token * pB,Token * pC)80 int sqliteJoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
81 int jointype = 0;
82 Token *apAll[3];
83 Token *p;
84 static struct {
85 const char *zKeyword;
86 int nChar;
87 int code;
88 } keywords[] = {
89 { "natural", 7, JT_NATURAL },
90 { "left", 4, JT_LEFT|JT_OUTER },
91 { "right", 5, JT_RIGHT|JT_OUTER },
92 { "full", 4, JT_LEFT|JT_RIGHT|JT_OUTER },
93 { "outer", 5, JT_OUTER },
94 { "inner", 5, JT_INNER },
95 { "cross", 5, JT_INNER },
96 };
97 int i, j;
98 apAll[0] = pA;
99 apAll[1] = pB;
100 apAll[2] = pC;
101 for(i=0; i<3 && apAll[i]; i++){
102 p = apAll[i];
103 for(j=0; j<sizeof(keywords)/sizeof(keywords[0]); j++){
104 if( p->n==keywords[j].nChar
105 && sqliteStrNICmp(p->z, keywords[j].zKeyword, p->n)==0 ){
106 jointype |= keywords[j].code;
107 break;
108 }
109 }
110 if( j>=sizeof(keywords)/sizeof(keywords[0]) ){
111 jointype |= JT_ERROR;
112 break;
113 }
114 }
115 if(
116 (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||
117 (jointype & JT_ERROR)!=0
118 ){
119 static Token dummy = { 0, 0 };
120 char *zSp1 = " ", *zSp2 = " ";
121 if( pB==0 ){ pB = &dummy; zSp1 = 0; }
122 if( pC==0 ){ pC = &dummy; zSp2 = 0; }
123 sqliteSetNString(&pParse->zErrMsg, "unknown or unsupported join type: ", 0,
124 pA->z, pA->n, zSp1, 1, pB->z, pB->n, zSp2, 1, pC->z, pC->n, 0);
125 pParse->nErr++;
126 jointype = JT_INNER;
127 }else if( jointype & JT_RIGHT ){
128 sqliteErrorMsg(pParse,
129 "RIGHT and FULL OUTER JOINs are not currently supported");
130 jointype = JT_INNER;
131 }
132 return jointype;
133 }
134
135 /*
136 ** Return the index of a column in a table. Return -1 if the column
137 ** is not contained in the table.
138 */
columnIndex(Table * pTab,const char * zCol)139 static int columnIndex(Table *pTab, const char *zCol){
140 int i;
141 for(i=0; i<pTab->nCol; i++){
142 if( sqliteStrICmp(pTab->aCol[i].zName, zCol)==0 ) return i;
143 }
144 return -1;
145 }
146
147 /*
148 ** Add a term to the WHERE expression in *ppExpr that requires the
149 ** zCol column to be equal in the two tables pTab1 and pTab2.
150 */
addWhereTerm(const char * zCol,const Table * pTab1,const Table * pTab2,Expr ** ppExpr)151 static void addWhereTerm(
152 const char *zCol, /* Name of the column */
153 const Table *pTab1, /* First table */
154 const Table *pTab2, /* Second table */
155 Expr **ppExpr /* Add the equality term to this expression */
156 ){
157 Token dummy;
158 Expr *pE1a, *pE1b, *pE1c;
159 Expr *pE2a, *pE2b, *pE2c;
160 Expr *pE;
161
162 dummy.z = zCol;
163 dummy.n = strlen(zCol);
164 dummy.dyn = 0;
165 pE1a = sqliteExpr(TK_ID, 0, 0, &dummy);
166 pE2a = sqliteExpr(TK_ID, 0, 0, &dummy);
167 dummy.z = pTab1->zName;
168 dummy.n = strlen(dummy.z);
169 pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);
170 dummy.z = pTab2->zName;
171 dummy.n = strlen(dummy.z);
172 pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);
173 pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
174 pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
175 pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
176 ExprSetProperty(pE, EP_FromJoin);
177 if( *ppExpr ){
178 *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
179 }else{
180 *ppExpr = pE;
181 }
182 }
183
184 /*
185 ** Set the EP_FromJoin property on all terms of the given expression.
186 **
187 ** The EP_FromJoin property is used on terms of an expression to tell
188 ** the LEFT OUTER JOIN processing logic that this term is part of the
189 ** join restriction specified in the ON or USING clause and not a part
190 ** of the more general WHERE clause. These terms are moved over to the
191 ** WHERE clause during join processing but we need to remember that they
192 ** originated in the ON or USING clause.
193 */
setJoinExpr(Expr * p)194 static void setJoinExpr(Expr *p){
195 while( p ){
196 ExprSetProperty(p, EP_FromJoin);
197 setJoinExpr(p->pLeft);
198 p = p->pRight;
199 }
200 }
201
202 /*
203 ** This routine processes the join information for a SELECT statement.
204 ** ON and USING clauses are converted into extra terms of the WHERE clause.
205 ** NATURAL joins also create extra WHERE clause terms.
206 **
207 ** This routine returns the number of errors encountered.
208 */
sqliteProcessJoin(Parse * pParse,Select * p)209 static int sqliteProcessJoin(Parse *pParse, Select *p){
210 SrcList *pSrc;
211 int i, j;
212 pSrc = p->pSrc;
213 for(i=0; i<pSrc->nSrc-1; i++){
214 struct SrcList_item *pTerm = &pSrc->a[i];
215 struct SrcList_item *pOther = &pSrc->a[i+1];
216
217 if( pTerm->pTab==0 || pOther->pTab==0 ) continue;
218
219 /* When the NATURAL keyword is present, add WHERE clause terms for
220 ** every column that the two tables have in common.
221 */
222 if( pTerm->jointype & JT_NATURAL ){
223 Table *pTab;
224 if( pTerm->pOn || pTerm->pUsing ){
225 sqliteErrorMsg(pParse, "a NATURAL join may not have "
226 "an ON or USING clause", 0);
227 return 1;
228 }
229 pTab = pTerm->pTab;
230 for(j=0; j<pTab->nCol; j++){
231 if( columnIndex(pOther->pTab, pTab->aCol[j].zName)>=0 ){
232 addWhereTerm(pTab->aCol[j].zName, pTab, pOther->pTab, &p->pWhere);
233 }
234 }
235 }
236
237 /* Disallow both ON and USING clauses in the same join
238 */
239 if( pTerm->pOn && pTerm->pUsing ){
240 sqliteErrorMsg(pParse, "cannot have both ON and USING "
241 "clauses in the same join");
242 return 1;
243 }
244
245 /* Add the ON clause to the end of the WHERE clause, connected by
246 ** and AND operator.
247 */
248 if( pTerm->pOn ){
249 setJoinExpr(pTerm->pOn);
250 if( p->pWhere==0 ){
251 p->pWhere = pTerm->pOn;
252 }else{
253 p->pWhere = sqliteExpr(TK_AND, p->pWhere, pTerm->pOn, 0);
254 }
255 pTerm->pOn = 0;
256 }
257
258 /* Create extra terms on the WHERE clause for each column named
259 ** in the USING clause. Example: If the two tables to be joined are
260 ** A and B and the USING clause names X, Y, and Z, then add this
261 ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
262 ** Report an error if any column mentioned in the USING clause is
263 ** not contained in both tables to be joined.
264 */
265 if( pTerm->pUsing ){
266 IdList *pList;
267 int j;
268 assert( i<pSrc->nSrc-1 );
269 pList = pTerm->pUsing;
270 for(j=0; j<pList->nId; j++){
271 if( columnIndex(pTerm->pTab, pList->a[j].zName)<0 ||
272 columnIndex(pOther->pTab, pList->a[j].zName)<0 ){
273 sqliteErrorMsg(pParse, "cannot join using column %s - column "
274 "not present in both tables", pList->a[j].zName);
275 return 1;
276 }
277 addWhereTerm(pList->a[j].zName, pTerm->pTab, pOther->pTab, &p->pWhere);
278 }
279 }
280 }
281 return 0;
282 }
283
284 /*
285 ** Delete the given Select structure and all of its substructures.
286 */
sqliteSelectDelete(Select * p)287 void sqliteSelectDelete(Select *p){
288 if( p==0 ) return;
289 sqliteExprListDelete(p->pEList);
290 sqliteSrcListDelete(p->pSrc);
291 sqliteExprDelete(p->pWhere);
292 sqliteExprListDelete(p->pGroupBy);
293 sqliteExprDelete(p->pHaving);
294 sqliteExprListDelete(p->pOrderBy);
295 sqliteSelectDelete(p->pPrior);
296 sqliteFree(p->zSelect);
297 sqliteFree(p);
298 }
299
300 /*
301 ** Delete the aggregate information from the parse structure.
302 */
sqliteAggregateInfoReset(Parse * pParse)303 static void sqliteAggregateInfoReset(Parse *pParse){
304 sqliteFree(pParse->aAgg);
305 pParse->aAgg = 0;
306 pParse->nAgg = 0;
307 pParse->useAgg = 0;
308 }
309
310 /*
311 ** Insert code into "v" that will push the record on the top of the
312 ** stack into the sorter.
313 */
pushOntoSorter(Parse * pParse,Vdbe * v,ExprList * pOrderBy)314 static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
315 char *zSortOrder;
316 int i;
317 zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
318 if( zSortOrder==0 ) return;
319 for(i=0; i<pOrderBy->nExpr; i++){
320 int order = pOrderBy->a[i].sortOrder;
321 int type;
322 int c;
323 if( (order & SQLITE_SO_TYPEMASK)==SQLITE_SO_TEXT ){
324 type = SQLITE_SO_TEXT;
325 }else if( (order & SQLITE_SO_TYPEMASK)==SQLITE_SO_NUM ){
326 type = SQLITE_SO_NUM;
327 }else if( pParse->db->file_format>=4 ){
328 type = sqliteExprType(pOrderBy->a[i].pExpr);
329 }else{
330 type = SQLITE_SO_NUM;
331 }
332 if( (order & SQLITE_SO_DIRMASK)==SQLITE_SO_ASC ){
333 c = type==SQLITE_SO_TEXT ? 'A' : '+';
334 }else{
335 c = type==SQLITE_SO_TEXT ? 'D' : '-';
336 }
337 zSortOrder[i] = c;
338 sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
339 }
340 zSortOrder[pOrderBy->nExpr] = 0;
341 sqliteVdbeOp3(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, P3_DYNAMIC);
342 sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
343 }
344
345 /*
346 ** This routine adds a P3 argument to the last VDBE opcode that was
347 ** inserted. The P3 argument added is a string suitable for the
348 ** OP_MakeKey or OP_MakeIdxKey opcodes. The string consists of
349 ** characters 't' or 'n' depending on whether or not the various
350 ** fields of the key to be generated should be treated as numeric
351 ** or as text. See the OP_MakeKey and OP_MakeIdxKey opcode
352 ** documentation for additional information about the P3 string.
353 ** See also the sqliteAddIdxKeyType() routine.
354 */
sqliteAddKeyType(Vdbe * v,ExprList * pEList)355 void sqliteAddKeyType(Vdbe *v, ExprList *pEList){
356 int nColumn = pEList->nExpr;
357 char *zType = sqliteMalloc( nColumn+1 );
358 int i;
359 if( zType==0 ) return;
360 for(i=0; i<nColumn; i++){
361 zType[i] = sqliteExprType(pEList->a[i].pExpr)==SQLITE_SO_NUM ? 'n' : 't';
362 }
363 zType[i] = 0;
364 sqliteVdbeChangeP3(v, -1, zType, P3_DYNAMIC);
365 }
366
367 /*
368 ** Add code to implement the OFFSET and LIMIT
369 */
codeLimiter(Vdbe * v,Select * p,int iContinue,int iBreak,int nPop)370 static void codeLimiter(
371 Vdbe *v, /* Generate code into this VM */
372 Select *p, /* The SELECT statement being coded */
373 int iContinue, /* Jump here to skip the current record */
374 int iBreak, /* Jump here to end the loop */
375 int nPop /* Number of times to pop stack when jumping */
376 ){
377 if( p->iOffset>=0 ){
378 int addr = sqliteVdbeCurrentAddr(v) + 2;
379 if( nPop>0 ) addr++;
380 sqliteVdbeAddOp(v, OP_MemIncr, p->iOffset, addr);
381 if( nPop>0 ){
382 sqliteVdbeAddOp(v, OP_Pop, nPop, 0);
383 }
384 sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
385 }
386 if( p->iLimit>=0 ){
387 sqliteVdbeAddOp(v, OP_MemIncr, p->iLimit, iBreak);
388 }
389 }
390
391 /*
392 ** This routine generates the code for the inside of the inner loop
393 ** of a SELECT.
394 **
395 ** If srcTab and nColumn are both zero, then the pEList expressions
396 ** are evaluated in order to get the data for this row. If nColumn>0
397 ** then data is pulled from srcTab and pEList is used only to get the
398 ** datatypes for each column.
399 */
selectInnerLoop(Parse * pParse,Select * p,ExprList * pEList,int srcTab,int nColumn,ExprList * pOrderBy,int distinct,int eDest,int iParm,int iContinue,int iBreak)400 static int selectInnerLoop(
401 Parse *pParse, /* The parser context */
402 Select *p, /* The complete select statement being coded */
403 ExprList *pEList, /* List of values being extracted */
404 int srcTab, /* Pull data from this table */
405 int nColumn, /* Number of columns in the source table */
406 ExprList *pOrderBy, /* If not NULL, sort results using this key */
407 int distinct, /* If >=0, make sure results are distinct */
408 int eDest, /* How to dispose of the results */
409 int iParm, /* An argument to the disposal method */
410 int iContinue, /* Jump here to continue with next row */
411 int iBreak /* Jump here to break out of the inner loop */
412 ){
413 Vdbe *v = pParse->pVdbe;
414 int i;
415 int hasDistinct; /* True if the DISTINCT keyword is present */
416
417 if( v==0 ) return 0;
418 assert( pEList!=0 );
419
420 /* If there was a LIMIT clause on the SELECT statement, then do the check
421 ** to see if this row should be output.
422 */
423 hasDistinct = distinct>=0 && pEList && pEList->nExpr>0;
424 if( pOrderBy==0 && !hasDistinct ){
425 codeLimiter(v, p, iContinue, iBreak, 0);
426 }
427
428 /* Pull the requested columns.
429 */
430 if( nColumn>0 ){
431 for(i=0; i<nColumn; i++){
432 sqliteVdbeAddOp(v, OP_Column, srcTab, i);
433 }
434 }else{
435 nColumn = pEList->nExpr;
436 for(i=0; i<pEList->nExpr; i++){
437 sqliteExprCode(pParse, pEList->a[i].pExpr);
438 }
439 }
440
441 /* If the DISTINCT keyword was present on the SELECT statement
442 ** and this row has been seen before, then do not make this row
443 ** part of the result.
444 */
445 if( hasDistinct ){
446 #if NULL_ALWAYS_DISTINCT
447 sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqliteVdbeCurrentAddr(v)+7);
448 #endif
449 sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1);
450 if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pEList);
451 sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
452 sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
453 sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
454 sqliteVdbeAddOp(v, OP_String, 0, 0);
455 sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0);
456 if( pOrderBy==0 ){
457 codeLimiter(v, p, iContinue, iBreak, nColumn);
458 }
459 }
460
461 switch( eDest ){
462 /* In this mode, write each query result to the key of the temporary
463 ** table iParm.
464 */
465 case SRT_Union: {
466 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
467 sqliteVdbeAddOp(v, OP_String, 0, 0);
468 sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
469 break;
470 }
471
472 /* Store the result as data using a unique key.
473 */
474 case SRT_Table:
475 case SRT_TempTable: {
476 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
477 if( pOrderBy ){
478 pushOntoSorter(pParse, v, pOrderBy);
479 }else{
480 sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
481 sqliteVdbeAddOp(v, OP_Pull, 1, 0);
482 sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
483 }
484 break;
485 }
486
487 /* Construct a record from the query result, but instead of
488 ** saving that record, use it as a key to delete elements from
489 ** the temporary table iParm.
490 */
491 case SRT_Except: {
492 int addr;
493 addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
494 sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
495 sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
496 break;
497 }
498
499 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
500 ** then there should be a single item on the stack. Write this
501 ** item into the set table with bogus data.
502 */
503 case SRT_Set: {
504 int addr1 = sqliteVdbeCurrentAddr(v);
505 int addr2;
506 assert( nColumn==1 );
507 sqliteVdbeAddOp(v, OP_NotNull, -1, addr1+3);
508 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
509 addr2 = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
510 if( pOrderBy ){
511 pushOntoSorter(pParse, v, pOrderBy);
512 }else{
513 sqliteVdbeAddOp(v, OP_String, 0, 0);
514 sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
515 }
516 sqliteVdbeChangeP2(v, addr2, sqliteVdbeCurrentAddr(v));
517 break;
518 }
519
520 /* If this is a scalar select that is part of an expression, then
521 ** store the results in the appropriate memory cell and break out
522 ** of the scan loop.
523 */
524 case SRT_Mem: {
525 assert( nColumn==1 );
526 if( pOrderBy ){
527 pushOntoSorter(pParse, v, pOrderBy);
528 }else{
529 sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
530 sqliteVdbeAddOp(v, OP_Goto, 0, iBreak);
531 }
532 break;
533 }
534
535 /* Send the data to the callback function.
536 */
537 case SRT_Callback:
538 case SRT_Sorter: {
539 if( pOrderBy ){
540 sqliteVdbeAddOp(v, OP_SortMakeRec, nColumn, 0);
541 pushOntoSorter(pParse, v, pOrderBy);
542 }else{
543 assert( eDest==SRT_Callback );
544 sqliteVdbeAddOp(v, OP_Callback, nColumn, 0);
545 }
546 break;
547 }
548
549 /* Invoke a subroutine to handle the results. The subroutine itself
550 ** is responsible for popping the results off of the stack.
551 */
552 case SRT_Subroutine: {
553 if( pOrderBy ){
554 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
555 pushOntoSorter(pParse, v, pOrderBy);
556 }else{
557 sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
558 }
559 break;
560 }
561
562 /* Discard the results. This is used for SELECT statements inside
563 ** the body of a TRIGGER. The purpose of such selects is to call
564 ** user-defined functions that have side effects. We do not care
565 ** about the actual results of the select.
566 */
567 default: {
568 assert( eDest==SRT_Discard );
569 sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
570 break;
571 }
572 }
573 return 0;
574 }
575
576 /*
577 ** If the inner loop was generated using a non-null pOrderBy argument,
578 ** then the results were placed in a sorter. After the loop is terminated
579 ** we need to run the sorter and output the results. The following
580 ** routine generates the code needed to do that.
581 */
generateSortTail(Select * p,Vdbe * v,int nColumn,int eDest,int iParm)582 static void generateSortTail(
583 Select *p, /* The SELECT statement */
584 Vdbe *v, /* Generate code into this VDBE */
585 int nColumn, /* Number of columns of data */
586 int eDest, /* Write the sorted results here */
587 int iParm /* Optional parameter associated with eDest */
588 ){
589 int end1 = sqliteVdbeMakeLabel(v);
590 int end2 = sqliteVdbeMakeLabel(v);
591 int addr;
592 if( eDest==SRT_Sorter ) return;
593 sqliteVdbeAddOp(v, OP_Sort, 0, 0);
594 addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end1);
595 codeLimiter(v, p, addr, end2, 1);
596 switch( eDest ){
597 case SRT_Callback: {
598 sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
599 break;
600 }
601 case SRT_Table:
602 case SRT_TempTable: {
603 sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
604 sqliteVdbeAddOp(v, OP_Pull, 1, 0);
605 sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
606 break;
607 }
608 case SRT_Set: {
609 assert( nColumn==1 );
610 sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
611 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
612 sqliteVdbeAddOp(v, OP_Goto, 0, sqliteVdbeCurrentAddr(v)+3);
613 sqliteVdbeAddOp(v, OP_String, 0, 0);
614 sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
615 break;
616 }
617 case SRT_Mem: {
618 assert( nColumn==1 );
619 sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
620 sqliteVdbeAddOp(v, OP_Goto, 0, end1);
621 break;
622 }
623 case SRT_Subroutine: {
624 int i;
625 for(i=0; i<nColumn; i++){
626 sqliteVdbeAddOp(v, OP_Column, -1-i, i);
627 }
628 sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
629 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
630 break;
631 }
632 default: {
633 /* Do nothing */
634 break;
635 }
636 }
637 sqliteVdbeAddOp(v, OP_Goto, 0, addr);
638 sqliteVdbeResolveLabel(v, end2);
639 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
640 sqliteVdbeResolveLabel(v, end1);
641 sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
642 }
643
644 /*
645 ** Generate code that will tell the VDBE the datatypes of
646 ** columns in the result set.
647 **
648 ** This routine only generates code if the "PRAGMA show_datatypes=on"
649 ** has been executed. The datatypes are reported out in the azCol
650 ** parameter to the callback function. The first N azCol[] entries
651 ** are the names of the columns, and the second N entries are the
652 ** datatypes for the columns.
653 **
654 ** The "datatype" for a result that is a column of a type is the
655 ** datatype definition extracted from the CREATE TABLE statement.
656 ** The datatype for an expression is either TEXT or NUMERIC. The
657 ** datatype for a ROWID field is INTEGER.
658 */
generateColumnTypes(Parse * pParse,SrcList * pTabList,ExprList * pEList)659 static void generateColumnTypes(
660 Parse *pParse, /* Parser context */
661 SrcList *pTabList, /* List of tables */
662 ExprList *pEList /* Expressions defining the result set */
663 ){
664 Vdbe *v = pParse->pVdbe;
665 int i, j;
666 for(i=0; i<pEList->nExpr; i++){
667 Expr *p = pEList->a[i].pExpr;
668 char *zType = 0;
669 if( p==0 ) continue;
670 if( p->op==TK_COLUMN && pTabList ){
671 Table *pTab;
672 int iCol = p->iColumn;
673 for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
674 assert( j<pTabList->nSrc );
675 pTab = pTabList->a[j].pTab;
676 if( iCol<0 ) iCol = pTab->iPKey;
677 assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
678 if( iCol<0 ){
679 zType = "INTEGER";
680 }else{
681 zType = pTab->aCol[iCol].zType;
682 }
683 }else{
684 if( sqliteExprType(p)==SQLITE_SO_TEXT ){
685 zType = "TEXT";
686 }else{
687 zType = "NUMERIC";
688 }
689 }
690 sqliteVdbeOp3(v, OP_ColumnName, i + pEList->nExpr, 0, zType, 0);
691 }
692 }
693
694 /*
695 ** Generate code that will tell the VDBE the names of columns
696 ** in the result set. This information is used to provide the
697 ** azCol[] values in the callback.
698 */
generateColumnNames(Parse * pParse,SrcList * pTabList,ExprList * pEList)699 static void generateColumnNames(
700 Parse *pParse, /* Parser context */
701 SrcList *pTabList, /* List of tables */
702 ExprList *pEList /* Expressions defining the result set */
703 ){
704 Vdbe *v = pParse->pVdbe;
705 int i, j;
706 sqlite *db = pParse->db;
707 int fullNames, shortNames;
708
709 assert( v!=0 );
710 if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
711 pParse->colNamesSet = 1;
712 fullNames = (db->flags & SQLITE_FullColNames)!=0;
713 shortNames = (db->flags & SQLITE_ShortColNames)!=0;
714 for(i=0; i<pEList->nExpr; i++){
715 Expr *p;
716 int p2 = i==pEList->nExpr-1;
717 p = pEList->a[i].pExpr;
718 if( p==0 ) continue;
719 if( pEList->a[i].zName ){
720 char *zName = pEList->a[i].zName;
721 sqliteVdbeOp3(v, OP_ColumnName, i, p2, zName, 0);
722 continue;
723 }
724 if( p->op==TK_COLUMN && pTabList ){
725 Table *pTab;
726 char *zCol;
727 int iCol = p->iColumn;
728 for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
729 assert( j<pTabList->nSrc );
730 pTab = pTabList->a[j].pTab;
731 if( iCol<0 ) iCol = pTab->iPKey;
732 assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
733 if( iCol<0 ){
734 zCol = "_ROWID_";
735 }else{
736 zCol = pTab->aCol[iCol].zName;
737 }
738 if( !shortNames && !fullNames && p->span.z && p->span.z[0] ){
739 int addr = sqliteVdbeOp3(v,OP_ColumnName, i, p2, p->span.z, p->span.n);
740 sqliteVdbeCompressSpace(v, addr);
741 }else if( fullNames || (!shortNames && pTabList->nSrc>1) ){
742 char *zName = 0;
743 char *zTab;
744
745 zTab = pTabList->a[j].zAlias;
746 if( fullNames || zTab==0 ) zTab = pTab->zName;
747 sqliteSetString(&zName, zTab, ".", zCol, 0);
748 sqliteVdbeOp3(v, OP_ColumnName, i, p2, zName, P3_DYNAMIC);
749 }else{
750 sqliteVdbeOp3(v, OP_ColumnName, i, p2, zCol, 0);
751 }
752 }else if( p->span.z && p->span.z[0] ){
753 int addr = sqliteVdbeOp3(v,OP_ColumnName, i, p2, p->span.z, p->span.n);
754 sqliteVdbeCompressSpace(v, addr);
755 }else{
756 char zName[30];
757 assert( p->op!=TK_COLUMN || pTabList==0 );
758 sprintf(zName, "column%d", i+1);
759 sqliteVdbeOp3(v, OP_ColumnName, i, p2, zName, 0);
760 }
761 }
762 }
763
764 /*
765 ** Name of the connection operator, used for error messages.
766 */
selectOpName(int id)767 static const char *selectOpName(int id){
768 char *z;
769 switch( id ){
770 case TK_ALL: z = "UNION ALL"; break;
771 case TK_INTERSECT: z = "INTERSECT"; break;
772 case TK_EXCEPT: z = "EXCEPT"; break;
773 default: z = "UNION"; break;
774 }
775 return z;
776 }
777
778 /*
779 ** Forward declaration
780 */
781 static int fillInColumnList(Parse*, Select*);
782
783 /*
784 ** Given a SELECT statement, generate a Table structure that describes
785 ** the result set of that SELECT.
786 */
sqliteResultSetOfSelect(Parse * pParse,char * zTabName,Select * pSelect)787 Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
788 Table *pTab;
789 int i, j;
790 ExprList *pEList;
791 Column *aCol;
792
793 if( fillInColumnList(pParse, pSelect) ){
794 return 0;
795 }
796 pTab = sqliteMalloc( sizeof(Table) );
797 if( pTab==0 ){
798 return 0;
799 }
800 pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
801 pEList = pSelect->pEList;
802 pTab->nCol = pEList->nExpr;
803 assert( pTab->nCol>0 );
804 pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
805 for(i=0; i<pTab->nCol; i++){
806 Expr *p, *pR;
807 if( pEList->a[i].zName ){
808 aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
809 }else if( (p=pEList->a[i].pExpr)->op==TK_DOT
810 && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
811 int cnt;
812 sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
813 for(j=cnt=0; j<i; j++){
814 if( sqliteStrICmp(aCol[j].zName, aCol[i].zName)==0 ){
815 int n;
816 char zBuf[30];
817 sprintf(zBuf,"_%d",++cnt);
818 n = strlen(zBuf);
819 sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, zBuf, n,0);
820 j = -1;
821 }
822 }
823 }else if( p->span.z && p->span.z[0] ){
824 sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
825 }else{
826 char zBuf[30];
827 sprintf(zBuf, "column%d", i+1);
828 aCol[i].zName = sqliteStrDup(zBuf);
829 }
830 sqliteDequote(aCol[i].zName);
831 }
832 pTab->iPKey = -1;
833 return pTab;
834 }
835
836 /*
837 ** For the given SELECT statement, do three things.
838 **
839 ** (1) Fill in the pTabList->a[].pTab fields in the SrcList that
840 ** defines the set of tables that should be scanned. For views,
841 ** fill pTabList->a[].pSelect with a copy of the SELECT statement
842 ** that implements the view. A copy is made of the view's SELECT
843 ** statement so that we can freely modify or delete that statement
844 ** without worrying about messing up the presistent representation
845 ** of the view.
846 **
847 ** (2) Add terms to the WHERE clause to accomodate the NATURAL keyword
848 ** on joins and the ON and USING clause of joins.
849 **
850 ** (3) Scan the list of columns in the result set (pEList) looking
851 ** for instances of the "*" operator or the TABLE.* operator.
852 ** If found, expand each "*" to be every column in every table
853 ** and TABLE.* to be every column in TABLE.
854 **
855 ** Return 0 on success. If there are problems, leave an error message
856 ** in pParse and return non-zero.
857 */
fillInColumnList(Parse * pParse,Select * p)858 static int fillInColumnList(Parse *pParse, Select *p){
859 int i, j, k, rc;
860 SrcList *pTabList;
861 ExprList *pEList;
862 Table *pTab;
863
864 if( p==0 || p->pSrc==0 ) return 1;
865 pTabList = p->pSrc;
866 pEList = p->pEList;
867
868 /* Look up every table in the table list.
869 */
870 for(i=0; i<pTabList->nSrc; i++){
871 if( pTabList->a[i].pTab ){
872 /* This routine has run before! No need to continue */
873 return 0;
874 }
875 if( pTabList->a[i].zName==0 ){
876 /* A sub-query in the FROM clause of a SELECT */
877 assert( pTabList->a[i].pSelect!=0 );
878 if( pTabList->a[i].zAlias==0 ){
879 char zFakeName[60];
880 sprintf(zFakeName, "sqlite_subquery_%p_",
881 (void*)pTabList->a[i].pSelect);
882 sqliteSetString(&pTabList->a[i].zAlias, zFakeName, 0);
883 }
884 pTabList->a[i].pTab = pTab =
885 sqliteResultSetOfSelect(pParse, pTabList->a[i].zAlias,
886 pTabList->a[i].pSelect);
887 if( pTab==0 ){
888 return 1;
889 }
890 /* The isTransient flag indicates that the Table structure has been
891 ** dynamically allocated and may be freed at any time. In other words,
892 ** pTab is not pointing to a persistent table structure that defines
893 ** part of the schema. */
894 pTab->isTransient = 1;
895 }else{
896 /* An ordinary table or view name in the FROM clause */
897 pTabList->a[i].pTab = pTab =
898 sqliteLocateTable(pParse,pTabList->a[i].zName,pTabList->a[i].zDatabase);
899 if( pTab==0 ){
900 return 1;
901 }
902 if( pTab->pSelect ){
903 /* We reach here if the named table is a really a view */
904 if( sqliteViewGetColumnNames(pParse, pTab) ){
905 return 1;
906 }
907 /* If pTabList->a[i].pSelect!=0 it means we are dealing with a
908 ** view within a view. The SELECT structure has already been
909 ** copied by the outer view so we can skip the copy step here
910 ** in the inner view.
911 */
912 if( pTabList->a[i].pSelect==0 ){
913 pTabList->a[i].pSelect = sqliteSelectDup(pTab->pSelect);
914 }
915 }
916 }
917 }
918
919 /* Process NATURAL keywords, and ON and USING clauses of joins.
920 */
921 if( sqliteProcessJoin(pParse, p) ) return 1;
922
923 /* For every "*" that occurs in the column list, insert the names of
924 ** all columns in all tables. And for every TABLE.* insert the names
925 ** of all columns in TABLE. The parser inserted a special expression
926 ** with the TK_ALL operator for each "*" that it found in the column list.
927 ** The following code just has to locate the TK_ALL expressions and expand
928 ** each one to the list of all columns in all tables.
929 **
930 ** The first loop just checks to see if there are any "*" operators
931 ** that need expanding.
932 */
933 for(k=0; k<pEList->nExpr; k++){
934 Expr *pE = pEList->a[k].pExpr;
935 if( pE->op==TK_ALL ) break;
936 if( pE->op==TK_DOT && pE->pRight && pE->pRight->op==TK_ALL
937 && pE->pLeft && pE->pLeft->op==TK_ID ) break;
938 }
939 rc = 0;
940 if( k<pEList->nExpr ){
941 /*
942 ** If we get here it means the result set contains one or more "*"
943 ** operators that need to be expanded. Loop through each expression
944 ** in the result set and expand them one by one.
945 */
946 struct ExprList_item *a = pEList->a;
947 ExprList *pNew = 0;
948 for(k=0; k<pEList->nExpr; k++){
949 Expr *pE = a[k].pExpr;
950 if( pE->op!=TK_ALL &&
951 (pE->op!=TK_DOT || pE->pRight==0 || pE->pRight->op!=TK_ALL) ){
952 /* This particular expression does not need to be expanded.
953 */
954 pNew = sqliteExprListAppend(pNew, a[k].pExpr, 0);
955 pNew->a[pNew->nExpr-1].zName = a[k].zName;
956 a[k].pExpr = 0;
957 a[k].zName = 0;
958 }else{
959 /* This expression is a "*" or a "TABLE.*" and needs to be
960 ** expanded. */
961 int tableSeen = 0; /* Set to 1 when TABLE matches */
962 char *zTName; /* text of name of TABLE */
963 if( pE->op==TK_DOT && pE->pLeft ){
964 zTName = sqliteTableNameFromToken(&pE->pLeft->token);
965 }else{
966 zTName = 0;
967 }
968 for(i=0; i<pTabList->nSrc; i++){
969 Table *pTab = pTabList->a[i].pTab;
970 char *zTabName = pTabList->a[i].zAlias;
971 if( zTabName==0 || zTabName[0]==0 ){
972 zTabName = pTab->zName;
973 }
974 if( zTName && (zTabName==0 || zTabName[0]==0 ||
975 sqliteStrICmp(zTName, zTabName)!=0) ){
976 continue;
977 }
978 tableSeen = 1;
979 for(j=0; j<pTab->nCol; j++){
980 Expr *pExpr, *pLeft, *pRight;
981 char *zName = pTab->aCol[j].zName;
982
983 if( i>0 && (pTabList->a[i-1].jointype & JT_NATURAL)!=0 &&
984 columnIndex(pTabList->a[i-1].pTab, zName)>=0 ){
985 /* In a NATURAL join, omit the join columns from the
986 ** table on the right */
987 continue;
988 }
989 if( i>0 && sqliteIdListIndex(pTabList->a[i-1].pUsing, zName)>=0 ){
990 /* In a join with a USING clause, omit columns in the
991 ** using clause from the table on the right. */
992 continue;
993 }
994 pRight = sqliteExpr(TK_ID, 0, 0, 0);
995 if( pRight==0 ) break;
996 pRight->token.z = zName;
997 pRight->token.n = strlen(zName);
998 pRight->token.dyn = 0;
999 if( zTabName && pTabList->nSrc>1 ){
1000 pLeft = sqliteExpr(TK_ID, 0, 0, 0);
1001 pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
1002 if( pExpr==0 ) break;
1003 pLeft->token.z = zTabName;
1004 pLeft->token.n = strlen(zTabName);
1005 pLeft->token.dyn = 0;
1006 sqliteSetString((char**)&pExpr->span.z, zTabName, ".", zName, 0);
1007 pExpr->span.n = strlen(pExpr->span.z);
1008 pExpr->span.dyn = 1;
1009 pExpr->token.z = 0;
1010 pExpr->token.n = 0;
1011 pExpr->token.dyn = 0;
1012 }else{
1013 pExpr = pRight;
1014 pExpr->span = pExpr->token;
1015 }
1016 pNew = sqliteExprListAppend(pNew, pExpr, 0);
1017 }
1018 }
1019 if( !tableSeen ){
1020 if( zTName ){
1021 sqliteErrorMsg(pParse, "no such table: %s", zTName);
1022 }else{
1023 sqliteErrorMsg(pParse, "no tables specified");
1024 }
1025 rc = 1;
1026 }
1027 sqliteFree(zTName);
1028 }
1029 }
1030 sqliteExprListDelete(pEList);
1031 p->pEList = pNew;
1032 }
1033 return rc;
1034 }
1035
1036 /*
1037 ** This routine recursively unlinks the Select.pSrc.a[].pTab pointers
1038 ** in a select structure. It just sets the pointers to NULL. This
1039 ** routine is recursive in the sense that if the Select.pSrc.a[].pSelect
1040 ** pointer is not NULL, this routine is called recursively on that pointer.
1041 **
1042 ** This routine is called on the Select structure that defines a
1043 ** VIEW in order to undo any bindings to tables. This is necessary
1044 ** because those tables might be DROPed by a subsequent SQL command.
1045 ** If the bindings are not removed, then the Select.pSrc->a[].pTab field
1046 ** will be left pointing to a deallocated Table structure after the
1047 ** DROP and a coredump will occur the next time the VIEW is used.
1048 */
sqliteSelectUnbind(Select * p)1049 void sqliteSelectUnbind(Select *p){
1050 int i;
1051 SrcList *pSrc = p->pSrc;
1052 Table *pTab;
1053 if( p==0 ) return;
1054 for(i=0; i<pSrc->nSrc; i++){
1055 if( (pTab = pSrc->a[i].pTab)!=0 ){
1056 if( pTab->isTransient ){
1057 sqliteDeleteTable(0, pTab);
1058 }
1059 pSrc->a[i].pTab = 0;
1060 if( pSrc->a[i].pSelect ){
1061 sqliteSelectUnbind(pSrc->a[i].pSelect);
1062 }
1063 }
1064 }
1065 }
1066
1067 /*
1068 ** This routine associates entries in an ORDER BY expression list with
1069 ** columns in a result. For each ORDER BY expression, the opcode of
1070 ** the top-level node is changed to TK_COLUMN and the iColumn value of
1071 ** the top-level node is filled in with column number and the iTable
1072 ** value of the top-level node is filled with iTable parameter.
1073 **
1074 ** If there are prior SELECT clauses, they are processed first. A match
1075 ** in an earlier SELECT takes precedence over a later SELECT.
1076 **
1077 ** Any entry that does not match is flagged as an error. The number
1078 ** of errors is returned.
1079 **
1080 ** This routine does NOT correctly initialize the Expr.dataType field
1081 ** of the ORDER BY expressions. The multiSelectSortOrder() routine
1082 ** must be called to do that after the individual select statements
1083 ** have all been analyzed. This routine is unable to compute Expr.dataType
1084 ** because it must be called before the individual select statements
1085 ** have been analyzed.
1086 */
matchOrderbyToColumn(Parse * pParse,Select * pSelect,ExprList * pOrderBy,int iTable,int mustComplete)1087 static int matchOrderbyToColumn(
1088 Parse *pParse, /* A place to leave error messages */
1089 Select *pSelect, /* Match to result columns of this SELECT */
1090 ExprList *pOrderBy, /* The ORDER BY values to match against columns */
1091 int iTable, /* Insert this value in iTable */
1092 int mustComplete /* If TRUE all ORDER BYs must match */
1093 ){
1094 int nErr = 0;
1095 int i, j;
1096 ExprList *pEList;
1097
1098 if( pSelect==0 || pOrderBy==0 ) return 1;
1099 if( mustComplete ){
1100 for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].done = 0; }
1101 }
1102 if( fillInColumnList(pParse, pSelect) ){
1103 return 1;
1104 }
1105 if( pSelect->pPrior ){
1106 if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
1107 return 1;
1108 }
1109 }
1110 pEList = pSelect->pEList;
1111 for(i=0; i<pOrderBy->nExpr; i++){
1112 Expr *pE = pOrderBy->a[i].pExpr;
1113 int iCol = -1;
1114 if( pOrderBy->a[i].done ) continue;
1115 if( sqliteExprIsInteger(pE, &iCol) ){
1116 if( iCol<=0 || iCol>pEList->nExpr ){
1117 sqliteErrorMsg(pParse,
1118 "ORDER BY position %d should be between 1 and %d",
1119 iCol, pEList->nExpr);
1120 nErr++;
1121 break;
1122 }
1123 if( !mustComplete ) continue;
1124 iCol--;
1125 }
1126 for(j=0; iCol<0 && j<pEList->nExpr; j++){
1127 if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
1128 char *zName, *zLabel;
1129 zName = pEList->a[j].zName;
1130 assert( pE->token.z );
1131 zLabel = sqliteStrNDup(pE->token.z, pE->token.n);
1132 sqliteDequote(zLabel);
1133 if( sqliteStrICmp(zName, zLabel)==0 ){
1134 iCol = j;
1135 }
1136 sqliteFree(zLabel);
1137 }
1138 if( iCol<0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
1139 iCol = j;
1140 }
1141 }
1142 if( iCol>=0 ){
1143 pE->op = TK_COLUMN;
1144 pE->iColumn = iCol;
1145 pE->iTable = iTable;
1146 pOrderBy->a[i].done = 1;
1147 }
1148 if( iCol<0 && mustComplete ){
1149 sqliteErrorMsg(pParse,
1150 "ORDER BY term number %d does not match any result column", i+1);
1151 nErr++;
1152 break;
1153 }
1154 }
1155 return nErr;
1156 }
1157
1158 /*
1159 ** Get a VDBE for the given parser context. Create a new one if necessary.
1160 ** If an error occurs, return NULL and leave a message in pParse.
1161 */
sqliteGetVdbe(Parse * pParse)1162 Vdbe *sqliteGetVdbe(Parse *pParse){
1163 Vdbe *v = pParse->pVdbe;
1164 if( v==0 ){
1165 v = pParse->pVdbe = sqliteVdbeCreate(pParse->db);
1166 }
1167 return v;
1168 }
1169
1170 /*
1171 ** This routine sets the Expr.dataType field on all elements of
1172 ** the pOrderBy expression list. The pOrderBy list will have been
1173 ** set up by matchOrderbyToColumn(). Hence each expression has
1174 ** a TK_COLUMN as its root node. The Expr.iColumn refers to a
1175 ** column in the result set. The datatype is set to SQLITE_SO_TEXT
1176 ** if the corresponding column in p and every SELECT to the left of
1177 ** p has a datatype of SQLITE_SO_TEXT. If the cooressponding column
1178 ** in p or any of the left SELECTs is SQLITE_SO_NUM, then the datatype
1179 ** of the order-by expression is set to SQLITE_SO_NUM.
1180 **
1181 ** Examples:
1182 **
1183 ** CREATE TABLE one(a INTEGER, b TEXT);
1184 ** CREATE TABLE two(c VARCHAR(5), d FLOAT);
1185 **
1186 ** SELECT b, b FROM one UNION SELECT d, c FROM two ORDER BY 1, 2;
1187 **
1188 ** The primary sort key will use SQLITE_SO_NUM because the "d" in
1189 ** the second SELECT is numeric. The 1st column of the first SELECT
1190 ** is text but that does not matter because a numeric always overrides
1191 ** a text.
1192 **
1193 ** The secondary key will use the SQLITE_SO_TEXT sort order because
1194 ** both the (second) "b" in the first SELECT and the "c" in the second
1195 ** SELECT have a datatype of text.
1196 */
multiSelectSortOrder(Select * p,ExprList * pOrderBy)1197 static void multiSelectSortOrder(Select *p, ExprList *pOrderBy){
1198 int i;
1199 ExprList *pEList;
1200 if( pOrderBy==0 ) return;
1201 if( p==0 ){
1202 for(i=0; i<pOrderBy->nExpr; i++){
1203 pOrderBy->a[i].pExpr->dataType = SQLITE_SO_TEXT;
1204 }
1205 return;
1206 }
1207 multiSelectSortOrder(p->pPrior, pOrderBy);
1208 pEList = p->pEList;
1209 for(i=0; i<pOrderBy->nExpr; i++){
1210 Expr *pE = pOrderBy->a[i].pExpr;
1211 if( pE->dataType==SQLITE_SO_NUM ) continue;
1212 assert( pE->iColumn>=0 );
1213 if( pEList->nExpr>pE->iColumn ){
1214 pE->dataType = sqliteExprType(pEList->a[pE->iColumn].pExpr);
1215 }
1216 }
1217 }
1218
1219 /*
1220 ** Compute the iLimit and iOffset fields of the SELECT based on the
1221 ** nLimit and nOffset fields. nLimit and nOffset hold the integers
1222 ** that appear in the original SQL statement after the LIMIT and OFFSET
1223 ** keywords. Or that hold -1 and 0 if those keywords are omitted.
1224 ** iLimit and iOffset are the integer memory register numbers for
1225 ** counters used to compute the limit and offset. If there is no
1226 ** limit and/or offset, then iLimit and iOffset are negative.
1227 **
1228 ** This routine changes the values if iLimit and iOffset only if
1229 ** a limit or offset is defined by nLimit and nOffset. iLimit and
1230 ** iOffset should have been preset to appropriate default values
1231 ** (usually but not always -1) prior to calling this routine.
1232 ** Only if nLimit>=0 or nOffset>0 do the limit registers get
1233 ** redefined. The UNION ALL operator uses this property to force
1234 ** the reuse of the same limit and offset registers across multiple
1235 ** SELECT statements.
1236 */
computeLimitRegisters(Parse * pParse,Select * p)1237 static void computeLimitRegisters(Parse *pParse, Select *p){
1238 /*
1239 ** If the comparison is p->nLimit>0 then "LIMIT 0" shows
1240 ** all rows. It is the same as no limit. If the comparision is
1241 ** p->nLimit>=0 then "LIMIT 0" show no rows at all.
1242 ** "LIMIT -1" always shows all rows. There is some
1243 ** contraversy about what the correct behavior should be.
1244 ** The current implementation interprets "LIMIT 0" to mean
1245 ** no rows.
1246 */
1247 if( p->nLimit>=0 ){
1248 int iMem = pParse->nMem++;
1249 Vdbe *v = sqliteGetVdbe(pParse);
1250 if( v==0 ) return;
1251 sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
1252 sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
1253 p->iLimit = iMem;
1254 }
1255 if( p->nOffset>0 ){
1256 int iMem = pParse->nMem++;
1257 Vdbe *v = sqliteGetVdbe(pParse);
1258 if( v==0 ) return;
1259 sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
1260 sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
1261 p->iOffset = iMem;
1262 }
1263 }
1264
1265 /*
1266 ** This routine is called to process a query that is really the union
1267 ** or intersection of two or more separate queries.
1268 **
1269 ** "p" points to the right-most of the two queries. the query on the
1270 ** left is p->pPrior. The left query could also be a compound query
1271 ** in which case this routine will be called recursively.
1272 **
1273 ** The results of the total query are to be written into a destination
1274 ** of type eDest with parameter iParm.
1275 **
1276 ** Example 1: Consider a three-way compound SQL statement.
1277 **
1278 ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
1279 **
1280 ** This statement is parsed up as follows:
1281 **
1282 ** SELECT c FROM t3
1283 ** |
1284 ** `-----> SELECT b FROM t2
1285 ** |
1286 ** `------> SELECT a FROM t1
1287 **
1288 ** The arrows in the diagram above represent the Select.pPrior pointer.
1289 ** So if this routine is called with p equal to the t3 query, then
1290 ** pPrior will be the t2 query. p->op will be TK_UNION in this case.
1291 **
1292 ** Notice that because of the way SQLite parses compound SELECTs, the
1293 ** individual selects always group from left to right.
1294 */
multiSelect(Parse * pParse,Select * p,int eDest,int iParm)1295 static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){
1296 int rc; /* Success code from a subroutine */
1297 Select *pPrior; /* Another SELECT immediately to our left */
1298 Vdbe *v; /* Generate code to this VDBE */
1299
1300 /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
1301 ** the last SELECT in the series may have an ORDER BY or LIMIT.
1302 */
1303 if( p==0 || p->pPrior==0 ) return 1;
1304 pPrior = p->pPrior;
1305 if( pPrior->pOrderBy ){
1306 sqliteErrorMsg(pParse,"ORDER BY clause should come after %s not before",
1307 selectOpName(p->op));
1308 return 1;
1309 }
1310 if( pPrior->nLimit>=0 || pPrior->nOffset>0 ){
1311 sqliteErrorMsg(pParse,"LIMIT clause should come after %s not before",
1312 selectOpName(p->op));
1313 return 1;
1314 }
1315
1316 /* Make sure we have a valid query engine. If not, create a new one.
1317 */
1318 v = sqliteGetVdbe(pParse);
1319 if( v==0 ) return 1;
1320
1321 /* Create the destination temporary table if necessary
1322 */
1323 if( eDest==SRT_TempTable ){
1324 sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
1325 eDest = SRT_Table;
1326 }
1327
1328 /* Generate code for the left and right SELECT statements.
1329 */
1330 switch( p->op ){
1331 case TK_ALL: {
1332 if( p->pOrderBy==0 ){
1333 pPrior->nLimit = p->nLimit;
1334 pPrior->nOffset = p->nOffset;
1335 rc = sqliteSelect(pParse, pPrior, eDest, iParm, 0, 0, 0);
1336 if( rc ) return rc;
1337 p->pPrior = 0;
1338 p->iLimit = pPrior->iLimit;
1339 p->iOffset = pPrior->iOffset;
1340 p->nLimit = -1;
1341 p->nOffset = 0;
1342 rc = sqliteSelect(pParse, p, eDest, iParm, 0, 0, 0);
1343 p->pPrior = pPrior;
1344 if( rc ) return rc;
1345 break;
1346 }
1347 /* For UNION ALL ... ORDER BY fall through to the next case */
1348 }
1349 case TK_EXCEPT:
1350 case TK_UNION: {
1351 int unionTab; /* Cursor number of the temporary table holding result */
1352 int op; /* One of the SRT_ operations to apply to self */
1353 int priorOp; /* The SRT_ operation to apply to prior selects */
1354 int nLimit, nOffset; /* Saved values of p->nLimit and p->nOffset */
1355 ExprList *pOrderBy; /* The ORDER BY clause for the right SELECT */
1356
1357 priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
1358 if( eDest==priorOp && p->pOrderBy==0 && p->nLimit<0 && p->nOffset==0 ){
1359 /* We can reuse a temporary table generated by a SELECT to our
1360 ** right.
1361 */
1362 unionTab = iParm;
1363 }else{
1364 /* We will need to create our own temporary table to hold the
1365 ** intermediate results.
1366 */
1367 unionTab = pParse->nTab++;
1368 if( p->pOrderBy
1369 && matchOrderbyToColumn(pParse, p, p->pOrderBy, unionTab, 1) ){
1370 return 1;
1371 }
1372 if( p->op!=TK_ALL ){
1373 sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 1);
1374 sqliteVdbeAddOp(v, OP_KeyAsData, unionTab, 1);
1375 }else{
1376 sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 0);
1377 }
1378 }
1379
1380 /* Code the SELECT statements to our left
1381 */
1382 rc = sqliteSelect(pParse, pPrior, priorOp, unionTab, 0, 0, 0);
1383 if( rc ) return rc;
1384
1385 /* Code the current SELECT statement
1386 */
1387 switch( p->op ){
1388 case TK_EXCEPT: op = SRT_Except; break;
1389 case TK_UNION: op = SRT_Union; break;
1390 case TK_ALL: op = SRT_Table; break;
1391 }
1392 p->pPrior = 0;
1393 pOrderBy = p->pOrderBy;
1394 p->pOrderBy = 0;
1395 nLimit = p->nLimit;
1396 p->nLimit = -1;
1397 nOffset = p->nOffset;
1398 p->nOffset = 0;
1399 rc = sqliteSelect(pParse, p, op, unionTab, 0, 0, 0);
1400 p->pPrior = pPrior;
1401 p->pOrderBy = pOrderBy;
1402 p->nLimit = nLimit;
1403 p->nOffset = nOffset;
1404 if( rc ) return rc;
1405
1406 /* Convert the data in the temporary table into whatever form
1407 ** it is that we currently need.
1408 */
1409 if( eDest!=priorOp || unionTab!=iParm ){
1410 int iCont, iBreak, iStart;
1411 assert( p->pEList );
1412 if( eDest==SRT_Callback ){
1413 generateColumnNames(pParse, 0, p->pEList);
1414 generateColumnTypes(pParse, p->pSrc, p->pEList);
1415 }
1416 iBreak = sqliteVdbeMakeLabel(v);
1417 iCont = sqliteVdbeMakeLabel(v);
1418 sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
1419 computeLimitRegisters(pParse, p);
1420 iStart = sqliteVdbeCurrentAddr(v);
1421 multiSelectSortOrder(p, p->pOrderBy);
1422 rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
1423 p->pOrderBy, -1, eDest, iParm,
1424 iCont, iBreak);
1425 if( rc ) return 1;
1426 sqliteVdbeResolveLabel(v, iCont);
1427 sqliteVdbeAddOp(v, OP_Next, unionTab, iStart);
1428 sqliteVdbeResolveLabel(v, iBreak);
1429 sqliteVdbeAddOp(v, OP_Close, unionTab, 0);
1430 if( p->pOrderBy ){
1431 generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
1432 }
1433 }
1434 break;
1435 }
1436 case TK_INTERSECT: {
1437 int tab1, tab2;
1438 int iCont, iBreak, iStart;
1439 int nLimit, nOffset;
1440
1441 /* INTERSECT is different from the others since it requires
1442 ** two temporary tables. Hence it has its own case. Begin
1443 ** by allocating the tables we will need.
1444 */
1445 tab1 = pParse->nTab++;
1446 tab2 = pParse->nTab++;
1447 if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
1448 return 1;
1449 }
1450 sqliteVdbeAddOp(v, OP_OpenTemp, tab1, 1);
1451 sqliteVdbeAddOp(v, OP_KeyAsData, tab1, 1);
1452
1453 /* Code the SELECTs to our left into temporary table "tab1".
1454 */
1455 rc = sqliteSelect(pParse, pPrior, SRT_Union, tab1, 0, 0, 0);
1456 if( rc ) return rc;
1457
1458 /* Code the current SELECT into temporary table "tab2"
1459 */
1460 sqliteVdbeAddOp(v, OP_OpenTemp, tab2, 1);
1461 sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1);
1462 p->pPrior = 0;
1463 nLimit = p->nLimit;
1464 p->nLimit = -1;
1465 nOffset = p->nOffset;
1466 p->nOffset = 0;
1467 rc = sqliteSelect(pParse, p, SRT_Union, tab2, 0, 0, 0);
1468 p->pPrior = pPrior;
1469 p->nLimit = nLimit;
1470 p->nOffset = nOffset;
1471 if( rc ) return rc;
1472
1473 /* Generate code to take the intersection of the two temporary
1474 ** tables.
1475 */
1476 assert( p->pEList );
1477 if( eDest==SRT_Callback ){
1478 generateColumnNames(pParse, 0, p->pEList);
1479 generateColumnTypes(pParse, p->pSrc, p->pEList);
1480 }
1481 iBreak = sqliteVdbeMakeLabel(v);
1482 iCont = sqliteVdbeMakeLabel(v);
1483 sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
1484 computeLimitRegisters(pParse, p);
1485 iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
1486 sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
1487 multiSelectSortOrder(p, p->pOrderBy);
1488 rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
1489 p->pOrderBy, -1, eDest, iParm,
1490 iCont, iBreak);
1491 if( rc ) return 1;
1492 sqliteVdbeResolveLabel(v, iCont);
1493 sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
1494 sqliteVdbeResolveLabel(v, iBreak);
1495 sqliteVdbeAddOp(v, OP_Close, tab2, 0);
1496 sqliteVdbeAddOp(v, OP_Close, tab1, 0);
1497 if( p->pOrderBy ){
1498 generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
1499 }
1500 break;
1501 }
1502 }
1503 assert( p->pEList && pPrior->pEList );
1504 if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
1505 sqliteErrorMsg(pParse, "SELECTs to the left and right of %s"
1506 " do not have the same number of result columns", selectOpName(p->op));
1507 return 1;
1508 }
1509 return 0;
1510 }
1511
1512 /*
1513 ** Scan through the expression pExpr. Replace every reference to
1514 ** a column in table number iTable with a copy of the iColumn-th
1515 ** entry in pEList. (But leave references to the ROWID column
1516 ** unchanged.)
1517 **
1518 ** This routine is part of the flattening procedure. A subquery
1519 ** whose result set is defined by pEList appears as entry in the
1520 ** FROM clause of a SELECT such that the VDBE cursor assigned to that
1521 ** FORM clause entry is iTable. This routine make the necessary
1522 ** changes to pExpr so that it refers directly to the source table
1523 ** of the subquery rather the result set of the subquery.
1524 */
1525 static void substExprList(ExprList*,int,ExprList*); /* Forward Decl */
substExpr(Expr * pExpr,int iTable,ExprList * pEList)1526 static void substExpr(Expr *pExpr, int iTable, ExprList *pEList){
1527 if( pExpr==0 ) return;
1528 if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
1529 if( pExpr->iColumn<0 ){
1530 pExpr->op = TK_NULL;
1531 }else{
1532 Expr *pNew;
1533 assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
1534 assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
1535 pNew = pEList->a[pExpr->iColumn].pExpr;
1536 assert( pNew!=0 );
1537 pExpr->op = pNew->op;
1538 pExpr->dataType = pNew->dataType;
1539 assert( pExpr->pLeft==0 );
1540 pExpr->pLeft = sqliteExprDup(pNew->pLeft);
1541 assert( pExpr->pRight==0 );
1542 pExpr->pRight = sqliteExprDup(pNew->pRight);
1543 assert( pExpr->pList==0 );
1544 pExpr->pList = sqliteExprListDup(pNew->pList);
1545 pExpr->iTable = pNew->iTable;
1546 pExpr->iColumn = pNew->iColumn;
1547 pExpr->iAgg = pNew->iAgg;
1548 sqliteTokenCopy(&pExpr->token, &pNew->token);
1549 sqliteTokenCopy(&pExpr->span, &pNew->span);
1550 }
1551 }else{
1552 substExpr(pExpr->pLeft, iTable, pEList);
1553 substExpr(pExpr->pRight, iTable, pEList);
1554 substExprList(pExpr->pList, iTable, pEList);
1555 }
1556 }
1557 static void
substExprList(ExprList * pList,int iTable,ExprList * pEList)1558 substExprList(ExprList *pList, int iTable, ExprList *pEList){
1559 int i;
1560 if( pList==0 ) return;
1561 for(i=0; i<pList->nExpr; i++){
1562 substExpr(pList->a[i].pExpr, iTable, pEList);
1563 }
1564 }
1565
1566 /*
1567 ** This routine attempts to flatten subqueries in order to speed
1568 ** execution. It returns 1 if it makes changes and 0 if no flattening
1569 ** occurs.
1570 **
1571 ** To understand the concept of flattening, consider the following
1572 ** query:
1573 **
1574 ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
1575 **
1576 ** The default way of implementing this query is to execute the
1577 ** subquery first and store the results in a temporary table, then
1578 ** run the outer query on that temporary table. This requires two
1579 ** passes over the data. Furthermore, because the temporary table
1580 ** has no indices, the WHERE clause on the outer query cannot be
1581 ** optimized.
1582 **
1583 ** This routine attempts to rewrite queries such as the above into
1584 ** a single flat select, like this:
1585 **
1586 ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
1587 **
1588 ** The code generated for this simpification gives the same result
1589 ** but only has to scan the data once. And because indices might
1590 ** exist on the table t1, a complete scan of the data might be
1591 ** avoided.
1592 **
1593 ** Flattening is only attempted if all of the following are true:
1594 **
1595 ** (1) The subquery and the outer query do not both use aggregates.
1596 **
1597 ** (2) The subquery is not an aggregate or the outer query is not a join.
1598 **
1599 ** (3) The subquery is not the right operand of a left outer join, or
1600 ** the subquery is not itself a join. (Ticket #306)
1601 **
1602 ** (4) The subquery is not DISTINCT or the outer query is not a join.
1603 **
1604 ** (5) The subquery is not DISTINCT or the outer query does not use
1605 ** aggregates.
1606 **
1607 ** (6) The subquery does not use aggregates or the outer query is not
1608 ** DISTINCT.
1609 **
1610 ** (7) The subquery has a FROM clause.
1611 **
1612 ** (8) The subquery does not use LIMIT or the outer query is not a join.
1613 **
1614 ** (9) The subquery does not use LIMIT or the outer query does not use
1615 ** aggregates.
1616 **
1617 ** (10) The subquery does not use aggregates or the outer query does not
1618 ** use LIMIT.
1619 **
1620 ** (11) The subquery and the outer query do not both have ORDER BY clauses.
1621 **
1622 ** (12) The subquery is not the right term of a LEFT OUTER JOIN or the
1623 ** subquery has no WHERE clause. (added by ticket #350)
1624 **
1625 ** In this routine, the "p" parameter is a pointer to the outer query.
1626 ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
1627 ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
1628 **
1629 ** If flattening is not attempted, this routine is a no-op and returns 0.
1630 ** If flattening is attempted this routine returns 1.
1631 **
1632 ** All of the expression analysis must occur on both the outer query and
1633 ** the subquery before this routine runs.
1634 */
flattenSubquery(Parse * pParse,Select * p,int iFrom,int isAgg,int subqueryIsAgg)1635 static int flattenSubquery(
1636 Parse *pParse, /* The parsing context */
1637 Select *p, /* The parent or outer SELECT statement */
1638 int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
1639 int isAgg, /* True if outer SELECT uses aggregate functions */
1640 int subqueryIsAgg /* True if the subquery uses aggregate functions */
1641 ){
1642 Select *pSub; /* The inner query or "subquery" */
1643 SrcList *pSrc; /* The FROM clause of the outer query */
1644 SrcList *pSubSrc; /* The FROM clause of the subquery */
1645 ExprList *pList; /* The result set of the outer query */
1646 int iParent; /* VDBE cursor number of the pSub result set temp table */
1647 int i;
1648 Expr *pWhere;
1649
1650 /* Check to see if flattening is permitted. Return 0 if not.
1651 */
1652 if( p==0 ) return 0;
1653 pSrc = p->pSrc;
1654 assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
1655 pSub = pSrc->a[iFrom].pSelect;
1656 assert( pSub!=0 );
1657 if( isAgg && subqueryIsAgg ) return 0;
1658 if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
1659 pSubSrc = pSub->pSrc;
1660 assert( pSubSrc );
1661 if( pSubSrc->nSrc==0 ) return 0;
1662 if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){
1663 return 0;
1664 }
1665 if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
1666 if( p->pOrderBy && pSub->pOrderBy ) return 0;
1667
1668 /* Restriction 3: If the subquery is a join, make sure the subquery is
1669 ** not used as the right operand of an outer join. Examples of why this
1670 ** is not allowed:
1671 **
1672 ** t1 LEFT OUTER JOIN (t2 JOIN t3)
1673 **
1674 ** If we flatten the above, we would get
1675 **
1676 ** (t1 LEFT OUTER JOIN t2) JOIN t3
1677 **
1678 ** which is not at all the same thing.
1679 */
1680 if( pSubSrc->nSrc>1 && iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 ){
1681 return 0;
1682 }
1683
1684 /* Restriction 12: If the subquery is the right operand of a left outer
1685 ** join, make sure the subquery has no WHERE clause.
1686 ** An examples of why this is not allowed:
1687 **
1688 ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
1689 **
1690 ** If we flatten the above, we would get
1691 **
1692 ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
1693 **
1694 ** But the t2.x>0 test will always fail on a NULL row of t2, which
1695 ** effectively converts the OUTER JOIN into an INNER JOIN.
1696 */
1697 if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0
1698 && pSub->pWhere!=0 ){
1699 return 0;
1700 }
1701
1702 /* If we reach this point, it means flattening is permitted for the
1703 ** iFrom-th entry of the FROM clause in the outer query.
1704 */
1705
1706 /* Move all of the FROM elements of the subquery into the
1707 ** the FROM clause of the outer query. Before doing this, remember
1708 ** the cursor number for the original outer query FROM element in
1709 ** iParent. The iParent cursor will never be used. Subsequent code
1710 ** will scan expressions looking for iParent references and replace
1711 ** those references with expressions that resolve to the subquery FROM
1712 ** elements we are now copying in.
1713 */
1714 iParent = pSrc->a[iFrom].iCursor;
1715 {
1716 int nSubSrc = pSubSrc->nSrc;
1717 int jointype = pSrc->a[iFrom].jointype;
1718
1719 if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
1720 sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
1721 }
1722 sqliteFree(pSrc->a[iFrom].zDatabase);
1723 sqliteFree(pSrc->a[iFrom].zName);
1724 sqliteFree(pSrc->a[iFrom].zAlias);
1725 if( nSubSrc>1 ){
1726 int extra = nSubSrc - 1;
1727 for(i=1; i<nSubSrc; i++){
1728 pSrc = sqliteSrcListAppend(pSrc, 0, 0);
1729 }
1730 p->pSrc = pSrc;
1731 for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){
1732 pSrc->a[i] = pSrc->a[i-extra];
1733 }
1734 }
1735 for(i=0; i<nSubSrc; i++){
1736 pSrc->a[i+iFrom] = pSubSrc->a[i];
1737 memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
1738 }
1739 pSrc->a[iFrom+nSubSrc-1].jointype = jointype;
1740 }
1741
1742 /* Now begin substituting subquery result set expressions for
1743 ** references to the iParent in the outer query.
1744 **
1745 ** Example:
1746 **
1747 ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
1748 ** \ \_____________ subquery __________/ /
1749 ** \_____________________ outer query ______________________________/
1750 **
1751 ** We look at every expression in the outer query and every place we see
1752 ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
1753 */
1754 substExprList(p->pEList, iParent, pSub->pEList);
1755 pList = p->pEList;
1756 for(i=0; i<pList->nExpr; i++){
1757 Expr *pExpr;
1758 if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
1759 pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
1760 }
1761 }
1762 if( isAgg ){
1763 substExprList(p->pGroupBy, iParent, pSub->pEList);
1764 substExpr(p->pHaving, iParent, pSub->pEList);
1765 }
1766 if( pSub->pOrderBy ){
1767 assert( p->pOrderBy==0 );
1768 p->pOrderBy = pSub->pOrderBy;
1769 pSub->pOrderBy = 0;
1770 }else if( p->pOrderBy ){
1771 substExprList(p->pOrderBy, iParent, pSub->pEList);
1772 }
1773 if( pSub->pWhere ){
1774 pWhere = sqliteExprDup(pSub->pWhere);
1775 }else{
1776 pWhere = 0;
1777 }
1778 if( subqueryIsAgg ){
1779 assert( p->pHaving==0 );
1780 p->pHaving = p->pWhere;
1781 p->pWhere = pWhere;
1782 substExpr(p->pHaving, iParent, pSub->pEList);
1783 if( pSub->pHaving ){
1784 Expr *pHaving = sqliteExprDup(pSub->pHaving);
1785 if( p->pHaving ){
1786 p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
1787 }else{
1788 p->pHaving = pHaving;
1789 }
1790 }
1791 assert( p->pGroupBy==0 );
1792 p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
1793 }else if( p->pWhere==0 ){
1794 p->pWhere = pWhere;
1795 }else{
1796 substExpr(p->pWhere, iParent, pSub->pEList);
1797 if( pWhere ){
1798 p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
1799 }
1800 }
1801
1802 /* The flattened query is distinct if either the inner or the
1803 ** outer query is distinct.
1804 */
1805 p->isDistinct = p->isDistinct || pSub->isDistinct;
1806
1807 /* Transfer the limit expression from the subquery to the outer
1808 ** query.
1809 */
1810 if( pSub->nLimit>=0 ){
1811 if( p->nLimit<0 ){
1812 p->nLimit = pSub->nLimit;
1813 }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
1814 p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
1815 }
1816 }
1817 p->nOffset += pSub->nOffset;
1818
1819 /* Finially, delete what is left of the subquery and return
1820 ** success.
1821 */
1822 sqliteSelectDelete(pSub);
1823 return 1;
1824 }
1825
1826 /*
1827 ** Analyze the SELECT statement passed in as an argument to see if it
1828 ** is a simple min() or max() query. If it is and this query can be
1829 ** satisfied using a single seek to the beginning or end of an index,
1830 ** then generate the code for this SELECT and return 1. If this is not a
1831 ** simple min() or max() query, then return 0;
1832 **
1833 ** A simply min() or max() query looks like this:
1834 **
1835 ** SELECT min(a) FROM table;
1836 ** SELECT max(a) FROM table;
1837 **
1838 ** The query may have only a single table in its FROM argument. There
1839 ** can be no GROUP BY or HAVING or WHERE clauses. The result set must
1840 ** be the min() or max() of a single column of the table. The column
1841 ** in the min() or max() function must be indexed.
1842 **
1843 ** The parameters to this routine are the same as for sqliteSelect().
1844 ** See the header comment on that routine for additional information.
1845 */
simpleMinMaxQuery(Parse * pParse,Select * p,int eDest,int iParm)1846 static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){
1847 Expr *pExpr;
1848 int iCol;
1849 Table *pTab;
1850 Index *pIdx;
1851 int base;
1852 Vdbe *v;
1853 int seekOp;
1854 int cont;
1855 ExprList *pEList, *pList, eList;
1856 struct ExprList_item eListItem;
1857 SrcList *pSrc;
1858
1859
1860 /* Check to see if this query is a simple min() or max() query. Return
1861 ** zero if it is not.
1862 */
1863 if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
1864 pSrc = p->pSrc;
1865 if( pSrc->nSrc!=1 ) return 0;
1866 pEList = p->pEList;
1867 if( pEList->nExpr!=1 ) return 0;
1868 pExpr = pEList->a[0].pExpr;
1869 if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
1870 pList = pExpr->pList;
1871 if( pList==0 || pList->nExpr!=1 ) return 0;
1872 if( pExpr->token.n!=3 ) return 0;
1873 if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
1874 seekOp = OP_Rewind;
1875 }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
1876 seekOp = OP_Last;
1877 }else{
1878 return 0;
1879 }
1880 pExpr = pList->a[0].pExpr;
1881 if( pExpr->op!=TK_COLUMN ) return 0;
1882 iCol = pExpr->iColumn;
1883 pTab = pSrc->a[0].pTab;
1884
1885 /* If we get to here, it means the query is of the correct form.
1886 ** Check to make sure we have an index and make pIdx point to the
1887 ** appropriate index. If the min() or max() is on an INTEGER PRIMARY
1888 ** key column, no index is necessary so set pIdx to NULL. If no
1889 ** usable index is found, return 0.
1890 */
1891 if( iCol<0 ){
1892 pIdx = 0;
1893 }else{
1894 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
1895 assert( pIdx->nColumn>=1 );
1896 if( pIdx->aiColumn[0]==iCol ) break;
1897 }
1898 if( pIdx==0 ) return 0;
1899 }
1900
1901 /* Identify column types if we will be using the callback. This
1902 ** step is skipped if the output is going to a table or a memory cell.
1903 ** The column names have already been generated in the calling function.
1904 */
1905 v = sqliteGetVdbe(pParse);
1906 if( v==0 ) return 0;
1907 if( eDest==SRT_Callback ){
1908 generateColumnTypes(pParse, p->pSrc, p->pEList);
1909 }
1910
1911 /* If the output is destined for a temporary table, open that table.
1912 */
1913 if( eDest==SRT_TempTable ){
1914 sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
1915 }
1916
1917 /* Generating code to find the min or the max. Basically all we have
1918 ** to do is find the first or the last entry in the chosen index. If
1919 ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
1920 ** or last entry in the main table.
1921 */
1922 sqliteCodeVerifySchema(pParse, pTab->iDb);
1923 base = pSrc->a[0].iCursor;
1924 computeLimitRegisters(pParse, p);
1925 if( pSrc->a[0].pSelect==0 ){
1926 sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
1927 sqliteVdbeOp3(v, OP_OpenRead, base, pTab->tnum, pTab->zName, 0);
1928 }
1929 cont = sqliteVdbeMakeLabel(v);
1930 if( pIdx==0 ){
1931 sqliteVdbeAddOp(v, seekOp, base, 0);
1932 }else{
1933 sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
1934 sqliteVdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum, pIdx->zName, P3_STATIC);
1935 if( seekOp==OP_Rewind ){
1936 sqliteVdbeAddOp(v, OP_String, 0, 0);
1937 sqliteVdbeAddOp(v, OP_MakeKey, 1, 0);
1938 sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
1939 seekOp = OP_MoveTo;
1940 }
1941 sqliteVdbeAddOp(v, seekOp, base+1, 0);
1942 sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
1943 sqliteVdbeAddOp(v, OP_Close, base+1, 0);
1944 sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
1945 }
1946 eList.nExpr = 1;
1947 memset(&eListItem, 0, sizeof(eListItem));
1948 eList.a = &eListItem;
1949 eList.a[0].pExpr = pExpr;
1950 selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont);
1951 sqliteVdbeResolveLabel(v, cont);
1952 sqliteVdbeAddOp(v, OP_Close, base, 0);
1953
1954 return 1;
1955 }
1956
1957 /*
1958 ** Generate code for the given SELECT statement.
1959 **
1960 ** The results are distributed in various ways depending on the
1961 ** value of eDest and iParm.
1962 **
1963 ** eDest Value Result
1964 ** ------------ -------------------------------------------
1965 ** SRT_Callback Invoke the callback for each row of the result.
1966 **
1967 ** SRT_Mem Store first result in memory cell iParm
1968 **
1969 ** SRT_Set Store results as keys of a table with cursor iParm
1970 **
1971 ** SRT_Union Store results as a key in a temporary table iParm
1972 **
1973 ** SRT_Except Remove results from the temporary table iParm.
1974 **
1975 ** SRT_Table Store results in temporary table iParm
1976 **
1977 ** The table above is incomplete. Additional eDist value have be added
1978 ** since this comment was written. See the selectInnerLoop() function for
1979 ** a complete listing of the allowed values of eDest and their meanings.
1980 **
1981 ** This routine returns the number of errors. If any errors are
1982 ** encountered, then an appropriate error message is left in
1983 ** pParse->zErrMsg.
1984 **
1985 ** This routine does NOT free the Select structure passed in. The
1986 ** calling function needs to do that.
1987 **
1988 ** The pParent, parentTab, and *pParentAgg fields are filled in if this
1989 ** SELECT is a subquery. This routine may try to combine this SELECT
1990 ** with its parent to form a single flat query. In so doing, it might
1991 ** change the parent query from a non-aggregate to an aggregate query.
1992 ** For that reason, the pParentAgg flag is passed as a pointer, so it
1993 ** can be changed.
1994 **
1995 ** Example 1: The meaning of the pParent parameter.
1996 **
1997 ** SELECT * FROM t1 JOIN (SELECT x, count(*) FROM t2) JOIN t3;
1998 ** \ \_______ subquery _______/ /
1999 ** \ /
2000 ** \____________________ outer query ___________________/
2001 **
2002 ** This routine is called for the outer query first. For that call,
2003 ** pParent will be NULL. During the processing of the outer query, this
2004 ** routine is called recursively to handle the subquery. For the recursive
2005 ** call, pParent will point to the outer query. Because the subquery is
2006 ** the second element in a three-way join, the parentTab parameter will
2007 ** be 1 (the 2nd value of a 0-indexed array.)
2008 */
sqliteSelect(Parse * pParse,Select * p,int eDest,int iParm,Select * pParent,int parentTab,int * pParentAgg)2009 int sqliteSelect(
2010 Parse *pParse, /* The parser context */
2011 Select *p, /* The SELECT statement being coded. */
2012 int eDest, /* How to dispose of the results */
2013 int iParm, /* A parameter used by the eDest disposal method */
2014 Select *pParent, /* Another SELECT for which this is a sub-query */
2015 int parentTab, /* Index in pParent->pSrc of this query */
2016 int *pParentAgg /* True if pParent uses aggregate functions */
2017 ){
2018 int i;
2019 WhereInfo *pWInfo;
2020 Vdbe *v;
2021 int isAgg = 0; /* True for select lists like "count(*)" */
2022 ExprList *pEList; /* List of columns to extract. */
2023 SrcList *pTabList; /* List of tables to select from */
2024 Expr *pWhere; /* The WHERE clause. May be NULL */
2025 ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */
2026 ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */
2027 Expr *pHaving; /* The HAVING clause. May be NULL */
2028 int isDistinct; /* True if the DISTINCT keyword is present */
2029 int distinct; /* Table to use for the distinct set */
2030 int rc = 1; /* Value to return from this function */
2031
2032 if( sqlite_malloc_failed || pParse->nErr || p==0 ) return 1;
2033 if( sqliteAuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
2034
2035 /* If there is are a sequence of queries, do the earlier ones first.
2036 */
2037 if( p->pPrior ){
2038 return multiSelect(pParse, p, eDest, iParm);
2039 }
2040
2041 /* Make local copies of the parameters for this query.
2042 */
2043 pTabList = p->pSrc;
2044 pWhere = p->pWhere;
2045 pOrderBy = p->pOrderBy;
2046 pGroupBy = p->pGroupBy;
2047 pHaving = p->pHaving;
2048 isDistinct = p->isDistinct;
2049
2050 /* Allocate VDBE cursors for each table in the FROM clause
2051 */
2052 sqliteSrcListAssignCursors(pParse, pTabList);
2053
2054 /*
2055 ** Do not even attempt to generate any code if we have already seen
2056 ** errors before this routine starts.
2057 */
2058 if( pParse->nErr>0 ) goto select_end;
2059
2060 /* Expand any "*" terms in the result set. (For example the "*" in
2061 ** "SELECT * FROM t1") The fillInColumnlist() routine also does some
2062 ** other housekeeping - see the header comment for details.
2063 */
2064 if( fillInColumnList(pParse, p) ){
2065 goto select_end;
2066 }
2067 pWhere = p->pWhere;
2068 pEList = p->pEList;
2069 if( pEList==0 ) goto select_end;
2070
2071 /* If writing to memory or generating a set
2072 ** only a single column may be output.
2073 */
2074 if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
2075 sqliteErrorMsg(pParse, "only a single result allowed for "
2076 "a SELECT that is part of an expression");
2077 goto select_end;
2078 }
2079
2080 /* ORDER BY is ignored for some destinations.
2081 */
2082 switch( eDest ){
2083 case SRT_Union:
2084 case SRT_Except:
2085 case SRT_Discard:
2086 pOrderBy = 0;
2087 break;
2088 default:
2089 break;
2090 }
2091
2092 /* At this point, we should have allocated all the cursors that we
2093 ** need to handle subquerys and temporary tables.
2094 **
2095 ** Resolve the column names and do a semantics check on all the expressions.
2096 */
2097 for(i=0; i<pEList->nExpr; i++){
2098 if( sqliteExprResolveIds(pParse, pTabList, 0, pEList->a[i].pExpr) ){
2099 goto select_end;
2100 }
2101 if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
2102 goto select_end;
2103 }
2104 }
2105 if( pWhere ){
2106 if( sqliteExprResolveIds(pParse, pTabList, pEList, pWhere) ){
2107 goto select_end;
2108 }
2109 if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
2110 goto select_end;
2111 }
2112 }
2113 if( pHaving ){
2114 if( pGroupBy==0 ){
2115 sqliteErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
2116 goto select_end;
2117 }
2118 if( sqliteExprResolveIds(pParse, pTabList, pEList, pHaving) ){
2119 goto select_end;
2120 }
2121 if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
2122 goto select_end;
2123 }
2124 }
2125 if( pOrderBy ){
2126 for(i=0; i<pOrderBy->nExpr; i++){
2127 int iCol;
2128 Expr *pE = pOrderBy->a[i].pExpr;
2129 if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
2130 sqliteExprDelete(pE);
2131 pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
2132 }
2133 if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
2134 goto select_end;
2135 }
2136 if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
2137 goto select_end;
2138 }
2139 if( sqliteExprIsConstant(pE) ){
2140 if( sqliteExprIsInteger(pE, &iCol)==0 ){
2141 sqliteErrorMsg(pParse,
2142 "ORDER BY terms must not be non-integer constants");
2143 goto select_end;
2144 }else if( iCol<=0 || iCol>pEList->nExpr ){
2145 sqliteErrorMsg(pParse,
2146 "ORDER BY column number %d out of range - should be "
2147 "between 1 and %d", iCol, pEList->nExpr);
2148 goto select_end;
2149 }
2150 }
2151 }
2152 }
2153 if( pGroupBy ){
2154 for(i=0; i<pGroupBy->nExpr; i++){
2155 int iCol;
2156 Expr *pE = pGroupBy->a[i].pExpr;
2157 if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
2158 sqliteExprDelete(pE);
2159 pE = pGroupBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
2160 }
2161 if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
2162 goto select_end;
2163 }
2164 if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
2165 goto select_end;
2166 }
2167 if( sqliteExprIsConstant(pE) ){
2168 if( sqliteExprIsInteger(pE, &iCol)==0 ){
2169 sqliteErrorMsg(pParse,
2170 "GROUP BY terms must not be non-integer constants");
2171 goto select_end;
2172 }else if( iCol<=0 || iCol>pEList->nExpr ){
2173 sqliteErrorMsg(pParse,
2174 "GROUP BY column number %d out of range - should be "
2175 "between 1 and %d", iCol, pEList->nExpr);
2176 goto select_end;
2177 }
2178 }
2179 }
2180 }
2181
2182 /* Begin generating code.
2183 */
2184 v = sqliteGetVdbe(pParse);
2185 if( v==0 ) goto select_end;
2186
2187 /* Identify column names if we will be using them in a callback. This
2188 ** step is skipped if the output is going to some other destination.
2189 */
2190 if( eDest==SRT_Callback ){
2191 generateColumnNames(pParse, pTabList, pEList);
2192 }
2193
2194 /* Generate code for all sub-queries in the FROM clause
2195 */
2196 for(i=0; i<pTabList->nSrc; i++){
2197 const char *zSavedAuthContext;
2198 int needRestoreContext;
2199
2200 if( pTabList->a[i].pSelect==0 ) continue;
2201 if( pTabList->a[i].zName!=0 ){
2202 zSavedAuthContext = pParse->zAuthContext;
2203 pParse->zAuthContext = pTabList->a[i].zName;
2204 needRestoreContext = 1;
2205 }else{
2206 needRestoreContext = 0;
2207 }
2208 sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable,
2209 pTabList->a[i].iCursor, p, i, &isAgg);
2210 if( needRestoreContext ){
2211 pParse->zAuthContext = zSavedAuthContext;
2212 }
2213 pTabList = p->pSrc;
2214 pWhere = p->pWhere;
2215 if( eDest!=SRT_Union && eDest!=SRT_Except && eDest!=SRT_Discard ){
2216 pOrderBy = p->pOrderBy;
2217 }
2218 pGroupBy = p->pGroupBy;
2219 pHaving = p->pHaving;
2220 isDistinct = p->isDistinct;
2221 }
2222
2223 /* Check for the special case of a min() or max() function by itself
2224 ** in the result set.
2225 */
2226 if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){
2227 rc = 0;
2228 goto select_end;
2229 }
2230
2231 /* Check to see if this is a subquery that can be "flattened" into its parent.
2232 ** If flattening is a possiblity, do so and return immediately.
2233 */
2234 if( pParent && pParentAgg &&
2235 flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
2236 if( isAgg ) *pParentAgg = 1;
2237 return rc;
2238 }
2239
2240 /* Set the limiter.
2241 */
2242 computeLimitRegisters(pParse, p);
2243
2244 /* Identify column types if we will be using a callback. This
2245 ** step is skipped if the output is going to a destination other
2246 ** than a callback.
2247 **
2248 ** We have to do this separately from the creation of column names
2249 ** above because if the pTabList contains views then they will not
2250 ** have been resolved and we will not know the column types until
2251 ** now.
2252 */
2253 if( eDest==SRT_Callback ){
2254 generateColumnTypes(pParse, pTabList, pEList);
2255 }
2256
2257 /* If the output is destined for a temporary table, open that table.
2258 */
2259 if( eDest==SRT_TempTable ){
2260 sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
2261 }
2262
2263 /* Do an analysis of aggregate expressions.
2264 */
2265 sqliteAggregateInfoReset(pParse);
2266 if( isAgg || pGroupBy ){
2267 assert( pParse->nAgg==0 );
2268 isAgg = 1;
2269 for(i=0; i<pEList->nExpr; i++){
2270 if( sqliteExprAnalyzeAggregates(pParse, pEList->a[i].pExpr) ){
2271 goto select_end;
2272 }
2273 }
2274 if( pGroupBy ){
2275 for(i=0; i<pGroupBy->nExpr; i++){
2276 if( sqliteExprAnalyzeAggregates(pParse, pGroupBy->a[i].pExpr) ){
2277 goto select_end;
2278 }
2279 }
2280 }
2281 if( pHaving && sqliteExprAnalyzeAggregates(pParse, pHaving) ){
2282 goto select_end;
2283 }
2284 if( pOrderBy ){
2285 for(i=0; i<pOrderBy->nExpr; i++){
2286 if( sqliteExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
2287 goto select_end;
2288 }
2289 }
2290 }
2291 }
2292
2293 /* Reset the aggregator
2294 */
2295 if( isAgg ){
2296 sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg);
2297 for(i=0; i<pParse->nAgg; i++){
2298 FuncDef *pFunc;
2299 if( (pFunc = pParse->aAgg[i].pFunc)!=0 && pFunc->xFinalize!=0 ){
2300 sqliteVdbeOp3(v, OP_AggInit, 0, i, (char*)pFunc, P3_POINTER);
2301 }
2302 }
2303 if( pGroupBy==0 ){
2304 sqliteVdbeAddOp(v, OP_String, 0, 0);
2305 sqliteVdbeAddOp(v, OP_AggFocus, 0, 0);
2306 }
2307 }
2308
2309 /* Initialize the memory cell to NULL
2310 */
2311 if( eDest==SRT_Mem ){
2312 sqliteVdbeAddOp(v, OP_String, 0, 0);
2313 sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
2314 }
2315
2316 /* Open a temporary table to use for the distinct set.
2317 */
2318 if( isDistinct ){
2319 distinct = pParse->nTab++;
2320 sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
2321 }else{
2322 distinct = -1;
2323 }
2324
2325 /* Begin the database scan
2326 */
2327 pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 0,
2328 pGroupBy ? 0 : &pOrderBy);
2329 if( pWInfo==0 ) goto select_end;
2330
2331 /* Use the standard inner loop if we are not dealing with
2332 ** aggregates
2333 */
2334 if( !isAgg ){
2335 if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2336 iParm, pWInfo->iContinue, pWInfo->iBreak) ){
2337 goto select_end;
2338 }
2339 }
2340
2341 /* If we are dealing with aggregates, then do the special aggregate
2342 ** processing.
2343 */
2344 else{
2345 AggExpr *pAgg;
2346 if( pGroupBy ){
2347 int lbl1;
2348 for(i=0; i<pGroupBy->nExpr; i++){
2349 sqliteExprCode(pParse, pGroupBy->a[i].pExpr);
2350 }
2351 sqliteVdbeAddOp(v, OP_MakeKey, pGroupBy->nExpr, 0);
2352 if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pGroupBy);
2353 lbl1 = sqliteVdbeMakeLabel(v);
2354 sqliteVdbeAddOp(v, OP_AggFocus, 0, lbl1);
2355 for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
2356 if( pAgg->isAgg ) continue;
2357 sqliteExprCode(pParse, pAgg->pExpr);
2358 sqliteVdbeAddOp(v, OP_AggSet, 0, i);
2359 }
2360 sqliteVdbeResolveLabel(v, lbl1);
2361 }
2362 for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
2363 Expr *pE;
2364 int nExpr;
2365 FuncDef *pDef;
2366 if( !pAgg->isAgg ) continue;
2367 assert( pAgg->pFunc!=0 );
2368 assert( pAgg->pFunc->xStep!=0 );
2369 pDef = pAgg->pFunc;
2370 pE = pAgg->pExpr;
2371 assert( pE!=0 );
2372 assert( pE->op==TK_AGG_FUNCTION );
2373 nExpr = sqliteExprCodeExprList(pParse, pE->pList, pDef->includeTypes);
2374 sqliteVdbeAddOp(v, OP_Integer, i, 0);
2375 sqliteVdbeOp3(v, OP_AggFunc, 0, nExpr, (char*)pDef, P3_POINTER);
2376 }
2377 }
2378
2379 /* End the database scan loop.
2380 */
2381 sqliteWhereEnd(pWInfo);
2382
2383 /* If we are processing aggregates, we need to set up a second loop
2384 ** over all of the aggregate values and process them.
2385 */
2386 if( isAgg ){
2387 int endagg = sqliteVdbeMakeLabel(v);
2388 int startagg;
2389 startagg = sqliteVdbeAddOp(v, OP_AggNext, 0, endagg);
2390 pParse->useAgg = 1;
2391 if( pHaving ){
2392 sqliteExprIfFalse(pParse, pHaving, startagg, 1);
2393 }
2394 if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2395 iParm, startagg, endagg) ){
2396 goto select_end;
2397 }
2398 sqliteVdbeAddOp(v, OP_Goto, 0, startagg);
2399 sqliteVdbeResolveLabel(v, endagg);
2400 sqliteVdbeAddOp(v, OP_Noop, 0, 0);
2401 pParse->useAgg = 0;
2402 }
2403
2404 /* If there is an ORDER BY clause, then we need to sort the results
2405 ** and send them to the callback one by one.
2406 */
2407 if( pOrderBy ){
2408 generateSortTail(p, v, pEList->nExpr, eDest, iParm);
2409 }
2410
2411 /* If this was a subquery, we have now converted the subquery into a
2412 ** temporary table. So delete the subquery structure from the parent
2413 ** to prevent this subquery from being evaluated again and to force the
2414 ** the use of the temporary table.
2415 */
2416 if( pParent ){
2417 assert( pParent->pSrc->nSrc>parentTab );
2418 assert( pParent->pSrc->a[parentTab].pSelect==p );
2419 sqliteSelectDelete(p);
2420 pParent->pSrc->a[parentTab].pSelect = 0;
2421 }
2422
2423 /* The SELECT was successfully coded. Set the return code to 0
2424 ** to indicate no errors.
2425 */
2426 rc = 0;
2427
2428 /* Control jumps to here if an error is encountered above, or upon
2429 ** successful coding of the SELECT.
2430 */
2431 select_end:
2432 sqliteAggregateInfoReset(pParse);
2433 return rc;
2434 }
2435