/ Changes On Branch partial-indices
Login

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

Changes In Branch partial-indices Excluding Merge-Ins

This is equivalent to a diff from eb6d4278 to 2e8c845e

2013-08-02
16:41
Add support for partial indices. (check-in: 478113f1 user: drh tags: trunk)
14:18
Silently ignore database name qualifiers in CHECK constraints and in partial index WHERE clauses. (Closed-Leaf check-in: 2e8c845e user: drh tags: partial-indices)
2013-08-01
16:02
Fix bug in the logic that determines the end of a CREATE INDEX statement. Added a VACUUM test case that exposed the bug. (check-in: 2e3df0bc user: drh tags: partial-indices)
2013-07-31
19:55
The MAX_PATH constant in windows is measured in characters, so multiple by 3 to get the number of bytes assuming worst-case UTF8 pathnames. (check-in: bb06e157 user: drh tags: trunk)
18:12
Here begins an experimental branch for exploring the idea of a partial index. This check-in is able to parse a WHERE clause on a CREATE INDEX statement, but does not actually do anythingn with that WHERE clause yet. (check-in: 6794b2dc user: drh tags: partial-indices)
2013-07-30
15:10
Reduce the size of the stack required by the codeOneLoopStart() function in where.c. (check-in: eb6d4278 user: drh tags: trunk)
2013-07-29
19:03
For the MSVC makefile, recompile vdbe.lo and parse.lo first. (check-in: 9e819f0f user: mistachkin tags: trunk)

Changes to src/analyze.c.

   437    437     int iIdxCur;                 /* Cursor open on index being analyzed */
   438    438     Vdbe *v;                     /* The virtual machine being built up */
   439    439     int i;                       /* Loop counter */
   440    440     int topOfLoop;               /* The top of the loop */
   441    441     int endOfLoop;               /* The end of the loop */
   442    442     int jZeroRows = -1;          /* Jump from here if number of rows is zero */
   443    443     int iDb;                     /* Index of database containing pTab */
          444  +  u8 needTableCnt = 1;         /* True to count the table */
   444    445     int regTabname = iMem++;     /* Register containing table name */
   445    446     int regIdxname = iMem++;     /* Register containing index name */
   446    447     int regStat1 = iMem++;       /* The stat column of sqlite_stat1 */
   447    448   #ifdef SQLITE_ENABLE_STAT3
   448    449     int regNumEq = regStat1;     /* Number of instances.  Same as regStat1 */
   449    450     int regNumLt = iMem++;       /* Number of keys less than regSample */
   450    451     int regNumDLt = iMem++;      /* Number of distinct keys less than regSample */
................................................................................
   496    497     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   497    498       int nCol;
   498    499       KeyInfo *pKey;
   499    500       int addrIfNot = 0;           /* address of OP_IfNot */
   500    501       int *aChngAddr;              /* Array of jump instruction addresses */
   501    502   
   502    503       if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;
          504  +    if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0;
   503    505       VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
   504    506       nCol = pIdx->nColumn;
   505    507       aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol);
   506    508       if( aChngAddr==0 ) continue;
   507    509       pKey = sqlite3IndexKeyinfo(pParse, pIdx);
   508    510       if( iMem+1+(nCol*2)>pParse->nMem ){
   509    511         pParse->nMem = iMem+1+(nCol*2);
................................................................................
   655    657       **        I = (K+D-1)/D
   656    658       **
   657    659       ** If K==0 then no entry is made into the sqlite_stat1 table.  
   658    660       ** If K>0 then it is always the case the D>0 so division by zero
   659    661       ** is never possible.
   660    662       */
   661    663       sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regStat1);
   662         -    if( jZeroRows<0 ){
   663         -      jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem);
   664         -    }
          664  +    jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem);
   665    665       for(i=0; i<nCol; i++){
   666    666         sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0);
   667    667         sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
   668    668         sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp);
   669    669         sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
   670    670         sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp);
   671    671         sqlite3VdbeAddOp1(v, OP_ToInt, regTemp);
   672    672         sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
   673    673       }
          674  +    if( pIdx->pPartIdxWhere!=0 ) sqlite3VdbeJumpHere(v, jZeroRows);
   674    675       sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   675    676       sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
   676    677       sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
   677    678       sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
          679  +    if( pIdx->pPartIdxWhere==0 ) sqlite3VdbeJumpHere(v, jZeroRows);
   678    680     }
   679    681   
   680         -  /* If the table has no indices, create a single sqlite_stat1 entry
   681         -  ** containing NULL as the index name and the row count as the content.
          682  +  /* Create a single sqlite_stat1 entry containing NULL as the index
          683  +  ** name and the row count as the content.
   682    684     */
   683         -  if( pTab->pIndex==0 ){
          685  +  if( pOnlyIdx==0 && needTableCnt ){
   684    686       sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb);
   685    687       VdbeComment((v, "%s", pTab->zName));
   686    688       sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat1);
   687    689       sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
   688    690       jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1);
   689         -  }else{
          691  +    sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname);
          692  +    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
          693  +    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
          694  +    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
          695  +    sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   690    696       sqlite3VdbeJumpHere(v, jZeroRows);
   691         -    jZeroRows = sqlite3VdbeAddOp0(v, OP_Goto);
   692    697     }
   693         -  sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname);
   694         -  sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   695         -  sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
   696         -  sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
   697         -  sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   698    698     if( pParse->nMem<regRec ) pParse->nMem = regRec;
   699         -  sqlite3VdbeJumpHere(v, jZeroRows);
   700    699   }
   701    700   
   702    701   
   703    702   /*
   704    703   ** Generate code that will cause the most recent index analysis to
   705    704   ** be loaded into internal hash tables where is can be used.
   706    705   */
................................................................................
   875    874     z = argv[2];
   876    875     for(i=0; *z && i<=n; i++){
   877    876       v = 0;
   878    877       while( (c=z[0])>='0' && c<='9' ){
   879    878         v = v*10 + c - '0';
   880    879         z++;
   881    880       }
   882         -    if( i==0 ) pTable->nRowEst = v;
   883         -    if( pIndex==0 ) break;
          881  +    if( i==0 && (pIndex==0 || pIndex->pPartIdxWhere==0) ){
          882  +      if( v>0 ) pTable->nRowEst = v;
          883  +      if( pIndex==0 ) break;
          884  +    }
   884    885       pIndex->aiRowEst[i] = v;
   885    886       if( *z==' ' ) z++;
   886    887       if( strcmp(z, "unordered")==0 ){
   887    888         pIndex->bUnordered = 1;
   888    889         break;
   889    890       }
   890    891     }

Changes to src/build.c.

   378    378   /*
   379    379   ** Reclaim the memory used by an index
   380    380   */
   381    381   static void freeIndex(sqlite3 *db, Index *p){
   382    382   #ifndef SQLITE_OMIT_ANALYZE
   383    383     sqlite3DeleteIndexSamples(db, p);
   384    384   #endif
          385  +  sqlite3ExprDelete(db, p->pPartIdxWhere);
   385    386     sqlite3DbFree(db, p->zColAff);
   386    387     sqlite3DbFree(db, p);
   387    388   }
   388    389   
   389    390   /*
   390    391   ** For the index called zIdxName which is found in the database iDb,
   391    392   ** unlike that index from its Table then remove the index from
................................................................................
  1221   1222     }else if( autoInc ){
  1222   1223   #ifndef SQLITE_OMIT_AUTOINCREMENT
  1223   1224       sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
  1224   1225          "INTEGER PRIMARY KEY");
  1225   1226   #endif
  1226   1227     }else{
  1227   1228       Index *p;
  1228         -    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
         1229  +    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0,
         1230  +                           0, sortOrder, 0);
  1229   1231       if( p ){
  1230   1232         p->autoIndex = 2;
  1231   1233       }
  1232   1234       pList = 0;
  1233   1235     }
  1234   1236   
  1235   1237   primary_key_exit:
................................................................................
  1516   1518   
  1517   1519     iDb = sqlite3SchemaToIndex(db, p->pSchema);
  1518   1520   
  1519   1521   #ifndef SQLITE_OMIT_CHECK
  1520   1522     /* Resolve names in all CHECK constraint expressions.
  1521   1523     */
  1522   1524     if( p->pCheck ){
  1523         -    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
  1524         -    NameContext sNC;                /* Name context for pParse->pNewTable */
  1525         -    ExprList *pList;                /* List of all CHECK constraints */
  1526         -    int i;                          /* Loop counter */
  1527         -
  1528         -    memset(&sNC, 0, sizeof(sNC));
  1529         -    memset(&sSrc, 0, sizeof(sSrc));
  1530         -    sSrc.nSrc = 1;
  1531         -    sSrc.a[0].zName = p->zName;
  1532         -    sSrc.a[0].pTab = p;
  1533         -    sSrc.a[0].iCursor = -1;
  1534         -    sNC.pParse = pParse;
  1535         -    sNC.pSrcList = &sSrc;
  1536         -    sNC.ncFlags = NC_IsCheck;
  1537         -    pList = p->pCheck;
  1538         -    for(i=0; i<pList->nExpr; i++){
  1539         -      if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
  1540         -        return;
  1541         -      }
  1542         -    }
         1525  +    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
  1543   1526     }
  1544   1527   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1545   1528   
  1546   1529     /* If the db->init.busy is 1 it means we are reading the SQL off the
  1547   1530     ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  1548   1531     ** So do not write to the disk again.  Extract the root page number
  1549   1532     ** for the table from the db->init.newTnum field.  (The page number
................................................................................
  2387   2370     Table *pTab = pIndex->pTable;  /* The table that is indexed */
  2388   2371     int iTab = pParse->nTab++;     /* Btree cursor used for pTab */
  2389   2372     int iIdx = pParse->nTab++;     /* Btree cursor used for pIndex */
  2390   2373     int iSorter;                   /* Cursor opened by OpenSorter (if in use) */
  2391   2374     int addr1;                     /* Address of top of loop */
  2392   2375     int addr2;                     /* Address to jump to for next iteration */
  2393   2376     int tnum;                      /* Root page of index */
         2377  +  int iPartIdxLabel;             /* Jump to this label to skip a row */
  2394   2378     Vdbe *v;                       /* Generate code into this virtual machine */
  2395   2379     KeyInfo *pKey;                 /* KeyInfo for index */
  2396   2380     int regRecord;                 /* Register holding assemblied index record */
  2397   2381     sqlite3 *db = pParse->db;      /* The database connection */
  2398   2382     int iDb = sqlite3SchemaToIndex(db, pIndex->pSchema);
  2399   2383   
  2400   2384   #ifndef SQLITE_OMIT_AUTHORIZATION
