/ Check-in [2131a5ca]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add support for CREATE INDEX statements that use deterministic expressions rather than only column names.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2131a5ca53f0e9b0b98a9dd9a20e495d54d146a7
User & Date: drh 2015-09-04 17:32:19
References
2018-04-24
14:30 New ticket [4ba5abf6] Index on expression leads to an incorrect LEFT JOIN. artifact: e5a141f8 user: drh
Context
2015-09-04
18:03
Fix over-length source code lines in Lemon. check-in: 1efece95 user: drh tags: trunk
17:32
Add support for CREATE INDEX statements that use deterministic expressions rather than only column names. check-in: 2131a5ca user: drh tags: trunk
13:02
Merge trunk enhancements, and espeically the fix for allowing strings as column identifers in CREATE INDEX statements. Closed-Leaf check-in: 5ff85529 user: drh tags: index-expr
12:54
Continue to support the (broken) legacy syntax of allowing strings for column names in CREATE INDEX statements and in UNIQUE and PRIMARY KEY constraints. check-in: 3d3df79b user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

  1182   1182         sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, regRowid);
  1183   1183       }else{
  1184   1184         Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
  1185   1185         int j, k, regKey;
  1186   1186         regKey = sqlite3GetTempRange(pParse, pPk->nKeyCol);
  1187   1187         for(j=0; j<pPk->nKeyCol; j++){
  1188   1188           k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
         1189  +        assert( k>=0 && k<pTab->nCol );
  1189   1190           sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, regKey+j);
  1190   1191           VdbeComment((v, "%s", pTab->aCol[pPk->aiColumn[j]].zName));
  1191   1192         }
  1192   1193         sqlite3VdbeAddOp3(v, OP_MakeRecord, regKey, pPk->nKeyCol, regRowid);
  1193   1194         sqlite3ReleaseTempRange(pParse, regKey, pPk->nKeyCol);
  1194   1195       }
  1195   1196   #endif
................................................................................
  1231   1232         callStatGet(v, regStat4, STAT_GET_NDLT, regDLt);
  1232   1233         sqlite3VdbeAddOp4Int(v, seekOp, iTabCur, addrNext, regSampleRowid, 0);
  1233   1234         /* We know that the regSampleRowid row exists because it was read by
  1234   1235         ** the previous loop.  Thus the not-found jump of seekOp will never
  1235   1236         ** be taken */
  1236   1237         VdbeCoverageNeverTaken(v);
  1237   1238   #ifdef SQLITE_ENABLE_STAT3
  1238         -      sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, 
  1239         -                                      pIdx->aiColumn[0], regSample);
         1239  +      sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iTabCur, 0, regSample);
  1240   1240   #else
  1241   1241         for(i=0; i<nCol; i++){
  1242         -        i16 iCol = pIdx->aiColumn[i];
  1243         -        sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i);
         1242  +        sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iTabCur, i, regCol+i);
  1244   1243         }
  1245   1244         sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample);
  1246   1245   #endif
  1247   1246         sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp);
  1248   1247         sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid);
  1249   1248         sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid);
  1250   1249         sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */

Changes to src/build.c.

   439    439   ** Reclaim the memory used by an index
   440    440   */
   441    441   static void freeIndex(sqlite3 *db, Index *p){
   442    442   #ifndef SQLITE_OMIT_ANALYZE
   443    443     sqlite3DeleteIndexSamples(db, p);
   444    444   #endif
   445    445     sqlite3ExprDelete(db, p->pPartIdxWhere);
          446  +  sqlite3ExprListDelete(db, p->aColExpr);
   446    447     sqlite3DbFree(db, p->zColAff);
   447    448     if( p->isResized ) sqlite3DbFree(db, p->azColl);
   448    449   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
   449    450     sqlite3_free(p->aiRowEst);
   450    451   #endif
   451    452     sqlite3DbFree(db, p);
   452    453   }
