From c3f047b2d70384860cee19c2682bc955ae8ba54e Mon Sep 17 00:00:00 2001 From: "D. Richard Hipp" Date: Mon, 16 Apr 2018 13:00:50 +0000 Subject: [PATCH] Add support for the "excluded.*" names in the UPDATE clause of an upsert. --- src/insert.c | 1 + src/parse.y | 4 +-- src/resolve.c | 83 ++++++++++++++++++++++++++++++++++++--------------------- src/sqliteInt.h | 6 ++++- src/trigger.c | 2 +- src/update.c | 5 +++- src/upsert.c | 2 +- 7 files changed, 67 insertions(+), 36 deletions(-) diff --git a/src/insert.c b/src/insert.c index d98f127c52..4f9e43b9e3 100644 --- a/src/insert.c +++ b/src/insert.c @@ -808,6 +808,7 @@ void sqlite3Insert( if( pUpsert ){ pTabList->a[0].iCursor = iDataCur; pUpsert->pUpsertSrc = pTabList; + pUpsert->regData = regData; if( pUpsert->pUpsertTarget ){ sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert); } diff --git a/src/parse.y b/src/parse.y index e7a48d0207..3e9b4d4cca 100644 --- a/src/parse.y +++ b/src/parse.y @@ -826,7 +826,7 @@ cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); - sqlite3Update(pParse,X,Y,W,R,O,L); + sqlite3Update(pParse,X,Y,W,R,O,L,0); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT @@ -834,7 +834,7 @@ cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); - sqlite3Update(pParse,X,Y,W,R,0,0); + sqlite3Update(pParse,X,Y,W,R,0,0,0); } %endif diff --git a/src/resolve.c b/src/resolve.c index aa60b0478c..4bf80b4fbe 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -191,7 +191,7 @@ static int lookupName( struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ - int isTrigger = 0; /* True if resolved to a trigger column */ + int eNewExprOp = TK_COLUMN; /* New value for pExpr->op on success */ Table *pTab = 0; /* Table hold the row */ Column *pCol; /* A column of pTab */ @@ -296,22 +296,35 @@ static int lookupName( } } /* if( pSrcList ) */ -#ifndef SQLITE_OMIT_TRIGGER +#if !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) /* If we have not already resolved the name, then maybe - ** it is a new.* or old.* trigger argument reference + ** it is a new.* or old.* trigger argument reference. Or + ** maybe it is an excluded.* from an upsert. */ - if( zDb==0 && zTab!=0 && cntTab==0 && pParse->pTriggerTab!=0 ){ - int op = pParse->eTriggerOp; - assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT ); - if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){ - pExpr->iTable = 1; - pTab = pParse->pTriggerTab; - }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){ - pExpr->iTable = 0; - pTab = pParse->pTriggerTab; - }else{ - pTab = 0; + if( zDb==0 && zTab!=0 && cntTab==0 ){ + pTab = 0; +#ifndef SQLITE_OMIT_TRIGGER + if( pParse->pTriggerTab!=0 ){ + int op = pParse->eTriggerOp; + assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT ); + if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){ + pExpr->iTable = 1; + pTab = pParse->pTriggerTab; + }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){ + pExpr->iTable = 0; + pTab = pParse->pTriggerTab; + } + } +#endif /* SQLITE_OMIT_TRIGGER */ +#ifndef SQLITE_OMIT_UPSERT + if( (pNC->ncFlags & NC_UUpsert)!=0 ){ + Upsert *pUpsert = pNC->uNC.pUpsert; + if( pUpsert && sqlite3StrICmp("excluded",zTab)==0 ){ + pTab = pUpsert->pUpsertSrc->a[0].pTab; + pExpr->iTable = 2; + } } +#endif /* SQLITE_OMIT_UPSERT */ if( pTab ){ int iCol; @@ -331,24 +344,34 @@ static int lookupName( } if( iColnCol ){ cnt++; - if( iCol<0 ){ - pExpr->affinity = SQLITE_AFF_INTEGER; - }else if( pExpr->iTable==0 ){ - testcase( iCol==31 ); - testcase( iCol==32 ); - pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<iTable==2 ){ + pExpr->iTable = pNC->uNC.pUpsert->regData + iCol; + eNewExprOp = TK_REGISTER; + }else +#endif + { +#ifndef SQLITE_OMIT_TRIGGER + if( iCol<0 ){ + pExpr->affinity = SQLITE_AFF_INTEGER; + }else if( pExpr->iTable==0 ){ + testcase( iCol==31 ); + testcase( iCol==32 ); + pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<pTab = pTab; + pExpr->iColumn = (i16)iCol; + eNewExprOp = TK_TRIGGER; +#endif /* SQLITE_OMIT_TRIGGER } - pExpr->iColumn = (i16)iCol; - pExpr->pTab = pTab; - isTrigger = 1; } } } -#endif /* !defined(SQLITE_OMIT_TRIGGER) */ +#endif /* !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) */ /* ** Perhaps the name is a reference to the ROWID @@ -485,7 +508,7 @@ static int lookupName( pExpr->pLeft = 0; sqlite3ExprDelete(db, pExpr->pRight); pExpr->pRight = 0; - pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN); + pExpr->op = eNewExprOp; ExprSetProperty(pExpr, EP_Leaf); lookupname_end: if( cnt==1 ){ @@ -1301,7 +1324,7 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ ** Minor point: If this is the case, then the expression will be ** re-evaluated for each reference to it. */ - assert( (sNC.ncFlags & (NC_UAggInfo))==0 ); + assert( (sNC.ncFlags & (NC_UAggInfo|NC_UUpsert))==0 ); sNC.uNC.pEList = p->pEList; sNC.ncFlags |= NC_UEList; if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort; diff --git a/src/sqliteInt.h b/src/sqliteInt.h index cd65ffb39f..1a5fc5e5a1 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -2685,6 +2685,7 @@ struct NameContext { union { ExprList *pEList; /* Optional list of result-set columns */ AggInfo *pAggInfo; /* Information about aggregates at this level */ + Upsert *pUpsert; /* ON CONFLICT clause information from an upsert */ } uNC; NameContext *pNext; /* Next outer name context. NULL for outermost */ int nRef; /* Number of names resolved by this context */ @@ -2709,6 +2710,7 @@ struct NameContext { #define NC_VarSelect 0x0040 /* A correlated subquery has been seen */ #define NC_UEList 0x0080 /* True if uNC.pEList is used */ #define NC_UAggInfo 0x0100 /* True if uNC.pAggInfo is used */ +#define NC_UUpsert 0x0200 /* True if uNC.pUpsert is used */ #define NC_MinMaxAgg 0x1000 /* min/max aggregates seen. See note above */ #define NC_Complex 0x2000 /* True if a function or subquery seen */ @@ -2733,6 +2735,7 @@ struct Upsert { ExprList *pUpsertSet; /* The SET clause from an ON CONFLICT UPDATE */ Expr *pUpsertWhere; /* WHERE clause for the ON CONFLICT UPDATE */ SrcList *pUpsertSrc; /* Table to be updated */ + int regData; /* First register holding array of VALUES */ }; /* @@ -3798,7 +3801,8 @@ void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,char*); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*); -void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*); +void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*, + Upsert*); WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int); void sqlite3WhereEnd(WhereInfo*); LogEst sqlite3WhereOutputRowCount(WhereInfo*); diff --git a/src/trigger.c b/src/trigger.c index bd5711f077..5e49b79076 100644 --- a/src/trigger.c +++ b/src/trigger.c @@ -750,7 +750,7 @@ static int codeTriggerProgram( targetSrcList(pParse, pStep), sqlite3ExprListDup(db, pStep->pExprList, 0), sqlite3ExprDup(db, pStep->pWhere, 0), - pParse->eOrconf, 0, 0 + pParse->eOrconf, 0, 0, 0 ); break; } diff --git a/src/update.c b/src/update.c index 6b3d5f4945..4da63a98ec 100644 --- a/src/update.c +++ b/src/update.c @@ -93,7 +93,8 @@ void sqlite3Update( Expr *pWhere, /* The WHERE clause. May be null */ int onError, /* How to handle constraint errors */ ExprList *pOrderBy, /* ORDER BY clause. May be null */ - Expr *pLimit /* LIMIT clause. May be null */ + Expr *pLimit, /* LIMIT clause. May be null */ + Upsert *pUpsert /* ON CONFLICT clause, or null */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ @@ -226,6 +227,8 @@ void sqlite3Update( memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; + sNC.uNC.pUpsert = pUpsert; + sNC.ncFlags = NC_UUpsert; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index diff --git a/src/upsert.c b/src/upsert.c index a92824f8fb..053bc924a5 100644 --- a/src/upsert.c +++ b/src/upsert.c @@ -251,7 +251,7 @@ void sqlite3UpsertDoUpdate( pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0); sqlite3Update(pParse, pSrc, sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0), - pWhere, OE_Abort, 0, 0); + pWhere, OE_Abort, 0, 0, pUpsert); VdbeNoopComment((v, "End DO UPDATE of UPSERT")); } -- 2.11.4.GIT