Debian Codegraph @

test/upsert1.test

raw
1 # 2018-04-12
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 zipfile
17
18 do_execsql_test upsert1-100 {
19 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
20 CREATE UNIQUE INDEX t1x1 ON t1(b);
21 INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
22 INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
23 SELECT * FROM t1;
24 } {1 2 0}
25 do_execsql_test upsert1-101 {
26 DELETE FROM t1;
27 INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
28 INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
29 SELECT * FROM t1;
30 } {2 3 0}
31 do_execsql_test upsert1-102 {
32 DELETE FROM t1;
33 INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
34 INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
35 SELECT * FROM t1;
36 } {3 4 0}
37 do_catchsql_test upsert1-110 {
38 INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
39 SELECT * FROM t1;
40 } {1 {no such column: x}}
41 do_catchsql_test upsert1-120 {
42 INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
43 SELECT * FROM t1;
44 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
45 breakpoint
46 do_catchsql_test upsert1-130 {
47 INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
48 SELECT * FROM t1;
49 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
50 do_execsql_test upsert1-140 {
51 DELETE FROM t1;
52 INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
53 SELECT * FROM t1;
54 } {5 6 0}
55
56 do_catchsql_test upsert1-200 {
57 DROP TABLE t1;
58 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0);
59 CREATE UNIQUE INDEX t1x1 ON t1(a+b);
60 INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING;
61 INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING;
62 SELECT * FROM t1;
63 } {0 {7 8 0}}
64 do_catchsql_test upsert1-201 {
65 INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING;
66 } {1 {UNIQUE constraint failed: index 't1x1'}}
67 do_catchsql_test upsert1-210 {
68 DELETE FROM t1;
69 INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING;
70 SELECT * FROM t1;
71 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
72
73 do_catchsql_test upsert1-300 {
74 DROP INDEX t1x1;
75 DELETE FROM t1;
76 CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
77 INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING;
78 SELECT * FROM t1;
79 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
80 do_catchsql_test upsert1-310 {
81 DELETE FROM t1;
82 INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
83 SELECT * FROM t1;
84 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
85 do_execsql_test upsert1-320 {
86 DELETE FROM t1;
87 INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
88 ON CONFLICT(b) WHERE b>10 DO NOTHING;
89 SELECT *, 'x' FROM t1 ORDER BY b, a;
90 } {1 2 0 x 3 2 0 x 4 20 0 x}
91
92 # Upsert works with count_changes=on;
93 do_execsql_test upsert1-400 {
94 DROP TABLE IF EXISTS t2;
95 CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1);
96 INSERT INTO t2(a) VALUES('one'),('two'),('three');
97 PRAGMA count_changes=ON;
98 INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four')
99 ON CONFLICT(a) DO UPDATE SET b=b+1;
100 } {1}
101 do_execsql_test upsert1-410 {
102 PRAGMA count_changes=OFF;
103 SELECT a, b FROM t2 ORDER BY a;
104 } {four 1 one 3 three 2 two 1}
105
106 # Problem found by AFL prior to any release
107 do_execsql_test upsert1-500 {
108 DROP TABLE t1;
109 CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT UNIQUE);
110 INSERT INTO t1(x,y) SELECT 1,2 WHERE true
111 ON CONFLICT(x) DO UPDATE SET y=max(t1.y,excluded.y) AND true;
112 SELECT * FROM t1;
113 } {1 2}
114
115 # 2018-07-11
116 # Ticket https://sqlite.org/src/tktview/79cad5e4b2e219dd197242e9e5f4
117 # UPSERT leads to a corrupt index.
118 #
119 do_execsql_test upsert1-600 {
120 DROP TABLE t1;
121 CREATE TABLE t1(b UNIQUE, a INT PRIMARY KEY) WITHOUT ROWID;
122 INSERT OR IGNORE INTO t1(a) VALUES('1') ON CONFLICT(a) DO NOTHING;
123 PRAGMA integrity_check;
124 } {ok}
125 do_execsql_test upsert1-610 {
126 DELETE FROM t1;
127 INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
128 PRAGMA integrity_check;
129 } {ok}
130
131 # 2018-08-14
132 # Ticket https://sqlite.org/src/info/908f001483982c43
133 # If there are multiple uniqueness contraints, the UPSERT should fire
134 # if the one constraint it targets fails, regardless of whether or not
135 # the other constraints pass or fail. In other words, the UPSERT constraint
136 # should be tested first.
137 #
138 do_execsql_test upsert1-700 {
139 DROP TABLE t1;
140 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
141 CREATE UNIQUE INDEX t1b ON t1(b);
142 CREATE UNIQUE INDEX t1e ON t1(e);
143 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
144 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
145 ON CONFLICT(e) DO UPDATE SET c=excluded.c;
146 SELECT * FROM t1;
147 } {1 2 33 4 5}
148 do_execsql_test upsert1-710 {
149 DELETE FROM t1;
150 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
151 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
152 ON CONFLICT(a) DO UPDATE SET c=excluded.c;
153 SELECT * FROM t1;
154 } {1 2 33 4 5}
155 do_execsql_test upsert1-720 {
156 DELETE FROM t1;
157 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
158 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
159 ON CONFLICT(b) DO UPDATE SET c=excluded.c;
160 SELECT * FROM t1;
161 } {1 2 33 4 5}
162 do_execsql_test upsert1-730 {
163 DROP TABLE t1;
164 CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
165 CREATE UNIQUE INDEX t1a ON t1(a);
166 CREATE UNIQUE INDEX t1b ON t1(b);
167 CREATE UNIQUE INDEX t1e ON t1(e);
168 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
169 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
170 ON CONFLICT(e) DO UPDATE SET c=excluded.c;
171 SELECT * FROM t1;
172 } {1 2 33 4 5}
173 do_execsql_test upsert1-740 {
174 DELETE FROM t1;
175 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
176 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
177 ON CONFLICT(a) DO UPDATE SET c=excluded.c;
178 SELECT * FROM t1;
179 } {1 2 33 4 5}
180 do_execsql_test upsert1-750 {
181 DELETE FROM t1;
182 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
183 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
184 ON CONFLICT(b) DO UPDATE SET c=excluded.c;
185 SELECT * FROM t1;
186 } {1 2 33 4 5}
187 do_execsql_test upsert1-760 {
188 DROP TABLE t1;
189 CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
190 CREATE UNIQUE INDEX t1a ON t1(a);
191 CREATE UNIQUE INDEX t1b ON t1(b);
192 CREATE UNIQUE INDEX t1e ON t1(e);
193 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
194 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
195 ON CONFLICT(e) DO UPDATE SET c=excluded.c;
196 SELECT * FROM t1;
197 } {1 2 33 4 5}
198 do_execsql_test upsert1-770 {
199 DELETE FROM t1;
200 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
201 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
202 ON CONFLICT(a) DO UPDATE SET c=excluded.c;
203 SELECT * FROM t1;
204 } {1 2 33 4 5}
205 do_execsql_test upsert1-780 {
206 DELETE FROM t1;
207 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
208 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
209 ON CONFLICT(b) DO UPDATE SET c=excluded.c;
210 SELECT * FROM t1;
211 } {1 2 33 4 5}
212
213 # 2019-08-30 ticket https://sqlite.org/src/info/5a3dba8104421320
214 do_execsql_test upsert1-800 {
215 DROP TABLE IF EXISTS t0;
216 CREATE TABLE t0(c0 REAL UNIQUE, c1);
217 CREATE UNIQUE INDEX test800i0 ON t0(0 || c1);
218 INSERT INTO t0(c0, c1) VALUES (1, 2), (2, 1);
219 INSERT INTO t0(c0) VALUES (1) ON CONFLICT(c0) DO UPDATE SET c1=excluded.c0;
220 PRAGMA integrity_check;
221 REINDEX;
222 } {ok}
223
224 # 2019-12-06 gramfuzz find
225 sqlite3 db :memory:
226 do_execsql_test upsert1-900 {
227 CREATE VIEW t1(a) AS SELECT 1;
228 CREATE TRIGGER t1r1 INSTEAD OF INSERT ON t1 BEGIN
229 SELECT 2;
230 END;
231 }
232 do_catchsql_test upsert1-910 {
233 INSERT INTO t1 VALUES(3) ON CONFLICT(x) DO NOTHING;
234 } {1 {cannot UPSERT a view}}
235
236 # 2019-12-26 ticket 7c13db5c3bf74001
237 reset_db
238 do_catchsql_test upsert1-1000 {
239 CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 UNIQUE) WITHOUT ROWID;
240 INSERT OR FAIL INTO t0(c2) VALUES (0), (NULL)
241 ON CONFLICT(c2) DO UPDATE SET c1 = c0;
242 } {1 {NOT NULL constraint failed: t0.c0}}
243
244 # 2021-12-29 forum post https://sqlite.org/forum/forumpost/06b16b8b29f8c8c3
245 # By Jingzhou Fu. When there is both an INTEGER PRIMARY KEY ON CONFLICT REPLACE
246 # and an upsert on a constraint other than the INTEGER PRIMARY KEY, the
247 # constraint checking logic generates invalid bytecode which might result
248 # in a NULL pointer dereference.
249 #
250 reset_db
251 do_execsql_test upsert1-1100 {
252 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE);
253 INSERT INTO t1(b) VALUES(22);
254 INSERT INTO t1 VALUES(2,22) ON CONFLICT (b) DO NOTHING;
255 SELECT * FROM t1;
256 } {1 22}
257
258 # 2023-08-17 dbsqlfuzz 9983e2c77634a8ccf33b5c91fa9982599de5f9e9
259 # Bound parameters in the ON CONFLICT clause of an UPSERT.
260 #
261 reset_db
262 do_execsql_test upsert1-1200 {
263 CREATE TABLE t1(a INT, b INT);
264 CREATE UNIQUE INDEX t1x ON t1(b+3);
265 }
266 sqlite3_db_config db ENABLE_QPSG 1
267 do_catchsql_test upsert1-1210 {
268 INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT(b+?1) DO NOTHING;
269 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
270
271 # 2024-04-11 https://sqlite.org/forum/forumpost/284955a3cd454a15
272 # Incorrect value passed into a trigger that fires as the result of
273 # an upsert.
274 #
275 reset_db
276 do_execsql_test upsert1-1300 {
277 CREATE TABLE t1(x INT, y TEXT);
278 INSERT INTO t1 VALUES
279 (11, printf('%.9000c','a')),
280 (11, printf('%.9000c','a')),
281 (33, printf('%.9000c','b')),
282 (33, printf('%.9000c','b'));
283 CREATE TABLE t2(x INT UNIQUE, y TEXT);
284 CREATE TRIGGER r1 BEFORE UPDATE ON t2 BEGIN
285 SELECT raise(ABORT,'Incorrect old.y value passed to trigger!')
286 WHERE old.y != new.y;
287 /* ^^^ This trigger will fire and cause the ABORT if the problem has
288 ** not been fixed, or if there is a regression. */
289 END;
290 INSERT INTO t2(x, y) SELECT x, y FROM t1
291 WHERE true
292 ON CONFLICT (x) DO UPDATE SET y = excluded.y;
293 } {}
294
295 finish_test