test/upsert4.test

   1 # 2018-04-17
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # Test cases for UPSERT
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix upsert4
  17
  18 foreach {tn sql} {
  19   1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) }
  20   2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) }
  21   3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID}
  22 } {
  23   reset_db
  24   execsql $sql
  25
  26   do_execsql_test 1.$tn.0 {
  27     INSERT INTO t1 VALUES(1, NULL, 'one');
  28     INSERT INTO t1 VALUES(2, NULL, 'two');
  29     INSERT INTO t1 VALUES(3, NULL, 'three');
  30   }
  31
  32   do_execsql_test 1.$tn.1 {
  33     INSERT INTO t1 VALUES(1, NULL, 'xyz') ON CONFLICT DO NOTHING;
  34     SELECT * FROM t1;
  35   } {
  36     1 {} one 2 {} two 3 {} three
  37   }
  38
  39   do_execsql_test 1.$tn.2 {
  40     INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT DO NOTHING;
  41     SELECT * FROM t1;
  42   } {
  43     1 {} one 2 {} two 3 {} three
  44   }
  45
  46   do_execsql_test 1.$tn.3 {
  47     INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT (c) DO UPDATE SET b = 1;
  48     SELECT * FROM t1;
  49   } {
  50     1 {} one 2 1 two 3 {} three
  51   }
  52
  53   do_execsql_test 1.$tn.4 {
  54     INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET b=2;
  55     SELECT * FROM t1;
  56   } {1 {} one 2 2 two 3 {} three}
  57
  58   do_catchsql_test 1.$tn.5 {
  59     INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a)
  60       DO UPDATE SET c = 'one';
  61   } {1 {UNIQUE constraint failed: t1.c}}
  62
  63   do_execsql_test 1.$tn.6 {
  64     SELECT * FROM t1;
  65   } {1 {} one 2 2 two 3 {} three}
  66
  67   do_execsql_test 1.$tn.7 {
  68     INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a)
  69       DO UPDATE SET (b, c) = (SELECT 'x', 'y');
  70     SELECT * FROM t1;
  71   } {1 {} one 2 x y 3 {} three}
  72
  73   do_execsql_test 1.$tn.8 {
  74     INSERT INTO t1 VALUES(1, NULL, NULL) ON CONFLICT (a)
  75       DO UPDATE SET (c, a) = ('four', 4);
  76     SELECT * FROM t1 ORDER BY 1;
  77   } {2 x y 3 {} three 4 {} four}
  78 }
  79
  80 #-------------------------------------------------------------------------
  81 # Test target analysis.
  82 #
  83 set rtbl(0) {0 {}}
  84 set rtbl(1) {/1 .*failed.*/}
  85 set rtbl(2) {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
  86
  87 foreach {tn sql} {
  88   1 {
  89       CREATE TABLE xyz(a INTEGER PRIMARY KEY, b, c, d);
  90       CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase);
  91   }
  92
  93   2 {
  94       CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d);
  95       CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase);
  96   }
  97
  98   3 {
  99       CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID;
 100       CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase);
 101   }
 102 } {
 103   reset_db
 104   execsql $sql
 105   do_execsql_test 2.$tn.1 {
 106     INSERT INTO xyz VALUES(10, 1, 1, 'one');
 107   }
 108
 109
 110   foreach {tn2 oc res} {
 111     1 "ON CONFLICT (b COLLATE nocase, c, d) DO NOTHING"   0
 112     2 "ON CONFLICT (b, c, d) DO NOTHING"                  0
 113     3 "ON CONFLICT (b, c COLLATE nocase, d) DO NOTHING"   2
 114     4 "ON CONFLICT (a) DO NOTHING"                        1
 115     5 "ON CONFLICT DO NOTHING"                            0
 116     6 "ON CONFLICT (b, c, d) WHERE a!=0 DO NOTHING"       0
 117     7 "ON CONFLICT (d, c, c) WHERE a!=0 DO NOTHING"       2
 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
 120   } {
 121
 122     do_catchsql_test 2.$tn.2.$tn2 "
 123       INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc
 124     " $rtbl($res)
 125   }
 126
 127   do_execsql_test 2.$tn.3 {
 128     SELECT * FROM xyz;
 129   } {10 1 1 one}
 130 }
 131
 132 foreach {tn sql} {
 133   1 {
 134     CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y);
 135     CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
 136   }
 137   2 {
 138     CREATE TABLE abc(a INT PRIMARY KEY, x, y);
 139     CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
 140   }
 141   3 {
 142     CREATE TABLE abc(a INT PRIMARY KEY, x, y) WITHOUT ROWID;
 143     CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
 144   }
 145 } {
 146   reset_db
 147   execsql $sql
 148   do_execsql_test 3.$tn.1 {
 149     INSERT INTO abc VALUES(1, 'one', 'two');
 150   }
 151
 152   foreach {tn2 oc res} {
 153     1 "ON CONFLICT DO NOTHING"                             0
 154     2 "ON CONFLICT ('x' || x) DO NOTHING"                  0
 155     3 "ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING" 0
 156     4 "ON CONFLICT (('x' || x) COLLATE binary) DO NOTHING" 2
 157     5 "ON CONFLICT (x || 'x') DO NOTHING"                  2
 158     6 "ON CONFLICT ((('x' || x))) DO NOTHING"              0
 159   } {
 160     do_catchsql_test 3.$tn.2.$tn2 "
 161       INSERT INTO abc VALUES(2, 'one', NULL) $oc;
 162     " $rtbl($res)
 163   }
 164
 165   do_execsql_test 3.$tn.3 {
 166     SELECT * FROM abc
 167   } {1 one two}
 168 }
 169
 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 {
 215   CREATE TABLE w1(a INT PRIMARY KEY, x, y);
 216   CREATE UNIQUE INDEX w1expr ON w1(('x' || x));
 217   INSERT INTO w1 VALUES(2, 'one', NULL)
 218     ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING;
 219 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
 220
 221 #-------------------------------------------------------------------------
 222 # Test that ON CONFLICT constraint processing occurs before any REPLACE
 223 # constraint processing.
 224 #
 225 foreach {tn sql} {
 226   1 {
 227     CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
 228   }
 229   2 {
 230     CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE, c);
 231   }
 232   3 {
 233     CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE, c) WITHOUT ROWID;
 234   }
 235 } {
 236   reset_db
 237   execsql $sql
 238   do_execsql_test 6.1.$tn {
 239     INSERT INTO t1 VALUES(1, 1, 'one');
 240     INSERT INTO t1 VALUES(2, 2, 'two');
 241     INSERT OR REPLACE INTO t1 VALUES(1, 2, 'two') ON CONFLICT(b) DO NOTHING;
 242     PRAGMA integrity_check;
 243   } {ok}
 244 }
 245
 246 foreach {tn sql} {
 247   1 {
 248     CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
 249   }
 250 } {
 251   reset_db
 252   execsql $sql
 253
 254   do_execsql_test 6.2.$tn.1 {
 255     INSERT INTO t1 VALUES(1, 1, 1);
 256     INSERT INTO t1 VALUES(2, 2, 2);
 257   }
 258
 259   do_execsql_test 6.2.$tn.2 {
 260     INSERT OR REPLACE INTO t1 VALUES(3, 1, 1) ON CONFLICT(b) DO NOTHING;
 261     SELECT * FROM t1;
 262     PRAGMA integrity_check;
 263   } {1 1 1 2 2 2 ok}
 264
 265   do_execsql_test 6.2.$tn.3 {
 266     INSERT OR REPLACE INTO t1 VALUES(3, 2, 2) ON CONFLICT(c) DO NOTHING;
 267     SELECT * FROM t1;
 268     PRAGMA integrity_check;
 269   } {1 1 1 2 2 2 ok}
 270
 271   do_execsql_test 6.2.$tn.2 {
 272     INSERT OR REPLACE INTO t1 VALUES(3, 1, 1) ON CONFLICT(b)
 273       DO UPDATE SET b=b||'x';
 274     SELECT * FROM t1;
 275     PRAGMA integrity_check;
 276   } {1 1x 1 2 2 2 ok}
 277
 278   do_execsql_test 6.2.$tn.2 {
 279     INSERT OR REPLACE INTO t1 VALUES(3, 2, 2) ON CONFLICT(c)
 280       DO UPDATE SET c=c||'x';
 281     SELECT * FROM t1;
 282     PRAGMA integrity_check;
 283   } {1 1x 1 2 2 2x ok}
 284 }
 285
 286 #-------------------------------------------------------------------------
 287 # Test references to "excluded". And using an alias in an INSERT
 288 # statement.
 289 #
 290 foreach {tn sql} {
 291   1 {
 292     CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y));
 293     CREATE UNIQUE INDEX zz ON t1(z);
 294   }
 295   2 {
 296     CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y)) WITHOUT ROWID;
 297     CREATE UNIQUE INDEX zz ON t1(z);
 298   }
 299 } {
 300   reset_db
 301   execsql $sql
 302   do_execsql_test 7.$tn.0 {
 303     INSERT INTO t1 VALUES('a', 1, 1, 1);
 304     INSERT INTO t1 VALUES('b', 2, 2, 2);
 305   }
 306
 307   do_execsql_test 7.$tn.1 {
 308     INSERT INTO t1 VALUES('c', 3, 3, 1) ON CONFLICT(z)
 309       DO UPDATE SET w = excluded.w;
 310     SELECT * FROM t1;
 311   } {c 1 1 1 b 2 2 2}
 312
 313   do_execsql_test 7.$tn.2 {
 314     INSERT INTO t1 VALUES('c', 2, 2, 3) ON CONFLICT(y, x)
 315       DO UPDATE SET w = w||w;
 316     SELECT * FROM t1;
 317   } {c 1 1 1 bb 2 2 2}
 318
 319   do_execsql_test 7.$tn.3 {
 320     INSERT INTO t1 VALUES('c', 2, 2, 3) ON CONFLICT(y, x)
 321       DO UPDATE SET w = w||t1.w;
 322     SELECT * FROM t1;
 323   } {c 1 1 1 bbbb 2 2 2}
 324
 325   do_execsql_test 7.$tn.4 {
 326     INSERT INTO t1 AS tbl VALUES('c', 2, 2, 3) ON CONFLICT(y, x)
 327       DO UPDATE SET w = w||tbl.w;
 328     SELECT * FROM t1;
 329   } {c 1 1 1 bbbbbbbb 2 2 2}
 330 }
 331
 332 foreach {tn sql} {
 333   1 {
 334     CREATE TABLE excluded(w, x INTEGER, 'a b', z, PRIMARY KEY(x, 'a b'));
 335     CREATE UNIQUE INDEX zz ON excluded(z);
 336     CREATE INDEX zz2 ON excluded(z);
 337   }
 338   2 {
 339     CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b')) WITHOUT ROWID;
 340     CREATE UNIQUE INDEX zz ON excluded(z);
 341     CREATE INDEX zz2 ON excluded(z);
 342   }
 343 } {
 344   reset_db
 345   execsql $sql
 346   do_execsql_test 8.$tn.0 {
 347     INSERT INTO excluded VALUES('a', 1, 1, 1);
 348     INSERT INTO excluded VALUES('b', 2, 2, 2);
 349   }
 350
 351   # Note: An error in Postgres: "table reference "excluded" is ambiguous".
 352   #
 353   do_execsql_test 8.$tn.1 {
 354     INSERT INTO excluded VALUES('hello', 1, 1, NULL) ON CONFLICT(x, "a b")
 355       DO UPDATE SET w=excluded.w;
 356     SELECT * FROM excluded;
 357   } {a 1 1 1 b 2 2 2}
 358
 359   do_execsql_test 8.$tn.2 {
 360     INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
 361       DO UPDATE SET w=excluded.w;
 362     SELECT * FROM excluded;
 363   } {hello 1 1 1 b 2 2 2}
 364
 365   do_execsql_test 8.$tn.3 {
 366     INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
 367       DO UPDATE SET w=w||w WHERE excluded.w!='hello';
 368     SELECT * FROM excluded;
 369   } {hello 1 1 1 b 2 2 2}
 370
 371   do_execsql_test 8.$tn.4 {
 372     INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
 373       DO UPDATE SET w=w||w WHERE excluded.x=1;
 374     SELECT * FROM excluded;
 375   } {hellohello 1 1 1 b 2 2 2}
 376
 377   do_catchsql_test 8.$tn.5 {
 378     INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL)
 379       ON CONFLICT(x, [a b]) WHERE y=1
 380       DO UPDATE SET w=w||w WHERE excluded.x=1;
 381   } {1 {no such column: y}}
 382 }
 383
 384 #--------------------------------------------------------------------------
 385 #
 386 do_execsql_test 9.0 {
 387   CREATE TABLE v(x INTEGER);
 388   CREATE TABLE hist(x INTEGER PRIMARY KEY, cnt INTEGER);
 389   CREATE TRIGGER vt AFTER INSERT ON v BEGIN
 390     INSERT INTO hist VALUES(new.x, 1) ON CONFLICT(x) DO
 391       UPDATE SET cnt=cnt+1;
 392   END;
 393 }
 394
 395 do_execsql_test 9.1 {
 396   INSERT INTO v VALUES(1), (4), (1), (5), (5), (8), (9), (1);
 397   SELECT * FROM hist;
 398 } {
 399   1 3
 400   4 1
 401   5 2
 402   8 1
 403   9 1
 404 }
 405
 406
 407 finish_test