................................................................................
  2426   2410   
  2427   2411     /* Open the table. Loop through all rows of the table, inserting index
  2428   2412     ** records into the sorter. */
  2429   2413     sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
  2430   2414     addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
  2431   2415     regRecord = sqlite3GetTempReg(pParse);
  2432   2416   
  2433         -  sqlite3GenerateIndexKey(pParse, pIndex, iTab, regRecord, 1);
         2417  +  sqlite3GenerateIndexKey(pParse, pIndex, iTab, regRecord, 1, &iPartIdxLabel);
  2434   2418     sqlite3VdbeAddOp2(v, OP_SorterInsert, iSorter, regRecord);
         2419  +  sqlite3VdbeResolveLabel(v, iPartIdxLabel);
  2435   2420     sqlite3VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  2436   2421     sqlite3VdbeJumpHere(v, addr1);
  2437   2422     addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0);
  2438   2423     if( pIndex->onError!=OE_None ){
  2439   2424       int j2 = sqlite3VdbeCurrentAddr(v) + 3;
  2440   2425       sqlite3VdbeAddOp2(v, OP_Goto, 0, j2);
  2441   2426       addr2 = sqlite3VdbeCurrentAddr(v);
................................................................................
  2478   2463     Parse *pParse,     /* All information about this parse */
  2479   2464     Token *pName1,     /* First part of index name. May be NULL */
  2480   2465     Token *pName2,     /* Second part of index name. May be NULL */
  2481   2466     SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */
  2482   2467     ExprList *pList,   /* A list of columns to be indexed */
  2483   2468     int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  2484   2469     Token *pStart,     /* The CREATE token that begins this statement */
  2485         -  Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
         2470  +  Expr *pPIWhere,    /* WHERE clause for partial indices */
  2486   2471     int sortOrder,     /* Sort order of primary key when pList==NULL */
  2487   2472     int ifNotExist     /* Omit error if index already exists */
  2488   2473   ){
  2489   2474     Index *pRet = 0;     /* Pointer to return */
  2490   2475     Table *pTab = 0;     /* Table to be indexed */
  2491   2476     Index *pIndex = 0;   /* The index to be created */
  2492   2477     char *zName = 0;     /* Name of the index */
................................................................................
  2500   2485     int iDb;             /* Index of the database that is being written */
  2501   2486     Token *pName = 0;    /* Unqualified name of the index to create */
  2502   2487     struct ExprList_item *pListItem; /* For looping over pList */
  2503   2488     int nCol;
  2504   2489     int nExtra = 0;
  2505   2490     char *zExtra;
  2506   2491   
  2507         -  assert( pStart==0 || pEnd!=0 ); /* pEnd must be non-NULL if pStart is */
  2508   2492     assert( pParse->nErr==0 );      /* Never called with prior errors */
  2509   2493     if( db->mallocFailed || IN_DECLARE_VTAB ){
  2510   2494       goto exit_create_index;
  2511   2495     }
  2512   2496     if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
  2513   2497       goto exit_create_index;
  2514   2498     }
................................................................................
  2695   2679     memcpy(pIndex->zName, zName, nName+1);
  2696   2680     pIndex->pTable = pTab;
  2697   2681     pIndex->nColumn = pList->nExpr;
  2698   2682     pIndex->onError = (u8)onError;
  2699   2683     pIndex->uniqNotNull = onError==OE_Abort;
  2700   2684     pIndex->autoIndex = (u8)(pName==0);
  2701   2685     pIndex->pSchema = db->aDb[iDb].pSchema;
         2686  +  if( pPIWhere ){
         2687  +    sqlite3ResolveSelfReference(pParse, pTab, NC_PartIdx, pPIWhere, 0);
         2688  +    pIndex->pPartIdxWhere = pPIWhere;
         2689  +    pPIWhere = 0;
         2690  +  }
  2702   2691     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  2703   2692   
  2704   2693     /* Check to see if we should honor DESC requests on index columns
  2705   2694     */
  2706   2695     if( pDb->pSchema->file_format>=4 ){
  2707   2696       sortOrderMask = -1;   /* Honor DESC */
  2708   2697     }else{
................................................................................
  2850   2839     ** we don't want to recreate it.
  2851   2840     **
  2852   2841     ** If pTblName==0 it means this index is generated as a primary key
  2853   2842     ** or UNIQUE constraint of a CREATE TABLE statement.  Since the table
  2854   2843     ** has just been created, it contains no data and the index initialization
  2855   2844     ** step can be skipped.
  2856   2845     */
  2857         -  else{ /* if( db->init.busy==0 ) */
         2846  +  else if( pParse->nErr==0 ){
  2858   2847       Vdbe *v;
  2859   2848       char *zStmt;
  2860   2849       int iMem = ++pParse->nMem;
  2861   2850   
  2862   2851       v = sqlite3GetVdbe(pParse);
  2863   2852       if( v==0 ) goto exit_create_index;
  2864   2853   
................................................................................
  2868   2857       sqlite3BeginWriteOperation(pParse, 1, iDb);
  2869   2858       sqlite3VdbeAddOp2(v, OP_CreateIndex, iDb, iMem);
  2870   2859   
  2871   2860       /* Gather the complete text of the CREATE INDEX statement into
  2872   2861       ** the zStmt variable
  2873   2862       */
  2874   2863       if( pStart ){
  2875         -      assert( pEnd!=0 );
         2864  +      int n = (pParse->sLastToken.z - pName->z) + pParse->sLastToken.n;
         2865  +      if( pName->z[n-1]==';' ) n--;
  2876   2866         /* A named index with an explicit CREATE INDEX statement */
  2877   2867         zStmt = sqlite3MPrintf(db, "CREATE%s INDEX %.*s",
  2878         -        onError==OE_None ? "" : " UNIQUE",
  2879         -        (int)(pEnd->z - pName->z) + 1,
  2880         -        pName->z);
         2868  +        onError==OE_None ? "" : " UNIQUE", n, pName->z);
  2881   2869       }else{
  2882   2870         /* An automatic index created by a PRIMARY KEY or UNIQUE constraint */
  2883   2871         /* zStmt = sqlite3MPrintf(""); */
  2884   2872         zStmt = 0;
  2885   2873       }
  2886   2874   
  2887   2875       /* Add an entry in sqlite_master for this index
................................................................................
  2929   2917       }
  2930   2918       pRet = pIndex;
  2931   2919       pIndex = 0;
  2932   2920     }
  2933   2921   
  2934   2922     /* Clean up before exiting */
  2935   2923   exit_create_index:
  2936         -  if( pIndex ){
  2937         -    sqlite3DbFree(db, pIndex->zColAff);
  2938         -    sqlite3DbFree(db, pIndex);
  2939         -  }
         2924  +  if( pIndex ) freeIndex(db, pIndex);
         2925  +  sqlite3ExprDelete(db, pPIWhere);
  2940   2926     sqlite3ExprListDelete(db, pList);
  2941   2927     sqlite3SrcListDelete(db, pTblName);
  2942   2928     sqlite3DbFree(db, zName);
  2943   2929     return pRet;
  2944   2930   }
  2945   2931   
  2946   2932   /*

Changes to src/delete.c.

   587    587     Table *pTab,       /* Table containing the row to be deleted */
   588    588     int iCur,          /* Cursor number for the table */
   589    589     int *aRegIdx       /* Only delete if aRegIdx!=0 && aRegIdx[i]>0 */
   590    590   ){
   591    591     int i;
   592    592     Index *pIdx;
   593    593     int r1;
          594  +  int iPartIdxLabel;
          595  +  Vdbe *v = pParse->pVdbe;
   594    596   
   595    597     for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   596    598       if( aRegIdx!=0 && aRegIdx[i-1]==0 ) continue;
   597         -    r1 = sqlite3GenerateIndexKey(pParse, pIdx, iCur, 0, 0);
   598         -    sqlite3VdbeAddOp3(pParse->pVdbe, OP_IdxDelete, iCur+i, r1,pIdx->nColumn+1);
          599  +    r1 = sqlite3GenerateIndexKey(pParse, pIdx, iCur, 0, 0, &iPartIdxLabel);
          600  +    sqlite3VdbeAddOp3(v, OP_IdxDelete, iCur+i, r1, pIdx->nColumn+1);
          601  +    sqlite3VdbeResolveLabel(v, iPartIdxLabel);
   599    602     }
   600    603   }
   601    604   
   602    605   /*
   603    606   ** Generate code that will assemble an index key and put it in register
   604    607   ** regOut.  The key with be for index pIdx which is an index on pTab.
   605    608   ** iCur is the index of a cursor open on the pTab table and pointing to
   606    609   ** the entry that needs indexing.
   607    610   **
   608    611   ** Return a register number which is the first in a block of
   609    612   ** registers that holds the elements of the index key.  The
   610    613   ** block of registers has already been deallocated by the time
   611    614   ** this routine returns.
          615  +**
          616  +** If *piPartIdxLabel is not NULL, fill it in with a label and jump
          617  +** to that label if pIdx is a partial index that should be skipped.
          618  +** A partial index should be skipped if its WHERE clause evaluates
          619  +** to false or null.  If pIdx is not a partial index, *piPartIdxLabel
          620  +** will be set to zero which is an empty label that is ignored by
          621  +** sqlite3VdbeResolveLabel().
   612    622   */
   613    623   int sqlite3GenerateIndexKey(
   614         -  Parse *pParse,     /* Parsing context */
   615         -  Index *pIdx,       /* The index for which to generate a key */
   616         -  int iCur,          /* Cursor number for the pIdx->pTable table */
   617         -  int regOut,        /* Write the new index key to this register */
   618         -  int doMakeRec      /* Run the OP_MakeRecord instruction if true */
          624  +  Parse *pParse,       /* Parsing context */
          625  +  Index *pIdx,         /* The index for which to generate a key */
          626  +  int iCur,            /* Cursor number for the pIdx->pTable table */
          627  +  int regOut,          /* Write the new index key to this register */
          628  +  int doMakeRec,       /* Run the OP_MakeRecord instruction if true */
          629  +  int *piPartIdxLabel  /* OUT: Jump to this label to skip partial index */
   619    630   ){
   620    631     Vdbe *v = pParse->pVdbe;
   621    632     int j;
   622    633     Table *pTab = pIdx->pTable;
   623    634     int regBase;
   624    635     int nCol;
   625    636   
          637  +  if( piPartIdxLabel ){
          638  +    if( pIdx->pPartIdxWhere ){
          639  +      *piPartIdxLabel = sqlite3VdbeMakeLabel(v);
          640  +      pParse->iPartIdxTab = iCur;
          641  +      sqlite3ExprIfFalse(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel, 
          642  +                         SQLITE_JUMPIFNULL);
          643  +    }else{
          644  +      *piPartIdxLabel = 0;
          645  +    }
          646  +  }
   626    647     nCol = pIdx->nColumn;
   627    648     regBase = sqlite3GetTempRange(pParse, nCol+1);
   628    649     sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regBase+nCol);
   629    650     for(j=0; j<nCol; j++){
   630    651       int idx = pIdx->aiColumn[j];
   631    652       if( idx==pTab->iPKey ){
   632    653         sqlite3VdbeAddOp2(v, OP_SCopy, regBase+nCol, regBase+j);

Changes to src/expr.c.

  2358   2358           sqlite3VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdxPTab,
  2359   2359                                 pCol->iSorterColumn, target);
  2360   2360           break;
  2361   2361         }
  2362   2362         /* Otherwise, fall thru into the TK_COLUMN case */
  2363   2363       }
  2364   2364       case TK_COLUMN: {
  2365         -      if( pExpr->iTable<0 ){
  2366         -        /* This only happens when coding check constraints */
  2367         -        assert( pParse->ckBase>0 );
  2368         -        inReg = pExpr->iColumn + pParse->ckBase;
  2369         -      }else{
  2370         -        inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
  2371         -                                 pExpr->iColumn, pExpr->iTable, target,
  2372         -                                 pExpr->op2);
         2365  +      int iTab = pExpr->iTable;
         2366  +      if( iTab<0 ){
         2367  +        if( pParse->ckBase>0 ){
         2368  +          /* Generating CHECK constraints or inserting into partial index */
         2369  +          inReg = pExpr->iColumn + pParse->ckBase;
         2370  +          break;
         2371  +        }else{
         2372  +          /* Deleting from a partial index */
         2373  +          iTab = pParse->iPartIdxTab;
         2374  +        }
  2373   2375         }
         2376  +      inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
         2377  +                               pExpr->iColumn, iTab, target,
         2378  +                               pExpr->op2);
  2374   2379         break;
  2375   2380       }
  2376   2381       case TK_INTEGER: {
  2377   2382         codeInteger(pParse, pExpr, 0, target);
  2378   2383         break;
  2379   2384       }
  2380   2385   #ifndef SQLITE_OMIT_FLOATING_POINT
