/ Check-in [6b73ae7c]
Login

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

Overview
Comment:Test cases and bug fixes for the partial index logic.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | partial-indices
Files: files | file ages | folders
SHA1: 6b73ae7c123801787c8994113cbeb87ee96ba653
User & Date: drh 2013-08-01 03:36:59
Context
2013-08-01
04:39
Fix the ANALYZE command to work with partial indices. check-in: 60353124 user: drh tags: partial-indices
03:36
Test cases and bug fixes for the partial index logic. check-in: 6b73ae7c user: drh tags: partial-indices
01:14
Add the logic to keep partial indices up to date through DML statements and when new partial indices are created. This new logic is untested except to verify that it does not interfere with full indices. check-in: fb9044d1 user: drh tags: partial-indices
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1222   1222     }else if( autoInc ){
  1223   1223   #ifndef SQLITE_OMIT_AUTOINCREMENT
  1224   1224       sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
  1225   1225          "INTEGER PRIMARY KEY");
  1226   1226   #endif
  1227   1227     }else{
  1228   1228       Index *p;
  1229         -    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,
         1229  +    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0,
  1230   1230                              0, sortOrder, 0);
  1231   1231       if( p ){
  1232   1232         p->autoIndex = 2;
  1233   1233       }
  1234   1234       pList = 0;
  1235   1235     }
  1236   1236   
................................................................................
  2463   2463     Parse *pParse,     /* All information about this parse */
  2464   2464     Token *pName1,     /* First part of index name. May be NULL */
  2465   2465     Token *pName2,     /* Second part of index name. May be NULL */
  2466   2466     SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */
  2467   2467     ExprList *pList,   /* A list of columns to be indexed */
  2468   2468     int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  2469   2469     Token *pStart,     /* The CREATE token that begins this statement */
  2470         -  Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
  2471   2470     Expr *pPIWhere,    /* WHERE clause for partial indices */
  2472   2471     int sortOrder,     /* Sort order of primary key when pList==NULL */
  2473   2472     int ifNotExist     /* Omit error if index already exists */
  2474   2473   ){
  2475   2474     Index *pRet = 0;     /* Pointer to return */
  2476   2475     Table *pTab = 0;     /* Table to be indexed */
  2477   2476     Index *pIndex = 0;   /* The index to be created */
................................................................................
  2486   2485     int iDb;             /* Index of the database that is being written */
  2487   2486     Token *pName = 0;    /* Unqualified name of the index to create */
  2488   2487     struct ExprList_item *pListItem; /* For looping over pList */
  2489   2488     int nCol;
  2490   2489     int nExtra = 0;
  2491   2490     char *zExtra;
  2492   2491   
  2493         -  assert( pStart==0 || pEnd!=0 ); /* pEnd must be non-NULL if pStart is */
  2494   2492     assert( pParse->nErr==0 );      /* Never called with prior errors */
  2495   2493     if( db->mallocFailed || IN_DECLARE_VTAB ){
  2496   2494       goto exit_create_index;
  2497   2495     }
  2498   2496     if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
  2499   2497       goto exit_create_index;
  2500   2498     }
................................................................................
  2859   2857       sqlite3BeginWriteOperation(pParse, 1, iDb);
  2860   2858       sqlite3VdbeAddOp2(v, OP_CreateIndex, iDb, iMem);
  2861   2859   
  2862   2860       /* Gather the complete text of the CREATE INDEX statement into
  2863   2861       ** the zStmt variable
  2864   2862       */
  2865   2863       if( pStart ){
  2866         -      assert( pEnd!=0 );
         2864  +      int n = (pParse->sLastToken.z - pName->z) + 1;
         2865  +      if( pName->z[n-1]==';' ) n--;
  2867   2866         /* A named index with an explicit CREATE INDEX statement */
  2868   2867         zStmt = sqlite3MPrintf(db, "CREATE%s INDEX %.*s",
  2869         -        onError==OE_None ? "" : " UNIQUE",
  2870         -        (int)(pEnd->z - pName->z) + 1,
  2871         -        pName->z);
         2868  +        onError==OE_None ? "" : " UNIQUE", n, pName->z);
  2872   2869       }else{
  2873   2870         /* An automatic index created by a PRIMARY KEY or UNIQUE constraint */
  2874   2871         /* zStmt = sqlite3MPrintf(""); */
  2875   2872         zStmt = 0;
  2876   2873       }
  2877   2874   
  2878   2875       /* Add an entry in sqlite_master for this index

Changes to src/insert.c.

  1407   1407       sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), P4_TRANSIENT);
  1408   1408       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);
  1409   1409   
  1410   1410       /* Find out what action to take in case there is an indexing conflict */
  1411   1411       onError = pIdx->onError;
  1412   1412       if( onError==OE_None ){ 
  1413   1413         sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
         1414  +      sqlite3VdbeResolveLabel(v, addrSkipRow);
  1414   1415         continue;  /* pIdx is not a UNIQUE index */
  1415   1416       }
  1416   1417       if( overrideError!=OE_Default ){
  1417   1418         onError = overrideError;
  1418   1419       }else if( onError==OE_Default ){
  1419   1420         onError = OE_Abort;
  1420   1421       }

