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 #
 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}
 230
 231 finish_test
 232