| blob | a7e94420bfd6220b405be0935e5ba0cdc3f754f8 |
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 ){
41 }
52 }
53 }
55 /*
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:
59 **
60 ** Character Column affinity
61 ** ------------------------------
62 ** 'A' BLOB
63 ** 'B' TEXT
64 ** 'C' NUMERIC
65 ** 'D' INTEGER
66 ** 'F' REAL
67 **
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.
70 **
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.
74 */
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.
79 **
80 ** The column affinity string will eventually be deleted by
81 ** sqliteDeleteIndex() when the Index structure itself is cleaned
82 ** up.
83 */
90 }
103 }
107 }
110 }
114 }
117 /*
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.
121 */
130 }
131 }
135 }
137 }
139 /*
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.
142 **
143 ** For ordinary (legacy, non-strict) tables:
144 ** -----------------------------------------
145 **
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.
148 **
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.
151 **
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.
156 **
157 ** A column affinity string has one character per column:
158 **
159 ** Character Column affinity
160 ** --------- ---------------
161 ** 'A' BLOB
162 ** 'B' TEXT
163 ** 'C' NUMERIC
164 ** 'D' INTEGER
165 ** 'E' REAL
166 **
167 ** For STRICT tables:
168 ** ------------------
169 **
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.
178 */
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 */
195 /* Insert an isolated OP_Typecheck */
198 }
200 }
207 }
209 }
219 }
220 }
221 }
223 /*
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.
228 */
233 #ifndef SQLITE_OMIT_VIRTUALTABLE
235 #endif
245 }
249 }
250 }
251 }
252 #ifndef SQLITE_OMIT_VIRTUALTABLE
257 }
258 #endif
259 }
261 }
263 /* This walker callback will compute the union of colFlags flags for all
264 ** referenced columns in a CHECK constraint or generated column expression.
265 */
270 }
272 }
274 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
275 /*
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.
281 */
286 ){
288 Walker w;
297 /* Before computing generated columns, first go through and make sure
298 ** that appropriate affinity has been applied to the regular columns
299 */
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. */
314 }
317 }
318 jj++;
319 }
321 /* If an OP_TypeCheck was generated because the table is STRICT,
322 ** then set the P3 operand to indicate that generated columns should
323 ** not be checked */
325 }
326 }
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".
331 */
337 }
338 }
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
348 ** they are needed.
349 */
365 }
371 }
372 }
376 }
378 }
382 #ifndef SQLITE_OMIT_AUTOINCREMENT
383 /*
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.)
389 **
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.
395 **
396 ** Four consecutive registers are allocated:
397 **
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
402 **
403 ** The 2nd register is the one that is returned. That is all the
404 ** insert routine needs to know about.
405 */
410 ){
415 ){
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 */
427 ){
431 }
447 }
449 }
451 }
453 /*
454 ** This routine generates code that will initialize all of the
455 ** register used by the autoincrement tracker.
456 */
464 /* This routine is never called during trigger-generation. It is
465 ** only called from the top-level */
485 };
507 }
508 }
510 /*
511 ** Update the maximum rowid for an autoincrement calculation.
512 **
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.
517 */
521 }
522 }
524 /*
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.
530 */
545 };
566 }
567 }
570 }
571 #else
572 /*
573 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
574 ** above are all no-ops
575 */
576 # define autoIncBegin(A,B,C) (0)
577 # define autoIncStep(A,B,C)
580 /*
581 ** If argument pVal is a Select object returned by an sqlite3MultiValues()
582 ** that was able to use the co-routine optimization, finish coding the
583 ** co-routine.
584 */
590 }
591 }
593 /*
594 ** Return true if all expressions in the expression-list passed as the
595 ** only argument are constant.
596 */
601 }
603 }
605 /*
606 ** Return true if all expressions in the expression-list passed as the
607 ** only argument are both constant and have no affinity.
608 */
617 }
620 }
622 /*
623 ** This function is called by the parser for the second and subsequent
624 ** rows of a multi-row VALUES clause. Argument pLeft is the part of
625 ** the VALUES clause already parsed, argument pRow is the vector of values
626 ** for the new row. The Select object returned represents the complete
627 ** VALUES clause, including the new row.
628 **
629 ** There are two ways in which this may be achieved - by incremental
630 ** coding of a co-routine (the "co-routine" method) or by returning a
631 ** Select object equivalent to the following (the "UNION ALL" method):
632 **
633 ** "pLeft UNION ALL SELECT pRow"
634 **
635 ** If the VALUES clause contains a lot of rows, this compound Select
636 ** object may consume a lot of memory.
637 **
638 ** When the co-routine method is used, each row that will be returned
639 ** by the VALUES clause is coded into part of a co-routine as it is
640 ** passed to this function. The returned Select object is equivalent to:
641 **
642 ** SELECT * FROM (
643 ** Select object to read co-routine
644 ** )
645 **
646 ** The co-routine method is used in most cases. Exceptions are:
647 **
648 ** a) If the current statement has a WITH clause. This is to avoid
649 ** statements like:
650 **
651 ** WITH cte AS ( VALUES('x'), ('y') ... )
652 ** SELECT * FROM cte AS a, cte AS b;
653 **
654 ** This will not work, as the co-routine uses a hard-coded register
655 ** for its OP_Yield instructions, and so it is not possible for two
656 ** cursors to iterate through it concurrently.
657 **
658 ** b) The schema is currently being parsed (i.e. the VALUES clause is part
659 ** of a schema item like a VIEW or TRIGGER). In this case there is no VM
660 ** being generated when parsing is taking place, and so generating
661 ** a co-routine is not possible.
662 **
663 ** c) There are non-constant expressions in the VALUES clause (e.g.
664 ** the VALUES clause is part of a correlated sub-query).
665 **
666 ** d) One or more of the values in the first row of the VALUES clause
667 ** has an affinity (i.e. is a CAST expression). This causes problems
668 ** because the complex rules SQLite uses (see function
669 ** sqlite3SubqueryColumnTypes() in select.c) to determine the effective
670 ** affinity of such a column for all rows require access to all values in
671 ** the column simultaneously.
672 */
680 || IN_SPECIAL_PARSE
681 ){
682 /* The co-routine method cannot be used. Fall back to UNION ALL. */
689 /* In this case set the SF_MultiValue flag only if it was set on pLeft */
691 }
698 }
703 /* Co-routine has not yet been started and the special Select object
704 ** that accesses the co-routine has not yet been created. This block
705 ** does both those things. */
709 /* Ensure the database schema has been read. This is to ensure we have
710 ** the correct text encoding. */
713 }
716 SelectDest dest;
735 /* Allocate registers for the output of the co-routine. Do so so
736 ** that there are two unused registers immediately before those
737 ** used by the co-routine. This allows the code in sqlite3Insert()
738 ** to use these registers directly, instead of copying the output
739 ** of the co-routine to a separate array for processing. */
749 }
754 }
763 }
764 }
766 }
769 }
771 /* Forward declaration */
778 );
780 /*
781 ** This routine is called to handle SQL of the following forms:
782 **
783 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
784 ** insert into TABLE (IDLIST) select
785 ** insert into TABLE (IDLIST) default values
786 **
787 ** The IDLIST following the table name is always optional. If omitted,
788 ** then a list of all (non-hidden) columns for the table is substituted.
789 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
790 ** is omitted.
791 **
792 ** For the pSelect parameter holds the values to be inserted for the
793 ** first two forms shown above. A VALUES clause is really just short-hand
794 ** for a SELECT statement that omits the FROM clause and everything else
795 ** that follows. If the pSelect parameter is NULL, that means that the
796 ** DEFAULT VALUES form of the INSERT statement is intended.
797 **
798 ** The code generated follows one of four templates. For a simple
799 ** insert with data coming from a single-row VALUES clause, the code executes
800 ** once straight down through. Pseudo-code follows (we call this
801 ** the "1st template"):
802 **
803 ** open write cursor to <table> and its indices
804 ** put VALUES clause expressions into registers
805 ** write the resulting record into <table>
806 ** cleanup
807 **
808 ** The three remaining templates assume the statement is of the form
809 **
810 ** INSERT INTO <table> SELECT ...
811 **
812 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
813 ** in other words if the SELECT pulls all columns from a single table
814 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
815 ** if <table2> and <table1> are distinct tables but have identical
816 ** schemas, including all the same indices, then a special optimization
817 ** is invoked that copies raw records from <table2> over to <table1>.
818 ** See the xferOptimization() function for the implementation of this
819 ** template. This is the 2nd template.
820 **
821 ** open a write cursor to <table>
822 ** open read cursor on <table2>
823 ** transfer all records in <table2> over to <table>
824 ** close cursors
825 ** foreach index on <table>
826 ** open a write cursor on the <table> index
827 ** open a read cursor on the corresponding <table2> index
828 ** transfer all records from the read to the write cursors
829 ** close cursors
830 ** end foreach
831 **
832 ** The 3rd template is for when the second template does not apply
833 ** and the SELECT clause does not read from <table> at any time.
834 ** The generated code follows this template:
835 **
836 ** X <- A
837 ** goto B
838 ** A: setup for the SELECT
839 ** loop over the rows in the SELECT
840 ** load values into registers R..R+n
841 ** yield X
842 ** end loop
843 ** cleanup after the SELECT
844 ** end-coroutine X
845 ** B: open write cursor to <table> and its indices
846 ** C: yield X, at EOF goto D
847 ** insert the select result into <table> from R..R+n
848 ** goto C
849 ** D: cleanup
850 **
851 ** The 4th template is used if the insert statement takes its
852 ** values from a SELECT but the data is being inserted into a table
853 ** that is also read as part of the SELECT. In the third form,
854 ** we have to use an intermediate table to store the results of
855 ** the select. The template is like this:
856 **
857 ** X <- A
858 ** goto B
859 ** A: setup for the SELECT
860 ** loop over the tables in the SELECT
861 ** load value into register R..R+n
862 ** yield X
863 ** end loop
864 ** cleanup after the SELECT
865 ** end co-routine R
866 ** B: open temp table
867 ** L: yield X, at EOF goto M
868 ** insert row from R..R+n into temp table
869 ** goto L
870 ** M: open write cursor to <table> and its indices
871 ** rewind temp table
872 ** C: loop over rows of intermediate table
873 ** transfer values form intermediate table into <table>
874 ** end loop
875 ** D: cleanup
876 */
884 ){
908 /* Register allocations */
917 #ifndef SQLITE_OMIT_TRIGGER
921 #endif
927 }
931 /* If the Select object is really just a simple VALUES() list with a
932 ** single row (the common case) then keep that one row of values
933 ** and discard the other (unused) parts of the pSelect object
934 */
940 }
942 /* Locate the table into which we will be inserting new information.
943 */
948 }
954 }
957 /* Figure out if we have any triggers and if the table being
958 ** inserted into is a view
959 */
960 #ifndef SQLITE_OMIT_TRIGGER
963 #else
964 # define pTrigger 0
965 # define tmask 0
966 # define isView 0
967 #endif
968 #ifdef SQLITE_OMIT_VIEW
969 # undef isView
970 # define isView 0
971 #endif
974 #if TREETRACE_ENABLED
979 }
980 #endif
982 /* If pTab is really a view, make sure it has been initialized.
983 ** ViewGetColumnNames() is a no-op if pTab is not a view.
984 */
987 }
989 /* Cannot insert into a read-only table.
990 */
993 }
995 /* Allocate a VDBE
996 */
1002 #ifndef SQLITE_OMIT_XFER_OPT
1003 /* If the statement is of the form
1004 **
1005 ** INSERT INTO <table1> SELECT * FROM <table2>;
1006 **
1007 ** Then special optimizations can be applied that make the transfer
1008 ** very fast and which reduce fragmentation of indices.
1009 **
1010 ** This is the 2nd template.
1011 */
1016 ){
1020 }
1023 /* If this is an AUTOINCREMENT table, look up the sequence number in the
1024 ** sqlite_sequence table and store it in memory cell regAutoinc.
1025 */
1028 /* Allocate a block registers to hold the rowid and the values
1029 ** for all columns of the new row.
1030 */
1034 regRowid++;
1036 }
1039 /* If the INSERT statement included an IDLIST term, then make sure
1040 ** all elements of the IDLIST really are columns of the table and
1041 ** remember the column indices.
1042 **
1043 ** If the table has an INTEGER PRIMARY KEY column and that column
1044 ** is named in the IDLIST, then record in the ipkColumn variable
1045 ** the index into IDLIST of the primary key column. ipkColumn is
1046 ** the index of the primary key as it appears in IDLIST, not as
1047 ** is appears in the original table. (The index of the INTEGER
1048 ** PRIMARY KEY in the original table is pTab->iPKey.) After this
1049 ** loop, if ipkColumn==(-1), that means that integer primary key
1050 ** is unspecified, and hence the table is either WITHOUT ROWID or
1051 ** it will automatically generated an integer primary key.
1052 **
1053 ** bIdListInOrder is true if the columns in IDLIST are in storage
1054 ** order. This enables an optimization that avoids shuffling the
1055 ** columns into storage order. False negatives are harmless,
1056 ** but false positives will cause database corruption.
1057 */
1064 }
1072 }
1073 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1079 }
1080 #endif
1082 }
1083 }
1093 }
1094 }
1095 }
1096 }
1098 /* Figure out how many columns of data are supplied. If the data
1099 ** is coming from a SELECT statement, then generate a co-routine that
1100 ** produces a single row of the SELECT on each invocation. The
1101 ** co-routine is the common header to the 3rd and 4th templates.
1102 */
1104 /* Data is coming from a SELECT or from a multi-row VALUES clause.
1105 ** Generate a co-routine to run the SELECT. */
1111 ){
1121 }
1139 }
1141 /* Set useTempTable to TRUE if the result of the SELECT statement
1142 ** should be written into a temporary table (template 4). Set to
1143 ** FALSE if each output row of the SELECT can be written directly into
1144 ** the destination table (template 3).
1145 **
1146 ** A temp table must be used if the table being updated is also one
1147 ** of the tables being read by the SELECT statement. Also use a
1148 ** temp table in the case of row triggers.
1149 */
1152 }
1155 /* Invoke the coroutine to extract information from the SELECT
1156 ** and add it to a transient table srcTab. The code generated
1157 ** here is from the 4th template:
1158 **
1159 ** B: open temp table
1160 ** L: yield X, goto M at EOF
1161 ** insert row from R..R+n into temp table
1162 ** goto L
1163 ** M: ...
1164 */
1181 }
1183 /* This is the case if the data for the INSERT is coming from a
1184 ** single-row VALUES clause
1185 */
1186 NameContext sNC;
1195 }
1198 }
1199 }
1201 /* If there is no IDLIST term but the table has an integer primary
1202 ** key, the set the ipkColumn variable to the integer primary key
1203 ** column index in the original table definition.
1204 */
1207 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1215 ipkColumn--;
1216 }
1217 }
1218 }
1219 #endif
1221 /* Make sure the number of columns in the source data matches the number
1222 ** of columns to be inserted into the table.
1223 */
1230 }
1231 }
1237 }
1238 }
1242 }
1244 /* Initialize the count of rows to be inserted
1245 */
1250 ){
1253 }
1255 /* If this is not a view, open the table and and all indices */
1263 }
1268 }
1270 }
1271 #ifndef SQLITE_OMIT_UPSERT
1278 }
1282 }
1285 }
1296 }
1297 }
1300 }
1301 #endif
1304 /* This is the top of the main insertion loop */
1306 /* This block codes the top of loop only. The complete loop is the
1307 ** following pseudocode (template 4):
1308 **
1309 ** rewind temp table, if empty goto D
1310 ** C: loop over rows of intermediate table
1311 ** transfer values form intermediate table into <table>
1312 ** end loop
1313 ** D: ...
1314 */
1318 /* This block codes the top of loop only. The complete loop is the
1319 ** following pseudocode (template 3):
1320 **
1321 ** C: yield X, at EOF goto D
1322 ** insert the select result into <table> from R..R+n
1323 ** goto C
1324 ** D: ...
1325 */
1330 /* tag-20191021-001: If the INTEGER PRIMARY KEY is being generated by the
1331 ** SELECT, go ahead and copy the value into the rowid slot now, so that
1332 ** the value does not get overwritten by a NULL at tag-20191021-002. */
1334 }
1335 }
1337 /* Compute data for ordinary columns of the new entry. Values
1338 ** are written in storage order into registers starting with regData.
1339 ** Only ordinary columns are computed in this loop. The rowid
1340 ** (if there is one) is computed later and generated columns are
1341 ** computed after the rowid since they might depend on the value
1342 ** of the rowid.
1343 */
1348 u32 colFlags;
1351 /* tag-20191021-002: References to the INTEGER PRIMARY KEY are filled
1352 ** using the rowid. So put a NULL in the IPK slot of the record to avoid
1353 ** using excess space. The file format definition requires this extra
1354 ** NULL - we cannot optimize further by skipping the column completely */
1357 }
1359 nHidden++;
1361 /* Virtual columns do not participate in OP_MakeRecord. So back up
1362 ** iRegStore by one slot to compensate for the iRegStore++ in the
1363 ** outer for() loop */
1364 iRegStore--;
1367 /* Stored columns are computed later. But if there are BEFORE
1368 ** triggers, the slots used for stored columns will be OP_Copy-ed
1369 ** to a second block of registers, so the register needs to be
1370 ** initialized to NULL to avoid an uninitialized register read */
1373 }
1376 /* Hidden columns that are not explicitly named in the INSERT
1377 ** get there default value */
1380 iRegStore);
1382 }
1383 }
1388 /* A column not named in the insert column list gets its
1389 ** default value */
1392 iRegStore);
1394 }
1397 /* This is INSERT INTO ... DEFAULT VALUES. Load the default value. */
1400 iRegStore);
1404 }
1411 }
1418 }
1419 }
1420 }
1423 /* Run the BEFORE and INSTEAD OF triggers, if there are any
1424 */
1429 /* build the NEW.* reference row. Note that if there is an INTEGER
1430 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
1431 ** translated into a unique ID for the row. But on a BEFORE trigger,
1432 ** we do not know what the unique ID will be (because the insert has
1433 ** not happened yet) so we substitute a rowid of -1
1434 */
1445 }
1450 }
1452 /* Copy the new data already generated. */
1456 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1457 /* Compute the new value for generated columns after all other
1458 ** columns have already been computed. This must be done after
1459 ** computing the ROWID in case one of the generated columns
1460 ** refers to the ROWID. */
1465 }
1466 #endif
1468 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
1469 ** do not attempt any conversions before assembling the record.
1470 ** If this is a real table, attempt conversions as required by the
1471 ** table column affinities.
1472 */
1475 }
1477 /* Fire BEFORE or INSTEAD OF triggers */
1482 }
1486 /* The row that the VUpdate opcode will delete: none */
1488 }
1490 /* Compute the new rowid */
1494 /* Rowid already initialized at tag-20191021-001 */
1502 }
1503 }
1504 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
1505 ** to generate a unique primary key value.
1506 */
1516 }
1518 }
1524 }
1527 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
1528 /* Compute the new value for generated columns after all other
1529 ** columns have already been computed. This must be done after
1530 ** computing the ROWID in case one of the generated columns
1531 ** is derived from the INTEGER PRIMARY KEY. */
1534 }
1535 #endif
1537 /* Generate code to check constraints and generate index keys and
1538 ** do the insertion.
1539 */
1540 #ifndef SQLITE_OMIT_VIRTUALTABLE
1548 #endif
1549 {
1554 );
1557 }
1559 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1560 ** constraints or (b) there are no triggers and this table is not a
1561 ** parent table in a foreign key constraint. It is safe to set the
1562 ** flag in the second case as if any REPLACE constraint is hit, an
1563 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1564 ** cursor that is disturbed. And these instructions both clear the
1565 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1566 ** functionality. */
1570 );
1571 }
1572 #ifdef SQLITE_ALLOW_ROWID_IN_VIEW
1574 /* If there is a RETURNING clause, populate the rowid register with
1575 ** constant value -1, in case one or more of the returned expressions
1576 ** refer to the "rowid" of the view. */
1578 #endif
1579 }
1581 /* Update the count of rows that are inserted
1582 */
1585 }
1588 /* Code AFTER triggers */
1591 }
1593 /* The bottom of the main insertion loop, if the data source
1594 ** is a SELECT statement.
1595 */
1603 #ifdef SQLITE_DEBUG
1604 /* If we are jumping back to an OP_Yield that is preceded by an
1605 ** OP_ReleaseReg, set the p5 flag on the OP_Goto so that the
1606 ** OP_ReleaseReg will be included in the loop. */
1610 }
1611 #endif
1613 }
1615 #ifndef SQLITE_OMIT_XFER_OPT
1616 insert_end:
1618 /* Update the sqlite_sequence table by storing the content of the
1619 ** maximum rowid counter values recorded while inserting into
1620 ** autoincrement tables.
1621 */
1624 }
1626 /*
1627 ** Return the number of rows inserted. If this routine is
1628 ** generating code because of a call to sqlite3NestedParse(), do not
1629 ** invoke the callback function.
1630 */
1633 }
1635 insert_cleanup:
1642 }
1644 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1645 ** they may interfere with compilation of other functions in this file
1646 ** (or in another file, if this file becomes part of the amalgamation). */
1647 #ifdef isView
1648 #undef isView
1649 #endif
1650 #ifdef pTrigger
1651 #undef pTrigger
1652 #endif
1653 #ifdef tmask
1654 #undef tmask
1655 #endif
1657 /*
1658 ** Meanings of bits in of pWalker->eCode for
1659 ** sqlite3ExprReferencesUpdatedColumn()
1660 */
1664 /* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
1665 * Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
1666 ** expression node references any of the
1667 ** columns that are being modified by an UPDATE statement.
1668 */
1675 }
1678 }
1679 }
1681 }
1683 /*
1684 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1685 ** only columns that are modified by the UPDATE are those for which
1686 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1687 **
1688 ** Return true if CHECK constraint pExpr uses any of the
1689 ** changing columns (or the rowid if it is changing). In other words,
1690 ** return true if this CHECK constraint must be validated for
1691 ** the new row in the UPDATE statement.
1692 **
1693 ** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
1694 ** The operation of this routine is the same - return true if an only if
1695 ** the expression uses one or more of columns identified by the second and
1696 ** third arguments.
1697 */
1702 ){
1703 Walker w;
1712 }
1718 }
1720 /*
1721 ** The sqlite3GenerateConstraintChecks() routine usually wants to visit
1722 ** the indexes of a table in the order provided in the Table->pIndex list.
1723 ** However, sometimes (rarely - when there is an upsert) it wants to visit
1724 ** the indexes in a different order. The following data structures accomplish
1725 ** this.
1726 **
1727 ** The IndexIterator object is used to walk through all of the indexes
1728 ** of a table in either Index.pNext order, or in some other order established
1729 ** by an array of IndexListTerm objects.
1730 */
1745 };
1747 /* When IndexIterator.eType==1, then each index is an array of instances
1748 ** of the following object
1749 */
1753 };
1755 /* Return the first index on the list */
1764 }
1765 }
1767 /* Return the next index from the list. Return NULL when out of indexes */
1774 }
1781 }
1782 }
1784 /*
1785 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1786 ** on table pTab.
1787 **
1788 ** The regNewData parameter is the first register in a range that contains
1789 ** the data to be inserted or the data after the update. There will be
1790 ** pTab->nCol+1 registers in this range. The first register (the one
1791 ** that regNewData points to) will contain the new rowid, or NULL in the
1792 ** case of a WITHOUT ROWID table. The second register in the range will
1793 ** contain the content of the first table column. The third register will
1794 ** contain the content of the second table column. And so forth.
1795 **
1796 ** The regOldData parameter is similar to regNewData except that it contains
1797 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1798 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1799 ** checking regOldData for zero.
1800 **
1801 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1802 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1803 ** might be modified by the UPDATE. If pkChng is false, then the key of
1804 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1805 **
1806 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1807 ** was explicitly specified as part of the INSERT statement. If pkChng
1808 ** is zero, it means that the either rowid is computed automatically or
1809 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1810 ** pkChng will only be true if the INSERT statement provides an integer
1811 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1812 **
1813 ** The code generated by this routine will store new index entries into
1814 ** registers identified by aRegIdx[]. No index entry is created for
1815 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1816 ** the same as the order of indices on the linked list of indices
1817 ** at pTab->pIndex.
1818 **
1819 ** (2019-05-07) The generated code also creates a new record for the
1820 ** main table, if pTab is a rowid table, and stores that record in the
1821 ** register identified by aRegIdx[nIdx] - in other words in the first
1822 ** entry of aRegIdx[] past the last index. It is important that the
1823 ** record be generated during constraint checks to avoid affinity changes
1824 ** to the register content that occur after constraint checks but before
1825 ** the new record is inserted.
1826 **
1827 ** The caller must have already opened writeable cursors on the main
1828 ** table and all applicable indices (that is to say, all indices for which
1829 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1830 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1831 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1832 ** for the first index in the pTab->pIndex list. Cursors for other indices
1833 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1834 **
1835 ** This routine also generates code to check constraints. NOT NULL,
1836 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1837 ** then the appropriate action is performed. There are five possible
1838 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1839 **
1840 ** Constraint type Action What Happens
1841 ** --------------- ---------- ----------------------------------------
1842 ** any ROLLBACK The current transaction is rolled back and
1843 ** sqlite3_step() returns immediately with a
1844 ** return code of SQLITE_CONSTRAINT.
1845 **
1846 ** any ABORT Back out changes from the current command
1847 ** only (do not do a complete rollback) then
1848 ** cause sqlite3_step() to return immediately
1849 ** with SQLITE_CONSTRAINT.
1850 **
1851 ** any FAIL Sqlite3_step() returns immediately with a
1852 ** return code of SQLITE_CONSTRAINT. The
1853 ** transaction is not rolled back and any
1854 ** changes to prior rows are retained.
1855 **
1856 ** any IGNORE The attempt in insert or update the current
1857 ** row is skipped, without throwing an error.
1858 ** Processing continues with the next row.
1859 ** (There is an immediate jump to ignoreDest.)
1860 **
1861 ** NOT NULL REPLACE The NULL value is replace by the default
1862 ** value for that column. If the default value
1863 ** is NULL, the action is the same as ABORT.
1864 **
1865 ** UNIQUE REPLACE The other row that conflicts with the row
1866 ** being inserted is removed.
1867 **
1868 ** CHECK REPLACE Illegal. The results in an exception.
1869 **
1870 ** Which action to take is determined by the overrideError parameter.
1871 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1872 ** is used. Or if pParse->onError==OE_Default then the onError value
1873 ** for the constraint is used.
1874 */
1889 ){
1907 /* Variables associated with retesting uniqueness constraints after
1908 ** replace triggers fire have run */
1923 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1924 ** normal rowid tables. nPkField is the number of key fields in the
1925 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1926 ** number of fields in the true primary key of the table. */
1933 }
1935 /* Record that this module has started */
1939 /* Test all NOT NULL constraints.
1940 */
1954 }
1957 nGenerated++;
1959 }
1961 /* Do not check NOT NULL on columns that do not change */
1963 }
1968 }
1972 ){
1979 }
1982 }
1992 nSeenReplace++;
1997 }
2000 /* no break */ deliberate_fall_through
2012 }
2018 }
2022 /* If there are no generated columns with NOT NULL constraints
2023 ** and no NOT NULL ON CONFLICT REPLACE constraints, then a single
2024 ** pass is sufficient */
2026 }
2029 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
2031 /* If any NOT NULL ON CONFLICT REPLACE constraints fired on the
2032 ** first pass, recomputed values for all generated columns, as
2033 ** those values might depend on columns affected by the REPLACE.
2034 */
2036 }
2037 #endif
2041 /* Test all CHECK constraints
2042 */
2043 #ifndef SQLITE_OMIT_CHECK
2054 ){
2055 /* The check constraints do not reference any of the columns being
2056 ** updated so there is no point it verifying the check constraint */
2058 }
2062 }
2068 }
2078 P5_ConstraintCheck);
2079 }
2081 }
2083 }
2086 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
2087 ** order:
2088 **
2089 ** (1) OE_Update
2090 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
2091 ** (3) OE_Replace
2092 **
2093 ** OE_Fail and OE_Ignore must happen before any changes are made.
2094 ** OE_Update guarantees that only a single row will change, so it
2095 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
2096 ** could happen in any order, but they are grouped up front for
2097 ** convenience.
2098 **
2099 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
2100 ** The order of constraints used to have OE_Update as (2) and OE_Abort
2101 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
2102 ** constraint before any others, so it had to be moved.
2103 **
2104 ** Constraint checking code is generated in this order:
2105 ** (A) The rowid constraint
2106 ** (B) Unique index constraints that do not have OE_Replace as their
2107 ** default conflict resolution strategy
2108 ** (C) Unique index that do use OE_Replace by default.
2109 **
2110 ** The ordering of (2) and (3) is accomplished by making sure the linked
2111 ** list of indexes attached to a table puts all OE_Replace indexes last
2112 ** in the list. See sqlite3CreateIndex() for where that happens.
2113 */
2120 /* There is just on ON CONFLICT clause and it has no constraint-target */
2123 /* A single ON CONFLICT DO NOTHING clause, without a constraint-target.
2124 ** Make all unique constraint resolution be OE_Ignore */
2128 /* A single ON CONFLICT DO UPDATE. Make all resolutions OE_Update */
2130 }
2132 /* Otherwise, we'll need to run the IndexListTerm array version of the
2133 ** iterator to ensure that all of the ON CONFLICT conditions are
2134 ** checked first and in order. */
2136 u64 nByte;
2141 }
2156 jj++;
2157 }
2162 i++;
2163 }
2168 i++;
2169 }
2171 }
2172 }
2174 /* Determine if it is possible that triggers (either explicitly coded
2175 ** triggers or FK resolution actions) might run as a result of deletes
2176 ** that happen when OE_Replace conflict resolution occurs. (Call these
2177 ** "replace triggers".) If any replace triggers run, we will need to
2178 ** recheck all of the uniqueness constraints after they have all run.
2179 ** But on the recheck, the resolution is OE_Abort instead of OE_Replace.
2180 **
2181 ** If replace triggers are a possibility, then
2182 **
2183 ** (1) Allocate register regTrigCnt and initialize it to zero.
2184 ** That register will count the number of replace triggers that
2185 ** fire. Constraint recheck only occurs if the number is positive.
2186 ** (2) Initialize pTrigger to the list of all DELETE triggers on pTab.
2187 ** (3) Initialize addrRecheck and lblRecheckOk
2188 **
2189 ** The uniqueness rechecking code will create a series of tests to run
2190 ** in a second pass. The addrRecheck and lblRecheckOk variables are
2191 ** used to link together these tests which are separated from each other
2192 ** in the generate bytecode.
2193 */
2195 /* There are not DELETE triggers nor FK constraints. No constraint
2196 ** rechecks are needed. */
2206 }
2208 /* Replace triggers might exist. Allocate the counter and
2209 ** initialize it to zero. */
2215 }
2216 }
2218 /* If rowid is changing, make sure the new rowid does not previously
2219 ** exist in the table.
2220 */
2224 /* Figure out what action to take in case of a rowid collision */
2230 }
2232 /* figure out whether or not upsert applies in this case */
2240 }
2241 }
2243 /* The first ON CONFLICT clause has a conflict target other than
2244 ** the IPK. We have to jump ahead to that first ON CONFLICT clause
2245 ** and then come back here and deal with the IPK afterwards */
2247 }
2248 }
2250 /* If the response to a rowid conflict is REPLACE but the response
2251 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
2252 ** to defer the running of the rowid conflict checking until after
2253 ** the UNIQUE constraints have run.
2254 */
2259 ){
2262 }
2265 /* pkChng!=0 does not mean that the rowid has changed, only that
2266 ** it might have changed. Skip the conflict logic below if the rowid
2267 ** is unchanged. */
2271 }
2273 /* Check to see if the new rowid already exists in the table. Skip
2274 ** the following conflict logic if it does not. */
2283 /* no break */ deliberate_fall_through
2284 }
2293 }
2295 /* If there are DELETE triggers on this table and the
2296 ** recursive-triggers flag is set, call GenerateRowDelete() to
2297 ** remove the conflicting row from the table. This will fire
2298 ** the triggers and remove both the table and index b-tree entries.
2299 **
2300 ** Otherwise, if there are no triggers or the recursive-triggers
2301 ** flag is not set, but the table has one or more indexes, call
2302 ** GenerateRowIndexDelete(). This removes the index b-tree entries
2303 ** only. The table b-tree entry will be replaced by the new entry
2304 ** when it is inserted.
2305 **
2306 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
2307 ** also invoke MultiWrite() to indicate that this VDBE may require
2308 ** statement rollback (if the statement is aborted after the delete
2309 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
2310 ** but being more selective here allows statements like:
2311 **
2312 ** REPLACE INTO t(rowid) VALUES($newrowid)
2313 **
2314 ** to run without a statement journal if there are no indexes on the
2315 ** table.
2316 */
2322 nReplaceTrig++;
2324 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2326 /* This OP_Delete opcode fires the pre-update-hook only. It does
2327 ** not modify the b-tree. It is more efficient to let the coming
2328 ** OP_Insert replace the existing entry than it is to delete the
2329 ** existing entry and then insert a new one. */
2336 }
2337 }
2340 }
2341 #ifndef SQLITE_OMIT_UPSERT
2344 /* no break */ deliberate_fall_through
2345 }
2346 #endif
2351 }
2352 }
2359 }
2360 }
2362 /* Test all UNIQUE constraints by creating entries for each UNIQUE
2363 ** index and making sure that duplicate entries do not already exist.
2364 ** Compute the revised record entries for indices as we go.
2365 **
2366 ** This loop also handles the case of the PRIMARY KEY index for a
2367 ** WITHOUT ROWID table.
2368 */
2370 pIdx;
2372 ){
2384 }
2385 }
2390 }
2395 /* Skip partial indices for which the WHERE clause is not true */
2400 SQLITE_JUMPIFNULL);
2402 }
2404 /* Create a record for this index entry as it should appear after
2405 ** the insert or update. Store that record in the aRegIdx[ix] register
2406 */
2425 }
2426 }
2429 #ifdef SQLITE_ENABLE_NULL_TRIM
2432 }
2433 #endif
2436 /* In an UPDATE operation, if this index is the PRIMARY KEY index
2437 ** of a WITHOUT ROWID table and there has been no change the
2438 ** primary key, then no collision is possible. The collision detection
2439 ** logic below can all be skipped. */
2443 }
2445 /* Find out what action to take in case there is a uniqueness conflict */
2450 }
2455 }
2457 /* Figure out if the upsert clause applies to this index */
2463 }
2464 }
2466 /* Collision detection may be omitted if all of the following are true:
2467 ** (1) The conflict resolution algorithm is REPLACE
2468 ** (2) The table is a WITHOUT ROWID table
2469 ** (3) There are no secondary indexes on the table
2470 ** (4) No delete triggers need to be fired if there is a conflict
2471 ** (5) No FK constraint counters need to be updated if a conflict occurs.
2472 **
2473 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row
2474 ** must be explicitly deleted in order to ensure any pre-update hook
2475 ** is invoked. */
2477 #ifndef SQLITE_ENABLE_PREUPDATE_HOOK
2485 ){
2488 }
2491 /* Check to see if the new index entry will be unique */
2493 addrConflictCk =
2497 /* Generate code to handle collisions */
2502 /* Conflict only if the rowid of the existing index entry
2503 ** is different from old-rowid */
2508 }
2511 /* Extract the PRIMARY KEY from the end of the index entry and
2512 ** store it in registers regR..regR+nPk-1 */
2520 }
2521 }
2523 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
2524 ** table, only conflict if the new PRIMARY KEY values are actually
2525 ** different from the old. See TH3 withoutrowid04.test.
2526 **
2527 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
2528 ** of the matched index row are different from the original PRIMARY
2529 ** KEY values of this row before the update. */
2541 }
2545 );
2549 }
2550 }
2551 }
2552 }
2554 /* Generate code that executes if the new index entry is not unique */
2566 }
2567 #ifndef SQLITE_OMIT_UPSERT
2570 /* no break */ deliberate_fall_through
2571 }
2572 #endif
2577 }
2588 nReplaceTrig++;
2589 }
2592 }
2598 }
2606 /* Here we insert code that will be invoked after all constraint
2607 ** checks have run, if and only if one or more replace triggers
2608 ** fired. */
2612 /* Bypass the recheck if this partial index is not defined
2613 ** for the current row */
2616 }
2617 /* Copy the constraint check code from above, except change
2618 ** the constraint-ok jump destination to be the address of
2619 ** the next retest block */
2622 /* The sqlite3VdbeAddOp4() call might reallocate the opcode array.
2623 ** Hence, make a complete copy of the opcode, rather than using
2624 ** a pointer to the opcode. */
2633 }
2638 }
2639 nConflictCk--;
2640 addrConflictCk++;
2641 }
2642 /* If the retest fails, issue an abort */
2646 }
2649 }
2650 }
2654 && upsertIpkReturn
2656 ){
2660 }
2661 }
2663 /* If the IPK constraint is a REPLACE, run it last */
2669 }
2671 /* Recheck all uniqueness constraints after replace triggers have run */
2681 }
2687 }
2689 }
2691 /* Generate the table record */
2698 }
2699 }
2703 }
2705 #ifdef SQLITE_ENABLE_NULL_TRIM
2706 /*
2707 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
2708 ** to be the number of columns in table pTab that must not be NULL-trimmed.
2709 **
2710 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
2711 */
2713 u16 i;
2715 /* Records with omitted columns are only allowed for schema format
2716 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
2722 }
2724 }
2725 #endif
2727 /*
2728 ** Table pTab is a WITHOUT ROWID table that is being written to. The cursor
2729 ** number is iCur, and register regData contains the new record for the
2730 ** PK index. This function adds code to invoke the pre-update hook,
2731 ** if one is registered.
2732 */
2733 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2739 ){
2748 }
2749 #else
2750 # define codeWithoutRowidPreupdate(a,b,c,d)
2751 #endif
2753 /*
2754 ** This routine generates code to finish the INSERT or UPDATE operation
2755 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
2756 ** A consecutive range of registers starting at regNewData contains the
2757 ** rowid and the content to be inserted.
2758 **
2759 ** The arguments to this routine should be the same as the first six
2760 ** arguments to sqlite3GenerateConstraintChecks.
2761 */
2772 ){
2781 );
2787 /* All REPLACE indexes are at the end of the list */
2795 }
2802 }
2803 }
2808 }
2815 }
2818 }
2821 }
2825 }
2827 }
2829 /*
2830 ** Allocate cursors for the pTab table and all its indices and generate
2831 ** code to open and initialized those cursors.
2832 **
2833 ** The cursor for the object that contains the complete data (normally
2834 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
2835 ** ROWID table) is returned in *piDataCur. The first index cursor is
2836 ** returned in *piIdxCur. The number of indices is returned.
2837 **
2838 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
2839 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
2840 ** If iBase is negative, then allocate the next available cursor.
2841 **
2842 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
2843 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
2844 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
2845 ** pTab->pIndex list.
2846 **
2847 ** If pTab is a virtual table, then this routine is a no-op and the
2848 ** *piDataCur and *piIdxCur values are left uninitialized.
2849 */
2859 ){
2871 /* This routine is a no-op for virtual tables. Leave the output
2872 ** variables *piDataCur and *piIdxCur set to illegal cursor numbers
2873 ** for improved error detection. */
2876 }
2887 }
2895 }
2901 }
2902 }
2905 }
2908 #ifdef SQLITE_TEST
2909 /*
2910 ** The following global variable is incremented whenever the
2911 ** transfer optimization is used. This is used for testing
2912 ** purposes only - to make sure the transfer optimization really
2913 ** is happening when it is supposed to.
2914 */
2919 #ifndef SQLITE_OMIT_XFER_OPT
2920 /*
2921 ** Check to see if index pSrc is compatible as a source of data
2922 ** for index pDest in an insert transfer optimization. The rules
2923 ** for a compatible index:
2924 **
2925 ** * The index is over the same set of columns
2926 ** * The same DESC and ASC markings occurs on all columns
2927 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
2928 ** * The same collating sequence on each column
2929 ** * The index has the exact same WHERE clause
2930 */
2937 }
2940 }
2944 }
2950 }
2951 }
2954 }
2957 }
2958 }
2961 }
2963 /* If no test above fails then the indices must be compatible */
2965 }
2967 /*
2968 ** Attempt the transfer optimization on INSERTs of the form
2969 **
2970 ** INSERT INTO tab1 SELECT * FROM tab2;
2971 **
2972 ** The xfer optimization transfers raw records from tab2 over to tab1.
2973 ** Columns are not decoded and reassembled, which greatly improves
2974 ** performance. Raw index records are transferred in the same way.
2975 **
2976 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2977 ** There are lots of rules for determining compatibility - see comments
2978 ** embedded in the code for details.
2979 **
2980 ** This routine returns TRUE if the optimization is guaranteed to be used.
2981 ** Sometimes the xfer optimization will only work if the destination table
2982 ** is empty - a factor that can only be determined at run-time. In that
2983 ** case, this routine generates code for the xfer optimization but also
2984 ** does a test to see if the destination table is empty and jumps over the
2985 ** xfer optimization code if the test fails. In that case, this routine
2986 ** returns FALSE so that the caller will know to go ahead and generate
2987 ** an unoptimized transfer. This routine also returns FALSE if there
2988 ** is no chance that the xfer optimization can be applied.
2989 **
2990 ** This optimization is particularly useful at making VACUUM run faster.
2991 */
2998 ){
3017 /* Do not attempt to process this query if there are an WITH clauses
3018 ** attached to it. Proceeding may generate a false "no such table: xxx"
3019 ** error if pSelect reads from a CTE named "xxx". */
3021 }
3022 #ifndef SQLITE_OMIT_VIRTUALTABLE
3025 }
3026 #endif
3030 }
3034 }
3037 }
3040 }
3043 }
3044 /* Do not need to test for a HAVING clause. If HAVING is present but
3045 ** there is no ORDER BY, we will get an error. */
3048 }
3051 }
3054 }
3057 }
3062 }
3066 }
3068 /* At this point we have established that the statement is of the
3069 ** correct syntactic form to participate in this optimization. Now
3070 ** we have to check the semantics.
3071 */
3076 }
3080 }
3083 }
3086 }
3089 }
3092 }
3095 }
3099 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
3102 ){
3104 }
3105 #endif
3106 #ifndef SQLITE_OMIT_GENERATED_COLUMNS
3107 /* Even if tables t1 and t2 have identical schemas, if they contain
3108 ** generated columns, then this statement is semantically incorrect:
3109 **
3110 ** INSERT INTO t2 SELECT * FROM t1;
3111 **
3112 ** The reason is that generated column values are returned by the
3113 ** the SELECT statement on the right but the INSERT statement on the
3114 ** left wants them to be omitted.
3115 **
3116 ** Nevertheless, this is a useful notational shorthand to tell SQLite
3117 ** to do a bulk transfer all of the content from t1 over to t2.
3118 **
3119 ** We could, in theory, disable this (except for internal use by the
3120 ** VACUUM command where it is actually needed). But why do that? It
3121 ** seems harmless enough, and provides a useful service.
3122 */
3126 }
3127 /* But the transfer is only allowed if both the source and destination
3128 ** tables have the exact same expressions for generated columns.
3129 ** This requirement could be relaxed for VIRTUAL columns, I suppose.
3130 */
3138 }
3139 }
3140 #endif
3143 }
3147 }
3150 }
3151 /* Default values for second and subsequent columns need to match. */
3162 ){
3164 }
3165 }
3166 }
3170 }
3173 }
3176 }
3179 /* The sqlite3FaultSim() call allows this corruption test to be
3180 ** bypassed during testing, in order to exercise other corruption tests
3181 ** further downstream. */
3183 }
3184 }
3185 #ifndef SQLITE_OMIT_CHECK
3189 ){
3191 }
3192 #endif
3193 #ifndef SQLITE_OMIT_FOREIGN_KEY
3194 /* Disallow the transfer optimization if the destination table contains
3195 ** any foreign key constraints. This is more restrictive than necessary.
3196 ** But the main beneficiary of the transfer optimization is the VACUUM
3197 ** command, and the VACUUM command disables foreign key constraints. So
3198 ** the extra complication to make this rule less restrictive is probably
3199 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
3200 */
3204 }
3205 #endif
3208 }
3210 /* If we get this far, it means that the xfer optimization is at
3211 ** least a possibility, though it might only work if the destination
3212 ** table (tab1) is initially empty.
3213 */
3214 #ifdef SQLITE_TEST
3215 sqlite3_xferopt_count++;
3216 #endif
3232 )){
3233 /* In some circumstances, we are able to run the xfer optimization
3234 ** only if the destination table is initially empty. Unless the
3235 ** DBFLAG_Vacuum flag is set, this block generates code to make
3236 ** that determination. If DBFLAG_Vacuum is set, then the destination
3237 ** table is always empty.
3238 **
3239 ** Conditions under which the destination must be empty:
3240 **
3241 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
3242 ** (If the destination is not initially empty, the rowid fields
3243 ** of index entries might need to change.)
3244 **
3245 ** (2) The destination has a unique index. (The xfer optimization
3246 ** is unable to test uniqueness.)
3247 **
3248 ** (3) onError is something other than OE_Abort and OE_Rollback.
3249 */
3253 }
3255 u8 insFlags;
3266 }
3273 }
3280 }
3281 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
3286 #endif
3287 {
3289 }
3293 }
3302 }
3307 }
3318 /* This INSERT command is part of a VACUUM operation, which guarantees
3319 ** that the destination table is empty. If all indexed columns use
3320 ** collation sequence BINARY, then it can also be assumed that the
3321 ** index will be populated by inserting keys in strictly sorted
3322 ** order. In this case, instead of seeking within the b-tree as part
3323 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
3324 ** OP_IdxInsert to seek to the point within the b-tree where each key
3325 ** should be inserted. This is faster.
3326 **
3327 ** If any of the indexed columns use a collation sequence other than
3328 ** BINARY, this optimization is disabled. This is because the user
3329 ** might change the definition of a collation sequence and then run
3330 ** a VACUUM command. In that case keys may not be written in strictly
3331 ** sorted order. */
3335 }
3340 }
3343 }
3349 ){
3351 }
3352 }
3359 }
3371 }
3372 }