4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle INSERT statements in SQLite.
15 #include "sqliteInt.h"
18 ** Generate code that will
20 ** (1) acquire a lock for table pTab then
21 ** (2) open pTab as cursor iCur.
23 ** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index
24 ** for that table that is actually opened.
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 */
34 assert( !IsVirtual(pTab
) );
35 assert( pParse
->pVdbe
!=0 );
37 assert( opcode
==OP_OpenWrite
|| opcode
==OP_OpenRead
);
38 if( !pParse
->db
->noSharedCache
){
39 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
,
40 (opcode
==OP_OpenWrite
)?1:0, pTab
->zName
);
43 sqlite3VdbeAddOp4Int(v
, opcode
, iCur
, pTab
->tnum
, iDb
, pTab
->nNVCol
);
44 VdbeComment((v
, "%s", pTab
->zName
));
46 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
48 assert( pPk
->tnum
==pTab
->tnum
|| CORRUPT_DB
);
49 sqlite3VdbeAddOp3(v
, opcode
, iCur
, pPk
->tnum
, iDb
);
50 sqlite3VdbeSetP4KeyInfo(pParse
, pPk
);
51 VdbeComment((v
, "%s", pTab
->zName
));
56 ** Return a pointer to the column affinity string associated with index
57 ** pIdx. A column affinity string has one character for each column in
58 ** the table, according to the affinity of the column:
60 ** Character Column affinity
61 ** ------------------------------
68 ** An extra 'D' is appended to the end of the string to cover the
69 ** rowid that appears as the last column in every index.
71 ** Memory for the buffer containing the column index affinity string
72 ** is managed along with the rest of the Index structure. It will be
73 ** released when sqlite3DeleteIndex() is called.
75 static SQLITE_NOINLINE
const char *computeIndexAffStr(sqlite3
*db
, Index
*pIdx
){
76 /* The first time a column affinity string for a particular index is
77 ** required, it is allocated and populated here. It is then stored as
78 ** a member of the Index structure for subsequent use.
80 ** The column affinity string will eventually be deleted by
81 ** sqliteDeleteIndex() when the Index structure itself is cleaned
85 Table
*pTab
= pIdx
->pTable
;
86 pIdx
->zColAff
= (char *)sqlite3DbMallocRaw(0, pIdx
->nColumn
+1);
91 for(n
=0; n
<pIdx
->nColumn
; n
++){
92 i16 x
= pIdx
->aiColumn
[n
];
95 aff
= pTab
->aCol
[x
].affinity
;
96 }else if( x
==XN_ROWID
){
97 aff
= SQLITE_AFF_INTEGER
;
100 assert( pIdx
->bHasExpr
);
101 assert( pIdx
->aColExpr
!=0 );
102 aff
= sqlite3ExprAffinity(pIdx
->aColExpr
->a
[n
].pExpr
);
104 if( aff
<SQLITE_AFF_BLOB
) aff
= SQLITE_AFF_BLOB
;
105 if( aff
>SQLITE_AFF_NUMERIC
) aff
= SQLITE_AFF_NUMERIC
;
106 pIdx
->zColAff
[n
] = aff
;
108 pIdx
->zColAff
[n
] = 0;
109 return pIdx
->zColAff
;
111 const char *sqlite3IndexAffinityStr(sqlite3
*db
, Index
*pIdx
){
112 if( !pIdx
->zColAff
) return computeIndexAffStr(db
, pIdx
);
113 return pIdx
->zColAff
;
118 ** Compute an affinity string for a table. Space is obtained
119 ** from sqlite3DbMalloc(). The caller is responsible for freeing
120 ** the space when done.
122 char *sqlite3TableAffinityStr(sqlite3
*db
, const Table
*pTab
){
124 zColAff
= (char *)sqlite3DbMallocRaw(db
, pTab
->nCol
+1);
127 for(i
=j
=0; i
<pTab
->nCol
; i
++){
128 if( (pTab
->aCol
[i
].colFlags
& COLFLAG_VIRTUAL
)==0 ){
129 zColAff
[j
++] = pTab
->aCol
[i
].affinity
;
134 }while( j
>=0 && zColAff
[j
]<=SQLITE_AFF_BLOB
);
140 ** Make changes to the evolving bytecode to do affinity transformations
141 ** of values that are about to be gathered into a row for table pTab.
143 ** For ordinary (legacy, non-strict) tables:
144 ** -----------------------------------------
146 ** Compute the affinity string for table pTab, if it has not already been
147 ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
149 ** If the affinity string is empty (because it was all SQLITE_AFF_BLOB entries
150 ** which were then optimized out) then this routine becomes a no-op.
152 ** Otherwise if iReg>0 then code an OP_Affinity opcode that will set the
153 ** affinities for register iReg and following. Or if iReg==0,
154 ** then just set the P4 operand of the previous opcode (which should be
155 ** an OP_MakeRecord) to the affinity string.
157 ** A column affinity string has one character per column:
159 ** Character Column affinity
160 ** --------- ---------------
167 ** For STRICT tables:
168 ** ------------------
170 ** Generate an appropriate OP_TypeCheck opcode that will verify the
171 ** datatypes against the column definitions in pTab. If iReg==0, that
172 ** means an OP_MakeRecord opcode has already been generated and should be
173 ** the last opcode generated. The new OP_TypeCheck needs to be inserted
174 ** before the OP_MakeRecord. The new OP_TypeCheck should use the same
175 ** register set as the OP_MakeRecord. If iReg>0 then register iReg is
176 ** the first of a series of registers that will form the new record.
177 ** Apply the type checking to that array of registers.
179 void sqlite3TableAffinity(Vdbe
*v
, Table
*pTab
, int iReg
){
182 if( pTab
->tabFlags
& TF_Strict
){
184 /* Move the previous opcode (which should be OP_MakeRecord) forward
185 ** by one slot and insert a new OP_TypeCheck where the current
186 ** OP_MakeRecord is found */
188 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
189 pPrev
= sqlite3VdbeGetLastOp(v
);
191 assert( pPrev
->opcode
==OP_MakeRecord
|| sqlite3VdbeDb(v
)->mallocFailed
);
192 pPrev
->opcode
= OP_TypeCheck
;
193 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, pPrev
->p1
, pPrev
->p2
, pPrev
->p3
);
195 /* Insert an isolated OP_Typecheck */
196 sqlite3VdbeAddOp2(v
, OP_TypeCheck
, iReg
, pTab
->nNVCol
);
197 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
201 zColAff
= pTab
->zColAff
;
203 zColAff
= sqlite3TableAffinityStr(0, pTab
);
205 sqlite3OomFault(sqlite3VdbeDb(v
));
208 pTab
->zColAff
= zColAff
;
210 assert( zColAff
!=0 );
211 i
= sqlite3Strlen30NN(zColAff
);
214 sqlite3VdbeAddOp4(v
, OP_Affinity
, iReg
, i
, 0, zColAff
, i
);
216 assert( sqlite3VdbeGetLastOp(v
)->opcode
==OP_MakeRecord
217 || sqlite3VdbeDb(v
)->mallocFailed
);
218 sqlite3VdbeChangeP4(v
, -1, zColAff
, i
);
224 ** Return non-zero if the table pTab in database iDb or any of its indices
225 ** have been opened at any point in the VDBE program. This is used to see if
226 ** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
227 ** run without using a temporary table for the results of the SELECT.
229 static int readsTable(Parse
*p
, int iDb
, Table
*pTab
){
230 Vdbe
*v
= sqlite3GetVdbe(p
);
232 int iEnd
= sqlite3VdbeCurrentAddr(v
);
233 #ifndef SQLITE_OMIT_VIRTUALTABLE
234 VTable
*pVTab
= IsVirtual(pTab
) ? sqlite3GetVTable(p
->db
, pTab
) : 0;
237 for(i
=1; i
<iEnd
; i
++){
238 VdbeOp
*pOp
= sqlite3VdbeGetOp(v
, i
);
240 if( pOp
->opcode
==OP_OpenRead
&& pOp
->p3
==iDb
){
243 if( tnum
==pTab
->tnum
){
246 for(pIndex
=pTab
->pIndex
; pIndex
; pIndex
=pIndex
->pNext
){
247 if( tnum
==pIndex
->tnum
){
252 #ifndef SQLITE_OMIT_VIRTUALTABLE
253 if( pOp
->opcode
==OP_VOpen
&& pOp
->p4
.pVtab
==pVTab
){
254 assert( pOp
->p4
.pVtab
!=0 );
255 assert( pOp
->p4type
==P4_VTAB
);
263 /* This walker callback will compute the union of colFlags flags for all
264 ** referenced columns in a CHECK constraint or generated column expression.
266 static int exprColumnFlagUnion(Walker
*pWalker
, Expr
*pExpr
){
267 if( pExpr
->op
==TK_COLUMN
&& pExpr
->iColumn
>=0 ){
268 assert( pExpr
->iColumn
< pWalker
->u
.pTab
->nCol
);
269 pWalker
->eCode
|= pWalker
->u
.pTab
->aCol
[pExpr
->iColumn
].colFlags
;
274 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
276 ** All regular columns for table pTab have been puts into registers
277 ** starting with iRegStore. The registers that correspond to STORED
278 ** or VIRTUAL columns have not yet been initialized. This routine goes
279 ** back and computes the values for those columns based on the previously
280 ** computed normal columns.
282 void sqlite3ComputeGeneratedColumns(
283 Parse
*pParse
, /* Parsing context */
284 int iRegStore
, /* Register holding the first column */
285 Table
*pTab
/* The table */
293 assert( pTab
->tabFlags
& TF_HasGenerated
);
294 testcase( pTab
->tabFlags
& TF_HasVirtual
);
295 testcase( pTab
->tabFlags
& TF_HasStored
);
297 /* Before computing generated columns, first go through and make sure
298 ** that appropriate affinity has been applied to the regular columns
300 sqlite3TableAffinity(pParse
->pVdbe
, pTab
, iRegStore
);
301 if( (pTab
->tabFlags
& TF_HasStored
)!=0 ){
302 pOp
= sqlite3VdbeGetLastOp(pParse
->pVdbe
);
303 if( pOp
->opcode
==OP_Affinity
){
304 /* Change the OP_Affinity argument to '@' (NONE) for all stored
305 ** columns. '@' is the no-op affinity and those columns have not
306 ** yet been computed. */
308 char *zP4
= pOp
->p4
.z
;
310 assert( pOp
->p4type
==P4_DYNAMIC
);
311 for(ii
=jj
=0; zP4
[jj
]; ii
++){
312 if( pTab
->aCol
[ii
].colFlags
& COLFLAG_VIRTUAL
){
315 if( pTab
->aCol
[ii
].colFlags
& COLFLAG_STORED
){
316 zP4
[jj
] = SQLITE_AFF_NONE
;
320 }else if( pOp
->opcode
==OP_TypeCheck
){
321 /* If an OP_TypeCheck was generated because the table is STRICT,
322 ** then set the P3 operand to indicate that generated columns should
328 /* Because there can be multiple generated columns that refer to one another,
329 ** this is a two-pass algorithm. On the first pass, mark all generated
330 ** columns as "not available".
332 for(i
=0; i
<pTab
->nCol
; i
++){
333 if( pTab
->aCol
[i
].colFlags
& COLFLAG_GENERATED
){
334 testcase( pTab
->aCol
[i
].colFlags
& COLFLAG_VIRTUAL
);
335 testcase( pTab
->aCol
[i
].colFlags
& COLFLAG_STORED
);
336 pTab
->aCol
[i
].colFlags
|= COLFLAG_NOTAVAIL
;
341 w
.xExprCallback
= exprColumnFlagUnion
;
342 w
.xSelectCallback
= 0;
343 w
.xSelectCallback2
= 0;
345 /* On the second pass, compute the value of each NOT-AVAILABLE column.
346 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
347 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
350 pParse
->iSelfTab
= -iRegStore
;
354 for(i
=0; i
<pTab
->nCol
; i
++){
355 Column
*pCol
= pTab
->aCol
+ i
;
356 if( (pCol
->colFlags
& COLFLAG_NOTAVAIL
)!=0 ){
358 pCol
->colFlags
|= COLFLAG_BUSY
;
360 sqlite3WalkExpr(&w
, sqlite3ColumnExpr(pTab
, pCol
));
361 pCol
->colFlags
&= ~COLFLAG_BUSY
;
362 if( w
.eCode
& COLFLAG_NOTAVAIL
){
367 assert( pCol
->colFlags
& COLFLAG_GENERATED
);
368 x
= sqlite3TableColumnToStorage(pTab
, i
) + iRegStore
;
369 sqlite3ExprCodeGeneratedColumn(pParse
, pTab
, pCol
, x
);
370 pCol
->colFlags
&= ~COLFLAG_NOTAVAIL
;
373 }while( pRedo
&& eProgress
);
375 sqlite3ErrorMsg(pParse
, "generated column loop on \"%s\"", pRedo
->zCnName
);
377 pParse
->iSelfTab
= 0;
379 #endif /* SQLITE_OMIT_GENERATED_COLUMNS */
382 #ifndef SQLITE_OMIT_AUTOINCREMENT
384 ** Locate or create an AutoincInfo structure associated with table pTab
385 ** which is in database iDb. Return the register number for the register
386 ** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT
387 ** table. (Also return zero when doing a VACUUM since we do not want to
388 ** update the AUTOINCREMENT counters during a VACUUM.)
390 ** There is at most one AutoincInfo structure per table even if the
391 ** same table is autoincremented multiple times due to inserts within
392 ** triggers. A new AutoincInfo structure is created if this is the
393 ** first use of table pTab. On 2nd and subsequent uses, the original
394 ** AutoincInfo structure is used.
396 ** Four consecutive registers are allocated:
398 ** (1) The name of the pTab table.
399 ** (2) The maximum ROWID of pTab.
400 ** (3) The rowid in sqlite_sequence of pTab
401 ** (4) The original value of the max ROWID in pTab, or NULL if none
403 ** The 2nd register is the one that is returned. That is all the
404 ** insert routine needs to know about.
406 static int autoIncBegin(
407 Parse
*pParse
, /* Parsing context */
408 int iDb
, /* Index of the database holding pTab */
409 Table
*pTab
/* The table we are writing to */
411 int memId
= 0; /* Register holding maximum rowid */
412 assert( pParse
->db
->aDb
[iDb
].pSchema
!=0 );
413 if( (pTab
->tabFlags
& TF_Autoincrement
)!=0
414 && (pParse
->db
->mDbFlags
& DBFLAG_Vacuum
)==0
416 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
418 Table
*pSeqTab
= pParse
->db
->aDb
[iDb
].pSchema
->pSeqTab
;
420 /* Verify that the sqlite_sequence table exists and is an ordinary
421 ** rowid table with exactly two columns.
422 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */
424 || !HasRowid(pSeqTab
)
425 || NEVER(IsVirtual(pSeqTab
))
429 pParse
->rc
= SQLITE_CORRUPT_SEQUENCE
;
433 pInfo
= pToplevel
->pAinc
;
434 while( pInfo
&& pInfo
->pTab
!=pTab
){ pInfo
= pInfo
->pNext
; }
436 pInfo
= sqlite3DbMallocRawNN(pParse
->db
, sizeof(*pInfo
));
437 sqlite3ParserAddCleanup(pToplevel
, sqlite3DbFree
, pInfo
);
438 testcase( pParse
->earlyCleanup
);
439 if( pParse
->db
->mallocFailed
) return 0;
440 pInfo
->pNext
= pToplevel
->pAinc
;
441 pToplevel
->pAinc
= pInfo
;
444 pToplevel
->nMem
++; /* Register to hold name of table */
445 pInfo
->regCtr
= ++pToplevel
->nMem
; /* Max rowid register */
446 pToplevel
->nMem
+=2; /* Rowid in sqlite_sequence + orig max val */
448 memId
= pInfo
->regCtr
;
454 ** This routine generates code that will initialize all of the
455 ** register used by the autoincrement tracker.
457 void sqlite3AutoincrementBegin(Parse
*pParse
){
458 AutoincInfo
*p
; /* Information about an AUTOINCREMENT */
459 sqlite3
*db
= pParse
->db
; /* The database connection */
460 Db
*pDb
; /* Database only autoinc table */
461 int memId
; /* Register holding max rowid */
462 Vdbe
*v
= pParse
->pVdbe
; /* VDBE under construction */
464 /* This routine is never called during trigger-generation. It is
465 ** only called from the top-level */
466 assert( pParse
->pTriggerTab
==0 );
467 assert( sqlite3IsToplevel(pParse
) );
469 assert( v
); /* We failed long ago if this is not so */
470 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
471 static const int iLn
= VDBE_OFFSET_LINENO(2);
472 static const VdbeOpList autoInc
[] = {
473 /* 0 */ {OP_Null
, 0, 0, 0},
474 /* 1 */ {OP_Rewind
, 0, 10, 0},
475 /* 2 */ {OP_Column
, 0, 0, 0},
476 /* 3 */ {OP_Ne
, 0, 9, 0},
477 /* 4 */ {OP_Rowid
, 0, 0, 0},
478 /* 5 */ {OP_Column
, 0, 1, 0},
479 /* 6 */ {OP_AddImm
, 0, 0, 0},
480 /* 7 */ {OP_Copy
, 0, 0, 0},
481 /* 8 */ {OP_Goto
, 0, 11, 0},
482 /* 9 */ {OP_Next
, 0, 2, 0},
483 /* 10 */ {OP_Integer
, 0, 0, 0},
484 /* 11 */ {OP_Close
, 0, 0, 0}
487 pDb
= &db
->aDb
[p
->iDb
];
489 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
490 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenRead
);
491 sqlite3VdbeLoadString(v
, memId
-1, p
->pTab
->zName
);
492 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoInc
), autoInc
, iLn
);
499 aOp
[3].p5
= SQLITE_JUMPIFNULL
;
506 if( pParse
->nTab
==0 ) pParse
->nTab
= 1;
511 ** Update the maximum rowid for an autoincrement calculation.
513 ** This routine should be called when the regRowid register holds a
514 ** new rowid that is about to be inserted. If that new rowid is
515 ** larger than the maximum rowid in the memId memory cell, then the
516 ** memory cell is updated.
518 static void autoIncStep(Parse
*pParse
, int memId
, int regRowid
){
520 sqlite3VdbeAddOp2(pParse
->pVdbe
, OP_MemMax
, memId
, regRowid
);
525 ** This routine generates the code needed to write autoincrement
526 ** maximum rowid values back into the sqlite_sequence register.
527 ** Every statement that might do an INSERT into an autoincrement
528 ** table (either directly or through triggers) needs to call this
529 ** routine just before the "exit" code.
531 static SQLITE_NOINLINE
void autoIncrementEnd(Parse
*pParse
){
533 Vdbe
*v
= pParse
->pVdbe
;
534 sqlite3
*db
= pParse
->db
;
537 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
538 static const int iLn
= VDBE_OFFSET_LINENO(2);
539 static const VdbeOpList autoIncEnd
[] = {
540 /* 0 */ {OP_NotNull
, 0, 2, 0},
541 /* 1 */ {OP_NewRowid
, 0, 0, 0},
542 /* 2 */ {OP_MakeRecord
, 0, 2, 0},
543 /* 3 */ {OP_Insert
, 0, 0, 0},
544 /* 4 */ {OP_Close
, 0, 0, 0}
547 Db
*pDb
= &db
->aDb
[p
->iDb
];
549 int memId
= p
->regCtr
;
551 iRec
= sqlite3GetTempReg(pParse
);
552 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
553 sqlite3VdbeAddOp3(v
, OP_Le
, memId
+2, sqlite3VdbeCurrentAddr(v
)+7, memId
);
555 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenWrite
);
556 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoIncEnd
), autoIncEnd
, iLn
);
564 aOp
[3].p5
= OPFLAG_APPEND
;
565 sqlite3ReleaseTempReg(pParse
, iRec
);
568 void sqlite3AutoincrementEnd(Parse
*pParse
){
569 if( pParse
->pAinc
) autoIncrementEnd(pParse
);
573 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
574 ** above are all no-ops
576 # define autoIncBegin(A,B,C) (0)
577 # define autoIncStep(A,B,C)
578 #endif /* SQLITE_OMIT_AUTOINCREMENT */
580 void sqlite3MultiValuesEnd(Parse
*pParse
, Select
*pVal
){
581 if( pVal
->pSrc
->nSrc
>0 ){
582 SrcItem
*pItem
= &pVal
->pSrc
->a
[0];
583 sqlite3VdbeEndCoroutine(pParse
->pVdbe
, pItem
->regReturn
);
584 sqlite3VdbeJumpHere(pParse
->pVdbe
, pItem
->addrFillSub
- 1);
588 static int multiValueIsConstant(ExprList
*pRow
){
590 for(ii
=0; ii
<pRow
->nExpr
; ii
++){
591 if( 0==sqlite3ExprIsConstant(pRow
->a
[ii
].pExpr
) ) return 0;
596 static int multiValueIsConstantNoAff(ExprList
*pRow
){
598 if( multiValueIsConstant(pRow
)==0 ) return 0;
599 for(ii
=0; ii
<pRow
->nExpr
; ii
++){
600 assert( pRow
->a
[ii
].pExpr
->affExpr
==0 );
601 if( 0!=sqlite3ExprAffinity(pRow
->a
[ii
].pExpr
) ) return 0;
607 Select
*sqlite3MultiValues(Parse
*pParse
, Select
*pLeft
, ExprList
*pRow
){
612 if( pParse
->db
->init
.busy
613 || pParse
->pNewTrigger
615 || multiValueIsConstant(pRow
)==0
617 || (pLeft
->pSrc
->nSrc
==0 && multiValueIsConstantNoAff(pLeft
->pEList
)==0)
619 /* This row of the VALUES clause cannot be coded immediately. */
620 int f
= SF_Values
| SF_MultiValue
;
621 if( pLeft
->pSrc
->nSrc
){
622 sqlite3MultiValuesEnd(pParse
, pLeft
);
624 }else if( pLeft
->pPrior
){
625 /* In this case set the SF_MultiValue flag only if it was set on
626 ** the previous Select structure. */
627 f
= (f
& pLeft
->selFlags
);
629 pSelect
= sqlite3SelectNew(pParse
,pRow
,0,0,0,0,0,f
,0);
630 pLeft
->selFlags
&= ~SF_MultiValue
;
632 pSelect
->op
= TK_ALL
;
633 pSelect
->pPrior
= pLeft
;
638 if( pLeft
->pSrc
->nSrc
==0 ){
639 /* Co-routine has not yet been started. */
640 Vdbe
*v
= sqlite3GetVdbe(pParse
);
643 if( v
==0 ) return pLeft
;
644 pRet
= sqlite3SelectNew(pParse
, 0, 0, 0, 0, 0, 0, 0, 0);
645 if( pRet
==0 ) return pLeft
;
646 p
= &pRet
->pSrc
->a
[0];
647 pRet
->pSrc
->nSrc
= 1;
650 p
->fg
.viaCoroutine
= 1;
651 p
->addrFillSub
= sqlite3VdbeCurrentAddr(v
) + 1;
652 p
->regReturn
= ++pParse
->nMem
;
655 sqlite3VdbeAddOp3(v
,OP_InitCoroutine
,p
->regReturn
,0,p
->addrFillSub
);
656 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, p
->regReturn
);
657 sqlite3Select(pParse
, pLeft
, &dest
);
658 p
->regResult
= dest
.iSdst
;
659 assert( pParse
->nErr
|| dest
.iSdst
>0 );
663 p
= &pLeft
->pSrc
->a
[0];
666 if( pParse
->nErr
==0 ){
667 pSelect
= sqlite3SelectNew(pParse
, pRow
, 0, 0, 0, 0, 0, SF_Values
, 0);
669 if( p
->pSelect
->pEList
->nExpr
!=pSelect
->pEList
->nExpr
){
670 sqlite3SelectWrongNumTermsError(pParse
, pSelect
);
672 sqlite3SelectPrep(pParse
, pSelect
, 0);
673 #ifndef SQLITE_OMIT_WINDOWFUNC
675 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, p
->regReturn
);
676 dest
.iSdst
= p
->regResult
;
677 dest
.nSdst
= pRow
->nExpr
;
678 dest
.iSDParm
= p
->regReturn
;
679 sqlite3Select(pParse
, pSelect
, &dest
);
683 sqlite3ExprCodeExprList(pParse
, pSelect
->pEList
,p
->regResult
,0,0);
684 sqlite3VdbeAddOp1(pParse
->pVdbe
, OP_Yield
, p
->regReturn
);
687 sqlite3SelectDelete(pParse
->db
, pSelect
);
695 /* Forward declaration */
696 static int xferOptimization(
697 Parse
*pParse
, /* Parser context */
698 Table
*pDest
, /* The table we are inserting into */
699 Select
*pSelect
, /* A SELECT statement to use as the data source */
700 int onError
, /* How to handle constraint errors */
701 int iDbDest
/* The database of pDest */
705 ** This routine is called to handle SQL of the following forms:
707 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
708 ** insert into TABLE (IDLIST) select
709 ** insert into TABLE (IDLIST) default values
711 ** The IDLIST following the table name is always optional. If omitted,
712 ** then a list of all (non-hidden) columns for the table is substituted.
713 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
716 ** For the pSelect parameter holds the values to be inserted for the
717 ** first two forms shown above. A VALUES clause is really just short-hand
718 ** for a SELECT statement that omits the FROM clause and everything else
719 ** that follows. If the pSelect parameter is NULL, that means that the
720 ** DEFAULT VALUES form of the INSERT statement is intended.
722 ** The code generated follows one of four templates. For a simple
723 ** insert with data coming from a single-row VALUES clause, the code executes
724 ** once straight down through. Pseudo-code follows (we call this
725 ** the "1st template"):
727 ** open write cursor to <table> and its indices
728 ** put VALUES clause expressions into registers
729 ** write the resulting record into <table>
732 ** The three remaining templates assume the statement is of the form
734 ** INSERT INTO <table> SELECT ...
736 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
737 ** in other words if the SELECT pulls all columns from a single table
738 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
739 ** if <table2> and <table1> are distinct tables but have identical
740 ** schemas, including all the same indices, then a special optimization
741 ** is invoked that copies raw records from <table2> over to <table1>.
742 ** See the xferOptimization() function for the implementation of this
743 ** template. This is the 2nd template.
745 ** open a write cursor to <table>
746 ** open read cursor on <table2>
747 ** transfer all records in <table2> over to <table>
749 ** foreach index on <table>
750 ** open a write cursor on the <table> index
751 ** open a read cursor on the corresponding <table2> index
752 ** transfer all records from the read to the write cursors
756 ** The 3rd template is for when the second template does not apply
757 ** and the SELECT clause does not read from <table> at any time.
758 ** The generated code follows this template:
762 ** A: setup for the SELECT
763 ** loop over the rows in the SELECT
764 ** load values into registers R..R+n
767 ** cleanup after the SELECT
769 ** B: open write cursor to <table> and its indices
770 ** C: yield X, at EOF goto D
771 ** insert the select result into <table> from R..R+n
775 ** The 4th template is used if the insert statement takes its
776 ** values from a SELECT but the data is being inserted into a table
777 ** that is also read as part of the SELECT. In the third form,
778 ** we have to use an intermediate table to store the results of
779 ** the select. The template is like this:
783 ** A: setup for the SELECT
784 ** loop over the tables in the SELECT
785 ** load value into register R..R+n
788 ** cleanup after the SELECT
790 ** B: open temp table
791 ** L: yield X, at EOF goto M
792 ** insert row from R..R+n into temp table
794 ** M: open write cursor to <table> and its indices
796 ** C: loop over rows of intermediate table
797 ** transfer values form intermediate table into <table>
802 Parse
*pParse
, /* Parser context */
803 SrcList
*pTabList
, /* Name of table into which we are inserting */
804 Select
*pSelect
, /* A SELECT statement to use as the data source */
805 IdList
*pColumn
, /* Column names corresponding to IDLIST, or NULL. */
806 int onError
, /* How to handle constraint errors */
807 Upsert
*pUpsert
/* ON CONFLICT clauses for upsert, or NULL */
809 sqlite3
*db
; /* The main database structure */
810 Table
*pTab
; /* The table to insert into. aka TABLE */
811 int i
, j
; /* Loop counters */
812 Vdbe
*v
; /* Generate code into this virtual machine */
813 Index
*pIdx
; /* For looping over indices of the table */
814 int nColumn
; /* Number of columns in the data */
815 int nHidden
= 0; /* Number of hidden columns if TABLE is virtual */
816 int iDataCur
= 0; /* VDBE cursor that is the main data repository */
817 int iIdxCur
= 0; /* First index cursor */
818 int ipkColumn
= -1; /* Column that is the INTEGER PRIMARY KEY */
819 int endOfLoop
; /* Label for the end of the insertion loop */
820 int srcTab
= 0; /* Data comes from this temporary cursor if >=0 */
821 int addrInsTop
= 0; /* Jump to label "D" */
822 int addrCont
= 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
823 SelectDest dest
; /* Destination for SELECT on rhs of INSERT */
824 int iDb
; /* Index of database holding TABLE */
825 u8 useTempTable
= 0; /* Store SELECT results in intermediate table */
826 u8 appendFlag
= 0; /* True if the insert is likely to be an append */
827 u8 withoutRowid
; /* 0 for normal table. 1 for WITHOUT ROWID table */
828 u8 bIdListInOrder
; /* True if IDLIST is in table order */
829 ExprList
*pList
= 0; /* List of VALUES() to be inserted */
830 int iRegStore
; /* Register in which to store next column */
832 /* Register allocations */
833 int regFromSelect
= 0;/* Base register for data coming from SELECT */
834 int regAutoinc
= 0; /* Register holding the AUTOINCREMENT counter */
835 int regRowCount
= 0; /* Memory cell used for the row counter */
836 int regIns
; /* Block of regs holding rowid+data being inserted */
837 int regRowid
; /* registers holding insert rowid */
838 int regData
; /* register holding first column to insert */
839 int *aRegIdx
= 0; /* One register allocated to each index */
841 #ifndef SQLITE_OMIT_TRIGGER
842 int isView
; /* True if attempting to insert into a view */
843 Trigger
*pTrigger
; /* List of triggers on pTab, if required */
844 int tmask
; /* Mask of trigger times */
848 assert( db
->pParse
==pParse
);
852 assert( db
->mallocFailed
==0 );
853 dest
.iSDParm
= 0; /* Suppress a harmless compiler warning */
855 /* If the Select object is really just a simple VALUES() list with a
856 ** single row (the common case) then keep that one row of values
857 ** and discard the other (unused) parts of the pSelect object
859 if( pSelect
&& (pSelect
->selFlags
& SF_Values
)!=0 && pSelect
->pPrior
==0 ){
860 pList
= pSelect
->pEList
;
862 sqlite3SelectDelete(db
, pSelect
);
866 /* Locate the table into which we will be inserting new information.
868 assert( pTabList
->nSrc
==1 );
869 pTab
= sqlite3SrcListLookup(pParse
, pTabList
);
873 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
874 assert( iDb
<db
->nDb
);
875 if( sqlite3AuthCheck(pParse
, SQLITE_INSERT
, pTab
->zName
, 0,
876 db
->aDb
[iDb
].zDbSName
) ){
879 withoutRowid
= !HasRowid(pTab
);
881 /* Figure out if we have any triggers and if the table being
882 ** inserted into is a view
884 #ifndef SQLITE_OMIT_TRIGGER
885 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_INSERT
, 0, &tmask
);
886 isView
= IsView(pTab
);
892 #ifdef SQLITE_OMIT_VIEW
896 assert( (pTrigger
&& tmask
) || (pTrigger
==0 && tmask
==0) );
898 #if TREETRACE_ENABLED
899 if( sqlite3TreeTrace
& 0x10000 ){
900 sqlite3TreeViewLine(0, "In sqlite3Insert() at %s:%d", __FILE__
, __LINE__
);
901 sqlite3TreeViewInsert(pParse
->pWith
, pTabList
, pColumn
, pSelect
, pList
,
902 onError
, pUpsert
, pTrigger
);
906 /* If pTab is really a view, make sure it has been initialized.
907 ** ViewGetColumnNames() is a no-op if pTab is not a view.
909 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ){
913 /* Cannot insert into a read-only table.
915 if( sqlite3IsReadOnly(pParse
, pTab
, pTrigger
) ){
921 v
= sqlite3GetVdbe(pParse
);
922 if( v
==0 ) goto insert_cleanup
;
923 if( pParse
->nested
==0 ) sqlite3VdbeCountChanges(v
);
924 sqlite3BeginWriteOperation(pParse
, pSelect
|| pTrigger
, iDb
);
926 #ifndef SQLITE_OMIT_XFER_OPT
927 /* If the statement is of the form
929 ** INSERT INTO <table1> SELECT * FROM <table2>;
931 ** Then special optimizations can be applied that make the transfer
932 ** very fast and which reduce fragmentation of indices.
934 ** This is the 2nd template.
939 && xferOptimization(pParse
, pTab
, pSelect
, onError
, iDb
)
945 #endif /* SQLITE_OMIT_XFER_OPT */
947 /* If this is an AUTOINCREMENT table, look up the sequence number in the
948 ** sqlite_sequence table and store it in memory cell regAutoinc.
950 regAutoinc
= autoIncBegin(pParse
, iDb
, pTab
);
952 /* Allocate a block registers to hold the rowid and the values
953 ** for all columns of the new row.
955 regRowid
= regIns
= pParse
->nMem
+1;
956 pParse
->nMem
+= pTab
->nCol
+ 1;
957 if( IsVirtual(pTab
) ){
961 regData
= regRowid
+1;
963 /* If the INSERT statement included an IDLIST term, then make sure
964 ** all elements of the IDLIST really are columns of the table and
965 ** remember the column indices.
967 ** If the table has an INTEGER PRIMARY KEY column and that column
968 ** is named in the IDLIST, then record in the ipkColumn variable
969 ** the index into IDLIST of the primary key column. ipkColumn is
970 ** the index of the primary key as it appears in IDLIST, not as
971 ** is appears in the original table. (The index of the INTEGER
972 ** PRIMARY KEY in the original table is pTab->iPKey.) After this
973 ** loop, if ipkColumn==(-1), that means that integer primary key
974 ** is unspecified, and hence the table is either WITHOUT ROWID or
975 ** it will automatically generated an integer primary key.
977 ** bIdListInOrder is true if the columns in IDLIST are in storage
978 ** order. This enables an optimization that avoids shuffling the
979 ** columns into storage order. False negatives are harmless,
980 ** but false positives will cause database corruption.
982 bIdListInOrder
= (pTab
->tabFlags
& (TF_OOOHidden
|TF_HasStored
))==0;
984 assert( pColumn
->eU4
!=EU4_EXPR
);
985 pColumn
->eU4
= EU4_IDX
;
986 for(i
=0; i
<pColumn
->nId
; i
++){
987 pColumn
->a
[i
].u4
.idx
= -1;
989 for(i
=0; i
<pColumn
->nId
; i
++){
990 for(j
=0; j
<pTab
->nCol
; j
++){
991 if( sqlite3StrICmp(pColumn
->a
[i
].zName
, pTab
->aCol
[j
].zCnName
)==0 ){
992 pColumn
->a
[i
].u4
.idx
= j
;
993 if( i
!=j
) bIdListInOrder
= 0;
994 if( j
==pTab
->iPKey
){
995 ipkColumn
= i
; assert( !withoutRowid
);
997 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
998 if( pTab
->aCol
[j
].colFlags
& (COLFLAG_STORED
|COLFLAG_VIRTUAL
) ){
999 sqlite3ErrorMsg(pParse
,
1000 "cannot INSERT into generated column \"%s\"",
1001 pTab
->aCol
[j
].zCnName
);
1002 goto insert_cleanup
;
1008 if( j
>=pTab
->nCol
){
1009 if( sqlite3IsRowid(pColumn
->a
[i
].zName
) && !withoutRowid
){
1013 sqlite3ErrorMsg(pParse
, "table %S has no column named %s",
1014 pTabList
->a
, pColumn
->a
[i
].zName
);
1015 pParse
->checkSchema
= 1;
1016 goto insert_cleanup
;
1022 /* Figure out how many columns of data are supplied. If the data
1023 ** is coming from a SELECT statement, then generate a co-routine that
1024 ** produces a single row of the SELECT on each invocation. The
1025 ** co-routine is the common header to the 3rd and 4th templates.
1028 /* Data is coming from a SELECT or from a multi-row VALUES clause.
1029 ** Generate a co-routine to run the SELECT. */
1030 int regYield
; /* Register holding co-routine entry-point */
1031 int rc
; /* Result code */
1033 if( pSelect
->pSrc
->nSrc
==1 && pSelect
->pSrc
->a
[0].fg
.viaCoroutine
){
1034 SrcItem
*pItem
= &pSelect
->pSrc
->a
[0];
1035 dest
.iSDParm
= regYield
= pItem
->regReturn
;
1036 regFromSelect
= pItem
->regResult
;
1037 nColumn
= pItem
->pSelect
->pEList
->nExpr
;
1039 int addrTop
; /* Top of the co-routine */
1040 regYield
= ++pParse
->nMem
;
1041 addrTop
= sqlite3VdbeCurrentAddr(v
) + 1;
1042 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regYield
, 0, addrTop
);
1043 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, regYield
);
1044 dest
.iSdst
= bIdListInOrder
? regData
: 0;
1045 dest
.nSdst
= pTab
->nCol
;
1046 rc
= sqlite3Select(pParse
, pSelect
, &dest
);
1047 regFromSelect
= dest
.iSdst
;
1048 assert( db
->pParse
==pParse
);
1049 if( rc
|| pParse
->nErr
) goto insert_cleanup
;
1050 assert( db
->mallocFailed
==0 );
1051 sqlite3VdbeEndCoroutine(v
, regYield
);
1052 sqlite3VdbeJumpHere(v
, addrTop
- 1); /* label B: */
1053 assert( pSelect
->pEList
);
1054 nColumn
= pSelect
->pEList
->nExpr
;
1057 /* Set useTempTable to TRUE if the result of the SELECT statement
1058 ** should be written into a temporary table (template 4). Set to
1059 ** FALSE if each output row of the SELECT can be written directly into
1060 ** the destination table (template 3).
1062 ** A temp table must be used if the table being updated is also one
1063 ** of the tables being read by the SELECT statement. Also use a
1064 ** temp table in the case of row triggers.
1066 if( pTrigger
|| readsTable(pParse
, iDb
, pTab
) ){
1071 /* Invoke the coroutine to extract information from the SELECT
1072 ** and add it to a transient table srcTab. The code generated
1073 ** here is from the 4th template:
1075 ** B: open temp table
1076 ** L: yield X, goto M at EOF
1077 ** insert row from R..R+n into temp table
1081 int regRec
; /* Register to hold packed record */
1082 int regTempRowid
; /* Register to hold temp table ROWID */
1083 int addrL
; /* Label "L" */
1085 srcTab
= pParse
->nTab
++;
1086 regRec
= sqlite3GetTempReg(pParse
);
1087 regTempRowid
= sqlite3GetTempReg(pParse
);
1088 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, srcTab
, nColumn
);
1089 addrL
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
); VdbeCoverage(v
);
1090 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regFromSelect
, nColumn
, regRec
);
1091 sqlite3VdbeAddOp2(v
, OP_NewRowid
, srcTab
, regTempRowid
);
1092 sqlite3VdbeAddOp3(v
, OP_Insert
, srcTab
, regRec
, regTempRowid
);
1093 sqlite3VdbeGoto(v
, addrL
);
1094 sqlite3VdbeJumpHere(v
, addrL
);
1095 sqlite3ReleaseTempReg(pParse
, regRec
);
1096 sqlite3ReleaseTempReg(pParse
, regTempRowid
);
1099 /* This is the case if the data for the INSERT is coming from a
1100 ** single-row VALUES clause
1103 memset(&sNC
, 0, sizeof(sNC
));
1104 sNC
.pParse
= pParse
;
1106 assert( useTempTable
==0 );
1108 nColumn
= pList
->nExpr
;
1109 if( sqlite3ResolveExprListNames(&sNC
, pList
) ){
1110 goto insert_cleanup
;
1117 /* If there is no IDLIST term but the table has an integer primary
1118 ** key, the set the ipkColumn variable to the integer primary key
1119 ** column index in the original table definition.
1121 if( pColumn
==0 && nColumn
>0 ){
1122 ipkColumn
= pTab
->iPKey
;
1123 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1124 if( ipkColumn
>=0 && (pTab
->tabFlags
& TF_HasGenerated
)!=0 ){
1125 testcase( pTab
->tabFlags
& TF_HasVirtual
);
1126 testcase( pTab
->tabFlags
& TF_HasStored
);
1127 for(i
=ipkColumn
-1; i
>=0; i
--){
1128 if( pTab
->aCol
[i
].colFlags
& COLFLAG_GENERATED
){
1129 testcase( pTab
->aCol
[i
].colFlags
& COLFLAG_VIRTUAL
);
1130 testcase( pTab
->aCol
[i
].colFlags
& COLFLAG_STORED
);
1137 /* Make sure the number of columns in the source data matches the number
1138 ** of columns to be inserted into the table.
1140 assert( TF_HasHidden
==COLFLAG_HIDDEN
);
1141 assert( TF_HasGenerated
==COLFLAG_GENERATED
);
1142 assert( COLFLAG_NOINSERT
==(COLFLAG_GENERATED
|COLFLAG_HIDDEN
) );
1143 if( (pTab
->tabFlags
& (TF_HasGenerated
|TF_HasHidden
))!=0 ){
1144 for(i
=0; i
<pTab
->nCol
; i
++){
1145 if( pTab
->aCol
[i
].colFlags
& COLFLAG_NOINSERT
) nHidden
++;
1148 if( nColumn
!=(pTab
->nCol
-nHidden
) ){
1149 sqlite3ErrorMsg(pParse
,
1150 "table %S has %d columns but %d values were supplied",
1151 pTabList
->a
, pTab
->nCol
-nHidden
, nColumn
);
1152 goto insert_cleanup
;
1155 if( pColumn
!=0 && nColumn
!=pColumn
->nId
){
1156 sqlite3ErrorMsg(pParse
, "%d values for %d columns", nColumn
, pColumn
->nId
);
1157 goto insert_cleanup
;
1160 /* Initialize the count of rows to be inserted
1162 if( (db
->flags
& SQLITE_CountRows
)!=0
1164 && !pParse
->pTriggerTab
1165 && !pParse
->bReturning
1167 regRowCount
= ++pParse
->nMem
;
1168 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regRowCount
);
1171 /* If this is not a view, open the table and and all indices */
1174 nIdx
= sqlite3OpenTableAndIndices(pParse
, pTab
, OP_OpenWrite
, 0, -1, 0,
1175 &iDataCur
, &iIdxCur
);
1176 aRegIdx
= sqlite3DbMallocRawNN(db
, sizeof(int)*(nIdx
+2));
1178 goto insert_cleanup
;
1180 for(i
=0, pIdx
=pTab
->pIndex
; i
<nIdx
; pIdx
=pIdx
->pNext
, i
++){
1182 aRegIdx
[i
] = ++pParse
->nMem
;
1183 pParse
->nMem
+= pIdx
->nColumn
;
1185 aRegIdx
[i
] = ++pParse
->nMem
; /* Register to store the table record */
1187 #ifndef SQLITE_OMIT_UPSERT
1190 if( IsVirtual(pTab
) ){
1191 sqlite3ErrorMsg(pParse
, "UPSERT not implemented for virtual table \"%s\"",
1193 goto insert_cleanup
;
1196 sqlite3ErrorMsg(pParse
, "cannot UPSERT a view");
1197 goto insert_cleanup
;
1199 if( sqlite3HasExplicitNulls(pParse
, pUpsert
->pUpsertTarget
) ){
1200 goto insert_cleanup
;
1202 pTabList
->a
[0].iCursor
= iDataCur
;
1205 pNx
->pUpsertSrc
= pTabList
;
1206 pNx
->regData
= regData
;
1207 pNx
->iDataCur
= iDataCur
;
1208 pNx
->iIdxCur
= iIdxCur
;
1209 if( pNx
->pUpsertTarget
){
1210 if( sqlite3UpsertAnalyzeTarget(pParse
, pTabList
, pNx
, pUpsert
) ){
1211 goto insert_cleanup
;
1214 pNx
= pNx
->pNextUpsert
;
1220 /* This is the top of the main insertion loop */
1222 /* This block codes the top of loop only. The complete loop is the
1223 ** following pseudocode (template 4):
1225 ** rewind temp table, if empty goto D
1226 ** C: loop over rows of intermediate table
1227 ** transfer values form intermediate table into <table>
1231 addrInsTop
= sqlite3VdbeAddOp1(v
, OP_Rewind
, srcTab
); VdbeCoverage(v
);
1232 addrCont
= sqlite3VdbeCurrentAddr(v
);
1233 }else if( pSelect
){
1234 /* This block codes the top of loop only. The complete loop is the
1235 ** following pseudocode (template 3):
1237 ** C: yield X, at EOF goto D
1238 ** insert the select result into <table> from R..R+n
1242 sqlite3VdbeReleaseRegisters(pParse
, regData
, pTab
->nCol
, 0, 0);
1243 addrInsTop
= addrCont
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
);
1246 /* tag-20191021-001: If the INTEGER PRIMARY KEY is being generated by the
1247 ** SELECT, go ahead and copy the value into the rowid slot now, so that
1248 ** the value does not get overwritten by a NULL at tag-20191021-002. */
1249 sqlite3VdbeAddOp2(v
, OP_Copy
, regFromSelect
+ipkColumn
, regRowid
);
1253 /* Compute data for ordinary columns of the new entry. Values
1254 ** are written in storage order into registers starting with regData.
1255 ** Only ordinary columns are computed in this loop. The rowid
1256 ** (if there is one) is computed later and generated columns are
1257 ** computed after the rowid since they might depend on the value
1261 iRegStore
= regData
; assert( regData
==regRowid
+1 );
1262 for(i
=0; i
<pTab
->nCol
; i
++, iRegStore
++){
1265 assert( i
>=nHidden
);
1266 if( i
==pTab
->iPKey
){
1267 /* tag-20191021-002: References to the INTEGER PRIMARY KEY are filled
1268 ** using the rowid. So put a NULL in the IPK slot of the record to avoid
1269 ** using excess space. The file format definition requires this extra
1270 ** NULL - we cannot optimize further by skipping the column completely */
1271 sqlite3VdbeAddOp1(v
, OP_SoftNull
, iRegStore
);
1274 if( ((colFlags
= pTab
->aCol
[i
].colFlags
) & COLFLAG_NOINSERT
)!=0 ){
1276 if( (colFlags
& COLFLAG_VIRTUAL
)!=0 ){
1277 /* Virtual columns do not participate in OP_MakeRecord. So back up
1278 ** iRegStore by one slot to compensate for the iRegStore++ in the
1279 ** outer for() loop */
1282 }else if( (colFlags
& COLFLAG_STORED
)!=0 ){
1283 /* Stored columns are computed later. But if there are BEFORE
1284 ** triggers, the slots used for stored columns will be OP_Copy-ed
1285 ** to a second block of registers, so the register needs to be
1286 ** initialized to NULL to avoid an uninitialized register read */
1287 if( tmask
& TRIGGER_BEFORE
){
1288 sqlite3VdbeAddOp1(v
, OP_SoftNull
, iRegStore
);
1291 }else if( pColumn
==0 ){
1292 /* Hidden columns that are not explicitly named in the INSERT
1293 ** get there default value */
1294 sqlite3ExprCodeFactorable(pParse
,
1295 sqlite3ColumnExpr(pTab
, &pTab
->aCol
[i
]),
1301 assert( pColumn
->eU4
==EU4_IDX
);
1302 for(j
=0; j
<pColumn
->nId
&& pColumn
->a
[j
].u4
.idx
!=i
; j
++){}
1303 if( j
>=pColumn
->nId
){
1304 /* A column not named in the insert column list gets its
1306 sqlite3ExprCodeFactorable(pParse
,
1307 sqlite3ColumnExpr(pTab
, &pTab
->aCol
[i
]),
1312 }else if( nColumn
==0 ){
1313 /* This is INSERT INTO ... DEFAULT VALUES. Load the default value. */
1314 sqlite3ExprCodeFactorable(pParse
,
1315 sqlite3ColumnExpr(pTab
, &pTab
->aCol
[i
]),
1323 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, k
, iRegStore
);
1324 }else if( pSelect
){
1325 if( regFromSelect
!=regData
){
1326 sqlite3VdbeAddOp2(v
, OP_SCopy
, regFromSelect
+k
, iRegStore
);
1329 Expr
*pX
= pList
->a
[k
].pExpr
;
1330 int y
= sqlite3ExprCodeTarget(pParse
, pX
, iRegStore
);
1332 sqlite3VdbeAddOp2(v
,
1333 ExprHasProperty(pX
, EP_Subquery
) ? OP_Copy
: OP_SCopy
, y
, iRegStore
);
1339 /* Run the BEFORE and INSTEAD OF triggers, if there are any
1341 endOfLoop
= sqlite3VdbeMakeLabel(pParse
);
1342 if( tmask
& TRIGGER_BEFORE
){
1343 int regCols
= sqlite3GetTempRange(pParse
, pTab
->nCol
+1);
1345 /* build the NEW.* reference row. Note that if there is an INTEGER
1346 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
1347 ** translated into a unique ID for the row. But on a BEFORE trigger,
1348 ** we do not know what the unique ID will be (because the insert has
1349 ** not happened yet) so we substitute a rowid of -1
1352 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regCols
);
1355 assert( !withoutRowid
);
1357 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, ipkColumn
, regCols
);
1359 assert( pSelect
==0 ); /* Otherwise useTempTable is true */
1360 sqlite3ExprCode(pParse
, pList
->a
[ipkColumn
].pExpr
, regCols
);
1362 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regCols
); VdbeCoverage(v
);
1363 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regCols
);
1364 sqlite3VdbeJumpHere(v
, addr1
);
1365 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regCols
); VdbeCoverage(v
);
1368 /* Copy the new data already generated. */
1369 assert( pTab
->nNVCol
>0 || pParse
->nErr
>0 );
1370 sqlite3VdbeAddOp3(v
, OP_Copy
, regRowid
+1, regCols
+1, pTab
->nNVCol
-1);
1372 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1373 /* Compute the new value for generated columns after all other
1374 ** columns have already been computed. This must be done after
1375 ** computing the ROWID in case one of the generated columns
1376 ** refers to the ROWID. */
1377 if( pTab
->tabFlags
& TF_HasGenerated
){
1378 testcase( pTab
->tabFlags
& TF_HasVirtual
);
1379 testcase( pTab
->tabFlags
& TF_HasStored
);
1380 sqlite3ComputeGeneratedColumns(pParse
, regCols
+1, pTab
);
1384 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
1385 ** do not attempt any conversions before assembling the record.
1386 ** If this is a real table, attempt conversions as required by the
1387 ** table column affinities.
1390 sqlite3TableAffinity(v
, pTab
, regCols
+1);
1393 /* Fire BEFORE or INSTEAD OF triggers */
1394 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_BEFORE
,
1395 pTab
, regCols
-pTab
->nCol
-1, onError
, endOfLoop
);
1397 sqlite3ReleaseTempRange(pParse
, regCols
, pTab
->nCol
+1);
1401 if( IsVirtual(pTab
) ){
1402 /* The row that the VUpdate opcode will delete: none */
1403 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regIns
);
1406 /* Compute the new rowid */
1408 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, ipkColumn
, regRowid
);
1409 }else if( pSelect
){
1410 /* Rowid already initialized at tag-20191021-001 */
1412 Expr
*pIpk
= pList
->a
[ipkColumn
].pExpr
;
1413 if( pIpk
->op
==TK_NULL
&& !IsVirtual(pTab
) ){
1414 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
1417 sqlite3ExprCode(pParse
, pList
->a
[ipkColumn
].pExpr
, regRowid
);
1420 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
1421 ** to generate a unique primary key value.
1425 if( !IsVirtual(pTab
) ){
1426 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regRowid
); VdbeCoverage(v
);
1427 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
1428 sqlite3VdbeJumpHere(v
, addr1
);
1430 addr1
= sqlite3VdbeCurrentAddr(v
);
1431 sqlite3VdbeAddOp2(v
, OP_IsNull
, regRowid
, addr1
+2); VdbeCoverage(v
);
1433 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regRowid
); VdbeCoverage(v
);
1435 }else if( IsVirtual(pTab
) || withoutRowid
){
1436 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regRowid
);
1438 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
1441 autoIncStep(pParse
, regAutoinc
, regRowid
);
1443 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1444 /* Compute the new value for generated columns after all other
1445 ** columns have already been computed. This must be done after
1446 ** computing the ROWID in case one of the generated columns
1447 ** is derived from the INTEGER PRIMARY KEY. */
1448 if( pTab
->tabFlags
& TF_HasGenerated
){
1449 sqlite3ComputeGeneratedColumns(pParse
, regRowid
+1, pTab
);
1453 /* Generate code to check constraints and generate index keys and
1454 ** do the insertion.
1456 #ifndef SQLITE_OMIT_VIRTUALTABLE
1457 if( IsVirtual(pTab
) ){
1458 const char *pVTab
= (const char *)sqlite3GetVTable(db
, pTab
);
1459 sqlite3VtabMakeWritable(pParse
, pTab
);
1460 sqlite3VdbeAddOp4(v
, OP_VUpdate
, 1, pTab
->nCol
+2, regIns
, pVTab
, P4_VTAB
);
1461 sqlite3VdbeChangeP5(v
, onError
==OE_Default
? OE_Abort
: onError
);
1462 sqlite3MayAbort(pParse
);
1466 int isReplace
= 0;/* Set to true if constraints may cause a replace */
1467 int bUseSeek
; /* True to use OPFLAG_SEEKRESULT */
1468 sqlite3GenerateConstraintChecks(pParse
, pTab
, aRegIdx
, iDataCur
, iIdxCur
,
1469 regIns
, 0, ipkColumn
>=0, onError
, endOfLoop
, &isReplace
, 0, pUpsert
1471 if( db
->flags
& SQLITE_ForeignKeys
){
1472 sqlite3FkCheck(pParse
, pTab
, 0, regIns
, 0, 0);
1475 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1476 ** constraints or (b) there are no triggers and this table is not a
1477 ** parent table in a foreign key constraint. It is safe to set the
1478 ** flag in the second case as if any REPLACE constraint is hit, an
1479 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1480 ** cursor that is disturbed. And these instructions both clear the
1481 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1482 ** functionality. */
1483 bUseSeek
= (isReplace
==0 || !sqlite3VdbeHasSubProgram(v
));
1484 sqlite3CompleteInsertion(pParse
, pTab
, iDataCur
, iIdxCur
,
1485 regIns
, aRegIdx
, 0, appendFlag
, bUseSeek
1488 #ifdef SQLITE_ALLOW_ROWID_IN_VIEW
1489 }else if( pParse
->bReturning
){
1490 /* If there is a RETURNING clause, populate the rowid register with
1491 ** constant value -1, in case one or more of the returned expressions
1492 ** refer to the "rowid" of the view. */
1493 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regRowid
);
1497 /* Update the count of rows that are inserted
1500 sqlite3VdbeAddOp2(v
, OP_AddImm
, regRowCount
, 1);
1504 /* Code AFTER triggers */
1505 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_AFTER
,
1506 pTab
, regData
-2-pTab
->nCol
, onError
, endOfLoop
);
1509 /* The bottom of the main insertion loop, if the data source
1510 ** is a SELECT statement.
1512 sqlite3VdbeResolveLabel(v
, endOfLoop
);
1514 sqlite3VdbeAddOp2(v
, OP_Next
, srcTab
, addrCont
); VdbeCoverage(v
);
1515 sqlite3VdbeJumpHere(v
, addrInsTop
);
1516 sqlite3VdbeAddOp1(v
, OP_Close
, srcTab
);
1517 }else if( pSelect
){
1518 sqlite3VdbeGoto(v
, addrCont
);
1520 /* If we are jumping back to an OP_Yield that is preceded by an
1521 ** OP_ReleaseReg, set the p5 flag on the OP_Goto so that the
1522 ** OP_ReleaseReg will be included in the loop. */
1523 if( sqlite3VdbeGetOp(v
, addrCont
-1)->opcode
==OP_ReleaseReg
){
1524 assert( sqlite3VdbeGetOp(v
, addrCont
)->opcode
==OP_Yield
);
1525 sqlite3VdbeChangeP5(v
, 1);
1528 sqlite3VdbeJumpHere(v
, addrInsTop
);
1531 #ifndef SQLITE_OMIT_XFER_OPT
1533 #endif /* SQLITE_OMIT_XFER_OPT */
1534 /* Update the sqlite_sequence table by storing the content of the
1535 ** maximum rowid counter values recorded while inserting into
1536 ** autoincrement tables.
1538 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 ){
1539 sqlite3AutoincrementEnd(pParse
);
1543 ** Return the number of rows inserted. If this routine is
1544 ** generating code because of a call to sqlite3NestedParse(), do not
1545 ** invoke the callback function.
1548 sqlite3CodeChangeCount(v
, regRowCount
, "rows inserted");
1552 sqlite3SrcListDelete(db
, pTabList
);
1553 sqlite3ExprListDelete(db
, pList
);
1554 sqlite3UpsertDelete(db
, pUpsert
);
1555 sqlite3SelectDelete(db
, pSelect
);
1556 sqlite3IdListDelete(db
, pColumn
);
1557 if( aRegIdx
) sqlite3DbNNFreeNN(db
, aRegIdx
);
1560 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1561 ** they may interfere with compilation of other functions in this file
1562 ** (or in another file, if this file becomes part of the amalgamation). */
1574 ** Meanings of bits in of pWalker->eCode for
1575 ** sqlite3ExprReferencesUpdatedColumn()
1577 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1578 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1580 /* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
1581 * Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
1582 ** expression node references any of the
1583 ** columns that are being modified by an UPDATE statement.
1585 static int checkConstraintExprNode(Walker
*pWalker
, Expr
*pExpr
){
1586 if( pExpr
->op
==TK_COLUMN
){
1587 assert( pExpr
->iColumn
>=0 || pExpr
->iColumn
==-1 );
1588 if( pExpr
->iColumn
>=0 ){
1589 if( pWalker
->u
.aiCol
[pExpr
->iColumn
]>=0 ){
1590 pWalker
->eCode
|= CKCNSTRNT_COLUMN
;
1593 pWalker
->eCode
|= CKCNSTRNT_ROWID
;
1596 return WRC_Continue
;
1600 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1601 ** only columns that are modified by the UPDATE are those for which
1602 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1604 ** Return true if CHECK constraint pExpr uses any of the
1605 ** changing columns (or the rowid if it is changing). In other words,
1606 ** return true if this CHECK constraint must be validated for
1607 ** the new row in the UPDATE statement.
1609 ** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
1610 ** The operation of this routine is the same - return true if an only if
1611 ** the expression uses one or more of columns identified by the second and
1614 int sqlite3ExprReferencesUpdatedColumn(
1615 Expr
*pExpr
, /* The expression to be checked */
1616 int *aiChng
, /* aiChng[x]>=0 if column x changed by the UPDATE */
1617 int chngRowid
/* True if UPDATE changes the rowid */
1620 memset(&w
, 0, sizeof(w
));
1622 w
.xExprCallback
= checkConstraintExprNode
;
1624 sqlite3WalkExpr(&w
, pExpr
);
1626 testcase( (w
.eCode
& CKCNSTRNT_ROWID
)!=0 );
1627 w
.eCode
&= ~CKCNSTRNT_ROWID
;
1629 testcase( w
.eCode
==0 );
1630 testcase( w
.eCode
==CKCNSTRNT_COLUMN
);
1631 testcase( w
.eCode
==CKCNSTRNT_ROWID
);
1632 testcase( w
.eCode
==(CKCNSTRNT_ROWID
|CKCNSTRNT_COLUMN
) );
1637 ** The sqlite3GenerateConstraintChecks() routine usually wants to visit
1638 ** the indexes of a table in the order provided in the Table->pIndex list.
1639 ** However, sometimes (rarely - when there is an upsert) it wants to visit
1640 ** the indexes in a different order. The following data structures accomplish
1643 ** The IndexIterator object is used to walk through all of the indexes
1644 ** of a table in either Index.pNext order, or in some other order established
1645 ** by an array of IndexListTerm objects.
1647 typedef struct IndexListTerm IndexListTerm
;
1648 typedef struct IndexIterator IndexIterator
;
1649 struct IndexIterator
{
1650 int eType
; /* 0 for Index.pNext list. 1 for an array of IndexListTerm */
1651 int i
; /* Index of the current item from the list */
1653 struct { /* Use this object for eType==0: A Index.pNext list */
1654 Index
*pIdx
; /* The current Index */
1656 struct { /* Use this object for eType==1; Array of IndexListTerm */
1657 int nIdx
; /* Size of the array */
1658 IndexListTerm
*aIdx
; /* Array of IndexListTerms */
1663 /* When IndexIterator.eType==1, then each index is an array of instances
1664 ** of the following object
1666 struct IndexListTerm
{
1667 Index
*p
; /* The index */
1668 int ix
; /* Which entry in the original Table.pIndex list is this index*/
1671 /* Return the first index on the list */
1672 static Index
*indexIteratorFirst(IndexIterator
*pIter
, int *pIx
){
1673 assert( pIter
->i
==0 );
1675 *pIx
= pIter
->u
.ax
.aIdx
[0].ix
;
1676 return pIter
->u
.ax
.aIdx
[0].p
;
1679 return pIter
->u
.lx
.pIdx
;
1683 /* Return the next index from the list. Return NULL when out of indexes */
1684 static Index
*indexIteratorNext(IndexIterator
*pIter
, int *pIx
){
1687 if( i
>=pIter
->u
.ax
.nIdx
){
1691 *pIx
= pIter
->u
.ax
.aIdx
[i
].ix
;
1692 return pIter
->u
.ax
.aIdx
[i
].p
;
1695 pIter
->u
.lx
.pIdx
= pIter
->u
.lx
.pIdx
->pNext
;
1696 return pIter
->u
.lx
.pIdx
;
1701 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1704 ** The regNewData parameter is the first register in a range that contains
1705 ** the data to be inserted or the data after the update. There will be
1706 ** pTab->nCol+1 registers in this range. The first register (the one
1707 ** that regNewData points to) will contain the new rowid, or NULL in the
1708 ** case of a WITHOUT ROWID table. The second register in the range will
1709 ** contain the content of the first table column. The third register will
1710 ** contain the content of the second table column. And so forth.
1712 ** The regOldData parameter is similar to regNewData except that it contains
1713 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1714 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1715 ** checking regOldData for zero.
1717 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1718 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1719 ** might be modified by the UPDATE. If pkChng is false, then the key of
1720 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1722 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1723 ** was explicitly specified as part of the INSERT statement. If pkChng
1724 ** is zero, it means that the either rowid is computed automatically or
1725 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1726 ** pkChng will only be true if the INSERT statement provides an integer
1727 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1729 ** The code generated by this routine will store new index entries into
1730 ** registers identified by aRegIdx[]. No index entry is created for
1731 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1732 ** the same as the order of indices on the linked list of indices
1735 ** (2019-05-07) The generated code also creates a new record for the
1736 ** main table, if pTab is a rowid table, and stores that record in the
1737 ** register identified by aRegIdx[nIdx] - in other words in the first
1738 ** entry of aRegIdx[] past the last index. It is important that the
1739 ** record be generated during constraint checks to avoid affinity changes
1740 ** to the register content that occur after constraint checks but before
1741 ** the new record is inserted.
1743 ** The caller must have already opened writeable cursors on the main
1744 ** table and all applicable indices (that is to say, all indices for which
1745 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1746 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1747 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1748 ** for the first index in the pTab->pIndex list. Cursors for other indices
1749 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1751 ** This routine also generates code to check constraints. NOT NULL,
1752 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1753 ** then the appropriate action is performed. There are five possible
1754 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1756 ** Constraint type Action What Happens
1757 ** --------------- ---------- ----------------------------------------
1758 ** any ROLLBACK The current transaction is rolled back and
1759 ** sqlite3_step() returns immediately with a
1760 ** return code of SQLITE_CONSTRAINT.
1762 ** any ABORT Back out changes from the current command
1763 ** only (do not do a complete rollback) then
1764 ** cause sqlite3_step() to return immediately
1765 ** with SQLITE_CONSTRAINT.
1767 ** any FAIL Sqlite3_step() returns immediately with a
1768 ** return code of SQLITE_CONSTRAINT. The
1769 ** transaction is not rolled back and any
1770 ** changes to prior rows are retained.
1772 ** any IGNORE The attempt in insert or update the current
1773 ** row is skipped, without throwing an error.
1774 ** Processing continues with the next row.
1775 ** (There is an immediate jump to ignoreDest.)
1777 ** NOT NULL REPLACE The NULL value is replace by the default
1778 ** value for that column. If the default value
1779 ** is NULL, the action is the same as ABORT.
1781 ** UNIQUE REPLACE The other row that conflicts with the row
1782 ** being inserted is removed.
1784 ** CHECK REPLACE Illegal. The results in an exception.
1786 ** Which action to take is determined by the overrideError parameter.
1787 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1788 ** is used. Or if pParse->onError==OE_Default then the onError value
1789 ** for the constraint is used.
1791 void sqlite3GenerateConstraintChecks(
1792 Parse
*pParse
, /* The parser context */
1793 Table
*pTab
, /* The table being inserted or updated */
1794 int *aRegIdx
, /* Use register aRegIdx[i] for index i. 0 for unused */
1795 int iDataCur
, /* Canonical data cursor (main table or PK index) */
1796 int iIdxCur
, /* First index cursor */
1797 int regNewData
, /* First register in a range holding values to insert */
1798 int regOldData
, /* Previous content. 0 for INSERTs */
1799 u8 pkChng
, /* Non-zero if the rowid or PRIMARY KEY changed */
1800 u8 overrideError
, /* Override onError to this if not OE_Default */
1801 int ignoreDest
, /* Jump to this label on an OE_Ignore resolution */
1802 int *pbMayReplace
, /* OUT: Set to true if constraint may cause a replace */
1803 int *aiChng
, /* column i is unchanged if aiChng[i]<0 */
1804 Upsert
*pUpsert
/* ON CONFLICT clauses, if any. NULL otherwise */
1806 Vdbe
*v
; /* VDBE under construction */
1807 Index
*pIdx
; /* Pointer to one of the indices */
1808 Index
*pPk
= 0; /* The PRIMARY KEY index for WITHOUT ROWID tables */
1809 sqlite3
*db
; /* Database connection */
1810 int i
; /* loop counter */
1811 int ix
; /* Index loop counter */
1812 int nCol
; /* Number of columns */
1813 int onError
; /* Conflict resolution strategy */
1814 int seenReplace
= 0; /* True if REPLACE is used to resolve INT PK conflict */
1815 int nPkField
; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
1816 Upsert
*pUpsertClause
= 0; /* The specific ON CONFLICT clause for pIdx */
1817 u8 isUpdate
; /* True if this is an UPDATE operation */
1818 u8 bAffinityDone
= 0; /* True if the OP_Affinity operation has been run */
1819 int upsertIpkReturn
= 0; /* Address of Goto at end of IPK uniqueness check */
1820 int upsertIpkDelay
= 0; /* Address of Goto to bypass initial IPK check */
1821 int ipkTop
= 0; /* Top of the IPK uniqueness check */
1822 int ipkBottom
= 0; /* OP_Goto at the end of the IPK uniqueness check */
1823 /* Variables associated with retesting uniqueness constraints after
1824 ** replace triggers fire have run */
1825 int regTrigCnt
; /* Register used to count replace trigger invocations */
1826 int addrRecheck
= 0; /* Jump here to recheck all uniqueness constraints */
1827 int lblRecheckOk
= 0; /* Each recheck jumps to this label if it passes */
1828 Trigger
*pTrigger
; /* List of DELETE triggers on the table pTab */
1829 int nReplaceTrig
= 0; /* Number of replace triggers coded */
1830 IndexIterator sIdxIter
; /* Index iterator */
1832 isUpdate
= regOldData
!=0;
1836 assert( !IsView(pTab
) ); /* This table is not a VIEW */
1839 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1840 ** normal rowid tables. nPkField is the number of key fields in the
1841 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1842 ** number of fields in the true primary key of the table. */
1843 if( HasRowid(pTab
) ){
1847 pPk
= sqlite3PrimaryKeyIndex(pTab
);
1848 nPkField
= pPk
->nKeyCol
;
1851 /* Record that this module has started */
1852 VdbeModuleComment((v
, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
1853 iDataCur
, iIdxCur
, regNewData
, regOldData
, pkChng
));
1855 /* Test all NOT NULL constraints.
1857 if( pTab
->tabFlags
& TF_HasNotNull
){
1858 int b2ndPass
= 0; /* True if currently running 2nd pass */
1859 int nSeenReplace
= 0; /* Number of ON CONFLICT REPLACE operations */
1860 int nGenerated
= 0; /* Number of generated columns with NOT NULL */
1861 while(1){ /* Make 2 passes over columns. Exit loop via "break" */
1862 for(i
=0; i
<nCol
; i
++){
1863 int iReg
; /* Register holding column value */
1864 Column
*pCol
= &pTab
->aCol
[i
]; /* The column to check for NOT NULL */
1865 int isGenerated
; /* non-zero if column is generated */
1866 onError
= pCol
->notNull
;
1867 if( onError
==OE_None
) continue; /* No NOT NULL on this column */
1868 if( i
==pTab
->iPKey
){
1869 continue; /* ROWID is never NULL */
1871 isGenerated
= pCol
->colFlags
& COLFLAG_GENERATED
;
1872 if( isGenerated
&& !b2ndPass
){
1874 continue; /* Generated columns processed on 2nd pass */
1876 if( aiChng
&& aiChng
[i
]<0 && !isGenerated
){
1877 /* Do not check NOT NULL on columns that do not change */
1880 if( overrideError
!=OE_Default
){
1881 onError
= overrideError
;
1882 }else if( onError
==OE_Default
){
1885 if( onError
==OE_Replace
){
1886 if( b2ndPass
/* REPLACE becomes ABORT on the 2nd pass */
1887 || pCol
->iDflt
==0 /* REPLACE is ABORT if no DEFAULT value */
1889 testcase( pCol
->colFlags
& COLFLAG_VIRTUAL
);
1890 testcase( pCol
->colFlags
& COLFLAG_STORED
);
1891 testcase( pCol
->colFlags
& COLFLAG_GENERATED
);
1894 assert( !isGenerated
);
1896 }else if( b2ndPass
&& !isGenerated
){
1899 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1900 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1901 testcase( i
!=sqlite3TableColumnToStorage(pTab
, i
) );
1902 iReg
= sqlite3TableColumnToStorage(pTab
, i
) + regNewData
+ 1;
1905 int addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, iReg
);
1907 assert( (pCol
->colFlags
& COLFLAG_GENERATED
)==0 );
1909 sqlite3ExprCodeCopy(pParse
,
1910 sqlite3ColumnExpr(pTab
, pCol
), iReg
);
1911 sqlite3VdbeJumpHere(v
, addr1
);
1915 sqlite3MayAbort(pParse
);
1916 /* no break */ deliberate_fall_through
1919 char *zMsg
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
,
1921 testcase( zMsg
==0 && db
->mallocFailed
==0 );
1922 sqlite3VdbeAddOp3(v
, OP_HaltIfNull
, SQLITE_CONSTRAINT_NOTNULL
,
1924 sqlite3VdbeAppendP4(v
, zMsg
, P4_DYNAMIC
);
1925 sqlite3VdbeChangeP5(v
, P5_ConstraintNotNull
);
1930 assert( onError
==OE_Ignore
);
1931 sqlite3VdbeAddOp2(v
, OP_IsNull
, iReg
, ignoreDest
);
1935 } /* end switch(onError) */
1936 } /* end loop i over columns */
1937 if( nGenerated
==0 && nSeenReplace
==0 ){
1938 /* If there are no generated columns with NOT NULL constraints
1939 ** and no NOT NULL ON CONFLICT REPLACE constraints, then a single
1940 ** pass is sufficient */
1943 if( b2ndPass
) break; /* Never need more than 2 passes */
1945 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1946 if( nSeenReplace
>0 && (pTab
->tabFlags
& TF_HasGenerated
)!=0 ){
1947 /* If any NOT NULL ON CONFLICT REPLACE constraints fired on the
1948 ** first pass, recomputed values for all generated columns, as
1949 ** those values might depend on columns affected by the REPLACE.
1951 sqlite3ComputeGeneratedColumns(pParse
, regNewData
+1, pTab
);
1954 } /* end of 2-pass loop */
1955 } /* end if( has-not-null-constraints ) */
1957 /* Test all CHECK constraints
1959 #ifndef SQLITE_OMIT_CHECK
1960 if( pTab
->pCheck
&& (db
->flags
& SQLITE_IgnoreChecks
)==0 ){
1961 ExprList
*pCheck
= pTab
->pCheck
;
1962 pParse
->iSelfTab
= -(regNewData
+1);
1963 onError
= overrideError
!=OE_Default
? overrideError
: OE_Abort
;
1964 for(i
=0; i
<pCheck
->nExpr
; i
++){
1967 Expr
*pExpr
= pCheck
->a
[i
].pExpr
;
1969 && !sqlite3ExprReferencesUpdatedColumn(pExpr
, aiChng
, pkChng
)
1971 /* The check constraints do not reference any of the columns being
1972 ** updated so there is no point it verifying the check constraint */
1975 if( bAffinityDone
==0 ){
1976 sqlite3TableAffinity(v
, pTab
, regNewData
+1);
1979 allOk
= sqlite3VdbeMakeLabel(pParse
);
1980 sqlite3VdbeVerifyAbortable(v
, onError
);
1981 pCopy
= sqlite3ExprDup(db
, pExpr
, 0);
1982 if( !db
->mallocFailed
){
1983 sqlite3ExprIfTrue(pParse
, pCopy
, allOk
, SQLITE_JUMPIFNULL
);
1985 sqlite3ExprDelete(db
, pCopy
);
1986 if( onError
==OE_Ignore
){
1987 sqlite3VdbeGoto(v
, ignoreDest
);
1989 char *zName
= pCheck
->a
[i
].zEName
;
1990 assert( zName
!=0 || pParse
->db
->mallocFailed
);
1991 if( onError
==OE_Replace
) onError
= OE_Abort
; /* IMP: R-26383-51744 */
1992 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_CHECK
,
1993 onError
, zName
, P4_TRANSIENT
,
1994 P5_ConstraintCheck
);
1996 sqlite3VdbeResolveLabel(v
, allOk
);
1998 pParse
->iSelfTab
= 0;
2000 #endif /* !defined(SQLITE_OMIT_CHECK) */
2002 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
2006 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
2009 ** OE_Fail and OE_Ignore must happen before any changes are made.
2010 ** OE_Update guarantees that only a single row will change, so it
2011 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
2012 ** could happen in any order, but they are grouped up front for
2015 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
2016 ** The order of constraints used to have OE_Update as (2) and OE_Abort
2017 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
2018 ** constraint before any others, so it had to be moved.
2020 ** Constraint checking code is generated in this order:
2021 ** (A) The rowid constraint
2022 ** (B) Unique index constraints that do not have OE_Replace as their
2023 ** default conflict resolution strategy
2024 ** (C) Unique index that do use OE_Replace by default.
2026 ** The ordering of (2) and (3) is accomplished by making sure the linked
2027 ** list of indexes attached to a table puts all OE_Replace indexes last
2028 ** in the list. See sqlite3CreateIndex() for where that happens.
2032 sIdxIter
.u
.ax
.aIdx
= 0; /* Silence harmless compiler warning */
2033 sIdxIter
.u
.lx
.pIdx
= pTab
->pIndex
;
2035 if( pUpsert
->pUpsertTarget
==0 ){
2036 /* There is just on ON CONFLICT clause and it has no constraint-target */
2037 assert( pUpsert
->pNextUpsert
==0 );
2038 if( pUpsert
->isDoUpdate
==0 ){
2039 /* A single ON CONFLICT DO NOTHING clause, without a constraint-target.
2040 ** Make all unique constraint resolution be OE_Ignore */
2041 overrideError
= OE_Ignore
;
2044 /* A single ON CONFLICT DO UPDATE. Make all resolutions OE_Update */
2045 overrideError
= OE_Update
;
2047 }else if( pTab
->pIndex
!=0 ){
2048 /* Otherwise, we'll need to run the IndexListTerm array version of the
2049 ** iterator to ensure that all of the ON CONFLICT conditions are
2050 ** checked first and in order. */
2055 for(nIdx
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, nIdx
++){
2056 assert( aRegIdx
[nIdx
]>0 );
2059 sIdxIter
.u
.ax
.nIdx
= nIdx
;
2060 nByte
= (sizeof(IndexListTerm
)+1)*nIdx
+ nIdx
;
2061 sIdxIter
.u
.ax
.aIdx
= sqlite3DbMallocZero(db
, nByte
);
2062 if( sIdxIter
.u
.ax
.aIdx
==0 ) return; /* OOM */
2063 bUsed
= (u8
*)&sIdxIter
.u
.ax
.aIdx
[nIdx
];
2064 pUpsert
->pToFree
= sIdxIter
.u
.ax
.aIdx
;
2065 for(i
=0, pTerm
=pUpsert
; pTerm
; pTerm
=pTerm
->pNextUpsert
){
2066 if( pTerm
->pUpsertTarget
==0 ) break;
2067 if( pTerm
->pUpsertIdx
==0 ) continue; /* Skip ON CONFLICT for the IPK */
2069 pIdx
= pTab
->pIndex
;
2070 while( ALWAYS(pIdx
!=0) && pIdx
!=pTerm
->pUpsertIdx
){
2074 if( bUsed
[jj
] ) continue; /* Duplicate ON CONFLICT clause ignored */
2076 sIdxIter
.u
.ax
.aIdx
[i
].p
= pIdx
;
2077 sIdxIter
.u
.ax
.aIdx
[i
].ix
= jj
;
2080 for(jj
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, jj
++){
2081 if( bUsed
[jj
] ) continue;
2082 sIdxIter
.u
.ax
.aIdx
[i
].p
= pIdx
;
2083 sIdxIter
.u
.ax
.aIdx
[i
].ix
= jj
;
2090 /* Determine if it is possible that triggers (either explicitly coded
2091 ** triggers or FK resolution actions) might run as a result of deletes
2092 ** that happen when OE_Replace conflict resolution occurs. (Call these
2093 ** "replace triggers".) If any replace triggers run, we will need to
2094 ** recheck all of the uniqueness constraints after they have all run.
2095 ** But on the recheck, the resolution is OE_Abort instead of OE_Replace.
2097 ** If replace triggers are a possibility, then
2099 ** (1) Allocate register regTrigCnt and initialize it to zero.
2100 ** That register will count the number of replace triggers that
2101 ** fire. Constraint recheck only occurs if the number is positive.
2102 ** (2) Initialize pTrigger to the list of all DELETE triggers on pTab.
2103 ** (3) Initialize addrRecheck and lblRecheckOk
2105 ** The uniqueness rechecking code will create a series of tests to run
2106 ** in a second pass. The addrRecheck and lblRecheckOk variables are
2107 ** used to link together these tests which are separated from each other
2108 ** in the generate bytecode.
2110 if( (db
->flags
& (SQLITE_RecTriggers
|SQLITE_ForeignKeys
))==0 ){
2111 /* There are not DELETE triggers nor FK constraints. No constraint
2112 ** rechecks are needed. */
2116 if( db
->flags
&SQLITE_RecTriggers
){
2117 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
2118 regTrigCnt
= pTrigger
!=0 || sqlite3FkRequired(pParse
, pTab
, 0, 0);
2121 regTrigCnt
= sqlite3FkRequired(pParse
, pTab
, 0, 0);
2124 /* Replace triggers might exist. Allocate the counter and
2125 ** initialize it to zero. */
2126 regTrigCnt
= ++pParse
->nMem
;
2127 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regTrigCnt
);
2128 VdbeComment((v
, "trigger count"));
2129 lblRecheckOk
= sqlite3VdbeMakeLabel(pParse
);
2130 addrRecheck
= lblRecheckOk
;
2134 /* If rowid is changing, make sure the new rowid does not previously
2135 ** exist in the table.
2137 if( pkChng
&& pPk
==0 ){
2138 int addrRowidOk
= sqlite3VdbeMakeLabel(pParse
);
2140 /* Figure out what action to take in case of a rowid collision */
2141 onError
= pTab
->keyConf
;
2142 if( overrideError
!=OE_Default
){
2143 onError
= overrideError
;
2144 }else if( onError
==OE_Default
){
2148 /* figure out whether or not upsert applies in this case */
2150 pUpsertClause
= sqlite3UpsertOfIndex(pUpsert
,0);
2151 if( pUpsertClause
!=0 ){
2152 if( pUpsertClause
->isDoUpdate
==0 ){
2153 onError
= OE_Ignore
; /* DO NOTHING is the same as INSERT OR IGNORE */
2155 onError
= OE_Update
; /* DO UPDATE */
2158 if( pUpsertClause
!=pUpsert
){
2159 /* The first ON CONFLICT clause has a conflict target other than
2160 ** the IPK. We have to jump ahead to that first ON CONFLICT clause
2161 ** and then come back here and deal with the IPK afterwards */
2162 upsertIpkDelay
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2166 /* If the response to a rowid conflict is REPLACE but the response
2167 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
2168 ** to defer the running of the rowid conflict checking until after
2169 ** the UNIQUE constraints have run.
2171 if( onError
==OE_Replace
/* IPK rule is REPLACE */
2172 && onError
!=overrideError
/* Rules for other constraints are different */
2173 && pTab
->pIndex
/* There exist other constraints */
2174 && !upsertIpkDelay
/* IPK check already deferred by UPSERT */
2176 ipkTop
= sqlite3VdbeAddOp0(v
, OP_Goto
)+1;
2177 VdbeComment((v
, "defer IPK REPLACE until last"));
2181 /* pkChng!=0 does not mean that the rowid has changed, only that
2182 ** it might have changed. Skip the conflict logic below if the rowid
2184 sqlite3VdbeAddOp3(v
, OP_Eq
, regNewData
, addrRowidOk
, regOldData
);
2185 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
2189 /* Check to see if the new rowid already exists in the table. Skip
2190 ** the following conflict logic if it does not. */
2191 VdbeNoopComment((v
, "uniqueness check for ROWID"));
2192 sqlite3VdbeVerifyAbortable(v
, onError
);
2193 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, addrRowidOk
, regNewData
);
2199 /* no break */ deliberate_fall_through
2204 testcase( onError
==OE_Rollback
);
2205 testcase( onError
==OE_Abort
);
2206 testcase( onError
==OE_Fail
);
2207 sqlite3RowidConstraint(pParse
, onError
, pTab
);
2211 /* If there are DELETE triggers on this table and the
2212 ** recursive-triggers flag is set, call GenerateRowDelete() to
2213 ** remove the conflicting row from the table. This will fire
2214 ** the triggers and remove both the table and index b-tree entries.
2216 ** Otherwise, if there are no triggers or the recursive-triggers
2217 ** flag is not set, but the table has one or more indexes, call
2218 ** GenerateRowIndexDelete(). This removes the index b-tree entries
2219 ** only. The table b-tree entry will be replaced by the new entry
2220 ** when it is inserted.
2222 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
2223 ** also invoke MultiWrite() to indicate that this VDBE may require
2224 ** statement rollback (if the statement is aborted after the delete
2225 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
2226 ** but being more selective here allows statements like:
2228 ** REPLACE INTO t(rowid) VALUES($newrowid)
2230 ** to run without a statement journal if there are no indexes on the
2234 sqlite3MultiWrite(pParse
);
2235 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
2236 regNewData
, 1, 0, OE_Replace
, 1, -1);
2237 sqlite3VdbeAddOp2(v
, OP_AddImm
, regTrigCnt
, 1); /* incr trigger cnt */
2240 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2241 assert( HasRowid(pTab
) );
2242 /* This OP_Delete opcode fires the pre-update-hook only. It does
2243 ** not modify the b-tree. It is more efficient to let the coming
2244 ** OP_Insert replace the existing entry than it is to delete the
2245 ** existing entry and then insert a new one. */
2246 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, OPFLAG_ISNOOP
);
2247 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
2248 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
2250 sqlite3MultiWrite(pParse
);
2251 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
,0,-1);
2257 #ifndef SQLITE_OMIT_UPSERT
2259 sqlite3UpsertDoUpdate(pParse
, pUpsert
, pTab
, 0, iDataCur
);
2260 /* no break */ deliberate_fall_through
2264 testcase( onError
==OE_Ignore
);
2265 sqlite3VdbeGoto(v
, ignoreDest
);
2269 sqlite3VdbeResolveLabel(v
, addrRowidOk
);
2270 if( pUpsert
&& pUpsertClause
!=pUpsert
){
2271 upsertIpkReturn
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2273 ipkBottom
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2274 sqlite3VdbeJumpHere(v
, ipkTop
-1);
2278 /* Test all UNIQUE constraints by creating entries for each UNIQUE
2279 ** index and making sure that duplicate entries do not already exist.
2280 ** Compute the revised record entries for indices as we go.
2282 ** This loop also handles the case of the PRIMARY KEY index for a
2283 ** WITHOUT ROWID table.
2285 for(pIdx
= indexIteratorFirst(&sIdxIter
, &ix
);
2287 pIdx
= indexIteratorNext(&sIdxIter
, &ix
)
2289 int regIdx
; /* Range of registers holding content for pIdx */
2290 int regR
; /* Range of registers holding conflicting PK */
2291 int iThisCur
; /* Cursor for this UNIQUE index */
2292 int addrUniqueOk
; /* Jump here if the UNIQUE constraint is satisfied */
2293 int addrConflictCk
; /* First opcode in the conflict check logic */
2295 if( aRegIdx
[ix
]==0 ) continue; /* Skip indices that do not change */
2297 pUpsertClause
= sqlite3UpsertOfIndex(pUpsert
, pIdx
);
2298 if( upsertIpkDelay
&& pUpsertClause
==pUpsert
){
2299 sqlite3VdbeJumpHere(v
, upsertIpkDelay
);
2302 addrUniqueOk
= sqlite3VdbeMakeLabel(pParse
);
2303 if( bAffinityDone
==0 ){
2304 sqlite3TableAffinity(v
, pTab
, regNewData
+1);
2307 VdbeNoopComment((v
, "prep index %s", pIdx
->zName
));
2308 iThisCur
= iIdxCur
+ix
;
2311 /* Skip partial indices for which the WHERE clause is not true */
2312 if( pIdx
->pPartIdxWhere
){
2313 sqlite3VdbeAddOp2(v
, OP_Null
, 0, aRegIdx
[ix
]);
2314 pParse
->iSelfTab
= -(regNewData
+1);
2315 sqlite3ExprIfFalseDup(pParse
, pIdx
->pPartIdxWhere
, addrUniqueOk
,
2317 pParse
->iSelfTab
= 0;
2320 /* Create a record for this index entry as it should appear after
2321 ** the insert or update. Store that record in the aRegIdx[ix] register
2323 regIdx
= aRegIdx
[ix
]+1;
2324 for(i
=0; i
<pIdx
->nColumn
; i
++){
2325 int iField
= pIdx
->aiColumn
[i
];
2327 if( iField
==XN_EXPR
){
2328 pParse
->iSelfTab
= -(regNewData
+1);
2329 sqlite3ExprCodeCopy(pParse
, pIdx
->aColExpr
->a
[i
].pExpr
, regIdx
+i
);
2330 pParse
->iSelfTab
= 0;
2331 VdbeComment((v
, "%s column %d", pIdx
->zName
, i
));
2332 }else if( iField
==XN_ROWID
|| iField
==pTab
->iPKey
){
2334 sqlite3VdbeAddOp2(v
, OP_IntCopy
, x
, regIdx
+i
);
2335 VdbeComment((v
, "rowid"));
2337 testcase( sqlite3TableColumnToStorage(pTab
, iField
)!=iField
);
2338 x
= sqlite3TableColumnToStorage(pTab
, iField
) + regNewData
+ 1;
2339 sqlite3VdbeAddOp2(v
, OP_SCopy
, x
, regIdx
+i
);
2340 VdbeComment((v
, "%s", pTab
->aCol
[iField
].zCnName
));
2343 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regIdx
, pIdx
->nColumn
, aRegIdx
[ix
]);
2344 VdbeComment((v
, "for %s", pIdx
->zName
));
2345 #ifdef SQLITE_ENABLE_NULL_TRIM
2346 if( pIdx
->idxType
==SQLITE_IDXTYPE_PRIMARYKEY
){
2347 sqlite3SetMakeRecordP5(v
, pIdx
->pTable
);
2350 sqlite3VdbeReleaseRegisters(pParse
, regIdx
, pIdx
->nColumn
, 0, 0);
2352 /* In an UPDATE operation, if this index is the PRIMARY KEY index
2353 ** of a WITHOUT ROWID table and there has been no change the
2354 ** primary key, then no collision is possible. The collision detection
2355 ** logic below can all be skipped. */
2356 if( isUpdate
&& pPk
==pIdx
&& pkChng
==0 ){
2357 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
2361 /* Find out what action to take in case there is a uniqueness conflict */
2362 onError
= pIdx
->onError
;
2363 if( onError
==OE_None
){
2364 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
2365 continue; /* pIdx is not a UNIQUE index */
2367 if( overrideError
!=OE_Default
){
2368 onError
= overrideError
;
2369 }else if( onError
==OE_Default
){
2373 /* Figure out if the upsert clause applies to this index */
2374 if( pUpsertClause
){
2375 if( pUpsertClause
->isDoUpdate
==0 ){
2376 onError
= OE_Ignore
; /* DO NOTHING is the same as INSERT OR IGNORE */
2378 onError
= OE_Update
; /* DO UPDATE */
2382 /* Collision detection may be omitted if all of the following are true:
2383 ** (1) The conflict resolution algorithm is REPLACE
2384 ** (2) The table is a WITHOUT ROWID table
2385 ** (3) There are no secondary indexes on the table
2386 ** (4) No delete triggers need to be fired if there is a conflict
2387 ** (5) No FK constraint counters need to be updated if a conflict occurs.
2389 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row
2390 ** must be explicitly deleted in order to ensure any pre-update hook
2392 assert( IsOrdinaryTable(pTab
) );
2393 #ifndef SQLITE_ENABLE_PREUPDATE_HOOK
2394 if( (ix
==0 && pIdx
->pNext
==0) /* Condition 3 */
2395 && pPk
==pIdx
/* Condition 2 */
2396 && onError
==OE_Replace
/* Condition 1 */
2397 && ( 0==(db
->flags
&SQLITE_RecTriggers
) || /* Condition 4 */
2398 0==sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0))
2399 && ( 0==(db
->flags
&SQLITE_ForeignKeys
) || /* Condition 5 */
2400 (0==pTab
->u
.tab
.pFKey
&& 0==sqlite3FkReferences(pTab
)))
2402 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
2405 #endif /* ifndef SQLITE_ENABLE_PREUPDATE_HOOK */
2407 /* Check to see if the new index entry will be unique */
2408 sqlite3VdbeVerifyAbortable(v
, onError
);
2410 sqlite3VdbeAddOp4Int(v
, OP_NoConflict
, iThisCur
, addrUniqueOk
,
2411 regIdx
, pIdx
->nKeyCol
); VdbeCoverage(v
);
2413 /* Generate code to handle collisions */
2414 regR
= pIdx
==pPk
? regIdx
: sqlite3GetTempRange(pParse
, nPkField
);
2415 if( isUpdate
|| onError
==OE_Replace
){
2416 if( HasRowid(pTab
) ){
2417 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iThisCur
, regR
);
2418 /* Conflict only if the rowid of the existing index entry
2419 ** is different from old-rowid */
2421 sqlite3VdbeAddOp3(v
, OP_Eq
, regR
, addrUniqueOk
, regOldData
);
2422 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
2427 /* Extract the PRIMARY KEY from the end of the index entry and
2428 ** store it in registers regR..regR+nPk-1 */
2430 for(i
=0; i
<pPk
->nKeyCol
; i
++){
2431 assert( pPk
->aiColumn
[i
]>=0 );
2432 x
= sqlite3TableColumnToIndex(pIdx
, pPk
->aiColumn
[i
]);
2433 sqlite3VdbeAddOp3(v
, OP_Column
, iThisCur
, x
, regR
+i
);
2434 VdbeComment((v
, "%s.%s", pTab
->zName
,
2435 pTab
->aCol
[pPk
->aiColumn
[i
]].zCnName
));
2439 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
2440 ** table, only conflict if the new PRIMARY KEY values are actually
2441 ** different from the old. See TH3 withoutrowid04.test.
2443 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
2444 ** of the matched index row are different from the original PRIMARY
2445 ** KEY values of this row before the update. */
2446 int addrJump
= sqlite3VdbeCurrentAddr(v
)+pPk
->nKeyCol
;
2448 int regCmp
= (IsPrimaryKeyIndex(pIdx
) ? regIdx
: regR
);
2450 for(i
=0; i
<pPk
->nKeyCol
; i
++){
2451 char *p4
= (char*)sqlite3LocateCollSeq(pParse
, pPk
->azColl
[i
]);
2452 x
= pPk
->aiColumn
[i
];
2454 if( i
==(pPk
->nKeyCol
-1) ){
2455 addrJump
= addrUniqueOk
;
2458 x
= sqlite3TableColumnToStorage(pTab
, x
);
2459 sqlite3VdbeAddOp4(v
, op
,
2460 regOldData
+1+x
, addrJump
, regCmp
+i
, p4
, P4_COLLSEQ
2462 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
2463 VdbeCoverageIf(v
, op
==OP_Eq
);
2464 VdbeCoverageIf(v
, op
==OP_Ne
);
2470 /* Generate code that executes if the new index entry is not unique */
2471 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
2472 || onError
==OE_Ignore
|| onError
==OE_Replace
|| onError
==OE_Update
);
2477 testcase( onError
==OE_Rollback
);
2478 testcase( onError
==OE_Abort
);
2479 testcase( onError
==OE_Fail
);
2480 sqlite3UniqueConstraint(pParse
, onError
, pIdx
);
2483 #ifndef SQLITE_OMIT_UPSERT
2485 sqlite3UpsertDoUpdate(pParse
, pUpsert
, pTab
, pIdx
, iIdxCur
+ix
);
2486 /* no break */ deliberate_fall_through
2490 testcase( onError
==OE_Ignore
);
2491 sqlite3VdbeGoto(v
, ignoreDest
);
2495 int nConflictCk
; /* Number of opcodes in conflict check logic */
2497 assert( onError
==OE_Replace
);
2498 nConflictCk
= sqlite3VdbeCurrentAddr(v
) - addrConflictCk
;
2499 assert( nConflictCk
>0 || db
->mallocFailed
);
2500 testcase( nConflictCk
<=0 );
2501 testcase( nConflictCk
>1 );
2503 sqlite3MultiWrite(pParse
);
2506 if( pTrigger
&& isUpdate
){
2507 sqlite3VdbeAddOp1(v
, OP_CursorLock
, iDataCur
);
2509 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
2510 regR
, nPkField
, 0, OE_Replace
,
2511 (pIdx
==pPk
? ONEPASS_SINGLE
: ONEPASS_OFF
), iThisCur
);
2512 if( pTrigger
&& isUpdate
){
2513 sqlite3VdbeAddOp1(v
, OP_CursorUnlock
, iDataCur
);
2516 int addrBypass
; /* Jump destination to bypass recheck logic */
2518 sqlite3VdbeAddOp2(v
, OP_AddImm
, regTrigCnt
, 1); /* incr trigger cnt */
2519 addrBypass
= sqlite3VdbeAddOp0(v
, OP_Goto
); /* Bypass recheck */
2520 VdbeComment((v
, "bypass recheck"));
2522 /* Here we insert code that will be invoked after all constraint
2523 ** checks have run, if and only if one or more replace triggers
2525 sqlite3VdbeResolveLabel(v
, lblRecheckOk
);
2526 lblRecheckOk
= sqlite3VdbeMakeLabel(pParse
);
2527 if( pIdx
->pPartIdxWhere
){
2528 /* Bypass the recheck if this partial index is not defined
2529 ** for the current row */
2530 sqlite3VdbeAddOp2(v
, OP_IsNull
, regIdx
-1, lblRecheckOk
);
2533 /* Copy the constraint check code from above, except change
2534 ** the constraint-ok jump destination to be the address of
2535 ** the next retest block */
2536 while( nConflictCk
>0 ){
2537 VdbeOp x
; /* Conflict check opcode to copy */
2538 /* The sqlite3VdbeAddOp4() call might reallocate the opcode array.
2539 ** Hence, make a complete copy of the opcode, rather than using
2540 ** a pointer to the opcode. */
2541 x
= *sqlite3VdbeGetOp(v
, addrConflictCk
);
2542 if( x
.opcode
!=OP_IdxRowid
){
2543 int p2
; /* New P2 value for copied conflict check opcode */
2545 if( sqlite3OpcodeProperty
[x
.opcode
]&OPFLG_JUMP
){
2550 zP4
= x
.p4type
==P4_INT32
? SQLITE_INT_TO_PTR(x
.p4
.i
) : x
.p4
.z
;
2551 sqlite3VdbeAddOp4(v
, x
.opcode
, x
.p1
, p2
, x
.p3
, zP4
, x
.p4type
);
2552 sqlite3VdbeChangeP5(v
, x
.p5
);
2553 VdbeCoverageIf(v
, p2
!=x
.p2
);
2558 /* If the retest fails, issue an abort */
2559 sqlite3UniqueConstraint(pParse
, OE_Abort
, pIdx
);
2561 sqlite3VdbeJumpHere(v
, addrBypass
); /* Terminate the recheck bypass */
2567 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
2568 if( regR
!=regIdx
) sqlite3ReleaseTempRange(pParse
, regR
, nPkField
);
2571 && sqlite3UpsertNextIsIPK(pUpsertClause
)
2573 sqlite3VdbeGoto(v
, upsertIpkDelay
+1);
2574 sqlite3VdbeJumpHere(v
, upsertIpkReturn
);
2575 upsertIpkReturn
= 0;
2579 /* If the IPK constraint is a REPLACE, run it last */
2581 sqlite3VdbeGoto(v
, ipkTop
);
2582 VdbeComment((v
, "Do IPK REPLACE"));
2583 assert( ipkBottom
>0 );
2584 sqlite3VdbeJumpHere(v
, ipkBottom
);
2587 /* Recheck all uniqueness constraints after replace triggers have run */
2588 testcase( regTrigCnt
!=0 && nReplaceTrig
==0 );
2589 assert( regTrigCnt
!=0 || nReplaceTrig
==0 );
2591 sqlite3VdbeAddOp2(v
, OP_IfNot
, regTrigCnt
, lblRecheckOk
);VdbeCoverage(v
);
2594 sqlite3VdbeAddOp3(v
, OP_Eq
, regNewData
, addrRecheck
, regOldData
);
2595 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
2598 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, addrRecheck
, regNewData
);
2600 sqlite3RowidConstraint(pParse
, OE_Abort
, pTab
);
2602 sqlite3VdbeGoto(v
, addrRecheck
);
2604 sqlite3VdbeResolveLabel(v
, lblRecheckOk
);
2607 /* Generate the table record */
2608 if( HasRowid(pTab
) ){
2609 int regRec
= aRegIdx
[ix
];
2610 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regNewData
+1, pTab
->nNVCol
, regRec
);
2611 sqlite3SetMakeRecordP5(v
, pTab
);
2612 if( !bAffinityDone
){
2613 sqlite3TableAffinity(v
, pTab
, 0);
2617 *pbMayReplace
= seenReplace
;
2618 VdbeModuleComment((v
, "END: GenCnstCks(%d)", seenReplace
));
2621 #ifdef SQLITE_ENABLE_NULL_TRIM
2623 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
2624 ** to be the number of columns in table pTab that must not be NULL-trimmed.
2626 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
2628 void sqlite3SetMakeRecordP5(Vdbe
*v
, Table
*pTab
){
2631 /* Records with omitted columns are only allowed for schema format
2632 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
2633 if( pTab
->pSchema
->file_format
<2 ) return;
2635 for(i
=pTab
->nCol
-1; i
>0; i
--){
2636 if( pTab
->aCol
[i
].iDflt
!=0 ) break;
2637 if( pTab
->aCol
[i
].colFlags
& COLFLAG_PRIMKEY
) break;
2639 sqlite3VdbeChangeP5(v
, i
+1);
2644 ** Table pTab is a WITHOUT ROWID table that is being written to. The cursor
2645 ** number is iCur, and register regData contains the new record for the
2646 ** PK index. This function adds code to invoke the pre-update hook,
2647 ** if one is registered.
2649 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2650 static void codeWithoutRowidPreupdate(
2651 Parse
*pParse
, /* Parse context */
2652 Table
*pTab
, /* Table being updated */
2653 int iCur
, /* Cursor number for table */
2654 int regData
/* Data containing new record */
2656 Vdbe
*v
= pParse
->pVdbe
;
2657 int r
= sqlite3GetTempReg(pParse
);
2658 assert( !HasRowid(pTab
) );
2659 assert( 0==(pParse
->db
->mDbFlags
& DBFLAG_Vacuum
) || CORRUPT_DB
);
2660 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, r
);
2661 sqlite3VdbeAddOp4(v
, OP_Insert
, iCur
, regData
, r
, (char*)pTab
, P4_TABLE
);
2662 sqlite3VdbeChangeP5(v
, OPFLAG_ISNOOP
);
2663 sqlite3ReleaseTempReg(pParse
, r
);
2666 # define codeWithoutRowidPreupdate(a,b,c,d)
2670 ** This routine generates code to finish the INSERT or UPDATE operation
2671 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
2672 ** A consecutive range of registers starting at regNewData contains the
2673 ** rowid and the content to be inserted.
2675 ** The arguments to this routine should be the same as the first six
2676 ** arguments to sqlite3GenerateConstraintChecks.
2678 void sqlite3CompleteInsertion(
2679 Parse
*pParse
, /* The parser context */
2680 Table
*pTab
, /* the table into which we are inserting */
2681 int iDataCur
, /* Cursor of the canonical data source */
2682 int iIdxCur
, /* First index cursor */
2683 int regNewData
, /* Range of content */
2684 int *aRegIdx
, /* Register used by each index. 0 for unused indices */
2685 int update_flags
, /* True for UPDATE, False for INSERT */
2686 int appendBias
, /* True if this is likely to be an append */
2687 int useSeekResult
/* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
2689 Vdbe
*v
; /* Prepared statements under construction */
2690 Index
*pIdx
; /* An index being inserted or updated */
2691 u8 pik_flags
; /* flag values passed to the btree insert */
2692 int i
; /* Loop counter */
2694 assert( update_flags
==0
2695 || update_flags
==OPFLAG_ISUPDATE
2696 || update_flags
==(OPFLAG_ISUPDATE
|OPFLAG_SAVEPOSITION
)
2701 assert( !IsView(pTab
) ); /* This table is not a VIEW */
2702 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
2703 /* All REPLACE indexes are at the end of the list */
2704 assert( pIdx
->onError
!=OE_Replace
2706 || pIdx
->pNext
->onError
==OE_Replace
);
2707 if( aRegIdx
[i
]==0 ) continue;
2708 if( pIdx
->pPartIdxWhere
){
2709 sqlite3VdbeAddOp2(v
, OP_IsNull
, aRegIdx
[i
], sqlite3VdbeCurrentAddr(v
)+2);
2712 pik_flags
= (useSeekResult
? OPFLAG_USESEEKRESULT
: 0);
2713 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
2714 pik_flags
|= OPFLAG_NCHANGE
;
2715 pik_flags
|= (update_flags
& OPFLAG_SAVEPOSITION
);
2716 if( update_flags
==0 ){
2717 codeWithoutRowidPreupdate(pParse
, pTab
, iIdxCur
+i
, aRegIdx
[i
]);
2720 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iIdxCur
+i
, aRegIdx
[i
],
2722 pIdx
->uniqNotNull
? pIdx
->nKeyCol
: pIdx
->nColumn
);
2723 sqlite3VdbeChangeP5(v
, pik_flags
);
2725 if( !HasRowid(pTab
) ) return;
2726 if( pParse
->nested
){
2729 pik_flags
= OPFLAG_NCHANGE
;
2730 pik_flags
|= (update_flags
?update_flags
:OPFLAG_LASTROWID
);
2733 pik_flags
|= OPFLAG_APPEND
;
2735 if( useSeekResult
){
2736 pik_flags
|= OPFLAG_USESEEKRESULT
;
2738 sqlite3VdbeAddOp3(v
, OP_Insert
, iDataCur
, aRegIdx
[i
], regNewData
);
2739 if( !pParse
->nested
){
2740 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
2742 sqlite3VdbeChangeP5(v
, pik_flags
);
2746 ** Allocate cursors for the pTab table and all its indices and generate
2747 ** code to open and initialized those cursors.
2749 ** The cursor for the object that contains the complete data (normally
2750 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
2751 ** ROWID table) is returned in *piDataCur. The first index cursor is
2752 ** returned in *piIdxCur. The number of indices is returned.
2754 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
2755 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
2756 ** If iBase is negative, then allocate the next available cursor.
2758 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
2759 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
2760 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
2761 ** pTab->pIndex list.
2763 ** If pTab is a virtual table, then this routine is a no-op and the
2764 ** *piDataCur and *piIdxCur values are left uninitialized.
2766 int sqlite3OpenTableAndIndices(
2767 Parse
*pParse
, /* Parsing context */
2768 Table
*pTab
, /* Table to be opened */
2769 int op
, /* OP_OpenRead or OP_OpenWrite */
2770 u8 p5
, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
2771 int iBase
, /* Use this for the table cursor, if there is one */
2772 u8
*aToOpen
, /* If not NULL: boolean for each table and index */
2773 int *piDataCur
, /* Write the database source cursor number here */
2774 int *piIdxCur
/* Write the first index cursor number here */
2782 assert( op
==OP_OpenRead
|| op
==OP_OpenWrite
);
2783 assert( op
==OP_OpenWrite
|| p5
==0 );
2784 assert( piDataCur
!=0 );
2785 assert( piIdxCur
!=0 );
2786 if( IsVirtual(pTab
) ){
2787 /* This routine is a no-op for virtual tables. Leave the output
2788 ** variables *piDataCur and *piIdxCur set to illegal cursor numbers
2789 ** for improved error detection. */
2790 *piDataCur
= *piIdxCur
= -999;
2793 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
2796 if( iBase
<0 ) iBase
= pParse
->nTab
;
2798 *piDataCur
= iDataCur
;
2799 if( HasRowid(pTab
) && (aToOpen
==0 || aToOpen
[0]) ){
2800 sqlite3OpenTable(pParse
, iDataCur
, iDb
, pTab
, op
);
2801 }else if( pParse
->db
->noSharedCache
==0 ){
2802 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, op
==OP_OpenWrite
, pTab
->zName
);
2805 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
2806 int iIdxCur
= iBase
++;
2807 assert( pIdx
->pSchema
==pTab
->pSchema
);
2808 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
2809 *piDataCur
= iIdxCur
;
2812 if( aToOpen
==0 || aToOpen
[i
+1] ){
2813 sqlite3VdbeAddOp3(v
, op
, iIdxCur
, pIdx
->tnum
, iDb
);
2814 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
2815 sqlite3VdbeChangeP5(v
, p5
);
2816 VdbeComment((v
, "%s", pIdx
->zName
));
2819 if( iBase
>pParse
->nTab
) pParse
->nTab
= iBase
;
2826 ** The following global variable is incremented whenever the
2827 ** transfer optimization is used. This is used for testing
2828 ** purposes only - to make sure the transfer optimization really
2829 ** is happening when it is supposed to.
2831 int sqlite3_xferopt_count
;
2832 #endif /* SQLITE_TEST */
2835 #ifndef SQLITE_OMIT_XFER_OPT
2837 ** Check to see if index pSrc is compatible as a source of data
2838 ** for index pDest in an insert transfer optimization. The rules
2839 ** for a compatible index:
2841 ** * The index is over the same set of columns
2842 ** * The same DESC and ASC markings occurs on all columns
2843 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
2844 ** * The same collating sequence on each column
2845 ** * The index has the exact same WHERE clause
2847 static int xferCompatibleIndex(Index
*pDest
, Index
*pSrc
){
2849 assert( pDest
&& pSrc
);
2850 assert( pDest
->pTable
!=pSrc
->pTable
);
2851 if( pDest
->nKeyCol
!=pSrc
->nKeyCol
|| pDest
->nColumn
!=pSrc
->nColumn
){
2852 return 0; /* Different number of columns */
2854 if( pDest
->onError
!=pSrc
->onError
){
2855 return 0; /* Different conflict resolution strategies */
2857 for(i
=0; i
<pSrc
->nKeyCol
; i
++){
2858 if( pSrc
->aiColumn
[i
]!=pDest
->aiColumn
[i
] ){
2859 return 0; /* Different columns indexed */
2861 if( pSrc
->aiColumn
[i
]==XN_EXPR
){
2862 assert( pSrc
->aColExpr
!=0 && pDest
->aColExpr
!=0 );
2863 if( sqlite3ExprCompare(0, pSrc
->aColExpr
->a
[i
].pExpr
,
2864 pDest
->aColExpr
->a
[i
].pExpr
, -1)!=0 ){
2865 return 0; /* Different expressions in the index */
2868 if( pSrc
->aSortOrder
[i
]!=pDest
->aSortOrder
[i
] ){
2869 return 0; /* Different sort orders */
2871 if( sqlite3_stricmp(pSrc
->azColl
[i
],pDest
->azColl
[i
])!=0 ){
2872 return 0; /* Different collating sequences */
2875 if( sqlite3ExprCompare(0, pSrc
->pPartIdxWhere
, pDest
->pPartIdxWhere
, -1) ){
2876 return 0; /* Different WHERE clauses */
2879 /* If no test above fails then the indices must be compatible */
2884 ** Attempt the transfer optimization on INSERTs of the form
2886 ** INSERT INTO tab1 SELECT * FROM tab2;
2888 ** The xfer optimization transfers raw records from tab2 over to tab1.
2889 ** Columns are not decoded and reassembled, which greatly improves
2890 ** performance. Raw index records are transferred in the same way.
2892 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2893 ** There are lots of rules for determining compatibility - see comments
2894 ** embedded in the code for details.
2896 ** This routine returns TRUE if the optimization is guaranteed to be used.
2897 ** Sometimes the xfer optimization will only work if the destination table
2898 ** is empty - a factor that can only be determined at run-time. In that
2899 ** case, this routine generates code for the xfer optimization but also
2900 ** does a test to see if the destination table is empty and jumps over the
2901 ** xfer optimization code if the test fails. In that case, this routine
2902 ** returns FALSE so that the caller will know to go ahead and generate
2903 ** an unoptimized transfer. This routine also returns FALSE if there
2904 ** is no chance that the xfer optimization can be applied.
2906 ** This optimization is particularly useful at making VACUUM run faster.
2908 static int xferOptimization(
2909 Parse
*pParse
, /* Parser context */
2910 Table
*pDest
, /* The table we are inserting into */
2911 Select
*pSelect
, /* A SELECT statement to use as the data source */
2912 int onError
, /* How to handle constraint errors */
2913 int iDbDest
/* The database of pDest */
2915 sqlite3
*db
= pParse
->db
;
2916 ExprList
*pEList
; /* The result set of the SELECT */
2917 Table
*pSrc
; /* The table in the FROM clause of SELECT */
2918 Index
*pSrcIdx
, *pDestIdx
; /* Source and destination indices */
2919 SrcItem
*pItem
; /* An element of pSelect->pSrc */
2920 int i
; /* Loop counter */
2921 int iDbSrc
; /* The database of pSrc */
2922 int iSrc
, iDest
; /* Cursors from source and destination */
2923 int addr1
, addr2
; /* Loop addresses */
2924 int emptyDestTest
= 0; /* Address of test for empty pDest */
2925 int emptySrcTest
= 0; /* Address of test for empty pSrc */
2926 Vdbe
*v
; /* The VDBE we are building */
2927 int regAutoinc
; /* Memory register used by AUTOINC */
2928 int destHasUniqueIdx
= 0; /* True if pDest has a UNIQUE index */
2929 int regData
, regRowid
; /* Registers holding data and rowid */
2931 assert( pSelect
!=0 );
2932 if( pParse
->pWith
|| pSelect
->pWith
){
2933 /* Do not attempt to process this query if there are an WITH clauses
2934 ** attached to it. Proceeding may generate a false "no such table: xxx"
2935 ** error if pSelect reads from a CTE named "xxx". */
2938 #ifndef SQLITE_OMIT_VIRTUALTABLE
2939 if( IsVirtual(pDest
) ){
2940 return 0; /* tab1 must not be a virtual table */
2943 if( onError
==OE_Default
){
2944 if( pDest
->iPKey
>=0 ) onError
= pDest
->keyConf
;
2945 if( onError
==OE_Default
) onError
= OE_Abort
;
2947 assert(pSelect
->pSrc
); /* allocated even if there is no FROM clause */
2948 if( pSelect
->pSrc
->nSrc
!=1 ){
2949 return 0; /* FROM clause must have exactly one term */
2951 if( pSelect
->pSrc
->a
[0].pSelect
){
2952 return 0; /* FROM clause cannot contain a subquery */
2954 if( pSelect
->pWhere
){
2955 return 0; /* SELECT may not have a WHERE clause */
2957 if( pSelect
->pOrderBy
){
2958 return 0; /* SELECT may not have an ORDER BY clause */
2960 /* Do not need to test for a HAVING clause. If HAVING is present but
2961 ** there is no ORDER BY, we will get an error. */
2962 if( pSelect
->pGroupBy
){
2963 return 0; /* SELECT may not have a GROUP BY clause */
2965 if( pSelect
->pLimit
){
2966 return 0; /* SELECT may not have a LIMIT clause */
2968 if( pSelect
->pPrior
){
2969 return 0; /* SELECT may not be a compound query */
2971 if( pSelect
->selFlags
& SF_Distinct
){
2972 return 0; /* SELECT may not be DISTINCT */
2974 pEList
= pSelect
->pEList
;
2975 assert( pEList
!=0 );
2976 if( pEList
->nExpr
!=1 ){
2977 return 0; /* The result set must have exactly one column */
2979 assert( pEList
->a
[0].pExpr
);
2980 if( pEList
->a
[0].pExpr
->op
!=TK_ASTERISK
){
2981 return 0; /* The result set must be the special operator "*" */
2984 /* At this point we have established that the statement is of the
2985 ** correct syntactic form to participate in this optimization. Now
2986 ** we have to check the semantics.
2988 pItem
= pSelect
->pSrc
->a
;
2989 pSrc
= sqlite3LocateTableItem(pParse
, 0, pItem
);
2991 return 0; /* FROM clause does not contain a real table */
2993 if( pSrc
->tnum
==pDest
->tnum
&& pSrc
->pSchema
==pDest
->pSchema
){
2994 testcase( pSrc
!=pDest
); /* Possible due to bad sqlite_schema.rootpage */
2995 return 0; /* tab1 and tab2 may not be the same table */
2997 if( HasRowid(pDest
)!=HasRowid(pSrc
) ){
2998 return 0; /* source and destination must both be WITHOUT ROWID or not */
3000 if( !IsOrdinaryTable(pSrc
) ){
3001 return 0; /* tab2 may not be a view or virtual table */
3003 if( pDest
->nCol
!=pSrc
->nCol
){
3004 return 0; /* Number of columns must be the same in tab1 and tab2 */
3006 if( pDest
->iPKey
!=pSrc
->iPKey
){
3007 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
3009 if( (pDest
->tabFlags
& TF_Strict
)!=0 && (pSrc
->tabFlags
& TF_Strict
)==0 ){
3010 return 0; /* Cannot feed from a non-strict into a strict table */
3012 for(i
=0; i
<pDest
->nCol
; i
++){
3013 Column
*pDestCol
= &pDest
->aCol
[i
];
3014 Column
*pSrcCol
= &pSrc
->aCol
[i
];
3015 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
3016 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0
3017 && (pDestCol
->colFlags
| pSrcCol
->colFlags
) & COLFLAG_HIDDEN
3019 return 0; /* Neither table may have __hidden__ columns */
3022 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
3023 /* Even if tables t1 and t2 have identical schemas, if they contain
3024 ** generated columns, then this statement is semantically incorrect:
3026 ** INSERT INTO t2 SELECT * FROM t1;
3028 ** The reason is that generated column values are returned by the
3029 ** the SELECT statement on the right but the INSERT statement on the
3030 ** left wants them to be omitted.
3032 ** Nevertheless, this is a useful notational shorthand to tell SQLite
3033 ** to do a bulk transfer all of the content from t1 over to t2.
3035 ** We could, in theory, disable this (except for internal use by the
3036 ** VACUUM command where it is actually needed). But why do that? It
3037 ** seems harmless enough, and provides a useful service.
3039 if( (pDestCol
->colFlags
& COLFLAG_GENERATED
) !=
3040 (pSrcCol
->colFlags
& COLFLAG_GENERATED
) ){
3041 return 0; /* Both columns have the same generated-column type */
3043 /* But the transfer is only allowed if both the source and destination
3044 ** tables have the exact same expressions for generated columns.
3045 ** This requirement could be relaxed for VIRTUAL columns, I suppose.
3047 if( (pDestCol
->colFlags
& COLFLAG_GENERATED
)!=0 ){
3048 if( sqlite3ExprCompare(0,
3049 sqlite3ColumnExpr(pSrc
, pSrcCol
),
3050 sqlite3ColumnExpr(pDest
, pDestCol
), -1)!=0 ){
3051 testcase( pDestCol
->colFlags
& COLFLAG_VIRTUAL
);
3052 testcase( pDestCol
->colFlags
& COLFLAG_STORED
);
3053 return 0; /* Different generator expressions */
3057 if( pDestCol
->affinity
!=pSrcCol
->affinity
){
3058 return 0; /* Affinity must be the same on all columns */
3060 if( sqlite3_stricmp(sqlite3ColumnColl(pDestCol
),
3061 sqlite3ColumnColl(pSrcCol
))!=0 ){
3062 return 0; /* Collating sequence must be the same on all columns */
3064 if( pDestCol
->notNull
&& !pSrcCol
->notNull
){
3065 return 0; /* tab2 must be NOT NULL if tab1 is */
3067 /* Default values for second and subsequent columns need to match. */
3068 if( (pDestCol
->colFlags
& COLFLAG_GENERATED
)==0 && i
>0 ){
3069 Expr
*pDestExpr
= sqlite3ColumnExpr(pDest
, pDestCol
);
3070 Expr
*pSrcExpr
= sqlite3ColumnExpr(pSrc
, pSrcCol
);
3071 assert( pDestExpr
==0 || pDestExpr
->op
==TK_SPAN
);
3072 assert( pDestExpr
==0 || !ExprHasProperty(pDestExpr
, EP_IntValue
) );
3073 assert( pSrcExpr
==0 || pSrcExpr
->op
==TK_SPAN
);
3074 assert( pSrcExpr
==0 || !ExprHasProperty(pSrcExpr
, EP_IntValue
) );
3075 if( (pDestExpr
==0)!=(pSrcExpr
==0)
3076 || (pDestExpr
!=0 && strcmp(pDestExpr
->u
.zToken
,
3077 pSrcExpr
->u
.zToken
)!=0)
3079 return 0; /* Default values must be the same for all columns */
3083 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
3084 if( IsUniqueIndex(pDestIdx
) ){
3085 destHasUniqueIdx
= 1;
3087 for(pSrcIdx
=pSrc
->pIndex
; pSrcIdx
; pSrcIdx
=pSrcIdx
->pNext
){
3088 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
3091 return 0; /* pDestIdx has no corresponding index in pSrc */
3093 if( pSrcIdx
->tnum
==pDestIdx
->tnum
&& pSrc
->pSchema
==pDest
->pSchema
3094 && sqlite3FaultSim(411)==SQLITE_OK
){
3095 /* The sqlite3FaultSim() call allows this corruption test to be
3096 ** bypassed during testing, in order to exercise other corruption tests
3097 ** further downstream. */
3098 return 0; /* Corrupt schema - two indexes on the same btree */
3101 #ifndef SQLITE_OMIT_CHECK
3102 if( pDest
->pCheck
&& sqlite3ExprListCompare(pSrc
->pCheck
,pDest
->pCheck
,-1) ){
3103 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
3106 #ifndef SQLITE_OMIT_FOREIGN_KEY
3107 /* Disallow the transfer optimization if the destination table contains
3108 ** any foreign key constraints. This is more restrictive than necessary.
3109 ** But the main beneficiary of the transfer optimization is the VACUUM
3110 ** command, and the VACUUM command disables foreign key constraints. So
3111 ** the extra complication to make this rule less restrictive is probably
3112 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
3114 assert( IsOrdinaryTable(pDest
) );
3115 if( (db
->flags
& SQLITE_ForeignKeys
)!=0 && pDest
->u
.tab
.pFKey
!=0 ){
3119 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
3120 return 0; /* xfer opt does not play well with PRAGMA count_changes */
3123 /* If we get this far, it means that the xfer optimization is at
3124 ** least a possibility, though it might only work if the destination
3125 ** table (tab1) is initially empty.
3128 sqlite3_xferopt_count
++;
3130 iDbSrc
= sqlite3SchemaToIndex(db
, pSrc
->pSchema
);
3131 v
= sqlite3GetVdbe(pParse
);
3132 sqlite3CodeVerifySchema(pParse
, iDbSrc
);
3133 iSrc
= pParse
->nTab
++;
3134 iDest
= pParse
->nTab
++;
3135 regAutoinc
= autoIncBegin(pParse
, iDbDest
, pDest
);
3136 regData
= sqlite3GetTempReg(pParse
);
3137 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regData
);
3138 regRowid
= sqlite3GetTempReg(pParse
);
3139 sqlite3OpenTable(pParse
, iDest
, iDbDest
, pDest
, OP_OpenWrite
);
3140 assert( HasRowid(pDest
) || destHasUniqueIdx
);
3141 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 && (
3142 (pDest
->iPKey
<0 && pDest
->pIndex
!=0) /* (1) */
3143 || destHasUniqueIdx
/* (2) */
3144 || (onError
!=OE_Abort
&& onError
!=OE_Rollback
) /* (3) */
3146 /* In some circumstances, we are able to run the xfer optimization
3147 ** only if the destination table is initially empty. Unless the
3148 ** DBFLAG_Vacuum flag is set, this block generates code to make
3149 ** that determination. If DBFLAG_Vacuum is set, then the destination
3150 ** table is always empty.
3152 ** Conditions under which the destination must be empty:
3154 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
3155 ** (If the destination is not initially empty, the rowid fields
3156 ** of index entries might need to change.)
3158 ** (2) The destination has a unique index. (The xfer optimization
3159 ** is unable to test uniqueness.)
3161 ** (3) onError is something other than OE_Abort and OE_Rollback.
3163 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iDest
, 0); VdbeCoverage(v
);
3164 emptyDestTest
= sqlite3VdbeAddOp0(v
, OP_Goto
);
3165 sqlite3VdbeJumpHere(v
, addr1
);
3167 if( HasRowid(pSrc
) ){
3169 sqlite3OpenTable(pParse
, iSrc
, iDbSrc
, pSrc
, OP_OpenRead
);
3170 emptySrcTest
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
3171 if( pDest
->iPKey
>=0 ){
3172 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
3173 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 ){
3174 sqlite3VdbeVerifyAbortable(v
, onError
);
3175 addr2
= sqlite3VdbeAddOp3(v
, OP_NotExists
, iDest
, 0, regRowid
);
3177 sqlite3RowidConstraint(pParse
, onError
, pDest
);
3178 sqlite3VdbeJumpHere(v
, addr2
);
3180 autoIncStep(pParse
, regAutoinc
, regRowid
);
3181 }else if( pDest
->pIndex
==0 && !(db
->mDbFlags
& DBFLAG_VacuumInto
) ){
3182 addr1
= sqlite3VdbeAddOp2(v
, OP_NewRowid
, iDest
, regRowid
);
3184 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
3185 assert( (pDest
->tabFlags
& TF_Autoincrement
)==0 );
3188 if( db
->mDbFlags
& DBFLAG_Vacuum
){
3189 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
3190 insFlags
= OPFLAG_APPEND
|OPFLAG_USESEEKRESULT
|OPFLAG_PREFORMAT
;
3192 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|OPFLAG_APPEND
|OPFLAG_PREFORMAT
;
3194 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
3195 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 ){
3196 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
3197 insFlags
&= ~OPFLAG_PREFORMAT
;
3201 sqlite3VdbeAddOp3(v
, OP_RowCell
, iDest
, iSrc
, regRowid
);
3203 sqlite3VdbeAddOp3(v
, OP_Insert
, iDest
, regData
, regRowid
);
3204 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 ){
3205 sqlite3VdbeChangeP4(v
, -1, (char*)pDest
, P4_TABLE
);
3207 sqlite3VdbeChangeP5(v
, insFlags
);
3209 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
); VdbeCoverage(v
);
3210 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
3211 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
3213 sqlite3TableLock(pParse
, iDbDest
, pDest
->tnum
, 1, pDest
->zName
);
3214 sqlite3TableLock(pParse
, iDbSrc
, pSrc
->tnum
, 0, pSrc
->zName
);
3216 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
3218 for(pSrcIdx
=pSrc
->pIndex
; ALWAYS(pSrcIdx
); pSrcIdx
=pSrcIdx
->pNext
){
3219 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
3222 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iSrc
, pSrcIdx
->tnum
, iDbSrc
);
3223 sqlite3VdbeSetP4KeyInfo(pParse
, pSrcIdx
);
3224 VdbeComment((v
, "%s", pSrcIdx
->zName
));
3225 sqlite3VdbeAddOp3(v
, OP_OpenWrite
, iDest
, pDestIdx
->tnum
, iDbDest
);
3226 sqlite3VdbeSetP4KeyInfo(pParse
, pDestIdx
);
3227 sqlite3VdbeChangeP5(v
, OPFLAG_BULKCSR
);
3228 VdbeComment((v
, "%s", pDestIdx
->zName
));
3229 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
3230 if( db
->mDbFlags
& DBFLAG_Vacuum
){
3231 /* This INSERT command is part of a VACUUM operation, which guarantees
3232 ** that the destination table is empty. If all indexed columns use
3233 ** collation sequence BINARY, then it can also be assumed that the
3234 ** index will be populated by inserting keys in strictly sorted
3235 ** order. In this case, instead of seeking within the b-tree as part
3236 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
3237 ** OP_IdxInsert to seek to the point within the b-tree where each key
3238 ** should be inserted. This is faster.
3240 ** If any of the indexed columns use a collation sequence other than
3241 ** BINARY, this optimization is disabled. This is because the user
3242 ** might change the definition of a collation sequence and then run
3243 ** a VACUUM command. In that case keys may not be written in strictly
3245 for(i
=0; i
<pSrcIdx
->nColumn
; i
++){
3246 const char *zColl
= pSrcIdx
->azColl
[i
];
3247 if( sqlite3_stricmp(sqlite3StrBINARY
, zColl
) ) break;
3249 if( i
==pSrcIdx
->nColumn
){
3250 idxInsFlags
= OPFLAG_USESEEKRESULT
|OPFLAG_PREFORMAT
;
3251 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
3252 sqlite3VdbeAddOp2(v
, OP_RowCell
, iDest
, iSrc
);
3254 }else if( !HasRowid(pSrc
) && pDestIdx
->idxType
==SQLITE_IDXTYPE_PRIMARYKEY
){
3255 idxInsFlags
|= OPFLAG_NCHANGE
;
3257 if( idxInsFlags
!=(OPFLAG_USESEEKRESULT
|OPFLAG_PREFORMAT
) ){
3258 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
3259 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0
3261 && IsPrimaryKeyIndex(pDestIdx
)
3263 codeWithoutRowidPreupdate(pParse
, pDest
, iDest
, regData
);
3266 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iDest
, regData
);
3267 sqlite3VdbeChangeP5(v
, idxInsFlags
|OPFLAG_APPEND
);
3268 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
+1); VdbeCoverage(v
);
3269 sqlite3VdbeJumpHere(v
, addr1
);
3270 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
3271 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
3273 if( emptySrcTest
) sqlite3VdbeJumpHere(v
, emptySrcTest
);
3274 sqlite3ReleaseTempReg(pParse
, regRowid
);
3275 sqlite3ReleaseTempReg(pParse
, regData
);
3276 if( emptyDestTest
){
3277 sqlite3AutoincrementEnd(pParse
);
3278 sqlite3VdbeAddOp2(v
, OP_Halt
, SQLITE_OK
, 0);
3279 sqlite3VdbeJumpHere(v
, emptyDestTest
);
3280 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
3286 #endif /* SQLITE_OMIT_XFER_OPT */