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: |
aa2d8b0e8154dd2f5e2c837dc11ab362 |
| 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
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 }