................................................................................
  1305   1306       pTab->aCol[iCol].colFlags |= COLFLAG_PRIMKEY;
  1306   1307       zType = pTab->aCol[iCol].zType;
  1307   1308       nTerm = 1;
  1308   1309     }else{
  1309   1310       nTerm = pList->nExpr;
  1310   1311       for(i=0; i<nTerm; i++){
  1311   1312         Expr *pCExpr = sqlite3ExprSkipCollate(pList->a[i].pExpr);
  1312         -      if( pCExpr && pCExpr->op==TK_ID ){
         1313  +      assert( pCExpr!=0 );
         1314  +      if( pCExpr->op==TK_ID ){
  1313   1315           const char *zCName = pCExpr->u.zToken;
  1314   1316           for(iCol=0; iCol<pTab->nCol; iCol++){
  1315   1317             if( sqlite3StrICmp(zCName, pTab->aCol[iCol].zName)==0 ){
  1316   1318               pTab->aCol[iCol].colFlags |= COLFLAG_PRIMKEY;
  1317   1319               zType = pTab->aCol[iCol].zType;
  1318   1320               break;
  1319   1321             }
................................................................................
  2909   2911     DbFixer sFix;        /* For assigning database names to pTable */
  2910   2912     int sortOrderMask;   /* 1 to honor DESC in index.  0 to ignore. */
  2911   2913     sqlite3 *db = pParse->db;
  2912   2914     Db *pDb;             /* The specific table containing the indexed database */
  2913   2915     int iDb;             /* Index of the database that is being written */
  2914   2916     Token *pName = 0;    /* Unqualified name of the index to create */
  2915   2917     struct ExprList_item *pListItem; /* For looping over pList */
  2916         -  const Column *pTabCol;           /* A column in the table */
  2917   2918     int nExtra = 0;                  /* Space allocated for zExtra[] */
  2918   2919     int nExtraCol;                   /* Number of extra columns needed */
  2919   2920     char *zExtra = 0;                /* Extra space after the Index object */
  2920   2921     Index *pPk = 0;      /* PRIMARY KEY index for WITHOUT ROWID tables */
  2921   2922   
  2922   2923     if( db->mallocFailed || IN_DECLARE_VTAB || pParse->nErr>0 ){
  2923   2924       goto exit_create_index;
................................................................................
  3081   3082     }
  3082   3083   
  3083   3084     /* Figure out how many bytes of space are required to store explicitly
  3084   3085     ** specified collation sequence names.
  3085   3086     */
  3086   3087     for(i=0; i<pList->nExpr; i++){
  3087   3088       Expr *pExpr = pList->a[i].pExpr;
  3088         -    if( pExpr && pExpr->op==TK_COLLATE ){
         3089  +    assert( pExpr!=0 );
         3090  +    if( pExpr->op==TK_COLLATE ){
  3089   3091         nExtra += (1 + sqlite3Strlen30(pExpr->u.zToken));
  3090   3092       }
  3091   3093     }
  3092   3094   
  3093   3095     /* 
  3094   3096     ** Allocate the index structure. 
  3095   3097     */
................................................................................
  3122   3124     */
  3123   3125     if( pDb->pSchema->file_format>=4 ){
  3124   3126       sortOrderMask = -1;   /* Honor DESC */
  3125   3127     }else{
  3126   3128       sortOrderMask = 0;    /* Ignore DESC */
  3127   3129     }
  3128   3130   
  3129         -  /* Scan the names of the columns of the table to be indexed and
  3130         -  ** load the column indices into the Index structure.  Report an error
  3131         -  ** if any column is not found.
         3131  +  /* Analyze the list of expressions that form the terms of the index and
         3132  +  ** report any errors.  In the common case where the expression is exactly
         3133  +  ** a table column, store that column in aiColumn[].  For general expressions,
         3134  +  ** populate pIndex->aColExpr and store -2 in aiColumn[].
  3132   3135     **
  3133         -  ** TODO:  Add a test to make sure that the same column is not named
  3134         -  ** more than once within the same index.  Only the first instance of
  3135         -  ** the column will ever be used by the optimizer.  Note that using the
  3136         -  ** same column more than once cannot be an error because that would 
  3137         -  ** break backwards compatibility - it needs to be a warning.
         3136  +  ** TODO: Issue a warning if two or more columns of the index are identical.
         3137  +  ** TODO: Issue a warning if the table primary key is used as part of the
         3138  +  ** index key.
  3138   3139     */
  3139   3140     for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
  3140         -    const char *zColName;
  3141         -    Expr *pCExpr;
  3142         -    int requestedSortOrder;
         3141  +    Expr *pCExpr;                  /* The i-th index expression */
         3142  +    int requestedSortOrder;        /* ASC or DESC on the i-th expression */
  3143   3143       char *zColl;                   /* Collation sequence name */
  3144   3144   
  3145   3145       sqlite3StringToId(pListItem->pExpr);
         3146  +    sqlite3ResolveSelfReference(pParse, pTab, NC_IdxExpr, pListItem->pExpr, 0);
         3147  +    if( pParse->nErr ) goto exit_create_index;
  3146   3148       pCExpr = sqlite3ExprSkipCollate(pListItem->pExpr);
  3147         -    if( pCExpr->op!=TK_ID ){
  3148         -      sqlite3ErrorMsg(pParse, "indexes on expressions not yet supported");
  3149         -      continue;
  3150         -    }
  3151         -    zColName = pCExpr->u.zToken;
  3152         -    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
  3153         -      if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break;
  3154         -    }
  3155         -    if( j>=pTab->nCol ){
  3156         -      sqlite3ErrorMsg(pParse, "table %s has no column named %s",
  3157         -        pTab->zName, zColName);
  3158         -      pParse->checkSchema = 1;
  3159         -      goto exit_create_index;
  3160         -    }
  3161         -    assert( j<=0x7fff );
  3162         -    pIndex->aiColumn[i] = (i16)j;
         3149  +    if( pCExpr->op!=TK_COLUMN ){
         3150  +      if( pTab==pParse->pNewTable ){
         3151  +        sqlite3ErrorMsg(pParse, "expressions prohibited in PRIMARY KEY and "
         3152  +                                "UNIQUE constraints");
         3153  +        goto exit_create_index;
         3154  +      }
         3155  +      if( pIndex->aColExpr==0 ){
         3156  +        ExprList *pCopy = sqlite3ExprListDup(db, pList, 0);
         3157  +        pIndex->aColExpr = pCopy;
         3158  +        if( !db->mallocFailed ){
         3159  +          assert( pCopy!=0 );
         3160  +          pListItem = &pCopy->a[i];
         3161  +        }
         3162  +      }
         3163  +      j = -2;
         3164  +      pIndex->aiColumn[i] = -2;
         3165  +      pIndex->uniqNotNull = 0;
         3166  +    }else{
         3167  +      j = pCExpr->iColumn;
         3168  +      assert( j<=0x7fff );
         3169  +      if( j<0 ){
         3170  +        j = pTab->iPKey;
         3171  +      }else if( pTab->aCol[j].notNull==0 ){
         3172  +        pIndex->uniqNotNull = 0;
         3173  +      }
         3174  +      pIndex->aiColumn[i] = (i16)j;
         3175  +    }
         3176  +    zColl = 0;
  3163   3177       if( pListItem->pExpr->op==TK_COLLATE ){
  3164   3178         int nColl;
  3165   3179         zColl = pListItem->pExpr->u.zToken;
  3166   3180         nColl = sqlite3Strlen30(zColl) + 1;
  3167   3181         assert( nExtra>=nColl );
  3168   3182         memcpy(zExtra, zColl, nColl);
  3169   3183         zColl = zExtra;
  3170   3184         zExtra += nColl;
  3171   3185         nExtra -= nColl;
  3172         -    }else{
         3186  +    }else if( j>=0 ){
  3173   3187         zColl = pTab->aCol[j].zColl;
  3174         -      if( !zColl ) zColl = "BINARY";
  3175   3188       }
         3189  +    if( !zColl ) zColl = "BINARY";
  3176   3190       if( !db->init.busy && !sqlite3LocateCollSeq(pParse, zColl) ){
  3177   3191         goto exit_create_index;
  3178   3192       }
  3179   3193       pIndex->azColl[i] = zColl;
  3180   3194       requestedSortOrder = pListItem->sortOrder & sortOrderMask;
  3181   3195       pIndex->aSortOrder[i] = (u8)requestedSortOrder;
  3182         -    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  3183   3196     }
         3197  +
         3198  +  /* Append the table key to the end of the index.  For WITHOUT ROWID
         3199  +  ** tables (when pPk!=0) this will be the declared PRIMARY KEY.  For
         3200  +  ** normal tables (when pPk==0) this will be the rowid.
         3201  +  */
  3184   3202     if( pPk ){
  3185   3203       for(j=0; j<pPk->nKeyCol; j++){
  3186   3204         int x = pPk->aiColumn[j];
         3205  +      assert( x>=0 );
  3187   3206         if( hasColumn(pIndex->aiColumn, pIndex->nKeyCol, x) ){
  3188   3207           pIndex->nColumn--; 
  3189   3208         }else{
  3190   3209           pIndex->aiColumn[i] = x;
  3191   3210           pIndex->azColl[i] = pPk->azColl[j];
  3192   3211           pIndex->aSortOrder[i] = pPk->aSortOrder[j];
  3193   3212           i++;
................................................................................
  3230   3249         assert( pIdx->idxType!=SQLITE_IDXTYPE_APPDEF );
  3231   3250         assert( IsUniqueIndex(pIndex) );
  3232   3251   
  3233   3252         if( pIdx->nKeyCol!=pIndex->nKeyCol ) continue;
  3234   3253         for(k=0; k<pIdx->nKeyCol; k++){
  3235   3254           const char *z1;
  3236   3255           const char *z2;
         3256  +        assert( pIdx->aiColumn[k]>=0 );
  3237   3257           if( pIdx->aiColumn[k]!=pIndex->aiColumn[k] ) break;
  3238   3258           z1 = pIdx->azColl[k];
  3239   3259           z2 = pIndex->azColl[k];
  3240   3260           if( z1!=z2 && sqlite3StrICmp(z1, z2) ) break;
  3241   3261         }
  3242   3262         if( k==pIdx->nKeyCol ){
  3243   3263           if( pIdx->onError!=pIndex->onError ){
................................................................................
  3261   3281         }
  3262   3282       }
  3263   3283     }
  3264   3284   
  3265   3285     /* Link the new Index structure to its table and to the other
  3266   3286     ** in-memory database structures. 
  3267   3287     */
         3288  +  assert( pParse->nErr==0 );
  3268   3289     if( db->init.busy ){
  3269   3290       Index *p;
  3270   3291       assert( sqlite3SchemaMutexHeld(db, 0, pIndex->pSchema) );
  3271   3292       p = sqlite3HashInsert(&pIndex->pSchema->idxHash, 
  3272   3293                             pIndex->zName, pIndex);
  3273   3294       if( p ){
  3274   3295         assert( p==pIndex );  /* Malloc must have failed */
................................................................................
  3290   3311     ** of a WITHOUT ROWID table.
  3291   3312     **
  3292   3313     ** If pTblName==0 it means this index is generated as an implied PRIMARY KEY
  3293   3314     ** or UNIQUE index in a CREATE TABLE statement.  Since the table
  3294   3315     ** has just been created, it contains no data and the index initialization
  3295   3316     ** step can be skipped.
  3296   3317     */
  3297         -  else if( pParse->nErr==0 && (HasRowid(pTab) || pTblName!=0) ){
         3318  +  else if( HasRowid(pTab) || pTblName!=0 ){
  3298   3319       Vdbe *v;
  3299   3320       char *zStmt;
  3300   3321       int iMem = ++pParse->nMem;
  3301   3322   
  3302   3323       v = sqlite3GetVdbe(pParse);
  3303   3324       if( v==0 ) goto exit_create_index;
  3304   3325   
................................................................................
  4120   4141   ){
  4121   4142     char *zErr;
  4122   4143     int j;
  4123   4144     StrAccum errMsg;
  4124   4145     Table *pTab = pIdx->pTable;
  4125   4146   
  4126   4147     sqlite3StrAccumInit(&errMsg, pParse->db, 0, 0, 200);
  4127         -  for(j=0; j<pIdx->nKeyCol; j++){
  4128         -    char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
  4129         -    if( j ) sqlite3StrAccumAppend(&errMsg, ", ", 2);
  4130         -    sqlite3StrAccumAppendAll(&errMsg, pTab->zName);
  4131         -    sqlite3StrAccumAppend(&errMsg, ".", 1);
  4132         -    sqlite3StrAccumAppendAll(&errMsg, zCol);
         4148  +  if( pIdx->aColExpr ){
         4149  +    sqlite3XPrintf(&errMsg, 0, "index '%q'", pIdx->zName);
         4150  +  }else{
         4151  +    for(j=0; j<pIdx->nKeyCol; j++){
         4152  +      char *zCol;
         4153  +      assert( pIdx->aiColumn[j]>=0 );
         4154  +      zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
         4155  +      if( j ) sqlite3StrAccumAppend(&errMsg, ", ", 2);
         4156  +      sqlite3XPrintf(&errMsg, 0, "%s.%s", pTab->zName, zCol);
         4157  +    }
  4133   4158     }
  4134   4159     zErr = sqlite3StrAccumFinish(&errMsg);
  4135   4160     sqlite3HaltConstraint(pParse, 
  4136   4161       IsPrimaryKeyIndex(pIdx) ? SQLITE_CONSTRAINT_PRIMARYKEY 
  4137   4162                               : SQLITE_CONSTRAINT_UNIQUE,
  4138   4163       onError, zErr, P4_DYNAMIC, P5_ConstraintUnique);
  4139   4164   }

Changes to src/date.c.

  1111   1111   ** This function registered all of the above C functions as SQL
  1112   1112   ** functions.  This should be the only routine in this file with
  1113   1113   ** external linkage.
  1114   1114   */
  1115   1115   void sqlite3RegisterDateTimeFunctions(void){
  1116   1116     static SQLITE_WSD FuncDef aDateTimeFuncs[] = {
  1117   1117   #ifndef SQLITE_OMIT_DATETIME_FUNCS
  1118         -    FUNCTION(julianday,        -1, 0, 0, juliandayFunc ),
  1119         -    FUNCTION(date,             -1, 0, 0, dateFunc      ),
  1120         -    FUNCTION(time,             -1, 0, 0, timeFunc      ),
  1121         -    FUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),
  1122         -    FUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),
  1123         -    FUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
  1124         -    FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
  1125         -    FUNCTION(current_date,      0, 0, 0, cdateFunc     ),
         1118  +    DFUNCTION(julianday,        -1, 0, 0, juliandayFunc ),
         1119  +    DFUNCTION(date,             -1, 0, 0, dateFunc      ),
         1120  +    DFUNCTION(time,             -1, 0, 0, timeFunc      ),
         1121  +    DFUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),
         1122  +    DFUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),
         1123  +    DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
         1124  +    DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
         1125  +    DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
  1126   1126   #else
  1127   1127       STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
  1128   1128       STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
  1129   1129       STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
  1130   1130   #endif
  1131   1131     };
  1132   1132     int i;

Changes to src/delete.c.

   407    407       if( db->flags & SQLITE_CountRows ){
   408    408         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   409    409       }
   410    410     
   411    411       /* Extract the rowid or primary key for the current row */
   412    412       if( pPk ){
   413    413         for(i=0; i<nPk; i++){
          414  +        assert( pPk->aiColumn[i]>=(-1) );
   414    415           sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur,
   415    416                                           pPk->aiColumn[i], iPk+i);
   416    417         }
   417    418         iKey = iPk;
   418    419       }else{
   419    420         iKey = pParse->nMem + 1;
   420    421         iKey = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iKey, 0);
................................................................................
   785    786     int prefixOnly,      /* Compute only a unique prefix of the key */
   786    787     int *piPartIdxLabel, /* OUT: Jump to this label to skip partial index */
   787    788     Index *pPrior,       /* Previously generated index key */
   788    789     int regPrior         /* Register holding previous generated key */
   789    790   ){
   790    791     Vdbe *v = pParse->pVdbe;
   791    792     int j;
   792         -  Table *pTab = pIdx->pTable;
   793    793     int regBase;
   794    794     int nCol;
   795    795   
   796    796     if( piPartIdxLabel ){
   797    797       if( pIdx->pPartIdxWhere ){
   798    798         *piPartIdxLabel = sqlite3VdbeMakeLabel(v);
   799         -      pParse->iPartIdxTab = iDataCur;
          799  +      pParse->iSelfTab = iDataCur;
   800    800         sqlite3ExprCachePush(pParse);
   801    801         sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel, 
   802    802                               SQLITE_JUMPIFNULL);
   803    803       }else{
   804    804         *piPartIdxLabel = 0;
   805    805       }
   806    806     }
   807    807     nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn;
   808    808     regBase = sqlite3GetTempRange(pParse, nCol);
   809    809     if( pPrior && (regBase!=regPrior || pPrior->pPartIdxWhere) ) pPrior = 0;
   810    810     for(j=0; j<nCol; j++){
   811         -    if( pPrior && pPrior->aiColumn[j]==pIdx->aiColumn[j] ) continue;
   812         -    sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pIdx->aiColumn[j],
   813         -                                    regBase+j);
          811  +    if( pPrior
          812  +     && pPrior->aiColumn[j]==pIdx->aiColumn[j]
          813  +     && pPrior->aiColumn[j]>=(-1)
          814  +    ){
          815  +      /* This column was already computed by the previous index */
          816  +      continue;
          817  +    }
          818  +    sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iDataCur, j, regBase+j);
   814    819       /* If the column affinity is REAL but the number is an integer, then it
   815    820       ** might be stored in the table as an integer (using a compact
   816    821       ** representation) then converted to REAL by an OP_RealAffinity opcode.
   817    822       ** But we are getting ready to store this value back into an index, where
   818    823       ** it should be converted by to INTEGER again.  So omit the OP_RealAffinity
   819    824       ** opcode if it is present */
   820    825       sqlite3VdbeDeletePriorOpcode(v, OP_RealAffinity);

Changes to src/expr.c.

  2427   2427     struct yColCache *p;
  2428   2428     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2429   2429       if( p->iReg==iReg ){
  2430   2430         p->tempReg = 0;
  2431   2431       }
  2432   2432     }
  2433   2433   }
         2434  +
         2435  +/* Generate code that will load into register regOut a value that is
         2436  +** appropriate for the iIdxCol-th column of index pIdx.
         2437  +*/
         2438  +void sqlite3ExprCodeLoadIndexColumn(
         2439  +  Parse *pParse,  /* The parsing context */
         2440  +  Index *pIdx,    /* The index whose column is to be loaded */
         2441  +  int iTabCur,    /* Cursor pointing to a table row */
         2442  +  int iIdxCol,    /* The column of the index to be loaded */
         2443  +  int regOut      /* Store the index column value in this register */
         2444  +){
         2445  +  i16 iTabCol = pIdx->aiColumn[iIdxCol];
         2446  +  if( iTabCol>=(-1) ){
         2447  +    sqlite3ExprCodeGetColumnOfTable(pParse->pVdbe, pIdx->pTable, iTabCur,
         2448  +                                    iTabCol, regOut);
         2449  +    return;
         2450  +  }
         2451  +  assert( pIdx->aColExpr );
         2452  +  assert( pIdx->aColExpr->nExpr>iIdxCol );
         2453  +  pParse->iSelfTab = iTabCur;
         2454  +  sqlite3ExprCode(pParse, pIdx->aColExpr->a[iIdxCol].pExpr, regOut);
         2455  +}
  2434   2456   
  2435   2457   /*
  2436   2458   ** Generate code to extract the value of the iCol-th column of a table.
  2437   2459   */
  2438   2460   void sqlite3ExprCodeGetColumnOfTable(
  2439   2461     Vdbe *v,        /* The VDBE under construction */
  2440   2462     Table *pTab,    /* The table containing the value */
................................................................................
  2613   2635         int iTab = pExpr->iTable;
  2614   2636         if( iTab<0 ){
  2615   2637           if( pParse->ckBase>0 ){
  2616   2638             /* Generating CHECK constraints or inserting into partial index */
  2617   2639             inReg = pExpr->iColumn + pParse->ckBase;
  2618   2640             break;
  2619   2641           }else{
  2620         -          /* Deleting from a partial index */
  2621         -          iTab = pParse->iPartIdxTab;
         2642  +          /* Coding an expression that is part of an index where column names
         2643  +          ** in the index refer to the table to which the index belongs */
         2644  +          iTab = pParse->iSelfTab;
  2622   2645           }
  2623   2646         }
  2624   2647         inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
  2625   2648                                  pExpr->iColumn, iTab, target,
  2626   2649                                  pExpr->op2);
  2627   2650         break;
  2628   2651       }
................................................................................
  3761   3784       }
  3762   3785       if( pB->op==TK_COLLATE && sqlite3ExprCompare(pA, pB->pLeft, iTab)<2 ){
  3763   3786         return 1;
  3764   3787       }
  3765   3788       return 2;
  3766   3789     }
  3767   3790     if( pA->op!=TK_COLUMN && ALWAYS(pA->op!=TK_AGG_COLUMN) && pA->u.zToken ){
  3768         -    if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
         3791  +    if( pA->op==TK_FUNCTION ){
         3792  +      if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2;
         3793  +    }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
  3769   3794         return pA->op==TK_COLLATE ? 1 : 2;
  3770   3795       }
  3771   3796     }
  3772   3797     if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
  3773   3798     if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){
  3774   3799       if( combinedFlags & EP_xIsSelect ) return 2;
  3775   3800       if( sqlite3ExprCompare(pA->pLeft, pB->pLeft, iTab) ) return 2;

Changes to src/func.c.

  1733   1733       FUNCTION2(ifnull,            2, 0, 0, noopFunc,  SQLITE_FUNC_COALESCE),
  1734   1734       FUNCTION2(unlikely,          1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1735   1735       FUNCTION2(likelihood,        2, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1736   1736       FUNCTION2(likely,            1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1737   1737       VFUNCTION(random,            0, 0, 0, randomFunc       ),
  1738   1738       VFUNCTION(randomblob,        1, 0, 0, randomBlob       ),
  1739   1739       FUNCTION(nullif,             2, 0, 1, nullifFunc       ),
  1740         -    FUNCTION(sqlite_version,     0, 0, 0, versionFunc      ),
  1741         -    FUNCTION(sqlite_source_id,   0, 0, 0, sourceidFunc     ),
         1740  +    DFUNCTION(sqlite_version,    0, 0, 0, versionFunc      ),
         1741  +    DFUNCTION(sqlite_source_id,  0, 0, 0, sourceidFunc     ),
  1742   1742       FUNCTION(sqlite_log,         2, 0, 0, errlogFunc       ),
  1743   1743   #if SQLITE_USER_AUTHENTICATION
  1744   1744       FUNCTION(sqlite_crypt,       2, 0, 0, sqlite3CryptFunc ),
  1745   1745   #endif
  1746   1746   #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
  1747         -    FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc  ),
  1748         -    FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc  ),
         1747  +    DFUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc  ),
         1748  +    DFUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc  ),
  1749   1749   #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
  1750   1750       FUNCTION(quote,              1, 0, 0, quoteFunc        ),
  1751   1751       VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
  1752   1752       VFUNCTION(changes,           0, 0, 0, changes          ),
  1753   1753       VFUNCTION(total_changes,     0, 0, 0, total_changes    ),
  1754   1754       FUNCTION(replace,            3, 0, 0, replaceFunc      ),
  1755   1755       FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
  1756   1756     #ifdef SQLITE_SOUNDEX
  1757   1757       FUNCTION(soundex,            1, 0, 0, soundexFunc      ),
  1758   1758     #endif
  1759   1759     #ifndef SQLITE_OMIT_LOAD_EXTENSION
  1760         -    FUNCTION(load_extension,     1, 0, 0, loadExt          ),
  1761         -    FUNCTION(load_extension,     2, 0, 0, loadExt          ),
         1760  +    VFUNCTION(load_extension,    1, 0, 0, loadExt          ),
         1761  +    VFUNCTION(load_extension,    2, 0, 0, loadExt          ),
  1762   1762     #endif
  1763   1763       AGGREGATE(sum,               1, 0, 0, sumStep,         sumFinalize    ),
  1764   1764       AGGREGATE(total,             1, 0, 0, sumStep,         totalFinalize    ),
  1765   1765       AGGREGATE(avg,               1, 0, 0, sumStep,         avgFinalize    ),
  1766   1766       AGGREGATE2(count,            0, 0, 0, countStep,       countFinalize,
  1767   1767                  SQLITE_FUNC_COUNT  ),
  1768   1768       AGGREGATE(count,             1, 0, 0, countStep,       countFinalize  ),

