/ Check-in [f7aadfab]
Login

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

Overview
Comment:Disable index seek optimizations on REINDEX when the order of primary keys in the index differs from the main table. Fix for ticket [bba7b69f9849b5bf11b4].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f7aadfab3bb8eb8eb2cd62dc8a6823538387b3329f1efc23ef75482488109478
User & Date: drh 2019-05-14 00:43:13
Context
2019-05-14
11:33
Fix an assert() and potential buffer overrun in fts5 that could occur if the database was corrupt. check-in: 8be8bd0d user: dan tags: trunk
00:43
Disable index seek optimizations on REINDEX when the order of primary keys in the index differs from the main table. Fix for ticket [bba7b69f9849b5bf11b4]. check-in: f7aadfab user: drh tags: trunk
2019-05-13
11:52
Fix an assert() failure in fts5 that could occur when processing a corrupt database. check-in: f158c048 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1924   1924       }
  1925   1925       if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
  1926   1926       for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
  1927   1927         if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
  1928   1928           testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
  1929   1929           pIdx->aiColumn[j] = pPk->aiColumn[i];
  1930   1930           pIdx->azColl[j] = pPk->azColl[i];
         1931  +        if( pPk->aSortOrder[i] ){
         1932  +          /* See ticket https://www.sqlite.org/src/info/bba7b69f9849b5bf */
         1933  +          pIdx->bAscKeyBug = 1;
         1934  +        }
  1931   1935           j++;
  1932   1936         }
  1933   1937       }
  1934   1938       assert( pIdx->nColumn>=pIdx->nKeyCol+n );
  1935   1939       assert( pIdx->nColumn>=j );
  1936   1940     }
  1937   1941   
................................................................................
  3045   3049                            pIndex->nKeyCol); VdbeCoverage(v);
  3046   3050       sqlite3UniqueConstraint(pParse, OE_Abort, pIndex);
  3047   3051       sqlite3VdbeJumpHere(v, j2);
  3048   3052     }else{
  3049   3053       addr2 = sqlite3VdbeCurrentAddr(v);
  3050   3054     }
  3051   3055     sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx);
  3052         -  sqlite3VdbeAddOp1(v, OP_SeekEnd, iIdx);
         3056  +  if( !pIndex->bAscKeyBug ){
         3057  +    /* This OP_SeekEnd opcode makes index insert for a REINDEX go much
         3058  +    ** faster by avoiding unnecessary seeks.  But the optimization does
         3059  +    ** not work for UNIQUE constraint indexes on WITHOUT ROWID tables
         3060  +    ** with DESC primary keys, since those indexes have there keys in
         3061  +    ** a different order from the main table.
         3062  +    ** See ticket: https://www.sqlite.org/src/info/bba7b69f9849b5bf
         3063  +    */
         3064  +    sqlite3VdbeAddOp1(v, OP_SeekEnd, iIdx);
         3065  +  }
  3053   3066     sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, regRecord);
  3054   3067     sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  3055   3068     sqlite3ReleaseTempReg(pParse, regRecord);
  3056   3069     sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v);
  3057   3070     sqlite3VdbeJumpHere(v, addr1);
  3058   3071   
  3059   3072     sqlite3VdbeAddOp1(v, OP_Close, iTab);

Changes to src/sqliteInt.h.

  2249   2249     unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  2250   2250     unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  2251   2251     unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  2252   2252     unsigned isCovering:1;   /* True if this is a covering index */
  2253   2253     unsigned noSkipScan:1;   /* Do not try to use skip-scan if true */
  2254   2254     unsigned hasStat1:1;     /* aiRowLogEst values come from sqlite_stat1 */
  2255   2255     unsigned bNoQuery:1;     /* Do not use this index to optimize queries */
         2256  +  unsigned bAscKeyBug:1;   /* True if the bba7b69f9849b5bf bug applies */
  2256   2257   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  2257   2258     int nSample;             /* Number of elements in aSample[] */
  2258   2259     int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  2259   2260     tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  2260   2261     IndexSample *aSample;    /* Samples of the left-most key */
  2261   2262     tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
  2262   2263     tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */

Changes to test/without_rowid1.test.

   395    395   do_execsql_test 11.1 {
   396    396     CREATE TABLE t11(a TEXT PRIMARY KEY, b INT) WITHOUT ROWID;
   397    397     CREATE INDEX t11a ON t11(a COLLATE NOCASE);
   398    398     INSERT INTO t11(a,b) VALUES ('A',1),('a',2);
   399    399     PRAGMA integrity_check;
   400    400     SELECT a FROM t11 ORDER BY a COLLATE binary;
   401    401   } {ok A a}
          402  +
          403  +# 2019-05-13 ticket https://www.sqlite.org/src/info/bba7b69f9849b5b
          404  +do_execsql_test 12.1 {
          405  +  DROP TABLE IF EXISTS t0;
          406  +  CREATE TABLE t0 (c0 INTEGER PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
          407  +  INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
          408  +  REINDEX;
          409  +  PRAGMA integrity_check;
          410  +} {ok}
          411  +
   402    412     
   403    413   finish_test