................................................................................
  3789   3794   
  3790   3795   /*
  3791   3796   ** Do a deep comparison of two expression trees.  Return 0 if the two
  3792   3797   ** expressions are completely identical.  Return 1 if they differ only
  3793   3798   ** by a COLLATE operator at the top level.  Return 2 if there are differences
  3794   3799   ** other than the top-level COLLATE operator.
  3795   3800   **
         3801  +** If any subelement of pB has Expr.iTable==(-1) then it is allowed
         3802  +** to compare equal to an equivalent element in pA with Expr.iTable==iTab.
         3803  +**
         3804  +** The pA side might be using TK_REGISTER.  If that is the case and pB is
         3805  +** not using TK_REGISTER but is otherwise equivalent, then still return 0.
         3806  +**
  3796   3807   ** Sometimes this routine will return 2 even if the two expressions
  3797   3808   ** really are equivalent.  If we cannot prove that the expressions are
  3798   3809   ** identical, we return 2 just to be safe.  So if this routine
  3799   3810   ** returns 2, then you do not really know for certain if the two
  3800   3811   ** expressions are the same.  But if you get a 0 or 1 return, then you
  3801   3812   ** can be sure the expressions are the same.  In the places where
  3802   3813   ** this routine is used, it does not hurt to get an extra 2 - that
  3803   3814   ** just might result in some slightly slower code.  But returning
  3804   3815   ** an incorrect 0 or 1 could lead to a malfunction.
  3805   3816   */
  3806         -int sqlite3ExprCompare(Expr *pA, Expr *pB){
         3817  +int sqlite3ExprCompare(Expr *pA, Expr *pB, int iTab){
  3807   3818     if( pA==0||pB==0 ){
  3808   3819       return pB==pA ? 0 : 2;
  3809   3820     }
  3810   3821     assert( !ExprHasAnyProperty(pA, EP_TokenOnly|EP_Reduced) );
  3811   3822     assert( !ExprHasAnyProperty(pB, EP_TokenOnly|EP_Reduced) );
  3812   3823     if( ExprHasProperty(pA, EP_xIsSelect) || ExprHasProperty(pB, EP_xIsSelect) ){
  3813   3824       return 2;
  3814   3825     }
  3815   3826     if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
  3816         -  if( pA->op!=pB->op ){
  3817         -    if( pA->op==TK_COLLATE && sqlite3ExprCompare(pA->pLeft, pB)<2 ){
         3827  +  if( pA->op!=pB->op && (pA->op!=TK_REGISTER || pA->op2!=pB->op) ){
         3828  +    if( pA->op==TK_COLLATE && sqlite3ExprCompare(pA->pLeft, pB, iTab)<2 ){
  3818   3829         return 1;
  3819   3830       }
  3820         -    if( pB->op==TK_COLLATE && sqlite3ExprCompare(pA, pB->pLeft)<2 ){
         3831  +    if( pB->op==TK_COLLATE && sqlite3ExprCompare(pA, pB->pLeft, iTab)<2 ){
  3821   3832         return 1;
  3822   3833       }
  3823   3834       return 2;
  3824   3835     }
  3825         -  if( sqlite3ExprCompare(pA->pLeft, pB->pLeft) ) return 2;
  3826         -  if( sqlite3ExprCompare(pA->pRight, pB->pRight) ) return 2;
  3827         -  if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList) ) return 2;
  3828         -  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 2;
         3836  +  if( sqlite3ExprCompare(pA->pLeft, pB->pLeft, iTab) ) return 2;
         3837  +  if( sqlite3ExprCompare(pA->pRight, pB->pRight, iTab) ) return 2;
         3838  +  if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList, iTab) ) return 2;
         3839  +  if( pA->iColumn!=pB->iColumn ) return 2;
         3840  +  if( pA->iTable!=pB->iTable 
         3841  +   && pA->op!=TK_REGISTER
         3842  +   && (pA->iTable!=iTab || pB->iTable>=0) ) return 2;
  3829   3843     if( ExprHasProperty(pA, EP_IntValue) ){
  3830   3844       if( !ExprHasProperty(pB, EP_IntValue) || pA->u.iValue!=pB->u.iValue ){
  3831   3845         return 2;
  3832   3846       }
  3833   3847     }else if( pA->op!=TK_COLUMN && ALWAYS(pA->op!=TK_AGG_COLUMN) && pA->u.zToken){
  3834   3848       if( ExprHasProperty(pB, EP_IntValue) || NEVER(pB->u.zToken==0) ) return 2;
  3835   3849       if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
................................................................................
  3839   3853     return 0;
  3840   3854   }
  3841   3855   
  3842   3856   /*
  3843   3857   ** Compare two ExprList objects.  Return 0 if they are identical and 
  3844   3858   ** non-zero if they differ in any way.
  3845   3859   **
         3860  +** If any subelement of pB has Expr.iTable==(-1) then it is allowed
         3861  +** to compare equal to an equivalent element in pA with Expr.iTable==iTab.
         3862  +**
  3846   3863   ** This routine might return non-zero for equivalent ExprLists.  The
  3847   3864   ** only consequence will be disabled optimizations.  But this routine
  3848   3865   ** must never return 0 if the two ExprList objects are different, or
  3849   3866   ** a malfunction will result.
  3850   3867   **
  3851   3868   ** Two NULL pointers are considered to be the same.  But a NULL pointer
  3852   3869   ** always differs from a non-NULL pointer.
  3853   3870   */
  3854         -int sqlite3ExprListCompare(ExprList *pA, ExprList *pB){
         3871  +int sqlite3ExprListCompare(ExprList *pA, ExprList *pB, int iTab){
  3855   3872     int i;
  3856   3873     if( pA==0 && pB==0 ) return 0;
  3857   3874     if( pA==0 || pB==0 ) return 1;
  3858   3875     if( pA->nExpr!=pB->nExpr ) return 1;
  3859   3876     for(i=0; i<pA->nExpr; i++){
  3860   3877       Expr *pExprA = pA->a[i].pExpr;
  3861   3878       Expr *pExprB = pB->a[i].pExpr;
  3862   3879       if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1;
  3863         -    if( sqlite3ExprCompare(pExprA, pExprB) ) return 1;
         3880  +    if( sqlite3ExprCompare(pExprA, pExprB, iTab) ) return 1;
         3881  +  }
         3882  +  return 0;
         3883  +}
         3884  +
         3885  +/*
         3886  +** Return true if we can prove the pE2 will always be true if pE1 is
         3887  +** true.  Return false if we cannot complete the proof or if pE2 might
         3888  +** be false.  Examples:
         3889  +**
         3890  +**     pE1: x==5       pE2: x==5             Result: true
         3891  +**     pE1: x>0        pE2: x==5             Result: false
         3892  +**     pE1: x=21       pE2: x=21 OR y=43     Result: true
         3893  +**     pE1: x!=123     pE2: x IS NOT NULL    Result: true
         3894  +**     pE1: x!=?1      pE2: x IS NOT NULL    Result: true
         3895  +**     pE1: x IS NULL  pE2: x IS NOT NULL    Result: false
         3896  +**     pE1: x IS ?2    pE2: x IS NOT NULL    Reuslt: false
         3897  +**
         3898  +** When comparing TK_COLUMN nodes between pE1 and pE2, if pE2 has
         3899  +** Expr.iTable<0 then assume a table number given by iTab.
         3900  +**
         3901  +** When in doubt, return false.  Returning true might give a performance
         3902  +** improvement.  Returning false might cause a performance reduction, but
         3903  +** it will always give the correct answer and is hence always safe.
         3904  +*/
         3905  +int sqlite3ExprImpliesExpr(Expr *pE1, Expr *pE2, int iTab){
         3906  +  if( sqlite3ExprCompare(pE1, pE2, iTab)==0 ){
         3907  +    return 1;
         3908  +  }
         3909  +  if( pE2->op==TK_OR
         3910  +   && (sqlite3ExprImpliesExpr(pE1, pE2->pLeft, iTab)
         3911  +             || sqlite3ExprImpliesExpr(pE1, pE2->pRight, iTab) )
         3912  +  ){
         3913  +    return 1;
         3914  +  }
         3915  +  if( pE2->op==TK_NOTNULL
         3916  +   && sqlite3ExprCompare(pE1->pLeft, pE2->pLeft, iTab)==0
         3917  +   && (pE1->op!=TK_ISNULL && pE1->op!=TK_IS)
         3918  +  ){
         3919  +    return 1;
  3864   3920     }
  3865   3921     return 0;
  3866   3922   }
  3867   3923   
  3868   3924   /*
  3869   3925   ** An instance of the following structure is used by the tree walker
  3870   3926   ** to count references to table columns in the arguments of an 
................................................................................
  4041   4097          && pWalker->walkerDepth==pExpr->op2
  4042   4098         ){
  4043   4099           /* Check to see if pExpr is a duplicate of another aggregate 
  4044   4100           ** function that is already in the pAggInfo structure
  4045   4101           */
  4046   4102           struct AggInfo_func *pItem = pAggInfo->aFunc;
  4047   4103           for(i=0; i<pAggInfo->nFunc; i++, pItem++){
  4048         -          if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){
         4104  +          if( sqlite3ExprCompare(pItem->pExpr, pExpr, -1)==0 ){
  4049   4105               break;
  4050   4106             }
  4051   4107           }
  4052   4108           if( i>=pAggInfo->nFunc ){
  4053   4109             /* pExpr is original.  Make a new entry in pAggInfo->aFunc[]
  4054   4110             */
  4055   4111             u8 enc = ENC(pParse->db);

Changes to src/insert.c.

  1375   1375     /* Test all UNIQUE constraints by creating entries for each UNIQUE
  1376   1376     ** index and making sure that duplicate entries do not already exist.
  1377   1377     ** Add the new records to the indices as we go.
  1378   1378     */
  1379   1379     for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
  1380   1380       int regIdx;
  1381   1381       int regR;
         1382  +    int addrSkipRow = 0;
  1382   1383   
  1383   1384       if( aRegIdx[iCur]==0 ) continue;  /* Skip unused indices */
         1385  +
         1386  +    if( pIdx->pPartIdxWhere ){
         1387  +      sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[iCur]);
         1388  +      addrSkipRow = sqlite3VdbeMakeLabel(v);
         1389  +      pParse->ckBase = regData;
         1390  +      sqlite3ExprIfFalse(pParse, pIdx->pPartIdxWhere, addrSkipRow,
         1391  +                         SQLITE_JUMPIFNULL);
         1392  +      pParse->ckBase = 0;
         1393  +    }
  1384   1394   
  1385   1395       /* Create a key for accessing the index entry */
  1386   1396       regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn+1);
  1387   1397       for(i=0; i<pIdx->nColumn; i++){
  1388   1398         int idx = pIdx->aiColumn[i];
  1389   1399         if( idx==pTab->iPKey ){
  1390   1400           sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
................................................................................
  1397   1407       sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), P4_TRANSIENT);
  1398   1408       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);
  1399   1409   
  1400   1410       /* Find out what action to take in case there is an indexing conflict */
  1401   1411       onError = pIdx->onError;
  1402   1412       if( onError==OE_None ){ 
  1403   1413         sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
         1414  +      sqlite3VdbeResolveLabel(v, addrSkipRow);
  1404   1415         continue;  /* pIdx is not a UNIQUE index */
  1405   1416       }
  1406   1417       if( overrideError!=OE_Default ){
  1407   1418         onError = overrideError;
  1408   1419       }else if( onError==OE_Default ){
  1409   1420         onError = OE_Abort;
  1410   1421       }
