| blob | 789b3b663450711c901470a2b3fb21e09f9ba6d9 |
1 /*
2 ** 2001 September 15
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle INSERT statements in SQLite.
14 */
17 /*
18 ** Generate code that will
19 **
20 ** (1) acquire a lock for table pTab then
21 ** (2) open pTab as cursor iCur.
22 **
23 ** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index
24 ** for that table that is actually opened.
25 */
32 ){
49 }
50 }
52 /*
53 ** Return a pointer to the column affinity string associated with index
54 ** pIdx. A column affinity string has one character for each column in
55 ** the table, according to the affinity of the column:
56 **
57 ** Character Column affinity
58 ** ------------------------------
59 ** 'A' BLOB
60 ** 'B' TEXT
61 ** 'C' NUMERIC
62 ** 'D' INTEGER
63 ** 'F' REAL
64 **
65 ** An extra 'D' is appended to the end of the string to cover the
66 ** rowid that appears as the last column in every index.
67 **
68 ** Memory for the buffer containing the column index affinity string
69 ** is managed along with the rest of the Index structure. It will be
70 ** released when sqlite3DeleteIndex() is called.
71 */
74 /* The first time a column affinity string for a particular index is
75 ** required, it is allocated and populated here. It is then stored as
76 ** a member of the Index structure for subsequent use.
77 **
78 ** The column affinity string will eventually be deleted by
79 ** sqliteDeleteIndex() when the Index structure itself is cleaned
80 ** up.
81 */
88 }
100 }
104 }
106 }
109 }
111 /*
112 ** Compute the affinity string for table pTab, if it has not already been
113 ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
114 **
115 ** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
116 ** if iReg>0 then code an OP_Affinity opcode that will set the affinities
117 ** for register iReg and following. Or if affinities exists and iReg==0,
118 ** then just set the P4 operand of the previous opcode (which should be
119 ** an OP_MakeRecord) to the affinity string.
120 **
121 ** A column affinity string has one character per column:
122 **
123 ** Character Column affinity
124 ** ------------------------------
125 ** 'A' BLOB
126 ** 'B' TEXT
127 ** 'C' NUMERIC
128 ** 'D' INTEGER
129 ** 'E' REAL
130 */
140 }
146 }
147 }
152 }
160 }
161 }
162 }
164 /*
165 ** Return non-zero if the table pTab in database iDb or any of its indices
166 ** have been opened at any point in the VDBE program. This is used to see if
167 ** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
168 ** run without using a temporary table for the results of the SELECT.
169 */
174 #ifndef SQLITE_OMIT_VIRTUALTABLE
176 #endif
186 }
190 }
191 }
192 }
193 #ifndef SQLITE_OMIT_VIRTUALTABLE
198 }
199 #endif
200 }
202 }
204 /* This walker callback will compute the union of colFlags flags for all
205 ** referenced columns in a CHECK constraint or generated column expression.
206 */
211 }
213 }
215 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
216 /*
217 ** All regular columns for table pTab have been puts into registers
218 ** starting with iRegStore. The registers that correspond to STORED
219 ** or VIRTUAL columns have not yet been initialized. This routine goes
220 ** back and computes the values for those columns based on the previously
221 ** computed normal columns.
222 */
227 ){
229 Walker w;
238 /* Before computing generated columns, first go through and make sure
239 ** that appropriate affinity has been applied to the regular columns
240 */
244 ){
245 /* Change the OP_Affinity argument to '@' (NONE) for all stored
246 ** columns. '@' is the no-op affinity and those columns have not
247 ** yet been computed. */
255 }
258 }
259 jj++;
260 }
261 }
263 /* Because there can be multiple generated columns that refer to one another,
264 ** this is a two-pass algorithm. On the first pass, mark all generated
265 ** columns as "not available".
266 */
272 }
273 }
280 /* On the second pass, compute the value of each NOT-AVAILABLE column.
281 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
282 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
283 ** they are needed.
284 */
300 }
306 }
307 }
311 }
313 }
317 #ifndef SQLITE_OMIT_AUTOINCREMENT
318 /*
319 ** Locate or create an AutoincInfo structure associated with table pTab
320 ** which is in database iDb. Return the register number for the register
321 ** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT
322 ** table. (Also return zero when doing a VACUUM since we do not want to
323 ** update the AUTOINCREMENT counters during a VACUUM.)
324 **
325 ** There is at most one AutoincInfo structure per table even if the
326 ** same table is autoincremented multiple times due to inserts within
327 ** triggers. A new AutoincInfo structure is created if this is the
328 ** first use of table pTab. On 2nd and subsequent uses, the original
329 ** AutoincInfo structure is used.
330 **
331 ** Four consecutive registers are allocated:
332 **
333 ** (1) The name of the pTab table.
334 ** (2) The maximum ROWID of pTab.
335 ** (3) The rowid in sqlite_sequence of pTab
336 ** (4) The original value of the max ROWID in pTab, or NULL if none
337 **
338 ** The 2nd register is the one that is returned. That is all the
339 ** insert routine needs to know about.
340 */
345 ){
350 ){
355 /* Verify that the sqlite_sequence table exists and is an ordinary
356 ** rowid table with exactly two columns.
357 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */
362 ){
366 }
380 }
382 }
384 }
386 /*
387 ** This routine generates code that will initialize all of the
388 ** register used by the autoincrement tracker.
389 */
397 /* This routine is never called during trigger-generation. It is
398 ** only called from the top-level */
418 };
440 }
441 }
443 /*
444 ** Update the maximum rowid for an autoincrement calculation.
445 **
446 ** This routine should be called when the regRowid register holds a
447 ** new rowid that is about to be inserted. If that new rowid is
448 ** larger than the maximum rowid in the memId memory cell, then the
449 ** memory cell is updated.
450 */
454 }
455 }
457 /*
458 ** This routine generates the code needed to write autoincrement
459 ** maximum rowid values back into the sqlite_sequence register.
460 ** Every statement that might do an INSERT into an autoincrement
461 ** table (either directly or through triggers) needs to call this
462 ** routine just before the "exit" code.
463 */
478 };
499 }
500 }
503 }
504 #else
505 /*
506 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
507 ** above are all no-ops
508 */
509 # define autoIncBegin(A,B,C) (0)
510 # define autoIncStep(A,B,C)
514 /* Forward declaration */
521 );
523 /*
524 ** This routine is called to handle SQL of the following forms:
525 **
526 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
527 ** insert into TABLE (IDLIST) select
528 ** insert into TABLE (IDLIST) default values
529 **
530 ** The IDLIST following the table name is always optional. If omitted,
531 ** then a list of all (non-hidden) columns for the table is substituted.
532 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
533 ** is omitted.
534 **
535 ** For the pSelect parameter holds the values to be inserted for the
536 ** first two forms shown above. A VALUES clause is really just short-hand
537 ** for a SELECT statement that omits the FROM clause and everything else
538 ** that follows. If the pSelect parameter is NULL, that means that the
539 ** DEFAULT VALUES form of the INSERT statement is intended.
540 **
541 ** The code generated follows one of four templates. For a simple
542 ** insert with data coming from a single-row VALUES clause, the code executes
543 ** once straight down through. Pseudo-code follows (we call this
544 ** the "1st template"):
545 **
546 ** open write cursor to <table> and its indices
547 ** put VALUES clause expressions into registers
548 ** write the resulting record into <table>
549 ** cleanup
550 **
551 ** The three remaining templates assume the statement is of the form
552 **
553 ** INSERT INTO <table> SELECT ...
554 **
555 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
556 ** in other words if the SELECT pulls all columns from a single table
557 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
558 ** if <table2> and <table1> are distinct tables but have identical
559 ** schemas, including all the same indices, then a special optimization
560 ** is invoked that copies raw records from <table2> over to <table1>.
561 ** See the xferOptimization() function for the implementation of this
562 ** template. This is the 2nd template.
563 **
564 ** open a write cursor to <table>
565 ** open read cursor on <table2>
566 ** transfer all records in <table2> over to <table>
567 ** close cursors
568 ** foreach index on <table>
569 ** open a write cursor on the <table> index
570 ** open a read cursor on the corresponding <table2> index
571 ** transfer all records from the read to the write cursors
572 ** close cursors
573 ** end foreach
574 **
575 ** The 3rd template is for when the second template does not apply
576 ** and the SELECT clause does not read from <table> at any time.
577 ** The generated code follows this template:
578 **
579 ** X <- A
580 ** goto B
581 ** A: setup for the SELECT
582 ** loop over the rows in the SELECT
583 ** load values into registers R..R+n
584 ** yield X
585 ** end loop
586 ** cleanup after the SELECT
587 ** end-coroutine X
588 ** B: open write cursor to <table> and its indices
589 ** C: yield X, at EOF goto D
590 ** insert the select result into <table> from R..R+n
591 ** goto C
592 ** D: cleanup
593 **
594 ** The 4th template is used if the insert statement takes its
595 ** values from a SELECT but the data is being inserted into a table
596 ** that is also read as part of the SELECT. In the third form,
597 ** we have to use an intermediate table to store the results of
598 ** the select. The template is like this:
599 **
600 ** X <- A
601 ** goto B
602 ** A: setup for the SELECT
603 ** loop over the tables in the SELECT
604 ** load value into register R..R+n
605 ** yield X
606 ** end loop
607 ** cleanup after the SELECT
608 ** end co-routine R
609 ** B: open temp table
610 ** L: yield X, at EOF goto M
611 ** insert row from R..R+n into temp table
612 ** goto L
613 ** M: open write cursor to <table> and its indices
614 ** rewind temp table
615 ** C: loop over rows of intermediate table
616 ** transfer values form intermediate table into <table>
617 ** end loop
618 ** D: cleanup
619 */
627 ){
651 /* Register allocations */
660 #ifndef SQLITE_OMIT_TRIGGER
664 #endif
669 }
672 /* If the Select object is really just a simple VALUES() list with a
673 ** single row (the common case) then keep that one row of values
674 ** and discard the other (unused) parts of the pSelect object
675 */
681 }
683 /* Locate the table into which we will be inserting new information.
684 */
689 }
695 }
698 /* Figure out if we have any triggers and if the table being
699 ** inserted into is a view
700 */
701 #ifndef SQLITE_OMIT_TRIGGER
704 #else
705 # define pTrigger 0
706 # define tmask 0
707 # define isView 0
708 #endif
709 #ifdef SQLITE_OMIT_VIEW
710 # undef isView
711 # define isView 0
712 #endif
715 /* If pTab is really a view, make sure it has been initialized.
716 ** ViewGetColumnNames() is a no-op if pTab is not a view.
717 */
720 }
722 /* Cannot insert into a read-only table.
723 */
726 }
728 /* Allocate a VDBE
729 */
735 #ifndef SQLITE_OMIT_XFER_OPT
736 /* If the statement is of the form
737 **
738 ** INSERT INTO <table1> SELECT * FROM <table2>;
739 **
740 ** Then special optimizations can be applied that make the transfer
741 ** very fast and which reduce fragmentation of indices.
742 **
743 ** This is the 2nd template.
744 */
749 }
752 /* If this is an AUTOINCREMENT table, look up the sequence number in the
753 ** sqlite_sequence table and store it in memory cell regAutoinc.
754 */
757 /* Allocate a block registers to hold the rowid and the values
758 ** for all columns of the new row.
759 */
763 regRowid++;
765 }
768 /* If the INSERT statement included an IDLIST term, then make sure
769 ** all elements of the IDLIST really are columns of the table and
770 ** remember the column indices.
771 **
772 ** If the table has an INTEGER PRIMARY KEY column and that column
773 ** is named in the IDLIST, then record in the ipkColumn variable
774 ** the index into IDLIST of the primary key column. ipkColumn is
775 ** the index of the primary key as it appears in IDLIST, not as
776 ** is appears in the original table. (The index of the INTEGER
777 ** PRIMARY KEY in the original table is pTab->iPKey.) After this
778 ** loop, if ipkColumn==(-1), that means that integer primary key
779 ** is unspecified, and hence the table is either WITHOUT ROWID or
780 ** it will automatically generated an integer primary key.
781 **
782 ** bIdListInOrder is true if the columns in IDLIST are in storage
783 ** order. This enables an optimization that avoids shuffling the
784 ** columns into storage order. False negatives are harmless,
785 ** but false positives will cause database corruption.
786 */
791 }
799 }
800 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
806 }
807 #endif
809 }
810 }
820 }
821 }
822 }
823 }
825 /* Figure out how many columns of data are supplied. If the data
826 ** is coming from a SELECT statement, then generate a co-routine that
827 ** produces a single row of the SELECT on each invocation. The
828 ** co-routine is the common header to the 3rd and 4th templates.
829 */
831 /* Data is coming from a SELECT or from a multi-row VALUES clause.
832 ** Generate a co-routine to run the SELECT. */
851 /* Set useTempTable to TRUE if the result of the SELECT statement
852 ** should be written into a temporary table (template 4). Set to
853 ** FALSE if each output row of the SELECT can be written directly into
854 ** the destination table (template 3).
855 **
856 ** A temp table must be used if the table being updated is also one
857 ** of the tables being read by the SELECT statement. Also use a
858 ** temp table in the case of row triggers.
859 */
862 }
865 /* Invoke the coroutine to extract information from the SELECT
866 ** and add it to a transient table srcTab. The code generated
867 ** here is from the 4th template:
868 **
869 ** B: open temp table
870 ** L: yield X, goto M at EOF
871 ** insert row from R..R+n into temp table
872 ** goto L
873 ** M: ...
874 */
891 }
893 /* This is the case if the data for the INSERT is coming from a
894 ** single-row VALUES clause
895 */
896 NameContext sNC;
905 }
908 }
909 }
911 /* If there is no IDLIST term but the table has an integer primary
912 ** key, the set the ipkColumn variable to the integer primary key
913 ** column index in the original table definition.
914 */
917 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
925 ipkColumn--;
926 }
927 }
928 }
929 #endif
930 }
932 /* Make sure the number of columns in the source data matches the number
933 ** of columns to be inserted into the table.
934 */
937 }
943 }
947 }
949 /* Initialize the count of rows to be inserted
950 */
954 ){
957 }
959 /* If this is not a view, open the table and and all indices */
967 }
972 }
974 }
975 #ifndef SQLITE_OMIT_UPSERT
981 }
985 }
988 }
996 }
997 }
998 #endif
1001 /* This is the top of the main insertion loop */
1003 /* This block codes the top of loop only. The complete loop is the
1004 ** following pseudocode (template 4):
1005 **
1006 ** rewind temp table, if empty goto D
1007 ** C: loop over rows of intermediate table
1008 ** transfer values form intermediate table into <table>
1009 ** end loop
1010 ** D: ...
1011 */
1015 /* This block codes the top of loop only. The complete loop is the
1016 ** following pseudocode (template 3):
1017 **
1018 ** C: yield X, at EOF goto D
1019 ** insert the select result into <table> from R..R+n
1020 ** goto C
1021 ** D: ...
1022 */
1027 /* tag-20191021-001: If the INTEGER PRIMARY KEY is being generated by the
1028 ** SELECT, go ahead and copy the value into the rowid slot now, so that
1029 ** the value does not get overwritten by a NULL at tag-20191021-002. */
1031 }
1032 }
1034 /* Compute data for ordinary columns of the new entry. Values
1035 ** are written in storage order into registers starting with regData.
1036 ** Only ordinary columns are computed in this loop. The rowid
1037 ** (if there is one) is computed later and generated columns are
1038 ** computed after the rowid since they might depend on the value
1039 ** of the rowid.
1040 */
1045 u32 colFlags;
1048 /* tag-20191021-002: References to the INTEGER PRIMARY KEY are filled
1049 ** using the rowid. So put a NULL in the IPK slot of the record to avoid
1050 ** using excess space. The file format definition requires this extra
1051 ** NULL - we cannot optimize further by skipping the column completely */
1054 }
1056 nHidden++;
1058 /* Virtual columns do not participate in OP_MakeRecord. So back up
1059 ** iRegStore by one slot to compensate for the iRegStore++ in the
1060 ** outer for() loop */
1061 iRegStore--;
1064 /* Stored columns are computed later. But if there are BEFORE
1065 ** triggers, the slots used for stored columns will be OP_Copy-ed
1066 ** to a second block of registers, so the register needs to be
1067 ** initialized to NULL to avoid an uninitialized register read */
1070 }
1073 /* Hidden columns that are not explicitly named in the INSERT
1074 ** get there default value */
1077 }
1078 }
1082 /* A column not named in the insert column list gets its
1083 ** default value */
1086 }
1089 /* This is INSERT INTO ... DEFAULT VALUES. Load the default value. */
1094 }
1101 }
1104 }
1105 }
1108 /* Run the BEFORE and INSTEAD OF triggers, if there are any
1109 */
1114 /* build the NEW.* reference row. Note that if there is an INTEGER
1115 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
1116 ** translated into a unique ID for the row. But on a BEFORE trigger,
1117 ** we do not know what the unique ID will be (because the insert has
1118 ** not happened yet) so we substitute a rowid of -1
1119 */
1130 }
1135 }
1137 /* Cannot have triggers on a virtual table. If it were possible,
1138 ** this block would have to account for hidden column.
1139 */
1142 /* Copy the new data already generated. */
1146 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1147 /* Compute the new value for generated columns after all other
1148 ** columns have already been computed. This must be done after
1149 ** computing the ROWID in case one of the generated columns
1150 ** refers to the ROWID. */
1155 }
1156 #endif
1158 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
1159 ** do not attempt any conversions before assembling the record.
1160 ** If this is a real table, attempt conversions as required by the
1161 ** table column affinities.
1162 */
1165 }
1167 /* Fire BEFORE or INSTEAD OF triggers */
1172 }
1176 /* The row that the VUpdate opcode will delete: none */
1178 }
1180 /* Compute the new rowid */
1184 /* Rowid already initialized at tag-20191021-001 */
1192 }
1193 }
1194 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
1195 ** to generate a unique primary key value.
1196 */
1206 }
1208 }
1214 }
1217 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1218 /* Compute the new value for generated columns after all other
1219 ** columns have already been computed. This must be done after
1220 ** computing the ROWID in case one of the generated columns
1221 ** is derived from the INTEGER PRIMARY KEY. */
1224 }
1225 #endif
1227 /* Generate code to check constraints and generate index keys and
1228 ** do the insertion.
1229 */
1230 #ifndef SQLITE_OMIT_VIRTUALTABLE
1238 #endif
1239 {
1244 );
1247 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1248 ** constraints or (b) there are no triggers and this table is not a
1249 ** parent table in a foreign key constraint. It is safe to set the
1250 ** flag in the second case as if any REPLACE constraint is hit, an
1251 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1252 ** cursor that is disturbed. And these instructions both clear the
1253 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1254 ** functionality. */
1258 );
1259 }
1260 }
1262 /* Update the count of rows that are inserted
1263 */
1266 }
1269 /* Code AFTER triggers */
1272 }
1274 /* The bottom of the main insertion loop, if the data source
1275 ** is a SELECT statement.
1276 */
1284 #ifdef SQLITE_DEBUG
1285 /* If we are jumping back to an OP_Yield that is preceded by an
1286 ** OP_ReleaseReg, set the p5 flag on the OP_Goto so that the
1287 ** OP_ReleaseReg will be included in the loop. */
1291 }
1292 #endif
1294 }
1296 insert_end:
1297 /* Update the sqlite_sequence table by storing the content of the
1298 ** maximum rowid counter values recorded while inserting into
1299 ** autoincrement tables.
1300 */
1303 }
1305 /*
1306 ** Return the number of rows inserted. If this routine is
1307 ** generating code because of a call to sqlite3NestedParse(), do not
1308 ** invoke the callback function.
1309 */
1314 }
1316 insert_cleanup:
1323 }
1325 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1326 ** they may interfere with compilation of other functions in this file
1327 ** (or in another file, if this file becomes part of the amalgamation). */
1328 #ifdef isView
1329 #undef isView
1330 #endif
1331 #ifdef pTrigger
1332 #undef pTrigger
1333 #endif
1334 #ifdef tmask
1335 #undef tmask
1336 #endif
1338 /*
1339 ** Meanings of bits in of pWalker->eCode for
1340 ** sqlite3ExprReferencesUpdatedColumn()
1341 */
1345 /* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
1346 * Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
1347 ** expression node references any of the
1348 ** columns that are being modifed by an UPDATE statement.
1349 */
1356 }
1359 }
1360 }
1362 }
1364 /*
1365 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1366 ** only columns that are modified by the UPDATE are those for which
1367 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1368 **
1369 ** Return true if CHECK constraint pExpr uses any of the
1370 ** changing columns (or the rowid if it is changing). In other words,
1371 ** return true if this CHECK constraint must be validated for
1372 ** the new row in the UPDATE statement.
1373 **
1374 ** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
1375 ** The operation of this routine is the same - return true if an only if
1376 ** the expression uses one or more of columns identified by the second and
1377 ** third arguments.
1378 */
1383 ){
1384 Walker w;
1393 }
1399 }
1401 /*
1402 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1403 ** on table pTab.
1404 **
1405 ** The regNewData parameter is the first register in a range that contains
1406 ** the data to be inserted or the data after the update. There will be
1407 ** pTab->nCol+1 registers in this range. The first register (the one
1408 ** that regNewData points to) will contain the new rowid, or NULL in the
1409 ** case of a WITHOUT ROWID table. The second register in the range will
1410 ** contain the content of the first table column. The third register will
1411 ** contain the content of the second table column. And so forth.
1412 **
1413 ** The regOldData parameter is similar to regNewData except that it contains
1414 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1415 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1416 ** checking regOldData for zero.
1417 **
1418 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1419 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1420 ** might be modified by the UPDATE. If pkChng is false, then the key of
1421 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1422 **
1423 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1424 ** was explicitly specified as part of the INSERT statement. If pkChng
1425 ** is zero, it means that the either rowid is computed automatically or
1426 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1427 ** pkChng will only be true if the INSERT statement provides an integer
1428 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1429 **
1430 ** The code generated by this routine will store new index entries into
1431 ** registers identified by aRegIdx[]. No index entry is created for
1432 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1433 ** the same as the order of indices on the linked list of indices
1434 ** at pTab->pIndex.
1435 **
1436 ** (2019-05-07) The generated code also creates a new record for the
1437 ** main table, if pTab is a rowid table, and stores that record in the
1438 ** register identified by aRegIdx[nIdx] - in other words in the first
1439 ** entry of aRegIdx[] past the last index. It is important that the
1440 ** record be generated during constraint checks to avoid affinity changes
1441 ** to the register content that occur after constraint checks but before
1442 ** the new record is inserted.
1443 **
1444 ** The caller must have already opened writeable cursors on the main
1445 ** table and all applicable indices (that is to say, all indices for which
1446 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1447 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1448 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1449 ** for the first index in the pTab->pIndex list. Cursors for other indices
1450 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1451 **
1452 ** This routine also generates code to check constraints. NOT NULL,
1453 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1454 ** then the appropriate action is performed. There are five possible
1455 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1456 **
1457 ** Constraint type Action What Happens
1458 ** --------------- ---------- ----------------------------------------
1459 ** any ROLLBACK The current transaction is rolled back and
1460 ** sqlite3_step() returns immediately with a
1461 ** return code of SQLITE_CONSTRAINT.
1462 **
1463 ** any ABORT Back out changes from the current command
1464 ** only (do not do a complete rollback) then
1465 ** cause sqlite3_step() to return immediately
1466 ** with SQLITE_CONSTRAINT.
1467 **
1468 ** any FAIL Sqlite3_step() returns immediately with a
1469 ** return code of SQLITE_CONSTRAINT. The
1470 ** transaction is not rolled back and any
1471 ** changes to prior rows are retained.
1472 **
1473 ** any IGNORE The attempt in insert or update the current
1474 ** row is skipped, without throwing an error.
1475 ** Processing continues with the next row.
1476 ** (There is an immediate jump to ignoreDest.)
1477 **
1478 ** NOT NULL REPLACE The NULL value is replace by the default
1479 ** value for that column. If the default value
1480 ** is NULL, the action is the same as ABORT.
1481 **
1482 ** UNIQUE REPLACE The other row that conflicts with the row
1483 ** being inserted is removed.
1484 **
1485 ** CHECK REPLACE Illegal. The results in an exception.
1486 **
1487 ** Which action to take is determined by the overrideError parameter.
1488 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1489 ** is used. Or if pParse->onError==OE_Default then the onError value
1490 ** for the constraint is used.
1491 */
1506 ){
1524 /* Variables associated with retesting uniqueness constraints after
1525 ** replace triggers fire have run */
1539 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1540 ** normal rowid tables. nPkField is the number of key fields in the
1541 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1542 ** number of fields in the true primary key of the table. */
1549 }
1551 /* Record that this module has started */
1555 /* Test all NOT NULL constraints.
1556 */
1570 }
1573 nGenerated++;
1575 }
1577 /* Do not check NOT NULL on columns that do not change */
1579 }
1584 }
1588 ){
1595 }
1598 }
1608 nSeenReplace++;
1612 }
1615 /* no break */ deliberate_fall_through
1626 }
1632 }
1636 /* If there are no generated columns with NOT NULL constraints
1637 ** and no NOT NULL ON CONFLICT REPLACE constraints, then a single
1638 ** pass is sufficient */
1640 }
1643 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1645 /* If any NOT NULL ON CONFLICT REPLACE constraints fired on the
1646 ** first pass, recomputed values for all generated columns, as
1647 ** those values might depend on columns affected by the REPLACE.
1648 */
1650 }
1651 #endif
1655 /* Test all CHECK constraints
1656 */
1657 #ifndef SQLITE_OMIT_CHECK
1668 ){
1669 /* The check constraints do not reference any of the columns being
1670 ** updated so there is no point it verifying the check constraint */
1672 }
1676 }
1682 }
1692 P5_ConstraintCheck);
1693 }
1695 }
1697 }
1700 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
1701 ** order:
1702 **
1703 ** (1) OE_Update
1704 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
1705 ** (3) OE_Replace
1706 **
1707 ** OE_Fail and OE_Ignore must happen before any changes are made.
1708 ** OE_Update guarantees that only a single row will change, so it
1709 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
1710 ** could happen in any order, but they are grouped up front for
1711 ** convenience.
1712 **
1713 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
1714 ** The order of constraints used to have OE_Update as (2) and OE_Abort
1715 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
1716 ** constraint before any others, so it had to be moved.
1717 **
1718 ** Constraint checking code is generated in this order:
1719 ** (A) The rowid constraint
1720 ** (B) Unique index constraints that do not have OE_Replace as their
1721 ** default conflict resolution strategy
1722 ** (C) Unique index that do use OE_Replace by default.
1723 **
1724 ** The ordering of (2) and (3) is accomplished by making sure the linked
1725 ** list of indexes attached to a table puts all OE_Replace indexes last
1726 ** in the list. See sqlite3CreateIndex() for where that happens.
1727 */
1731 /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
1732 ** Make all unique constraint resolution be OE_Ignore */
1737 /* If the constraint-target uniqueness check must be run first.
1738 ** Jump to that uniqueness check now */
1741 }
1742 }
1744 /* Determine if it is possible that triggers (either explicitly coded
1745 ** triggers or FK resolution actions) might run as a result of deletes
1746 ** that happen when OE_Replace conflict resolution occurs. (Call these
1747 ** "replace triggers".) If any replace triggers run, we will need to
1748 ** recheck all of the uniqueness constraints after they have all run.
1749 ** But on the recheck, the resolution is OE_Abort instead of OE_Replace.
1750 **
1751 ** If replace triggers are a possibility, then
1752 **
1753 ** (1) Allocate register regTrigCnt and initialize it to zero.
1754 ** That register will count the number of replace triggers that
1755 ** fire. Constraint recheck only occurs if the number is positive.
1756 ** (2) Initialize pTrigger to the list of all DELETE triggers on pTab.
1757 ** (3) Initialize addrRecheck and lblRecheckOk
1758 **
1759 ** The uniqueness rechecking code will create a series of tests to run
1760 ** in a second pass. The addrRecheck and lblRecheckOk variables are
1761 ** used to link together these tests which are separated from each other
1762 ** in the generate bytecode.
1763 */
1765 /* There are not DELETE triggers nor FK constraints. No constraint
1766 ** rechecks are needed. */
1776 }
1778 /* Replace triggers might exist. Allocate the counter and
1779 ** initialize it to zero. */
1785 }
1786 }
1788 /* If rowid is changing, make sure the new rowid does not previously
1789 ** exist in the table.
1790 */
1794 /* Figure out what action to take in case of a rowid collision */
1800 }
1802 /* figure out whether or not upsert applies in this case */
1808 }
1809 }
1811 /* If the response to a rowid conflict is REPLACE but the response
1812 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1813 ** to defer the running of the rowid conflict checking until after
1814 ** the UNIQUE constraints have run.
1815 */
1819 ){
1822 }
1825 /* pkChng!=0 does not mean that the rowid has changed, only that
1826 ** it might have changed. Skip the conflict logic below if the rowid
1827 ** is unchanged. */
1831 }
1833 /* Check to see if the new rowid already exists in the table. Skip
1834 ** the following conflict logic if it does not. */
1843 /* no break */ deliberate_fall_through
1844 }
1853 }
1855 /* If there are DELETE triggers on this table and the
1856 ** recursive-triggers flag is set, call GenerateRowDelete() to
1857 ** remove the conflicting row from the table. This will fire
1858 ** the triggers and remove both the table and index b-tree entries.
1859 **
1860 ** Otherwise, if there are no triggers or the recursive-triggers
1861 ** flag is not set, but the table has one or more indexes, call
1862 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1863 ** only. The table b-tree entry will be replaced by the new entry
1864 ** when it is inserted.
1865 **
1866 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1867 ** also invoke MultiWrite() to indicate that this VDBE may require
1868 ** statement rollback (if the statement is aborted after the delete
1869 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1870 ** but being more selective here allows statements like:
1871 **
1872 ** REPLACE INTO t(rowid) VALUES($newrowid)
1873 **
1874 ** to run without a statement journal if there are no indexes on the
1875 ** table.
1876 */
1882 nReplaceTrig++;
1884 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1886 /* This OP_Delete opcode fires the pre-update-hook only. It does
1887 ** not modify the b-tree. It is more efficient to let the coming
1888 ** OP_Insert replace the existing entry than it is to delete the
1889 ** existing entry and then insert a new one. */
1896 }
1897 }
1900 }
1901 #ifndef SQLITE_OMIT_UPSERT
1904 /* no break */ deliberate_fall_through
1905 }
1906 #endif
1911 }
1912 }
1917 }
1918 }
1920 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1921 ** index and making sure that duplicate entries do not already exist.
1922 ** Compute the revised record entries for indices as we go.
1923 **
1924 ** This loop also handles the case of the PRIMARY KEY index for a
1925 ** WITHOUT ROWID table.
1926 */
1942 }
1946 }
1951 /* Skip partial indices for which the WHERE clause is not true */
1956 SQLITE_JUMPIFNULL);
1958 }
1960 /* Create a record for this index entry as it should appear after
1961 ** the insert or update. Store that record in the aRegIdx[ix] register
1962 */
1981 }
1982 }
1985 #ifdef SQLITE_ENABLE_NULL_TRIM
1988 }
1989 #endif
1992 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1993 ** of a WITHOUT ROWID table and there has been no change the
1994 ** primary key, then no collision is possible. The collision detection
1995 ** logic below can all be skipped. */
1999 }
2001 /* Find out what action to take in case there is a uniqueness conflict */
2006 }
2011 }
2013 /* Figure out if the upsert clause applies to this index */
2019 }
2020 }
2022 /* Collision detection may be omitted if all of the following are true:
2023 ** (1) The conflict resolution algorithm is REPLACE
2024 ** (2) The table is a WITHOUT ROWID table
2025 ** (3) There are no secondary indexes on the table
2026 ** (4) No delete triggers need to be fired if there is a conflict
2027 ** (5) No FK constraint counters need to be updated if a conflict occurs.
2028 **
2029 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row
2030 ** must be explicitly deleted in order to ensure any pre-update hook
2031 ** is invoked. */
2032 #ifndef SQLITE_ENABLE_PREUPDATE_HOOK
2040 ){
2043 }
2046 /* Check to see if the new index entry will be unique */
2048 addrConflictCk =
2052 /* Generate code to handle collisions */
2057 /* Conflict only if the rowid of the existing index entry
2058 ** is different from old-rowid */
2063 }
2066 /* Extract the PRIMARY KEY from the end of the index entry and
2067 ** store it in registers regR..regR+nPk-1 */
2075 }
2076 }
2078 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
2079 ** table, only conflict if the new PRIMARY KEY values are actually
2080 ** different from the old.
2081 **
2082 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
2083 ** of the matched index row are different from the original PRIMARY
2084 ** KEY values of this row before the update. */
2096 }
2100 );
2104 }
2105 }
2106 }
2107 }
2109 /* Generate code that executes if the new index entry is not unique */
2121 }
2122 #ifndef SQLITE_OMIT_UPSERT
2125 /* no break */ deliberate_fall_through
2126 }
2127 #endif
2132 }
2142 nReplaceTrig++;
2143 }
2146 }
2152 }
2160 /* Here we insert code that will be invoked after all constraint
2161 ** checks have run, if and only if one or more replace triggers
2162 ** fired. */
2166 /* Bypass the recheck if this partial index is not defined
2167 ** for the current row */
2170 }
2171 /* Copy the constraint check code from above, except change
2172 ** the constraint-ok jump destination to be the address of
2173 ** the next retest block */
2176 /* The sqlite3VdbeAddOp4() call might reallocate the opcode array.
2177 ** Hence, make a complete copy of the opcode, rather than using
2178 ** a pointer to the opcode. */
2187 }
2192 }
2193 nConflictCk--;
2194 addrConflictCk++;
2195 }
2196 /* If the retest fails, issue an abort */
2200 }
2203 }
2204 }
2210 }
2212 }
2214 /* If the IPK constraint is a REPLACE, run it last */
2219 }
2221 /* Recheck all uniqueness constraints after replace triggers have run */
2231 }
2237 }
2239 }
2241 /* Generate the table record */
2248 }
2249 }
2253 }
2255 #ifdef SQLITE_ENABLE_NULL_TRIM
2256 /*
2257 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
2258 ** to be the number of columns in table pTab that must not be NULL-trimmed.
2259 **
2260 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
2261 */
2263 u16 i;
2265 /* Records with omitted columns are only allowed for schema format
2266 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
2272 }
2274 }
2275 #endif
2277 /*
2278 ** This routine generates code to finish the INSERT or UPDATE operation
2279 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
2280 ** A consecutive range of registers starting at regNewData contains the
2281 ** rowid and the content to be inserted.
2282 **
2283 ** The arguments to this routine should be the same as the first six
2284 ** arguments to sqlite3GenerateConstraintChecks.
2285 */
2296 ){
2305 );
2311 /* All REPLACE indexes are at the end of the list */
2319 }
2325 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2331 );
2334 }
2335 #endif
2336 }
2341 }
2348 }
2351 }
2354 }
2358 }
2360 }
2362 /*
2363 ** Allocate cursors for the pTab table and all its indices and generate
2364 ** code to open and initialized those cursors.
2365 **
2366 ** The cursor for the object that contains the complete data (normally
2367 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
2368 ** ROWID table) is returned in *piDataCur. The first index cursor is
2369 ** returned in *piIdxCur. The number of indices is returned.
2370 **
2371 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
2372 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
2373 ** If iBase is negative, then allocate the next available cursor.
2374 **
2375 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
2376 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
2377 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
2378 ** pTab->pIndex list.
2379 **
2380 ** If pTab is a virtual table, then this routine is a no-op and the
2381 ** *piDataCur and *piIdxCur values are left uninitialized.
2382 */
2392 ){
2402 /* This routine is a no-op for virtual tables. Leave the output
2403 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
2404 ** can detect if they are used by mistake in the caller. */
2406 }
2417 }
2425 }
2431 }
2432 }
2435 }
2438 #ifdef SQLITE_TEST
2439 /*
2440 ** The following global variable is incremented whenever the
2441 ** transfer optimization is used. This is used for testing
2442 ** purposes only - to make sure the transfer optimization really
2443 ** is happening when it is supposed to.
2444 */
2449 #ifndef SQLITE_OMIT_XFER_OPT
2450 /*
2451 ** Check to see if index pSrc is compatible as a source of data
2452 ** for index pDest in an insert transfer optimization. The rules
2453 ** for a compatible index:
2454 **
2455 ** * The index is over the same set of columns
2456 ** * The same DESC and ASC markings occurs on all columns
2457 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
2458 ** * The same collating sequence on each column
2459 ** * The index has the exact same WHERE clause
2460 */
2467 }
2470 }
2474 }
2480 }
2481 }
2484 }
2487 }
2488 }
2491 }
2493 /* If no test above fails then the indices must be compatible */
2495 }
2497 /*
2498 ** Attempt the transfer optimization on INSERTs of the form
2499 **
2500 ** INSERT INTO tab1 SELECT * FROM tab2;
2501 **
2502 ** The xfer optimization transfers raw records from tab2 over to tab1.
2503 ** Columns are not decoded and reassembled, which greatly improves
2504 ** performance. Raw index records are transferred in the same way.
2505 **
2506 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2507 ** There are lots of rules for determining compatibility - see comments
2508 ** embedded in the code for details.
2509 **
2510 ** This routine returns TRUE if the optimization is guaranteed to be used.
2511 ** Sometimes the xfer optimization will only work if the destination table
2512 ** is empty - a factor that can only be determined at run-time. In that
2513 ** case, this routine generates code for the xfer optimization but also
2514 ** does a test to see if the destination table is empty and jumps over the
2515 ** xfer optimization code if the test fails. In that case, this routine
2516 ** returns FALSE so that the caller will know to go ahead and generate
2517 ** an unoptimized transfer. This routine also returns FALSE if there
2518 ** is no chance that the xfer optimization can be applied.
2519 **
2520 ** This optimization is particularly useful at making VACUUM run faster.
2521 */
2528 ){
2547 }
2549 /* Do not attempt to process this query if there are an WITH clauses
2550 ** attached to it. Proceeding may generate a false "no such table: xxx"
2551 ** error if pSelect reads from a CTE named "xxx". */
2553 }
2556 }
2557 #ifndef SQLITE_OMIT_VIRTUALTABLE
2560 }
2561 #endif
2565 }
2569 }
2572 }
2575 }
2578 }
2579 /* Do not need to test for a HAVING clause. If HAVING is present but
2580 ** there is no ORDER BY, we will get an error. */
2583 }
2586 }
2589 }
2592 }
2597 }
2601 }
2603 /* At this point we have established that the statement is of the
2604 ** correct syntactic form to participate in this optimization. Now
2605 ** we have to check the semantics.
2606 */
2611 }
2615 }
2618 }
2619 #ifndef SQLITE_OMIT_VIRTUALTABLE
2622 }
2623 #endif
2626 }
2629 }
2632 }
2636 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
2639 ){
2641 }
2642 #endif
2643 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
2644 /* Even if tables t1 and t2 have identical schemas, if they contain
2645 ** generated columns, then this statement is semantically incorrect:
2646 **
2647 ** INSERT INTO t2 SELECT * FROM t1;
2648 **
2649 ** The reason is that generated column values are returned by the
2650 ** the SELECT statement on the right but the INSERT statement on the
2651 ** left wants them to be omitted.
2652 **
2653 ** Nevertheless, this is a useful notational shorthand to tell SQLite
2654 ** to do a bulk transfer all of the content from t1 over to t2.
2655 **
2656 ** We could, in theory, disable this (except for internal use by the
2657 ** VACUUM command where it is actually needed). But why do that? It
2658 ** seems harmless enough, and provides a useful service.
2659 */
2663 }
2664 /* But the transfer is only allowed if both the source and destination
2665 ** tables have the exact same expressions for generated columns.
2666 ** This requirement could be relaxed for VIRTUAL columns, I suppose.
2667 */
2673 }
2674 }
2675 #endif
2678 }
2681 }
2684 }
2685 /* Default values for second and subsequent columns need to match. */
2692 ){
2694 }
2695 }
2696 }
2700 }
2703 }
2706 }
2709 /* The sqlite3FaultSim() call allows this corruption test to be
2710 ** bypassed during testing, in order to exercise other corruption tests
2711 ** further downstream. */
2713 }
2714 }
2715 #ifndef SQLITE_OMIT_CHECK
2718 }
2719 #endif
2720 #ifndef SQLITE_OMIT_FOREIGN_KEY
2721 /* Disallow the transfer optimization if the destination table constains
2722 ** any foreign key constraints. This is more restrictive than necessary.
2723 ** But the main beneficiary of the transfer optimization is the VACUUM
2724 ** command, and the VACUUM command disables foreign key constraints. So
2725 ** the extra complication to make this rule less restrictive is probably
2726 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2727 */
2730 }
2731 #endif
2734 }
2736 /* If we get this far, it means that the xfer optimization is at
2737 ** least a possibility, though it might only work if the destination
2738 ** table (tab1) is initially empty.
2739 */
2740 #ifdef SQLITE_TEST
2741 sqlite3_xferopt_count++;
2742 #endif
2757 )){
2758 /* In some circumstances, we are able to run the xfer optimization
2759 ** only if the destination table is initially empty. Unless the
2760 ** DBFLAG_Vacuum flag is set, this block generates code to make
2761 ** that determination. If DBFLAG_Vacuum is set, then the destination
2762 ** table is always empty.
2763 **
2764 ** Conditions under which the destination must be empty:
2765 **
2766 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2767 ** (If the destination is not initially empty, the rowid fields
2768 ** of index entries might need to change.)
2769 **
2770 ** (2) The destination has a unique index. (The xfer optimization
2771 ** is unable to test uniqueness.)
2772 **
2773 ** (3) onError is something other than OE_Abort and OE_Rollback.
2774 */
2778 }
2780 u8 insFlags;
2796 }
2802 }
2813 }
2818 }
2829 /* This INSERT command is part of a VACUUM operation, which guarantees
2830 ** that the destination table is empty. If all indexed columns use
2831 ** collation sequence BINARY, then it can also be assumed that the
2832 ** index will be populated by inserting keys in strictly sorted
2833 ** order. In this case, instead of seeking within the b-tree as part
2834 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
2835 ** OP_IdxInsert to seek to the point within the b-tree where each key
2836 ** should be inserted. This is faster.
2837 **
2838 ** If any of the indexed columns use a collation sequence other than
2839 ** BINARY, this optimization is disabled. This is because the user
2840 ** might change the definition of a collation sequence and then run
2841 ** a VACUUM command. In that case keys may not be written in strictly
2842 ** sorted order. */
2846 }
2850 }
2853 }
2861 }
2873 }
2874 }