wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 September 18 |
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 | # The majority of this file implements tests to verify that the "testable |
||
13 | # statements" in the lang_insert.html document are correct. |
||
14 | # |
||
15 | # Also, it contains tests to verify the statements in (the very short) |
||
16 | # lang_replace.html. |
||
17 | # |
||
18 | set testdir [file dirname $argv0] |
||
19 | source $testdir/tester.tcl |
||
20 | |||
21 | # Organization of tests: |
||
22 | # |
||
23 | # e_insert-0.*: Test the syntax diagram. |
||
24 | # |
||
25 | # e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)". |
||
26 | # |
||
27 | # e_insert-2.*: Test statements of the form "INSERT ... SELECT ...". |
||
28 | # |
||
29 | # e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES". |
||
30 | # |
||
31 | # e_insert-4.*: Test statements regarding the conflict clause. |
||
32 | # |
||
33 | # e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES" |
||
34 | # syntaxes do not work in trigger bodies. |
||
35 | # |
||
36 | |||
37 | do_execsql_test e_insert-0.0 { |
||
38 | CREATE TABLE a1(a, b); |
||
39 | CREATE TABLE a2(a, b, c DEFAULT 'xyz'); |
||
40 | CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z); |
||
41 | CREATE TABLE a4(c UNIQUE, d); |
||
42 | } {} |
||
43 | |||
44 | proc do_insert_tests {args} { |
||
45 | uplevel do_select_tests $args |
||
46 | } |
||
47 | |||
48 | # EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt |
||
49 | # |
||
50 | do_insert_tests e_insert-0 { |
||
51 | 1 "INSERT INTO a1 DEFAULT VALUES" {} |
||
52 | 2 "INSERT INTO main.a1 DEFAULT VALUES" {} |
||
53 | 3 "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES" {} |
||
54 | 4 "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES" {} |
||
55 | 5 "INSERT OR ABORT INTO main.a1 DEFAULT VALUES" {} |
||
56 | 6 "INSERT OR ABORT INTO a1 DEFAULT VALUES" {} |
||
57 | 7 "INSERT OR REPLACE INTO main.a1 DEFAULT VALUES" {} |
||
58 | 8 "INSERT OR REPLACE INTO a1 DEFAULT VALUES" {} |
||
59 | 9 "INSERT OR FAIL INTO main.a1 DEFAULT VALUES" {} |
||
60 | 10 "INSERT OR FAIL INTO a1 DEFAULT VALUES" {} |
||
61 | 11 "INSERT OR FAIL INTO main.a1 DEFAULT VALUES" {} |
||
62 | 12 "INSERT OR IGNORE INTO a1 DEFAULT VALUES" {} |
||
63 | 13 "REPLACE INTO a1 DEFAULT VALUES" {} |
||
64 | 14 "REPLACE INTO main.a1 DEFAULT VALUES" {} |
||
65 | 15 "INSERT INTO a1 VALUES(1, 2)" {} |
||
66 | 16 "INSERT INTO main.a1 VALUES(1, 2)" {} |
||
67 | 17 "INSERT OR ROLLBACK INTO main.a1 VALUES(1, 2)" {} |
||
68 | 18 "INSERT OR ROLLBACK INTO a1 VALUES(1, 2)" {} |
||
69 | 19 "INSERT OR ABORT INTO main.a1 VALUES(1, 2)" {} |
||
70 | 20 "INSERT OR ABORT INTO a1 VALUES(1, 2)" {} |
||
71 | 21 "INSERT OR REPLACE INTO main.a1 VALUES(1, 2)" {} |
||
72 | 22 "INSERT OR REPLACE INTO a1 VALUES(1, 2)" {} |
||
73 | 23 "INSERT OR FAIL INTO main.a1 VALUES(1, 2)" {} |
||
74 | 24 "INSERT OR FAIL INTO a1 VALUES(1, 2)" {} |
||
75 | 25 "INSERT OR FAIL INTO main.a1 VALUES(1, 2)" {} |
||
76 | 26 "INSERT OR IGNORE INTO a1 VALUES(1, 2)" {} |
||
77 | 27 "REPLACE INTO a1 VALUES(1, 2)" {} |
||
78 | 28 "REPLACE INTO main.a1 VALUES(1, 2)" {} |
||
79 | 29 "INSERT INTO a1 (b, a) VALUES(1, 2)" {} |
||
80 | 30 "INSERT INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
81 | 31 "INSERT OR ROLLBACK INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
82 | 32 "INSERT OR ROLLBACK INTO a1 (b, a) VALUES(1, 2)" {} |
||
83 | 33 "INSERT OR ABORT INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
84 | 34 "INSERT OR ABORT INTO a1 (b, a) VALUES(1, 2)" {} |
||
85 | 35 "INSERT OR REPLACE INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
86 | 36 "INSERT OR REPLACE INTO a1 (b, a) VALUES(1, 2)" {} |
||
87 | 37 "INSERT OR FAIL INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
88 | 38 "INSERT OR FAIL INTO a1 (b, a) VALUES(1, 2)" {} |
||
89 | 39 "INSERT OR FAIL INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
90 | 40 "INSERT OR IGNORE INTO a1 (b, a) VALUES(1, 2)" {} |
||
91 | 41 "REPLACE INTO a1 (b, a) VALUES(1, 2)" {} |
||
92 | 42 "REPLACE INTO main.a1 (b, a) VALUES(1, 2)" {} |
||
93 | 43 "INSERT INTO a1 SELECT c, b FROM a2" {} |
||
94 | 44 "INSERT INTO main.a1 SELECT c, b FROM a2" {} |
||
95 | 45 "INSERT OR ROLLBACK INTO main.a1 SELECT c, b FROM a2" {} |
||
96 | 46 "INSERT OR ROLLBACK INTO a1 SELECT c, b FROM a2" {} |
||
97 | 47 "INSERT OR ABORT INTO main.a1 SELECT c, b FROM a2" {} |
||
98 | 48 "INSERT OR ABORT INTO a1 SELECT c, b FROM a2" {} |
||
99 | 49 "INSERT OR REPLACE INTO main.a1 SELECT c, b FROM a2" {} |
||
100 | 50 "INSERT OR REPLACE INTO a1 SELECT c, b FROM a2" {} |
||
101 | 51 "INSERT OR FAIL INTO main.a1 SELECT c, b FROM a2" {} |
||
102 | 52 "INSERT OR FAIL INTO a1 SELECT c, b FROM a2" {} |
||
103 | 53 "INSERT OR FAIL INTO main.a1 SELECT c, b FROM a2" {} |
||
104 | 54 "INSERT OR IGNORE INTO a1 SELECT c, b FROM a2" {} |
||
105 | 55 "REPLACE INTO a1 SELECT c, b FROM a2" {} |
||
106 | 56 "REPLACE INTO main.a1 SELECT c, b FROM a2" {} |
||
107 | 57 "INSERT INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
108 | 58 "INSERT INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
109 | 59 "INSERT OR ROLLBACK INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
110 | 60 "INSERT OR ROLLBACK INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
111 | 61 "INSERT OR ABORT INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
112 | 62 "INSERT OR ABORT INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
113 | 63 "INSERT OR REPLACE INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
114 | 64 "INSERT OR REPLACE INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
115 | 65 "INSERT OR FAIL INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
116 | 66 "INSERT OR FAIL INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
117 | 67 "INSERT OR FAIL INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
118 | 68 "INSERT OR IGNORE INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
119 | 69 "REPLACE INTO a1 (b, a) SELECT c, b FROM a2" {} |
||
120 | 70 "REPLACE INTO main.a1 (b, a) SELECT c, b FROM a2" {} |
||
121 | } |
||
122 | |||
123 | delete_all_data |
||
124 | |||
125 | # EVIDENCE-OF: R-20288-20462 The first form (with the "VALUES" keyword) |
||
126 | # creates a single new row in an existing table. |
||
127 | # |
||
128 | do_insert_tests e_insert-1.1 { |
||
129 | |||
130 | |||
131 | 1a "INSERT INTO a2 VALUES(1, 2, 3)" {} |
||
132 | 1b "SELECT count(*) FROM a2" {1} |
||
133 | |||
134 | 2a "INSERT INTO a2(a, b) VALUES(1, 2)" {} |
||
135 | 2b "SELECT count(*) FROM a2" {2} |
||
136 | } |
||
137 | |||
138 | # EVIDENCE-OF: R-36040-20870 If no column-list is specified then the |
||
139 | # number of values must be the same as the number of columns in the |
||
140 | # table. |
||
141 | # |
||
142 | # A test in the block above verifies that if the VALUES list has the |
||
143 | # correct number of columns (for table a2, 3 columns) works. So these |
||
144 | # tests just show that other values cause an error. |
||
145 | # |
||
146 | do_insert_tests e_insert-1.2 -error { |
||
147 | table %s has %d columns but %d values were supplied |
||
148 | } { |
||
149 | 1 "INSERT INTO a2 VALUES(1)" {a2 3 1} |
||
150 | 2 "INSERT INTO a2 VALUES(1,2)" {a2 3 2} |
||
151 | 3 "INSERT INTO a2 VALUES(1,2,3,4)" {a2 3 4} |
||
152 | 4 "INSERT INTO a2 VALUES(1,2,3,4,5)" {a2 3 5} |
||
153 | } |
||
154 | |||
155 | # EVIDENCE-OF: R-04006-57648 In this case the result of evaluating the |
||
156 | # left-most expression in the VALUES list is inserted into the left-most |
||
157 | # column of the new row, and so on. |
||
158 | # |
||
159 | delete_all_data |
||
160 | do_insert_tests e_insert-1.3 { |
||
161 | 1a "INSERT INTO a2 VALUES(1, 2, 3)" {} |
||
162 | 1b "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {1 2 3} |
||
163 | |||
164 | 2a "INSERT INTO a2 VALUES('abc', NULL, 3*3+1)" {} |
||
165 | 2b "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {abc {} 10} |
||
166 | |||
167 | 3a "INSERT INTO a2 VALUES((SELECT count(*) FROM a2), 'x', 'y')" {} |
||
168 | 3b "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {2 x y} |
||
169 | } |
||
170 | |||
171 | # EVIDENCE-OF: R-62524-00361 If a column-list is specified, then the |
||
172 | # number of values must match the number of specified columns. |
||
173 | # |
||
174 | do_insert_tests e_insert-1.4 -error { |
||
175 | %d values for %d columns |
||
176 | } { |
||
177 | 1 "INSERT INTO a2(a, b, c) VALUES(1)" {1 3} |
||
178 | 2 "INSERT INTO a2(a, b, c) VALUES(1,2)" {2 3} |
||
179 | 3 "INSERT INTO a2(a, b, c) VALUES(1,2,3,4)" {4 3} |
||
180 | 4 "INSERT INTO a2(a, b, c) VALUES(1,2,3,4,5)" {5 3} |
||
181 | |||
182 | 5 "INSERT INTO a2(c, a) VALUES(1)" {1 2} |
||
183 | 6 "INSERT INTO a2(c, a) VALUES(1,2,3)" {3 2} |
||
184 | 7 "INSERT INTO a2(c, a) VALUES(1,2,3,4)" {4 2} |
||
185 | 8 "INSERT INTO a2(c, a) VALUES(1,2,3,4,5)" {5 2} |
||
186 | } |
||
187 | |||
188 | # EVIDENCE-OF: R-07016-26442 Each of the named columns of the new row is |
||
189 | # populated with the results of evaluating the corresponding VALUES |
||
190 | # expression. |
||
191 | # |
||
192 | # EVIDENCE-OF: R-12183-43719 Table columns that do not appear in the |
||
193 | # column list are populated with the default column value (specified as |
||
194 | # part of the CREATE TABLE statement), or with NULL if no default value |
||
195 | # is specified. |
||
196 | # |
||
197 | delete_all_data |
||
198 | do_insert_tests e_insert-1.5 { |
||
199 | 1a "INSERT INTO a2(b, c) VALUES('b', 'c')" {} |
||
200 | 1b "SELECT * FROM a2" {{} b c} |
||
201 | |||
202 | 2a "INSERT INTO a2(a, b) VALUES('a', 'b')" {} |
||
203 | 2b "SELECT * FROM a2" {{} b c a b xyz} |
||
204 | } |
||
205 | |||
206 | # EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for |
||
207 | # each row of data returned by executing the SELECT statement. |
||
208 | # |
||
209 | delete_all_data |
||
210 | do_insert_tests e_insert-2.1 { |
||
211 | |||
212 | |||
213 | 1a "SELECT count(*) FROM (SELECT 1, 2)" {1} |
||
214 | 1b "INSERT INTO a1 SELECT 1, 2" {} |
||
215 | 1c "SELECT count(*) FROM a1" {1} |
||
216 | |||
217 | 2a "SELECT count(*) FROM (SELECT b, a FROM a1)" {1} |
||
218 | 2b "INSERT INTO a1 SELECT b, a FROM a1" {} |
||
219 | 2c "SELECT count(*) FROM a1" {2} |
||
220 | |||
221 | 3a "SELECT count(*) FROM (SELECT b, a FROM a1)" {2} |
||
222 | 3b "INSERT INTO a1 SELECT b, a FROM a1" {} |
||
223 | 3c "SELECT count(*) FROM a1" {4} |
||
224 | |||
225 | 4a "SELECT count(*) FROM (SELECT b, a FROM a1)" {4} |
||
226 | 4b "INSERT INTO a1 SELECT b, a FROM a1" {} |
||
227 | 4c "SELECT count(*) FROM a1" {8} |
||
228 | |||
229 | 4a "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1} |
||
230 | 4b "INSERT INTO a1 SELECT min(b), min(a) FROM a1" {} |
||
231 | 4c "SELECT count(*) FROM a1" {9} |
||
232 | } |
||
233 | |||
234 | |||
235 | # EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number |
||
236 | # of columns in the result of the SELECT must be the same as the number |
||
237 | # of items in the column-list. |
||
238 | # |
||
239 | do_insert_tests e_insert-2.2 -error { |
||
240 | %d values for %d columns |
||
241 | } { |
||
242 | 1 "INSERT INTO a3(x, y) SELECT a, b, c FROM a2" {3 2} |
||
243 | 2 "INSERT INTO a3(x, y) SELECT * FROM a2" {3 2} |
||
244 | 3 "INSERT INTO a3(x, y) SELECT * FROM a2 CROSS JOIN a1" {5 2} |
||
245 | 4 "INSERT INTO a3(x, y) SELECT * FROM a2 NATURAL JOIN a1" {3 2} |
||
246 | 5 "INSERT INTO a3(x, y) SELECT a2.a FROM a2,a1" {1 2} |
||
247 | |||
248 | 6 "INSERT INTO a3(z) SELECT a, b, c FROM a2" {3 1} |
||
249 | 7 "INSERT INTO a3(z) SELECT * FROM a2" {3 1} |
||
250 | 8 "INSERT INTO a3(z) SELECT * FROM a2 CROSS JOIN a1" {5 1} |
||
251 | 9 "INSERT INTO a3(z) SELECT * FROM a2 NATURAL JOIN a1" {3 1} |
||
252 | 10 "INSERT INTO a3(z) SELECT a1.* FROM a2,a1" {2 1} |
||
253 | } |
||
254 | |||
255 | # EVIDENCE-OF: R-58951-07798 Otherwise, if no column-list is specified, |
||
256 | # the number of columns in the result of the SELECT must be the same as |
||
257 | # the number of columns in the table. |
||
258 | # |
||
259 | do_insert_tests e_insert-2.3 -error { |
||
260 | table %s has %d columns but %d values were supplied |
||
261 | } { |
||
262 | 1 "INSERT INTO a1 SELECT a, b, c FROM a2" {a1 2 3} |
||
263 | 2 "INSERT INTO a1 SELECT * FROM a2" {a1 2 3} |
||
264 | 3 "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1" {a1 2 5} |
||
265 | 4 "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1" {a1 2 3} |
||
266 | 5 "INSERT INTO a1 SELECT a2.a FROM a2,a1" {a1 2 1} |
||
267 | } |
||
268 | |||
269 | # EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound |
||
270 | # SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may |
||
271 | # be used in an INSERT statement of this form. |
||
272 | # |
||
273 | delete_all_data |
||
274 | do_execsql_test e_insert-2.3.0 { |
||
275 | INSERT INTO a1 VALUES('x', 'y'); |
||
276 | } {} |
||
277 | do_insert_tests e_insert-2.3 { |
||
278 | 1 "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {} |
||
279 | 2 "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1" {} |
||
280 | 3 "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1" {} |
||
281 | 4 "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a" {} |
||
282 | S "SELECT * FROM a1" { |
||
283 | x y |
||
284 | x y y x |
||
285 | y x |
||
286 | ax by ay bx |
||
287 | ay bx ax by y x y x x y x y |
||
288 | } |
||
289 | } |
||
290 | |||
291 | # EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement |
||
292 | # inserts a single new row into the named table. |
||
293 | # |
||
294 | delete_all_data |
||
295 | do_insert_tests e_insert-3.1 { |
||
296 | 1 "SELECT count(*) FROM a3" {0} |
||
297 | 2a "INSERT INTO a3 DEFAULT VALUES" {} |
||
298 | 2b "SELECT count(*) FROM a3" {1} |
||
299 | } |
||
300 | |||
301 | # EVIDENCE-OF: R-18927-01951 Each column of the new row is populated |
||
302 | # with its default value, or with a NULL if no default value is |
||
303 | # specified as part of the column definition in the CREATE TABLE |
||
304 | # statement. |
||
305 | # |
||
306 | delete_all_data |
||
307 | do_insert_tests e_insert-3.2 { |
||
308 | 1.1 "INSERT INTO a3 DEFAULT VALUES" {} |
||
309 | 1.2 "SELECT * FROM a3" {1.0 string {}} |
||
310 | |||
311 | 2.1 "INSERT INTO a3 DEFAULT VALUES" {} |
||
312 | 2.2 "SELECT * FROM a3" {1.0 string {} 1.0 string {}} |
||
313 | |||
314 | 3.1 "INSERT INTO a2 DEFAULT VALUES" {} |
||
315 | 3.2 "SELECT * FROM a2" {{} {} xyz} |
||
316 | |||
317 | 4.1 "INSERT INTO a2 DEFAULT VALUES" {} |
||
318 | 4.2 "SELECT * FROM a2" {{} {} xyz {} {} xyz} |
||
319 | |||
320 | 5.1 "INSERT INTO a1 DEFAULT VALUES" {} |
||
321 | 5.2 "SELECT * FROM a1" {{} {}} |
||
322 | |||
323 | 6.1 "INSERT INTO a1 DEFAULT VALUES" {} |
||
324 | 6.2 "SELECT * FROM a1" {{} {} {} {}} |
||
325 | } |
||
326 | |||
327 | # EVIDENCE-OF: R-46928-50290 The optional conflict-clause allows the |
||
328 | # specification of an alternative constraint conflict resolution |
||
329 | # algorithm to use during this one INSERT command. |
||
330 | # |
||
331 | # EVIDENCE-OF: R-23110-47146 the parser allows the use of the single |
||
332 | # keyword REPLACE as an alias for "INSERT OR REPLACE". |
||
333 | # |
||
334 | # The two requirements above are tested by e_select-4.1.* and |
||
335 | # e_select-4.2.*, respectively. |
||
336 | # |
||
337 | # EVIDENCE-OF: R-03421-22330 The REPLACE command is an alias for the |
||
338 | # "INSERT OR REPLACE" variant of the INSERT command. |
||
339 | # |
||
340 | # This is a dup of R-23110-47146. Therefore it is also verified |
||
341 | # by e_select-4.2.*. This requirement is the only one from |
||
342 | # lang_replace.html. |
||
343 | # |
||
344 | do_execsql_test e_insert-4.1.0 { |
||
345 | INSERT INTO a4 VALUES(1, 'a'); |
||
346 | INSERT INTO a4 VALUES(2, 'a'); |
||
347 | INSERT INTO a4 VALUES(3, 'a'); |
||
348 | } {} |
||
349 | foreach {tn sql error ac data } { |
||
350 | 1.1 "INSERT INTO a4 VALUES(2,'b')" {column c is not unique} 1 {1 a 2 a 3 a} |
||
351 | 1.2 "INSERT OR REPLACE INTO a4 VALUES(2, 'b')" {} 1 {1 a 3 a 2 b} |
||
352 | 1.3 "INSERT OR IGNORE INTO a4 VALUES(3, 'c')" {} 1 {1 a 3 a 2 b} |
||
353 | 1.4 "BEGIN" {} 0 {1 a 3 a 2 b} |
||
354 | 1.5 "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique} 0 {1 a 3 a 2 b} |
||
355 | 1.6 "INSERT OR ABORT INTO a4 VALUES(1, 'd')" |
||
356 | {column c is not unique} 0 {1 a 3 a 2 b} |
||
357 | 1.7 "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" |
||
358 | {column c is not unique} 1 {1 a 3 a 2 b} |
||
359 | 1.8 "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'" |
||
360 | {column c is not unique} 1 {1 a 3 a 2 b} |
||
361 | 1.9 "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'" |
||
362 | {column c is not unique} 1 {1 a 3 a 2 b 4 e} |
||
363 | |||
364 | 2.1 "INSERT INTO a4 VALUES(2,'f')" |
||
365 | {column c is not unique} 1 {1 a 3 a 2 b 4 e} |
||
366 | 2.2 "REPLACE INTO a4 VALUES(2, 'f')" {} 1 {1 a 3 a 4 e 2 f} |
||
367 | } { |
||
368 | do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error] |
||
369 | do_execsql_test e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data] |
||
370 | do_test e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac |
||
371 | } |
||
372 | |||
373 | # EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the |
||
374 | # table-name is support for top-level INSERT statements only. |
||
375 | # |
||
376 | # EVIDENCE-OF: R-05731-00924 The table name must be unqualified for |
||
377 | # INSERT statements that occur within CREATE TRIGGER statements. |
||
378 | # |
||
379 | set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}} |
||
380 | |||
381 | do_catchsql_test e_insert-5.1.1 { |
||
382 | CREATE TRIGGER AFTER UPDATE ON a1 BEGIN |
||
383 | INSERT INTO main.a4 VALUES(new.a, new.b); |
||
384 | END; |
||
385 | } $err |
||
386 | do_catchsql_test e_insert-5.1.2 { |
||
387 | CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b); |
||
388 | CREATE TRIGGER AFTER DELETE ON a3 BEGIN |
||
389 | INSERT INTO temp.tmptable VALUES(1, 2); |
||
390 | END; |
||
391 | } $err |
||
392 | |||
393 | # EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the |
||
394 | # INSERT statement is supported for top-level INSERT statements only and |
||
395 | # not for INSERT statements within triggers. |
||
396 | # |
||
397 | do_catchsql_test e_insert-5.2.1 { |
||
398 | CREATE TRIGGER AFTER UPDATE ON a1 BEGIN |
||
399 | INSERT INTO a4 DEFAULT VALUES; |
||
400 | END; |
||
401 | } {1 {near "DEFAULT": syntax error}} |
||
402 | |||
403 | |||
404 | delete_all_data |
||
405 | |||
406 | finish_test |