/ Check-in [aa2d8b0e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhance the code generator for INSERT INTO ... SELECT so that the SELECT generates output directly in the registers that INSERT INTO will be using, in many cases, and OP_SCopy operations can thus be avoided.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | insert-optimization
Files: files | file ages | folders
SHA1: aa2d8b0e8154dd2f5e2c837dc11ab362b083495b
User & Date: drh 2014-02-16 01:55:49
Context
2014-02-17
14:59
Avoid unnecessary calls to applyAffinity() during INSERT and UPDATE operations, especially for table that have indices and tables for which all columns have affinity "NONE". check-in: 35b4d6e9 user: drh tags: insert-optimization
2014-02-16
01:55
Enhance the code generator for INSERT INTO ... SELECT so that the SELECT generates output directly in the registers that INSERT INTO will be using, in many cases, and OP_SCopy operations can thus be avoided. check-in: aa2d8b0e user: drh tags: insert-optimization
2014-02-14
23:49
Seek past NULLs in a top-constrained search. Avoid checking for NULLs in the body of the search. check-in: e07a32f3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  3108   3108   }
  3109   3109   
  3110   3110   /*
  3111   3111   ** Generate code that will evaluate expression pExpr and store the
  3112   3112   ** results in register target.  The results are guaranteed to appear
  3113   3113   ** in register target.
  3114   3114   */
  3115         -int sqlite3ExprCode(Parse *pParse, Expr *pExpr, int target){
         3115  +void sqlite3ExprCode(Parse *pParse, Expr *pExpr, int target){
  3116   3116     int inReg;
  3117   3117   
  3118   3118     assert( target>0 && target<=pParse->nMem );
  3119   3119     if( pExpr && pExpr->op==TK_REGISTER ){
  3120   3120       sqlite3VdbeAddOp2(pParse->pVdbe, OP_Copy, pExpr->iTable, target);
  3121   3121     }else{
  3122   3122       inReg = sqlite3ExprCodeTarget(pParse, pExpr, target);
  3123   3123       assert( pParse->pVdbe || pParse->db->mallocFailed );
  3124   3124       if( inReg!=target && pParse->pVdbe ){
  3125   3125         sqlite3VdbeAddOp2(pParse->pVdbe, OP_SCopy, inReg, target);
  3126   3126       }
  3127   3127     }
  3128         -  return target;
         3128  +}
         3129  +
         3130  +/*
         3131  +** Generate code that will evaluate expression pExpr and store the
         3132  +** results in register target.  The results are guaranteed to appear
         3133  +** in register target.  If the expression is constant, then this routine
         3134  +** might choose to code the expression at initialization time.
         3135  +*/
         3136  +void sqlite3ExprCodeFactorable(Parse *pParse, Expr *pExpr, int target){
         3137  +  if( pParse->okConstFactor && sqlite3ExprIsConstant(pExpr) ){
         3138  +    sqlite3ExprCodeAtInit(pParse, pExpr, target, 0);
         3139  +  }else{
         3140  +    sqlite3ExprCode(pParse, pExpr, target);
         3141  +  }
  3129   3142   }
  3130   3143   
  3131   3144   /*
  3132   3145   ** Generate code that evalutes the given expression and puts the result
  3133   3146   ** in register target.
  3134   3147   **
  3135   3148   ** Also make a copy of the expression results into another "cache" register
................................................................................
  3136   3149   ** and modify the expression so that the next time it is evaluated,
  3137   3150   ** the result is a copy of the cache register.
  3138   3151   **
  3139   3152   ** This routine is used for expressions that are used multiple 
  3140   3153   ** times.  They are evaluated once and the results of the expression
  3141   3154   ** are reused.
  3142   3155   */
  3143         -int sqlite3ExprCodeAndCache(Parse *pParse, Expr *pExpr, int target){
         3156  +void sqlite3ExprCodeAndCache(Parse *pParse, Expr *pExpr, int target){
  3144   3157     Vdbe *v = pParse->pVdbe;
  3145         -  int inReg;
  3146         -  inReg = sqlite3ExprCode(pParse, pExpr, target);
         3158  +  int iMem;
         3159  +
  3147   3160     assert( target>0 );
  3148         -  /* The only place, other than this routine, where expressions can be
  3149         -  ** converted to TK_REGISTER is internal subexpressions in BETWEEN and
  3150         -  ** CASE operators.  Neither ever calls this routine.  And this routine
  3151         -  ** is never called twice on the same expression.  Hence it is impossible
  3152         -  ** for the input to this routine to already be a register.  Nevertheless,
  3153         -  ** it seems prudent to keep the ALWAYS() in case the conditions above
  3154         -  ** change with future modifications or enhancements. */
  3155         -  if( ALWAYS(pExpr->op!=TK_REGISTER) ){  
  3156         -    int iMem;
  3157         -    iMem = ++pParse->nMem;
  3158         -    sqlite3VdbeAddOp2(v, OP_Copy, inReg, iMem);
  3159         -    exprToRegister(pExpr, iMem);
  3160         -  }
  3161         -  return inReg;
         3161  +  assert( pExpr->op!=TK_REGISTER );
         3162  +  sqlite3ExprCode(pParse, pExpr, target);
         3163  +  iMem = ++pParse->nMem;
         3164  +  sqlite3VdbeAddOp2(v, OP_Copy, target, iMem);
         3165  +  exprToRegister(pExpr, iMem);
  3162   3166   }
  3163   3167   
  3164   3168   #if defined(SQLITE_ENABLE_TREE_EXPLAIN)
  3165   3169   /*
  3166   3170   ** Generate a human-readable explanation of an expression tree.
  3167   3171   */
  3168   3172   void sqlite3ExplainExpr(Vdbe *pOut, Expr *pExpr){

Changes to src/insert.c.

   144    144   /*
   145    145   ** Return non-zero if the table pTab in database iDb or any of its indices
   146    146   ** have been opened at any point in the VDBE program beginning at location
   147    147   ** iStartAddr throught the end of the program.  This is used to see if 
   148    148   ** a statement of the form  "INSERT INTO <iDb, pTab> SELECT ..." can 
   149    149   ** run without using temporary table for the results of the SELECT. 
   150    150   */
   151         -static int readsTable(Parse *p, int iStartAddr, int iDb, Table *pTab){
          151  +static int readsTable(Parse *p, int iDb, Table *pTab){
   152    152     Vdbe *v = sqlite3GetVdbe(p);
   153    153     int i;
   154    154     int iEnd = sqlite3VdbeCurrentAddr(v);
   155    155   #ifndef SQLITE_OMIT_VIRTUALTABLE
   156    156     VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0;
   157    157   #endif
   158    158   
   159         -  for(i=iStartAddr; i<iEnd; i++){
          159  +  for(i=1; i<iEnd; i++){
   160    160       VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
   161    161       assert( pOp!=0 );
   162    162       if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
   163    163         Index *pIndex;
   164    164         int tnum = pOp->p2;
   165    165         if( tnum==pTab->tnum ){
   166    166           return 1;
................................................................................
   330    330   ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
   331    331   ** above are all no-ops
   332    332   */
   333    333   # define autoIncBegin(A,B,C) (0)
   334    334   # define autoIncStep(A,B,C)
   335    335   #endif /* SQLITE_OMIT_AUTOINCREMENT */
   336    336   
   337         -
   338         -/*
   339         -** Generate code for a co-routine that will evaluate a subquery one
   340         -** row at a time.
   341         -**
   342         -** The pSelect parameter is the subquery that the co-routine will evaluation.
   343         -** Information about the location of co-routine and the registers it will use
   344         -** is returned by filling in the pDest object.
   345         -**
   346         -** Registers are allocated as follows:
   347         -**
   348         -**   pDest->iSDParm      The register holding the next entry-point of the
   349         -**                       co-routine.  Run the co-routine to its next breakpoint
   350         -**                       by calling "OP_Yield $X" where $X is pDest->iSDParm.
   351         -**
   352         -**   pDest->iSdst        First result register.
   353         -**
   354         -**   pDest->nSdst        Number of result registers.
   355         -**
   356         -** At EOF the first result register will be marked as "undefined" so that
   357         -** the caller can know when to stop reading results.
   358         -**
   359         -** This routine handles all of the register allocation and fills in the
   360         -** pDest structure appropriately.
   361         -**
   362         -** Here is a schematic of the generated code assuming that X is the 
   363         -** co-routine entry-point register reg[pDest->iSDParm], that EOF is the
   364         -** completed flag reg[pDest->iSDParm+1], and R and S are the range of
   365         -** registers that hold the result set, reg[pDest->iSdst] through
   366         -** reg[pDest->iSdst+pDest->nSdst-1]:
   367         -**
   368         -**         X <- A
   369         -**         goto B
   370         -**      A: setup for the SELECT
   371         -**         loop rows in the SELECT
   372         -**           load results into registers R..S
   373         -**           yield X
   374         -**         end loop
   375         -**         cleanup after the SELECT
   376         -**         end co-routine R
   377         -**      B:
   378         -**
   379         -** To use this subroutine, the caller generates code as follows:
   380         -**
   381         -**         [ Co-routine generated by this subroutine, shown above ]
   382         -**      S: yield X, at EOF goto E
   383         -**         if skip this row, goto C
   384         -**         if terminate loop, goto E
   385         -**         deal with this row
   386         -**      C: goto S
   387         -**      E:
   388         -*/
   389         -int sqlite3CodeCoroutine(Parse *pParse, Select *pSelect, SelectDest *pDest){
   390         -  int regYield;       /* Register holding co-routine entry-point */
   391         -  int addrTop;        /* Top of the co-routine */
   392         -  int rc;             /* Result code */
   393         -  Vdbe *v;            /* VDBE under construction */
   394         -
   395         -  regYield = ++pParse->nMem;
   396         -  v = sqlite3GetVdbe(pParse);
   397         -  addrTop = sqlite3VdbeCurrentAddr(v) + 1;
   398         -  sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
   399         -  sqlite3SelectDestInit(pDest, SRT_Coroutine, regYield);
   400         -  rc = sqlite3Select(pParse, pSelect, pDest);
   401         -  assert( pParse->nErr==0 || rc );
   402         -  if( pParse->db->mallocFailed && rc==SQLITE_OK ) rc = SQLITE_NOMEM;
   403         -  if( rc ) return rc;
   404         -  sqlite3VdbeAddOp1(v, OP_EndCoroutine, regYield);
   405         -  sqlite3VdbeJumpHere(v, addrTop - 1);                       /* label B: */
   406         -  return rc;
   407         -}
   408         -
   409         -
   410    337   
   411    338   /* Forward declaration */
   412    339   static int xferOptimization(
   413    340     Parse *pParse,        /* Parser context */
   414    341     Table *pDest,         /* The table we are inserting into */
   415    342     Select *pSelect,      /* A SELECT statement to use as the data source */
   416    343     int onError,          /* How to handle constraint errors */
................................................................................
   527    454     Index *pIdx;          /* For looping over indices of the table */
   528    455     int nColumn;          /* Number of columns in the data */
   529    456     int nHidden = 0;      /* Number of hidden columns if TABLE is virtual */
   530    457     int iDataCur = 0;     /* VDBE cursor that is the main data repository */
   531    458     int iIdxCur = 0;      /* First index cursor */
   532    459     int ipkColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
   533    460     int endOfLoop;        /* Label for the end of the insertion loop */
   534         -  int useTempTable = 0; /* Store SELECT results in intermediate table */
   535    461     int srcTab = 0;       /* Data comes from this temporary cursor if >=0 */
   536    462     int addrInsTop = 0;   /* Jump to label "D" */
   537    463     int addrCont = 0;     /* Top of insert loop. Label "C" in templates 3 and 4 */
   538         -  int addrSelect = 0;   /* Address of coroutine that implements the SELECT */
   539    464     SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
   540    465     int iDb;              /* Index of database holding TABLE */
   541    466     Db *pDb;              /* The database containing table being inserted into */
   542         -  int appendFlag = 0;   /* True if the insert is likely to be an append */
   543         -  int withoutRowid;     /* 0 for normal table.  1 for WITHOUT ROWID table */
          467  +  u8 useTempTable = 0;  /* Store SELECT results in intermediate table */
          468  +  u8 appendFlag = 0;    /* True if the insert is likely to be an append */
          469  +  u8 withoutRowid;      /* 0 for normal table.  1 for WITHOUT ROWID table */
          470  +  u8 bIdListInOrder = 1; /* True if IDLIST is in table order */
   544    471     ExprList *pList = 0;  /* List of VALUES() to be inserted  */
   545    472   
   546    473     /* Register allocations */
   547    474     int regFromSelect = 0;/* Base register for data coming from SELECT */
   548    475     int regAutoinc = 0;   /* Register holding the AUTOINCREMENT counter */
   549    476     int regRowCount = 0;  /* Memory cell used for the row counter */
   550    477     int regIns;           /* Block of regs holding rowid+data being inserted */
................................................................................
   647    574     }
   648    575   #endif /* SQLITE_OMIT_XFER_OPT */
   649    576   
   650    577     /* If this is an AUTOINCREMENT table, look up the sequence number in the
   651    578     ** sqlite_sequence table and store it in memory cell regAutoinc.
   652    579     */
   653    580     regAutoinc = autoIncBegin(pParse, iDb, pTab);
          581  +
          582  +  /* Allocate registers for holding the rowid of the new row,
          583  +  ** the content of the new row, and the assemblied row record.
          584  +  */
          585  +  regRowid = regIns = pParse->nMem+1;
          586  +  pParse->nMem += pTab->nCol + 1;
          587  +  if( IsVirtual(pTab) ){
          588  +    regRowid++;
          589  +    pParse->nMem++;
          590  +  }
          591  +  regData = regRowid+1;
          592  +
          593  +  /* If the INSERT statement included an IDLIST term, then make sure
          594  +  ** all elements of the IDLIST really are columns of the table and 
          595  +  ** remember the column indices.
          596  +  **
          597  +  ** If the table has an INTEGER PRIMARY KEY column and that column
          598  +  ** is named in the IDLIST, then record in the ipkColumn variable
          599  +  ** the index into IDLIST of the primary key column.  ipkColumn is
          600  +  ** the index of the primary key as it appears in IDLIST, not as
          601  +  ** is appears in the original table.  (The index of the INTEGER
          602  +  ** PRIMARY KEY in the original table is pTab->iPKey.)
          603  +  */
          604  +  if( pColumn ){
          605  +    for(i=0; i<pColumn->nId; i++){
          606  +      pColumn->a[i].idx = -1;
          607  +    }
          608  +    for(i=0; i<pColumn->nId; i++){
          609  +      for(j=0; j<pTab->nCol; j++){
          610  +        if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
          611  +          pColumn->a[i].idx = j;
          612  +          if( i!=j ) bIdListInOrder = 0;
          613  +          if( j==pTab->iPKey ){
          614  +            ipkColumn = i;  assert( !withoutRowid );
          615  +          }
          616  +          break;
          617  +        }
          618  +      }
          619  +      if( j>=pTab->nCol ){
          620  +        if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
          621  +          ipkColumn = i;
          622  +        }else{
          623  +          sqlite3ErrorMsg(pParse, "table %S has no column named %s",
          624  +              pTabList, 0, pColumn->a[i].zName);
          625  +          pParse->checkSchema = 1;
          626  +          goto insert_cleanup;
          627  +        }
          628  +      }
          629  +    }
          630  +  }
   654    631   
   655    632     /* Figure out how many columns of data are supplied.  If the data
   656    633     ** is coming from a SELECT statement, then generate a co-routine that
   657    634     ** produces a single row of the SELECT on each invocation.  The
   658    635     ** co-routine is the common header to the 3rd and 4th templates.
   659    636     */
   660    637     if( pSelect ){
   661    638       /* Data is coming from a SELECT.  Generate a co-routine to run the SELECT */
   662         -    int rc = sqlite3CodeCoroutine(pParse, pSelect, &dest);
   663         -    if( rc ) goto insert_cleanup;
          639  +    int regYield;       /* Register holding co-routine entry-point */
          640  +    int addrTop;        /* Top of the co-routine */
          641  +    int rc;             /* Result code */
   664    642   
          643  +    regYield = ++pParse->nMem;
          644  +    addrTop = sqlite3VdbeCurrentAddr(v) + 1;
          645  +    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
          646  +    sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
          647  +    dest.iSdst = bIdListInOrder ? regData : 0;
          648  +    dest.nSdst = pTab->nCol;
          649  +    rc = sqlite3Select(pParse, pSelect, &dest);
   665    650       regFromSelect = dest.iSdst;
          651  +    assert( pParse->nErr==0 || rc );
          652  +    if( rc || db->mallocFailed ) goto insert_cleanup;
          653  +    sqlite3VdbeAddOp1(v, OP_EndCoroutine, regYield);
          654  +    sqlite3VdbeJumpHere(v, addrTop - 1);                       /* label B: */
   666    655       assert( pSelect->pEList );
   667    656       nColumn = pSelect->pEList->nExpr;
   668         -    assert( dest.nSdst==nColumn );
   669    657   
   670    658       /* Set useTempTable to TRUE if the result of the SELECT statement
   671    659       ** should be written into a temporary table (template 4).  Set to
   672    660       ** FALSE if each output row of the SELECT can be written directly into
   673    661       ** the destination table (template 3).
   674    662       **
   675    663       ** A temp table must be used if the table being updated is also one
   676    664       ** of the tables being read by the SELECT statement.  Also use a 
   677    665       ** temp table in the case of row triggers.
   678    666       */
   679         -    if( pTrigger || readsTable(pParse, addrSelect, iDb, pTab) ){
          667  +    if( pTrigger || readsTable(pParse, iDb, pTab) ){
   680    668         useTempTable = 1;
   681    669       }
   682    670   
   683    671       if( useTempTable ){
   684    672         /* Invoke the coroutine to extract information from the SELECT
   685    673         ** and add it to a transient table srcTab.  The code generated
   686    674         ** here is from the 4th template:
................................................................................
   720    708       nColumn = pList ? pList->nExpr : 0;
   721    709       for(i=0; i<nColumn; i++){
   722    710         if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
   723    711           goto insert_cleanup;
   724    712         }
   725    713       }
   726    714     }
          715  +
          716  +  /* If there is no IDLIST term but the table has an integer primary
          717  +  ** key, the set the ipkColumn variable to the integer primary key 
          718  +  ** column index in the original table definition.
          719  +  */
          720  +  if( pColumn==0 && nColumn>0 ){
          721  +    ipkColumn = pTab->iPKey;
          722  +  }
   727    723   
   728    724     /* Make sure the number of columns in the source data matches the number
   729    725     ** of columns to be inserted into the table.
   730    726     */
   731    727     if( IsVirtual(pTab) ){
   732    728       for(i=0; i<pTab->nCol; i++){
   733    729         nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
................................................................................
   739    735          pTabList, 0, pTab->nCol-nHidden, nColumn);
   740    736       goto insert_cleanup;
   741    737     }
   742    738     if( pColumn!=0 && nColumn!=pColumn->nId ){
   743    739       sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
   744    740       goto insert_cleanup;
   745    741     }
   746         -
   747         -  /* If the INSERT statement included an IDLIST term, then make sure
   748         -  ** all elements of the IDLIST really are columns of the table and 
   749         -  ** remember the column indices.
   750         -  **
   751         -  ** If the table has an INTEGER PRIMARY KEY column and that column
   752         -  ** is named in the IDLIST, then record in the ipkColumn variable
   753         -  ** the index into IDLIST of the primary key column.  ipkColumn is
   754         -  ** the index of the primary key as it appears in IDLIST, not as
   755         -  ** is appears in the original table.  (The index of the INTEGER
   756         -  ** PRIMARY KEY in the original table is pTab->iPKey.)
   757         -  */
   758         -  if( pColumn ){
   759         -    for(i=0; i<pColumn->nId; i++){
   760         -      pColumn->a[i].idx = -1;
   761         -    }
   762         -    for(i=0; i<pColumn->nId; i++){
   763         -      for(j=0; j<pTab->nCol; j++){
   764         -        if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
   765         -          pColumn->a[i].idx = j;
   766         -          if( j==pTab->iPKey ){
   767         -            ipkColumn = i;  assert( !withoutRowid );
   768         -          }
   769         -          break;
   770         -        }
   771         -      }
   772         -      if( j>=pTab->nCol ){
   773         -        if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
   774         -          ipkColumn = i;
   775         -        }else{
   776         -          sqlite3ErrorMsg(pParse, "table %S has no column named %s",
   777         -              pTabList, 0, pColumn->a[i].zName);
   778         -          pParse->checkSchema = 1;
   779         -          goto insert_cleanup;
   780         -        }
   781         -      }
   782         -    }
   783         -  }
   784         -
   785         -  /* If there is no IDLIST term but the table has an integer primary
   786         -  ** key, the set the ipkColumn variable to the integer primary key 
   787         -  ** column index in the original table definition.
   788         -  */
   789         -  if( pColumn==0 && nColumn>0 ){
   790         -    ipkColumn = pTab->iPKey;
   791         -  }
   792    742       
   793    743     /* Initialize the count of rows to be inserted
   794    744     */
   795    745     if( db->flags & SQLITE_CountRows ){
   796    746       regRowCount = ++pParse->nMem;
   797    747       sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
   798    748     }
................................................................................
   832    782       **         insert the select result into <table> from R..R+n
   833    783       **         goto C
   834    784       **      D: ...
   835    785       */
   836    786       addrInsTop = addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm);
   837    787     }
   838    788   
   839         -  /* Allocate registers for holding the rowid of the new row,
   840         -  ** the content of the new row, and the assemblied row record.
   841         -  */
   842         -  regRowid = regIns = pParse->nMem+1;
   843         -  pParse->nMem += pTab->nCol + 1;
   844         -  if( IsVirtual(pTab) ){
   845         -    regRowid++;
   846         -    pParse->nMem++;
   847         -  }
   848         -  regData = regRowid+1;
   849         -
   850    789     /* Run the BEFORE and INSTEAD OF triggers, if there are any
   851    790     */
   852    791     endOfLoop = sqlite3VdbeMakeLabel(v);
   853    792     if( tmask & TRIGGER_BEFORE ){
   854    793       int regCols = sqlite3GetTempRange(pParse, pTab->nCol+1);
   855    794   
   856    795       /* build the NEW.* reference row.  Note that if there is an INTEGER
................................................................................
   926    865         /* The row that the VUpdate opcode will delete: none */
   927    866         sqlite3VdbeAddOp2(v, OP_Null, 0, regIns);
   928    867       }
   929    868       if( ipkColumn>=0 ){
   930    869         if( useTempTable ){
   931    870           sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regRowid);
   932    871         }else if( pSelect ){
   933         -        sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+ipkColumn, regRowid);
          872  +        sqlite3VdbeAddOp2(v, OP_Copy, regFromSelect+ipkColumn, regRowid);
   934    873         }else{
   935    874           VdbeOp *pOp;
   936    875           sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regRowid);
   937    876           pOp = sqlite3VdbeGetOp(v, -1);
   938    877           if( ALWAYS(pOp) && pOp->opcode==OP_Null && !IsVirtual(pTab) ){
   939    878             appendFlag = 1;
   940    879             pOp->opcode = OP_NewRowid;
................................................................................
   972    911       nHidden = 0;
   973    912       for(i=0; i<pTab->nCol; i++){
   974    913         int iRegStore = regRowid+1+i;
   975    914         if( i==pTab->iPKey ){
   976    915           /* The value of the INTEGER PRIMARY KEY column is always a NULL.
   977    916           ** Whenever this column is read, the rowid will be substituted
   978    917           ** in its place.  Hence, fill this column with a NULL to avoid
   979         -        ** taking up data space with information that will never be used. */
   980         -        sqlite3VdbeAddOp2(v, OP_Null, 0, iRegStore);
          918  +        ** taking up data space with information that will never be used.
          919  +        ** As there may be shallow copies of this value, make it a soft-NULL */
          920  +        sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
   981    921           continue;
   982    922         }
   983    923         if( pColumn==0 ){
   984    924           if( IsHiddenColumn(&pTab->aCol[i]) ){
   985    925             assert( IsVirtual(pTab) );
   986    926             j = -1;
   987    927             nHidden++;
................................................................................
   990    930           }
   991    931         }else{
   992    932           for(j=0; j<pColumn->nId; j++){
   993    933             if( pColumn->a[j].idx==i ) break;
   994    934           }
   995    935         }
   996    936         if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){
   997         -        sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore);
          937  +        sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
   998    938         }else if( useTempTable ){
   999    939           sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, iRegStore); 
  1000    940         }else if( pSelect ){
  1001         -        sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+j, iRegStore);
          941  +        if( regFromSelect!=regData ){
          942  +          sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+j, iRegStore);
          943  +        }
  1002    944         }else{
  1003    945           sqlite3ExprCode(pParse, pList->a[j].pExpr, iRegStore);
  1004    946         }
  1005    947       }
  1006    948   
  1007    949       /* Generate code to check constraints and generate index keys and
  1008    950       ** do the insertion.

Changes to src/select.c.

   581    581     if( pOrderBy==0 && !hasDistinct ){
   582    582       codeOffset(v, p->iOffset, iContinue);
   583    583     }
   584    584   
   585    585     /* Pull the requested columns.
   586    586     */
   587    587     nResultCol = pEList->nExpr;
          588  +
   588    589     if( pDest->iSdst==0 ){
   589    590       pDest->iSdst = pParse->nMem+1;
   590         -    pDest->nSdst = nResultCol;
          591  +    pParse->nMem += nResultCol;
          592  +  }else if( pDest->iSdst+nResultCol > pParse->nMem ){
          593  +    /* This is an error condition that can result, for example, when a SELECT
          594  +    ** on the right-hand side of an INSERT contains more result columns than
          595  +    ** there are columns in the table on the left.  The error will be caught
          596  +    ** and reported later.  But we need to make sure enough memory is allocated
          597  +    ** to avoid other spurious errors in the meantime. */
   591    598       pParse->nMem += nResultCol;
   592         -  }else{ 
   593         -    assert( pDest->nSdst==nResultCol );
   594    599     }
          600  +  pDest->nSdst = nResultCol;
   595    601     regResult = pDest->iSdst;
   596    602     if( srcTab>=0 ){
   597    603       for(i=0; i<nResultCol; i++){
   598    604         sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, regResult+i);
   599    605         VdbeComment((v, "%s", pEList->a[i].zName));
   600    606       }
   601    607     }else if( eDest!=SRT_Exists ){

Changes to src/sqliteInt.h.

  2986   2986   #ifndef SQLITE_OMIT_AUTOINCREMENT
  2987   2987     void sqlite3AutoincrementBegin(Parse *pParse);
  2988   2988     void sqlite3AutoincrementEnd(Parse *pParse);
  2989   2989   #else
  2990   2990   # define sqlite3AutoincrementBegin(X)
  2991   2991   # define sqlite3AutoincrementEnd(X)
  2992   2992   #endif
  2993         -int sqlite3CodeCoroutine(Parse*, Select*, SelectDest*);
  2994   2993   void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int);
  2995   2994   void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
  2996   2995   IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
  2997   2996   int sqlite3IdListIndex(IdList*,const char*);
  2998   2997   SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
  2999   2998   SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
  3000   2999   SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
