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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
0a514e62ad1ebe5c12da8daed429ae2f |
User & Date: | drh 2018-04-26 15:50:10.476 |
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: 9fd0faf517 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: 0fcfc36ceb 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: 0a514e62ad 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: 7bb23c2a3d user: drh tags: trunk) | |
Changes
Changes to src/update.c.
︙ | ︙ | |||
598 599 600 601 602 603 604 605 606 607 608 609 610 611 | for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){ /* This branch loads the value of a column that will not be changed ** into a register. This is done if there are no BEFORE triggers, or ** if there are one or more BEFORE triggers that use this value via ** a new.* reference in a trigger program. */ testcase( i==31 ); | > > > > > > | 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 | for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); if( tmask&TRIGGER_BEFORE ){ /* Must preserve copied values even in case the original is ** reloaded in the After-BEFORE-trigger-reload-loop below. ** Ticket d85fffd6ffe856092ed8daefa811b1e399706b28 */ sqlite3VdbeSwapOpcode(v, -1, OP_SCopy, OP_Copy); } }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){ /* This branch loads the value of a column that will not be changed ** into a register. This is done if there are no BEFORE triggers, or ** if there are one or more BEFORE triggers that use this value via ** a new.* reference in a trigger program. */ testcase( i==31 ); |
︙ | ︙ | |||
635 636 637 638 639 640 641 | sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey); VdbeCoverage(v); }else{ sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } | > | | 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 | sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey); VdbeCoverage(v); }else{ sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } /* After-BEFORE-trigger-reload-loop: ** If it did not delete it, the BEFORE trigger may still have modified ** some of the columns of the row being updated. Load the values for ** all columns not modified by the update statement into their registers ** in case this has happened. Only unmodified columns are reloaded. ** The values computed for modified columns use the values before the ** BEFORE trigger runs. See test case trigger1-18.0 (added 2018-04-26) ** for an example. */ |
︙ | ︙ |
Changes to src/vdbe.h.
︙ | ︙ | |||
196 197 198 199 200 201 202 203 204 205 206 207 208 209 | #else # define sqlite3VdbeVerifyNoMallocRequired(A,B) # define sqlite3VdbeVerifyNoResultRow(A) #endif VdbeOp *sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp, int iLineno); void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*); void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8); void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1); void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2); void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3); void sqlite3VdbeChangeP5(Vdbe*, u16 P5); void sqlite3VdbeJumpHere(Vdbe*, int addr); int sqlite3VdbeChangeToNoop(Vdbe*, int addr); int sqlite3VdbeDeletePriorOpcode(Vdbe*, u8 op); | > | 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 | #else # define sqlite3VdbeVerifyNoMallocRequired(A,B) # define sqlite3VdbeVerifyNoResultRow(A) #endif VdbeOp *sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp, int iLineno); void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*); void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8); void sqlite3VdbeSwapOpcode(Vdbe*, u32 addr, u8, u8); void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1); void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2); void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3); void sqlite3VdbeChangeP5(Vdbe*, u16 P5); void sqlite3VdbeJumpHere(Vdbe*, int addr); int sqlite3VdbeChangeToNoop(Vdbe*, int addr); int sqlite3VdbeDeletePriorOpcode(Vdbe*, u8 op); |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
838 839 840 841 842 843 844 845 846 847 848 849 850 851 | void sqlite3VdbeChangeP3(Vdbe *p, u32 addr, int val){ sqlite3VdbeGetOp(p,addr)->p3 = val; } void sqlite3VdbeChangeP5(Vdbe *p, u16 p5){ assert( p->nOp>0 || p->db->mallocFailed ); if( p->nOp>0 ) p->aOp[p->nOp-1].p5 = p5; } /* ** Change the P2 operand of instruction addr so that it points to ** the address of the next instruction to be coded. */ void sqlite3VdbeJumpHere(Vdbe *p, int addr){ sqlite3VdbeChangeP2(p, addr, p->nOp); | > > > > > > | 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 | void sqlite3VdbeChangeP3(Vdbe *p, u32 addr, int val){ sqlite3VdbeGetOp(p,addr)->p3 = val; } void sqlite3VdbeChangeP5(Vdbe *p, u16 p5){ assert( p->nOp>0 || p->db->mallocFailed ); if( p->nOp>0 ) p->aOp[p->nOp-1].p5 = p5; } /* Change the opcode to iNew if it was previously iOld */ void sqlite3VdbeSwapOpcode(Vdbe *p, u32 addr, u8 iOld, u8 iNew){ VdbeOp *pOp = sqlite3VdbeGetOp(p,addr); if( pOp->opcode==iOld ) pOp->opcode = iNew; } /* ** Change the P2 operand of instruction addr so that it points to ** the address of the next instruction to be coded. */ void sqlite3VdbeJumpHere(Vdbe *p, int addr){ sqlite3VdbeChangeP2(p, addr, p->nOp); |
︙ | ︙ |
Changes to test/trigger1.test.
︙ | ︙ | |||
746 747 748 749 750 751 752 753 754 | } {1 1000 2} ;# Not: 1 1000 1000 do_execsql_test trigger1-18.1 { DELETE FROM t18; INSERT INTO t18(a,b,c) VALUES(1,2,3); UPDATE t18 SET c=b, b=b+1 WHERE a=1; SELECT * FROM t18; } {1 3 2} ;# Not: 1 1001 1000 finish_test | > > > > > > > > > > > | 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 | } {1 1000 2} ;# Not: 1 1000 1000 do_execsql_test trigger1-18.1 { DELETE FROM t18; INSERT INTO t18(a,b,c) VALUES(1,2,3); UPDATE t18 SET c=b, b=b+1 WHERE a=1; SELECT * FROM t18; } {1 3 2} ;# Not: 1 1001 1000 # 2018-04-26 ticket [https://www.sqlite.org/src/tktview/d85fffd6ffe856092e] # VDBE Program uses an expired value. # do_execsql_test trigger1-19.0 { CREATE TABLE t19(a INT PRIMARY KEY, b, c)WITHOUT ROWID; INSERT INTO t19(a,b,c) VALUES(1,2,3); CREATE TRIGGER t19r3 BEFORE UPDATE ON t19 BEGIN SELECT new.b; END; UPDATE t19 SET c=b WHERE a=1; SELECT * FROM t19; } {1 2 2} finish_test |