/ Check-in [e6808c98]
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:Fix cases where modifying bound variables using the clear_bindings() or transfer_bindings() APIs can invalidate a query plan.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e6808c988c9cd7b2db23f18b5fe2177151b6f586
User & Date: dan 2009-10-20 07:01:24
Context
2009-10-20
14:08
Update documentation on the sqlite3_prepare_v2() interface. No changes to code. check-in: 3b00e769 user: drh tags: trunk
07:01
Fix cases where modifying bound variables using the clear_bindings() or transfer_bindings() APIs can invalidate a query plan. check-in: e6808c98 user: dan tags: trunk
2009-10-19
22:41
Remove an unreachable condition in the WHERE clause processing of bound parameters and replace it with an assert(), for test coverage. check-in: 3bcd78a1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbeapi.c.

    94     94   #if SQLITE_THREADSAFE
    95     95     sqlite3_mutex *mutex = ((Vdbe*)pStmt)->db->mutex;
    96     96   #endif
    97     97     sqlite3_mutex_enter(mutex);
    98     98     for(i=0; i<p->nVar; i++){
    99     99       sqlite3VdbeMemRelease(&p->aVar[i]);
   100    100       p->aVar[i].flags = MEM_Null;
          101  +  }
          102  +  if( p->isPrepareV2 && p->expmask ){
          103  +    p->expired = 1;
   101    104     }
   102    105     sqlite3_mutex_leave(mutex);
   103    106     return rc;
   104    107   }
   105    108   
   106    109   
   107    110   /**************************** sqlite3_value_  *******************************
................................................................................
  1168   1171   ** SQLITE_OK is returned.
  1169   1172   */
  1170   1173   int sqlite3_transfer_bindings(sqlite3_stmt *pFromStmt, sqlite3_stmt *pToStmt){
  1171   1174     Vdbe *pFrom = (Vdbe*)pFromStmt;
  1172   1175     Vdbe *pTo = (Vdbe*)pToStmt;
  1173   1176     if( pFrom->nVar!=pTo->nVar ){
  1174   1177       return SQLITE_ERROR;
         1178  +  }
         1179  +  if( pTo->isPrepareV2 && pTo->expmask ){
         1180  +    pTo->expired = 1;
         1181  +  }
         1182  +  if( pFrom->isPrepareV2 && pFrom->expmask ){
         1183  +    pFrom->expired = 1;
  1175   1184     }
  1176   1185     return sqlite3TransferBindings(pFromStmt, pToStmt);
  1177   1186   }
  1178   1187   #endif
  1179   1188   
  1180   1189   /*
  1181   1190   ** Return the sqlite3* database handle to which the prepared statement given

Changes to test/analyze3.test.

    36     36   # analyze3-3.*: Test that binding to a variable does not invalidate the 
    37     37   #               query plan when there is no way in which replanning the
    38     38   #               query may produce a superior outcome.
    39     39   #
    40     40   # analyze3-4.*: Test that SQL or authorization callback errors occuring
    41     41   #               within sqlite3Reprepare() are handled correctly.
    42     42   #
           43  +# analyze3-5.*: Check that the query plans of applicable statements are
           44  +#               invalidated if the values of SQL parameter are modified
           45  +#               using the clear_bindings() or transfer_bindings() APIs.
           46  +#
    43     47   
    44     48   proc getvar {varname} { uplevel #0 set $varname }
    45     49   db function var getvar
    46     50   
    47     51   proc eqp {sql {db db}} {
    48     52     uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
    49     53   }
................................................................................
   522    526     execsql { CREATE TABLE t2(d, e, f) }
   523    527     db auth auth
   524    528     sqlite3_step $S
   525    529   } {SQLITE_SCHEMA}
   526    530   do_test analyze3-4.3.2 {
   527    531     sqlite3_finalize $S
   528    532   } {SQLITE_SCHEMA}
          533  +db auth {}
          534  +
          535  +#-------------------------------------------------------------------------
          536  +# Test that modifying bound variables using the clear_bindings() or
          537  +# transfer_bindings() APIs works.
          538  +#
          539  +#   analyze3-5.1.*: sqlite3_clear_bindings()
          540  +#   analyze3-5.2.*: sqlite3_transfer_bindings()
          541  +#
          542  +do_test analyze3-5.1.1 {
          543  +  drop_all_tables
          544  +  execsql {
          545  +    CREATE TABLE t1(x TEXT COLLATE NOCASE);
          546  +    CREATE INDEX i1 ON t1(x);
          547  +    INSERT INTO t1 VALUES('aaa');
          548  +    INSERT INTO t1 VALUES('abb');
          549  +    INSERT INTO t1 VALUES('acc');
          550  +    INSERT INTO t1 VALUES('baa');
          551  +    INSERT INTO t1 VALUES('bbb');
          552  +    INSERT INTO t1 VALUES('bcc');
          553  +  }
          554  +
          555  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy]
          556  +  sqlite3_bind_text $S 1 "a%" 2
          557  +  set R [list]
          558  +  while { "SQLITE_ROW" == [sqlite3_step $S] } {
          559  +    lappend R [sqlite3_column_text $S 0]
          560  +  }
          561  +  concat [sqlite3_reset $S] $R
          562  +} {SQLITE_OK aaa abb acc}
          563  +do_test analyze3-5.1.2 {
          564  +  sqlite3_clear_bindings $S
          565  +  set R [list]
          566  +  while { "SQLITE_ROW" == [sqlite3_step $S] } {
          567  +    lappend R [sqlite3_column_text $S 0]
          568  +  }
          569  +  concat [sqlite3_reset $S] $R
          570  +} {SQLITE_OK}
          571  +do_test analyze3-5.1.3 {
          572  +  sqlite3_finalize $S
          573  +} {SQLITE_OK}
          574  +
          575  +do_test analyze3-5.1.1 {
          576  +  set S1 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy]
          577  +  sqlite3_bind_text $S1 1 "b%" 2
          578  +  set R [list]
          579  +  while { "SQLITE_ROW" == [sqlite3_step $S1] } {
          580  +    lappend R [sqlite3_column_text $S1 0]
          581  +  }
          582  +  concat [sqlite3_reset $S1] $R
          583  +} {SQLITE_OK baa bbb bcc}
          584  +
          585  +do_test analyze3-5.1.2 {
          586  +  set S2 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x = ?" -1 dummy]
          587  +  sqlite3_bind_text $S2 1 "a%" 2
          588  +  sqlite3_transfer_bindings $S2 $S1
          589  +  set R [list]
          590  +  while { "SQLITE_ROW" == [sqlite3_step $S1] } {
          591  +    lappend R [sqlite3_column_text $S1 0]
          592  +  }
          593  +  concat [sqlite3_reset $S1] $R
          594  +} {SQLITE_OK aaa abb acc}
          595  +do_test analyze3-5.1.3 {
          596  +  sqlite3_finalize $S2
          597  +  sqlite3_finalize $S1
          598  +} {SQLITE_OK}
   529    599   
   530    600   finish_test
          601  +