................................................................................
  1466   1477               pParse, pTab, baseCur, regR, 0, pTrigger, OE_Replace
  1467   1478           );
  1468   1479           seenReplace = 1;
  1469   1480           break;
  1470   1481         }
  1471   1482       }
  1472   1483       sqlite3VdbeJumpHere(v, j3);
         1484  +    sqlite3VdbeResolveLabel(v, addrSkipRow);
  1473   1485       sqlite3ReleaseTempReg(pParse, regR);
  1474   1486     }
  1475   1487     
  1476   1488     if( pbMayReplace ){
  1477   1489       *pbMayReplace = seenReplace;
  1478   1490     }
  1479   1491   }
................................................................................
  1495   1507     int *aRegIdx,       /* Register used by each index.  0 for unused indices */
  1496   1508     int isUpdate,       /* True for UPDATE, False for INSERT */
  1497   1509     int appendBias,     /* True if this is likely to be an append */
  1498   1510     int useSeekResult   /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
  1499   1511   ){
  1500   1512     int i;
  1501   1513     Vdbe *v;
  1502         -  int nIdx;
  1503   1514     Index *pIdx;
  1504   1515     u8 pik_flags;
  1505   1516     int regData;
  1506   1517     int regRec;
  1507   1518   
  1508   1519     v = sqlite3GetVdbe(pParse);
  1509   1520     assert( v!=0 );
  1510   1521     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1511         -  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  1512         -  for(i=nIdx-1; i>=0; i--){
         1522  +  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1513   1523       if( aRegIdx[i]==0 ) continue;
         1524  +    if( pIdx->pPartIdxWhere ){
         1525  +      sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
         1526  +    }
  1514   1527       sqlite3VdbeAddOp2(v, OP_IdxInsert, baseCur+i+1, aRegIdx[i]);
  1515   1528       if( useSeekResult ){
  1516   1529         sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  1517   1530       }
  1518   1531     }
  1519   1532     regData = regRowid + 1;
  1520   1533     regRec = sqlite3GetTempReg(pParse);
................................................................................
  1608   1621   ** for index pDest in an insert transfer optimization.  The rules
  1609   1622   ** for a compatible index:
  1610   1623   **
  1611   1624   **    *   The index is over the same set of columns
  1612   1625   **    *   The same DESC and ASC markings occurs on all columns
  1613   1626   **    *   The same onError processing (OE_Abort, OE_Ignore, etc)
  1614   1627   **    *   The same collating sequence on each column
         1628  +**    *   The index has the exact same WHERE clause
  1615   1629   */
  1616   1630   static int xferCompatibleIndex(Index *pDest, Index *pSrc){
  1617   1631     int i;
  1618   1632     assert( pDest && pSrc );
  1619   1633     assert( pDest->pTable!=pSrc->pTable );
  1620   1634     if( pDest->nColumn!=pSrc->nColumn ){
  1621   1635       return 0;   /* Different number of columns */
................................................................................
  1629   1643       }
  1630   1644       if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
  1631   1645         return 0;   /* Different sort orders */
  1632   1646       }
  1633   1647       if( !xferCompatibleCollation(pSrc->azColl[i],pDest->azColl[i]) ){
  1634   1648         return 0;   /* Different collating sequences */
  1635   1649       }
         1650  +  }
         1651  +  if( sqlite3ExprCompare(pSrc->pPartIdxWhere, pDest->pPartIdxWhere, -1) ){
         1652  +    return 0;     /* Different WHERE clauses */
  1636   1653     }
  1637   1654   
  1638   1655     /* If no test above fails then the indices must be compatible */
  1639   1656     return 1;
  1640   1657   }
  1641   1658   
  1642   1659   /*
................................................................................
  1785   1802         if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
  1786   1803       }
  1787   1804       if( pSrcIdx==0 ){
  1788   1805         return 0;    /* pDestIdx has no corresponding index in pSrc */
  1789   1806       }
  1790   1807     }
  1791   1808   #ifndef SQLITE_OMIT_CHECK
  1792         -  if( pDest->pCheck && sqlite3ExprListCompare(pSrc->pCheck, pDest->pCheck) ){
         1809  +  if( pDest->pCheck && sqlite3ExprListCompare(pSrc->pCheck,pDest->pCheck,-1) ){
  1793   1810       return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
  1794   1811     }
  1795   1812   #endif
  1796   1813   #ifndef SQLITE_OMIT_FOREIGN_KEY
  1797   1814     /* Disallow the transfer optimization if the destination table constains
  1798   1815     ** any foreign key constraints.  This is more restrictive than necessary.
  1799   1816     ** But the main beneficiary of the transfer optimization is the VACUUM 

Changes to src/parse.y.

  1121   1121   nexprlist(A) ::= expr(Y).
  1122   1122       {A = sqlite3ExprListAppend(pParse,0,Y.pExpr);}
  1123   1123   
  1124   1124   
  1125   1125   ///////////////////////////// The CREATE INDEX command ///////////////////////
  1126   1126   //
  1127   1127   cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
  1128         -        ON nm(Y) LP idxlist(Z) RP(E). {
         1128  +        ON nm(Y) LP idxlist(Z) RP where_opt(W). {
  1129   1129     sqlite3CreateIndex(pParse, &X, &D, 
  1130   1130                        sqlite3SrcListAppend(pParse->db,0,&Y,0), Z, U,
  1131         -                      &S, &E, SQLITE_SO_ASC, NE);
         1131  +                      &S, W, SQLITE_SO_ASC, NE);
  1132   1132   }
  1133   1133   
  1134   1134   %type uniqueflag {int}
  1135   1135   uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
  1136   1136   uniqueflag(A) ::= .        {A = OE_None;}
  1137   1137   
  1138   1138   %type idxlist {ExprList*}

Changes to src/pragma.c.

  1396   1396           for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1397   1397             sqlite3VdbeAddOp2(v, OP_Integer, pIdx->tnum, 2+cnt);
  1398   1398             cnt++;
  1399   1399           }
  1400   1400         }
  1401   1401   
  1402   1402         /* Make sure sufficient number of registers have been allocated */
  1403         -      if( pParse->nMem < cnt+4 ){
  1404         -        pParse->nMem = cnt+4;
  1405         -      }
         1403  +      pParse->nMem = MAX( pParse->nMem, cnt+7 );
  1406   1404   
  1407   1405         /* Do the b-tree integrity checks */
  1408   1406         sqlite3VdbeAddOp3(v, OP_IntegrityCk, 2, cnt, 1);
  1409   1407         sqlite3VdbeChangeP5(v, (u8)i);
  1410   1408         addr = sqlite3VdbeAddOp1(v, OP_IsNull, 2);
  1411   1409         sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0,
  1412   1410            sqlite3MPrintf(db, "*** in database %s ***\n", db->aDb[i].zName),
................................................................................
  1423   1421           Index *pIdx;
  1424   1422           int loopTop;
  1425   1423   
  1426   1424           if( pTab->pIndex==0 ) continue;
  1427   1425           addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1);  /* Stop if out of errors */
  1428   1426           sqlite3VdbeAddOp2(v, OP_Halt, 0, 0);
  1429   1427           sqlite3VdbeJumpHere(v, addr);
         1428  +        sqlite3ExprCacheClear(pParse);
  1430   1429           sqlite3OpenTableAndIndices(pParse, pTab, 1, OP_OpenRead);
  1431         -        sqlite3VdbeAddOp2(v, OP_Integer, 0, 2);  /* reg(2) will count entries */
  1432         -        loopTop = sqlite3VdbeAddOp2(v, OP_Rewind, 1, 0);
  1433         -        sqlite3VdbeAddOp2(v, OP_AddImm, 2, 1);   /* increment entry count */
         1430  +        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
         1431  +          sqlite3VdbeAddOp2(v, OP_Integer, 0, 7+j); /* index entries counter */
         1432  +        }
         1433  +        pParse->nMem = MAX(pParse->nMem, 7+j);
         1434  +        loopTop = sqlite3VdbeAddOp2(v, OP_Rewind, 1, 0) + 1;
  1434   1435           for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1435         -          int jmp2;
         1436  +          int jmp2, jmp3;
  1436   1437             int r1;
  1437   1438             static const VdbeOpList idxErr[] = {
  1438   1439               { OP_AddImm,      1, -1,  0},
  1439   1440               { OP_String8,     0,  3,  0},    /* 1 */
  1440   1441               { OP_Rowid,       1,  4,  0},
  1441   1442               { OP_String8,     0,  5,  0},    /* 3 */
  1442   1443               { OP_String8,     0,  6,  0},    /* 4 */
................................................................................
  1443   1444               { OP_Concat,      4,  3,  3},
  1444   1445               { OP_Concat,      5,  3,  3},
  1445   1446               { OP_Concat,      6,  3,  3},
  1446   1447               { OP_ResultRow,   3,  1,  0},
  1447   1448               { OP_IfPos,       1,  0,  0},    /* 9 */
  1448   1449               { OP_Halt,        0,  0,  0},
  1449   1450             };
  1450         -          r1 = sqlite3GenerateIndexKey(pParse, pIdx, 1, 3, 0);
         1451  +          r1 = sqlite3GenerateIndexKey(pParse, pIdx, 1, 3, 0, &jmp3);
         1452  +          sqlite3VdbeAddOp2(v, OP_AddImm, 7+j, 1);  /* increment entry count */
  1451   1453             jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, j+2, 0, r1, pIdx->nColumn+1);
  1452   1454             addr = sqlite3VdbeAddOpList(v, ArraySize(idxErr), idxErr);
  1453   1455             sqlite3VdbeChangeP4(v, addr+1, "rowid ", P4_STATIC);
  1454   1456             sqlite3VdbeChangeP4(v, addr+3, " missing from index ", P4_STATIC);
  1455   1457             sqlite3VdbeChangeP4(v, addr+4, pIdx->zName, P4_TRANSIENT);
  1456   1458             sqlite3VdbeJumpHere(v, addr+9);
  1457   1459             sqlite3VdbeJumpHere(v, jmp2);
         1460  +          sqlite3VdbeResolveLabel(v, jmp3);
  1458   1461           }
  1459         -        sqlite3VdbeAddOp2(v, OP_Next, 1, loopTop+1);
  1460         -        sqlite3VdbeJumpHere(v, loopTop);
         1462  +        sqlite3VdbeAddOp2(v, OP_Next, 1, loopTop);
         1463  +        sqlite3VdbeJumpHere(v, loopTop-1);
         1464  +#ifndef SQLITE_OMIT_BTREECOUNT
         1465  +        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, 
         1466  +                     "wrong # of entries in index ", P4_STATIC);
  1461   1467           for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1462         -          static const VdbeOpList cntIdx[] = {
  1463         -             { OP_Integer,      0,  3,  0},
  1464         -             { OP_Rewind,       0,  0,  0},  /* 1 */
  1465         -             { OP_AddImm,       3,  1,  0},
  1466         -             { OP_Next,         0,  0,  0},  /* 3 */
  1467         -             { OP_Eq,           2,  0,  3},  /* 4 */
  1468         -             { OP_AddImm,       1, -1,  0},
  1469         -             { OP_String8,      0,  2,  0},  /* 6 */
  1470         -             { OP_String8,      0,  3,  0},  /* 7 */
  1471         -             { OP_Concat,       3,  2,  2},
  1472         -             { OP_ResultRow,    2,  1,  0},
  1473         -          };
  1474         -          addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1);
         1468  +          addr = sqlite3VdbeCurrentAddr(v);
         1469  +          sqlite3VdbeAddOp2(v, OP_IfPos, 1, addr+2);
  1475   1470             sqlite3VdbeAddOp2(v, OP_Halt, 0, 0);
  1476         -          sqlite3VdbeJumpHere(v, addr);
  1477         -          addr = sqlite3VdbeAddOpList(v, ArraySize(cntIdx), cntIdx);
  1478         -          sqlite3VdbeChangeP1(v, addr+1, j+2);
  1479         -          sqlite3VdbeChangeP2(v, addr+1, addr+4);
  1480         -          sqlite3VdbeChangeP1(v, addr+3, j+2);
  1481         -          sqlite3VdbeChangeP2(v, addr+3, addr+2);
  1482         -          sqlite3VdbeJumpHere(v, addr+4);
  1483         -          sqlite3VdbeChangeP4(v, addr+6, 
  1484         -                     "wrong # of entries in index ", P4_STATIC);
  1485         -          sqlite3VdbeChangeP4(v, addr+7, pIdx->zName, P4_TRANSIENT);
         1471  +          sqlite3VdbeAddOp2(v, OP_Count, j+2, 3);
         1472  +          sqlite3VdbeAddOp3(v, OP_Eq, 7+j, addr+8, 3);
         1473  +          sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1);
         1474  +          sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pIdx->zName, P4_TRANSIENT);
         1475  +          sqlite3VdbeAddOp3(v, OP_Concat, 3, 2, 7);
         1476  +          sqlite3VdbeAddOp2(v, OP_ResultRow, 7, 1);
  1486   1477           }
         1478  +#endif /* SQLITE_OMIT_BTREECOUNT */
  1487   1479         } 
  1488   1480       }
  1489   1481       addr = sqlite3VdbeAddOpList(v, ArraySize(endCode), endCode);
  1490   1482       sqlite3VdbeChangeP2(v, addr, -mxErr);
  1491   1483       sqlite3VdbeJumpHere(v, addr+1);
  1492   1484       sqlite3VdbeChangeP4(v, addr+2, "ok", P4_STATIC);
  1493   1485     }else

