/ Check-in [0f92736d]
Login

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

Overview
Comment:Make sure the ON CONFLICT clause on a BEGIN overrides the conflict resolution specified by an index. This fixes a bug reported on the newsgroup. (CVS 975)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:0f92736d1fbe3b587592fe1f26dfb3558cc49727
User & Date: drh 2003-05-16 02:30:27
Context
2003-05-17
00:05
Fix memory allocation problem in the sqlite_get_table() API. Ticket #315. (CVS 976) check-in: f1d955ef user: drh tags: trunk
2003-05-16
02:30
Make sure the ON CONFLICT clause on a BEGIN overrides the conflict resolution specified by an index. This fixes a bug reported on the newsgroup. (CVS 975) check-in: 0f92736d user: drh tags: trunk
2003-05-13
08:01
Removed 3rd (file) column from PRAGMA database_list until tests are fixed. (CVS 974) check-in: 4183cf97 user: jplyon tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
664
665
666
667
668
669
670
671
672
673

674
675
676
677
678
679
680
...
714
715
716
717
718
719
720
721
722
723

724
725
726
727
728
729
730
...
778
779
780
781
782
783
784
785
786
787

788
789
790
791
792
793
794
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.83 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
      /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = pParse->db->onError;
      if( onError==OE_Default ) onError = OE_Abort;

    }
    if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
      onError = OE_Abort;
    }
    sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
    addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
    switch( onError ){
................................................................................
  ** Also, if the conflict resolution policy is REPLACE, then we
  ** can skip this test.
  */
  if( (recnoChng || !isUpdate) && pTab->iPKey>=0 ){
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = pParse->db->onError;
      if( onError==OE_Default ) onError = OE_Abort;

    }
    if( onError!=OE_Replace ){
      if( isUpdate ){
        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
        jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
      }
................................................................................
    if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);

    /* Find out what action to take in case there is an indexing conflict */
    onError = pIdx->onError;
    if( onError==OE_None ) continue;  /* pIdx is not a UNIQUE index */
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = pParse->db->onError;
      if( onError==OE_Default ) onError = OE_Abort;

    }

    /* Check to see if the new index entry will be unique */
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
    jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);

    /* Generate code that executes if the new index entry is not unique */







|







 







|

|
>







 







|

|
>







 







|

|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
...
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
...
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.84 2003/05/16 02:30:27 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
      /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
      continue;
    }
    onError = pTab->aCol[i].notNull;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
      onError = OE_Abort;
    }
    sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
    addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
    switch( onError ){
................................................................................
  ** Also, if the conflict resolution policy is REPLACE, then we
  ** can skip this test.
  */
  if( (recnoChng || !isUpdate) && pTab->iPKey>=0 ){
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }
    if( onError!=OE_Replace ){
      if( isUpdate ){
        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
        jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
      }
................................................................................
    if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);

    /* Find out what action to take in case there is an indexing conflict */
    onError = pIdx->onError;
    if( onError==OE_None ) continue;  /* pIdx is not a UNIQUE index */
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( pParse->db->onError!=OE_Default ){
      onError = pParse->db->onError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }

    /* Check to see if the new index entry will be unique */
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
    jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);

    /* Generate code that executes if the new index entry is not unique */

Changes to test/capi2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.6 2003/03/01 19:53:16 drh Exp $
#

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

# Check basic functionality
#
................................................................................
} {1 {uniqueness constraint failed}}
do_test capi2-6.28 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 13 {x counter}}
do_test capi2-6.99 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}
execsql {ROLLBACK}

do_test capi2-7.1 {
  stepsql $DB {
    SELECT * FROM t1
  }
} {0 1 2 3}
do_test capi2-7.2 {







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.7 2003/05/16 02:30:27 drh Exp $
#

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

# Check basic functionality
#
................................................................................
} {1 {uniqueness constraint failed}}
do_test capi2-6.28 {
  list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
} {SQLITE_ROW 1 13 {x counter}}
do_test capi2-6.99 {
  list [catch {sqlite_finalize $VM1} msg] [set msg]
} {0 {}}
catchsql {ROLLBACK}

