Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1184,10 +1184,11 @@ Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable); int j, k, regKey; regKey = sqlite3GetTempRange(pParse, pPk->nKeyCol); for(j=0; jnKeyCol; j++){ k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]); + assert( k>=0 && knCol ); sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, regKey+j); VdbeComment((v, "%s", pTab->aCol[pPk->aiColumn[j]].zName)); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regKey, pPk->nKeyCol, regRowid); sqlite3ReleaseTempRange(pParse, regKey, pPk->nKeyCol); @@ -1233,16 +1234,14 @@ /* We know that the regSampleRowid row exists because it was read by ** the previous loop. Thus the not-found jump of seekOp will never ** be taken */ VdbeCoverageNeverTaken(v); #ifdef SQLITE_ENABLE_STAT3 - sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, - pIdx->aiColumn[0], regSample); + sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iTabCur, 0, regSample); #else for(i=0; iaiColumn[i]; - sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i); + sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iTabCur, i, regCol+i); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample); #endif sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid); Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -441,10 +441,11 @@ static void freeIndex(sqlite3 *db, Index *p){ #ifndef SQLITE_OMIT_ANALYZE sqlite3DeleteIndexSamples(db, p); #endif sqlite3ExprDelete(db, p->pPartIdxWhere); + sqlite3ExprListDelete(db, p->aColExpr); sqlite3DbFree(db, p->zColAff); if( p->isResized ) sqlite3DbFree(db, p->azColl); #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3_free(p->aiRowEst); #endif @@ -1307,11 +1308,12 @@ nTerm = 1; }else{ nTerm = pList->nExpr; for(i=0; ia[i].pExpr); - if( pCExpr && pCExpr->op==TK_ID ){ + assert( pCExpr!=0 ); + if( pCExpr->op==TK_ID ){ const char *zCName = pCExpr->u.zToken; for(iCol=0; iColnCol; iCol++){ if( sqlite3StrICmp(zCName, pTab->aCol[iCol].zName)==0 ){ pTab->aCol[iCol].colFlags |= COLFLAG_PRIMKEY; zType = pTab->aCol[iCol].zType; @@ -2911,11 +2913,10 @@ sqlite3 *db = pParse->db; Db *pDb; /* The specific table containing the indexed database */ int iDb; /* Index of the database that is being written */ Token *pName = 0; /* Unqualified name of the index to create */ struct ExprList_item *pListItem; /* For looping over pList */ - const Column *pTabCol; /* A column in the table */ int nExtra = 0; /* Space allocated for zExtra[] */ int nExtraCol; /* Number of extra columns needed */ char *zExtra = 0; /* Extra space after the Index object */ Index *pPk = 0; /* PRIMARY KEY index for WITHOUT ROWID tables */ @@ -3083,11 +3084,12 @@ /* Figure out how many bytes of space are required to store explicitly ** specified collation sequence names. */ for(i=0; inExpr; i++){ Expr *pExpr = pList->a[i].pExpr; - if( pExpr && pExpr->op==TK_COLLATE ){ + assert( pExpr!=0 ); + if( pExpr->op==TK_COLLATE ){ nExtra += (1 + sqlite3Strlen30(pExpr->u.zToken)); } } /* @@ -3124,68 +3126,85 @@ sortOrderMask = -1; /* Honor DESC */ }else{ sortOrderMask = 0; /* Ignore DESC */ } - /* Scan the names of the columns of the table to be indexed and - ** load the column indices into the Index structure. Report an error - ** if any column is not found. + /* Analyze the list of expressions that form the terms of the index and + ** report any errors. In the common case where the expression is exactly + ** a table column, store that column in aiColumn[]. For general expressions, + ** populate pIndex->aColExpr and store -2 in aiColumn[]. ** - ** TODO: Add a test to make sure that the same column is not named - ** more than once within the same index. Only the first instance of - ** the column will ever be used by the optimizer. Note that using the - ** same column more than once cannot be an error because that would - ** break backwards compatibility - it needs to be a warning. + ** TODO: Issue a warning if two or more columns of the index are identical. + ** TODO: Issue a warning if the table primary key is used as part of the + ** index key. */ for(i=0, pListItem=pList->a; inExpr; i++, pListItem++){ - const char *zColName; - Expr *pCExpr; - int requestedSortOrder; + Expr *pCExpr; /* The i-th index expression */ + int requestedSortOrder; /* ASC or DESC on the i-th expression */ char *zColl; /* Collation sequence name */ sqlite3StringToId(pListItem->pExpr); + sqlite3ResolveSelfReference(pParse, pTab, NC_IdxExpr, pListItem->pExpr, 0); + if( pParse->nErr ) goto exit_create_index; pCExpr = sqlite3ExprSkipCollate(pListItem->pExpr); - if( pCExpr->op!=TK_ID ){ - sqlite3ErrorMsg(pParse, "indexes on expressions not yet supported"); - continue; - } - zColName = pCExpr->u.zToken; - for(j=0, pTabCol=pTab->aCol; jnCol; j++, pTabCol++){ - if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break; - } - if( j>=pTab->nCol ){ - sqlite3ErrorMsg(pParse, "table %s has no column named %s", - pTab->zName, zColName); - pParse->checkSchema = 1; - goto exit_create_index; - } - assert( j<=0x7fff ); - pIndex->aiColumn[i] = (i16)j; + if( pCExpr->op!=TK_COLUMN ){ + if( pTab==pParse->pNewTable ){ + sqlite3ErrorMsg(pParse, "expressions prohibited in PRIMARY KEY and " + "UNIQUE constraints"); + goto exit_create_index; + } + if( pIndex->aColExpr==0 ){ + ExprList *pCopy = sqlite3ExprListDup(db, pList, 0); + pIndex->aColExpr = pCopy; + if( !db->mallocFailed ){ + assert( pCopy!=0 ); + pListItem = &pCopy->a[i]; + } + } + j = -2; + pIndex->aiColumn[i] = -2; + pIndex->uniqNotNull = 0; + }else{ + j = pCExpr->iColumn; + assert( j<=0x7fff ); + if( j<0 ){ + j = pTab->iPKey; + }else if( pTab->aCol[j].notNull==0 ){ + pIndex->uniqNotNull = 0; + } + pIndex->aiColumn[i] = (i16)j; + } + zColl = 0; if( pListItem->pExpr->op==TK_COLLATE ){ int nColl; zColl = pListItem->pExpr->u.zToken; nColl = sqlite3Strlen30(zColl) + 1; assert( nExtra>=nColl ); memcpy(zExtra, zColl, nColl); zColl = zExtra; zExtra += nColl; nExtra -= nColl; - }else{ + }else if( j>=0 ){ zColl = pTab->aCol[j].zColl; - if( !zColl ) zColl = "BINARY"; } + if( !zColl ) zColl = "BINARY"; if( !db->init.busy && !sqlite3LocateCollSeq(pParse, zColl) ){ goto exit_create_index; } pIndex->azColl[i] = zColl; requestedSortOrder = pListItem->sortOrder & sortOrderMask; pIndex->aSortOrder[i] = (u8)requestedSortOrder; - if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0; } + + /* Append the table key to the end of the index. For WITHOUT ROWID + ** tables (when pPk!=0) this will be the declared PRIMARY KEY. For + ** normal tables (when pPk==0) this will be the rowid. + */ if( pPk ){ for(j=0; jnKeyCol; j++){ int x = pPk->aiColumn[j]; + assert( x>=0 ); if( hasColumn(pIndex->aiColumn, pIndex->nKeyCol, x) ){ pIndex->nColumn--; }else{ pIndex->aiColumn[i] = x; pIndex->azColl[i] = pPk->azColl[j]; @@ -3232,10 +3251,11 @@ if( pIdx->nKeyCol!=pIndex->nKeyCol ) continue; for(k=0; knKeyCol; k++){ const char *z1; const char *z2; + assert( pIdx->aiColumn[k]>=0 ); if( pIdx->aiColumn[k]!=pIndex->aiColumn[k] ) break; z1 = pIdx->azColl[k]; z2 = pIndex->azColl[k]; if( z1!=z2 && sqlite3StrICmp(z1, z2) ) break; } @@ -3263,10 +3283,11 @@ } /* Link the new Index structure to its table and to the other ** in-memory database structures. */ + assert( pParse->nErr==0 ); if( db->init.busy ){ Index *p; assert( sqlite3SchemaMutexHeld(db, 0, pIndex->pSchema) ); p = sqlite3HashInsert(&pIndex->pSchema->idxHash, pIndex->zName, pIndex); @@ -3292,11 +3313,11 @@ ** If pTblName==0 it means this index is generated as an implied PRIMARY KEY ** or UNIQUE index in a CREATE TABLE statement. Since the table ** has just been created, it contains no data and the index initialization ** step can be skipped. */ - else if( pParse->nErr==0 && (HasRowid(pTab) || pTblName!=0) ){ + else if( HasRowid(pTab) || pTblName!=0 ){ Vdbe *v; char *zStmt; int iMem = ++pParse->nMem; v = sqlite3GetVdbe(pParse); @@ -4122,16 +4143,20 @@ int j; StrAccum errMsg; Table *pTab = pIdx->pTable; sqlite3StrAccumInit(&errMsg, pParse->db, 0, 0, 200); - for(j=0; jnKeyCol; j++){ - char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName; - if( j ) sqlite3StrAccumAppend(&errMsg, ", ", 2); - sqlite3StrAccumAppendAll(&errMsg, pTab->zName); - sqlite3StrAccumAppend(&errMsg, ".", 1); - sqlite3StrAccumAppendAll(&errMsg, zCol); + if( pIdx->aColExpr ){ + sqlite3XPrintf(&errMsg, 0, "index '%q'", pIdx->zName); + }else{ + for(j=0; jnKeyCol; j++){ + char *zCol; + assert( pIdx->aiColumn[j]>=0 ); + zCol = pTab->aCol[pIdx->aiColumn[j]].zName; + if( j ) sqlite3StrAccumAppend(&errMsg, ", ", 2); + sqlite3XPrintf(&errMsg, 0, "%s.%s", pTab->zName, zCol); + } } zErr = sqlite3StrAccumFinish(&errMsg); sqlite3HaltConstraint(pParse, IsPrimaryKeyIndex(pIdx) ? SQLITE_CONSTRAINT_PRIMARYKEY : SQLITE_CONSTRAINT_UNIQUE, Index: src/date.c ================================================================== --- src/date.c +++ src/date.c @@ -1113,18 +1113,18 @@ ** external linkage. */ void sqlite3RegisterDateTimeFunctions(void){ static SQLITE_WSD FuncDef aDateTimeFuncs[] = { #ifndef SQLITE_OMIT_DATETIME_FUNCS - FUNCTION(julianday, -1, 0, 0, juliandayFunc ), - FUNCTION(date, -1, 0, 0, dateFunc ), - FUNCTION(time, -1, 0, 0, timeFunc ), - FUNCTION(datetime, -1, 0, 0, datetimeFunc ), - FUNCTION(strftime, -1, 0, 0, strftimeFunc ), - FUNCTION(current_time, 0, 0, 0, ctimeFunc ), - FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), - FUNCTION(current_date, 0, 0, 0, cdateFunc ), + DFUNCTION(julianday, -1, 0, 0, juliandayFunc ), + DFUNCTION(date, -1, 0, 0, dateFunc ), + DFUNCTION(time, -1, 0, 0, timeFunc ), + DFUNCTION(datetime, -1, 0, 0, datetimeFunc ), + DFUNCTION(strftime, -1, 0, 0, strftimeFunc ), + DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), + DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), + DFUNCTION(current_date, 0, 0, 0, cdateFunc ), #else STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), #endif Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -409,10 +409,11 @@ } /* Extract the rowid or primary key for the current row */ if( pPk ){ for(i=0; iaiColumn[i]>=(-1) ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pPk->aiColumn[i], iPk+i); } iKey = iPk; }else{ @@ -787,18 +788,17 @@ Index *pPrior, /* Previously generated index key */ int regPrior /* Register holding previous generated key */ ){ Vdbe *v = pParse->pVdbe; int j; - Table *pTab = pIdx->pTable; int regBase; int nCol; if( piPartIdxLabel ){ if( pIdx->pPartIdxWhere ){ *piPartIdxLabel = sqlite3VdbeMakeLabel(v); - pParse->iPartIdxTab = iDataCur; + pParse->iSelfTab = iDataCur; sqlite3ExprCachePush(pParse); sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel, SQLITE_JUMPIFNULL); }else{ *piPartIdxLabel = 0; @@ -806,13 +806,18 @@ } nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn; regBase = sqlite3GetTempRange(pParse, nCol); if( pPrior && (regBase!=regPrior || pPrior->pPartIdxWhere) ) pPrior = 0; for(j=0; jaiColumn[j]==pIdx->aiColumn[j] ) continue; - sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pIdx->aiColumn[j], - regBase+j); + if( pPrior + && pPrior->aiColumn[j]==pIdx->aiColumn[j] + && pPrior->aiColumn[j]>=(-1) + ){ + /* This column was already computed by the previous index */ + continue; + } + sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iDataCur, j, regBase+j); /* If the column affinity is REAL but the number is an integer, then it ** might be stored in the table as an integer (using a compact ** representation) then converted to REAL by an OP_RealAffinity opcode. ** But we are getting ready to store this value back into an index, where ** it should be converted by to INTEGER again. So omit the OP_RealAffinity Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -2429,10 +2429,32 @@ if( p->iReg==iReg ){ p->tempReg = 0; } } } + +/* Generate code that will load into register regOut a value that is +** appropriate for the iIdxCol-th column of index pIdx. +*/ +void sqlite3ExprCodeLoadIndexColumn( + Parse *pParse, /* The parsing context */ + Index *pIdx, /* The index whose column is to be loaded */ + int iTabCur, /* Cursor pointing to a table row */ + int iIdxCol, /* The column of the index to be loaded */ + int regOut /* Store the index column value in this register */ +){ + i16 iTabCol = pIdx->aiColumn[iIdxCol]; + if( iTabCol>=(-1) ){ + sqlite3ExprCodeGetColumnOfTable(pParse->pVdbe, pIdx->pTable, iTabCur, + iTabCol, regOut); + return; + } + assert( pIdx->aColExpr ); + assert( pIdx->aColExpr->nExpr>iIdxCol ); + pParse->iSelfTab = iTabCur; + sqlite3ExprCode(pParse, pIdx->aColExpr->a[iIdxCol].pExpr, regOut); +} /* ** Generate code to extract the value of the iCol-th column of a table. */ void sqlite3ExprCodeGetColumnOfTable( @@ -2615,12 +2637,13 @@ if( pParse->ckBase>0 ){ /* Generating CHECK constraints or inserting into partial index */ inReg = pExpr->iColumn + pParse->ckBase; break; }else{ - /* Deleting from a partial index */ - iTab = pParse->iPartIdxTab; + /* Coding an expression that is part of an index where column names + ** in the index refer to the table to which the index belongs */ + iTab = pParse->iSelfTab; } } inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab, pExpr->iColumn, iTab, target, pExpr->op2); @@ -3763,11 +3786,13 @@ return 1; } return 2; } if( pA->op!=TK_COLUMN && ALWAYS(pA->op!=TK_AGG_COLUMN) && pA->u.zToken ){ - if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ + if( pA->op==TK_FUNCTION ){ + if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; + }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ return pA->op==TK_COLLATE ? 1 : 2; } } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){ Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -1735,19 +1735,19 @@ FUNCTION2(likelihood, 2, 0, 0, noopFunc, SQLITE_FUNC_UNLIKELY), FUNCTION2(likely, 1, 0, 0, noopFunc, SQLITE_FUNC_UNLIKELY), VFUNCTION(random, 0, 0, 0, randomFunc ), VFUNCTION(randomblob, 1, 0, 0, randomBlob ), FUNCTION(nullif, 2, 0, 1, nullifFunc ), - FUNCTION(sqlite_version, 0, 0, 0, versionFunc ), - FUNCTION(sqlite_source_id, 0, 0, 0, sourceidFunc ), + DFUNCTION(sqlite_version, 0, 0, 0, versionFunc ), + DFUNCTION(sqlite_source_id, 0, 0, 0, sourceidFunc ), FUNCTION(sqlite_log, 2, 0, 0, errlogFunc ), #if SQLITE_USER_AUTHENTICATION FUNCTION(sqlite_crypt, 2, 0, 0, sqlite3CryptFunc ), #endif #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS - FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc ), - FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc ), + DFUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc ), + DFUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc ), #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ FUNCTION(quote, 1, 0, 0, quoteFunc ), VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid), VFUNCTION(changes, 0, 0, 0, changes ), VFUNCTION(total_changes, 0, 0, 0, total_changes ), @@ -1755,12 +1755,12 @@ FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ), #ifdef SQLITE_SOUNDEX FUNCTION(soundex, 1, 0, 0, soundexFunc ), #endif #ifndef SQLITE_OMIT_LOAD_EXTENSION - FUNCTION(load_extension, 1, 0, 0, loadExt ), - FUNCTION(load_extension, 2, 0, 0, loadExt ), + VFUNCTION(load_extension, 1, 0, 0, loadExt ), + VFUNCTION(load_extension, 2, 0, 0, loadExt ), #endif AGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize ), AGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ), AGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ), AGGREGATE2(count, 0, 0, 0, countStep, countFinalize, Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -86,11 +86,22 @@ db->mallocFailed = 1; return 0; } for(n=0; nnColumn; n++){ i16 x = pIdx->aiColumn[n]; - pIdx->zColAff[n] = x<0 ? SQLITE_AFF_INTEGER : pTab->aCol[x].affinity; + if( x>=0 ){ + pIdx->zColAff[n] = pTab->aCol[x].affinity; + }else if( x==(-1) ){ + pIdx->zColAff[n] = SQLITE_AFF_INTEGER; + }else{ + char aff; + assert( x==(-2) ); + assert( pIdx->aColExpr!=0 ); + aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr); + if( aff==0 ) aff = SQLITE_AFF_BLOB; + pIdx->zColAff[n] = aff; + } } pIdx->zColAff[n] = 0; } return pIdx->zColAff; @@ -1392,19 +1403,26 @@ */ regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn); for(i=0; inColumn; i++){ int iField = pIdx->aiColumn[i]; int x; - if( iField<0 || iField==pTab->iPKey ){ - if( regRowid==regIdx+i ) continue; /* ROWID already in regIdx+i */ - x = regNewData; - regRowid = pIdx->pPartIdxWhere ? -1 : regIdx+i; + if( iField==(-2) ){ + pParse->ckBase = regNewData+1; + sqlite3ExprCode(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i); + pParse->ckBase = 0; + VdbeComment((v, "%s column %d", pIdx->zName, i)); }else{ - x = iField + regNewData + 1; + if( iField==(-1) || iField==pTab->iPKey ){ + if( regRowid==regIdx+i ) continue; /* ROWID already in regIdx+i */ + x = regNewData; + regRowid = pIdx->pPartIdxWhere ? -1 : regIdx+i; + }else{ + x = iField + regNewData + 1; + } + sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i); + VdbeComment((v, "%s", iField<0 ? "rowid" : pTab->aCol[iField].zName)); } - sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i); - VdbeComment((v, "%s", iField<0 ? "rowid" : pTab->aCol[iField].zName)); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]); VdbeComment((v, "for %s", pIdx->zName)); sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn); @@ -1720,10 +1738,17 @@ return 0; /* Different conflict resolution strategies */ } for(i=0; inKeyCol; i++){ if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ return 0; /* Different columns indexed */ + } + if( pSrc->aiColumn[i]==(-2) ){ + assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 ); + if( sqlite3ExprCompare(pSrc->aColExpr->a[i].pExpr, + pDest->aColExpr->a[i].pExpr, -1)!=0 ){ + return 0; /* Different expressions in the index */ + } } if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){ return 0; /* Different sort orders */ } if( !xferCompatibleCollation(pSrc->azColl[i],pDest->azColl[i]) ){ Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -545,40 +545,29 @@ } return p; } /* -** Report an error that an expression is not valid for a partial index WHERE -** clause. -*/ -static void notValidPartIdxWhere( - Parse *pParse, /* Leave error message here */ - NameContext *pNC, /* The name context */ - const char *zMsg /* Type of error */ -){ - if( (pNC->ncFlags & NC_PartIdx)!=0 ){ - sqlite3ErrorMsg(pParse, "%s prohibited in partial index WHERE clauses", - zMsg); - } -} - -#ifndef SQLITE_OMIT_CHECK -/* -** Report an error that an expression is not valid for a CHECK constraint. -*/ -static void notValidCheckConstraint( - Parse *pParse, /* Leave error message here */ - NameContext *pNC, /* The name context */ - const char *zMsg /* Type of error */ -){ - if( (pNC->ncFlags & NC_IsCheck)!=0 ){ - sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg); - } -} -#else -# define notValidCheckConstraint(P,N,M) -#endif +** Report an error that an expression is not valid for some set of +** pNC->ncFlags values determined by validMask. +*/ +static void notValid( + Parse *pParse, /* Leave error message here */ + NameContext *pNC, /* The name context */ + const char *zMsg, /* Type of error */ + int validMask /* Set of contexts for which prohibited */ +){ + assert( (validMask&~(NC_IsCheck|NC_PartIdx|NC_IdxExpr))==0 ); + if( (pNC->ncFlags & validMask)!=0 ){ + const char *zIn = "partial index WHERE clauses"; + if( pNC->ncFlags & NC_IdxExpr ) zIn = "index expressions"; +#ifndef SQLITE_OMIT_CHECK + else if( pNC->ncFlags & NC_IsCheck ) zIn = "CHECK constraints"; +#endif + sqlite3ErrorMsg(pParse, "%s prohibited in %s", zMsg, zIn); + } +} /* ** Expression p should encode a floating point value between 1.0 and 0.0. ** Return 1024 times this value. Or return -1 if p is not a floating point ** value between 1.0 and 0.0. @@ -659,10 +648,12 @@ const char *zTable; const char *zDb; Expr *pRight; /* if( pSrcList==0 ) break; */ + notValid(pParse, pNC, "the \".\" operator", NC_IdxExpr); + /*notValid(pParse, pNC, "the \".\" operator", NC_PartIdx|NC_IsCheck, 1);*/ pRight = pExpr->pRight; if( pRight->op==TK_ID ){ zDb = 0; zTable = pExpr->pLeft->u.zToken; zColumn = pRight->u.zToken; @@ -688,11 +679,11 @@ const char *zId; /* The function name. */ FuncDef *pDef; /* Information about the function */ u8 enc = ENC(pParse->db); /* The database encoding */ assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); - notValidPartIdxWhere(pParse, pNC, "functions"); + notValid(pParse, pNC, "functions", NC_PartIdx); zId = pExpr->u.zToken; nId = sqlite3Strlen30(zId); pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0); if( pDef==0 ){ pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0); @@ -736,13 +727,22 @@ } pExpr->op = TK_NULL; return WRC_Prune; } #endif - if( pDef->funcFlags & SQLITE_FUNC_CONSTANT ){ + if( pDef->funcFlags & (SQLITE_FUNC_CONSTANT|SQLITE_FUNC_SLOCHNG) ){ + /* For the purposes of the EP_ConstFunc flag, date and time + ** functions and other functions that change slowly are considered + ** constant because they are constant for the duration of one query */ ExprSetProperty(pExpr,EP_ConstFunc); } + if( (pDef->funcFlags & SQLITE_FUNC_CONSTANT)==0 ){ + /* Date/time functions that use 'now', and other functions like + ** sqlite_version() that might change over time cannot be used + ** in an index. */ + notValid(pParse, pNC, "non-deterministic functions", NC_IdxExpr); + } } if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){ sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId); pNC->nErr++; is_agg = 0; @@ -784,23 +784,21 @@ #endif case TK_IN: { testcase( pExpr->op==TK_IN ); if( ExprHasProperty(pExpr, EP_xIsSelect) ){ int nRef = pNC->nRef; - notValidCheckConstraint(pParse, pNC, "subqueries"); - notValidPartIdxWhere(pParse, pNC, "subqueries"); + notValid(pParse, pNC, "subqueries", NC_IsCheck|NC_PartIdx|NC_IdxExpr); sqlite3WalkSelect(pWalker, pExpr->x.pSelect); assert( pNC->nRef>=nRef ); if( nRef!=pNC->nRef ){ ExprSetProperty(pExpr, EP_VarSelect); } } break; } case TK_VARIABLE: { - notValidCheckConstraint(pParse, pNC, "parameters"); - notValidPartIdxWhere(pParse, pNC, "parameters"); + notValid(pParse, pNC, "parameters", NC_IsCheck|NC_PartIdx|NC_IdxExpr); break; } } return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue; } @@ -1499,18 +1497,18 @@ ** Any errors cause an error message to be set in pParse. */ void sqlite3ResolveSelfReference( Parse *pParse, /* Parsing context */ Table *pTab, /* The table being referenced */ - int type, /* NC_IsCheck or NC_PartIdx */ + int type, /* NC_IsCheck or NC_PartIdx or NC_IdxExpr */ Expr *pExpr, /* Expression to resolve. May be NULL. */ ExprList *pList /* Expression list to resolve. May be NUL. */ ){ SrcList sSrc; /* Fake SrcList for pParse->pNewTable */ NameContext sNC; /* Name context for pParse->pNewTable */ - assert( type==NC_IsCheck || type==NC_PartIdx ); + assert( type==NC_IsCheck || type==NC_PartIdx || type==NC_IdxExpr ); memset(&sNC, 0, sizeof(sNC)); memset(&sSrc, 0, sizeof(sSrc)); sSrc.nSrc = 1; sSrc.a[0].zName = pTab->zName; sSrc.a[0].pTab = pTab; Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -4250,12 +4250,12 @@ if( nSql ){ if( !_all_whitespace(zSql) ){ fprintf(stderr, "Error: incomplete SQL: %s\n", zSql); errCnt++; } - free(zSql); } + free(zSql); free(zLine); return errCnt>0; } /* Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1382,22 +1382,24 @@ /* ** Possible values for FuncDef.flags. Note that the _LENGTH and _TYPEOF ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG. There ** are assert() statements in the code to verify this. */ -#define SQLITE_FUNC_ENCMASK 0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */ -#define SQLITE_FUNC_LIKE 0x004 /* Candidate for the LIKE optimization */ -#define SQLITE_FUNC_CASE 0x008 /* Case-sensitive LIKE-type function */ -#define SQLITE_FUNC_EPHEM 0x010 /* Ephemeral. Delete with VDBE */ -#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */ -#define SQLITE_FUNC_LENGTH 0x040 /* Built-in length() function */ -#define SQLITE_FUNC_TYPEOF 0x080 /* Built-in typeof() function */ -#define SQLITE_FUNC_COUNT 0x100 /* Built-in count(*) aggregate */ -#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */ -#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */ -#define SQLITE_FUNC_CONSTANT 0x800 /* Constant inputs give a constant output */ -#define SQLITE_FUNC_MINMAX 0x1000 /* True for min() and max() aggregates */ +#define SQLITE_FUNC_ENCMASK 0x0003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */ +#define SQLITE_FUNC_LIKE 0x0004 /* Candidate for the LIKE optimization */ +#define SQLITE_FUNC_CASE 0x0008 /* Case-sensitive LIKE-type function */ +#define SQLITE_FUNC_EPHEM 0x0010 /* Ephemeral. Delete with VDBE */ +#define SQLITE_FUNC_NEEDCOLL 0x0020 /* sqlite3GetFuncCollSeq() might be called*/ +#define SQLITE_FUNC_LENGTH 0x0040 /* Built-in length() function */ +#define SQLITE_FUNC_TYPEOF 0x0080 /* Built-in typeof() function */ +#define SQLITE_FUNC_COUNT 0x0100 /* Built-in count(*) aggregate */ +#define SQLITE_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */ +#define SQLITE_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */ +#define SQLITE_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */ +#define SQLITE_FUNC_MINMAX 0x1000 /* True for min() and max() aggregates */ +#define SQLITE_FUNC_SLOCHNG 0x2000 /* "Slow Change". Value constant during a + ** single query - might change over time */ /* ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are ** used to create the initializers for the FuncDef structures. ** @@ -1408,10 +1410,16 @@ ** as the user-data (sqlite3_user_data()) for the function. If ** argument bNC is true, then the SQLITE_FUNC_NEEDCOLL flag is set. ** ** VFUNCTION(zName, nArg, iArg, bNC, xFunc) ** Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag. +** +** DFUNCTION(zName, nArg, iArg, bNC, xFunc) +** Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and +** adds the SQLITE_FUNC_SLOCHNG flag. Used for date & time functions +** and functions like sqlite_version() that can change, but not during +** a single query. ** ** AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal) ** Used to create an aggregate function definition implemented by ** the C functions xStep and xFinal. The first four parameters ** are interpreted in the same way as the first 4 parameters to @@ -1429,15 +1437,18 @@ {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \ {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} +#define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \ + {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ + SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \ {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \ - {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ + {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ pArg, 0, xFunc, 0, 0, #zName, 0, 0} #define LIKEFUNC(zName, nArg, arg, flags) \ {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|flags, \ (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0} #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \ @@ -1869,10 +1880,11 @@ Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ u8 *aSortOrder; /* for each column: True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ Expr *pPartIdxWhere; /* WHERE clause for partial indices */ + ExprList *aColExpr; /* Column expressions */ int tnum; /* DB Page containing root of this index */ LogEst szIdxRow; /* Estimated average row size in bytes */ u16 nKeyCol; /* Number of columns forming the key */ u16 nColumn; /* Number of columns stored in the index */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ @@ -2118,11 +2130,11 @@ #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ -#define EP_ConstFunc 0x080000 /* Node is a SQLITE_FUNC_CONSTANT function */ +#define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ /* ** Combinations of two or more EP_* flags @@ -2389,10 +2401,11 @@ #define NC_AllowAgg 0x0001 /* Aggregate functions are allowed here */ #define NC_HasAgg 0x0002 /* One or more aggregate functions seen */ #define NC_IsCheck 0x0004 /* True if resolving names in a CHECK constraint */ #define NC_InAggFunc 0x0008 /* True if analyzing arguments to an agg func */ #define NC_PartIdx 0x0010 /* True if resolving a partial index WHERE */ +#define NC_IdxExpr 0x0020 /* True if resolving columns of CREATE INDEX */ #define NC_MinMaxAgg 0x1000 /* min/max aggregates seen. See note above */ /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. @@ -2658,11 +2671,11 @@ int nSet; /* Number of sets used so far */ int nOnce; /* Number of OP_Once instructions so far */ int nOpAlloc; /* Number of slots allocated for Vdbe.aOp[] */ int iFixedOp; /* Never back out opcodes iFixedOp-1 or earlier */ int ckBase; /* Base register of data during check constraints */ - int iPartIdxTab; /* Table corresponding to a partial index */ + int iSelfTab; /* Table of an index whose exprs are being coded */ int iCacheLevel; /* ColCache valid when aColCache[].iLevel<=iCacheLevel */ int iCacheCnt; /* Counter used to generate aColCache[].lru values */ int nLabel; /* Number of labels used */ int *aLabel; /* Space to hold the labels */ struct yColCache { @@ -3359,10 +3372,11 @@ int sqlite3WhereIsOrdered(WhereInfo*); int sqlite3WhereIsSorted(WhereInfo*); int sqlite3WhereContinueLabel(WhereInfo*); int sqlite3WhereBreakLabel(WhereInfo*); int sqlite3WhereOkOnePass(WhereInfo*, int*); +void sqlite3ExprCodeLoadIndexColumn(Parse*, Index*, int, int, int); int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8); void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int); void sqlite3ExprCodeMove(Parse*, int, int, int); void sqlite3ExprCacheStore(Parse*, int, int, int); void sqlite3ExprCachePush(Parse*); Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -270,20 +270,23 @@ hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey); /* There is one entry in the aRegIdx[] array for each index on the table ** being updated. Fill in aRegIdx[] with a register number that will hold - ** the key for accessing each index. + ** the key for accessing each index. + ** + ** FIXME: Be smarter about omitting indexes that use expressions. */ for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngKey || hasFK || pIdx->pPartIdxWhere || pIdx==pPk ){ reg = ++pParse->nMem; }else{ reg = 0; for(i=0; inKeyCol; i++){ - if( aXRef[pIdx->aiColumn[i]]>=0 ){ + i16 iIdxCol = pIdx->aiColumn[i]; + if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){ reg = ++pParse->nMem; break; } } } @@ -379,10 +382,11 @@ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur); if( pWInfo==0 ) goto update_cleanup; okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); for(i=0; iaiColumn[i]>=(-1) ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i], iPk+i); } if( okOnePass ){ sqlite3VdbeChangeToNoop(v, addrOpen); Index: src/vdbeblob.c ================================================================== --- src/vdbeblob.c +++ src/vdbeblob.c @@ -245,11 +245,12 @@ } #endif for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int j; for(j=0; jnKeyCol; j++){ - if( pIdx->aiColumn[j]==iCol ){ + /* FIXME: Be smarter about indexes that use expressions */ + if( pIdx->aiColumn[j]==iCol || pIdx->aiColumn[j]==(-2) ){ zFault = "indexed"; } } } if( zFault ){ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -178,22 +178,24 @@ int k = pScan->k; /* Where to start scanning */ while( pScan->iEquiv<=pScan->nEquiv ){ iCur = pScan->aiCur[pScan->iEquiv-1]; iColumn = pScan->aiColumn[pScan->iEquiv-1]; + assert( iColumn!=(-2) || pScan->pIdxExpr!=0 ); while( (pWC = pScan->pWC)!=0 ){ for(pTerm=pWC->a+k; knTerm; k++, pTerm++){ if( pTerm->leftCursor==iCur && pTerm->u.leftColumn==iColumn + && (iColumn!=(-2) + || sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0) && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin)) ){ if( (pTerm->eOperator & WO_EQUIV)!=0 && pScan->nEquivaiCur) + && (pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight))->op==TK_COLUMN ){ int j; - pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight); - assert( pX->op==TK_COLUMN ); for(j=0; jnEquiv; j++){ if( pScan->aiCur[j]==pX->iTable && pScan->aiColumn[j]==pX->iColumn ){ break; } @@ -271,15 +273,18 @@ int j; /* memset(pScan, 0, sizeof(*pScan)); */ pScan->pOrigWC = pWC; pScan->pWC = pWC; + pScan->pIdxExpr = 0; + if( pIdx ){ + j = iColumn; + iColumn = pIdx->aiColumn[j]; + if( iColumn==(-2) ) pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr; + } if( pIdx && iColumn>=0 ){ pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity; - for(j=0; pIdx->aiColumn[j]!=iColumn; j++){ - if( NEVER(j>pIdx->nColumn) ) return 0; - } pScan->zCollName = pIdx->azColl[j]; }else{ pScan->idxaff = 0; pScan->zCollName = 0; } @@ -295,10 +300,13 @@ /* ** Search for a term in the WHERE clause that is of the form "X " ** where X is a reference to the iColumn of table iCur and is one of ** the WO_xx operator codes specified by the op parameter. ** Return a pointer to the term. Return 0 if not found. +** +** If pIdx!=0 then search for terms matching the iColumn-th column of pIdx +** rather than the iColumn-th column of table iCur. ** ** The term returned might by Y= if there is another constraint in ** the WHERE clause that specifies that X=Y. Any such constraints will be ** identified by the WO_EQUIV bit in the pTerm->eOperator field. The ** aiCur[]/iaColumn[] arrays hold X and all its equivalents. There are 11 @@ -370,10 +378,29 @@ } } return -1; } + +/* +** Return TRUE if the iCol-th column of index pIdx is NOT NULL +*/ +static int indexColumnNotNull(Index *pIdx, int iCol){ + int j; + assert( pIdx!=0 ); + assert( iCol>=0 && iColnColumn ); + j = pIdx->aiColumn[iCol]; + if( j>=0 ){ + return pIdx->pTable->aCol[j].notNull; + }else if( j==(-1) ){ + return 1; + }else{ + assert( j==(-2) ); + return 0; /* Assume an indexed expression can always yield a NULL */ + + } +} /* ** Return true if the DISTINCT expression-list passed as the third argument ** is redundant. ** @@ -421,16 +448,13 @@ ** contain a "col=X" term are subject to a NOT NULL constraint. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( !IsUniqueIndex(pIdx) ) continue; for(i=0; inKeyCol; i++){ - i16 iCol = pIdx->aiColumn[i]; - if( 0==sqlite3WhereFindTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){ - int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i); - if( iIdxCol<0 || pTab->aCol[iCol].notNull==0 ){ - break; - } + if( 0==sqlite3WhereFindTerm(pWC, iBase, i, ~(Bitmask)0, WO_EQ, pIdx) ){ + if( findIndexCol(pParse, pDistinct, iBase, pIdx, i)<0 ) break; + if( indexColumnNotNull(pIdx, i)==0 ) break; } } if( i==pIdx->nKeyCol ){ /* This index implies that the DISTINCT qualifier is redundant. */ return 1; @@ -778,10 +802,11 @@ testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ALL ); if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; + assert( pTerm->u.leftColumn>=(-1) ); nTerm++; } /* If the ORDER BY clause contains only columns in the current ** virtual table then allocate space for the aOrderBy part of @@ -833,10 +858,11 @@ testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_ALL ); if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; + assert( pTerm->u.leftColumn>=(-1) ); pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = (u8)pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; pIdxCons[j].op = op; @@ -2124,11 +2150,10 @@ u16 saved_nLTerm; /* Original value of pNew->nLTerm */ u16 saved_nEq; /* Original value of pNew->u.btree.nEq */ u16 saved_nSkip; /* Original value of pNew->nSkip */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ LogEst saved_nOut; /* Original value of pNew->nOut */ - int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ LogEst rSize; /* Number of rows in the table */ LogEst rLogSize; /* Logarithm of table size */ WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */ @@ -2145,20 +2170,19 @@ opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEqnColumn ); - iCol = pProbe->aiColumn[pNew->u.btree.nEq]; - pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, - opMask, pProbe); saved_nEq = pNew->u.btree.nEq; saved_nSkip = pNew->nSkip; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; + pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, saved_nEq, + opMask, pProbe); pNew->rSetup = 0; rSize = pProbe->aiRowLogEst[0]; rLogSize = estLog(rSize); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ u16 eOp = pTerm->eOperator; /* Shorthand for pTerm->eOperator */ @@ -2167,11 +2191,11 @@ int nIn = 0; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nRecValid = pBuilder->nRecValid; #endif if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) - && (iCol<0 || pSrc->pTab->aCol[iCol].notNull) + && indexColumnNotNull(pProbe, saved_nEq) ){ continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */ } if( pTerm->prereqRight & pNew->maskSelf ) continue; @@ -2204,12 +2228,14 @@ } assert( nIn>0 ); /* RHS always has 2 or more terms... The parser ** changes "x IN (?)" into "x=?". */ }else if( eOp & (WO_EQ|WO_IS) ){ + int iCol = pProbe->aiColumn[saved_nEq]; pNew->wsFlags |= WHERE_COLUMN_EQ; - if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){ + assert( saved_nEq==pNew->u.btree.nEq ); + if( iCol==(-1) || (iCol>0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1) ){ if( iCol>=0 && pProbe->uniqNotNull==0 ){ pNew->wsFlags |= WHERE_UNQ_WANTED; }else{ pNew->wsFlags |= WHERE_ONEROW; } @@ -2256,11 +2282,11 @@ }else{ int nEq = ++pNew->u.btree.nEq; assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) ); assert( pNew->nOut==saved_nOut ); - if( pTerm->truthProb<=0 && iCol>=0 ){ + if( pTerm->truthProb<=0 && pProbe->aiColumn[saved_nEq]>=0 ){ assert( (eOp & WO_IN) || nIn==0 ); testcase( eOp & WO_IN ); pNew->nOut += pTerm->truthProb; pNew->nOut -= nIn; }else{ @@ -3783,11 +3809,11 @@ || pIdx->pPartIdxWhere!=0 || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) ) continue; opMask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ; for(j=0; jnKeyCol; j++){ - pTerm = sqlite3WhereFindTerm(pWC, iCur, pIdx->aiColumn[j], 0, opMask, pIdx); + pTerm = sqlite3WhereFindTerm(pWC, iCur, j, 0, opMask, pIdx); if( pTerm==0 ) break; testcase( pTerm->eOperator & WO_IS ); pLoop->aLTerm[j] = pTerm; } if( j!=pIdx->nKeyCol ) continue; @@ -4023,18 +4049,16 @@ } } /* Assign a bit from the bitmask to every term in the FROM clause. ** - ** When assigning bitmask values to FROM clause cursors, it must be - ** the case that if X is the bitmask for the N-th FROM clause term then - ** the bitmask for all FROM clause terms to the left of the N-th term - ** is (X-1). An expression from the ON clause of a LEFT JOIN can use - ** its Expr.iRightJoinTable value to find the bitmask of the right table - ** of the join. Subtracting one from the right table bitmask gives a - ** bitmask for all tables to the left of the join. Knowing the bitmask - ** for all tables to the left of a left join is important. Ticket #3015. + ** The N-th term of the FROM clause is assigned a bitmask of 1<nSrc tables in ** pTabList, not just the first nTabList tables. nTabList is normally ** equal to pTabList->nSrc but might be shortened to 1 if the ** WHERE_ONETABLE_ONLY flag is set. @@ -4041,18 +4065,14 @@ */ for(ii=0; iinSrc; ii++){ createMask(pMaskSet, pTabList->a[ii].iCursor); sqlite3WhereTabFuncArgs(pParse, &pTabList->a[ii], &pWInfo->sWC); } -#ifndef NDEBUG - { - Bitmask toTheLeft = 0; - for(ii=0; iinSrc; ii++){ - Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor); - assert( (m-1)==toTheLeft ); - toTheLeft |= m; - } +#ifdef SQLITE_DEBUG + for(ii=0; iinSrc; ii++){ + Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor); + assert( m==MASKBIT(ii) ); } #endif /* Analyze all of the subexpressions. */ sqlite3WhereExprAnalyze(pTabList, &pWInfo->sWC); Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -284,10 +284,11 @@ */ struct WhereScan { WhereClause *pOrigWC; /* Original, innermost WhereClause */ WhereClause *pWC; /* WhereClause currently being scanned */ char *zCollName; /* Required collating sequence, if not NULL */ + Expr *pIdxExpr; /* Search for this index expression */ char idxaff; /* Must match this affinity, if zCollName!=NULL */ unsigned char nEquiv; /* Number of entries in aEquiv[] */ unsigned char iEquiv; /* Next unused slot in aEquiv[] */ u32 opMask; /* Acceptable operators */ int k; /* Resume scanning at this->pWC->a[this->k] */ Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -38,10 +38,20 @@ if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5); sqlite3StrAccumAppendAll(pStr, zColumn); sqlite3StrAccumAppend(pStr, zOp, 1); sqlite3StrAccumAppend(pStr, "?", 1); } + +/* +** Return the name of the i-th column of the pIdx index. +*/ +static const char *explainIndexColumnName(Index *pIdx, int i){ + i = pIdx->aiColumn[i]; + if( i==(-2) ) return ""; + if( i==(-1) ) return "rowid"; + return pIdx->pTable->aCol[i].zName; +} /* ** Argument pLevel describes a strategy for scanning table pTab. This ** function appends text to pStr that describes the subset of table ** rows scanned by the strategy in the form of an SQL expression. @@ -58,28 +68,26 @@ static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){ Index *pIndex = pLoop->u.btree.pIndex; u16 nEq = pLoop->u.btree.nEq; u16 nSkip = pLoop->nSkip; int i, j; - Column *aCol = pTab->aCol; - i16 *aiColumn = pIndex->aiColumn; if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return; sqlite3StrAccumAppend(pStr, " (", 2); for(i=0; i=nSkip ? "%s=?" : "ANY(%s)", z); } j = i; if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ - char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName; + const char *z = explainIndexColumnName(pIndex, i); explainAppendTerm(pStr, i++, z, ">"); } if( pLoop->wsFlags&WHERE_TOP_LIMIT ){ - char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName; + const char *z = explainIndexColumnName(pIndex, j); explainAppendTerm(pStr, i, z, "<"); } sqlite3StrAccumAppend(pStr, ")", 1); } Index: src/whereexpr.c ================================================================== --- src/whereexpr.c +++ src/whereexpr.c @@ -792,10 +792,55 @@ } pS = pS->pPrior; } return mask; } + +/* +** Expression pExpr is one operand of a comparison operator that might +** be useful for indexing. This routine checks to see if pExpr appears +** in any index. Return TRUE (1) if pExpr is an indexed term and return +** FALSE (0) if not. If TRUE is returned, also set *piCur to the cursor +** number of the table that is indexed and *piColumn to the column number +** of the column that is indexed, or -2 if an expression is being indexed. +** +** If pExpr is a TK_COLUMN column reference, then this routine always returns +** true even if that particular column is not indexed, because the column +** might be added to an automatic index later. +*/ +static int exprMightBeIndexed( + SrcList *pFrom, /* The FROM clause */ + Bitmask mPrereq, /* Bitmask of FROM clause terms referenced by pExpr */ + Expr *pExpr, /* An operand of a comparison operator */ + int *piCur, /* Write the referenced table cursor number here */ + int *piColumn /* Write the referenced table column number here */ +){ + Index *pIdx; + int i; + int iCur; + if( pExpr->op==TK_COLUMN ){ + *piCur = pExpr->iTable; + *piColumn = pExpr->iColumn; + return 1; + } + if( mPrereq==0 ) return 0; /* No table references */ + if( (mPrereq&(mPrereq-1))!=0 ) return 0; /* Refs more than one table */ + for(i=0; mPrereq>1; i++, mPrereq>>=1){} + iCur = pFrom->a[i].iCursor; + for(pIdx=pFrom->a[i].pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + if( pIdx->aColExpr==0 ) continue; + for(i=0; inKeyCol; i++){ + if( pIdx->aiColumn[i]!=(-2) ) continue; + if( sqlite3ExprCompare(pExpr, pIdx->aColExpr->a[i].pExpr, iCur)==0 ){ + *piCur = iCur; + *piColumn = -2; + return 1; + } + } + } + return 0; +} /* ** The input to this routine is an WhereTerm structure with only the ** "pExpr" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the WhereTerm @@ -863,20 +908,23 @@ pTerm->prereqAll = prereqAll; pTerm->leftCursor = -1; pTerm->iParent = -1; pTerm->eOperator = 0; if( allowedOp(op) ){ + int iCur, iColumn; Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft); Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight); u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV; - if( pLeft->op==TK_COLUMN ){ - pTerm->leftCursor = pLeft->iTable; - pTerm->u.leftColumn = pLeft->iColumn; + if( exprMightBeIndexed(pSrc, prereqLeft, pLeft, &iCur, &iColumn) ){ + pTerm->leftCursor = iCur; + pTerm->u.leftColumn = iColumn; pTerm->eOperator = operatorMask(op) & opMask; } if( op==TK_IS ) pTerm->wtFlags |= TERM_IS; - if( pRight && pRight->op==TK_COLUMN ){ + if( pRight + && exprMightBeIndexed(pSrc, pTerm->prereqRight, pRight, &iCur, &iColumn) + ){ WhereTerm *pNew; Expr *pDup; u16 eExtraOp = 0; /* Extra bits for pNew->eOperator */ if( pTerm->leftCursor>=0 ){ int idxNew; @@ -901,12 +949,12 @@ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); pLeft = sqlite3ExprSkipCollate(pDup->pLeft); - pNew->leftCursor = pLeft->iTable; - pNew->u.leftColumn = pLeft->iColumn; + pNew->leftCursor = iCur; + pNew->u.leftColumn = iColumn; testcase( (prereqLeft | extraRight) != prereqLeft ); pNew->prereqRight = prereqLeft | extraRight; pNew->prereqAll = prereqAll; pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask; } Index: test/index.test ================================================================== --- test/index.test +++ test/index.test @@ -54,23 +54,23 @@ } {1 {no such table: main.test1}} # Try adding an index on a column of a table where the table # exists but the column does not. # -do_test index-2.1 { +do_test index-2.1b { execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg] lappend v $msg -} {1 {table test1 has no column named f4}} +} {1 {no such column: f4}} # Try an index with some columns that match and others that do now. # do_test index-2.2 { set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg] execsql {DROP TABLE test1} lappend v $msg -} {1 {table test1 has no column named f4}} +} {1 {no such column: f4}} # Try creating a bunch of indices on the same table # set r {} for {set i 1} {$i<100} {incr i} { ADDED test/indexexpr1.test Index: test/indexexpr1.test ================================================================== --- /dev/null +++ test/indexexpr1.test @@ -0,0 +1,201 @@ +# 2015-08-31 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing indexes on expressions. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test indexexpr1-100 { + CREATE TABLE t1(a,b,c); + INSERT INTO t1(a,b,c) + /* 123456789 123456789 123456789 123456789 123456789 123456789 */ + VALUES('In_the_beginning_was_the_Word',1,1), + ('and_the_Word_was_with_God',1,2), + ('and_the_Word_was_God',1,3), + ('The_same_was_in_the_beginning_with_God',2,1), + ('All_things_were_made_by_him',3,1), + ('and_without_him_was_not_any_thing_made_that_was_made',3,2); + CREATE INDEX t1a1 ON t1(substr(a,1,12)); +} {} +do_execsql_test indexexpr1-110 { + SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; +} {1 2 | 1 3 |} +do_execsql_test indexexpr1-110eqp { + EXPLAIN QUERY PLAN + SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; +} {/USING INDEX t1a1/} +do_execsql_test indexexpr1-120 { + SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; +} {1 2 | 1 3 |} +do_execsql_test indexexpr1-120eqp { + EXPLAIN QUERY PLAN + SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; +} {/USING INDEX t1a1/} + +do_execsql_test indexexpr1-130 { + CREATE INDEX t1ba ON t1(b,substr(a,2,3),c); + SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; +} {2 3} +do_execsql_test indexexpr1-130eqp { + EXPLAIN QUERY PLAN + SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; +} {/USING INDEX t1ba/} + +do_execsql_test indexexpr1-140 { + SELECT rowid, substr(a,b,3), '|' FROM t1 ORDER BY 2; +} {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |} +do_execsql_test indexexpr1-141 { + CREATE INDEX t1abx ON t1(substr(a,b,3)); + SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid; +} {1 2 3} +do_execsql_test indexexpr1-141eqp { + EXPLAIN QUERY PLAN + SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid; +} {/USING INDEX t1abx/} +do_execsql_test indexexpr1-142 { + SELECT rowid FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +rowid; +} {1 2 3} +do_execsql_test indexexpr1-150 { + SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') + ORDER BY +rowid; +} {2 3 5} +do_execsql_test indexexpr1-150eqp { + EXPLAIN QUERY PLAN + SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') + ORDER BY +rowid; +} {/USING INDEX t1abx/} + +do_execsql_test indexexpr1-160 { + ALTER TABLE t1 ADD COLUMN d; + UPDATE t1 SET d=length(a); + CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29; + SELECT rowid, b, c FROM t1 + WHERE substr(a,27,3)=='ord' AND d>=29; +} {1 1 1} +do_execsql_test indexexpr1-160eqp { + EXPLAIN QUERY PLAN + SELECT rowid, b, c FROM t1 + WHERE substr(a,27,3)=='ord' AND d>=29; +} {/USING INDEX t1a2/} + + +do_execsql_test indexexpr1-200 { + DROP TABLE t1; + CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID; + INSERT INTO t1(id,a,b,c) + VALUES(1,'In_the_beginning_was_the_Word',1,1), + (2,'and_the_Word_was_with_God',1,2), + (3,'and_the_Word_was_God',1,3), + (4,'The_same_was_in_the_beginning_with_God',2,1), + (5,'All_things_were_made_by_him',3,1), + (6,'and_without_him_was_not_any_thing_made_that_was_made',3,2); + CREATE INDEX t1a1 ON t1(substr(a,1,12)); +} {} +do_execsql_test indexexpr1-210 { + SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; +} {1 2 | 1 3 |} +do_execsql_test indexexpr1-210eqp { + EXPLAIN QUERY PLAN + SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; +} {/USING INDEX t1a1/} +do_execsql_test indexexpr1-220 { + SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; +} {1 2 | 1 3 |} +do_execsql_test indexexpr1-220eqp { + EXPLAIN QUERY PLAN + SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; +} {/USING INDEX t1a1/} + +do_execsql_test indexexpr1-230 { + CREATE INDEX t1ba ON t1(b,substr(a,2,3),c); + SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; +} {2 3} +do_execsql_test indexexpr1-230eqp { + EXPLAIN QUERY PLAN + SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; +} {/USING INDEX t1ba/} + +do_execsql_test indexexpr1-240 { + SELECT id, substr(a,b,3), '|' FROM t1 ORDER BY 2; +} {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |} +do_execsql_test indexexpr1-241 { + CREATE INDEX t1abx ON t1(substr(a,b,3)); + SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id; +} {1 2 3} +do_execsql_test indexexpr1-241eqp { + EXPLAIN QUERY PLAN + SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id; +} {/USING INDEX t1abx/} +do_execsql_test indexexpr1-242 { + SELECT id FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +id; +} {1 2 3} +do_execsql_test indexexpr1-250 { + SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') + ORDER BY +id; +} {2 3 5} +do_execsql_test indexexpr1-250eqp { + EXPLAIN QUERY PLAN + SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') + ORDER BY +id; +} {/USING INDEX t1abx/} + +do_execsql_test indexexpr1-260 { + ALTER TABLE t1 ADD COLUMN d; + UPDATE t1 SET d=length(a); + CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29; + SELECT id, b, c FROM t1 + WHERE substr(a,27,3)=='ord' AND d>=29; +} {1 1 1} +do_execsql_test indexexpr1-260eqp { + EXPLAIN QUERY PLAN + SELECT id, b, c FROM t1 + WHERE substr(a,27,3)=='ord' AND d>=29; +} {/USING INDEX t1a2/} + + +do_catchsql_test indexexpr1-300 { + CREATE TABLE t2(a,b,c); + CREATE INDEX t2x1 ON t2(a,b+random()); +} {1 {non-deterministic functions prohibited in index expressions}} +do_catchsql_test indexexpr1-301 { + CREATE INDEX t2x1 ON t2(a+julianday('now')); +} {1 {non-deterministic functions prohibited in index expressions}} +do_catchsql_test indexexpr1-310 { + CREATE INDEX t2x2 ON t2(a,b+(SELECT 15)); +} {1 {subqueries prohibited in index expressions}} +do_catchsql_test indexexpr1-320 { + CREATE TABLE e1(x,y,UNIQUE(y,substr(x,1,5))); +} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} +do_catchsql_test indexexpr1-330 { + CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5))); +} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} +do_catchsql_test indexexpr1-331 { + CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5))) WITHOUT ROWID; +} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} +do_catchsql_test indexexpr1-340 { + CREATE TABLE e1(x,y,FOREIGN KEY(substr(y,1,5)) REFERENCES t1); +} {1 {near "(": syntax error}} + +do_execsql_test indexexpr1-400 { + CREATE TABLE t3(a,b,c); + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<30) + INSERT INTO t3(a,b,c) + SELECT x, printf('ab%04xyz',x), random() FROM c; + CREATE UNIQUE INDEX t3abc ON t3(CAST(a AS text), b, substr(c,1,3)); + SELECT a FROM t3 WHERE CAST(a AS text)<='10' ORDER BY +a; +} {1 10} +do_catchsql_test indexexpr1-410 { + INSERT INTO t3 SELECT * FROM t3 WHERE rowid=10; +} {1 {UNIQUE constraint failed: index 't3abc'}} + +finish_test Index: test/rowid.test ================================================================== --- test/rowid.test +++ test/rowid.test @@ -142,10 +142,12 @@ set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)" execsql $sql execsql {SELECT x FROM t1 ORDER BY x} } {1 3 5 7 9} +if 0 { # With the index-on-expressions enhancement, creating + # an index on ROWID has become possible. # We cannot index by ROWID # do_test rowid-2.9 { set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg] lappend v $msg @@ -160,10 +162,11 @@ } {1 {table t1 has no column named oid}} do_test rowid-2.12 { set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg] lappend v $msg } {1 {table t1 has no column named rowid}} +} # Columns defined in the CREATE statement override the buildin ROWID # column names. # do_test rowid-3.1 {