Changes to src/resolve.c.

   236    236     ExprSetIrreducible(pExpr);
   237    237   
   238    238     /* Translate the schema name in zDb into a pointer to the corresponding
   239    239     ** schema.  If not found, pSchema will remain NULL and nothing will match
   240    240     ** resulting in an appropriate error message toward the end of this routine
   241    241     */
   242    242     if( zDb ){
   243         -    for(i=0; i<db->nDb; i++){
   244         -      assert( db->aDb[i].zName );
   245         -      if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){
   246         -        pSchema = db->aDb[i].pSchema;
   247         -        break;
          243  +    testcase( pNC->ncFlags & NC_PartIdx );
          244  +    testcase( pNC->ncFlags & NC_IsCheck );
          245  +    if( (pNC->ncFlags & (NC_PartIdx|NC_IsCheck))!=0 ){
          246  +      /* Silently ignore database qualifiers inside CHECK constraints and partial
          247  +      ** indices.  Do not raise errors because that might break legacy and
          248  +      ** because it does not hurt anything to just ignore the database name. */
          249  +      zDb = 0;
          250  +    }else{
          251  +      for(i=0; i<db->nDb; i++){
          252  +        assert( db->aDb[i].zName );
          253  +        if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){
          254  +          pSchema = db->aDb[i].pSchema;
          255  +          break;
          256  +        }
   248    257         }
   249    258       }
   250    259     }
   251    260   
   252    261     /* Start at the inner-most context and move outward until a match is found */
   253    262     while( pNC && cnt==0 ){
   254    263       ExprList *pEList;
................................................................................
   517    526         testcase( iCol==BMS-1 );
   518    527         pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
   519    528       }
   520    529       ExprSetProperty(p, EP_Resolved);
   521    530     }
   522    531     return p;
   523    532   }
          533  +
          534  +/*
          535  +** Report an error that an expression is not valid for a partial index WHERE
          536  +** clause.
          537  +*/
          538  +static void notValidPartIdxWhere(
          539  +  Parse *pParse,       /* Leave error message here */
          540  +  NameContext *pNC,    /* The name context */
          541  +  const char *zMsg     /* Type of error */
          542  +){
          543  +  if( (pNC->ncFlags & NC_PartIdx)!=0 ){
          544  +    sqlite3ErrorMsg(pParse, "%s prohibited in partial index WHERE clauses",
          545  +                    zMsg);
          546  +  }
          547  +}
          548  +
          549  +#ifndef SQLITE_OMIT_CHECK
          550  +/*
          551  +** Report an error that an expression is not valid for a CHECK constraint.
          552  +*/
          553  +static void notValidCheckConstraint(
          554  +  Parse *pParse,       /* Leave error message here */
          555  +  NameContext *pNC,    /* The name context */
          556  +  const char *zMsg     /* Type of error */
          557  +){
          558  +  if( (pNC->ncFlags & NC_IsCheck)!=0 ){
          559  +    sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg);
          560  +  }
          561  +}
          562  +#else
          563  +# define notValidCheckConstraint(P,N,M)
          564  +#endif
          565  +
   524    566   
   525    567   /*
   526    568   ** This routine is callback for sqlite3WalkExpr().
   527    569   **
   528    570   ** Resolve symbolic names into TK_COLUMN operators for the current
   529    571   ** node in the expression tree.  Return 0 to continue the search down
   530    572   ** the tree or 2 to abort the tree walk.
................................................................................
   617    659         int nId;                    /* Number of characters in function name */
   618    660         const char *zId;            /* The function name. */
   619    661         FuncDef *pDef;              /* Information about the function */
   620    662         u8 enc = ENC(pParse->db);   /* The database encoding */
   621    663   
   622    664         testcase( pExpr->op==TK_CONST_FUNC );
   623    665         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
          666  +      notValidPartIdxWhere(pParse, pNC, "functions");
   624    667         zId = pExpr->u.zToken;
   625    668         nId = sqlite3Strlen30(zId);
   626    669         pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
   627    670         if( pDef==0 ){
   628    671           pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
   629    672           if( pDef==0 ){
   630    673             no_such_func = 1;
................................................................................
   682    725       case TK_SELECT:
   683    726       case TK_EXISTS:  testcase( pExpr->op==TK_EXISTS );
   684    727   #endif
   685    728       case TK_IN: {
   686    729         testcase( pExpr->op==TK_IN );
   687    730         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
   688    731           int nRef = pNC->nRef;
   689         -#ifndef SQLITE_OMIT_CHECK
   690         -        if( (pNC->ncFlags & NC_IsCheck)!=0 ){
   691         -          sqlite3ErrorMsg(pParse,"subqueries prohibited in CHECK constraints");
   692         -        }
   693         -#endif
          732  +        notValidCheckConstraint(pParse, pNC, "subqueries");
          733  +        notValidPartIdxWhere(pParse, pNC, "subqueries");
   694    734           sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
   695    735           assert( pNC->nRef>=nRef );
   696    736           if( nRef!=pNC->nRef ){
   697    737             ExprSetProperty(pExpr, EP_VarSelect);
   698    738           }
   699    739         }
   700    740         break;
   701    741       }
   702         -#ifndef SQLITE_OMIT_CHECK
   703    742       case TK_VARIABLE: {
   704         -      if( (pNC->ncFlags & NC_IsCheck)!=0 ){
   705         -        sqlite3ErrorMsg(pParse,"parameters prohibited in CHECK constraints");
   706         -      }
          743  +      notValidCheckConstraint(pParse, pNC, "parameters");
          744  +      notValidPartIdxWhere(pParse, pNC, "parameters");
   707    745         break;
   708    746       }
   709         -#endif
   710    747     }
   711    748     return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
   712    749   }
   713    750   
   714    751   /*
   715    752   ** pEList is a list of expressions which are really the result set of the
   716    753   ** a SELECT statement.  pE is a term in an ORDER BY or GROUP BY clause.
................................................................................
   793    830     if( rc ) return 0;
   794    831   
   795    832     /* Try to match the ORDER BY expression against an expression
   796    833     ** in the result set.  Return an 1-based index of the matching
   797    834     ** result-set entry.
   798    835     */
   799    836     for(i=0; i<pEList->nExpr; i++){
   800         -    if( sqlite3ExprCompare(pEList->a[i].pExpr, pE)<2 ){
          837  +    if( sqlite3ExprCompare(pEList->a[i].pExpr, pE, -1)<2 ){
   801    838         return i+1;
   802    839       }
   803    840     }
   804    841   
   805    842     /* If no match, return 0. */
   806    843     return 0;
   807    844   }
