1# 2020-12-11 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 generalized UPSERT 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix upsert5 17 18foreach {tn sql} { 19 1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) } 20 2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) } 21 3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) WITHOUT ROWID} 22 4 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INTEGER PRIMARY KEY, b) } 23 5 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INT PRIMARY KEY, b) } 24 6 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INT PRIMARY KEY, b) WITHOUT ROWID} 25} { 26 reset_db 27 execsql $sql 28 29 do_execsql_test 1.$tn.100 { 30 DELETE FROM t1; 31 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 32 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,3,4,5) 33 ON CONFLICT(a) DO UPDATE SET b='a' 34 ON CONFLICT(c) DO UPDATE SET b='c' 35 ON CONFLICT(d) DO UPDATE SET b='d' 36 ON CONFLICT(e) DO UPDATE SET b='e'; 37 SELECT a,b,c,d,e FROM t1; 38 } {1 a 3 4 5} 39 do_execsql_test 1.$tn.101 { 40 DELETE FROM t1; 41 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 42 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,4,5) 43 ON CONFLICT(a) DO UPDATE SET b='a' 44 ON CONFLICT(c) DO UPDATE SET b='c' 45 ON CONFLICT(d) DO UPDATE SET b='d' 46 ON CONFLICT(e) DO UPDATE SET b='e'; 47 SELECT a,b,c,d,e FROM t1; 48 } {1 c 3 4 5} 49 do_execsql_test 1.$tn.102 { 50 DELETE FROM t1; 51 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 52 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,4,5) 53 ON CONFLICT(a) DO UPDATE SET b='a' 54 ON CONFLICT(c) DO UPDATE SET b='c' 55 ON CONFLICT(d) DO UPDATE SET b='d' 56 ON CONFLICT(e) DO UPDATE SET b='e'; 57 SELECT a,b,c,d,e FROM t1; 58 } {1 d 3 4 5} 59 do_execsql_test 1.$tn.103 { 60 DELETE FROM t1; 61 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 62 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 63 ON CONFLICT(a) DO UPDATE SET b='a' 64 ON CONFLICT(c) DO UPDATE SET b='c' 65 ON CONFLICT(d) DO UPDATE SET b='d' 66 ON CONFLICT(e) DO UPDATE SET b='e'; 67 SELECT a,b,c,d,e FROM t1; 68 } {1 e 3 4 5} 69 do_execsql_test 1.$tn.200 { 70 DELETE FROM t1; 71 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 72 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 73 ON CONFLICT(c) DO UPDATE SET b='c' 74 ON CONFLICT(a) DO UPDATE SET b='a' 75 ON CONFLICT(d) DO UPDATE SET b='d' 76 ON CONFLICT(e) DO UPDATE SET b='e'; 77 SELECT a,b,c,d,e FROM t1; 78 } {1 a 3 4 5} 79 do_execsql_test 1.$tn.201 { 80 DELETE FROM t1; 81 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 82 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,3,94,95) 83 ON CONFLICT(c) DO UPDATE SET b='c' 84 ON CONFLICT(a) DO UPDATE SET b='a' 85 ON CONFLICT(d) DO UPDATE SET b='d' 86 ON CONFLICT(e) DO UPDATE SET b='e'; 87 SELECT a,b,c,d,e FROM t1; 88 } {1 c 3 4 5} 89 do_execsql_test 1.$tn.202 { 90 DELETE FROM t1; 91 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 92 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,3,4,5) 93 ON CONFLICT(c) DO UPDATE SET b='c' 94 ON CONFLICT(a) DO UPDATE SET b='a' 95 ON CONFLICT(d) DO UPDATE SET b='d' 96 ON CONFLICT(e) DO UPDATE SET b='e'; 97 SELECT a,b,c,d,e FROM t1; 98 } {1 c 3 4 5} 99 do_execsql_test 1.$tn.203 { 100 DELETE FROM t1; 101 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 102 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,5) 103 ON CONFLICT(c) DO UPDATE SET b='c' 104 ON CONFLICT(a) DO UPDATE SET b='a' 105 ON CONFLICT(d) DO UPDATE SET b='d' 106 ON CONFLICT(e) DO UPDATE SET b='e'; 107 SELECT a,b,c,d,e FROM t1; 108 } {1 a 3 4 5} 109 do_execsql_test 1.$tn.204 { 110 DELETE FROM t1; 111 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 112 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,95) 113 ON CONFLICT(c) DO UPDATE SET b='c' 114 ON CONFLICT(a) DO UPDATE SET b='a' 115 ON CONFLICT(d) DO UPDATE SET b='d' 116 ON CONFLICT(e) DO UPDATE SET b='e'; 117 SELECT a,b,c,d,e FROM t1; 118 } {1 a 3 4 5} 119 do_execsql_test 1.$tn.210 { 120 DELETE FROM t1; 121 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 122 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 123 ON CONFLICT(c) DO UPDATE SET b='c' 124 ON CONFLICT(d) DO UPDATE SET b='d' 125 ON CONFLICT(a) DO UPDATE SET b='a' 126 ON CONFLICT(e) DO UPDATE SET b='e'; 127 SELECT a,b,c,d,e FROM t1; 128 } {1 a 3 4 5} 129 do_execsql_test 1.$tn.211 { 130 DELETE FROM t1; 131 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 132 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,95) 133 ON CONFLICT(c) DO UPDATE SET b='c' 134 ON CONFLICT(d) DO UPDATE SET b='d' 135 ON CONFLICT(a) DO UPDATE SET b='a' 136 ON CONFLICT(e) DO UPDATE SET b='e'; 137 SELECT a,b,c,d,e FROM t1; 138 } {1 d 3 4 5} 139 do_execsql_test 1.$tn.212 { 140 DELETE FROM t1; 141 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 142 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,5) 143 ON CONFLICT(c) DO UPDATE SET b='c' 144 ON CONFLICT(d) DO UPDATE SET b='d' 145 ON CONFLICT(a) DO UPDATE SET b='a' 146 ON CONFLICT(e) DO UPDATE SET b='e'; 147 SELECT a,b,c,d,e FROM t1; 148 } {1 a 3 4 5} 149 do_execsql_test 1.$tn.213 { 150 DELETE FROM t1; 151 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 152 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 153 ON CONFLICT(c) DO UPDATE SET b='c' 154 ON CONFLICT(d) DO UPDATE SET b='d' 155 ON CONFLICT(a) DO UPDATE SET b='a' 156 ON CONFLICT(e) DO UPDATE SET b='e'; 157 SELECT a,b,c,d,e FROM t1; 158 } {1 e 3 4 5} 159 do_execsql_test 1.$tn.214 { 160 DELETE FROM t1; 161 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 162 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 163 ON CONFLICT(c) DO UPDATE SET b='c' 164 ON CONFLICT(d) DO UPDATE SET b='d' 165 ON CONFLICT(e) DO UPDATE SET b='e' 166 ON CONFLICT(a) DO UPDATE SET b='a'; 167 SELECT a,b,c,d,e FROM t1; 168 } {1 e 3 4 5} 169 do_execsql_test 1.$tn.215 { 170 DELETE FROM t1; 171 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 172 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,5) 173 ON CONFLICT(c) DO UPDATE SET b='c' 174 ON CONFLICT(d) DO UPDATE SET b='d' 175 ON CONFLICT(e) DO UPDATE SET b='e' 176 ON CONFLICT(a) DO UPDATE SET b='a'; 177 SELECT a,b,c,d,e FROM t1; 178 } {1 e 3 4 5} 179 do_execsql_test 1.$tn.216 { 180 DELETE FROM t1; 181 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 182 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 183 ON CONFLICT(c) DO UPDATE SET b='c' 184 ON CONFLICT(d) DO UPDATE SET b='d' 185 ON CONFLICT(e) DO UPDATE SET b='e' 186 ON CONFLICT(a) DO UPDATE SET b='a'; 187 SELECT a,b,c,d,e FROM t1; 188 } {1 a 3 4 5} 189 190 do_execsql_test 1.$tn.300 { 191 DELETE FROM t1; 192 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 193 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 194 ON CONFLICT(c) DO UPDATE SET b='c' 195 ON CONFLICT(d) DO UPDATE SET b='d' 196 ON CONFLICT(a) DO UPDATE SET b='a1' 197 ON CONFLICT(a) DO UPDATE SET b='a2' 198 ON CONFLICT(a) DO UPDATE SET b='a3' 199 ON CONFLICT(a) DO UPDATE SET b='a4' 200 ON CONFLICT(a) DO UPDATE SET b='a5' 201 ON CONFLICT(e) DO UPDATE SET b='e'; 202 SELECT a,b,c,d,e FROM t1; 203 } {1 a1 3 4 5} 204 do_execsql_test 1.$tn.301 { 205 DELETE FROM t1; 206 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 207 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 208 ON CONFLICT(c) DO UPDATE SET b='c' 209 ON CONFLICT(d) DO UPDATE SET b='d' 210 ON CONFLICT(a) DO UPDATE SET b='a1' 211 ON CONFLICT(a) DO UPDATE SET b='a2' 212 ON CONFLICT(a) DO UPDATE SET b='a3' 213 ON CONFLICT(a) DO UPDATE SET b='a4' 214 ON CONFLICT(a) DO UPDATE SET b='a5' 215 ON CONFLICT(e) DO UPDATE SET b='e'; 216 SELECT a,b,c,d,e FROM t1; 217 } {1 e 3 4 5} 218 219 do_execsql_test 1.$tn.400 { 220 DELETE FROM t1; 221 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 222 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 223 ON CONFLICT(c) DO UPDATE SET b='c' 224 ON CONFLICT(d) DO UPDATE SET b='d' 225 ON CONFLICT DO UPDATE set b='x'; 226 SELECT a,b,c,d,e FROM t1; 227 } {1 x 3 4 5} 228 do_execsql_test 1.$tn.401 { 229 DELETE FROM t1; 230 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 231 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 232 ON CONFLICT(c) DO UPDATE SET b='c' 233 ON CONFLICT(d) DO UPDATE SET b='d' 234 ON CONFLICT DO UPDATE set b='x'; 235 SELECT a,b,c,d,e FROM t1; 236 } {1 x 3 4 5} 237 do_execsql_test 1.$tn.402 { 238 DELETE FROM t1; 239 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 240 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 241 ON CONFLICT(c) DO UPDATE SET b='c' 242 ON CONFLICT(d) DO UPDATE SET b='d' 243 ON CONFLICT DO UPDATE set b='x'; 244 SELECT a,b,c,d,e FROM t1; 245 } {1 x 3 4 5} 246 do_execsql_test 1.$tn.403 { 247 DELETE FROM t1; 248 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 249 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) 250 ON CONFLICT(c) DO UPDATE SET b='c' 251 ON CONFLICT(d) DO UPDATE SET b='d' 252 ON CONFLICT DO UPDATE set b='x'; 253 SELECT a,b,c,d,e FROM t1; 254 } {1 c 3 4 5} 255 do_execsql_test 1.$tn.404 { 256 DELETE FROM t1; 257 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 258 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,4,95) 259 ON CONFLICT(c) DO UPDATE SET b='c' 260 ON CONFLICT(d) DO UPDATE SET b='d' 261 ON CONFLICT DO UPDATE set b='x'; 262 SELECT a,b,c,d,e FROM t1; 263 } {1 c 3 4 5} 264 do_execsql_test 1.$tn.405 { 265 DELETE FROM t1; 266 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 267 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,5) 268 ON CONFLICT(c) DO UPDATE SET b='c' 269 ON CONFLICT(d) DO UPDATE SET b='d' 270 ON CONFLICT DO UPDATE set b='x'; 271 SELECT a,b,c,d,e FROM t1; 272 } {1 d 3 4 5} 273 274 do_execsql_test 1.$tn.410 { 275 DELETE FROM t1; 276 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 277 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 278 ON CONFLICT DO UPDATE set b='x'; 279 SELECT a,b,c,d,e FROM t1; 280 } {1 x 3 4 5} 281 do_execsql_test 1.$tn.411 { 282 DELETE FROM t1; 283 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 284 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 285 ON CONFLICT DO UPDATE set b='x'; 286 SELECT a,b,c,d,e FROM t1; 287 } {1 x 3 4 5} 288 do_execsql_test 1.$tn.412 { 289 DELETE FROM t1; 290 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 291 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,4,95) 292 ON CONFLICT DO UPDATE set b='x'; 293 SELECT a,b,c,d,e FROM t1; 294 } {1 x 3 4 5} 295 do_execsql_test 1.$tn.413 { 296 DELETE FROM t1; 297 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 298 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) 299 ON CONFLICT DO UPDATE set b='x'; 300 SELECT a,b,c,d,e FROM t1; 301 } {1 x 3 4 5} 302 303 do_execsql_test 1.$tn.420 { 304 DELETE FROM t1; 305 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 306 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 307 ON CONFLICT(c) DO NOTHING 308 ON CONFLICT(d) DO NOTHING 309 ON CONFLICT DO UPDATE set b='x'; 310 SELECT a,b,c,d,e FROM t1; 311 } {1 x 3 4 5} 312 do_execsql_test 1.$tn.421 { 313 DELETE FROM t1; 314 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 315 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 316 ON CONFLICT(c) DO NOTHING 317 ON CONFLICT(d) DO NOTHING 318 ON CONFLICT DO UPDATE set b='x'; 319 SELECT a,b,c,d,e FROM t1; 320 } {1 x 3 4 5} 321 do_execsql_test 1.$tn.422 { 322 DELETE FROM t1; 323 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 324 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,4,95) 325 ON CONFLICT(c) DO NOTHING 326 ON CONFLICT(d) DO NOTHING 327 ON CONFLICT DO UPDATE set b='x'; 328 SELECT a,b,c,d,e FROM t1; 329 } {1 2 3 4 5} 330 do_execsql_test 1.$tn.423 { 331 DELETE FROM t1; 332 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 333 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) 334 ON CONFLICT(c) DO NOTHING 335 ON CONFLICT(d) DO NOTHING 336 ON CONFLICT DO UPDATE set b='x'; 337 SELECT a,b,c,d,e FROM t1; 338 } {1 2 3 4 5} 339 340 do_execsql_test 1.$tn.500 { 341 DELETE FROM t1; 342 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 343 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 344 ON CONFLICT(c) DO UPDATE SET b='c' 345 ON CONFLICT(d) DO UPDATE SET b='d' 346 ON CONFLICT DO NOTHING; 347 SELECT a,b,c,d,e FROM t1; 348 } {1 2 3 4 5} 349 do_execsql_test 1.$tn.501 { 350 DELETE FROM t1; 351 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 352 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) 353 ON CONFLICT(c) DO UPDATE SET b='c' 354 ON CONFLICT(d) DO UPDATE SET b='d' 355 ON CONFLICT DO NOTHING; 356 SELECT a,b,c,d,e FROM t1; 357 } {1 2 3 4 5} 358 do_execsql_test 1.$tn.502 { 359 DELETE FROM t1; 360 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 361 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) 362 ON CONFLICT(c) DO UPDATE SET b='c' 363 ON CONFLICT(d) DO UPDATE SET b='d' 364 ON CONFLICT DO NOTHING; 365 SELECT a,b,c,d,e FROM t1; 366 } {1 2 3 4 5} 367 do_execsql_test 1.$tn.503 { 368 DELETE FROM t1; 369 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 370 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) 371 ON CONFLICT(c) DO UPDATE SET b='c' 372 ON CONFLICT(d) DO UPDATE SET b='d' 373 ON CONFLICT DO NOTHING; 374 SELECT a,b,c,d,e FROM t1; 375 } {1 c 3 4 5} 376 do_execsql_test 1.$tn.504 { 377 DELETE FROM t1; 378 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 379 INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,4,95) 380 ON CONFLICT(c) DO UPDATE SET b='c' 381 ON CONFLICT(d) DO UPDATE SET b='d' 382 ON CONFLICT DO NOTHING; 383 SELECT a,b,c,d,e FROM t1; 384 } {1 c 3 4 5} 385 do_execsql_test 1.$tn.505 { 386 DELETE FROM t1; 387 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 388 INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,5) 389 ON CONFLICT(c) DO UPDATE SET b='c' 390 ON CONFLICT(d) DO UPDATE SET b='d' 391 ON CONFLICT DO NOTHING; 392 SELECT a,b,c,d,e FROM t1; 393 } {1 d 3 4 5} 394 395} 396 397#-------------------------------------------------------------------------- 398reset_db 399do_execsql_test 2.0 { 400 CREATE TABLE t2(a, b, c REAL, d, e, PRIMARY KEY(a,b)) WITHOUT ROWID; 401 CREATE UNIQUE INDEX t2c ON t2(c); 402} 403 404do_catchsql_test 2.1 { 405 INSERT INTO t2(a,b,c,e,d) VALUES(1,2,3,4,5) 406 ON CONFLICT(c) DO UPDATE SET b='' 407 ON CONFLICT((SELECT t2 FROM nosuchtable)) DO NOTHING; 408 409} {1 {no such table: nosuchtable}} 410 411finish_test 412