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