................................................................................
  1021   1058   
  1022   1059       /* Otherwise, treat the ORDER BY term as an ordinary expression */
  1023   1060       pItem->iOrderByCol = 0;
  1024   1061       if( sqlite3ResolveExprNames(pNC, pE) ){
  1025   1062         return 1;
  1026   1063       }
  1027   1064       for(j=0; j<pSelect->pEList->nExpr; j++){
  1028         -      if( sqlite3ExprCompare(pE, pSelect->pEList->a[j].pExpr)==0 ){
         1065  +      if( sqlite3ExprCompare(pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
  1029   1066           pItem->iOrderByCol = j+1;
  1030   1067         }
  1031   1068       }
  1032   1069     }
  1033   1070     return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
  1034   1071   }
  1035   1072   
................................................................................
  1327   1364     memset(&w, 0, sizeof(w));
  1328   1365     w.xExprCallback = resolveExprStep;
  1329   1366     w.xSelectCallback = resolveSelectStep;
  1330   1367     w.pParse = pParse;
  1331   1368     w.u.pNC = pOuterNC;
  1332   1369     sqlite3WalkSelect(&w, p);
  1333   1370   }
         1371  +
         1372  +/*
         1373  +** Resolve names in expressions that can only reference a single table:
         1374  +**
         1375  +**    *   CHECK constraints
         1376  +**    *   WHERE clauses on partial indices
         1377  +**
         1378  +** The Expr.iTable value for Expr.op==TK_COLUMN nodes of the expression
         1379  +** is set to -1 and the Expr.iColumn value is set to the column number.
         1380  +**
         1381  +** Any errors cause an error message to be set in pParse.
         1382  +*/
         1383  +void sqlite3ResolveSelfReference(
         1384  +  Parse *pParse,      /* Parsing context */
         1385  +  Table *pTab,        /* The table being referenced */
         1386  +  int type,           /* NC_IsCheck or NC_PartIdx */
         1387  +  Expr *pExpr,        /* Expression to resolve.  May be NULL. */
         1388  +  ExprList *pList     /* Expression list to resolve.  May be NUL. */
         1389  +){
         1390  +  SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
         1391  +  NameContext sNC;                /* Name context for pParse->pNewTable */
         1392  +  int i;                          /* Loop counter */
         1393  +
         1394  +  assert( type==NC_IsCheck || type==NC_PartIdx );
         1395  +  memset(&sNC, 0, sizeof(sNC));
         1396  +  memset(&sSrc, 0, sizeof(sSrc));
         1397  +  sSrc.nSrc = 1;
         1398  +  sSrc.a[0].zName = pTab->zName;
         1399  +  sSrc.a[0].pTab = pTab;
         1400  +  sSrc.a[0].iCursor = -1;
         1401  +  sNC.pParse = pParse;
         1402  +  sNC.pSrcList = &sSrc;
         1403  +  sNC.ncFlags = type;
         1404  +  if( sqlite3ResolveExprNames(&sNC, pExpr) ) return;
         1405  +  if( pList ){
         1406  +    for(i=0; i<pList->nExpr; i++){
         1407  +      if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
         1408  +        return;
         1409  +      }
         1410  +    }
         1411  +  }
         1412  +}

Changes to src/select.c.

  4173   4173     /* If there is both a GROUP BY and an ORDER BY clause and they are
  4174   4174     ** identical, then disable the ORDER BY clause since the GROUP BY
  4175   4175     ** will cause elements to come out in the correct order.  This is
  4176   4176     ** an optimization - the correct answer should result regardless.
  4177   4177     ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  4178   4178     ** to disable this optimization for testing purposes.
  4179   4179     */
  4180         -  if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy)==0
         4180  +  if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
  4181   4181            && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
  4182   4182       pOrderBy = 0;
  4183   4183     }
  4184   4184   
  4185   4185     /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  4186   4186     ** if the select-list is the same as the ORDER BY list, then this query
  4187   4187     ** can be rewritten as a GROUP BY. In other words, this:
................................................................................
  4194   4194     **
  4195   4195     ** The second form is preferred as a single index (or temp-table) may be 
  4196   4196     ** used for both the ORDER BY and DISTINCT processing. As originally 
  4197   4197     ** written the query must use a temp-table for at least one of the ORDER 
  4198   4198     ** BY and DISTINCT, and an index or separate temp-table for the other.
  4199   4199     */
  4200   4200     if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
  4201         -   && sqlite3ExprListCompare(pOrderBy, p->pEList)==0
         4201  +   && sqlite3ExprListCompare(pOrderBy, p->pEList, -1)==0
  4202   4202     ){
  4203   4203       p->selFlags &= ~SF_Distinct;
  4204   4204       p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
  4205   4205       pGroupBy = p->pGroupBy;
  4206   4206       pOrderBy = 0;
  4207   4207       /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
  4208   4208       ** the sDistinct.isTnct is still set.  Hence, isTnct represents the

Changes to src/sqliteInt.h.

  1534   1534     tRowcnt *aiRowEst;       /* From ANALYZE: Est. rows selected by each column */
  1535   1535     Table *pTable;           /* The SQL table being indexed */
  1536   1536     char *zColAff;           /* String defining the affinity of each column */
  1537   1537     Index *pNext;            /* The next index associated with the same table */
  1538   1538     Schema *pSchema;         /* Schema containing this index */
  1539   1539     u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  1540   1540     char **azColl;           /* Array of collation sequence names for index */
         1541  +  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  1541   1542     int tnum;                /* DB Page containing root of this index */
  1542   1543     u16 nColumn;             /* Number of columns in table used by this index */
  1543   1544     u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  1544   1545     unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  1545   1546     unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  1546   1547     unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  1547   1548   #ifdef SQLITE_ENABLE_STAT3
................................................................................
  2014   2015   */
  2015   2016   #define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
  2016   2017   #define NC_HasAgg    0x02    /* One or more aggregate functions seen */
  2017   2018   #define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
  2018   2019   #define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */
  2019   2020   #define NC_AsMaybe   0x10    /* Resolve to AS terms of the result set only
  2020   2021                                ** if no other resolution is available */
         2022  +#define NC_PartIdx   0x20    /* True if resolving a partial index WHERE */
  2021   2023   
  2022   2024   /*
  2023   2025   ** An instance of the following structure contains all information
  2024   2026   ** needed to generate code for a single SELECT statement.
  2025   2027   **
  2026   2028   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
  2027   2029   ** If there is a LIMIT clause, the parser sets nLimit to the value of the
................................................................................
  2198   2200     int iRangeReg;       /* First register in temporary register block */
  2199   2201     int nErr;            /* Number of errors seen */
  2200   2202     int nTab;            /* Number of previously allocated VDBE cursors */
  2201   2203     int nMem;            /* Number of memory cells used so far */
  2202   2204     int nSet;            /* Number of sets used so far */
  2203   2205     int nOnce;           /* Number of OP_Once instructions so far */
  2204   2206     int ckBase;          /* Base register of data during check constraints */
         2207  +  int iPartIdxTab;     /* Table corresponding to a partial index */
  2205   2208     int iCacheLevel;     /* ColCache valid when aColCache[].iLevel<=iCacheLevel */
  2206   2209     int iCacheCnt;       /* Counter used to generate aColCache[].lru values */
  2207   2210     struct yColCache {
  2208   2211       int iTable;           /* Table cursor number */
  2209   2212       int iColumn;          /* Table column number */
  2210   2213       u8 tempReg;           /* iReg is a temp register that needs to be freed */
  2211   2214       int iLevel;           /* Nesting level */
................................................................................
  2779   2782   void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
  2780   2783   int sqlite3IndexedByLookup(Parse *, struct SrcList_item *);
  2781   2784   void sqlite3SrcListShiftJoinType(SrcList*);
  2782   2785   void sqlite3SrcListAssignCursors(Parse*, SrcList*);
  2783   2786   void sqlite3IdListDelete(sqlite3*, IdList*);
  2784   2787   void sqlite3SrcListDelete(sqlite3*, SrcList*);
  2785   2788   Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
  2786         -                        Token*, int, int);
         2789  +                          Expr*, int, int);
  2787   2790   void sqlite3DropIndex(Parse*, SrcList*, int);
  2788   2791   int sqlite3Select(Parse*, Select*, SelectDest*);
  2789   2792   Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
  2790   2793                            Expr*,ExprList*,u16,Expr*,Expr*);
  2791   2794   void sqlite3SelectDelete(sqlite3*, Select*);
  2792   2795   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  2793   2796   int sqlite3IsReadOnly(Parse*, Table*, int);
................................................................................
  2827   2830   Table *sqlite3LocateTableItem(Parse*,int isView,struct SrcList_item *);
  2828   2831   Index *sqlite3FindIndex(sqlite3*,const char*, const char*);
  2829   2832   void sqlite3UnlinkAndDeleteTable(sqlite3*,int,const char*);
  2830   2833   void sqlite3UnlinkAndDeleteIndex(sqlite3*,int,const char*);
  2831   2834   void sqlite3Vacuum(Parse*);
  2832   2835   int sqlite3RunVacuum(char**, sqlite3*);
  2833   2836   char *sqlite3NameFromToken(sqlite3*, Token*);
  2834         -int sqlite3ExprCompare(Expr*, Expr*);
  2835         -int sqlite3ExprListCompare(ExprList*, ExprList*);
         2837  +int sqlite3ExprCompare(Expr*, Expr*, int);
         2838  +int sqlite3ExprListCompare(ExprList*, ExprList*, int);
         2839  +int sqlite3ExprImpliesExpr(Expr*, Expr*, int);
  2836   2840   void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*);
  2837   2841   void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*);
  2838   2842   int sqlite3FunctionUsesThisSrc(Expr*, SrcList*);
  2839   2843   Vdbe *sqlite3GetVdbe(Parse*);
  2840   2844   void sqlite3PrngSaveState(void);
  2841   2845   void sqlite3PrngRestoreState(void);
  2842   2846   void sqlite3PrngResetState(void);
