SQLite

Check-in [cab86c9094]
Login

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

Overview
Comment:Fix a problem preventing UPDATE statements that use a range-scan on the PK index of a WITHOUT ROWID table from using a one-pass strategy.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | onepass-update
Files: files | file ages | folders
SHA1: cab86c90945126c24c40cf2dedd053a8c46d00d6
User & Date: dan 2017-01-11 15:42:14.353
Context
2017-01-11
19:03
Fix a problem with single-pass multi-row UPDATE statements that invoke REPLACE conflict handling. (check-in: 0a2b8e1b9d user: dan tags: onepass-update)
15:42
Fix a problem preventing UPDATE statements that use a range-scan on the PK index of a WITHOUT ROWID table from using a one-pass strategy. (check-in: cab86c9094 user: dan tags: onepass-update)
2017-01-10
20:04
Changes to allow some multi-row UPDATE statements to avoid the two-pass approach. (check-in: 46db23ccd1 user: dan tags: onepass-update)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/update.c.
︙︙
391
392
393
394
395
396
397

398


399
400
401
402
403
404
405

  **
  ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
  ** strategy that uses an index for which one or more columns are being
  ** updated.  */
  eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
  if( eOnePass==ONEPASS_MULTI ){
    int iCur = aiCurOnePass[1];

    if( iCur>=0 && aToOpen[iCur-iBaseCur] ) eOnePass = ONEPASS_OFF;


  }
  
  if( HasRowid(pTab) ){
    /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
    ** mode, write the rowid into the FIFO. In either of the one-pass modes,
    ** leave it in register regOldRowid.  */
    sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);








>
|
>
>








391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408

  **
  ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
  ** strategy that uses an index for which one or more columns are being
  ** updated.  */
  eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
  if( eOnePass==ONEPASS_MULTI ){
    int iCur = aiCurOnePass[1];
    if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){
      eOnePass = ONEPASS_OFF;
    }
    assert( iCur!=iDataCur || !HasRowid(pTab) );
  }
  
  if( HasRowid(pTab) ){
    /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
    ** mode, write the rowid into the FIFO. In either of the one-pass modes,
    ** leave it in register regOldRowid.  */
    sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);
︙︙
Changes to test/update2.test.
︙︙
76
77
78
79
80
81
82


































83
84

  INSERT INTO t4 VALUES(2, 3, 4);
}

do_execsql_test 3.1 {
  UPDATE t4 SET c=c+2 WHERE c>2;
  SELECT a, c FROM t4 ORDER BY a;
} {1 5 2 6}



































finish_test








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



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

  INSERT INTO t4 VALUES(2, 3, 4);
}

do_execsql_test 3.1 {
  UPDATE t4 SET c=c+2 WHERE c>2;
  SELECT a, c FROM t4 ORDER BY a;
} {1 5 2 6}

#-------------------------------------------------------------------------
#
foreach {tn sql} {
  1 { CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c) }
  2 { CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID }
} {
  execsql { DROP TABLE IF EXISTS b1 }
  execsql $sql
  do_execsql_test 4.$tn.0 {
    CREATE UNIQUE INDEX b1c ON b1(c);

    INSERT INTO b1 VALUES(1, 'a', 1);
    INSERT INTO b1 VALUES(2, 'b', 15);
    INSERT INTO b1 VALUES(3, 'c', 3);
    INSERT INTO b1 VALUES(4, 'd', 4);
    INSERT INTO b1 VALUES(5, 'e', 5);
    INSERT INTO b1 VALUES(6, 'f', 6);
    INSERT INTO b1 VALUES(7, 'g', 7);
  }

  do_execsql_test 4.$tn.1 {
    UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7;
    SELECT * FROM b1 ORDER BY a;
  } {
    1 a 1
    3 c 3
    4 d 14
    5 e 15
    6 f 16
    7 g 17
  }
}


finish_test