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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix upsert4 17 18foreach {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# 83set rtbl(0) {0 {}} 84set rtbl(1) {/1 .*failed.*/} 85set rtbl(2) {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} 86 87foreach {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 132foreach {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 170foreach {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 214do_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# 225foreach {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 246foreach {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# 290foreach {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 332foreach {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# 386do_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 395do_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 407finish_test 408