/ Check-in [31932a9e]
Login

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

Overview
Comment:Fix handling of partial indexes in checkindex.c (sqlite3_checker).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 31932a9eb8dbb33d5535715ae8bbfdc55ce66b1a1a0abd57cefe720eeb31e231
User & Date: dan 2017-11-07 19:02:00
Context
2017-11-08
02:50
Improved comments used for documentation of sqlite3_vfs. No changes to code. check-in: db8f22d0 user: drh tags: trunk
2017-11-07
19:02
Fix handling of partial indexes in checkindex.c (sqlite3_checker). check-in: 31932a9e user: dan tags: trunk
18:20
Fix a problem causing an INDEXED BY specifying an unusable partial index to be mishandled. check-in: 292a0408 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/repair/checkindex.c.

    55     55     char *zExpr;                    /* Text for indexed expression */
    56     56     int bDesc;                      /* True for DESC columns, otherwise false */
    57     57     int bKey;                       /* Part of index, not PK */
    58     58   };
    59     59   
    60     60   typedef struct CidxIndex CidxIndex;
    61     61   struct CidxIndex {
           62  +  char *zWhere;                   /* WHERE clause, if any */
    62     63     int nCol;                       /* Elements in aCol[] array */
    63     64     CidxColumn aCol[1];             /* Array of indexed columns */
    64     65   };
    65     66   
    66     67   static void *cidxMalloc(int *pRc, int n){
    67     68     void *pRet = 0;
    68     69     assert( n!=0 );
................................................................................
   289    290   
   290    291   static void cidxFreeIndex(CidxIndex *pIdx){
   291    292     if( pIdx ){
   292    293       int i;
   293    294       for(i=0; i<pIdx->nCol; i++){
   294    295         sqlite3_free(pIdx->aCol[i].zExpr);
   295    296       }
          297  +    sqlite3_free(pIdx->zWhere);
   296    298       sqlite3_free(pIdx);
   297    299     }
   298    300   }
   299    301   
   300    302   static int cidx_isspace(char c){
   301    303     return c==' ' || c=='\t' || c=='\r' || c=='\n';
   302    304   }
................................................................................
   308    310   }
   309    311   
   310    312   #define CIDX_PARSE_EOF   0
   311    313   #define CIDX_PARSE_COMMA 1      /*  "," */
   312    314   #define CIDX_PARSE_OPEN  2      /*  "(" */
   313    315   #define CIDX_PARSE_CLOSE 3      /*  ")" */
   314    316   
          317  +/*
          318  +** Argument zIn points into the start, middle or end of a CREATE INDEX
          319  +** statement. If argument pbDoNotTrim is non-NULL, then this function
          320  +** scans the input until it finds EOF, a comma (",") or an open or
          321  +** close parenthesis character. It then sets (*pzOut) to point to said
          322  +** character and returns a CIDX_PARSE_XXX constant as appropriate. The
          323  +** parser is smart enough that special characters inside SQL strings
          324  +** or comments are not returned for.
          325  +**
          326  +** Or, if argument pbDoNotTrim is NULL, then this function sets *pzOut
          327  +** to point to the first character of the string that is not whitespace
          328  +** or part of an SQL comment and returns CIDX_PARSE_EOF.
          329  +**
          330  +** Additionally, if pbDoNotTrim is not NULL and the element immediately
          331  +** before (*pzOut) is an SQL comment of the form "-- comment", then
          332  +** (*pbDoNotTrim) is set before returning. In all other cases it is
          333  +** cleared.
          334  +*/
   315    335   static int cidxFindNext(
   316    336     const char *zIn, 
   317    337     const char **pzOut,
   318    338     int *pbDoNotTrim                /* OUT: True if prev is -- comment */
   319    339   ){
   320    340     const char *z = zIn;
   321    341   
   322    342     while( 1 ){
          343  +    while( cidx_isspace(*z) ) z++;
   323    344       if( z[0]=='-' && z[1]=='-' ){
   324    345         z += 2;
   325    346         while( z[0]!='\n' ){
   326    347           if( z[0]=='\0' ) return CIDX_PARSE_EOF;
   327    348           z++;
   328    349         }
   329    350         while( cidx_isspace(*z) ) z++;
   330         -      *pbDoNotTrim = 1;
          351  +      if( pbDoNotTrim ) *pbDoNotTrim = 1;
          352  +    }else
          353  +    if( z[0]=='/' && z[1]=='*' ){
          354  +      z += 2;
          355  +      while( z[0]!='*' || z[1]!='/' ){
          356  +        if( z[1]=='\0' ) return CIDX_PARSE_EOF;
          357  +        z++;
          358  +      }
          359  +      z += 2;
   331    360       }else{
   332    361         *pzOut = z;
          362  +      if( pbDoNotTrim==0 ) return CIDX_PARSE_EOF;
   333    363         switch( *z ){
   334    364           case '\0':
   335    365             return CIDX_PARSE_EOF;
   336    366           case '(':
   337    367             return CIDX_PARSE_OPEN;
   338    368           case ')':
   339    369             return CIDX_PARSE_CLOSE;
................................................................................
   355    385             break;
   356    386           }
   357    387     
   358    388           case '[':
   359    389             while( *z++!=']' );
   360    390             break;
   361    391     
   362         -        case '/':
   363         -          if( z[1]=='*' ){
   364         -            z += 2;
   365         -            while( z[0]!='*' || z[1]!='/' ){
   366         -              if( z[1]=='\0' ) return CIDX_PARSE_EOF;
   367         -              z++;
   368         -            }
   369         -            z += 2;
   370         -            break;
   371         -          }
   372         -  
   373    392           default:
   374    393             z++;
   375    394             break;
   376    395         }
   377    396         *pbDoNotTrim = 0;
   378    397       }
   379    398     }
