| blob | 3161abf15e46b7896861e28ace4b3a219952c79f |
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix upsert5
18 foreach {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
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}
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}
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}
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}
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}
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}
395 }
397 #--------------------------------------------------------------------------
398 reset_db
399 do_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 }
404 do_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;
409 } {1 {no such table: nosuchtable}}
411 finish_test