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: |
0a514e62ad1ebe5c12da8daed429ae2f |
| 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
Changes to src/update.c.
598
599
600
601
602
603
604
605
606
607
608
609
610
611
...
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
|
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 );
................................................................................
sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey);
VdbeCoverage(v);
}else{
sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
VdbeCoverage(v);
}
/* 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.
*/
|
>
>
>
>
>
>
>
|
|
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
...
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
|
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 );
................................................................................
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
|