/ Check-in [0a514e62]
Login

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

Overview
Comment:Ensure that new.* values of an UPDATE do not get clobbered after the BEFORE triggers run when unmodified columns of the row being updated are reloaded. Fix for ticket [d85fffd6ffe856092ed8da]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0a514e62ad1ebe5c12da8daed429ae2f9d9910471d3c5cef3b6870bdadfefca1
User & Date: drh 2018-04-26 15:50:10
References
2018-04-26
18:34
The previous fix for ticket [d85fffd6ffe856092ed8da] in check-in [0a514e62ad1ebe5c12da8dae] did not completely address the probably in that it only worked for cases where the OP_SCopy that loaded the register was the last instruction in the sequence for the expression, which is not necessarily the case for expressions like CASE...END. This revision prevents the registered that will be recomputed from being cached in the first place. check-in: 9fd0faf5 user: drh tags: trunk
Context
2018-04-26
17:43
When processing an "ORDER BY ... LIMIT" that does not use an index, check whether or not a record may appear in the final result set before adding it to the temp b-tree used for sorting. check-in: 0fcfc36c user: dan tags: trunk
15:50
Ensure that new.* values of an UPDATE do not get clobbered after the BEFORE triggers run when unmodified columns of the row being updated are reloaded. Fix for ticket [d85fffd6ffe856092ed8da] check-in: 0a514e62 user: drh tags: trunk
15:04
Clarification of the behavior of a BEFORE UPDATE trigger when the trigger changes the values of some of the columns used to compute new columns in the UPDATE. check-in: 7bb23c2a user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/update.c.

   598    598     for(i=0; i<pTab->nCol; i++){
   599    599       if( i==pTab->iPKey ){
   600    600         sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i);
   601    601       }else{
   602    602         j = aXRef[i];
   603    603         if( j>=0 ){
   604    604           sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i);
          605  +        if( tmask&TRIGGER_BEFORE ){
          606  +          /* Must preserve copied values even in case the original is
          607  +          ** reloaded in the After-BEFORE-trigger-reload-loop below.
          608  +          ** Ticket d85fffd6ffe856092ed8daefa811b1e399706b28 */
          609  +          sqlite3VdbeSwapOpcode(v, -1, OP_SCopy, OP_Copy);
          610  +        }
   605    611         }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){
   606    612           /* This branch loads the value of a column that will not be changed 
   607    613           ** into a register. This is done if there are no BEFORE triggers, or
   608    614           ** if there are one or more BEFORE triggers that use this value via
   609    615           ** a new.* reference in a trigger program.
   610    616           */
   611    617           testcase( i==31 );
................................................................................
   635    641         sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey);
   636    642         VdbeCoverage(v);
   637    643       }else{
   638    644         sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
   639    645         VdbeCoverage(v);
   640    646       }
   641    647   
   642         -    /* If it did not delete it, the BEFORE trigger may still have modified 
          648  +    /* After-BEFORE-trigger-reload-loop:
          649  +    ** If it did not delete it, the BEFORE trigger may still have modified 
   643    650       ** some of the columns of the row being updated. Load the values for 
   644    651       ** all columns not modified by the update statement into their registers
   645    652       ** in case this has happened. Only unmodified columns are reloaded.
   646    653       ** The values computed for modified columns use the values before the
   647    654       ** BEFORE trigger runs.  See test case trigger1-18.0 (added 2018-04-26)
   648    655       ** for an example.
   649    656       */

Changes to src/vdbe.h.

   196    196   #else
   197    197   # define sqlite3VdbeVerifyNoMallocRequired(A,B)
   198    198   # define sqlite3VdbeVerifyNoResultRow(A)
   199    199   #endif
   200    200   VdbeOp *sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp, int iLineno);
   201    201   void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
   202    202   void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8);
          203  +void sqlite3VdbeSwapOpcode(Vdbe*, u32 addr, u8, u8);
   203    204   void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
   204    205   void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
   205    206   void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3);
   206    207   void sqlite3VdbeChangeP5(Vdbe*, u16 P5);
   207    208   void sqlite3VdbeJumpHere(Vdbe*, int addr);
   208    209   int sqlite3VdbeChangeToNoop(Vdbe*, int addr);
   209    210   int sqlite3VdbeDeletePriorOpcode(Vdbe*, u8 op);

Changes to src/vdbeaux.c.

   838    838   void sqlite3VdbeChangeP3(Vdbe *p, u32 addr, int val){
   839    839     sqlite3VdbeGetOp(p,addr)->p3 = val;
   840    840   }
   841    841   void sqlite3VdbeChangeP5(Vdbe *p, u16 p5){
   842    842     assert( p->nOp>0 || p->db->mallocFailed );
   843    843     if( p->nOp>0 ) p->aOp[p->nOp-1].p5 = p5;
   844    844   }
          845  +
          846  +/* Change the opcode to iNew if it was previously iOld */
          847  +void sqlite3VdbeSwapOpcode(Vdbe *p, u32 addr, u8 iOld, u8 iNew){
          848  +  VdbeOp *pOp = sqlite3VdbeGetOp(p,addr);
          849  +  if( pOp->opcode==iOld ) pOp->opcode = iNew;
          850  +}
   845    851   
   846    852   /*
   847    853   ** Change the P2 operand of instruction addr so that it points to
   848    854   ** the address of the next instruction to be coded.
   849    855   */
   850    856   void sqlite3VdbeJumpHere(Vdbe *p, int addr){
   851    857     sqlite3VdbeChangeP2(p, addr, p->nOp);

Changes to test/trigger1.test.

   746    746   } {1 1000 2}  ;# Not: 1 1000 1000 
   747    747   do_execsql_test trigger1-18.1 {
   748    748     DELETE FROM t18;
   749    749     INSERT INTO t18(a,b,c) VALUES(1,2,3);
   750    750     UPDATE t18 SET c=b, b=b+1 WHERE a=1;
   751    751     SELECT * FROM t18;
   752    752   } {1 3 2}     ;# Not: 1 1001 1000
          753  +
          754  +# 2018-04-26 ticket [https://www.sqlite.org/src/tktview/d85fffd6ffe856092e]
          755  +# VDBE Program uses an expired value.
          756  +#
          757  +do_execsql_test trigger1-19.0 {
          758  +  CREATE TABLE t19(a INT PRIMARY KEY, b, c)WITHOUT ROWID;
          759  +  INSERT INTO t19(a,b,c) VALUES(1,2,3);
          760  +  CREATE TRIGGER t19r3 BEFORE UPDATE ON t19 BEGIN SELECT new.b; END;
          761  +  UPDATE t19 SET c=b WHERE a=1;
          762  +  SELECT * FROM t19;
          763  +} {1 2 2}
   753    764   
   754    765   finish_test