Changes to src/parse.y.

   296    296   // In addition to the type name, we also care about the primary key and
   297    297   // UNIQUE constraints.
   298    298   //
   299    299   ccons ::= NULL onconf.
   300    300   ccons ::= NOT NULL onconf(R).    {sqlite3AddNotNull(pParse, R);}
   301    301   ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
   302    302                                    {sqlite3AddPrimaryKey(pParse,0,R,I,Z);}
   303         -ccons ::= UNIQUE onconf(R).      {sqlite3CreateIndex(pParse,0,0,0,0,R,0,
   304         -                                                     0,0,0,0);}
          303  +ccons ::= UNIQUE onconf(R).      {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0,0,0);}
   305    304   ccons ::= CHECK LP expr(X) RP.   {sqlite3AddCheckConstraint(pParse,X.pExpr);}
   306    305   ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
   307    306                                    {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
   308    307   ccons ::= defer_subclause(D).    {sqlite3DeferForeignKey(pParse,D);}
   309    308   ccons ::= COLLATE ids(C).        {sqlite3AddCollateType(pParse, &C);}
   310    309   
   311    310   // The optional AUTOINCREMENT keyword
................................................................................
   346    345   conslist ::= tcons.
   347    346   tconscomma ::= COMMA.            {pParse->constraintName.n = 0;}
   348    347   tconscomma ::= .
   349    348   tcons ::= CONSTRAINT nm(X).      {pParse->constraintName = X;}
   350    349   tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
   351    350                                    {sqlite3AddPrimaryKey(pParse,X,R,I,0);}
   352    351   tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
   353         -                               {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0,0);}
          352  +                                 {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0);}
   354    353   tcons ::= CHECK LP expr(E) RP onconf.
   355    354                                    {sqlite3AddCheckConstraint(pParse,E.pExpr);}
   356    355   tcons ::= FOREIGN KEY LP idxlist(FA) RP
   357    356             REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
   358    357       sqlite3CreateForeignKey(pParse, FA, &T, TA, R);
   359    358       sqlite3DeferForeignKey(pParse, D);
   360    359   }
................................................................................
  1122   1121   nexprlist(A) ::= expr(Y).
  1123   1122       {A = sqlite3ExprListAppend(pParse,0,Y.pExpr);}
  1124   1123   
  1125   1124   
  1126   1125   ///////////////////////////// The CREATE INDEX command ///////////////////////
  1127   1126   //
  1128   1127   cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
  1129         -        ON nm(Y) LP idxlist(Z) RP(E) where_opt(W). {
         1128  +        ON nm(Y) LP idxlist(Z) RP where_opt(W). {
  1130   1129     sqlite3CreateIndex(pParse, &X, &D, 
  1131   1130                        sqlite3SrcListAppend(pParse->db,0,&Y,0), Z, U,
  1132         -                      &S, &E, W, SQLITE_SO_ASC, NE);
         1131  +                      &S, W, SQLITE_SO_ASC, NE);
  1133   1132   }
  1134   1133   
  1135   1134   %type uniqueflag {int}
  1136   1135   uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
  1137   1136   uniqueflag(A) ::= .        {A = OE_None;}
  1138   1137   
  1139   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+4 );
  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),