................................................................................
   419    438         pCol++;
   420    439         z = z1 = z2+1;
   421    440       }
   422    441       if( e==CIDX_PARSE_OPEN ) nParen++;
   423    442       if( e==CIDX_PARSE_CLOSE ) nParen--;
   424    443       z++;
   425    444     }
          445  +
          446  +  /* Search for a WHERE clause */
          447  +  cidxFindNext(z, &z, 0);
          448  +  if( 0==sqlite3_strnicmp(z, "where", 5) ){
          449  +    pIdx->zWhere = cidxMprintf(&rc, "%s\n", &z[5]);
          450  +  }else if( z[0]!='\0' ){
          451  +    goto parse_error;
          452  +  }
   426    453   
   427    454     return rc;
   428    455   
   429    456    parse_error:
   430    457     cidxCursorError(pCsr, "Parse error in: %s", zSql);
   431    458     return SQLITE_ERROR;
   432    459   }
................................................................................
   473    500           p->bKey = sqlite3_column_int(pInfo, 5);
   474    501           if( zSql==0 || p->bKey==0 ){
   475    502             p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl);
   476    503           }else{
   477    504             p->zExpr = 0;
   478    505           }
   479    506           pIdx->nCol = iCol;
          507  +        pIdx->zWhere = 0;
   480    508         }
   481    509         cidxFinalize(&rc, pInfo);
   482    510       }
   483    511   
   484    512       if( rc==SQLITE_OK && zSql ){
   485    513         rc = cidxParseSQL(pCsr, pIdx, zSql);
   486    514       }
