/ Check-in [31eb27f4]
Login

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

Overview
Comment:Fix RBU so that it does not write rows that should be excluded into partial indexes (corrupting the database).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 31eb27f438ad727b095a518bfe0f7ed37cb806fc1e6929b821eddcc6cc9de260
User & Date: dan 2019-04-11 16:54:20
Context
2019-04-11
17:06
Remove the vfslog.c extension from the testfixture target in main.mk, as that extension has no TCL bindings and is inaccessible. check-in: d71f8bbc user: drh tags: trunk
16:54
Fix RBU so that it does not write rows that should be excluded into partial indexes (corrupting the database). check-in: 31eb27f4 user: dan tags: trunk
2019-04-10
18:29
Update the list of OMIT options in the omittest.tcl script. check-in: f294cfc1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/rbu/rbupartial.test.

            1  +# 2019 April 11
            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  +
           13  +source [file join [file dirname [info script]] rbu_common.tcl]
           14  +set ::testprefix rbupartial
           15  +
           16  +db close
           17  +sqlite3_shutdown
           18  +sqlite3_config_uri 1
           19  +
           20  +foreach {tn without_rowid a b c d} {
           21  +  1 ""              a b c d
           22  +  2 "WITHOUT ROWID" aaa bbb ccc ddd
           23  +  3 "WITHOUT ROWID" "\"hello\"" {"one'two"}  {[c]} ddd
           24  +  4 "WITHOUT ROWID" {`a b`} {"one'two"}  {[c c c]} ddd
           25  +  5 "" a b c {"d""d"}
           26  +  6 "" {'one''two'} b {"c""c"} {"d""d"}
           27  +} {
           28  +  eval [string map [list \
           29  +    %WITHOUT_ROWID% $without_rowid %A% $a %B% $b %C% $c %D% $d
           30  +  ] {
           31  +  reset_db
           32  +  do_execsql_test $tn.1.0 {
           33  +    CREATE TABLE t1(%A% PRIMARY KEY, %B%, %C%, %D%) %WITHOUT_ROWID% ;
           34  +    CREATE INDEX i1b  ON t1(%B%);
           35  +    CREATE INDEX i1b2 ON t1(%B%) WHERE %C%<5;
           36  +    CREATE INDEX i1b3 ON t1(%B%) WHERE %C%>=5;
           37  +
           38  +    CREATE INDEX i1c  ON t1(%C%);
           39  +    CREATE INDEX i1c2 ON t1(%C%) WHERE %C% IS NULL;
           40  +    CREATE INDEX i1c3 ON t1(%C%) WHERE %C% IS NOT NULL;
           41  +
           42  +    CREATE INDEX i1c4 ON t1(%C%) WHERE %D% < 'd';
           43  +  }
           44  +
           45  +  do_execsql_test $tn.1.1 {
           46  +    INSERT INTO t1 VALUES(0, NULL, NULL, 'a');
           47  +    INSERT INTO t1 VALUES(1, 2, 3, 'b');
           48  +    INSERT INTO t1 VALUES(4, 5, 6, 'c');
           49  +    INSERT INTO t1 VALUES(7, 8, 9, 'd');
           50  +  }
           51  +
           52  +  forcedelete rbu.db
           53  +  do_test $tn.1.2 {
           54  +    sqlite3 rbu rbu.db
           55  +    rbu eval {
           56  +      CREATE TABLE data_t1(%A%, %B%, %C%, %D%, rbu_control);
           57  +
           58  +      INSERT INTO data_t1 VALUES(10, 11, 12, 'e', 0);
           59  +      INSERT INTO data_t1 VALUES(13, 14, NULL, 'f', 0);
           60  +
           61  +      INSERT INTO data_t1 VALUES(0, NULL, NULL, NULL, 1);
           62  +      INSERT INTO data_t1 VALUES(4, NULL, NULL, NULL, 1);
           63  +
           64  +      INSERT INTO data_t1 VALUES(7, NULL, 4, NULL, '..x.');
           65  +      INSERT INTO data_t1 VALUES(1, 10, NULL, NULL, '.xx.');
           66  +    }
           67  +    rbu close
           68  +  } {}
           69  +
           70  +  do_test $tn.1.3 {
           71  +    run_rbu test.db rbu.db
           72  +    execsql { PRAGMA integrity_check }
           73  +  } {ok}
           74  +
           75  +  do_execsql_test $tn.1.4 {
           76  +    SELECT * FROM t1 ORDER BY %A%;
           77  +  } {
           78  +    1 10 {} b   7 8 4 d   10 11 12 e   13 14 {} f
           79  +  }
           80  +
           81  +  set step 0
           82  +  do_rbu_vacuum_test $tn.1.5 0
           83  +  }]
           84  +}
           85  +
           86  +finish_test

