| blob | 889d5e780780ed11ade6e6637f7cc1763df67f51 |
1 # 2007 January 24
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 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the INSERT transfer optimization.
13 #
14 # $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 ifcapable !view||!subquery {
20 finish_test
21 return
22 }
24 # The sqlite3_xferopt_count variable is incremented whenever the
25 # insert transfer optimization applies.
26 #
27 # This procedure runs a test to see if the sqlite3_xferopt_count is
28 # set to N.
29 #
30 proc xferopt_test {testname N} {
31 do_test $testname {set ::sqlite3_xferopt_count} $N
32 }
34 # Create tables used for testing.
35 #
36 execsql {
37 PRAGMA legacy_file_format = 0;
38 CREATE TABLE t1(a int, b int, check(b>a));
39 CREATE TABLE t2(x int, y int);
40 CREATE VIEW v2 AS SELECT y, x FROM t2;
41 CREATE TABLE t3(a int, b int);
42 }
44 # Ticket #2252. Make sure the an INSERT from identical tables
45 # does not violate constraints.
46 #
47 do_test insert4-1.1 {
48 set sqlite3_xferopt_count 0
49 execsql {
50 DELETE FROM t1;
51 DELETE FROM t2;
52 INSERT INTO t2 VALUES(9,1);
53 }
54 catchsql {
55 INSERT INTO t1 SELECT * FROM t2;
56 }
57 } {1 {CHECK constraint failed: t1}}
58 xferopt_test insert4-1.2 0
59 do_test insert4-1.3 {
60 execsql {
61 SELECT * FROM t1;
62 }
63 } {}
65 # Tests to make sure that the transfer optimization is not occurring
66 # when it is not a valid optimization.
67 #
68 # The SELECT must be against a real table.
69 do_test insert4-2.1.1 {
70 execsql {
71 DELETE FROM t1;
72 INSERT INTO t1 SELECT 4, 8;
73 SELECT * FROM t1;
74 }
75 } {4 8}
76 xferopt_test insert4-2.1.2 0
77 do_test insert4-2.2.1 {
78 catchsql {
79 DELETE FROM t1;
80 INSERT INTO t1 SELECT * FROM v2;
81 SELECT * FROM t1;
82 }
83 } {0 {1 9}}
84 xferopt_test insert4-2.2.2 0
86 # Do not run the transfer optimization if there is a LIMIT clause
87 #
88 do_test insert4-2.3.1 {
89 execsql {
90 DELETE FROM t2;
91 INSERT INTO t2 VALUES(9,1);
92 INSERT INTO t2 SELECT y, x FROM t2;
93 INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
94 SELECT * FROM t3;
95 }
96 } {9 1}
97 xferopt_test insert4-2.3.2 0
98 do_test insert4-2.3.3 {
99 catchsql {
100 DELETE FROM t1;
101 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
102 SELECT * FROM t1;
103 }
104 } {1 {CHECK constraint failed: t1}}
105 xferopt_test insert4-2.3.4 0
107 # Do not run the transfer optimization if there is a DISTINCT
108 #
109 do_test insert4-2.4.1 {
110 execsql {
111 DELETE FROM t3;
112 INSERT INTO t3 SELECT DISTINCT * FROM t2;
113 SELECT * FROM t3;
114 }
115 } {9 1 1 9}
116 xferopt_test insert4-2.4.2 0
117 do_test insert4-2.4.3 {
118 catchsql {
119 DELETE FROM t1;
120 INSERT INTO t1 SELECT DISTINCT * FROM t2;
121 }
122 } {1 {CHECK constraint failed: t1}}
123 xferopt_test insert4-2.4.4 0
125 # The following procedure constructs two tables then tries to transfer
126 # data from one table to the other. Checks are made to make sure the
127 # transfer is successful and that the transfer optimization was used or
128 # not, as appropriate.
129 #
130 # xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA
131 #
132 # The TESTID argument is the symbolic name for this test. The XFER-USED
133 # argument is true if the transfer optimization should be employed and
134 # false if not. INIT-DATA is a single row of data that is to be
135 # transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for
136 # the destination and source tables.
137 #
138 proc xfer_check {testid xferused initdata destschema srcschema} {
139 execsql "CREATE TABLE dest($destschema)"
140 execsql "CREATE TABLE src($srcschema)"
141 execsql "INSERT INTO src VALUES([join $initdata ,])"
142 set ::sqlite3_xferopt_count 0
143 do_test $testid.1 {
144 execsql {
145 INSERT INTO dest SELECT * FROM src;
146 SELECT * FROM dest;
147 }
148 } $initdata
149 do_test $testid.2 {
150 set ::sqlite3_xferopt_count
151 } $xferused
152 execsql {
153 DROP TABLE dest;
154 DROP TABLE src;
155 }
156 }
159 # Do run the transfer optimization if tables have identical
160 # CHECK constraints.
161 #
162 xfer_check insert4-3.1 1 {1 9} \
163 {a int, b int CHECK(b>a)} \
164 {x int, y int CHECK(y>x)}
165 xfer_check insert4-3.2 1 {1 9} \
166 {a int, b int CHECK(b>a)} \
167 {x int CHECK(y>x), y int}
169 # Do run the transfer optimization if the destination table lacks
170 # any CHECK constraints regardless of whether or not there are CHECK
171 # constraints on the source table.
172 #
173 xfer_check insert4-3.3 1 {1 9} \
174 {a int, b int} \
175 {x int, y int CHECK(y>x)}
177 # Do run the transfer optimization if the destination table omits
178 # NOT NULL constraints that the source table has.
179 #
180 xfer_check insert4-3.4 0 {1 9} \
181 {a int, b int CHECK(b>a)} \
182 {x int, y int}
184 # Do not run the optimization if the destination has NOT NULL
185 # constraints that the source table lacks.
186 #
187 xfer_check insert4-3.5 0 {1 9} \
188 {a int, b int NOT NULL} \
189 {x int, y int}
190 xfer_check insert4-3.6 0 {1 9} \
191 {a int, b int NOT NULL} \
192 {x int NOT NULL, y int}
193 xfer_check insert4-3.7 0 {1 9} \
194 {a int NOT NULL, b int NOT NULL} \
195 {x int NOT NULL, y int}
196 xfer_check insert4-3.8 0 {1 9} \
197 {a int NOT NULL, b int} \
198 {x int, y int}
201 # Do run the transfer optimization if the destination table and
202 # source table have the same NOT NULL constraints or if the
203 # source table has extra NOT NULL constraints.
204 #
205 xfer_check insert4-3.9 1 {1 9} \
206 {a int, b int} \
207 {x int NOT NULL, y int}
208 xfer_check insert4-3.10 1 {1 9} \
209 {a int, b int} \
210 {x int NOT NULL, y int NOT NULL}
211 xfer_check insert4-3.11 1 {1 9} \
212 {a int NOT NULL, b int} \
213 {x int NOT NULL, y int NOT NULL}
214 xfer_check insert4-3.12 1 {1 9} \
215 {a int, b int NOT NULL} \
216 {x int NOT NULL, y int NOT NULL}
218 # Do not run the optimization if any corresponding table
219 # columns have different affinities.
220 #
221 xfer_check insert4-3.20 0 {1 9} \
222 {a text, b int} \
223 {x int, b int}
224 xfer_check insert4-3.21 0 {1 9} \
225 {a int, b int} \
226 {x text, b int}
228 # "int" and "integer" are equivalent so the optimization should
229 # run here.
230 #
231 xfer_check insert4-3.22 1 {1 9} \
232 {a int, b int} \
233 {x integer, b int}
235 # Ticket #2291.
236 #
238 do_test insert4-4.1a {
239 execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
240 } {}
241 ifcapable vacuum {
242 do_test insert4-4.1b {
243 execsql {
244 INSERT INTO t4 VALUES(NULL,0);
245 INSERT INTO t4 VALUES(NULL,1);
246 INSERT INTO t4 VALUES(NULL,1);
247 VACUUM;
248 }
249 } {}
250 }
252 # Check some error conditions:
253 #
254 do_test insert4-5.1 {
255 # Table does not exist.
256 catchsql { INSERT INTO t2 SELECT a, b FROM nosuchtable }
257 } {1 {no such table: nosuchtable}}
258 do_test insert4-5.2 {
259 # Number of columns does not match.
260 catchsql {
261 CREATE TABLE t5(a, b, c);
262 INSERT INTO t4 SELECT * FROM t5;
263 }
264 } {1 {table t4 has 2 columns but 3 values were supplied}}
266 do_test insert4-6.1 {
267 set ::sqlite3_xferopt_count 0
268 execsql {
269 CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase);
270 CREATE INDEX t2_i1 ON t2(x ASC, y DESC);
271 CREATE INDEX t3_i1 ON t3(a, b);
272 INSERT INTO t2 SELECT * FROM t3;
273 }
274 set ::sqlite3_xferopt_count
275 } {0}
276 do_test insert4-6.2 {
277 set ::sqlite3_xferopt_count 0
278 execsql {
279 DROP INDEX t2_i2;
280 INSERT INTO t2 SELECT * FROM t3;
281 }
282 set ::sqlite3_xferopt_count
283 } {0}
284 do_test insert4-6.3 {
285 set ::sqlite3_xferopt_count 0
286 execsql {
287 DROP INDEX t2_i1;
288 CREATE INDEX t2_i1 ON t2(x ASC, y ASC);
289 INSERT INTO t2 SELECT * FROM t3;
290 }
291 set ::sqlite3_xferopt_count
292 } {1}
293 do_test insert4-6.4 {
294 set ::sqlite3_xferopt_count 0
295 execsql {
296 DROP INDEX t2_i1;
297 CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
298 INSERT INTO t2 SELECT * FROM t3;
299 }
300 set ::sqlite3_xferopt_count
301 } {0}
304 do_test insert4-6.5 {
305 execsql {
306 CREATE TABLE t6a(x CHECK( x<>'abc' ));
307 INSERT INTO t6a VALUES('ABC');
308 SELECT * FROM t6a;
309 }
310 } {ABC}
311 do_test insert4-6.6 {
312 execsql {
313 CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase ));
314 }
315 catchsql {
316 INSERT INTO t6b SELECT * FROM t6a;
317 }
318 } {1 {CHECK constraint failed: t6b}}
319 do_test insert4-6.7 {
320 execsql {
321 DROP TABLE t6b;
322 CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
323 }
324 catchsql {
325 INSERT INTO t6b SELECT * FROM t6a;
326 }
327 } {1 {CHECK constraint failed: t6b}}
329 # Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
330 # Disable the xfer optimization if the destination table contains
331 # a foreign key constraint
332 #
333 ifcapable foreignkey {
334 do_test insert4-7.1 {
335 set ::sqlite3_xferopt_count 0
336 execsql {
337 CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123);
338 CREATE TABLE t7b(y INTEGER REFERENCES t7a);
339 CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234);
340 INSERT INTO t7b SELECT * FROM t7c;
341 SELECT * FROM t7b;
342 }
343 } {234}
344 do_test insert4-7.2 {
345 set ::sqlite3_xferopt_count
346 } {1}
347 do_test insert4-7.3 {
348 set ::sqlite3_xferopt_count 0
349 execsql {
350 DELETE FROM t7b;
351 PRAGMA foreign_keys=ON;
352 }
353 catchsql {
354 INSERT INTO t7b SELECT * FROM t7c;
355 }
356 } {1 {FOREIGN KEY constraint failed}}
357 do_test insert4-7.4 {
358 execsql {SELECT * FROM t7b}
359 } {}
360 do_test insert4-7.5 {
361 set ::sqlite3_xferopt_count
362 } {0}
363 do_test insert4-7.6 {
364 set ::sqlite3_xferopt_count 0
365 execsql {
366 DELETE FROM t7b; DELETE FROM t7c;
367 INSERT INTO t7c VALUES(123);
368 INSERT INTO t7b SELECT * FROM t7c;
369 SELECT * FROM t7b;
370 }
371 } {123}
372 do_test insert4-7.7 {
373 set ::sqlite3_xferopt_count
374 } {0}
375 do_test insert4-7.7 {
376 set ::sqlite3_xferopt_count 0
377 execsql {
378 PRAGMA foreign_keys=OFF;
379 DELETE FROM t7b;
380 INSERT INTO t7b SELECT * FROM t7c;
381 SELECT * FROM t7b;
382 }
383 } {123}
384 do_test insert4-7.8 {
385 set ::sqlite3_xferopt_count
386 } {1}
387 }
389 # Ticket [676bc02b87176125635cb174d110b431581912bb]
390 # Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer
391 # optimization.
392 #
393 do_test insert4-8.1 {
394 execsql {
395 DROP TABLE IF EXISTS t1;
396 DROP TABLE IF EXISTS t2;
397 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
398 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
399 INSERT INTO t1 VALUES(1,2);
400 INSERT INTO t2 VALUES(1,3);
401 INSERT INTO t1 SELECT * FROM t2;
402 SELECT * FROM t1;
403 }
404 } {1 3}
405 do_test insert4-8.2 {
406 execsql {
407 DROP TABLE IF EXISTS t1;
408 DROP TABLE IF EXISTS t2;
409 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
410 CREATE TABLE t2(x, y);
411 INSERT INTO t1 VALUES(1,2);
412 INSERT INTO t2 VALUES(1,3);
413 INSERT INTO t1 SELECT * FROM t2;
414 SELECT * FROM t1;
415 }
416 } {1 3}
417 do_test insert4-8.3 {
418 execsql {
419 DROP TABLE IF EXISTS t1;
420 DROP TABLE IF EXISTS t2;
421 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
422 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
423 INSERT INTO t1 VALUES(1,2);
424 INSERT INTO t2 VALUES(1,3);
425 INSERT INTO t1 SELECT * FROM t2;
426 SELECT * FROM t1;
427 }
428 } {1 2}
429 do_test insert4-8.4 {
430 execsql {
431 DROP TABLE IF EXISTS t1;
432 DROP TABLE IF EXISTS t2;
433 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
434 CREATE TABLE t2(x, y);
435 INSERT INTO t1 VALUES(1,2);
436 INSERT INTO t2 VALUES(1,3);
437 INSERT INTO t1 SELECT * FROM t2;
438 SELECT * FROM t1;
439 }
440 } {1 2}
441 do_test insert4-8.5 {
442 execsql {
443 DROP TABLE IF EXISTS t1;
444 DROP TABLE IF EXISTS t2;
445 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
446 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
447 INSERT INTO t1 VALUES(1,2);
448 INSERT INTO t2 VALUES(-99,100);
449 INSERT INTO t2 VALUES(1,3);
450 SELECT * FROM t1;
451 }
452 catchsql {
453 INSERT INTO t1 SELECT * FROM t2;
454 }
455 } {1 {UNIQUE constraint failed: t1.a}}
456 do_test insert4-8.6 {
457 execsql {
458 SELECT * FROM t1;
459 }
460 } {-99 100 1 2}
461 do_test insert4-8.7 {
462 execsql {
463 DROP TABLE IF EXISTS t1;
464 DROP TABLE IF EXISTS t2;
465 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
466 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
467 INSERT INTO t1 VALUES(1,2);
468 INSERT INTO t2 VALUES(-99,100);
469 INSERT INTO t2 VALUES(1,3);
470 SELECT * FROM t1;
471 }
472 catchsql {
473 INSERT INTO t1 SELECT * FROM t2;
474 }
475 } {1 {UNIQUE constraint failed: t1.a}}
476 do_test insert4-8.8 {
477 execsql {
478 SELECT * FROM t1;
479 }
480 } {1 2}
481 do_test insert4-8.9 {
482 execsql {
483 DROP TABLE IF EXISTS t1;
484 DROP TABLE IF EXISTS t2;
485 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
486 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
487 INSERT INTO t1 VALUES(1,2);
488 INSERT INTO t2 VALUES(-99,100);
489 INSERT INTO t2 VALUES(1,3);
490 SELECT * FROM t1;
491 }
492 catchsql {
493 BEGIN;
494 INSERT INTO t1 VALUES(2,3);
495 INSERT INTO t1 SELECT * FROM t2;
496 }
497 } {1 {UNIQUE constraint failed: t1.a}}
498 do_test insert4-8.10 {
499 catchsql {COMMIT}
500 } {1 {cannot commit - no transaction is active}}
501 do_test insert4-8.11 {
502 execsql {
503 SELECT * FROM t1;
504 }
505 } {1 2}
507 do_test insert4-8.21 {
508 execsql {
509 DROP TABLE IF EXISTS t1;
510 DROP TABLE IF EXISTS t2;
511 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
512 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
513 INSERT INTO t2 VALUES(1,3);
514 INSERT INTO t1 SELECT * FROM t2;
515 SELECT * FROM t1;
516 }
517 } {1 3}
518 do_test insert4-8.22 {
519 execsql {
520 DROP TABLE IF EXISTS t1;
521 DROP TABLE IF EXISTS t2;
522 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
523 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
524 INSERT INTO t2 VALUES(1,3);
525 INSERT INTO t1 SELECT * FROM t2;
526 SELECT * FROM t1;
527 }
528 } {1 3}
529 do_test insert4-8.23 {
530 execsql {
531 DROP TABLE IF EXISTS t1;
532 DROP TABLE IF EXISTS t2;
533 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
534 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
535 INSERT INTO t2 VALUES(1,3);
536 INSERT INTO t1 SELECT * FROM t2;
537 SELECT * FROM t1;
538 }
539 } {1 3}
540 do_test insert4-8.24 {
541 execsql {
542 DROP TABLE IF EXISTS t1;
543 DROP TABLE IF EXISTS t2;
544 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
545 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
546 INSERT INTO t2 VALUES(1,3);
547 INSERT INTO t1 SELECT * FROM t2;
548 SELECT * FROM t1;
549 }
550 } {1 3}
551 do_test insert4-8.25 {
552 execsql {
553 DROP TABLE IF EXISTS t1;
554 DROP TABLE IF EXISTS t2;
555 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
556 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
557 INSERT INTO t2 VALUES(1,3);
558 INSERT INTO t1 SELECT * FROM t2;
559 SELECT * FROM t1;
560 }
561 } {1 3}
564 finish_test