/ Check-in [0a7f2051]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Where possible insert the set of new keys for each index in sorted order within "INSERT INTO ... SELECT" statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | insert-select-opt
Files: files | file ages | folders
SHA1: 0a7f2051b2cc9b4e060fd6081587502124e16c9e
User & Date: dan 2015-03-20 20:30:26
Context
2015-03-21
07:03
Disable the sorter optimization used by INSERT INTO SELECT statements if the statement explicitly specifies REPLACE, IGNORE or FAIL conflict handling. check-in: d4215942 user: dan tags: insert-select-opt
2015-03-20
20:30
Where possible insert the set of new keys for each index in sorted order within "INSERT INTO ... SELECT" statements. check-in: 0a7f2051 user: dan tags: insert-select-opt
08:43
Fix a problem causing collation sequence names to be dequoted multiple times under some circumstances. check-in: eddc05e7 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

334
335
336
337
338
339
340

















341
342
343
344
345
346
347
...
447
448
449
450
451
452
453

454
455
456
457
458
459
460
...
751
752
753
754
755
756
757


































758
759
760
761
762
763
764
...
952
953
954
955
956
957
958


959
960
961





962
963
964

965
966
967
968
969
970
971
...
987
988
989
990
991
992
993

























994
995
996
997
998
999
1000
....
1129
1130
1131
1132
1133
1134
1135

1136
1137
1138
1139
1140
1141
1142
....
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
....
1534
1535
1536
1537
1538
1539
1540

1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553


1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  int onError,          /* How to handle constraint errors */
  int iDbDest           /* The database of pDest */
);


















/*
** This routine is called to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
**    insert into TABLE (IDLIST) select
**
................................................................................
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int nColumn;          /* Number of columns in the data */
  int nHidden = 0;      /* Number of hidden columns if TABLE is virtual */
  int iDataCur = 0;     /* VDBE cursor that is the main data repository */
  int iIdxCur = 0;      /* First index cursor */

  int ipkColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
  int endOfLoop;        /* Label for the end of the insertion loop */
  int srcTab = 0;       /* Data comes from this temporary cursor if >=0 */
  int addrInsTop = 0;   /* Jump to label "D" */
  int addrCont = 0;     /* Top of insert loop. Label "C" in templates 3 and 4 */
  SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
  int iDb;              /* Index of database holding TABLE */
................................................................................
    aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1));
    if( aRegIdx==0 ){
      goto insert_cleanup;
    }
    for(i=0; i<nIdx; i++){
      aRegIdx[i] = ++pParse->nMem;
    }


































  }

  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the
    ** following pseudocode (template 4):
    **
................................................................................
      sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
      sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
      sqlite3MayAbort(pParse);
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */


      sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace
      );





      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
      sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
                               regIns, aRegIdx, 0, appendFlag, isReplace==0);

    }
  }

  /* Update the count of rows that are inserted
  */
  if( (db->flags & SQLITE_CountRows)!=0 ){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
................................................................................
    sqlite3VdbeAddOp1(v, OP_Close, srcTab);
  }else if( pSelect ){
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont);
    sqlite3VdbeJumpHere(v, addrInsTop);
  }

  if( !IsVirtual(pTab) && !isView ){

























    /* Close all tables opened */
    if( iDataCur<iIdxCur ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur);
    for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqlite3VdbeAddOp1(v, OP_Close, idx+iIdxCur);
    }
  }

................................................................................
  int iDataCur,        /* Canonical data cursor (main table or PK index) */
  int iIdxCur,         /* First index cursor */
  int regNewData,      /* First register in a range holding values to insert */
  int regOldData,      /* Previous content.  0 for INSERTs */
  u8 pkChng,           /* Non-zero if the rowid or PRIMARY KEY changed */
  u8 overrideError,    /* Override onError to this if not OE_Default */
  int ignoreDest,      /* Jump to this label on an OE_Ignore resolution */

  int *pbMayReplace    /* OUT: Set to true if constraint may cause a replace */
){
  Vdbe *v;             /* VDBE under constrution */
  Index *pIdx;         /* Pointer to one of the indices */
  Index *pPk = 0;      /* The PRIMARY KEY index */
  sqlite3 *db;         /* Database connection */
  int i;               /* loop counter */
................................................................................
    ** logic below can all be skipped. */
    if( isUpdate && pPk==pIdx && pkChng==0 ){
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
      continue;
    }

    /* Find out what action to take in case there is a uniqueness conflict */
    onError = pIdx->onError;
    if( onError==OE_None ){ 
      sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
      continue;  /* pIdx is not a UNIQUE index */
    }
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    
    /* Check to see if the new index entry will be unique */
    sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
                         regIdx, pIdx->nKeyCol); VdbeCoverage(v);

    /* Generate code to handle collisions */
    regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
