SQLite

Check-in [0978bac6b8]
Login

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

Overview
Comment:Make sure the update hook is not invoked for WITHOUT ROWID tables, as the documentation specifies. This bug was found while adding requirements marks, so a few extraneous requirements marks are included in this check-in.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0978bac6b8aee229d7a0d148546f50d380d06a06
User & Date: drh 2013-11-26 23:27:07.055
Context
2013-11-27
00:45
Add additional test cases and requirements evidence marks for WITHOUT ROWID. (check-in: b408d78810 user: drh tags: trunk)
2013-11-26
23:27
Make sure the update hook is not invoked for WITHOUT ROWID tables, as the documentation specifies. This bug was found while adding requirements marks, so a few extraneous requirements marks are included in this check-in. (check-in: 0978bac6b8 user: drh tags: trunk)
22:46
Add requirements test cases for determining when an expression is true and when it is false. (check-in: 838654e563 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/resolve.c.
348
349
350
351
352
353
354

355

356
357
358
359
360
361
362
            if( iCol==pTab->iPKey ){
              iCol = -1;
            }
            break;
          }
        }
        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && HasRowid(pTab) ){

          iCol = -1;        /* IMP: R-44911-55124 */

        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
            pExpr->affinity = SQLITE_AFF_INTEGER;
          }else if( pExpr->iTable==0 ){
            testcase( iCol==31 );







>
|
>







348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
            if( iCol==pTab->iPKey ){
              iCol = -1;
            }
            break;
          }
        }
        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && HasRowid(pTab) ){
          /* IMP: R-24309-18625 */
          /* IMP: R-44911-55124 */
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
            pExpr->affinity = SQLITE_AFF_INTEGER;
          }else if( pExpr->iTable==0 ){
            testcase( iCol==31 );
Changes to src/vdbe.c.
4105
4106
4107
4108
4109
4110
4111
4112
4113
4114
4115
4116
4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
** If P4 is not NULL then the P1 cursor must have been positioned
** using OP_NotFound prior to invoking this opcode.
*/
case OP_Delete: {
  i64 iKey;
  VdbeCursor *pC;

  iKey = 0;
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->pCursor!=0 );  /* Only valid for real tables, no pseudotables */

  /* If the update-hook will be invoked, set iKey to the rowid of the
  ** row being deleted.
  */
  if( db->xUpdateCallback && pOp->p4.z ){
    assert( pC->isTable );
    assert( pC->rowidIsValid );  /* lastRowid set by previous OP_NotFound */
    iKey = pC->lastRowid;
  }

  /* The OP_Delete opcode always follows an OP_NotExists or OP_Last or
  ** OP_Column on the same table without any intervening operations that
  ** might move or invalidate the cursor.  Hence cursor pC is always pointing
  ** to the row to be deleted and the sqlite3VdbeCursorMoveto() operation
  ** below is always a no-op and cannot fail.  We will run it anyhow, though,
  ** to guard against future changes to the code generator.
  **/
  assert( pC->deferredMoveto==0 );
  rc = sqlite3VdbeCursorMoveto(pC);
  if( NEVER(rc!=SQLITE_OK) ) goto abort_due_to_error;

  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  rc = sqlite3BtreeDelete(pC->pCursor);
  pC->cacheStatus = CACHE_STALE;

  /* Invoke the update-hook if required. */
  if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p4.z ){
    const char *zDb = db->aDb[pC->iDb].zName;
    const char *zTbl = pOp->p4.z;
    db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, zTbl, iKey);
    assert( pC->iDb>=0 );
  }
  if( pOp->p2 & OPFLAG_NCHANGE ) p->nChange++;
  break;







<




<
<
<
<
<
<
<
|
<

















|







4105
4106
4107
4108
4109
4110
4111

4112
4113
4114
4115







4116

4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
** If P4 is not NULL then the P1 cursor must have been positioned
** using OP_NotFound prior to invoking this opcode.
*/
case OP_Delete: {
  i64 iKey;
  VdbeCursor *pC;


  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->pCursor!=0 );  /* Only valid for real tables, no pseudotables */







  iKey = pC->lastRowid;      /* Only used for the update hook */


  /* The OP_Delete opcode always follows an OP_NotExists or OP_Last or
  ** OP_Column on the same table without any intervening operations that
  ** might move or invalidate the cursor.  Hence cursor pC is always pointing
  ** to the row to be deleted and the sqlite3VdbeCursorMoveto() operation
  ** below is always a no-op and cannot fail.  We will run it anyhow, though,
  ** to guard against future changes to the code generator.
  **/
  assert( pC->deferredMoveto==0 );
  rc = sqlite3VdbeCursorMoveto(pC);
  if( NEVER(rc!=SQLITE_OK) ) goto abort_due_to_error;

  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  rc = sqlite3BtreeDelete(pC->pCursor);
  pC->cacheStatus = CACHE_STALE;

  /* Invoke the update-hook if required. */
  if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p4.z && pC->isTable ){
    const char *zDb = db->aDb[pC->iDb].zName;
    const char *zTbl = pOp->p4.z;
    db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, zTbl, iKey);
    assert( pC->iDb>=0 );
  }
  if( pOp->p2 & OPFLAG_NCHANGE ) p->nChange++;
  break;
