Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -185,11 +185,11 @@ notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \ pager.lo parse.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \ random.lo resolve.lo rowset.lo rtree.lo \ sqlite3session.lo select.lo sqlite3rbu.lo status.lo stmt.lo \ table.lo threads.lo tokenize.lo treeview.lo trigger.lo \ - update.lo util.lo vacuum.lo \ + update.lo upsert.lo util.lo vacuum.lo \ vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \ vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo whereexpr.lo \ utf.lo vtab.lo # Object files for the amalgamation. @@ -282,10 +282,11 @@ $(TOP)/src/tokenize.c \ $(TOP)/src/treeview.c \ $(TOP)/src/trigger.c \ $(TOP)/src/utf.c \ $(TOP)/src/update.c \ + $(TOP)/src/upsert.c \ $(TOP)/src/util.c \ $(TOP)/src/vacuum.c \ $(TOP)/src/vdbe.c \ $(TOP)/src/vdbe.h \ $(TOP)/src/vdbeapi.c \ @@ -912,10 +913,13 @@ $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/trigger.c update.lo: $(TOP)/src/update.c $(HDR) $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/update.c +upsert.lo: $(TOP)/src/upsert.c $(HDR) + $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/upsert.c + utf.lo: $(TOP)/src/utf.c $(HDR) $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/utf.c util.lo: $(TOP)/src/util.c $(HDR) $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/util.c Index: Makefile.msc ================================================================== --- Makefile.msc +++ Makefile.msc @@ -1191,11 +1191,11 @@ notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \ pager.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \ random.lo resolve.lo rowset.lo rtree.lo \ sqlite3session.lo select.lo sqlite3rbu.lo status.lo \ table.lo threads.lo tokenize.lo treeview.lo trigger.lo \ - update.lo util.lo vacuum.lo \ + update.lo upsert.lo util.lo vacuum.lo \ vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \ vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo whereexpr.lo \ utf.lo vtab.lo # <> @@ -1290,10 +1290,11 @@ $(TOP)\src\tokenize.c \ $(TOP)\src\treeview.c \ $(TOP)\src\trigger.c \ $(TOP)\src\utf.c \ $(TOP)\src\update.c \ + $(TOP)\src\upsert.c \ $(TOP)\src\util.c \ $(TOP)\src\vacuum.c \ $(TOP)\src\vdbe.c \ $(TOP)\src\vdbeapi.c \ $(TOP)\src\vdbeaux.c \ @@ -1991,10 +1992,13 @@ $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\trigger.c update.lo: $(TOP)\src\update.c $(HDR) $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\update.c +upsert.lo: $(TOP)\src\upsert.c $(HDR) + $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\upsert.c + utf.lo: $(TOP)\src\utf.c $(HDR) $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\utf.c util.lo: $(TOP)\src\util.c $(HDR) $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\util.c Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -70,11 +70,11 @@ notify.o opcodes.o os.o os_unix.o os_win.o \ pager.o pcache.o pcache1.o pragma.o prepare.o printf.o \ random.o resolve.o rowset.o rtree.o \ select.o sqlite3rbu.o status.o stmt.o \ table.o threads.o tokenize.o treeview.o trigger.o \ - update.o userauth.o util.o vacuum.o \ + update.o upsert.o userauth.o util.o vacuum.o \ vdbeapi.o vdbeaux.o vdbeblob.o vdbemem.o vdbesort.o \ vdbetrace.o wal.o walker.o where.o wherecode.o whereexpr.o \ utf.o vtab.o LIBOBJ += sqlite3session.o @@ -160,10 +160,11 @@ $(TOP)/src/tokenize.c \ $(TOP)/src/treeview.c \ $(TOP)/src/trigger.c \ $(TOP)/src/utf.c \ $(TOP)/src/update.c \ + $(TOP)/src/upsert.c \ $(TOP)/src/util.c \ $(TOP)/src/vacuum.c \ $(TOP)/src/vdbe.c \ $(TOP)/src/vdbe.h \ $(TOP)/src/vdbeapi.c \ Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1823,10 +1823,12 @@ if( pWalker->eCode==3 && pExpr->iTable==pWalker->u.iCur ){ return WRC_Continue; } /* Fall through */ case TK_IF_NULL_ROW: + case TK_REGISTER: + testcase( pExpr->op==TK_REGISTER ); testcase( pExpr->op==TK_IF_NULL_ROW ); pWalker->eCode = 0; return WRC_Abort; case TK_VARIABLE: if( pWalker->eCode==5 ){ @@ -1840,12 +1842,12 @@ pWalker->eCode = 0; return WRC_Abort; } /* Fall through */ default: - testcase( pExpr->op==TK_SELECT ); /* sqlite3SelectWalkFail will disallow */ - testcase( pExpr->op==TK_EXISTS ); /* sqlite3SelectWalkFail will disallow */ + testcase( pExpr->op==TK_SELECT ); /* sqlite3SelectWalkFail() disallows */ + testcase( pExpr->op==TK_EXISTS ); /* sqlite3SelectWalkFail() disallows */ return WRC_Continue; } } static int exprIsConst(Expr *p, int initFlag, int iCur){ Walker w; @@ -4897,12 +4899,14 @@ return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ if( pA->op==TK_FUNCTION ){ if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; + }else if( pA->op==TK_COLLATE ){ + return sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ? 2 : 0; }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ - return pA->op==TK_COLLATE ? 1 : 2; + return 2; } } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){ if( combinedFlags & EP_xIsSelect ) return 2; Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -486,11 +486,12 @@ void sqlite3Insert( Parse *pParse, /* Parser context */ SrcList *pTabList, /* Name of table into which we are inserting */ Select *pSelect, /* A SELECT statement to use as the data source */ IdList *pColumn, /* Column names corresponding to IDLIST. */ - int onError /* How to handle constraint errors */ + int onError, /* How to handle constraint errors */ + Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */ ){ sqlite3 *db; /* The main database structure */ Table *pTab; /* The table to insert into. aka TABLE */ int i, j; /* Loop counters */ Vdbe *v; /* Generate code into this virtual machine */ @@ -801,10 +802,21 @@ assert( pIdx ); aRegIdx[i] = ++pParse->nMem; pParse->nMem += pIdx->nColumn; } } +#ifndef SQLITE_OMIT_UPSERT + if( pUpsert ){ + pTabList->a[0].iCursor = iDataCur; + pUpsert->pUpsertSrc = pTabList; + pUpsert->regData = regData; + if( pUpsert->pUpsertTarget ){ + sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert); + } + } +#endif + /* This is the top of the main insertion loop */ if( useTempTable ){ /* This block codes the top of loop only. The complete loop is the ** following pseudocode (template 4): @@ -1003,11 +1015,11 @@ #endif { int isReplace; /* Set to true if constraints may cause a replace */ int bUseSeek; /* True to use OPFLAG_SEEKRESULT */ sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, - regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0 + regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert ); sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE ** constraints or (b) there are no triggers and this table is not a @@ -1072,10 +1084,11 @@ } insert_cleanup: sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pList); + sqlite3UpsertDelete(db, pUpsert); sqlite3SelectDelete(db, pSelect); sqlite3IdListDelete(db, pColumn); sqlite3DbFree(db, aRegIdx); } @@ -1142,10 +1155,48 @@ testcase( w.eCode==CKCNSTRNT_COLUMN ); testcase( w.eCode==CKCNSTRNT_ROWID ); testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) ); return !w.eCode; } + +/* +** An instance of the ConstraintAddr object remembers the byte-code addresses +** for sections of the constraint checks that deal with uniqueness constraints +** on the rowid and on the upsert constraint. +** +** This information is passed into checkReorderConstraintChecks() to insert +** some OP_Goto operations so that the rowid and upsert constraints occur +** in the correct order relative to other constraints. +*/ +typedef struct ConstraintAddr ConstraintAddr; +struct ConstraintAddr { + int ipkTop; /* Subroutine for rowid constraint check */ + int upsertTop; /* Label for upsert constraint check subroutine */ + int upsertTop2; /* Copy of upsertTop not cleared by the call */ + int upsertBtm; /* upsert constraint returns to this label */ + int ipkBtm; /* Return opcode rowid constraint check */ +}; + +/* +** Generate any OP_Goto operations needed to cause constraints to be +** run that haven't already been run. +*/ +static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){ + if( p->upsertTop ){ + testcase( sqlite3VdbeLabelHasBeenResolved(v, p->upsertTop) ); + sqlite3VdbeGoto(v, p->upsertTop); + VdbeComment((v, "call upsert subroutine")); + sqlite3VdbeResolveLabel(v, p->upsertBtm); + p->upsertTop = 0; + } + if( p->ipkTop ){ + sqlite3VdbeGoto(v, p->ipkTop); + VdbeComment((v, "call rowid unique-check subroutine")); + sqlite3VdbeJumpHere(v, p->ipkBtm); + p->ipkTop = 0; + } +} /* ** Generate code to do constraint checks prior to an INSERT or an UPDATE ** on table pTab. ** @@ -1238,11 +1289,12 @@ int regOldData, /* Previous content. 0 for INSERTs */ u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */ u8 overrideError, /* Override onError to this if not OE_Default */ int ignoreDest, /* Jump to this label on an OE_Ignore resolution */ int *pbMayReplace, /* OUT: Set to true if constraint may cause a replace */ - int *aiChng /* column i is unchanged if aiChng[i]<0 */ + int *aiChng, /* column i is unchanged if aiChng[i]<0 */ + Upsert *pUpsert /* ON CONFLICT clauses, if any. NULL otherwise */ ){ Vdbe *v; /* VDBE under constrution */ Index *pIdx; /* Pointer to one of the indices */ Index *pPk = 0; /* The PRIMARY KEY index */ sqlite3 *db; /* Database connection */ @@ -1251,21 +1303,23 @@ int nCol; /* Number of columns */ int onError; /* Conflict resolution strategy */ int addr1; /* Address of jump instruction */ int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */ int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */ - int ipkTop = 0; /* Top of the rowid change constraint check */ - int ipkBottom = 0; /* Bottom of the rowid change constraint check */ + ConstraintAddr sAddr;/* Address information for constraint reordering */ + Index *pUpIdx = 0; /* Index to which to apply the upsert */ u8 isUpdate; /* True if this is an UPDATE operation */ u8 bAffinityDone = 0; /* True if the OP_Affinity operation has been run */ + int upsertBypass = 0; /* Address of Goto to bypass upsert subroutine */ isUpdate = regOldData!=0; db = pParse->db; v = sqlite3GetVdbe(pParse); assert( v!=0 ); assert( pTab->pSelect==0 ); /* This table is not a VIEW */ nCol = pTab->nCol; + memset(&sAddr, 0, sizeof(sAddr)); /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for ** normal rowid tables. nPkField is the number of key fields in the ** pPk index or 1 for a rowid table. In other words, nPkField is the ** number of fields in the true primary key of the table. */ @@ -1360,10 +1414,50 @@ sqlite3VdbeResolveLabel(v, allOk); } pParse->iSelfTab = 0; } #endif /* !defined(SQLITE_OMIT_CHECK) */ + + /* UNIQUE and PRIMARY KEY constraints should be handled in the following + ** order: + ** + ** (1) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore + ** (2) OE_Update + ** (3) OE_Replace + ** + ** OE_Fail and OE_Ignore must happen before any changes are made. + ** OE_Update guarantees that only a single row will change, so it + ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback + ** could happen in any order, but they are grouped up front for + ** convenience. + ** + ** Constraint checking code is generated in this order: + ** (A) The rowid constraint + ** (B) Unique index constraints that do not have OE_Replace as their + ** default conflict resolution strategy + ** (C) Unique index that do use OE_Replace by default. + ** + ** The ordering of (2) and (3) is accomplished by making sure the linked + ** list of indexes attached to a table puts all OE_Replace indexes last + ** in the list. See sqlite3CreateIndex() for where that happens. + */ + + if( pUpsert ){ + if( pUpsert->pUpsertTarget==0 ){ + /* An ON CONFLICT DO NOTHING clause, without a constraint-target. + ** Make all unique constraint resolution be OE_Ignore */ + assert( pUpsert->pUpsertSet==0 ); + overrideError = OE_Ignore; + pUpsert = 0; + }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){ + /* If the constraint-target is on some column other than + ** then ROWID, then we might need to move the UPSERT around + ** so that it occurs in the correct order. */ + sAddr.upsertTop = sAddr.upsertTop2 = sqlite3VdbeMakeLabel(v); + sAddr.upsertBtm = sqlite3VdbeMakeLabel(v); + } + } /* If rowid is changing, make sure the new rowid does not previously ** exist in the table. */ if( pkChng && pPk==0 ){ @@ -1383,39 +1477,54 @@ ** is unchanged. */ sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData); sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); VdbeCoverage(v); } + + /* figure out whether or not upsert applies in this case */ + if( pUpsert && pUpsert->pUpsertIdx==0 ){ + if( pUpsert->pUpsertSet==0 ){ + onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */ + }else{ + onError = OE_Update; /* DO UPDATE */ + } + } /* If the response to a rowid conflict is REPLACE but the response ** to some other UNIQUE constraint is FAIL or IGNORE, then we need ** to defer the running of the rowid conflict checking until after ** the UNIQUE constraints have run. */ - if( onError==OE_Replace && overrideError!=OE_Replace ){ - for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ - if( pIdx->onError==OE_Ignore || pIdx->onError==OE_Fail ){ - ipkTop = sqlite3VdbeAddOp0(v, OP_Goto); - break; - } - } + assert( OE_Update>OE_Replace ); + assert( OE_Ignore=OE_Replace + && onError!=overrideError + && pTab->pIndex + ){ + sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1; } /* Check to see if the new rowid already exists in the table. Skip ** the following conflict logic if it does not. */ + VdbeNoopComment((v, "uniqueness check for ROWID")); sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData); VdbeCoverage(v); - /* Generate code that deals with a rowid collision */ switch( onError ){ default: { onError = OE_Abort; /* Fall thru into the next case */ } case OE_Rollback: case OE_Abort: case OE_Fail: { + testcase( onError==OE_Rollback ); + testcase( onError==OE_Abort ); + testcase( onError==OE_Fail ); sqlite3RowidConstraint(pParse, onError, pTab); break; } case OE_Replace: { /* If there are DELETE triggers on this table and the @@ -1464,20 +1573,26 @@ } } seenReplace = 1; break; } +#ifndef SQLITE_OMIT_UPSERT + case OE_Update: { + sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur); + /* Fall through */ + } +#endif case OE_Ignore: { - /*assert( seenReplace==0 );*/ + testcase( onError==OE_Ignore ); sqlite3VdbeGoto(v, ignoreDest); break; } } sqlite3VdbeResolveLabel(v, addrRowidOk); - if( ipkTop ){ - ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto); - sqlite3VdbeJumpHere(v, ipkTop); + if( sAddr.ipkTop ){ + sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto); + sqlite3VdbeJumpHere(v, sAddr.ipkTop-1); } } /* Test all UNIQUE constraints by creating entries for each UNIQUE ** index and making sure that duplicate entries do not already exist. @@ -1491,16 +1606,25 @@ int regR; /* Range of registers holding conflicting PK */ int iThisCur; /* Cursor for this UNIQUE index */ int addrUniqueOk; /* Jump here if the UNIQUE constraint is satisfied */ if( aRegIdx[ix]==0 ) continue; /* Skip indices that do not change */ + if( pUpIdx==pIdx ){ + addrUniqueOk = sAddr.upsertBtm; + upsertBypass = sqlite3VdbeGoto(v, 0); + VdbeComment((v, "Skip upsert subroutine")); + sqlite3VdbeResolveLabel(v, sAddr.upsertTop2); + }else{ + addrUniqueOk = sqlite3VdbeMakeLabel(v); + } + VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName)); if( bAffinityDone==0 ){ sqlite3TableAffinity(v, pTab, regNewData+1); bAffinityDone = 1; } iThisCur = iIdxCur+ix; - addrUniqueOk = sqlite3VdbeMakeLabel(v); + /* Skip partial indices for which the WHERE clause is not true */ if( pIdx->pPartIdxWhere ){ sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]); pParse->iSelfTab = -(regNewData+1); @@ -1555,10 +1679,28 @@ if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ onError = OE_Abort; } + + /* Figure out if the upsert clause applies to this index */ + if( pUpIdx==pIdx ){ + if( pUpsert->pUpsertSet==0 ){ + onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */ + }else{ + onError = OE_Update; /* DO UPDATE */ + } + } + + /* Invoke subroutines to handle IPK replace and upsert prior to running + ** the first REPLACE constraint check. */ + if( onError==OE_Replace ){ + testcase( sAddr.ipkTop ); + testcase( sAddr.upsertTop + && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) ); + reorderConstraintChecks(v, &sAddr); + } /* Collision detection may be omitted if all of the following are true: ** (1) The conflict resolution algorithm is REPLACE ** (2) The table is a WITHOUT ROWID table ** (3) There are no secondary indexes on the table @@ -1638,19 +1780,29 @@ } } /* Generate code that executes if the new index entry is not unique */ assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail - || onError==OE_Ignore || onError==OE_Replace ); + || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update ); switch( onError ){ case OE_Rollback: case OE_Abort: case OE_Fail: { + testcase( onError==OE_Rollback ); + testcase( onError==OE_Abort ); + testcase( onError==OE_Fail ); sqlite3UniqueConstraint(pParse, onError, pIdx); break; } +#ifndef SQLITE_OMIT_UPSERT + case OE_Update: { + sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix); + /* Fall through */ + } +#endif case OE_Ignore: { + testcase( onError==OE_Ignore ); sqlite3VdbeGoto(v, ignoreDest); break; } default: { Trigger *pTrigger = 0; @@ -1664,18 +1816,23 @@ (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur); seenReplace = 1; break; } } - sqlite3VdbeResolveLabel(v, addrUniqueOk); + if( pUpIdx==pIdx ){ + sqlite3VdbeJumpHere(v, upsertBypass); + }else{ + sqlite3VdbeResolveLabel(v, addrUniqueOk); + } sqlite3ExprCachePop(pParse); if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField); + } - if( ipkTop ){ - sqlite3VdbeGoto(v, ipkTop+1); - sqlite3VdbeJumpHere(v, ipkBottom); - } + testcase( sAddr.ipkTop!=0 ); + testcase( sAddr.upsertTop + && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) ); + reorderConstraintChecks(v, &sAddr); *pbMayReplace = seenReplace; VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace)); } Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -203,11 +203,12 @@ // fallback to ID if they will not parse as their original value. // This obviates the need for the "id" nonterminal. // %fallback ID ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW - CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR + CONFLICT DATABASE DEFERRED DESC DETACH DO + EACH END EXCLUSIVE EXPLAIN FAIL FOR IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT %ifdef SQLITE_OMIT_COMPOUND_SELECT EXCEPT INTERSECT UNION @@ -237,10 +238,11 @@ %left PLUS MINUS. %left STAR SLASH REM. %left CONCAT. %left COLLATE. %right BITNOT. +%nonassoc ON. // An IDENTIFIER can be a generic identifier, or one of several // keywords. Any non-standard keyword can also be an identifier. // %token_class id ID|INDEXED. @@ -695,14 +697,31 @@ joinop(X) ::= JOIN_KW(A) nm(B) JOIN. {X = sqlite3JoinType(pParse,&A,&B,0); /*X-overwrites-A*/} joinop(X) ::= JOIN_KW(A) nm(B) nm(C) JOIN. {X = sqlite3JoinType(pParse,&A,&B,&C);/*X-overwrites-A*/} +// There is a parsing abiguity in an upsert statement that uses a +// SELECT on the RHS of a the INSERT: +// +// INSERT INTO tab SELECT * FROM aaa JOIN bbb ON CONFLICT ... +// here ----^^ +// +// When the ON token is encountered, the parser does not know if it is +// the beginning of an ON CONFLICT clause, or the beginning of an ON +// clause associated with the JOIN. The conflict is resolved in favor +// of the JOIN. If an ON CONFLICT clause is intended, insert a dummy +// WHERE clause in between, like this: +// +// INSERT INTO tab SELECT * FROM aaa JOIN bbb WHERE true ON CONFLICT ... +// +// The [AND] and [OR] precedence marks in the rules for on_opt cause the +// ON in this context to always be interpreted as belonging to the JOIN. +// %type on_opt {Expr*} %destructor on_opt {sqlite3ExprDelete(pParse->db, $$);} -on_opt(N) ::= ON expr(E). {N = E;} -on_opt(N) ::= . {N = 0;} +on_opt(N) ::= ON expr(E). {N = E;} +on_opt(N) ::= . [OR] {N = 0;} // Note that this block abuses the Token type just a little. If there is // no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If // there is an INDEXED BY clause, then the token is populated as per normal, // with z pointing to the token data and n containing the number of bytes @@ -805,19 +824,19 @@ %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT 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 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 %type setlist {ExprList*} %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);} @@ -837,18 +856,43 @@ A = sqlite3ExprListAppendVector(pParse, 0, X, Y); } ////////////////////////// The INSERT command ///////////////////////////////// // -cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). { - sqlite3Insert(pParse, X, S, F, R); +cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S) + upsert(U). { + sqlite3Insert(pParse, X, S, F, R, U); +} +cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) AS nm(A) select(S) + upsert(U). { + /* X could only be NULL following a OOM, but an OOM would have been detected + ** and stopped the parse long before this rule reduces. */ + assert( X!=0 ); + X->a[0].zAlias = sqlite3NameFromToken(pParse->db, &A); + sqlite3Insert(pParse, X, S, F, R, U); } cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES. { - sqlite3Insert(pParse, X, 0, F, R); + sqlite3Insert(pParse, X, 0, F, R, 0); } +%type upsert {Upsert*} + +// Because upsert only occurs at the tip end of the INSERT rule for cmd, +// there is never a case where the value of the upsert pointer will not +// be destroyed by the cmd action. So comment-out the destructor to +// avoid unreachable code. +//%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);} +upsert(A) ::= . { A = 0; } +upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) + DO UPDATE SET setlist(Z) where_opt(W). + { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);} +upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING. + { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); } +upsert(A) ::= ON CONFLICT DO NOTHING. + { A = sqlite3UpsertNew(pParse->db,0,0,0,0); } + %type insert_cmd {int} insert_cmd(A) ::= INSERT orconf(R). {A = R;} insert_cmd(A) ::= REPLACE. {A = OE_Replace;} %type idlist_opt {IdList*} @@ -1390,13 +1434,13 @@ UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E). {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);} // INSERT trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO - trnm(X) idlist_opt(F) select(S) scanpt(Z). - {A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,B,Z);/*A-overwrites-R*/} - + trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). { + A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,U,B,Z);/*A-overwrites-R*/ +} // DELETE trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E). {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y, B.z, E);} // SELECT Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -189,11 +189,11 @@ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ 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 */ assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ @@ -294,26 +294,39 @@ } pSchema = pExpr->pTab->pSchema; } } /* 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; pSchema = pTab->pSchema; cntTab++; @@ -329,28 +342,39 @@ /* IMP: R-51414-32910 */ iCol = -1; } 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)<iColumn = (i16)iCol; - pExpr->pTab = pTab; - isTrigger = 1; - } - } - } -#endif /* !defined(SQLITE_OMIT_TRIGGER) */ +#ifndef SQLITE_OMIT_UPSERT + if( pExpr->iTable==2 ){ + testcase( iCol==(-1) ); + pExpr->iTable = pNC->uNC.pUpsert->regData + iCol; + eNewExprOp = TK_REGISTER; + }else +#endif /* SQLITE_OMIT_UPSERT */ + { +#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 */ + } + } + } + } +#endif /* !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) */ /* ** Perhaps the name is a reference to the ROWID */ if( cnt==0 @@ -483,11 +507,11 @@ */ sqlite3ExprDelete(db, pExpr->pLeft); 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 ){ assert( pNC!=0 ); if( !ExprHasProperty(pExpr, EP_Alias) ){ @@ -1299,11 +1323,11 @@ ** aliases in the result set. ** ** 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; if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort; @@ -1534,11 +1558,11 @@ void sqlite3ResolveSelfReference( Parse *pParse, /* Parsing context */ Table *pTab, /* The table being referenced */ int type, /* NC_IsCheck or NC_PartIdx or NC_IdxExpr */ Expr *pExpr, /* Expression to resolve. May be NULL. */ - ExprList *pList /* Expression list to resolve. May be NUL. */ + ExprList *pList /* Expression list to resolve. May be NULL. */ ){ SrcList sSrc; /* Fake SrcList for pParse->pNewTable */ NameContext sNC; /* Name context for pParse->pNewTable */ assert( type==NC_IsCheck || type==NC_PartIdx || type==NC_IdxExpr ); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1093,10 +1093,11 @@ typedef struct TreeView TreeView; typedef struct Trigger Trigger; typedef struct TriggerPrg TriggerPrg; typedef struct TriggerStep TriggerStep; typedef struct UnpackedRecord UnpackedRecord; +typedef struct Upsert Upsert; typedef struct VTable VTable; typedef struct VtabCtx VtabCtx; typedef struct Walker Walker; typedef struct WhereInfo WhereInfo; typedef struct With With; @@ -2044,17 +2045,16 @@ #define OE_Rollback 1 /* Fail the operation and rollback the transaction */ #define OE_Abort 2 /* Back out changes but do no rollback transaction */ #define OE_Fail 3 /* Stop the operation but leave all prior changes */ #define OE_Ignore 4 /* Ignore the error. Do not do the INSERT or UPDATE */ #define OE_Replace 5 /* Delete existing record, then do INSERT or UPDATE */ - -#define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */ -#define OE_SetNull 7 /* Set the foreign key value to NULL */ -#define OE_SetDflt 8 /* Set the foreign key value to its default */ -#define OE_Cascade 9 /* Cascade the changes */ - -#define OE_Default 10 /* Do whatever the default action is */ +#define OE_Update 6 /* Process as a DO UPDATE in an upsert */ +#define OE_Restrict 7 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */ +#define OE_SetNull 8 /* Set the foreign key value to NULL */ +#define OE_SetDflt 9 /* Set the foreign key value to its default */ +#define OE_Cascade 10 /* Cascade the changes */ +#define OE_Default 11 /* Do whatever the default action is */ /* ** An instance of the following structure is passed as the first ** argument to sqlite3VdbeKeyCompare and is used to control the @@ -2683,10 +2683,11 @@ Parse *pParse; /* The parser */ SrcList *pSrcList; /* One or more tables used to resolve names */ 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 */ int nErr; /* Number of errors encountered while resolving names */ u16 ncFlags; /* Zero or more NC_* flags defined below */ @@ -2707,12 +2708,37 @@ #define NC_HasAgg 0x0010 /* One or more aggregate functions seen */ #define NC_IdxExpr 0x0020 /* True if resolving columns of CREATE INDEX */ #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 */ + +/* +** An instance of the following object describes a single ON CONFLICT +** clause in an upsert. +** +** The pUpsertTarget field is only set if the ON CONFLICT clause includes +** conflict-target clause. (In "ON CONFLICT(a,b)" the "(a,b)" is the +** conflict-target clause.) The pUpsertTargetWhere is the optional +** WHERE clause used to identify partial unique indexes. +** +** pUpsertSet is the list of column=expr terms of the UPDATE statement. +** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING. The +** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the +** WHERE clause is omitted. +*/ +struct Upsert { + ExprList *pUpsertTarget; /* Optional description of conflicting index */ + Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */ + Index *pUpsertIdx; /* Constraint that pUpsertTarget identifies */ + 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 */ +}; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** @@ -3209,12 +3235,13 @@ 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 */ Expr *pWhere; /* The WHERE clause for DELETE or UPDATE steps */ - ExprList *pExprList; /* SET clause for UPDATE. */ + 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 */ TriggerStep *pNext; /* Next in the link-list */ TriggerStep *pLast; /* Last element in link-list. Valid for 1st elem only */ }; @@ -3742,11 +3769,11 @@ void sqlite3AutoincrementEnd(Parse *pParse); #else # define sqlite3AutoincrementBegin(X) # define sqlite3AutoincrementEnd(X) #endif -void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int); +void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, Upsert*); void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*); IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*); int sqlite3IdListIndex(IdList*,const char*); SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int); SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*); @@ -3772,11 +3799,12 @@ void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) 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*); int sqlite3WhereIsDistinct(WhereInfo*); int sqlite3WhereIsOrdered(WhereInfo*); @@ -3865,11 +3893,11 @@ Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8,int); void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int); int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int); void sqlite3ResolvePartIdxLabel(Parse*,int); void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int, - u8,u8,int,int*,int*); + u8,u8,int,int*,int*,Upsert*); #ifdef SQLITE_ENABLE_NULL_TRIM void sqlite3SetMakeRecordP5(Vdbe*,Table*); #else # define sqlite3SetMakeRecordP5(A,B) #endif @@ -3918,11 +3946,12 @@ void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*, const char*,const char*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, - Select*,u8,const char*,const char*); + Select*,u8,Upsert*, + const char*,const char*); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8, const char*,const char*); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*, const char*,const char*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); @@ -4257,10 +4286,22 @@ void sqlite3WithPush(Parse*, With*, u8); #else #define sqlite3WithPush(x,y,z) #define sqlite3WithDelete(x,y) #endif +#ifndef SQLITE_OMIT_UPSERT + Upsert *sqlite3UpsertNew(sqlite3*,ExprList*,Expr*,ExprList*,Expr*); + void sqlite3UpsertDelete(sqlite3*,Upsert*); + Upsert *sqlite3UpsertDup(sqlite3*,Upsert*); + int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*); + void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int); +#else +#define sqlite3UpsertNew(v,w,x,y,z) ((Upsert*)0) +#define sqlite3UpsertDelete(x,y) +#define sqlite3UpsertDup(x,y) ((Upsert*)0) +#endif + /* Declarations for functions in fkey.c. All of these are replaced by ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign ** key functionality is available. If OMIT_TRIGGER is defined but ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In Index: src/trigger.c ================================================================== --- src/trigger.c +++ src/trigger.c @@ -23,10 +23,11 @@ sqlite3ExprDelete(db, pTmp->pWhere); sqlite3ExprListDelete(db, pTmp->pExprList); sqlite3SelectDelete(db, pTmp->pSelect); sqlite3IdListDelete(db, pTmp->pIdList); + sqlite3UpsertDelete(db, pTmp->pUpsert); sqlite3DbFree(db, pTmp->zSpan); sqlite3DbFree(db, pTmp); } } @@ -414,10 +415,11 @@ sqlite3 *db, /* The database connection */ Token *pTableName, /* Name of the table into which we insert */ IdList *pColumn, /* List of columns in pTableName to insert into */ Select *pSelect, /* A SELECT statement that supplies values */ u8 orconf, /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */ + Upsert *pUpsert, /* ON CONFLICT clauses for upsert */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ TriggerStep *pTriggerStep; @@ -425,13 +427,17 @@ pTriggerStep = triggerStepAllocate(db, TK_INSERT, pTableName, zStart, zEnd); if( pTriggerStep ){ pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); pTriggerStep->pIdList = pColumn; + pTriggerStep->pUpsert = pUpsert; pTriggerStep->orconf = orconf; }else{ + testcase( pColumn ); sqlite3IdListDelete(db, pColumn); + testcase( pUpsert ); + sqlite3UpsertDelete(db, pUpsert); } sqlite3SelectDelete(db, pSelect); return pTriggerStep; } @@ -744,20 +750,21 @@ case TK_UPDATE: { sqlite3Update(pParse, targetSrcList(pParse, pStep), sqlite3ExprListDup(db, pStep->pExprList, 0), sqlite3ExprDup(db, pStep->pWhere, 0), - pParse->eOrconf, 0, 0 + pParse->eOrconf, 0, 0, 0 ); break; } case TK_INSERT: { sqlite3Insert(pParse, targetSrcList(pParse, pStep), sqlite3SelectDup(db, pStep->pSelect, 0), sqlite3IdListDup(db, pStep->pIdList), - pParse->eOrconf + pParse->eOrconf, + sqlite3UpsertDup(db, pStep->pUpsert) ); break; } case TK_DELETE: { sqlite3DeleteFrom(pParse, Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -91,11 +91,12 @@ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ 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 */ int addrTop = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ @@ -224,10 +225,12 @@ /* Initialize the name-context */ 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 ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change @@ -512,12 +515,12 @@ VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } - /* If the record number will change, set register regNewRowid to - ** contain the new value. If the record number is not being modified, + /* If the rowid value will change, set register regNewRowid to + ** contain the new value. If the rowid is not being modified, ** then regNewRowid is the same register as regOldRowid, which is ** already populated. */ assert( chngKey || pTrigger || hasFK || regOldRowid==regNewRowid ); if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); @@ -624,11 +627,11 @@ /* Do constraint checks. */ assert( regOldRowid>0 ); sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace, - aXRef); + aXRef, 0); /* Do FK constraint checks. */ if( hasFK ){ sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey); } ADDED src/upsert.c Index: src/upsert.c ================================================================== --- /dev/null +++ src/upsert.c @@ -0,0 +1,263 @@ +/* +** 2018-04-12 +** +** 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 contains code to implement various aspects of UPSERT +** processing and handling of the Upsert object. +*/ +#include "sqliteInt.h" + +#ifndef SQLITE_OMIT_UPSERT +/* +** Free a list of Upsert objects +*/ +void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){ + if( p ){ + sqlite3ExprListDelete(db, p->pUpsertTarget); + sqlite3ExprDelete(db, p->pUpsertTargetWhere); + sqlite3ExprListDelete(db, p->pUpsertSet); + sqlite3ExprDelete(db, p->pUpsertWhere); + sqlite3DbFree(db, p); + } +} + +/* +** Duplicate an Upsert object. +*/ +Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){ + if( p==0 ) return 0; + return sqlite3UpsertNew(db, + sqlite3ExprListDup(db, p->pUpsertTarget, 0), + sqlite3ExprDup(db, p->pUpsertTargetWhere, 0), + sqlite3ExprListDup(db, p->pUpsertSet, 0), + sqlite3ExprDup(db, p->pUpsertWhere, 0) + ); +} + +/* +** Create a new Upsert object. +*/ +Upsert *sqlite3UpsertNew( + sqlite3 *db, /* Determines which memory allocator to use */ + ExprList *pTarget, /* Target argument to ON CONFLICT, or NULL */ + Expr *pTargetWhere, /* Optional WHERE clause on the target */ + ExprList *pSet, /* UPDATE columns, or NULL for a DO NOTHING */ + Expr *pWhere /* WHERE clause for the ON CONFLICT UPDATE */ +){ + Upsert *pNew; + pNew = sqlite3DbMallocRaw(db, sizeof(Upsert)); + if( pNew==0 ){ + sqlite3ExprListDelete(db, pTarget); + sqlite3ExprDelete(db, pTargetWhere); + sqlite3ExprListDelete(db, pSet); + sqlite3ExprDelete(db, pWhere); + return 0; + }else{ + pNew->pUpsertTarget = pTarget; + pNew->pUpsertTargetWhere = pTargetWhere; + pNew->pUpsertSet = pSet; + pNew->pUpsertWhere = pWhere; + pNew->pUpsertIdx = 0; + } + return pNew; +} + +/* +** Analyze the ON CONFLICT clause described by pUpsert. Resolve all +** symbols in the conflict-target. +** +** Return SQLITE_OK if everything works, or an error code is something +** is wrong. +*/ +int sqlite3UpsertAnalyzeTarget( + Parse *pParse, /* The parsing context */ + SrcList *pTabList, /* Table into which we are inserting */ + Upsert *pUpsert /* The ON CONFLICT clauses */ +){ + Table *pTab; /* That table into which we are inserting */ + int rc; /* Result code */ + int iCursor; /* Cursor used by pTab */ + Index *pIdx; /* One of the indexes of pTab */ + ExprList *pTarget; /* The conflict-target clause */ + Expr *pTerm; /* One term of the conflict-target clause */ + NameContext sNC; /* Context for resolving symbolic names */ + Expr sCol[2]; /* Index column converted into an Expr */ + + assert( pTabList->nSrc==1 ); + assert( pTabList->a[0].pTab!=0 ); + assert( pUpsert!=0 ); + assert( pUpsert->pUpsertTarget!=0 ); + + /* Resolve all symbolic names in the conflict-target clause, which + ** includes both the list of columns and the optional partial-index + ** WHERE clause. + */ + memset(&sNC, 0, sizeof(sNC)); + sNC.pParse = pParse; + sNC.pSrcList = pTabList; + rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget); + if( rc ) return rc; + rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere); + if( rc ) return rc; + + /* Check to see if the conflict target matches the rowid. */ + pTab = pTabList->a[0].pTab; + pTarget = pUpsert->pUpsertTarget; + iCursor = pTabList->a[0].iCursor; + if( HasRowid(pTab) + && pTarget->nExpr==1 + && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN + && pTerm->iColumn==XN_ROWID + ){ + /* The conflict-target is the rowid of the primary table */ + assert( pUpsert->pUpsertIdx==0 ); + return SQLITE_OK; + } + + /* Initialize sCol[0..1] to be an expression parse tree for a + ** single column of an index. The sCol[0] node will be the TK_COLLATE + ** operator and sCol[1] will be the TK_COLUMN operator. Code below + ** will populate the specific collation and column number values + ** prior to comparing against the conflict-target expression. + */ + memset(sCol, 0, sizeof(sCol)); + sCol[0].op = TK_COLLATE; + sCol[0].pLeft = &sCol[1]; + sCol[1].op = TK_COLUMN; + sCol[1].iTable = pTabList->a[0].iCursor; + + /* Check for matches against other indexes */ + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + int ii, jj, nn; + if( !IsUniqueIndex(pIdx) ) continue; + if( pTarget->nExpr!=pIdx->nKeyCol ) continue; + if( pIdx->pPartIdxWhere ){ + if( pUpsert->pUpsertTargetWhere==0 ) continue; + if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere, + pIdx->pPartIdxWhere, iCursor)!=0 ){ + continue; + } + } + nn = pIdx->nKeyCol; + for(ii=0; iiaiColumn[ii]==XN_EXPR ){ + assert( pIdx->aColExpr!=0 ); + assert( pIdx->aColExpr->nExpr>ii ); + pExpr = pIdx->aColExpr->a[ii].pExpr; + }else{ + sCol[1].iColumn = pIdx->aiColumn[ii]; + sCol[0].u.zToken = (char*)pIdx->azColl[ii]; + pExpr = &sCol[0]; + } + for(jj=0; jja[jj].pExpr, pExpr,iCursor)<2 ){ + break; /* Column ii of the index matches column jj of target */ + } + } + if( jj>=nn ){ + /* The target contains no match for column jj of the index */ + break; + } + } + if( iipUpsertIdx = pIdx; + return SQLITE_OK; + } + sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any " + "PRIMARY KEY or UNIQUE constraint"); + return SQLITE_ERROR; +} + +/* +** Generate bytecode that does an UPDATE as part of an upsert. +** +** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK. +** In this case parameter iCur is a cursor open on the table b-tree that +** currently points to the conflicting table row. Otherwise, if pIdx +** is not NULL, then pIdx is the constraint that failed and iCur is a +** cursor points to the conflicting row. +*/ +void sqlite3UpsertDoUpdate( + Parse *pParse, /* The parsing and code-generating context */ + Upsert *pUpsert, /* The ON CONFLICT clause for the upsert */ + Table *pTab, /* The table being updated */ + Index *pIdx, /* The UNIQUE constraint that failed */ + int iCur /* Cursor for pIdx (or pTab if pIdx==NULL) */ +){ + Vdbe *v = pParse->pVdbe; + sqlite3 *db = pParse->db; + int regKey; /* Register(s) containing the key */ + Expr *pWhere; /* Where clause for the UPDATE */ + Expr *pE1, *pE2; + SrcList *pSrc; /* FROM clause for the UPDATE */ + + assert( v!=0 ); + VdbeNoopComment((v, "Begin DO UPDATE of UPSERT")); + pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0); + if( pIdx==0 || HasRowid(pTab) ){ + /* We are dealing with an IPK */ + regKey = ++pParse->nMem; + if( pIdx ){ + sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey); + }else{ + sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey); + } + pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); + if( pE1 ){ + pE1->pTab = pTab; + pE1->iTable = pParse->nTab; + pE1->iColumn = -1; + } + pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0); + if( pE2 ){ + pE2->iTable = regKey; + pE2->affinity = SQLITE_AFF_INTEGER; + } + pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2)); + }else{ + /* a WITHOUT ROWID table */ + int i, j; + int iTab = pParse->nTab+1; + Index *pX; + for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){ + iTab++; + } + for(i=0; inKeyCol; i++){ + regKey = ++pParse->nMem; + sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey); + j = pIdx->aiColumn[i]; + VdbeComment((v, "%s", pTab->aCol[j].zName)); + pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); + if( pE1 ){ + pE1->pTab = pTab; + pE1->iTable = iTab; + pE1->iColumn = j; + } + pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0); + if( pE2 ){ + pE2->iTable = regKey; + pE2->affinity = pTab->zColAff[j]; + } + pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2)); + } + } + pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0); + sqlite3Update(pParse, pSrc, + sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0), + pWhere, OE_Abort, 0, 0, pUpsert); + VdbeNoopComment((v, "End DO UPDATE of UPSERT")); +} + +#endif /* SQLITE_OMIT_UPSERT */ Index: src/vdbe.h ================================================================== --- src/vdbe.h +++ src/vdbe.h @@ -218,10 +218,13 @@ void sqlite3VdbeDelete(Vdbe*); void sqlite3VdbeClearObject(sqlite3*,Vdbe*); void sqlite3VdbeMakeReady(Vdbe*,Parse*); int sqlite3VdbeFinalize(Vdbe*); void sqlite3VdbeResolveLabel(Vdbe*, int); +#ifdef SQLITE_COVERAGE_TEST + int sqlite3VdbeLabelHasBeenResolved(Vdbe*,int); +#endif int sqlite3VdbeCurrentAddr(Vdbe*); #ifdef SQLITE_DEBUG int sqlite3VdbeAssertMayAbort(Vdbe *, int); #endif void sqlite3VdbeResetStepResult(Vdbe*); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -390,14 +390,33 @@ int j = ADDR(x); assert( v->magic==VDBE_MAGIC_INIT ); assert( jnLabel ); assert( j>=0 ); if( p->aLabel ){ +#ifdef SQLITE_DEBUG + if( p->db->flags & SQLITE_VdbeAddopTrace ){ + printf("RESOLVE LABEL %d to %d\n", x, v->nOp); + } +#endif + assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */ p->aLabel[j] = v->nOp; } } +#ifdef SQLITE_COVERAGE_TEST +/* +** Return TRUE if and only if the label x has already been resolved. +** Return FALSE (zero) if label x is still unresolved. +** +** This routine is only used inside of testcase() macros, and so it +** only exists when measuring test coverage. +*/ +int sqlite3VdbeLabelHasBeenResolved(Vdbe *v, int x){ + return v->pParse->aLabel && v->pParse->aLabel[ADDR(x)]>=0; +} +#endif /* SQLITE_COVERAGE_TEST */ + /* ** Mark the VDBE as one that can only be run one time. */ void sqlite3VdbeRunOnlyOnce(Vdbe *p){ p->runOnlyOnce = 1; Index: test/distinct.test ================================================================== --- test/distinct.test +++ test/distinct.test @@ -166,11 +166,11 @@ 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c} 5 "b FROM t1 WHERE a = 'a'" {} {b} 6 "b FROM t1 ORDER BY +b COLLATE binary" {btree hash} {B b} 7 "a FROM t1" {} {A a} 8 "b COLLATE nocase FROM t1" {} {b} - 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {b} + 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {B} } { do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables } ADDED test/upsert1.test Index: test/upsert1.test ================================================================== --- /dev/null +++ test/upsert1.test @@ -0,0 +1,92 @@ +# 2018-04-12 +# +# 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. +# +#*********************************************************************** +# +# Test cases for UPSERT + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix zipfile + +do_execsql_test upsert1-100 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0); + CREATE UNIQUE INDEX t1x1 ON t1(b); + INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING; + INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING; + SELECT * FROM t1; +} {1 2 0} +do_execsql_test upsert1-101 { + DELETE FROM t1; + INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING; + INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING; + SELECT * FROM t1; +} {2 3 0} +do_execsql_test upsert1-102 { + DELETE FROM t1; + INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING; + INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING; + SELECT * FROM t1; +} {3 4 0} +do_catchsql_test upsert1-110 { + INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING; + SELECT * FROM t1; +} {1 {no such column: x}} +do_catchsql_test upsert1-120 { + INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING; + SELECT * FROM t1; +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} +breakpoint +do_catchsql_test upsert1-130 { + INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING; + SELECT * FROM t1; +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} +do_execsql_test upsert1-140 { + DELETE FROM t1; + INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING; + SELECT * FROM t1; +} {5 6 0} + +do_catchsql_test upsert1-200 { + DROP TABLE t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0); + CREATE UNIQUE INDEX t1x1 ON t1(a+b); + INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING; + INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING; + SELECT * FROM t1; +} {0 {7 8 0}} +do_catchsql_test upsert1-201 { + INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING; +} {1 {UNIQUE constraint failed: index 't1x1'}} +do_catchsql_test upsert1-210 { + DELETE FROM t1; + INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING; + SELECT * FROM t1; +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} + +do_catchsql_test upsert1-300 { + DROP INDEX t1x1; + DELETE FROM t1; + CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10; + INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING; + SELECT * FROM t1; +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} +do_catchsql_test upsert1-310 { + DELETE FROM t1; + INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING; + SELECT * FROM t1; +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} +do_execsql_test upsert1-320 { + DELETE FROM t1; + INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20) + ON CONFLICT(b) WHERE b>10 DO NOTHING; + SELECT *, 'x' FROM t1 ORDER BY b, a; +} {1 2 0 x 3 2 0 x 4 20 0 x} + +finish_test ADDED test/upsert2.test Index: test/upsert2.test ================================================================== --- /dev/null +++ test/upsert2.test @@ -0,0 +1,170 @@ +# 2018-04-17 +# +# 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. +# +#*********************************************************************** +# +# Test cases for UPSERT + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix zipfile + +do_execsql_test upsert2-100 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0); + INSERT INTO t1(a,b) VALUES(1,2),(3,4); + INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1) + ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b