................................................................................
*/
void sqlite3CompleteInsertion(
  Parse *pParse,      /* The parser context */
  Table *pTab,        /* the table into which we are inserting */
  int iDataCur,       /* Cursor of the canonical data source */
  int iIdxCur,        /* First index cursor */
  int regNewData,     /* Range of content */

  int *aRegIdx,       /* Register used by each index.  0 for unused indices */
  int isUpdate,       /* True for UPDATE, False for INSERT */
  int appendBias,     /* True if this is likely to be an append */
  int useSeekResult   /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
){
  Vdbe *v;            /* Prepared statements under construction */
  Index *pIdx;        /* An index being inserted or updated */
  u8 pik_flags;       /* flag values passed to the btree insert */
  int regData;        /* Content registers (after the rowid) */
  int regRec;         /* Register holding assembled record for the table */
  int i;              /* Loop counter */
  u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */



  v = sqlite3GetVdbe(pParse);
  assert( v!=0 );
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( aRegIdx[i]==0 ) continue;
    bAffinityDone = 1;
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
      VdbeCoverage(v);
    }
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]);
    pik_flags = 0;
    if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT;
    if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
      assert( pParse->nested==0 );
      pik_flags |= OPFLAG_NCHANGE;
    }
    if( pik_flags )  sqlite3VdbeChangeP5(v, pik_flags);







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>

|

>
>
>
>
>

|
|
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







|
|




<
<
<
<
<







 







>













>
>










|







334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
...
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
...
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
....
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
....
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
....
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
....
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507





1508
1509
1510
1511
1512
1513
1514
....
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  int onError,          /* How to handle constraint errors */
  int iDbDest           /* The database of pDest */
);

/*
** Return the conflict handling mode that should be used for index pIdx
** if the statement specified conflict mode overrideError.
**
** If the index is not a UNIQUE index, then the conflict handling mode is
** always OE_None. Otherwise, it is one of OE_Abort, OE_Rollback, OE_Fail, 
** OE_Ignore or OE_Replace.
*/
static u8 idxConflictMode(Index *pIdx, u8 overrideError){
  u8 ret = pIdx->onError;
  if( ret!=OE_None ){
    if( overrideError!=OE_Default ) ret = overrideError;
    if( ret==OE_Default ) ret = OE_Abort;
  }
  return ret;
}

/*
** This routine is called to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
**    insert into TABLE (IDLIST) select
**
................................................................................
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int nColumn;          /* Number of columns in the data */
  int nHidden = 0;      /* Number of hidden columns if TABLE is virtual */
  int iDataCur = 0;     /* VDBE cursor that is the main data repository */
  int iIdxCur = 0;      /* First index cursor */
  int iSortCur = 0;     /* First sorter cursor (for INSERT INTO ... SELECT) */
  int ipkColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
  int endOfLoop;        /* Label for the end of the insertion loop */
  int srcTab = 0;       /* Data comes from this temporary cursor if >=0 */
  int addrInsTop = 0;   /* Jump to label "D" */
  int addrCont = 0;     /* Top of insert loop. Label "C" in templates 3 and 4 */
  SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
  int iDb;              /* Index of database holding TABLE */
................................................................................
    aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1));
    if( aRegIdx==0 ){
      goto insert_cleanup;
    }
    for(i=0; i<nIdx; i++){
      aRegIdx[i] = ++pParse->nMem;
    }

    /* If this is an INSERT INTO ... SELECT statement on a non-virtual table,
    ** check if it is possible to defer updating any indexes until after
    ** all rows have been processed. If it is, the index keys can be sorted
    ** before they are inserted into the index b-tree, which is more efficient
    ** for large inserts. It is possible to defer updating the indexes if:
    **
    **    * there are no triggers to fire, and
    **    * no foreign key processing to perform, and
    **    * the on-conflict mode used for all UNIQUE indexes is either 
    **      ROLLBACK or ABORT.
    */
    if( pSelect 
     && !IsVirtual(pTab) 
     && pTrigger==0 
     && 0==sqlite3FkRequired(pParse, pTab, 0, 0) 
    ){
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        u8 oe = idxConflictMode(pIdx, onError);
        if( oe==OE_Fail || oe==OE_Replace || oe==OE_Ignore ) break;
        assert( oe==OE_None||oe==OE_Abort||oe==OE_Rollback );
      }
      if( pIdx==0 ){
        /* This statement can sort the set of new keys for each index before
        ** writing them into the b-tree on disk. So open a sorter for each
        ** index on the table. */
        iSortCur = pParse->nTab;
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          sqlite3VdbeAddOp1(v, OP_SorterOpen, pParse->nTab++);
          sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
        }
        assert( iSortCur>0 );
      }
    }
  }

  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the
    ** following pseudocode (template 4):
    **
