Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -20,11 +20,11 @@ ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** -** $Id: build.c,v 1.546 2009/05/28 01:00:55 drh Exp $ +** $Id: build.c,v 1.547 2009/05/28 21:04:38 drh Exp $ */ #include "sqliteInt.h" /* ** This routine is called when a new SQL statement is beginning to @@ -1878,11 +1878,12 @@ ** is non-zero, then it is the root page number of a table moved to ** location iTable. The following code modifies the sqlite_master table to ** reflect this. ** ** The "#NNN" in the SQL is a special constant that means whatever value - ** is in register NNN. See sqlite3RegisterExpr(). + ** is in register NNN. See grammar rules associated with the TK_REGISTER + ** token for additional information. */ sqlite3NestedParse(pParse, "UPDATE %Q.%s SET rootpage=%d WHERE #%d AND rootpage=#%d", pParse->db->aDb[iDb].zName, SCHEMA_TABLE(iDb), iTable, r1, r1); #endif Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** -** $Id: expr.c,v 1.438 2009/05/28 01:00:55 drh Exp $ +** $Id: expr.c,v 1.439 2009/05/28 21:04:38 drh Exp $ */ #include "sqliteInt.h" /* ** Return the 'affinity' of the expression pExpr if any. @@ -1154,23 +1154,19 @@ /* Fall through */ case TK_ID: case TK_COLUMN: case TK_AGG_FUNCTION: case TK_AGG_COLUMN: -#ifndef SQLITE_OMIT_SUBQUERY - case TK_SELECT: - case TK_EXISTS: - testcase( pExpr->op==TK_SELECT ); - testcase( pExpr->op==TK_EXISTS ); -#endif testcase( pExpr->op==TK_ID ); testcase( pExpr->op==TK_COLUMN ); testcase( pExpr->op==TK_AGG_FUNCTION ); testcase( pExpr->op==TK_AGG_COLUMN ); pWalker->u.i = 0; return WRC_Abort; default: + testcase( pExpr->op==TK_SELECT ); /* selectNodeIsConstant will disallow */ + testcase( pExpr->op==TK_EXISTS ); /* selectNodeIsConstant will disallow */ return WRC_Continue; } } static int selectNodeIsConstant(Walker *pWalker, Select *NotUsed){ UNUSED_PARAMETER(NotUsed); @@ -1272,40 +1268,44 @@ if( sqlite3StrICmp(z, "OID")==0 ) return 1; return 0; } /* -** Return true if the IN operator optimization is enabled and -** the SELECT statement p exists and is of the -** simple form: +** Return true if we are able to the IN operator optimization on a +** query of the form ** -** SELECT FROM +** x IN (SELECT ...) ** -** If this is the case, it may be possible to use an existing table -** or index instead of generating an epheremal table. +** Where the SELECT... clause is as specified by the parameter to this +** routine. +** +** The Select object passed in has already been preprocessed and no +** errors have been found. */ #ifndef SQLITE_OMIT_SUBQUERY static int isCandidateForInOpt(Select *p){ SrcList *pSrc; ExprList *pEList; Table *pTab; if( p==0 ) return 0; /* right-hand side of IN is SELECT */ if( p->pPrior ) return 0; /* Not a compound SELECT */ if( p->selFlags & (SF_Distinct|SF_Aggregate) ){ - return 0; /* No DISTINCT keyword and no aggregate functions */ + testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ); + testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate ); + return 0; /* No DISTINCT keyword and no aggregate functions */ } - if( p->pGroupBy ) return 0; /* Has no GROUP BY clause */ + assert( p->pGroupBy==0 ); /* Has no GROUP BY clause */ if( p->pLimit ) return 0; /* Has no LIMIT clause */ - if( p->pOffset ) return 0; + assert( p->pOffset==0 ); /* No LIMIT means no OFFSET */ if( p->pWhere ) return 0; /* Has no WHERE clause */ pSrc = p->pSrc; assert( pSrc!=0 ); if( pSrc->nSrc!=1 ) return 0; /* Single term in FROM clause */ - if( pSrc->a[0].pSelect ) return 0; /* FROM clause is not a subquery */ + if( pSrc->a[0].pSelect ) return 0; /* FROM is not a subquery or view */ pTab = pSrc->a[0].pTab; - if( pTab==0 ) return 0; - if( pTab->pSelect ) return 0; /* FROM clause is not a view */ + if( NEVER(pTab==0) ) return 0; + assert( pTab->pSelect==0 ); /* FROM clause is not a view */ if( IsVirtual(pTab) ) return 0; /* FROM clause not a virtual table */ pEList = p->pEList; if( pEList->nExpr!=1 ) return 0; /* One column in the result set */ if( pEList->a[0].pExpr->op!=TK_COLUMN ) return 0; /* Result is a column */ return 1; @@ -1316,49 +1316,49 @@ ** This function is used by the implementation of the IN (...) operator. ** It's job is to find or create a b-tree structure that may be used ** either to test for membership of the (...) set or to iterate through ** its members, skipping duplicates. ** -** The cursor opened on the structure (database table, database index +** The index of the cursor opened on the b-tree (database table, database index ** or ephermal table) is stored in pX->iTable before this function returns. -** The returned value indicates the structure type, as follows: +** The returned value of this function indicates the b-tree type, as follows: ** ** IN_INDEX_ROWID - The cursor was opened on a database table. ** IN_INDEX_INDEX - The cursor was opened on a database index. ** IN_INDEX_EPH - The cursor was opened on a specially created and ** populated epheremal table. ** -** An existing structure may only be used if the SELECT is of the simple +** An existing b-tree may only be used if the SELECT is of the simple ** form: ** ** SELECT FROM
** -** If prNotFound parameter is 0, then the structure will be used to iterate +** If the prNotFound parameter is 0, then the b-tree will be used to iterate ** through the set members, skipping any duplicates. In this case an ** epheremal table must be used unless the selected is guaranteed ** to be unique - either because it is an INTEGER PRIMARY KEY or it -** is unique by virtue of a constraint or implicit index. +** has a UNIQUE constraint or UNIQUE index. ** -** If the prNotFound parameter is not 0, then the structure will be used +** If the prNotFound parameter is not 0, then the b-tree will be used ** for fast set membership tests. In this case an epheremal table must ** be used unless is an INTEGER PRIMARY KEY or an index can ** be found with as its left-most column. ** -** When the structure is being used for set membership tests, the user +** When the b-tree is being used for membership tests, the calling function ** needs to know whether or not the structure contains an SQL NULL ** value in order to correctly evaluate expressions like "X IN (Y, Z)". -** If there is a chance that the structure may contain a NULL value at +** If there is a chance that the b-tree might contain a NULL value at ** runtime, then a register is allocated and the register number written -** to *prNotFound. If there is no chance that the structure contains a +** to *prNotFound. If there is no chance that the b-tree contains a ** NULL value, then *prNotFound is left unchanged. ** ** If a register is allocated and its location stored in *prNotFound, then -** its initial value is NULL. If the structure does not remain constant -** for the duration of the query (i.e. the set is a correlated sub-select), -** the value of the allocated register is reset to NULL each time the -** structure is repopulated. This allows the caller to use vdbe code -** equivalent to the following: +** its initial value is NULL. If the b-tree does not remain constant +** for the duration of the query (i.e. the SELECT that generates the b-tree +** is a correlated subquery) then the value of the allocated register is +** reset to NULL each time the b-tree is repopulated. This allows the +** caller to use vdbe code equivalent to the following: ** ** if( register==NULL ){ ** has_null = ** register = 1 ** } @@ -1366,25 +1366,21 @@ ** in order to avoid running the ** test more often than is necessary. */ #ifndef SQLITE_OMIT_SUBQUERY int sqlite3FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){ - Select *p; - int eType = 0; - int iTab = pParse->nTab++; - int mustBeUnique = !prNotFound; + Select *p; /* SELECT to the right of IN operator */ + int eType = 0; /* Type of RHS table. IN_INDEX_* */ + int iTab = pParse->nTab++; /* Cursor of the RHS table */ + int mustBeUnique = (prNotFound==0); /* True if RHS must be unique */ - /* The follwing if(...) expression is true if the SELECT is of the - ** simple form: - ** - ** SELECT FROM
- ** - ** If this is the case, it may be possible to use an existing table - ** or index instead of generating an epheremal table. + /* Check to see if an existing table or index can be used to + ** satisfy the query. This is preferable to generating a new + ** ephemeral table. */ p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0); - if( isCandidateForInOpt(p) ){ + if( pParse->nErr==0 && isCandidateForInOpt(p) ){ sqlite3 *db = pParse->db; /* Database connection */ Expr *pExpr = p->pEList->a[0].pExpr; /* Expression */ int iCol = pExpr->iColumn; /* Index of column */ Vdbe *v = sqlite3GetVdbe(pParse); /* Virtual machine being coded */ Table *pTab = p->pSrc->a[0].pTab; /* Table
. */ @@ -1413,11 +1409,11 @@ sqlite3VdbeJumpHere(v, iAddr); }else{ Index *pIdx; /* Iterator variable */ - /* The collation sequence used by the comparison. If an index is to + /* The collation sequence used by the comparison. If an index is to ** be used in place of a temp-table, it must be ordered according ** to this collation sequence. */ CollSeq *pReq = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pExpr); /* Check that the affinity that will be used to perform the @@ -1427,11 +1423,11 @@ char aff = comparisonAffinity(pX); int affinity_ok = (pTab->aCol[iCol].affinity==aff||aff==SQLITE_AFF_NONE); for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){ if( (pIdx->aiColumn[0]==iCol) - && (pReq==sqlite3FindCollSeq(db, ENC(db), pIdx->azColl[0], 0)) + && sqlite3FindCollSeq(db, ENC(db), pIdx->azColl[0], 0)==pReq && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None)) ){ int iMem = ++pParse->nMem; int iAddr; char *pKey; @@ -1456,10 +1452,13 @@ } } } if( eType==0 ){ + /* Could not found an existing able or index to use as the RHS b-tree. + ** We will have to generate an ephemeral table to do the job. + */ int rMayHaveNull = 0; eType = IN_INDEX_EPH; if( prNotFound ){ *prNotFound = rMayHaveNull = ++pParse->nMem; }else if( pX->pLeft->iColumn<0 && !ExprHasAnyProperty(pX, EP_xIsSelect) ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -9,11 +9,11 @@ ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.877 2009/05/28 01:00:55 drh Exp $ +** @(#) $Id: sqliteInt.h,v 1.878 2009/05/28 21:04:39 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* @@ -2401,11 +2401,10 @@ void sqlite3ReleaseTempRange(Parse*,int,int); Expr *sqlite3ExprAlloc(sqlite3*,int,const Token*,int); Expr *sqlite3Expr(sqlite3*,int,const char*); void sqlite3ExprAttachSubtrees(sqlite3*,Expr*,Expr*,Expr*); Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*); -Expr *sqlite3RegisterExpr(Parse*,Token*); Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*); Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*); void sqlite3ExprAssignVarNumber(Parse*, Expr*); void sqlite3ExprClear(sqlite3*, Expr*); void sqlite3ExprDelete(sqlite3*, Expr*);