Changes to src/insert.c.

    84     84       pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);
    85     85       if( !pIdx->zColAff ){
    86     86         db->mallocFailed = 1;
    87     87         return 0;
    88     88       }
    89     89       for(n=0; n<pIdx->nColumn; n++){
    90     90         i16 x = pIdx->aiColumn[n];
    91         -      pIdx->zColAff[n] = x<0 ? SQLITE_AFF_INTEGER : pTab->aCol[x].affinity;
           91  +      if( x>=0 ){
           92  +        pIdx->zColAff[n] = pTab->aCol[x].affinity;
           93  +      }else if( x==(-1) ){
           94  +        pIdx->zColAff[n] = SQLITE_AFF_INTEGER;
           95  +      }else{
           96  +        char aff;
           97  +        assert( x==(-2) );
           98  +        assert( pIdx->aColExpr!=0 );
           99  +        aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
          100  +        if( aff==0 ) aff = SQLITE_AFF_BLOB;
          101  +        pIdx->zColAff[n] = aff;
          102  +      }
    92    103       }
    93    104       pIdx->zColAff[n] = 0;
    94    105     }
    95    106    
    96    107     return pIdx->zColAff;
    97    108   }
    98    109   
................................................................................
  1390   1401       /* Create a record for this index entry as it should appear after
  1391   1402       ** the insert or update.  Store that record in the aRegIdx[ix] register
  1392   1403       */
  1393   1404       regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn);
  1394   1405       for(i=0; i<pIdx->nColumn; i++){
  1395   1406         int iField = pIdx->aiColumn[i];
  1396   1407         int x;
  1397         -      if( iField<0 || iField==pTab->iPKey ){
  1398         -        if( regRowid==regIdx+i ) continue; /* ROWID already in regIdx+i */
  1399         -        x = regNewData;
  1400         -        regRowid =  pIdx->pPartIdxWhere ? -1 : regIdx+i;
         1408  +      if( iField==(-2) ){
         1409  +        pParse->ckBase = regNewData+1;
         1410  +        sqlite3ExprCode(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i);
         1411  +        pParse->ckBase = 0;
         1412  +        VdbeComment((v, "%s column %d", pIdx->zName, i));
  1401   1413         }else{
  1402         -        x = iField + regNewData + 1;
         1414  +        if( iField==(-1) || iField==pTab->iPKey ){
         1415  +          if( regRowid==regIdx+i ) continue; /* ROWID already in regIdx+i */
         1416  +          x = regNewData;
         1417  +          regRowid =  pIdx->pPartIdxWhere ? -1 : regIdx+i;
         1418  +        }else{
         1419  +          x = iField + regNewData + 1;
         1420  +        }
         1421  +        sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i);
         1422  +        VdbeComment((v, "%s", iField<0 ? "rowid" : pTab->aCol[iField].zName));
  1403   1423         }
  1404         -      sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i);
  1405         -      VdbeComment((v, "%s", iField<0 ? "rowid" : pTab->aCol[iField].zName));
  1406   1424       }
  1407   1425       sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]);
  1408   1426       VdbeComment((v, "for %s", pIdx->zName));
  1409   1427       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn);
  1410   1428   
  1411   1429       /* In an UPDATE operation, if this index is the PRIMARY KEY index 
  1412   1430       ** of a WITHOUT ROWID table and there has been no change the
................................................................................
  1718   1736     }
  1719   1737     if( pDest->onError!=pSrc->onError ){
  1720   1738       return 0;   /* Different conflict resolution strategies */
  1721   1739     }
  1722   1740     for(i=0; i<pSrc->nKeyCol; i++){
  1723   1741       if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
  1724   1742         return 0;   /* Different columns indexed */
         1743  +    }
         1744  +    if( pSrc->aiColumn[i]==(-2) ){
         1745  +      assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 );
         1746  +      if( sqlite3ExprCompare(pSrc->aColExpr->a[i].pExpr,
         1747  +                             pDest->aColExpr->a[i].pExpr, -1)!=0 ){
         1748  +        return 0;   /* Different expressions in the index */
         1749  +      }
  1725   1750       }
  1726   1751       if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
  1727   1752         return 0;   /* Different sort orders */
  1728   1753       }
  1729   1754       if( !xferCompatibleCollation(pSrc->azColl[i],pDest->azColl[i]) ){
  1730   1755         return 0;   /* Different collating sequences */
  1731   1756       }