................................................................................
      sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
      sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
      sqlite3MayAbort(pParse);
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      int iIdxBase = iIdxCur;
      int op = OP_IdxInsert;
      sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
          regIns, 0, ipkColumn>=0, onError, endOfLoop, iSortCur!=0, &isReplace
      );
      if( iSortCur ){
        iIdxBase = iSortCur;
        isReplace = 1;
        op = OP_SorterInsert;
      }
      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
      sqlite3CompleteInsertion(pParse, pTab, 
          iDataCur, iIdxBase, regIns, op, aRegIdx, 0, appendFlag, isReplace==0
      );
    }
  }

  /* Update the count of rows that are inserted
  */
  if( (db->flags & SQLITE_CountRows)!=0 ){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
................................................................................
    sqlite3VdbeAddOp1(v, OP_Close, srcTab);
  }else if( pSelect ){
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont);
    sqlite3VdbeJumpHere(v, addrInsTop);
  }

  if( !IsVirtual(pTab) && !isView ){
    /* If new index keys were written into sorter objects instead of
    ** directly to the index b-trees, copy them from the sorters into the
    ** indexes now. And close all the sorters. */
    if( iSortCur ){
      int iTmp = sqlite3GetTempReg(pParse);
      for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
        int oe = idxConflictMode(pIdx, onError);
        int iCur = iSortCur + idx;
        int iIdx = iIdxCur + idx;
        int addr = sqlite3VdbeAddOp1(v, OP_SorterSort, iCur);
        sqlite3VdbeAddOp3(v, OP_SorterData, iCur, iTmp, iIdx);
        if( oe!=OE_None ){
          int nField = -1 * pIdx->nKeyCol;
          int jmp = sqlite3VdbeCurrentAddr(v)+2;
          sqlite3VdbeAddOp4Int(v, OP_NoConflict, iIdx, jmp, iTmp, nField);
          sqlite3UniqueConstraint(pParse, oe, pIdx);
        }
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, iTmp); 
        sqlite3VdbeAddOp2(v, OP_SorterNext, iCur, addr+1); VdbeCoverage(v);
        sqlite3VdbeJumpHere(v, addr);
        sqlite3VdbeAddOp1(v, OP_Close, iCur);
      }
      sqlite3ReleaseTempReg(pParse, iTmp);
    }

    /* Close all tables opened */
    if( iDataCur<iIdxCur ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur);
    for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqlite3VdbeAddOp1(v, OP_Close, idx+iIdxCur);
    }
  }

................................................................................
  int iDataCur,        /* Canonical data cursor (main table or PK index) */
  int iIdxCur,         /* First index cursor */
  int regNewData,      /* First register in a range holding values to insert */
  int regOldData,      /* Previous content.  0 for INSERTs */
  u8 pkChng,           /* Non-zero if the rowid or PRIMARY KEY changed */
  u8 overrideError,    /* Override onError to this if not OE_Default */
  int ignoreDest,      /* Jump to this label on an OE_Ignore resolution */
  int ignoreUnique,    /* Do not enforce UNIQUE constraints */
  int *pbMayReplace    /* OUT: Set to true if constraint may cause a replace */
){
  Vdbe *v;             /* VDBE under constrution */
  Index *pIdx;         /* Pointer to one of the indices */
  Index *pPk = 0;      /* The PRIMARY KEY index */
  sqlite3 *db;         /* Database connection */
  int i;               /* loop counter */
................................................................................
    ** logic below can all be skipped. */
    if( isUpdate && pPk==pIdx && pkChng==0 ){
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
      continue;
    }

    /* Find out what action to take in case there is a uniqueness conflict */
    onError = idxConflictMode(pIdx, overrideError);
    if( onError==OE_None || ignoreUnique ){ 
      sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
      continue;  /* pIdx is not a UNIQUE index */
    }





    
    /* Check to see if the new index entry will be unique */
    sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
                         regIdx, pIdx->nKeyCol); VdbeCoverage(v);

    /* Generate code to handle collisions */
    regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