................................................................................
   696    724     zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR);
   697    725     zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL);
   698    726   
   699    727     if( rc==SQLITE_OK && zAfterKey ){
   700    728       rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter);
   701    729     }
   702    730   
   703         -  if( rc || zAfterKey==0 ){
   704         -    *pzSqlOut = cidxMprintf(&rc,
   705         -        "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s "
   706         -        "FROM (SELECT %s FROM %Q ORDER BY %s) AS i",
   707         -        zSubExpr, zTab, zSubWhere, zCurrentKey, 
   708         -        zSrcList, zTab, zOrderBy
   709         -    );
   710         -  }else{
   711         -    const char *zSep = "";
   712         -    char *zSql;
   713         -    int i;
   714         -
   715         -    zSql = cidxMprintf(&rc, 
   716         -        "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
   717         -        zSubExpr, zTab, zSubWhere, zCurrentKey
   718         -    );
   719         -    for(i=pIdx->nCol-1; i>=0; i--){
   720         -      int j;
   721         -      if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue;
   722         -      for(j=0; j<2; j++){
   723         -        char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j);
   724         -        zSql = cidxMprintf(&rc, "%z"
   725         -            "%sSELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)",
   726         -            zSql, zSep, zSrcList, zTab, zWhere, zOrderBy
   727         -        );
   728         -        zSep = " UNION ALL ";
   729         -        if( pIdx->aCol[i].bDesc==0 ) break;
          731  +  if( rc==SQLITE_OK ){
          732  +    if( zAfterKey==0 ){
          733  +      *pzSqlOut = cidxMprintf(&rc,
          734  +          "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s "
          735  +          "FROM (SELECT %s FROM %Q INDEXED BY %Q %s%sORDER BY %s) AS i",
          736  +          zSubExpr, zTab, zSubWhere, zCurrentKey, 
          737  +          zSrcList, zTab, zIdxName, 
          738  +          (pIdx->zWhere ? "WHERE " : ""), (pIdx->zWhere ? pIdx->zWhere : ""),
          739  +          zOrderBy
          740  +      );
          741  +    }else{
          742  +      const char *zSep = "";
          743  +      char *zSql;
          744  +      int i;
          745  +  
          746  +      zSql = cidxMprintf(&rc, 
          747  +          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
          748  +          zSubExpr, zTab, zSubWhere, zCurrentKey
          749  +      );
          750  +      for(i=pIdx->nCol-1; i>=0; i--){
          751  +        int j;
          752  +        if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue;
          753  +        for(j=0; j<2; j++){
          754  +          char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j);
          755  +          zSql = cidxMprintf(&rc, "%z"
          756  +              "%sSELECT * FROM ("
          757  +                "SELECT %s FROM %Q INDEXED BY %Q WHERE %s%s%z ORDER BY %s"
          758  +              ")",
          759  +              zSql, zSep, zSrcList, zTab, zIdxName, 
          760  +              pIdx->zWhere ? pIdx->zWhere : "",
          761  +              pIdx->zWhere ? " AND " : "",
          762  +              zWhere, zOrderBy
          763  +          );
          764  +          zSep = " UNION ALL ";
          765  +          if( pIdx->aCol[i].bDesc==0 ) break;
          766  +        }
   730    767         }
          768  +      *pzSqlOut = cidxMprintf(&rc, "%z) AS i", zSql);
   731    769       }
   732         -    *pzSqlOut = cidxMprintf(&rc, "%z) AS i", zSql);
   733    770     }
   734    771   
   735    772     sqlite3_free(zTab);
   736    773     sqlite3_free(zCurrentKey);
   737    774     sqlite3_free(zOrderBy);
   738    775     sqlite3_free(zSubWhere);
   739    776     sqlite3_free(zSubExpr);

Changes to ext/repair/test/checkindex01.test.

    56     56     SELECT errmsg IS NULL, current_key, index_name, after_key, scanner_sql
    57     57       FROM incremental_index_check('i1') LIMIT 1;
    58     58   } {
    59     59     1
    60     60     'five',5
    61     61     i1
    62     62     {}
    63         -  {SELECT (SELECT a IS i.i0 FROM 't1' AS t WHERE "rowid" COLLATE BINARY IS i.i1), quote(i0)||','||quote(i1) FROM (SELECT (a) AS i0, ("rowid" COLLATE BINARY) AS i1 FROM 't1' ORDER BY 1,2) AS i}
           63  +  {SELECT (SELECT a IS i.i0 FROM 't1' AS t WHERE "rowid" COLLATE BINARY IS i.i1), quote(i0)||','||quote(i1) FROM (SELECT (a) AS i0, ("rowid" COLLATE BINARY) AS i1 FROM 't1' INDEXED BY 'i1' ORDER BY 1,2) AS i}
    64     64   }
    65     65   
    66     66   do_index_check_test 1.3 i1 {
    67     67     {} 'five',5
    68     68     {} 'four',4
    69     69     {} 'one',1
    70     70     {} 'three',3
................................................................................
   315    315     {} 3,2,1 
   316    316     {} 6,5,4
   317    317   }
   318    318   do_index_check_test 6.2 t6x3 {
   319    319     {} 3,2,1 
   320    320     {} 6,5,4
   321    321   }
          322  +
          323  +#-------------------------------------------------------------------------
          324  +#
          325  +do_execsql_test 7.0 {
          326  +  CREATE TABLE t7(x INTEGER PRIMARY KEY, y, z);
          327  +  INSERT INTO t7 VALUES(1, 1, 1);
          328  +  INSERT INTO t7 VALUES(2, 2, 0);
          329  +  INSERT INTO t7 VALUES(3, 3, 1);
          330  +  INSERT INTO t7 VALUES(4, 4, 0);
          331  +
          332  +  CREATE INDEX t7i1 ON t7(y) WHERE z=1;
          333  +  CREATE INDEX t7i2 ON t7(y) /* hello,world */ WHERE z=1;
          334  +  CREATE INDEX t7i3 ON t7(y) WHERE -- yep 
          335  +  z=1;
          336  +  CREATE INDEX t7i4 ON t7(y) WHERE z=1 -- yep;
          337  +}
          338  +do_index_check_test 7.1 t7i1 {
          339  +  {} 1,1 {} 3,3
          340  +}
          341  +do_index_check_test 7.2 t7i2 {
          342  +  {} 1,1 {} 3,3
          343  +}
          344  +do_index_check_test 7.3 t7i3 {
          345  +  {} 1,1 {} 3,3
          346  +}
          347  +do_index_check_test 7.4 t7i4 {
          348  +  {} 1,1 {} 3,3
          349  +}
          350  +
          351  +