................................................................................
  3034   3033   void sqlite3ExprCodeMove(Parse*, int, int, int);
  3035   3034   void sqlite3ExprCacheStore(Parse*, int, int, int);
  3036   3035   void sqlite3ExprCachePush(Parse*);
  3037   3036   void sqlite3ExprCachePop(Parse*, int);
  3038   3037   void sqlite3ExprCacheRemove(Parse*, int, int);
  3039   3038   void sqlite3ExprCacheClear(Parse*);
  3040   3039   void sqlite3ExprCacheAffinityChange(Parse*, int, int);
  3041         -int sqlite3ExprCode(Parse*, Expr*, int);
         3040  +void sqlite3ExprCode(Parse*, Expr*, int);
         3041  +void sqlite3ExprCodeFactorable(Parse*, Expr*, int);
  3042   3042   void sqlite3ExprCodeAtInit(Parse*, Expr*, int, u8);
  3043   3043   int sqlite3ExprCodeTemp(Parse*, Expr*, int*);
  3044   3044   int sqlite3ExprCodeTarget(Parse*, Expr*, int);
  3045         -int sqlite3ExprCodeAndCache(Parse*, Expr*, int);
         3045  +void sqlite3ExprCodeAndCache(Parse*, Expr*, int);
  3046   3046   int sqlite3ExprCodeExprList(Parse*, ExprList*, int, u8);
  3047   3047   #define SQLITE_ECEL_DUP      0x01  /* Deep, not shallow copies */
  3048   3048   #define SQLITE_ECEL_FACTOR   0x02  /* Factor out constant terms */
  3049   3049   void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
  3050   3050   void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
  3051   3051   Table *sqlite3FindTable(sqlite3*,const char*, const char*);
  3052   3052   Table *sqlite3LocateTable(Parse*,int isView,const char*, const char*);