................................................................................
*/
void sqlite3CompleteInsertion(
  Parse *pParse,      /* The parser context */
  Table *pTab,        /* the table into which we are inserting */
  int iDataCur,       /* Cursor of the canonical data source */
  int iIdxCur,        /* First index cursor */
  int regNewData,     /* Range of content */
  int idxop,          /* Opcode to use to write to "indexes" */
  int *aRegIdx,       /* Register used by each index.  0 for unused indices */
  int isUpdate,       /* True for UPDATE, False for INSERT */
  int appendBias,     /* True if this is likely to be an append */
  int useSeekResult   /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
){
  Vdbe *v;            /* Prepared statements under construction */
  Index *pIdx;        /* An index being inserted or updated */
  u8 pik_flags;       /* flag values passed to the btree insert */
  int regData;        /* Content registers (after the rowid) */
  int regRec;         /* Register holding assembled record for the table */
  int i;              /* Loop counter */
  u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */

  assert( idxop==OP_IdxInsert || idxop==OP_SorterInsert );

  v = sqlite3GetVdbe(pParse);
  assert( v!=0 );
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( aRegIdx[i]==0 ) continue;
    bAffinityDone = 1;
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
      VdbeCoverage(v);
    }
    sqlite3VdbeAddOp2(v, idxop, iIdxCur+i, aRegIdx[i]);
    pik_flags = 0;
    if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT;
    if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
      assert( pParse->nested==0 );
      pik_flags |= OPFLAG_NCHANGE;
    }
    if( pik_flags )  sqlite3VdbeChangeP5(v, pik_flags);

Changes to src/sqliteInt.h.

3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8);
void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
void sqlite3ResolvePartIdxLabel(Parse*,int);
void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
                                     u8,u8,int,int*);
void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*);
void sqlite3BeginWriteOperation(Parse*, int, int);
void sqlite3MultiWrite(Parse*);
void sqlite3MayAbort(Parse*);
void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8);
void sqlite3UniqueConstraint(Parse*, int, Index*);
void sqlite3RowidConstraint(Parse*, int, Table*);







|
|







3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8);
void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
void sqlite3ResolvePartIdxLabel(Parse*,int);
void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
                                     u8,u8,int,int,int*);
void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int,int*,int,int,int);
int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*);
void sqlite3BeginWriteOperation(Parse*, int, int);
void sqlite3MultiWrite(Parse*);
void sqlite3MayAbort(Parse*);
void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8);
void sqlite3UniqueConstraint(Parse*, int, Index*);
void sqlite3RowidConstraint(Parse*, int, Table*);

Changes to src/update.c.

563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
...
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
  if( !isView ){
    int j1 = 0;           /* Address of jump instruction */
    int bReplace = 0;     /* True if REPLACE conflict resolution might happen */

    /* Do constraint checks. */
    assert( regOldRowid>0 );
    sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
        regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace);

    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
    }

    /* Delete the index entries associated with the current record.  */
................................................................................

    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey);
    }
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
                             regNewRowid, aRegIdx, 1, 0, 0);

    /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
    ** handle rows (possibly in other tables) that refer via a foreign key
    ** to the row just updated. */ 
    if( hasFK ){
      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey);
    }







|







 







|







563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
...
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
  if( !isView ){
    int j1 = 0;           /* Address of jump instruction */
    int bReplace = 0;     /* True if REPLACE conflict resolution might happen */

    /* Do constraint checks. */
    assert( regOldRowid>0 );
    sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
        regNewRowid, regOldRowid, chngKey, onError, labelContinue, 0,&bReplace);

    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
    }

    /* Delete the index entries associated with the current record.  */
................................................................................

    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey);
    }
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
                             regNewRowid, OP_IdxInsert, aRegIdx, 1, 0, 0);

    /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
    ** handle rows (possibly in other tables) that refer via a foreign key
    ** to the row just updated. */ 
    if( hasFK ){
      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey);
    }

Changes to src/vdbe.c.

160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
....
3765
3766
3767
3768
3769
3770
3771
3772

3773
3774
3775
3776
3777
3778
3779
....
3830
3831
3832
3833
3834
3835
3836



3837
3838
3839
3840
3841
3842
3843
....
3844
3845
3846
3847
3848
3849
3850
3851
3852
3853
3854
3855
3856
3857
3858
** string that the register itself controls.  In other words, it
** converts an MEM_Ephem string into a string with P.z==P.zMalloc.
*/
#define Deephemeralize(P) \
   if( ((P)->flags&MEM_Ephem)!=0 \
       && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;}

/* Return true if the cursor was opened using the OP_OpenSorter opcode. */
#define isSorter(x) ((x)->pSorter!=0)