Changes to src/resolve.c.

   543    543       }
   544    544       ExprSetProperty(p, EP_Resolved);
   545    545     }
   546    546     return p;
   547    547   }
   548    548   
   549    549   /*
   550         -** Report an error that an expression is not valid for a partial index WHERE
   551         -** clause.
          550  +** Report an error that an expression is not valid for some set of
          551  +** pNC->ncFlags values determined by validMask.
   552    552   */
   553         -static void notValidPartIdxWhere(
          553  +static void notValid(
   554    554     Parse *pParse,       /* Leave error message here */
   555    555     NameContext *pNC,    /* The name context */
   556         -  const char *zMsg     /* Type of error */
          556  +  const char *zMsg,    /* Type of error */
          557  +  int validMask        /* Set of contexts for which prohibited */
   557    558   ){
   558         -  if( (pNC->ncFlags & NC_PartIdx)!=0 ){
   559         -    sqlite3ErrorMsg(pParse, "%s prohibited in partial index WHERE clauses",
   560         -                    zMsg);
   561         -  }
   562         -}
   563         -
          559  +  assert( (validMask&~(NC_IsCheck|NC_PartIdx|NC_IdxExpr))==0 );
          560  +  if( (pNC->ncFlags & validMask)!=0 ){
          561  +    const char *zIn = "partial index WHERE clauses";
          562  +    if( pNC->ncFlags & NC_IdxExpr )      zIn = "index expressions";
   564    563   #ifndef SQLITE_OMIT_CHECK
   565         -/*
   566         -** Report an error that an expression is not valid for a CHECK constraint.
   567         -*/
   568         -static void notValidCheckConstraint(
   569         -  Parse *pParse,       /* Leave error message here */
   570         -  NameContext *pNC,    /* The name context */
   571         -  const char *zMsg     /* Type of error */
   572         -){
   573         -  if( (pNC->ncFlags & NC_IsCheck)!=0 ){
   574         -    sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg);
   575         -  }
   576         -}
   577         -#else
   578         -# define notValidCheckConstraint(P,N,M)
          564  +    else if( pNC->ncFlags & NC_IsCheck ) zIn = "CHECK constraints";
   579    565   #endif
          566  +    sqlite3ErrorMsg(pParse, "%s prohibited in %s", zMsg, zIn);
          567  +  }
          568  +}
   580    569   
   581    570   /*
   582    571   ** Expression p should encode a floating point value between 1.0 and 0.0.
   583    572   ** Return 1024 times this value.  Or return -1 if p is not a floating point
   584    573   ** value between 1.0 and 0.0.
   585    574   */
   586    575   static int exprProbability(Expr *p){
................................................................................
   657    646       case TK_DOT: {
   658    647         const char *zColumn;
   659    648         const char *zTable;
   660    649         const char *zDb;
   661    650         Expr *pRight;
   662    651   
   663    652         /* if( pSrcList==0 ) break; */
          653  +      notValid(pParse, pNC, "the \".\" operator", NC_IdxExpr);
          654  +      /*notValid(pParse, pNC, "the \".\" operator", NC_PartIdx|NC_IsCheck, 1);*/
   664    655         pRight = pExpr->pRight;
   665    656         if( pRight->op==TK_ID ){
   666    657           zDb = 0;
   667    658           zTable = pExpr->pLeft->u.zToken;
   668    659           zColumn = pRight->u.zToken;
   669    660         }else{
   670    661           assert( pRight->op==TK_DOT );
................................................................................
   686    677         int auth;                   /* Authorization to use the function */
   687    678         int nId;                    /* Number of characters in function name */
   688    679         const char *zId;            /* The function name. */
   689    680         FuncDef *pDef;              /* Information about the function */
   690    681         u8 enc = ENC(pParse->db);   /* The database encoding */
   691    682   
   692    683         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
   693         -      notValidPartIdxWhere(pParse, pNC, "functions");
          684  +      notValid(pParse, pNC, "functions", NC_PartIdx);
   694    685         zId = pExpr->u.zToken;
   695    686         nId = sqlite3Strlen30(zId);
   696    687         pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
   697    688         if( pDef==0 ){
   698    689           pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
   699    690           if( pDef==0 ){
   700    691             no_such_func = 1;
................................................................................
   734    725                                       pDef->zName);
   735    726               pNC->nErr++;
   736    727             }
   737    728             pExpr->op = TK_NULL;
   738    729             return WRC_Prune;
   739    730           }
   740    731   #endif
   741         -        if( pDef->funcFlags & SQLITE_FUNC_CONSTANT ){
          732  +        if( pDef->funcFlags & (SQLITE_FUNC_CONSTANT|SQLITE_FUNC_SLOCHNG) ){
          733  +          /* For the purposes of the EP_ConstFunc flag, date and time
          734  +          ** functions and other functions that change slowly are considered
          735  +          ** constant because they are constant for the duration of one query */
   742    736             ExprSetProperty(pExpr,EP_ConstFunc);
   743    737           }
          738  +        if( (pDef->funcFlags & SQLITE_FUNC_CONSTANT)==0 ){
          739  +          /* Date/time functions that use 'now', and other functions like
          740  +          ** sqlite_version() that might change over time cannot be used
          741  +          ** in an index. */
          742  +          notValid(pParse, pNC, "non-deterministic functions", NC_IdxExpr);
          743  +        }
   744    744         }
   745    745         if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){
   746    746           sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
   747    747           pNC->nErr++;
   748    748           is_agg = 0;
   749    749         }else if( no_such_func && pParse->db->init.busy==0 ){
   750    750           sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
................................................................................
   782    782       case TK_SELECT:
   783    783       case TK_EXISTS:  testcase( pExpr->op==TK_EXISTS );
   784    784   #endif
   785    785       case TK_IN: {
   786    786         testcase( pExpr->op==TK_IN );
   787    787         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
   788    788           int nRef = pNC->nRef;
   789         -        notValidCheckConstraint(pParse, pNC, "subqueries");
   790         -        notValidPartIdxWhere(pParse, pNC, "subqueries");
          789  +        notValid(pParse, pNC, "subqueries", NC_IsCheck|NC_PartIdx|NC_IdxExpr);
   791    790           sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
   792    791           assert( pNC->nRef>=nRef );
   793    792           if( nRef!=pNC->nRef ){
   794    793             ExprSetProperty(pExpr, EP_VarSelect);
   795    794           }
   796    795         }
   797    796         break;
   798    797       }
   799    798       case TK_VARIABLE: {
   800         -      notValidCheckConstraint(pParse, pNC, "parameters");
   801         -      notValidPartIdxWhere(pParse, pNC, "parameters");
          799  +      notValid(pParse, pNC, "parameters", NC_IsCheck|NC_PartIdx|NC_IdxExpr);
   802    800         break;
   803    801       }
   804    802     }
   805    803     return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
   806    804   }
   807    805   
   808    806   /*
................................................................................
  1497   1495   ** is set to -1 and the Expr.iColumn value is set to the column number.
  1498   1496   **
  1499   1497   ** Any errors cause an error message to be set in pParse.
  1500   1498   */
  1501   1499   void sqlite3ResolveSelfReference(
  1502   1500     Parse *pParse,      /* Parsing context */
  1503   1501     Table *pTab,        /* The table being referenced */
  1504         -  int type,           /* NC_IsCheck or NC_PartIdx */
         1502  +  int type,           /* NC_IsCheck or NC_PartIdx or NC_IdxExpr */
  1505   1503     Expr *pExpr,        /* Expression to resolve.  May be NULL. */
  1506   1504     ExprList *pList     /* Expression list to resolve.  May be NUL. */
  1507   1505   ){
  1508   1506     SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
  1509   1507     NameContext sNC;                /* Name context for pParse->pNewTable */
  1510   1508   
  1511         -  assert( type==NC_IsCheck || type==NC_PartIdx );
         1509  +  assert( type==NC_IsCheck || type==NC_PartIdx || type==NC_IdxExpr );
  1512   1510     memset(&sNC, 0, sizeof(sNC));
  1513   1511     memset(&sSrc, 0, sizeof(sSrc));
  1514   1512     sSrc.nSrc = 1;
  1515   1513     sSrc.a[0].zName = pTab->zName;
  1516   1514     sSrc.a[0].pTab = pTab;
  1517   1515     sSrc.a[0].iCursor = -1;
  1518   1516     sNC.pParse = pParse;
  1519   1517     sNC.pSrcList = &sSrc;
  1520   1518     sNC.ncFlags = type;
  1521   1519     if( sqlite3ResolveExprNames(&sNC, pExpr) ) return;
  1522   1520     if( pList ) sqlite3ResolveExprListNames(&sNC, pList);
  1523   1521   }

