/ Check-in [a6727eef]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Clear the BTCF_ValidNKey flag set if a cursor is moved by sqlite3BtreeInsert(). Fix for [f68dc596c4].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a6727eef6d757a39ad23e5c8cbe960f5d909e5d37cb4e90bc3bdbb8bf58cd6f8
User & Date: dan 2017-05-01 18:12:56
Context
2017-05-01
18:24
Enhance "PRAGMA integrity_check" to detect duplicate rowids within a leaf page. check-in: adcad37b user: dan tags: trunk
18:12
Clear the BTCF_ValidNKey flag set if a cursor is moved by sqlite3BtreeInsert(). Fix for [f68dc596c4]. check-in: a6727eef user: dan tags: trunk
2017-04-29
19:29
Add a single testcase() macro to the subquery processing logic. check-in: 4e1df76e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/btree.c.

  8186   8186         return SQLITE_OK;
  8187   8187       }
  8188   8188       dropCell(pPage, idx, info.nSize, &rc);
  8189   8189       if( rc ) goto end_insert;
  8190   8190     }else if( loc<0 && pPage->nCell>0 ){
  8191   8191       assert( pPage->leaf );
  8192   8192       idx = ++pCur->ix;
         8193  +    pCur->curFlags &= ~BTCF_ValidNKey;
  8193   8194     }else{
  8194   8195       assert( pPage->leaf );
  8195   8196     }
  8196   8197     insertCell(pPage, idx, newCell, szNew, 0, 0, &rc);
  8197   8198     assert( pPage->nOverflow==0 || rc==SQLITE_OK );
  8198   8199     assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 );
  8199   8200   