/*
** Allocate VdbeCursor number iCur.  Return a pointer to it.  Return NULL
** if we run out of memory.
*/
static VdbeCursor *allocateCursor(
................................................................................
** See also: Found, NotExists, NoConflict
*/
/* Opcode: NoConflict P1 P2 P3 P4 *
** Synopsis: key=r[P3@P4]
**
** If P4==0 then register P3 holds a blob constructed by MakeRecord.  If
** P4>0 then register P3 is the first of P4 registers that form an unpacked
** record.

** 
** Cursor P1 is on an index btree.  If the record identified by P3 and P4
** contains any NULL value, jump immediately to P2.  If all terms of the
** record are not-NULL then a check is done to determine if any row in the
** P1 index btree has a matching key prefix.  If there are no matches, jump
** immediately to P2.  If there is a match, fall through and leave the P1
** cursor pointing to the matching row.
................................................................................
    pIdxKey = sqlite3VdbeAllocUnpackedRecord(
        pC->pKeyInfo, aTempRec, sizeof(aTempRec), &pFree
    );
    if( pIdxKey==0 ) goto no_mem;
    assert( pIn3->flags & MEM_Blob );
    ExpandBlob(pIn3);
    sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey);



  }
  pIdxKey->default_rc = 0;
  if( pOp->opcode==OP_NoConflict ){
    /* For the OP_NoConflict opcode, take the jump if any of the
    ** input fields are NULL, since any key with a NULL will not
    ** conflict */
    for(ii=0; ii<pIdxKey->nField; ii++){
................................................................................
      if( pIdxKey->aMem[ii].flags & MEM_Null ){
        pc = pOp->p2 - 1; VdbeBranchTaken(1,2);
        break;
      }
    }
  }
  rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, pIdxKey, 0, 0, &res);
  if( pOp->p4.i==0 ){
    sqlite3DbFree(db, pFree);
  }
  if( rc!=SQLITE_OK ){
    break;
  }
  pC->seekResult = res;
  alreadyExists = (res==0);







|







 







|
>







 







>
>
>







 







|







160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
....
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
....
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
....
3848
3849
3850
3851
3852
3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
** string that the register itself controls.  In other words, it
** converts an MEM_Ephem string into a string with P.z==P.zMalloc.
*/
#define Deephemeralize(P) \
   if( ((P)->flags&MEM_Ephem)!=0 \
       && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;}

/* Return true if the cursor was opened using the OP_SorterOpen opcode. */
#define isSorter(x) ((x)->pSorter!=0)

/*
** Allocate VdbeCursor number iCur.  Return a pointer to it.  Return NULL
** if we run out of memory.
*/
static VdbeCursor *allocateCursor(
................................................................................
** See also: Found, NotExists, NoConflict
*/
/* Opcode: NoConflict P1 P2 P3 P4 *
** Synopsis: key=r[P3@P4]
**
** If P4==0 then register P3 holds a blob constructed by MakeRecord.  If
** P4>0 then register P3 is the first of P4 registers that form an unpacked
** record. If P4<0, then P3 holds a blob constructed by MakeRecord, but
** only the first |P4| fields should be considered.
** 
** Cursor P1 is on an index btree.  If the record identified by P3 and P4
** contains any NULL value, jump immediately to P2.  If all terms of the
** record are not-NULL then a check is done to determine if any row in the
** P1 index btree has a matching key prefix.  If there are no matches, jump
** immediately to P2.  If there is a match, fall through and leave the P1
** cursor pointing to the matching row.
................................................................................
    pIdxKey = sqlite3VdbeAllocUnpackedRecord(
        pC->pKeyInfo, aTempRec, sizeof(aTempRec), &pFree
    );
    if( pIdxKey==0 ) goto no_mem;
    assert( pIn3->flags & MEM_Blob );
    ExpandBlob(pIn3);
    sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey);
    if( pOp->p4.i<0 ){
      pIdxKey->nField = pOp->p4.i * -1;
    }
  }
  pIdxKey->default_rc = 0;
  if( pOp->opcode==OP_NoConflict ){
    /* For the OP_NoConflict opcode, take the jump if any of the
    ** input fields are NULL, since any key with a NULL will not
    ** conflict */
    for(ii=0; ii<pIdxKey->nField; ii++){
................................................................................
      if( pIdxKey->aMem[ii].flags & MEM_Null ){
        pc = pOp->p2 - 1; VdbeBranchTaken(1,2);
        break;
      }
    }
  }
  rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, pIdxKey, 0, 0, &res);
  if( pOp->p4.i<=0 ){
    sqlite3DbFree(db, pFree);
  }
  if( rc!=SQLITE_OK ){
    break;
  }
  pC->seekResult = res;
  alreadyExists = (res==0);

Changes to test/e_vacuum.test.

