SQLite: Check-in [0f92736d]

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.

**    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 */

**    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.

#    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 {

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

#
#***********************************************************************
# 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








|







 







|
|
|
|
|
>
>
>
>







 







|
|







 







>
>
|
>
|







 








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

#
#***********************************************************************
# 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.

#    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;








|







 







>
>
|
>
|

#    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;