................................................................................
  2855   2859   int sqlite3ExprIsInteger(Expr*, int*);
  2856   2860   int sqlite3ExprCanBeNull(const Expr*);
  2857   2861   void sqlite3ExprCodeIsNullJump(Vdbe*, const Expr*, int, int);
  2858   2862   int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
  2859   2863   int sqlite3IsRowid(const char*);
  2860   2864   void sqlite3GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int);
  2861   2865   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int*);
  2862         -int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int);
         2866  +int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*);
  2863   2867   void sqlite3GenerateConstraintChecks(Parse*,Table*,int,int,
  2864   2868                                        int*,int,int,int,int,int*);
  2865   2869   void sqlite3CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);
  2866   2870   int sqlite3OpenTableAndIndices(Parse*, Table*, int, int);
  2867   2871   void sqlite3BeginWriteOperation(Parse*, int, int);
  2868   2872   void sqlite3MultiWrite(Parse*);
  2869   2873   void sqlite3MayAbort(Parse*);
................................................................................
  3058   3062   void sqlite3NestedParse(Parse*, const char*, ...);
  3059   3063   void sqlite3ExpirePreparedStatements(sqlite3*);
  3060   3064   int sqlite3CodeSubselect(Parse *, Expr *, int, int);
  3061   3065   void sqlite3SelectPrep(Parse*, Select*, NameContext*);
  3062   3066   int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
  3063   3067   int sqlite3ResolveExprNames(NameContext*, Expr*);
  3064   3068   void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
         3069  +void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
  3065   3070   int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
  3066   3071   void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
  3067   3072   void sqlite3AlterFinishAddColumn(Parse *, Token *);
  3068   3073   void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
  3069   3074   CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
  3070   3075   char sqlite3AffinityType(const char*);
  3071   3076   void sqlite3Analyze(Parse*, Token*, Token*);

Changes to src/update.c.

   242    242     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
   243    243     if( nIdx>0 ){
   244    244       aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx );
   245    245       if( aRegIdx==0 ) goto update_cleanup;
   246    246     }
   247    247     for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
   248    248       int reg;
   249         -    if( hasFK || chngRowid ){
          249  +    if( hasFK || chngRowid || pIdx->pPartIdxWhere ){
   250    250         reg = ++pParse->nMem;
   251    251       }else{
   252    252         reg = 0;
   253    253         for(i=0; i<pIdx->nColumn; i++){
   254    254           if( aXRef[pIdx->aiColumn[i]]>=0 ){
   255    255             reg = ++pParse->nMem;
   256    256             break;

Changes to src/vdbe.h.

   200    200   void sqlite3VdbeSetNumCols(Vdbe*,int);
   201    201   int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*));
   202    202   void sqlite3VdbeCountChanges(Vdbe*);
   203    203   sqlite3 *sqlite3VdbeDb(Vdbe*);
   204    204   void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, int);
   205    205   void sqlite3VdbeSwap(Vdbe*,Vdbe*);
   206    206   VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
   207         -sqlite3_value *sqlite3VdbeGetValue(Vdbe*, int, u8);
          207  +sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
   208    208   void sqlite3VdbeSetVarmask(Vdbe*, int);
   209    209   #ifndef SQLITE_OMIT_TRACE
   210    210     char *sqlite3VdbeExpandSql(Vdbe*, const char*);
   211    211   #endif
   212    212   
   213    213   void sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,UnpackedRecord*);
   214    214   int sqlite3VdbeRecordCompare(int,const void*,UnpackedRecord*);

Changes to src/vdbeaux.c.

   246    246   ** Resolve label "x" to be the address of the next instruction to
   247    247   ** be inserted.  The parameter "x" must have been obtained from
   248    248   ** a prior call to sqlite3VdbeMakeLabel().
   249    249   */
   250    250   void sqlite3VdbeResolveLabel(Vdbe *p, int x){
   251    251     int j = -1-x;
   252    252     assert( p->magic==VDBE_MAGIC_INIT );
   253         -  assert( j>=0 && j<p->nLabel );
   254         -  if( p->aLabel ){
          253  +  assert( j<p->nLabel );
          254  +  if( j>=0 && p->aLabel ){
   255    255       p->aLabel[j] = p->nOp;
   256    256     }
   257    257   }
   258    258   
   259    259   /*
   260    260   ** Mark the VDBE as one that can only be run one time.
   261    261   */
................................................................................
  3249   3249   ** Return a pointer to an sqlite3_value structure containing the value bound
  3250   3250   ** parameter iVar of VM v. Except, if the value is an SQL NULL, return 
  3251   3251   ** 0 instead. Unless it is NULL, apply affinity aff (one of the SQLITE_AFF_*
  3252   3252   ** constants) to the value before returning it.
  3253   3253   **
  3254   3254   ** The returned value must be freed by the caller using sqlite3ValueFree().
  3255   3255   */
  3256         -sqlite3_value *sqlite3VdbeGetValue(Vdbe *v, int iVar, u8 aff){
         3256  +sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe *v, int iVar, u8 aff){
  3257   3257     assert( iVar>0 );
  3258   3258     if( v ){
  3259   3259       Mem *pMem = &v->aVar[iVar-1];
  3260   3260       if( 0==(pMem->flags & MEM_Null) ){
  3261   3261         sqlite3_value *pRet = sqlite3ValueNew(v->db);
  3262   3262         if( pRet ){
  3263   3263           sqlite3VdbeMemCopy((Mem *)pRet, pMem);

Changes to src/where.c.

  1124   1124     op = pRight->op;
  1125   1125     if( op==TK_REGISTER ){
  1126   1126       op = pRight->op2;
  1127   1127     }
  1128   1128     if( op==TK_VARIABLE ){
  1129   1129       Vdbe *pReprepare = pParse->pReprepare;
  1130   1130       int iCol = pRight->iColumn;
  1131         -    pVal = sqlite3VdbeGetValue(pReprepare, iCol, SQLITE_AFF_NONE);
         1131  +    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_NONE);
  1132   1132       if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
  1133   1133         z = (char *)sqlite3_value_text(pVal);
  1134   1134       }
  1135   1135       sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);
  1136   1136       assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
  1137   1137     }else if( op==TK_STRING ){
  1138   1138       z = pRight->u.zToken;
................................................................................
  2231   2231     sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
  2232   2232                       (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
  2233   2233     VdbeComment((v, "for %s", pTable->zName));
  2234   2234   
  2235   2235     /* Fill the automatic index with content */
  2236   2236     addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
  2237   2237     regRecord = sqlite3GetTempReg(pParse);
  2238         -  sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);
         2238  +  sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1, 0);
  2239   2239     sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  2240   2240     sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  2241   2241     sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
  2242   2242     sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
  2243   2243     sqlite3VdbeJumpHere(v, addrTop);
  2244   2244     sqlite3ReleaseTempReg(pParse, regRecord);
  2245   2245     
................................................................................
  2588   2588     sqlite3_value **pp
  2589   2589   ){
  2590   2590     if( pExpr->op==TK_VARIABLE
  2591   2591      || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  2592   2592     ){
  2593   2593       int iVar = pExpr->iColumn;
  2594   2594       sqlite3VdbeSetVarmask(pParse->pVdbe, iVar);
  2595         -    *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff);
         2595  +    *pp = sqlite3VdbeGetBoundValue(pParse->pReprepare, iVar, aff);
  2596   2596       return SQLITE_OK;
  2597   2597     }
  2598   2598     return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp);
  2599   2599   }
  2600   2600   #endif
  2601   2601   
  2602   2602   /*
................................................................................
  4499   4499       testcase( x==BMS-1 );
  4500   4500       testcase( x==BMS-2 );
  4501   4501       if( x<BMS-1 ) m |= MASKBIT(x);
  4502   4502     }
  4503   4503     return m;
  4504   4504   }
  4505   4505   
         4506  +/* Check to see if a partial index with pPartIndexWhere can be used
         4507  +** in the current query.  Return true if it can be and false if not.
         4508  +*/
         4509  +static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
         4510  +  int i;
         4511  +  WhereTerm *pTerm;
         4512  +  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
         4513  +    if( sqlite3ExprImpliesExpr(pTerm->pExpr, pWhere, iTab) ) return 1;
         4514  +  }
         4515  +  return 0;
         4516  +}
  4506   4517   
  4507   4518   /*
  4508   4519   ** Add all WhereLoop objects for a single table of the join where the table
  4509   4520   ** is idenfied by pBuilder->pNew->iTab.  That table is guaranteed to be
  4510   4521   ** a b-tree table, not a virtual table.
  4511   4522   */
  4512   4523   static int whereLoopAddBtree(
................................................................................
  4522   4533     struct SrcList_item *pSrc;  /* The FROM clause btree term to add */
  4523   4534     WhereLoop *pNew;            /* Template WhereLoop object */
  4524   4535     int rc = SQLITE_OK;         /* Return code */
  4525   4536     int iSortIdx = 1;           /* Index number */
  4526   4537     int b;                      /* A boolean value */
  4527   4538     WhereCost rSize;            /* number of rows in the table */
  4528   4539     WhereCost rLogSize;         /* Logarithm of the number of rows in the table */
         4540  +  WhereClause *pWC;           /* The parsed WHERE clause */
  4529   4541     
  4530   4542     pNew = pBuilder->pNew;
  4531   4543     pWInfo = pBuilder->pWInfo;
  4532   4544     pTabList = pWInfo->pTabList;
  4533   4545     pSrc = pTabList->a + pNew->iTab;
         4546  +  pWC = pBuilder->pWC;
  4534   4547     assert( !IsVirtual(pSrc->pTab) );
  4535   4548   
  4536   4549     if( pSrc->pIndex ){
  4537   4550       /* An INDEXED BY clause specifies a particular index to use */
  4538   4551       pProbe = pSrc->pIndex;
  4539   4552     }else{
  4540   4553       /* There is no INDEXED BY clause.  Create a fake Index object in local
................................................................................
  4566   4579      && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0
  4567   4580      && pSrc->pIndex==0
  4568   4581      && !pSrc->viaCoroutine
  4569   4582      && !pSrc->notIndexed
  4570   4583      && !pSrc->isCorrelated
  4571   4584     ){
  4572   4585       /* Generate auto-index WhereLoops */
  4573         -    WhereClause *pWC = pBuilder->pWC;
  4574   4586       WhereTerm *pTerm;
  4575   4587       WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
  4576   4588       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  4577   4589         if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4578   4590         if( termCanDriveIndex(pTerm, pSrc, 0) ){
  4579   4591           pNew->u.btree.nEq = 1;
  4580   4592           pNew->u.btree.pIndex = 0;
................................................................................
  4596   4608         }
  4597   4609       }
  4598   4610     }
  4599   4611   
  4600   4612     /* Loop over all indices
  4601   4613     */
  4602   4614     for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){
         4615  +    if( pProbe->pPartIdxWhere!=0
         4616  +     && !whereUsablePartialIndex(pNew->iTab, pWC, pProbe->pPartIdxWhere) ){
         4617  +      continue;  /* Partial index inappropriate for this query */
         4618  +    }
  4603   4619       pNew->u.btree.nEq = 0;
  4604   4620       pNew->nLTerm = 0;
  4605   4621       pNew->iSortIdx = 0;
  4606   4622       pNew->rSetup = 0;
  4607   4623       pNew->prereq = mExtra;
  4608   4624       pNew->nOut = rSize;
  4609   4625       pNew->u.btree.pIndex = pProbe;
................................................................................
  5536   5552       pLoop->aLTerm[0] = pTerm;
  5537   5553       pLoop->nLTerm = 1;
  5538   5554       pLoop->u.btree.nEq = 1;
  5539   5555       /* TUNING: Cost of a rowid lookup is 10 */
  5540   5556       pLoop->rRun = 33;  /* 33==whereCost(10) */
  5541   5557     }else{
  5542   5558       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  5543         -      if( pIdx->onError==OE_None ) continue;
         5559  +      if( pIdx->onError==OE_None || pIdx->pPartIdxWhere!=0 ) continue;
  5544   5560         for(j=0; j<pIdx->nColumn; j++){
  5545   5561           pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
  5546   5562           if( pTerm==0 ) break;
  5547   5563           whereLoopResize(pWInfo->pParse->db, pLoop, j);
  5548   5564           pLoop->aLTerm[j] = pTerm;
  5549   5565         }
  5550   5566         if( j!=pIdx->nColumn ) continue;

Changes to test/check.test.

   447    447   } {}
   448    448   
   449    449   do_test 7.8 {
   450    450     db2 func myfunc myfunc
   451    451     catchsql { INSERT INTO t6 VALUES(12) } db2
   452    452   } {1 {constraint failed}}
   453    453   
          454  +# 2013-08-02:  Silently ignore database name qualifiers in CHECK constraints.
          455  +#
          456  +do_execsql_test 8.1 {
          457  +  CREATE TABLE t810(a, CHECK( main.t810.a>0 ));
          458  +  CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 ));
          459  +} {}
   454    460   
   455    461   finish_test