Changes to ext/rbu/sqlite3rbu.c.

   236    236   **     * a special "cleanup table" state.
   237    237   **
   238    238   ** abIndexed:
   239    239   **   If the table has no indexes on it, abIndexed is set to NULL. Otherwise,
   240    240   **   it points to an array of flags nTblCol elements in size. The flag is
   241    241   **   set for each column that is either a part of the PK or a part of an
   242    242   **   index. Or clear otherwise.
          243  +**
          244  +**   If there are one or more partial indexes on the table, all fields of
          245  +**   this array set set to 1. This is because in that case, the module has
          246  +**   no way to tell which fields will be required to add and remove entries
          247  +**   from the partial indexes.
   243    248   **   
   244    249   */
   245    250   struct RbuObjIter {
   246    251     sqlite3_stmt *pTblIter;         /* Iterate through tables */
   247    252     sqlite3_stmt *pIdxIter;         /* Index iterator */
   248    253     int nTblCol;                    /* Size of azTblCol[] array */
   249    254     char **azTblCol;                /* Array of unquoted target column names */
................................................................................
  1246   1251           sqlite3_mprintf("PRAGMA main.index_list = %Q", pIter->zTbl)
  1247   1252       );
  1248   1253     }
  1249   1254   
  1250   1255     pIter->nIndex = 0;
  1251   1256     while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pList) ){
  1252   1257       const char *zIdx = (const char*)sqlite3_column_text(pList, 1);
         1258  +    int bPartial = sqlite3_column_int(pList, 4);
  1253   1259       sqlite3_stmt *pXInfo = 0;
  1254   1260       if( zIdx==0 ) break;
         1261  +    if( bPartial ){
         1262  +      memset(pIter->abIndexed, 0x01, sizeof(u8)*pIter->nTblCol);
         1263  +    }
  1255   1264       p->rc = prepareFreeAndCollectError(p->dbMain, &pXInfo, &p->zErrmsg,
  1256   1265           sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", zIdx)
  1257   1266       );
  1258   1267       while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){
  1259   1268         int iCid = sqlite3_column_int(pXInfo, 1);
  1260   1269         if( iCid>=0 ) pIter->abIndexed[iCid] = 1;
  1261   1270       }
