/ Check-in [e813faae]
Login

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

Overview
Comment:Avoid corrupting indices when doing a REPLACE on a table with an INTEGER PRIMARY KEY that also has another index. Ticket #334. (CVS 999)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e813faae0e4086571d13c769f793add7f1f979af
User & Date: drh 2003-06-04 12:23:31
Context
2003-06-04
12:31
Disable the unreadable file test (attach-6.2) under windows because of problems in the OS. (CVS 1000) check-in: 4bfb30d2 user: drh tags: trunk
12:23
Avoid corrupting indices when doing a REPLACE on a table with an INTEGER PRIMARY KEY that also has another index. Ticket #334. (CVS 999) check-in: e813faae user: drh tags: trunk
2003-06-03
01:47
Additional testing of the ATTACH command with bug fixes for the new problems that the tests found. (CVS 998) check-in: 3e8889d7 user: drh 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
...
711
712
713
714
715
716
717
718
719
720
721

722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750









751

752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
...
792
793
794
795
796
797
798





799
800
801
802
803
804
805
**    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.86 2003/06/01 01:10:33 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  /* Test all CHECK constraints
  */
  /**** TBD ****/

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.
  ** Also, if the conflict resolution policy is REPLACE, then we
  ** can skip this test.
  */
  if( /* (recnoChng || !isUpdate) && pTab->iPKey>=0 */ recnoChng ){

    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);
      }
      sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
      jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
      switch( onError ){
        default: {
          onError = OE_Abort;
          /* Fall thru into the next case */
        }
        case OE_Rollback:
        case OE_Abort:
        case OE_Fail: {
          sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
          sqliteVdbeChangeP3(v, -1, "PRIMARY KEY must be unique", P3_STATIC);
          break;
        }









        case OE_Ignore: {

          sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
          sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
          break;
        }
      }
      contAddr = sqliteVdbeCurrentAddr(v);
      sqliteVdbeChangeP2(v, jumpInst2, contAddr);
      if( isUpdate ){
        sqliteVdbeChangeP2(v, jumpInst1, contAddr);
        sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
        sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
      }
    }
  }

  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Add the new records to the indices as we go.
  */
................................................................................
    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 */
    switch( onError ){







|







 







<
<

<
>









<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
|
>
|
|
|
|
|
|
|
|
|
|
|
<







 







>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
711
712
713
714
715
716
717


718

719
720
721
722
723
724
725
726
727
728

729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769

770
771
772
773
774
775
776
...
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
**    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.87 2003/06/04 12:23:31 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  /* Test all CHECK constraints
  */
  /**** TBD ****/

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.


  */

  if( recnoChng ){
    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( isUpdate ){
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
    jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
    switch( onError ){
      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
      }
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
        sqliteVdbeChangeP3(v, -1, "PRIMARY KEY must be unique", P3_STATIC);
        break;
      }
      case OE_Replace: {
        sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0);
        if( isUpdate ){
          sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
          sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        }
        seenReplace = 1;
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
    }
    contAddr = sqliteVdbeCurrentAddr(v);
    sqliteVdbeChangeP2(v, jumpInst2, contAddr);
    if( isUpdate ){
      sqliteVdbeChangeP2(v, jumpInst1, contAddr);
      sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
      sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

    }
  }

  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Add the new records to the indices as we go.
  */
................................................................................
    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( seenReplace ){
      if( onError==OE_Ignore ) onError = OE_Replace;
      else if( onError==OE_Fail ) 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 */
    switch( onError ){

Changes to test/insert.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
249
250
251
252
253
254
255

256















257
#    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 file is testing the INSERT statement.
#
# $Id: insert.test,v 1.12 2003/04/03 01:50:48 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  # verify that no temporary table is used to copy test1 to t4
  set x [execsql {
    EXPLAIN INSERT INTO t4 SELECT one FROM test1;
  }]
  expr {[lsearch $x OpenTemp]>0}
} {0}


















finish_test







|







 







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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 file is testing the INSERT statement.
#
# $Id: insert.test,v 1.13 2003/06/04 12:23:32 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  # verify that no temporary table is used to copy test1 to t4
  set x [execsql {
    EXPLAIN INSERT INTO t4 SELECT one FROM test1;
  }]
  expr {[lsearch $x OpenTemp]>0}
} {0}

# Ticket #334:  REPLACE statement corrupting indices.
#
do_test insert-6.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(2,3);
    SELECT b FROM t1 WHERE b=2;
  }
} {2}
do_test insert-6.2 {
  execsql {
    REPLACE INTO t1 VALUES(1,4);
    SELECT b FROM t1 WHERE b=2;
  }
} {}

finish_test