/ Check-in [dc555b10]
Login

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

Overview
Comment:Avoid redundant table b-tree cursor seeks in UPDATE statements that use the two-pass strategy.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dc555b1039c6930f6d15355c698ff917a85e8056
User & Date: dan 2017-01-28 19:45:34
Context
2017-01-28
19:53
Fix a couple comment typos. No changes to code. check-in: 2a2e7d86 user: mistachkin tags: trunk
19:45
Avoid redundant table b-tree cursor seeks in UPDATE statements that use the two-pass strategy. check-in: dc555b10 user: dan tags: trunk
15:26
Updates to the sqlite3_blob documentation. No changes to code. check-in: 426b440a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/session/session1.test.

577
578
579
580
581
582
583
584



















585
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL);
  INSERT INTO t1 VALUES(1, 0.0);
}
do_iterator_test 11.2 * {
  UPDATE t1 SET b = 0.0;
} {
}




















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL);
  INSERT INTO t1 VALUES(1, 0.0);
}
do_iterator_test 11.2 * {
  UPDATE t1 SET b = 0.0;
} {
}

reset_db
do_execsql_test 12.1 {
  CREATE TABLE t1(r INTEGER PRIMARY KEY, a, b);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES(1, 1, 1);
  INSERT INTO t1 VALUES(2, 1, 2);
  INSERT INTO t1 VALUES(3, 1, 3);
}

do_iterator_test 12.2 * {
  UPDATE t1 SET b='one' WHERE a=1;
} {
  {UPDATE t1 0 X.. {i 1 {} {} i 1} {{} {} {} {} t one}}
  {UPDATE t1 0 X.. {i 2 {} {} i 2} {{} {} {} {} t one}}
  {UPDATE t1 0 X.. {i 3 {} {} i 3} {{} {} {} {} t one}}
}



finish_test

Changes to src/sqliteInt.h.

2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
                                      ** the OR optimization  */
#define WHERE_GROUPBY          0x0040 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0080 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0100 /* All output needs to be distinct */
#define WHERE_SORTBYGROUP      0x0200 /* Support sqlite3WhereIsSorted() */
#define WHERE_SEEK_TABLE       0x0400 /* Do not defer seeks on main table */
#define WHERE_ORDERBY_LIMIT    0x0800 /* ORDERBY+LIMIT on the inner loop */
                        /*     0x1000    not currently used */
                        /*     0x2000    not currently used */
#define WHERE_USE_LIMIT        0x4000 /* Use the LIMIT in cost estimates */
                        /*     0x8000    not currently used */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */







|







2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
                                      ** the OR optimization  */
#define WHERE_GROUPBY          0x0040 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0080 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0100 /* All output needs to be distinct */
#define WHERE_SORTBYGROUP      0x0200 /* Support sqlite3WhereIsSorted() */
#define WHERE_SEEK_TABLE       0x0400 /* Do not defer seeks on main table */
#define WHERE_ORDERBY_LIMIT    0x0800 /* ORDERBY+LIMIT on the inner loop */
#define WHERE_SEEK_UNIQ_TABLE  0x1000 /* Do not defer seeks if unique */
                        /*     0x2000    not currently used */
#define WHERE_USE_LIMIT        0x4000 /* Use the LIMIT in cost estimates */
                        /*     0x8000    not currently used */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */

Changes to src/update.c.

388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
  /* Begin the database scan. 
  **
  ** Do not consider a single-pass strategy for a multi-row update if
  ** there are any triggers or foreign keys to process, or rows may
  ** be deleted as a result of REPLACE conflict handling. Any of these
  ** things might disturb a cursor being used to scan through the table
  ** or index, causing a single-pass approach to malfunction.  */
  flags = WHERE_ONEPASS_DESIRED | WHERE_SEEK_TABLE;
  if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
    flags |= WHERE_ONEPASS_MULTIROW;
  }
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur);
  if( pWInfo==0 ) goto update_cleanup;

  /* A one-pass strategy that might update more than one row may not







|







388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
  /* Begin the database scan. 
  **
  ** Do not consider a single-pass strategy for a multi-row update if
  ** there are any triggers or foreign keys to process, or rows may
  ** be deleted as a result of REPLACE conflict handling. Any of these
  ** things might disturb a cursor being used to scan through the table
  ** or index, causing a single-pass approach to malfunction.  */
  flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE;
  if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
    flags |= WHERE_ONEPASS_MULTIROW;
  }
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur);
  if( pWInfo==0 ) goto update_cleanup;

  /* A one-pass strategy that might update more than one row may not

Changes to src/wherecode.c.

1587
1588
1589
1590
1591
1592
1593
1594



1595
1596
1597
1598
1599
1600
1601
      testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
    }

    /* Seek the table cursor, if required */
    if( omitTable ){
      /* pIdx is a covering index.  No need to access the main table. */
    }else if( HasRowid(pIdx->pTable) ){
      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0 ){



        iRowidReg = ++pParse->nMem;
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
        sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
        sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
        VdbeCoverage(v);
      }else{
        codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);







|
>
>
>







1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
      testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
    }

    /* Seek the table cursor, if required */
    if( omitTable ){
      /* pIdx is a covering index.  No need to access the main table. */
    }else if( HasRowid(pIdx->pTable) ){
      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE) || (
          (pWInfo->wctrlFlags & WHERE_SEEK_UNIQ_TABLE) 
       && (pWInfo->eOnePass==ONEPASS_SINGLE)
      )){
        iRowidReg = ++pParse->nMem;
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
        sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
        sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
        VdbeCoverage(v);
      }else{
        codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);

Changes to test/update2.test.

171
172
173
174
175
176
177
178

























179
180
    3 a 3 3
    4 a 14 4
    5 a 15 5
    6 a 16 6
    7 a 17 7
  }
}


























finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
    3 a 3 3
    4 a 14 4
    5 a 15 5
    6 a 16 6
    7 a 17 7
  }
}

#-------------------------------------------------------------------------
#
do_execsql_test 5.0 {
  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX x1c ON x1(b, c);
  INSERT INTO x1 VALUES(1, 'a', 1);
  INSERT INTO x1 VALUES(2, 'a', 2);
  INSERT INTO x1 VALUES(3, 'a', 3);
}

do_execsql_test 5.1.1 {
  UPDATE x1 SET c=c+1 WHERE b='a';
}

do_execsql_test 5.1.2 {
  SELECT * FROM x1;
} {1 a 2 2 a 3 3 a 4}

do_test 5.2 {
  catch { array unset A }
  db eval { EXPLAIN UPDATE x1 SET c=c+1 WHERE b='a' } { incr A($opcode) }
  set A(NotExists)
} {1}


finish_test