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