Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch insert-select-opt Excluding Merge-Ins
This is equivalent to a diff from 0a72726da2 to 08c0b19b89
|
2015-05-21
| ||
| 01:04 | Do not allow virtual table constructors to be called recursively. Cherrypick [0a72726da21581ab] (check-in: 0f0694e424 user: drh tags: branch-3.7.11) | |
|
2015-05-20
| ||
| 19:50 | Do not allow virtual table constructors to be called recursively. Cherrypick of [0a72726da215] (check-in: 023a29baf0 user: dan tags: branch-3.8.6) | |
|
2015-04-10
| ||
| 12:04 | Fix foreign key CASCADE for cases where the parent key is an INTEGER PRIMARY KEY and the parent table contains other columns named "rowid", "_rowid_", and "oid". (check-in: ed3cbaab6a user: drh tags: trunk) | |
| 08:28 | Update this branch with the latest changes from sorter-opt. (Leaf check-in: 08c0b19b89 user: dan tags: insert-select-opt) | |
| 08:20 | Update this branch with latest trunk changes. (check-in: 60be9c1c1a user: dan tags: sorter-opt) | |
| 07:55 | Do not allow virtual table constructors to be called recursively. (check-in: 0a72726da2 user: dan tags: trunk) | |
|
2015-04-09
| ||
| 19:39 | Fix incorrect column names in UPDATE statements generated by the sqldiff utility. (check-in: ee53b46011 user: drh tags: trunk) | |
|
2015-03-30
| ||
| 15:45 | Merge sorter optimizations with this branch. (check-in: 9bf1cfb4d9 user: dan tags: insert-select-opt) | |
Changes to src/build.c.
2759 2759 sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord, 2760 2760 pIndex->nKeyCol); VdbeCoverage(v); 2761 2761 sqlite3UniqueConstraint(pParse, OE_Abort, pIndex); 2762 2762 }else{ 2763 2763 addr2 = sqlite3VdbeCurrentAddr(v); 2764 2764 } 2765 2765 sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx); 2766 - sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1); 2766 + sqlite3VdbeAddOp3(v, OP_Last, iIdx, 0, -1); 2767 + sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 0); 2767 2768 sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); 2768 2769 sqlite3ReleaseTempReg(pParse, regRecord); 2769 2770 sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v); 2770 2771 sqlite3VdbeJumpHere(v, addr1); 2771 2772 2772 2773 sqlite3VdbeAddOp1(v, OP_Close, iTab); 2773 2774 sqlite3VdbeAddOp1(v, OP_Close, iIdx);
Changes to src/insert.c.
334 334 static int xferOptimization( 335 335 Parse *pParse, /* Parser context */ 336 336 Table *pDest, /* The table we are inserting into */ 337 337 Select *pSelect, /* A SELECT statement to use as the data source */ 338 338 int onError, /* How to handle constraint errors */ 339 339 int iDbDest /* The database of pDest */ 340 340 ); 341 + 342 +/* 343 +** Return the conflict handling mode that should be used for index pIdx 344 +** if the statement specified conflict mode overrideError. 345 +** 346 +** If the index is not a UNIQUE index, then the conflict handling mode is 347 +** always OE_None. Otherwise, it is one of OE_Abort, OE_Rollback, OE_Fail, 348 +** OE_Ignore or OE_Replace. 349 +*/ 350 +static u8 idxConflictMode(Index *pIdx, u8 overrideError){ 351 + u8 ret = pIdx->onError; 352 + if( ret!=OE_None ){ 353 + if( overrideError!=OE_Default ) ret = overrideError; 354 + if( ret==OE_Default ) ret = OE_Abort; 355 + } 356 + return ret; 357 +} 341 358 342 359 /* 343 360 ** This routine is called to handle SQL of the following forms: 344 361 ** 345 362 ** insert into TABLE (IDLIST) values(EXPRLIST) 346 363 ** insert into TABLE (IDLIST) select 347 364 ** ................................................................................ 447 464 int i, j, idx; /* Loop counters */ 448 465 Vdbe *v; /* Generate code into this virtual machine */ 449 466 Index *pIdx; /* For looping over indices of the table */ 450 467 int nColumn; /* Number of columns in the data */ 451 468 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */ 452 469 int iDataCur = 0; /* VDBE cursor that is the main data repository */ 453 470 int iIdxCur = 0; /* First index cursor */ 471 + int iSortCur = 0; /* First sorter cursor (for INSERT INTO ... SELECT) */ 454 472 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ 455 473 int endOfLoop; /* Label for the end of the insertion loop */ 456 474 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */ 457 475 int addrInsTop = 0; /* Jump to label "D" */ 458 476 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */ 459 477 SelectDest dest; /* Destination for SELECT on rhs of INSERT */ 460 478 int iDb; /* Index of database holding TABLE */ ................................................................................ 751 769 aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1)); 752 770 if( aRegIdx==0 ){ 753 771 goto insert_cleanup; 754 772 } 755 773 for(i=0; i<nIdx; i++){ 756 774 aRegIdx[i] = ++pParse->nMem; 757 775 } 776 + 777 + /* If this is an INSERT INTO ... SELECT statement on a non-virtual table, 778 + ** check if it is possible to defer updating any indexes until after 779 + ** all rows have been processed. If it is, the index keys can be sorted 780 + ** before they are inserted into the index b-tree, which is more efficient 781 + ** for large inserts. It is possible to defer updating the indexes if: 782 + ** 783 + ** * there are no triggers to fire, and 784 + ** * no foreign key processing to perform, and 785 + ** * the on-conflict mode used for all UNIQUE and PRIMARY KEY indexes, 786 + ** including INTEGER PRIMARY KEYs, is either ROLLBACK or ABORT. 787 + */ 788 + if( pSelect 789 + && 0==(pSelect->selFlags & SF_Values) 790 + && onError!=OE_Fail && onError!=OE_Replace && onError!=OE_Ignore 791 + && !IsVirtual(pTab) 792 + && pTrigger==0 793 + && 0==sqlite3FkRequired(pParse, pTab, 0, 0) 794 + ){ 795 + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 796 + u8 oe = idxConflictMode(pIdx, onError); 797 + if( oe==OE_Fail || oe==OE_Replace || oe==OE_Ignore ) break; 798 + assert( oe==OE_None || oe==OE_Abort || oe==OE_Rollback ); 799 + } 800 + if( pIdx==0 ){ 801 + /* This statement can sort the set of new keys for each index before 802 + ** writing them into the b-tree on disk. So open a sorter for each 803 + ** index on the table. */ 804 + iSortCur = pParse->nTab; 805 + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 806 + sqlite3VdbeAddOp1(v, OP_SorterOpen, pParse->nTab++); 807 + sqlite3VdbeSetP4KeyInfo(pParse, pIdx); 808 + } 809 + assert( iSortCur>0 ); 810 + } 811 + } 758 812 } 759 813 760 814 /* This is the top of the main insertion loop */ 761 815 if( useTempTable ){ 762 816 /* This block codes the top of loop only. The complete loop is the 763 817 ** following pseudocode (template 4): 764 818 ** ................................................................................ 952 1006 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB); 953 1007 sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError); 954 1008 sqlite3MayAbort(pParse); 955 1009 }else 956 1010 #endif 957 1011 { 958 1012 int isReplace; /* Set to true if constraints may cause a replace */ 1013 + int iIdxBase = iIdxCur; 1014 + int op = OP_IdxInsert; 959 1015 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, 960 - regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace 1016 + regIns, 0, ipkColumn>=0, onError, endOfLoop, iSortCur!=0, &isReplace 961 1017 ); 1018 + if( iSortCur ){ 1019 + iIdxBase = iSortCur; 1020 + isReplace = 1; 1021 + op = OP_SorterInsert; 1022 + } 962 1023 sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); 963 - sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, 964 - regIns, aRegIdx, 0, appendFlag, isReplace==0); 1024 + sqlite3CompleteInsertion(pParse, pTab, 1025 + iDataCur, iIdxBase, regIns, op, aRegIdx, 0, appendFlag, isReplace==0 1026 + ); 965 1027 } 966 1028 } 967 1029 968 1030 /* Update the count of rows that are inserted 969 1031 */ 970 1032 if( (db->flags & SQLITE_CountRows)!=0 ){ 971 1033 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); ................................................................................ 987 1049 sqlite3VdbeAddOp1(v, OP_Close, srcTab); 988 1050 }else if( pSelect ){ 989 1051 sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont); 990 1052 sqlite3VdbeJumpHere(v, addrInsTop); 991 1053 } 992 1054 993 1055 if( !IsVirtual(pTab) && !isView ){ 1056 + /* If new index keys were written into sorter objects instead of 1057 + ** directly to the index b-trees, copy them from the sorters into the 1058 + ** indexes now. And close all the sorters. */ 1059 + if( iSortCur ){ 1060 + int iTmp = sqlite3GetTempReg(pParse); 1061 + for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 1062 + int oe = idxConflictMode(pIdx, onError); 1063 + int iCur = iSortCur + idx; 1064 + int iIdx = iIdxCur + idx; 1065 + int addr = sqlite3VdbeAddOp1(v, OP_SorterSort, iCur); 1066 + sqlite3VdbeAddOp3(v, OP_SorterData, iCur, iTmp, iIdx); 1067 + if( oe!=OE_None ){ 1068 + int nField = -1 * pIdx->nKeyCol; 1069 + int jmp = sqlite3VdbeCurrentAddr(v)+2; 1070 + sqlite3VdbeAddOp4Int(v, OP_NoConflict, iIdx, jmp, iTmp, nField); 1071 + sqlite3UniqueConstraint(pParse, oe, pIdx); 1072 + } 1073 + sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, iTmp); 1074 + if( oe!=OE_None ) sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); 1075 + sqlite3VdbeAddOp2(v, OP_SorterNext, iCur, addr+1); VdbeCoverage(v); 1076 + sqlite3VdbeJumpHere(v, addr); 1077 + sqlite3VdbeAddOp1(v, OP_Close, iCur); 1078 + } 1079 + sqlite3ReleaseTempReg(pParse, iTmp); 1080 + } 1081 + 994 1082 /* Close all tables opened */ 995 1083 if( iDataCur<iIdxCur ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur); 996 1084 for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 997 1085 sqlite3VdbeAddOp1(v, OP_Close, idx+iIdxCur); 998 1086 } 999 1087 } 1000 1088 ................................................................................ 1129 1217 int iDataCur, /* Canonical data cursor (main table or PK index) */ 1130 1218 int iIdxCur, /* First index cursor */ 1131 1219 int regNewData, /* First register in a range holding values to insert */ 1132 1220 int regOldData, /* Previous content. 0 for INSERTs */ 1133 1221 u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */ 1134 1222 u8 overrideError, /* Override onError to this if not OE_Default */ 1135 1223 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */ 1224 + int ignoreUnique, /* Do not enforce UNIQUE constraints */ 1136 1225 int *pbMayReplace /* OUT: Set to true if constraint may cause a replace */ 1137 1226 ){ 1138 1227 Vdbe *v; /* VDBE under constrution */ 1139 1228 Index *pIdx; /* Pointer to one of the indices */ 1140 1229 Index *pPk = 0; /* The PRIMARY KEY index */ 1141 1230 sqlite3 *db; /* Database connection */ 1142 1231 int i; /* loop counter */ ................................................................................ 1409 1498 ** logic below can all be skipped. */ 1410 1499 if( isUpdate && pPk==pIdx && pkChng==0 ){ 1411 1500 sqlite3VdbeResolveLabel(v, addrUniqueOk); 1412 1501 continue; 1413 1502 } 1414 1503 1415 1504 /* Find out what action to take in case there is a uniqueness conflict */ 1416 - onError = pIdx->onError; 1417 - if( onError==OE_None ){ 1505 + onError = idxConflictMode(pIdx, overrideError); 1506 + if( onError==OE_None || ignoreUnique ){ 1418 1507 sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn); 1419 1508 sqlite3VdbeResolveLabel(v, addrUniqueOk); 1420 1509 continue; /* pIdx is not a UNIQUE index */ 1421 1510 } 1422 - if( overrideError!=OE_Default ){ 1423 - onError = overrideError; 1424 - }else if( onError==OE_Default ){ 1425 - onError = OE_Abort; 1426 - } 1427 1511 1428 1512 /* Check to see if the new index entry will be unique */ 1429 1513 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, 1430 1514 regIdx, pIdx->nKeyCol); VdbeCoverage(v); 1431 1515 1432 1516 /* Generate code to handle collisions */ 1433 1517 regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField); ................................................................................ 1534 1618 */ 1535 1619 void sqlite3CompleteInsertion( 1536 1620 Parse *pParse, /* The parser context */ 1537 1621 Table *pTab, /* the table into which we are inserting */ 1538 1622 int iDataCur, /* Cursor of the canonical data source */ 1539 1623 int iIdxCur, /* First index cursor */ 1540 1624 int regNewData, /* Range of content */ 1625 + int idxop, /* Opcode to use to write to "indexes" */ 1541 1626 int *aRegIdx, /* Register used by each index. 0 for unused indices */ 1542 1627 int isUpdate, /* True for UPDATE, False for INSERT */ 1543 1628 int appendBias, /* True if this is likely to be an append */ 1544 1629 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */ 1545 1630 ){ 1546 1631 Vdbe *v; /* Prepared statements under construction */ 1547 1632 Index *pIdx; /* An index being inserted or updated */ 1548 1633 u8 pik_flags; /* flag values passed to the btree insert */ 1549 1634 int regData; /* Content registers (after the rowid) */ 1550 1635 int regRec; /* Register holding assembled record for the table */ 1551 1636 int i; /* Loop counter */ 1552 1637 u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */ 1553 1638 1639 + assert( idxop==OP_IdxInsert || idxop==OP_SorterInsert ); 1640 + 1554 1641 v = sqlite3GetVdbe(pParse); 1555 1642 assert( v!=0 ); 1556 1643 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 1557 1644 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 1558 1645 if( aRegIdx[i]==0 ) continue; 1559 1646 bAffinityDone = 1; 1560 1647 if( pIdx->pPartIdxWhere ){ 1561 1648 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2); 1562 1649 VdbeCoverage(v); 1563 1650 } 1564 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]); 1651 + sqlite3VdbeAddOp2(v, idxop, iIdxCur+i, aRegIdx[i]); 1565 1652 pik_flags = 0; 1566 1653 if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT; 1567 1654 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ 1568 1655 assert( pParse->nested==0 ); 1569 1656 pik_flags |= OPFLAG_NCHANGE; 1570 1657 } 1571 1658 if( pik_flags ) sqlite3VdbeChangeP5(v, pik_flags); ................................................................................ 1761 1848 static int xferOptimization( 1762 1849 Parse *pParse, /* Parser context */ 1763 1850 Table *pDest, /* The table we are inserting into */ 1764 1851 Select *pSelect, /* A SELECT statement to use as the data source */ 1765 1852 int onError, /* How to handle constraint errors */ 1766 1853 int iDbDest /* The database of pDest */ 1767 1854 ){ 1855 + sqlite3 *db = pParse->db; 1768 1856 ExprList *pEList; /* The result set of the SELECT */ 1769 1857 Table *pSrc; /* The table in the FROM clause of SELECT */ 1770 1858 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */ 1771 1859 struct SrcList_item *pItem; /* An element of pSelect->pSrc */ 1772 1860 int i; /* Loop counter */ 1773 1861 int iDbSrc; /* The database of pSrc */ 1774 1862 int iSrc, iDest; /* Cursors from source and destination */ ................................................................................ 1908 1996 /* Disallow the transfer optimization if the destination table constains 1909 1997 ** any foreign key constraints. This is more restrictive than necessary. 1910 1998 ** But the main beneficiary of the transfer optimization is the VACUUM 1911 1999 ** command, and the VACUUM command disables foreign key constraints. So 1912 2000 ** the extra complication to make this rule less restrictive is probably 1913 2001 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e] 1914 2002 */ 1915 - if( (pParse->db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){ 2003 + if( (db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){ 1916 2004 return 0; 1917 2005 } 1918 2006 #endif 1919 - if( (pParse->db->flags & SQLITE_CountRows)!=0 ){ 2007 + if( (db->flags & SQLITE_CountRows)!=0 ){ 1920 2008 return 0; /* xfer opt does not play well with PRAGMA count_changes */ 1921 2009 } 1922 2010 1923 2011 /* If we get this far, it means that the xfer optimization is at 1924 2012 ** least a possibility, though it might only work if the destination 1925 2013 ** table (tab1) is initially empty. 1926 2014 */ 1927 2015 #ifdef SQLITE_TEST 1928 2016 sqlite3_xferopt_count++; 1929 2017 #endif 1930 - iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema); 2018 + iDbSrc = sqlite3SchemaToIndex(db, pSrc->pSchema); 1931 2019 v = sqlite3GetVdbe(pParse); 1932 2020 sqlite3CodeVerifySchema(pParse, iDbSrc); 1933 2021 iSrc = pParse->nTab++; 1934 2022 iDest = pParse->nTab++; 1935 2023 regAutoinc = autoIncBegin(pParse, iDbDest, pDest); 1936 2024 regData = sqlite3GetTempReg(pParse); 1937 2025 regRowid = sqlite3GetTempReg(pParse); 1938 2026 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); 1939 2027 assert( HasRowid(pDest) || destHasUniqueIdx ); 1940 - if( (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */ 2028 + if( (db->flags & SQLITE_Vacuum)==0 && ( 2029 + (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */ 1941 2030 || destHasUniqueIdx /* (2) */ 1942 2031 || (onError!=OE_Abort && onError!=OE_Rollback) /* (3) */ 1943 - ){ 2032 + )){ 1944 2033 /* In some circumstances, we are able to run the xfer optimization 1945 - ** only if the destination table is initially empty. This code makes 1946 - ** that determination. Conditions under which the destination must 1947 - ** be empty: 2034 + ** only if the destination table is initially empty. Unless the 2035 + ** SQLITE_Vacuum flag is set, this block generates code to make 2036 + ** that determination. If SQLITE_Vacuum is set, then the destination 2037 + ** table is always empty. 2038 + ** 2039 + ** Conditions under which the destination must be empty: 1948 2040 ** 1949 2041 ** (1) There is no INTEGER PRIMARY KEY but there are indices. 1950 2042 ** (If the destination is not initially empty, the rowid fields 1951 2043 ** of index entries might need to change.) 1952 2044 ** 1953 2045 ** (2) The destination has a unique index. (The xfer optimization 1954 2046 ** is unable to test uniqueness.) ................................................................................ 1983 2075 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 1984 2076 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 1985 2077 }else{ 1986 2078 sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName); 1987 2079 sqlite3TableLock(pParse, iDbSrc, pSrc->tnum, 0, pSrc->zName); 1988 2080 } 1989 2081 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ 2082 + u8 useSeekResult = 0; 1990 2083 for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){ 1991 2084 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; 1992 2085 } 1993 2086 assert( pSrcIdx ); 1994 2087 sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc); 1995 2088 sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx); 1996 2089 VdbeComment((v, "%s", pSrcIdx->zName)); 1997 2090 sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest); 1998 2091 sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx); 1999 2092 sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR); 2000 2093 VdbeComment((v, "%s", pDestIdx->zName)); 2001 2094 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v); 2002 2095 sqlite3VdbeAddOp2(v, OP_RowKey, iSrc, regData); 2096 + if( db->flags & SQLITE_Vacuum ){ 2097 + /* This INSERT command is part of a VACUUM operation, which guarantees 2098 + ** that the destination table is empty. If all indexed columns use 2099 + ** collation sequence BINARY, then it can also be assumed that the 2100 + ** index will be populated by inserting keys in strictly sorted 2101 + ** order. In this case, instead of seeking within the b-tree as part 2102 + ** of every OP_IdxInsert opcode, an OP_Last is added before the 2103 + ** OP_IdxInsert to seek to the point within the b-tree where each key 2104 + ** should be inserted. This is faster. 2105 + ** 2106 + ** If any of the indexed columns use a collation sequence other than 2107 + ** BINARY, this optimization is disabled. This is because the user 2108 + ** might change the definition of a collation sequence and then run 2109 + ** a VACUUM command. In that case keys may not be written in strictly 2110 + ** sorted order. */ 2111 + int i; 2112 + for(i=0; i<pSrcIdx->nColumn; i++){ 2113 + char *zColl = pSrcIdx->azColl[i]; 2114 + if( zColl && sqlite3_stricmp("BINARY", zColl) ) break; 2115 + } 2116 + if( i==pSrcIdx->nColumn ){ 2117 + useSeekResult = OPFLAG_USESEEKRESULT; 2118 + sqlite3VdbeAddOp3(v, OP_Last, iDest, 0, -1); 2119 + } 2120 + } 2003 2121 sqlite3VdbeAddOp3(v, OP_IdxInsert, iDest, regData, 1); 2122 + sqlite3VdbeChangeP5(v, useSeekResult); 2004 2123 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v); 2005 2124 sqlite3VdbeJumpHere(v, addr1); 2006 2125 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 2007 2126 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 2008 2127 } 2009 2128 if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest); 2010 2129 sqlite3ReleaseTempReg(pParse, regRowid);
Changes to src/sqliteInt.h.
1222 1222 #define SQLITE_AutoIndex 0x00100000 /* Enable automatic indexes */ 1223 1223 #define SQLITE_PreferBuiltin 0x00200000 /* Preference to built-in funcs */ 1224 1224 #define SQLITE_LoadExtension 0x00400000 /* Enable load_extension */ 1225 1225 #define SQLITE_EnableTrigger 0x00800000 /* True to enable triggers */ 1226 1226 #define SQLITE_DeferFKs 0x01000000 /* Defer all FK constraints */ 1227 1227 #define SQLITE_QueryOnly 0x02000000 /* Disable database changes */ 1228 1228 #define SQLITE_VdbeEQP 0x04000000 /* Debug EXPLAIN QUERY PLAN */ 1229 +#define SQLITE_Vacuum 0x08000000 /* Currently in a VACUUM */ 1229 1230 1230 1231 1231 1232 /* 1232 1233 ** Bits of the sqlite3.dbOptFlags field that are used by the 1233 1234 ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to 1234 1235 ** selectively disable various optimizations. 1235 1236 */ ................................................................................ 3349 3350 int sqlite3ExprNeedsNoAffinityChange(const Expr*, char); 3350 3351 int sqlite3IsRowid(const char*); 3351 3352 void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8); 3352 3353 void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*); 3353 3354 int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int); 3354 3355 void sqlite3ResolvePartIdxLabel(Parse*,int); 3355 3356 void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int, 3356 - u8,u8,int,int*); 3357 -void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int); 3357 + u8,u8,int,int,int*); 3358 +void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int,int*,int,int,int); 3358 3359 int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*); 3359 3360 void sqlite3BeginWriteOperation(Parse*, int, int); 3360 3361 void sqlite3MultiWrite(Parse*); 3361 3362 void sqlite3MayAbort(Parse*); 3362 3363 void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8); 3363 3364 void sqlite3UniqueConstraint(Parse*, int, Index*); 3364 3365 void sqlite3RowidConstraint(Parse*, int, Table*);
Changes to src/update.c.
563 563 if( !isView ){ 564 564 int j1 = 0; /* Address of jump instruction */ 565 565 int bReplace = 0; /* True if REPLACE conflict resolution might happen */ 566 566 567 567 /* Do constraint checks. */ 568 568 assert( regOldRowid>0 ); 569 569 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, 570 - regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace); 570 + regNewRowid, regOldRowid, chngKey, onError, labelContinue, 0,&bReplace); 571 571 572 572 /* Do FK constraint checks. */ 573 573 if( hasFK ){ 574 574 sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey); 575 575 } 576 576 577 577 /* Delete the index entries associated with the current record. */ ................................................................................ 595 595 596 596 if( hasFK ){ 597 597 sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey); 598 598 } 599 599 600 600 /* Insert the new index entries and the new record. */ 601 601 sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, 602 - regNewRowid, aRegIdx, 1, 0, 0); 602 + regNewRowid, OP_IdxInsert, aRegIdx, 1, 0, 0); 603 603 604 604 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 605 605 ** handle rows (possibly in other tables) that refer via a foreign key 606 606 ** to the row just updated. */ 607 607 if( hasFK ){ 608 608 sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); 609 609 }
Changes to src/vacuum.c.
246 246 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"); 247 247 if( rc!=SQLITE_OK ) goto end_of_vacuum; 248 248 249 249 /* Loop through the tables in the main database. For each, do 250 250 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy 251 251 ** the contents to the temporary database. 252 252 */ 253 + assert( (db->flags & SQLITE_Vacuum)==0 ); 254 + db->flags |= SQLITE_Vacuum; 253 255 rc = execExecSql(db, pzErrMsg, 254 256 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 255 257 "|| ' SELECT * FROM main.' || quote(name) || ';'" 256 258 "FROM main.sqlite_master " 257 259 "WHERE type = 'table' AND name!='sqlite_sequence' " 258 260 " AND coalesce(rootpage,1)>0" 259 261 ); 262 + assert( (db->flags & SQLITE_Vacuum)!=0 ); 263 + db->flags &= ~SQLITE_Vacuum; 260 264 if( rc!=SQLITE_OK ) goto end_of_vacuum; 261 265 262 266 /* Copy over the sequence table 263 267 */ 264 268 rc = execExecSql(db, pzErrMsg, 265 269 "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " 266 270 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
Changes to src/vdbe.c.
160 160 ** string that the register itself controls. In other words, it 161 161 ** converts an MEM_Ephem string into a string with P.z==P.zMalloc. 162 162 */ 163 163 #define Deephemeralize(P) \ 164 164 if( ((P)->flags&MEM_Ephem)!=0 \ 165 165 && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;} 166 166 167 -/* Return true if the cursor was opened using the OP_OpenSorter opcode. */ 167 +/* Return true if the cursor was opened using the OP_SorterOpen opcode. */ 168 168 #define isSorter(x) ((x)->pSorter!=0) 169 169 170 170 /* 171 171 ** Allocate VdbeCursor number iCur. Return a pointer to it. Return NULL 172 172 ** if we run out of memory. 173 173 */ 174 174 static VdbeCursor *allocateCursor( ................................................................................ 3788 3788 ** See also: Found, NotExists, NoConflict 3789 3789 */ 3790 3790 /* Opcode: NoConflict P1 P2 P3 P4 * 3791 3791 ** Synopsis: key=r[P3@P4] 3792 3792 ** 3793 3793 ** If P4==0 then register P3 holds a blob constructed by MakeRecord. If 3794 3794 ** P4>0 then register P3 is the first of P4 registers that form an unpacked 3795 -** record. 3795 +** record. If P4<0, then P3 holds a blob constructed by MakeRecord, but 3796 +** only the first |P4| fields should be considered. 3796 3797 ** 3797 3798 ** Cursor P1 is on an index btree. If the record identified by P3 and P4 3798 3799 ** contains any NULL value, jump immediately to P2. If all terms of the 3799 3800 ** record are not-NULL then a check is done to determine if any row in the 3800 3801 ** P1 index btree has a matching key prefix. If there are no matches, jump 3801 3802 ** immediately to P2. If there is a match, fall through and leave the P1 3802 3803 ** cursor pointing to the matching row. ................................................................................ 3853 3854 pIdxKey = sqlite3VdbeAllocUnpackedRecord( 3854 3855 pC->pKeyInfo, aTempRec, sizeof(aTempRec), &pFree 3855 3856 ); 3856 3857 if( pIdxKey==0 ) goto no_mem; 3857 3858 assert( pIn3->flags & MEM_Blob ); 3858 3859 ExpandBlob(pIn3); 3859 3860 sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey); 3861 + if( pOp->p4.i<0 ){ 3862 + pIdxKey->nField = pOp->p4.i * -1; 3863 + } 3860 3864 } 3861 3865 pIdxKey->default_rc = 0; 3862 3866 if( pOp->opcode==OP_NoConflict ){ 3863 3867 /* For the OP_NoConflict opcode, take the jump if any of the 3864 3868 ** input fields are NULL, since any key with a NULL will not 3865 3869 ** conflict */ 3866 3870 for(ii=0; ii<pIdxKey->nField; ii++){ ................................................................................ 3867 3871 if( pIdxKey->aMem[ii].flags & MEM_Null ){ 3868 3872 pc = pOp->p2 - 1; VdbeBranchTaken(1,2); 3869 3873 break; 3870 3874 } 3871 3875 } 3872 3876 } 3873 3877 rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, pIdxKey, 0, 0, &res); 3874 - if( pOp->p4.i==0 ){ 3878 + if( pOp->p4.i<=0 ){ 3875 3879 sqlite3DbFree(db, pFree); 3876 3880 } 3877 3881 if( rc!=SQLITE_OK ){ 3878 3882 break; 3879 3883 } 3880 3884 pC->seekResult = res; 3881 3885 alreadyExists = (res==0); ................................................................................ 4482 4486 pC->cacheStatus = CACHE_STALE; 4483 4487 if( pC->pCursor ){ 4484 4488 sqlite3BtreeClearCursor(pC->pCursor); 4485 4489 } 4486 4490 break; 4487 4491 } 4488 4492 4489 -/* Opcode: Last P1 P2 * * * 4493 +/* Opcode: Last P1 P2 P3 * * 4490 4494 ** 4491 4495 ** The next use of the Rowid or Column or Prev instruction for P1 4492 4496 ** will refer to the last entry in the database table or index. 4493 4497 ** If the table or index is empty and P2>0, then jump immediately to P2. 4494 4498 ** If P2 is 0 or if the table or index is not empty, fall through 4495 4499 ** to the following instruction. 4496 4500 ** ................................................................................ 4509 4513 pCrsr = pC->pCursor; 4510 4514 res = 0; 4511 4515 assert( pCrsr!=0 ); 4512 4516 rc = sqlite3BtreeLast(pCrsr, &res); 4513 4517 pC->nullRow = (u8)res; 4514 4518 pC->deferredMoveto = 0; 4515 4519 pC->cacheStatus = CACHE_STALE; 4520 + pC->seekResult = pOp->p3; 4516 4521 #ifdef SQLITE_DEBUG 4517 4522 pC->seekOp = OP_Last; 4518 4523 #endif 4519 4524 if( pOp->p2>0 ){ 4520 4525 VdbeBranchTaken(res!=0,2); 4521 4526 if( res ) pc = pOp->p2 - 1; 4522 4527 }
Changes to src/vdbe.h.
209 209 #ifndef SQLITE_OMIT_TRACE 210 210 char *sqlite3VdbeExpandSql(Vdbe*, const char*); 211 211 #endif 212 212 int sqlite3MemCompare(const Mem*, const Mem*, const CollSeq*); 213 213 214 214 void sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,UnpackedRecord*); 215 215 int sqlite3VdbeRecordCompare(int,const void*,UnpackedRecord*); 216 +int sqlite3VdbeRecordCompareWithSkip(int, const void *, UnpackedRecord *, int); 216 217 UnpackedRecord *sqlite3VdbeAllocUnpackedRecord(KeyInfo *, char *, int, char **); 217 218 218 219 typedef int (*RecordCompare)(int,const void*,UnpackedRecord*); 219 220 RecordCompare sqlite3VdbeFindCompare(UnpackedRecord*); 220 221 221 222 #ifndef SQLITE_OMIT_TRIGGER 222 223 void sqlite3VdbeLinkSubProgram(Vdbe *, SubProgram *);
Changes to src/vdbeaux.c.
3581 3581 ** returned. 3582 3582 ** 3583 3583 ** If database corruption is discovered, set pPKey2->errCode to 3584 3584 ** SQLITE_CORRUPT and return 0. If an OOM error is encountered, 3585 3585 ** pPKey2->errCode is set to SQLITE_NOMEM and, if it is not NULL, the 3586 3586 ** malloc-failed flag set on database handle (pPKey2->pKeyInfo->db). 3587 3587 */ 3588 -static int vdbeRecordCompareWithSkip( 3588 +int sqlite3VdbeRecordCompareWithSkip( 3589 3589 int nKey1, const void *pKey1, /* Left key */ 3590 3590 UnpackedRecord *pPKey2, /* Right key */ 3591 3591 int bSkip /* If true, skip the first field */ 3592 3592 ){ 3593 3593 u32 d1; /* Offset into aKey[] of next data element */ 3594 3594 int i; /* Index of next field to compare */ 3595 3595 u32 szHdr1; /* Size of record header in bytes */ ................................................................................ 3767 3767 ); 3768 3768 return pPKey2->default_rc; 3769 3769 } 3770 3770 int sqlite3VdbeRecordCompare( 3771 3771 int nKey1, const void *pKey1, /* Left key */ 3772 3772 UnpackedRecord *pPKey2 /* Right key */ 3773 3773 ){ 3774 - return vdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0); 3774 + return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0); 3775 3775 } 3776 3776 3777 3777 3778 3778 /* 3779 3779 ** This function is an optimized version of sqlite3VdbeRecordCompare() 3780 3780 ** that (a) the first field of pPKey2 is an integer, and (b) the 3781 3781 ** size-of-header varint at the start of (pKey1/nKey1) fits in a single ................................................................................ 3855 3855 if( v>lhs ){ 3856 3856 res = pPKey2->r1; 3857 3857 }else if( v<lhs ){ 3858 3858 res = pPKey2->r2; 3859 3859 }else if( pPKey2->nField>1 ){ 3860 3860 /* The first fields of the two keys are equal. Compare the trailing 3861 3861 ** fields. */ 3862 - res = vdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); 3862 + res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); 3863 3863 }else{ 3864 3864 /* The first fields of the two keys are equal and there are no trailing 3865 3865 ** fields. Return pPKey2->default_rc in this case. */ 3866 3866 res = pPKey2->default_rc; 3867 3867 } 3868 3868 3869 3869 assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) ); ................................................................................ 3903 3903 nCmp = MIN( pPKey2->aMem[0].n, nStr ); 3904 3904 res = memcmp(&aKey1[szHdr], pPKey2->aMem[0].z, nCmp); 3905 3905 3906 3906 if( res==0 ){ 3907 3907 res = nStr - pPKey2->aMem[0].n; 3908 3908 if( res==0 ){ 3909 3909 if( pPKey2->nField>1 ){ 3910 - res = vdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); 3910 + res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); 3911 3911 }else{ 3912 3912 res = pPKey2->default_rc; 3913 3913 } 3914 3914 }else if( res>0 ){ 3915 3915 res = pPKey2->r2; 3916 3916 }else{ 3917 3917 res = pPKey2->r1;
Changes to src/vdbesort.c.
287 287 ** to sqlite3ThreadJoin() is likely to block. Cases that are likely to 288 288 ** block provoke debugging output. 289 289 ** 290 290 ** In both cases, the effects of the main thread seeing (bDone==0) even 291 291 ** after the thread has finished are not dire. So we don't worry about 292 292 ** memory barriers and such here. 293 293 */ 294 +typedef int (*SorterCompare)(SortSubtask*,int*,const void*,int,const void*,int); 294 295 struct SortSubtask { 295 296 SQLiteThread *pThread; /* Background thread, if any */ 296 297 int bDone; /* Set if thread is finished but not joined */ 297 298 VdbeSorter *pSorter; /* Sorter that owns this sub-task */ 298 299 UnpackedRecord *pUnpacked; /* Space to unpack a record */ 299 300 SorterList list; /* List for thread to write to a PMA */ 300 301 int nPMA; /* Number of PMAs currently in file */ 302 + SorterCompare xCompare; /* Compare function to use */ 301 303 SorterFile file; /* Temp file for level-0 PMAs */ 302 304 SorterFile file2; /* Space for other PMAs */ 303 305 }; 306 + 304 307 305 308 /* 306 309 ** Main sorter structure. A single instance of this is allocated for each 307 310 ** sorter cursor created by the VDBE. 308 311 ** 309 312 ** mxKeysize: 310 313 ** As records are added to the sorter by calls to sqlite3VdbeSorterWrite(), ................................................................................ 324 327 SorterList list; /* List of in-memory records */ 325 328 int iMemory; /* Offset of free space in list.aMemory */ 326 329 int nMemory; /* Size of list.aMemory allocation in bytes */ 327 330 u8 bUsePMA; /* True if one or more PMAs created */ 328 331 u8 bUseThreads; /* True to use background threads */ 329 332 u8 iPrev; /* Previous thread used to flush PMA */ 330 333 u8 nTask; /* Size of aTask[] array */ 334 + u8 typeMask; 331 335 SortSubtask aTask[1]; /* One or more subtasks */ 332 336 }; 337 + 338 +#define SORTER_TYPE_INTEGER 0x01 339 +#define SORTER_TYPE_TEXT 0x02 333 340 334 341 /* 335 342 ** An instance of the following object is used to read records out of a 336 343 ** PMA, in sorted order. The next key to be read is cached in nKey/aKey. 337 344 ** aKey might point into aMap or into aBuffer. If neither of those locations 338 345 ** contain a contiguous representation of the key, then aAlloc is allocated 339 346 ** and the key is copied into aAlloc and aKey is made to poitn to aAlloc. ................................................................................ 738 745 739 746 if( rc==SQLITE_OK ){ 740 747 rc = vdbePmaReaderNext(pReadr); 741 748 } 742 749 return rc; 743 750 } 744 751 752 +/* 753 +** A version of vdbeSorterCompare() that assumes that it has already been 754 +** determined that the first field of key1 is equal to the first field of 755 +** key2. 756 +*/ 757 +static int vdbeSorterCompareTail( 758 + SortSubtask *pTask, /* Subtask context (for pKeyInfo) */ 759 + int *pbKey2Cached, /* True if pTask->pUnpacked is pKey2 */ 760 + const void *pKey1, int nKey1, /* Left side of comparison */ 761 + const void *pKey2, int nKey2 /* Right side of comparison */ 762 +){ 763 + UnpackedRecord *r2 = pTask->pUnpacked; 764 + if( *pbKey2Cached==0 ){ 765 + sqlite3VdbeRecordUnpack(pTask->pSorter->pKeyInfo, nKey2, pKey2, r2); 766 + *pbKey2Cached = 1; 767 + } 768 + return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, r2, 1); 769 +} 745 770 746 771 /* 747 772 ** Compare key1 (buffer pKey1, size nKey1 bytes) with key2 (buffer pKey2, 748 773 ** size nKey2 bytes). Use (pTask->pKeyInfo) for the collation sequences 749 774 ** used by the comparison. Return the result of the comparison. 750 775 ** 751 -** Before returning, object (pTask->pUnpacked) is populated with the 752 -** unpacked version of key2. Or, if pKey2 is passed a NULL pointer, then it 753 -** is assumed that the (pTask->pUnpacked) structure already contains the 754 -** unpacked key to use as key2. 776 +** If IN/OUT parameter *pbKey2Cached is true when this function is called, 777 +** it is assumed that (pTask->pUnpacked) contains the unpacked version 778 +** of key2. If it is false, (pTask->pUnpacked) is populated with the unpacked 779 +** version of key2 and *pbKey2Cached set to true before returning. 755 780 ** 756 781 ** If an OOM error is encountered, (pTask->pUnpacked->error_rc) is set 757 782 ** to SQLITE_NOMEM. 758 783 */ 759 784 static int vdbeSorterCompare( 760 785 SortSubtask *pTask, /* Subtask context (for pKeyInfo) */ 786 + int *pbKey2Cached, /* True if pTask->pUnpacked is pKey2 */ 761 787 const void *pKey1, int nKey1, /* Left side of comparison */ 762 788 const void *pKey2, int nKey2 /* Right side of comparison */ 763 789 ){ 764 790 UnpackedRecord *r2 = pTask->pUnpacked; 765 - if( pKey2 ){ 791 + if( !*pbKey2Cached ){ 766 792 sqlite3VdbeRecordUnpack(pTask->pSorter->pKeyInfo, nKey2, pKey2, r2); 793 + *pbKey2Cached = 1; 767 794 } 768 795 return sqlite3VdbeRecordCompare(nKey1, pKey1, r2); 769 796 } 797 + 798 +/* 799 +** A specially optimized version of vdbeSorterCompare() that assumes that 800 +** the first field of each key is a TEXT value and that the collation 801 +** sequence to compare them with is BINARY. 802 +*/ 803 +static int vdbeSorterCompareText( 804 + SortSubtask *pTask, /* Subtask context (for pKeyInfo) */ 805 + int *pbKey2Cached, /* True if pTask->pUnpacked is pKey2 */ 806 + const void *pKey1, int nKey1, /* Left side of comparison */ 807 + const void *pKey2, int nKey2 /* Right side of comparison */ 808 +){ 809 + const u8 * const p1 = (const u8 * const)pKey1; 810 + const u8 * const p2 = (const u8 * const)pKey2; 811 + const u8 * const v1 = &p1[ p1[0] ]; /* Pointer to value 1 */ 812 + const u8 * const v2 = &p2[ p2[0] ]; /* Pointer to value 2 */ 813 + 814 + int n1; 815 + int n2; 816 + int res; 817 + 818 + getVarint32(&p1[1], n1); n1 = (n1 - 13) / 2; 819 + getVarint32(&p2[1], n2); n2 = (n2 - 13) / 2; 820 + res = memcmp(v1, v2, MIN(n1, n2)); 821 + if( res==0 ){ 822 + res = n1 - n2; 823 + } 824 + 825 + if( res==0 ){ 826 + if( pTask->pSorter->pKeyInfo->nField>1 ){ 827 + res = vdbeSorterCompareTail( 828 + pTask, pbKey2Cached, pKey1, nKey1, pKey2, nKey2 829 + ); 830 + } 831 + }else{ 832 + if( pTask->pSorter->pKeyInfo->aSortOrder[0] ){ 833 + res = res * -1; 834 + } 835 + } 836 + 837 + return res; 838 +} 839 + 840 +/* 841 +** A specially optimized version of vdbeSorterCompare() that assumes that 842 +** the first field of each key is an INTEGER value. 843 +*/ 844 +static int vdbeSorterCompareInt( 845 + SortSubtask *pTask, /* Subtask context (for pKeyInfo) */ 846 + int *pbKey2Cached, /* True if pTask->pUnpacked is pKey2 */ 847 + const void *pKey1, int nKey1, /* Left side of comparison */ 848 + const void *pKey2, int nKey2 /* Right side of comparison */ 849 +){ 850 + const u8 * const p1 = (const u8 * const)pKey1; 851 + const u8 * const p2 = (const u8 * const)pKey2; 852 + const int s1 = p1[1]; /* Left hand serial type */ 853 + const int s2 = p2[1]; /* Right hand serial type */ 854 + const u8 * const v1 = &p1[ p1[0] ]; /* Pointer to value 1 */ 855 + const u8 * const v2 = &p2[ p2[0] ]; /* Pointer to value 2 */ 856 + int res; /* Return value */ 857 + 858 + assert( (s1>0 && s1<7) || s1==8 || s1==9 ); 859 + assert( (s2>0 && s2<7) || s2==8 || s2==9 ); 860 + 861 + if( s1>7 && s2>7 ){ 862 + res = s1 - s2; 863 + }else{ 864 + if( s1==s2 ){ 865 + if( (*v1 ^ *v2) & 0x80 ){ 866 + /* The two values have different signs */ 867 + res = (*v1 & 0x80) ? -1 : +1; 868 + }else{ 869 + /* The two values have the same sign. Compare using memcmp(). */ 870 + static const u8 aLen[] = {0, 1, 2, 3, 4, 6, 8 }; 871 + int i; 872 + res = 0; 873 + for(i=0; i<aLen[s1]; i++){ 874 + if( (res = v1[i] - v2[i]) ) break; 875 + } 876 + } 877 + }else{ 878 + if( s2>7 ){ 879 + res = +1; 880 + }else if( s1>7 ){ 881 + res = -1; 882 + }else{ 883 + res = s1 - s2; 884 + } 885 + 886 + if( res>0 ){ 887 + if( *v1 & 0x80 ) res = -1; 888 + }else if( res<0 ){ 889 + if( *v2 & 0x80 ) res = +1; 890 + } 891 + } 892 + } 893 + 894 + if( res==0 ){ 895 + if( pTask->pSorter->pKeyInfo->nField>1 ){ 896 + res = vdbeSorterCompareTail( 897 + pTask, pbKey2Cached, pKey1, nKey1, pKey2, nKey2 898 + ); 899 + } 900 + }else if( pTask->pSorter->pKeyInfo->aSortOrder[0] ){ 901 + res = res * -1; 902 + } 903 + 904 + return res; 905 +} 770 906 771 907 /* 772 908 ** Initialize the temporary index cursor just opened as a sorter cursor. 773 909 ** 774 910 ** Usually, the sorter module uses the value of (pCsr->pKeyInfo->nField) 775 911 ** to determine the number of fields that should be compared from the 776 912 ** records being sorted. However, if the value passed as argument nField ................................................................................ 831 967 pCsr->pSorter = pSorter; 832 968 if( pSorter==0 ){ 833 969 rc = SQLITE_NOMEM; 834 970 }else{ 835 971 pSorter->pKeyInfo = pKeyInfo = (KeyInfo*)((u8*)pSorter + sz); 836 972 memcpy(pKeyInfo, pCsr->pKeyInfo, szKeyInfo); 837 973 pKeyInfo->db = 0; 838 - if( nField && nWorker==0 ) pKeyInfo->nField = nField; 974 + if( nField && nWorker==0 ){ 975 + pKeyInfo->nXField += (pKeyInfo->nField - nField); 976 + pKeyInfo->nField = nField; 977 + } 839 978 pSorter->pgsz = pgsz = sqlite3BtreeGetPageSize(db->aDb[0].pBt); 840 979 pSorter->nTask = nWorker + 1; 980 + pSorter->iPrev = nWorker-1; 841 981 pSorter->bUseThreads = (pSorter->nTask>1); 842 982 pSorter->db = db; 843 983 for(i=0; i<pSorter->nTask; i++){ 844 984 SortSubtask *pTask = &pSorter->aTask[i]; 845 985 pTask->pSorter = pSorter; 846 986 } 847 987 ................................................................................ 859 999 if( sqlite3GlobalConfig.pScratch==0 ){ 860 1000 assert( pSorter->iMemory==0 ); 861 1001 pSorter->nMemory = pgsz; 862 1002 pSorter->list.aMemory = (u8*)sqlite3Malloc(pgsz); 863 1003 if( !pSorter->list.aMemory ) rc = SQLITE_NOMEM; 864 1004 } 865 1005 } 1006 + 1007 + if( (pKeyInfo->nField+pKeyInfo->nXField)<13 1008 + && (pKeyInfo->aColl[0]==0 || pKeyInfo->aColl[0]==db->pDfltColl) 1009 + ){ 1010 + pSorter->typeMask = SORTER_TYPE_INTEGER | SORTER_TYPE_TEXT; 1011 + } 866 1012 } 867 1013 868 1014 return rc; 869 1015 } 870 1016 #undef nWorker /* Defined at the top of this function */ 871 1017 872 1018 /* ................................................................................ 1195 1341 SortSubtask *pTask, /* Calling thread context */ 1196 1342 SorterRecord *p1, /* First list to merge */ 1197 1343 SorterRecord *p2, /* Second list to merge */ 1198 1344 SorterRecord **ppOut /* OUT: Head of merged list */ 1199 1345 ){ 1200 1346 SorterRecord *pFinal = 0; 1201 1347 SorterRecord **pp = &pFinal; 1202 - void *pVal2 = p2 ? SRVAL(p2) : 0; 1348 + int bCached = 0; 1203 1349 1204 1350 while( p1 && p2 ){ 1205 1351 int res; 1206 - res = vdbeSorterCompare(pTask, SRVAL(p1), p1->nVal, pVal2, p2->nVal); 1352 + res = pTask->xCompare( 1353 + pTask, &bCached, SRVAL(p1), p1->nVal, SRVAL(p2), p2->nVal 1354 + ); 1355 + 1207 1356 if( res<=0 ){ 1208 1357 *pp = p1; 1209 1358 pp = &p1->u.pNext; 1210 1359 p1 = p1->u.pNext; 1211 - pVal2 = 0; 1212 1360 }else{ 1213 1361 *pp = p2; 1214 - pp = &p2->u.pNext; 1362 + pp = &p2->u.pNext; 1215 1363 p2 = p2->u.pNext; 1216 - if( p2==0 ) break; 1217 - pVal2 = SRVAL(p2); 1364 + bCached = 0; 1218 1365 } 1219 1366 } 1220 1367 *pp = p1 ? p1 : p2; 1221 1368 *ppOut = pFinal; 1222 1369 } 1370 + 1371 +/* 1372 +** Return the SorterCompare function to compare values collected by the 1373 +** sorter object passed as the only argument. 1374 +*/ 1375 +static SorterCompare vdbeSorterGetCompare(VdbeSorter *p){ 1376 + if( p->typeMask==SORTER_TYPE_INTEGER ){ 1377 + return vdbeSorterCompareInt; 1378 + }else if( p->typeMask==SORTER_TYPE_TEXT ){ 1379 + return vdbeSorterCompareText; 1380 + } 1381 + return vdbeSorterCompare; 1382 +} 1223 1383 1224 1384 /* 1225 1385 ** Sort the linked list of records headed at pTask->pList. Return 1226 1386 ** SQLITE_OK if successful, or an SQLite error code (i.e. SQLITE_NOMEM) if 1227 1387 ** an error occurs. 1228 1388 */ 1229 1389 static int vdbeSorterSort(SortSubtask *pTask, SorterList *pList){ ................................................................................ 1231 1391 SorterRecord **aSlot; 1232 1392 SorterRecord *p; 1233 1393 int rc; 1234 1394 1235 1395 rc = vdbeSortAllocUnpacked(pTask); 1236 1396 if( rc!=SQLITE_OK ) return rc; 1237 1397 1398 + p = pList->pList; 1399 + pTask->xCompare = vdbeSorterGetCompare(pTask->pSorter); 1400 + 1238 1401 aSlot = (SorterRecord **)sqlite3MallocZero(64 * sizeof(SorterRecord *)); 1239 1402 if( !aSlot ){ 1240 1403 return SQLITE_NOMEM; 1241 1404 } 1242 1405 1243 - p = pList->pList; 1244 1406 while( p ){ 1245 1407 SorterRecord *pNext; 1246 1408 if( pList->aMemory ){ 1247 1409 if( (u8*)p==pList->aMemory ){ 1248 1410 pNext = 0; 1249 1411 }else{ 1250 1412 assert( p->u.iNext<sqlite3MallocSize(pList->aMemory) ); ................................................................................ 1450 1612 rc = vdbePmaReaderNext(&pMerger->aReadr[iPrev]); 1451 1613 1452 1614 /* Update contents of aTree[] */ 1453 1615 if( rc==SQLITE_OK ){ 1454 1616 int i; /* Index of aTree[] to recalculate */ 1455 1617 PmaReader *pReadr1; /* First PmaReader to compare */ 1456 1618 PmaReader *pReadr2; /* Second PmaReader to compare */ 1457 - u8 *pKey2; /* To pReadr2->aKey, or 0 if record cached */ 1619 + int bCached = 0; 1458 1620 1459 1621 /* Find the first two PmaReaders to compare. The one that was just 1460 1622 ** advanced (iPrev) and the one next to it in the array. */ 1461 1623 pReadr1 = &pMerger->aReadr[(iPrev & 0xFFFE)]; 1462 1624 pReadr2 = &pMerger->aReadr[(iPrev | 0x0001)]; 1463 - pKey2 = pReadr2->aKey; 1464 1625 1465 1626 for(i=(pMerger->nTree+iPrev)/2; i>0; i=i/2){ 1466 1627 /* Compare pReadr1 and pReadr2. Store the result in variable iRes. */ 1467 1628 int iRes; 1468 1629 if( pReadr1->pFd==0 ){ 1469 1630 iRes = +1; 1470 1631 }else if( pReadr2->pFd==0 ){ 1471 1632 iRes = -1; 1472 1633 }else{ 1473 - iRes = vdbeSorterCompare(pTask, 1474 - pReadr1->aKey, pReadr1->nKey, pKey2, pReadr2->nKey 1634 + iRes = pTask->xCompare(pTask, &bCached, 1635 + pReadr1->aKey, pReadr1->nKey, pReadr2->aKey, pReadr2->nKey 1475 1636 ); 1476 1637 } 1477 1638 1478 1639 /* If pReadr1 contained the smaller value, set aTree[i] to its index. 1479 1640 ** Then set pReadr2 to the next PmaReader to compare to pReadr1. In this 1480 1641 ** case there is no cache of pReadr2 in pTask->pUnpacked, so set 1481 1642 ** pKey2 to point to the record belonging to pReadr2. ................................................................................ 1489 1650 ** If the two values were equal, then the value from the oldest 1490 1651 ** PMA should be considered smaller. The VdbeSorter.aReadr[] array 1491 1652 ** is sorted from oldest to newest, so pReadr1 contains older values 1492 1653 ** than pReadr2 iff (pReadr1<pReadr2). */ 1493 1654 if( iRes<0 || (iRes==0 && pReadr1<pReadr2) ){ 1494 1655 pMerger->aTree[i] = (int)(pReadr1 - pMerger->aReadr); 1495 1656 pReadr2 = &pMerger->aReadr[ pMerger->aTree[i ^ 0x0001] ]; 1496 - pKey2 = pReadr2->aKey; 1657 + bCached = 0; 1497 1658 }else{ 1498 - if( pReadr1->pFd ) pKey2 = 0; 1659 + if( pReadr1->pFd ) bCached = 0; 1499 1660 pMerger->aTree[i] = (int)(pReadr2 - pMerger->aReadr); 1500 1661 pReadr1 = &pMerger->aReadr[ pMerger->aTree[i ^ 0x0001] ]; 1501 1662 } 1502 1663 } 1503 1664 *pbEof = (pMerger->aReadr[pMerger->aTree[1]].pFd==0); 1504 1665 } 1505 1666 ................................................................................ 1598 1759 VdbeSorter *pSorter = pCsr->pSorter; 1599 1760 int rc = SQLITE_OK; /* Return Code */ 1600 1761 SorterRecord *pNew; /* New list element */ 1601 1762 1602 1763 int bFlush; /* True to flush contents of memory to PMA */ 1603 1764 int nReq; /* Bytes of memory required */ 1604 1765 int nPMA; /* Bytes of PMA space required */ 1766 + int t; /* serial type of first record field */ 1767 + 1768 + getVarint32((const u8*)&pVal->z[1], t); 1769 + if( t>0 && t<10 && t!=7 ){ 1770 + pSorter->typeMask &= SORTER_TYPE_INTEGER; 1771 + }else if( t>10 && (t & 0x01) ){ 1772 + pSorter->typeMask &= SORTER_TYPE_TEXT; 1773 + }else{ 1774 + pSorter->typeMask = 0; 1775 + } 1605 1776 1606 1777 assert( pSorter ); 1607 1778 1608 1779 /* Figure out whether or not the current contents of memory should be 1609 1780 ** flushed to a PMA before continuing. If so, do so. 1610 1781 ** 1611 1782 ** If using the single large allocation mode (pSorter->aMemory!=0), then ................................................................................ 1863 2034 p2 = &pMerger->aReadr[i2]; 1864 2035 1865 2036 if( p1->pFd==0 ){ 1866 2037 iRes = i2; 1867 2038 }else if( p2->pFd==0 ){ 1868 2039 iRes = i1; 1869 2040 }else{ 2041 + SortSubtask *pTask = pMerger->pTask; 2042 + int bCached = 0; 1870 2043 int res; 1871 - assert( pMerger->pTask->pUnpacked!=0 ); /* from vdbeSortSubtaskMain() */ 1872 - res = vdbeSorterCompare( 1873 - pMerger->pTask, p1->aKey, p1->nKey, p2->aKey, p2->nKey 2044 + assert( pTask->pUnpacked!=0 ); /* from vdbeSortSubtaskMain() */ 2045 + res = pTask->xCompare( 2046 + pTask, &bCached, p1->aKey, p1->nKey, p2->aKey, p2->nKey 1874 2047 ); 1875 2048 if( res<=0 ){ 1876 2049 iRes = i1; 1877 2050 }else{ 1878 2051 iRes = i2; 1879 2052 } 1880 2053 } ................................................................................ 2284 2457 */ 2285 2458 static int vdbeSorterSetupMerge(VdbeSorter *pSorter){ 2286 2459 int rc; /* Return code */ 2287 2460 SortSubtask *pTask0 = &pSorter->aTask[0]; 2288 2461 MergeEngine *pMain = 0; 2289 2462 #if SQLITE_MAX_WORKER_THREADS 2290 2463 sqlite3 *db = pTask0->pSorter->db; 2464 + int i; 2465 + SorterCompare xCompare = vdbeSorterGetCompare(pSorter); 2466 + for(i=0; i<pSorter->nTask; i++){ 2467 + pSorter->aTask[i].xCompare = xCompare; 2468 + } 2291 2469 #endif 2292 2470 2293 2471 rc = vdbeSorterMergeTreeBuild(pSorter, &pMain); 2294 2472 if( rc==SQLITE_OK ){ 2295 2473 #if SQLITE_MAX_WORKER_THREADS 2296 2474 assert( pSorter->bUseThreads==0 || pSorter->nTask>1 ); 2297 2475 if( pSorter->bUseThreads ){
Changes to test/e_vacuum.test.
25 25 26 26 db transaction { 27 27 execsql { PRAGMA page_size = 1024; } 28 28 execsql $sql 29 29 execsql { 30 30 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); 31 31 INSERT INTO t1 VALUES(1, randomblob(400)); 32 - INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1; 33 - INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1; 34 - INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1; 35 - INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1; 36 - INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1; 37 - INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1; 38 - INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1; 32 + INSERT OR FAIL INTO t1 SELECT a+1, randomblob(400) FROM t1; 33 + INSERT OR FAIL INTO t1 SELECT a+2, randomblob(400) FROM t1; 34 + INSERT OR FAIL INTO t1 SELECT a+4, randomblob(400) FROM t1; 35 + INSERT OR FAIL INTO t1 SELECT a+8, randomblob(400) FROM t1; 36 + INSERT OR FAIL INTO t1 SELECT a+16, randomblob(400) FROM t1; 37 + INSERT OR FAIL INTO t1 SELECT a+32, randomblob(400) FROM t1; 38 + INSERT OR FAIL INTO t1 SELECT a+64, randomblob(400) FROM t1; 39 39 40 40 CREATE TABLE t2(a PRIMARY KEY, b UNIQUE); 41 - INSERT INTO t2 SELECT * FROM t1; 41 + INSERT OR FAIL INTO t2 SELECT * FROM t1; 42 42 } 43 43 } 44 44 45 45 return [expr {[file size test.db] / 1024}] 46 46 } 47 47 48 48 # This proc returns the number of contiguous blocks of pages that make up ................................................................................ 123 123 # less fragmented. 124 124 # 125 125 ifcapable vtab&&compound { 126 126 create_db 127 127 register_dbstat_vtab db 128 128 do_execsql_test e_vacuum-1.2.1 { 129 129 DELETE FROM t1 WHERE a%2; 130 - INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2; 130 + INSERT OR REPLACE INTO t1 SELECT b, a FROM t2 WHERE a%2; 131 131 UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0; 132 132 } {} 133 133 134 134 do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1 135 135 do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1 136 136 do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1 137 137
Added test/insert6.test.
1 +# 2015 March 20 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 +# The tests in this file ensure that sorter objects are used by 13 +# "INSERT INTO ... SELECT ..." statements when possible. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 +set testprefix insert6 19 + 20 +# Return the number of OP_SorterOpen instructions in the SQL passed as 21 +# the only argument if it is compiled using connection [db]. 22 +# 23 +proc sorter_count {sql} { 24 + set res 0 25 + db cache flush 26 + db eval "EXPLAIN $sql" x { 27 + if {$x(opcode) == "SorterOpen"} { incr res } 28 + } 29 + return $res 30 +} 31 + 32 + 33 +#------------------------------------------------------------------------- 34 +# Warm body test. This verifies that the simplest case works for both 35 +# regular and WITHOUT ROWID tables. 36 +# 37 +do_execsql_test 1.1 { 38 + CREATE TABLE t2(x UNIQUE ON CONFLICT IGNORE, y, z); 39 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<99 ) 40 + INSERT INTO t2 SELECT abs(random()), abs(random()), abs(random()) FROM cnt; 41 +} 42 + 43 +foreach {tn nSort schema} { 44 + 1 3 { CREATE TABLE t1(a, b, c) } 45 + 2 4 { CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID } 46 +} { 47 + 48 + do_test 1.$tn.1 { 49 + execsql { DROP TABLE IF EXISTS t1 } 50 + execsql $schema 51 + } {} 52 + 53 + do_execsql_test 1.$tn.2 { 54 + CREATE INDEX t1a ON t1(a); 55 + CREATE INDEX t1b ON t1(b); 56 + CREATE INDEX t1c ON t1(c); 57 + } 58 + 59 + do_execsql_test 1.$tn.3 { 60 + INSERT INTO t1 SELECT x, y, z FROM t2; 61 + PRAGMA integrity_check; 62 + SELECT count(*) FROM t1; 63 + } {ok 100} 64 + 65 + do_execsql_test 1.$tn.4 { 66 + INSERT INTO t1 SELECT -x, y, z FROM t2; 67 + PRAGMA integrity_check; 68 + } {ok} 69 + 70 + do_execsql_test 1.$tn.5 { 71 + SELECT count(*) FROM t1; 72 + } {200} 73 + 74 + do_test 1.$tn.6 { 75 + sorter_count { INSERT INTO t1 SELECT * FROM t2 } 76 + } $nSort 77 +} 78 + 79 +#------------------------------------------------------------------------- 80 +# The following test cases check that the sorters are disabled if any 81 +# of the following are true: 82 +# 83 +# 2.1: The statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE". 84 +# 85 +# 2.2: The statement does not explicitly specify a conflict mode and 86 +# there are one or more PRIMARY KEY or UNIQUE constraints with 87 +# "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling 88 +# mode. 89 +# 90 +# 2.3: There are one or more INSERT triggers on the target table. 91 +# 92 +# 2.4: The target table is the parent or child of an FK constraint. 93 +# 94 + 95 +do_execsql_test 2.1.1 { 96 + CREATE TABLE x1(a, b, c); 97 + CREATE INDEX x1a ON x1(a); 98 + 99 + CREATE TABLE x2(a, b, c); 100 + CREATE UNIQUE INDEX x2a ON x2(a); 101 + 102 + CREATE TABLE x3(a PRIMARY KEY, b, c); 103 + CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID; 104 +} 105 + 106 +do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 0 107 +do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0 108 +do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0 109 +do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0 110 + 111 +do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 0 112 +do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0 113 +do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0 114 +do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0 115 + 116 +do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 0 117 +do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0 118 +do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0 119 +do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0 120 + 121 +do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1 122 +do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1 123 +do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1 124 +do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1 125 + 126 +do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1 127 +do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1 128 +do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1 129 +do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1 130 + 131 + 132 +foreach {tn scount schema} { 133 + 2.1 0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) } 134 + 2.2 0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) } 135 + 2.3 0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) } 136 + 2.4 0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) } 137 + 2.5 0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) } 138 + 2.6 0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) } 139 + 2.7 0 { 140 + CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID 141 + } 142 + 2.8 0 { 143 + CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID 144 + } 145 + 2.9 0 { 146 + CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID 147 + } 148 + 149 + 3.1 1 { 150 + CREATE TABLE t1(a, b, c); 151 + CREATE INDEX i1 ON t1(a); 152 + } 153 + 3.2 0 { 154 + CREATE TABLE t1(a, b, c); 155 + CREATE INDEX i1 ON t1(a); 156 + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END; 157 + } 158 + 3.3 0 { 159 + CREATE TABLE t1(a, b, c); 160 + CREATE INDEX i1 ON t1(a); 161 + CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END; 162 + } 163 + 164 + 4.1 2 { 165 + CREATE TABLE t1(a PRIMARY KEY, b, c); 166 + CREATE INDEX i1 ON t1(a); 167 + CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); 168 + PRAGMA foreign_keys = 0; 169 + } 170 + 4.2 0 { 171 + CREATE TABLE t1(a PRIMARY KEY, b, c); 172 + CREATE INDEX i1 ON t1(a); 173 + CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); 174 + PRAGMA foreign_keys = 1; 175 + } 176 + 177 + 4.3 1 { 178 + CREATE TABLE p1(x, y UNIQUE); 179 + CREATE TABLE t1(a, b, c REFERENCES p1(y)); 180 + CREATE INDEX i1 ON t1(a); 181 + PRAGMA foreign_keys = 0; 182 + } 183 + 4.4 0 { 184 + CREATE TABLE p1(x, y UNIQUE); 185 + CREATE TABLE t1(a, b, c REFERENCES p1(y)); 186 + CREATE INDEX i1 ON t1(a); 187 + PRAGMA foreign_keys = 1; 188 + } 189 + 190 +} { 191 + execsql { 192 + DROP TABLE IF EXISTS t1; 193 + DROP TABLE IF EXISTS c1; 194 + DROP TABLE IF EXISTS p1; 195 + } 196 + 197 + do_test 2.2.$tn { 198 + execsql $schema 199 + sorter_count { INSERT INTO t1 SELECT * FROM t2 } 200 + } $scount 201 +} 202 + 203 +#------------------------------------------------------------------------- 204 +# Test that if a UNIQUE constraint is violated and the on conflict mode 205 +# is either ABORT or ROLLBACK, the conflict is handled correctly. 206 +# 207 +# 3.2: Check that conflicts are actually detected. 208 +# 3.3: Check that OR ROLLBACK really does rollback the transaction. 209 +# 3.4: Check that OR ABORT does not. 210 +# 211 +do_execsql_test 3.1 { 212 + DROP TABLE IF EXISTS t1; 213 + CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c)); 214 + INSERT INTO t1 VALUES(1, 2, 3); 215 + INSERT INTO t1 VALUES(4, 5, 6); 216 + INSERT INTO t1 VALUES(7, 8, 9); 217 + CREATE TABLE src(a, b, c); 218 +} 219 + 220 +do_catchsql_test 3.2.1 { 221 + INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); 222 + INSERT INTO t1 SELECT * FROM src; 223 +} {1 {UNIQUE constraint failed: t1.a}} 224 + 225 +do_catchsql_test 3.2.2 { 226 + DELETE FROM src; 227 + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); 228 + INSERT INTO t1 SELECT * FROM src; 229 +} {1 {UNIQUE constraint failed: t1.b, t1.c}} 230 + 231 +do_catchsql_test 3.2.3.1 { 232 + CREATE TABLE t3(a); 233 + CREATE UNIQUE INDEX t3a ON t3(a); 234 + 235 + CREATE TABLE t3src(a); 236 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 ) 237 + INSERT INTO t3src SELECT 'abc' FROM cnt; 238 +} {0 {}} 239 + 240 +# execsql { PRAGMA vdbe_trace = 1 } 241 +do_catchsql_test 3.2.3.2 { 242 + INSERT INTO t3 SELECT * FROM t3src; 243 +} {1 {UNIQUE constraint failed: t3.a}} 244 + 245 +do_catchsql_test 3.3.1 { 246 + DELETE FROM src; 247 + BEGIN; 248 + INSERT INTO src VALUES (10, 11, 12), (7, 13, 14); 249 + INSERT OR ROLLBACK INTO t1 SELECT * FROM src; 250 +} {1 {UNIQUE constraint failed: t1.a}} 251 +do_catchsql_test 3.3.2 { 252 + DELETE FROM src; 253 + BEGIN; 254 + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); 255 + INSERT OR ROLLBACK INTO t1 SELECT * FROM src; 256 +} {1 {UNIQUE constraint failed: t1.b, t1.c}} 257 +do_test 3.3.3 { 258 + sqlite3_get_autocommit db 259 +} 1 260 + 261 +do_catchsql_test 3.4.1 { 262 + DELETE FROM src; 263 + BEGIN; 264 + INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); 265 + INSERT OR ABORT INTO t1 SELECT * FROM src; 266 +} {1 {UNIQUE constraint failed: t1.a}} 267 +do_catchsql_test 3.4.2 { 268 + ROLLBACK; 269 + DELETE FROM src; 270 + BEGIN; 271 + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); 272 + INSERT OR ABORT INTO t1 SELECT * FROM src; 273 +} {1 {UNIQUE constraint failed: t1.b, t1.c}} 274 +do_test 3.4.3 { 275 + sqlite3_get_autocommit db 276 +} 0 277 +do_execsql_test 3.4.4 { ROLLBACK } 278 + 279 +#------------------------------------------------------------------------- 280 +# The following tests - 4.* - check that this optimization is actually 281 +# doing something helpful. They do this by executing a big 282 +# "INSERT INTO SELECT" statement in wal mode with a small pager cache. 283 +# Once with "OR FAIL" (so that the sorters are not used) and once with 284 +# the default "OR ABORT" (so that they are). 285 +# 286 +# If the sorters are doing their job, the wal file generated by the 287 +# "OR ABORT" case should be much smaller than the "OR FAIL" trial. 288 +# 289 + 290 +proc odd_collate {lhs rhs} { 291 + string compare [string range $lhs 6 end] [string range $rhs 6 end] 292 +} 293 + 294 +proc do_insert6_4_test {tn sql} { 295 + 296 + reset_db 297 + db collate odd_collate odd_collate 298 + execsql $sql 299 + db_save_and_close 300 + 301 + foreach {tn2 ::onerror ::var} { 302 + 1 "OR ABORT" ::sz1 303 + 2 "OR FAIL" ::sz2 304 + } { 305 + do_test $tn.$tn2 { 306 + db_restore_and_reopen 307 + db collate odd_collate odd_collate 308 + execsql " 309 + PRAGMA journal_mode = wal; 310 + PRAGMA cache_size = 5; 311 + PRAGMA wal_autocheckpoint = 0; 312 + INSERT $onerror INTO t1 SELECT * FROM src; 313 + " 314 + set $var [file size test.db-wal] 315 + db close 316 + } {} 317 + } 318 + 319 + do_test $tn.3.($::sz1<$::sz2) { 320 + expr {$sz1 < ($sz2/2)} 321 + } 1 322 + 323 + sqlite3 db test.db 324 + db collate odd_collate odd_collate 325 + integrity_check $tn.4 326 +} 327 + 328 +do_insert6_4_test 4.1 { 329 + CREATE TABLE t1(a, b, c); 330 + CREATE UNIQUE INDEX t1a ON t1(a); 331 + CREATE UNIQUE INDEX t1bc ON t1(b, c); 332 + 333 + CREATE TABLE src(x, y, z); 334 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 335 + INSERT INTO src 336 + SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt; 337 +} 338 + 339 +do_insert6_4_test 4.2 { 340 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x); 341 + CREATE UNIQUE INDEX t1b ON t1(b); 342 + CREATE INDEX t1x1 ON t1(x); 343 + CREATE INDEX t1x2 ON t1(x); 344 + CREATE INDEX t1x3 ON t1(x); 345 + CREATE INDEX t1x4 ON t1(x); 346 + 347 + CREATE TABLE src(a, b, x); 348 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 349 + INSERT INTO src 350 + SELECT random(), x, zeroblob(50) FROM cnt; 351 +} 352 + 353 +do_insert6_4_test 4.3 { 354 + CREATE TABLE t1(a, b, c); 355 + CREATE UNIQUE INDEX t1ab ON t1(a, b); 356 + CREATE UNIQUE INDEX t1ac ON t1(a, c); 357 + 358 + CREATE TABLE src(a, b, c); 359 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 360 + INSERT INTO src 361 + SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt; 362 +} 363 + 364 +db collate odd_collate odd_collate 365 +do_insert6_4_test 4.5 { 366 + CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate); 367 + CREATE UNIQUE INDEX t1t ON t1(t); 368 + CREATE UNIQUE INDEX t1v ON t1(v); 369 + 370 + CREATE TABLE src(t, v); 371 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 372 + INSERT INTO src 373 + SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt; 374 +} 375 + 376 +db collate odd_collate odd_collate 377 +do_insert6_4_test 4.6 { 378 + CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID; 379 + CREATE TABLE src(t); 380 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 381 + INSERT INTO src 382 + SELECT hex(randomblob(50)) FROM cnt; 383 +} 384 + 385 +#------------------------------------------------------------------------- 386 +# At one point the sorters were used for INSERT statements that specify 387 +# "OR FAIL", "REPLACE" or "IGNORE" if there were no PRIMARY KEY or 388 +# UNIQUE indexes. This is incorrect, as all such tables have an implicit 389 +# IPK column. So using the sorters can cause corruption. This test checks 390 +# that that problem no longer exists. 391 +# 392 +reset_db 393 +do_execsql_test 5.1 { 394 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 395 + CREATE INDEX t1b ON t1(b); 396 + INSERT INTO t1 VALUES(1, 2, 3); 397 + INSERT INTO t1 VALUES(4, 5, 6); 398 +} 399 + 400 +do_catchsql_test 5.2 { 401 + INSERT OR FAIL INTO t1 402 + SELECT 2, 'x', 'x' UNION ALL SELECT 3, 'x', 'x' UNION ALL SELECT 4, 'x', 'x'; 403 +} {1 {UNIQUE constraint failed: t1.a}} 404 + 405 +integrity_check 5.3 406 + 407 + 408 +finish_test 409 +
Changes to test/stat.test.
72 72 DROP TABLE t1; 73 73 } 74 74 } {} 75 75 76 76 do_execsql_test stat-2.1 { 77 77 CREATE TABLE t3(a PRIMARY KEY, b); 78 78 INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222)); 79 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 79 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 80 + ORDER BY rowid; 81 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 80 82 ORDER BY rowid; 81 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 83 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 82 84 ORDER BY rowid; 83 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 85 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 84 86 ORDER BY rowid; 85 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 86 - ORDER BY rowid; 87 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 87 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 88 88 ORDER BY rowid; 89 89 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload 90 90 FROM stat WHERE name != 'sqlite_master'; 91 91 } [list \ 92 92 sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \ 93 93 sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \ 94 94 sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \
Changes to test/wal.test.
691 691 execsql { INSERT INTO t1 VALUES( blob(900) ) } 692 692 list [expr [file size test.db]/1024] [file size test.db-wal] 693 693 } [list 3 [wal_file_size 4 1024]] 694 694 695 695 do_test wal-11.4 { 696 696 execsql { 697 697 BEGIN; 698 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 2 699 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 4 700 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 8 701 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 16 698 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 2 699 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 4 700 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 8 701 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 16 702 702 } 703 703 list [expr [file size test.db]/1024] [file size test.db-wal] 704 704 } [list 3 [wal_file_size 32 1024]] 705 705 do_test wal-11.5 { 706 706 execsql { 707 707 SELECT count(*) FROM t1; 708 708 PRAGMA integrity_check; ................................................................................ 730 730 set nWal 39 731 731 if {[permutation]!="mmap"} {set nWal 37} 732 732 ifcapable !mmap {set nWal 37} 733 733 do_test wal-11.10 { 734 734 execsql { 735 735 PRAGMA cache_size = 10; 736 736 BEGIN; 737 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 32 737 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 32 738 738 SELECT count(*) FROM t1; 739 739 } 740 740 list [expr [file size test.db]/1024] [file size test.db-wal] 741 741 } [list 37 [wal_file_size $nWal 1024]] 742 742 do_test wal-11.11 { 743 743 execsql { 744 744 SELECT count(*) FROM t1;