Changes to src/shell.c.

  4248   4248       }
  4249   4249     }
  4250   4250     if( nSql ){
  4251   4251       if( !_all_whitespace(zSql) ){
  4252   4252         fprintf(stderr, "Error: incomplete SQL: %s\n", zSql);
  4253   4253         errCnt++;
  4254   4254       }
  4255         -    free(zSql);
  4256   4255     }
         4256  +  free(zSql);
  4257   4257     free(zLine);
  4258   4258     return errCnt>0;
  4259   4259   }
  4260   4260   
  4261   4261   /*
  4262   4262   ** Return a pathname which is the user's home directory.  A
  4263   4263   ** 0 return indicates an error of some kind.

Changes to src/sqliteInt.h.

  1380   1380   };
  1381   1381   
  1382   1382   /*
  1383   1383   ** Possible values for FuncDef.flags.  Note that the _LENGTH and _TYPEOF
  1384   1384   ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG.  There
  1385   1385   ** are assert() statements in the code to verify this.
  1386   1386   */
  1387         -#define SQLITE_FUNC_ENCMASK  0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
  1388         -#define SQLITE_FUNC_LIKE     0x004 /* Candidate for the LIKE optimization */
  1389         -#define SQLITE_FUNC_CASE     0x008 /* Case-sensitive LIKE-type function */
  1390         -#define SQLITE_FUNC_EPHEM    0x010 /* Ephemeral.  Delete with VDBE */
  1391         -#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */
  1392         -#define SQLITE_FUNC_LENGTH   0x040 /* Built-in length() function */
  1393         -#define SQLITE_FUNC_TYPEOF   0x080 /* Built-in typeof() function */
  1394         -#define SQLITE_FUNC_COUNT    0x100 /* Built-in count(*) aggregate */
  1395         -#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */
  1396         -#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */
  1397         -#define SQLITE_FUNC_CONSTANT 0x800 /* Constant inputs give a constant output */
  1398         -#define SQLITE_FUNC_MINMAX  0x1000 /* True for min() and max() aggregates */
         1387  +#define SQLITE_FUNC_ENCMASK  0x0003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
         1388  +#define SQLITE_FUNC_LIKE     0x0004 /* Candidate for the LIKE optimization */
         1389  +#define SQLITE_FUNC_CASE     0x0008 /* Case-sensitive LIKE-type function */
         1390  +#define SQLITE_FUNC_EPHEM    0x0010 /* Ephemeral.  Delete with VDBE */
         1391  +#define SQLITE_FUNC_NEEDCOLL 0x0020 /* sqlite3GetFuncCollSeq() might be called*/
         1392  +#define SQLITE_FUNC_LENGTH   0x0040 /* Built-in length() function */
         1393  +#define SQLITE_FUNC_TYPEOF   0x0080 /* Built-in typeof() function */
         1394  +#define SQLITE_FUNC_COUNT    0x0100 /* Built-in count(*) aggregate */
         1395  +#define SQLITE_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */
         1396  +#define SQLITE_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */
         1397  +#define SQLITE_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */
         1398  +#define SQLITE_FUNC_MINMAX   0x1000 /* True for min() and max() aggregates */
         1399  +#define SQLITE_FUNC_SLOCHNG  0x2000 /* "Slow Change". Value constant during a
         1400  +                                    ** single query - might change over time */
  1399   1401   
  1400   1402   /*
  1401   1403   ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
  1402   1404   ** used to create the initializers for the FuncDef structures.
  1403   1405   **
  1404   1406   **   FUNCTION(zName, nArg, iArg, bNC, xFunc)
  1405   1407   **     Used to create a scalar function definition of a function zName 
................................................................................
  1406   1408   **     implemented by C function xFunc that accepts nArg arguments. The
  1407   1409   **     value passed as iArg is cast to a (void*) and made available
  1408   1410   **     as the user-data (sqlite3_user_data()) for the function. If 
  1409   1411   **     argument bNC is true, then the SQLITE_FUNC_NEEDCOLL flag is set.
  1410   1412   **
  1411   1413   **   VFUNCTION(zName, nArg, iArg, bNC, xFunc)
  1412   1414   **     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag.
         1415  +**
         1416  +**   DFUNCTION(zName, nArg, iArg, bNC, xFunc)
         1417  +**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and
         1418  +**     adds the SQLITE_FUNC_SLOCHNG flag.  Used for date & time functions
         1419  +**     and functions like sqlite_version() that can change, but not during
         1420  +**     a single query.
  1413   1421   **
  1414   1422   **   AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal)
  1415   1423   **     Used to create an aggregate function definition implemented by
  1416   1424   **     the C functions xStep and xFinal. The first four parameters
  1417   1425   **     are interpreted in the same way as the first 4 parameters to
  1418   1426   **     FUNCTION().
  1419   1427   **
................................................................................
  1427   1435   */
  1428   1436   #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1429   1437     {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1430   1438      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
  1431   1439   #define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1432   1440     {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1433   1441      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
         1442  +#define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \
         1443  +  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
         1444  +   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
  1434   1445   #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  1435   1446     {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\
  1436   1447      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
  1437   1448   #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  1438         -  {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
         1449  +  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1439   1450      pArg, 0, xFunc, 0, 0, #zName, 0, 0}
  1440   1451   #define LIKEFUNC(zName, nArg, arg, flags) \
  1441   1452     {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|flags, \
  1442   1453      (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0}
  1443   1454   #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \
  1444   1455     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
  1445   1456      SQLITE_INT_TO_PTR(arg), 0, 0, xStep,xFinal,#zName,0,0}
................................................................................
  1867   1878     Table *pTable;           /* The SQL table being indexed */
  1868   1879     char *zColAff;           /* String defining the affinity of each column */
  1869   1880     Index *pNext;            /* The next index associated with the same table */
  1870   1881     Schema *pSchema;         /* Schema containing this index */
  1871   1882     u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  1872   1883     char **azColl;           /* Array of collation sequence names for index */
  1873   1884     Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
         1885  +  ExprList *aColExpr;      /* Column expressions */
  1874   1886     int tnum;                /* DB Page containing root of this index */
  1875   1887     LogEst szIdxRow;         /* Estimated average row size in bytes */
  1876   1888     u16 nKeyCol;             /* Number of columns forming the key */
  1877   1889     u16 nColumn;             /* Number of columns stored in the index */
  1878   1890     u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  1879   1891     unsigned idxType:2;      /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  1880   1892     unsigned bUnordered:1;   /* Use this index for == or IN queries only */
................................................................................
  2116   2128   #define EP_Skip      0x001000 /* COLLATE, AS, or UNLIKELY */
  2117   2129   #define EP_Reduced   0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
  2118   2130   #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
  2119   2131   #define EP_Static    0x008000 /* Held in memory not obtained from malloc() */
  2120   2132   #define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
  2121   2133   #define EP_NoReduce  0x020000 /* Cannot EXPRDUP_REDUCE this Expr */
  2122   2134   #define EP_Unlikely  0x040000 /* unlikely() or likelihood() function */
  2123         -#define EP_ConstFunc 0x080000 /* Node is a SQLITE_FUNC_CONSTANT function */
         2135  +#define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
  2124   2136   #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
  2125   2137   #define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
  2126   2138   
  2127   2139   /*
  2128   2140   ** Combinations of two or more EP_* flags
  2129   2141   */
  2130   2142   #define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */
................................................................................
  2387   2399   ** 
  2388   2400   */
  2389   2401   #define NC_AllowAgg  0x0001  /* Aggregate functions are allowed here */
  2390   2402   #define NC_HasAgg    0x0002  /* One or more aggregate functions seen */
  2391   2403   #define NC_IsCheck   0x0004  /* True if resolving names in a CHECK constraint */
  2392   2404   #define NC_InAggFunc 0x0008  /* True if analyzing arguments to an agg func */
  2393   2405   #define NC_PartIdx   0x0010  /* True if resolving a partial index WHERE */
         2406  +#define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
  2394   2407   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2395   2408   
  2396   2409   /*
  2397   2410   ** An instance of the following structure contains all information
  2398   2411   ** needed to generate code for a single SELECT statement.
  2399   2412   **
  2400   2413   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
................................................................................
  2656   2669     int nTab;            /* Number of previously allocated VDBE cursors */
  2657   2670     int nMem;            /* Number of memory cells used so far */
  2658   2671     int nSet;            /* Number of sets used so far */
  2659   2672     int nOnce;           /* Number of OP_Once instructions so far */
  2660   2673     int nOpAlloc;        /* Number of slots allocated for Vdbe.aOp[] */
  2661   2674     int iFixedOp;        /* Never back out opcodes iFixedOp-1 or earlier */
  2662   2675     int ckBase;          /* Base register of data during check constraints */
  2663         -  int iPartIdxTab;     /* Table corresponding to a partial index */
         2676  +  int iSelfTab;        /* Table of an index whose exprs are being coded */
  2664   2677     int iCacheLevel;     /* ColCache valid when aColCache[].iLevel<=iCacheLevel */
  2665   2678     int iCacheCnt;       /* Counter used to generate aColCache[].lru values */
  2666   2679     int nLabel;          /* Number of labels used */
  2667   2680     int *aLabel;         /* Space to hold the labels */
  2668   2681     struct yColCache {
  2669   2682       int iTable;           /* Table cursor number */
  2670   2683       i16 iColumn;          /* Table column number */
................................................................................
  3357   3370   u64 sqlite3WhereOutputRowCount(WhereInfo*);
  3358   3371   int sqlite3WhereIsDistinct(WhereInfo*);
  3359   3372   int sqlite3WhereIsOrdered(WhereInfo*);
  3360   3373   int sqlite3WhereIsSorted(WhereInfo*);
  3361   3374   int sqlite3WhereContinueLabel(WhereInfo*);
  3362   3375   int sqlite3WhereBreakLabel(WhereInfo*);
  3363   3376   int sqlite3WhereOkOnePass(WhereInfo*, int*);
         3377  +void sqlite3ExprCodeLoadIndexColumn(Parse*, Index*, int, int, int);
  3364   3378   int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
  3365   3379   void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
  3366   3380   void sqlite3ExprCodeMove(Parse*, int, int, int);
  3367   3381   void sqlite3ExprCacheStore(Parse*, int, int, int);
  3368   3382   void sqlite3ExprCachePush(Parse*);
  3369   3383   void sqlite3ExprCachePop(Parse*);
  3370   3384   void sqlite3ExprCacheRemove(Parse*, int, int);

Changes to src/update.c.

   268    268     */
   269    269     pTabList->a[0].colUsed = 0;
   270    270   
   271    271     hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);
   272    272   
   273    273     /* There is one entry in the aRegIdx[] array for each index on the table
   274    274     ** being updated.  Fill in aRegIdx[] with a register number that will hold
   275         -  ** the key for accessing each index.  
          275  +  ** the key for accessing each index.
          276  +  **
          277  +  ** FIXME:  Be smarter about omitting indexes that use expressions.
   276    278     */
   277    279     for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
   278    280       int reg;
   279    281       if( chngKey || hasFK || pIdx->pPartIdxWhere || pIdx==pPk ){
   280    282         reg = ++pParse->nMem;
   281    283       }else{
   282    284         reg = 0;
   283    285         for(i=0; i<pIdx->nKeyCol; i++){
   284         -        if( aXRef[pIdx->aiColumn[i]]>=0 ){
          286  +        i16 iIdxCol = pIdx->aiColumn[i];
          287  +        if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){
   285    288             reg = ++pParse->nMem;
   286    289             break;
   287    290           }
   288    291         }
   289    292       }
   290    293       if( reg==0 ) aToOpen[j+1] = 0;
   291    294       aRegIdx[j] = reg;
................................................................................
   377    380       addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
   378    381       sqlite3VdbeSetP4KeyInfo(pParse, pPk);
   379    382       pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
   380    383                                  WHERE_ONEPASS_DESIRED, iIdxCur);
   381    384       if( pWInfo==0 ) goto update_cleanup;
   382    385       okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   383    386       for(i=0; i<nPk; i++){
          387  +      assert( pPk->aiColumn[i]>=(-1) );
   384    388         sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
   385    389                                         iPk+i);
   386    390       }
   387    391       if( okOnePass ){
   388    392         sqlite3VdbeChangeToNoop(v, addrOpen);
   389    393         nKey = nPk;
   390    394         regKey = iPk;

Changes to src/vdbeblob.c.

   243    243             }
   244    244           }
   245    245         }
   246    246   #endif
   247    247         for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   248    248           int j;
   249    249           for(j=0; j<pIdx->nKeyCol; j++){
   250         -          if( pIdx->aiColumn[j]==iCol ){
          250  +          /* FIXME: Be smarter about indexes that use expressions */
          251  +          if( pIdx->aiColumn[j]==iCol || pIdx->aiColumn[j]==(-2) ){
   251    252               zFault = "indexed";
   252    253             }
   253    254           }
   254    255         }
   255    256         if( zFault ){
   256    257           sqlite3DbFree(db, zErr);
   257    258           zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault);

