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