/ Check-in [d4215942]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:Disable the sorter optimization used by INSERT INTO SELECT statements if the statement explicitly specifies REPLACE, IGNORE or FAIL conflict handling.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | insert-select-opt
Files: files | file ages | folders
SHA1: d42159429735c4a03f1d66887135487a9513ea29
User & Date: dan 2015-03-21 07:03:55
Context
2015-03-21
07:16
Merge recent trunk changes with this branch. (check-in: 35eef4dd user: dan tags: insert-select-opt)
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)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c. 

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

    ** 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){








|
|


>







|








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

    ** 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){

Changes to test/insert6.test. 

76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
...
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
...
378
379
380
381
382
383
384























385
386
387

  } $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.
................................................................................
  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_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









<
|







 







|




|




|







 







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




76
77
78
79
80
81
82

83
84
85
86
87
88
89
90
..
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
...
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409

  } $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

This page was generated in about 0.01s by Fossil 2.11 [0c374456b3] 2020-02-27 19:16:53