................................................................................
  1953   1962       rc = sqlite3_reset(p->objiter.pTmpInsert);
  1954   1963     }
  1955   1964   
  1956   1965     if( rc!=SQLITE_OK ){
  1957   1966       sqlite3_result_error_code(pCtx, rc);
  1958   1967     }
  1959   1968   }
         1969  +
         1970  +static char *rbuObjIterGetIndexWhere(sqlite3rbu *p, RbuObjIter *pIter){
         1971  +  sqlite3_stmt *pStmt = 0;
         1972  +  int rc = p->rc;
         1973  +  char *zRet = 0;
         1974  +
         1975  +  if( rc==SQLITE_OK ){
         1976  +    rc = prepareAndCollectError(p->dbMain, &pStmt, &p->zErrmsg,
         1977  +        "SELECT trim(sql) FROM sqlite_master WHERE type='index' AND name=?"
         1978  +    );
         1979  +  }
         1980  +  if( rc==SQLITE_OK ){
         1981  +    int rc2;
         1982  +    rc = sqlite3_bind_text(pStmt, 1, pIter->zIdx, -1, SQLITE_STATIC);
         1983  +    if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
         1984  +      const char *zSql = (const char*)sqlite3_column_text(pStmt, 0);
         1985  +      if( zSql ){
         1986  +        int nParen = 0;           /* Number of open parenthesis */
         1987  +        int i;
         1988  +        for(i=0; zSql[i]; i++){
         1989  +          char c = zSql[i];
         1990  +          if( c=='(' ){
         1991  +            nParen++;
         1992  +          }
         1993  +          else if( c==')' ){
         1994  +            nParen--;
         1995  +            if( nParen==0 ){
         1996  +              i++;
         1997  +              break;
         1998  +            }
         1999  +          }else if( c=='"' || c=='\'' || c=='`' ){
         2000  +            for(i++; 1; i++){
         2001  +              if( zSql[i]==c ){
         2002  +                if( zSql[i+1]!=c ) break;
         2003  +                i++;
         2004  +              }
         2005  +            }
         2006  +          }else if( c=='[' ){
         2007  +            for(i++; 1; i++){
         2008  +              if( zSql[i]==']' ) break;
         2009  +            }
         2010  +          }
         2011  +        }
         2012  +        if( zSql[i] ){
         2013  +          zRet = rbuStrndup(&zSql[i], &rc);
         2014  +        }
         2015  +      }
         2016  +    }
         2017  +
         2018  +    rc2 = sqlite3_finalize(pStmt);
         2019  +    if( rc==SQLITE_OK ) rc = rc2;
         2020  +  }
         2021  +
         2022  +  p->rc = rc;
         2023  +  return zRet;
         2024  +}
  1960   2025   
  1961   2026   /*
  1962   2027   ** Ensure that the SQLite statement handles required to update the 
  1963   2028   ** target database object currently indicated by the iterator passed 
  1964   2029   ** as the second argument are available.
  1965   2030   */
  1966   2031   static int rbuObjIterPrepareAll(
................................................................................
  1983   2048   
  1984   2049       if( zIdx ){
  1985   2050         const char *zTbl = pIter->zTbl;
  1986   2051         char *zImposterCols = 0;    /* Columns for imposter table */
  1987   2052         char *zImposterPK = 0;      /* Primary key declaration for imposter */
  1988   2053         char *zWhere = 0;           /* WHERE clause on PK columns */
  1989   2054         char *zBind = 0;
         2055  +      char *zPart = 0;
  1990   2056         int nBind = 0;
  1991   2057   
  1992   2058         assert( pIter->eType!=RBU_PK_VTAB );
  1993   2059         zCollist = rbuObjIterGetIndexCols(
  1994   2060             p, pIter, &zImposterCols, &zImposterPK, &zWhere, &nBind
  1995   2061         );
  1996   2062         zBind = rbuObjIterGetBindlist(p, nBind);
         2063  +      zPart = rbuObjIterGetIndexWhere(p, pIter);
  1997   2064   
  1998   2065         /* Create the imposter table used to write to this index. */
  1999   2066         sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 1);
  2000   2067         sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 1,tnum);
  2001   2068         rbuMPrintfExec(p, p->dbMain,
  2002   2069             "CREATE TABLE \"rbu_imp_%w\"( %s, PRIMARY KEY( %s ) ) WITHOUT ROWID",
  2003   2070             zTbl, zImposterCols, zImposterPK
................................................................................
  2022   2089         }
  2023   2090   
  2024   2091         /* Create the SELECT statement to read keys in sorted order */
  2025   2092         if( p->rc==SQLITE_OK ){
  2026   2093           char *zSql;
  2027   2094           if( rbuIsVacuum(p) ){
  2028   2095             zSql = sqlite3_mprintf(
  2029         -              "SELECT %s, 0 AS rbu_control FROM '%q' ORDER BY %s%s",
         2096  +              "SELECT %s, 0 AS rbu_control FROM '%q' %s ORDER BY %s%s",
  2030   2097                 zCollist, 
  2031   2098                 pIter->zDataTbl,
  2032         -              zCollist, zLimit
         2099  +              zPart, zCollist, zLimit
  2033   2100             );
  2034   2101           }else
  2035   2102   
  2036   2103           if( pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE ){
  2037   2104             zSql = sqlite3_mprintf(
  2038         -              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' ORDER BY %s%s",
         2105  +              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' %s ORDER BY %s%s",
  2039   2106                 zCollist, p->zStateDb, pIter->zDataTbl,
  2040         -              zCollist, zLimit
         2107  +              zPart, zCollist, zLimit
  2041   2108             );
  2042   2109           }else{
  2043   2110             zSql = sqlite3_mprintf(
  2044         -              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' "
         2111  +              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' %s "
  2045   2112                 "UNION ALL "
  2046   2113                 "SELECT %s, rbu_control FROM '%q' "
  2047         -              "WHERE typeof(rbu_control)='integer' AND rbu_control!=1 "
         2114  +              "%s %s typeof(rbu_control)='integer' AND rbu_control!=1 "
  2048   2115                 "ORDER BY %s%s",
  2049         -              zCollist, p->zStateDb, pIter->zDataTbl, 
         2116  +              zCollist, p->zStateDb, pIter->zDataTbl, zPart,
  2050   2117                 zCollist, pIter->zDataTbl, 
         2118  +              zPart,
         2119  +              (zPart ? "AND" : "WHERE"),
  2051   2120                 zCollist, zLimit
  2052   2121             );
  2053   2122           }
  2054   2123           p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz, zSql);
  2055   2124         }
  2056   2125   
  2057   2126         sqlite3_free(zImposterCols);
  2058   2127         sqlite3_free(zImposterPK);
  2059   2128         sqlite3_free(zWhere);
  2060   2129         sqlite3_free(zBind);
         2130  +      sqlite3_free(zPart);
  2061   2131       }else{
  2062   2132         int bRbuRowid = (pIter->eType==RBU_PK_VTAB)
  2063   2133                       ||(pIter->eType==RBU_PK_NONE)
  2064   2134                       ||(pIter->eType==RBU_PK_EXTERNAL && rbuIsVacuum(p));
  2065   2135         const char *zTbl = pIter->zTbl;       /* Table this step applies to */
  2066   2136         const char *zWrite;                   /* Imposter table name */
  2067   2137