Changes to src/where.c.

   176    176     WhereClause *pWC;    /* Shorthand for pScan->pWC */
   177    177     WhereTerm *pTerm;    /* The term being tested */
   178    178     int k = pScan->k;    /* Where to start scanning */
   179    179   
   180    180     while( pScan->iEquiv<=pScan->nEquiv ){
   181    181       iCur = pScan->aiCur[pScan->iEquiv-1];
   182    182       iColumn = pScan->aiColumn[pScan->iEquiv-1];
          183  +    assert( iColumn!=(-2) || pScan->pIdxExpr!=0 );
   183    184       while( (pWC = pScan->pWC)!=0 ){
   184    185         for(pTerm=pWC->a+k; k<pWC->nTerm; k++, pTerm++){
   185    186           if( pTerm->leftCursor==iCur
   186    187            && pTerm->u.leftColumn==iColumn
          188  +         && (iColumn!=(-2)
          189  +               || sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
   187    190            && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
   188    191           ){
   189    192             if( (pTerm->eOperator & WO_EQUIV)!=0
   190    193              && pScan->nEquiv<ArraySize(pScan->aiCur)
          194  +           && (pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight))->op==TK_COLUMN
   191    195             ){
   192    196               int j;
   193         -            pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight);
   194         -            assert( pX->op==TK_COLUMN );
   195    197               for(j=0; j<pScan->nEquiv; j++){
   196    198                 if( pScan->aiCur[j]==pX->iTable
   197    199                  && pScan->aiColumn[j]==pX->iColumn ){
   198    200                     break;
   199    201                 }
   200    202               }
   201    203               if( j==pScan->nEquiv ){
................................................................................
   269    271     Index *pIdx             /* Must be compatible with this index */
   270    272   ){
   271    273     int j;
   272    274   
   273    275     /* memset(pScan, 0, sizeof(*pScan)); */
   274    276     pScan->pOrigWC = pWC;
   275    277     pScan->pWC = pWC;
          278  +  pScan->pIdxExpr = 0;
          279  +  if( pIdx ){
          280  +    j = iColumn;
          281  +    iColumn = pIdx->aiColumn[j];
          282  +    if( iColumn==(-2) ) pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
          283  +  }
   276    284     if( pIdx && iColumn>=0 ){
   277    285       pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
   278         -    for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
   279         -      if( NEVER(j>pIdx->nColumn) ) return 0;
   280         -    }
   281    286       pScan->zCollName = pIdx->azColl[j];
   282    287     }else{
   283    288       pScan->idxaff = 0;
   284    289       pScan->zCollName = 0;
   285    290     }
   286    291     pScan->opMask = opMask;
   287    292     pScan->k = 0;
................................................................................
   293    298   }
   294    299   
   295    300   /*
   296    301   ** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
   297    302   ** where X is a reference to the iColumn of table iCur and <op> is one of
   298    303   ** the WO_xx operator codes specified by the op parameter.
   299    304   ** Return a pointer to the term.  Return 0 if not found.
          305  +**
          306  +** If pIdx!=0 then search for terms matching the iColumn-th column of pIdx
          307  +** rather than the iColumn-th column of table iCur.
   300    308   **
   301    309   ** The term returned might by Y=<expr> if there is another constraint in
   302    310   ** the WHERE clause that specifies that X=Y.  Any such constraints will be
   303    311   ** identified by the WO_EQUIV bit in the pTerm->eOperator field.  The
   304    312   ** aiCur[]/iaColumn[] arrays hold X and all its equivalents. There are 11
   305    313   ** slots in aiCur[]/aiColumn[] so that means we can look for X plus up to 10
   306    314   ** other equivalent values.  Hence a search for X will return <expr> if X=A1
................................................................................
   368    376           return i;
   369    377         }
   370    378       }
   371    379     }
   372    380   
   373    381     return -1;
   374    382   }
          383  +
          384  +/*
          385  +** Return TRUE if the iCol-th column of index pIdx is NOT NULL
          386  +*/
          387  +static int indexColumnNotNull(Index *pIdx, int iCol){
          388  +  int j;
          389  +  assert( pIdx!=0 );
          390  +  assert( iCol>=0 && iCol<pIdx->nColumn );
          391  +  j = pIdx->aiColumn[iCol];
          392  +  if( j>=0 ){
          393  +    return pIdx->pTable->aCol[j].notNull;
          394  +  }else if( j==(-1) ){
          395  +    return 1;
          396  +  }else{
          397  +    assert( j==(-2) );
          398  +    return 0;  /* Assume an indexed expression can always yield a NULL */
          399  +
          400  +  }
          401  +}
   375    402   
   376    403   /*
   377    404   ** Return true if the DISTINCT expression-list passed as the third argument
   378    405   ** is redundant.
   379    406   **
   380    407   ** A DISTINCT list is redundant if any subset of the columns in the
   381    408   ** DISTINCT list are collectively unique and individually non-null.
................................................................................
   419    446     **
   420    447     **   3. All of those index columns for which the WHERE clause does not
   421    448     **      contain a "col=X" term are subject to a NOT NULL constraint.
   422    449     */
   423    450     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   424    451       if( !IsUniqueIndex(pIdx) ) continue;
   425    452       for(i=0; i<pIdx->nKeyCol; i++){
   426         -      i16 iCol = pIdx->aiColumn[i];
   427         -      if( 0==sqlite3WhereFindTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){
   428         -        int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i);
   429         -        if( iIdxCol<0 || pTab->aCol[iCol].notNull==0 ){
   430         -          break;
   431         -        }
          453  +      if( 0==sqlite3WhereFindTerm(pWC, iBase, i, ~(Bitmask)0, WO_EQ, pIdx) ){
          454  +        if( findIndexCol(pParse, pDistinct, iBase, pIdx, i)<0 ) break;
          455  +        if( indexColumnNotNull(pIdx, i)==0 ) break;
   432    456         }
   433    457       }
   434    458       if( i==pIdx->nKeyCol ){
   435    459         /* This index implies that the DISTINCT qualifier is redundant. */
   436    460         return 1;
   437    461       }
   438    462     }
................................................................................
   776    800       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
   777    801       testcase( pTerm->eOperator & WO_IN );
   778    802       testcase( pTerm->eOperator & WO_ISNULL );
   779    803       testcase( pTerm->eOperator & WO_IS );
   780    804       testcase( pTerm->eOperator & WO_ALL );
   781    805       if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
   782    806       if( pTerm->wtFlags & TERM_VNULL ) continue;
          807  +    assert( pTerm->u.leftColumn>=(-1) );
   783    808       nTerm++;
   784    809     }
   785    810   
   786    811     /* If the ORDER BY clause contains only columns in the current 
   787    812     ** virtual table then allocate space for the aOrderBy part of
   788    813     ** the sqlite3_index_info structure.
   789    814     */
................................................................................
   831    856       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
   832    857       testcase( pTerm->eOperator & WO_IN );
   833    858       testcase( pTerm->eOperator & WO_IS );
   834    859       testcase( pTerm->eOperator & WO_ISNULL );
   835    860       testcase( pTerm->eOperator & WO_ALL );
   836    861       if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
   837    862       if( pTerm->wtFlags & TERM_VNULL ) continue;
          863  +    assert( pTerm->u.leftColumn>=(-1) );
   838    864       pIdxCons[j].iColumn = pTerm->u.leftColumn;
   839    865       pIdxCons[j].iTermOffset = i;
   840    866       op = (u8)pTerm->eOperator & WO_ALL;
   841    867       if( op==WO_IN ) op = WO_EQ;
   842    868       pIdxCons[j].op = op;
   843    869       /* The direct assignment in the previous line is possible only because
   844    870       ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
................................................................................
  2122   2148     WhereScan scan;                 /* Iterator for WHERE terms */
  2123   2149     Bitmask saved_prereq;           /* Original value of pNew->prereq */
  2124   2150     u16 saved_nLTerm;               /* Original value of pNew->nLTerm */
  2125   2151     u16 saved_nEq;                  /* Original value of pNew->u.btree.nEq */
  2126   2152     u16 saved_nSkip;                /* Original value of pNew->nSkip */
  2127   2153     u32 saved_wsFlags;              /* Original value of pNew->wsFlags */
  2128   2154     LogEst saved_nOut;              /* Original value of pNew->nOut */
  2129         -  int iCol;                       /* Index of the column in the table */
  2130   2155     int rc = SQLITE_OK;             /* Return code */
  2131   2156     LogEst rSize;                   /* Number of rows in the table */
  2132   2157     LogEst rLogSize;                /* Logarithm of table size */
  2133   2158     WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */
  2134   2159   
  2135   2160     pNew = pBuilder->pNew;
  2136   2161     if( db->mallocFailed ) return SQLITE_NOMEM;
................................................................................
  2143   2168       opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  2144   2169     }else{
  2145   2170       opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  2146   2171     }
  2147   2172     if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
  2148   2173   
  2149   2174     assert( pNew->u.btree.nEq<pProbe->nColumn );
  2150         -  iCol = pProbe->aiColumn[pNew->u.btree.nEq];
  2151   2175   
  2152         -  pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
  2153         -                        opMask, pProbe);
  2154   2176     saved_nEq = pNew->u.btree.nEq;
  2155   2177     saved_nSkip = pNew->nSkip;
  2156   2178     saved_nLTerm = pNew->nLTerm;
  2157   2179     saved_wsFlags = pNew->wsFlags;
  2158   2180     saved_prereq = pNew->prereq;
  2159   2181     saved_nOut = pNew->nOut;
         2182  +  pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, saved_nEq,
         2183  +                        opMask, pProbe);
  2160   2184     pNew->rSetup = 0;
  2161   2185     rSize = pProbe->aiRowLogEst[0];
  2162   2186     rLogSize = estLog(rSize);
  2163   2187     for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
  2164   2188       u16 eOp = pTerm->eOperator;   /* Shorthand for pTerm->eOperator */
  2165   2189       LogEst rCostIdx;
  2166   2190       LogEst nOutUnadjusted;        /* nOut before IN() and WHERE adjustments */
  2167   2191       int nIn = 0;
  2168   2192   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  2169   2193       int nRecValid = pBuilder->nRecValid;
  2170   2194   #endif
  2171   2195       if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
  2172         -     && (iCol<0 || pSrc->pTab->aCol[iCol].notNull)
         2196  +     && indexColumnNotNull(pProbe, saved_nEq)
  2173   2197       ){
  2174   2198         continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
  2175   2199       }
  2176   2200       if( pTerm->prereqRight & pNew->maskSelf ) continue;
  2177   2201   
  2178   2202       /* Do not allow the upper bound of a LIKE optimization range constraint
  2179   2203       ** to mix with a lower range bound from some other source */
................................................................................
  2202   2226           /* "x IN (value, value, ...)" */
  2203   2227           nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
  2204   2228         }
  2205   2229         assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
  2206   2230                           ** changes "x IN (?)" into "x=?". */
  2207   2231   
  2208   2232       }else if( eOp & (WO_EQ|WO_IS) ){
         2233  +      int iCol = pProbe->aiColumn[saved_nEq];
  2209   2234         pNew->wsFlags |= WHERE_COLUMN_EQ;
  2210         -      if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
         2235  +      assert( saved_nEq==pNew->u.btree.nEq );
         2236  +      if( iCol==(-1) || (iCol>0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1) ){
  2211   2237           if( iCol>=0 && pProbe->uniqNotNull==0 ){
  2212   2238             pNew->wsFlags |= WHERE_UNQ_WANTED;
  2213   2239           }else{
  2214   2240             pNew->wsFlags |= WHERE_ONEROW;
  2215   2241           }
  2216   2242         }
  2217   2243       }else if( eOp & WO_ISNULL ){
................................................................................
  2254   2280         ** data, using some other estimate.  */
  2255   2281         whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
  2256   2282       }else{
  2257   2283         int nEq = ++pNew->u.btree.nEq;
  2258   2284         assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) );
  2259   2285   
  2260   2286         assert( pNew->nOut==saved_nOut );
  2261         -      if( pTerm->truthProb<=0 && iCol>=0 ){
         2287  +      if( pTerm->truthProb<=0 && pProbe->aiColumn[saved_nEq]>=0 ){
  2262   2288           assert( (eOp & WO_IN) || nIn==0 );
  2263   2289           testcase( eOp & WO_IN );
  2264   2290           pNew->nOut += pTerm->truthProb;
  2265   2291           pNew->nOut -= nIn;
  2266   2292         }else{
  2267   2293   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  2268   2294           tRowcnt nOut = 0;
................................................................................
  3781   3807         assert( pLoop->aLTermSpace==pLoop->aLTerm );
  3782   3808         if( !IsUniqueIndex(pIdx)
  3783   3809          || pIdx->pPartIdxWhere!=0 
  3784   3810          || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
  3785   3811         ) continue;
  3786   3812         opMask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ;
  3787   3813         for(j=0; j<pIdx->nKeyCol; j++){
  3788         -        pTerm = sqlite3WhereFindTerm(pWC, iCur, pIdx->aiColumn[j], 0, opMask, pIdx);
         3814  +        pTerm = sqlite3WhereFindTerm(pWC, iCur, j, 0, opMask, pIdx);
  3789   3815           if( pTerm==0 ) break;
  3790   3816           testcase( pTerm->eOperator & WO_IS );
  3791   3817           pLoop->aLTerm[j] = pTerm;
  3792   3818         }
  3793   3819         if( j!=pIdx->nKeyCol ) continue;
  3794   3820         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
  3795   3821         if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
................................................................................
  4021   4047       if( wctrlFlags & WHERE_WANT_DISTINCT ){
  4022   4048         pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  4023   4049       }
  4024   4050     }
  4025   4051   
  4026   4052     /* Assign a bit from the bitmask to every term in the FROM clause.
  4027   4053     **
  4028         -  ** When assigning bitmask values to FROM clause cursors, it must be
  4029         -  ** the case that if X is the bitmask for the N-th FROM clause term then
  4030         -  ** the bitmask for all FROM clause terms to the left of the N-th term
  4031         -  ** is (X-1).   An expression from the ON clause of a LEFT JOIN can use
  4032         -  ** its Expr.iRightJoinTable value to find the bitmask of the right table
  4033         -  ** of the join.  Subtracting one from the right table bitmask gives a
  4034         -  ** bitmask for all tables to the left of the join.  Knowing the bitmask
  4035         -  ** for all tables to the left of a left join is important.  Ticket #3015.
         4054  +  ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
         4055  +  **
         4056  +  ** The rule of the previous sentence ensures thta if X is the bitmask for
         4057  +  ** a table T, then X-1 is the bitmask for all other tables to the left of T.
         4058  +  ** Knowing the bitmask for all tables to the left of a left join is
         4059  +  ** important.  Ticket #3015.
  4036   4060     **
  4037   4061     ** Note that bitmasks are created for all pTabList->nSrc tables in
  4038   4062     ** pTabList, not just the first nTabList tables.  nTabList is normally
  4039   4063     ** equal to pTabList->nSrc but might be shortened to 1 if the
  4040   4064     ** WHERE_ONETABLE_ONLY flag is set.
  4041   4065     */
  4042   4066     for(ii=0; ii<pTabList->nSrc; ii++){
  4043   4067       createMask(pMaskSet, pTabList->a[ii].iCursor);
  4044   4068       sqlite3WhereTabFuncArgs(pParse, &pTabList->a[ii], &pWInfo->sWC);
  4045   4069     }
  4046         -#ifndef NDEBUG
  4047         -  {
  4048         -    Bitmask toTheLeft = 0;
  4049         -    for(ii=0; ii<pTabList->nSrc; ii++){
  4050         -      Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor);
  4051         -      assert( (m-1)==toTheLeft );
  4052         -      toTheLeft |= m;
  4053         -    }
         4070  +#ifdef SQLITE_DEBUG
         4071  +  for(ii=0; ii<pTabList->nSrc; ii++){
         4072  +    Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor);
         4073  +    assert( m==MASKBIT(ii) );
  4054   4074     }
  4055   4075   #endif
  4056   4076   
  4057   4077     /* Analyze all of the subexpressions. */
  4058   4078     sqlite3WhereExprAnalyze(pTabList, &pWInfo->sWC);
  4059   4079     if( db->mallocFailed ) goto whereBeginError;
  4060   4080   

