Index: src/alter.c ================================================================== --- src/alter.c +++ src/alter.c @@ -1189,31 +1189,36 @@ if( pStep->pSelect ){ sqlite3SelectPrep(pParse, pStep->pSelect, &sNC); if( pParse->nErr ) rc = pParse->rc; } if( rc==SQLITE_OK && pStep->zTarget ){ - Table *pTarget = sqlite3LocateTable(pParse, 0, pStep->zTarget, zDb); - if( pTarget==0 ){ - rc = SQLITE_ERROR; - }else if( SQLITE_OK==(rc = sqlite3ViewGetColumnNames(pParse, pTarget)) ){ - SrcList sSrc; - memset(&sSrc, 0, sizeof(sSrc)); - sSrc.nSrc = 1; - sSrc.a[0].zName = pStep->zTarget; - sSrc.a[0].pTab = pTarget; - sNC.pSrcList = &sSrc; - if( pStep->pWhere ){ + SrcList *pSrc = sqlite3TriggerStepSrc(pParse, pStep); + if( pSrc ){ + int i; + for(i=0; inSrc; i++){ + struct SrcList_item *p = &pSrc->a[i]; + p->pTab = sqlite3LocateTableItem(pParse, 0, p); + p->iCursor = pParse->nTab++; + if( p->pTab==0 ){ + rc = SQLITE_ERROR; + }else{ + p->pTab->nTabRef++; + rc = sqlite3ViewGetColumnNames(pParse, p->pTab); + } + } + sNC.pSrcList = pSrc; + if( rc==SQLITE_OK && pStep->pWhere ){ rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere); } if( rc==SQLITE_OK ){ rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList); } assert( !pStep->pUpsert || (!pStep->pWhere && !pStep->pExprList) ); if( pStep->pUpsert ){ Upsert *pUpsert = pStep->pUpsert; assert( rc==SQLITE_OK ); - pUpsert->pUpsertSrc = &sSrc; + pUpsert->pUpsertSrc = pSrc; sNC.uNC.pUpsert = pUpsert; sNC.ncFlags = NC_UUpsert; rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget); if( rc==SQLITE_OK ){ ExprList *pUpsertSet = pUpsert->pUpsertSet; @@ -1226,10 +1231,13 @@ rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere); } sNC.ncFlags = 0; } sNC.pSrcList = 0; + sqlite3SrcListDelete(db, pSrc); + }else{ + rc = SQLITE_NOMEM; } } } return rc; } Index: src/attach.c ================================================================== --- src/attach.c +++ src/attach.c @@ -596,10 +596,13 @@ if( sqlite3FixExpr(pFix, pStep->pWhere) ){ return 1; } if( sqlite3FixExprList(pFix, pStep->pExprList) ){ return 1; + } + if( pStep->pFrom && sqlite3FixSrcList(pFix, pStep->pFrom) ){ + return 1; } #ifndef SQLITE_OMIT_UPSERT if( pStep->pUpsert ){ Upsert *pUp = pStep->pUpsert; if( sqlite3FixExprList(pFix, pUp->pUpsertTarget) Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -4566,10 +4566,30 @@ pItem->u1.zIndexedBy = sqlite3NameFromToken(pParse->db, pIndexedBy); pItem->fg.isIndexedBy = 1; } } } + +/* +** Append the contents of SrcList p2 to SrcList p1 and return the resulting +** SrcList. Or, if an error occurs, return NULL. In all cases, p1 and p2 +** are deleted by this function. +*/ +SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2){ + assert( p1 && p1->nSrc==1 ); + if( p2 ){ + SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, 1); + if( pNew==0 ){ + sqlite3SrcListDelete(pParse->db, p2); + }else{ + p1 = pNew; + memcpy(&p1->a[1], p2->a, p2->nSrc*sizeof(struct SrcList_item)); + sqlite3DbFree(pParse->db, p2); + } + } + return p1; +} /* ** Add the list of function arguments to the SrcList entry for a ** table-valued-function. */ Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -29,11 +29,11 @@ ** */ Table *sqlite3SrcListLookup(Parse *pParse, SrcList *pSrc){ struct SrcList_item *pItem = pSrc->a; Table *pTab; - assert( pItem && pSrc->nSrc==1 ); + assert( pItem && pSrc->nSrc>=1 ); pTab = sqlite3LocateTableItem(pParse, 0, pItem); sqlite3DeleteTable(pParse->db, pItem->pTab); pItem->pTab = pTab; if( pTab ){ pTab->nTabRef++; Index: src/mem2.c ================================================================== --- src/mem2.c +++ src/mem2.c @@ -377,11 +377,11 @@ /* ** Set the "type" of an allocation. */ void sqlite3MemdebugSetType(void *p, u8 eType){ - if( p && sqlite3GlobalConfig.m.xMalloc==sqlite3MemMalloc ){ + if( p && sqlite3GlobalConfig.m.xFree==sqlite3MemFree ){ struct MemBlockHdr *pHdr; pHdr = sqlite3MemsysGetHeader(p); assert( pHdr->iForeGuard==FOREGUARD ); pHdr->eType = eType; } @@ -396,11 +396,11 @@ ** ** assert( sqlite3MemdebugHasType(p, MEMTYPE_HEAP) ); */ int sqlite3MemdebugHasType(void *p, u8 eType){ int rc = 1; - if( p && sqlite3GlobalConfig.m.xMalloc==sqlite3MemMalloc ){ + if( p && sqlite3GlobalConfig.m.xFree==sqlite3MemFree ){ struct MemBlockHdr *pHdr; pHdr = sqlite3MemsysGetHeader(p); assert( pHdr->iForeGuard==FOREGUARD ); /* Allocation is valid */ if( (pHdr->eType&eType)==0 ){ rc = 0; @@ -418,11 +418,11 @@ ** ** assert( sqlite3MemdebugNoType(p, MEMTYPE_LOOKASIDE) ); */ int sqlite3MemdebugNoType(void *p, u8 eType){ int rc = 1; - if( p && sqlite3GlobalConfig.m.xMalloc==sqlite3MemMalloc ){ + if( p && sqlite3GlobalConfig.m.xFree==sqlite3MemFree ){ struct MemBlockHdr *pHdr; pHdr = sqlite3MemsysGetHeader(p); assert( pHdr->iForeGuard==FOREGUARD ); /* Allocation is valid */ if( (pHdr->eType&eType)!=0 ){ rc = 0; Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -656,11 +656,11 @@ %type from {SrcList*} %destructor from {sqlite3SrcListDelete(pParse->db, $$);} // A complete FROM clause. // -from(A) ::= . {A = sqlite3DbMallocZero(pParse->db, sizeof(*A));} +from(A) ::= . {A = 0;} from(A) ::= FROM seltablist(X). { A = X; sqlite3SrcListShiftJoinType(A); } @@ -888,13 +888,14 @@ where_opt(A) ::= WHERE expr(X). {A = X;} ////////////////////////// The UPDATE command //////////////////////////////// // %if SQLITE_ENABLE_UPDATE_DELETE_LIMIT || SQLITE_UDL_CAPABLE_PARSER -cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) +cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) from(F) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); + X = sqlite3SrcListAppendList(pParse, X, F); sqlite3ExprListCheckLength(pParse,Y,"set list"); #ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT if( O || L ){ updateDeleteLimitError(pParse,O,L); O = 0; @@ -902,14 +903,15 @@ } #endif sqlite3Update(pParse,X,Y,W,R,O,L,0); } %else -cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) +cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) from(F) where_opt(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); + X = sqlite3SrcListAppendList(pParse, X, F); sqlite3Update(pParse,X,Y,W,R,0,0,0); } %endif @@ -1513,12 +1515,12 @@ %type trigger_cmd {TriggerStep*} %destructor trigger_cmd {sqlite3DeleteTriggerStep(pParse->db, $$);} // UPDATE trigger_cmd(A) ::= - UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E). - {A = sqlite3TriggerUpdateStep(pParse, &X, Y, Z, R, B.z, E);} + UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) from(F) where_opt(Z) scanpt(E). + {A = sqlite3TriggerUpdateStep(pParse, &X, F, Y, Z, R, B.z, E);} // INSERT trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). { A = sqlite3TriggerInsertStep(pParse,&X,F,S,R,U,B,Z);/*A-overwrites-R*/ Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -754,30 +754,27 @@ } } #endif switch( pExpr->op ){ -#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) /* The special operator TK_ROW means use the rowid for the first ** column in the FROM clause. This is used by the LIMIT and ORDER BY - ** clause processing on UPDATE and DELETE statements. + ** clause processing on UPDATE and DELETE statements, and by + ** UPDATE ... FROM statement processing. */ case TK_ROW: { SrcList *pSrcList = pNC->pSrcList; struct SrcList_item *pItem; - assert( pSrcList && pSrcList->nSrc==1 ); + assert( pSrcList && pSrcList->nSrc>=1 ); pItem = pSrcList->a; - assert( HasRowid(pItem->pTab) && pItem->pTab->pSelect==0 ); pExpr->op = TK_COLUMN; pExpr->y.pTab = pItem->pTab; pExpr->iTable = pItem->iCursor; - pExpr->iColumn = -1; + pExpr->iColumn--; pExpr->affExpr = SQLITE_AFF_INTEGER; break; } -#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) - && !defined(SQLITE_OMIT_SUBQUERY) */ /* A column name: ID ** Or table name and column name: ID.ID ** Or a database, table and column: ID.ID.ID ** Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -101,10 +101,11 @@ ** Initialize a SelectDest structure. */ void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){ pDest->eDest = (u8)eDest; pDest->iSDParm = iParm; + pDest->iSDParm2 = 0; pDest->zAffSdst = 0; pDest->iSdst = 0; pDest->nSdst = 0; } @@ -973,11 +974,12 @@ testcase( eDest==SRT_Set ); testcase( eDest==SRT_Mem ); testcase( eDest==SRT_Coroutine ); testcase( eDest==SRT_Output ); assert( eDest==SRT_Set || eDest==SRT_Mem - || eDest==SRT_Coroutine || eDest==SRT_Output ); + || eDest==SRT_Coroutine || eDest==SRT_Output + || eDest==SRT_Upfrom ); } sRowLoadInfo.regResult = regResult; sRowLoadInfo.ecelFlags = ecelFlags; #ifdef SQLITE_ENABLE_SORTER_REFERENCES sRowLoadInfo.pExtra = pExtra; @@ -1121,10 +1123,30 @@ sqlite3ReleaseTempReg(pParse, r2); } sqlite3ReleaseTempRange(pParse, r1, nPrefixReg+1); break; } + + case SRT_Upfrom: { +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + if( pSort ){ + pushOntoSorter( + pParse, pSort, p, regResult, regOrig, nResultCol, nPrefixReg); + }else +#endif + { + int i2 = pDest->iSDParm2; + int r1 = sqlite3GetTempReg(pParse); + sqlite3VdbeAddOp3(v, OP_MakeRecord,regResult+(i2<0),nResultCol-(i2<0),r1); + if( i2<0 ){ + sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, regResult); + }else{ + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, regResult, i2); + } + } + break; + } #ifndef SQLITE_OMIT_SUBQUERY /* If we are creating a set for an "expr IN (SELECT ...)" construct, ** then there should be a single item on the stack. Write this ** item into the set table with bogus data. @@ -1145,10 +1167,11 @@ sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, regResult, nResultCol); sqlite3ReleaseTempReg(pParse, r1); } break; } + /* If any row exist in the result set, record that fact and abort. */ case SRT_Exists: { sqlite3VdbeAddOp2(v, OP_Integer, 1, iParm); @@ -1552,10 +1575,23 @@ } case SRT_Mem: { /* The LIMIT clause will terminate the loop for us */ break; } +#endif +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + case SRT_Upfrom: { + int i2 = pDest->iSDParm2; + int r1 = sqlite3GetTempReg(pParse); + sqlite3VdbeAddOp3(v, OP_MakeRecord,regRow+(i2<0),nColumn-(i2<0),r1); + if( i2<0 ){ + sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, regRow); + }else{ + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, regRow, i2); + } + break; + } #endif default: { assert( eDest==SRT_Output || eDest==SRT_Coroutine ); testcase( eDest==SRT_Output ); testcase( eDest==SRT_Coroutine ); @@ -4967,12 +5003,12 @@ ** then create a transient table structure to describe the subquery. */ for(i=0, pFrom=pTabList->a; inSrc; i++, pFrom++){ Table *pTab; assert( pFrom->fg.isRecursive==0 || pFrom->pTab!=0 ); - if( pFrom->fg.isRecursive ) continue; - assert( pFrom->pTab==0 ); + if( pFrom->pTab ) continue; + assert( pFrom->fg.isRecursive==0 ); #ifndef SQLITE_OMIT_CTE if( withExpand(pWalker, pFrom) ) return WRC_Abort; if( pFrom->pTab ) {} else #endif if( pFrom->zName==0 ){ @@ -5789,10 +5825,28 @@ if( sqlite3SelectTrace & 0x104 ){ SELECTTRACE(0x104,pParse,p, ("after name resolution:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif + + /* If the SF_UpdateFrom flag is set, then this function is being called + ** as part of populating the temp table for an UPDATE...FROM statement. + ** In this case, it is an error if the target object (pSrc->a[0]) name + ** or alias is duplicated within FROM clause (pSrc->a[1..n]). */ + if( p->selFlags & SF_UpdateFrom ){ + struct SrcList_item *p0 = &p->pSrc->a[0]; + for(i=1; ipSrc->nSrc; i++){ + struct SrcList_item *p1 = &p->pSrc->a[i]; + if( p0->pTab==p1->pTab && 0==sqlite3_stricmp(p0->zAlias, p1->zAlias) ){ + sqlite3ErrorMsg(pParse, + "target object/alias may not appear in FROM clause: %s", + p0->zAlias ? p0->zAlias : p0->pTab->zName + ); + goto select_end; + } + } + } if( pDest->eDest==SRT_Output ){ generateColumnNames(pParse, p); } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3124,10 +3124,11 @@ #define SF_ComplexResult 0x0040000 /* Result contains subquery or function */ #define SF_WhereBegin 0x0080000 /* Really a WhereBegin() call. Debug Only */ #define SF_WinRewrite 0x0100000 /* Window function rewrite accomplished */ #define SF_View 0x0200000 /* SELECT statement is a view */ #define SF_NoopOrderBy 0x0400000 /* ORDER BY is ignored for this query */ +#define SF_UpdateFrom 0x0800000 /* Statement is an UPDATE...FROM */ /* ** The results of a SELECT can be distributed in several ways, as defined ** by one of the following macros. The "SRT" prefix means "SELECT Result ** Type". @@ -3188,10 +3189,18 @@ ** are distinct. ** ** SRT_DistQueue Store results in priority queue pDest->iSDParm only if ** the same record has never been stored before. The ** index at pDest->iSDParm+1 hold all prior stores. +** +** SRT_Upfrom Store results in the temporary table already opened by +** pDest->iSDParm. If (pDest->iSDParm<0), then the temp +** table is an intkey table - in this case the first +** column returned by the SELECT is used as the integer +** key. If (pDest->iSDParm>0), then the table is an index +** table. (pDest->iSDParm) is the number of key columns in +** each index record in this case. */ #define SRT_Union 1 /* Store result as keys in an index */ #define SRT_Except 2 /* Remove result from a UNION index */ #define SRT_Exists 3 /* Store 1 if the result is not empty */ #define SRT_Discard 4 /* Do not save the results anywhere */ @@ -3207,18 +3216,20 @@ #define SRT_Mem 10 /* Store result in a memory cell */ #define SRT_Set 11 /* Store results as keys in an index */ #define SRT_EphemTab 12 /* Create transient tab and store like SRT_Table */ #define SRT_Coroutine 13 /* Generate a single row of result */ #define SRT_Table 14 /* Store result as data with an automatic rowid */ +#define SRT_Upfrom 15 /* Store result as data with rowid */ /* ** An instance of this object describes where to put of the results of ** a SELECT statement. */ struct SelectDest { - u8 eDest; /* How to dispose of the results. On of SRT_* above. */ + u8 eDest; /* How to dispose of the results. One of SRT_* above. */ int iSDParm; /* A parameter used by the eDest disposal method */ + int iSDParm2; /* A second parameter for the eDest disposal method */ int iSdst; /* Base register where results are written */ int nSdst; /* Number of registers allocated */ char *zAffSdst; /* Affinity used when eDest==SRT_Set */ ExprList *pOrderBy; /* Key columns for SRT_Queue and SRT_DistQueue */ }; @@ -3560,10 +3571,11 @@ u8 op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */ u8 orconf; /* OE_Rollback etc. */ Trigger *pTrig; /* The trigger that this step is a part of */ Select *pSelect; /* SELECT statement or RHS of INSERT INTO SELECT ... */ char *zTarget; /* Target table for DELETE, UPDATE, INSERT */ + SrcList *pFrom; /* FROM clause for UPDATE statement (if any) */ Expr *pWhere; /* The WHERE clause for DELETE or UPDATE steps */ ExprList *pExprList; /* SET clause for UPDATE */ IdList *pIdList; /* Column names for INSERT */ Upsert *pUpsert; /* Upsert clauses on an INSERT */ char *zSpan; /* Original SQL text of this command */ @@ -4228,10 +4240,11 @@ #endif void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*); IdList *sqlite3IdListAppend(Parse*, IdList*, Token*); int sqlite3IdListIndex(IdList*,const char*); SrcList *sqlite3SrcListEnlarge(Parse*, SrcList*, int, int); +SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2); SrcList *sqlite3SrcListAppend(Parse*, SrcList*, Token*, Token*); SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*, Token*, Select*, Expr*, IdList*); void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *); void sqlite3SrcListFuncArgs(Parse*, SrcList*, ExprList*); @@ -4397,17 +4410,18 @@ TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*, const char*,const char*); TriggerStep *sqlite3TriggerInsertStep(Parse*,Token*, IdList*, Select*,u8,Upsert*, const char*,const char*); - TriggerStep *sqlite3TriggerUpdateStep(Parse*,Token*,ExprList*, Expr*, u8, - const char*,const char*); + TriggerStep *sqlite3TriggerUpdateStep(Parse*,Token*,SrcList*,ExprList*, + Expr*, u8, const char*,const char*); TriggerStep *sqlite3TriggerDeleteStep(Parse*,Token*, Expr*, const char*,const char*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); u32 sqlite3TriggerColmask(Parse*,Trigger*,ExprList*,int,int,Table*,int); + SrcList *sqlite3TriggerStepSrc(Parse*, TriggerStep*); # define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p)) # define sqlite3IsToplevel(p) ((p)->pToplevel==0) #else # define sqlite3TriggersExist(B,C,D,E,F) 0 # define sqlite3DeleteTrigger(A,B) @@ -4417,10 +4431,11 @@ # define sqlite3CodeRowTriggerDirect(A,B,C,D,E,F) # define sqlite3TriggerList(X, Y) 0 # define sqlite3ParseToplevel(p) p # define sqlite3IsToplevel(p) 1 # define sqlite3TriggerColmask(A,B,C,D,E,F,G) 0 +# define sqlite3TriggerStepSrc(A,B) 0 #endif int sqlite3JoinType(Parse*, Token*, Token*, Token*); void sqlite3SetJoinExpr(Expr*,int); void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); Index: src/test_malloc.c ================================================================== --- src/test_malloc.c +++ src/test_malloc.c @@ -110,36 +110,10 @@ p = memfault.m.xRealloc(pOld, n); } return p; } -/* -** The following method calls are passed directly through to the underlying -** malloc system: -** -** xFree -** xSize -** xRoundup -** xInit -** xShutdown -*/ -static void faultsimFree(void *p){ - memfault.m.xFree(p); -} -static int faultsimSize(void *p){ - return memfault.m.xSize(p); -} -static int faultsimRoundup(int n){ - return memfault.m.xRoundup(n); -} -static int faultsimInit(void *p){ - return memfault.m.xInit(memfault.m.pAppData); -} -static void faultsimShutdown(void *p){ - memfault.m.xShutdown(memfault.m.pAppData); -} - /* ** This routine configures the malloc failure simulation. After ** calling this routine, the next nDelay mallocs will succeed, followed ** by a block of nRepeat failures, after which malloc() calls will begin ** to succeed again. @@ -202,20 +176,10 @@ /* ** Add or remove the fault-simulation layer using sqlite3_config(). If ** the argument is non-zero, the */ static int faultsimInstall(int install){ - static struct sqlite3_mem_methods m = { - faultsimMalloc, /* xMalloc */ - faultsimFree, /* xFree */ - faultsimRealloc, /* xRealloc */ - faultsimSize, /* xSize */ - faultsimRoundup, /* xRoundup */ - faultsimInit, /* xInit */ - faultsimShutdown, /* xShutdown */ - 0 /* pAppData */ - }; int rc; install = (install ? 1 : 0); assert(memfault.isInstalled==1 || memfault.isInstalled==0); @@ -225,10 +189,13 @@ if( install ){ rc = sqlite3_config(SQLITE_CONFIG_GETMALLOC, &memfault.m); assert(memfault.m.xMalloc); if( rc==SQLITE_OK ){ + sqlite3_mem_methods m = memfault.m; + m.xMalloc = faultsimMalloc; + m.xRealloc = faultsimRealloc; rc = sqlite3_config(SQLITE_CONFIG_MALLOC, &m); } sqlite3_test_control(SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS, faultsimBeginBenign, faultsimEndBenign ); Index: src/trigger.c ================================================================== --- src/trigger.c +++ src/trigger.c @@ -24,10 +24,11 @@ sqlite3ExprDelete(db, pTmp->pWhere); sqlite3ExprListDelete(db, pTmp->pExprList); sqlite3SelectDelete(db, pTmp->pSelect); sqlite3IdListDelete(db, pTmp->pIdList); sqlite3UpsertDelete(db, pTmp->pUpsert); + sqlite3SrcListDelete(db, pTmp->pFrom); sqlite3DbFree(db, pTmp->zSpan); sqlite3DbFree(db, pTmp); } } @@ -484,10 +485,11 @@ ** sees an UPDATE statement inside the body of a CREATE TRIGGER. */ TriggerStep *sqlite3TriggerUpdateStep( Parse *pParse, /* Parser */ Token *pTableName, /* Name of the table to be updated */ + SrcList *pFrom, ExprList *pEList, /* The SET clause: list of column and new values */ Expr *pWhere, /* The WHERE clause */ u8 orconf, /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ @@ -498,20 +500,24 @@ pTriggerStep = triggerStepAllocate(pParse, TK_UPDATE, pTableName,zStart,zEnd); if( pTriggerStep ){ if( IN_RENAME_OBJECT ){ pTriggerStep->pExprList = pEList; pTriggerStep->pWhere = pWhere; + pTriggerStep->pFrom = pFrom; pEList = 0; pWhere = 0; + pFrom = 0; }else{ pTriggerStep->pExprList = sqlite3ExprListDup(db, pEList, EXPRDUP_REDUCE); pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); + pTriggerStep->pFrom = sqlite3SrcListDup(db, pFrom, EXPRDUP_REDUCE); } pTriggerStep->orconf = orconf; } sqlite3ExprListDelete(db, pEList); sqlite3ExprDelete(db, pWhere); + sqlite3SrcListDelete(db, pFrom); return pTriggerStep; } /* ** Construct a trigger step that implements a DELETE statement and return @@ -734,29 +740,32 @@ ** forming the SrcList. This prevents a trigger in one database from ** referring to a target in another database. An exception is when the ** trigger is in TEMP in which case it can refer to any other database it ** wants. */ -static SrcList *targetSrcList( +SrcList *sqlite3TriggerStepSrc( Parse *pParse, /* The parsing context */ TriggerStep *pStep /* The trigger containing the target token */ ){ sqlite3 *db = pParse->db; - int iDb; /* Index of the database to use */ - SrcList *pSrc; /* SrcList to be returned */ - + SrcList *pSrc; /* SrcList to be returned */ + char *zName = sqlite3DbStrDup(db, pStep->zTarget); pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0); + assert( pSrc==0 || pSrc->nSrc==1 ); + assert( zName || pSrc==0 ); if( pSrc ){ - assert( pSrc->nSrc>0 ); - pSrc->a[pSrc->nSrc-1].zName = sqlite3DbStrDup(db, pStep->zTarget); - iDb = sqlite3SchemaToIndex(db, pStep->pTrig->pSchema); - if( iDb==0 || iDb>=2 ){ - const char *zDb; - assert( iDbnDb ); - zDb = db->aDb[iDb].zDbSName; - pSrc->a[pSrc->nSrc-1].zDatabase = sqlite3DbStrDup(db, zDb); - } + Schema *pSchema = pStep->pTrig->pSchema; + pSrc->a[0].zName = zName; + if( pSchema!=db->aDb[1].pSchema ){ + pSrc->a[0].pSchema = pSchema; + } + if( pStep->pFrom ){ + SrcList *pDup = sqlite3SrcListDup(db, pStep->pFrom, 0); + pSrc = sqlite3SrcListAppendList(pParse, pSrc, pDup); + } + }else{ + sqlite3DbFree(db, zName); } return pSrc; } /* @@ -801,30 +810,30 @@ #endif switch( pStep->op ){ case TK_UPDATE: { sqlite3Update(pParse, - targetSrcList(pParse, pStep), + sqlite3TriggerStepSrc(pParse, pStep), sqlite3ExprListDup(db, pStep->pExprList, 0), sqlite3ExprDup(db, pStep->pWhere, 0), pParse->eOrconf, 0, 0, 0 ); break; } case TK_INSERT: { sqlite3Insert(pParse, - targetSrcList(pParse, pStep), + sqlite3TriggerStepSrc(pParse, pStep), sqlite3SelectDup(db, pStep->pSelect, 0), sqlite3IdListDup(db, pStep->pIdList), pParse->eOrconf, sqlite3UpsertDup(db, pStep->pUpsert) ); break; } case TK_DELETE: { sqlite3DeleteFrom(pParse, - targetSrcList(pParse, pStep), + sqlite3TriggerStepSrc(pParse, pStep), sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0 ); break; } default: assert( pStep->op==TK_SELECT ); { Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -127,17 +127,151 @@ ){ if( pIdx->pPartIdxWhere==0 ) return 0; return sqlite3ExprReferencesUpdatedColumn(pIdx->pPartIdxWhere, aXRef, chngRowid); } + +/* +** Allocate and return a pointer to an expression of type TK_ROW with +** Expr.iColumn set to value (iCol+1). The resolver will modify the +** expression to be a TK_COLUMN reading column iCol of the first +** table in the source-list (pSrc->a[0]). +*/ +static Expr *exprRowColumn(Parse *pParse, int iCol){ + Expr *pRet = sqlite3PExpr(pParse, TK_ROW, 0, 0); + if( pRet ) pRet->iColumn = iCol+1; + return pRet; +} + +/* +** Assuming both the pLimit and pOrderBy parameters are NULL, this function +** generates VM code to run the query: +** +** SELECT , pChanges FROM pTabList WHERE pWhere +** +** and write the results to the ephemeral table already opened as cursor +** iEph. None of pChanges, pTabList or pWhere are modified or consumed by +** this function, they must be deleted by the caller. +** +** Or, if pLimit and pOrderBy are not NULL, and pTab is not a view: +** +** SELECT , pChanges FROM pTabList +** WHERE pWhere +** GROUP BY +** ORDER BY pOrderBy LIMIT pLimit +** +** If pTab is a view, the GROUP BY clause is omitted. +** +** Exactly how results are written to table iEph, and exactly what +** the in the query above are is determined by the type +** of table pTabList->a[0].pTab. +** +** If the table is a WITHOUT ROWID table, then argument pPk must be its +** PRIMARY KEY. In this case are the primary key columns +** of the table, in order. The results of the query are written to ephemeral +** table iEph as index keys, using OP_IdxInsert. +** +** If the table is actually a view, then are all columns of +** the view. The results are written to the ephemeral table iEph as records +** with automatically assigned integer keys. +** +** If the table is a virtual or ordinary intkey table, then +** is its rowid. For a virtual table, the results are written to iEph as +** records with automatically assigned integer keys For intkey tables, the +** rowid value in is used as the integer key, and the +** remaining fields make up the table record. +*/ +static void updateFromSelect( + Parse *pParse, /* Parse context */ + int iEph, /* Cursor for open eph. table */ + Index *pPk, /* PK if table 0 is WITHOUT ROWID */ + ExprList *pChanges, /* List of expressions to return */ + SrcList *pTabList, /* List of tables to select from */ + Expr *pWhere, /* WHERE clause for query */ + ExprList *pOrderBy, + Expr *pLimit +){ + int i; + SelectDest dest; + Select *pSelect = 0; + ExprList *pList = 0; + ExprList *pGrp = 0; + Expr *pLimit2 = 0; + ExprList *pOrderBy2 = 0; + sqlite3 *db = pParse->db; + Table *pTab = pTabList->a[0].pTab; + SrcList *pSrc; + Expr *pWhere2; + int eDest; + +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + if( pOrderBy && pLimit==0 ) { + sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on UPDATE"); + return; + } + pOrderBy2 = sqlite3ExprListDup(db, pOrderBy, 0); + pLimit2 = sqlite3ExprDup(db, pLimit, 0); +#endif + + pSrc = sqlite3SrcListDup(db, pTabList, 0); + pWhere2 = sqlite3ExprDup(db, pWhere, 0); + + assert( pTabList->nSrc>1 ); + if( pSrc ){ + pSrc->a[0].iCursor = -1; + pSrc->a[0].pTab->nTabRef--; + pSrc->a[0].pTab = 0; + } + if( pPk ){ + for(i=0; inKeyCol; i++){ + Expr *pNew = exprRowColumn(pParse, pPk->aiColumn[i]); +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + if( pLimit ){ + pGrp = sqlite3ExprListAppend(pParse, pGrp, sqlite3ExprDup(db, pNew, 0)); + } +#endif + pList = sqlite3ExprListAppend(pParse, pList, pNew); + } + eDest = SRT_Upfrom; + }else if( pTab->pSelect ){ + for(i=0; inCol; i++){ + pList = sqlite3ExprListAppend(pParse, pList, exprRowColumn(pParse, i)); + } + eDest = SRT_Table; + }else{ + eDest = IsVirtual(pTab) ? SRT_Table : SRT_Upfrom; + pList = sqlite3ExprListAppend(pParse, 0, sqlite3PExpr(pParse,TK_ROW,0,0)); +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + if( pLimit ){ + pGrp = sqlite3ExprListAppend(pParse, 0, sqlite3PExpr(pParse,TK_ROW,0,0)); + } +#endif + } + if( ALWAYS(pChanges) ){ + for(i=0; inExpr; i++){ + pList = sqlite3ExprListAppend(pParse, pList, + sqlite3ExprDup(db, pChanges->a[i].pExpr, 0) + ); + } + } + pSelect = sqlite3SelectNew(pParse, pList, + pSrc, pWhere2, pGrp, 0, pOrderBy2, SF_UpdateFrom|SF_IncludeHidden, pLimit2 + ); + sqlite3SelectDestInit(&dest, eDest, iEph); + dest.iSDParm2 = (pPk ? pPk->nKeyCol : -1); + sqlite3Select(pParse, pSelect, &dest); + sqlite3SelectDelete(db, pSelect); +} /* ** Process an UPDATE statement. ** -** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; -** \_______/ \________/ \______/ \________________/ -* onError pTabList pChanges pWhere +** UPDATE OR IGNORE tbl SET a=b, c=d FROM tbl2... WHERE e<5 AND f NOT NULL; +** \_______/ \_/ \______/ \_____/ \________________/ +** onError | pChanges | pWhere +** \_______________________/ +** pTabList */ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ @@ -167,10 +301,11 @@ u8 *aToOpen; /* 1 for tables and indices to be opened */ u8 chngPk; /* PRIMARY KEY changed in a WITHOUT ROWID table */ u8 chngRowid; /* Rowid changed in a normal table */ u8 chngKey; /* Either chngPk or chngRowid */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ + int iRowidExpr = -1; /* Index of "rowid=" (or IPK) assignment in pChanges */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int eOnePass; /* ONEPASS_XXX value from where.c */ int hasFK; /* True if foreign key processing is required */ @@ -190,10 +325,11 @@ int addrOpen = 0; /* Address of OP_OpenEphemeral */ int iPk = 0; /* First of nPk cells holding PRIMARY KEY value */ i16 nPk = 0; /* Number of components of the PRIMARY KEY */ int bReplace = 0; /* True if REPLACE conflict resolution might happen */ int bFinishSeek = 1; /* The OP_FinishSeek opcode is needed */ + int nChangeFrom = 0; /* If there is a FROM, pChanges->nExpr, else 0 */ /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid = 0; /* The old rowid */ int regNewRowid = 0; /* The new rowid */ @@ -205,11 +341,10 @@ memset(&sContext, 0, sizeof(sContext)); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto update_cleanup; } - assert( pTabList->nSrc==1 ); /* Locate the table which we want to update. */ pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; @@ -230,12 +365,19 @@ #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif + /* If there was a FROM clause, set nChangeFrom to the number of expressions + ** in the change-list. Otherwise, set it to 0. There cannot be a FROM + ** clause if this function is being called to generate code for part of + ** an UPSERT statement. */ + nChangeFrom = (pTabList->nSrc>1) ? pChanges->nExpr : 0; + assert( nChangeFrom==0 || pUpsert==0 ); + #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT - if( !isView ){ + if( !isView && nChangeFrom==0 ){ pWhere = sqlite3LimitWhere( pParse, pTabList, pWhere, pOrderBy, pLimit, "UPDATE" ); pOrderBy = 0; pLimit = 0; @@ -300,18 +442,21 @@ ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = chngPk = 0; for(i=0; inExpr; i++){ - if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ + /* If this is an UPDATE with a FROM clause, do not resolve expressions + ** here. The call to sqlite3Select() below will do that. */ + if( nChangeFrom==0 && sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; jnCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zEName)==0 ){ if( j==pTab->iPKey ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; + iRowidExpr = i; }else if( pPk && (pTab->aCol[j].colFlags & COLFLAG_PRIMKEY)!=0 ){ chngPk = 1; } #ifndef SQLITE_OMIT_GENERATED_COLUMNS else if( pTab->aCol[j].colFlags & COLFLAG_GENERATED ){ @@ -330,10 +475,11 @@ if( j>=pTab->nCol ){ if( pPk==0 && sqlite3IsRowid(pChanges->a[i].zEName) ){ j = -1; chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; + iRowidExpr = i; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zEName); pParse->checkSchema = 1; goto update_cleanup; } @@ -459,11 +605,11 @@ /* If we are trying to update a view, realize that view into ** an ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) - if( isView ){ + if( nChangeFrom==0 && isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, pOrderBy, pLimit, iDataCur ); pOrderBy = 0; pLimit = 0; @@ -471,11 +617,11 @@ #endif /* Resolve the column names in all the expressions in the ** WHERE clause. */ - if( sqlite3ResolveExprNames(&sNC, pWhere) ){ + if( nChangeFrom==0 && sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } #ifndef SQLITE_OMIT_VIRTUALTABLE /* Virtual tables must be handled separately */ @@ -498,109 +644,132 @@ ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } - if( HasRowid(pTab) ){ - sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); - }else{ - assert( pPk!=0 ); - nPk = pPk->nKeyCol; - iPk = pParse->nMem+1; - pParse->nMem += nPk; - regKey = ++pParse->nMem; - if( pUpsert==0 ){ - iEph = pParse->nTab++; - sqlite3VdbeAddOp3(v, OP_Null, 0, iPk, iPk+nPk-1); - addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk); - sqlite3VdbeSetP4KeyInfo(pParse, pPk); - } - } - - if( pUpsert ){ - /* If this is an UPSERT, then all cursors have already been opened by - ** the outer INSERT and the data cursor should be pointing at the row - ** that is to be updated. So bypass the code that searches for the - ** row(s) to be updated. - */ - pWInfo = 0; - eOnePass = ONEPASS_SINGLE; - sqlite3ExprIfFalse(pParse, pWhere, labelBreak, SQLITE_JUMPIFNULL); - bFinishSeek = 0; - }else{ - /* Begin the database scan. - ** - ** Do not consider a single-pass strategy for a multi-row update if - ** there are any triggers or foreign keys to process, or rows may - ** be deleted as a result of REPLACE conflict handling. Any of these - ** things might disturb a cursor being used to scan through the table - ** or index, causing a single-pass approach to malfunction. */ - flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE; - if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){ - flags |= WHERE_ONEPASS_MULTIROW; - } - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur); - if( pWInfo==0 ) goto update_cleanup; - - /* A one-pass strategy that might update more than one row may not - ** be used if any column of the index used for the scan is being - ** updated. Otherwise, if there is an index on "b", statements like - ** the following could create an infinite loop: - ** - ** UPDATE t1 SET b=b+1 WHERE b>? - ** - ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI - ** strategy that uses an index for which one or more columns are being - ** updated. */ - eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); - bFinishSeek = sqlite3WhereUsesDeferredSeek(pWInfo); - if( eOnePass!=ONEPASS_SINGLE ){ - sqlite3MultiWrite(pParse); - if( eOnePass==ONEPASS_MULTI ){ - int iCur = aiCurOnePass[1]; - if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){ - eOnePass = ONEPASS_OFF; - } - assert( iCur!=iDataCur || !HasRowid(pTab) ); - } - } - } - - if( HasRowid(pTab) ){ - /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF - ** mode, write the rowid into the FIFO. In either of the one-pass modes, - ** leave it in register regOldRowid. */ - sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid); - if( eOnePass==ONEPASS_OFF ){ - /* We need to use regRowSet, so reallocate aRegIdx[nAllIdx] */ - aRegIdx[nAllIdx] = ++pParse->nMem; - sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); - } - }else{ - /* Read the PK of the current row into an array of registers. In - ** ONEPASS_OFF mode, serialize the array into a record and store it in - ** the ephemeral table. Or, in ONEPASS_SINGLE or MULTI mode, change - ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table - ** is not required) and leave the PK fields in the array of registers. */ - for(i=0; iaiColumn[i]>=0 ); - sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, - pPk->aiColumn[i], iPk+i); - } - if( eOnePass ){ - if( addrOpen ) sqlite3VdbeChangeToNoop(v, addrOpen); - nKey = nPk; - regKey = iPk; - }else{ - sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey, - sqlite3IndexAffinityStr(db, pPk), nPk); - sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk); - } - } - - if( pUpsert==0 ){ - if( eOnePass!=ONEPASS_MULTI ){ + if( nChangeFrom==0 && HasRowid(pTab) ){ + sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); + }else{ + assert( pPk!=0 || HasRowid(pTab) ); + nPk = pPk ? pPk->nKeyCol : 0; + iPk = pParse->nMem+1; + pParse->nMem += nPk; + pParse->nMem += nChangeFrom; + regKey = ++pParse->nMem; + if( pUpsert==0 ){ + int nEphCol = nPk + nChangeFrom + (isView ? pTab->nCol : 0); + iEph = pParse->nTab++; + if( pPk ) sqlite3VdbeAddOp3(v, OP_Null, 0, iPk, iPk+nPk-1); + addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nEphCol); + if( pPk ){ + KeyInfo *pKeyInfo = sqlite3KeyInfoOfIndex(pParse, pPk); + if( pKeyInfo ){ + pKeyInfo->nAllField = nEphCol; + sqlite3VdbeAppendP4(v, pKeyInfo, P4_KEYINFO); + } + } + if( nChangeFrom ){ + updateFromSelect( + pParse, iEph, pPk, pChanges, pTabList, pWhere, pOrderBy, pLimit + ); +#ifndef SQLITE_OMIT_SUBQUERY + if( isView ) iDataCur = iEph; +#endif + } + } + } + + if( nChangeFrom ){ + sqlite3MultiWrite(pParse); + eOnePass = ONEPASS_OFF; + nKey = nPk; + regKey = iPk; + }else{ + if( pUpsert ){ + /* If this is an UPSERT, then all cursors have already been opened by + ** the outer INSERT and the data cursor should be pointing at the row + ** that is to be updated. So bypass the code that searches for the + ** row(s) to be updated. + */ + pWInfo = 0; + eOnePass = ONEPASS_SINGLE; + sqlite3ExprIfFalse(pParse, pWhere, labelBreak, SQLITE_JUMPIFNULL); + bFinishSeek = 0; + }else{ + /* Begin the database scan. + ** + ** Do not consider a single-pass strategy for a multi-row update if + ** there are any triggers or foreign keys to process, or rows may + ** be deleted as a result of REPLACE conflict handling. Any of these + ** things might disturb a cursor being used to scan through the table + ** or index, causing a single-pass approach to malfunction. */ + flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE; + if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){ + flags |= WHERE_ONEPASS_MULTIROW; + } + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags,iIdxCur); + if( pWInfo==0 ) goto update_cleanup; + + /* A one-pass strategy that might update more than one row may not + ** be used if any column of the index used for the scan is being + ** updated. Otherwise, if there is an index on "b", statements like + ** the following could create an infinite loop: + ** + ** UPDATE t1 SET b=b+1 WHERE b>? + ** + ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI + ** strategy that uses an index for which one or more columns are being + ** updated. */ + eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); + bFinishSeek = sqlite3WhereUsesDeferredSeek(pWInfo); + if( eOnePass!=ONEPASS_SINGLE ){ + sqlite3MultiWrite(pParse); + if( eOnePass==ONEPASS_MULTI ){ + int iCur = aiCurOnePass[1]; + if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){ + eOnePass = ONEPASS_OFF; + } + assert( iCur!=iDataCur || !HasRowid(pTab) ); + } + } + } + + if( HasRowid(pTab) ){ + /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF + ** mode, write the rowid into the FIFO. In either of the one-pass modes, + ** leave it in register regOldRowid. */ + sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid); + if( eOnePass==ONEPASS_OFF ){ + /* We need to use regRowSet, so reallocate aRegIdx[nAllIdx] */ + aRegIdx[nAllIdx] = ++pParse->nMem; + sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); + } + }else{ + /* Read the PK of the current row into an array of registers. In + ** ONEPASS_OFF mode, serialize the array into a record and store it in + ** the ephemeral table. Or, in ONEPASS_SINGLE or MULTI mode, change + ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table + ** is not required) and leave the PK fields in the array of registers. */ + for(i=0; iaiColumn[i]>=0 ); + sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, + pPk->aiColumn[i], iPk+i); + } + if( eOnePass ){ + if( addrOpen ) sqlite3VdbeChangeToNoop(v, addrOpen); + nKey = nPk; + regKey = iPk; + }else{ + sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey, + sqlite3IndexAffinityStr(db, pPk), nPk); + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk); + } + } + } + + if( pUpsert==0 ){ + if( nChangeFrom==0 && eOnePass!=ONEPASS_MULTI ){ sqlite3WhereEnd(pWInfo); } if( !isView ){ int addrOnce = 0; @@ -632,16 +801,35 @@ labelContinue = sqlite3VdbeMakeLabel(pParse); } sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak); VdbeCoverageIf(v, pPk==0); VdbeCoverageIf(v, pPk!=0); - }else if( pPk ){ + }else if( pPk || nChangeFrom ){ labelContinue = sqlite3VdbeMakeLabel(pParse); sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v); - addrTop = sqlite3VdbeAddOp2(v, OP_RowData, iEph, regKey); - sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0); - VdbeCoverage(v); + addrTop = sqlite3VdbeCurrentAddr(v); + if( nChangeFrom ){ + if( !isView ){ + if( pPk ){ + for(i=0; i=0 ); + if( nChangeFrom==0 ){ + sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); + }else{ + sqlite3VdbeAddOp3(v, OP_Column, iEph, iRowidExpr, regNewRowid); + } sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); VdbeCoverage(v); } /* Compute the old pre-UPDATE content of the row being changed, if that ** information is needed */ @@ -706,11 +899,17 @@ }else if( (pTab->aCol[i].colFlags & COLFLAG_GENERATED)!=0 ){ if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--; }else{ j = aXRef[i]; if( j>=0 ){ - sqlite3ExprCode(pParse, pChanges->a[j].pExpr, k); + if( nChangeFrom ){ + assert( eOnePass==ONEPASS_OFF ); + int nOff = (isView ? pTab->nCol : nPk); + sqlite3VdbeAddOp3(v, OP_Column, iEph, nOff+j, k); + }else{ + sqlite3ExprCode(pParse, pChanges->a[j].pExpr, k); + } }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. @@ -738,47 +937,49 @@ if( tmask&TRIGGER_BEFORE ){ sqlite3TableAffinity(v, pTab, regNew); sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, regOldRowid, onError, labelContinue); - /* The row-trigger may have deleted the row being updated. In this - ** case, jump to the next row. No updates or AFTER triggers are - ** required. This behavior - what happens when the row being updated - ** is deleted or renamed by a BEFORE trigger - is left undefined in the - ** documentation. - */ - if( pPk ){ - 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. - */ - for(i=0, k=regNew; inCol; i++, k++){ - if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){ - if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--; - }else if( aXRef[i]<0 && i!=pTab->iPKey ){ - sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k); - } - } + if( !isView ){ + /* The row-trigger may have deleted the row being updated. In this + ** case, jump to the next row. No updates or AFTER triggers are + ** required. This behavior - what happens when the row being updated + ** is deleted or renamed by a BEFORE trigger - is left undefined in the + ** documentation. + */ + if( pPk ){ + 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. + */ + for(i=0, k=regNew; inCol; i++, k++){ + if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){ + if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--; + }else if( aXRef[i]<0 && i!=pTab->iPKey ){ + sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k); + } + } #ifndef SQLITE_OMIT_GENERATED_COLUMNS - if( pTab->tabFlags & TF_HasGenerated ){ - testcase( pTab->tabFlags & TF_HasVirtual ); - testcase( pTab->tabFlags & TF_HasStored ); - sqlite3ComputeGeneratedColumns(pParse, regNew, pTab); - } + if( pTab->tabFlags & TF_HasGenerated ){ + testcase( pTab->tabFlags & TF_HasVirtual ); + testcase( pTab->tabFlags & TF_HasStored ); + sqlite3ComputeGeneratedColumns(pParse, regNew, pTab); + } #endif + } } if( !isView ){ /* Do constraint checks. */ assert( regOldRowid>0 ); @@ -877,11 +1078,11 @@ if( eOnePass==ONEPASS_SINGLE ){ /* Nothing to do at end-of-loop for a single-pass */ }else if( eOnePass==ONEPASS_MULTI ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3WhereEnd(pWInfo); - }else if( pPk ){ + }else if( pPk || nChangeFrom ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop); VdbeCoverage(v); }else{ sqlite3VdbeGoto(v, labelContinue); } @@ -980,73 +1181,100 @@ assert( v ); ephemTab = pParse->nTab++; addr= sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, nArg); regArg = pParse->nMem + 1; pParse->nMem += nArg; - regRec = ++pParse->nMem; - regRowid = ++pParse->nMem; - - /* Start scanning the virtual table */ - pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0,0,WHERE_ONEPASS_DESIRED,0); - if( pWInfo==0 ) return; - - /* Populate the argument registers. */ - for(i=0; inCol; i++){ - assert( (pTab->aCol[i].colFlags & COLFLAG_GENERATED)==0 ); - if( aXRef[i]>=0 ){ - sqlite3ExprCode(pParse, pChanges->a[aXRef[i]].pExpr, regArg+2+i); - }else{ - sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, i, regArg+2+i); - sqlite3VdbeChangeP5(v, OPFLAG_NOCHNG);/* Enable sqlite3_vtab_nochange() */ - } - } - if( HasRowid(pTab) ){ - sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg); - if( pRowid ){ - sqlite3ExprCode(pParse, pRowid, regArg+1); - }else{ - sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg+1); - } - }else{ - Index *pPk; /* PRIMARY KEY index */ - i16 iPk; /* PRIMARY KEY column */ - pPk = sqlite3PrimaryKeyIndex(pTab); - assert( pPk!=0 ); - assert( pPk->nKeyCol==1 ); - iPk = pPk->aiColumn[0]; - sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, iPk, regArg); - sqlite3VdbeAddOp2(v, OP_SCopy, regArg+2+iPk, regArg+1); - } - - eOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy); - - /* There is no ONEPASS_MULTI on virtual tables */ - assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE ); - - if( eOnePass ){ - /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded - ** above. */ - sqlite3VdbeChangeToNoop(v, addr); - sqlite3VdbeAddOp1(v, OP_Close, iCsr); - }else{ - /* Create a record from the argument register contents and insert it into - ** the ephemeral table. */ - sqlite3MultiWrite(pParse); - sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec); -#if defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_NULL_TRIM) - /* Signal an assert() within OP_MakeRecord that it is allowed to - ** accept no-change records with serial_type 10 */ - sqlite3VdbeChangeP5(v, OPFLAG_NOCHNG_MAGIC); -#endif - sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid); - sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid); + if( pSrc->nSrc>1 ){ + Expr *pRow; + ExprList *pList; + if( pRowid ){ + pRow = sqlite3ExprDup(db, pRowid, 0); + }else{ + pRow = sqlite3PExpr(pParse, TK_ROW, 0, 0); + } + pList = sqlite3ExprListAppend(pParse, 0, pRow); + + for(i=0; inCol; i++){ + if( aXRef[i]>=0 ){ + pList = sqlite3ExprListAppend(pParse, pList, + sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0) + ); + }else{ + pList = sqlite3ExprListAppend(pParse, pList, exprRowColumn(pParse, i)); + } + } + + updateFromSelect(pParse, ephemTab, 0, pList, pSrc, pWhere, 0, 0); + sqlite3ExprListDelete(db, pList); + eOnePass = ONEPASS_OFF; + }else{ + regRec = ++pParse->nMem; + regRowid = ++pParse->nMem; + + /* Start scanning the virtual table */ + pWInfo = sqlite3WhereBegin(pParse, pSrc,pWhere,0,0,WHERE_ONEPASS_DESIRED,0); + if( pWInfo==0 ) return; + + /* Populate the argument registers. */ + for(i=0; inCol; i++){ + assert( (pTab->aCol[i].colFlags & COLFLAG_GENERATED)==0 ); + if( aXRef[i]>=0 ){ + sqlite3ExprCode(pParse, pChanges->a[aXRef[i]].pExpr, regArg+2+i); + }else{ + sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, i, regArg+2+i); + sqlite3VdbeChangeP5(v, OPFLAG_NOCHNG);/* For sqlite3_vtab_nochange() */ + } + } + if( HasRowid(pTab) ){ + sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg); + if( pRowid ){ + sqlite3ExprCode(pParse, pRowid, regArg+1); + }else{ + sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg+1); + } + }else{ + Index *pPk; /* PRIMARY KEY index */ + i16 iPk; /* PRIMARY KEY column */ + pPk = sqlite3PrimaryKeyIndex(pTab); + assert( pPk!=0 ); + assert( pPk->nKeyCol==1 ); + iPk = pPk->aiColumn[0]; + sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, iPk, regArg); + sqlite3VdbeAddOp2(v, OP_SCopy, regArg+2+iPk, regArg+1); + } + + eOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy); + + /* There is no ONEPASS_MULTI on virtual tables */ + assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE ); + + if( eOnePass ){ + /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded + ** above. */ + sqlite3VdbeChangeToNoop(v, addr); + sqlite3VdbeAddOp1(v, OP_Close, iCsr); + }else{ + /* Create a record from the argument register contents and insert it into + ** the ephemeral table. */ + sqlite3MultiWrite(pParse); + sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec); +#if defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_NULL_TRIM) + /* Signal an assert() within OP_MakeRecord that it is allowed to + ** accept no-change records with serial_type 10 */ + sqlite3VdbeChangeP5(v, OPFLAG_NOCHNG_MAGIC); +#endif + sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid); + sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid); + } } if( eOnePass==ONEPASS_OFF ){ /* End the virtual table scan */ - sqlite3WhereEnd(pWInfo); + if( pSrc->nSrc==1 ){ + sqlite3WhereEnd(pWInfo); + } /* Begin scannning through the ephemeral table. */ addr = sqlite3VdbeAddOp1(v, OP_Rewind, ephemTab); VdbeCoverage(v); /* Extract arguments from the current row of the ephemeral table and Index: test/altertab3.test ================================================================== --- test/altertab3.test +++ test/altertab3.test @@ -583,8 +583,22 @@ } {{CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( 0 AND (SELECT rowid FROM "xyz") ) BEGIN DELETE FROM v2; END}} + +#------------------------------------------------------------------------ +# +reset_db +do_execsql_test 25.1 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(a, b, c); + CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN + UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a; + END; +} +#do_execsql_test 25.2 { +# ALTER TABLE t2 RENAME COLUMN a TO aaa; +#} finish_test ADDED test/fts4upfrom.test Index: test/fts4upfrom.test ================================================================== --- /dev/null +++ test/fts4upfrom.test @@ -0,0 +1,140 @@ +# 2020 February 24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#************************************************************************* +# This file implements regression tests for SQLite library. The +# focus of this script is testing UPDATE statements with FROM clauses +# against FTS4 tables. +# +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix fts4upfrom + +# If SQLITE_ENABLE_FTS3 is defined, omit this file. +ifcapable !fts3 { + finish_test + return +} + +foreach {tn create_table} { + 0 { CREATE VIRTUAL TABLE ft USING fts5(a, b, c) } + 1 { CREATE VIRTUAL TABLE ft USING fts3(a, b, c) } + 2 { CREATE TABLE ft(a, b, c) } + 3 { + CREATE TABLE real(a, b, c); + CREATE INDEX i1 ON real(a); + CREATE VIEW ft AS SELECT rowid, a, b, c FROM real; + CREATE TRIGGER tr1 INSTEAD OF INSERT ON ft BEGIN + INSERT INTO real(rowid, a, b, c) VALUES(new.rowid, new.a, new.b, new.c); + END; + CREATE TRIGGER tr2 INSTEAD OF UPDATE ON ft BEGIN + UPDATE real SET rowid=new.rowid, a=new.a, b=new.b, c=new.c + WHERE rowid=old.rowid; + END; + } +} { + if {$tn==0} { ifcapable !fts5 { continue } } + catchsql { DROP VIEW IF EXISTS changes } + catchsql { DROP TABLE IF EXISTS ft } + catchsql { DROP VIEW IF EXISTS ft } + execsql $create_table + + do_execsql_test 1.$tn.0 { + INSERT INTO ft(a, b, c) VALUES('a', NULL, 'apple'); + INSERT INTO ft(a, b, c) VALUES('b', NULL, 'banana'); + INSERT INTO ft(a, b, c) VALUES('c', NULL, 'cherry'); + INSERT INTO ft(a, b, c) VALUES('d', NULL, 'damson plum'); + } + + do_execsql_test 1.$tn.1 { + SELECT a, b, c FROM ft ORDER BY rowid; + } { + a {} apple + b {} banana + c {} cherry + d {} {damson plum} + } + + do_execsql_test 1.$tn.2 { + UPDATE ft SET b=o.c FROM ft AS o WHERE (ft.a == char(unicode(o.a)+1)) + } + + do_execsql_test 1.$tn.3 { + SELECT a, b, c FROM ft ORDER BY rowid; + } { + a {} apple + b apple banana + c banana cherry + d cherry {damson plum} + } + + do_catchsql_test 1.$tn.4 { + UPDATE ft SET c=v FROM changes WHERE a=k; + } {1 {no such table: changes}} + + do_execsql_test 1.$tn.5 { + create view changes(k, v) AS + VALUES( 'd', 'dewberry' ) UNION ALL + VALUES( 'c', 'clementine' ) UNION ALL + VALUES( 'b', 'blueberry' ) UNION ALL + VALUES( 'a', 'apricot' ) + ; + } + + do_execsql_test 1.$tn.6 { + UPDATE ft SET c=v FROM changes WHERE a=k; + } + + do_execsql_test 1.$tn.7 { + SELECT rowid, a, b, c FROM ft ORDER BY rowid; + } { + 1 a {} apricot + 2 b apple blueberry + 3 c banana clementine + 4 d cherry dewberry + } + + do_execsql_test 1.$tn.8 " + WITH x1(o, n) AS ( + VALUES(1, 11) UNION ALL + VALUES(2, 12) UNION ALL + VALUES(3, 13) UNION ALL + VALUES(4, 14) + ) + SELECT ft.rowid, a, b, c, o, n FROM ft, x1 WHERE ft.rowid = o; + " { + 1 a {} apricot 1 11 + 2 b apple blueberry 2 12 + 3 c banana clementine 3 13 + 4 d cherry dewberry 4 14 + } + + set ROWID rowid + if {$tn==1} { set ROWID docid } + do_execsql_test 1.$tn.9 " + WITH x1(o, n) AS ( + VALUES(1, 11) UNION ALL + VALUES(2, 12) UNION ALL + VALUES(3, 13) UNION ALL + VALUES(4, 14) + ) + UPDATE ft SET $ROWID = n FROM x1 WHERE ft.rowid = o; + SELECT rowid, a, b, c FROM ft ORDER BY rowid; + " { + 11 a {} apricot + 12 b apple blueberry + 13 c banana clementine + 14 d cherry dewberry + } +} + +finish_test + Index: test/pg_common.tcl ================================================================== --- test/pg_common.tcl +++ test/pg_common.tcl @@ -15,10 +15,12 @@ set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"] sqlite3 sqlite "" proc execsql {sql} { + + set sql [string map {{WITHOUT ROWID} {}} $sql] set lSql [list] set frag "" while {[string length $sql]>0} { set i [string first ";" $sql] Index: test/tester.tcl ================================================================== --- test/tester.tcl +++ test/tester.tcl @@ -127,10 +127,11 @@ } if {[info exists ::G(perm:dbconfig)]} { set ::dbhandle [lindex $args 0] uplevel #0 $::G(perm:dbconfig) } + [lindex $args 0] cache size 3 set res } else { # This command is not opening a new database connection. Pass the # arguments through to the C implementation as the are. # ADDED test/triggerupfrom.test Index: test/triggerupfrom.test ================================================================== --- /dev/null +++ test/triggerupfrom.test @@ -0,0 +1,174 @@ +# 2020 July 14 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix triggerupfrom + +do_execsql_test 1.0 { + CREATE TABLE map(k, v); + INSERT INTO map VALUES(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'); + + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + + CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN + UPDATE t1 SET c = v FROM map WHERE k=new.a AND a=new.a; + END; +} + +do_execsql_test 1.1 { + INSERT INTO t1(a) VALUES(1); +} + +do_execsql_test 1.2 { + SELECT a, c FROM t1 ORDER BY a; +} {1 one} + +do_execsql_test 1.3 { + INSERT INTO t1(a) VALUES(2), (3), (4), (5); + SELECT a, c FROM t1 ORDER BY a; +} {1 one 2 two 3 three 4 four 5 {}} + +forcedelete test.db2 +do_execsql_test 2.0 { + ATTACH 'test.db2' AS aux; + CREATE TABLE aux.t3(x, y); + INSERT INTO aux.t3 VALUES('x', 'y'); +} + +do_catchsql_test 2.1 { + CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN + UPDATE t1 SET b = y FROM aux.t3 WHERE k=new.a; + END; +} {1 {trigger tr2 cannot reference objects in database aux}} + +do_execsql_test 2.2 { + CREATE TEMP TRIGGER tr2 AFTER INSERT ON t1 BEGIN + UPDATE t1 SET b = y FROM aux.t3 WHERE a=new.a; + END; + INSERT INTO t1(a) VALUES(10), (20); + SELECT * FROM t1; +} { + 1 {} one + 2 {} two + 3 {} three + 4 {} four + 5 {} {} + 10 y {} + 20 y {} +} + +do_execsql_test 2.3 { + CREATE TABLE link(f, t); + INSERT INTO link VALUES(5, 2), (20, 10), (2, 1); + CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN + UPDATE t1 SET b=coalesce(old.b,old.c) FROM main.link WHERE a=t AND old.a=f; + END; + DELETE FROM t1 WHERE a=2; + SELECT * FROM t1; +} { + 1 two one + 3 {} three + 4 {} four + 5 {} {} + 10 y {} + 20 y {} +} + +db close +sqlite3 db "" +do_catchsql_test 2.4 { + ATTACH 'test.db' AS yyy; + SELECT * FROM t1; +} {1 {malformed database schema (tr3) - trigger tr3 cannot reference objects in database main}} + +#------------------------------------------------------------------------- +reset_db +forcedelete test.db2 +do_execsql_test 3.0 { + CREATE TABLE mmm(x, y); + INSERT INTO mmm VALUES(1, 'one'); + INSERT INTO mmm VALUES(2, 'two'); + INSERT INTO mmm VALUES(3, 'three'); + + ATTACH 'test.db2' AS aux; + CREATE TABLE aux.t1(a, b); + CREATE TABLE aux.mmm(x, y); + INSERT INTO aux.mmm VALUES(1, 'ONE'); + INSERT INTO aux.mmm VALUES(2, 'TWO'); + INSERT INTO aux.mmm VALUES(3, 'THREE'); + + CREATE TRIGGER aux.ttt AFTER INSERT ON t1 BEGIN + UPDATE t1 SET b=y FROM mmm WHERE x=new.a AND a=new.a; + END; + + INSERT INTO t1(a) VALUES (2); + SELECT * FROM t1; +} {2 TWO} + +#------------------------------------------------------------------------- +# Test that INSTEAD OF UPDATE triggers on views work with UPDATE...FROM +# statements. Including, if the library is built with ENABLE_HIDDEN_COLUMNS, +# that they work correctly on views with hidden columns. +# +reset_db +do_execsql_test 4.0 { + CREATE TABLE t1(k, a, b); + INSERT INTO t1 VALUES('a', 1, 'one'); + INSERT INTO t1 VALUES('b', 2, 'two'); + INSERT INTO t1 VALUES('c', 3, 'three'); + INSERT INTO t1 VALUES('d', 4, 'four'); + + CREATE TABLE log(x); + CREATE VIEW v1 AS SELECT k, a, b AS __hidden__b FROM t1; + CREATE TRIGGER tr1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES( + '('||old.a||','||old.__hidden__b||')->('||new.a||','||new.__hidden__b||')' + ); + END; +} + +ifcapable hiddencolumns { + do_execsql_test 4.1-hc-enabled { + SELECT * FROM v1 + } {a 1 b 2 c 3 d 4} +} else { + do_execsql_test 4.1-hc-disabled { + SELECT * FROM v1 + } {a 1 one b 2 two c 3 three d 4 four} +} + +do_execsql_test 4.2 { + UPDATE v1 SET a='xyz' WHERE k IN ('a', 'c'); + SELECT * FROM log; + DELETE FROM log; +} { + (1,one)->(xyz,one) + (3,three)->(xyz,three) +} + +do_execsql_test 4.3 { + CREATE TABLE map(k, v); + INSERT INTO map VALUES('b', 'twelve'); + INSERT INTO map VALUES('d', 'fourteen'); + UPDATE v1 SET a=map.v FROM map WHERE v1.k=map.k; + SELECT * FROM log; + DELETE FROM log; +} { + (2,two)->(twelve,two) + (4,four)->(fourteen,four) +} + + + +finish_test + ADDED test/upfrom1.tcl Index: test/upfrom1.tcl ================================================================== --- /dev/null +++ test/upfrom1.tcl @@ -0,0 +1,115 @@ +# 2020 April 22 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname $argv0] pg_common.tcl] + +#========================================================================= + +start_test upfrom1 "2020 April 22" + +foreach {tn wo} { + 1 "WITHOUT ROWID" + 2 "" +} { +eval [string map [list %TN% $tn %WITHOUT_ROWID% $wo] { +execsql_test 1.%TN%.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) %WITHOUT_ROWID%; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} + +execsql_test 1.%TN%.1 { + SELECT * FROM t2; +} + +execsql_test 1.%TN%.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} + +execsql_test 1.%TN%.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} + +execsql_test 1.%TN%.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} + +execsql_test 1.%TN%.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) %WITHOUT_ROWID%; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} + +execsql_test 1.%TN%.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} +}]} + +execsql_test 2.1 { + DROP TABLE IF EXISTS t5; + DROP TABLE IF EXISTS m1; + DROP TABLE IF EXISTS m2; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); + CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); + + INSERT INTO t5 VALUES(1, 'one', 'ONE'); + INSERT INTO t5 VALUES(2, 'two', 'TWO'); + INSERT INTO t5 VALUES(3, 'three', 'THREE'); + INSERT INTO t5 VALUES(4, 'four', 'FOUR'); + + INSERT INTO m1 VALUES(1, 'i'); + INSERT INTO m1 VALUES(2, 'ii'); + INSERT INTO m1 VALUES(3, 'iii'); + + INSERT INTO m2 VALUES(1, 'I'); + INSERT INTO m2 VALUES(3, 'II'); + INSERT INTO m2 VALUES(4, 'III'); +} + +execsql_test 2.2 { + UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; +} + +errorsql_test 2.3.1 { + UPDATE t5 SET b=1 FROM t5; +} +errorsql_test 2.3.2 { + UPDATE t5 AS apples SET b=1 FROM t5 AS apples; +} + + +finish_test + ADDED test/upfrom1.test Index: test/upfrom1.test ================================================================== --- /dev/null +++ test/upfrom1.test @@ -0,0 +1,167 @@ +# 2020 April 22 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom1 + +do_execsql_test 1.1.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) WITHOUT ROWID; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} {} + +do_execsql_test 1.1.1 { + SELECT * FROM t2; +} {1 2 3 4 5 6 7 8 9} + +do_execsql_test 1.1.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.1.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} {} + +do_execsql_test 1.1.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.1.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) WITHOUT ROWID; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} {1 1 one 2 2 two 3 3 three} + +do_execsql_test 1.1.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} {1 1 five 2 2 five 3 3 five} + +do_execsql_test 1.2.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) ; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} {} + +do_execsql_test 1.2.1 { + SELECT * FROM t2; +} {1 2 3 4 5 6 7 8 9} + +do_execsql_test 1.2.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.2.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} {} + +do_execsql_test 1.2.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.2.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) ; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} {1 1 one 2 2 two 3 3 three} + +do_execsql_test 1.2.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} {1 1 five 2 2 five 3 3 five} + +do_execsql_test 2.1 { + DROP TABLE IF EXISTS t5; + DROP TABLE IF EXISTS m1; + DROP TABLE IF EXISTS m2; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); + CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); + + INSERT INTO t5 VALUES(1, 'one', 'ONE'); + INSERT INTO t5 VALUES(2, 'two', 'TWO'); + INSERT INTO t5 VALUES(3, 'three', 'THREE'); + INSERT INTO t5 VALUES(4, 'four', 'FOUR'); + + INSERT INTO m1 VALUES(1, 'i'); + INSERT INTO m1 VALUES(2, 'ii'); + INSERT INTO m1 VALUES(3, 'iii'); + + INSERT INTO m2 VALUES(1, 'I'); + INSERT INTO m2 VALUES(3, 'II'); + INSERT INTO m2 VALUES(4, 'III'); +} {} + +do_execsql_test 2.2 { + UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; +} {1 i I 2 ii {} 3 iii II 4 four FOUR} + +# PG says ERROR: table name "t5" specified more than once +do_test 2.3.1 { catch { execsql { + UPDATE t5 SET b=1 FROM t5; +} } } 1 + +# PG says ERROR: table name "apples" specified more than once +do_test 2.3.2 { catch { execsql { + UPDATE t5 AS apples SET b=1 FROM t5 AS apples; +} } } 1 + +finish_test ADDED test/upfrom2.test Index: test/upfrom2.test ================================================================== --- /dev/null +++ test/upfrom2.test @@ -0,0 +1,371 @@ +# 2020 April 29 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom2 + +# Test cases: +# +# 1.*: Test that triggers are fired correctly for UPDATE FROM statements, +# and only once for each row. Except for INSTEAD OF triggers on +# views - these are fired once for each row returned by the join, +# including duplicates. +# +# 2.*: Test adding ORDER BY and LIMIT clauses with UPDATE FROM statements. +# +# 5.*: Test that specifying the target table name or alias in the FROM +# clause of an UPDATE statement is an error. +# + +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + + eval [string map [list %WO% $wo %TN% $tn] { + do_execsql_test 1.%TN%.0 { + CREATE TABLE log(t TEXT); + CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) %WO%; + CREATE INDEX t1y ON t1(y); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); + + CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.y || '->' || new.y); + END; + } + + do_execsql_test 1.%TN%.1 { + WITH data(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE t1 SET z=v FROM data WHERE x=k; + + SELECT * FROM t1; + SELECT * FROM log; + } { + 1 i ten 2 ii two 3 iii thirty 4 iv four + one->ten i->i + three->thirty iii->iii + } + + do_execsql_test 1.%TN%.2 { + CREATE TABLE t2(a, b); + CREATE TABLE t3(k, v); + + INSERT INTO t3 VALUES(5, 'v'); + INSERT INTO t3 VALUES(12, 'xii'); + + INSERT INTO t2 VALUES(2, 12); + INSERT INTO t2 VALUES(3, 5); + + DELETE FROM log; + UPDATE t1 SET y=v FROM t2, t3 WHERE t1.x=t2.a AND t3.k=t2.b; + + SELECT * FROM t1; + SELECT * FROM log; + } { + 1 i ten 2 xii two 3 v thirty 4 iv four + two->two ii->xii + thirty->thirty iii->v + } + + do_execsql_test 1.%TN%.3 { + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve') + ) + UPDATE t1 SET z=v FROM data WHERE x=k; + + SELECT * FROM t1; + SELECT * FROM log; + } { + 1 i eight 2 xii twelve 3 v thirty 4 iv four + ten->eight i->i + two->twelve xii->xii + } + + do_test 1.%TN%.4 { db changes } {2} + + do_execsql_test 1.%TN%.5 { + CREATE VIEW v1 AS SELECT * FROM t1; + CREATE TRIGGER v1tr INSTEAD OF UPDATE ON v1 BEGIN + UPDATE t1 SET y=new.y, z=new.z WHERE x=new.x; + END; + + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen') + ) + UPDATE v1 SET z=v FROM data WHERE x=k; + } + + do_execsql_test 1.%TN%.6 { + SELECT * FROM v1; + SELECT * FROM log; + } { + 1 i eight 2 xii twelve 3 v fourteen 4 iv sixteen + thirty->thirteen v->v + thirteen->fourteen v->v + four->fifteen iv->iv + fifteen->sixteen iv->iv + } + + #-------------------------------------------------------------- + + do_execsql_test 1.%TN%.7 { + CREATE TABLE o1(w, x, y, z UNIQUE, PRIMARY KEY(w, x)) %WO%; + CREATE INDEX o1y ON t1(y); + + INSERT INTO o1 VALUES(0, 0, 'i', 'one'); + INSERT INTO o1 VALUES(0, 1, 'ii', 'two'); + INSERT INTO o1 VALUES(1, 0, 'iii', 'three'); + INSERT INTO o1 VALUES(1, 1, 'iv', 'four'); + + CREATE TRIGGER tro1 BEFORE UPDATE ON o1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + CREATE TRIGGER tro2 AFTER UPDATE ON o1 BEGIN + INSERT INTO log VALUES(old.y || '->' || new.y); + END; + } + + do_execsql_test 1.%TN%.8 { + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE o1 SET z=v FROM data WHERE (1+x+w*2)=k; + + SELECT * FROM o1; + SELECT * FROM log; + } { + 0 0 i ten 0 1 ii two 1 0 iii thirty 1 1 iv four + one->ten i->i + three->thirty iii->iii + } + + do_execsql_test 1.%TN%.9 { + DELETE FROM log; + UPDATE o1 SET y=v FROM t2, t3 WHERE (1+o1.w*2+o1.x)=t2.a AND t3.k=t2.b; + + SELECT * FROM o1; + SELECT * FROM log; + } { + 0 0 i ten 0 1 xii two 1 0 v thirty 1 1 iv four + two->two ii->xii + thirty->thirty iii->v + } + + do_execsql_test 1.%TN%.10 { + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve') + ) + UPDATE o1 SET z=v FROM data WHERE (1+w*2+x)=k; + + SELECT * FROM o1; + SELECT * FROM log; + } { + 0 0 i eight 0 1 xii twelve 1 0 v thirty 1 1 iv four + ten->eight i->i + two->twelve xii->xii + } + + do_test 1.%TN%.11 { db changes } {2} + + do_execsql_test 1.%TN%.12 { + CREATE VIEW w1 AS SELECT * FROM o1; + CREATE TRIGGER w1tr INSTEAD OF UPDATE ON w1 BEGIN + UPDATE o1 SET y=new.y, z=new.z WHERE w=new.w AND x=new.x; + END; + + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen') + ) + UPDATE w1 SET z=v FROM data WHERE (1+w*2+x)=k; + } + + do_execsql_test 1.%TN%.13 { + SELECT * FROM w1; + SELECT * FROM log; + } { + 0 0 i eight 0 1 xii twelve 1 0 v fourteen 1 1 iv sixteen + thirty->thirteen v->v + thirteen->fourteen v->v + four->fifteen iv->iv + fifteen->sixteen iv->iv + } + +}] +} + +ifcapable update_delete_limit { +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + +eval [string map [list %WO% $wo %TN% $tn] { + do_execsql_test 2.%TN%.1 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b) %WO%; + INSERT INTO x1 VALUES + (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), + (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'); + } + + do_execsql_test 2.%TN%.2 { + CREATE TABLE data1(x, y); + INSERT INTO data1 VALUES + (1, 'eleven'), (1, 'twenty-one'), (2, 'twelve'), (2, 'twenty-two'), + (3, 'thirteen'), (3, 'twenty-three'), (4, 'fourteen'), (4, 'twenty-four'); + } + + do_execsql_test 2.%TN%.3 { + UPDATE x1 SET b=y FROM data1 WHERE a=x ORDER BY a LIMIT 3; + SELECT * FROM x1; + } { + 1 eleven 2 twelve 3 thirteen 4 four 5 five 6 six 7 seven 8 eight + } + + do_execsql_test 2.%TN%.4 { + UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b LIMIT 3; + SELECT * FROM x1; + } { + 1 eleveneleven 2 twelve 3 thirteenthirteen 4 fourfourteen + 5 five 6 six 7 seven 8 eight + } + + do_catchsql_test 2.%TN%.5 { + UPDATE x1 SET b=b||b ORDER BY b; + } {1 {ORDER BY without LIMIT on UPDATE}} + do_catchsql_test 2.%TN%.6 { + UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b; + } {1 {ORDER BY without LIMIT on UPDATE}} + + #----------------------------------------------------------------------- + + do_execsql_test 2.%TN%.6 { + DROP TABLE x1; + CREATE TABLE x1(u, v, b, PRIMARY KEY(u, v)) %WO%; + INSERT INTO x1 VALUES + (0, 1, 'one'), (1, 0, 'two'), (1, 1, 'three'), (2, 0, 'four'), + (2, 1, 'five'), (3, 0, 'six'), (3, 1, 'seven'), (4, 0, 'eight'); + } + + do_execsql_test 2.%TN%.7 { + UPDATE x1 SET b=y FROM data1 WHERE (u*2+v)=x ORDER BY u, v LIMIT 3; + SELECT * FROM x1; + } { + 0 1 eleven 1 0 twelve 1 1 thirteen 2 0 four + 2 1 five 3 0 six 3 1 seven 4 0 eight + } + + do_execsql_test 2.%TN%.8 { + UPDATE x1 SET b=b||y FROM data1 WHERE (u*2+v)=x ORDER BY b LIMIT 3; + SELECT * FROM x1; + } { + 0 1 eleveneleven 1 0 twelve 1 1 thirteenthirteen 2 0 fourfourteen + 2 1 five 3 0 six 3 1 seven 4 0 eight + } + + +}] +}} + +reset_db +do_execsql_test 3.0 { + CREATE TABLE data(x, y, z); + CREATE VIEW t1 AS SELECT * FROM data; + CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN + INSERT INTO data VALUES(new.x, new.y, new.z); + END; + CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + + CREATE TABLE log(t TEXT); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); +} + +do_execsql_test 3.1 { + WITH input(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE t1 SET z=v FROM input WHERE x=k; +} + +foreach {tn sql} { + 2 { + CREATE TABLE x1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; + } + 1 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c); + } + 3 { + CREATE TABLE x1(a INT PRIMARY KEY, b, c); + } +} { + + reset_db + execsql $sql + + do_execsql_test 4.$tn.0 { + INSERT INTO x1 VALUES(1, 1, 1); + INSERT INTO x1 VALUES(2, 2, 2); + INSERT INTO x1 VALUES(3, 3, 3); + INSERT INTO x1 VALUES(4, 4, 4); + INSERT INTO x1 VALUES(5, 5, 5); + CREATE TABLE map(o, t); + INSERT INTO map VALUES(3, 30), (4, 40), (1, 10); + } + + do_execsql_test 4.$tn.1 { + UPDATE x1 SET a=t FROM map WHERE a=o; + SELECT * FROM x1 ORDER BY a; + } {2 2 2 5 5 5 10 1 1 30 3 3 40 4 4} +} + +reset_db +do_execsql_test 5.0 { + CREATE TABLE x1(a, b, c); + CREATE TABLE x2(a, b, c); +} + +foreach {tn update nm} { + 1 "UPDATE x1 SET a=5 FROM x1" x1 + 2 "UPDATE x1 AS grapes SET a=5 FROM x1 AS grapes" grapes + 3 "UPDATE x1 SET a=5 FROM x2, x1" x1 + 4 "UPDATE x1 AS grapes SET a=5 FROM x2, x1 AS grapes" grapes +} { + do_catchsql_test 5.$tn $update \ + "1 {target object/alias may not appear in FROM clause: $nm}" +} + + +finish_test + + ADDED test/upfrom3.test Index: test/upfrom3.test ================================================================== --- /dev/null +++ test/upfrom3.test @@ -0,0 +1,262 @@ +# 2020 July 14 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom3 + +# Test plan: +# +# 1.*: Test UPDATE ... FROM statements that modify IPK fields. And that +# modify "INTEGER PRIMARY KEY" fields on WITHOUT ROWID tables. +# +# 2.*: Test UPDATE ... FROM statements that modify PK fields of WITHOUT +# ROWID tables. +# +# 3.*: Test that UPDATE ... FROM statements are not confused if there +# are multiple tables of the same name in attached databases. +# +# 4.*: Tests for UPDATE ... FROM statements and foreign keys. +# + +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + eval [string map [list %WO% $wo %TN% $tn] { + + do_execsql_test 1.%TN%.0 { + CREATE TABLE log(t TEXT); + CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z UNIQUE) %WO%; + CREATE INDEX t1y ON t1(y); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); + } + + do_execsql_test 1.%TN%.1 { + CREATE TABLE x1(o, n); + INSERT INTO x1 VALUES(1, 11); + INSERT INTO x1 VALUES(2, 12); + INSERT INTO x1 VALUES(3, 13); + INSERT INTO x1 VALUES(4, 14); + UPDATE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 11 i one + 12 ii two + 13 iii three + 14 iv four + } + + do_test 1.%TN%.2 { db changes } 4 + + do_execsql_test 1.%TN%.3 { + INSERT INTO x1 VALUES(11, 21); + INSERT INTO x1 VALUES(12, 22); + INSERT INTO x1 VALUES(13, 23); + INSERT INTO x1 VALUES(14, 24); + + INSERT INTO x1 VALUES(21, 31); + INSERT INTO x1 VALUES(22, 32); + INSERT INTO x1 VALUES(23, 33); + INSERT INTO x1 VALUES(24, 34); + UPDATE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 21 i one + 22 ii two + 23 iii three + 24 iv four + } + + do_execsql_test 1.%TN%.4 { + UPDATE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 31 i one + 32 ii two + 33 iii three + 34 iv four + } + + do_execsql_test 1.%TN%.5 { + INSERT INTO x1 VALUES(31, 32); + INSERT INTO x1 VALUES(33, 34); + UPDATE OR REPLACE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 32 i one + 34 iii three + } + + do_execsql_test 1.%TN%.6 { + INSERT INTO t1 VALUES(33, 'ii', 'two'); + INSERT INTO t1 VALUES(35, 'iv', 'four'); + } + + do_execsql_test 1.%TN%.7 { + CREATE TABLE x2(o, n, zz); + INSERT INTO x2 VALUES(32, 41, 'four'); + INSERT INTO x2 VALUES(33, 42, 'three'); + UPDATE OR IGNORE t1 SET x=n, z=zz FROM x2 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 32 i one + 33 ii two + 34 iii three + 35 iv four + } + + do_execsql_test 1.%TN%.8 { + UPDATE OR REPLACE t1 SET x=n, z=zz FROM x2 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 41 i four + 42 ii three + } + + }] +} + +do_execsql_test 2.1.1 { + CREATE TABLE u1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; + INSERT INTO u1 VALUES(0, 0, 0); + INSERT INTO u1 VALUES(1, 0, 1); + INSERT INTO u1 VALUES(2, 1, 0); + INSERT INTO u1 VALUES(3, 1, 1); +} + +do_execsql_test 2.1.2 { + CREATE TABLE map(f, t); + INSERT INTO map VALUES(0, 10); + INSERT INTO map VALUES(1, 11); + UPDATE u1 SET c=t FROM map WHERE c=f; + SELECT * FROM u1 ORDER BY a; +} { + 0 0 10 + 1 0 11 + 2 1 10 + 3 1 11 +} + +do_execsql_test 2.1.3 { + UPDATE u1 SET b=t FROM map WHERE b=f; + SELECT * FROM u1 ORDER BY a; +} { + 0 10 10 + 1 10 11 + 2 11 10 + 3 11 11 +} + +do_execsql_test 2.1.4 { + CREATE TABLE map2(o1, o2, n1, n2); + INSERT INTO map2 VALUES + (10, 10, 50, 50), (10, 11, 50, 60), + (11, 10, 60, 50), (11, 11, 60, 60); + UPDATE u1 SET b=n1, c=n2 FROM map2 WHERE b=o1 AND c=o2; + SELECT * FROM u1 ORDER BY a; +} { + 0 50 50 + 1 50 60 + 2 60 50 + 3 60 60 +} + +#------------------------------------------------------------------------- +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + forcedelete test.db2 + eval [string map [list %WO% $wo %TN% $tn] { + do_execsql_test 3.$tn.1 { + CREATE TABLE g1(a, b, c, PRIMARY KEY(a, b)) %WO%; + INSERT INTO g1 VALUES(1, 1, 1); + + ATTACH 'test.db2' AS aux; + CREATE TABLE aux.g1(a, b, c, PRIMARY KEY(a, b)) %WO%; + INSERT INTO aux.g1 VALUES(10, 1, 10); + INSERT INTO aux.g1 VALUES(20, 2, 20); + INSERT INTO aux.g1 VALUES(30, 3, 30); + } + + do_execsql_test 3.$tn.2 { + UPDATE aux.g1 SET c=101 FROM main.g1; + } + do_execsql_test 3.$tn.3 { + SELECT * FROM aux.g1; + } {10 1 101 20 2 101 30 3 101} + + do_execsql_test 3.$tn.4 { + UPDATE g1 SET c=101 FROM g1 AS g2; + } + do_execsql_test 3.$tn.5 { + SELECT * FROM g1; + } {1 1 101} + }] +} + +#------------------------------------------------------------------------- +reset_db +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + forcedelete test.db2 + eval [string map [list %WO% $wo %TN% $tn] { + + do_execsql_test 4.$tn.1 { + CREATE TABLE p1(a INTEGER PRIMARY KEY, b) %WO%; + CREATE TABLE c1(x PRIMARY KEY, y REFERENCES p1 ON UPDATE CASCADE) %WO%; + PRAGMA foreign_keys = 1; + + INSERT INTO p1 VALUES(1, 'one'); + INSERT INTO p1 VALUES(11, 'eleven'); + INSERT INTO p1 VALUES(111, 'eleventyone'); + + INSERT INTO c1 VALUES('a', 1); + INSERT INTO c1 VALUES('b', 11); + INSERT INTO c1 VALUES('c', 111); + } + + do_execsql_test 4.$tn.2 { + CREATE TABLE map(f, t); + INSERT INTO map VALUES('a', 111); + INSERT INTO map VALUES('c', 112); + } + + do_catchsql_test 4.$tn.3 { + UPDATE c1 SET y=t FROM map WHERE x=f; + } {1 {FOREIGN KEY constraint failed}} + + do_execsql_test 4.$tn.4 { + INSERT INTO map VALUES('eleven', 12); + INSERT INTO map VALUES('eleventyone', 112); + UPDATE p1 SET a=t FROM map WHERE b=f; + } + + do_execsql_test 4.$tn.5 { + SELECT * FROM c1 + } {a 1 b 12 c 112} + + }] +} + +finish_test + ADDED test/upfromfault.test Index: test/upfromfault.test ================================================================== --- /dev/null +++ test/upfromfault.test @@ -0,0 +1,140 @@ +# 2020 April 29 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfromfault + +foreach {tn sql} { + 1 { + CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE); + CREATE INDEX t1y ON t1(y); + } + 2 { + CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) WITHOUT ROWID; + CREATE INDEX t1y ON t1(y); + } + 3 { + CREATE TABLE t1(x, y, z UNIQUE, PRIMARY KEY(x,y)) WITHOUT ROWID; + } + 4 { + CREATE VIRTUAL TABLE t1 USING fts5(x, y, z); + } + 5 { + CREATE TABLE real(x, y, z); + CREATE VIEW t1 AS SELECT * FROM real; + CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN + INSERT INTO real VALUES(new.x, new.y, new.z); + END; + CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + UPDATE real SET y=new.y, z=new.z WHERE x=old.x; + END; + } +} { +if {$tn<5} continue + reset_db + + ifcapable !fts5 { if {$tn==4} continue } + + execsql $sql + do_execsql_test 1.$tn.0 { + CREATE TABLE log(t TEXT); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); + } + if {$tn!=4 && $tn!=5} { + do_execsql_test 1.$tn.0b { + CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.y || '->' || new.y); + END; + } + } + + faultsim_save_and_close + + do_faultsim_test 1.$tn -prep { + faultsim_restore_and_reopen + execsql { SELECT * FROM t1 } + } -body { + execsql { + WITH data(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE t1 SET z=v FROM data WHERE x=k; + } + } -test { + faultsim_test_result {0 {}} {1 {vtable constructor failed: t1}} + if {$testrc==0} { + set res [execsql { SELECT * FROM t1 }] + if {$res!="1 i ten 2 ii two 3 iii thirty 4 iv four"} { + error "unexpected result: $res" + } + } + } +} + +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(x, y, z); +} +faultsim_save_and_close +do_faultsim_test 2.1 -prep { + faultsim_restore_and_reopen +} -body { + execsql { + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + UPDATE t2 SET x=a FROM t1 WHERE c=z; + END; + } +} -test { + faultsim_test_result {0 {}} +} + +faultsim_restore_and_reopen +do_execsql_test 2.2 { + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + UPDATE t1 SET a=x FROM t2 WHERE c=z; + END; + + INSERT INTO t2 VALUES(1, 1, 1); + INSERT INTO t2 VALUES(2, 2, 2); + INSERT INTO t2 VALUES(3, 3, 3); +} +faultsim_save_and_close + +do_faultsim_test 2.3 -prep { + faultsim_restore_and_reopen +} -body { + execsql { + INSERT INTO t1 VALUES(NULL, NULL, 1), (NULL, NULL, 3); + } +} -test { + faultsim_test_result {0 {}} + if {$testrc==0} { + set res [execsql { SELECT * FROM t1 }] + if {$res!="1 {} 1 3 {} 3"} { + error "unexpected result: $res" + } + } +} + + +finish_test +