Changes to src/vdbe.c.

   990    990       VdbeMemRelease(pOut);
   991    991       pOut->flags = nullFlag;
   992    992       cnt--;
   993    993     }
   994    994     break;
   995    995   }
   996    996   
          997  +/* Opcode: SoftNull P1 * * * *
          998  +** Synopsis:  r[P1]=NULL
          999  +**
         1000  +** Set register P1 to have the value NULL as seen by the OP_MakeRecord
         1001  +** instruction, but do not free any string or blob memory associated with
         1002  +** the register, so that if the value was a string or blob that was
         1003  +** previously copied using OP_SCopy, the copies will continue to be valid.
         1004  +*/
         1005  +case OP_SoftNull: {
         1006  +  assert( pOp->p1>0 && pOp->p1<=(p->nMem-p->nCursor) );
         1007  +  pOut = &aMem[pOp->p1];
         1008  +  pOut->flags = (pOut->flags|MEM_Null)&~MEM_Undefined;
         1009  +  break;
         1010  +}
   997   1011   
   998   1012   /* Opcode: Blob P1 P2 * P4 *
   999   1013   ** Synopsis: r[P2]=P4 (len=P1)
  1000   1014   **
  1001   1015   ** P4 points to a blob of data P1 bytes long.  Store this
  1002   1016   ** blob in register P2.
  1003   1017   */