Changes to src/whereInt.h.

   282    282   ** An instance of the WhereScan object is used as an iterator for locating
   283    283   ** terms in the WHERE clause that are useful to the query planner.
   284    284   */
   285    285   struct WhereScan {
   286    286     WhereClause *pOrigWC;      /* Original, innermost WhereClause */
   287    287     WhereClause *pWC;          /* WhereClause currently being scanned */
   288    288     char *zCollName;           /* Required collating sequence, if not NULL */
          289  +  Expr *pIdxExpr;            /* Search for this index expression */
   289    290     char idxaff;               /* Must match this affinity, if zCollName!=NULL */
   290    291     unsigned char nEquiv;      /* Number of entries in aEquiv[] */
   291    292     unsigned char iEquiv;      /* Next unused slot in aEquiv[] */
   292    293     u32 opMask;                /* Acceptable operators */
   293    294     int k;                     /* Resume scanning at this->pWC->a[this->k] */
   294    295     int aiCur[11];             /* Cursors in the equivalence class */
   295    296     i16 aiColumn[11];          /* Corresponding column number in the eq-class */

Changes to src/wherecode.c.

    36     36     const char *zOp             /* Name of the operator */
    37     37   ){
    38     38     if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5);
    39     39     sqlite3StrAccumAppendAll(pStr, zColumn);
    40     40     sqlite3StrAccumAppend(pStr, zOp, 1);
    41     41     sqlite3StrAccumAppend(pStr, "?", 1);
    42     42   }
           43  +
           44  +/*
           45  +** Return the name of the i-th column of the pIdx index.
           46  +*/
           47  +static const char *explainIndexColumnName(Index *pIdx, int i){
           48  +  i = pIdx->aiColumn[i];
           49  +  if( i==(-2) ) return "<expr>";
           50  +  if( i==(-1) ) return "rowid";
           51  +  return pIdx->pTable->aCol[i].zName;
           52  +}
    43     53   
    44     54   /*
    45     55   ** Argument pLevel describes a strategy for scanning table pTab. This 
    46     56   ** function appends text to pStr that describes the subset of table
    47     57   ** rows scanned by the strategy in the form of an SQL expression.
    48     58   **
    49     59   ** For example, if the query:
................................................................................
    56     66   **   "a=? AND b>?"
    57     67   */
    58     68   static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){
    59     69     Index *pIndex = pLoop->u.btree.pIndex;
    60     70     u16 nEq = pLoop->u.btree.nEq;
    61     71     u16 nSkip = pLoop->nSkip;
    62     72     int i, j;
    63         -  Column *aCol = pTab->aCol;
    64         -  i16 *aiColumn = pIndex->aiColumn;
    65     73   
    66     74     if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;
    67     75     sqlite3StrAccumAppend(pStr, " (", 2);
    68     76     for(i=0; i<nEq; i++){
    69         -    char *z = aiColumn[i] < 0 ? "rowid" : aCol[aiColumn[i]].zName;
           77  +    const char *z = explainIndexColumnName(pIndex, i);
    70     78       if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5);
    71     79       sqlite3XPrintf(pStr, 0, i>=nSkip ? "%s=?" : "ANY(%s)", z);
    72     80     }
    73     81   
    74     82     j = i;
    75     83     if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
    76         -    char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
           84  +    const char *z = explainIndexColumnName(pIndex, i);
    77     85       explainAppendTerm(pStr, i++, z, ">");
    78     86     }
    79     87     if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
    80         -    char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
           88  +    const char *z = explainIndexColumnName(pIndex, j);
    81     89       explainAppendTerm(pStr, i, z, "<");
    82     90     }
    83     91     sqlite3StrAccumAppend(pStr, ")", 1);
    84     92   }
    85     93   
    86     94   /*
    87     95   ** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN

Changes to src/whereexpr.c.

   790    790           mask |= sqlite3WhereExprUsage(pMaskSet, pSrc->a[i].pOn);
   791    791         }
   792    792       }
   793    793       pS = pS->pPrior;
   794    794     }
   795    795     return mask;
   796    796   }
          797  +
          798  +/*
          799  +** Expression pExpr is one operand of a comparison operator that might
          800  +** be useful for indexing.  This routine checks to see if pExpr appears
          801  +** in any index.  Return TRUE (1) if pExpr is an indexed term and return
          802  +** FALSE (0) if not.  If TRUE is returned, also set *piCur to the cursor
          803  +** number of the table that is indexed and *piColumn to the column number
          804  +** of the column that is indexed, or -2 if an expression is being indexed.
          805  +**
          806  +** If pExpr is a TK_COLUMN column reference, then this routine always returns
          807  +** true even if that particular column is not indexed, because the column
          808  +** might be added to an automatic index later.
          809  +*/
          810  +static int exprMightBeIndexed(
          811  +  SrcList *pFrom,        /* The FROM clause */
          812  +  Bitmask mPrereq,       /* Bitmask of FROM clause terms referenced by pExpr */
          813  +  Expr *pExpr,           /* An operand of a comparison operator */
          814  +  int *piCur,            /* Write the referenced table cursor number here */
          815  +  int *piColumn          /* Write the referenced table column number here */
          816  +){
          817  +  Index *pIdx;
          818  +  int i;
          819  +  int iCur;
          820  +  if( pExpr->op==TK_COLUMN ){
          821  +    *piCur = pExpr->iTable;
          822  +    *piColumn = pExpr->iColumn;
          823  +    return 1;
          824  +  }
          825  +  if( mPrereq==0 ) return 0;                 /* No table references */
          826  +  if( (mPrereq&(mPrereq-1))!=0 ) return 0;   /* Refs more than one table */
          827  +  for(i=0; mPrereq>1; i++, mPrereq>>=1){}
          828  +  iCur = pFrom->a[i].iCursor;
          829  +  for(pIdx=pFrom->a[i].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          830  +    if( pIdx->aColExpr==0 ) continue;
          831  +    for(i=0; i<pIdx->nKeyCol; i++){
          832  +      if( pIdx->aiColumn[i]!=(-2) ) continue;
          833  +      if( sqlite3ExprCompare(pExpr, pIdx->aColExpr->a[i].pExpr, iCur)==0 ){
          834  +        *piCur = iCur;
          835  +        *piColumn = -2;
          836  +        return 1;
          837  +      }
          838  +    }
          839  +  }
          840  +  return 0;
          841  +}
   797    842   
   798    843   /*
   799    844   ** The input to this routine is an WhereTerm structure with only the
   800    845   ** "pExpr" field filled in.  The job of this routine is to analyze the
   801    846   ** subexpression and populate all the other fields of the WhereTerm
   802    847   ** structure.
   803    848   **
................................................................................
   861    906                          ** on left table of a LEFT JOIN.  Ticket #3015 */
   862    907     }
   863    908     pTerm->prereqAll = prereqAll;
   864    909     pTerm->leftCursor = -1;
   865    910     pTerm->iParent = -1;
   866    911     pTerm->eOperator = 0;
   867    912     if( allowedOp(op) ){
          913  +    int iCur, iColumn;
   868    914       Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft);
   869    915       Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
   870    916       u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;
   871         -    if( pLeft->op==TK_COLUMN ){
   872         -      pTerm->leftCursor = pLeft->iTable;
   873         -      pTerm->u.leftColumn = pLeft->iColumn;
          917  +    if( exprMightBeIndexed(pSrc, prereqLeft, pLeft, &iCur, &iColumn) ){
          918  +      pTerm->leftCursor = iCur;
          919  +      pTerm->u.leftColumn = iColumn;
   874    920         pTerm->eOperator = operatorMask(op) & opMask;
   875    921       }
   876    922       if( op==TK_IS ) pTerm->wtFlags |= TERM_IS;
   877         -    if( pRight && pRight->op==TK_COLUMN ){
          923  +    if( pRight 
          924  +     && exprMightBeIndexed(pSrc, pTerm->prereqRight, pRight, &iCur, &iColumn)
          925  +    ){
   878    926         WhereTerm *pNew;
   879    927         Expr *pDup;
   880    928         u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
   881    929         if( pTerm->leftCursor>=0 ){
   882    930           int idxNew;
   883    931           pDup = sqlite3ExprDup(db, pExpr, 0);
   884    932           if( db->mallocFailed ){
................................................................................
   899    947           }
   900    948         }else{
   901    949           pDup = pExpr;
   902    950           pNew = pTerm;
   903    951         }
   904    952         exprCommute(pParse, pDup);
   905    953         pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
   906         -      pNew->leftCursor = pLeft->iTable;
   907         -      pNew->u.leftColumn = pLeft->iColumn;
          954  +      pNew->leftCursor = iCur;
          955  +      pNew->u.leftColumn = iColumn;
   908    956         testcase( (prereqLeft | extraRight) != prereqLeft );
   909    957         pNew->prereqRight = prereqLeft | extraRight;
   910    958         pNew->prereqAll = prereqAll;
   911    959         pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask;
   912    960       }
   913    961     }
   914    962   

Changes to test/index.test.

    52     52     set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
    53     53     lappend v $msg
    54     54   } {1 {no such table: main.test1}}
    55     55   
    56     56   # Try adding an index on a column of a table where the table
    57     57   # exists but the column does not.
    58     58   #
    59         -do_test index-2.1 {
           59  +do_test index-2.1b {
    60     60     execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
    61     61     set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
    62     62     lappend v $msg
    63         -} {1 {table test1 has no column named f4}}
           63  +} {1 {no such column: f4}}
    64     64   
    65     65   # Try an index with some columns that match and others that do now.
    66     66   #
    67     67   do_test index-2.2 {
    68     68     set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
    69     69     execsql {DROP TABLE test1}
    70     70     lappend v $msg
    71         -} {1 {table test1 has no column named f4}}
           71  +} {1 {no such column: f4}}
    72     72   
    73     73   # Try creating a bunch of indices on the same table
    74     74   #
    75     75   set r {}
    76     76   for {set i 1} {$i<100} {incr i} {
    77     77     lappend r [format index%02d $i]
    78     78   }