do_test capi2-7.1 {
  stepsql $DB {
    SELECT * FROM t1
  }
} {0 1 2 3}
do_test capi2-7.2 {

Changes to test/conflict.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
216
217
218
219
220
221
222
223
224
225
226
227




228
229
230
231
232
233
234
...
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
...
349
350
351
352
353
354
355


356

357
358
359
360
361
362
363
364
...
655
656
657
658
659
660
661
662






















663
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.16 2003/02/26 13:52:52 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
  5 ABORT    {}       INSERT                  1 {}  1
  6 ROLLBACK {}       INSERT                  1 {}  {}
  7 REPLACE  {}       {INSERT OR IGNORE}      0 3   1
  8 IGNORE   {}       {INSERT OR REPLACE}     0 4   1
  9 FAIL     {}       {INSERT OR IGNORE}      0 3   1
 10 ABORT    {}       {INSERT OR REPLACE}     0 4   1
 11 ROLLBACK {}       {INSERT OR IGNORE }     0 3   1
 12 REPLACE  IGNORE   INSERT                  0 4   1
 13 IGNORE   REPLACE  INSERT                  0 3   1
 14 FAIL     IGNORE   INSERT                  1 {}  1
 15 ABORT    REPLACE  INSERT                  1 {}  1
 16 ROLLBACK IGNORE   INSERT                  1 {}  {}




} {
  do_test conflict-4.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
................................................................................
 15 {}       {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {}       {INSERT OR ROLLBACK}    1 {}  {}
 17 {}       IGNORE   INSERT                  0 {}  1
 18 {}       REPLACE  INSERT                  0 5   1
 19 {}       FAIL     INSERT                  1 {}  1
 20 {}       ABORT    INSERT                  1 {}  1
 21 {}       ROLLBACK INSERT                  1 {}  {}
 22 REPLACE  FAIL     INSERT                  0 5   1
 23 IGNORE   ROLLBACK INSERT                  0 {}  1
} {
  if {$t0} {set t1 {t1.c may not be NULL}}
  do_test conflict-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0
 17 {}       IGNORE   UPDATE                  0 {6 7 3 9}  1
 18 {}       REPLACE  UPDATE                  0 {7 6 9}    1
 19 {}       FAIL     UPDATE                  1 {6 7 3 4}  1
 20 {}       ABORT    UPDATE                  1 {1 2 3 4}  1
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0


 22 REPLACE  FAIL     UPDATE                  0 {7 6 9}    1

 23 IGNORE   ROLLBACK UPDATE                  0 {6 7 3 9}  1
} {
  if {$t0} {set t1 {uniqueness constraint failed}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
    SELECT * FROM t2;
  }
} {0 {3 3 1 3 3}}
do_test conflict-9.26 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {6}























finish_test







|







 







|
|
|
|
|
>
>
>
>







 







|
|







 







>
>
|
>
|







 








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

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
...
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
...
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.17 2003/05/16 02:30:27 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {
................................................................................
  5 ABORT    {}       INSERT                  1 {}  1
  6 ROLLBACK {}       INSERT                  1 {}  {}
  7 REPLACE  {}       {INSERT OR IGNORE}      0 3   1
  8 IGNORE   {}       {INSERT OR REPLACE}     0 4   1
  9 FAIL     {}       {INSERT OR IGNORE}      0 3   1
 10 ABORT    {}       {INSERT OR REPLACE}     0 4   1
 11 ROLLBACK {}       {INSERT OR IGNORE }     0 3   1
 12 REPLACE  IGNORE   INSERT                  0 3   1
 13 IGNORE   REPLACE  INSERT                  0 4   1
 14 FAIL     IGNORE   INSERT                  0 3   1
 15 ABORT    REPLACE  INSERT                  0 4   1
 16 ROLLBACK IGNORE   INSERT                  0 3   1
 12 IGNORE   REPLACE  INSERT                  0 4   1
 13 IGNORE   FAIL     INSERT                  1 {}  1
 14 IGNORE   ABORT    INSERT                  1 {}  1
 15 IGNORE   ROLLBACK INSERT                  1 {}  {}
} {
  do_test conflict-4.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
................................................................................
 15 {}       {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {}       {INSERT OR ROLLBACK}    1 {}  {}
 17 {}       IGNORE   INSERT                  0 {}  1
 18 {}       REPLACE  INSERT                  0 5   1
 19 {}       FAIL     INSERT                  1 {}  1
 20 {}       ABORT    INSERT                  1 {}  1
 21 {}       ROLLBACK INSERT                  1 {}  {}
 22 REPLACE  FAIL     INSERT                  1 {}  1
 23 IGNORE   ROLLBACK INSERT                  1 {}  {}
} {
  if {$t0} {set t1 {t1.c may not be NULL}}
  do_test conflict-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0
 17 {}       IGNORE   UPDATE                  0 {6 7 3 9}  1
 18 {}       REPLACE  UPDATE                  0 {7 6 9}    1
 19 {}       FAIL     UPDATE                  1 {6 7 3 4}  1
 20 {}       ABORT    UPDATE                  1 {1 2 3 4}  1
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {uniqueness constraint failed}}
  do_test conflict-6.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
................................................................................
    SELECT * FROM t2;
  }
} {0 {3 3 1 3 3}}
do_test conflict-9.26 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {6}

