SQLite

    ** 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 and PRIMARY KEY indexes, 
    **      including INTEGER PRIMARY KEYs, is either ROLLBACK or ABORT.
    */
    if( pSelect 
     && onError!=OE_Fail && onError!=OE_Replace && onError!=OE_Ignore
     && !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){

  } $nSort
}

#-------------------------------------------------------------------------
# The following test cases check that the sorters are disabled if any
# of the following are true:
#

#   2.1: 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.

  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 } } 0
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 } } 0
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 } } 0
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_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;
}

#-------------------------------------------------------------------------
# At one point the sorters were used for INSERT statements that specify
# "OR FAIL", "REPLACE" or "IGNORE" if there were no PRIMARY KEY or
# UNIQUE indexes. This is incorrect, as all such tables have an implicit
# IPK column. So using the sorters can cause corruption. This test checks
# that that problem no longer exists.
#
reset_db
do_execsql_test 5.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX t1b ON t1(b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
}

do_catchsql_test 5.2 {
  INSERT OR FAIL INTO t1 
  SELECT 2, 'x', 'x' UNION ALL SELECT 3, 'x', 'x' UNION ALL SELECT 4, 'x', 'x';
} {1 {UNIQUE constraint failed: t1.a}}

integrity_check 5.3


finish_test