Changes to test/conflict3.test.

    15     15   #
    16     16   # This file focuses on making sure that combinations of REPLACE,
    17     17   # IGNORE, and FAIL conflict resolution play well together.
    18     18   #
    19     19   
    20     20   set testdir [file dirname $argv0]
    21     21   source $testdir/tester.tcl
           22  +set testprefix conflict3
    22     23   
    23     24   ifcapable !conflict {
    24     25     finish_test
    25     26     return
    26     27   }
    27     28   
    28         -do_execsql_test conflict-1.1 {
           29  +do_execsql_test 1.1 {
    29     30     CREATE TABLE t1(
    30     31       a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    31     32       b UNIQUE ON CONFLICT IGNORE,
    32     33       c UNIQUE ON CONFLICT FAIL
    33     34     );
    34     35     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
    35     36     SELECT a,b,c FROM t1 ORDER BY a;
    36     37   } {1 2 3 2 3 4}
    37     38   
    38     39   # Insert a row that conflicts on column B.  The insert should be ignored.
    39     40   #
    40         -do_execsql_test conflict-1.2 {
           41  +do_execsql_test 1.2 {
    41     42     INSERT INTO t1(a,b,c) VALUES(3,2,5);
    42     43     SELECT a,b,c FROM t1 ORDER BY a;
    43     44   } {1 2 3 2 3 4}
    44     45   
    45     46   # Insert two rows where the second conflicts on C.  The first row show go
    46     47   # and and then there should be a constraint error.
    47     48   #
    48         -do_test conflict-1.3 {
           49  +do_test 1.3 {
    49     50     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
    50     51   } {1 {UNIQUE constraint failed: t1.c}}
    51         -do_execsql_test conflict-1.4 {
           52  +do_execsql_test 1.4 {
    52     53     SELECT a,b,c FROM t1 ORDER BY a;
    53     54   } {1 2 3 2 3 4 4 5 6}
    54     55   
    55     56   # Replete the tests above, but this time on a table non-INTEGER primary key.
    56     57   #
    57         -do_execsql_test conflict-2.1 {
           58  +do_execsql_test 2.1 {
    58     59     DROP TABLE t1;
    59     60     CREATE TABLE t1(
    60     61       a INT PRIMARY KEY ON CONFLICT REPLACE, 
    61     62       b UNIQUE ON CONFLICT IGNORE,
    62     63       c UNIQUE ON CONFLICT FAIL
    63     64     );
    64     65     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
    65     66     SELECT a,b,c FROM t1 ORDER BY a;
    66     67   } {1 2 3 2 3 4}
    67     68   
    68     69   # Insert a row that conflicts on column B.  The insert should be ignored.
    69     70   #
    70         -do_execsql_test conflict-2.2 {
           71  +do_execsql_test 2.2 {
    71     72     INSERT INTO t1(a,b,c) VALUES(3,2,5);
    72     73     SELECT a,b,c FROM t1 ORDER BY a;
    73     74   } {1 2 3 2 3 4}
    74     75   
    75     76   # Insert two rows where the second conflicts on C.  The first row show go
    76     77   # and and then there should be a constraint error.
    77     78   #
    78         -do_test conflict-2.3 {
           79  +do_test 2.3 {
    79     80     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
    80     81   } {1 {UNIQUE constraint failed: t1.c}}
    81         -do_execsql_test conflict-2.4 {
           82  +do_execsql_test 2.4 {
    82     83     SELECT a,b,c FROM t1 ORDER BY a;
    83     84   } {1 2 3 2 3 4 4 5 6}
    84     85   
    85     86   # Replete again on a WITHOUT ROWID table.
    86     87   #
    87         -do_execsql_test conflict-3.1 {
           88  +do_execsql_test 3.1 {
    88     89     DROP TABLE t1;
    89     90     CREATE TABLE t1(
    90     91       a INT PRIMARY KEY ON CONFLICT REPLACE, 
    91     92       b UNIQUE ON CONFLICT IGNORE,
    92     93       c UNIQUE ON CONFLICT FAIL
    93     94     ) WITHOUT ROWID;
    94     95     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
    95     96     SELECT a,b,c FROM t1 ORDER BY a;
    96     97   } {1 2 3 2 3 4}
    97     98   
    98     99   # Insert a row that conflicts on column B.  The insert should be ignored.
    99    100   #
   100         -do_execsql_test conflict-3.2 {
          101  +do_execsql_test 3.2 {
   101    102     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   102    103     SELECT a,b,c FROM t1 ORDER BY a;
   103    104   } {1 2 3 2 3 4}
   104    105   
   105    106   # Insert two rows where the second conflicts on C.  The first row show go
   106    107   # and and then there should be a constraint error.
   107    108   #
   108         -do_test conflict-3.3 {
          109  +do_test 3.3 {
   109    110     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   110    111   } {1 {UNIQUE constraint failed: t1.c}}
   111         -do_execsql_test conflict-3.4 {
          112  +do_execsql_test 3.4 {
   112    113     SELECT a,b,c FROM t1 ORDER BY a;
   113    114   } {1 2 3 2 3 4 4 5 6}
   114    115   
   115    116   # Arrange the table rows in a different order and repeat.
   116    117   #
   117         -do_execsql_test conflict-4.1 {
          118  +do_execsql_test 4.1 {
   118    119     DROP TABLE t1;
   119    120     CREATE TABLE t1(
   120    121       b UNIQUE ON CONFLICT IGNORE,
   121    122       c UNIQUE ON CONFLICT FAIL,
   122    123       a INT PRIMARY KEY ON CONFLICT REPLACE
   123    124     ) WITHOUT ROWID;
   124    125     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   125    126     SELECT a,b,c FROM t1 ORDER BY a;
   126    127   } {1 2 3 2 3 4}
   127    128   
   128    129   # Insert a row that conflicts on column B.  The insert should be ignored.
   129    130   #
   130         -do_execsql_test conflict-4.2 {
          131  +do_execsql_test 4.2 {
   131    132     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   132    133     SELECT a,b,c FROM t1 ORDER BY a;
   133    134   } {1 2 3 2 3 4}
   134    135   
   135    136   # Insert two rows where the second conflicts on C.  The first row show go
   136    137   # and and then there should be a constraint error.
   137    138   #
   138         -do_test conflict-4.3 {
          139  +do_test 4.3 {
   139    140     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   140    141   } {1 {UNIQUE constraint failed: t1.c}}
   141         -do_execsql_test conflict-4.4 {
          142  +do_execsql_test 4.4 {
   142    143     SELECT a,b,c FROM t1 ORDER BY a;
   143    144   } {1 2 3 2 3 4 4 5 6}
   144    145   
   145    146   # Arrange the table rows in a different order and repeat.
   146    147   #
   147         -do_execsql_test conflict-5.1 {
          148  +do_execsql_test 5.1 {
   148    149     DROP TABLE t1;
   149    150     CREATE TABLE t1(
   150    151       b UNIQUE ON CONFLICT IGNORE,
   151    152       a INT PRIMARY KEY ON CONFLICT REPLACE,
   152    153       c UNIQUE ON CONFLICT FAIL
   153    154     );
   154    155     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   155    156     SELECT a,b,c FROM t1 ORDER BY a;
   156    157   } {1 2 3 2 3 4}
   157    158   
   158    159   # Insert a row that conflicts on column B.  The insert should be ignored.
   159    160   #
   160         -do_execsql_test conflict-5.2 {
          161  +do_execsql_test 5.2 {
   161    162     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   162    163     SELECT a,b,c FROM t1 ORDER BY a;
   163    164   } {1 2 3 2 3 4}
   164    165   
   165    166   # Insert two rows where the second conflicts on C.  The first row show go
   166    167   # and and then there should be a constraint error.
   167    168   #
   168         -do_test conflict-5.3 {
          169  +do_test 5.3 {
   169    170     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   170    171   } {1 {UNIQUE constraint failed: t1.c}}
   171         -do_execsql_test conflict-5.4 {
          172  +do_execsql_test 5.4 {
   172    173     SELECT a,b,c FROM t1 ORDER BY a;
   173    174   } {1 2 3 2 3 4 4 5 6}
   174    175   
   175    176   # Arrange the table rows in a different order and repeat.
   176    177   #
   177         -do_execsql_test conflict-6.1 {
          178  +do_execsql_test 6.1 {
   178    179     DROP TABLE t1;
   179    180     CREATE TABLE t1(
   180    181       c UNIQUE ON CONFLICT FAIL,
   181    182       a INT PRIMARY KEY ON CONFLICT REPLACE,
   182    183       b UNIQUE ON CONFLICT IGNORE
   183    184     );
   184    185     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   185    186     SELECT a,b,c FROM t1 ORDER BY a;
   186    187   } {1 2 3 2 3 4}
   187    188   
   188    189   # Insert a row that conflicts on column B.  The insert should be ignored.
   189    190   #
   190         -do_execsql_test conflict-6.2 {
          191  +do_execsql_test 6.2 {
   191    192     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   192    193     SELECT a,b,c FROM t1 ORDER BY a;
   193    194   } {1 2 3 2 3 4}
   194    195   
   195    196   # Insert two rows where the second conflicts on C.  The first row show go
   196    197   # and and then there should be a constraint error.
   197    198   #
   198         -do_test conflict-6.3 {
          199  +do_test 6.3 {
   199    200     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   200    201   } {1 {UNIQUE constraint failed: t1.c}}
   201         -do_execsql_test conflict-6.4 {
          202  +do_execsql_test 6.4 {
   202    203     SELECT a,b,c FROM t1 ORDER BY a;
   203    204   } {1 2 3 2 3 4 4 5 6}
   204    205   
   205    206   # Change which column is the PRIMARY KEY
   206    207   #
   207         -do_execsql_test conflict-7.1 {
          208  +do_execsql_test 7.1 {
   208    209     DROP TABLE t1;
   209    210     CREATE TABLE t1(
   210    211       a UNIQUE ON CONFLICT REPLACE, 
   211    212       b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
   212    213       c UNIQUE ON CONFLICT FAIL
   213    214     );
   214    215     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   215    216     SELECT a,b,c FROM t1 ORDER BY a;
   216    217   } {1 2 3 2 3 4}
   217    218   
   218    219   # Insert a row that conflicts on column B.  The insert should be ignored.
   219    220   #
   220         -do_execsql_test conflict-7.2 {
          221  +do_execsql_test 7.2 {
   221    222     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   222    223     SELECT a,b,c FROM t1 ORDER BY a;
   223    224   } {1 2 3 2 3 4}
   224    225   
   225    226   # Insert two rows where the second conflicts on C.  The first row show go
   226    227   # and and then there should be a constraint error.
   227    228   #
   228         -do_test conflict-7.3 {
          229  +do_test 7.3 {
   229    230     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   230    231   } {1 {UNIQUE constraint failed: t1.c}}
   231         -do_execsql_test conflict-7.4 {
          232  +do_execsql_test 7.4 {
   232    233     SELECT a,b,c FROM t1 ORDER BY a;
   233    234   } {1 2 3 2 3 4 4 5 6}
   234    235   
   235    236   # Change which column is the PRIMARY KEY
   236    237   #
   237         -do_execsql_test conflict-8.1 {
          238  +do_execsql_test 8.1 {
   238    239     DROP TABLE t1;
   239    240     CREATE TABLE t1(
   240    241       a UNIQUE ON CONFLICT REPLACE, 
   241    242       b INT PRIMARY KEY ON CONFLICT IGNORE,
   242    243       c UNIQUE ON CONFLICT FAIL
   243    244     );
   244    245     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   245    246     SELECT a,b,c FROM t1 ORDER BY a;
   246    247   } {1 2 3 2 3 4}
   247    248   
   248    249   # Insert a row that conflicts on column B.  The insert should be ignored.
   249    250   #
   250         -do_execsql_test conflict-8.2 {
          251  +do_execsql_test 8.2 {
   251    252     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   252    253     SELECT a,b,c FROM t1 ORDER BY a;
   253    254   } {1 2 3 2 3 4}
   254    255   
   255    256   # Insert two rows where the second conflicts on C.  The first row show go
   256    257   # and and then there should be a constraint error.
   257    258   #
   258         -do_test conflict-8.3 {
          259  +do_test 8.3 {
   259    260     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   260    261   } {1 {UNIQUE constraint failed: t1.c}}
   261         -do_execsql_test conflict-8.4 {
          262  +do_execsql_test 8.4 {
   262    263     SELECT a,b,c FROM t1 ORDER BY a;
   263    264   } {1 2 3 2 3 4 4 5 6}
   264    265   
   265    266   # Change which column is the PRIMARY KEY
   266    267   #
   267         -do_execsql_test conflict-9.1 {
          268  +do_execsql_test 9.1 {
   268    269     DROP TABLE t1;
   269    270     CREATE TABLE t1(
   270    271       a UNIQUE ON CONFLICT REPLACE, 
   271    272       b INT PRIMARY KEY ON CONFLICT IGNORE,
   272    273       c UNIQUE ON CONFLICT FAIL
   273    274     ) WITHOUT ROWID;
   274    275     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   275    276     SELECT a,b,c FROM t1 ORDER BY a;
   276    277   } {1 2 3 2 3 4}
   277    278   
   278    279   # Insert a row that conflicts on column B.  The insert should be ignored.
   279    280   #
   280         -do_execsql_test conflict-9.2 {
          281  +do_execsql_test 9.2 {
   281    282     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   282    283     SELECT a,b,c FROM t1 ORDER BY a;
   283    284   } {1 2 3 2 3 4}
   284    285   
   285    286   # Insert two rows where the second conflicts on C.  The first row show go
   286    287   # and and then there should be a constraint error.
   287    288   #
   288         -do_test conflict-9.3 {
          289  +do_test 9.3 {
   289    290     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   290    291   } {1 {UNIQUE constraint failed: t1.c}}
   291         -do_execsql_test conflict-9.4 {
          292  +do_execsql_test 9.4 {
   292    293     SELECT a,b,c FROM t1 ORDER BY a;
   293    294   } {1 2 3 2 3 4 4 5 6}
   294    295   
   295    296   # Change which column is the PRIMARY KEY
   296    297   #
   297         -do_execsql_test conflict-10.1 {
          298  +do_execsql_test 10.1 {
   298    299     DROP TABLE t1;
   299    300     CREATE TABLE t1(
   300    301       a UNIQUE ON CONFLICT REPLACE, 
   301    302       b UNIQUE ON CONFLICT IGNORE,
   302    303       c INTEGER PRIMARY KEY ON CONFLICT FAIL
   303    304     );
   304    305     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   305    306     SELECT a,b,c FROM t1 ORDER BY a;
   306    307   } {1 2 3 2 3 4}
   307    308   
   308    309   # Insert a row that conflicts on column B.  The insert should be ignored.
   309    310   #
   310         -do_execsql_test conflict-10.2 {
          311  +do_execsql_test 10.2 {
   311    312     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   312    313     SELECT a,b,c FROM t1 ORDER BY a;
   313    314   } {1 2 3 2 3 4}
   314    315   
   315    316   # Insert two rows where the second conflicts on C.  The first row show go
   316    317   # and and then there should be a constraint error.
   317    318   #
   318         -do_test conflict-10.3 {
          319  +do_test 10.3 {
   319    320     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   320    321   } {1 {UNIQUE constraint failed: t1.c}}
   321         -do_execsql_test conflict-10.4 {
          322  +do_execsql_test 10.4 {
   322    323     SELECT a,b,c FROM t1 ORDER BY a;
   323    324   } {1 2 3 2 3 4 4 5 6}
   324    325   
   325    326   # Change which column is the PRIMARY KEY
   326    327   #
   327         -do_execsql_test conflict-11.1 {
          328  +do_execsql_test 11.1 {
   328    329     DROP TABLE t1;
   329    330     CREATE TABLE t1(
   330    331       a UNIQUE ON CONFLICT REPLACE, 
   331    332       b UNIQUE ON CONFLICT IGNORE,
   332    333       c PRIMARY KEY ON CONFLICT FAIL
   333    334     ) WITHOUT ROWID;
   334    335     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   335    336     SELECT a,b,c FROM t1 ORDER BY a;
   336    337   } {1 2 3 2 3 4}
   337    338   
   338    339   # Insert a row that conflicts on column B.  The insert should be ignored.
   339    340   #
   340         -do_execsql_test conflict-11.2 {
          341  +do_execsql_test 11.2 {
   341    342     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   342    343     SELECT a,b,c FROM t1 ORDER BY a;
   343    344   } {1 2 3 2 3 4}
   344    345   
   345    346   # Insert two rows where the second conflicts on C.  The first row show go
   346    347   # and and then there should be a constraint error.
   347    348   #
   348         -do_test conflict-11.3 {
          349  +do_test 11.3 {
   349    350     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   350    351   } {1 {UNIQUE constraint failed: t1.c}}
   351         -do_execsql_test conflict-11.4 {
          352  +do_execsql_test 11.4 {
   352    353     SELECT a,b,c FROM t1 ORDER BY a;
   353    354   } {1 2 3 2 3 4 4 5 6}
          355  +
          356  +# Check that ticket [f68dc596c4] has been fixed.
          357  +#
          358  +do_execsql_test 12.1 {
          359  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
          360  +  INSERT INTO t2 VALUES(111, '111');
          361  +}
          362  +do_execsql_test 12.2 {
          363  +  REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B');
          364  +}
          365  +do_execsql_test 12.3 {
          366  +  SELECT * FROM t2;
          367  +} {111 111B 112 112}
   354    368   
   355    369   
   356    370   finish_test