................................................................................
  2905   2919       rc = SQLITE_ERROR;
  2906   2920     }
  2907   2921     break;
  2908   2922   }
  2909   2923   
  2910   2924   /* Opcode: Transaction P1 P2 P3 P4 P5
  2911   2925   **
  2912         -** Begin a transaction.  The transaction ends when a Commit or Rollback
  2913         -** opcode is encountered.  Depending on the ON CONFLICT setting, the
  2914         -** transaction might also be rolled back if an error is encountered.
         2926  +** Begin a transaction on database P1 if a transaction is not already
         2927  +** active.
         2928  +** If P2 is non-zero, then a write-transaction is started, or if a 
         2929  +** read-transaction is already active, it is upgraded to a write-transaction.
         2930  +** If P2 is zero, then a read-transaction is started.
  2915   2931   **
  2916   2932   ** P1 is the index of the database file on which the transaction is
  2917   2933   ** started.  Index 0 is the main database file and index 1 is the
  2918   2934   ** file used for temporary tables.  Indices of 2 or more are used for
  2919   2935   ** attached databases.
  2920   2936   **
  2921         -** If P2 is non-zero, then a write-transaction is started.  A RESERVED lock is
  2922         -** obtained on the database file when a write-transaction is started.  No
  2923         -** other process can start another write transaction while this transaction is
  2924         -** underway.  Starting a write transaction also creates a rollback journal. A
  2925         -** write transaction must be started before any changes can be made to the
  2926         -** database.  If P2 is greater than or equal to 2 then an EXCLUSIVE lock is
  2927         -** also obtained on the file.
  2928         -**
  2929   2937   ** If a write-transaction is started and the Vdbe.usesStmtJournal flag is
  2930   2938   ** true (this flag is set if the Vdbe may modify more than one row and may
  2931   2939   ** throw an ABORT exception), a statement transaction may also be opened.
  2932   2940   ** More specifically, a statement transaction is opened iff the database
  2933   2941   ** connection is currently not in autocommit mode, or if there are other
  2934   2942   ** active statements. A statement transaction allows the changes made by this
  2935   2943   ** VDBE to be rolled back after an error without having to roll back the
  2936   2944   ** entire transaction. If no error is encountered, the statement transaction
  2937   2945   ** will automatically commit when the VDBE halts.
  2938   2946   **
  2939         -** If P2 is zero, then a read-lock is obtained on the database file.
  2940         -**
  2941   2947   ** If P5!=0 then this opcode also checks the schema cookie against P3
  2942   2948   ** and the schema generation counter against P4.
  2943   2949   ** The cookie changes its value whenever the database schema changes.
  2944   2950   ** This operation is used to detect when that the cookie has changed
  2945         -** and that the current process needs to reread the schema.
         2951  +** and that the current process needs to reread the schema.  If the schema
         2952  +** cookie in P3 differs from the schema cookie in the database header or
         2953  +** if the schema generation counter in P4 differs from the current
         2954  +** generation counter, then an SQLITE_SCHEMA error is raised and execution
         2955  +** halts.  The sqlite3_step() wrapper function might then reprepare the
         2956  +** statement and rerun it from the beginning.
  2946   2957   */
  2947   2958   case OP_Transaction: {
  2948   2959     Btree *pBt;
  2949   2960     int iMeta;
  2950   2961     int iGen;
  2951   2962   
  2952   2963     assert( p->bIsReader );

Changes to test/insert.test.

   394    394         CREATE TABLE t10(a,b,c);
   395    395         INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9);
   396    396         SELECT * FROM t10;
   397    397       }
   398    398     } {1 2 3 4 5 6 7 8 9}
   399    399     do_test insert-10.2 {
   400    400       catchsql {
   401         -      INSERT INTO t10 VALUES(11,12,13), (14,15);
          401  +      INSERT INTO t10 VALUES(11,12,13), (14,15), (16,17,28);
   402    402       }
   403    403     } {1 {all VALUES must have the same number of terms}}
   404    404   }
   405    405   
   406    406   integrity_check insert-99.0
   407    407   
   408    408   finish_test

Changes to test/insert4.test.

   249    249     } {}
   250    250   }
   251    251   
   252    252   # Check some error conditions:
   253    253   #
   254    254   do_test insert4-5.1 {
   255    255     # Table does not exist.
   256         -  catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
          256  +  catchsql { INSERT INTO t2 SELECT a, b FROM nosuchtable }
   257    257   } {1 {no such table: nosuchtable}}
   258    258   do_test insert4-5.2 {
   259    259     # Number of columns does not match.
   260    260     catchsql { 
   261    261       CREATE TABLE t5(a, b, c);
   262    262       INSERT INTO t4 SELECT * FROM t5;
   263    263     }