/ Check-in [150dd09e]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:In the WHERE generator, when building code for a DELETE operation, make sure that seeks to the main table are not deferred. This is a better fix for the [16c9801ceba49] bug than the previous.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ticket-16c9801ce
Files: files | file ages | folders
SHA1: 150dd09ebd7b17234a79e1811a0fae8b0a7a40d5
User & Date: drh 2016-05-06 16:06:59
Context
2016-05-06
16:49
For DELETE operations, make sure that seeks on the main table are not deferred, since if they are and none of the indexes reference columns of the table, the seek might never occur until the OP_Delete opcode, which is too late. Fix for ticket [16c9801ceba49]. check-in: 93a2bace user: drh tags: trunk
16:06
In the WHERE generator, when building code for a DELETE operation, make sure that seeks to the main table are not deferred. This is a better fix for the [16c9801ceba49] bug than the previous. Closed-Leaf check-in: 150dd09e user: drh tags: ticket-16c9801ce
11:31
Do not scan indexes that have the integer primary key as their left-most column. This fixes the crash of ticket [16c9801ceba] but it seems like the wrong fix. More investigation needed. check-in: 50312273 user: drh tags: ticket-16c9801ce
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   369    369       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   370    370         assert( pIdx->pSchema==pTab->pSchema );
   371    371         sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
   372    372       }
   373    373     }else
   374    374   #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
   375    375     {
   376         -    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
          376  +    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK|WHERE_SEEK_TABLE;
   377    377       if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
   378    378       wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
   379    379       if( HasRowid(pTab) ){
   380    380         /* For a rowid table, initialize the RowSet to an empty set */
   381    381         pPk = 0;
   382    382         nPk = 1;
   383    383         iRowSet = ++pParse->nMem;

Changes to src/sqliteInt.h.

  2539   2539   #define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
  2540   2540   #define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
  2541   2541   #define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
  2542   2542   #define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
  2543   2543   #define WHERE_REOPEN_IDX       0x1000 /* Try to use OP_ReopenIdx */
  2544   2544   #define WHERE_ONEPASS_MULTIROW 0x2000 /* ONEPASS is ok with multiple rows */
  2545   2545   #define WHERE_USE_LIMIT        0x4000 /* There is a constant LIMIT clause */
         2546  +#define WHERE_SEEK_TABLE       0x8000 /* Always seek the main table */
  2546   2547   
  2547   2548   /* Allowed return values from sqlite3WhereIsDistinct()
  2548   2549   */
  2549   2550   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
  2550   2551   #define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
  2551   2552   #define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
  2552   2553   #define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */

Changes to src/where.c.

   285    285     pScan->pOrigWC = pWC;
   286    286     pScan->pWC = pWC;
   287    287     pScan->pIdxExpr = 0;
   288    288     if( pIdx ){
   289    289       j = iColumn;
   290    290       iColumn = pIdx->aiColumn[j];
   291    291       if( iColumn==XN_EXPR ) pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
   292         -    if( iColumn==pIdx->pTable->iPKey && j>0 ) iColumn = XN_ROWID;
          292  +    if( iColumn==pIdx->pTable->iPKey ) iColumn = XN_ROWID;
   293    293     }
   294    294     if( pIdx && iColumn>=0 ){
   295    295       pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
   296    296       pScan->zCollName = pIdx->azColl[j];
   297    297     }else{
   298    298       pScan->idxaff = 0;
   299    299       pScan->zCollName = 0;

Changes to src/wherecode.c.

  1320   1320   
  1321   1321       /* Seek the table cursor, if required */
  1322   1322       disableTerm(pLevel, pRangeStart);
  1323   1323       disableTerm(pLevel, pRangeEnd);
  1324   1324       if( omitTable ){
  1325   1325         /* pIdx is a covering index.  No need to access the main table. */
  1326   1326       }else if( HasRowid(pIdx->pTable) ){
  1327         -      if( pWInfo->eOnePass!=ONEPASS_OFF ){
         1327  +      if( pWInfo->eOnePass!=ONEPASS_OFF
         1328  +       || (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0
         1329  +      ){
  1328   1330           iRowidReg = ++pParse->nMem;
  1329   1331           sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
  1330   1332           sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  1331   1333           sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
  1332   1334           VdbeCoverage(v);
  1333   1335         }else{
  1334   1336           codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);
................................................................................
  1516   1518       /* Run a separate WHERE clause for each term of the OR clause.  After
  1517   1519       ** eliminating duplicates from other WHERE clauses, the action for each
  1518   1520       ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
  1519   1521       */
  1520   1522       wctrlFlags =  WHERE_OMIT_OPEN_CLOSE
  1521   1523                   | WHERE_FORCE_TABLE
  1522   1524                   | WHERE_ONETABLE_ONLY
  1523         -                | WHERE_NO_AUTOINDEX;
         1525  +                | WHERE_NO_AUTOINDEX
         1526  +                | (pWInfo->wctrlFlags & WHERE_SEEK_TABLE);
  1524   1527       for(ii=0; ii<pOrWc->nTerm; ii++){
  1525   1528         WhereTerm *pOrTerm = &pOrWc->a[ii];
  1526   1529         if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
  1527   1530           WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
  1528   1531           Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
  1529   1532           int jmp1 = 0;                   /* Address of jump operation */
  1530   1533           if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){

Changes to test/intpkey.test.

   622    622   #
   623    623   do_execsql_test intpkey-17.0 {
   624    624     CREATE TABLE t17(x INTEGER PRIMARY KEY, y TEXT);
   625    625     INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
   626    626     CREATE INDEX t17x ON t17(x);
   627    627     DELETE FROM t17 WHERE x=99 OR x<130;
   628    628     SELECT * FROM t17;
          629  +} {248 giraffe}
          630  +do_execsql_test intpkey-17.1 {
          631  +  DROP INDEX t17x;
          632  +  DELETE FROM t17;
          633  +  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
          634  +  CREATE INDEX t17x ON t17(abs(x));
          635  +  DELETE FROM t17 WHERE abs(x) IS NULL OR abs(x)<130;
          636  +  SELECT * FROM t17;
   629    637   } {248 giraffe}
   630    638   
   631    639   finish_test