/ Check-in [d8eb9f8d]
Login

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

Overview
Comment:Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d8eb9f8d9b61400c7e12f01ef5c233257b03532221f7c7a8386f7ac2db439626
User & Date: dan 2018-04-20 15:34:08
Context
2018-04-20
17:02
Avoid unnecessary cursor seeking when performing an UPSERT. check-in: 693a3dcb user: drh tags: trunk
15:56
Avoid unnecessary cursor seeks during upsert processing. check-in: 7c4b6d54 user: drh tags: upsert-opt2
15:34
Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes. check-in: d8eb9f8d user: dan tags: trunk
13:18
Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT. check-in: c37f39d1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1492   1492       */
  1493   1493       assert( OE_Update>OE_Replace );
  1494   1494       assert( OE_Ignore<OE_Replace );
  1495   1495       assert( OE_Fail<OE_Replace );
  1496   1496       assert( OE_Abort<OE_Replace );
  1497   1497       assert( OE_Rollback<OE_Replace );
  1498   1498       if( onError>=OE_Replace
  1499         -     && onError!=overrideError
         1499  +     && (pUpsert || onError!=overrideError)
  1500   1500        && pTab->pIndex
  1501   1501       ){
  1502   1502         sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
  1503   1503       }
  1504   1504   
  1505   1505       if( isUpdate ){
  1506   1506         /* pkChng!=0 does not mean that the rowid has changed, only that

Changes to test/upsert4.test.

   112    112       2 "ON CONFLICT (b, c, d) DO NOTHING"                  0
   113    113       3 "ON CONFLICT (b, c COLLATE nocase, d) DO NOTHING"   2
   114    114       4 "ON CONFLICT (a) DO NOTHING"                        1
   115    115       5 "ON CONFLICT DO NOTHING"                            0
   116    116       6 "ON CONFLICT (b, c, d) WHERE a!=0 DO NOTHING"       0
   117    117       7 "ON CONFLICT (d, c, c) WHERE a!=0 DO NOTHING"       2
   118    118       8 "ON CONFLICT (b COLLATE nocase, c COLLATE nocase, d) DO NOTHING"   2
          119  +    9 "ON CONFLICT (b, c, d) WHERE b==45 DO NOTHING"      0
   119    120     } {
   120    121   
   121    122       do_catchsql_test 2.$tn.2.$tn2 "
   122    123         INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc
   123    124       " $rtbl($res)
   124    125     }
   125    126   
................................................................................
   140    141     3 { 
   141    142       CREATE TABLE abc(a INT PRIMARY KEY, x, y) WITHOUT ROWID;
   142    143       CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
   143    144     }
   144    145   } {
   145    146     reset_db
   146    147     execsql $sql
   147         -  do_execsql_test 2.$tn.1 {
          148  +  do_execsql_test 3.$tn.1 {
   148    149       INSERT INTO abc VALUES(1, 'one', 'two');
   149    150     }
   150    151   
   151    152     foreach {tn2 oc res} {
   152    153       1 "ON CONFLICT DO NOTHING"                             0
   153    154       2 "ON CONFLICT ('x' || x) DO NOTHING"                  0
   154    155       3 "ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING" 0
   155    156       4 "ON CONFLICT (('x' || x) COLLATE binary) DO NOTHING" 2
   156    157       5 "ON CONFLICT (x || 'x') DO NOTHING"                  2
   157    158       6 "ON CONFLICT ((('x' || x))) DO NOTHING"              0
   158    159     } {
   159         -    do_catchsql_test 2.$tn.2.$tn2 "
          160  +    do_catchsql_test 3.$tn.2.$tn2 "
   160    161         INSERT INTO abc VALUES(2, 'one', NULL) $oc;
   161    162       " $rtbl($res)
   162    163     }
   163    164   
   164         -  do_execsql_test 2.$tn.3 {
          165  +  do_execsql_test 3.$tn.3 {
   165    166       SELECT * FROM abc
   166    167     } {1 one two}
   167    168   }
   168    169   
   169         -do_catchsql_test 3.0 {
          170  +foreach {tn sql} {
          171  +  1 {
          172  +    CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y);
          173  +    CREATE UNIQUE INDEX abc1 ON abc(x) WHERE y>0;
          174  +    CREATE UNIQUE INDEX abc2 ON abc(y) WHERE x='xyz' COLLATE nocase;
          175  +  }
          176  +} {
          177  +  reset_db
          178  +  execsql $sql
          179  +  do_execsql_test 4.$tn.1 {
          180  +    INSERT INTO abc VALUES(1, 'one', 1);
          181  +    INSERT INTO abc VALUES(2, 'two', 2);
          182  +    INSERT INTO abc VALUES(3, 'xyz', 3);
          183  +    INSERT INTO abc VALUES(4, 'XYZ', 4);
          184  +  }
          185  +
          186  +  foreach {tn2 oc res} {
          187  +    1 "ON CONFLICT DO NOTHING"                                 0
          188  +    2 "ON CONFLICT(x) WHERE y>0 DO NOTHING"                    0
          189  +    3 "ON CONFLICT(x) DO NOTHING"                              2
          190  +    4 "ON CONFLICT(x) WHERE y>=0 DO NOTHING"                   2
          191  +    5 "ON CONFLICT(y) WHERE x='xyz' COLLATE nocase DO NOTHING" 1
          192  +  } {
          193  +    do_catchsql_test 4.$tn.2.$tn2 "
          194  +      INSERT INTO abc VALUES(5, 'one', 10) $oc
          195  +    " $rtbl($res)
          196  +  }
          197  +
          198  +  do_execsql_test 4.$tn.3 {
          199  +    SELECT * FROM abc
          200  +  } {1 one 1 2 two 2 3 xyz 3 4 XYZ 4}
          201  +
          202  +  foreach {tn2 oc res} {
          203  +    1 "ON CONFLICT DO NOTHING"                                 0
          204  +    2 "ON CONFLICT(y) WHERE x='xyz' COLLATE nocase DO NOTHING" 0
          205  +    3 "ON CONFLICT(y) WHERE x='xyz' COLLATE binary DO NOTHING" 2
          206  +    4 "ON CONFLICT(x) WHERE y>0 DO NOTHING"                    1
          207  +  } {
          208  +    do_catchsql_test 4.$tn.2.$tn2 "
          209  +      INSERT INTO abc VALUES(5, 'xYz', 3) $oc
          210  +    " $rtbl($res)
          211  +  }
          212  +}
          213  +
          214  +do_catchsql_test 5.0 {
   170    215     CREATE TABLE w1(a INT PRIMARY KEY, x, y);
   171    216     CREATE UNIQUE INDEX w1expr ON w1(('x' || x));
   172    217     INSERT INTO w1 VALUES(2, 'one', NULL)
   173    218       ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING;
   174    219   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
          220  +
          221  +#-------------------------------------------------------------------------
          222  +#
          223  +do_execsql_test 6.0 {
          224  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
          225  +  INSERT INTO t1 VALUES(1, 1, 'one');
          226  +  INSERT INTO t1 VALUES(2, 2, 'two');
          227  +  INSERT OR REPLACE INTO t1 VALUES(1, 2, 'two') ON CONFLICT(b) DO NOTHING;
          228  +  PRAGMA integrity_check;
          229  +} {ok}
   175    230   
   176    231   finish_test
   177    232