25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

  db transaction {
    execsql { PRAGMA page_size = 1024; }
    execsql $sql
    execsql {
      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
      INSERT INTO t1 VALUES(1, randomblob(400));
      INSERT INTO t1 SELECT a+1,  randomblob(400) FROM t1;
      INSERT INTO t1 SELECT a+2,  randomblob(400) FROM t1;
      INSERT INTO t1 SELECT a+4,  randomblob(400) FROM t1;
      INSERT INTO t1 SELECT a+8,  randomblob(400) FROM t1;
      INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1;
      INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1;
      INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1;

      CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
      INSERT INTO t2 SELECT * FROM t1;
    }
  }

  return [expr {[file size test.db] / 1024}]
}

# This proc returns the number of contiguous blocks of pages that make up
................................................................................
#                    less fragmented.
#
ifcapable vtab&&compound {
  create_db 
  register_dbstat_vtab db
  do_execsql_test e_vacuum-1.2.1 {
    DELETE FROM t1 WHERE a%2;
    INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
    UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
  } {}
  
  do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1
  do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1
  do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1
  







|
|
|
|
|
|
|


|







 







|







25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

  db transaction {
    execsql { PRAGMA page_size = 1024; }
    execsql $sql
    execsql {
      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
      INSERT INTO t1 VALUES(1, randomblob(400));
      INSERT OR FAIL INTO t1 SELECT a+1,  randomblob(400) FROM t1;
      INSERT OR FAIL INTO t1 SELECT a+2,  randomblob(400) FROM t1;
      INSERT OR FAIL INTO t1 SELECT a+4,  randomblob(400) FROM t1;
      INSERT OR FAIL INTO t1 SELECT a+8,  randomblob(400) FROM t1;
      INSERT OR FAIL INTO t1 SELECT a+16, randomblob(400) FROM t1;
      INSERT OR FAIL INTO t1 SELECT a+32, randomblob(400) FROM t1;
      INSERT OR FAIL INTO t1 SELECT a+64, randomblob(400) FROM t1;

      CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
      INSERT OR FAIL INTO t2 SELECT * FROM t1;
    }
  }

  return [expr {[file size test.db] / 1024}]
}

# This proc returns the number of contiguous blocks of pages that make up
................................................................................
#                    less fragmented.
#
ifcapable vtab&&compound {
  create_db 
  register_dbstat_vtab db
  do_execsql_test e_vacuum-1.2.1 {
    DELETE FROM t1 WHERE a%2;
    INSERT OR REPLACE INTO t1 SELECT b, a FROM t2 WHERE a%2;
    UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
  } {}
  
  do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1
  do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1
  do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1
  

Added test/insert6.test.







































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
# 2015 March 20
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that sorter objects are used by 
# "INSERT INTO ... SELECT ..." statements when possible.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix insert6

# Return the number of OP_SorterOpen instructions in the SQL passed as
# the only argument if it is compiled using connection [db].
#
proc sorter_count {sql} {
  set res 0
  db cache flush
  db eval "EXPLAIN $sql" x {
    if {$x(opcode) == "SorterOpen"} { incr res }
  }
  return $res
}


#-------------------------------------------------------------------------
# Warm body test. This verifies that the simplest case works for both
# regular and WITHOUT ROWID tables.
#
do_execsql_test 1.1 {
  CREATE TABLE t2(x UNIQUE ON CONFLICT IGNORE, y, z);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<99 )
  INSERT INTO t2 SELECT abs(random()), abs(random()), abs(random()) FROM cnt;
}

foreach {tn nSort schema} {
  1 3 { CREATE TABLE t1(a, b, c) }
  2 4 { CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID }
} {

  do_test 1.$tn.1 {
    execsql { DROP TABLE IF EXISTS t1 }
    execsql $schema 
  } {}

  do_execsql_test 1.$tn.2 {
    CREATE INDEX t1a ON t1(a);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1c ON t1(c);
  }

  do_execsql_test 1.$tn.3 {
    INSERT INTO t1 SELECT x, y, z FROM t2;
    PRAGMA integrity_check;
    SELECT count(*) FROM t1;
  } {ok 100}
  
  do_execsql_test 1.$tn.4 {
    INSERT INTO t1 SELECT -x, y, z FROM t2;
    PRAGMA integrity_check;
  } {ok}

  do_execsql_test 1.$tn.5 {
    SELECT count(*) FROM t1;
  } {200}

  do_test 1.$tn.6 {
    sorter_count { INSERT INTO t1 SELECT * FROM t2 }
  } $nSort
}

#-------------------------------------------------------------------------
# The following test cases check that the sorters are disabled if any
# of the following are true:
#
#   2.1: There are one or more UNIQUE constraints or indexes and the 
#        statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE".
#
#   2.2: The statement does not explicitly specify a conflict mode and 
#        there are one or more PRIMARY KEY or UNIQUE constraints with 
#        "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling 
#        mode.
#
#   2.3: There are one or more INSERT triggers on the target table.
#
#   2.4: The target table is the parent or child of an FK constraint.
#

