blob: 3aef002cce85e51eba0c1e25dfc933b8f5e95617 [file] [log] [blame]
drhcce7d172000-05-31 15:34:51 +00001/*
drhb19a2bc2001-09-16 00:13:26 +00002** 2001 September 15
drhcce7d172000-05-31 15:34:51 +00003**
drhb19a2bc2001-09-16 00:13:26 +00004** The author disclaims copyright to this source code. In place of
5** a legal notice, here is a blessing:
drhcce7d172000-05-31 15:34:51 +00006**
drhb19a2bc2001-09-16 00:13:26 +00007** 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.
drhcce7d172000-05-31 15:34:51 +000010**
11*************************************************************************
12** This file contains C code routines that are called by the parser
drhb19a2bc2001-09-16 00:13:26 +000013** to handle SELECT statements in SQLite.
drhcce7d172000-05-31 15:34:51 +000014**
drha69d9162003-04-17 22:57:53 +000015** $Id: select.c,v 1.132 2003/04/17 22:57:54 drh Exp $
drhcce7d172000-05-31 15:34:51 +000016*/
17#include "sqliteInt.h"
18
drh315555c2002-10-20 15:53:03 +000019
drhcce7d172000-05-31 15:34:51 +000020/*
drh9bb61fe2000-06-05 16:01:39 +000021** Allocate a new Select structure and return a pointer to that
22** structure.
drhcce7d172000-05-31 15:34:51 +000023*/
drh9bb61fe2000-06-05 16:01:39 +000024Select *sqliteSelectNew(
drhdaffd0e2001-04-11 14:28:42 +000025 ExprList *pEList, /* which columns to include in the result */
drhad3cab52002-05-24 02:04:32 +000026 SrcList *pSrc, /* the FROM clause -- which tables to scan */
drhdaffd0e2001-04-11 14:28:42 +000027 Expr *pWhere, /* the WHERE clause */
28 ExprList *pGroupBy, /* the GROUP BY clause */
29 Expr *pHaving, /* the HAVING clause */
30 ExprList *pOrderBy, /* the ORDER BY clause */
drh9bbca4c2001-11-06 04:00:18 +000031 int isDistinct, /* true if the DISTINCT keyword is present */
32 int nLimit, /* LIMIT value. -1 means not used */
33 int nOffset /* OFFSET value. -1 means not used */
drh9bb61fe2000-06-05 16:01:39 +000034){
35 Select *pNew;
36 pNew = sqliteMalloc( sizeof(*pNew) );
drhdaffd0e2001-04-11 14:28:42 +000037 if( pNew==0 ){
38 sqliteExprListDelete(pEList);
drhad3cab52002-05-24 02:04:32 +000039 sqliteSrcListDelete(pSrc);
drhdaffd0e2001-04-11 14:28:42 +000040 sqliteExprDelete(pWhere);
41 sqliteExprListDelete(pGroupBy);
42 sqliteExprDelete(pHaving);
43 sqliteExprListDelete(pOrderBy);
44 }else{
45 pNew->pEList = pEList;
46 pNew->pSrc = pSrc;
47 pNew->pWhere = pWhere;
48 pNew->pGroupBy = pGroupBy;
49 pNew->pHaving = pHaving;
50 pNew->pOrderBy = pOrderBy;
51 pNew->isDistinct = isDistinct;
52 pNew->op = TK_SELECT;
drh9bbca4c2001-11-06 04:00:18 +000053 pNew->nLimit = nLimit;
54 pNew->nOffset = nOffset;
drhdaffd0e2001-04-11 14:28:42 +000055 }
drh9bb61fe2000-06-05 16:01:39 +000056 return pNew;
57}
58
59/*
drh01f3f252002-05-24 16:14:15 +000060** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
61** type of join. Return an integer constant that expresses that type
62** in terms of the following bit values:
63**
64** JT_INNER
65** JT_OUTER
66** JT_NATURAL
67** JT_LEFT
68** JT_RIGHT
69**
70** A full outer join is the combination of JT_LEFT and JT_RIGHT.
71**
72** If an illegal or unsupported join type is seen, then still return
73** a join type, but put an error in the pParse structure.
74*/
75int sqliteJoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
76 int jointype = 0;
77 Token *apAll[3];
78 Token *p;
79 static struct {
80 const char *zKeyword;
81 int nChar;
82 int code;
83 } keywords[] = {
84 { "natural", 7, JT_NATURAL },
drh195e6962002-05-25 00:18:20 +000085 { "left", 4, JT_LEFT|JT_OUTER },
86 { "right", 5, JT_RIGHT|JT_OUTER },
87 { "full", 4, JT_LEFT|JT_RIGHT|JT_OUTER },
drh01f3f252002-05-24 16:14:15 +000088 { "outer", 5, JT_OUTER },
89 { "inner", 5, JT_INNER },
90 { "cross", 5, JT_INNER },
91 };
92 int i, j;
93 apAll[0] = pA;
94 apAll[1] = pB;
95 apAll[2] = pC;
drh195e6962002-05-25 00:18:20 +000096 for(i=0; i<3 && apAll[i]; i++){
drh01f3f252002-05-24 16:14:15 +000097 p = apAll[i];
98 for(j=0; j<sizeof(keywords)/sizeof(keywords[0]); j++){
99 if( p->n==keywords[j].nChar
100 && sqliteStrNICmp(p->z, keywords[j].zKeyword, p->n)==0 ){
101 jointype |= keywords[j].code;
102 break;
103 }
104 }
105 if( j>=sizeof(keywords)/sizeof(keywords[0]) ){
106 jointype |= JT_ERROR;
107 break;
108 }
109 }
drhad2d8302002-05-24 20:31:36 +0000110 if(
111 (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||
drh195e6962002-05-25 00:18:20 +0000112 (jointype & JT_ERROR)!=0
drhad2d8302002-05-24 20:31:36 +0000113 ){
drh01f3f252002-05-24 16:14:15 +0000114 static Token dummy = { 0, 0 };
115 char *zSp1 = " ", *zSp2 = " ";
116 if( pB==0 ){ pB = &dummy; zSp1 = 0; }
117 if( pC==0 ){ pC = &dummy; zSp2 = 0; }
118 sqliteSetNString(&pParse->zErrMsg, "unknown or unsupported join type: ", 0,
119 pA->z, pA->n, zSp1, 1, pB->z, pB->n, zSp2, 1, pC->z, pC->n, 0);
120 pParse->nErr++;
121 jointype = JT_INNER;
drh195e6962002-05-25 00:18:20 +0000122 }else if( jointype & JT_RIGHT ){
drhda93d232003-03-31 02:12:46 +0000123 sqliteErrorMsg(pParse,
124 "RIGHT and FULL OUTER JOINs are not currently supported");
drh195e6962002-05-25 00:18:20 +0000125 jointype = JT_INNER;
drh01f3f252002-05-24 16:14:15 +0000126 }
127 return jointype;
128}
129
130/*
drhad2d8302002-05-24 20:31:36 +0000131** Return the index of a column in a table. Return -1 if the column
132** is not contained in the table.
133*/
134static int columnIndex(Table *pTab, const char *zCol){
135 int i;
136 for(i=0; i<pTab->nCol; i++){
137 if( sqliteStrICmp(pTab->aCol[i].zName, zCol)==0 ) return i;
138 }
139 return -1;
140}
141
142/*
143** Add a term to the WHERE expression in *ppExpr that requires the
144** zCol column to be equal in the two tables pTab1 and pTab2.
145*/
146static void addWhereTerm(
147 const char *zCol, /* Name of the column */
148 const Table *pTab1, /* First table */
149 const Table *pTab2, /* Second table */
150 Expr **ppExpr /* Add the equality term to this expression */
151){
152 Token dummy;
153 Expr *pE1a, *pE1b, *pE1c;
154 Expr *pE2a, *pE2b, *pE2c;
155 Expr *pE;
156
157 dummy.z = zCol;
158 dummy.n = strlen(zCol);
drh4b59ab52002-08-24 18:24:51 +0000159 dummy.dyn = 0;
drhad2d8302002-05-24 20:31:36 +0000160 pE1a = sqliteExpr(TK_ID, 0, 0, &dummy);
161 pE2a = sqliteExpr(TK_ID, 0, 0, &dummy);
162 dummy.z = pTab1->zName;
163 dummy.n = strlen(dummy.z);
164 pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);
165 dummy.z = pTab2->zName;
166 dummy.n = strlen(dummy.z);
167 pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);
168 pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
169 pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
170 pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
drh1f162302002-10-27 19:35:33 +0000171 ExprSetProperty(pE, EP_FromJoin);
drhad2d8302002-05-24 20:31:36 +0000172 if( *ppExpr ){
173 *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
174 }else{
175 *ppExpr = pE;
176 }
177}
178
179/*
drh1f162302002-10-27 19:35:33 +0000180** Set the EP_FromJoin property on all terms of the given expression.
drh1cc093c2002-06-24 22:01:57 +0000181**
drhe78e8282003-01-19 03:59:45 +0000182** The EP_FromJoin property is used on terms of an expression to tell
drh1cc093c2002-06-24 22:01:57 +0000183** the LEFT OUTER JOIN processing logic that this term is part of the
drh1f162302002-10-27 19:35:33 +0000184** join restriction specified in the ON or USING clause and not a part
185** of the more general WHERE clause. These terms are moved over to the
186** WHERE clause during join processing but we need to remember that they
187** originated in the ON or USING clause.
drh1cc093c2002-06-24 22:01:57 +0000188*/
189static void setJoinExpr(Expr *p){
190 while( p ){
drh1f162302002-10-27 19:35:33 +0000191 ExprSetProperty(p, EP_FromJoin);
drh1cc093c2002-06-24 22:01:57 +0000192 setJoinExpr(p->pLeft);
193 p = p->pRight;
194 }
195}
196
197/*
drhad2d8302002-05-24 20:31:36 +0000198** This routine processes the join information for a SELECT statement.
199** ON and USING clauses are converted into extra terms of the WHERE clause.
200** NATURAL joins also create extra WHERE clause terms.
201**
202** This routine returns the number of errors encountered.
203*/
204static int sqliteProcessJoin(Parse *pParse, Select *p){
205 SrcList *pSrc;
206 int i, j;
207 pSrc = p->pSrc;
208 for(i=0; i<pSrc->nSrc-1; i++){
209 struct SrcList_item *pTerm = &pSrc->a[i];
210 struct SrcList_item *pOther = &pSrc->a[i+1];
211
212 if( pTerm->pTab==0 || pOther->pTab==0 ) continue;
213
214 /* When the NATURAL keyword is present, add WHERE clause terms for
215 ** every column that the two tables have in common.
216 */
217 if( pTerm->jointype & JT_NATURAL ){
218 Table *pTab;
219 if( pTerm->pOn || pTerm->pUsing ){
drhda93d232003-03-31 02:12:46 +0000220 sqliteErrorMsg(pParse, "a NATURAL join may not have "
drhad2d8302002-05-24 20:31:36 +0000221 "an ON or USING clause", 0);
drhad2d8302002-05-24 20:31:36 +0000222 return 1;
223 }
224 pTab = pTerm->pTab;
225 for(j=0; j<pTab->nCol; j++){
226 if( columnIndex(pOther->pTab, pTab->aCol[j].zName)>=0 ){
227 addWhereTerm(pTab->aCol[j].zName, pTab, pOther->pTab, &p->pWhere);
228 }
229 }
230 }
231
232 /* Disallow both ON and USING clauses in the same join
233 */
234 if( pTerm->pOn && pTerm->pUsing ){
drhda93d232003-03-31 02:12:46 +0000235 sqliteErrorMsg(pParse, "cannot have both ON and USING "
236 "clauses in the same join");
drhad2d8302002-05-24 20:31:36 +0000237 return 1;
238 }
239
240 /* Add the ON clause to the end of the WHERE clause, connected by
241 ** and AND operator.
242 */
243 if( pTerm->pOn ){
drh1cc093c2002-06-24 22:01:57 +0000244 setJoinExpr(pTerm->pOn);
drhad2d8302002-05-24 20:31:36 +0000245 if( p->pWhere==0 ){
246 p->pWhere = pTerm->pOn;
247 }else{
248 p->pWhere = sqliteExpr(TK_AND, p->pWhere, pTerm->pOn, 0);
249 }
250 pTerm->pOn = 0;
251 }
252
253 /* Create extra terms on the WHERE clause for each column named
254 ** in the USING clause. Example: If the two tables to be joined are
255 ** A and B and the USING clause names X, Y, and Z, then add this
256 ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
257 ** Report an error if any column mentioned in the USING clause is
258 ** not contained in both tables to be joined.
259 */
260 if( pTerm->pUsing ){
261 IdList *pList;
262 int j;
263 assert( i<pSrc->nSrc-1 );
264 pList = pTerm->pUsing;
265 for(j=0; j<pList->nId; j++){
drhbf5cd972002-06-24 12:20:23 +0000266 if( columnIndex(pTerm->pTab, pList->a[j].zName)<0 ||
267 columnIndex(pOther->pTab, pList->a[j].zName)<0 ){
drhda93d232003-03-31 02:12:46 +0000268 sqliteErrorMsg(pParse, "cannot join using column %s - column "
269 "not present in both tables", pList->a[j].zName);
drhad2d8302002-05-24 20:31:36 +0000270 return 1;
271 }
drhbf5cd972002-06-24 12:20:23 +0000272 addWhereTerm(pList->a[j].zName, pTerm->pTab, pOther->pTab, &p->pWhere);
drhad2d8302002-05-24 20:31:36 +0000273 }
274 }
275 }
276 return 0;
277}
278
279/*
drh1f162302002-10-27 19:35:33 +0000280** This routine implements a minimal Oracle8 join syntax immulation.
281** The precise oracle8 syntax is not implemented - it is easy enough
282** to get this routine confused. But this routine does make it possible
283** to write a single SQL statement that does a left outer join in both
284** oracle8 and in SQLite.
285**
286** This routine looks for TK_COLUMN expression nodes that are marked
287** with the EP_Oracle8Join property. Such nodes are generated by a
288** column name (either "column" or "table.column") that is followed by
289** the special "(+)" operator. If the table of the column marked with
290** the (+) operator is the second are subsequent table in a join, then
291** that table becomes the left table in a LEFT OUTER JOIN. The expression
292** that uses that table becomes part of the ON clause for the join.
293**
294** It is important to enphasize that this is not exactly how oracle8
295** works. But it is close enough so that one can construct queries that
296** will work correctly for both SQLite and Oracle8.
297*/
298static int sqliteOracle8JoinFixup(
299 int base, /* VDBE cursor number for first table in pSrc */
300 SrcList *pSrc, /* List of tables being joined */
301 Expr *pWhere /* The WHERE clause of the SELECT statement */
302){
303 int rc = 0;
304 if( ExprHasProperty(pWhere, EP_Oracle8Join) && pWhere->op==TK_COLUMN ){
305 int idx = pWhere->iTable - base;
306 assert( idx>=0 && idx<pSrc->nSrc );
307 if( idx>0 ){
308 pSrc->a[idx-1].jointype &= ~JT_INNER;
309 pSrc->a[idx-1].jointype |= JT_OUTER|JT_LEFT;
310 return 1;
311 }
312 }
313 if( pWhere->pRight ){
314 rc = sqliteOracle8JoinFixup(base, pSrc, pWhere->pRight);
315 }
316 if( pWhere->pLeft ){
317 rc |= sqliteOracle8JoinFixup(base, pSrc, pWhere->pLeft);
318 }
319 if( pWhere->pList ){
320 int i;
321 ExprList *pList = pWhere->pList;
322 for(i=0; i<pList->nExpr && rc==0; i++){
323 rc |= sqliteOracle8JoinFixup(base, pSrc, pList->a[i].pExpr);
324 }
325 }
326 if( rc==1 && (pWhere->op==TK_AND || pWhere->op==TK_EQ) ){
327 setJoinExpr(pWhere);
328 rc = 0;
329 }
330 return rc;
331}
332
333/*
drh9bb61fe2000-06-05 16:01:39 +0000334** Delete the given Select structure and all of its substructures.
335*/
336void sqliteSelectDelete(Select *p){
drh82c3d632000-06-06 21:56:07 +0000337 if( p==0 ) return;
drh9bb61fe2000-06-05 16:01:39 +0000338 sqliteExprListDelete(p->pEList);
drhad3cab52002-05-24 02:04:32 +0000339 sqliteSrcListDelete(p->pSrc);
drh9bb61fe2000-06-05 16:01:39 +0000340 sqliteExprDelete(p->pWhere);
341 sqliteExprListDelete(p->pGroupBy);
342 sqliteExprDelete(p->pHaving);
343 sqliteExprListDelete(p->pOrderBy);
drh82c3d632000-06-06 21:56:07 +0000344 sqliteSelectDelete(p->pPrior);
drha76b5df2002-02-23 02:32:10 +0000345 sqliteFree(p->zSelect);
drh9bb61fe2000-06-05 16:01:39 +0000346 sqliteFree(p);
347}
348
349/*
drh22827922000-06-06 17:27:05 +0000350** Delete the aggregate information from the parse structure.
351*/
drh1d83f052002-02-17 00:30:36 +0000352static void sqliteAggregateInfoReset(Parse *pParse){
drh22827922000-06-06 17:27:05 +0000353 sqliteFree(pParse->aAgg);
354 pParse->aAgg = 0;
355 pParse->nAgg = 0;
drh22827922000-06-06 17:27:05 +0000356 pParse->useAgg = 0;
357}
358
359/*
drhc926afb2002-06-20 03:38:26 +0000360** Insert code into "v" that will push the record on the top of the
361** stack into the sorter.
362*/
363static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
364 char *zSortOrder;
365 int i;
366 zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
367 if( zSortOrder==0 ) return;
368 for(i=0; i<pOrderBy->nExpr; i++){
drh38640e12002-07-05 21:42:36 +0000369 int order = pOrderBy->a[i].sortOrder;
370 int type;
371 int c;
372 if( (order & SQLITE_SO_TYPEMASK)==SQLITE_SO_TEXT ){
373 type = SQLITE_SO_TEXT;
374 }else if( (order & SQLITE_SO_TYPEMASK)==SQLITE_SO_NUM ){
375 type = SQLITE_SO_NUM;
drh491791a2002-07-18 00:34:09 +0000376 }else if( pParse->db->file_format>=4 ){
drh38640e12002-07-05 21:42:36 +0000377 type = sqliteExprType(pOrderBy->a[i].pExpr);
378 }else{
379 type = SQLITE_SO_NUM;
380 }
381 if( (order & SQLITE_SO_DIRMASK)==SQLITE_SO_ASC ){
382 c = type==SQLITE_SO_TEXT ? 'A' : '+';
383 }else{
384 c = type==SQLITE_SO_TEXT ? 'D' : '-';
385 }
386 zSortOrder[i] = c;
drhc926afb2002-06-20 03:38:26 +0000387 sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
388 }
389 zSortOrder[pOrderBy->nExpr] = 0;
390 sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0);
391 sqliteVdbeChangeP3(v, -1, zSortOrder, strlen(zSortOrder));
392 sqliteFree(zSortOrder);
393 sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
394}
395
396/*
drh38640e12002-07-05 21:42:36 +0000397** This routine adds a P3 argument to the last VDBE opcode that was
398** inserted. The P3 argument added is a string suitable for the
399** OP_MakeKey or OP_MakeIdxKey opcodes. The string consists of
400** characters 't' or 'n' depending on whether or not the various
401** fields of the key to be generated should be treated as numeric
402** or as text. See the OP_MakeKey and OP_MakeIdxKey opcode
403** documentation for additional information about the P3 string.
404** See also the sqliteAddIdxKeyType() routine.
405*/
406void sqliteAddKeyType(Vdbe *v, ExprList *pEList){
407 int nColumn = pEList->nExpr;
408 char *zType = sqliteMalloc( nColumn+1 );
409 int i;
410 if( zType==0 ) return;
411 for(i=0; i<nColumn; i++){
412 zType[i] = sqliteExprType(pEList->a[i].pExpr)==SQLITE_SO_NUM ? 'n' : 't';
413 }
414 zType[i] = 0;
415 sqliteVdbeChangeP3(v, -1, zType, nColumn);
416 sqliteFree(zType);
417}
418
419/*
drh22827922000-06-06 17:27:05 +0000420** This routine generates the code for the inside of the inner loop
421** of a SELECT.
drh82c3d632000-06-06 21:56:07 +0000422**
drh38640e12002-07-05 21:42:36 +0000423** If srcTab and nColumn are both zero, then the pEList expressions
424** are evaluated in order to get the data for this row. If nColumn>0
425** then data is pulled from srcTab and pEList is used only to get the
426** datatypes for each column.
drh22827922000-06-06 17:27:05 +0000427*/
428static int selectInnerLoop(
429 Parse *pParse, /* The parser context */
drhdf199a22002-06-14 22:38:41 +0000430 Select *p, /* The complete select statement being coded */
drh22827922000-06-06 17:27:05 +0000431 ExprList *pEList, /* List of values being extracted */
drh82c3d632000-06-06 21:56:07 +0000432 int srcTab, /* Pull data from this table */
drh967e8b72000-06-21 13:59:10 +0000433 int nColumn, /* Number of columns in the source table */
drh22827922000-06-06 17:27:05 +0000434 ExprList *pOrderBy, /* If not NULL, sort results using this key */
435 int distinct, /* If >=0, make sure results are distinct */
436 int eDest, /* How to dispose of the results */
437 int iParm, /* An argument to the disposal method */
438 int iContinue, /* Jump here to continue with next row */
439 int iBreak /* Jump here to break out of the inner loop */
440){
441 Vdbe *v = pParse->pVdbe;
442 int i;
drh38640e12002-07-05 21:42:36 +0000443
drhdaffd0e2001-04-11 14:28:42 +0000444 if( v==0 ) return 0;
drh38640e12002-07-05 21:42:36 +0000445 assert( pEList!=0 );
drh22827922000-06-06 17:27:05 +0000446
drhdf199a22002-06-14 22:38:41 +0000447 /* If there was a LIMIT clause on the SELECT statement, then do the check
448 ** to see if this row should be output.
449 */
450 if( pOrderBy==0 ){
451 if( p->nOffset>0 ){
drhd11d3822002-06-21 23:01:49 +0000452 int addr = sqliteVdbeCurrentAddr(v);
453 sqliteVdbeAddOp(v, OP_MemIncr, p->nOffset, addr+2);
454 sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
drhdf199a22002-06-14 22:38:41 +0000455 }
drhd11d3822002-06-21 23:01:49 +0000456 if( p->nLimit>=0 ){
457 sqliteVdbeAddOp(v, OP_MemIncr, p->nLimit, iBreak);
drhdf199a22002-06-14 22:38:41 +0000458 }
459 }
460
drh967e8b72000-06-21 13:59:10 +0000461 /* Pull the requested columns.
drh22827922000-06-06 17:27:05 +0000462 */
drh38640e12002-07-05 21:42:36 +0000463 if( nColumn>0 ){
drh967e8b72000-06-21 13:59:10 +0000464 for(i=0; i<nColumn; i++){
drh99fcd712001-10-13 01:06:47 +0000465 sqliteVdbeAddOp(v, OP_Column, srcTab, i);
drh82c3d632000-06-06 21:56:07 +0000466 }
drh38640e12002-07-05 21:42:36 +0000467 }else{
468 nColumn = pEList->nExpr;
469 for(i=0; i<pEList->nExpr; i++){
470 sqliteExprCode(pParse, pEList->a[i].pExpr);
471 }
drh22827922000-06-06 17:27:05 +0000472 }
473
drhdaffd0e2001-04-11 14:28:42 +0000474 /* If the DISTINCT keyword was present on the SELECT statement
475 ** and this row has been seen before, then do not make this row
476 ** part of the result.
drh22827922000-06-06 17:27:05 +0000477 */
drhf5905aa2002-05-26 20:54:33 +0000478 if( distinct>=0 && pEList && pEList->nExpr>0 ){
drh0bd1f4e2002-06-06 18:54:39 +0000479#if NULL_ALWAYS_DISTINCT
480 sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqliteVdbeCurrentAddr(v)+7);
481#endif
drh99fcd712001-10-13 01:06:47 +0000482 sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1);
drh491791a2002-07-18 00:34:09 +0000483 if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pEList);
drhf5905aa2002-05-26 20:54:33 +0000484 sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
drh99fcd712001-10-13 01:06:47 +0000485 sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
486 sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
drh99fcd712001-10-13 01:06:47 +0000487 sqliteVdbeAddOp(v, OP_String, 0, 0);
drh6b125452002-01-28 15:53:03 +0000488 sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0);
drh22827922000-06-06 17:27:05 +0000489 }
drh82c3d632000-06-06 21:56:07 +0000490
drhc926afb2002-06-20 03:38:26 +0000491 switch( eDest ){
492 /* In this mode, write each query result to the key of the temporary
493 ** table iParm.
494 */
495 case SRT_Union: {
496 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
497 sqliteVdbeAddOp(v, OP_String, 0, 0);
498 sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
499 break;
drh22827922000-06-06 17:27:05 +0000500 }
drh22827922000-06-06 17:27:05 +0000501
drhc926afb2002-06-20 03:38:26 +0000502 /* Store the result as data using a unique key.
503 */
504 case SRT_Table:
505 case SRT_TempTable: {
506 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
507 if( pOrderBy ){
508 pushOntoSorter(pParse, v, pOrderBy);
509 }else{
510 sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
511 sqliteVdbeAddOp(v, OP_Pull, 1, 0);
512 sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
513 }
514 break;
515 }
drh82c3d632000-06-06 21:56:07 +0000516
drhc926afb2002-06-20 03:38:26 +0000517 /* Construct a record from the query result, but instead of
518 ** saving that record, use it as a key to delete elements from
519 ** the temporary table iParm.
520 */
521 case SRT_Except: {
522 int addr;
523 addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
524 sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
525 sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
526 break;
527 }
drh5974a302000-06-07 14:42:26 +0000528
drhc926afb2002-06-20 03:38:26 +0000529 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
530 ** then there should be a single item on the stack. Write this
531 ** item into the set table with bogus data.
532 */
533 case SRT_Set: {
drha9f9d1c2002-06-29 02:20:08 +0000534 int lbl = sqliteVdbeMakeLabel(v);
drhc926afb2002-06-20 03:38:26 +0000535 assert( nColumn==1 );
drha9f9d1c2002-06-29 02:20:08 +0000536 sqliteVdbeAddOp(v, OP_IsNull, -1, lbl);
drhc926afb2002-06-20 03:38:26 +0000537 if( pOrderBy ){
538 pushOntoSorter(pParse, v, pOrderBy);
539 }else{
drha9f9d1c2002-06-29 02:20:08 +0000540 sqliteVdbeAddOp(v, OP_String, 0, 0);
drhc926afb2002-06-20 03:38:26 +0000541 sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
542 }
drha9f9d1c2002-06-29 02:20:08 +0000543 sqliteVdbeResolveLabel(v, lbl);
drhc926afb2002-06-20 03:38:26 +0000544 break;
545 }
drh22827922000-06-06 17:27:05 +0000546
drhc926afb2002-06-20 03:38:26 +0000547 /* If this is a scalar select that is part of an expression, then
548 ** store the results in the appropriate memory cell and break out
549 ** of the scan loop.
550 */
551 case SRT_Mem: {
552 assert( nColumn==1 );
553 if( pOrderBy ){
554 pushOntoSorter(pParse, v, pOrderBy);
555 }else{
556 sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
557 sqliteVdbeAddOp(v, OP_Goto, 0, iBreak);
558 }
559 break;
560 }
drh22827922000-06-06 17:27:05 +0000561
drhf46f9052002-06-22 02:33:38 +0000562 /* Send the data to the callback function.
563 */
564 case SRT_Callback:
565 case SRT_Sorter: {
566 if( pOrderBy ){
567 sqliteVdbeAddOp(v, OP_SortMakeRec, nColumn, 0);
568 pushOntoSorter(pParse, v, pOrderBy);
569 }else{
570 assert( eDest==SRT_Callback );
571 sqliteVdbeAddOp(v, OP_Callback, nColumn, 0);
572 }
573 break;
574 }
575
drh142e30d2002-08-28 03:00:58 +0000576 /* Invoke a subroutine to handle the results. The subroutine itself
577 ** is responsible for popping the results off of the stack.
578 */
579 case SRT_Subroutine: {
drhac82fcf2002-09-08 17:23:41 +0000580 if( pOrderBy ){
581 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
582 pushOntoSorter(pParse, v, pOrderBy);
583 }else{
584 sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
585 }
drh142e30d2002-08-28 03:00:58 +0000586 break;
587 }
588
drhc926afb2002-06-20 03:38:26 +0000589 /* Discard the results. This is used for SELECT statements inside
590 ** the body of a TRIGGER. The purpose of such selects is to call
591 ** user-defined functions that have side effects. We do not care
592 ** about the actual results of the select.
593 */
drhc926afb2002-06-20 03:38:26 +0000594 default: {
drhf46f9052002-06-22 02:33:38 +0000595 assert( eDest==SRT_Discard );
596 sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
drhc926afb2002-06-20 03:38:26 +0000597 break;
598 }
drh82c3d632000-06-06 21:56:07 +0000599 }
600 return 0;
601}
602
603/*
drhd8bc7082000-06-07 23:51:50 +0000604** If the inner loop was generated using a non-null pOrderBy argument,
605** then the results were placed in a sorter. After the loop is terminated
606** we need to run the sorter and output the results. The following
607** routine generates the code needed to do that.
608*/
drhc926afb2002-06-20 03:38:26 +0000609static void generateSortTail(
610 Select *p, /* The SELECT statement */
611 Vdbe *v, /* Generate code into this VDBE */
612 int nColumn, /* Number of columns of data */
613 int eDest, /* Write the sorted results here */
614 int iParm /* Optional parameter associated with eDest */
615){
drhd8bc7082000-06-07 23:51:50 +0000616 int end = sqliteVdbeMakeLabel(v);
617 int addr;
drhf46f9052002-06-22 02:33:38 +0000618 if( eDest==SRT_Sorter ) return;
drh99fcd712001-10-13 01:06:47 +0000619 sqliteVdbeAddOp(v, OP_Sort, 0, 0);
620 addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end);
drhdf199a22002-06-14 22:38:41 +0000621 if( p->nOffset>0 ){
drhd11d3822002-06-21 23:01:49 +0000622 sqliteVdbeAddOp(v, OP_MemIncr, p->nOffset, addr+4);
623 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
624 sqliteVdbeAddOp(v, OP_Goto, 0, addr);
drhdf199a22002-06-14 22:38:41 +0000625 }
drhd11d3822002-06-21 23:01:49 +0000626 if( p->nLimit>=0 ){
627 sqliteVdbeAddOp(v, OP_MemIncr, p->nLimit, end);
drhdf199a22002-06-14 22:38:41 +0000628 }
drhc926afb2002-06-20 03:38:26 +0000629 switch( eDest ){
630 case SRT_Callback: {
631 sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
632 break;
633 }
634 case SRT_Table:
635 case SRT_TempTable: {
636 sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
637 sqliteVdbeAddOp(v, OP_Pull, 1, 0);
638 sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
639 break;
640 }
641 case SRT_Set: {
642 assert( nColumn==1 );
643 sqliteVdbeAddOp(v, OP_IsNull, -1, sqliteVdbeCurrentAddr(v)+3);
644 sqliteVdbeAddOp(v, OP_String, 0, 0);
645 sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
646 break;
647 }
648 case SRT_Mem: {
649 assert( nColumn==1 );
650 sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
651 sqliteVdbeAddOp(v, OP_Goto, 0, end);
652 break;
653 }
drhac82fcf2002-09-08 17:23:41 +0000654 case SRT_Subroutine: {
655 int i;
656 for(i=0; i<nColumn; i++){
657 sqliteVdbeAddOp(v, OP_Column, -1-i, i);
658 }
659 sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
660 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
661 break;
662 }
drhc926afb2002-06-20 03:38:26 +0000663 default: {
drhf46f9052002-06-22 02:33:38 +0000664 /* Do nothing */
drhc926afb2002-06-20 03:38:26 +0000665 break;
666 }
667 }
drh99fcd712001-10-13 01:06:47 +0000668 sqliteVdbeAddOp(v, OP_Goto, 0, addr);
669 sqliteVdbeResolveLabel(v, end);
drha8b38d22001-11-01 14:41:34 +0000670 sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
drhd8bc7082000-06-07 23:51:50 +0000671}
672
673/*
drhfcb78a42003-01-18 20:11:05 +0000674** Generate code that will tell the VDBE the datatypes of
675** columns in the result set.
drhe78e8282003-01-19 03:59:45 +0000676**
677** This routine only generates code if the "PRAGMA show_datatypes=on"
678** has been executed. The datatypes are reported out in the azCol
679** parameter to the callback function. The first N azCol[] entries
680** are the names of the columns, and the second N entries are the
681** datatypes for the columns.
682**
683** The "datatype" for a result that is a column of a type is the
684** datatype definition extracted from the CREATE TABLE statement.
685** The datatype for an expression is either TEXT or NUMERIC. The
686** datatype for a ROWID field is INTEGER.
drhfcb78a42003-01-18 20:11:05 +0000687*/
688static void generateColumnTypes(
689 Parse *pParse, /* Parser context */
690 int base, /* VDBE cursor corresponding to first entry in pTabList */
691 SrcList *pTabList, /* List of tables */
692 ExprList *pEList /* Expressions defining the result set */
693){
694 Vdbe *v = pParse->pVdbe;
695 int i;
drh326dce72003-01-29 14:06:07 +0000696 if( pParse->useCallback && (pParse->db->flags & SQLITE_ReportTypes)==0 ){
697 return;
698 }
drhfcb78a42003-01-18 20:11:05 +0000699 for(i=0; i<pEList->nExpr; i++){
700 Expr *p = pEList->a[i].pExpr;
701 char *zType = 0;
702 if( p==0 ) continue;
703 if( p->op==TK_COLUMN && pTabList ){
704 Table *pTab = pTabList->a[p->iTable - base].pTab;
705 int iCol = p->iColumn;
706 if( iCol<0 ) iCol = pTab->iPKey;
707 assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
708 if( iCol<0 ){
709 zType = "INTEGER";
710 }else{
711 zType = pTab->aCol[iCol].zType;
712 }
713 }else{
714 if( sqliteExprType(p)==SQLITE_SO_TEXT ){
715 zType = "TEXT";
716 }else{
717 zType = "NUMERIC";
718 }
719 }
720 sqliteVdbeAddOp(v, OP_ColumnName, i + pEList->nExpr, 0);
721 sqliteVdbeChangeP3(v, -1, zType, P3_STATIC);
722 }
723}
724
725/*
726** Generate code that will tell the VDBE the names of columns
727** in the result set. This information is used to provide the
728** azCol[] vaolues in the callback.
drh82c3d632000-06-06 21:56:07 +0000729*/
drh832508b2002-03-02 17:04:07 +0000730static void generateColumnNames(
731 Parse *pParse, /* Parser context */
732 int base, /* VDBE cursor corresponding to first entry in pTabList */
drhad3cab52002-05-24 02:04:32 +0000733 SrcList *pTabList, /* List of tables */
drh832508b2002-03-02 17:04:07 +0000734 ExprList *pEList /* Expressions defining the result set */
735){
drhd8bc7082000-06-07 23:51:50 +0000736 Vdbe *v = pParse->pVdbe;
drh82c3d632000-06-06 21:56:07 +0000737 int i;
drhdaffd0e2001-04-11 14:28:42 +0000738 if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
drhd8bc7082000-06-07 23:51:50 +0000739 pParse->colNamesSet = 1;
drh82c3d632000-06-06 21:56:07 +0000740 for(i=0; i<pEList->nExpr; i++){
741 Expr *p;
drhb1363202002-06-26 02:45:03 +0000742 char *zType = 0;
drh1bee3d72001-10-15 00:44:35 +0000743 int showFullNames;
drh5a387052003-01-11 14:19:51 +0000744 p = pEList->a[i].pExpr;
745 if( p==0 ) continue;
drh82c3d632000-06-06 21:56:07 +0000746 if( pEList->a[i].zName ){
747 char *zName = pEList->a[i].zName;
drh99fcd712001-10-13 01:06:47 +0000748 sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
749 sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
drh82c3d632000-06-06 21:56:07 +0000750 continue;
751 }
drh1bee3d72001-10-15 00:44:35 +0000752 showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
drhfa173a72002-07-10 21:26:00 +0000753 if( p->op==TK_COLUMN && pTabList ){
drh832508b2002-03-02 17:04:07 +0000754 Table *pTab = pTabList->a[p->iTable - base].pTab;
drh97665872002-02-13 23:22:53 +0000755 char *zCol;
drh8aff1012001-12-22 14:49:24 +0000756 int iCol = p->iColumn;
757 if( iCol<0 ) iCol = pTab->iPKey;
drh97665872002-02-13 23:22:53 +0000758 assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
drhb1363202002-06-26 02:45:03 +0000759 if( iCol<0 ){
760 zCol = "_ROWID_";
761 zType = "INTEGER";
762 }else{
763 zCol = pTab->aCol[iCol].zName;
764 zType = pTab->aCol[iCol].zType;
765 }
drh6977fea2002-10-22 23:38:04 +0000766 if( p->span.z && p->span.z[0] && !showFullNames ){
drhfa173a72002-07-10 21:26:00 +0000767 int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
drh6977fea2002-10-22 23:38:04 +0000768 sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
drhfa173a72002-07-10 21:26:00 +0000769 sqliteVdbeCompressSpace(v, addr);
770 }else if( pTabList->nSrc>1 || showFullNames ){
drh82c3d632000-06-06 21:56:07 +0000771 char *zName = 0;
drh82c3d632000-06-06 21:56:07 +0000772 char *zTab;
773
drh832508b2002-03-02 17:04:07 +0000774 zTab = pTabList->a[p->iTable - base].zAlias;
drh01a34662001-10-20 12:30:10 +0000775 if( showFullNames || zTab==0 ) zTab = pTab->zName;
drh97665872002-02-13 23:22:53 +0000776 sqliteSetString(&zName, zTab, ".", zCol, 0);
drh99fcd712001-10-13 01:06:47 +0000777 sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
778 sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
drh82c3d632000-06-06 21:56:07 +0000779 sqliteFree(zName);
780 }else{
drh99fcd712001-10-13 01:06:47 +0000781 sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
drh22f70c32002-02-18 01:17:00 +0000782 sqliteVdbeChangeP3(v, -1, zCol, 0);
drh82c3d632000-06-06 21:56:07 +0000783 }
drh6977fea2002-10-22 23:38:04 +0000784 }else if( p->span.z && p->span.z[0] ){
drhfa173a72002-07-10 21:26:00 +0000785 int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
drh6977fea2002-10-22 23:38:04 +0000786 sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
drh1bee3d72001-10-15 00:44:35 +0000787 sqliteVdbeCompressSpace(v, addr);
788 }else{
789 char zName[30];
790 assert( p->op!=TK_COLUMN || pTabList==0 );
791 sprintf(zName, "column%d", i+1);
792 sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
793 sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
drh82c3d632000-06-06 21:56:07 +0000794 }
795 }
796}
797
798/*
drhd8bc7082000-06-07 23:51:50 +0000799** Name of the connection operator, used for error messages.
800*/
801static const char *selectOpName(int id){
802 char *z;
803 switch( id ){
804 case TK_ALL: z = "UNION ALL"; break;
805 case TK_INTERSECT: z = "INTERSECT"; break;
806 case TK_EXCEPT: z = "EXCEPT"; break;
807 default: z = "UNION"; break;
808 }
809 return z;
810}
811
812/*
drh315555c2002-10-20 15:53:03 +0000813** Forward declaration
814*/
815static int fillInColumnList(Parse*, Select*);
816
817/*
drh22f70c32002-02-18 01:17:00 +0000818** Given a SELECT statement, generate a Table structure that describes
819** the result set of that SELECT.
820*/
821Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
822 Table *pTab;
823 int i;
824 ExprList *pEList;
drh22f70c32002-02-18 01:17:00 +0000825
826 if( fillInColumnList(pParse, pSelect) ){
827 return 0;
828 }
829 pTab = sqliteMalloc( sizeof(Table) );
830 if( pTab==0 ){
831 return 0;
832 }
833 pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
834 pEList = pSelect->pEList;
835 pTab->nCol = pEList->nExpr;
drh417be792002-03-03 18:59:40 +0000836 assert( pTab->nCol>0 );
drh22f70c32002-02-18 01:17:00 +0000837 pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
838 for(i=0; i<pTab->nCol; i++){
839 Expr *p;
840 if( pEList->a[i].zName ){
841 pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
drh6977fea2002-10-22 23:38:04 +0000842 }else if( (p=pEList->a[i].pExpr)->span.z && p->span.z[0] ){
843 sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
drhd820cb12002-02-18 03:21:45 +0000844 }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
845 p->pRight->token.z[0] ){
846 sqliteSetNString(&pTab->aCol[i].zName,
847 p->pRight->token.z, p->pRight->token.n, 0);
drh22f70c32002-02-18 01:17:00 +0000848 }else{
849 char zBuf[30];
850 sprintf(zBuf, "column%d", i+1);
851 pTab->aCol[i].zName = sqliteStrDup(zBuf);
852 }
853 }
854 pTab->iPKey = -1;
855 return pTab;
856}
857
858/*
drhad2d8302002-05-24 20:31:36 +0000859** For the given SELECT statement, do three things.
drhd8bc7082000-06-07 23:51:50 +0000860**
drhad3cab52002-05-24 02:04:32 +0000861** (1) Fill in the pTabList->a[].pTab fields in the SrcList that
drh22f70c32002-02-18 01:17:00 +0000862** defines the set of tables that should be scanned.
drhd8bc7082000-06-07 23:51:50 +0000863**
drhad2d8302002-05-24 20:31:36 +0000864** (2) Add terms to the WHERE clause to accomodate the NATURAL keyword
865** on joins and the ON and USING clause of joins.
866**
867** (3) Scan the list of columns in the result set (pEList) looking
drh54473222002-04-04 02:10:55 +0000868** for instances of the "*" operator or the TABLE.* operator.
869** If found, expand each "*" to be every column in every table
870** and TABLE.* to be every column in TABLE.
drhd8bc7082000-06-07 23:51:50 +0000871**
872** Return 0 on success. If there are problems, leave an error message
873** in pParse and return non-zero.
874*/
875static int fillInColumnList(Parse *pParse, Select *p){
drh54473222002-04-04 02:10:55 +0000876 int i, j, k, rc;
drhad3cab52002-05-24 02:04:32 +0000877 SrcList *pTabList;
drhdaffd0e2001-04-11 14:28:42 +0000878 ExprList *pEList;
drha76b5df2002-02-23 02:32:10 +0000879 Table *pTab;
drhdaffd0e2001-04-11 14:28:42 +0000880
881 if( p==0 || p->pSrc==0 ) return 1;
882 pTabList = p->pSrc;
883 pEList = p->pEList;
drhd8bc7082000-06-07 23:51:50 +0000884
885 /* Look up every table in the table list.
886 */
drhad3cab52002-05-24 02:04:32 +0000887 for(i=0; i<pTabList->nSrc; i++){
drhd8bc7082000-06-07 23:51:50 +0000888 if( pTabList->a[i].pTab ){
889 /* This routine has run before! No need to continue */
890 return 0;
891 }
drhdaffd0e2001-04-11 14:28:42 +0000892 if( pTabList->a[i].zName==0 ){
drh22f70c32002-02-18 01:17:00 +0000893 /* A sub-query in the FROM clause of a SELECT */
drh22f70c32002-02-18 01:17:00 +0000894 assert( pTabList->a[i].pSelect!=0 );
drhad2d8302002-05-24 20:31:36 +0000895 if( pTabList->a[i].zAlias==0 ){
896 char zFakeName[60];
897 sprintf(zFakeName, "sqlite_subquery_%p_",
898 (void*)pTabList->a[i].pSelect);
899 sqliteSetString(&pTabList->a[i].zAlias, zFakeName, 0);
900 }
drh22f70c32002-02-18 01:17:00 +0000901 pTabList->a[i].pTab = pTab =
902 sqliteResultSetOfSelect(pParse, pTabList->a[i].zAlias,
903 pTabList->a[i].pSelect);
904 if( pTab==0 ){
905 return 1;
906 }
907 pTab->isTransient = 1;
908 }else{
drha76b5df2002-02-23 02:32:10 +0000909 /* An ordinary table or view name in the FROM clause */
910 pTabList->a[i].pTab = pTab =
drha69d9162003-04-17 22:57:53 +0000911 sqliteLocateTable(pParse,pTabList->a[i].zName,pTabList->a[i].zDatabase);
drha76b5df2002-02-23 02:32:10 +0000912 if( pTab==0 ){
drh22f70c32002-02-18 01:17:00 +0000913 return 1;
914 }
drha76b5df2002-02-23 02:32:10 +0000915 if( pTab->pSelect ){
drh417be792002-03-03 18:59:40 +0000916 if( sqliteViewGetColumnNames(pParse, pTab) ){
917 return 1;
918 }
drhd94a6692002-08-25 18:29:11 +0000919 sqliteSelectDelete(pTabList->a[i].pSelect);
drhff78bd22002-02-27 01:47:11 +0000920 pTabList->a[i].pSelect = sqliteSelectDup(pTab->pSelect);
drha76b5df2002-02-23 02:32:10 +0000921 }
drhd8bc7082000-06-07 23:51:50 +0000922 }
923 }
924
drhad2d8302002-05-24 20:31:36 +0000925 /* Process NATURAL keywords, and ON and USING clauses of joins.
926 */
927 if( sqliteProcessJoin(pParse, p) ) return 1;
928
drh7c917d12001-12-16 20:05:05 +0000929 /* For every "*" that occurs in the column list, insert the names of
drh54473222002-04-04 02:10:55 +0000930 ** all columns in all tables. And for every TABLE.* insert the names
931 ** of all columns in TABLE. The parser inserted a special expression
drh7c917d12001-12-16 20:05:05 +0000932 ** with the TK_ALL operator for each "*" that it found in the column list.
933 ** The following code just has to locate the TK_ALL expressions and expand
934 ** each one to the list of all columns in all tables.
drh54473222002-04-04 02:10:55 +0000935 **
936 ** The first loop just checks to see if there are any "*" operators
937 ** that need expanding.
drhd8bc7082000-06-07 23:51:50 +0000938 */
drh7c917d12001-12-16 20:05:05 +0000939 for(k=0; k<pEList->nExpr; k++){
drh54473222002-04-04 02:10:55 +0000940 Expr *pE = pEList->a[k].pExpr;
941 if( pE->op==TK_ALL ) break;
942 if( pE->op==TK_DOT && pE->pRight && pE->pRight->op==TK_ALL
943 && pE->pLeft && pE->pLeft->op==TK_ID ) break;
drh7c917d12001-12-16 20:05:05 +0000944 }
drh54473222002-04-04 02:10:55 +0000945 rc = 0;
drh7c917d12001-12-16 20:05:05 +0000946 if( k<pEList->nExpr ){
drh54473222002-04-04 02:10:55 +0000947 /*
948 ** If we get here it means the result set contains one or more "*"
949 ** operators that need to be expanded. Loop through each expression
950 ** in the result set and expand them one by one.
951 */
drh7c917d12001-12-16 20:05:05 +0000952 struct ExprList_item *a = pEList->a;
953 ExprList *pNew = 0;
954 for(k=0; k<pEList->nExpr; k++){
drh54473222002-04-04 02:10:55 +0000955 Expr *pE = a[k].pExpr;
956 if( pE->op!=TK_ALL &&
957 (pE->op!=TK_DOT || pE->pRight==0 || pE->pRight->op!=TK_ALL) ){
958 /* This particular expression does not need to be expanded.
959 */
drh7c917d12001-12-16 20:05:05 +0000960 pNew = sqliteExprListAppend(pNew, a[k].pExpr, 0);
961 pNew->a[pNew->nExpr-1].zName = a[k].zName;
962 a[k].pExpr = 0;
963 a[k].zName = 0;
964 }else{
drh54473222002-04-04 02:10:55 +0000965 /* This expression is a "*" or a "TABLE.*" and needs to be
966 ** expanded. */
967 int tableSeen = 0; /* Set to 1 when TABLE matches */
968 Token *pName; /* text of name of TABLE */
969 if( pE->op==TK_DOT && pE->pLeft ){
970 pName = &pE->pLeft->token;
971 }else{
972 pName = 0;
973 }
drhad3cab52002-05-24 02:04:32 +0000974 for(i=0; i<pTabList->nSrc; i++){
drh7c917d12001-12-16 20:05:05 +0000975 Table *pTab = pTabList->a[i].pTab;
drh54473222002-04-04 02:10:55 +0000976 char *zTabName = pTabList->a[i].zAlias;
977 if( zTabName==0 || zTabName[0]==0 ){
978 zTabName = pTab->zName;
979 }
drhc754fa52002-05-27 03:25:51 +0000980 if( pName && (zTabName==0 || zTabName[0]==0 ||
981 sqliteStrNICmp(pName->z, zTabName, pName->n)!=0 ||
982 zTabName[pName->n]!=0) ){
drh54473222002-04-04 02:10:55 +0000983 continue;
984 }
985 tableSeen = 1;
drh7c917d12001-12-16 20:05:05 +0000986 for(j=0; j<pTab->nCol; j++){
drh22f70c32002-02-18 01:17:00 +0000987 Expr *pExpr, *pLeft, *pRight;
drhad2d8302002-05-24 20:31:36 +0000988 char *zName = pTab->aCol[j].zName;
989
990 if( i>0 && (pTabList->a[i-1].jointype & JT_NATURAL)!=0 &&
991 columnIndex(pTabList->a[i-1].pTab, zName)>=0 ){
992 /* In a NATURAL join, omit the join columns from the
993 ** table on the right */
994 continue;
995 }
996 if( i>0 && sqliteIdListIndex(pTabList->a[i-1].pUsing, zName)>=0 ){
997 /* In a join with a USING clause, omit columns in the
998 ** using clause from the table on the right. */
999 continue;
1000 }
drh22f70c32002-02-18 01:17:00 +00001001 pRight = sqliteExpr(TK_ID, 0, 0, 0);
1002 if( pRight==0 ) break;
drhad2d8302002-05-24 20:31:36 +00001003 pRight->token.z = zName;
1004 pRight->token.n = strlen(zName);
drh4b59ab52002-08-24 18:24:51 +00001005 pRight->token.dyn = 0;
drh4b59ab52002-08-24 18:24:51 +00001006 if( zTabName && pTabList->nSrc>1 ){
drh22f70c32002-02-18 01:17:00 +00001007 pLeft = sqliteExpr(TK_ID, 0, 0, 0);
drh22f70c32002-02-18 01:17:00 +00001008 pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
1009 if( pExpr==0 ) break;
drh4b59ab52002-08-24 18:24:51 +00001010 pLeft->token.z = zTabName;
1011 pLeft->token.n = strlen(zTabName);
1012 pLeft->token.dyn = 0;
drh6977fea2002-10-22 23:38:04 +00001013 sqliteSetString((char**)&pExpr->span.z, zTabName, ".", zName, 0);
1014 pExpr->span.n = strlen(pExpr->span.z);
1015 pExpr->span.dyn = 1;
1016 pExpr->token.z = 0;
1017 pExpr->token.n = 0;
1018 pExpr->token.dyn = 0;
drh7c917d12001-12-16 20:05:05 +00001019 }else{
drh22f70c32002-02-18 01:17:00 +00001020 pExpr = pRight;
drh6977fea2002-10-22 23:38:04 +00001021 pExpr->span = pExpr->token;
drh7c917d12001-12-16 20:05:05 +00001022 }
drh7c917d12001-12-16 20:05:05 +00001023 pNew = sqliteExprListAppend(pNew, pExpr, 0);
1024 }
drh17e24df2001-11-06 14:10:41 +00001025 }
drh54473222002-04-04 02:10:55 +00001026 if( !tableSeen ){
drhf5db2d32002-06-06 23:42:27 +00001027 if( pName ){
drhda93d232003-03-31 02:12:46 +00001028 sqliteErrorMsg(pParse, "no such table: %T", pName);
drhf5db2d32002-06-06 23:42:27 +00001029 }else{
drhda93d232003-03-31 02:12:46 +00001030 sqliteErrorMsg(pParse, "no tables specified");
drhf5db2d32002-06-06 23:42:27 +00001031 }
drh54473222002-04-04 02:10:55 +00001032 rc = 1;
1033 }
drhd8bc7082000-06-07 23:51:50 +00001034 }
1035 }
drh7c917d12001-12-16 20:05:05 +00001036 sqliteExprListDelete(pEList);
1037 p->pEList = pNew;
drhd8bc7082000-06-07 23:51:50 +00001038 }
drh54473222002-04-04 02:10:55 +00001039 return rc;
drhd8bc7082000-06-07 23:51:50 +00001040}
1041
1042/*
drhff78bd22002-02-27 01:47:11 +00001043** This routine recursively unlinks the Select.pSrc.a[].pTab pointers
1044** in a select structure. It just sets the pointers to NULL. This
1045** routine is recursive in the sense that if the Select.pSrc.a[].pSelect
1046** pointer is not NULL, this routine is called recursively on that pointer.
1047**
1048** This routine is called on the Select structure that defines a
1049** VIEW in order to undo any bindings to tables. This is necessary
1050** because those tables might be DROPed by a subsequent SQL command.
1051*/
1052void sqliteSelectUnbind(Select *p){
1053 int i;
drhad3cab52002-05-24 02:04:32 +00001054 SrcList *pSrc = p->pSrc;
drhff78bd22002-02-27 01:47:11 +00001055 Table *pTab;
1056 if( p==0 ) return;
drhad3cab52002-05-24 02:04:32 +00001057 for(i=0; i<pSrc->nSrc; i++){
drhff78bd22002-02-27 01:47:11 +00001058 if( (pTab = pSrc->a[i].pTab)!=0 ){
1059 if( pTab->isTransient ){
1060 sqliteDeleteTable(0, pTab);
drh4b59ab52002-08-24 18:24:51 +00001061#if 0
drhff78bd22002-02-27 01:47:11 +00001062 sqliteSelectDelete(pSrc->a[i].pSelect);
1063 pSrc->a[i].pSelect = 0;
drh4b59ab52002-08-24 18:24:51 +00001064#endif
drhff78bd22002-02-27 01:47:11 +00001065 }
1066 pSrc->a[i].pTab = 0;
1067 if( pSrc->a[i].pSelect ){
1068 sqliteSelectUnbind(pSrc->a[i].pSelect);
1069 }
1070 }
1071 }
1072}
1073
1074/*
drhd8bc7082000-06-07 23:51:50 +00001075** This routine associates entries in an ORDER BY expression list with
1076** columns in a result. For each ORDER BY expression, the opcode of
drh967e8b72000-06-21 13:59:10 +00001077** the top-level node is changed to TK_COLUMN and the iColumn value of
drhd8bc7082000-06-07 23:51:50 +00001078** the top-level node is filled in with column number and the iTable
1079** value of the top-level node is filled with iTable parameter.
1080**
1081** If there are prior SELECT clauses, they are processed first. A match
1082** in an earlier SELECT takes precedence over a later SELECT.
1083**
1084** Any entry that does not match is flagged as an error. The number
1085** of errors is returned.
drhfcb78a42003-01-18 20:11:05 +00001086**
1087** This routine does NOT correctly initialize the Expr.dataType field
1088** of the ORDER BY expressions. The multiSelectSortOrder() routine
1089** must be called to do that after the individual select statements
1090** have all been analyzed. This routine is unable to compute Expr.dataType
1091** because it must be called before the individual select statements
1092** have been analyzed.
drhd8bc7082000-06-07 23:51:50 +00001093*/
1094static int matchOrderbyToColumn(
1095 Parse *pParse, /* A place to leave error messages */
1096 Select *pSelect, /* Match to result columns of this SELECT */
1097 ExprList *pOrderBy, /* The ORDER BY values to match against columns */
drhe4de1fe2002-06-02 16:09:01 +00001098 int iTable, /* Insert this value in iTable */
drhd8bc7082000-06-07 23:51:50 +00001099 int mustComplete /* If TRUE all ORDER BYs must match */
1100){
1101 int nErr = 0;
1102 int i, j;
1103 ExprList *pEList;
1104
drhdaffd0e2001-04-11 14:28:42 +00001105 if( pSelect==0 || pOrderBy==0 ) return 1;
drhd8bc7082000-06-07 23:51:50 +00001106 if( mustComplete ){
1107 for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].done = 0; }
1108 }
1109 if( fillInColumnList(pParse, pSelect) ){
1110 return 1;
1111 }
1112 if( pSelect->pPrior ){
drh92cd52f2000-06-08 01:55:29 +00001113 if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
1114 return 1;
1115 }
drhd8bc7082000-06-07 23:51:50 +00001116 }
1117 pEList = pSelect->pEList;
1118 for(i=0; i<pOrderBy->nExpr; i++){
1119 Expr *pE = pOrderBy->a[i].pExpr;
drhe4de1fe2002-06-02 16:09:01 +00001120 int iCol = -1;
drhd8bc7082000-06-07 23:51:50 +00001121 if( pOrderBy->a[i].done ) continue;
drhe4de1fe2002-06-02 16:09:01 +00001122 if( sqliteExprIsInteger(pE, &iCol) ){
1123 if( iCol<=0 || iCol>pEList->nExpr ){
drhda93d232003-03-31 02:12:46 +00001124 sqliteErrorMsg(pParse,
1125 "ORDER BY position %d should be between 1 and %d",
1126 iCol, pEList->nExpr);
drhe4de1fe2002-06-02 16:09:01 +00001127 nErr++;
1128 break;
1129 }
drhfcb78a42003-01-18 20:11:05 +00001130 if( !mustComplete ) continue;
drhe4de1fe2002-06-02 16:09:01 +00001131 iCol--;
1132 }
1133 for(j=0; iCol<0 && j<pEList->nExpr; j++){
drh4cfa7932000-06-08 15:10:46 +00001134 if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
drha76b5df2002-02-23 02:32:10 +00001135 char *zName, *zLabel;
1136 zName = pEList->a[j].zName;
1137 assert( pE->token.z );
1138 zLabel = sqliteStrNDup(pE->token.z, pE->token.n);
drhd8bc7082000-06-07 23:51:50 +00001139 sqliteDequote(zLabel);
1140 if( sqliteStrICmp(zName, zLabel)==0 ){
drhe4de1fe2002-06-02 16:09:01 +00001141 iCol = j;
drhd8bc7082000-06-07 23:51:50 +00001142 }
drh6e142f52000-06-08 13:36:40 +00001143 sqliteFree(zLabel);
drhd8bc7082000-06-07 23:51:50 +00001144 }
drhe4de1fe2002-06-02 16:09:01 +00001145 if( iCol<0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
1146 iCol = j;
drhd8bc7082000-06-07 23:51:50 +00001147 }
1148 }
drhe4de1fe2002-06-02 16:09:01 +00001149 if( iCol>=0 ){
1150 pE->op = TK_COLUMN;
1151 pE->iColumn = iCol;
1152 pE->iTable = iTable;
1153 pOrderBy->a[i].done = 1;
1154 }
1155 if( iCol<0 && mustComplete ){
drhda93d232003-03-31 02:12:46 +00001156 sqliteErrorMsg(pParse,
1157 "ORDER BY term number %d does not match any result column", i+1);
drhd8bc7082000-06-07 23:51:50 +00001158 nErr++;
1159 break;
1160 }
1161 }
1162 return nErr;
1163}
1164
1165/*
1166** Get a VDBE for the given parser context. Create a new one if necessary.
1167** If an error occurs, return NULL and leave a message in pParse.
1168*/
1169Vdbe *sqliteGetVdbe(Parse *pParse){
1170 Vdbe *v = pParse->pVdbe;
1171 if( v==0 ){
drh4c504392000-10-16 22:06:40 +00001172 v = pParse->pVdbe = sqliteVdbeCreate(pParse->db);
drhd8bc7082000-06-07 23:51:50 +00001173 }
drhd8bc7082000-06-07 23:51:50 +00001174 return v;
1175}
drhfcb78a42003-01-18 20:11:05 +00001176
1177/*
1178** This routine sets the Expr.dataType field on all elements of
1179** the pOrderBy expression list. The pOrderBy list will have been
1180** set up by matchOrderbyToColumn(). Hence each expression has
1181** a TK_COLUMN as its root node. The Expr.iColumn refers to a
1182** column in the result set. The datatype is set to SQLITE_SO_TEXT
1183** if the corresponding column in p and every SELECT to the left of
1184** p has a datatype of SQLITE_SO_TEXT. If the cooressponding column
1185** in p or any of the left SELECTs is SQLITE_SO_NUM, then the datatype
1186** of the order-by expression is set to SQLITE_SO_NUM.
1187**
1188** Examples:
1189**
drhe78e8282003-01-19 03:59:45 +00001190** CREATE TABLE one(a INTEGER, b TEXT);
1191** CREATE TABLE two(c VARCHAR(5), d FLOAT);
1192**
1193** SELECT b, b FROM one UNION SELECT d, c FROM two ORDER BY 1, 2;
1194**
1195** The primary sort key will use SQLITE_SO_NUM because the "d" in
1196** the second SELECT is numeric. The 1st column of the first SELECT
1197** is text but that does not matter because a numeric always overrides
1198** a text.
1199**
1200** The secondary key will use the SQLITE_SO_TEXT sort order because
1201** both the (second) "b" in the first SELECT and the "c" in the second
1202** SELECT have a datatype of text.
drhfcb78a42003-01-18 20:11:05 +00001203*/
1204static void multiSelectSortOrder(Select *p, ExprList *pOrderBy){
1205 int i;
1206 ExprList *pEList;
1207 if( pOrderBy==0 ) return;
1208 if( p==0 ){
1209 for(i=0; i<pOrderBy->nExpr; i++){
1210 pOrderBy->a[i].pExpr->dataType = SQLITE_SO_TEXT;
1211 }
1212 return;
1213 }
1214 multiSelectSortOrder(p->pPrior, pOrderBy);
1215 pEList = p->pEList;
1216 for(i=0; i<pOrderBy->nExpr; i++){
1217 Expr *pE = pOrderBy->a[i].pExpr;
1218 if( pE->dataType==SQLITE_SO_NUM ) continue;
1219 assert( pE->iColumn>=0 );
1220 if( pEList->nExpr>pE->iColumn ){
1221 pE->dataType = sqliteExprType(pEList->a[pE->iColumn].pExpr);
1222 }
1223 }
1224}
drhd8bc7082000-06-07 23:51:50 +00001225
1226/*
drh82c3d632000-06-06 21:56:07 +00001227** This routine is called to process a query that is really the union
1228** or intersection of two or more separate queries.
drhc926afb2002-06-20 03:38:26 +00001229**
drhe78e8282003-01-19 03:59:45 +00001230** "p" points to the right-most of the two queries. the query on the
1231** left is p->pPrior. The left query could also be a compound query
1232** in which case this routine will be called recursively.
1233**
1234** The results of the total query are to be written into a destination
1235** of type eDest with parameter iParm.
1236**
1237** Example 1: Consider a three-way compound SQL statement.
1238**
1239** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
1240**
1241** This statement is parsed up as follows:
1242**
1243** SELECT c FROM t3
1244** |
1245** `-----> SELECT b FROM t2
1246** |
1247** `------> SELECT c FROM t1
1248**
1249** The arrows in the diagram above represent the Select.pPrior pointer.
1250** So if this routine is called with p equal to the t3 query, then
1251** pPrior will be the t2 query. p->op will be TK_UNION in this case.
1252**
1253** Notice that because of the way SQLite parses compound SELECTs, the
1254** individual selects always group from left to right.
drh82c3d632000-06-06 21:56:07 +00001255*/
1256static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){
drh10e5e3c2000-06-08 00:19:02 +00001257 int rc; /* Success code from a subroutine */
1258 Select *pPrior; /* Another SELECT immediately to our left */
1259 Vdbe *v; /* Generate code to this VDBE */
drh82c3d632000-06-06 21:56:07 +00001260
drhd8bc7082000-06-07 23:51:50 +00001261 /* Make sure there is no ORDER BY clause on prior SELECTs. Only the
1262 ** last SELECT in the series may have an ORDER BY.
drh82c3d632000-06-06 21:56:07 +00001263 */
drhdaffd0e2001-04-11 14:28:42 +00001264 if( p==0 || p->pPrior==0 ) return 1;
drhd8bc7082000-06-07 23:51:50 +00001265 pPrior = p->pPrior;
1266 if( pPrior->pOrderBy ){
drhda93d232003-03-31 02:12:46 +00001267 sqliteErrorMsg(pParse,"ORDER BY clause should come after %s not before",
1268 selectOpName(p->op));
drh82c3d632000-06-06 21:56:07 +00001269 return 1;
1270 }
1271
drhd8bc7082000-06-07 23:51:50 +00001272 /* Make sure we have a valid query engine. If not, create a new one.
1273 */
1274 v = sqliteGetVdbe(pParse);
1275 if( v==0 ) return 1;
1276
drh1cc3d752002-03-23 00:31:29 +00001277 /* Create the destination temporary table if necessary
1278 */
1279 if( eDest==SRT_TempTable ){
1280 sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
1281 eDest = SRT_Table;
1282 }
1283
drhf46f9052002-06-22 02:33:38 +00001284 /* Generate code for the left and right SELECT statements.
drhd8bc7082000-06-07 23:51:50 +00001285 */
drh82c3d632000-06-06 21:56:07 +00001286 switch( p->op ){
drhf46f9052002-06-22 02:33:38 +00001287 case TK_ALL: {
1288 if( p->pOrderBy==0 ){
1289 rc = sqliteSelect(pParse, pPrior, eDest, iParm, 0, 0, 0);
1290 if( rc ) return rc;
1291 p->pPrior = 0;
1292 rc = sqliteSelect(pParse, p, eDest, iParm, 0, 0, 0);
1293 p->pPrior = pPrior;
1294 if( rc ) return rc;
1295 break;
1296 }
1297 /* For UNION ALL ... ORDER BY fall through to the next case */
1298 }
drh82c3d632000-06-06 21:56:07 +00001299 case TK_EXCEPT:
1300 case TK_UNION: {
drhd8bc7082000-06-07 23:51:50 +00001301 int unionTab; /* Cursor number of the temporary table holding result */
1302 int op; /* One of the SRT_ operations to apply to self */
1303 int priorOp; /* The SRT_ operation to apply to prior selects */
drhc926afb2002-06-20 03:38:26 +00001304 ExprList *pOrderBy; /* The ORDER BY clause for the right SELECT */
drh82c3d632000-06-06 21:56:07 +00001305
drhd8bc7082000-06-07 23:51:50 +00001306 priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
drhc926afb2002-06-20 03:38:26 +00001307 if( eDest==priorOp && p->pOrderBy==0 ){
drhd8bc7082000-06-07 23:51:50 +00001308 /* We can reuse a temporary table generated by a SELECT to our
drhc926afb2002-06-20 03:38:26 +00001309 ** right.
drhd8bc7082000-06-07 23:51:50 +00001310 */
drh82c3d632000-06-06 21:56:07 +00001311 unionTab = iParm;
1312 }else{
drhd8bc7082000-06-07 23:51:50 +00001313 /* We will need to create our own temporary table to hold the
1314 ** intermediate results.
1315 */
1316 unionTab = pParse->nTab++;
1317 if( p->pOrderBy
1318 && matchOrderbyToColumn(pParse, p, p->pOrderBy, unionTab, 1) ){
1319 return 1;
1320 }
drhd8bc7082000-06-07 23:51:50 +00001321 if( p->op!=TK_ALL ){
drhc6b52df2002-01-04 03:09:29 +00001322 sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 1);
drh99fcd712001-10-13 01:06:47 +00001323 sqliteVdbeAddOp(v, OP_KeyAsData, unionTab, 1);
drh345fda32001-01-15 22:51:08 +00001324 }else{
drh99fcd712001-10-13 01:06:47 +00001325 sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 0);
drhd8bc7082000-06-07 23:51:50 +00001326 }
drh82c3d632000-06-06 21:56:07 +00001327 }
drhd8bc7082000-06-07 23:51:50 +00001328
1329 /* Code the SELECT statements to our left
1330 */
drh832508b2002-03-02 17:04:07 +00001331 rc = sqliteSelect(pParse, pPrior, priorOp, unionTab, 0, 0, 0);
drh82c3d632000-06-06 21:56:07 +00001332 if( rc ) return rc;
drhd8bc7082000-06-07 23:51:50 +00001333
1334 /* Code the current SELECT statement
1335 */
1336 switch( p->op ){
1337 case TK_EXCEPT: op = SRT_Except; break;
1338 case TK_UNION: op = SRT_Union; break;
1339 case TK_ALL: op = SRT_Table; break;
1340 }
drh82c3d632000-06-06 21:56:07 +00001341 p->pPrior = 0;
drhc926afb2002-06-20 03:38:26 +00001342 pOrderBy = p->pOrderBy;
1343 p->pOrderBy = 0;
drh832508b2002-03-02 17:04:07 +00001344 rc = sqliteSelect(pParse, p, op, unionTab, 0, 0, 0);
drh82c3d632000-06-06 21:56:07 +00001345 p->pPrior = pPrior;
drhc926afb2002-06-20 03:38:26 +00001346 p->pOrderBy = pOrderBy;
drh82c3d632000-06-06 21:56:07 +00001347 if( rc ) return rc;
drhd8bc7082000-06-07 23:51:50 +00001348
1349 /* Convert the data in the temporary table into whatever form
1350 ** it is that we currently need.
1351 */
drhc926afb2002-06-20 03:38:26 +00001352 if( eDest!=priorOp || unionTab!=iParm ){
drh6b563442001-11-07 16:48:26 +00001353 int iCont, iBreak, iStart;
drh82c3d632000-06-06 21:56:07 +00001354 assert( p->pEList );
drh41202cc2002-04-23 17:10:18 +00001355 if( eDest==SRT_Callback ){
1356 generateColumnNames(pParse, p->base, 0, p->pEList);
drhfcb78a42003-01-18 20:11:05 +00001357 generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
drh41202cc2002-04-23 17:10:18 +00001358 }
drh82c3d632000-06-06 21:56:07 +00001359 iBreak = sqliteVdbeMakeLabel(v);
drh6b563442001-11-07 16:48:26 +00001360 iCont = sqliteVdbeMakeLabel(v);
1361 sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
1362 iStart = sqliteVdbeCurrentAddr(v);
drhfcb78a42003-01-18 20:11:05 +00001363 multiSelectSortOrder(p, p->pOrderBy);
drh38640e12002-07-05 21:42:36 +00001364 rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
drhd8bc7082000-06-07 23:51:50 +00001365 p->pOrderBy, -1, eDest, iParm,
drh82c3d632000-06-06 21:56:07 +00001366 iCont, iBreak);
1367 if( rc ) return 1;
drh6b563442001-11-07 16:48:26 +00001368 sqliteVdbeResolveLabel(v, iCont);
1369 sqliteVdbeAddOp(v, OP_Next, unionTab, iStart);
drh99fcd712001-10-13 01:06:47 +00001370 sqliteVdbeResolveLabel(v, iBreak);
1371 sqliteVdbeAddOp(v, OP_Close, unionTab, 0);
drhd8bc7082000-06-07 23:51:50 +00001372 if( p->pOrderBy ){
drhc926afb2002-06-20 03:38:26 +00001373 generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
drhd8bc7082000-06-07 23:51:50 +00001374 }
drh82c3d632000-06-06 21:56:07 +00001375 }
1376 break;
1377 }
1378 case TK_INTERSECT: {
1379 int tab1, tab2;
drh6b563442001-11-07 16:48:26 +00001380 int iCont, iBreak, iStart;
drh82c3d632000-06-06 21:56:07 +00001381
drhd8bc7082000-06-07 23:51:50 +00001382 /* INTERSECT is different from the others since it requires
drh6206d502000-06-19 19:09:08 +00001383 ** two temporary tables. Hence it has its own case. Begin
drhd8bc7082000-06-07 23:51:50 +00001384 ** by allocating the tables we will need.
1385 */
drh82c3d632000-06-06 21:56:07 +00001386 tab1 = pParse->nTab++;
1387 tab2 = pParse->nTab++;
drhd8bc7082000-06-07 23:51:50 +00001388 if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
1389 return 1;
1390 }
drhc6b52df2002-01-04 03:09:29 +00001391 sqliteVdbeAddOp(v, OP_OpenTemp, tab1, 1);
drh99fcd712001-10-13 01:06:47 +00001392 sqliteVdbeAddOp(v, OP_KeyAsData, tab1, 1);
drhd8bc7082000-06-07 23:51:50 +00001393
1394 /* Code the SELECTs to our left into temporary table "tab1".
1395 */
drh832508b2002-03-02 17:04:07 +00001396 rc = sqliteSelect(pParse, pPrior, SRT_Union, tab1, 0, 0, 0);
drh82c3d632000-06-06 21:56:07 +00001397 if( rc ) return rc;
drhd8bc7082000-06-07 23:51:50 +00001398
1399 /* Code the current SELECT into temporary table "tab2"
1400 */
drhc6b52df2002-01-04 03:09:29 +00001401 sqliteVdbeAddOp(v, OP_OpenTemp, tab2, 1);
drh99fcd712001-10-13 01:06:47 +00001402 sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1);
drh82c3d632000-06-06 21:56:07 +00001403 p->pPrior = 0;
drh832508b2002-03-02 17:04:07 +00001404 rc = sqliteSelect(pParse, p, SRT_Union, tab2, 0, 0, 0);
drh82c3d632000-06-06 21:56:07 +00001405 p->pPrior = pPrior;
1406 if( rc ) return rc;
drhd8bc7082000-06-07 23:51:50 +00001407
1408 /* Generate code to take the intersection of the two temporary
1409 ** tables.
1410 */
drh82c3d632000-06-06 21:56:07 +00001411 assert( p->pEList );
drh41202cc2002-04-23 17:10:18 +00001412 if( eDest==SRT_Callback ){
1413 generateColumnNames(pParse, p->base, 0, p->pEList);
drhfcb78a42003-01-18 20:11:05 +00001414 generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
drh41202cc2002-04-23 17:10:18 +00001415 }
drh82c3d632000-06-06 21:56:07 +00001416 iBreak = sqliteVdbeMakeLabel(v);
drh6b563442001-11-07 16:48:26 +00001417 iCont = sqliteVdbeMakeLabel(v);
1418 sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
1419 iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
drh99fcd712001-10-13 01:06:47 +00001420 sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
drhfcb78a42003-01-18 20:11:05 +00001421 multiSelectSortOrder(p, p->pOrderBy);
drh38640e12002-07-05 21:42:36 +00001422 rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
drhd8bc7082000-06-07 23:51:50 +00001423 p->pOrderBy, -1, eDest, iParm,
drh82c3d632000-06-06 21:56:07 +00001424 iCont, iBreak);
1425 if( rc ) return 1;
drh6b563442001-11-07 16:48:26 +00001426 sqliteVdbeResolveLabel(v, iCont);
1427 sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
drh99fcd712001-10-13 01:06:47 +00001428 sqliteVdbeResolveLabel(v, iBreak);
1429 sqliteVdbeAddOp(v, OP_Close, tab2, 0);
1430 sqliteVdbeAddOp(v, OP_Close, tab1, 0);
drhd8bc7082000-06-07 23:51:50 +00001431 if( p->pOrderBy ){
drhc926afb2002-06-20 03:38:26 +00001432 generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
drhd8bc7082000-06-07 23:51:50 +00001433 }
drh82c3d632000-06-06 21:56:07 +00001434 break;
1435 }
1436 }
1437 assert( p->pEList && pPrior->pEList );
1438 if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
drhda93d232003-03-31 02:12:46 +00001439 sqliteErrorMsg(pParse, "SELECTs to the left and right of %s"
1440 " do not have the same number of result columns", selectOpName(p->op));
drh82c3d632000-06-06 21:56:07 +00001441 return 1;
drh22827922000-06-06 17:27:05 +00001442 }
drhfcb78a42003-01-18 20:11:05 +00001443
1444 /* Issue a null callback if that is what the user wants.
1445 */
drh326dce72003-01-29 14:06:07 +00001446 if( eDest==SRT_Callback &&
1447 (pParse->useCallback==0 || (pParse->db->flags & SQLITE_NullCallback)!=0)
1448 ){
drhfcb78a42003-01-18 20:11:05 +00001449 sqliteVdbeAddOp(v, OP_NullCallback, p->pEList->nExpr, 0);
1450 }
drh22827922000-06-06 17:27:05 +00001451 return 0;
1452}
1453
1454/*
drh832508b2002-03-02 17:04:07 +00001455** Recursively scan through an expression tree. For every reference
1456** to a column in table number iFrom, change that reference to the
1457** same column in table number iTo.
1458*/
drh315555c2002-10-20 15:53:03 +00001459static void changeTablesInList(ExprList*, int, int); /* Forward Declaration */
drh832508b2002-03-02 17:04:07 +00001460static void changeTables(Expr *pExpr, int iFrom, int iTo){
1461 if( pExpr==0 ) return;
1462 if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){
1463 pExpr->iTable = iTo;
1464 }else{
1465 changeTables(pExpr->pLeft, iFrom, iTo);
1466 changeTables(pExpr->pRight, iFrom, iTo);
drh1b2e0322002-03-03 02:49:51 +00001467 changeTablesInList(pExpr->pList, iFrom, iTo);
1468 }
1469}
1470static void changeTablesInList(ExprList *pList, int iFrom, int iTo){
1471 if( pList ){
1472 int i;
1473 for(i=0; i<pList->nExpr; i++){
1474 changeTables(pList->a[i].pExpr, iFrom, iTo);
drh832508b2002-03-02 17:04:07 +00001475 }
1476 }
1477}
1478
1479/*
1480** Scan through the expression pExpr. Replace every reference to
1481** a column in table number iTable with a copy of the corresponding
drh84e59202002-03-14 14:33:31 +00001482** entry in pEList. (But leave references to the ROWID column
1483** unchanged.) When making a copy of an expression in pEList, change
1484** references to columns in table iSub into references to table iTable.
drh832508b2002-03-02 17:04:07 +00001485**
1486** This routine is part of the flattening procedure. A subquery
1487** whose result set is defined by pEList appears as entry in the
1488** FROM clause of a SELECT such that the VDBE cursor assigned to that
1489** FORM clause entry is iTable. This routine make the necessary
1490** changes to pExpr so that it refers directly to the source table
1491** of the subquery rather the result set of the subquery.
1492*/
drh315555c2002-10-20 15:53:03 +00001493static void substExprList(ExprList*,int,ExprList*,int); /* Forward Decl */
drh832508b2002-03-02 17:04:07 +00001494static void substExpr(Expr *pExpr, int iTable, ExprList *pEList, int iSub){
1495 if( pExpr==0 ) return;
drh84e59202002-03-14 14:33:31 +00001496 if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable && pExpr->iColumn>=0 ){
drh832508b2002-03-02 17:04:07 +00001497 Expr *pNew;
drh84e59202002-03-14 14:33:31 +00001498 assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
drh832508b2002-03-02 17:04:07 +00001499 assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
1500 pNew = pEList->a[pExpr->iColumn].pExpr;
1501 assert( pNew!=0 );
1502 pExpr->op = pNew->op;
drhfcb78a42003-01-18 20:11:05 +00001503 pExpr->dataType = pNew->dataType;
drhd94a6692002-08-25 18:29:11 +00001504 assert( pExpr->pLeft==0 );
drh832508b2002-03-02 17:04:07 +00001505 pExpr->pLeft = sqliteExprDup(pNew->pLeft);
drhd94a6692002-08-25 18:29:11 +00001506 assert( pExpr->pRight==0 );
drh832508b2002-03-02 17:04:07 +00001507 pExpr->pRight = sqliteExprDup(pNew->pRight);
drhd94a6692002-08-25 18:29:11 +00001508 assert( pExpr->pList==0 );
drh832508b2002-03-02 17:04:07 +00001509 pExpr->pList = sqliteExprListDup(pNew->pList);
1510 pExpr->iTable = pNew->iTable;
1511 pExpr->iColumn = pNew->iColumn;
1512 pExpr->iAgg = pNew->iAgg;
drh4b59ab52002-08-24 18:24:51 +00001513 sqliteTokenCopy(&pExpr->token, &pNew->token);
drh6977fea2002-10-22 23:38:04 +00001514 sqliteTokenCopy(&pExpr->span, &pNew->span);
drh832508b2002-03-02 17:04:07 +00001515 if( iSub!=iTable ){
1516 changeTables(pExpr, iSub, iTable);
1517 }
1518 }else{
drh832508b2002-03-02 17:04:07 +00001519 substExpr(pExpr->pLeft, iTable, pEList, iSub);
1520 substExpr(pExpr->pRight, iTable, pEList, iSub);
1521 substExprList(pExpr->pList, iTable, pEList, iSub);
1522 }
1523}
1524static void
1525substExprList(ExprList *pList, int iTable, ExprList *pEList, int iSub){
1526 int i;
1527 if( pList==0 ) return;
1528 for(i=0; i<pList->nExpr; i++){
1529 substExpr(pList->a[i].pExpr, iTable, pEList, iSub);
1530 }
1531}
1532
1533/*
drh1350b032002-02-27 19:00:20 +00001534** This routine attempts to flatten subqueries in order to speed
1535** execution. It returns 1 if it makes changes and 0 if no flattening
1536** occurs.
1537**
1538** To understand the concept of flattening, consider the following
1539** query:
1540**
1541** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
1542**
1543** The default way of implementing this query is to execute the
1544** subquery first and store the results in a temporary table, then
1545** run the outer query on that temporary table. This requires two
1546** passes over the data. Furthermore, because the temporary table
1547** has no indices, the WHERE clause on the outer query cannot be
drh832508b2002-03-02 17:04:07 +00001548** optimized.
drh1350b032002-02-27 19:00:20 +00001549**
drh832508b2002-03-02 17:04:07 +00001550** This routine attempts to rewrite queries such as the above into
drh1350b032002-02-27 19:00:20 +00001551** a single flat select, like this:
1552**
1553** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
1554**
1555** The code generated for this simpification gives the same result
drh832508b2002-03-02 17:04:07 +00001556** but only has to scan the data once. And because indices might
1557** exist on the table t1, a complete scan of the data might be
1558** avoided.
drh1350b032002-02-27 19:00:20 +00001559**
drh832508b2002-03-02 17:04:07 +00001560** Flattening is only attempted if all of the following are true:
drh1350b032002-02-27 19:00:20 +00001561**
drh832508b2002-03-02 17:04:07 +00001562** (1) The subquery and the outer query do not both use aggregates.
drh1350b032002-02-27 19:00:20 +00001563**
drh832508b2002-03-02 17:04:07 +00001564** (2) The subquery is not an aggregate or the outer query is not a join.
1565**
1566** (3) The subquery is not a join.
1567**
1568** (4) The subquery is not DISTINCT or the outer query is not a join.
1569**
1570** (5) The subquery is not DISTINCT or the outer query does not use
1571** aggregates.
1572**
1573** (6) The subquery does not use aggregates or the outer query is not
1574** DISTINCT.
1575**
drh08192d52002-04-30 19:20:28 +00001576** (7) The subquery has a FROM clause.
1577**
drhdf199a22002-06-14 22:38:41 +00001578** (8) The subquery does not use LIMIT or the outer query is not a join.
1579**
1580** (9) The subquery does not use LIMIT or the outer query does not use
1581** aggregates.
1582**
1583** (10) The subquery does not use aggregates or the outer query does not
1584** use LIMIT.
1585**
drh174b6192002-12-03 02:22:52 +00001586** (11) The subquery and the outer query do not both have ORDER BY clauses.
1587**
drh832508b2002-03-02 17:04:07 +00001588** In this routine, the "p" parameter is a pointer to the outer query.
1589** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
1590** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
1591**
drh665de472003-03-31 13:36:09 +00001592** If flattening is not attempted, this routine is a no-op and returns 0.
drh832508b2002-03-02 17:04:07 +00001593** If flattening is attempted this routine returns 1.
1594**
1595** All of the expression analysis must occur on both the outer query and
1596** the subquery before this routine runs.
drh1350b032002-02-27 19:00:20 +00001597*/
drh8c74a8c2002-08-25 19:20:40 +00001598static int flattenSubquery(
1599 Parse *pParse, /* The parsing context */
1600 Select *p, /* The parent or outer SELECT statement */
1601 int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
1602 int isAgg, /* True if outer SELECT uses aggregate functions */
1603 int subqueryIsAgg /* True if the subquery uses aggregate functions */
1604){
drh0bb28102002-05-08 11:54:14 +00001605 Select *pSub; /* The inner query or "subquery" */
drhad3cab52002-05-24 02:04:32 +00001606 SrcList *pSrc; /* The FROM clause of the outer query */
1607 SrcList *pSubSrc; /* The FROM clause of the subquery */
drh0bb28102002-05-08 11:54:14 +00001608 ExprList *pList; /* The result set of the outer query */
drh832508b2002-03-02 17:04:07 +00001609 int i;
1610 int iParent, iSub;
1611 Expr *pWhere;
drh1350b032002-02-27 19:00:20 +00001612
drh832508b2002-03-02 17:04:07 +00001613 /* Check to see if flattening is permitted. Return 0 if not.
1614 */
1615 if( p==0 ) return 0;
1616 pSrc = p->pSrc;
drhad3cab52002-05-24 02:04:32 +00001617 assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
drh832508b2002-03-02 17:04:07 +00001618 pSub = pSrc->a[iFrom].pSelect;
1619 assert( pSub!=0 );
1620 if( isAgg && subqueryIsAgg ) return 0;
drhad3cab52002-05-24 02:04:32 +00001621 if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
drh832508b2002-03-02 17:04:07 +00001622 pSubSrc = pSub->pSrc;
1623 assert( pSubSrc );
drhad3cab52002-05-24 02:04:32 +00001624 if( pSubSrc->nSrc!=1 ) return 0;
drhdf199a22002-06-14 22:38:41 +00001625 if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){
1626 return 0;
1627 }
drhd11d3822002-06-21 23:01:49 +00001628 if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
drh174b6192002-12-03 02:22:52 +00001629 if( p->pOrderBy && pSub->pOrderBy ) return 0;
drh832508b2002-03-02 17:04:07 +00001630
drh0bb28102002-05-08 11:54:14 +00001631 /* If we reach this point, it means flattening is permitted for the
drh832508b2002-03-02 17:04:07 +00001632 ** i-th entry of the FROM clause in the outer query.
1633 */
1634 iParent = p->base + iFrom;
1635 iSub = pSub->base;
1636 substExprList(p->pEList, iParent, pSub->pEList, iSub);
1637 pList = p->pEList;
1638 for(i=0; i<pList->nExpr; i++){
drh6977fea2002-10-22 23:38:04 +00001639 Expr *pExpr;
1640 if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
1641 pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
drh832508b2002-03-02 17:04:07 +00001642 }
1643 }
drh1b2e0322002-03-03 02:49:51 +00001644 if( isAgg ){
1645 substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
1646 substExpr(p->pHaving, iParent, pSub->pEList, iSub);
1647 }
drh174b6192002-12-03 02:22:52 +00001648 if( pSub->pOrderBy ){
1649 assert( p->pOrderBy==0 );
1650 p->pOrderBy = pSub->pOrderBy;
1651 pSub->pOrderBy = 0;
1652 changeTablesInList(p->pOrderBy, iSub, iParent);
1653 }else if( p->pOrderBy ){
1654 substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
1655 }
drh832508b2002-03-02 17:04:07 +00001656 if( pSub->pWhere ){
1657 pWhere = sqliteExprDup(pSub->pWhere);
1658 if( iParent!=iSub ){
1659 changeTables(pWhere, iSub, iParent);
1660 }
1661 }else{
1662 pWhere = 0;
1663 }
1664 if( subqueryIsAgg ){
1665 assert( p->pHaving==0 );
drh1b2e0322002-03-03 02:49:51 +00001666 p->pHaving = p->pWhere;
1667 p->pWhere = pWhere;
drh832508b2002-03-02 17:04:07 +00001668 substExpr(p->pHaving, iParent, pSub->pEList, iSub);
drh1b2e0322002-03-03 02:49:51 +00001669 if( pSub->pHaving ){
1670 Expr *pHaving = sqliteExprDup(pSub->pHaving);
1671 if( iParent!=iSub ){
1672 changeTables(pHaving, iSub, iParent);
1673 }
1674 if( p->pHaving ){
1675 p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
1676 }else{
1677 p->pHaving = pHaving;
1678 }
1679 }
1680 assert( p->pGroupBy==0 );
1681 p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
1682 if( iParent!=iSub ){
1683 changeTablesInList(p->pGroupBy, iSub, iParent);
1684 }
drh832508b2002-03-02 17:04:07 +00001685 }else if( p->pWhere==0 ){
1686 p->pWhere = pWhere;
1687 }else{
1688 substExpr(p->pWhere, iParent, pSub->pEList, iSub);
1689 if( pWhere ){
1690 p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
1691 }
1692 }
1693 p->isDistinct = p->isDistinct || pSub->isDistinct;
drh8c74a8c2002-08-25 19:20:40 +00001694
drhdf199a22002-06-14 22:38:41 +00001695 if( pSub->nLimit>=0 ){
1696 if( p->nLimit<0 ){
1697 p->nLimit = pSub->nLimit;
1698 }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
1699 p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
1700 }
1701 }
1702 p->nOffset += pSub->nOffset;
drh8c74a8c2002-08-25 19:20:40 +00001703
1704 /* If the subquery contains subqueries of its own, that were not
1705 ** flattened, then code will have already been generated to put
1706 ** the results of those sub-subqueries into VDBE cursors relative
1707 ** to the subquery. We must translate the cursor number into values
1708 ** suitable for use by the outer query.
1709 */
1710 for(i=0; i<pSubSrc->nSrc; i++){
1711 Vdbe *v;
1712 if( pSubSrc->a[i].pSelect==0 ) continue;
1713 v = sqliteGetVdbe(pParse);
1714 sqliteVdbeAddOp(v, OP_RenameCursor, pSub->base+i, p->base+i);
1715 }
1716
drh832508b2002-03-02 17:04:07 +00001717 if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
1718 sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
1719 }
1720 pSrc->a[iFrom].pTab = pSubSrc->a[0].pTab;
1721 pSubSrc->a[0].pTab = 0;
drhd94a6692002-08-25 18:29:11 +00001722 assert( pSrc->a[iFrom].pSelect==pSub );
drh832508b2002-03-02 17:04:07 +00001723 pSrc->a[iFrom].pSelect = pSubSrc->a[0].pSelect;
1724 pSubSrc->a[0].pSelect = 0;
1725 sqliteSelectDelete(pSub);
1726 return 1;
1727}
drh1350b032002-02-27 19:00:20 +00001728
1729/*
drh9562b552002-02-19 15:00:07 +00001730** Analyze the SELECT statement passed in as an argument to see if it
1731** is a simple min() or max() query. If it is and this query can be
1732** satisfied using a single seek to the beginning or end of an index,
drhe78e8282003-01-19 03:59:45 +00001733** then generate the code for this SELECT and return 1. If this is not a
drh9562b552002-02-19 15:00:07 +00001734** simple min() or max() query, then return 0;
1735**
1736** A simply min() or max() query looks like this:
1737**
1738** SELECT min(a) FROM table;
1739** SELECT max(a) FROM table;
1740**
1741** The query may have only a single table in its FROM argument. There
1742** can be no GROUP BY or HAVING or WHERE clauses. The result set must
1743** be the min() or max() of a single column of the table. The column
1744** in the min() or max() function must be indexed.
1745**
1746** The parameters to this routine are the same as for sqliteSelect().
1747** See the header comment on that routine for additional information.
1748*/
1749static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){
1750 Expr *pExpr;
1751 int iCol;
1752 Table *pTab;
1753 Index *pIdx;
1754 int base;
1755 Vdbe *v;
drh9562b552002-02-19 15:00:07 +00001756 int seekOp;
1757 int cont;
1758 ExprList eList;
1759 struct ExprList_item eListItem;
1760
1761 /* Check to see if this query is a simple min() or max() query. Return
1762 ** zero if it is not.
1763 */
1764 if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
drhad3cab52002-05-24 02:04:32 +00001765 if( p->pSrc->nSrc!=1 ) return 0;
drh9562b552002-02-19 15:00:07 +00001766 if( p->pEList->nExpr!=1 ) return 0;
1767 pExpr = p->pEList->a[0].pExpr;
1768 if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
1769 if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0;
drh6977fea2002-10-22 23:38:04 +00001770 if( pExpr->token.n!=3 ) return 0;
drh0bce8352002-02-28 00:41:10 +00001771 if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
1772 seekOp = OP_Rewind;
1773 }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
1774 seekOp = OP_Last;
1775 }else{
1776 return 0;
1777 }
drh9562b552002-02-19 15:00:07 +00001778 pExpr = pExpr->pList->a[0].pExpr;
1779 if( pExpr->op!=TK_COLUMN ) return 0;
1780 iCol = pExpr->iColumn;
1781 pTab = p->pSrc->a[0].pTab;
1782
1783 /* If we get to here, it means the query is of the correct form.
drh17f71932002-02-21 12:01:27 +00001784 ** Check to make sure we have an index and make pIdx point to the
1785 ** appropriate index. If the min() or max() is on an INTEGER PRIMARY
1786 ** key column, no index is necessary so set pIdx to NULL. If no
1787 ** usable index is found, return 0.
drh9562b552002-02-19 15:00:07 +00001788 */
1789 if( iCol<0 ){
1790 pIdx = 0;
1791 }else{
1792 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
1793 assert( pIdx->nColumn>=1 );
1794 if( pIdx->aiColumn[0]==iCol ) break;
1795 }
1796 if( pIdx==0 ) return 0;
1797 }
1798
drh17f71932002-02-21 12:01:27 +00001799 /* Identify column names if we will be using the callback. This
drh9562b552002-02-19 15:00:07 +00001800 ** step is skipped if the output is going to a table or a memory cell.
1801 */
1802 v = sqliteGetVdbe(pParse);
1803 if( v==0 ) return 0;
1804 if( eDest==SRT_Callback ){
drh832508b2002-03-02 17:04:07 +00001805 generateColumnNames(pParse, p->base, p->pSrc, p->pEList);
drhfcb78a42003-01-18 20:11:05 +00001806 generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
drh9562b552002-02-19 15:00:07 +00001807 }
1808
drh17f71932002-02-21 12:01:27 +00001809 /* Generating code to find the min or the max. Basically all we have
1810 ** to do is find the first or the last entry in the chosen index. If
1811 ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
1812 ** or last entry in the main table.
drh9562b552002-02-19 15:00:07 +00001813 */
drh5cf8e8c2002-02-19 22:42:05 +00001814 if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
drh001bbcb2003-03-19 03:14:00 +00001815 sqliteCodeVerifySchema(pParse);
drh5cf8e8c2002-02-19 22:42:05 +00001816 }
drh832508b2002-03-02 17:04:07 +00001817 base = p->base;
drhd24cc422003-03-27 12:51:24 +00001818 sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
drh001bbcb2003-03-19 03:14:00 +00001819 sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
drh5cf8e8c2002-02-19 22:42:05 +00001820 sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
drhd4d595f2003-04-17 12:44:23 +00001821 cont = sqliteVdbeMakeLabel(v);
drh9562b552002-02-19 15:00:07 +00001822 if( pIdx==0 ){
1823 sqliteVdbeAddOp(v, seekOp, base, 0);
1824 }else{
drhd24cc422003-03-27 12:51:24 +00001825 sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
drh001bbcb2003-03-19 03:14:00 +00001826 sqliteVdbeAddOp(v, OP_OpenRead, base+1, pIdx->tnum);
drh5cf8e8c2002-02-19 22:42:05 +00001827 sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
drh9562b552002-02-19 15:00:07 +00001828 sqliteVdbeAddOp(v, seekOp, base+1, 0);
1829 sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
1830 sqliteVdbeAddOp(v, OP_Close, base+1, 0);
1831 sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
1832 }
drh5cf8e8c2002-02-19 22:42:05 +00001833 eList.nExpr = 1;
1834 memset(&eListItem, 0, sizeof(eListItem));
1835 eList.a = &eListItem;
1836 eList.a[0].pExpr = pExpr;
drh38640e12002-07-05 21:42:36 +00001837 selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont);
drh9562b552002-02-19 15:00:07 +00001838 sqliteVdbeResolveLabel(v, cont);
1839 sqliteVdbeAddOp(v, OP_Close, base, 0);
1840 return 1;
1841}
1842
1843/*
drh9bb61fe2000-06-05 16:01:39 +00001844** Generate code for the given SELECT statement.
1845**
drhfef52082000-06-06 01:50:43 +00001846** The results are distributed in various ways depending on the
1847** value of eDest and iParm.
1848**
1849** eDest Value Result
1850** ------------ -------------------------------------------
1851** SRT_Callback Invoke the callback for each row of the result.
1852**
1853** SRT_Mem Store first result in memory cell iParm
1854**
1855** SRT_Set Store results as keys of a table with cursor iParm
1856**
drh82c3d632000-06-06 21:56:07 +00001857** SRT_Union Store results as a key in a temporary table iParm
1858**
drhc4a3c772001-04-04 11:48:57 +00001859** SRT_Except Remove results form the temporary table iParm.
1860**
1861** SRT_Table Store results in temporary table iParm
drh9bb61fe2000-06-05 16:01:39 +00001862**
drhe78e8282003-01-19 03:59:45 +00001863** The table above is incomplete. Additional eDist value have be added
1864** since this comment was written. See the selectInnerLoop() function for
1865** a complete listing of the allowed values of eDest and their meanings.
1866**
drh9bb61fe2000-06-05 16:01:39 +00001867** This routine returns the number of errors. If any errors are
1868** encountered, then an appropriate error message is left in
1869** pParse->zErrMsg.
1870**
1871** This routine does NOT free the Select structure passed in. The
1872** calling function needs to do that.
drh1b2e0322002-03-03 02:49:51 +00001873**
1874** The pParent, parentTab, and *pParentAgg fields are filled in if this
1875** SELECT is a subquery. This routine may try to combine this SELECT
1876** with its parent to form a single flat query. In so doing, it might
1877** change the parent query from a non-aggregate to an aggregate query.
1878** For that reason, the pParentAgg flag is passed as a pointer, so it
1879** can be changed.
drhe78e8282003-01-19 03:59:45 +00001880**
1881** Example 1: The meaning of the pParent parameter.
1882**
1883** SELECT * FROM t1 JOIN (SELECT x, count(*) FROM t2) JOIN t3;
1884** \ \_______ subquery _______/ /
1885** \ /
1886** \____________________ outer query ___________________/
1887**
1888** This routine is called for the outer query first. For that call,
1889** pParent will be NULL. During the processing of the outer query, this
1890** routine is called recursively to handle the subquery. For the recursive
1891** call, pParent will point to the outer query. Because the subquery is
1892** the second element in a three-way join, the parentTab parameter will
1893** be 1 (the 2nd value of a 0-indexed array.)
drh9bb61fe2000-06-05 16:01:39 +00001894*/
1895int sqliteSelect(
drhcce7d172000-05-31 15:34:51 +00001896 Parse *pParse, /* The parser context */
drh9bb61fe2000-06-05 16:01:39 +00001897 Select *p, /* The SELECT statement being coded. */
drhe78e8282003-01-19 03:59:45 +00001898 int eDest, /* How to dispose of the results */
1899 int iParm, /* A parameter used by the eDest disposal method */
drh832508b2002-03-02 17:04:07 +00001900 Select *pParent, /* Another SELECT for which this is a sub-query */
1901 int parentTab, /* Index in pParent->pSrc of this query */
drh1b2e0322002-03-03 02:49:51 +00001902 int *pParentAgg /* True if pParent uses aggregate functions */
drhcce7d172000-05-31 15:34:51 +00001903){
drhd8bc7082000-06-07 23:51:50 +00001904 int i;
drhcce7d172000-05-31 15:34:51 +00001905 WhereInfo *pWInfo;
1906 Vdbe *v;
1907 int isAgg = 0; /* True for select lists like "count(*)" */
drha2e00042002-01-22 03:13:42 +00001908 ExprList *pEList; /* List of columns to extract. */
drhad3cab52002-05-24 02:04:32 +00001909 SrcList *pTabList; /* List of tables to select from */
drh9bb61fe2000-06-05 16:01:39 +00001910 Expr *pWhere; /* The WHERE clause. May be NULL */
1911 ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */
drh22827922000-06-06 17:27:05 +00001912 ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */
1913 Expr *pHaving; /* The HAVING clause. May be NULL */
drh19a775c2000-06-05 18:54:46 +00001914 int isDistinct; /* True if the DISTINCT keyword is present */
1915 int distinct; /* Table to use for the distinct set */
drh10e5e3c2000-06-08 00:19:02 +00001916 int base; /* First cursor available for use */
drh1d83f052002-02-17 00:30:36 +00001917 int rc = 1; /* Value to return from this function */
drh9bb61fe2000-06-05 16:01:39 +00001918
drhdaffd0e2001-04-11 14:28:42 +00001919 if( sqlite_malloc_failed || pParse->nErr || p==0 ) return 1;
drhe5f9c642003-01-13 23:27:31 +00001920 if( sqliteAuthCheck(pParse, SQLITE_SELECT, 0, 0) ) return 1;
drhdaffd0e2001-04-11 14:28:42 +00001921
drh82c3d632000-06-06 21:56:07 +00001922 /* If there is are a sequence of queries, do the earlier ones first.
1923 */
1924 if( p->pPrior ){
1925 return multiSelect(pParse, p, eDest, iParm);
1926 }
1927
1928 /* Make local copies of the parameters for this query.
1929 */
drh9bb61fe2000-06-05 16:01:39 +00001930 pTabList = p->pSrc;
1931 pWhere = p->pWhere;
1932 pOrderBy = p->pOrderBy;
drh22827922000-06-06 17:27:05 +00001933 pGroupBy = p->pGroupBy;
1934 pHaving = p->pHaving;
drh19a775c2000-06-05 18:54:46 +00001935 isDistinct = p->isDistinct;
drh9bb61fe2000-06-05 16:01:39 +00001936
drh832508b2002-03-02 17:04:07 +00001937 /* Allocate a block of VDBE cursors, one for each table in the FROM clause.
1938 ** The WHERE processing requires that the cursors for the tables in the
1939 ** FROM clause be consecutive.
drh10e5e3c2000-06-08 00:19:02 +00001940 */
drh832508b2002-03-02 17:04:07 +00001941 base = p->base = pParse->nTab;
drhad3cab52002-05-24 02:04:32 +00001942 pParse->nTab += pTabList->nSrc;
drh10e5e3c2000-06-08 00:19:02 +00001943
drh9bb61fe2000-06-05 16:01:39 +00001944 /*
1945 ** Do not even attempt to generate any code if we have already seen
1946 ** errors before this routine starts.
1947 */
drh1d83f052002-02-17 00:30:36 +00001948 if( pParse->nErr>0 ) goto select_end;
drhcce7d172000-05-31 15:34:51 +00001949
drhe78e8282003-01-19 03:59:45 +00001950 /* Expand any "*" terms in the result set. (For example the "*" in
1951 ** "SELECT * FROM t1") The fillInColumnlist() routine also does some
1952 ** other housekeeping - see the header comment for details.
drhcce7d172000-05-31 15:34:51 +00001953 */
drhd8bc7082000-06-07 23:51:50 +00001954 if( fillInColumnList(pParse, p) ){
drh1d83f052002-02-17 00:30:36 +00001955 goto select_end;
drhcce7d172000-05-31 15:34:51 +00001956 }
drhad2d8302002-05-24 20:31:36 +00001957 pWhere = p->pWhere;
drhd8bc7082000-06-07 23:51:50 +00001958 pEList = p->pEList;
drh1d83f052002-02-17 00:30:36 +00001959 if( pEList==0 ) goto select_end;
drhcce7d172000-05-31 15:34:51 +00001960
drh22827922000-06-06 17:27:05 +00001961 /* If writing to memory or generating a set
1962 ** only a single column may be output.
drh19a775c2000-06-05 18:54:46 +00001963 */
drhfef52082000-06-06 01:50:43 +00001964 if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
drhda93d232003-03-31 02:12:46 +00001965 sqliteErrorMsg(pParse, "only a single result allowed for "
1966 "a SELECT that is part of an expression");
drh1d83f052002-02-17 00:30:36 +00001967 goto select_end;
drh19a775c2000-06-05 18:54:46 +00001968 }
1969
drhc926afb2002-06-20 03:38:26 +00001970 /* ORDER BY is ignored for some destinations.
drh22827922000-06-06 17:27:05 +00001971 */
drhc926afb2002-06-20 03:38:26 +00001972 switch( eDest ){
1973 case SRT_Union:
1974 case SRT_Except:
1975 case SRT_Discard:
1976 pOrderBy = 0;
1977 break;
1978 default:
1979 break;
drh22827922000-06-06 17:27:05 +00001980 }
1981
drh10e5e3c2000-06-08 00:19:02 +00001982 /* At this point, we should have allocated all the cursors that we
drh832508b2002-03-02 17:04:07 +00001983 ** need to handle subquerys and temporary tables.
drh10e5e3c2000-06-08 00:19:02 +00001984 **
drh967e8b72000-06-21 13:59:10 +00001985 ** Resolve the column names and do a semantics check on all the expressions.
drh22827922000-06-06 17:27:05 +00001986 */
drh4794b982000-06-06 13:54:14 +00001987 for(i=0; i<pEList->nExpr; i++){
drh832508b2002-03-02 17:04:07 +00001988 if( sqliteExprResolveIds(pParse, base, pTabList, 0, pEList->a[i].pExpr) ){
drh1d83f052002-02-17 00:30:36 +00001989 goto select_end;
drhcce7d172000-05-31 15:34:51 +00001990 }
drh22827922000-06-06 17:27:05 +00001991 if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
drh1d83f052002-02-17 00:30:36 +00001992 goto select_end;
drhcce7d172000-05-31 15:34:51 +00001993 }
1994 }
drhcce7d172000-05-31 15:34:51 +00001995 if( pWhere ){
drh832508b2002-03-02 17:04:07 +00001996 if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pWhere) ){
drh1d83f052002-02-17 00:30:36 +00001997 goto select_end;
drhcce7d172000-05-31 15:34:51 +00001998 }
1999 if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
drh1d83f052002-02-17 00:30:36 +00002000 goto select_end;
drhcce7d172000-05-31 15:34:51 +00002001 }
drh1f162302002-10-27 19:35:33 +00002002 sqliteOracle8JoinFixup(base, pTabList, pWhere);
drhcce7d172000-05-31 15:34:51 +00002003 }
drhc66c5a22002-12-03 02:34:49 +00002004 if( pHaving ){
2005 if( pGroupBy==0 ){
drhda93d232003-03-31 02:12:46 +00002006 sqliteErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
drhc66c5a22002-12-03 02:34:49 +00002007 goto select_end;
2008 }
2009 if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pHaving) ){
2010 goto select_end;
2011 }
2012 if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
2013 goto select_end;
2014 }
2015 }
drhcce7d172000-05-31 15:34:51 +00002016 if( pOrderBy ){
2017 for(i=0; i<pOrderBy->nExpr; i++){
drh88eee382003-01-31 17:16:36 +00002018 int iCol;
drh22827922000-06-06 17:27:05 +00002019 Expr *pE = pOrderBy->a[i].pExpr;
drh88eee382003-01-31 17:16:36 +00002020 if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
2021 sqliteExprDelete(pE);
2022 pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
2023 }
2024 if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
2025 goto select_end;
2026 }
2027 if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
2028 goto select_end;
2029 }
drh92086432002-01-22 14:11:29 +00002030 if( sqliteExprIsConstant(pE) ){
drhe4de1fe2002-06-02 16:09:01 +00002031 if( sqliteExprIsInteger(pE, &iCol)==0 ){
drhda93d232003-03-31 02:12:46 +00002032 sqliteErrorMsg(pParse,
2033 "ORDER BY terms must not be non-integer constants");
drhe4de1fe2002-06-02 16:09:01 +00002034 goto select_end;
2035 }else if( iCol<=0 || iCol>pEList->nExpr ){
drhda93d232003-03-31 02:12:46 +00002036 sqliteErrorMsg(pParse,
2037 "ORDER BY column number %d out of range - should be "
drhe4de1fe2002-06-02 16:09:01 +00002038 "between 1 and %d", iCol, pEList->nExpr);
drhe4de1fe2002-06-02 16:09:01 +00002039 goto select_end;
2040 }
drhcce7d172000-05-31 15:34:51 +00002041 }
2042 }
2043 }
drh22827922000-06-06 17:27:05 +00002044 if( pGroupBy ){
2045 for(i=0; i<pGroupBy->nExpr; i++){
drh88eee382003-01-31 17:16:36 +00002046 int iCol;
drh22827922000-06-06 17:27:05 +00002047 Expr *pE = pGroupBy->a[i].pExpr;
drh88eee382003-01-31 17:16:36 +00002048 if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
2049 sqliteExprDelete(pE);
2050 pE = pGroupBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
drh92086432002-01-22 14:11:29 +00002051 }
drh832508b2002-03-02 17:04:07 +00002052 if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
drh1d83f052002-02-17 00:30:36 +00002053 goto select_end;
drh22827922000-06-06 17:27:05 +00002054 }
2055 if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
drh1d83f052002-02-17 00:30:36 +00002056 goto select_end;
drh22827922000-06-06 17:27:05 +00002057 }
drh88eee382003-01-31 17:16:36 +00002058 if( sqliteExprIsConstant(pE) ){
2059 if( sqliteExprIsInteger(pE, &iCol)==0 ){
drhda93d232003-03-31 02:12:46 +00002060 sqliteErrorMsg(pParse,
2061 "GROUP BY terms must not be non-integer constants");
drh88eee382003-01-31 17:16:36 +00002062 goto select_end;
2063 }else if( iCol<=0 || iCol>pEList->nExpr ){
drhda93d232003-03-31 02:12:46 +00002064 sqliteErrorMsg(pParse,
2065 "GROUP BY column number %d out of range - should be "
drh88eee382003-01-31 17:16:36 +00002066 "between 1 and %d", iCol, pEList->nExpr);
drh88eee382003-01-31 17:16:36 +00002067 goto select_end;
2068 }
2069 }
drh22827922000-06-06 17:27:05 +00002070 }
2071 }
drhcce7d172000-05-31 15:34:51 +00002072
drh9562b552002-02-19 15:00:07 +00002073 /* Check for the special case of a min() or max() function by itself
2074 ** in the result set.
2075 */
2076 if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){
drh5cf8e8c2002-02-19 22:42:05 +00002077 rc = 0;
drh9562b552002-02-19 15:00:07 +00002078 goto select_end;
2079 }
2080
drhd820cb12002-02-18 03:21:45 +00002081 /* Begin generating code.
2082 */
2083 v = sqliteGetVdbe(pParse);
2084 if( v==0 ) goto select_end;
2085
drhe78e8282003-01-19 03:59:45 +00002086 /* Identify column names if we will be using them in a callback. This
2087 ** step is skipped if the output is going to some other destination.
drh0bb28102002-05-08 11:54:14 +00002088 */
2089 if( eDest==SRT_Callback ){
2090 generateColumnNames(pParse, p->base, pTabList, pEList);
2091 }
2092
2093 /* Set the limiter
2094 */
2095 if( p->nLimit<=0 ){
drhd11d3822002-06-21 23:01:49 +00002096 p->nLimit = -1;
drh0bb28102002-05-08 11:54:14 +00002097 p->nOffset = 0;
2098 }else{
drhd11d3822002-06-21 23:01:49 +00002099 int iMem = pParse->nMem++;
2100 sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
drhbf5cd972002-06-24 12:20:23 +00002101 sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
drhd11d3822002-06-21 23:01:49 +00002102 p->nLimit = iMem;
2103 if( p->nOffset<=0 ){
2104 p->nOffset = 0;
2105 }else{
2106 iMem = pParse->nMem++;
2107 sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
drhbf5cd972002-06-24 12:20:23 +00002108 sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
drhd11d3822002-06-21 23:01:49 +00002109 p->nOffset = iMem;
2110 }
drh0bb28102002-05-08 11:54:14 +00002111 }
2112
drhd820cb12002-02-18 03:21:45 +00002113 /* Generate code for all sub-queries in the FROM clause
2114 */
drhad3cab52002-05-24 02:04:32 +00002115 for(i=0; i<pTabList->nSrc; i++){
drha76b5df2002-02-23 02:32:10 +00002116 if( pTabList->a[i].pSelect==0 ) continue;
drh2d0794e2002-03-03 03:03:52 +00002117 sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, base+i,
drh1b2e0322002-03-03 02:49:51 +00002118 p, i, &isAgg);
2119 pTabList = p->pSrc;
2120 pWhere = p->pWhere;
drhacd4c692002-03-07 02:02:51 +00002121 if( eDest==SRT_Callback ){
2122 pOrderBy = p->pOrderBy;
2123 }
drh1b2e0322002-03-03 02:49:51 +00002124 pGroupBy = p->pGroupBy;
2125 pHaving = p->pHaving;
2126 isDistinct = p->isDistinct;
drhd820cb12002-02-18 03:21:45 +00002127 }
2128
drh832508b2002-03-02 17:04:07 +00002129 /* Check to see if this is a subquery that can be "flattened" into its parent.
2130 ** If flattening is a possiblity, do so and return immediately.
2131 */
drh1b2e0322002-03-03 02:49:51 +00002132 if( pParent && pParentAgg &&
drh8c74a8c2002-08-25 19:20:40 +00002133 flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
drh1b2e0322002-03-03 02:49:51 +00002134 if( isAgg ) *pParentAgg = 1;
drh832508b2002-03-02 17:04:07 +00002135 return rc;
2136 }
drh832508b2002-03-02 17:04:07 +00002137
drhe78e8282003-01-19 03:59:45 +00002138 /* Identify column types if we will be using a callback. This
2139 ** step is skipped if the output is going to a destination other
2140 ** than a callback.
drhfcb78a42003-01-18 20:11:05 +00002141 */
2142 if( eDest==SRT_Callback ){
2143 generateColumnTypes(pParse, p->base, pTabList, pEList);
2144 }
2145
drh2d0794e2002-03-03 03:03:52 +00002146 /* If the output is destined for a temporary table, open that table.
2147 */
2148 if( eDest==SRT_TempTable ){
2149 sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
2150 }
2151
drh22827922000-06-06 17:27:05 +00002152 /* Do an analysis of aggregate expressions.
drhefb72512000-05-31 20:00:52 +00002153 */
drhd820cb12002-02-18 03:21:45 +00002154 sqliteAggregateInfoReset(pParse);
drhbb999ef2003-02-02 12:41:25 +00002155 if( isAgg || pGroupBy ){
drh0bce8352002-02-28 00:41:10 +00002156 assert( pParse->nAgg==0 );
drhbb999ef2003-02-02 12:41:25 +00002157 isAgg = 1;
drh22827922000-06-06 17:27:05 +00002158 for(i=0; i<pEList->nExpr; i++){
2159 if( sqliteExprAnalyzeAggregates(pParse, pEList->a[i].pExpr) ){
drh1d83f052002-02-17 00:30:36 +00002160 goto select_end;
drh22827922000-06-06 17:27:05 +00002161 }
2162 }
2163 if( pGroupBy ){
2164 for(i=0; i<pGroupBy->nExpr; i++){
2165 if( sqliteExprAnalyzeAggregates(pParse, pGroupBy->a[i].pExpr) ){
drh1d83f052002-02-17 00:30:36 +00002166 goto select_end;
drh22827922000-06-06 17:27:05 +00002167 }
2168 }
2169 }
2170 if( pHaving && sqliteExprAnalyzeAggregates(pParse, pHaving) ){
drh1d83f052002-02-17 00:30:36 +00002171 goto select_end;
drh22827922000-06-06 17:27:05 +00002172 }
drh191b6902000-06-08 11:13:01 +00002173 if( pOrderBy ){
2174 for(i=0; i<pOrderBy->nExpr; i++){
2175 if( sqliteExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
drh1d83f052002-02-17 00:30:36 +00002176 goto select_end;
drh191b6902000-06-08 11:13:01 +00002177 }
2178 }
2179 }
drhefb72512000-05-31 20:00:52 +00002180 }
2181
drh22827922000-06-06 17:27:05 +00002182 /* Reset the aggregator
drhcce7d172000-05-31 15:34:51 +00002183 */
2184 if( isAgg ){
drh99fcd712001-10-13 01:06:47 +00002185 sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg);
drhe5095352002-02-24 03:25:14 +00002186 for(i=0; i<pParse->nAgg; i++){
drh0bce8352002-02-28 00:41:10 +00002187 FuncDef *pFunc;
2188 if( (pFunc = pParse->aAgg[i].pFunc)!=0 && pFunc->xFinalize!=0 ){
drh1350b032002-02-27 19:00:20 +00002189 sqliteVdbeAddOp(v, OP_AggInit, 0, i);
drh0bce8352002-02-28 00:41:10 +00002190 sqliteVdbeChangeP3(v, -1, (char*)pFunc, P3_POINTER);
drhe5095352002-02-24 03:25:14 +00002191 }
2192 }
drh1bee3d72001-10-15 00:44:35 +00002193 if( pGroupBy==0 ){
2194 sqliteVdbeAddOp(v, OP_String, 0, 0);
drh1bee3d72001-10-15 00:44:35 +00002195 sqliteVdbeAddOp(v, OP_AggFocus, 0, 0);
drh1bee3d72001-10-15 00:44:35 +00002196 }
drhcce7d172000-05-31 15:34:51 +00002197 }
2198
drh19a775c2000-06-05 18:54:46 +00002199 /* Initialize the memory cell to NULL
2200 */
drhfef52082000-06-06 01:50:43 +00002201 if( eDest==SRT_Mem ){
drh99fcd712001-10-13 01:06:47 +00002202 sqliteVdbeAddOp(v, OP_String, 0, 0);
drh8721ce42001-11-07 14:22:00 +00002203 sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
drh19a775c2000-06-05 18:54:46 +00002204 }
2205
drh832508b2002-03-02 17:04:07 +00002206 /* Open a temporary table to use for the distinct set.
drhefb72512000-05-31 20:00:52 +00002207 */
drh19a775c2000-06-05 18:54:46 +00002208 if( isDistinct ){
drh832508b2002-03-02 17:04:07 +00002209 distinct = pParse->nTab++;
drhc6b52df2002-01-04 03:09:29 +00002210 sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
drh832508b2002-03-02 17:04:07 +00002211 }else{
2212 distinct = -1;
drhefb72512000-05-31 20:00:52 +00002213 }
drh832508b2002-03-02 17:04:07 +00002214
2215 /* Begin the database scan
2216 */
drh68d2e592002-08-04 00:52:38 +00002217 pWInfo = sqliteWhereBegin(pParse, p->base, pTabList, pWhere, 0,
2218 pGroupBy ? 0 : &pOrderBy);
drh1d83f052002-02-17 00:30:36 +00002219 if( pWInfo==0 ) goto select_end;
drhcce7d172000-05-31 15:34:51 +00002220
drh22827922000-06-06 17:27:05 +00002221 /* Use the standard inner loop if we are not dealing with
2222 ** aggregates
drhcce7d172000-05-31 15:34:51 +00002223 */
drhda9d6c42000-05-31 18:20:14 +00002224 if( !isAgg ){
drhdf199a22002-06-14 22:38:41 +00002225 if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2226 iParm, pWInfo->iContinue, pWInfo->iBreak) ){
drh1d83f052002-02-17 00:30:36 +00002227 goto select_end;
drhda9d6c42000-05-31 18:20:14 +00002228 }
drhcce7d172000-05-31 15:34:51 +00002229 }
drhefb72512000-05-31 20:00:52 +00002230
drhe3184742002-06-19 14:27:05 +00002231 /* If we are dealing with aggregates, then do the special aggregate
drh22827922000-06-06 17:27:05 +00002232 ** processing.
drhefb72512000-05-31 20:00:52 +00002233 */
drh22827922000-06-06 17:27:05 +00002234 else{
drh22827922000-06-06 17:27:05 +00002235 if( pGroupBy ){
drh1bee3d72001-10-15 00:44:35 +00002236 int lbl1;
drh22827922000-06-06 17:27:05 +00002237 for(i=0; i<pGroupBy->nExpr; i++){
2238 sqliteExprCode(pParse, pGroupBy->a[i].pExpr);
2239 }
drh99fcd712001-10-13 01:06:47 +00002240 sqliteVdbeAddOp(v, OP_MakeKey, pGroupBy->nExpr, 0);
drh491791a2002-07-18 00:34:09 +00002241 if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pGroupBy);
drh1bee3d72001-10-15 00:44:35 +00002242 lbl1 = sqliteVdbeMakeLabel(v);
drh99fcd712001-10-13 01:06:47 +00002243 sqliteVdbeAddOp(v, OP_AggFocus, 0, lbl1);
drh22827922000-06-06 17:27:05 +00002244 for(i=0; i<pParse->nAgg; i++){
2245 if( pParse->aAgg[i].isAgg ) continue;
2246 sqliteExprCode(pParse, pParse->aAgg[i].pExpr);
drh99fcd712001-10-13 01:06:47 +00002247 sqliteVdbeAddOp(v, OP_AggSet, 0, i);
drhcce7d172000-05-31 15:34:51 +00002248 }
drh22827922000-06-06 17:27:05 +00002249 sqliteVdbeResolveLabel(v, lbl1);
drhcce7d172000-05-31 15:34:51 +00002250 }
drh22827922000-06-06 17:27:05 +00002251 for(i=0; i<pParse->nAgg; i++){
2252 Expr *pE;
drh0bce8352002-02-28 00:41:10 +00002253 int j;
drh22827922000-06-06 17:27:05 +00002254 if( !pParse->aAgg[i].isAgg ) continue;
2255 pE = pParse->aAgg[i].pExpr;
drh22827922000-06-06 17:27:05 +00002256 assert( pE->op==TK_AGG_FUNCTION );
drh0bce8352002-02-28 00:41:10 +00002257 if( pE->pList ){
2258 for(j=0; j<pE->pList->nExpr; j++){
2259 sqliteExprCode(pParse, pE->pList->a[j].pExpr);
2260 }
drhe5095352002-02-24 03:25:14 +00002261 }
drh0bce8352002-02-28 00:41:10 +00002262 sqliteVdbeAddOp(v, OP_Integer, i, 0);
drhf55f25f2002-02-28 01:46:11 +00002263 sqliteVdbeAddOp(v, OP_AggFunc, 0, pE->pList ? pE->pList->nExpr : 0);
drh0bce8352002-02-28 00:41:10 +00002264 assert( pParse->aAgg[i].pFunc!=0 );
2265 assert( pParse->aAgg[i].pFunc->xStep!=0 );
2266 sqliteVdbeChangeP3(v, -1, (char*)pParse->aAgg[i].pFunc, P3_POINTER);
drh22827922000-06-06 17:27:05 +00002267 }
drhcce7d172000-05-31 15:34:51 +00002268 }
2269
2270 /* End the database scan loop.
2271 */
2272 sqliteWhereEnd(pWInfo);
2273
drh22827922000-06-06 17:27:05 +00002274 /* If we are processing aggregates, we need to set up a second loop
2275 ** over all of the aggregate values and process them.
2276 */
2277 if( isAgg ){
2278 int endagg = sqliteVdbeMakeLabel(v);
2279 int startagg;
drh99fcd712001-10-13 01:06:47 +00002280 startagg = sqliteVdbeAddOp(v, OP_AggNext, 0, endagg);
drh22827922000-06-06 17:27:05 +00002281 pParse->useAgg = 1;
2282 if( pHaving ){
drhf5905aa2002-05-26 20:54:33 +00002283 sqliteExprIfFalse(pParse, pHaving, startagg, 1);
drh22827922000-06-06 17:27:05 +00002284 }
drhdf199a22002-06-14 22:38:41 +00002285 if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2286 iParm, startagg, endagg) ){
drh1d83f052002-02-17 00:30:36 +00002287 goto select_end;
drh22827922000-06-06 17:27:05 +00002288 }
drh99fcd712001-10-13 01:06:47 +00002289 sqliteVdbeAddOp(v, OP_Goto, 0, startagg);
2290 sqliteVdbeResolveLabel(v, endagg);
2291 sqliteVdbeAddOp(v, OP_Noop, 0, 0);
drh22827922000-06-06 17:27:05 +00002292 pParse->useAgg = 0;
2293 }
2294
drhcce7d172000-05-31 15:34:51 +00002295 /* If there is an ORDER BY clause, then we need to sort the results
2296 ** and send them to the callback one by one.
2297 */
2298 if( pOrderBy ){
drhc926afb2002-06-20 03:38:26 +00002299 generateSortTail(p, v, pEList->nExpr, eDest, iParm);
drhcce7d172000-05-31 15:34:51 +00002300 }
drh6a535342001-10-19 16:44:56 +00002301
2302
2303 /* Issue a null callback if that is what the user wants.
2304 */
drh326dce72003-01-29 14:06:07 +00002305 if( eDest==SRT_Callback &&
2306 (pParse->useCallback==0 || (pParse->db->flags & SQLITE_NullCallback)!=0)
2307 ){
drh6a535342001-10-19 16:44:56 +00002308 sqliteVdbeAddOp(v, OP_NullCallback, pEList->nExpr, 0);
2309 }
2310
drh1d83f052002-02-17 00:30:36 +00002311 /* The SELECT was successfully coded. Set the return code to 0
2312 ** to indicate no errors.
2313 */
2314 rc = 0;
2315
2316 /* Control jumps to here if an error is encountered above, or upon
2317 ** successful coding of the SELECT.
2318 */
2319select_end:
2320 sqliteAggregateInfoReset(pParse);
2321 return rc;
drhcce7d172000-05-31 15:34:51 +00002322}