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 : ** Delete all the content of a Select structure but do not deallocate
21 : ** the select structure itself.
22 : */
23 153 : static void clearSelect(Select *p){
24 153 : sqlite3ExprListDelete(p->pEList);
25 153 : sqlite3SrcListDelete(p->pSrc);
26 153 : sqlite3ExprDelete(p->pWhere);
27 153 : sqlite3ExprListDelete(p->pGroupBy);
28 153 : sqlite3ExprDelete(p->pHaving);
29 153 : sqlite3ExprListDelete(p->pOrderBy);
30 153 : sqlite3SelectDelete(p->pPrior);
31 153 : sqlite3ExprDelete(p->pLimit);
32 153 : sqlite3ExprDelete(p->pOffset);
33 153 : }
34 :
35 : /*
36 : ** Allocate a new Select structure and return a pointer to that
37 : ** structure.
38 : */
39 : Select *sqlite3SelectNew(
40 : ExprList *pEList, /* which columns to include in the result */
41 : SrcList *pSrc, /* the FROM clause -- which tables to scan */
42 : Expr *pWhere, /* the WHERE clause */
43 : ExprList *pGroupBy, /* the GROUP BY clause */
44 : Expr *pHaving, /* the HAVING clause */
45 : ExprList *pOrderBy, /* the ORDER BY clause */
46 : int isDistinct, /* true if the DISTINCT keyword is present */
47 : Expr *pLimit, /* LIMIT value. NULL means not used */
48 : Expr *pOffset /* OFFSET value. NULL means no offset */
49 153 : ){
50 : Select *pNew;
51 : Select standin;
52 153 : pNew = sqliteMalloc( sizeof(*pNew) );
53 : assert( !pOffset || pLimit ); /* Can't have OFFSET without LIMIT. */
54 153 : if( pNew==0 ){
55 0 : pNew = &standin;
56 0 : memset(pNew, 0, sizeof(*pNew));
57 : }
58 153 : if( pEList==0 ){
59 0 : pEList = sqlite3ExprListAppend(0, sqlite3Expr(TK_ALL,0,0,0), 0);
60 : }
61 153 : pNew->pEList = pEList;
62 153 : pNew->pSrc = pSrc;
63 153 : pNew->pWhere = pWhere;
64 153 : pNew->pGroupBy = pGroupBy;
65 153 : pNew->pHaving = pHaving;
66 153 : pNew->pOrderBy = pOrderBy;
67 153 : pNew->isDistinct = isDistinct;
68 153 : pNew->op = TK_SELECT;
69 : assert( pOffset==0 || pLimit!=0 );
70 153 : pNew->pLimit = pLimit;
71 153 : pNew->pOffset = pOffset;
72 153 : pNew->iLimit = -1;
73 153 : pNew->iOffset = -1;
74 153 : pNew->addrOpenEphm[0] = -1;
75 153 : pNew->addrOpenEphm[1] = -1;
76 153 : pNew->addrOpenEphm[2] = -1;
77 153 : if( pNew==&standin) {
78 0 : clearSelect(pNew);
79 0 : pNew = 0;
80 : }
81 153 : return pNew;
82 : }
83 :
84 : /*
85 : ** Delete the given Select structure and all of its substructures.
86 : */
87 3173 : void sqlite3SelectDelete(Select *p){
88 3173 : if( p ){
89 153 : clearSelect(p);
90 153 : sqliteFree(p);
91 : }
92 3173 : }
93 :
94 : /*
95 : ** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
96 : ** type of join. Return an integer constant that expresses that type
97 : ** in terms of the following bit values:
98 : **
99 : ** JT_INNER
100 : ** JT_CROSS
101 : ** JT_OUTER
102 : ** JT_NATURAL
103 : ** JT_LEFT
104 : ** JT_RIGHT
105 : **
106 : ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
107 : **
108 : ** If an illegal or unsupported join type is seen, then still return
109 : ** a join type, but put an error in the pParse structure.
110 : */
111 5 : int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
112 5 : int jointype = 0;
113 : Token *apAll[3];
114 : Token *p;
115 : static const struct {
116 : const char zKeyword[8];
117 : u8 nChar;
118 : u8 code;
119 : } keywords[] = {
120 : { "natural", 7, JT_NATURAL },
121 : { "left", 4, JT_LEFT|JT_OUTER },
122 : { "right", 5, JT_RIGHT|JT_OUTER },
123 : { "full", 4, JT_LEFT|JT_RIGHT|JT_OUTER },
124 : { "outer", 5, JT_OUTER },
125 : { "inner", 5, JT_INNER },
126 : { "cross", 5, JT_INNER|JT_CROSS },
127 : };
128 : int i, j;
129 5 : apAll[0] = pA;
130 5 : apAll[1] = pB;
131 5 : apAll[2] = pC;
132 10 : for(i=0; i<3 && apAll[i]; i++){
133 5 : p = apAll[i];
134 10 : for(j=0; j<sizeof(keywords)/sizeof(keywords[0]); j++){
135 10 : if( p->n==keywords[j].nChar
136 : && sqlite3StrNICmp((char*)p->z, keywords[j].zKeyword, p->n)==0 ){
137 5 : jointype |= keywords[j].code;
138 5 : break;
139 : }
140 : }
141 5 : if( j>=sizeof(keywords)/sizeof(keywords[0]) ){
142 0 : jointype |= JT_ERROR;
143 0 : break;
144 : }
145 : }
146 5 : if(
147 : (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||
148 : (jointype & JT_ERROR)!=0
149 : ){
150 0 : const char *zSp1 = " ";
151 0 : const char *zSp2 = " ";
152 0 : if( pB==0 ){ zSp1++; }
153 0 : if( pC==0 ){ zSp2++; }
154 0 : sqlite3ErrorMsg(pParse, "unknown or unsupported join type: "
155 : "%T%s%T%s%T", pA, zSp1, pB, zSp2, pC);
156 0 : jointype = JT_INNER;
157 5 : }else if( jointype & JT_RIGHT ){
158 0 : sqlite3ErrorMsg(pParse,
159 : "RIGHT and FULL OUTER JOINs are not currently supported");
160 0 : jointype = JT_INNER;
161 : }
162 5 : return jointype;
163 : }
164 :
165 : /*
166 : ** Return the index of a column in a table. Return -1 if the column
167 : ** is not contained in the table.
168 : */
169 0 : static int columnIndex(Table *pTab, const char *zCol){
170 : int i;
171 0 : for(i=0; i<pTab->nCol; i++){
172 0 : if( sqlite3StrICmp(pTab->aCol[i].zName, zCol)==0 ) return i;
173 : }
174 0 : return -1;
175 : }
176 :
177 : /*
178 : ** Set the value of a token to a '\000'-terminated string.
179 : */
180 60 : static void setToken(Token *p, const char *z){
181 60 : p->z = (u8*)z;
182 60 : p->n = z ? strlen(z) : 0;
183 60 : p->dyn = 0;
184 60 : }
185 :
186 : /*
187 : ** Create an expression node for an identifier with the name of zName
188 : */
189 0 : Expr *sqlite3CreateIdExpr(const char *zName){
190 : Token dummy;
191 0 : setToken(&dummy, zName);
192 0 : return sqlite3Expr(TK_ID, 0, 0, &dummy);
193 : }
194 :
195 :
196 : /*
197 : ** Add a term to the WHERE expression in *ppExpr that requires the
198 : ** zCol column to be equal in the two tables pTab1 and pTab2.
199 : */
200 : static void addWhereTerm(
201 : const char *zCol, /* Name of the column */
202 : const Table *pTab1, /* First table */
203 : const char *zAlias1, /* Alias for first table. May be NULL */
204 : const Table *pTab2, /* Second table */
205 : const char *zAlias2, /* Alias for second table. May be NULL */
206 : int iRightJoinTable, /* VDBE cursor for the right table */
207 : Expr **ppExpr /* Add the equality term to this expression */
208 0 : ){
209 : Expr *pE1a, *pE1b, *pE1c;
210 : Expr *pE2a, *pE2b, *pE2c;
211 : Expr *pE;
212 :
213 0 : pE1a = sqlite3CreateIdExpr(zCol);
214 0 : pE2a = sqlite3CreateIdExpr(zCol);
215 0 : if( zAlias1==0 ){
216 0 : zAlias1 = pTab1->zName;
217 : }
218 0 : pE1b = sqlite3CreateIdExpr(zAlias1);
219 0 : if( zAlias2==0 ){
220 0 : zAlias2 = pTab2->zName;
221 : }
222 0 : pE2b = sqlite3CreateIdExpr(zAlias2);
223 0 : pE1c = sqlite3ExprOrFree(TK_DOT, pE1b, pE1a, 0);
224 0 : pE2c = sqlite3ExprOrFree(TK_DOT, pE2b, pE2a, 0);
225 0 : pE = sqlite3ExprOrFree(TK_EQ, pE1c, pE2c, 0);
226 0 : if( pE ){
227 0 : ExprSetProperty(pE, EP_FromJoin);
228 0 : pE->iRightJoinTable = iRightJoinTable;
229 : }
230 0 : pE = sqlite3ExprAnd(*ppExpr, pE);
231 0 : if( pE ){
232 0 : *ppExpr = pE;
233 : }
234 0 : }
235 :
236 : /*
237 : ** Set the EP_FromJoin property on all terms of the given expression.
238 : ** And set the Expr.iRightJoinTable to iTable for every term in the
239 : ** expression.
240 : **
241 : ** The EP_FromJoin property is used on terms of an expression to tell
242 : ** the LEFT OUTER JOIN processing logic that this term is part of the
243 : ** join restriction specified in the ON or USING clause and not a part
244 : ** of the more general WHERE clause. These terms are moved over to the
245 : ** WHERE clause during join processing but we need to remember that they
246 : ** originated in the ON or USING clause.
247 : **
248 : ** The Expr.iRightJoinTable tells the WHERE clause processing that the
249 : ** expression depends on table iRightJoinTable even if that table is not
250 : ** explicitly mentioned in the expression. That information is needed
251 : ** for cases like this:
252 : **
253 : ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
254 : **
255 : ** The where clause needs to defer the handling of the t1.x=5
256 : ** term until after the t2 loop of the join. In that way, a
257 : ** NULL t2 row will be inserted whenever t1.x!=5. If we do not
258 : ** defer the handling of t1.x=5, it will be processed immediately
259 : ** after the t1 loop and rows with t1.x!=5 will never appear in
260 : ** the output, which is incorrect.
261 : */
262 40 : static void setJoinExpr(Expr *p, int iTable){
263 115 : while( p ){
264 35 : ExprSetProperty(p, EP_FromJoin);
265 35 : p->iRightJoinTable = iTable;
266 35 : setJoinExpr(p->pLeft, iTable);
267 35 : p = p->pRight;
268 : }
269 40 : }
270 :
271 : /*
272 : ** This routine processes the join information for a SELECT statement.
273 : ** ON and USING clauses are converted into extra terms of the WHERE clause.
274 : ** NATURAL joins also create extra WHERE clause terms.
275 : **
276 : ** The terms of a FROM clause are contained in the Select.pSrc structure.
277 : ** The left most table is the first entry in Select.pSrc. The right-most
278 : ** table is the last entry. The join operator is held in the entry to
279 : ** the left. Thus entry 0 contains the join operator for the join between
280 : ** entries 0 and 1. Any ON or USING clauses associated with the join are
281 : ** also attached to the left entry.
282 : **
283 : ** This routine returns the number of errors encountered.
284 : */
285 153 : static int sqliteProcessJoin(Parse *pParse, Select *p){
286 : SrcList *pSrc; /* All tables in the FROM clause */
287 : int i, j; /* Loop counters */
288 : struct SrcList_item *pLeft; /* Left table being joined */
289 : struct SrcList_item *pRight; /* Right table being joined */
290 :
291 153 : pSrc = p->pSrc;
292 153 : pLeft = &pSrc->a[0];
293 153 : pRight = &pLeft[1];
294 158 : for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){
295 5 : Table *pLeftTab = pLeft->pTab;
296 5 : Table *pRightTab = pRight->pTab;
297 :
298 5 : if( pLeftTab==0 || pRightTab==0 ) continue;
299 :
300 : /* When the NATURAL keyword is present, add WHERE clause terms for
301 : ** every column that the two tables have in common.
302 : */
303 5 : if( pRight->jointype & JT_NATURAL ){
304 0 : if( pRight->pOn || pRight->pUsing ){
305 0 : sqlite3ErrorMsg(pParse, "a NATURAL join may not have "
306 : "an ON or USING clause", 0);
307 0 : return 1;
308 : }
309 0 : for(j=0; j<pLeftTab->nCol; j++){
310 0 : char *zName = pLeftTab->aCol[j].zName;
311 0 : if( columnIndex(pRightTab, zName)>=0 ){
312 0 : addWhereTerm(zName, pLeftTab, pLeft->zAlias,
313 : pRightTab, pRight->zAlias,
314 : pRight->iCursor, &p->pWhere);
315 :
316 : }
317 : }
318 : }
319 :
320 : /* Disallow both ON and USING clauses in the same join
321 : */
322 5 : if( pRight->pOn && pRight->pUsing ){
323 0 : sqlite3ErrorMsg(pParse, "cannot have both ON and USING "
324 : "clauses in the same join");
325 0 : return 1;
326 : }
327 :
328 : /* Add the ON clause to the end of the WHERE clause, connected by
329 : ** an AND operator.
330 : */
331 5 : if( pRight->pOn ){
332 5 : setJoinExpr(pRight->pOn, pRight->iCursor);
333 5 : p->pWhere = sqlite3ExprAnd(p->pWhere, pRight->pOn);
334 5 : pRight->pOn = 0;
335 : }
336 :
337 : /* Create extra terms on the WHERE clause for each column named
338 : ** in the USING clause. Example: If the two tables to be joined are
339 : ** A and B and the USING clause names X, Y, and Z, then add this
340 : ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
341 : ** Report an error if any column mentioned in the USING clause is
342 : ** not contained in both tables to be joined.
343 : */
344 5 : if( pRight->pUsing ){
345 0 : IdList *pList = pRight->pUsing;
346 0 : for(j=0; j<pList->nId; j++){
347 0 : char *zName = pList->a[j].zName;
348 0 : if( columnIndex(pLeftTab, zName)<0 || columnIndex(pRightTab, zName)<0 ){
349 0 : sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
350 : "not present in both tables", zName);
351 0 : return 1;
352 : }
353 0 : addWhereTerm(zName, pLeftTab, pLeft->zAlias,
354 : pRightTab, pRight->zAlias,
355 : pRight->iCursor, &p->pWhere);
356 : }
357 : }
358 : }
359 153 : return 0;
360 : }
361 :
362 : /*
363 : ** Insert code into "v" that will push the record on the top of the
364 : ** stack into the sorter.
365 : */
366 : static void pushOntoSorter(
367 : Parse *pParse, /* Parser context */
368 : ExprList *pOrderBy, /* The ORDER BY clause */
369 : Select *pSelect /* The whole SELECT statement */
370 0 : ){
371 0 : Vdbe *v = pParse->pVdbe;
372 0 : sqlite3ExprCodeExprList(pParse, pOrderBy);
373 0 : sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iECursor, 0);
374 0 : sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0);
375 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0);
376 0 : sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iECursor, 0);
377 0 : if( pSelect->iLimit>=0 ){
378 : int addr1, addr2;
379 0 : addr1 = sqlite3VdbeAddOp(v, OP_IfMemZero, pSelect->iLimit+1, 0);
380 0 : sqlite3VdbeAddOp(v, OP_MemIncr, -1, pSelect->iLimit+1);
381 0 : addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
382 0 : sqlite3VdbeJumpHere(v, addr1);
383 0 : sqlite3VdbeAddOp(v, OP_Last, pOrderBy->iECursor, 0);
384 0 : sqlite3VdbeAddOp(v, OP_Delete, pOrderBy->iECursor, 0);
385 0 : sqlite3VdbeJumpHere(v, addr2);
386 0 : pSelect->iLimit = -1;
387 : }
388 0 : }
389 :
390 : /*
391 : ** Add code to implement the OFFSET
392 : */
393 : static void codeOffset(
394 : Vdbe *v, /* Generate code into this VM */
395 : Select *p, /* The SELECT statement being coded */
396 : int iContinue, /* Jump here to skip the current record */
397 : int nPop /* Number of times to pop stack when jumping */
398 153 : ){
399 153 : if( p->iOffset>=0 && iContinue!=0 ){
400 : int addr;
401 0 : sqlite3VdbeAddOp(v, OP_MemIncr, -1, p->iOffset);
402 0 : addr = sqlite3VdbeAddOp(v, OP_IfMemNeg, p->iOffset, 0);
403 0 : if( nPop>0 ){
404 0 : sqlite3VdbeAddOp(v, OP_Pop, nPop, 0);
405 : }
406 0 : sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue);
407 : VdbeComment((v, "# skip OFFSET records"));
408 0 : sqlite3VdbeJumpHere(v, addr);
409 : }
410 153 : }
411 :
412 : /*
413 : ** Add code that will check to make sure the top N elements of the
414 : ** stack are distinct. iTab is a sorting index that holds previously
415 : ** seen combinations of the N values. A new entry is made in iTab
416 : ** if the current N values are new.
417 : **
418 : ** A jump to addrRepeat is made and the N+1 values are popped from the
419 : ** stack if the top N elements are not distinct.
420 : */
421 : static void codeDistinct(
422 : Vdbe *v, /* Generate code into this VM */
423 : int iTab, /* A sorting index used to test for distinctness */
424 : int addrRepeat, /* Jump to here if not distinct */
425 : int N /* The top N elements of the stack must be distinct */
426 0 : ){
427 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, -N, 0);
428 0 : sqlite3VdbeAddOp(v, OP_Distinct, iTab, sqlite3VdbeCurrentAddr(v)+3);
429 0 : sqlite3VdbeAddOp(v, OP_Pop, N+1, 0);
430 0 : sqlite3VdbeAddOp(v, OP_Goto, 0, addrRepeat);
431 : VdbeComment((v, "# skip indistinct records"));
432 0 : sqlite3VdbeAddOp(v, OP_IdxInsert, iTab, 0);
433 0 : }
434 :
435 :
436 : /*
437 : ** This routine generates the code for the inside of the inner loop
438 : ** of a SELECT.
439 : **
440 : ** If srcTab and nColumn are both zero, then the pEList expressions
441 : ** are evaluated in order to get the data for this row. If nColumn>0
442 : ** then data is pulled from srcTab and pEList is used only to get the
443 : ** datatypes for each column.
444 : */
445 : static int selectInnerLoop(
446 : Parse *pParse, /* The parser context */
447 : Select *p, /* The complete select statement being coded */
448 : ExprList *pEList, /* List of values being extracted */
449 : int srcTab, /* Pull data from this table */
450 : int nColumn, /* Number of columns in the source table */
451 : ExprList *pOrderBy, /* If not NULL, sort results using this key */
452 : int distinct, /* If >=0, make sure results are distinct */
453 : int eDest, /* How to dispose of the results */
454 : int iParm, /* An argument to the disposal method */
455 : int iContinue, /* Jump here to continue with next row */
456 : int iBreak, /* Jump here to break out of the inner loop */
457 : char *aff /* affinity string if eDest is SRT_Union */
458 153 : ){
459 153 : Vdbe *v = pParse->pVdbe;
460 : int i;
461 : int hasDistinct; /* True if the DISTINCT keyword is present */
462 :
463 153 : if( v==0 ) return 0;
464 : assert( pEList!=0 );
465 :
466 : /* If there was a LIMIT clause on the SELECT statement, then do the check
467 : ** to see if this row should be output.
468 : */
469 153 : hasDistinct = distinct>=0 && pEList->nExpr>0;
470 153 : if( pOrderBy==0 && !hasDistinct ){
471 153 : codeOffset(v, p, iContinue, 0);
472 : }
473 :
474 : /* Pull the requested columns.
475 : */
476 153 : if( nColumn>0 ){
477 0 : for(i=0; i<nColumn; i++){
478 0 : sqlite3VdbeAddOp(v, OP_Column, srcTab, i);
479 : }
480 : }else{
481 153 : nColumn = pEList->nExpr;
482 153 : sqlite3ExprCodeExprList(pParse, pEList);
483 : }
484 :
485 : /* If the DISTINCT keyword was present on the SELECT statement
486 : ** and this row has been seen before, then do not make this row
487 : ** part of the result.
488 : */
489 153 : if( hasDistinct ){
490 : assert( pEList!=0 );
491 : assert( pEList->nExpr==nColumn );
492 0 : codeDistinct(v, distinct, iContinue, nColumn);
493 0 : if( pOrderBy==0 ){
494 0 : codeOffset(v, p, iContinue, nColumn);
495 : }
496 : }
497 :
498 153 : switch( eDest ){
499 : /* In this mode, write each query result to the key of the temporary
500 : ** table iParm.
501 : */
502 : #ifndef SQLITE_OMIT_COMPOUND_SELECT
503 : case SRT_Union: {
504 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
505 0 : if( aff ){
506 0 : sqlite3VdbeChangeP3(v, -1, aff, P3_STATIC);
507 : }
508 0 : sqlite3VdbeAddOp(v, OP_IdxInsert, iParm, 0);
509 0 : break;
510 : }
511 :
512 : /* Construct a record from the query result, but instead of
513 : ** saving that record, use it as a key to delete elements from
514 : ** the temporary table iParm.
515 : */
516 : case SRT_Except: {
517 : int addr;
518 0 : addr = sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
519 0 : sqlite3VdbeChangeP3(v, -1, aff, P3_STATIC);
520 0 : sqlite3VdbeAddOp(v, OP_NotFound, iParm, addr+3);
521 0 : sqlite3VdbeAddOp(v, OP_Delete, iParm, 0);
522 0 : break;
523 : }
524 : #endif
525 :
526 : /* Store the result as data using a unique key.
527 : */
528 : case SRT_Table:
529 : case SRT_EphemTab: {
530 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
531 0 : if( pOrderBy ){
532 0 : pushOntoSorter(pParse, pOrderBy, p);
533 : }else{
534 0 : sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
535 0 : sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
536 0 : sqlite3VdbeAddOp(v, OP_Insert, iParm, OPFLAG_APPEND);
537 : }
538 0 : break;
539 : }
540 :
541 : #ifndef SQLITE_OMIT_SUBQUERY
542 : /* If we are creating a set for an "expr IN (SELECT ...)" construct,
543 : ** then there should be a single item on the stack. Write this
544 : ** item into the set table with bogus data.
545 : */
546 : case SRT_Set: {
547 0 : int addr1 = sqlite3VdbeCurrentAddr(v);
548 : int addr2;
549 :
550 : assert( nColumn==1 );
551 0 : sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3);
552 0 : sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
553 0 : addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
554 0 : p->affinity = sqlite3CompareAffinity(pEList->a[0].pExpr,(iParm>>16)&0xff);
555 0 : if( pOrderBy ){
556 : /* At first glance you would think we could optimize out the
557 : ** ORDER BY in this case since the order of entries in the set
558 : ** does not matter. But there might be a LIMIT clause, in which
559 : ** case the order does matter */
560 0 : pushOntoSorter(pParse, pOrderBy, p);
561 : }else{
562 0 : sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &p->affinity, 1);
563 0 : sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
564 : }
565 0 : sqlite3VdbeJumpHere(v, addr2);
566 0 : break;
567 : }
568 :
569 : /* If any row exist in the result set, record that fact and abort.
570 : */
571 : case SRT_Exists: {
572 0 : sqlite3VdbeAddOp(v, OP_MemInt, 1, iParm);
573 0 : sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
574 : /* The LIMIT clause will terminate the loop for us */
575 0 : break;
576 : }
577 :
578 : /* If this is a scalar select that is part of an expression, then
579 : ** store the results in the appropriate memory cell and break out
580 : ** of the scan loop.
581 : */
582 : case SRT_Mem: {
583 : assert( nColumn==1 );
584 0 : if( pOrderBy ){
585 0 : pushOntoSorter(pParse, pOrderBy, p);
586 : }else{
587 0 : sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
588 : /* The LIMIT clause will jump out of the loop for us */
589 : }
590 0 : break;
591 : }
592 : #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
593 :
594 : /* Send the data to the callback function or to a subroutine. In the
595 : ** case of a subroutine, the subroutine itself is responsible for
596 : ** popping the data from the stack.
597 : */
598 : case SRT_Subroutine:
599 : case SRT_Callback: {
600 153 : if( pOrderBy ){
601 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
602 0 : pushOntoSorter(pParse, pOrderBy, p);
603 153 : }else if( eDest==SRT_Subroutine ){
604 0 : sqlite3VdbeAddOp(v, OP_Gosub, 0, iParm);
605 : }else{
606 153 : sqlite3VdbeAddOp(v, OP_Callback, nColumn, 0);
607 : }
608 153 : break;
609 : }
610 :
611 : #if !defined(SQLITE_OMIT_TRIGGER)
612 : /* Discard the results. This is used for SELECT statements inside
613 : ** the body of a TRIGGER. The purpose of such selects is to call
614 : ** user-defined functions that have side effects. We do not care
615 : ** about the actual results of the select.
616 : */
617 : default: {
618 : assert( eDest==SRT_Discard );
619 0 : sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
620 : break;
621 : }
622 : #endif
623 : }
624 :
625 : /* Jump to the end of the loop if the LIMIT is reached.
626 : */
627 153 : if( p->iLimit>=0 && pOrderBy==0 ){
628 0 : sqlite3VdbeAddOp(v, OP_MemIncr, -1, p->iLimit);
629 0 : sqlite3VdbeAddOp(v, OP_IfMemZero, p->iLimit, iBreak);
630 : }
631 153 : return 0;
632 : }
633 :
634 : /*
635 : ** Given an expression list, generate a KeyInfo structure that records
636 : ** the collating sequence for each expression in that expression list.
637 : **
638 : ** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
639 : ** KeyInfo structure is appropriate for initializing a virtual index to
640 : ** implement that clause. If the ExprList is the result set of a SELECT
641 : ** then the KeyInfo structure is appropriate for initializing a virtual
642 : ** index to implement a DISTINCT test.
643 : **
644 : ** Space to hold the KeyInfo structure is obtain from malloc. The calling
645 : ** function is responsible for seeing that this structure is eventually
646 : ** freed. Add the KeyInfo structure to the P3 field of an opcode using
647 : ** P3_KEYINFO_HANDOFF is the usual way of dealing with this.
648 : */
649 4 : static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList){
650 4 : sqlite3 *db = pParse->db;
651 : int nExpr;
652 : KeyInfo *pInfo;
653 : struct ExprList_item *pItem;
654 : int i;
655 :
656 4 : nExpr = pList->nExpr;
657 4 : pInfo = sqliteMalloc( sizeof(*pInfo) + nExpr*(sizeof(CollSeq*)+1) );
658 4 : if( pInfo ){
659 4 : pInfo->aSortOrder = (u8*)&pInfo->aColl[nExpr];
660 4 : pInfo->nField = nExpr;
661 4 : pInfo->enc = ENC(db);
662 8 : for(i=0, pItem=pList->a; i<nExpr; i++, pItem++){
663 : CollSeq *pColl;
664 4 : pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
665 4 : if( !pColl ){
666 0 : pColl = db->pDfltColl;
667 : }
668 4 : pInfo->aColl[i] = pColl;
669 4 : pInfo->aSortOrder[i] = pItem->sortOrder;
670 : }
671 : }
672 4 : return pInfo;
673 : }
674 :
675 :
676 : /*
677 : ** If the inner loop was generated using a non-null pOrderBy argument,
678 : ** then the results were placed in a sorter. After the loop is terminated
679 : ** we need to run the sorter and output the results. The following
680 : ** routine generates the code needed to do that.
681 : */
682 : static void generateSortTail(
683 : Parse *pParse, /* Parsing context */
684 : Select *p, /* The SELECT statement */
685 : Vdbe *v, /* Generate code into this VDBE */
686 : int nColumn, /* Number of columns of data */
687 : int eDest, /* Write the sorted results here */
688 : int iParm /* Optional parameter associated with eDest */
689 0 : ){
690 0 : int brk = sqlite3VdbeMakeLabel(v);
691 0 : int cont = sqlite3VdbeMakeLabel(v);
692 : int addr;
693 : int iTab;
694 0 : int pseudoTab = 0;
695 0 : ExprList *pOrderBy = p->pOrderBy;
696 :
697 0 : iTab = pOrderBy->iECursor;
698 0 : if( eDest==SRT_Callback || eDest==SRT_Subroutine ){
699 0 : pseudoTab = pParse->nTab++;
700 0 : sqlite3VdbeAddOp(v, OP_OpenPseudo, pseudoTab, 0);
701 0 : sqlite3VdbeAddOp(v, OP_SetNumColumns, pseudoTab, nColumn);
702 : }
703 0 : addr = 1 + sqlite3VdbeAddOp(v, OP_Sort, iTab, brk);
704 0 : codeOffset(v, p, cont, 0);
705 0 : if( eDest==SRT_Callback || eDest==SRT_Subroutine ){
706 0 : sqlite3VdbeAddOp(v, OP_Integer, 1, 0);
707 : }
708 0 : sqlite3VdbeAddOp(v, OP_Column, iTab, pOrderBy->nExpr + 1);
709 0 : switch( eDest ){
710 : case SRT_Table:
711 : case SRT_EphemTab: {
712 0 : sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
713 0 : sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
714 0 : sqlite3VdbeAddOp(v, OP_Insert, iParm, OPFLAG_APPEND);
715 0 : break;
716 : }
717 : #ifndef SQLITE_OMIT_SUBQUERY
718 : case SRT_Set: {
719 : assert( nColumn==1 );
720 0 : sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
721 0 : sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
722 0 : sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
723 0 : sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &p->affinity, 1);
724 0 : sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
725 0 : break;
726 : }
727 : case SRT_Mem: {
728 : assert( nColumn==1 );
729 0 : sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
730 : /* The LIMIT clause will terminate the loop for us */
731 0 : break;
732 : }
733 : #endif
734 : case SRT_Callback:
735 : case SRT_Subroutine: {
736 : int i;
737 0 : sqlite3VdbeAddOp(v, OP_Insert, pseudoTab, 0);
738 0 : for(i=0; i<nColumn; i++){
739 0 : sqlite3VdbeAddOp(v, OP_Column, pseudoTab, i);
740 : }
741 0 : if( eDest==SRT_Callback ){
742 0 : sqlite3VdbeAddOp(v, OP_Callback, nColumn, 0);
743 : }else{
744 0 : sqlite3VdbeAddOp(v, OP_Gosub, 0, iParm);
745 : }
746 : break;
747 : }
748 : default: {
749 : /* Do nothing */
750 : break;
751 : }
752 : }
753 :
754 : /* Jump to the end of the loop when the LIMIT is reached
755 : */
756 0 : if( p->iLimit>=0 ){
757 0 : sqlite3VdbeAddOp(v, OP_MemIncr, -1, p->iLimit);
758 0 : sqlite3VdbeAddOp(v, OP_IfMemZero, p->iLimit, brk);
759 : }
760 :
761 : /* The bottom of the loop
762 : */
763 0 : sqlite3VdbeResolveLabel(v, cont);
764 0 : sqlite3VdbeAddOp(v, OP_Next, iTab, addr);
765 0 : sqlite3VdbeResolveLabel(v, brk);
766 0 : if( eDest==SRT_Callback || eDest==SRT_Subroutine ){
767 0 : sqlite3VdbeAddOp(v, OP_Close, pseudoTab, 0);
768 : }
769 :
770 0 : }
771 :
772 : /*
773 : ** Return a pointer to a string containing the 'declaration type' of the
774 : ** expression pExpr. The string may be treated as static by the caller.
775 : **
776 : ** The declaration type is the exact datatype definition extracted from the
777 : ** original CREATE TABLE statement if the expression is a column. The
778 : ** declaration type for a ROWID field is INTEGER. Exactly when an expression
779 : ** is considered a column can be complex in the presence of subqueries. The
780 : ** result-set expression in all of the following SELECT statements is
781 : ** considered a column by this function.
782 : **
783 : ** SELECT col FROM tbl;
784 : ** SELECT (SELECT col FROM tbl;
785 : ** SELECT (SELECT col FROM tbl);
786 : ** SELECT abc FROM (SELECT col AS abc FROM tbl);
787 : **
788 : ** The declaration type for any expression other than a column is NULL.
789 : */
790 : static const char *columnType(
791 : NameContext *pNC,
792 : Expr *pExpr,
793 : const char **pzOriginDb,
794 : const char **pzOriginTab,
795 : const char **pzOriginCol
796 353 : ){
797 353 : char const *zType = 0;
798 353 : char const *zOriginDb = 0;
799 353 : char const *zOriginTab = 0;
800 353 : char const *zOriginCol = 0;
801 : int j;
802 353 : if( pExpr==0 || pNC->pSrcList==0 ) return 0;
803 :
804 : /* The TK_AS operator can only occur in ORDER BY, GROUP BY, HAVING,
805 : ** and LIMIT clauses. But pExpr originates in the result set of a
806 : ** SELECT. So pExpr can never contain an AS operator.
807 : */
808 : assert( pExpr->op!=TK_AS );
809 :
810 353 : switch( pExpr->op ){
811 : case TK_AGG_COLUMN:
812 : case TK_COLUMN: {
813 : /* The expression is a column. Locate the table the column is being
814 : ** extracted from in NameContext.pSrcList. This table may be real
815 : ** database table or a subquery.
816 : */
817 321 : Table *pTab = 0; /* Table structure column is extracted from */
818 321 : Select *pS = 0; /* Select the column is extracted from */
819 321 : int iCol = pExpr->iColumn; /* Index of column in pTab */
820 963 : while( pNC && !pTab ){
821 321 : SrcList *pTabList = pNC->pSrcList;
822 321 : for(j=0;j<pTabList->nSrc && pTabList->a[j].iCursor!=pExpr->iTable;j++);
823 321 : if( j<pTabList->nSrc ){
824 321 : pTab = pTabList->a[j].pTab;
825 321 : pS = pTabList->a[j].pSelect;
826 : }else{
827 0 : pNC = pNC->pNext;
828 : }
829 : }
830 :
831 321 : if( pTab==0 ){
832 : /* FIX ME:
833 : ** This can occurs if you have something like "SELECT new.x;" inside
834 : ** a trigger. In other words, if you reference the special "new"
835 : ** table in the result set of a select. We do not have a good way
836 : ** to find the actual table type, so call it "TEXT". This is really
837 : ** something of a bug, but I do not know how to fix it.
838 : **
839 : ** This code does not produce the correct answer - it just prevents
840 : ** a segfault. See ticket #1229.
841 : */
842 0 : zType = "TEXT";
843 0 : break;
844 : }
845 :
846 : assert( pTab );
847 321 : if( pS ){
848 : /* The "table" is actually a sub-select or a view in the FROM clause
849 : ** of the SELECT statement. Return the declaration type and origin
850 : ** data for the result-set column of the sub-select.
851 : */
852 0 : if( iCol>=0 && iCol<pS->pEList->nExpr ){
853 : /* If iCol is less than zero, then the expression requests the
854 : ** rowid of the sub-select or view. This expression is legal (see
855 : ** test case misc2.2.2) - it always evaluates to NULL.
856 : */
857 : NameContext sNC;
858 0 : Expr *p = pS->pEList->a[iCol].pExpr;
859 0 : sNC.pSrcList = pS->pSrc;
860 0 : sNC.pNext = 0;
861 0 : sNC.pParse = pNC->pParse;
862 0 : zType = columnType(&sNC, p, &zOriginDb, &zOriginTab, &zOriginCol);
863 : }
864 321 : }else if( pTab->pSchema ){
865 : /* A real table */
866 : assert( !pS );
867 321 : if( iCol<0 ) iCol = pTab->iPKey;
868 : assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
869 321 : if( iCol<0 ){
870 0 : zType = "INTEGER";
871 0 : zOriginCol = "rowid";
872 : }else{
873 321 : zType = pTab->aCol[iCol].zType;
874 321 : zOriginCol = pTab->aCol[iCol].zName;
875 : }
876 321 : zOriginTab = pTab->zName;
877 321 : if( pNC->pParse ){
878 321 : int iDb = sqlite3SchemaToIndex(pNC->pParse->db, pTab->pSchema);
879 321 : zOriginDb = pNC->pParse->db->aDb[iDb].zName;
880 : }
881 : }
882 321 : break;
883 : }
884 : #ifndef SQLITE_OMIT_SUBQUERY
885 : case TK_SELECT: {
886 : /* The expression is a sub-select. Return the declaration type and
887 : ** origin info for the single column in the result set of the SELECT
888 : ** statement.
889 : */
890 : NameContext sNC;
891 0 : Select *pS = pExpr->pSelect;
892 0 : Expr *p = pS->pEList->a[0].pExpr;
893 0 : sNC.pSrcList = pS->pSrc;
894 0 : sNC.pNext = pNC;
895 0 : sNC.pParse = pNC->pParse;
896 0 : zType = columnType(&sNC, p, &zOriginDb, &zOriginTab, &zOriginCol);
897 : break;
898 : }
899 : #endif
900 : }
901 :
902 353 : if( pzOriginDb ){
903 : assert( pzOriginTab && pzOriginCol );
904 353 : *pzOriginDb = zOriginDb;
905 353 : *pzOriginTab = zOriginTab;
906 353 : *pzOriginCol = zOriginCol;
907 : }
908 353 : return zType;
909 : }
910 :
911 : /*
912 : ** Generate code that will tell the VDBE the declaration types of columns
913 : ** in the result set.
914 : */
915 : static void generateColumnTypes(
916 : Parse *pParse, /* Parser context */
917 : SrcList *pTabList, /* List of tables */
918 : ExprList *pEList /* Expressions defining the result set */
919 153 : ){
920 153 : Vdbe *v = pParse->pVdbe;
921 : int i;
922 : NameContext sNC;
923 153 : sNC.pSrcList = pTabList;
924 153 : sNC.pParse = pParse;
925 506 : for(i=0; i<pEList->nExpr; i++){
926 353 : Expr *p = pEList->a[i].pExpr;
927 353 : const char *zOrigDb = 0;
928 353 : const char *zOrigTab = 0;
929 353 : const char *zOrigCol = 0;
930 353 : const char *zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol);
931 :
932 : /* The vdbe must make it's own copy of the column-type and other
933 : ** column specific strings, in case the schema is reset before this
934 : ** virtual machine is deleted.
935 : */
936 353 : sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, P3_TRANSIENT);
937 353 : sqlite3VdbeSetColName(v, i, COLNAME_DATABASE, zOrigDb, P3_TRANSIENT);
938 353 : sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, P3_TRANSIENT);
939 353 : sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, P3_TRANSIENT);
940 : }
941 153 : }
942 :
943 : /*
944 : ** Generate code that will tell the VDBE the names of columns
945 : ** in the result set. This information is used to provide the
946 : ** azCol[] values in the callback.
947 : */
948 : static void generateColumnNames(
949 : Parse *pParse, /* Parser context */
950 : SrcList *pTabList, /* List of tables */
951 : ExprList *pEList /* Expressions defining the result set */
952 153 : ){
953 153 : Vdbe *v = pParse->pVdbe;
954 : int i, j;
955 153 : sqlite3 *db = pParse->db;
956 : int fullNames, shortNames;
957 :
958 : #ifndef SQLITE_OMIT_EXPLAIN
959 : /* If this is an EXPLAIN, skip this step */
960 153 : if( pParse->explain ){
961 0 : return;
962 : }
963 : #endif
964 :
965 : assert( v!=0 );
966 153 : if( pParse->colNamesSet || v==0 || sqlite3MallocFailed() ) return;
967 153 : pParse->colNamesSet = 1;
968 153 : fullNames = (db->flags & SQLITE_FullColNames)!=0;
969 153 : shortNames = (db->flags & SQLITE_ShortColNames)!=0;
970 153 : sqlite3VdbeSetNumCols(v, pEList->nExpr);
971 506 : for(i=0; i<pEList->nExpr; i++){
972 : Expr *p;
973 353 : p = pEList->a[i].pExpr;
974 353 : if( p==0 ) continue;
975 353 : if( pEList->a[i].zName ){
976 79 : char *zName = pEList->a[i].zName;
977 79 : sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, strlen(zName));
978 79 : continue;
979 : }
980 526 : if( p->op==TK_COLUMN && pTabList ){
981 : Table *pTab;
982 : char *zCol;
983 252 : int iCol = p->iColumn;
984 252 : for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
985 : assert( j<pTabList->nSrc );
986 252 : pTab = pTabList->a[j].pTab;
987 252 : if( iCol<0 ) iCol = pTab->iPKey;
988 : assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
989 252 : if( iCol<0 ){
990 0 : zCol = "rowid";
991 : }else{
992 252 : zCol = pTab->aCol[iCol].zName;
993 : }
994 252 : if( !shortNames && !fullNames && p->span.z && p->span.z[0] ){
995 0 : sqlite3VdbeSetColName(v, i, COLNAME_NAME, (char*)p->span.z, p->span.n);
996 252 : }else if( fullNames || (!shortNames && pTabList->nSrc>1) ){
997 0 : char *zName = 0;
998 : char *zTab;
999 :
1000 0 : zTab = pTabList->a[j].zAlias;
1001 0 : if( fullNames || zTab==0 ) zTab = pTab->zName;
1002 0 : sqlite3SetString(&zName, zTab, ".", zCol, (char*)0);
1003 0 : sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, P3_DYNAMIC);
1004 : }else{
1005 252 : sqlite3VdbeSetColName(v, i, COLNAME_NAME, zCol, strlen(zCol));
1006 : }
1007 44 : }else if( p->span.z && p->span.z[0] ){
1008 22 : sqlite3VdbeSetColName(v, i, COLNAME_NAME, (char*)p->span.z, p->span.n);
1009 : /* sqlite3VdbeCompressSpace(v, addr); */
1010 : }else{
1011 : char zName[30];
1012 : assert( p->op!=TK_COLUMN || pTabList==0 );
1013 0 : sprintf(zName, "column%d", i+1);
1014 0 : sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, 0);
1015 : }
1016 : }
1017 153 : generateColumnTypes(pParse, pTabList, pEList);
1018 : }
1019 :
1020 : #ifndef SQLITE_OMIT_COMPOUND_SELECT
1021 : /*
1022 : ** Name of the connection operator, used for error messages.
1023 : */
1024 0 : static const char *selectOpName(int id){
1025 : char *z;
1026 0 : switch( id ){
1027 0 : case TK_ALL: z = "UNION ALL"; break;
1028 0 : case TK_INTERSECT: z = "INTERSECT"; break;
1029 0 : case TK_EXCEPT: z = "EXCEPT"; break;
1030 0 : default: z = "UNION"; break;
1031 : }
1032 0 : return z;
1033 : }
1034 : #endif /* SQLITE_OMIT_COMPOUND_SELECT */
1035 :
1036 : /*
1037 : ** Forward declaration
1038 : */
1039 : static int prepSelectStmt(Parse*, Select*);
1040 :
1041 : /*
1042 : ** Given a SELECT statement, generate a Table structure that describes
1043 : ** the result set of that SELECT.
1044 : */
1045 0 : Table *sqlite3ResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
1046 : Table *pTab;
1047 : int i, j;
1048 : ExprList *pEList;
1049 : Column *aCol, *pCol;
1050 :
1051 0 : while( pSelect->pPrior ) pSelect = pSelect->pPrior;
1052 0 : if( prepSelectStmt(pParse, pSelect) ){
1053 0 : return 0;
1054 : }
1055 0 : if( sqlite3SelectResolve(pParse, pSelect, 0) ){
1056 0 : return 0;
1057 : }
1058 0 : pTab = sqliteMalloc( sizeof(Table) );
1059 0 : if( pTab==0 ){
1060 0 : return 0;
1061 : }
1062 0 : pTab->nRef = 1;
1063 0 : pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
1064 0 : pEList = pSelect->pEList;
1065 0 : pTab->nCol = pEList->nExpr;
1066 : assert( pTab->nCol>0 );
1067 0 : pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
1068 0 : for(i=0, pCol=aCol; i<pTab->nCol; i++, pCol++){
1069 : Expr *p, *pR;
1070 : char *zType;
1071 : char *zName;
1072 : int nName;
1073 : CollSeq *pColl;
1074 : int cnt;
1075 : NameContext sNC;
1076 :
1077 : /* Get an appropriate name for the column
1078 : */
1079 0 : p = pEList->a[i].pExpr;
1080 : assert( p->pRight==0 || p->pRight->token.z==0 || p->pRight->token.z[0]!=0 );
1081 0 : if( (zName = pEList->a[i].zName)!=0 ){
1082 : /* If the column contains an "AS <name>" phrase, use <name> as the name */
1083 0 : zName = sqliteStrDup(zName);
1084 0 : }else if( p->op==TK_DOT
1085 : && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
1086 : /* For columns of the from A.B use B as the name */
1087 0 : zName = sqlite3MPrintf("%T", &pR->token);
1088 0 : }else if( p->span.z && p->span.z[0] ){
1089 : /* Use the original text of the column expression as its name */
1090 0 : zName = sqlite3MPrintf("%T", &p->span);
1091 : }else{
1092 : /* If all else fails, make up a name */
1093 0 : zName = sqlite3MPrintf("column%d", i+1);
1094 : }
1095 0 : sqlite3Dequote(zName);
1096 0 : if( sqlite3MallocFailed() ){
1097 0 : sqliteFree(zName);
1098 0 : sqlite3DeleteTable(pTab);
1099 0 : return 0;
1100 : }
1101 :
1102 : /* Make sure the column name is unique. If the name is not unique,
1103 : ** append a integer to the name so that it becomes unique.
1104 : */
1105 0 : nName = strlen(zName);
1106 0 : for(j=cnt=0; j<i; j++){
1107 0 : if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
1108 0 : zName[nName] = 0;
1109 0 : zName = sqlite3MPrintf("%z:%d", zName, ++cnt);
1110 0 : j = -1;
1111 0 : if( zName==0 ) break;
1112 : }
1113 : }
1114 0 : pCol->zName = zName;
1115 :
1116 : /* Get the typename, type affinity, and collating sequence for the
1117 : ** column.
1118 : */
1119 0 : memset(&sNC, 0, sizeof(sNC));
1120 0 : sNC.pSrcList = pSelect->pSrc;
1121 0 : zType = sqliteStrDup(columnType(&sNC, p, 0, 0, 0));
1122 0 : pCol->zType = zType;
1123 0 : pCol->affinity = sqlite3ExprAffinity(p);
1124 0 : pColl = sqlite3ExprCollSeq(pParse, p);
1125 0 : if( pColl ){
1126 0 : pCol->zColl = sqliteStrDup(pColl->zName);
1127 : }
1128 : }
1129 0 : pTab->iPKey = -1;
1130 0 : return pTab;
1131 : }
1132 :
1133 : /*
1134 : ** Prepare a SELECT statement for processing by doing the following
1135 : ** things:
1136 : **
1137 : ** (1) Make sure VDBE cursor numbers have been assigned to every
1138 : ** element of the FROM clause.
1139 : **
1140 : ** (2) Fill in the pTabList->a[].pTab fields in the SrcList that
1141 : ** defines FROM clause. When views appear in the FROM clause,
1142 : ** fill pTabList->a[].pSelect with a copy of the SELECT statement
1143 : ** that implements the view. A copy is made of the view's SELECT
1144 : ** statement so that we can freely modify or delete that statement
1145 : ** without worrying about messing up the presistent representation
1146 : ** of the view.
1147 : **
1148 : ** (3) Add terms to the WHERE clause to accomodate the NATURAL keyword
1149 : ** on joins and the ON and USING clause of joins.
1150 : **
1151 : ** (4) Scan the list of columns in the result set (pEList) looking
1152 : ** for instances of the "*" operator or the TABLE.* operator.
1153 : ** If found, expand each "*" to be every column in every table
1154 : ** and TABLE.* to be every column in TABLE.
1155 : **
1156 : ** Return 0 on success. If there are problems, leave an error message
1157 : ** in pParse and return non-zero.
1158 : */
1159 153 : static int prepSelectStmt(Parse *pParse, Select *p){
1160 : int i, j, k, rc;
1161 : SrcList *pTabList;
1162 : ExprList *pEList;
1163 : struct SrcList_item *pFrom;
1164 :
1165 153 : if( p==0 || p->pSrc==0 || sqlite3MallocFailed() ){
1166 0 : return 1;
1167 : }
1168 153 : pTabList = p->pSrc;
1169 153 : pEList = p->pEList;
1170 :
1171 : /* Make sure cursor numbers have been assigned to all entries in
1172 : ** the FROM clause of the SELECT statement.
1173 : */
1174 153 : sqlite3SrcListAssignCursors(pParse, p->pSrc);
1175 :
1176 : /* Look up every table named in the FROM clause of the select. If
1177 : ** an entry of the FROM clause is a subquery instead of a table or view,
1178 : ** then create a transient table structure to describe the subquery.
1179 : */
1180 302 : for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
1181 : Table *pTab;
1182 149 : if( pFrom->pTab!=0 ){
1183 : /* This statement has already been prepared. There is no need
1184 : ** to go further. */
1185 : assert( i==0 );
1186 0 : return 0;
1187 : }
1188 149 : if( pFrom->zName==0 ){
1189 : #ifndef SQLITE_OMIT_SUBQUERY
1190 : /* A sub-query in the FROM clause of a SELECT */
1191 : assert( pFrom->pSelect!=0 );
1192 0 : if( pFrom->zAlias==0 ){
1193 0 : pFrom->zAlias =
1194 : sqlite3MPrintf("sqlite_subquery_%p_", (void*)pFrom->pSelect);
1195 : }
1196 : assert( pFrom->pTab==0 );
1197 0 : pFrom->pTab = pTab =
1198 : sqlite3ResultSetOfSelect(pParse, pFrom->zAlias, pFrom->pSelect);
1199 0 : if( pTab==0 ){
1200 0 : return 1;
1201 : }
1202 : /* The isEphem flag indicates that the Table structure has been
1203 : ** dynamically allocated and may be freed at any time. In other words,
1204 : ** pTab is not pointing to a persistent table structure that defines
1205 : ** part of the schema. */
1206 0 : pTab->isEphem = 1;
1207 : #endif
1208 : }else{
1209 : /* An ordinary table or view name in the FROM clause */
1210 : assert( pFrom->pTab==0 );
1211 149 : pFrom->pTab = pTab =
1212 : sqlite3LocateTable(pParse,pFrom->zName,pFrom->zDatabase);
1213 149 : if( pTab==0 ){
1214 0 : return 1;
1215 : }
1216 149 : pTab->nRef++;
1217 : #if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE)
1218 149 : if( pTab->pSelect || IsVirtual(pTab) ){
1219 : /* We reach here if the named table is a really a view */
1220 0 : if( sqlite3ViewGetColumnNames(pParse, pTab) ){
1221 0 : return 1;
1222 : }
1223 : /* If pFrom->pSelect!=0 it means we are dealing with a
1224 : ** view within a view. The SELECT structure has already been
1225 : ** copied by the outer view so we can skip the copy step here
1226 : ** in the inner view.
1227 : */
1228 0 : if( pFrom->pSelect==0 ){
1229 0 : pFrom->pSelect = sqlite3SelectDup(pTab->pSelect);
1230 : }
1231 : }
1232 : #endif
1233 : }
1234 : }
1235 :
1236 : /* Process NATURAL keywords, and ON and USING clauses of joins.
1237 : */
1238 153 : if( sqliteProcessJoin(pParse, p) ) return 1;
1239 :
1240 : /* For every "*" that occurs in the column list, insert the names of
1241 : ** all columns in all tables. And for every TABLE.* insert the names
1242 : ** of all columns in TABLE. The parser inserted a special expression
1243 : ** with the TK_ALL operator for each "*" that it found in the column list.
1244 : ** The following code just has to locate the TK_ALL expressions and expand
1245 : ** each one to the list of all columns in all tables.
1246 : **
1247 : ** The first loop just checks to see if there are any "*" operators
1248 : ** that need expanding.
1249 : */
1250 446 : for(k=0; k<pEList->nExpr; k++){
1251 320 : Expr *pE = pEList->a[k].pExpr;
1252 320 : if( pE->op==TK_ALL ) break;
1253 293 : if( pE->op==TK_DOT && pE->pRight && pE->pRight->op==TK_ALL
1254 0 : && pE->pLeft && pE->pLeft->op==TK_ID ) break;
1255 : }
1256 153 : rc = 0;
1257 153 : if( k<pEList->nExpr ){
1258 : /*
1259 : ** If we get here it means the result set contains one or more "*"
1260 : ** operators that need to be expanded. Loop through each expression
1261 : ** in the result set and expand them one by one.
1262 : */
1263 27 : struct ExprList_item *a = pEList->a;
1264 27 : ExprList *pNew = 0;
1265 27 : int flags = pParse->db->flags;
1266 : int longNames = (flags & SQLITE_FullColNames)!=0 &&
1267 27 : (flags & SQLITE_ShortColNames)==0;
1268 :
1269 54 : for(k=0; k<pEList->nExpr; k++){
1270 27 : Expr *pE = a[k].pExpr;
1271 27 : if( pE->op!=TK_ALL &&
1272 : (pE->op!=TK_DOT || pE->pRight==0 || pE->pRight->op!=TK_ALL) ){
1273 : /* This particular expression does not need to be expanded.
1274 : */
1275 0 : pNew = sqlite3ExprListAppend(pNew, a[k].pExpr, 0);
1276 0 : if( pNew ){
1277 0 : pNew->a[pNew->nExpr-1].zName = a[k].zName;
1278 : }else{
1279 0 : rc = 1;
1280 : }
1281 0 : a[k].pExpr = 0;
1282 0 : a[k].zName = 0;
1283 : }else{
1284 : /* This expression is a "*" or a "TABLE.*" and needs to be
1285 : ** expanded. */
1286 27 : int tableSeen = 0; /* Set to 1 when TABLE matches */
1287 : char *zTName; /* text of name of TABLE */
1288 27 : if( pE->op==TK_DOT && pE->pLeft ){
1289 0 : zTName = sqlite3NameFromToken(&pE->pLeft->token);
1290 : }else{
1291 27 : zTName = 0;
1292 : }
1293 54 : for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
1294 27 : Table *pTab = pFrom->pTab;
1295 27 : char *zTabName = pFrom->zAlias;
1296 27 : if( zTabName==0 || zTabName[0]==0 ){
1297 27 : zTabName = pTab->zName;
1298 : }
1299 27 : if( zTName && (zTabName==0 || zTabName[0]==0 ||
1300 : sqlite3StrICmp(zTName, zTabName)!=0) ){
1301 : continue;
1302 : }
1303 27 : tableSeen = 1;
1304 87 : for(j=0; j<pTab->nCol; j++){
1305 : Expr *pExpr, *pRight;
1306 60 : char *zName = pTab->aCol[j].zName;
1307 :
1308 60 : if( i>0 ){
1309 0 : struct SrcList_item *pLeft = &pTabList->a[i-1];
1310 0 : if( (pLeft[1].jointype & JT_NATURAL)!=0 &&
1311 : columnIndex(pLeft->pTab, zName)>=0 ){
1312 : /* In a NATURAL join, omit the join columns from the
1313 : ** table on the right */
1314 0 : continue;
1315 : }
1316 0 : if( sqlite3IdListIndex(pLeft[1].pUsing, zName)>=0 ){
1317 : /* In a join with a USING clause, omit columns in the
1318 : ** using clause from the table on the right. */
1319 0 : continue;
1320 : }
1321 : }
1322 60 : pRight = sqlite3Expr(TK_ID, 0, 0, 0);
1323 60 : if( pRight==0 ) break;
1324 60 : setToken(&pRight->token, zName);
1325 60 : if( zTabName && (longNames || pTabList->nSrc>1) ){
1326 0 : Expr *pLeft = sqlite3Expr(TK_ID, 0, 0, 0);
1327 0 : pExpr = sqlite3Expr(TK_DOT, pLeft, pRight, 0);
1328 0 : if( pExpr==0 ) break;
1329 0 : setToken(&pLeft->token, zTabName);
1330 0 : setToken(&pExpr->span, sqlite3MPrintf("%s.%s", zTabName, zName));
1331 0 : pExpr->span.dyn = 1;
1332 0 : pExpr->token.z = 0;
1333 0 : pExpr->token.n = 0;
1334 0 : pExpr->token.dyn = 0;
1335 : }else{
1336 60 : pExpr = pRight;
1337 60 : pExpr->span = pExpr->token;
1338 : }
1339 60 : if( longNames ){
1340 0 : pNew = sqlite3ExprListAppend(pNew, pExpr, &pExpr->span);
1341 : }else{
1342 60 : pNew = sqlite3ExprListAppend(pNew, pExpr, &pRight->token);
1343 : }
1344 : }
1345 : }
1346 27 : if( !tableSeen ){
1347 0 : if( zTName ){
1348 0 : sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
1349 : }else{
1350 0 : sqlite3ErrorMsg(pParse, "no tables specified");
1351 : }
1352 0 : rc = 1;
1353 : }
1354 27 : sqliteFree(zTName);
1355 : }
1356 : }
1357 27 : sqlite3ExprListDelete(pEList);
1358 27 : p->pEList = pNew;
1359 : }
1360 153 : return rc;
1361 : }
1362 :
1363 : #ifndef SQLITE_OMIT_COMPOUND_SELECT
1364 : /*
1365 : ** This routine associates entries in an ORDER BY expression list with
1366 : ** columns in a result. For each ORDER BY expression, the opcode of
1367 : ** the top-level node is changed to TK_COLUMN and the iColumn value of
1368 : ** the top-level node is filled in with column number and the iTable
1369 : ** value of the top-level node is filled with iTable parameter.
1370 : **
1371 : ** If there are prior SELECT clauses, they are processed first. A match
1372 : ** in an earlier SELECT takes precedence over a later SELECT.
1373 : **
1374 : ** Any entry that does not match is flagged as an error. The number
1375 : ** of errors is returned.
1376 : */
1377 : static int matchOrderbyToColumn(
1378 : Parse *pParse, /* A place to leave error messages */
1379 : Select *pSelect, /* Match to result columns of this SELECT */
1380 : ExprList *pOrderBy, /* The ORDER BY values to match against columns */
1381 : int iTable, /* Insert this value in iTable */
1382 : int mustComplete /* If TRUE all ORDER BYs must match */
1383 0 : ){
1384 0 : int nErr = 0;
1385 : int i, j;
1386 : ExprList *pEList;
1387 :
1388 0 : if( pSelect==0 || pOrderBy==0 ) return 1;
1389 0 : if( mustComplete ){
1390 0 : for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].done = 0; }
1391 : }
1392 0 : if( prepSelectStmt(pParse, pSelect) ){
1393 0 : return 1;
1394 : }
1395 0 : if( pSelect->pPrior ){
1396 0 : if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
1397 0 : return 1;
1398 : }
1399 : }
1400 0 : pEList = pSelect->pEList;
1401 0 : for(i=0; i<pOrderBy->nExpr; i++){
1402 : struct ExprList_item *pItem;
1403 0 : Expr *pE = pOrderBy->a[i].pExpr;
1404 0 : int iCol = -1;
1405 : char *zLabel;
1406 :
1407 0 : if( pOrderBy->a[i].done ) continue;
1408 0 : if( sqlite3ExprIsInteger(pE, &iCol) ){
1409 0 : if( iCol<=0 || iCol>pEList->nExpr ){
1410 0 : sqlite3ErrorMsg(pParse,
1411 : "ORDER BY position %d should be between 1 and %d",
1412 : iCol, pEList->nExpr);
1413 0 : nErr++;
1414 0 : break;
1415 : }
1416 0 : if( !mustComplete ) continue;
1417 0 : iCol--;
1418 : }
1419 0 : if( iCol<0 && (zLabel = sqlite3NameFromToken(&pE->token))!=0 ){
1420 0 : for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){
1421 : char *zName;
1422 : int isMatch;
1423 0 : if( pItem->zName ){
1424 0 : zName = sqlite3StrDup(pItem->zName);
1425 : }else{
1426 0 : zName = sqlite3NameFromToken(&pItem->pExpr->token);
1427 : }
1428 0 : isMatch = zName && sqlite3StrICmp(zName, zLabel)==0;
1429 0 : sqliteFree(zName);
1430 0 : if( isMatch ){
1431 0 : iCol = j;
1432 0 : break;
1433 : }
1434 : }
1435 0 : sqliteFree(zLabel);
1436 : }
1437 0 : if( iCol>=0 ){
1438 0 : pE->op = TK_COLUMN;
1439 0 : pE->iColumn = iCol;
1440 0 : pE->iTable = iTable;
1441 0 : pE->iAgg = -1;
1442 0 : pOrderBy->a[i].done = 1;
1443 0 : }else if( mustComplete ){
1444 0 : sqlite3ErrorMsg(pParse,
1445 : "ORDER BY term number %d does not match any result column", i+1);
1446 0 : nErr++;
1447 0 : break;
1448 : }
1449 : }
1450 0 : return nErr;
1451 : }
1452 : #endif /* #ifndef SQLITE_OMIT_COMPOUND_SELECT */
1453 :
1454 : /*
1455 : ** Get a VDBE for the given parser context. Create a new one if necessary.
1456 : ** If an error occurs, return NULL and leave a message in pParse.
1457 : */
1458 3714 : Vdbe *sqlite3GetVdbe(Parse *pParse){
1459 3714 : Vdbe *v = pParse->pVdbe;
1460 3714 : if( v==0 ){
1461 620 : v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db);
1462 : }
1463 3714 : return v;
1464 : }
1465 :
1466 :
1467 : /*
1468 : ** Compute the iLimit and iOffset fields of the SELECT based on the
1469 : ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions
1470 : ** that appear in the original SQL statement after the LIMIT and OFFSET
1471 : ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset
1472 : ** are the integer memory register numbers for counters used to compute
1473 : ** the limit and offset. If there is no limit and/or offset, then
1474 : ** iLimit and iOffset are negative.
1475 : **
1476 : ** This routine changes the values of iLimit and iOffset only if
1477 : ** a limit or offset is defined by pLimit and pOffset. iLimit and
1478 : ** iOffset should have been preset to appropriate default values
1479 : ** (usually but not always -1) prior to calling this routine.
1480 : ** Only if pLimit!=0 or pOffset!=0 do the limit registers get
1481 : ** redefined. The UNION ALL operator uses this property to force
1482 : ** the reuse of the same limit and offset registers across multiple
1483 : ** SELECT statements.
1484 : */
1485 153 : static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){
1486 153 : Vdbe *v = 0;
1487 153 : int iLimit = 0;
1488 : int iOffset;
1489 : int addr1, addr2;
1490 :
1491 : /*
1492 : ** "LIMIT -1" always shows all rows. There is some
1493 : ** contraversy about what the correct behavior should be.
1494 : ** The current implementation interprets "LIMIT 0" to mean
1495 : ** no rows.
1496 : */
1497 153 : if( p->pLimit ){
1498 0 : p->iLimit = iLimit = pParse->nMem;
1499 0 : pParse->nMem += 2;
1500 0 : v = sqlite3GetVdbe(pParse);
1501 0 : if( v==0 ) return;
1502 0 : sqlite3ExprCode(pParse, p->pLimit);
1503 0 : sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
1504 0 : sqlite3VdbeAddOp(v, OP_MemStore, iLimit, 0);
1505 : VdbeComment((v, "# LIMIT counter"));
1506 0 : sqlite3VdbeAddOp(v, OP_IfMemZero, iLimit, iBreak);
1507 : }
1508 153 : if( p->pOffset ){
1509 0 : p->iOffset = iOffset = pParse->nMem++;
1510 0 : v = sqlite3GetVdbe(pParse);
1511 0 : if( v==0 ) return;
1512 0 : sqlite3ExprCode(pParse, p->pOffset);
1513 0 : sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
1514 0 : sqlite3VdbeAddOp(v, OP_MemStore, iOffset, p->pLimit==0);
1515 : VdbeComment((v, "# OFFSET counter"));
1516 0 : addr1 = sqlite3VdbeAddOp(v, OP_IfMemPos, iOffset, 0);
1517 0 : sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
1518 0 : sqlite3VdbeAddOp(v, OP_Integer, 0, 0);
1519 0 : sqlite3VdbeJumpHere(v, addr1);
1520 0 : if( p->pLimit ){
1521 0 : sqlite3VdbeAddOp(v, OP_Add, 0, 0);
1522 : }
1523 : }
1524 153 : if( p->pLimit ){
1525 0 : addr1 = sqlite3VdbeAddOp(v, OP_IfMemPos, iLimit, 0);
1526 0 : sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
1527 0 : sqlite3VdbeAddOp(v, OP_MemInt, -1, iLimit+1);
1528 0 : addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
1529 0 : sqlite3VdbeJumpHere(v, addr1);
1530 0 : sqlite3VdbeAddOp(v, OP_MemStore, iLimit+1, 1);
1531 : VdbeComment((v, "# LIMIT+OFFSET"));
1532 0 : sqlite3VdbeJumpHere(v, addr2);
1533 : }
1534 : }
1535 :
1536 : /*
1537 : ** Allocate a virtual index to use for sorting.
1538 : */
1539 0 : static void createSortingIndex(Parse *pParse, Select *p, ExprList *pOrderBy){
1540 0 : if( pOrderBy ){
1541 : int addr;
1542 : assert( pOrderBy->iECursor==0 );
1543 0 : pOrderBy->iECursor = pParse->nTab++;
1544 0 : addr = sqlite3VdbeAddOp(pParse->pVdbe, OP_OpenEphemeral,
1545 : pOrderBy->iECursor, pOrderBy->nExpr+1);
1546 : assert( p->addrOpenEphm[2] == -1 );
1547 0 : p->addrOpenEphm[2] = addr;
1548 : }
1549 0 : }
1550 :
1551 : #ifndef SQLITE_OMIT_COMPOUND_SELECT
1552 : /*
1553 : ** Return the appropriate collating sequence for the iCol-th column of
1554 : ** the result set for the compound-select statement "p". Return NULL if
1555 : ** the column has no default collating sequence.
1556 : **
1557 : ** The collating sequence for the compound select is taken from the
1558 : ** left-most term of the select that has a collating sequence.
1559 : */
1560 0 : static CollSeq *multiSelectCollSeq(Parse *pParse, Select *p, int iCol){
1561 : CollSeq *pRet;
1562 0 : if( p->pPrior ){
1563 0 : pRet = multiSelectCollSeq(pParse, p->pPrior, iCol);
1564 : }else{
1565 0 : pRet = 0;
1566 : }
1567 0 : if( pRet==0 ){
1568 0 : pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
1569 : }
1570 0 : return pRet;
1571 : }
1572 : #endif /* SQLITE_OMIT_COMPOUND_SELECT */
1573 :
1574 : #ifndef SQLITE_OMIT_COMPOUND_SELECT
1575 : /*
1576 : ** This routine is called to process a query that is really the union
1577 : ** or intersection of two or more separate queries.
1578 : **
1579 : ** "p" points to the right-most of the two queries. the query on the
1580 : ** left is p->pPrior. The left query could also be a compound query
1581 : ** in which case this routine will be called recursively.
1582 : **
1583 : ** The results of the total query are to be written into a destination
1584 : ** of type eDest with parameter iParm.
1585 : **
1586 : ** Example 1: Consider a three-way compound SQL statement.
1587 : **
1588 : ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
1589 : **
1590 : ** This statement is parsed up as follows:
1591 : **
1592 : ** SELECT c FROM t3
1593 : ** |
1594 : ** `-----> SELECT b FROM t2
1595 : ** |
1596 : ** `------> SELECT a FROM t1
1597 : **
1598 : ** The arrows in the diagram above represent the Select.pPrior pointer.
1599 : ** So if this routine is called with p equal to the t3 query, then
1600 : ** pPrior will be the t2 query. p->op will be TK_UNION in this case.
1601 : **
1602 : ** Notice that because of the way SQLite parses compound SELECTs, the
1603 : ** individual selects always group from left to right.
1604 : */
1605 : static int multiSelect(
1606 : Parse *pParse, /* Parsing context */
1607 : Select *p, /* The right-most of SELECTs to be coded */
1608 : int eDest, /* \___ Store query results as specified */
1609 : int iParm, /* / by these two parameters. */
1610 : char *aff /* If eDest is SRT_Union, the affinity string */
1611 0 : ){
1612 0 : int rc = SQLITE_OK; /* Success code from a subroutine */
1613 : Select *pPrior; /* Another SELECT immediately to our left */
1614 : Vdbe *v; /* Generate code to this VDBE */
1615 : int nCol; /* Number of columns in the result set */
1616 : ExprList *pOrderBy; /* The ORDER BY clause on p */
1617 : int aSetP2[2]; /* Set P2 value of these op to number of columns */
1618 0 : int nSetP2 = 0; /* Number of slots in aSetP2[] used */
1619 :
1620 : /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
1621 : ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
1622 : */
1623 0 : if( p==0 || p->pPrior==0 ){
1624 0 : rc = 1;
1625 0 : goto multi_select_end;
1626 : }
1627 0 : pPrior = p->pPrior;
1628 : assert( pPrior->pRightmost!=pPrior );
1629 : assert( pPrior->pRightmost==p->pRightmost );
1630 0 : if( pPrior->pOrderBy ){
1631 0 : sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
1632 : selectOpName(p->op));
1633 0 : rc = 1;
1634 0 : goto multi_select_end;
1635 : }
1636 0 : if( pPrior->pLimit ){
1637 0 : sqlite3ErrorMsg(pParse,"LIMIT clause should come after %s not before",
1638 : selectOpName(p->op));
1639 0 : rc = 1;
1640 0 : goto multi_select_end;
1641 : }
1642 :
1643 : /* Make sure we have a valid query engine. If not, create a new one.
1644 : */
1645 0 : v = sqlite3GetVdbe(pParse);
1646 0 : if( v==0 ){
1647 0 : rc = 1;
1648 0 : goto multi_select_end;
1649 : }
1650 :
1651 : /* Create the destination temporary table if necessary
1652 : */
1653 0 : if( eDest==SRT_EphemTab ){
1654 : assert( p->pEList );
1655 : assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
1656 0 : aSetP2[nSetP2++] = sqlite3VdbeAddOp(v, OP_OpenEphemeral, iParm, 0);
1657 0 : eDest = SRT_Table;
1658 : }
1659 :
1660 : /* Generate code for the left and right SELECT statements.
1661 : */
1662 0 : pOrderBy = p->pOrderBy;
1663 0 : switch( p->op ){
1664 : case TK_ALL: {
1665 0 : if( pOrderBy==0 ){
1666 0 : int addr = 0;
1667 : assert( !pPrior->pLimit );
1668 0 : pPrior->pLimit = p->pLimit;
1669 0 : pPrior->pOffset = p->pOffset;
1670 0 : rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
1671 0 : p->pLimit = 0;
1672 0 : p->pOffset = 0;
1673 0 : if( rc ){
1674 0 : goto multi_select_end;
1675 : }
1676 0 : p->pPrior = 0;
1677 0 : p->iLimit = pPrior->iLimit;
1678 0 : p->iOffset = pPrior->iOffset;
1679 0 : if( p->iLimit>=0 ){
1680 0 : addr = sqlite3VdbeAddOp(v, OP_IfMemZero, p->iLimit, 0);
1681 : VdbeComment((v, "# Jump ahead if LIMIT reached"));
1682 : }
1683 0 : rc = sqlite3Select(pParse, p, eDest, iParm, 0, 0, 0, aff);
1684 0 : p->pPrior = pPrior;
1685 0 : if( rc ){
1686 0 : goto multi_select_end;
1687 : }
1688 0 : if( addr ){
1689 0 : sqlite3VdbeJumpHere(v, addr);
1690 : }
1691 0 : break;
1692 : }
1693 : /* For UNION ALL ... ORDER BY fall through to the next case */
1694 : }
1695 : case TK_EXCEPT:
1696 : case TK_UNION: {
1697 : int unionTab; /* Cursor number of the temporary table holding result */
1698 0 : int op = 0; /* One of the SRT_ operations to apply to self */
1699 : int priorOp; /* The SRT_ operation to apply to prior selects */
1700 : Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */
1701 : int addr;
1702 :
1703 0 : priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
1704 0 : if( eDest==priorOp && pOrderBy==0 && !p->pLimit && !p->pOffset ){
1705 : /* We can reuse a temporary table generated by a SELECT to our
1706 : ** right.
1707 : */
1708 0 : unionTab = iParm;
1709 : }else{
1710 : /* We will need to create our own temporary table to hold the
1711 : ** intermediate results.
1712 : */
1713 0 : unionTab = pParse->nTab++;
1714 0 : if( pOrderBy && matchOrderbyToColumn(pParse, p, pOrderBy, unionTab,1) ){
1715 0 : rc = 1;
1716 0 : goto multi_select_end;
1717 : }
1718 0 : addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, unionTab, 0);
1719 0 : if( priorOp==SRT_Table ){
1720 : assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
1721 0 : aSetP2[nSetP2++] = addr;
1722 : }else{
1723 : assert( p->addrOpenEphm[0] == -1 );
1724 0 : p->addrOpenEphm[0] = addr;
1725 0 : p->pRightmost->usesEphm = 1;
1726 : }
1727 0 : createSortingIndex(pParse, p, pOrderBy);
1728 : assert( p->pEList );
1729 : }
1730 :
1731 : /* Code the SELECT statements to our left
1732 : */
1733 : assert( !pPrior->pOrderBy );
1734 0 : rc = sqlite3Select(pParse, pPrior, priorOp, unionTab, 0, 0, 0, aff);
1735 0 : if( rc ){
1736 0 : goto multi_select_end;
1737 : }
1738 :
1739 : /* Code the current SELECT statement
1740 : */
1741 0 : switch( p->op ){
1742 0 : case TK_EXCEPT: op = SRT_Except; break;
1743 0 : case TK_UNION: op = SRT_Union; break;
1744 0 : case TK_ALL: op = SRT_Table; break;
1745 : }
1746 0 : p->pPrior = 0;
1747 0 : p->pOrderBy = 0;
1748 0 : p->disallowOrderBy = pOrderBy!=0;
1749 0 : pLimit = p->pLimit;
1750 0 : p->pLimit = 0;
1751 0 : pOffset = p->pOffset;
1752 0 : p->pOffset = 0;
1753 0 : rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff);
1754 0 : p->pPrior = pPrior;
1755 0 : p->pOrderBy = pOrderBy;
1756 0 : sqlite3ExprDelete(p->pLimit);
1757 0 : p->pLimit = pLimit;
1758 0 : p->pOffset = pOffset;
1759 0 : p->iLimit = -1;
1760 0 : p->iOffset = -1;
1761 0 : if( rc ){
1762 0 : goto multi_select_end;
1763 : }
1764 :
1765 :
1766 : /* Convert the data in the temporary table into whatever form
1767 : ** it is that we currently need.
1768 : */
1769 0 : if( eDest!=priorOp || unionTab!=iParm ){
1770 : int iCont, iBreak, iStart;
1771 : assert( p->pEList );
1772 0 : if( eDest==SRT_Callback ){
1773 0 : Select *pFirst = p;
1774 0 : while( pFirst->pPrior ) pFirst = pFirst->pPrior;
1775 0 : generateColumnNames(pParse, 0, pFirst->pEList);
1776 : }
1777 0 : iBreak = sqlite3VdbeMakeLabel(v);
1778 0 : iCont = sqlite3VdbeMakeLabel(v);
1779 0 : computeLimitRegisters(pParse, p, iBreak);
1780 0 : sqlite3VdbeAddOp(v, OP_Rewind, unionTab, iBreak);
1781 0 : iStart = sqlite3VdbeCurrentAddr(v);
1782 0 : rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
1783 : pOrderBy, -1, eDest, iParm,
1784 : iCont, iBreak, 0);
1785 0 : if( rc ){
1786 0 : rc = 1;
1787 0 : goto multi_select_end;
1788 : }
1789 0 : sqlite3VdbeResolveLabel(v, iCont);
1790 0 : sqlite3VdbeAddOp(v, OP_Next, unionTab, iStart);
1791 0 : sqlite3VdbeResolveLabel(v, iBreak);
1792 0 : sqlite3VdbeAddOp(v, OP_Close, unionTab, 0);
1793 : }
1794 0 : break;
1795 : }
1796 : case TK_INTERSECT: {
1797 : int tab1, tab2;
1798 : int iCont, iBreak, iStart;
1799 : Expr *pLimit, *pOffset;
1800 : int addr;
1801 :
1802 : /* INTERSECT is different from the others since it requires
1803 : ** two temporary tables. Hence it has its own case. Begin
1804 : ** by allocating the tables we will need.
1805 : */
1806 0 : tab1 = pParse->nTab++;
1807 0 : tab2 = pParse->nTab++;
1808 0 : if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,tab1,1) ){
1809 0 : rc = 1;
1810 0 : goto multi_select_end;
1811 : }
1812 0 : createSortingIndex(pParse, p, pOrderBy);
1813 :
1814 0 : addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, tab1, 0);
1815 : assert( p->addrOpenEphm[0] == -1 );
1816 0 : p->addrOpenEphm[0] = addr;
1817 0 : p->pRightmost->usesEphm = 1;
1818 : assert( p->pEList );
1819 :
1820 : /* Code the SELECTs to our left into temporary table "tab1".
1821 : */
1822 0 : rc = sqlite3Select(pParse, pPrior, SRT_Union, tab1, 0, 0, 0, aff);
1823 0 : if( rc ){
1824 0 : goto multi_select_end;
1825 : }
1826 :
1827 : /* Code the current SELECT into temporary table "tab2"
1828 : */
1829 0 : addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, tab2, 0);
1830 : assert( p->addrOpenEphm[1] == -1 );
1831 0 : p->addrOpenEphm[1] = addr;
1832 0 : p->pPrior = 0;
1833 0 : pLimit = p->pLimit;
1834 0 : p->pLimit = 0;
1835 0 : pOffset = p->pOffset;
1836 0 : p->pOffset = 0;
1837 0 : rc = sqlite3Select(pParse, p, SRT_Union, tab2, 0, 0, 0, aff);
1838 0 : p->pPrior = pPrior;
1839 0 : sqlite3ExprDelete(p->pLimit);
1840 0 : p->pLimit = pLimit;
1841 0 : p->pOffset = pOffset;
1842 0 : if( rc ){
1843 0 : goto multi_select_end;
1844 : }
1845 :
1846 : /* Generate code to take the intersection of the two temporary
1847 : ** tables.
1848 : */
1849 : assert( p->pEList );
1850 0 : if( eDest==SRT_Callback ){
1851 0 : Select *pFirst = p;
1852 0 : while( pFirst->pPrior ) pFirst = pFirst->pPrior;
1853 0 : generateColumnNames(pParse, 0, pFirst->pEList);
1854 : }
1855 0 : iBreak = sqlite3VdbeMakeLabel(v);
1856 0 : iCont = sqlite3VdbeMakeLabel(v);
1857 0 : computeLimitRegisters(pParse, p, iBreak);
1858 0 : sqlite3VdbeAddOp(v, OP_Rewind, tab1, iBreak);
1859 0 : iStart = sqlite3VdbeAddOp(v, OP_RowKey, tab1, 0);
1860 0 : sqlite3VdbeAddOp(v, OP_NotFound, tab2, iCont);
1861 0 : rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
1862 : pOrderBy, -1, eDest, iParm,
1863 : iCont, iBreak, 0);
1864 0 : if( rc ){
1865 0 : rc = 1;
1866 0 : goto multi_select_end;
1867 : }
1868 0 : sqlite3VdbeResolveLabel(v, iCont);
1869 0 : sqlite3VdbeAddOp(v, OP_Next, tab1, iStart);
1870 0 : sqlite3VdbeResolveLabel(v, iBreak);
1871 0 : sqlite3VdbeAddOp(v, OP_Close, tab2, 0);
1872 0 : sqlite3VdbeAddOp(v, OP_Close, tab1, 0);
1873 : break;
1874 : }
1875 : }
1876 :
1877 : /* Make sure all SELECTs in the statement have the same number of elements
1878 : ** in their result sets.
1879 : */
1880 : assert( p->pEList && pPrior->pEList );
1881 0 : if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
1882 0 : sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
1883 : " do not have the same number of result columns", selectOpName(p->op));
1884 0 : rc = 1;
1885 0 : goto multi_select_end;
1886 : }
1887 :
1888 : /* Set the number of columns in temporary tables
1889 : */
1890 0 : nCol = p->pEList->nExpr;
1891 0 : while( nSetP2 ){
1892 0 : sqlite3VdbeChangeP2(v, aSetP2[--nSetP2], nCol);
1893 : }
1894 :
1895 : /* Compute collating sequences used by either the ORDER BY clause or
1896 : ** by any temporary tables needed to implement the compound select.
1897 : ** Attach the KeyInfo structure to all temporary tables. Invoke the
1898 : ** ORDER BY processing if there is an ORDER BY clause.
1899 : **
1900 : ** This section is run by the right-most SELECT statement only.
1901 : ** SELECT statements to the left always skip this part. The right-most
1902 : ** SELECT might also skip this part if it has no ORDER BY clause and
1903 : ** no temp tables are required.
1904 : */
1905 0 : if( pOrderBy || p->usesEphm ){
1906 : int i; /* Loop counter */
1907 : KeyInfo *pKeyInfo; /* Collating sequence for the result set */
1908 : Select *pLoop; /* For looping through SELECT statements */
1909 : int nKeyCol; /* Number of entries in pKeyInfo->aCol[] */
1910 : CollSeq **apColl;
1911 : CollSeq **aCopy;
1912 :
1913 : assert( p->pRightmost==p );
1914 0 : nKeyCol = nCol + (pOrderBy ? pOrderBy->nExpr : 0);
1915 0 : pKeyInfo = sqliteMalloc(sizeof(*pKeyInfo)+nKeyCol*(sizeof(CollSeq*) + 1));
1916 0 : if( !pKeyInfo ){
1917 0 : rc = SQLITE_NOMEM;
1918 0 : goto multi_select_end;
1919 : }
1920 :
1921 0 : pKeyInfo->enc = ENC(pParse->db);
1922 0 : pKeyInfo->nField = nCol;
1923 :
1924 0 : for(i=0, apColl=pKeyInfo->aColl; i<nCol; i++, apColl++){
1925 0 : *apColl = multiSelectCollSeq(pParse, p, i);
1926 0 : if( 0==*apColl ){
1927 0 : *apColl = pParse->db->pDfltColl;
1928 : }
1929 : }
1930 :
1931 0 : for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
1932 0 : for(i=0; i<2; i++){
1933 0 : int addr = pLoop->addrOpenEphm[i];
1934 0 : if( addr<0 ){
1935 : /* If [0] is unused then [1] is also unused. So we can
1936 : ** always safely abort as soon as the first unused slot is found */
1937 : assert( pLoop->addrOpenEphm[1]<0 );
1938 0 : break;
1939 : }
1940 0 : sqlite3VdbeChangeP2(v, addr, nCol);
1941 0 : sqlite3VdbeChangeP3(v, addr, (char*)pKeyInfo, P3_KEYINFO);
1942 0 : pLoop->addrOpenEphm[i] = -1;
1943 : }
1944 : }
1945 :
1946 0 : if( pOrderBy ){
1947 0 : struct ExprList_item *pOTerm = pOrderBy->a;
1948 0 : int nOrderByExpr = pOrderBy->nExpr;
1949 : int addr;
1950 : u8 *pSortOrder;
1951 :
1952 0 : aCopy = &pKeyInfo->aColl[nOrderByExpr];
1953 0 : pSortOrder = pKeyInfo->aSortOrder = (u8*)&aCopy[nCol];
1954 0 : memcpy(aCopy, pKeyInfo->aColl, nCol*sizeof(CollSeq*));
1955 0 : apColl = pKeyInfo->aColl;
1956 0 : for(i=0; i<nOrderByExpr; i++, pOTerm++, apColl++, pSortOrder++){
1957 0 : Expr *pExpr = pOTerm->pExpr;
1958 0 : if( (pExpr->flags & EP_ExpCollate) ){
1959 : assert( pExpr->pColl!=0 );
1960 0 : *apColl = pExpr->pColl;
1961 : }else{
1962 0 : *apColl = aCopy[pExpr->iColumn];
1963 : }
1964 0 : *pSortOrder = pOTerm->sortOrder;
1965 : }
1966 : assert( p->pRightmost==p );
1967 : assert( p->addrOpenEphm[2]>=0 );
1968 0 : addr = p->addrOpenEphm[2];
1969 0 : sqlite3VdbeChangeP2(v, addr, p->pEList->nExpr+2);
1970 0 : pKeyInfo->nField = nOrderByExpr;
1971 0 : sqlite3VdbeChangeP3(v, addr, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
1972 0 : pKeyInfo = 0;
1973 0 : generateSortTail(pParse, p, v, p->pEList->nExpr, eDest, iParm);
1974 : }
1975 :
1976 0 : sqliteFree(pKeyInfo);
1977 : }
1978 :
1979 0 : multi_select_end:
1980 0 : return rc;
1981 : }
1982 : #endif /* SQLITE_OMIT_COMPOUND_SELECT */
1983 :
1984 : #ifndef SQLITE_OMIT_VIEW
1985 : /*
1986 : ** Scan through the expression pExpr. Replace every reference to
1987 : ** a column in table number iTable with a copy of the iColumn-th
1988 : ** entry in pEList. (But leave references to the ROWID column
1989 : ** unchanged.)
1990 : **
1991 : ** This routine is part of the flattening procedure. A subquery
1992 : ** whose result set is defined by pEList appears as entry in the
1993 : ** FROM clause of a SELECT such that the VDBE cursor assigned to that
1994 : ** FORM clause entry is iTable. This routine make the necessary
1995 : ** changes to pExpr so that it refers directly to the source table
1996 : ** of the subquery rather the result set of the subquery.
1997 : */
1998 : static void substExprList(ExprList*,int,ExprList*); /* Forward Decl */
1999 : static void substSelect(Select *, int, ExprList *); /* Forward Decl */
2000 0 : static void substExpr(Expr *pExpr, int iTable, ExprList *pEList){
2001 0 : if( pExpr==0 ) return;
2002 0 : if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
2003 0 : if( pExpr->iColumn<0 ){
2004 0 : pExpr->op = TK_NULL;
2005 : }else{
2006 : Expr *pNew;
2007 : assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
2008 : assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
2009 0 : pNew = pEList->a[pExpr->iColumn].pExpr;
2010 : assert( pNew!=0 );
2011 0 : pExpr->op = pNew->op;
2012 : assert( pExpr->pLeft==0 );
2013 0 : pExpr->pLeft = sqlite3ExprDup(pNew->pLeft);
2014 : assert( pExpr->pRight==0 );
2015 0 : pExpr->pRight = sqlite3ExprDup(pNew->pRight);
2016 : assert( pExpr->pList==0 );
2017 0 : pExpr->pList = sqlite3ExprListDup(pNew->pList);
2018 0 : pExpr->iTable = pNew->iTable;
2019 0 : pExpr->pTab = pNew->pTab;
2020 0 : pExpr->iColumn = pNew->iColumn;
2021 0 : pExpr->iAgg = pNew->iAgg;
2022 0 : sqlite3TokenCopy(&pExpr->token, &pNew->token);
2023 0 : sqlite3TokenCopy(&pExpr->span, &pNew->span);
2024 0 : pExpr->pSelect = sqlite3SelectDup(pNew->pSelect);
2025 0 : pExpr->flags = pNew->flags;
2026 : }
2027 : }else{
2028 0 : substExpr(pExpr->pLeft, iTable, pEList);
2029 0 : substExpr(pExpr->pRight, iTable, pEList);
2030 0 : substSelect(pExpr->pSelect, iTable, pEList);
2031 0 : substExprList(pExpr->pList, iTable, pEList);
2032 : }
2033 : }
2034 0 : static void substExprList(ExprList *pList, int iTable, ExprList *pEList){
2035 : int i;
2036 0 : if( pList==0 ) return;
2037 0 : for(i=0; i<pList->nExpr; i++){
2038 0 : substExpr(pList->a[i].pExpr, iTable, pEList);
2039 : }
2040 : }
2041 0 : static void substSelect(Select *p, int iTable, ExprList *pEList){
2042 0 : if( !p ) return;
2043 0 : substExprList(p->pEList, iTable, pEList);
2044 0 : substExprList(p->pGroupBy, iTable, pEList);
2045 0 : substExprList(p->pOrderBy, iTable, pEList);
2046 0 : substExpr(p->pHaving, iTable, pEList);
2047 0 : substExpr(p->pWhere, iTable, pEList);
2048 : }
2049 : #endif /* !defined(SQLITE_OMIT_VIEW) */
2050 :
2051 : #ifndef SQLITE_OMIT_VIEW
2052 : /*
2053 : ** This routine attempts to flatten subqueries in order to speed
2054 : ** execution. It returns 1 if it makes changes and 0 if no flattening
2055 : ** occurs.
2056 : **
2057 : ** To understand the concept of flattening, consider the following
2058 : ** query:
2059 : **
2060 : ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
2061 : **
2062 : ** The default way of implementing this query is to execute the
2063 : ** subquery first and store the results in a temporary table, then
2064 : ** run the outer query on that temporary table. This requires two
2065 : ** passes over the data. Furthermore, because the temporary table
2066 : ** has no indices, the WHERE clause on the outer query cannot be
2067 : ** optimized.
2068 : **
2069 : ** This routine attempts to rewrite queries such as the above into
2070 : ** a single flat select, like this:
2071 : **
2072 : ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
2073 : **
2074 : ** The code generated for this simpification gives the same result
2075 : ** but only has to scan the data once. And because indices might
2076 : ** exist on the table t1, a complete scan of the data might be
2077 : ** avoided.
2078 : **
2079 : ** Flattening is only attempted if all of the following are true:
2080 : **
2081 : ** (1) The subquery and the outer query do not both use aggregates.
2082 : **
2083 : ** (2) The subquery is not an aggregate or the outer query is not a join.
2084 : **
2085 : ** (3) The subquery is not the right operand of a left outer join, or
2086 : ** the subquery is not itself a join. (Ticket #306)
2087 : **
2088 : ** (4) The subquery is not DISTINCT or the outer query is not a join.
2089 : **
2090 : ** (5) The subquery is not DISTINCT or the outer query does not use
2091 : ** aggregates.
2092 : **
2093 : ** (6) The subquery does not use aggregates or the outer query is not
2094 : ** DISTINCT.
2095 : **
2096 : ** (7) The subquery has a FROM clause.
2097 : **
2098 : ** (8) The subquery does not use LIMIT or the outer query is not a join.
2099 : **
2100 : ** (9) The subquery does not use LIMIT or the outer query does not use
2101 : ** aggregates.
2102 : **
2103 : ** (10) The subquery does not use aggregates or the outer query does not
2104 : ** use LIMIT.
2105 : **
2106 : ** (11) The subquery and the outer query do not both have ORDER BY clauses.
2107 : **
2108 : ** (12) The subquery is not the right term of a LEFT OUTER JOIN or the
2109 : ** subquery has no WHERE clause. (added by ticket #350)
2110 : **
2111 : ** (13) The subquery and outer query do not both use LIMIT
2112 : **
2113 : ** (14) The subquery does not use OFFSET
2114 : **
2115 : ** In this routine, the "p" parameter is a pointer to the outer query.
2116 : ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
2117 : ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
2118 : **
2119 : ** If flattening is not attempted, this routine is a no-op and returns 0.
2120 : ** If flattening is attempted this routine returns 1.
2121 : **
2122 : ** All of the expression analysis must occur on both the outer query and
2123 : ** the subquery before this routine runs.
2124 : */
2125 : static int flattenSubquery(
2126 : Select *p, /* The parent or outer SELECT statement */
2127 : int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
2128 : int isAgg, /* True if outer SELECT uses aggregate functions */
2129 : int subqueryIsAgg /* True if the subquery uses aggregate functions */
2130 0 : ){
2131 : Select *pSub; /* The inner query or "subquery" */
2132 : SrcList *pSrc; /* The FROM clause of the outer query */
2133 : SrcList *pSubSrc; /* The FROM clause of the subquery */
2134 : ExprList *pList; /* The result set of the outer query */
2135 : int iParent; /* VDBE cursor number of the pSub result set temp table */
2136 : int i; /* Loop counter */
2137 : Expr *pWhere; /* The WHERE clause */
2138 : struct SrcList_item *pSubitem; /* The subquery */
2139 :
2140 : /* Check to see if flattening is permitted. Return 0 if not.
2141 : */
2142 0 : if( p==0 ) return 0;
2143 0 : pSrc = p->pSrc;
2144 : assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
2145 0 : pSubitem = &pSrc->a[iFrom];
2146 0 : pSub = pSubitem->pSelect;
2147 : assert( pSub!=0 );
2148 0 : if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */
2149 0 : if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; /* Restriction (2) */
2150 0 : pSubSrc = pSub->pSrc;
2151 : assert( pSubSrc );
2152 : /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
2153 : ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
2154 : ** because they could be computed at compile-time. But when LIMIT and OFFSET
2155 : ** became arbitrary expressions, we were forced to add restrictions (13)
2156 : ** and (14). */
2157 0 : if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */
2158 0 : if( pSub->pOffset ) return 0; /* Restriction (14) */
2159 0 : if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
2160 0 : if( (pSub->isDistinct || pSub->pLimit)
2161 : && (pSrc->nSrc>1 || isAgg) ){ /* Restrictions (4)(5)(8)(9) */
2162 0 : return 0;
2163 : }
2164 0 : if( p->isDistinct && subqueryIsAgg ) return 0; /* Restriction (6) */
2165 0 : if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ){
2166 0 : return 0; /* Restriction (11) */
2167 : }
2168 :
2169 : /* Restriction 3: If the subquery is a join, make sure the subquery is
2170 : ** not used as the right operand of an outer join. Examples of why this
2171 : ** is not allowed:
2172 : **
2173 : ** t1 LEFT OUTER JOIN (t2 JOIN t3)
2174 : **
2175 : ** If we flatten the above, we would get
2176 : **
2177 : ** (t1 LEFT OUTER JOIN t2) JOIN t3
2178 : **
2179 : ** which is not at all the same thing.
2180 : */
2181 0 : if( pSubSrc->nSrc>1 && (pSubitem->jointype & JT_OUTER)!=0 ){
2182 0 : return 0;
2183 : }
2184 :
2185 : /* Restriction 12: If the subquery is the right operand of a left outer
2186 : ** join, make sure the subquery has no WHERE clause.
2187 : ** An examples of why this is not allowed:
2188 : **
2189 : ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
2190 : **
2191 : ** If we flatten the above, we would get
2192 : **
2193 : ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
2194 : **
2195 : ** But the t2.x>0 test will always fail on a NULL row of t2, which
2196 : ** effectively converts the OUTER JOIN into an INNER JOIN.
2197 : */
2198 0 : if( (pSubitem->jointype & JT_OUTER)!=0 && pSub->pWhere!=0 ){
2199 0 : return 0;
2200 : }
2201 :
2202 : /* If we reach this point, it means flattening is permitted for the
2203 : ** iFrom-th entry of the FROM clause in the outer query.
2204 : */
2205 :
2206 : /* Move all of the FROM elements of the subquery into the
2207 : ** the FROM clause of the outer query. Before doing this, remember
2208 : ** the cursor number for the original outer query FROM element in
2209 : ** iParent. The iParent cursor will never be used. Subsequent code
2210 : ** will scan expressions looking for iParent references and replace
2211 : ** those references with expressions that resolve to the subquery FROM
2212 : ** elements we are now copying in.
2213 : */
2214 0 : iParent = pSubitem->iCursor;
2215 : {
2216 0 : int nSubSrc = pSubSrc->nSrc;
2217 0 : int jointype = pSubitem->jointype;
2218 :
2219 0 : sqlite3DeleteTable(pSubitem->pTab);
2220 0 : sqliteFree(pSubitem->zDatabase);
2221 0 : sqliteFree(pSubitem->zName);
2222 0 : sqliteFree(pSubitem->zAlias);
2223 0 : if( nSubSrc>1 ){
2224 0 : int extra = nSubSrc - 1;
2225 0 : for(i=1; i<nSubSrc; i++){
2226 0 : pSrc = sqlite3SrcListAppend(pSrc, 0, 0);
2227 : }
2228 0 : p->pSrc = pSrc;
2229 0 : for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){
2230 0 : pSrc->a[i] = pSrc->a[i-extra];
2231 : }
2232 : }
2233 0 : for(i=0; i<nSubSrc; i++){
2234 0 : pSrc->a[i+iFrom] = pSubSrc->a[i];
2235 0 : memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
2236 : }
2237 0 : pSrc->a[iFrom].jointype = jointype;
2238 : }
2239 :
2240 : /* Now begin substituting subquery result set expressions for
2241 : ** references to the iParent in the outer query.
2242 : **
2243 : ** Example:
2244 : **
2245 : ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
2246 : ** \ \_____________ subquery __________/ /
2247 : ** \_____________________ outer query ______________________________/
2248 : **
2249 : ** We look at every expression in the outer query and every place we see
2250 : ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
2251 : */
2252 0 : pList = p->pEList;
2253 0 : for(i=0; i<pList->nExpr; i++){
2254 : Expr *pExpr;
2255 0 : if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
2256 0 : pList->a[i].zName = sqliteStrNDup((char*)pExpr->span.z, pExpr->span.n);
2257 : }
2258 : }
2259 0 : substExprList(p->pEList, iParent, pSub->pEList);
2260 0 : if( isAgg ){
2261 0 : substExprList(p->pGroupBy, iParent, pSub->pEList);
2262 0 : substExpr(p->pHaving, iParent, pSub->pEList);
2263 : }
2264 0 : if( pSub->pOrderBy ){
2265 : assert( p->pOrderBy==0 );
2266 0 : p->pOrderBy = pSub->pOrderBy;
2267 0 : pSub->pOrderBy = 0;
2268 0 : }else if( p->pOrderBy ){
2269 0 : substExprList(p->pOrderBy, iParent, pSub->pEList);
2270 : }
2271 0 : if( pSub->pWhere ){
2272 0 : pWhere = sqlite3ExprDup(pSub->pWhere);
2273 : }else{
2274 0 : pWhere = 0;
2275 : }
2276 0 : if( subqueryIsAgg ){
2277 : assert( p->pHaving==0 );
2278 0 : p->pHaving = p->pWhere;
2279 0 : p->pWhere = pWhere;
2280 0 : substExpr(p->pHaving, iParent, pSub->pEList);
2281 0 : p->pHaving = sqlite3ExprAnd(p->pHaving, sqlite3ExprDup(pSub->pHaving));
2282 : assert( p->pGroupBy==0 );
2283 0 : p->pGroupBy = sqlite3ExprListDup(pSub->pGroupBy);
2284 : }else{
2285 0 : substExpr(p->pWhere, iParent, pSub->pEList);
2286 0 : p->pWhere = sqlite3ExprAnd(p->pWhere, pWhere);
2287 : }
2288 :
2289 : /* The flattened query is distinct if either the inner or the
2290 : ** outer query is distinct.
2291 : */
2292 0 : p->isDistinct = p->isDistinct || pSub->isDistinct;
2293 :
2294 : /*
2295 : ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
2296 : **
2297 : ** One is tempted to try to add a and b to combine the limits. But this
2298 : ** does not work if either limit is negative.
2299 : */
2300 0 : if( pSub->pLimit ){
2301 0 : p->pLimit = pSub->pLimit;
2302 0 : pSub->pLimit = 0;
2303 : }
2304 :
2305 : /* Finially, delete what is left of the subquery and return
2306 : ** success.
2307 : */
2308 0 : sqlite3SelectDelete(pSub);
2309 0 : return 1;
2310 : }
2311 : #endif /* SQLITE_OMIT_VIEW */
2312 :
2313 : /*
2314 : ** Analyze the SELECT statement passed in as an argument to see if it
2315 : ** is a simple min() or max() query. If it is and this query can be
2316 : ** satisfied using a single seek to the beginning or end of an index,
2317 : ** then generate the code for this SELECT and return 1. If this is not a
2318 : ** simple min() or max() query, then return 0;
2319 : **
2320 : ** A simply min() or max() query looks like this:
2321 : **
2322 : ** SELECT min(a) FROM table;
2323 : ** SELECT max(a) FROM table;
2324 : **
2325 : ** The query may have only a single table in its FROM argument. There
2326 : ** can be no GROUP BY or HAVING or WHERE clauses. The result set must
2327 : ** be the min() or max() of a single column of the table. The column
2328 : ** in the min() or max() function must be indexed.
2329 : **
2330 : ** The parameters to this routine are the same as for sqlite3Select().
2331 : ** See the header comment on that routine for additional information.
2332 : */
2333 153 : static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){
2334 : Expr *pExpr;
2335 : int iCol;
2336 : Table *pTab;
2337 : Index *pIdx;
2338 : int base;
2339 : Vdbe *v;
2340 : int seekOp;
2341 : ExprList *pEList, *pList, eList;
2342 : struct ExprList_item eListItem;
2343 : SrcList *pSrc;
2344 : int brk;
2345 : int iDb;
2346 :
2347 : /* Check to see if this query is a simple min() or max() query. Return
2348 : ** zero if it is not.
2349 : */
2350 153 : if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
2351 81 : pSrc = p->pSrc;
2352 81 : if( pSrc->nSrc!=1 ) return 0;
2353 72 : pEList = p->pEList;
2354 72 : if( pEList->nExpr!=1 ) return 0;
2355 19 : pExpr = pEList->a[0].pExpr;
2356 19 : if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
2357 13 : pList = pExpr->pList;
2358 13 : if( pList==0 || pList->nExpr!=1 ) return 0;
2359 4 : if( pExpr->token.n!=3 ) return 0;
2360 0 : if( sqlite3StrNICmp((char*)pExpr->token.z,"min",3)==0 ){
2361 0 : seekOp = OP_Rewind;
2362 0 : }else if( sqlite3StrNICmp((char*)pExpr->token.z,"max",3)==0 ){
2363 0 : seekOp = OP_Last;
2364 : }else{
2365 0 : return 0;
2366 : }
2367 0 : pExpr = pList->a[0].pExpr;
2368 0 : if( pExpr->op!=TK_COLUMN ) return 0;
2369 0 : iCol = pExpr->iColumn;
2370 0 : pTab = pSrc->a[0].pTab;
2371 :
2372 : /* This optimization cannot be used with virtual tables. */
2373 0 : if( IsVirtual(pTab) ) return 0;
2374 :
2375 : /* If we get to here, it means the query is of the correct form.
2376 : ** Check to make sure we have an index and make pIdx point to the
2377 : ** appropriate index. If the min() or max() is on an INTEGER PRIMARY
2378 : ** key column, no index is necessary so set pIdx to NULL. If no
2379 : ** usable index is found, return 0.
2380 : */
2381 0 : if( iCol<0 ){
2382 0 : pIdx = 0;
2383 : }else{
2384 0 : CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
2385 0 : if( pColl==0 ) return 0;
2386 0 : for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
2387 : assert( pIdx->nColumn>=1 );
2388 0 : if( pIdx->aiColumn[0]==iCol &&
2389 : 0==sqlite3StrICmp(pIdx->azColl[0], pColl->zName) ){
2390 0 : break;
2391 : }
2392 : }
2393 0 : if( pIdx==0 ) return 0;
2394 : }
2395 :
2396 : /* Identify column types if we will be using the callback. This
2397 : ** step is skipped if the output is going to a table or a memory cell.
2398 : ** The column names have already been generated in the calling function.
2399 : */
2400 0 : v = sqlite3GetVdbe(pParse);
2401 0 : if( v==0 ) return 0;
2402 :
2403 : /* If the output is destined for a temporary table, open that table.
2404 : */
2405 0 : if( eDest==SRT_EphemTab ){
2406 0 : sqlite3VdbeAddOp(v, OP_OpenEphemeral, iParm, 1);
2407 : }
2408 :
2409 : /* Generating code to find the min or the max. Basically all we have
2410 : ** to do is find the first or the last entry in the chosen index. If
2411 : ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
2412 : ** or last entry in the main table.
2413 : */
2414 0 : iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
2415 : assert( iDb>=0 || pTab->isEphem );
2416 0 : sqlite3CodeVerifySchema(pParse, iDb);
2417 0 : sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
2418 0 : base = pSrc->a[0].iCursor;
2419 0 : brk = sqlite3VdbeMakeLabel(v);
2420 0 : computeLimitRegisters(pParse, p, brk);
2421 0 : if( pSrc->a[0].pSelect==0 ){
2422 0 : sqlite3OpenTable(pParse, base, iDb, pTab, OP_OpenRead);
2423 : }
2424 0 : if( pIdx==0 ){
2425 0 : sqlite3VdbeAddOp(v, seekOp, base, 0);
2426 : }else{
2427 : /* Even though the cursor used to open the index here is closed
2428 : ** as soon as a single value has been read from it, allocate it
2429 : ** using (pParse->nTab++) to prevent the cursor id from being
2430 : ** reused. This is important for statements of the form
2431 : ** "INSERT INTO x SELECT max() FROM x".
2432 : */
2433 : int iIdx;
2434 0 : KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
2435 0 : iIdx = pParse->nTab++;
2436 : assert( pIdx->pSchema==pTab->pSchema );
2437 0 : sqlite3VdbeAddOp(v, OP_Integer, iDb, 0);
2438 0 : sqlite3VdbeOp3(v, OP_OpenRead, iIdx, pIdx->tnum,
2439 : (char*)pKey, P3_KEYINFO_HANDOFF);
2440 0 : if( seekOp==OP_Rewind ){
2441 0 : sqlite3VdbeAddOp(v, OP_Null, 0, 0);
2442 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, 1, 0);
2443 0 : seekOp = OP_MoveGt;
2444 : }
2445 0 : sqlite3VdbeAddOp(v, seekOp, iIdx, 0);
2446 0 : sqlite3VdbeAddOp(v, OP_IdxRowid, iIdx, 0);
2447 0 : sqlite3VdbeAddOp(v, OP_Close, iIdx, 0);
2448 0 : sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
2449 : }
2450 0 : eList.nExpr = 1;
2451 0 : memset(&eListItem, 0, sizeof(eListItem));
2452 0 : eList.a = &eListItem;
2453 0 : eList.a[0].pExpr = pExpr;
2454 0 : selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, brk, brk, 0);
2455 0 : sqlite3VdbeResolveLabel(v, brk);
2456 0 : sqlite3VdbeAddOp(v, OP_Close, base, 0);
2457 :
2458 0 : return 1;
2459 : }
2460 :
2461 : /*
2462 : ** Analyze and ORDER BY or GROUP BY clause in a SELECT statement. Return
2463 : ** the number of errors seen.
2464 : **
2465 : ** An ORDER BY or GROUP BY is a list of expressions. If any expression
2466 : ** is an integer constant, then that expression is replaced by the
2467 : ** corresponding entry in the result set.
2468 : */
2469 : static int processOrderGroupBy(
2470 : NameContext *pNC, /* Name context of the SELECT statement. */
2471 : ExprList *pOrderBy, /* The ORDER BY or GROUP BY clause to be processed */
2472 : const char *zType /* Either "ORDER" or "GROUP", as appropriate */
2473 306 : ){
2474 : int i;
2475 306 : ExprList *pEList = pNC->pEList; /* The result set of the SELECT */
2476 306 : Parse *pParse = pNC->pParse; /* The result set of the SELECT */
2477 : assert( pEList );
2478 :
2479 306 : if( pOrderBy==0 ) return 0;
2480 8 : for(i=0; i<pOrderBy->nExpr; i++){
2481 : int iCol;
2482 4 : Expr *pE = pOrderBy->a[i].pExpr;
2483 4 : if( sqlite3ExprIsInteger(pE, &iCol) ){
2484 0 : if( iCol>0 && iCol<=pEList->nExpr ){
2485 0 : CollSeq *pColl = pE->pColl;
2486 0 : int flags = pE->flags & EP_ExpCollate;
2487 0 : sqlite3ExprDelete(pE);
2488 0 : pE = pOrderBy->a[i].pExpr = sqlite3ExprDup(pEList->a[iCol-1].pExpr);
2489 0 : if( pColl && flags ){
2490 0 : pE->pColl = pColl;
2491 0 : pE->flags |= flags;
2492 : }
2493 : }else{
2494 0 : sqlite3ErrorMsg(pParse,
2495 : "%s BY column number %d out of range - should be "
2496 : "between 1 and %d", zType, iCol, pEList->nExpr);
2497 0 : return 1;
2498 : }
2499 : }
2500 4 : if( sqlite3ExprResolveNames(pNC, pE) ){
2501 0 : return 1;
2502 : }
2503 : }
2504 4 : return 0;
2505 : }
2506 :
2507 : /*
2508 : ** This routine resolves any names used in the result set of the
2509 : ** supplied SELECT statement. If the SELECT statement being resolved
2510 : ** is a sub-select, then pOuterNC is a pointer to the NameContext
2511 : ** of the parent SELECT.
2512 : */
2513 : int sqlite3SelectResolve(
2514 : Parse *pParse, /* The parser context */
2515 : Select *p, /* The SELECT statement being coded. */
2516 : NameContext *pOuterNC /* The outer name context. May be NULL. */
2517 153 : ){
2518 : ExprList *pEList; /* Result set. */
2519 : int i; /* For-loop variable used in multiple places */
2520 : NameContext sNC; /* Local name-context */
2521 : ExprList *pGroupBy; /* The group by clause */
2522 :
2523 : /* If this routine has run before, return immediately. */
2524 153 : if( p->isResolved ){
2525 : assert( !pOuterNC );
2526 0 : return SQLITE_OK;
2527 : }
2528 153 : p->isResolved = 1;
2529 :
2530 : /* If there have already been errors, do nothing. */
2531 153 : if( pParse->nErr>0 ){
2532 0 : return SQLITE_ERROR;
2533 : }
2534 :
2535 : /* Prepare the select statement. This call will allocate all cursors
2536 : ** required to handle the tables and subqueries in the FROM clause.
2537 : */
2538 153 : if( prepSelectStmt(pParse, p) ){
2539 0 : return SQLITE_ERROR;
2540 : }
2541 :
2542 : /* Resolve the expressions in the LIMIT and OFFSET clauses. These
2543 : ** are not allowed to refer to any names, so pass an empty NameContext.
2544 : */
2545 153 : memset(&sNC, 0, sizeof(sNC));
2546 153 : sNC.pParse = pParse;
2547 153 : if( sqlite3ExprResolveNames(&sNC, p->pLimit) ||
2548 : sqlite3ExprResolveNames(&sNC, p->pOffset) ){
2549 0 : return SQLITE_ERROR;
2550 : }
2551 :
2552 : /* Set up the local name-context to pass to ExprResolveNames() to
2553 : ** resolve the expression-list.
2554 : */
2555 153 : sNC.allowAgg = 1;
2556 153 : sNC.pSrcList = p->pSrc;
2557 153 : sNC.pNext = pOuterNC;
2558 :
2559 : /* Resolve names in the result set. */
2560 153 : pEList = p->pEList;
2561 153 : if( !pEList ) return SQLITE_ERROR;
2562 506 : for(i=0; i<pEList->nExpr; i++){
2563 353 : Expr *pX = pEList->a[i].pExpr;
2564 353 : if( sqlite3ExprResolveNames(&sNC, pX) ){
2565 0 : return SQLITE_ERROR;
2566 : }
2567 : }
2568 :
2569 : /* If there are no aggregate functions in the result-set, and no GROUP BY
2570 : ** expression, do not allow aggregates in any of the other expressions.
2571 : */
2572 : assert( !p->isAgg );
2573 153 : pGroupBy = p->pGroupBy;
2574 167 : if( pGroupBy || sNC.hasAgg ){
2575 14 : p->isAgg = 1;
2576 : }else{
2577 139 : sNC.allowAgg = 0;
2578 : }
2579 :
2580 : /* If a HAVING clause is present, then there must be a GROUP BY clause.
2581 : */
2582 153 : if( p->pHaving && !pGroupBy ){
2583 0 : sqlite3ErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
2584 0 : return SQLITE_ERROR;
2585 : }
2586 :
2587 : /* Add the expression list to the name-context before parsing the
2588 : ** other expressions in the SELECT statement. This is so that
2589 : ** expressions in the WHERE clause (etc.) can refer to expressions by
2590 : ** aliases in the result set.
2591 : **
2592 : ** Minor point: If this is the case, then the expression will be
2593 : ** re-evaluated for each reference to it.
2594 : */
2595 153 : sNC.pEList = p->pEList;
2596 153 : if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
2597 : sqlite3ExprResolveNames(&sNC, p->pHaving) ){
2598 0 : return SQLITE_ERROR;
2599 : }
2600 153 : if( p->pPrior==0 ){
2601 153 : if( processOrderGroupBy(&sNC, p->pOrderBy, "ORDER") ||
2602 : processOrderGroupBy(&sNC, pGroupBy, "GROUP") ){
2603 0 : return SQLITE_ERROR;
2604 : }
2605 : }
2606 :
2607 : /* Make sure the GROUP BY clause does not contain aggregate functions.
2608 : */
2609 153 : if( pGroupBy ){
2610 : struct ExprList_item *pItem;
2611 :
2612 0 : for(i=0, pItem=pGroupBy->a; i<pGroupBy->nExpr; i++, pItem++){
2613 0 : if( ExprHasProperty(pItem->pExpr, EP_Agg) ){
2614 0 : sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
2615 : "the GROUP BY clause");
2616 0 : return SQLITE_ERROR;
2617 : }
2618 : }
2619 : }
2620 :
2621 : /* If this is one SELECT of a compound, be sure to resolve names
2622 : ** in the other SELECTs.
2623 : */
2624 153 : if( p->pPrior ){
2625 0 : return sqlite3SelectResolve(pParse, p->pPrior, pOuterNC);
2626 : }else{
2627 153 : return SQLITE_OK;
2628 : }
2629 : }
2630 :
2631 : /*
2632 : ** Reset the aggregate accumulator.
2633 : **
2634 : ** The aggregate accumulator is a set of memory cells that hold
2635 : ** intermediate results while calculating an aggregate. This
2636 : ** routine simply stores NULLs in all of those memory cells.
2637 : */
2638 14 : static void resetAccumulator(Parse *pParse, AggInfo *pAggInfo){
2639 14 : Vdbe *v = pParse->pVdbe;
2640 : int i;
2641 : struct AggInfo_func *pFunc;
2642 14 : if( pAggInfo->nFunc+pAggInfo->nColumn==0 ){
2643 0 : return;
2644 : }
2645 18 : for(i=0; i<pAggInfo->nColumn; i++){
2646 4 : sqlite3VdbeAddOp(v, OP_MemNull, pAggInfo->aCol[i].iMem, 0);
2647 : }
2648 28 : for(pFunc=pAggInfo->aFunc, i=0; i<pAggInfo->nFunc; i++, pFunc++){
2649 14 : sqlite3VdbeAddOp(v, OP_MemNull, pFunc->iMem, 0);
2650 14 : if( pFunc->iDistinct>=0 ){
2651 0 : Expr *pE = pFunc->pExpr;
2652 0 : if( pE->pList==0 || pE->pList->nExpr!=1 ){
2653 0 : sqlite3ErrorMsg(pParse, "DISTINCT in aggregate must be followed "
2654 : "by an expression");
2655 0 : pFunc->iDistinct = -1;
2656 : }else{
2657 0 : KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->pList);
2658 0 : sqlite3VdbeOp3(v, OP_OpenEphemeral, pFunc->iDistinct, 0,
2659 : (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
2660 : }
2661 : }
2662 : }
2663 : }
2664 :
2665 : /*
2666 : ** Invoke the OP_AggFinalize opcode for every aggregate function
2667 : ** in the AggInfo structure.
2668 : */
2669 14 : static void finalizeAggFunctions(Parse *pParse, AggInfo *pAggInfo){
2670 14 : Vdbe *v = pParse->pVdbe;
2671 : int i;
2672 : struct AggInfo_func *pF;
2673 28 : for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
2674 14 : ExprList *pList = pF->pExpr->pList;
2675 14 : sqlite3VdbeOp3(v, OP_AggFinal, pF->iMem, pList ? pList->nExpr : 0,
2676 : (void*)pF->pFunc, P3_FUNCDEF);
2677 : }
2678 14 : }
2679 :
2680 : /*
2681 : ** Update the accumulator memory cells for an aggregate based on
2682 : ** the current cursor position.
2683 : */
2684 14 : static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){
2685 14 : Vdbe *v = pParse->pVdbe;
2686 : int i;
2687 : struct AggInfo_func *pF;
2688 : struct AggInfo_col *pC;
2689 :
2690 14 : pAggInfo->directMode = 1;
2691 28 : for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
2692 : int nArg;
2693 14 : int addrNext = 0;
2694 14 : ExprList *pList = pF->pExpr->pList;
2695 14 : if( pList ){
2696 4 : nArg = pList->nExpr;
2697 4 : sqlite3ExprCodeExprList(pParse, pList);
2698 : }else{
2699 10 : nArg = 0;
2700 : }
2701 14 : if( pF->iDistinct>=0 ){
2702 0 : addrNext = sqlite3VdbeMakeLabel(v);
2703 : assert( nArg==1 );
2704 0 : codeDistinct(v, pF->iDistinct, addrNext, 1);
2705 : }
2706 14 : if( pF->pFunc->needCollSeq ){
2707 0 : CollSeq *pColl = 0;
2708 : struct ExprList_item *pItem;
2709 : int j;
2710 : assert( pList!=0 ); /* pList!=0 if pF->pFunc->needCollSeq is true */
2711 0 : for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){
2712 0 : pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
2713 : }
2714 0 : if( !pColl ){
2715 0 : pColl = pParse->db->pDfltColl;
2716 : }
2717 0 : sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
2718 : }
2719 14 : sqlite3VdbeOp3(v, OP_AggStep, pF->iMem, nArg, (void*)pF->pFunc, P3_FUNCDEF);
2720 14 : if( addrNext ){
2721 0 : sqlite3VdbeResolveLabel(v, addrNext);
2722 : }
2723 : }
2724 14 : for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
2725 0 : sqlite3ExprCode(pParse, pC->pExpr);
2726 0 : sqlite3VdbeAddOp(v, OP_MemStore, pC->iMem, 1);
2727 : }
2728 14 : pAggInfo->directMode = 0;
2729 14 : }
2730 :
2731 :
2732 : /*
2733 : ** Generate code for the given SELECT statement.
2734 : **
2735 : ** The results are distributed in various ways depending on the
2736 : ** value of eDest and iParm.
2737 : **
2738 : ** eDest Value Result
2739 : ** ------------ -------------------------------------------
2740 : ** SRT_Callback Invoke the callback for each row of the result.
2741 : **
2742 : ** SRT_Mem Store first result in memory cell iParm
2743 : **
2744 : ** SRT_Set Store results as keys of table iParm.
2745 : **
2746 : ** SRT_Union Store results as a key in a temporary table iParm
2747 : **
2748 : ** SRT_Except Remove results from the temporary table iParm.
2749 : **
2750 : ** SRT_Table Store results in temporary table iParm
2751 : **
2752 : ** The table above is incomplete. Additional eDist value have be added
2753 : ** since this comment was written. See the selectInnerLoop() function for
2754 : ** a complete listing of the allowed values of eDest and their meanings.
2755 : **
2756 : ** This routine returns the number of errors. If any errors are
2757 : ** encountered, then an appropriate error message is left in
2758 : ** pParse->zErrMsg.
2759 : **
2760 : ** This routine does NOT free the Select structure passed in. The
2761 : ** calling function needs to do that.
2762 : **
2763 : ** The pParent, parentTab, and *pParentAgg fields are filled in if this
2764 : ** SELECT is a subquery. This routine may try to combine this SELECT
2765 : ** with its parent to form a single flat query. In so doing, it might
2766 : ** change the parent query from a non-aggregate to an aggregate query.
2767 : ** For that reason, the pParentAgg flag is passed as a pointer, so it
2768 : ** can be changed.
2769 : **
2770 : ** Example 1: The meaning of the pParent parameter.
2771 : **
2772 : ** SELECT * FROM t1 JOIN (SELECT x, count(*) FROM t2) JOIN t3;
2773 : ** \ \_______ subquery _______/ /
2774 : ** \ /
2775 : ** \____________________ outer query ___________________/
2776 : **
2777 : ** This routine is called for the outer query first. For that call,
2778 : ** pParent will be NULL. During the processing of the outer query, this
2779 : ** routine is called recursively to handle the subquery. For the recursive
2780 : ** call, pParent will point to the outer query. Because the subquery is
2781 : ** the second element in a three-way join, the parentTab parameter will
2782 : ** be 1 (the 2nd value of a 0-indexed array.)
2783 : */
2784 : int sqlite3Select(
2785 : Parse *pParse, /* The parser context */
2786 : Select *p, /* The SELECT statement being coded. */
2787 : int eDest, /* How to dispose of the results */
2788 : int iParm, /* A parameter used by the eDest disposal method */
2789 : Select *pParent, /* Another SELECT for which this is a sub-query */
2790 : int parentTab, /* Index in pParent->pSrc of this query */
2791 : int *pParentAgg, /* True if pParent uses aggregate functions */
2792 : char *aff /* If eDest is SRT_Union, the affinity string */
2793 153 : ){
2794 : int i, j; /* Loop counters */
2795 : WhereInfo *pWInfo; /* Return from sqlite3WhereBegin() */
2796 : Vdbe *v; /* The virtual machine under construction */
2797 : int isAgg; /* True for select lists like "count(*)" */
2798 : ExprList *pEList; /* List of columns to extract. */
2799 : SrcList *pTabList; /* List of tables to select from */
2800 : Expr *pWhere; /* The WHERE clause. May be NULL */
2801 : ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */
2802 : ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */
2803 : Expr *pHaving; /* The HAVING clause. May be NULL */
2804 : int isDistinct; /* True if the DISTINCT keyword is present */
2805 : int distinct; /* Table to use for the distinct set */
2806 153 : int rc = 1; /* Value to return from this function */
2807 : int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */
2808 : AggInfo sAggInfo; /* Information used by aggregate queries */
2809 : int iEnd; /* Address of the end of the query */
2810 :
2811 153 : if( p==0 || sqlite3MallocFailed() || pParse->nErr ){
2812 0 : return 1;
2813 : }
2814 153 : if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
2815 153 : memset(&sAggInfo, 0, sizeof(sAggInfo));
2816 :
2817 : #ifndef SQLITE_OMIT_COMPOUND_SELECT
2818 : /* If there is are a sequence of queries, do the earlier ones first.
2819 : */
2820 153 : if( p->pPrior ){
2821 0 : if( p->pRightmost==0 ){
2822 : Select *pLoop;
2823 0 : for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
2824 0 : pLoop->pRightmost = p;
2825 : }
2826 : }
2827 0 : return multiSelect(pParse, p, eDest, iParm, aff);
2828 : }
2829 : #endif
2830 :
2831 153 : pOrderBy = p->pOrderBy;
2832 153 : if( IgnorableOrderby(eDest) ){
2833 0 : p->pOrderBy = 0;
2834 : }
2835 153 : if( sqlite3SelectResolve(pParse, p, 0) ){
2836 0 : goto select_end;
2837 : }
2838 153 : p->pOrderBy = pOrderBy;
2839 :
2840 : /* Make local copies of the parameters for this query.
2841 : */
2842 153 : pTabList = p->pSrc;
2843 153 : pWhere = p->pWhere;
2844 153 : pGroupBy = p->pGroupBy;
2845 153 : pHaving = p->pHaving;
2846 153 : isAgg = p->isAgg;
2847 153 : isDistinct = p->isDistinct;
2848 153 : pEList = p->pEList;
2849 153 : if( pEList==0 ) goto select_end;
2850 :
2851 : /*
2852 : ** Do not even attempt to generate any code if we have already seen
2853 : ** errors before this routine starts.
2854 : */
2855 153 : if( pParse->nErr>0 ) goto select_end;
2856 :
2857 : /* If writing to memory or generating a set
2858 : ** only a single column may be output.
2859 : */
2860 : #ifndef SQLITE_OMIT_SUBQUERY
2861 153 : if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
2862 0 : sqlite3ErrorMsg(pParse, "only a single result allowed for "
2863 : "a SELECT that is part of an expression");
2864 0 : goto select_end;
2865 : }
2866 : #endif
2867 :
2868 : /* ORDER BY is ignored for some destinations.
2869 : */
2870 153 : if( IgnorableOrderby(eDest) ){
2871 0 : pOrderBy = 0;
2872 : }
2873 :
2874 : /* Begin generating code.
2875 : */
2876 153 : v = sqlite3GetVdbe(pParse);
2877 153 : if( v==0 ) goto select_end;
2878 :
2879 : /* Generate code for all sub-queries in the FROM clause
2880 : */
2881 : #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
2882 302 : for(i=0; i<pTabList->nSrc; i++){
2883 149 : const char *zSavedAuthContext = 0;
2884 : int needRestoreContext;
2885 149 : struct SrcList_item *pItem = &pTabList->a[i];
2886 :
2887 149 : if( pItem->pSelect==0 || pItem->isPopulated ) continue;
2888 0 : if( pItem->zName!=0 ){
2889 0 : zSavedAuthContext = pParse->zAuthContext;
2890 0 : pParse->zAuthContext = pItem->zName;
2891 0 : needRestoreContext = 1;
2892 : }else{
2893 0 : needRestoreContext = 0;
2894 : }
2895 0 : sqlite3Select(pParse, pItem->pSelect, SRT_EphemTab,
2896 : pItem->iCursor, p, i, &isAgg, 0);
2897 0 : if( needRestoreContext ){
2898 0 : pParse->zAuthContext = zSavedAuthContext;
2899 : }
2900 0 : pTabList = p->pSrc;
2901 0 : pWhere = p->pWhere;
2902 0 : if( !IgnorableOrderby(eDest) ){
2903 0 : pOrderBy = p->pOrderBy;
2904 : }
2905 0 : pGroupBy = p->pGroupBy;
2906 0 : pHaving = p->pHaving;
2907 0 : isDistinct = p->isDistinct;
2908 : }
2909 : #endif
2910 :
2911 : /* Check for the special case of a min() or max() function by itself
2912 : ** in the result set.
2913 : */
2914 153 : if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){
2915 0 : rc = 0;
2916 0 : goto select_end;
2917 : }
2918 :
2919 : /* Check to see if this is a subquery that can be "flattened" into its parent.
2920 : ** If flattening is a possiblity, do so and return immediately.
2921 : */
2922 : #ifndef SQLITE_OMIT_VIEW
2923 153 : if( pParent && pParentAgg &&
2924 : flattenSubquery(pParent, parentTab, *pParentAgg, isAgg) ){
2925 0 : if( isAgg ) *pParentAgg = 1;
2926 0 : goto select_end;
2927 : }
2928 : #endif
2929 :
2930 : /* If there is an ORDER BY clause, then this sorting
2931 : ** index might end up being unused if the data can be
2932 : ** extracted in pre-sorted order. If that is the case, then the
2933 : ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
2934 : ** we figure out that the sorting index is not needed. The addrSortIndex
2935 : ** variable is used to facilitate that change.
2936 : */
2937 153 : if( pOrderBy ){
2938 : KeyInfo *pKeyInfo;
2939 4 : if( pParse->nErr ){
2940 0 : goto select_end;
2941 : }
2942 4 : pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);
2943 4 : pOrderBy->iECursor = pParse->nTab++;
2944 4 : p->addrOpenEphm[2] = addrSortIndex =
2945 : sqlite3VdbeOp3(v, OP_OpenEphemeral, pOrderBy->iECursor, pOrderBy->nExpr+2, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
2946 : }else{
2947 149 : addrSortIndex = -1;
2948 : }
2949 :
2950 : /* If the output is destined for a temporary table, open that table.
2951 : */
2952 153 : if( eDest==SRT_EphemTab ){
2953 0 : sqlite3VdbeAddOp(v, OP_OpenEphemeral, iParm, pEList->nExpr);
2954 : }
2955 :
2956 : /* Set the limiter.
2957 : */
2958 153 : iEnd = sqlite3VdbeMakeLabel(v);
2959 153 : computeLimitRegisters(pParse, p, iEnd);
2960 :
2961 : /* Open a virtual index to use for the distinct set.
2962 : */
2963 153 : if( isDistinct ){
2964 : KeyInfo *pKeyInfo;
2965 0 : distinct = pParse->nTab++;
2966 0 : pKeyInfo = keyInfoFromExprList(pParse, p->pEList);
2967 0 : sqlite3VdbeOp3(v, OP_OpenEphemeral, distinct, 0,
2968 : (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
2969 : }else{
2970 153 : distinct = -1;
2971 : }
2972 :
2973 : /* Aggregate and non-aggregate queries are handled differently */
2974 292 : if( !isAgg && pGroupBy==0 ){
2975 : /* This case is for non-aggregate queries
2976 : ** Begin the database scan
2977 : */
2978 139 : pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy);
2979 139 : if( pWInfo==0 ) goto select_end;
2980 :
2981 : /* If sorting index that was created by a prior OP_OpenEphemeral
2982 : ** instruction ended up not being needed, then change the OP_OpenEphemeral
2983 : ** into an OP_Noop.
2984 : */
2985 139 : if( addrSortIndex>=0 && pOrderBy==0 ){
2986 4 : sqlite3VdbeChangeToNoop(v, addrSortIndex, 1);
2987 4 : p->addrOpenEphm[2] = -1;
2988 : }
2989 :
2990 : /* Use the standard inner loop
2991 : */
2992 139 : if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2993 : iParm, pWInfo->iContinue, pWInfo->iBreak, aff) ){
2994 0 : goto select_end;
2995 : }
2996 :
2997 : /* End the database scan loop.
2998 : */
2999 139 : sqlite3WhereEnd(pWInfo);
3000 : }else{
3001 : /* This is the processing for aggregate queries */
3002 : NameContext sNC; /* Name context for processing aggregate information */
3003 : int iAMem; /* First Mem address for storing current GROUP BY */
3004 : int iBMem; /* First Mem address for previous GROUP BY */
3005 : int iUseFlag; /* Mem address holding flag indicating that at least
3006 : ** one row of the input to the aggregator has been
3007 : ** processed */
3008 : int iAbortFlag; /* Mem address which causes query abort if positive */
3009 : int groupBySort; /* Rows come from source in GROUP BY order */
3010 :
3011 :
3012 : /* The following variables hold addresses or labels for parts of the
3013 : ** virtual machine program we are putting together */
3014 : int addrOutputRow; /* Start of subroutine that outputs a result row */
3015 : int addrSetAbort; /* Set the abort flag and return */
3016 : int addrInitializeLoop; /* Start of code that initializes the input loop */
3017 : int addrTopOfLoop; /* Top of the input loop */
3018 : int addrGroupByChange; /* Code that runs when any GROUP BY term changes */
3019 : int addrProcessRow; /* Code to process a single input row */
3020 : int addrEnd; /* End of all processing */
3021 : int addrSortingIdx; /* The OP_OpenEphemeral for the sorting index */
3022 : int addrReset; /* Subroutine for resetting the accumulator */
3023 :
3024 14 : addrEnd = sqlite3VdbeMakeLabel(v);
3025 :
3026 : /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
3027 : ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
3028 : ** SELECT statement.
3029 : */
3030 14 : memset(&sNC, 0, sizeof(sNC));
3031 14 : sNC.pParse = pParse;
3032 14 : sNC.pSrcList = pTabList;
3033 14 : sNC.pAggInfo = &sAggInfo;
3034 14 : sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0;
3035 14 : sAggInfo.pGroupBy = pGroupBy;
3036 14 : if( sqlite3ExprAnalyzeAggList(&sNC, pEList) ){
3037 0 : goto select_end;
3038 : }
3039 14 : if( sqlite3ExprAnalyzeAggList(&sNC, pOrderBy) ){
3040 0 : goto select_end;
3041 : }
3042 14 : if( pHaving && sqlite3ExprAnalyzeAggregates(&sNC, pHaving) ){
3043 0 : goto select_end;
3044 : }
3045 14 : sAggInfo.nAccumulator = sAggInfo.nColumn;
3046 28 : for(i=0; i<sAggInfo.nFunc; i++){
3047 14 : if( sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->pList) ){
3048 0 : goto select_end;
3049 : }
3050 : }
3051 14 : if( sqlite3MallocFailed() ) goto select_end;
3052 :
3053 : /* Processing for aggregates with GROUP BY is very different and
3054 : ** much more complex tha aggregates without a GROUP BY.
3055 : */
3056 14 : if( pGroupBy ){
3057 : KeyInfo *pKeyInfo; /* Keying information for the group by clause */
3058 :
3059 : /* Create labels that we will be needing
3060 : */
3061 :
3062 0 : addrInitializeLoop = sqlite3VdbeMakeLabel(v);
3063 0 : addrGroupByChange = sqlite3VdbeMakeLabel(v);
3064 0 : addrProcessRow = sqlite3VdbeMakeLabel(v);
3065 :
3066 : /* If there is a GROUP BY clause we might need a sorting index to
3067 : ** implement it. Allocate that sorting index now. If it turns out
3068 : ** that we do not need it after all, the OpenEphemeral instruction
3069 : ** will be converted into a Noop.
3070 : */
3071 0 : sAggInfo.sortingIdx = pParse->nTab++;
3072 0 : pKeyInfo = keyInfoFromExprList(pParse, pGroupBy);
3073 0 : addrSortingIdx =
3074 : sqlite3VdbeOp3(v, OP_OpenEphemeral, sAggInfo.sortingIdx,
3075 : sAggInfo.nSortingColumn,
3076 : (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
3077 :
3078 : /* Initialize memory locations used by GROUP BY aggregate processing
3079 : */
3080 0 : iUseFlag = pParse->nMem++;
3081 0 : iAbortFlag = pParse->nMem++;
3082 0 : iAMem = pParse->nMem;
3083 0 : pParse->nMem += pGroupBy->nExpr;
3084 0 : iBMem = pParse->nMem;
3085 0 : pParse->nMem += pGroupBy->nExpr;
3086 0 : sqlite3VdbeAddOp(v, OP_MemInt, 0, iAbortFlag);
3087 : VdbeComment((v, "# clear abort flag"));
3088 0 : sqlite3VdbeAddOp(v, OP_MemInt, 0, iUseFlag);
3089 : VdbeComment((v, "# indicate accumulator empty"));
3090 0 : sqlite3VdbeAddOp(v, OP_Goto, 0, addrInitializeLoop);
3091 :
3092 : /* Generate a subroutine that outputs a single row of the result
3093 : ** set. This subroutine first looks at the iUseFlag. If iUseFlag
3094 : ** is less than or equal to zero, the subroutine is a no-op. If
3095 : ** the processing calls for the query to abort, this subroutine
3096 : ** increments the iAbortFlag memory location before returning in
3097 : ** order to signal the caller to abort.
3098 : */
3099 0 : addrSetAbort = sqlite3VdbeCurrentAddr(v);
3100 0 : sqlite3VdbeAddOp(v, OP_MemInt, 1, iAbortFlag);
3101 : VdbeComment((v, "# set abort flag"));
3102 0 : sqlite3VdbeAddOp(v, OP_Return, 0, 0);
3103 0 : addrOutputRow = sqlite3VdbeCurrentAddr(v);
3104 0 : sqlite3VdbeAddOp(v, OP_IfMemPos, iUseFlag, addrOutputRow+2);
3105 : VdbeComment((v, "# Groupby result generator entry point"));
3106 0 : sqlite3VdbeAddOp(v, OP_Return, 0, 0);
3107 0 : finalizeAggFunctions(pParse, &sAggInfo);
3108 0 : if( pHaving ){
3109 0 : sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, 1);
3110 : }
3111 0 : rc = selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy,
3112 : distinct, eDest, iParm,
3113 : addrOutputRow+1, addrSetAbort, aff);
3114 0 : if( rc ){
3115 0 : goto select_end;
3116 : }
3117 0 : sqlite3VdbeAddOp(v, OP_Return, 0, 0);
3118 : VdbeComment((v, "# end groupby result generator"));
3119 :
3120 : /* Generate a subroutine that will reset the group-by accumulator
3121 : */
3122 0 : addrReset = sqlite3VdbeCurrentAddr(v);
3123 0 : resetAccumulator(pParse, &sAggInfo);
3124 0 : sqlite3VdbeAddOp(v, OP_Return, 0, 0);
3125 :
3126 : /* Begin a loop that will extract all source rows in GROUP BY order.
3127 : ** This might involve two separate loops with an OP_Sort in between, or
3128 : ** it might be a single loop that uses an index to extract information
3129 : ** in the right order to begin with.
3130 : */
3131 0 : sqlite3VdbeResolveLabel(v, addrInitializeLoop);
3132 0 : sqlite3VdbeAddOp(v, OP_Gosub, 0, addrReset);
3133 0 : pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy);
3134 0 : if( pWInfo==0 ) goto select_end;
3135 0 : if( pGroupBy==0 ){
3136 : /* The optimizer is able to deliver rows in group by order so
3137 : ** we do not have to sort. The OP_OpenEphemeral table will be
3138 : ** cancelled later because we still need to use the pKeyInfo
3139 : */
3140 0 : pGroupBy = p->pGroupBy;
3141 0 : groupBySort = 0;
3142 : }else{
3143 : /* Rows are coming out in undetermined order. We have to push
3144 : ** each row into a sorting index, terminate the first loop,
3145 : ** then loop over the sorting index in order to get the output
3146 : ** in sorted order
3147 : */
3148 0 : groupBySort = 1;
3149 0 : sqlite3ExprCodeExprList(pParse, pGroupBy);
3150 0 : sqlite3VdbeAddOp(v, OP_Sequence, sAggInfo.sortingIdx, 0);
3151 0 : j = pGroupBy->nExpr+1;
3152 0 : for(i=0; i<sAggInfo.nColumn; i++){
3153 0 : struct AggInfo_col *pCol = &sAggInfo.aCol[i];
3154 0 : if( pCol->iSorterColumn<j ) continue;
3155 0 : sqlite3ExprCodeGetColumn(v, pCol->pTab, pCol->iColumn, pCol->iTable);
3156 0 : j++;
3157 : }
3158 0 : sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
3159 0 : sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
3160 0 : sqlite3WhereEnd(pWInfo);
3161 0 : sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
3162 : VdbeComment((v, "# GROUP BY sort"));
3163 0 : sAggInfo.useSortingIdx = 1;
3164 : }
3165 :
3166 : /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
3167 : ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
3168 : ** Then compare the current GROUP BY terms against the GROUP BY terms
3169 : ** from the previous row currently stored in a0, a1, a2...
3170 : */
3171 0 : addrTopOfLoop = sqlite3VdbeCurrentAddr(v);
3172 0 : for(j=0; j<pGroupBy->nExpr; j++){
3173 0 : if( groupBySort ){
3174 0 : sqlite3VdbeAddOp(v, OP_Column, sAggInfo.sortingIdx, j);
3175 : }else{
3176 0 : sAggInfo.directMode = 1;
3177 0 : sqlite3ExprCode(pParse, pGroupBy->a[j].pExpr);
3178 : }
3179 0 : sqlite3VdbeAddOp(v, OP_MemStore, iBMem+j, j<pGroupBy->nExpr-1);
3180 : }
3181 0 : for(j=pGroupBy->nExpr-1; j>=0; j--){
3182 0 : if( j<pGroupBy->nExpr-1 ){
3183 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iBMem+j, 0);
3184 : }
3185 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iAMem+j, 0);
3186 0 : if( j==0 ){
3187 0 : sqlite3VdbeAddOp(v, OP_Eq, 0x200, addrProcessRow);
3188 : }else{
3189 0 : sqlite3VdbeAddOp(v, OP_Ne, 0x200, addrGroupByChange);
3190 : }
3191 0 : sqlite3VdbeChangeP3(v, -1, (void*)pKeyInfo->aColl[j], P3_COLLSEQ);
3192 : }
3193 :
3194 : /* Generate code that runs whenever the GROUP BY changes.
3195 : ** Change in the GROUP BY are detected by the previous code
3196 : ** block. If there were no changes, this block is skipped.
3197 : **
3198 : ** This code copies current group by terms in b0,b1,b2,...
3199 : ** over to a0,a1,a2. It then calls the output subroutine
3200 : ** and resets the aggregate accumulator registers in preparation
3201 : ** for the next GROUP BY batch.
3202 : */
3203 0 : sqlite3VdbeResolveLabel(v, addrGroupByChange);
3204 0 : for(j=0; j<pGroupBy->nExpr; j++){
3205 0 : sqlite3VdbeAddOp(v, OP_MemMove, iAMem+j, iBMem+j);
3206 : }
3207 0 : sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow);
3208 : VdbeComment((v, "# output one row"));
3209 0 : sqlite3VdbeAddOp(v, OP_IfMemPos, iAbortFlag, addrEnd);
3210 : VdbeComment((v, "# check abort flag"));
3211 0 : sqlite3VdbeAddOp(v, OP_Gosub, 0, addrReset);
3212 : VdbeComment((v, "# reset accumulator"));
3213 :
3214 : /* Update the aggregate accumulators based on the content of
3215 : ** the current row
3216 : */
3217 0 : sqlite3VdbeResolveLabel(v, addrProcessRow);
3218 0 : updateAccumulator(pParse, &sAggInfo);
3219 0 : sqlite3VdbeAddOp(v, OP_MemInt, 1, iUseFlag);
3220 : VdbeComment((v, "# indicate data in accumulator"));
3221 :
3222 : /* End of the loop
3223 : */
3224 0 : if( groupBySort ){
3225 0 : sqlite3VdbeAddOp(v, OP_Next, sAggInfo.sortingIdx, addrTopOfLoop);
3226 : }else{
3227 0 : sqlite3WhereEnd(pWInfo);
3228 0 : sqlite3VdbeChangeToNoop(v, addrSortingIdx, 1);
3229 : }
3230 :
3231 : /* Output the final row of result
3232 : */
3233 0 : sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow);
3234 : VdbeComment((v, "# output final row"));
3235 :
3236 : } /* endif pGroupBy */
3237 : else {
3238 : /* This case runs if the aggregate has no GROUP BY clause. The
3239 : ** processing is much simpler since there is only a single row
3240 : ** of output.
3241 : */
3242 14 : resetAccumulator(pParse, &sAggInfo);
3243 14 : pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
3244 14 : if( pWInfo==0 ) goto select_end;
3245 14 : updateAccumulator(pParse, &sAggInfo);
3246 14 : sqlite3WhereEnd(pWInfo);
3247 14 : finalizeAggFunctions(pParse, &sAggInfo);
3248 14 : pOrderBy = 0;
3249 14 : if( pHaving ){
3250 0 : sqlite3ExprIfFalse(pParse, pHaving, addrEnd, 1);
3251 : }
3252 14 : selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1,
3253 : eDest, iParm, addrEnd, addrEnd, aff);
3254 : }
3255 14 : sqlite3VdbeResolveLabel(v, addrEnd);
3256 :
3257 : } /* endif aggregate query */
3258 :
3259 : /* If there is an ORDER BY clause, then we need to sort the results
3260 : ** and send them to the callback one by one.
3261 : */
3262 153 : if( pOrderBy ){
3263 0 : generateSortTail(pParse, p, v, pEList->nExpr, eDest, iParm);
3264 : }
3265 :
3266 : #ifndef SQLITE_OMIT_SUBQUERY
3267 : /* If this was a subquery, we have now converted the subquery into a
3268 : ** temporary table. So set the SrcList_item.isPopulated flag to prevent
3269 : ** this subquery from being evaluated again and to force the use of
3270 : ** the temporary table.
3271 : */
3272 153 : if( pParent ){
3273 : assert( pParent->pSrc->nSrc>parentTab );
3274 : assert( pParent->pSrc->a[parentTab].pSelect==p );
3275 0 : pParent->pSrc->a[parentTab].isPopulated = 1;
3276 : }
3277 : #endif
3278 :
3279 : /* Jump here to skip this query
3280 : */
3281 153 : sqlite3VdbeResolveLabel(v, iEnd);
3282 :
3283 : /* The SELECT was successfully coded. Set the return code to 0
3284 : ** to indicate no errors.
3285 : */
3286 153 : rc = 0;
3287 :
3288 : /* Control jumps to here if an error is encountered above, or upon
3289 : ** successful coding of the SELECT.
3290 : */
3291 153 : select_end:
3292 :
3293 : /* Identify column names if we will be using them in a callback. This
3294 : ** step is skipped if the output is going to some other destination.
3295 : */
3296 153 : if( rc==SQLITE_OK && eDest==SRT_Callback ){
3297 153 : generateColumnNames(pParse, pTabList, pEList);
3298 : }
3299 :
3300 153 : sqliteFree(sAggInfo.aCol);
3301 153 : sqliteFree(sAggInfo.aFunc);
3302 153 : return rc;
3303 : }
3304 :
3305 : #if defined(SQLITE_DEBUG)
3306 : /*
3307 : *******************************************************************************
3308 : ** The following code is used for testing and debugging only. The code
3309 : ** that follows does not appear in normal builds.
3310 : **
3311 : ** These routines are used to print out the content of all or part of a
3312 : ** parse structures such as Select or Expr. Such printouts are useful
3313 : ** for helping to understand what is happening inside the code generator
3314 : ** during the execution of complex SELECT statements.
3315 : **
3316 : ** These routine are not called anywhere from within the normal
3317 : ** code base. Then are intended to be called from within the debugger
3318 : ** or from temporary "printf" statements inserted for debugging.
3319 : */
3320 : void sqlite3PrintExpr(Expr *p){
3321 : if( p->token.z && p->token.n>0 ){
3322 : sqlite3DebugPrintf("(%.*s", p->token.n, p->token.z);
3323 : }else{
3324 : sqlite3DebugPrintf("(%d", p->op);
3325 : }
3326 : if( p->pLeft ){
3327 : sqlite3DebugPrintf(" ");
3328 : sqlite3PrintExpr(p->pLeft);
3329 : }
3330 : if( p->pRight ){
3331 : sqlite3DebugPrintf(" ");
3332 : sqlite3PrintExpr(p->pRight);
3333 : }
3334 : sqlite3DebugPrintf(")");
3335 : }
3336 : void sqlite3PrintExprList(ExprList *pList){
3337 : int i;
3338 : for(i=0; i<pList->nExpr; i++){
3339 : sqlite3PrintExpr(pList->a[i].pExpr);
3340 : if( i<pList->nExpr-1 ){
3341 : sqlite3DebugPrintf(", ");
3342 : }
3343 : }
3344 : }
3345 : void sqlite3PrintSelect(Select *p, int indent){
3346 : sqlite3DebugPrintf("%*sSELECT(%p) ", indent, "", p);
3347 : sqlite3PrintExprList(p->pEList);
3348 : sqlite3DebugPrintf("\n");
3349 : if( p->pSrc ){
3350 : char *zPrefix;
3351 : int i;
3352 : zPrefix = "FROM";
3353 : for(i=0; i<p->pSrc->nSrc; i++){
3354 : struct SrcList_item *pItem = &p->pSrc->a[i];
3355 : sqlite3DebugPrintf("%*s ", indent+6, zPrefix);
3356 : zPrefix = "";
3357 : if( pItem->pSelect ){
3358 : sqlite3DebugPrintf("(\n");
3359 : sqlite3PrintSelect(pItem->pSelect, indent+10);
3360 : sqlite3DebugPrintf("%*s)", indent+8, "");
3361 : }else if( pItem->zName ){
3362 : sqlite3DebugPrintf("%s", pItem->zName);
3363 : }
3364 : if( pItem->pTab ){
3365 : sqlite3DebugPrintf("(table: %s)", pItem->pTab->zName);
3366 : }
3367 : if( pItem->zAlias ){
3368 : sqlite3DebugPrintf(" AS %s", pItem->zAlias);
3369 : }
3370 : if( i<p->pSrc->nSrc-1 ){
3371 : sqlite3DebugPrintf(",");
3372 : }
3373 : sqlite3DebugPrintf("\n");
3374 : }
3375 : }
3376 : if( p->pWhere ){
3377 : sqlite3DebugPrintf("%*s WHERE ", indent, "");
3378 : sqlite3PrintExpr(p->pWhere);
3379 : sqlite3DebugPrintf("\n");
3380 : }
3381 : if( p->pGroupBy ){
3382 : sqlite3DebugPrintf("%*s GROUP BY ", indent, "");
3383 : sqlite3PrintExprList(p->pGroupBy);
3384 : sqlite3DebugPrintf("\n");
3385 : }
3386 : if( p->pHaving ){
3387 : sqlite3DebugPrintf("%*s HAVING ", indent, "");
3388 : sqlite3PrintExpr(p->pHaving);
3389 : sqlite3DebugPrintf("\n");
3390 : }
3391 : if( p->pOrderBy ){
3392 : sqlite3DebugPrintf("%*s ORDER BY ", indent, "");
3393 : sqlite3PrintExprList(p->pOrderBy);
3394 : sqlite3DebugPrintf("\n");
3395 : }
3396 : }
3397 : /* End of the structure debug printing code
3398 : *****************************************************************************/
3399 : #endif /* defined(SQLITE_TEST) || defined(SQLITE_DEBUG) */
|