do_execsql_test 2.1.1 {
  CREATE TABLE x1(a, b, c);
  CREATE INDEX x1a ON x1(a);

  CREATE TABLE x2(a, b, c);
  CREATE UNIQUE INDEX x2a ON x2(a);

  CREATE TABLE x3(a PRIMARY KEY, b, c);
  CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID;
}

do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 1
do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0
do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0
do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0

do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 1
do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0
do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0
do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0

do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 1
do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0
do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0
do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0

do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1
do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1
do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1
do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1

do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1
do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1
do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1
do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1


foreach {tn scount schema} {
  2.1   0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) }
  2.2   0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) }
  2.3   0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) }
  2.4   0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) }
  2.5   0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) }
  2.6   0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) }
  2.7   0 { 
    CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID
  }
  2.8   0 { 
    CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID
  }
  2.9   0 { 
    CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID
  }

  3.1   1 {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
  }
  3.2   0 {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END;
  }
  3.3   0 {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END;
  }

  4.1   2 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED);
    PRAGMA foreign_keys = 0;
  }
  4.2   0 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED);
    PRAGMA foreign_keys = 1;
  }

  4.3   1 {
    CREATE TABLE p1(x, y UNIQUE);
    CREATE TABLE t1(a, b, c REFERENCES p1(y));
    CREATE INDEX i1 ON t1(a);
    PRAGMA foreign_keys = 0;
  }
  4.4   0 {
    CREATE TABLE p1(x, y UNIQUE);
    CREATE TABLE t1(a, b, c REFERENCES p1(y));
    CREATE INDEX i1 ON t1(a);
    PRAGMA foreign_keys = 1;
  }

} {
  execsql { 
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS c1;
    DROP TABLE IF EXISTS p1;
  }

  do_test 2.2.$tn {
    execsql $schema
    sorter_count { INSERT INTO t1 SELECT * FROM t2 }
  } $scount
}

#-------------------------------------------------------------------------
# Test that if a UNIQUE constraint is violated and the on conflict mode
# is either ABORT or ROLLBACK, the conflict is handled correctly.
#
#   3.2: Check that conflicts are actually detected. 
#   3.3: Check that OR ROLLBACK really does rollback the transaction.
#   3.4: Check that OR ABORT does not.
#
do_execsql_test 3.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c));
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
  CREATE TABLE src(a, b, c);
}

do_catchsql_test 3.2.1 {
  INSERT INTO src VALUES (10, 11, 12), (7, 14, 12);
  INSERT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.a}}

do_catchsql_test 3.2.2 {
  DELETE FROM src;
  INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
  INSERT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.b, t1.c}}

do_catchsql_test 3.2.3.1 {
  CREATE TABLE t3(a);
  CREATE UNIQUE INDEX t3a ON t3(a);

  CREATE TABLE t3src(a);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 )
  INSERT INTO t3src SELECT 'abc' FROM cnt;
} {0 {}}

#  execsql { PRAGMA vdbe_trace = 1 }
do_catchsql_test 3.2.3.2 {
  INSERT INTO t3 SELECT * FROM t3src;
} {1 {UNIQUE constraint failed: t3.a}}

do_catchsql_test 3.3.1 {
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (7, 13, 14);
    INSERT OR ROLLBACK INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.a}}
do_catchsql_test 3.3.2 {
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
    INSERT OR ROLLBACK INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.b, t1.c}}
do_test 3.3.3 {
  sqlite3_get_autocommit db
} 1

do_catchsql_test 3.4.1 {
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (7, 14, 12);
    INSERT OR ABORT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.a}}
do_catchsql_test 3.4.2 {
  ROLLBACK;
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
    INSERT OR ABORT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.b, t1.c}}
do_test 3.4.3 {
  sqlite3_get_autocommit db
} 0
do_execsql_test 3.4.4 { ROLLBACK }

#-------------------------------------------------------------------------
# The following tests - 4.* - check that this optimization is actually
# doing something helpful. They do this by executing a big 
# "INSERT INTO SELECT" statement in wal mode with a small pager cache.
# Once with "OR FAIL" (so that the sorters are not used) and once with
# the default "OR ABORT" (so that they are).
#
# If the sorters are doing their job, the wal file generated by the 
# "OR ABORT" case should be much smaller than the "OR FAIL" trial.
#

proc odd_collate {lhs rhs} {
  string compare [string range $lhs 6 end] [string range $rhs 6 end]
}

