/ Check-in [93a2bace]
Login

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

Overview
Comment: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].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:93a2bace5704f7ecad56541a6b3e06a88f7f393f
User & Date: drh 2016-05-06 16:49:54
Context
2016-05-06
18:47
Corrections to comments on the whereScanInit() interface. check-in: ab7ab415 user: drh tags: trunk
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:30
Fix an error in main.mk causing testfixture to use individual fts5 source code files instead of fts5.c. check-in: 933a1c81 user: dan 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
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 /* Do not defer seeks on 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/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.

   610    610   do_execsql_test intpkey-16.0 {
   611    611     CREATE TABLE t16a(id "INTEGER" PRIMARY KEY AUTOINCREMENT, b [TEXT], c `INT`);
   612    612   } {}
   613    613   do_execsql_test intpkey-16.1 {
   614    614     PRAGMA table_info=t16a;
   615    615   } {0 id INTEGER 0 {} 1 1 b TEXT 0 {} 0 2 c INT 0 {} 0}
   616    616   
   617         -
          617  +# 2016-05-06 ticket https://www.sqlite.org/src/tktview/16c9801ceba4923939085
          618  +# When the schema contains an index on the IPK and no other index
          619  +# and a WHERE clause on a delete uses an OR where both sides referencing
          620  +# the IPK, then it is possible that the OP_Delete will fail because there
          621  +# deferred seek of the OP_Seek is not resolved prior to reaching the OP_Delete.
          622  +#
          623  +do_execsql_test intpkey-17.0 {
          624  +  CREATE TABLE t17(x INTEGER PRIMARY KEY, y TEXT);
          625  +  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
          626  +  CREATE INDEX t17x ON t17(x);
          627  +  DELETE FROM t17 WHERE x=99 OR x<130;
          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;
          637  +} {248 giraffe}
   618    638   
   619    639   finish_test