................................................................................
  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);
  1430   1428           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 */
         1429  +        for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
         1430  +          sqlite3VdbeAddOp2(v, OP_Integer, 0, 7+j); /* index entries counter */
         1431  +        }
         1432  +        pParse->nMem = MAX(pParse->nMem, 7+j);
         1433  +        loopTop = sqlite3VdbeAddOp2(v, OP_Rewind, 1, 0) + 1;
  1434   1434           for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1435   1435             int jmp2, jmp3;
  1436   1436             int r1;
  1437   1437             static const VdbeOpList idxErr[] = {
  1438   1438               { OP_AddImm,      1, -1,  0},
  1439   1439               { OP_String8,     0,  3,  0},    /* 1 */
  1440   1440               { OP_Rowid,       1,  4,  0},
................................................................................
  1444   1444               { OP_Concat,      5,  3,  3},
  1445   1445               { OP_Concat,      6,  3,  3},
  1446   1446               { OP_ResultRow,   3,  1,  0},
  1447   1447               { OP_IfPos,       1,  0,  0},    /* 9 */
  1448   1448               { OP_Halt,        0,  0,  0},
  1449   1449             };
  1450   1450             r1 = sqlite3GenerateIndexKey(pParse, pIdx, 1, 3, 0, &jmp3);
         1451  +          sqlite3VdbeAddOp2(v, OP_AddImm, 7+j, 1);  /* increment entry count */
  1451   1452             jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, j+2, 0, r1, pIdx->nColumn+1);
  1452   1453             addr = sqlite3VdbeAddOpList(v, ArraySize(idxErr), idxErr);
  1453   1454             sqlite3VdbeChangeP4(v, addr+1, "rowid ", P4_STATIC);
  1454   1455             sqlite3VdbeChangeP4(v, addr+3, " missing from index ", P4_STATIC);
  1455   1456             sqlite3VdbeChangeP4(v, addr+4, pIdx->zName, P4_TRANSIENT);
  1456   1457             sqlite3VdbeJumpHere(v, addr+9);
  1457   1458             sqlite3VdbeJumpHere(v, jmp2);
  1458   1459             sqlite3VdbeResolveLabel(v, jmp3);
  1459   1460           }
  1460         -        sqlite3VdbeAddOp2(v, OP_Next, 1, loopTop+1);
  1461         -        sqlite3VdbeJumpHere(v, loopTop);
         1461  +        sqlite3VdbeAddOp2(v, OP_Next, 1, loopTop);
         1462  +        sqlite3VdbeJumpHere(v, loopTop-1);
         1463  +#ifndef SQLITE_OMIT_BTREECOUNT
         1464  +        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, 
         1465  +                     "wrong # of entries in index ", P4_STATIC);
  1462   1466           for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1463         -          static const VdbeOpList cntIdx[] = {
  1464         -             { OP_Integer,      0,  3,  0},
  1465         -             { OP_Rewind,       0,  0,  0},  /* 1 */
  1466         -             { OP_AddImm,       3,  1,  0},
  1467         -             { OP_Next,         0,  0,  0},  /* 3 */
  1468         -             { OP_Eq,           2,  0,  3},  /* 4 */
  1469         -             { OP_AddImm,       1, -1,  0},
  1470         -             { OP_String8,      0,  2,  0},  /* 6 */
  1471         -             { OP_String8,      0,  3,  0},  /* 7 */
  1472         -             { OP_Concat,       3,  2,  2},
  1473         -             { OP_ResultRow,    2,  1,  0},
  1474         -          };
  1475         -          addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1);
         1467  +          addr = sqlite3VdbeCurrentAddr(v);
         1468  +          sqlite3VdbeAddOp2(v, OP_IfPos, 1, addr+2);
  1476   1469             sqlite3VdbeAddOp2(v, OP_Halt, 0, 0);
  1477         -          sqlite3VdbeJumpHere(v, addr);
  1478         -          addr = sqlite3VdbeAddOpList(v, ArraySize(cntIdx), cntIdx);
  1479         -          sqlite3VdbeChangeP1(v, addr+1, j+2);
  1480         -          sqlite3VdbeChangeP2(v, addr+1, addr+4);
  1481         -          sqlite3VdbeChangeP1(v, addr+3, j+2);
  1482         -          sqlite3VdbeChangeP2(v, addr+3, addr+2);
  1483         -          sqlite3VdbeJumpHere(v, addr+4);
  1484         -          sqlite3VdbeChangeP4(v, addr+6, 
  1485         -                     "wrong # of entries in index ", P4_STATIC);
  1486         -          sqlite3VdbeChangeP4(v, addr+7, pIdx->zName, P4_TRANSIENT);
         1470  +          sqlite3VdbeAddOp2(v, OP_Count, j+2, 3);
         1471  +          sqlite3VdbeAddOp3(v, OP_Eq, 7+j, addr+8, 3);
         1472  +          sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1);
         1473  +          sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pIdx->zName, P4_TRANSIENT);
         1474  +          sqlite3VdbeAddOp3(v, OP_Concat, 3, 2, 7);
         1475  +          sqlite3VdbeAddOp2(v, OP_ResultRow, 7, 1);
  1487   1476           }
         1477  +#endif /* SQLITE_OMIT_BTREECOUNT */
  1488   1478         } 
  1489   1479       }
  1490   1480       addr = sqlite3VdbeAddOpList(v, ArraySize(endCode), endCode);
  1491   1481       sqlite3VdbeChangeP2(v, addr, -mxErr);
  1492   1482       sqlite3VdbeJumpHere(v, addr+1);
  1493   1483       sqlite3VdbeChangeP4(v, addr+2, "ok", P4_STATIC);
  1494   1484     }else