proc do_insert6_4_test {tn sql} {

  reset_db
  db collate odd_collate odd_collate
  execsql $sql
  db_save_and_close

  foreach {tn2 ::onerror ::var} {
    1 "OR ABORT" ::sz1
    2 "OR FAIL"  ::sz2
  } {
    do_test $tn.$tn2 {
      db_restore_and_reopen
      db collate odd_collate odd_collate
      execsql "
        PRAGMA journal_mode = wal;
        PRAGMA cache_size = 5;
        PRAGMA wal_autocheckpoint = 0;
        INSERT $onerror INTO t1 SELECT * FROM src;
      "
      set $var [file size test.db-wal]
      db close
    } {}
  }

  do_test $tn.3.($::sz1<$::sz2) {
    expr {$sz1 < ($sz2/2)}
  } 1

  sqlite3 db test.db
  db collate odd_collate odd_collate
  integrity_check $tn.4 
}

do_insert6_4_test 4.1 {
  CREATE TABLE t1(a, b, c);
  CREATE UNIQUE INDEX t1a ON t1(a);
  CREATE UNIQUE INDEX t1bc ON t1(b, c);

  CREATE TABLE src(x, y, z);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt;
}

do_insert6_4_test 4.2 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x);
  CREATE UNIQUE INDEX t1b ON t1(b);
  CREATE INDEX t1x1 ON t1(x);
  CREATE INDEX t1x2 ON t1(x);
  CREATE INDEX t1x3 ON t1(x);
  CREATE INDEX t1x4 ON t1(x);

  CREATE TABLE src(a, b, x);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT random(), x, zeroblob(50) FROM cnt;
}

do_insert6_4_test 4.3 {
  CREATE TABLE t1(a, b, c);
  CREATE UNIQUE INDEX t1ab ON t1(a, b);
  CREATE UNIQUE INDEX t1ac ON t1(a, c);

  CREATE TABLE src(a, b, c);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt;
}

db collate odd_collate odd_collate
do_insert6_4_test 4.5 {
  CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate);
  CREATE UNIQUE INDEX t1t ON t1(t);
  CREATE UNIQUE INDEX t1v ON t1(v);

  CREATE TABLE src(t, v);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt;
}

db collate odd_collate odd_collate
do_insert6_4_test 4.6 {
  CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID;
  CREATE TABLE src(t);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT hex(randomblob(50)) FROM cnt;
}

finish_test

Changes to test/stat.test.

72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
    DROP TABLE t1;
  }
} {}

do_execsql_test stat-2.1 {
  CREATE TABLE t3(a PRIMARY KEY, b);
  INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name != 'sqlite_master';
} [list \
  sqlite_autoindex_t3_1 / 3 internal 3 368 623 125       \
  sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123        \
  sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131         \







|

|

|

|

|







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
    DROP TABLE t1;
  }
} {}

do_execsql_test stat-2.1 {
  CREATE TABLE t3(a PRIMARY KEY, b);
  INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
  INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name != 'sqlite_master';
} [list \
  sqlite_autoindex_t3_1 / 3 internal 3 368 623 125       \
  sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123        \
  sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131         \

Changes to test/wal.test.

691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
...
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
  execsql { INSERT INTO t1 VALUES( blob(900) ) }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [wal_file_size 4 1024]]

do_test wal-11.4 {
  execsql { 
    BEGIN;
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 2
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 4
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 8
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 16
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [wal_file_size 32 1024]]
do_test wal-11.5 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
................................................................................
set nWal 39
if {[permutation]!="mmap"} {set nWal 37}
ifcapable !mmap {set nWal 37}
do_test wal-11.10 {
  execsql {
    PRAGMA cache_size = 10;
    BEGIN;
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 32
      SELECT count(*) FROM t1;
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [wal_file_size $nWal 1024]]
do_test wal-11.11 {
  execsql {
      SELECT count(*) FROM t1;







|
|
|
|







 







|







691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
...
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
  execsql { INSERT INTO t1 VALUES( blob(900) ) }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [wal_file_size 4 1024]]

do_test wal-11.4 {
  execsql { 
    BEGIN;
      INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1;   -- 2
      INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1;   -- 4
      INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1;   -- 8
      INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1;   -- 16
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [wal_file_size 32 1024]]
do_test wal-11.5 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
................................................................................
set nWal 39
if {[permutation]!="mmap"} {set nWal 37}
ifcapable !mmap {set nWal 37}
do_test wal-11.10 {
  execsql {
    PRAGMA cache_size = 10;
    BEGIN;
      INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1;   -- 32
      SELECT count(*) FROM t1;
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [wal_file_size $nWal 1024]]
do_test wal-11.11 {
  execsql {
      SELECT count(*) FROM t1;