1 /*
2 ** 2001 September 15
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle INSERT statements in SQLite.
14 */
15 #include "sqliteInt.h"
16
17 /*
18 ** Generate code that will
19 **
20 ** (1) acquire a lock for table pTab then
21 ** (2) open pTab as cursor iCur.
22 **
23 ** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index
24 ** for that table that is actually opened.
25 */
sqlite3OpenTable(Parse * pParse,int iCur,int iDb,Table * pTab,int opcode)26 void sqlite3OpenTable(
27 Parse *pParse, /* Generate code into this VDBE */
28 int iCur, /* The cursor number of the table */
29 int iDb, /* The database index in sqlite3.aDb[] */
30 Table *pTab, /* The table to be opened */
31 int opcode /* OP_OpenRead or OP_OpenWrite */
32 ){
33 Vdbe *v;
34 assert( !IsVirtual(pTab) );
35 v = sqlite3GetVdbe(pParse);
36 assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
37 sqlite3TableLock(pParse, iDb, pTab->tnum,
38 (opcode==OP_OpenWrite)?1:0, pTab->zName);
39 if( HasRowid(pTab) ){
40 sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb, pTab->nNVCol);
41 VdbeComment((v, "%s", pTab->zName));
42 }else{
43 Index *pPk = sqlite3PrimaryKeyIndex(pTab);
44 assert( pPk!=0 );
45 assert( pPk->tnum==pTab->tnum );
46 sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb);
47 sqlite3VdbeSetP4KeyInfo(pParse, pPk);
48 VdbeComment((v, "%s", pTab->zName));
49 }
50 }
51
52 /*
53 ** Return a pointer to the column affinity string associated with index
54 ** pIdx. A column affinity string has one character for each column in
55 ** the table, according to the affinity of the column:
56 **
57 ** Character Column affinity
58 ** ------------------------------
59 ** 'A' BLOB
60 ** 'B' TEXT
61 ** 'C' NUMERIC
62 ** 'D' INTEGER
63 ** 'F' REAL
64 **
65 ** An extra 'D' is appended to the end of the string to cover the
66 ** rowid that appears as the last column in every index.
67 **
68 ** Memory for the buffer containing the column index affinity string
69 ** is managed along with the rest of the Index structure. It will be
70 ** released when sqlite3DeleteIndex() is called.
71 */
sqlite3IndexAffinityStr(sqlite3 * db,Index * pIdx)72 const char *sqlite3IndexAffinityStr(sqlite3 *db, Index *pIdx){
73 if( !pIdx->zColAff ){
74 /* The first time a column affinity string for a particular index is
75 ** required, it is allocated and populated here. It is then stored as
76 ** a member of the Index structure for subsequent use.
77 **
78 ** The column affinity string will eventually be deleted by
79 ** sqliteDeleteIndex() when the Index structure itself is cleaned
80 ** up.
81 */
82 int n;
83 Table *pTab = pIdx->pTable;
84 pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);
85 if( !pIdx->zColAff ){
86 sqlite3OomFault(db);
87 return 0;
88 }
89 for(n=0; n<pIdx->nColumn; n++){
90 i16 x = pIdx->aiColumn[n];
91 char aff;
92 if( x>=0 ){
93 aff = pTab->aCol[x].affinity;
94 }else if( x==XN_ROWID ){
95 aff = SQLITE_AFF_INTEGER;
96 }else{
97 assert( x==XN_EXPR );
98 assert( pIdx->aColExpr!=0 );
99 aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
100 }
101 if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;
102 if( aff>SQLITE_AFF_NUMERIC) aff = SQLITE_AFF_NUMERIC;
103 pIdx->zColAff[n] = aff;
104 }
105 pIdx->zColAff[n] = 0;
106 }
107
108 return pIdx->zColAff;
109 }
110
111 /*
112 ** Compute the affinity string for table pTab, if it has not already been
113 ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
114 **
115 ** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
116 ** if iReg>0 then code an OP_Affinity opcode that will set the affinities
117 ** for register iReg and following. Or if affinities exists and iReg==0,
118 ** then just set the P4 operand of the previous opcode (which should be
119 ** an OP_MakeRecord) to the affinity string.
120 **
121 ** A column affinity string has one character per column:
122 **
123 ** Character Column affinity
124 ** ------------------------------
125 ** 'A' BLOB
126 ** 'B' TEXT
127 ** 'C' NUMERIC
128 ** 'D' INTEGER
129 ** 'E' REAL
130 */
sqlite3TableAffinity(Vdbe * v,Table * pTab,int iReg)131 void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
132 int i, j;
133 char *zColAff = pTab->zColAff;
134 if( zColAff==0 ){
135 sqlite3 *db = sqlite3VdbeDb(v);
136 zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
137 if( !zColAff ){
138 sqlite3OomFault(db);
139 return;
140 }
141
142 for(i=j=0; i<pTab->nCol; i++){
143 assert( pTab->aCol[i].affinity!=0 );
144 if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ){
145 zColAff[j++] = pTab->aCol[i].affinity;
146 }
147 }
148 do{
149 zColAff[j--] = 0;
150 }while( j>=0 && zColAff[j]<=SQLITE_AFF_BLOB );
151 pTab->zColAff = zColAff;
152 }
153 assert( zColAff!=0 );
154 i = sqlite3Strlen30NN(zColAff);
155 if( i ){
156 if( iReg ){
157 sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
158 }else{
159 sqlite3VdbeChangeP4(v, -1, zColAff, i);
160 }
161 }
162 }
163
164 /*
165 ** Return non-zero if the table pTab in database iDb or any of its indices
166 ** have been opened at any point in the VDBE program. This is used to see if
167 ** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
168 ** run without using a temporary table for the results of the SELECT.
169 */
readsTable(Parse * p,int iDb,Table * pTab)170 static int readsTable(Parse *p, int iDb, Table *pTab){
171 Vdbe *v = sqlite3GetVdbe(p);
172 int i;
173 int iEnd = sqlite3VdbeCurrentAddr(v);
174 #ifndef SQLITE_OMIT_VIRTUALTABLE
175 VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0;
176 #endif
177
178 for(i=1; i<iEnd; i++){
179 VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
180 assert( pOp!=0 );
181 if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
182 Index *pIndex;
183 int tnum = pOp->p2;
184 if( tnum==pTab->tnum ){
185 return 1;
186 }
187 for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
188 if( tnum==pIndex->tnum ){
189 return 1;
190 }
191 }
192 }
193 #ifndef SQLITE_OMIT_VIRTUALTABLE
194 if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pVTab ){
195 assert( pOp->p4.pVtab!=0 );
196 assert( pOp->p4type==P4_VTAB );
197 return 1;
198 }
199 #endif
200 }
201 return 0;
202 }
203
204 /* This walker callback will compute the union of colFlags flags for all
205 ** referenced columns in a CHECK constraint or generated column expression.
206 */
exprColumnFlagUnion(Walker * pWalker,Expr * pExpr)207 static int exprColumnFlagUnion(Walker *pWalker, Expr *pExpr){
208 if( pExpr->op==TK_COLUMN && pExpr->iColumn>=0 ){
209 assert( pExpr->iColumn < pWalker->u.pTab->nCol );
210 pWalker->eCode |= pWalker->u.pTab->aCol[pExpr->iColumn].colFlags;
211 }
212 return WRC_Continue;
213 }
214
215 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
216 /*
217 ** All regular columns for table pTab have been puts into registers
218 ** starting with iRegStore. The registers that correspond to STORED
219 ** or VIRTUAL columns have not yet been initialized. This routine goes
220 ** back and computes the values for those columns based on the previously
221 ** computed normal columns.
222 */
sqlite3ComputeGeneratedColumns(Parse * pParse,int iRegStore,Table * pTab)223 void sqlite3ComputeGeneratedColumns(
224 Parse *pParse, /* Parsing context */
225 int iRegStore, /* Register holding the first column */
226 Table *pTab /* The table */
227 ){
228 int i;
229 Walker w;
230 Column *pRedo;
231 int eProgress;
232 VdbeOp *pOp;
233
234 assert( pTab->tabFlags & TF_HasGenerated );
235 testcase( pTab->tabFlags & TF_HasVirtual );
236 testcase( pTab->tabFlags & TF_HasStored );
237
238 /* Before computing generated columns, first go through and make sure
239 ** that appropriate affinity has been applied to the regular columns
240 */
241 sqlite3TableAffinity(pParse->pVdbe, pTab, iRegStore);
242 if( (pTab->tabFlags & TF_HasStored)!=0
243 && (pOp = sqlite3VdbeGetOp(pParse->pVdbe,-1))->opcode==OP_Affinity
244 ){
245 /* Change the OP_Affinity argument to '@' (NONE) for all stored
246 ** columns. '@' is the no-op affinity and those columns have not
247 ** yet been computed. */
248 int ii, jj;
249 char *zP4 = pOp->p4.z;
250 assert( zP4!=0 );
251 assert( pOp->p4type==P4_DYNAMIC );
252 for(ii=jj=0; zP4[jj]; ii++){
253 if( pTab->aCol[ii].colFlags & COLFLAG_VIRTUAL ){
254 continue;
255 }
256 if( pTab->aCol[ii].colFlags & COLFLAG_STORED ){
257 zP4[jj] = SQLITE_AFF_NONE;
258 }
259 jj++;
260 }
261 }
262
263 /* Because there can be multiple generated columns that refer to one another,
264 ** this is a two-pass algorithm. On the first pass, mark all generated
265 ** columns as "not available".
266 */
267 for(i=0; i<pTab->nCol; i++){
268 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
269 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
270 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
271 pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL;
272 }
273 }
274
275 w.u.pTab = pTab;
276 w.xExprCallback = exprColumnFlagUnion;
277 w.xSelectCallback = 0;
278 w.xSelectCallback2 = 0;
279
280 /* On the second pass, compute the value of each NOT-AVAILABLE column.
281 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
282 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
283 ** they are needed.
284 */
285 pParse->iSelfTab = -iRegStore;
286 do{
287 eProgress = 0;
288 pRedo = 0;
289 for(i=0; i<pTab->nCol; i++){
290 Column *pCol = pTab->aCol + i;
291 if( (pCol->colFlags & COLFLAG_NOTAVAIL)!=0 ){
292 int x;
293 pCol->colFlags |= COLFLAG_BUSY;
294 w.eCode = 0;
295 sqlite3WalkExpr(&w, pCol->pDflt);
296 pCol->colFlags &= ~COLFLAG_BUSY;
297 if( w.eCode & COLFLAG_NOTAVAIL ){
298 pRedo = pCol;
299 continue;
300 }
301 eProgress = 1;
302 assert( pCol->colFlags & COLFLAG_GENERATED );
303 x = sqlite3TableColumnToStorage(pTab, i) + iRegStore;
304 sqlite3ExprCodeGeneratedColumn(pParse, pCol, x);
305 pCol->colFlags &= ~COLFLAG_NOTAVAIL;
306 }
307 }
308 }while( pRedo && eProgress );
309 if( pRedo ){
310 sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"", pRedo->zName);
311 }
312 pParse->iSelfTab = 0;
313 }
314 #endif /* SQLITE_OMIT_GENERATED_COLUMNS */
315
316
317 #ifndef SQLITE_OMIT_AUTOINCREMENT
318 /*
319 ** Locate or create an AutoincInfo structure associated with table pTab
320 ** which is in database iDb. Return the register number for the register
321 ** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT
322 ** table. (Also return zero when doing a VACUUM since we do not want to
323 ** update the AUTOINCREMENT counters during a VACUUM.)
324 **
325 ** There is at most one AutoincInfo structure per table even if the
326 ** same table is autoincremented multiple times due to inserts within
327 ** triggers. A new AutoincInfo structure is created if this is the
328 ** first use of table pTab. On 2nd and subsequent uses, the original
329 ** AutoincInfo structure is used.
330 **
331 ** Four consecutive registers are allocated:
332 **
333 ** (1) The name of the pTab table.
334 ** (2) The maximum ROWID of pTab.
335 ** (3) The rowid in sqlite_sequence of pTab
336 ** (4) The original value of the max ROWID in pTab, or NULL if none
337 **
338 ** The 2nd register is the one that is returned. That is all the
339 ** insert routine needs to know about.
340 */
autoIncBegin(Parse * pParse,int iDb,Table * pTab)341 static int autoIncBegin(
342 Parse *pParse, /* Parsing context */
343 int iDb, /* Index of the database holding pTab */
344 Table *pTab /* The table we are writing to */
345 ){
346 int memId = 0; /* Register holding maximum rowid */
347 assert( pParse->db->aDb[iDb].pSchema!=0 );
348 if( (pTab->tabFlags & TF_Autoincrement)!=0
349 && (pParse->db->mDbFlags & DBFLAG_Vacuum)==0
350 ){
351 Parse *pToplevel = sqlite3ParseToplevel(pParse);
352 AutoincInfo *pInfo;
353 Table *pSeqTab = pParse->db->aDb[iDb].pSchema->pSeqTab;
354
355 /* Verify that the sqlite_sequence table exists and is an ordinary
356 ** rowid table with exactly two columns.
357 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */
358 if( pSeqTab==0
359 || !HasRowid(pSeqTab)
360 || IsVirtual(pSeqTab)
361 || pSeqTab->nCol!=2
362 ){
363 pParse->nErr++;
364 pParse->rc = SQLITE_CORRUPT_SEQUENCE;
365 return 0;
366 }
367
368 pInfo = pToplevel->pAinc;
369 while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; }
370 if( pInfo==0 ){
371 pInfo = sqlite3DbMallocRawNN(pParse->db, sizeof(*pInfo));
372 if( pInfo==0 ) return 0;
373 pInfo->pNext = pToplevel->pAinc;
374 pToplevel->pAinc = pInfo;
375 pInfo->pTab = pTab;
376 pInfo->iDb = iDb;
377 pToplevel->nMem++; /* Register to hold name of table */
378 pInfo->regCtr = ++pToplevel->nMem; /* Max rowid register */
379 pToplevel->nMem +=2; /* Rowid in sqlite_sequence + orig max val */
380 }
381 memId = pInfo->regCtr;
382 }
383 return memId;
384 }
385
386 /*
387 ** This routine generates code that will initialize all of the
388 ** register used by the autoincrement tracker.
389 */
sqlite3AutoincrementBegin(Parse * pParse)390 void sqlite3AutoincrementBegin(Parse *pParse){
391 AutoincInfo *p; /* Information about an AUTOINCREMENT */
392 sqlite3 *db = pParse->db; /* The database connection */
393 Db *pDb; /* Database only autoinc table */
394 int memId; /* Register holding max rowid */
395 Vdbe *v = pParse->pVdbe; /* VDBE under construction */
396
397 /* This routine is never called during trigger-generation. It is
398 ** only called from the top-level */
399 assert( pParse->pTriggerTab==0 );
400 assert( sqlite3IsToplevel(pParse) );
401
402 assert( v ); /* We failed long ago if this is not so */
403 for(p = pParse->pAinc; p; p = p->pNext){
404 static const int iLn = VDBE_OFFSET_LINENO(2);
405 static const VdbeOpList autoInc[] = {
406 /* 0 */ {OP_Null, 0, 0, 0},
407 /* 1 */ {OP_Rewind, 0, 10, 0},
408 /* 2 */ {OP_Column, 0, 0, 0},
409 /* 3 */ {OP_Ne, 0, 9, 0},
410 /* 4 */ {OP_Rowid, 0, 0, 0},
411 /* 5 */ {OP_Column, 0, 1, 0},
412 /* 6 */ {OP_AddImm, 0, 0, 0},
413 /* 7 */ {OP_Copy, 0, 0, 0},
414 /* 8 */ {OP_Goto, 0, 11, 0},
415 /* 9 */ {OP_Next, 0, 2, 0},
416 /* 10 */ {OP_Integer, 0, 0, 0},
417 /* 11 */ {OP_Close, 0, 0, 0}
418 };
419 VdbeOp *aOp;
420 pDb = &db->aDb[p->iDb];
421 memId = p->regCtr;
422 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );
423 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
424 sqlite3VdbeLoadString(v, memId-1, p->pTab->zName);
425 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoInc), autoInc, iLn);
426 if( aOp==0 ) break;
427 aOp[0].p2 = memId;
428 aOp[0].p3 = memId+2;
429 aOp[2].p3 = memId;
430 aOp[3].p1 = memId-1;
431 aOp[3].p3 = memId;
432 aOp[3].p5 = SQLITE_JUMPIFNULL;
433 aOp[4].p2 = memId+1;
434 aOp[5].p3 = memId;
435 aOp[6].p1 = memId;
436 aOp[7].p2 = memId+2;
437 aOp[7].p1 = memId;
438 aOp[10].p2 = memId;
439 if( pParse->nTab==0 ) pParse->nTab = 1;
440 }
441 }
442
443 /*
444 ** Update the maximum rowid for an autoincrement calculation.
445 **
446 ** This routine should be called when the regRowid register holds a
447 ** new rowid that is about to be inserted. If that new rowid is
448 ** larger than the maximum rowid in the memId memory cell, then the
449 ** memory cell is updated.
450 */
autoIncStep(Parse * pParse,int memId,int regRowid)451 static void autoIncStep(Parse *pParse, int memId, int regRowid){
452 if( memId>0 ){
453 sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);
454 }
455 }
456
457 /*
458 ** This routine generates the code needed to write autoincrement
459 ** maximum rowid values back into the sqlite_sequence register.
460 ** Every statement that might do an INSERT into an autoincrement
461 ** table (either directly or through triggers) needs to call this
462 ** routine just before the "exit" code.
463 */
autoIncrementEnd(Parse * pParse)464 static SQLITE_NOINLINE void autoIncrementEnd(Parse *pParse){
465 AutoincInfo *p;
466 Vdbe *v = pParse->pVdbe;
467 sqlite3 *db = pParse->db;
468
469 assert( v );
470 for(p = pParse->pAinc; p; p = p->pNext){
471 static const int iLn = VDBE_OFFSET_LINENO(2);
472 static const VdbeOpList autoIncEnd[] = {
473 /* 0 */ {OP_NotNull, 0, 2, 0},
474 /* 1 */ {OP_NewRowid, 0, 0, 0},
475 /* 2 */ {OP_MakeRecord, 0, 2, 0},
476 /* 3 */ {OP_Insert, 0, 0, 0},
477 /* 4 */ {OP_Close, 0, 0, 0}
478 };
479 VdbeOp *aOp;
480 Db *pDb = &db->aDb[p->iDb];
481 int iRec;
482 int memId = p->regCtr;
483
484 iRec = sqlite3GetTempReg(pParse);
485 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );
486 sqlite3VdbeAddOp3(v, OP_Le, memId+2, sqlite3VdbeCurrentAddr(v)+7, memId);
487 VdbeCoverage(v);
488 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
489 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoIncEnd), autoIncEnd, iLn);
490 if( aOp==0 ) break;
491 aOp[0].p1 = memId+1;
492 aOp[1].p2 = memId+1;
493 aOp[2].p1 = memId-1;
494 aOp[2].p3 = iRec;
495 aOp[3].p2 = iRec;
496 aOp[3].p3 = memId+1;
497 aOp[3].p5 = OPFLAG_APPEND;
498 sqlite3ReleaseTempReg(pParse, iRec);
499 }
500 }
sqlite3AutoincrementEnd(Parse * pParse)501 void sqlite3AutoincrementEnd(Parse *pParse){
502 if( pParse->pAinc ) autoIncrementEnd(pParse);
503 }
504 #else
505 /*
506 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
507 ** above are all no-ops
508 */
509 # define autoIncBegin(A,B,C) (0)
510 # define autoIncStep(A,B,C)
511 #endif /* SQLITE_OMIT_AUTOINCREMENT */
512
513
514 /* Forward declaration */
515 static int xferOptimization(
516 Parse *pParse, /* Parser context */
517 Table *pDest, /* The table we are inserting into */
518 Select *pSelect, /* A SELECT statement to use as the data source */
519 int onError, /* How to handle constraint errors */
520 int iDbDest /* The database of pDest */
521 );
522
523 /*
524 ** This routine is called to handle SQL of the following forms:
525 **
526 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
527 ** insert into TABLE (IDLIST) select
528 ** insert into TABLE (IDLIST) default values
529 **
530 ** The IDLIST following the table name is always optional. If omitted,
531 ** then a list of all (non-hidden) columns for the table is substituted.
532 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
533 ** is omitted.
534 **
535 ** For the pSelect parameter holds the values to be inserted for the
536 ** first two forms shown above. A VALUES clause is really just short-hand
537 ** for a SELECT statement that omits the FROM clause and everything else
538 ** that follows. If the pSelect parameter is NULL, that means that the
539 ** DEFAULT VALUES form of the INSERT statement is intended.
540 **
541 ** The code generated follows one of four templates. For a simple
542 ** insert with data coming from a single-row VALUES clause, the code executes
543 ** once straight down through. Pseudo-code follows (we call this
544 ** the "1st template"):
545 **
546 ** open write cursor to <table> and its indices
547 ** put VALUES clause expressions into registers
548 ** write the resulting record into <table>
549 ** cleanup
550 **
551 ** The three remaining templates assume the statement is of the form
552 **
553 ** INSERT INTO <table> SELECT ...
554 **
555 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
556 ** in other words if the SELECT pulls all columns from a single table
557 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
558 ** if <table2> and <table1> are distinct tables but have identical
559 ** schemas, including all the same indices, then a special optimization
560 ** is invoked that copies raw records from <table2> over to <table1>.
561 ** See the xferOptimization() function for the implementation of this
562 ** template. This is the 2nd template.
563 **
564 ** open a write cursor to <table>
565 ** open read cursor on <table2>
566 ** transfer all records in <table2> over to <table>
567 ** close cursors
568 ** foreach index on <table>
569 ** open a write cursor on the <table> index
570 ** open a read cursor on the corresponding <table2> index
571 ** transfer all records from the read to the write cursors
572 ** close cursors
573 ** end foreach
574 **
575 ** The 3rd template is for when the second template does not apply
576 ** and the SELECT clause does not read from <table> at any time.
577 ** The generated code follows this template:
578 **
579 ** X <- A
580 ** goto B
581 ** A: setup for the SELECT
582 ** loop over the rows in the SELECT
583 ** load values into registers R..R+n
584 ** yield X
585 ** end loop
586 ** cleanup after the SELECT
587 ** end-coroutine X
588 ** B: open write cursor to <table> and its indices
589 ** C: yield X, at EOF goto D
590 ** insert the select result into <table> from R..R+n
591 ** goto C
592 ** D: cleanup
593 **
594 ** The 4th template is used if the insert statement takes its
595 ** values from a SELECT but the data is being inserted into a table
596 ** that is also read as part of the SELECT. In the third form,
597 ** we have to use an intermediate table to store the results of
598 ** the select. The template is like this:
599 **
600 ** X <- A
601 ** goto B
602 ** A: setup for the SELECT
603 ** loop over the tables in the SELECT
604 ** load value into register R..R+n
605 ** yield X
606 ** end loop
607 ** cleanup after the SELECT
608 ** end co-routine R
609 ** B: open temp table
610 ** L: yield X, at EOF goto M
611 ** insert row from R..R+n into temp table
612 ** goto L
613 ** M: open write cursor to <table> and its indices
614 ** rewind temp table
615 ** C: loop over rows of intermediate table
616 ** transfer values form intermediate table into <table>
617 ** end loop
618 ** D: cleanup
619 */
sqlite3Insert(Parse * pParse,SrcList * pTabList,Select * pSelect,IdList * pColumn,int onError,Upsert * pUpsert)620 void sqlite3Insert(
621 Parse *pParse, /* Parser context */
622 SrcList *pTabList, /* Name of table into which we are inserting */
623 Select *pSelect, /* A SELECT statement to use as the data source */
624 IdList *pColumn, /* Column names corresponding to IDLIST, or NULL. */
625 int onError, /* How to handle constraint errors */
626 Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */
627 ){
628 sqlite3 *db; /* The main database structure */
629 Table *pTab; /* The table to insert into. aka TABLE */
630 int i, j; /* Loop counters */
631 Vdbe *v; /* Generate code into this virtual machine */
632 Index *pIdx; /* For looping over indices of the table */
633 int nColumn; /* Number of columns in the data */
634 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */
635 int iDataCur = 0; /* VDBE cursor that is the main data repository */
636 int iIdxCur = 0; /* First index cursor */
637 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
638 int endOfLoop; /* Label for the end of the insertion loop */
639 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */
640 int addrInsTop = 0; /* Jump to label "D" */
641 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
642 SelectDest dest; /* Destination for SELECT on rhs of INSERT */
643 int iDb; /* Index of database holding TABLE */
644 u8 useTempTable = 0; /* Store SELECT results in intermediate table */
645 u8 appendFlag = 0; /* True if the insert is likely to be an append */
646 u8 withoutRowid; /* 0 for normal table. 1 for WITHOUT ROWID table */
647 u8 bIdListInOrder; /* True if IDLIST is in table order */
648 ExprList *pList = 0; /* List of VALUES() to be inserted */
649 int iRegStore; /* Register in which to store next column */
650
651 /* Register allocations */
652 int regFromSelect = 0;/* Base register for data coming from SELECT */
653 int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */
654 int regRowCount = 0; /* Memory cell used for the row counter */
655 int regIns; /* Block of regs holding rowid+data being inserted */
656 int regRowid; /* registers holding insert rowid */
657 int regData; /* register holding first column to insert */
658 int *aRegIdx = 0; /* One register allocated to each index */
659
660 #ifndef SQLITE_OMIT_TRIGGER
661 int isView; /* True if attempting to insert into a view */
662 Trigger *pTrigger; /* List of triggers on pTab, if required */
663 int tmask; /* Mask of trigger times */
664 #endif
665
666 db = pParse->db;
667 if( pParse->nErr || db->mallocFailed ){
668 goto insert_cleanup;
669 }
670 dest.iSDParm = 0; /* Suppress a harmless compiler warning */
671
672 /* If the Select object is really just a simple VALUES() list with a
673 ** single row (the common case) then keep that one row of values
674 ** and discard the other (unused) parts of the pSelect object
675 */
676 if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){
677 pList = pSelect->pEList;
678 pSelect->pEList = 0;
679 sqlite3SelectDelete(db, pSelect);
680 pSelect = 0;
681 }
682
683 /* Locate the table into which we will be inserting new information.
684 */
685 assert( pTabList->nSrc==1 );
686 pTab = sqlite3SrcListLookup(pParse, pTabList);
687 if( pTab==0 ){
688 goto insert_cleanup;
689 }
690 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
691 assert( iDb<db->nDb );
692 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0,
693 db->aDb[iDb].zDbSName) ){
694 goto insert_cleanup;
695 }
696 withoutRowid = !HasRowid(pTab);
697
698 /* Figure out if we have any triggers and if the table being
699 ** inserted into is a view
700 */
701 #ifndef SQLITE_OMIT_TRIGGER
702 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);
703 isView = pTab->pSelect!=0;
704 #else
705 # define pTrigger 0
706 # define tmask 0
707 # define isView 0
708 #endif
709 #ifdef SQLITE_OMIT_VIEW
710 # undef isView
711 # define isView 0
712 #endif
713 assert( (pTrigger && tmask) || (pTrigger==0 && tmask==0) );
714
715 /* If pTab is really a view, make sure it has been initialized.
716 ** ViewGetColumnNames() is a no-op if pTab is not a view.
717 */
718 if( sqlite3ViewGetColumnNames(pParse, pTab) ){
719 goto insert_cleanup;
720 }
721
722 /* Cannot insert into a read-only table.
723 */
724 if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
725 goto insert_cleanup;
726 }
727
728 /* Allocate a VDBE
729 */
730 v = sqlite3GetVdbe(pParse);
731 if( v==0 ) goto insert_cleanup;
732 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
733 sqlite3BeginWriteOperation(pParse, pSelect || pTrigger, iDb);
734
735 #ifndef SQLITE_OMIT_XFER_OPT
736 /* If the statement is of the form
737 **
738 ** INSERT INTO <table1> SELECT * FROM <table2>;
739 **
740 ** Then special optimizations can be applied that make the transfer
741 ** very fast and which reduce fragmentation of indices.
742 **
743 ** This is the 2nd template.
744 */
745 if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
746 assert( !pTrigger );
747 assert( pList==0 );
748 goto insert_end;
749 }
750 #endif /* SQLITE_OMIT_XFER_OPT */
751
752 /* If this is an AUTOINCREMENT table, look up the sequence number in the
753 ** sqlite_sequence table and store it in memory cell regAutoinc.
754 */
755 regAutoinc = autoIncBegin(pParse, iDb, pTab);
756
757 /* Allocate a block registers to hold the rowid and the values
758 ** for all columns of the new row.
759 */
760 regRowid = regIns = pParse->nMem+1;
761 pParse->nMem += pTab->nCol + 1;
762 if( IsVirtual(pTab) ){
763 regRowid++;
764 pParse->nMem++;
765 }
766 regData = regRowid+1;
767
768 /* If the INSERT statement included an IDLIST term, then make sure
769 ** all elements of the IDLIST really are columns of the table and
770 ** remember the column indices.
771 **
772 ** If the table has an INTEGER PRIMARY KEY column and that column
773 ** is named in the IDLIST, then record in the ipkColumn variable
774 ** the index into IDLIST of the primary key column. ipkColumn is
775 ** the index of the primary key as it appears in IDLIST, not as
776 ** is appears in the original table. (The index of the INTEGER
777 ** PRIMARY KEY in the original table is pTab->iPKey.) After this
778 ** loop, if ipkColumn==(-1), that means that integer primary key
779 ** is unspecified, and hence the table is either WITHOUT ROWID or
780 ** it will automatically generated an integer primary key.
781 **
782 ** bIdListInOrder is true if the columns in IDLIST are in storage
783 ** order. This enables an optimization that avoids shuffling the
784 ** columns into storage order. False negatives are harmless,
785 ** but false positives will cause database corruption.
786 */
787 bIdListInOrder = (pTab->tabFlags & (TF_OOOHidden|TF_HasStored))==0;
788 if( pColumn ){
789 for(i=0; i<pColumn->nId; i++){
790 pColumn->a[i].idx = -1;
791 }
792 for(i=0; i<pColumn->nId; i++){
793 for(j=0; j<pTab->nCol; j++){
794 if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
795 pColumn->a[i].idx = j;
796 if( i!=j ) bIdListInOrder = 0;
797 if( j==pTab->iPKey ){
798 ipkColumn = i; assert( !withoutRowid );
799 }
800 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
801 if( pTab->aCol[j].colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL) ){
802 sqlite3ErrorMsg(pParse,
803 "cannot INSERT into generated column \"%s\"",
804 pTab->aCol[j].zName);
805 goto insert_cleanup;
806 }
807 #endif
808 break;
809 }
810 }
811 if( j>=pTab->nCol ){
812 if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
813 ipkColumn = i;
814 bIdListInOrder = 0;
815 }else{
816 sqlite3ErrorMsg(pParse, "table %S has no column named %s",
817 pTabList, 0, pColumn->a[i].zName);
818 pParse->checkSchema = 1;
819 goto insert_cleanup;
820 }
821 }
822 }
823 }
824
825 /* Figure out how many columns of data are supplied. If the data
826 ** is coming from a SELECT statement, then generate a co-routine that
827 ** produces a single row of the SELECT on each invocation. The
828 ** co-routine is the common header to the 3rd and 4th templates.
829 */
830 if( pSelect ){
831 /* Data is coming from a SELECT or from a multi-row VALUES clause.
832 ** Generate a co-routine to run the SELECT. */
833 int regYield; /* Register holding co-routine entry-point */
834 int addrTop; /* Top of the co-routine */
835 int rc; /* Result code */
836
837 regYield = ++pParse->nMem;
838 addrTop = sqlite3VdbeCurrentAddr(v) + 1;
839 sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
840 sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
841 dest.iSdst = bIdListInOrder ? regData : 0;
842 dest.nSdst = pTab->nCol;
843 rc = sqlite3Select(pParse, pSelect, &dest);
844 regFromSelect = dest.iSdst;
845 if( rc || db->mallocFailed || pParse->nErr ) goto insert_cleanup;
846 sqlite3VdbeEndCoroutine(v, regYield);
847 sqlite3VdbeJumpHere(v, addrTop - 1); /* label B: */
848 assert( pSelect->pEList );
849 nColumn = pSelect->pEList->nExpr;
850
851 /* Set useTempTable to TRUE if the result of the SELECT statement
852 ** should be written into a temporary table (template 4). Set to
853 ** FALSE if each output row of the SELECT can be written directly into
854 ** the destination table (template 3).
855 **
856 ** A temp table must be used if the table being updated is also one
857 ** of the tables being read by the SELECT statement. Also use a
858 ** temp table in the case of row triggers.
859 */
860 if( pTrigger || readsTable(pParse, iDb, pTab) ){
861 useTempTable = 1;
862 }
863
864 if( useTempTable ){
865 /* Invoke the coroutine to extract information from the SELECT
866 ** and add it to a transient table srcTab. The code generated
867 ** here is from the 4th template:
868 **
869 ** B: open temp table
870 ** L: yield X, goto M at EOF
871 ** insert row from R..R+n into temp table
872 ** goto L
873 ** M: ...
874 */
875 int regRec; /* Register to hold packed record */
876 int regTempRowid; /* Register to hold temp table ROWID */
877 int addrL; /* Label "L" */
878
879 srcTab = pParse->nTab++;
880 regRec = sqlite3GetTempReg(pParse);
881 regTempRowid = sqlite3GetTempReg(pParse);
882 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn);
883 addrL = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); VdbeCoverage(v);
884 sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec);
885 sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regTempRowid);
886 sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid);
887 sqlite3VdbeGoto(v, addrL);
888 sqlite3VdbeJumpHere(v, addrL);
889 sqlite3ReleaseTempReg(pParse, regRec);
890 sqlite3ReleaseTempReg(pParse, regTempRowid);
891 }
892 }else{
893 /* This is the case if the data for the INSERT is coming from a
894 ** single-row VALUES clause
895 */
896 NameContext sNC;
897 memset(&sNC, 0, sizeof(sNC));
898 sNC.pParse = pParse;
899 srcTab = -1;
900 assert( useTempTable==0 );
901 if( pList ){
902 nColumn = pList->nExpr;
903 if( sqlite3ResolveExprListNames(&sNC, pList) ){
904 goto insert_cleanup;
905 }
906 }else{
907 nColumn = 0;
908 }
909 }
910
911 /* If there is no IDLIST term but the table has an integer primary
912 ** key, the set the ipkColumn variable to the integer primary key
913 ** column index in the original table definition.
914 */
915 if( pColumn==0 && nColumn>0 ){
916 ipkColumn = pTab->iPKey;
917 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
918 if( ipkColumn>=0 && (pTab->tabFlags & TF_HasGenerated)!=0 ){
919 testcase( pTab->tabFlags & TF_HasVirtual );
920 testcase( pTab->tabFlags & TF_HasStored );
921 for(i=ipkColumn-1; i>=0; i--){
922 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
923 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
924 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
925 ipkColumn--;
926 }
927 }
928 }
929 #endif
930 }
931
932 /* Make sure the number of columns in the source data matches the number
933 ** of columns to be inserted into the table.
934 */
935 for(i=0; i<pTab->nCol; i++){
936 if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ) nHidden++;
937 }
938 if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
939 sqlite3ErrorMsg(pParse,
940 "table %S has %d columns but %d values were supplied",
941 pTabList, 0, pTab->nCol-nHidden, nColumn);
942 goto insert_cleanup;
943 }
944 if( pColumn!=0 && nColumn!=pColumn->nId ){
945 sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
946 goto insert_cleanup;
947 }
948
949 /* Initialize the count of rows to be inserted
950 */
951 if( (db->flags & SQLITE_CountRows)!=0
952 && !pParse->nested
953 && !pParse->pTriggerTab
954 ){
955 regRowCount = ++pParse->nMem;
956 sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
957 }
958
959 /* If this is not a view, open the table and and all indices */
960 if( !isView ){
961 int nIdx;
962 nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, -1, 0,
963 &iDataCur, &iIdxCur);
964 aRegIdx = sqlite3DbMallocRawNN(db, sizeof(int)*(nIdx+2));
965 if( aRegIdx==0 ){
966 goto insert_cleanup;
967 }
968 for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
969 assert( pIdx );
970 aRegIdx[i] = ++pParse->nMem;
971 pParse->nMem += pIdx->nColumn;
972 }
973 aRegIdx[i] = ++pParse->nMem; /* Register to store the table record */
974 }
975 #ifndef SQLITE_OMIT_UPSERT
976 if( pUpsert ){
977 if( IsVirtual(pTab) ){
978 sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"",
979 pTab->zName);
980 goto insert_cleanup;
981 }
982 if( pTab->pSelect ){
983 sqlite3ErrorMsg(pParse, "cannot UPSERT a view");
984 goto insert_cleanup;
985 }
986 if( sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget) ){
987 goto insert_cleanup;
988 }
989 pTabList->a[0].iCursor = iDataCur;
990 pUpsert->pUpsertSrc = pTabList;
991 pUpsert->regData = regData;
992 pUpsert->iDataCur = iDataCur;
993 pUpsert->iIdxCur = iIdxCur;
994 if( pUpsert->pUpsertTarget ){
995 sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
996 }
997 }
998 #endif
999
1000
1001 /* This is the top of the main insertion loop */
1002 if( useTempTable ){
1003 /* This block codes the top of loop only. The complete loop is the
1004 ** following pseudocode (template 4):
1005 **
1006 ** rewind temp table, if empty goto D
1007 ** C: loop over rows of intermediate table
1008 ** transfer values form intermediate table into <table>
1009 ** end loop
1010 ** D: ...
1011 */
1012 addrInsTop = sqlite3VdbeAddOp1(v, OP_Rewind, srcTab); VdbeCoverage(v);
1013 addrCont = sqlite3VdbeCurrentAddr(v);
1014 }else if( pSelect ){
1015 /* This block codes the top of loop only. The complete loop is the
1016 ** following pseudocode (template 3):
1017 **
1018 ** C: yield X, at EOF goto D
1019 ** insert the select result into <table> from R..R+n
1020 ** goto C
1021 ** D: ...
1022 */
1023 sqlite3VdbeReleaseRegisters(pParse, regData, pTab->nCol, 0, 0);
1024 addrInsTop = addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm);
1025 VdbeCoverage(v);
1026 if( ipkColumn>=0 ){
1027 /* tag-20191021-001: If the INTEGER PRIMARY KEY is being generated by the
1028 ** SELECT, go ahead and copy the value into the rowid slot now, so that
1029 ** the value does not get overwritten by a NULL at tag-20191021-002. */
1030 sqlite3VdbeAddOp2(v, OP_Copy, regFromSelect+ipkColumn, regRowid);
1031 }
1032 }
1033
1034 /* Compute data for ordinary columns of the new entry. Values
1035 ** are written in storage order into registers starting with regData.
1036 ** Only ordinary columns are computed in this loop. The rowid
1037 ** (if there is one) is computed later and generated columns are
1038 ** computed after the rowid since they might depend on the value
1039 ** of the rowid.
1040 */
1041 nHidden = 0;
1042 iRegStore = regData; assert( regData==regRowid+1 );
1043 for(i=0; i<pTab->nCol; i++, iRegStore++){
1044 int k;
1045 u32 colFlags;
1046 assert( i>=nHidden );
1047 if( i==pTab->iPKey ){
1048 /* tag-20191021-002: References to the INTEGER PRIMARY KEY are filled
1049 ** using the rowid. So put a NULL in the IPK slot of the record to avoid
1050 ** using excess space. The file format definition requires this extra
1051 ** NULL - we cannot optimize further by skipping the column completely */
1052 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
1053 continue;
1054 }
1055 if( ((colFlags = pTab->aCol[i].colFlags) & COLFLAG_NOINSERT)!=0 ){
1056 nHidden++;
1057 if( (colFlags & COLFLAG_VIRTUAL)!=0 ){
1058 /* Virtual columns do not participate in OP_MakeRecord. So back up
1059 ** iRegStore by one slot to compensate for the iRegStore++ in the
1060 ** outer for() loop */
1061 iRegStore--;
1062 continue;
1063 }else if( (colFlags & COLFLAG_STORED)!=0 ){
1064 /* Stored columns are computed later. But if there are BEFORE
1065 ** triggers, the slots used for stored columns will be OP_Copy-ed
1066 ** to a second block of registers, so the register needs to be
1067 ** initialized to NULL to avoid an uninitialized register read */
1068 if( tmask & TRIGGER_BEFORE ){
1069 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
1070 }
1071 continue;
1072 }else if( pColumn==0 ){
1073 /* Hidden columns that are not explicitly named in the INSERT
1074 ** get there default value */
1075 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
1076 continue;
1077 }
1078 }
1079 if( pColumn ){
1080 for(j=0; j<pColumn->nId && pColumn->a[j].idx!=i; j++){}
1081 if( j>=pColumn->nId ){
1082 /* A column not named in the insert column list gets its
1083 ** default value */
1084 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
1085 continue;
1086 }
1087 k = j;
1088 }else if( nColumn==0 ){
1089 /* This is INSERT INTO ... DEFAULT VALUES. Load the default value. */
1090 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
1091 continue;
1092 }else{
1093 k = i - nHidden;
1094 }
1095
1096 if( useTempTable ){
1097 sqlite3VdbeAddOp3(v, OP_Column, srcTab, k, iRegStore);
1098 }else if( pSelect ){
1099 if( regFromSelect!=regData ){
1100 sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+k, iRegStore);
1101 }
1102 }else{
1103 sqlite3ExprCode(pParse, pList->a[k].pExpr, iRegStore);
1104 }
1105 }
1106
1107
1108 /* Run the BEFORE and INSTEAD OF triggers, if there are any
1109 */
1110 endOfLoop = sqlite3VdbeMakeLabel(pParse);
1111 if( tmask & TRIGGER_BEFORE ){
1112 int regCols = sqlite3GetTempRange(pParse, pTab->nCol+1);
1113
1114 /* build the NEW.* reference row. Note that if there is an INTEGER
1115 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
1116 ** translated into a unique ID for the row. But on a BEFORE trigger,
1117 ** we do not know what the unique ID will be (because the insert has
1118 ** not happened yet) so we substitute a rowid of -1
1119 */
1120 if( ipkColumn<0 ){
1121 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols);
1122 }else{
1123 int addr1;
1124 assert( !withoutRowid );
1125 if( useTempTable ){
1126 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regCols);
1127 }else{
1128 assert( pSelect==0 ); /* Otherwise useTempTable is true */
1129 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regCols);
1130 }
1131 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regCols); VdbeCoverage(v);
1132 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols);
1133 sqlite3VdbeJumpHere(v, addr1);
1134 sqlite3VdbeAddOp1(v, OP_MustBeInt, regCols); VdbeCoverage(v);
1135 }
1136
1137 /* Cannot have triggers on a virtual table. If it were possible,
1138 ** this block would have to account for hidden column.
1139 */
1140 assert( !IsVirtual(pTab) );
1141
1142 /* Copy the new data already generated. */
1143 assert( pTab->nNVCol>0 );
1144 sqlite3VdbeAddOp3(v, OP_Copy, regRowid+1, regCols+1, pTab->nNVCol-1);
1145
1146 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1147 /* Compute the new value for generated columns after all other
1148 ** columns have already been computed. This must be done after
1149 ** computing the ROWID in case one of the generated columns
1150 ** refers to the ROWID. */
1151 if( pTab->tabFlags & TF_HasGenerated ){
1152 testcase( pTab->tabFlags & TF_HasVirtual );
1153 testcase( pTab->tabFlags & TF_HasStored );
1154 sqlite3ComputeGeneratedColumns(pParse, regCols+1, pTab);
1155 }
1156 #endif
1157
1158 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
1159 ** do not attempt any conversions before assembling the record.
1160 ** If this is a real table, attempt conversions as required by the
1161 ** table column affinities.
1162 */
1163 if( !isView ){
1164 sqlite3TableAffinity(v, pTab, regCols+1);
1165 }
1166
1167 /* Fire BEFORE or INSTEAD OF triggers */
1168 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_BEFORE,
1169 pTab, regCols-pTab->nCol-1, onError, endOfLoop);
1170
1171 sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol+1);
1172 }
1173
1174 if( !isView ){
1175 if( IsVirtual(pTab) ){
1176 /* The row that the VUpdate opcode will delete: none */
1177 sqlite3VdbeAddOp2(v, OP_Null, 0, regIns);
1178 }
1179 if( ipkColumn>=0 ){
1180 /* Compute the new rowid */
1181 if( useTempTable ){
1182 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regRowid);
1183 }else if( pSelect ){
1184 /* Rowid already initialized at tag-20191021-001 */
1185 }else{
1186 Expr *pIpk = pList->a[ipkColumn].pExpr;
1187 if( pIpk->op==TK_NULL && !IsVirtual(pTab) ){
1188 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
1189 appendFlag = 1;
1190 }else{
1191 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regRowid);
1192 }
1193 }
1194 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
1195 ** to generate a unique primary key value.
1196 */
1197 if( !appendFlag ){
1198 int addr1;
1199 if( !IsVirtual(pTab) ){
1200 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid); VdbeCoverage(v);
1201 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
1202 sqlite3VdbeJumpHere(v, addr1);
1203 }else{
1204 addr1 = sqlite3VdbeCurrentAddr(v);
1205 sqlite3VdbeAddOp2(v, OP_IsNull, regRowid, addr1+2); VdbeCoverage(v);
1206 }
1207 sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid); VdbeCoverage(v);
1208 }
1209 }else if( IsVirtual(pTab) || withoutRowid ){
1210 sqlite3VdbeAddOp2(v, OP_Null, 0, regRowid);
1211 }else{
1212 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
1213 appendFlag = 1;
1214 }
1215 autoIncStep(pParse, regAutoinc, regRowid);
1216
1217 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1218 /* Compute the new value for generated columns after all other
1219 ** columns have already been computed. This must be done after
1220 ** computing the ROWID in case one of the generated columns
1221 ** is derived from the INTEGER PRIMARY KEY. */
1222 if( pTab->tabFlags & TF_HasGenerated ){
1223 sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab);
1224 }
1225 #endif
1226
1227 /* Generate code to check constraints and generate index keys and
1228 ** do the insertion.
1229 */
1230 #ifndef SQLITE_OMIT_VIRTUALTABLE
1231 if( IsVirtual(pTab) ){
1232 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
1233 sqlite3VtabMakeWritable(pParse, pTab);
1234 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
1235 sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
1236 sqlite3MayAbort(pParse);
1237 }else
1238 #endif
1239 {
1240 int isReplace; /* Set to true if constraints may cause a replace */
1241 int bUseSeek; /* True to use OPFLAG_SEEKRESULT */
1242 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
1243 regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert
1244 );
1245 sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
1246
1247 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1248 ** constraints or (b) there are no triggers and this table is not a
1249 ** parent table in a foreign key constraint. It is safe to set the
1250 ** flag in the second case as if any REPLACE constraint is hit, an
1251 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1252 ** cursor that is disturbed. And these instructions both clear the
1253 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1254 ** functionality. */
1255 bUseSeek = (isReplace==0 || !sqlite3VdbeHasSubProgram(v));
1256 sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
1257 regIns, aRegIdx, 0, appendFlag, bUseSeek
1258 );
1259 }
1260 }
1261
1262 /* Update the count of rows that are inserted
1263 */
1264 if( regRowCount ){
1265 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
1266 }
1267
1268 if( pTrigger ){
1269 /* Code AFTER triggers */
1270 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER,
1271 pTab, regData-2-pTab->nCol, onError, endOfLoop);
1272 }
1273
1274 /* The bottom of the main insertion loop, if the data source
1275 ** is a SELECT statement.
1276 */
1277 sqlite3VdbeResolveLabel(v, endOfLoop);
1278 if( useTempTable ){
1279 sqlite3VdbeAddOp2(v, OP_Next, srcTab, addrCont); VdbeCoverage(v);
1280 sqlite3VdbeJumpHere(v, addrInsTop);
1281 sqlite3VdbeAddOp1(v, OP_Close, srcTab);
1282 }else if( pSelect ){
1283 sqlite3VdbeGoto(v, addrCont);
1284 #ifdef SQLITE_DEBUG
1285 /* If we are jumping back to an OP_Yield that is preceded by an
1286 ** OP_ReleaseReg, set the p5 flag on the OP_Goto so that the
1287 ** OP_ReleaseReg will be included in the loop. */
1288 if( sqlite3VdbeGetOp(v, addrCont-1)->opcode==OP_ReleaseReg ){
1289 assert( sqlite3VdbeGetOp(v, addrCont)->opcode==OP_Yield );
1290 sqlite3VdbeChangeP5(v, 1);
1291 }
1292 #endif
1293 sqlite3VdbeJumpHere(v, addrInsTop);
1294 }
1295
1296 insert_end:
1297 /* Update the sqlite_sequence table by storing the content of the
1298 ** maximum rowid counter values recorded while inserting into
1299 ** autoincrement tables.
1300 */
1301 if( pParse->nested==0 && pParse->pTriggerTab==0 ){
1302 sqlite3AutoincrementEnd(pParse);
1303 }
1304
1305 /*
1306 ** Return the number of rows inserted. If this routine is
1307 ** generating code because of a call to sqlite3NestedParse(), do not
1308 ** invoke the callback function.
1309 */
1310 if( regRowCount ){
1311 sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
1312 sqlite3VdbeSetNumCols(v, 1);
1313 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
1314 }
1315
1316 insert_cleanup:
1317 sqlite3SrcListDelete(db, pTabList);
1318 sqlite3ExprListDelete(db, pList);
1319 sqlite3UpsertDelete(db, pUpsert);
1320 sqlite3SelectDelete(db, pSelect);
1321 sqlite3IdListDelete(db, pColumn);
1322 sqlite3DbFree(db, aRegIdx);
1323 }
1324
1325 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1326 ** they may interfere with compilation of other functions in this file
1327 ** (or in another file, if this file becomes part of the amalgamation). */
1328 #ifdef isView
1329 #undef isView
1330 #endif
1331 #ifdef pTrigger
1332 #undef pTrigger
1333 #endif
1334 #ifdef tmask
1335 #undef tmask
1336 #endif
1337
1338 /*
1339 ** Meanings of bits in of pWalker->eCode for
1340 ** sqlite3ExprReferencesUpdatedColumn()
1341 */
1342 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1343 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1344
1345 /* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
1346 * Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
1347 ** expression node references any of the
1348 ** columns that are being modifed by an UPDATE statement.
1349 */
checkConstraintExprNode(Walker * pWalker,Expr * pExpr)1350 static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
1351 if( pExpr->op==TK_COLUMN ){
1352 assert( pExpr->iColumn>=0 || pExpr->iColumn==-1 );
1353 if( pExpr->iColumn>=0 ){
1354 if( pWalker->u.aiCol[pExpr->iColumn]>=0 ){
1355 pWalker->eCode |= CKCNSTRNT_COLUMN;
1356 }
1357 }else{
1358 pWalker->eCode |= CKCNSTRNT_ROWID;
1359 }
1360 }
1361 return WRC_Continue;
1362 }
1363
1364 /*
1365 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1366 ** only columns that are modified by the UPDATE are those for which
1367 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1368 **
1369 ** Return true if CHECK constraint pExpr uses any of the
1370 ** changing columns (or the rowid if it is changing). In other words,
1371 ** return true if this CHECK constraint must be validated for
1372 ** the new row in the UPDATE statement.
1373 **
1374 ** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
1375 ** The operation of this routine is the same - return true if an only if
1376 ** the expression uses one or more of columns identified by the second and
1377 ** third arguments.
1378 */
sqlite3ExprReferencesUpdatedColumn(Expr * pExpr,int * aiChng,int chngRowid)1379 int sqlite3ExprReferencesUpdatedColumn(
1380 Expr *pExpr, /* The expression to be checked */
1381 int *aiChng, /* aiChng[x]>=0 if column x changed by the UPDATE */
1382 int chngRowid /* True if UPDATE changes the rowid */
1383 ){
1384 Walker w;
1385 memset(&w, 0, sizeof(w));
1386 w.eCode = 0;
1387 w.xExprCallback = checkConstraintExprNode;
1388 w.u.aiCol = aiChng;
1389 sqlite3WalkExpr(&w, pExpr);
1390 if( !chngRowid ){
1391 testcase( (w.eCode & CKCNSTRNT_ROWID)!=0 );
1392 w.eCode &= ~CKCNSTRNT_ROWID;
1393 }
1394 testcase( w.eCode==0 );
1395 testcase( w.eCode==CKCNSTRNT_COLUMN );
1396 testcase( w.eCode==CKCNSTRNT_ROWID );
1397 testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
1398 return w.eCode!=0;
1399 }
1400
1401 /*
1402 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1403 ** on table pTab.
1404 **
1405 ** The regNewData parameter is the first register in a range that contains
1406 ** the data to be inserted or the data after the update. There will be
1407 ** pTab->nCol+1 registers in this range. The first register (the one
1408 ** that regNewData points to) will contain the new rowid, or NULL in the
1409 ** case of a WITHOUT ROWID table. The second register in the range will
1410 ** contain the content of the first table column. The third register will
1411 ** contain the content of the second table column. And so forth.
1412 **
1413 ** The regOldData parameter is similar to regNewData except that it contains
1414 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1415 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1416 ** checking regOldData for zero.
1417 **
1418 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1419 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1420 ** might be modified by the UPDATE. If pkChng is false, then the key of
1421 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1422 **
1423 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1424 ** was explicitly specified as part of the INSERT statement. If pkChng
1425 ** is zero, it means that the either rowid is computed automatically or
1426 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1427 ** pkChng will only be true if the INSERT statement provides an integer
1428 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1429 **
1430 ** The code generated by this routine will store new index entries into
1431 ** registers identified by aRegIdx[]. No index entry is created for
1432 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1433 ** the same as the order of indices on the linked list of indices
1434 ** at pTab->pIndex.
1435 **
1436 ** (2019-05-07) The generated code also creates a new record for the
1437 ** main table, if pTab is a rowid table, and stores that record in the
1438 ** register identified by aRegIdx[nIdx] - in other words in the first
1439 ** entry of aRegIdx[] past the last index. It is important that the
1440 ** record be generated during constraint checks to avoid affinity changes
1441 ** to the register content that occur after constraint checks but before
1442 ** the new record is inserted.
1443 **
1444 ** The caller must have already opened writeable cursors on the main
1445 ** table and all applicable indices (that is to say, all indices for which
1446 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1447 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1448 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1449 ** for the first index in the pTab->pIndex list. Cursors for other indices
1450 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1451 **
1452 ** This routine also generates code to check constraints. NOT NULL,
1453 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1454 ** then the appropriate action is performed. There are five possible
1455 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1456 **
1457 ** Constraint type Action What Happens
1458 ** --------------- ---------- ----------------------------------------
1459 ** any ROLLBACK The current transaction is rolled back and
1460 ** sqlite3_step() returns immediately with a
1461 ** return code of SQLITE_CONSTRAINT.
1462 **
1463 ** any ABORT Back out changes from the current command
1464 ** only (do not do a complete rollback) then
1465 ** cause sqlite3_step() to return immediately
1466 ** with SQLITE_CONSTRAINT.
1467 **
1468 ** any FAIL Sqlite3_step() returns immediately with a
1469 ** return code of SQLITE_CONSTRAINT. The
1470 ** transaction is not rolled back and any
1471 ** changes to prior rows are retained.
1472 **
1473 ** any IGNORE The attempt in insert or update the current
1474 ** row is skipped, without throwing an error.
1475 ** Processing continues with the next row.
1476 ** (There is an immediate jump to ignoreDest.)
1477 **
1478 ** NOT NULL REPLACE The NULL value is replace by the default
1479 ** value for that column. If the default value
1480 ** is NULL, the action is the same as ABORT.
1481 **
1482 ** UNIQUE REPLACE The other row that conflicts with the row
1483 ** being inserted is removed.
1484 **
1485 ** CHECK REPLACE Illegal. The results in an exception.
1486 **
1487 ** Which action to take is determined by the overrideError parameter.
1488 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1489 ** is used. Or if pParse->onError==OE_Default then the onError value
1490 ** for the constraint is used.
1491 */
sqlite3GenerateConstraintChecks(Parse * pParse,Table * pTab,int * aRegIdx,int iDataCur,int iIdxCur,int regNewData,int regOldData,u8 pkChng,u8 overrideError,int ignoreDest,int * pbMayReplace,int * aiChng,Upsert * pUpsert)1492 void sqlite3GenerateConstraintChecks(
1493 Parse *pParse, /* The parser context */
1494 Table *pTab, /* The table being inserted or updated */
1495 int *aRegIdx, /* Use register aRegIdx[i] for index i. 0 for unused */
1496 int iDataCur, /* Canonical data cursor (main table or PK index) */
1497 int iIdxCur, /* First index cursor */
1498 int regNewData, /* First register in a range holding values to insert */
1499 int regOldData, /* Previous content. 0 for INSERTs */
1500 u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */
1501 u8 overrideError, /* Override onError to this if not OE_Default */
1502 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */
1503 int *pbMayReplace, /* OUT: Set to true if constraint may cause a replace */
1504 int *aiChng, /* column i is unchanged if aiChng[i]<0 */
1505 Upsert *pUpsert /* ON CONFLICT clauses, if any. NULL otherwise */
1506 ){
1507 Vdbe *v; /* VDBE under constrution */
1508 Index *pIdx; /* Pointer to one of the indices */
1509 Index *pPk = 0; /* The PRIMARY KEY index */
1510 sqlite3 *db; /* Database connection */
1511 int i; /* loop counter */
1512 int ix; /* Index loop counter */
1513 int nCol; /* Number of columns */
1514 int onError; /* Conflict resolution strategy */
1515 int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
1516 int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
1517 Index *pUpIdx = 0; /* Index to which to apply the upsert */
1518 u8 isUpdate; /* True if this is an UPDATE operation */
1519 u8 bAffinityDone = 0; /* True if the OP_Affinity operation has been run */
1520 int upsertBypass = 0; /* Address of Goto to bypass upsert subroutine */
1521 int upsertJump = 0; /* Address of Goto that jumps into upsert subroutine */
1522 int ipkTop = 0; /* Top of the IPK uniqueness check */
1523 int ipkBottom = 0; /* OP_Goto at the end of the IPK uniqueness check */
1524 /* Variables associated with retesting uniqueness constraints after
1525 ** replace triggers fire have run */
1526 int regTrigCnt; /* Register used to count replace trigger invocations */
1527 int addrRecheck = 0; /* Jump here to recheck all uniqueness constraints */
1528 int lblRecheckOk = 0; /* Each recheck jumps to this label if it passes */
1529 Trigger *pTrigger; /* List of DELETE triggers on the table pTab */
1530 int nReplaceTrig = 0; /* Number of replace triggers coded */
1531
1532 isUpdate = regOldData!=0;
1533 db = pParse->db;
1534 v = sqlite3GetVdbe(pParse);
1535 assert( v!=0 );
1536 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
1537 nCol = pTab->nCol;
1538
1539 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1540 ** normal rowid tables. nPkField is the number of key fields in the
1541 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1542 ** number of fields in the true primary key of the table. */
1543 if( HasRowid(pTab) ){
1544 pPk = 0;
1545 nPkField = 1;
1546 }else{
1547 pPk = sqlite3PrimaryKeyIndex(pTab);
1548 nPkField = pPk->nKeyCol;
1549 }
1550
1551 /* Record that this module has started */
1552 VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
1553 iDataCur, iIdxCur, regNewData, regOldData, pkChng));
1554
1555 /* Test all NOT NULL constraints.
1556 */
1557 if( pTab->tabFlags & TF_HasNotNull ){
1558 int b2ndPass = 0; /* True if currently running 2nd pass */
1559 int nSeenReplace = 0; /* Number of ON CONFLICT REPLACE operations */
1560 int nGenerated = 0; /* Number of generated columns with NOT NULL */
1561 while(1){ /* Make 2 passes over columns. Exit loop via "break" */
1562 for(i=0; i<nCol; i++){
1563 int iReg; /* Register holding column value */
1564 Column *pCol = &pTab->aCol[i]; /* The column to check for NOT NULL */
1565 int isGenerated; /* non-zero if column is generated */
1566 onError = pCol->notNull;
1567 if( onError==OE_None ) continue; /* No NOT NULL on this column */
1568 if( i==pTab->iPKey ){
1569 continue; /* ROWID is never NULL */
1570 }
1571 isGenerated = pCol->colFlags & COLFLAG_GENERATED;
1572 if( isGenerated && !b2ndPass ){
1573 nGenerated++;
1574 continue; /* Generated columns processed on 2nd pass */
1575 }
1576 if( aiChng && aiChng[i]<0 && !isGenerated ){
1577 /* Do not check NOT NULL on columns that do not change */
1578 continue;
1579 }
1580 if( overrideError!=OE_Default ){
1581 onError = overrideError;
1582 }else if( onError==OE_Default ){
1583 onError = OE_Abort;
1584 }
1585 if( onError==OE_Replace ){
1586 if( b2ndPass /* REPLACE becomes ABORT on the 2nd pass */
1587 || pCol->pDflt==0 /* REPLACE is ABORT if no DEFAULT value */
1588 ){
1589 testcase( pCol->colFlags & COLFLAG_VIRTUAL );
1590 testcase( pCol->colFlags & COLFLAG_STORED );
1591 testcase( pCol->colFlags & COLFLAG_GENERATED );
1592 onError = OE_Abort;
1593 }else{
1594 assert( !isGenerated );
1595 }
1596 }else if( b2ndPass && !isGenerated ){
1597 continue;
1598 }
1599 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
1600 || onError==OE_Ignore || onError==OE_Replace );
1601 testcase( i!=sqlite3TableColumnToStorage(pTab, i) );
1602 iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1;
1603 switch( onError ){
1604 case OE_Replace: {
1605 int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, iReg);
1606 VdbeCoverage(v);
1607 assert( (pCol->colFlags & COLFLAG_GENERATED)==0 );
1608 nSeenReplace++;
1609 sqlite3ExprCodeCopy(pParse, pCol->pDflt, iReg);
1610 sqlite3VdbeJumpHere(v, addr1);
1611 break;
1612 }
1613 case OE_Abort:
1614 sqlite3MayAbort(pParse);
1615 /* Fall through */
1616 case OE_Rollback:
1617 case OE_Fail: {
1618 char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName,
1619 pCol->zName);
1620 sqlite3VdbeAddOp3(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL,
1621 onError, iReg);
1622 sqlite3VdbeAppendP4(v, zMsg, P4_DYNAMIC);
1623 sqlite3VdbeChangeP5(v, P5_ConstraintNotNull);
1624 VdbeCoverage(v);
1625 break;
1626 }
1627 default: {
1628 assert( onError==OE_Ignore );
1629 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, ignoreDest);
1630 VdbeCoverage(v);
1631 break;
1632 }
1633 } /* end switch(onError) */
1634 } /* end loop i over columns */
1635 if( nGenerated==0 && nSeenReplace==0 ){
1636 /* If there are no generated columns with NOT NULL constraints
1637 ** and no NOT NULL ON CONFLICT REPLACE constraints, then a single
1638 ** pass is sufficient */
1639 break;
1640 }
1641 if( b2ndPass ) break; /* Never need more than 2 passes */
1642 b2ndPass = 1;
1643 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1644 if( nSeenReplace>0 && (pTab->tabFlags & TF_HasGenerated)!=0 ){
1645 /* If any NOT NULL ON CONFLICT REPLACE constraints fired on the
1646 ** first pass, recomputed values for all generated columns, as
1647 ** those values might depend on columns affected by the REPLACE.
1648 */
1649 sqlite3ComputeGeneratedColumns(pParse, regNewData+1, pTab);
1650 }
1651 #endif
1652 } /* end of 2-pass loop */
1653 } /* end if( has-not-null-constraints ) */
1654
1655 /* Test all CHECK constraints
1656 */
1657 #ifndef SQLITE_OMIT_CHECK
1658 if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
1659 ExprList *pCheck = pTab->pCheck;
1660 pParse->iSelfTab = -(regNewData+1);
1661 onError = overrideError!=OE_Default ? overrideError : OE_Abort;
1662 for(i=0; i<pCheck->nExpr; i++){
1663 int allOk;
1664 Expr *pCopy;
1665 Expr *pExpr = pCheck->a[i].pExpr;
1666 if( aiChng
1667 && !sqlite3ExprReferencesUpdatedColumn(pExpr, aiChng, pkChng)
1668 ){
1669 /* The check constraints do not reference any of the columns being
1670 ** updated so there is no point it verifying the check constraint */
1671 continue;
1672 }
1673 allOk = sqlite3VdbeMakeLabel(pParse);
1674 sqlite3VdbeVerifyAbortable(v, onError);
1675 pCopy = sqlite3ExprDup(db, pExpr, 0);
1676 if( !db->mallocFailed ){
1677 sqlite3ExprIfTrue(pParse, pCopy, allOk, SQLITE_JUMPIFNULL);
1678 }
1679 sqlite3ExprDelete(db, pCopy);
1680 if( onError==OE_Ignore ){
1681 sqlite3VdbeGoto(v, ignoreDest);
1682 }else{
1683 char *zName = pCheck->a[i].zEName;
1684 if( zName==0 ) zName = pTab->zName;
1685 if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-26383-51744 */
1686 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
1687 onError, zName, P4_TRANSIENT,
1688 P5_ConstraintCheck);
1689 }
1690 sqlite3VdbeResolveLabel(v, allOk);
1691 }
1692 pParse->iSelfTab = 0;
1693 }
1694 #endif /* !defined(SQLITE_OMIT_CHECK) */
1695
1696 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
1697 ** order:
1698 **
1699 ** (1) OE_Update
1700 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
1701 ** (3) OE_Replace
1702 **
1703 ** OE_Fail and OE_Ignore must happen before any changes are made.
1704 ** OE_Update guarantees that only a single row will change, so it
1705 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
1706 ** could happen in any order, but they are grouped up front for
1707 ** convenience.
1708 **
1709 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
1710 ** The order of constraints used to have OE_Update as (2) and OE_Abort
1711 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
1712 ** constraint before any others, so it had to be moved.
1713 **
1714 ** Constraint checking code is generated in this order:
1715 ** (A) The rowid constraint
1716 ** (B) Unique index constraints that do not have OE_Replace as their
1717 ** default conflict resolution strategy
1718 ** (C) Unique index that do use OE_Replace by default.
1719 **
1720 ** The ordering of (2) and (3) is accomplished by making sure the linked
1721 ** list of indexes attached to a table puts all OE_Replace indexes last
1722 ** in the list. See sqlite3CreateIndex() for where that happens.
1723 */
1724
1725 if( pUpsert ){
1726 if( pUpsert->pUpsertTarget==0 ){
1727 /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
1728 ** Make all unique constraint resolution be OE_Ignore */
1729 assert( pUpsert->pUpsertSet==0 );
1730 overrideError = OE_Ignore;
1731 pUpsert = 0;
1732 }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
1733 /* If the constraint-target uniqueness check must be run first.
1734 ** Jump to that uniqueness check now */
1735 upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
1736 VdbeComment((v, "UPSERT constraint goes first"));
1737 }
1738 }
1739
1740 /* Determine if it is possible that triggers (either explicitly coded
1741 ** triggers or FK resolution actions) might run as a result of deletes
1742 ** that happen when OE_Replace conflict resolution occurs. (Call these
1743 ** "replace triggers".) If any replace triggers run, we will need to
1744 ** recheck all of the uniqueness constraints after they have all run.
1745 ** But on the recheck, the resolution is OE_Abort instead of OE_Replace.
1746 **
1747 ** If replace triggers are a possibility, then
1748 **
1749 ** (1) Allocate register regTrigCnt and initialize it to zero.
1750 ** That register will count the number of replace triggers that
1751 ** fire. Constraint recheck only occurs if the number is positive.
1752 ** (2) Initialize pTrigger to the list of all DELETE triggers on pTab.
1753 ** (3) Initialize addrRecheck and lblRecheckOk
1754 **
1755 ** The uniqueness rechecking code will create a series of tests to run
1756 ** in a second pass. The addrRecheck and lblRecheckOk variables are
1757 ** used to link together these tests which are separated from each other
1758 ** in the generate bytecode.
1759 */
1760 if( (db->flags & (SQLITE_RecTriggers|SQLITE_ForeignKeys))==0 ){
1761 /* There are not DELETE triggers nor FK constraints. No constraint
1762 ** rechecks are needed. */
1763 pTrigger = 0;
1764 regTrigCnt = 0;
1765 }else{
1766 if( db->flags&SQLITE_RecTriggers ){
1767 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
1768 regTrigCnt = pTrigger!=0 || sqlite3FkRequired(pParse, pTab, 0, 0);
1769 }else{
1770 pTrigger = 0;
1771 regTrigCnt = sqlite3FkRequired(pParse, pTab, 0, 0);
1772 }
1773 if( regTrigCnt ){
1774 /* Replace triggers might exist. Allocate the counter and
1775 ** initialize it to zero. */
1776 regTrigCnt = ++pParse->nMem;
1777 sqlite3VdbeAddOp2(v, OP_Integer, 0, regTrigCnt);
1778 VdbeComment((v, "trigger count"));
1779 lblRecheckOk = sqlite3VdbeMakeLabel(pParse);
1780 addrRecheck = lblRecheckOk;
1781 }
1782 }
1783
1784 /* If rowid is changing, make sure the new rowid does not previously
1785 ** exist in the table.
1786 */
1787 if( pkChng && pPk==0 ){
1788 int addrRowidOk = sqlite3VdbeMakeLabel(pParse);
1789
1790 /* Figure out what action to take in case of a rowid collision */
1791 onError = pTab->keyConf;
1792 if( overrideError!=OE_Default ){
1793 onError = overrideError;
1794 }else if( onError==OE_Default ){
1795 onError = OE_Abort;
1796 }
1797
1798 /* figure out whether or not upsert applies in this case */
1799 if( pUpsert && pUpsert->pUpsertIdx==0 ){
1800 if( pUpsert->pUpsertSet==0 ){
1801 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
1802 }else{
1803 onError = OE_Update; /* DO UPDATE */
1804 }
1805 }
1806
1807 /* If the response to a rowid conflict is REPLACE but the response
1808 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1809 ** to defer the running of the rowid conflict checking until after
1810 ** the UNIQUE constraints have run.
1811 */
1812 if( onError==OE_Replace /* IPK rule is REPLACE */
1813 && onError!=overrideError /* Rules for other contraints are different */
1814 && pTab->pIndex /* There exist other constraints */
1815 ){
1816 ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
1817 VdbeComment((v, "defer IPK REPLACE until last"));
1818 }
1819
1820 if( isUpdate ){
1821 /* pkChng!=0 does not mean that the rowid has changed, only that
1822 ** it might have changed. Skip the conflict logic below if the rowid
1823 ** is unchanged. */
1824 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
1825 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
1826 VdbeCoverage(v);
1827 }
1828
1829 /* Check to see if the new rowid already exists in the table. Skip
1830 ** the following conflict logic if it does not. */
1831 VdbeNoopComment((v, "uniqueness check for ROWID"));
1832 sqlite3VdbeVerifyAbortable(v, onError);
1833 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
1834 VdbeCoverage(v);
1835
1836 switch( onError ){
1837 default: {
1838 onError = OE_Abort;
1839 /* Fall thru into the next case */
1840 }
1841 case OE_Rollback:
1842 case OE_Abort:
1843 case OE_Fail: {
1844 testcase( onError==OE_Rollback );
1845 testcase( onError==OE_Abort );
1846 testcase( onError==OE_Fail );
1847 sqlite3RowidConstraint(pParse, onError, pTab);
1848 break;
1849 }
1850 case OE_Replace: {
1851 /* If there are DELETE triggers on this table and the
1852 ** recursive-triggers flag is set, call GenerateRowDelete() to
1853 ** remove the conflicting row from the table. This will fire
1854 ** the triggers and remove both the table and index b-tree entries.
1855 **
1856 ** Otherwise, if there are no triggers or the recursive-triggers
1857 ** flag is not set, but the table has one or more indexes, call
1858 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1859 ** only. The table b-tree entry will be replaced by the new entry
1860 ** when it is inserted.
1861 **
1862 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1863 ** also invoke MultiWrite() to indicate that this VDBE may require
1864 ** statement rollback (if the statement is aborted after the delete
1865 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1866 ** but being more selective here allows statements like:
1867 **
1868 ** REPLACE INTO t(rowid) VALUES($newrowid)
1869 **
1870 ** to run without a statement journal if there are no indexes on the
1871 ** table.
1872 */
1873 if( regTrigCnt ){
1874 sqlite3MultiWrite(pParse);
1875 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
1876 regNewData, 1, 0, OE_Replace, 1, -1);
1877 sqlite3VdbeAddOp2(v, OP_AddImm, regTrigCnt, 1); /* incr trigger cnt */
1878 nReplaceTrig++;
1879 }else{
1880 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1881 assert( HasRowid(pTab) );
1882 /* This OP_Delete opcode fires the pre-update-hook only. It does
1883 ** not modify the b-tree. It is more efficient to let the coming
1884 ** OP_Insert replace the existing entry than it is to delete the
1885 ** existing entry and then insert a new one. */
1886 sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, OPFLAG_ISNOOP);
1887 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
1888 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
1889 if( pTab->pIndex ){
1890 sqlite3MultiWrite(pParse);
1891 sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,-1);
1892 }
1893 }
1894 seenReplace = 1;
1895 break;
1896 }
1897 #ifndef SQLITE_OMIT_UPSERT
1898 case OE_Update: {
1899 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur);
1900 /* Fall through */
1901 }
1902 #endif
1903 case OE_Ignore: {
1904 testcase( onError==OE_Ignore );
1905 sqlite3VdbeGoto(v, ignoreDest);
1906 break;
1907 }
1908 }
1909 sqlite3VdbeResolveLabel(v, addrRowidOk);
1910 if( ipkTop ){
1911 ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
1912 sqlite3VdbeJumpHere(v, ipkTop-1);
1913 }
1914 }
1915
1916 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1917 ** index and making sure that duplicate entries do not already exist.
1918 ** Compute the revised record entries for indices as we go.
1919 **
1920 ** This loop also handles the case of the PRIMARY KEY index for a
1921 ** WITHOUT ROWID table.
1922 */
1923 for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
1924 int regIdx; /* Range of registers hold conent for pIdx */
1925 int regR; /* Range of registers holding conflicting PK */
1926 int iThisCur; /* Cursor for this UNIQUE index */
1927 int addrUniqueOk; /* Jump here if the UNIQUE constraint is satisfied */
1928 int addrConflictCk; /* First opcode in the conflict check logic */
1929
1930 if( aRegIdx[ix]==0 ) continue; /* Skip indices that do not change */
1931 if( pUpIdx==pIdx ){
1932 addrUniqueOk = upsertJump+1;
1933 upsertBypass = sqlite3VdbeGoto(v, 0);
1934 VdbeComment((v, "Skip upsert subroutine"));
1935 sqlite3VdbeJumpHere(v, upsertJump);
1936 }else{
1937 addrUniqueOk = sqlite3VdbeMakeLabel(pParse);
1938 }
1939 if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
1940 sqlite3TableAffinity(v, pTab, regNewData+1);
1941 bAffinityDone = 1;
1942 }
1943 VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
1944 iThisCur = iIdxCur+ix;
1945
1946
1947 /* Skip partial indices for which the WHERE clause is not true */
1948 if( pIdx->pPartIdxWhere ){
1949 sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
1950 pParse->iSelfTab = -(regNewData+1);
1951 sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
1952 SQLITE_JUMPIFNULL);
1953 pParse->iSelfTab = 0;
1954 }
1955
1956 /* Create a record for this index entry as it should appear after
1957 ** the insert or update. Store that record in the aRegIdx[ix] register
1958 */
1959 regIdx = aRegIdx[ix]+1;
1960 for(i=0; i<pIdx->nColumn; i++){
1961 int iField = pIdx->aiColumn[i];
1962 int x;
1963 if( iField==XN_EXPR ){
1964 pParse->iSelfTab = -(regNewData+1);
1965 sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i);
1966 pParse->iSelfTab = 0;
1967 VdbeComment((v, "%s column %d", pIdx->zName, i));
1968 }else if( iField==XN_ROWID || iField==pTab->iPKey ){
1969 x = regNewData;
1970 sqlite3VdbeAddOp2(v, OP_IntCopy, x, regIdx+i);
1971 VdbeComment((v, "rowid"));
1972 }else{
1973 testcase( sqlite3TableColumnToStorage(pTab, iField)!=iField );
1974 x = sqlite3TableColumnToStorage(pTab, iField) + regNewData + 1;
1975 sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i);
1976 VdbeComment((v, "%s", pTab->aCol[iField].zName));
1977 }
1978 }
1979 sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]);
1980 VdbeComment((v, "for %s", pIdx->zName));
1981 #ifdef SQLITE_ENABLE_NULL_TRIM
1982 if( pIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
1983 sqlite3SetMakeRecordP5(v, pIdx->pTable);
1984 }
1985 #endif
1986 sqlite3VdbeReleaseRegisters(pParse, regIdx, pIdx->nColumn, 0, 0);
1987
1988 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1989 ** of a WITHOUT ROWID table and there has been no change the
1990 ** primary key, then no collision is possible. The collision detection
1991 ** logic below can all be skipped. */
1992 if( isUpdate && pPk==pIdx && pkChng==0 ){
1993 sqlite3VdbeResolveLabel(v, addrUniqueOk);
1994 continue;
1995 }
1996
1997 /* Find out what action to take in case there is a uniqueness conflict */
1998 onError = pIdx->onError;
1999 if( onError==OE_None ){
2000 sqlite3VdbeResolveLabel(v, addrUniqueOk);
2001 continue; /* pIdx is not a UNIQUE index */
2002 }
2003 if( overrideError!=OE_Default ){
2004 onError = overrideError;
2005 }else if( onError==OE_Default ){
2006 onError = OE_Abort;
2007 }
2008
2009 /* Figure out if the upsert clause applies to this index */
2010 if( pUpIdx==pIdx ){
2011 if( pUpsert->pUpsertSet==0 ){
2012 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
2013 }else{
2014 onError = OE_Update; /* DO UPDATE */
2015 }
2016 }
2017
2018 /* Collision detection may be omitted if all of the following are true:
2019 ** (1) The conflict resolution algorithm is REPLACE
2020 ** (2) The table is a WITHOUT ROWID table
2021 ** (3) There are no secondary indexes on the table
2022 ** (4) No delete triggers need to be fired if there is a conflict
2023 ** (5) No FK constraint counters need to be updated if a conflict occurs.
2024 **
2025 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row
2026 ** must be explicitly deleted in order to ensure any pre-update hook
2027 ** is invoked. */
2028 #ifndef SQLITE_ENABLE_PREUPDATE_HOOK
2029 if( (ix==0 && pIdx->pNext==0) /* Condition 3 */
2030 && pPk==pIdx /* Condition 2 */
2031 && onError==OE_Replace /* Condition 1 */
2032 && ( 0==(db->flags&SQLITE_RecTriggers) || /* Condition 4 */
2033 0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0))
2034 && ( 0==(db->flags&SQLITE_ForeignKeys) || /* Condition 5 */
2035 (0==pTab->pFKey && 0==sqlite3FkReferences(pTab)))
2036 ){
2037 sqlite3VdbeResolveLabel(v, addrUniqueOk);
2038 continue;
2039 }
2040 #endif /* ifndef SQLITE_ENABLE_PREUPDATE_HOOK */
2041
2042 /* Check to see if the new index entry will be unique */
2043 sqlite3VdbeVerifyAbortable(v, onError);
2044 addrConflictCk =
2045 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
2046 regIdx, pIdx->nKeyCol); VdbeCoverage(v);
2047
2048 /* Generate code to handle collisions */
2049 regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
2050 if( isUpdate || onError==OE_Replace ){
2051 if( HasRowid(pTab) ){
2052 sqlite3VdbeAddOp2(v, OP_IdxRowid, iThisCur, regR);
2053 /* Conflict only if the rowid of the existing index entry
2054 ** is different from old-rowid */
2055 if( isUpdate ){
2056 sqlite3VdbeAddOp3(v, OP_Eq, regR, addrUniqueOk, regOldData);
2057 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
2058 VdbeCoverage(v);
2059 }
2060 }else{
2061 int x;
2062 /* Extract the PRIMARY KEY from the end of the index entry and
2063 ** store it in registers regR..regR+nPk-1 */
2064 if( pIdx!=pPk ){
2065 for(i=0; i<pPk->nKeyCol; i++){
2066 assert( pPk->aiColumn[i]>=0 );
2067 x = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[i]);
2068 sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i);
2069 VdbeComment((v, "%s.%s", pTab->zName,
2070 pTab->aCol[pPk->aiColumn[i]].zName));
2071 }
2072 }
2073 if( isUpdate ){
2074 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
2075 ** table, only conflict if the new PRIMARY KEY values are actually
2076 ** different from the old.
2077 **
2078 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
2079 ** of the matched index row are different from the original PRIMARY
2080 ** KEY values of this row before the update. */
2081 int addrJump = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
2082 int op = OP_Ne;
2083 int regCmp = (IsPrimaryKeyIndex(pIdx) ? regIdx : regR);
2084
2085 for(i=0; i<pPk->nKeyCol; i++){
2086 char *p4 = (char*)sqlite3LocateCollSeq(pParse, pPk->azColl[i]);
2087 x = pPk->aiColumn[i];
2088 assert( x>=0 );
2089 if( i==(pPk->nKeyCol-1) ){
2090 addrJump = addrUniqueOk;
2091 op = OP_Eq;
2092 }
2093 x = sqlite3TableColumnToStorage(pTab, x);
2094 sqlite3VdbeAddOp4(v, op,
2095 regOldData+1+x, addrJump, regCmp+i, p4, P4_COLLSEQ
2096 );
2097 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
2098 VdbeCoverageIf(v, op==OP_Eq);
2099 VdbeCoverageIf(v, op==OP_Ne);
2100 }
2101 }
2102 }
2103 }
2104
2105 /* Generate code that executes if the new index entry is not unique */
2106 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
2107 || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update );
2108 switch( onError ){
2109 case OE_Rollback:
2110 case OE_Abort:
2111 case OE_Fail: {
2112 testcase( onError==OE_Rollback );
2113 testcase( onError==OE_Abort );
2114 testcase( onError==OE_Fail );
2115 sqlite3UniqueConstraint(pParse, onError, pIdx);
2116 break;
2117 }
2118 #ifndef SQLITE_OMIT_UPSERT
2119 case OE_Update: {
2120 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix);
2121 /* Fall through */
2122 }
2123 #endif
2124 case OE_Ignore: {
2125 testcase( onError==OE_Ignore );
2126 sqlite3VdbeGoto(v, ignoreDest);
2127 break;
2128 }
2129 default: {
2130 int nConflictCk; /* Number of opcodes in conflict check logic */
2131
2132 assert( onError==OE_Replace );
2133 nConflictCk = sqlite3VdbeCurrentAddr(v) - addrConflictCk;
2134 assert( nConflictCk>0 );
2135 testcase( nConflictCk>1 );
2136 if( regTrigCnt ){
2137 sqlite3MultiWrite(pParse);
2138 nReplaceTrig++;
2139 }
2140 if( pTrigger && isUpdate ){
2141 sqlite3VdbeAddOp1(v, OP_CursorLock, iDataCur);
2142 }
2143 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
2144 regR, nPkField, 0, OE_Replace,
2145 (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
2146 if( pTrigger && isUpdate ){
2147 sqlite3VdbeAddOp1(v, OP_CursorUnlock, iDataCur);
2148 }
2149 if( regTrigCnt ){
2150 int addrBypass; /* Jump destination to bypass recheck logic */
2151
2152 sqlite3VdbeAddOp2(v, OP_AddImm, regTrigCnt, 1); /* incr trigger cnt */
2153 addrBypass = sqlite3VdbeAddOp0(v, OP_Goto); /* Bypass recheck */
2154 VdbeComment((v, "bypass recheck"));
2155
2156 /* Here we insert code that will be invoked after all constraint
2157 ** checks have run, if and only if one or more replace triggers
2158 ** fired. */
2159 sqlite3VdbeResolveLabel(v, lblRecheckOk);
2160 lblRecheckOk = sqlite3VdbeMakeLabel(pParse);
2161 if( pIdx->pPartIdxWhere ){
2162 /* Bypass the recheck if this partial index is not defined
2163 ** for the current row */
2164 sqlite3VdbeAddOp2(v, OP_IsNull, regIdx-1, lblRecheckOk);
2165 VdbeCoverage(v);
2166 }
2167 /* Copy the constraint check code from above, except change
2168 ** the constraint-ok jump destination to be the address of
2169 ** the next retest block */
2170 while( nConflictCk>0 ){
2171 VdbeOp x; /* Conflict check opcode to copy */
2172 /* The sqlite3VdbeAddOp4() call might reallocate the opcode array.
2173 ** Hence, make a complete copy of the opcode, rather than using
2174 ** a pointer to the opcode. */
2175 x = *sqlite3VdbeGetOp(v, addrConflictCk);
2176 if( x.opcode!=OP_IdxRowid ){
2177 int p2; /* New P2 value for copied conflict check opcode */
2178 if( sqlite3OpcodeProperty[x.opcode]&OPFLG_JUMP ){
2179 p2 = lblRecheckOk;
2180 }else{
2181 p2 = x.p2;
2182 }
2183 sqlite3VdbeAddOp4(v, x.opcode, x.p1, p2, x.p3, x.p4.z, x.p4type);
2184 sqlite3VdbeChangeP5(v, x.p5);
2185 VdbeCoverageIf(v, p2!=x.p2);
2186 }
2187 nConflictCk--;
2188 addrConflictCk++;
2189 }
2190 /* If the retest fails, issue an abort */
2191 sqlite3UniqueConstraint(pParse, OE_Abort, pIdx);
2192
2193 sqlite3VdbeJumpHere(v, addrBypass); /* Terminate the recheck bypass */
2194 }
2195 seenReplace = 1;
2196 break;
2197 }
2198 }
2199 if( pUpIdx==pIdx ){
2200 sqlite3VdbeGoto(v, upsertJump+1);
2201 sqlite3VdbeJumpHere(v, upsertBypass);
2202 }else{
2203 sqlite3VdbeResolveLabel(v, addrUniqueOk);
2204 }
2205 if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
2206 }
2207
2208 /* If the IPK constraint is a REPLACE, run it last */
2209 if( ipkTop ){
2210 sqlite3VdbeGoto(v, ipkTop);
2211 VdbeComment((v, "Do IPK REPLACE"));
2212 sqlite3VdbeJumpHere(v, ipkBottom);
2213 }
2214
2215 /* Recheck all uniqueness constraints after replace triggers have run */
2216 testcase( regTrigCnt!=0 && nReplaceTrig==0 );
2217 assert( regTrigCnt!=0 || nReplaceTrig==0 );
2218 if( nReplaceTrig ){
2219 sqlite3VdbeAddOp2(v, OP_IfNot, regTrigCnt, lblRecheckOk);VdbeCoverage(v);
2220 if( !pPk ){
2221 if( isUpdate ){
2222 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRecheck, regOldData);
2223 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
2224 VdbeCoverage(v);
2225 }
2226 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRecheck, regNewData);
2227 VdbeCoverage(v);
2228 sqlite3RowidConstraint(pParse, OE_Abort, pTab);
2229 }else{
2230 sqlite3VdbeGoto(v, addrRecheck);
2231 }
2232 sqlite3VdbeResolveLabel(v, lblRecheckOk);
2233 }
2234
2235 /* Generate the table record */
2236 if( HasRowid(pTab) ){
2237 int regRec = aRegIdx[ix];
2238 sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1, pTab->nNVCol, regRec);
2239 sqlite3SetMakeRecordP5(v, pTab);
2240 if( !bAffinityDone ){
2241 sqlite3TableAffinity(v, pTab, 0);
2242 }
2243 }
2244
2245 *pbMayReplace = seenReplace;
2246 VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
2247 }
2248
2249 #ifdef SQLITE_ENABLE_NULL_TRIM
2250 /*
2251 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
2252 ** to be the number of columns in table pTab that must not be NULL-trimmed.
2253 **
2254 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
2255 */
sqlite3SetMakeRecordP5(Vdbe * v,Table * pTab)2256 void sqlite3SetMakeRecordP5(Vdbe *v, Table *pTab){
2257 u16 i;
2258
2259 /* Records with omitted columns are only allowed for schema format
2260 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
2261 if( pTab->pSchema->file_format<2 ) return;
2262
2263 for(i=pTab->nCol-1; i>0; i--){
2264 if( pTab->aCol[i].pDflt!=0 ) break;
2265 if( pTab->aCol[i].colFlags & COLFLAG_PRIMKEY ) break;
2266 }
2267 sqlite3VdbeChangeP5(v, i+1);
2268 }
2269 #endif
2270
2271 /*
2272 ** This routine generates code to finish the INSERT or UPDATE operation
2273 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
2274 ** A consecutive range of registers starting at regNewData contains the
2275 ** rowid and the content to be inserted.
2276 **
2277 ** The arguments to this routine should be the same as the first six
2278 ** arguments to sqlite3GenerateConstraintChecks.
2279 */
sqlite3CompleteInsertion(Parse * pParse,Table * pTab,int iDataCur,int iIdxCur,int regNewData,int * aRegIdx,int update_flags,int appendBias,int useSeekResult)2280 void sqlite3CompleteInsertion(
2281 Parse *pParse, /* The parser context */
2282 Table *pTab, /* the table into which we are inserting */
2283 int iDataCur, /* Cursor of the canonical data source */
2284 int iIdxCur, /* First index cursor */
2285 int regNewData, /* Range of content */
2286 int *aRegIdx, /* Register used by each index. 0 for unused indices */
2287 int update_flags, /* True for UPDATE, False for INSERT */
2288 int appendBias, /* True if this is likely to be an append */
2289 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
2290 ){
2291 Vdbe *v; /* Prepared statements under construction */
2292 Index *pIdx; /* An index being inserted or updated */
2293 u8 pik_flags; /* flag values passed to the btree insert */
2294 int i; /* Loop counter */
2295
2296 assert( update_flags==0
2297 || update_flags==OPFLAG_ISUPDATE
2298 || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION)
2299 );
2300
2301 v = sqlite3GetVdbe(pParse);
2302 assert( v!=0 );
2303 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
2304 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
2305 /* All REPLACE indexes are at the end of the list */
2306 assert( pIdx->onError!=OE_Replace
2307 || pIdx->pNext==0
2308 || pIdx->pNext->onError==OE_Replace );
2309 if( aRegIdx[i]==0 ) continue;
2310 if( pIdx->pPartIdxWhere ){
2311 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
2312 VdbeCoverage(v);
2313 }
2314 pik_flags = (useSeekResult ? OPFLAG_USESEEKRESULT : 0);
2315 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
2316 assert( pParse->nested==0 );
2317 pik_flags |= OPFLAG_NCHANGE;
2318 pik_flags |= (update_flags & OPFLAG_SAVEPOSITION);
2319 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2320 if( update_flags==0 ){
2321 int r = sqlite3GetTempReg(pParse);
2322 sqlite3VdbeAddOp2(v, OP_Integer, 0, r);
2323 sqlite3VdbeAddOp4(v, OP_Insert,
2324 iIdxCur+i, aRegIdx[i], r, (char*)pTab, P4_TABLE
2325 );
2326 sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP);
2327 sqlite3ReleaseTempReg(pParse, r);
2328 }
2329 #endif
2330 }
2331 sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i],
2332 aRegIdx[i]+1,
2333 pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn);
2334 sqlite3VdbeChangeP5(v, pik_flags);
2335 }
2336 if( !HasRowid(pTab) ) return;
2337 if( pParse->nested ){
2338 pik_flags = 0;
2339 }else{
2340 pik_flags = OPFLAG_NCHANGE;
2341 pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID);
2342 }
2343 if( appendBias ){
2344 pik_flags |= OPFLAG_APPEND;
2345 }
2346 if( useSeekResult ){
2347 pik_flags |= OPFLAG_USESEEKRESULT;
2348 }
2349 sqlite3VdbeAddOp3(v, OP_Insert, iDataCur, aRegIdx[i], regNewData);
2350 if( !pParse->nested ){
2351 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
2352 }
2353 sqlite3VdbeChangeP5(v, pik_flags);
2354 }
2355
2356 /*
2357 ** Allocate cursors for the pTab table and all its indices and generate
2358 ** code to open and initialized those cursors.
2359 **
2360 ** The cursor for the object that contains the complete data (normally
2361 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
2362 ** ROWID table) is returned in *piDataCur. The first index cursor is
2363 ** returned in *piIdxCur. The number of indices is returned.
2364 **
2365 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
2366 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
2367 ** If iBase is negative, then allocate the next available cursor.
2368 **
2369 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
2370 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
2371 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
2372 ** pTab->pIndex list.
2373 **
2374 ** If pTab is a virtual table, then this routine is a no-op and the
2375 ** *piDataCur and *piIdxCur values are left uninitialized.
2376 */
sqlite3OpenTableAndIndices(Parse * pParse,Table * pTab,int op,u8 p5,int iBase,u8 * aToOpen,int * piDataCur,int * piIdxCur)2377 int sqlite3OpenTableAndIndices(
2378 Parse *pParse, /* Parsing context */
2379 Table *pTab, /* Table to be opened */
2380 int op, /* OP_OpenRead or OP_OpenWrite */
2381 u8 p5, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
2382 int iBase, /* Use this for the table cursor, if there is one */
2383 u8 *aToOpen, /* If not NULL: boolean for each table and index */
2384 int *piDataCur, /* Write the database source cursor number here */
2385 int *piIdxCur /* Write the first index cursor number here */
2386 ){
2387 int i;
2388 int iDb;
2389 int iDataCur;
2390 Index *pIdx;
2391 Vdbe *v;
2392
2393 assert( op==OP_OpenRead || op==OP_OpenWrite );
2394 assert( op==OP_OpenWrite || p5==0 );
2395 if( IsVirtual(pTab) ){
2396 /* This routine is a no-op for virtual tables. Leave the output
2397 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
2398 ** can detect if they are used by mistake in the caller. */
2399 return 0;
2400 }
2401 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
2402 v = sqlite3GetVdbe(pParse);
2403 assert( v!=0 );
2404 if( iBase<0 ) iBase = pParse->nTab;
2405 iDataCur = iBase++;
2406 if( piDataCur ) *piDataCur = iDataCur;
2407 if( HasRowid(pTab) && (aToOpen==0 || aToOpen[0]) ){
2408 sqlite3OpenTable(pParse, iDataCur, iDb, pTab, op);
2409 }else{
2410 sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName);
2411 }
2412 if( piIdxCur ) *piIdxCur = iBase;
2413 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
2414 int iIdxCur = iBase++;
2415 assert( pIdx->pSchema==pTab->pSchema );
2416 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
2417 if( piDataCur ) *piDataCur = iIdxCur;
2418 p5 = 0;
2419 }
2420 if( aToOpen==0 || aToOpen[i+1] ){
2421 sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
2422 sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
2423 sqlite3VdbeChangeP5(v, p5);
2424 VdbeComment((v, "%s", pIdx->zName));
2425 }
2426 }
2427 if( iBase>pParse->nTab ) pParse->nTab = iBase;
2428 return i;
2429 }
2430
2431
2432 #ifdef SQLITE_TEST
2433 /*
2434 ** The following global variable is incremented whenever the
2435 ** transfer optimization is used. This is used for testing
2436 ** purposes only - to make sure the transfer optimization really
2437 ** is happening when it is supposed to.
2438 */
2439 int sqlite3_xferopt_count;
2440 #endif /* SQLITE_TEST */
2441
2442
2443 #ifndef SQLITE_OMIT_XFER_OPT
2444 /*
2445 ** Check to see if index pSrc is compatible as a source of data
2446 ** for index pDest in an insert transfer optimization. The rules
2447 ** for a compatible index:
2448 **
2449 ** * The index is over the same set of columns
2450 ** * The same DESC and ASC markings occurs on all columns
2451 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
2452 ** * The same collating sequence on each column
2453 ** * The index has the exact same WHERE clause
2454 */
xferCompatibleIndex(Index * pDest,Index * pSrc)2455 static int xferCompatibleIndex(Index *pDest, Index *pSrc){
2456 int i;
2457 assert( pDest && pSrc );
2458 assert( pDest->pTable!=pSrc->pTable );
2459 if( pDest->nKeyCol!=pSrc->nKeyCol || pDest->nColumn!=pSrc->nColumn ){
2460 return 0; /* Different number of columns */
2461 }
2462 if( pDest->onError!=pSrc->onError ){
2463 return 0; /* Different conflict resolution strategies */
2464 }
2465 for(i=0; i<pSrc->nKeyCol; i++){
2466 if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
2467 return 0; /* Different columns indexed */
2468 }
2469 if( pSrc->aiColumn[i]==XN_EXPR ){
2470 assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 );
2471 if( sqlite3ExprCompare(0, pSrc->aColExpr->a[i].pExpr,
2472 pDest->aColExpr->a[i].pExpr, -1)!=0 ){
2473 return 0; /* Different expressions in the index */
2474 }
2475 }
2476 if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
2477 return 0; /* Different sort orders */
2478 }
2479 if( sqlite3_stricmp(pSrc->azColl[i],pDest->azColl[i])!=0 ){
2480 return 0; /* Different collating sequences */
2481 }
2482 }
2483 if( sqlite3ExprCompare(0, pSrc->pPartIdxWhere, pDest->pPartIdxWhere, -1) ){
2484 return 0; /* Different WHERE clauses */
2485 }
2486
2487 /* If no test above fails then the indices must be compatible */
2488 return 1;
2489 }
2490
2491 /*
2492 ** Attempt the transfer optimization on INSERTs of the form
2493 **
2494 ** INSERT INTO tab1 SELECT * FROM tab2;
2495 **
2496 ** The xfer optimization transfers raw records from tab2 over to tab1.
2497 ** Columns are not decoded and reassembled, which greatly improves
2498 ** performance. Raw index records are transferred in the same way.
2499 **
2500 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2501 ** There are lots of rules for determining compatibility - see comments
2502 ** embedded in the code for details.
2503 **
2504 ** This routine returns TRUE if the optimization is guaranteed to be used.
2505 ** Sometimes the xfer optimization will only work if the destination table
2506 ** is empty - a factor that can only be determined at run-time. In that
2507 ** case, this routine generates code for the xfer optimization but also
2508 ** does a test to see if the destination table is empty and jumps over the
2509 ** xfer optimization code if the test fails. In that case, this routine
2510 ** returns FALSE so that the caller will know to go ahead and generate
2511 ** an unoptimized transfer. This routine also returns FALSE if there
2512 ** is no chance that the xfer optimization can be applied.
2513 **
2514 ** This optimization is particularly useful at making VACUUM run faster.
2515 */
xferOptimization(Parse * pParse,Table * pDest,Select * pSelect,int onError,int iDbDest)2516 static int xferOptimization(
2517 Parse *pParse, /* Parser context */
2518 Table *pDest, /* The table we are inserting into */
2519 Select *pSelect, /* A SELECT statement to use as the data source */
2520 int onError, /* How to handle constraint errors */
2521 int iDbDest /* The database of pDest */
2522 ){
2523 sqlite3 *db = pParse->db;
2524 ExprList *pEList; /* The result set of the SELECT */
2525 Table *pSrc; /* The table in the FROM clause of SELECT */
2526 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */
2527 struct SrcList_item *pItem; /* An element of pSelect->pSrc */
2528 int i; /* Loop counter */
2529 int iDbSrc; /* The database of pSrc */
2530 int iSrc, iDest; /* Cursors from source and destination */
2531 int addr1, addr2; /* Loop addresses */
2532 int emptyDestTest = 0; /* Address of test for empty pDest */
2533 int emptySrcTest = 0; /* Address of test for empty pSrc */
2534 Vdbe *v; /* The VDBE we are building */
2535 int regAutoinc; /* Memory register used by AUTOINC */
2536 int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
2537 int regData, regRowid; /* Registers holding data and rowid */
2538
2539 if( pSelect==0 ){
2540 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
2541 }
2542 if( pParse->pWith || pSelect->pWith ){
2543 /* Do not attempt to process this query if there are an WITH clauses
2544 ** attached to it. Proceeding may generate a false "no such table: xxx"
2545 ** error if pSelect reads from a CTE named "xxx". */
2546 return 0;
2547 }
2548 if( sqlite3TriggerList(pParse, pDest) ){
2549 return 0; /* tab1 must not have triggers */
2550 }
2551 #ifndef SQLITE_OMIT_VIRTUALTABLE
2552 if( IsVirtual(pDest) ){
2553 return 0; /* tab1 must not be a virtual table */
2554 }
2555 #endif
2556 if( onError==OE_Default ){
2557 if( pDest->iPKey>=0 ) onError = pDest->keyConf;
2558 if( onError==OE_Default ) onError = OE_Abort;
2559 }
2560 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
2561 if( pSelect->pSrc->nSrc!=1 ){
2562 return 0; /* FROM clause must have exactly one term */
2563 }
2564 if( pSelect->pSrc->a[0].pSelect ){
2565 return 0; /* FROM clause cannot contain a subquery */
2566 }
2567 if( pSelect->pWhere ){
2568 return 0; /* SELECT may not have a WHERE clause */
2569 }
2570 if( pSelect->pOrderBy ){
2571 return 0; /* SELECT may not have an ORDER BY clause */
2572 }
2573 /* Do not need to test for a HAVING clause. If HAVING is present but
2574 ** there is no ORDER BY, we will get an error. */
2575 if( pSelect->pGroupBy ){
2576 return 0; /* SELECT may not have a GROUP BY clause */
2577 }
2578 if( pSelect->pLimit ){
2579 return 0; /* SELECT may not have a LIMIT clause */
2580 }
2581 if( pSelect->pPrior ){
2582 return 0; /* SELECT may not be a compound query */
2583 }
2584 if( pSelect->selFlags & SF_Distinct ){
2585 return 0; /* SELECT may not be DISTINCT */
2586 }
2587 pEList = pSelect->pEList;
2588 assert( pEList!=0 );
2589 if( pEList->nExpr!=1 ){
2590 return 0; /* The result set must have exactly one column */
2591 }
2592 assert( pEList->a[0].pExpr );
2593 if( pEList->a[0].pExpr->op!=TK_ASTERISK ){
2594 return 0; /* The result set must be the special operator "*" */
2595 }
2596
2597 /* At this point we have established that the statement is of the
2598 ** correct syntactic form to participate in this optimization. Now
2599 ** we have to check the semantics.
2600 */
2601 pItem = pSelect->pSrc->a;
2602 pSrc = sqlite3LocateTableItem(pParse, 0, pItem);
2603 if( pSrc==0 ){
2604 return 0; /* FROM clause does not contain a real table */
2605 }
2606 if( pSrc->tnum==pDest->tnum && pSrc->pSchema==pDest->pSchema ){
2607 testcase( pSrc!=pDest ); /* Possible due to bad sqlite_master.rootpage */
2608 return 0; /* tab1 and tab2 may not be the same table */
2609 }
2610 if( HasRowid(pDest)!=HasRowid(pSrc) ){
2611 return 0; /* source and destination must both be WITHOUT ROWID or not */
2612 }
2613 #ifndef SQLITE_OMIT_VIRTUALTABLE
2614 if( IsVirtual(pSrc) ){
2615 return 0; /* tab2 must not be a virtual table */
2616 }
2617 #endif
2618 if( pSrc->pSelect ){
2619 return 0; /* tab2 may not be a view */
2620 }
2621 if( pDest->nCol!=pSrc->nCol ){
2622 return 0; /* Number of columns must be the same in tab1 and tab2 */
2623 }
2624 if( pDest->iPKey!=pSrc->iPKey ){
2625 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
2626 }
2627 for(i=0; i<pDest->nCol; i++){
2628 Column *pDestCol = &pDest->aCol[i];
2629 Column *pSrcCol = &pSrc->aCol[i];
2630 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
2631 if( (db->mDbFlags & DBFLAG_Vacuum)==0
2632 && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN
2633 ){
2634 return 0; /* Neither table may have __hidden__ columns */
2635 }
2636 #endif
2637 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
2638 /* Even if tables t1 and t2 have identical schemas, if they contain
2639 ** generated columns, then this statement is semantically incorrect:
2640 **
2641 ** INSERT INTO t2 SELECT * FROM t1;
2642 **
2643 ** The reason is that generated column values are returned by the
2644 ** the SELECT statement on the right but the INSERT statement on the
2645 ** left wants them to be omitted.
2646 **
2647 ** Nevertheless, this is a useful notational shorthand to tell SQLite
2648 ** to do a bulk transfer all of the content from t1 over to t2.
2649 **
2650 ** We could, in theory, disable this (except for internal use by the
2651 ** VACUUM command where it is actually needed). But why do that? It
2652 ** seems harmless enough, and provides a useful service.
2653 */
2654 if( (pDestCol->colFlags & COLFLAG_GENERATED) !=
2655 (pSrcCol->colFlags & COLFLAG_GENERATED) ){
2656 return 0; /* Both columns have the same generated-column type */
2657 }
2658 /* But the transfer is only allowed if both the source and destination
2659 ** tables have the exact same expressions for generated columns.
2660 ** This requirement could be relaxed for VIRTUAL columns, I suppose.
2661 */
2662 if( (pDestCol->colFlags & COLFLAG_GENERATED)!=0 ){
2663 if( sqlite3ExprCompare(0, pSrcCol->pDflt, pDestCol->pDflt, -1)!=0 ){
2664 testcase( pDestCol->colFlags & COLFLAG_VIRTUAL );
2665 testcase( pDestCol->colFlags & COLFLAG_STORED );
2666 return 0; /* Different generator expressions */
2667 }
2668 }
2669 #endif
2670 if( pDestCol->affinity!=pSrcCol->affinity ){
2671 return 0; /* Affinity must be the same on all columns */
2672 }
2673 if( sqlite3_stricmp(pDestCol->zColl, pSrcCol->zColl)!=0 ){
2674 return 0; /* Collating sequence must be the same on all columns */
2675 }
2676 if( pDestCol->notNull && !pSrcCol->notNull ){
2677 return 0; /* tab2 must be NOT NULL if tab1 is */
2678 }
2679 /* Default values for second and subsequent columns need to match. */
2680 if( (pDestCol->colFlags & COLFLAG_GENERATED)==0 && i>0 ){
2681 assert( pDestCol->pDflt==0 || pDestCol->pDflt->op==TK_SPAN );
2682 assert( pSrcCol->pDflt==0 || pSrcCol->pDflt->op==TK_SPAN );
2683 if( (pDestCol->pDflt==0)!=(pSrcCol->pDflt==0)
2684 || (pDestCol->pDflt && strcmp(pDestCol->pDflt->u.zToken,
2685 pSrcCol->pDflt->u.zToken)!=0)
2686 ){
2687 return 0; /* Default values must be the same for all columns */
2688 }
2689 }
2690 }
2691 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
2692 if( IsUniqueIndex(pDestIdx) ){
2693 destHasUniqueIdx = 1;
2694 }
2695 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
2696 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
2697 }
2698 if( pSrcIdx==0 ){
2699 return 0; /* pDestIdx has no corresponding index in pSrc */
2700 }
2701 if( pSrcIdx->tnum==pDestIdx->tnum && pSrc->pSchema==pDest->pSchema
2702 && sqlite3FaultSim(411)==SQLITE_OK ){
2703 /* The sqlite3FaultSim() call allows this corruption test to be
2704 ** bypassed during testing, in order to exercise other corruption tests
2705 ** further downstream. */
2706 return 0; /* Corrupt schema - two indexes on the same btree */
2707 }
2708 }
2709 #ifndef SQLITE_OMIT_CHECK
2710 if( pDest->pCheck && sqlite3ExprListCompare(pSrc->pCheck,pDest->pCheck,-1) ){
2711 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2712 }
2713 #endif
2714 #ifndef SQLITE_OMIT_FOREIGN_KEY
2715 /* Disallow the transfer optimization if the destination table constains
2716 ** any foreign key constraints. This is more restrictive than necessary.
2717 ** But the main beneficiary of the transfer optimization is the VACUUM
2718 ** command, and the VACUUM command disables foreign key constraints. So
2719 ** the extra complication to make this rule less restrictive is probably
2720 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2721 */
2722 if( (db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
2723 return 0;
2724 }
2725 #endif
2726 if( (db->flags & SQLITE_CountRows)!=0 ){
2727 return 0; /* xfer opt does not play well with PRAGMA count_changes */
2728 }
2729
2730 /* If we get this far, it means that the xfer optimization is at
2731 ** least a possibility, though it might only work if the destination
2732 ** table (tab1) is initially empty.
2733 */
2734 #ifdef SQLITE_TEST
2735 sqlite3_xferopt_count++;
2736 #endif
2737 iDbSrc = sqlite3SchemaToIndex(db, pSrc->pSchema);
2738 v = sqlite3GetVdbe(pParse);
2739 sqlite3CodeVerifySchema(pParse, iDbSrc);
2740 iSrc = pParse->nTab++;
2741 iDest = pParse->nTab++;
2742 regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
2743 regData = sqlite3GetTempReg(pParse);
2744 regRowid = sqlite3GetTempReg(pParse);
2745 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
2746 assert( HasRowid(pDest) || destHasUniqueIdx );
2747 if( (db->mDbFlags & DBFLAG_Vacuum)==0 && (
2748 (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */
2749 || destHasUniqueIdx /* (2) */
2750 || (onError!=OE_Abort && onError!=OE_Rollback) /* (3) */
2751 )){
2752 /* In some circumstances, we are able to run the xfer optimization
2753 ** only if the destination table is initially empty. Unless the
2754 ** DBFLAG_Vacuum flag is set, this block generates code to make
2755 ** that determination. If DBFLAG_Vacuum is set, then the destination
2756 ** table is always empty.
2757 **
2758 ** Conditions under which the destination must be empty:
2759 **
2760 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2761 ** (If the destination is not initially empty, the rowid fields
2762 ** of index entries might need to change.)
2763 **
2764 ** (2) The destination has a unique index. (The xfer optimization
2765 ** is unable to test uniqueness.)
2766 **
2767 ** (3) onError is something other than OE_Abort and OE_Rollback.
2768 */
2769 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); VdbeCoverage(v);
2770 emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto);
2771 sqlite3VdbeJumpHere(v, addr1);
2772 }
2773 if( HasRowid(pSrc) ){
2774 u8 insFlags;
2775 sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
2776 emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
2777 if( pDest->iPKey>=0 ){
2778 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
2779 sqlite3VdbeVerifyAbortable(v, onError);
2780 addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
2781 VdbeCoverage(v);
2782 sqlite3RowidConstraint(pParse, onError, pDest);
2783 sqlite3VdbeJumpHere(v, addr2);
2784 autoIncStep(pParse, regAutoinc, regRowid);
2785 }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_VacuumInto) ){
2786 addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
2787 }else{
2788 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
2789 assert( (pDest->tabFlags & TF_Autoincrement)==0 );
2790 }
2791 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
2792 if( db->mDbFlags & DBFLAG_Vacuum ){
2793 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
2794 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|
2795 OPFLAG_APPEND|OPFLAG_USESEEKRESULT;
2796 }else{
2797 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND;
2798 }
2799 sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid,
2800 (char*)pDest, P4_TABLE);
2801 sqlite3VdbeChangeP5(v, insFlags);
2802 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); VdbeCoverage(v);
2803 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
2804 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
2805 }else{
2806 sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName);
2807 sqlite3TableLock(pParse, iDbSrc, pSrc->tnum, 0, pSrc->zName);
2808 }
2809 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
2810 u8 idxInsFlags = 0;
2811 for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){
2812 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
2813 }
2814 assert( pSrcIdx );
2815 sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc);
2816 sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);
2817 VdbeComment((v, "%s", pSrcIdx->zName));
2818 sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest);
2819 sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);
2820 sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
2821 VdbeComment((v, "%s", pDestIdx->zName));
2822 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
2823 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
2824 if( db->mDbFlags & DBFLAG_Vacuum ){
2825 /* This INSERT command is part of a VACUUM operation, which guarantees
2826 ** that the destination table is empty. If all indexed columns use
2827 ** collation sequence BINARY, then it can also be assumed that the
2828 ** index will be populated by inserting keys in strictly sorted
2829 ** order. In this case, instead of seeking within the b-tree as part
2830 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
2831 ** OP_IdxInsert to seek to the point within the b-tree where each key
2832 ** should be inserted. This is faster.
2833 **
2834 ** If any of the indexed columns use a collation sequence other than
2835 ** BINARY, this optimization is disabled. This is because the user
2836 ** might change the definition of a collation sequence and then run
2837 ** a VACUUM command. In that case keys may not be written in strictly
2838 ** sorted order. */
2839 for(i=0; i<pSrcIdx->nColumn; i++){
2840 const char *zColl = pSrcIdx->azColl[i];
2841 if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
2842 }
2843 if( i==pSrcIdx->nColumn ){
2844 idxInsFlags = OPFLAG_USESEEKRESULT;
2845 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
2846 }
2847 }
2848 if( !HasRowid(pSrc) && pDestIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
2849 idxInsFlags |= OPFLAG_NCHANGE;
2850 }
2851 sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
2852 sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);
2853 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v);
2854 sqlite3VdbeJumpHere(v, addr1);
2855 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
2856 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
2857 }
2858 if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest);
2859 sqlite3ReleaseTempReg(pParse, regRowid);
2860 sqlite3ReleaseTempReg(pParse, regData);
2861 if( emptyDestTest ){
2862 sqlite3AutoincrementEnd(pParse);
2863 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_OK, 0);
2864 sqlite3VdbeJumpHere(v, emptyDestTest);
2865 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
2866 return 0;
2867 }else{
2868 return 1;
2869 }
2870 }
2871 #endif /* SQLITE_OMIT_XFER_OPT */
2872