Added test/indexexpr1.test.

            1  +# 2015-08-31
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing indexes on expressions.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +do_execsql_test indexexpr1-100 {
           19  +  CREATE TABLE t1(a,b,c);
           20  +  INSERT INTO t1(a,b,c)
           21  +      /*  123456789 123456789 123456789 123456789 123456789 123456789 */ 
           22  +  VALUES('In_the_beginning_was_the_Word',1,1),
           23  +        ('and_the_Word_was_with_God',1,2),
           24  +        ('and_the_Word_was_God',1,3),
           25  +        ('The_same_was_in_the_beginning_with_God',2,1),
           26  +        ('All_things_were_made_by_him',3,1),
           27  +        ('and_without_him_was_not_any_thing_made_that_was_made',3,2);
           28  +  CREATE INDEX t1a1 ON t1(substr(a,1,12));
           29  +} {}
           30  +do_execsql_test indexexpr1-110 {
           31  +  SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c;
           32  +} {1 2 | 1 3 |}
           33  +do_execsql_test indexexpr1-110eqp {
           34  +  EXPLAIN QUERY PLAN
           35  +  SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c;
           36  +} {/USING INDEX t1a1/}
           37  +do_execsql_test indexexpr1-120 {
           38  +  SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c;
           39  +} {1 2 | 1 3 |}
           40  +do_execsql_test indexexpr1-120eqp {
           41  +  EXPLAIN QUERY PLAN
           42  +  SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c;
           43  +} {/USING INDEX t1a1/}
           44  +
           45  +do_execsql_test indexexpr1-130 {
           46  +  CREATE INDEX t1ba ON t1(b,substr(a,2,3),c);
           47  +  SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c;
           48  +} {2 3}
           49  +do_execsql_test indexexpr1-130eqp {
           50  +  EXPLAIN QUERY PLAN
           51  +  SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c;
           52  +} {/USING INDEX t1ba/}
           53  +
           54  +do_execsql_test indexexpr1-140 {
           55  +  SELECT rowid, substr(a,b,3), '|' FROM t1 ORDER BY 2;
           56  +} {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |}
           57  +do_execsql_test indexexpr1-141 {
           58  +  CREATE INDEX t1abx ON t1(substr(a,b,3));
           59  +  SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid;
           60  +} {1 2 3}
           61  +do_execsql_test indexexpr1-141eqp {
           62  +  EXPLAIN QUERY PLAN
           63  +  SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid;
           64  +} {/USING INDEX t1abx/}
           65  +do_execsql_test indexexpr1-142 {
           66  +  SELECT rowid FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +rowid;
           67  +} {1 2 3}
           68  +do_execsql_test indexexpr1-150 {
           69  +  SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
           70  +   ORDER BY +rowid;
           71  +} {2 3 5}
           72  +do_execsql_test indexexpr1-150eqp {
           73  +  EXPLAIN QUERY PLAN
           74  +  SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
           75  +   ORDER BY +rowid;
           76  +} {/USING INDEX t1abx/}
           77  +
           78  +do_execsql_test indexexpr1-160 {
           79  +  ALTER TABLE t1 ADD COLUMN d;
           80  +  UPDATE t1 SET d=length(a);
           81  +  CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29;
           82  +  SELECT rowid, b, c FROM t1
           83  +   WHERE substr(a,27,3)=='ord' AND d>=29;
           84  +} {1 1 1}
           85  +do_execsql_test indexexpr1-160eqp {
           86  +  EXPLAIN QUERY PLAN
           87  +  SELECT rowid, b, c FROM t1
           88  +   WHERE substr(a,27,3)=='ord' AND d>=29;
           89  +} {/USING INDEX t1a2/}
           90  +
           91  +
           92  +do_execsql_test indexexpr1-200 {
           93  +  DROP TABLE t1;
           94  +  CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID;
           95  +  INSERT INTO t1(id,a,b,c)
           96  +  VALUES(1,'In_the_beginning_was_the_Word',1,1),
           97  +        (2,'and_the_Word_was_with_God',1,2),
           98  +        (3,'and_the_Word_was_God',1,3),
           99  +        (4,'The_same_was_in_the_beginning_with_God',2,1),
          100  +        (5,'All_things_were_made_by_him',3,1),
          101  +        (6,'and_without_him_was_not_any_thing_made_that_was_made',3,2);
          102  +  CREATE INDEX t1a1 ON t1(substr(a,1,12));
          103  +} {}
          104  +do_execsql_test indexexpr1-210 {
          105  +  SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c;
          106  +} {1 2 | 1 3 |}
          107  +do_execsql_test indexexpr1-210eqp {
          108  +  EXPLAIN QUERY PLAN
          109  +  SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c;
          110  +} {/USING INDEX t1a1/}
          111  +do_execsql_test indexexpr1-220 {
          112  +  SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c;
          113  +} {1 2 | 1 3 |}
          114  +do_execsql_test indexexpr1-220eqp {
          115  +  EXPLAIN QUERY PLAN
          116  +  SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c;
          117  +} {/USING INDEX t1a1/}
          118  +
          119  +do_execsql_test indexexpr1-230 {
          120  +  CREATE INDEX t1ba ON t1(b,substr(a,2,3),c);
          121  +  SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c;
          122  +} {2 3}
          123  +do_execsql_test indexexpr1-230eqp {
          124  +  EXPLAIN QUERY PLAN
          125  +  SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c;
          126  +} {/USING INDEX t1ba/}
          127  +
          128  +do_execsql_test indexexpr1-240 {
          129  +  SELECT id, substr(a,b,3), '|' FROM t1 ORDER BY 2;
          130  +} {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |}
          131  +do_execsql_test indexexpr1-241 {
          132  +  CREATE INDEX t1abx ON t1(substr(a,b,3));
          133  +  SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id;
          134  +} {1 2 3}
          135  +do_execsql_test indexexpr1-241eqp {
          136  +  EXPLAIN QUERY PLAN
          137  +  SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id;
          138  +} {/USING INDEX t1abx/}
          139  +do_execsql_test indexexpr1-242 {
          140  +  SELECT id FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +id;
          141  +} {1 2 3}
          142  +do_execsql_test indexexpr1-250 {
          143  +  SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
          144  +   ORDER BY +id;
          145  +} {2 3 5}
          146  +do_execsql_test indexexpr1-250eqp {
          147  +  EXPLAIN QUERY PLAN
          148  +  SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
          149  +   ORDER BY +id;
          150  +} {/USING INDEX t1abx/}
          151  +
          152  +do_execsql_test indexexpr1-260 {
          153  +  ALTER TABLE t1 ADD COLUMN d;
          154  +  UPDATE t1 SET d=length(a);
          155  +  CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29;
          156  +  SELECT id, b, c FROM t1
          157  +   WHERE substr(a,27,3)=='ord' AND d>=29;
          158  +} {1 1 1}
          159  +do_execsql_test indexexpr1-260eqp {
          160  +  EXPLAIN QUERY PLAN
          161  +  SELECT id, b, c FROM t1
          162  +   WHERE substr(a,27,3)=='ord' AND d>=29;
          163  +} {/USING INDEX t1a2/}
          164  +
          165  +
          166  +do_catchsql_test indexexpr1-300 {
          167  +  CREATE TABLE t2(a,b,c);
          168  +  CREATE INDEX t2x1 ON t2(a,b+random());
          169  +} {1 {non-deterministic functions prohibited in index expressions}}
          170  +do_catchsql_test indexexpr1-301 {
          171  +  CREATE INDEX t2x1 ON t2(a+julianday('now'));
          172  +} {1 {non-deterministic functions prohibited in index expressions}}
          173  +do_catchsql_test indexexpr1-310 {
          174  +  CREATE INDEX t2x2 ON t2(a,b+(SELECT 15));
          175  +} {1 {subqueries prohibited in index expressions}}
          176  +do_catchsql_test indexexpr1-320 {
          177  +  CREATE TABLE e1(x,y,UNIQUE(y,substr(x,1,5)));
          178  +} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
          179  +do_catchsql_test indexexpr1-330 {
          180  +  CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5)));
          181  +} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
          182  +do_catchsql_test indexexpr1-331 {
          183  +  CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5))) WITHOUT ROWID;
          184  +} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
          185  +do_catchsql_test indexexpr1-340 {
          186  +  CREATE TABLE e1(x,y,FOREIGN KEY(substr(y,1,5)) REFERENCES t1);
          187  +} {1 {near "(": syntax error}}
          188  +
          189  +do_execsql_test indexexpr1-400 {
          190  +  CREATE TABLE t3(a,b,c);
          191  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<30)
          192  +  INSERT INTO t3(a,b,c)
          193  +    SELECT x, printf('ab%04xyz',x), random() FROM c;
          194  +  CREATE UNIQUE INDEX t3abc ON t3(CAST(a AS text), b, substr(c,1,3));
          195  +  SELECT a FROM t3 WHERE CAST(a AS text)<='10' ORDER BY +a;
          196  +} {1 10}
          197  +do_catchsql_test indexexpr1-410 {
          198  +  INSERT INTO t3 SELECT * FROM t3 WHERE rowid=10;
          199  +} {1 {UNIQUE constraint failed: index 't3abc'}}
          200  +
          201  +finish_test

Changes to test/rowid.test.

   140    140   do_test rowid-2.8 {
   141    141     global x2rowid
   142    142     set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
   143    143     execsql $sql
   144    144     execsql {SELECT x FROM t1 ORDER BY x}
   145    145   } {1 3 5 7 9}
   146    146   
          147  +if 0 {  # With the index-on-expressions enhancement, creating
          148  +        # an index on ROWID has become possible.
   147    149   # We cannot index by ROWID
   148    150   #
   149    151   do_test rowid-2.9 {
   150    152     set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
   151    153     lappend v $msg
   152    154   } {1 {table t1 has no column named rowid}}
   153    155   do_test rowid-2.10 {
................................................................................
   158    160     set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
   159    161     lappend v $msg
   160    162   } {1 {table t1 has no column named oid}}
   161    163   do_test rowid-2.12 {
   162    164     set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
   163    165     lappend v $msg
   164    166   } {1 {table t1 has no column named rowid}}
          167  +}
   165    168   
   166    169   # Columns defined in the CREATE statement override the buildin ROWID
   167    170   # column names.
   168    171   #
   169    172   do_test rowid-3.1 {
   170    173     execsql {
   171    174       CREATE TABLE t2(rowid int, x int, y int);