Added test/index6.test.

            1  +# 2013-07-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  +#
           12  +# Test cases for partial indices
           13  +#
           14  +
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +load_static_extension db wholenumber;
           20  +do_test index6-1.1 {
           21  +  # Able to parse and manage partial indices
           22  +  execsql {
           23  +    CREATE TABLE t1(a,b,c);
           24  +    CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
           25  +    CREATE INDEX t1b ON t1(b) WHERE b>10;
           26  +    CREATE VIRTUAL TABLE nums USING wholenumber;
           27  +    INSERT INTO t1(a,b,c)
           28  +       SELECT CASE WHEN value%3!=0 THEN value END, value, value
           29  +         FROM nums WHERE value<=20;
           30  +    SELECT count(a), count(b) FROM t1;
           31  +    PRAGMA integrity_check;
           32  +  }
           33  +} {14 20 ok}
           34  +
           35  +# Error conditions during parsing...
           36  +#
           37  +do_test index6-1.2 {
           38  +  catchsql {
           39  +    CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL;
           40  +  }
           41  +} {1 {no such column: x}}
           42  +do_test index6-1.3 {
           43  +  catchsql {
           44  +    CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
           45  +  }
           46  +} {1 {subqueries prohibited in partial index WHERE clauses}}
           47  +do_test index6-1.4 {
           48  +  catchsql {
           49  +    CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
           50  +  }
           51  +} {1 {parameters prohibited in partial index WHERE clauses}}
           52  +do_test index6-1.5 {
           53  +  catchsql {
           54  +    CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
           55  +  }
           56  +} {1 {functions prohibited in partial index WHERE clauses}}
           57  +do_test index6-1.6 {
           58  +  catchsql {
           59  +    CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
           60  +  }
           61  +} {1 {functions prohibited in partial index WHERE clauses}}
           62  +
           63  +do_test index6-1.10 {
           64  +  execsql {
           65  +    ANALYZE;
           66  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           67  +    PRAGMA integrity_check;
           68  +  }
           69  +} {{} 20 t1a {14 1} t1b {10 1} ok}
           70  +
           71  +# STAT1 shows the partial indices have a reduced number of
           72  +# rows.
           73  +#
           74  +do_test index6-1.11 {
           75  +  execsql {
           76  +    UPDATE t1 SET a=b;
           77  +    ANALYZE;
           78  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           79  +    PRAGMA integrity_check;
           80  +  }
           81  +} {{} 20 t1a {20 1} t1b {10 1} ok}
           82  +
           83  +do_test index6-1.11 {
           84  +  execsql {
           85  +    UPDATE t1 SET a=NULL WHERE b%3!=0;
           86  +    UPDATE t1 SET b=b+100;
           87  +    ANALYZE;
           88  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           89  +    PRAGMA integrity_check;
           90  +  }
           91  +} {{} 20 t1a {6 1} t1b {20 1} ok}
           92  +
           93  +do_test index6-1.12 {
           94  +  execsql {
           95  +    UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;
           96  +    UPDATE t1 SET b=b-100;
           97  +    ANALYZE;
           98  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           99  +    PRAGMA integrity_check;
          100  +  }
          101  +} {{} 20 t1a {13 1} t1b {10 1} ok}
          102  +
          103  +do_test index6-1.13 {
          104  +  execsql {
          105  +    DELETE FROM t1 WHERE b BETWEEN 8 AND 12;
          106  +    ANALYZE;
          107  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          108  +    PRAGMA integrity_check;
          109  +  }
          110  +} {{} 15 t1a {10 1} t1b {8 1} ok}
          111  +
          112  +do_test index6-1.14 {
          113  +  execsql {
          114  +    REINDEX;
          115  +    ANALYZE;
          116  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          117  +    PRAGMA integrity_check;
          118  +  }
          119  +} {{} 15 t1a {10 1} t1b {8 1} ok}
          120  +
          121  +do_test index6-1.15 {
          122  +  execsql {
          123  +    CREATE INDEX t1c ON t1(c);
          124  +    ANALYZE;
          125  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          126  +    PRAGMA integrity_check;
          127  +  }
          128  +} {t1a {10 1} t1b {8 1} t1c {15 1} ok}
          129  +
          130  +# Queries use partial indices as appropriate times.
          131  +#
          132  +do_test index6-2.1 {
          133  +  execsql {
          134  +    CREATE TABLE t2(a,b);
          135  +    INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
          136  +    UPDATE t2 SET a=NULL WHERE b%5==0;
          137  +    CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
          138  +    SELECT count(*) FROM t2 WHERE a IS NOT NULL;
          139  +  }
          140  +} {800}
          141  +do_test index6-2.2 {
          142  +  execsql {
          143  +    EXPLAIN QUERY PLAN
          144  +    SELECT * FROM t2 WHERE a=5;
          145  +  }
          146  +} {/.* TABLE t2 USING INDEX t2a1 .*/}
          147  +do_test index6-2.3 {
          148  +  execsql {
          149  +    EXPLAIN QUERY PLAN
          150  +    SELECT * FROM t2 WHERE a IS NOT NULL;
          151  +  }
          152  +} {/.* TABLE t2 USING INDEX t2a1 .*/}
          153  +do_test index6-2.4 {
          154  +  execsql {
          155  +    EXPLAIN QUERY PLAN
          156  +    SELECT * FROM t2 WHERE a IS NULL;
          157  +  }
          158  +} {~/.*INDEX t2a1.*/}
          159  +
          160  +do_execsql_test index6-2.101 {
          161  +  DROP INDEX t2a1;
          162  +  UPDATE t2 SET a=b, b=b+10000;
          163  +  SELECT b FROM t2 WHERE a=15;
          164  +} {10015}
          165  +do_execsql_test index6-2.102 {
          166  +  CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;
          167  +  SELECT b FROM t2 WHERE a=15;
          168  +  PRAGMA integrity_check;
          169  +} {10015 ok}
          170  +do_execsql_test index6-2.102eqp {
          171  +  EXPLAIN QUERY PLAN
          172  +  SELECT b FROM t2 WHERE a=15;
          173  +} {~/.*INDEX t2a2.*/}
          174  +do_execsql_test index6-2.103 {
          175  +  SELECT b FROM t2 WHERE a=15 AND a<100;
          176  +} {10015}
          177  +do_execsql_test index6-2.103eqp {
          178  +  EXPLAIN QUERY PLAN
          179  +  SELECT b FROM t2 WHERE a=15 AND a<100;
          180  +} {/.*INDEX t2a2.*/}
          181  +do_execsql_test index6-2.104 {
          182  +  SELECT b FROM t2 WHERE a=515 AND a>200;
          183  +} {10515}
          184  +do_execsql_test index6-2.104eqp {
          185  +  EXPLAIN QUERY PLAN
          186  +  SELECT b FROM t2 WHERE a=515 AND a>200;
          187  +} {/.*INDEX t2a2.*/}
          188  +
          189  +# Partial UNIQUE indices
          190  +#
          191  +do_execsql_test index6-3.1 {
          192  +  CREATE TABLE t3(a,b);
          193  +  INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;
          194  +  UPDATE t3 SET a=999 WHERE b%5!=0;
          195  +  CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
          196  +} {}
          197  +do_test index6-3.2 {
          198  +  # unable to insert a duplicate row a-value that is not 999.
          199  +  catchsql {
          200  +    INSERT INTO t3(a,b) VALUES(150, 'test1');
          201  +  }
          202  +} {1 {column a is not unique}}
          203  +do_test index6-3.3 {
          204  +  # can insert multiple rows with a==999 because such rows are not
          205  +  # part of the unique index.
          206  +  catchsql {
          207  +    INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
          208  +  }
          209  +} {0 {}}
          210  +do_execsql_test index6-3.4 {
          211  +  SELECT count(*) FROM t3 WHERE a=999;
          212  +} {162}
          213  +integrity_check index6-3.5
          214  +
          215  +do_execsql_test index6-4.0 {
          216  +  VACUUM;
          217  +  PRAGMA integrity_check;
          218  +} {ok}
          219  +
          220  +# Silently ignore database name qualifiers in partial indices.
          221  +#
          222  +do_execsql_test index6-5.0 {
          223  +  CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;
          224  +                               /* ^^^^^-- ignored */
          225  +  ANALYZE;
          226  +  SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;
          227  +  SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';
          228  +} {6 6}
          229  +
          230  +finish_test