Changes to src/sqliteInt.h.

  2782   2782   void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
  2783   2783   int sqlite3IndexedByLookup(Parse *, struct SrcList_item *);
  2784   2784   void sqlite3SrcListShiftJoinType(SrcList*);
  2785   2785   void sqlite3SrcListAssignCursors(Parse*, SrcList*);
  2786   2786   void sqlite3IdListDelete(sqlite3*, IdList*);
  2787   2787   void sqlite3SrcListDelete(sqlite3*, SrcList*);
  2788   2788   Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
  2789         -                        Token*, Expr*, int, int);
         2789  +                          Expr*, int, int);
  2790   2790   void sqlite3DropIndex(Parse*, SrcList*, int);
  2791   2791   int sqlite3Select(Parse*, Select*, SelectDest*);
  2792   2792   Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
  2793   2793                            Expr*,ExprList*,u16,Expr*,Expr*);
  2794   2794   void sqlite3SelectDelete(sqlite3*, Select*);
  2795   2795   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  2796   2796   int sqlite3IsReadOnly(Parse*, Table*, int);

Changes to test/index6.test.

    52     52   } {1 {functions prohibited in partial index WHERE clauses}}
    53     53   do_test index6-1.6 {
    54     54     catchsql {
    55     55       CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
    56     56     }
    57     57   } {1 {functions prohibited in partial index WHERE clauses}}
    58     58   
           59  +do_test index6-1.10 {
           60  +  execsql {
           61  +    ANALYZE;
           62  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           63  +    PRAGMA integrity_check;
           64  +  }
           65  +} {t1a {14 1} t1b {10 1} ok}
           66  +
           67  +do_test index6-1.11 {
           68  +  execsql {
           69  +    UPDATE t1 SET a=b;
           70  +    ANALYZE;
           71  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           72  +    PRAGMA integrity_check;
           73  +  }
           74  +} {t1a {20 1} t1b {10 1} ok}
           75  +
           76  +do_test index6-1.11 {
           77  +  execsql {
           78  +    UPDATE t1 SET a=NULL WHERE b%3!=0;
           79  +    UPDATE t1 SET b=b+100;
           80  +    ANALYZE;
           81  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           82  +    PRAGMA integrity_check;
           83  +  }
           84  +} {t1a {6 1} t1b {20 1} ok}
           85  +
           86  +do_test index6-1.12 {
           87  +  execsql {
           88  +    UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;
           89  +    UPDATE t1 SET b=b-100;
           90  +    ANALYZE;
           91  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           92  +    PRAGMA integrity_check;
           93  +  }
           94  +} {t1a {13 1} t1b {10 1} ok}
           95  +
           96  +do_test index6-1.13 {
           97  +  execsql {
           98  +    DELETE FROM t1 WHERE b BETWEEN 8 AND 12;
           99  +    ANALYZE;
          100  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          101  +    PRAGMA integrity_check;
          102  +  }
          103  +} {t1a {10 1} t1b {8 1} ok}
          104  +
          105  +do_test index6-1.14 {
          106  +  execsql {
          107  +    REINDEX;
          108  +    ANALYZE;
          109  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          110  +    PRAGMA integrity_check;
          111  +  }
          112  +} {t1a {10 1} t1b {8 1} ok}
    59    113   
    60    114   finish_test