Changes to test/e_createtable.test.
1650
1651
1652
1653
1654
1655
1656




1657
1658
1659
1660
1661
1662
1663
# Tests for INTEGER PRIMARY KEY and rowid related statements.
#

# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
# of the special case-independent names "rowid", "oid", or "_rowid_" in
# place of a column name.
#




drop_all_tables
do_execsql_test 5.1.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('one', 'first');
  INSERT INTO t1 VALUES('two', 'second');
  INSERT INTO t1 VALUES('three', 'third');
}







>
>
>
>







1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
# Tests for INTEGER PRIMARY KEY and rowid related statements.
#

# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
# of the special case-independent names "rowid", "oid", or "_rowid_" in
# place of a column name.
#
# EVIDENCE-OF: R-06726-07466 A column name can be any of the names
# defined in the CREATE TABLE statement or one of the following special
# identifiers: "ROWID", "OID", or "_ROWID_".
#
drop_all_tables
do_execsql_test 5.1.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('one', 'first');
  INSERT INTO t1 VALUES('two', 'second');
  INSERT INTO t1 VALUES('three', 'third');
}
1673
1674
1675
1676
1677
1678
1679




1680
1681
1682
1683
1684
1685
1686
  9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
}

# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
# named "rowid", "oid" or "_rowid_", then that name always refers the
# explicitly declared column and cannot be used to retrieve the integer
# rowid value.




#
do_execsql_test 5.2.0 {
  CREATE TABLE t2(oid, b);
  CREATE TABLE t3(a, _rowid_);
  CREATE TABLE t4(a, b, rowid);

  INSERT INTO t2 VALUES('one', 'two');







>
>
>
>







1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
  9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
}

# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
# named "rowid", "oid" or "_rowid_", then that name always refers the
# explicitly declared column and cannot be used to retrieve the integer
# rowid value.
#
# EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the
# row key if the CREATE TABLE statement does not define a real column
# with the same name.
#
do_execsql_test 5.2.0 {
  CREATE TABLE t2(oid, b);
  CREATE TABLE t3(a, _rowid_);
  CREATE TABLE t4(a, b, rowid);

  INSERT INTO t2 VALUES('one', 'two');
Changes to test/hook.test.
131
132
133
134
135
136
137

138
139
140

141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160














161
162
163
164
165
166
167
# Simple tests
do_test hook-4.1.1 {
  catchsql {
    DROP TABLE t1;
  }
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);

    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
    INSERT INTO t1 VALUES(3, 'three');

  }
  db update_hook [list lappend ::update_hook]
} {}
do_test hook-4.1.2 {
  execsql {
    INSERT INTO t1 VALUES(4, 'four');
    DELETE FROM t1 WHERE b = 'two';
    UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
    DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
  }
  set ::update_hook
} [list \
    INSERT main t1 4 \
    DELETE main t1 2 \
    UPDATE main t1 1 \
    UPDATE main t1 3 \
    DELETE main t1 1 \
    DELETE main t1 3 \
    DELETE main t1 4 \
]















ifcapable trigger {
  # Update hook is not invoked for changes to sqlite_master
  #
  do_test hook-4.1.3 {
    set ::update_hook {}
    execsql {







>



>




















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







131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
# Simple tests
do_test hook-4.1.1 {
  catchsql {
    DROP TABLE t1;
  }
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
    CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;
    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
    INSERT INTO t1 VALUES(3, 'three');
    INSERT INTO t1w SELECT * FROM t1;
  }
  db update_hook [list lappend ::update_hook]
} {}
do_test hook-4.1.2 {
  execsql {
    INSERT INTO t1 VALUES(4, 'four');
    DELETE FROM t1 WHERE b = 'two';
    UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
    DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
  }
  set ::update_hook
} [list \
    INSERT main t1 4 \
    DELETE main t1 2 \
    UPDATE main t1 1 \
    UPDATE main t1 3 \
    DELETE main t1 1 \
    DELETE main t1 3 \
    DELETE main t1 4 \
]

# EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does
# not fire callbacks for changes to a WITHOUT ROWID table.
#
do_test hook-4.1.2w {
  set ::update_hook {}
  execsql {
    INSERT INTO t1w VALUES(4, 'four');
    DELETE FROM t1w WHERE b = 'two';
    UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;
    DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)
  }
  set ::update_hook
} {}

ifcapable trigger {
  # Update hook is not invoked for changes to sqlite_master
  #
  do_test hook-4.1.3 {
    set ::update_hook {}
    execsql {