do_test conflict-10.1 {
  catchsql {
    DELETE FROM t1;
    BEGIN ON CONFLICT ROLLBACK;
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(1,3);
    COMMIT;
  }
  execsql {SELECT * FROM t1}
} {}
do_test conflict-10.2 {
  catchsql {
    CREATE TABLE t4(x);
    CREATE UNIQUE INDEX t4x ON t4(x);
    BEGIN ON CONFLICT ROLLBACK;
    INSERT INTO t4 VALUES(1);
    INSERT INTO t4 VALUES(1);
    COMMIT;
  }
  execsql {SELECT * FROM t4}
} {}

finish_test

Changes to test/memdb.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
243
244
245
246
247
248
249


250

251
252
253
254
255
256
257
258
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is in-memory database backend.
#
# $Id: memdb.test,v 1.3 2003/05/02 14:32:15 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
................................................................................
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0
 17 {}       IGNORE   UPDATE                  0 {6 7 3 9}  1
 18 {}       REPLACE  UPDATE                  0 {7 6 9}    1
 19 {}       FAIL     UPDATE                  1 {6 7 3 4}  1
 20 {}       ABORT    UPDATE                  1 {1 2 3 4}  1
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0


 22 REPLACE  FAIL     UPDATE                  0 {7 6 9}    1

 23 IGNORE   ROLLBACK UPDATE                  0 {6 7 3 9}  1
} {
  if {$t0} {set t1 {uniqueness constraint failed}}
  do_test memdb-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;







|







 







>
>
|
>
|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is in-memory database backend.
#
# $Id: memdb.test,v 1.4 2003/05/16 02:30:27 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
................................................................................
 15 {}       {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1
 16 {}       {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0
 17 {}       IGNORE   UPDATE                  0 {6 7 3 9}  1
 18 {}       REPLACE  UPDATE                  0 {7 6 9}    1
 19 {}       FAIL     UPDATE                  1 {6 7 3 4}  1
 20 {}       ABORT    UPDATE                  1 {1 2 3 4}  1
 21 {}       ROLLBACK UPDATE                  1 {1 2 3 4}  0
 22 REPLACE  IGNORE   UPDATE                  0 {6 7 3 9}  1
 23 IGNORE   REPLACE  UPDATE                  0 {7 6 9}    1
 24 REPLACE  FAIL     UPDATE                  1 {6 7 3 4}  1
 25 IGNORE   ABORT    UPDATE                  1 {1 2 3 4}  1
 26 REPLACE  ROLLBACK UPDATE                  1 {1 2 3 4}  0
} {
  if {$t0} {set t1 {uniqueness constraint failed}}
  do_test memdb-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;