Debian Codegraph @

test/table.test

raw
1 # 2001 September 15
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 CREATE TABLE statement.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 # Create a basic table and verify it is added to sqlite_master
19 #
20 do_test table-1.1 {
21 execsql {
22 CREATE TABLE test1 (
23 one varchar(10),
24 two text
25 )
26 }
27 execsql {
28 SELECT sql FROM sqlite_master WHERE type!='meta'
29 }
30 } {{CREATE TABLE test1 (
31 one varchar(10),
32 two text
33 )}}
34
35
36 # Verify the other fields of the sqlite_master file.
37 #
38 do_test table-1.3 {
39 execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
40 } {test1 test1 table}
41
42 # Close and reopen the database. Verify that everything is
43 # still the same.
44 #
45 do_test table-1.4 {
46 db close
47 sqlite3 db test.db
48 execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
49 } {test1 test1 table}
50
51 # Drop the database and make sure it disappears.
52 #
53 do_test table-1.5 {
54 execsql {DROP TABLE test1}
55 execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
56 } {}
57
58 # Close and reopen the database. Verify that the table is
59 # still gone.
60 #
61 do_test table-1.6 {
62 db close
63 sqlite3 db test.db
64 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
65 } {}
66
67 # Repeat the above steps, but this time quote the table name.
68 #
69 do_test table-1.10 {
70 execsql {CREATE TABLE "create" (f1 int)}
71 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
72 } {create}
73 do_test table-1.11 {
74 execsql {DROP TABLE "create"}
75 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
76 } {}
77 do_test table-1.12 {
78 execsql {CREATE TABLE test1("f1 ho" int)}
79 execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
80 } {test1}
81 do_test table-1.13 {
82 execsql {DROP TABLE "TEST1"}
83 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
84 } {}
85
86
87
88 # Verify that we cannot make two tables with the same name
89 #
90 do_test table-2.1 {
91 execsql {CREATE TABLE TEST2(one text)}
92 catchsql {CREATE TABLE test2(two text default 'hi')}
93 } {1 {table test2 already exists}}
94 do_test table-2.1.1 {
95 catchsql {CREATE TABLE "test2" (two)}
96 } {1 {table "test2" already exists}}
97 do_test table-2.1b {
98 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
99 lappend v $msg
100 } {1 {object name reserved for internal use: sqlite_master}}
101 do_test table-2.1c {
102 db close
103 sqlite3 db test.db
104 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
105 lappend v $msg
106 } {1 {object name reserved for internal use: sqlite_master}}
107 do_test table-2.1d {
108 catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
109 } {0 {}}
110 do_test table-2.1e {
111 catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
112 } {0 {}}
113 do_test table-2.1f {
114 execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
115 } {}
116
117 # Verify that we cannot make a table with the same name as an index
118 #
119 do_test table-2.2a {
120 execsql {CREATE TABLE test2(one text)}
121 execsql {CREATE INDEX test3 ON test2(one)}
122 catchsql {CREATE TABLE test3(two text)}
123 } {1 {there is already an index named test3}}
124 do_test table-2.2b {
125 db close
126 sqlite3 db test.db
127 set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
128 lappend v $msg
129 } {1 {there is already an index named test3}}
130 do_test table-2.2c {
131 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
132 } {test2 test3}
133 do_test table-2.2d {
134 execsql {DROP INDEX test3}
135 set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
136 lappend v $msg
137 } {0 {}}
138 do_test table-2.2e {
139 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
140 } {test2 test3}
141 do_test table-2.2f {
142 execsql {DROP TABLE test2; DROP TABLE test3}
143 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
144 } {}
145
146 # Create a table with many field names
147 #
148 set big_table \
149 {CREATE TABLE big(
150 f1 varchar(20),
151 f2 char(10),
152 f3 varchar(30) primary key,
153 f4 text,
154 f5 text,
155 f6 text,
156 f7 text,
157 f8 text,
158 f9 text,
159 f10 text,
160 f11 text,
161 f12 text,
162 f13 text,
163 f14 text,
164 f15 text,
165 f16 text,
166 f17 text,
167 f18 text,
168 f19 text,
169 f20 text
170 )}
171 do_test table-3.1 {
172 execsql $big_table
173 execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
174 } \{$big_table\}
175 do_test table-3.2 {
176 set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
177 lappend v $msg
178 } {1 {table BIG already exists}}
179 do_test table-3.3 {
180 set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
181 lappend v $msg
182 } {1 {table biG already exists}}
183 do_test table-3.4 {
184 set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
185 lappend v $msg
186 } {1 {table bIg already exists}}
187 do_test table-3.5 {
188 db close
189 sqlite3 db test.db
190 set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
191 lappend v $msg
192 } {1 {table Big already exists}}
193 do_test table-3.6 {
194 execsql {DROP TABLE big}
195 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
196 } {}
197
198 # Try creating large numbers of tables
199 #
200 set r {}
201 for {set i 1} {$i<=100} {incr i} {
202 lappend r [format test%03d $i]
203 }
204 do_test table-4.1 {
205 for {set i 1} {$i<=100} {incr i} {
206 set sql "CREATE TABLE [format test%03d $i] ("
207 for {set k 1} {$k<$i} {incr k} {
208 append sql "field$k text,"
209 }
210 append sql "last_field text)"
211 execsql $sql
212 }
213 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
214 } $r
215 do_test table-4.1b {
216 db close
217 sqlite3 db test.db
218 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
219 } $r
220
221 # Drop the even numbered tables
222 #
223 set r {}
224 for {set i 1} {$i<=100} {incr i 2} {
225 lappend r [format test%03d $i]
226 }
227 do_test table-4.2 {
228 for {set i 2} {$i<=100} {incr i 2} {
229 # if {$i==38} {execsql {pragma vdbe_trace=on}}
230 set sql "DROP TABLE [format TEST%03d $i]"
231 execsql $sql
232 }
233 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
234 } $r
235 #exit
236
237 # Drop the odd number tables
238 #
239 do_test table-4.3 {
240 for {set i 1} {$i<=100} {incr i 2} {
241 set sql "DROP TABLE [format test%03d $i]"
242 execsql $sql
243 }
244 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
245 } {}
246
247 # Try to drop a table that does not exist
248 #
249 do_test table-5.1.1 {
250 catchsql {DROP TABLE test009}
251 } {1 {no such table: test009}}
252 do_test table-5.1.2 {
253 catchsql {DROP TABLE IF EXISTS test009}
254 } {0 {}}
255
256 # Try to drop sqlite_master
257 #
258 do_test table-5.2 {
259 catchsql {DROP TABLE IF EXISTS sqlite_master}
260 } {1 {table sqlite_master may not be dropped}}
261
262 # Dropping sqlite_statN tables is OK.
263 #
264 do_test table-5.2.1 {
265 db eval {
266 ANALYZE;
267 DROP TABLE IF EXISTS sqlite_stat1;
268 DROP TABLE IF EXISTS sqlite_stat2;
269 DROP TABLE IF EXISTS sqlite_stat3;
270 DROP TABLE IF EXISTS sqlite_stat4;
271 SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*';
272 }
273 } {}
274
275 do_test table-5.2.2 {
276 db close
277 forcedelete test.db
278 sqlite3 db test.db
279 sqlite3_db_config db DEFENSIVE 0
280 db eval {
281 CREATE TABLE t0(a,b);
282 CREATE INDEX t ON t0(a);
283 PRAGMA writable_schema=ON;
284 UPDATE sqlite_master SET sql='CREATE TABLE a.b(a UNIQUE';
285 BEGIN;
286 CREATE TABLE t1(x);
287 ROLLBACK;
288 DROP TABLE IF EXISTS t99;
289 }
290 } {}
291 db close
292 forcedelete test.db
293 sqlite3 db test.db
294
295 # Make sure an EXPLAIN does not really create a new table
296 #
297 do_test table-5.3 {
298 ifcapable {explain} {
299 execsql {EXPLAIN CREATE TABLE test1(f1 int)}
300 }
301 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
302 } {}
303
304 # Make sure an EXPLAIN does not really drop an existing table
305 #
306 do_test table-5.4 {
307 execsql {CREATE TABLE test1(f1 int)}
308 ifcapable {explain} {
309 execsql {EXPLAIN DROP TABLE test1}
310 }
311 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
312 } {test1}
313
314 # Create a table with a goofy name
315 #
316 #do_test table-6.1 {
317 # execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
318 # execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
319 # set list [glob -nocomplain testdb/spaces*.tbl]
320 #} {testdb/spaces+in+this+name+.tbl}
321
322 # Try using keywords as table names or column names.
323 #
324 do_test table-7.1 {
325 set v [catch {execsql {
326 CREATE TABLE weird(
327 desc text,
328 asc text,
329 key int,
330 [14_vac] boolean,
331 fuzzy_dog_12 varchar(10),
332 begin blob,
333 end clob
334 )
335 }} msg]
336 lappend v $msg
337 } {0 {}}
338 do_test table-7.2 {
339 execsql {
340 INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
341 SELECT * FROM weird;
342 }
343 } {a b 9 0 xyz hi y'all}
344 do_test table-7.3 {
345 execsql2 {
346 SELECT * FROM weird;
347 }
348 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
349 do_test table-7.3 {
350 execsql {
351 CREATE TABLE savepoint(release);
352 INSERT INTO savepoint(release) VALUES(10);
353 UPDATE savepoint SET release = 5;
354 SELECT release FROM savepoint;
355 }
356 } {5}
357
358 # Try out the CREATE TABLE AS syntax
359 #
360 do_test table-8.1 {
361 execsql2 {
362 CREATE TABLE t2 AS SELECT * FROM weird;
363 SELECT * FROM t2;
364 }
365 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
366 do_test table-8.1.1 {
367 execsql {
368 SELECT sql FROM sqlite_master WHERE name='t2';
369 }
370 } {{CREATE TABLE t2(
371 "desc" TEXT,
372 "asc" TEXT,
373 "key" INT,
374 "14_vac" NUM,
375 fuzzy_dog_12 TEXT,
376 "begin",
377 "end" TEXT
378 )}}
379 do_test table-8.2 {
380 execsql {
381 CREATE TABLE "t3""xyz"(a,b,c);
382 INSERT INTO [t3"xyz] VALUES(1,2,3);
383 SELECT * FROM [t3"xyz];
384 }
385 } {1 2 3}
386 do_test table-8.3 {
387 execsql2 {
388 CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
389 SELECT * FROM [t4"abc];
390 }
391 } {cnt 1 max(b+c) 5}
392
393 # Update for v3: The declaration type of anything except a column is now a
394 # NULL pointer, so the created table has no column types. (Changed result
395 # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
396 do_test table-8.3.1 {
397 execsql {
398 SELECT sql FROM sqlite_master WHERE name='t4"abc'
399 }
400 } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
401
402 ifcapable tempdb {
403 do_test table-8.4 {
404 execsql2 {
405 CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
406 SELECT * FROM t5;
407 }
408 } {y'all 1}
409 }
410
411 do_test table-8.5 {
412 db close
413 sqlite3 db test.db
414 execsql2 {
415 SELECT * FROM [t4"abc];
416 }
417 } {cnt 1 max(b+c) 5}
418 do_test table-8.6 {
419 execsql2 {
420 SELECT * FROM t2;
421 }
422 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
423 do_test table-8.7 {
424 catchsql {
425 SELECT * FROM t5;
426 }
427 } {1 {no such table: t5}}
428 do_test table-8.8 {
429 catchsql {
430 CREATE TABLE t5 AS SELECT * FROM no_such_table;
431 }
432 } {1 {no such table: no_such_table}}
433
434 do_test table-8.9 {
435 execsql {
436 CREATE TABLE t10("col.1" [char.3]);
437 CREATE TABLE t11 AS SELECT * FROM t10;
438 SELECT sql FROM sqlite_master WHERE name = 't11';
439 }
440 } {{CREATE TABLE t11("col.1" TEXT)}}
441 do_test table-8.10 {
442 execsql {
443 CREATE TABLE t12(
444 a INTEGER,
445 b VARCHAR(10),
446 c VARCHAR(1,10),
447 d VARCHAR(+1,-10),
448 e VARCHAR (+1,-10),
449 f "VARCHAR (+1,-10, 5)",
450 g BIG INTEGER
451 );
452 CREATE TABLE t13 AS SELECT * FROM t12;
453 SELECT sql FROM sqlite_master WHERE name = 't13';
454 }
455 } {{CREATE TABLE t13(
456 a INT,
457 b TEXT,
458 c TEXT,
459 d TEXT,
460 e TEXT,
461 f TEXT,
462 g INT
463 )}}
464
465 # Make sure we cannot have duplicate column names within a table.
466 #
467 do_test table-9.1 {
468 catchsql {
469 CREATE TABLE t6(a,b,a);
470 }
471 } {1 {duplicate column name: a}}
472 do_test table-9.2 {
473 catchsql {
474 CREATE TABLE t6(a varchar(100), b blob, a integer);
475 }
476 } {1 {duplicate column name: a}}
477
478 # Check the foreign key syntax.
479 #
480 ifcapable {foreignkey} {
481 do_test table-10.1 {
482 catchsql {
483 CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
484 INSERT INTO t6 VALUES(NULL);
485 }
486 } {1 {NOT NULL constraint failed: t6.a}}
487 do_test table-10.2 {
488 catchsql {
489 DROP TABLE t6;
490 CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
491 }
492 } {0 {}}
493 do_test table-10.3 {
494 catchsql {
495 DROP TABLE t6;
496 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
497 }
498 } {0 {}}
499 do_test table-10.4 {
500 catchsql {
501 DROP TABLE t6;
502 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
503 }
504 } {0 {}}
505 do_test table-10.5 {
506 catchsql {
507 DROP TABLE t6;
508 CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
509 }
510 } {0 {}}
511 do_test table-10.6 {
512 catchsql {
513 DROP TABLE t6;
514 CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
515 }
516 } {0 {}}
517 do_test table-10.7 {
518 catchsql {
519 DROP TABLE t6;
520 CREATE TABLE t6(a,
521 FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
522 );
523 }
524 } {0 {}}
525 do_test table-10.8 {
526 catchsql {
527 DROP TABLE t6;
528 CREATE TABLE t6(a,b,c,
529 FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
530 ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
531 );
532 }
533 } {0 {}}
534 do_test table-10.9 {
535 catchsql {
536 DROP TABLE t6;
537 CREATE TABLE t6(a,b,c,
538 FOREIGN KEY (b,c) REFERENCES t4(x)
539 );
540 }
541 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
542 do_test table-10.10 {
543 catchsql {DROP TABLE t6}
544 catchsql {
545 CREATE TABLE t6(a,b,c,
546 FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
547 );
548 }
549 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
550 do_test table-10.11 {
551 catchsql {DROP TABLE t6}
552 catchsql {
553 CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
554 }
555 } {1 {foreign key on c should reference only one column of table t4}}
556 do_test table-10.12 {
557 catchsql {DROP TABLE t6}
558 catchsql {
559 CREATE TABLE t6(a,b,c,
560 FOREIGN KEY (b,x) REFERENCES t4(x,y)
561 );
562 }
563 } {1 {unknown column "x" in foreign key definition}}
564 do_test table-10.13 {
565 catchsql {DROP TABLE t6}
566 catchsql {
567 CREATE TABLE t6(a,b,c,
568 FOREIGN KEY (x,b) REFERENCES t4(x,y)
569 );
570 }
571 } {1 {unknown column "x" in foreign key definition}}
572 } ;# endif foreignkey
573
574 # Test for the "typeof" function. More tests for the
575 # typeof() function are found in bind.test and types.test.
576 #
577 do_test table-11.1 {
578 execsql {
579 CREATE TABLE t7(
580 a integer primary key,
581 b number(5,10),
582 c character varying (8),
583 d VARCHAR(9),
584 e clob,
585 f BLOB,
586 g Text,
587 h
588 );
589 INSERT INTO t7(a) VALUES(1);
590 SELECT typeof(a), typeof(b), typeof(c), typeof(d),
591 typeof(e), typeof(f), typeof(g), typeof(h)
592 FROM t7 LIMIT 1;
593 }
594 } {integer null null null null null null null}
595 do_test table-11.2 {
596 execsql {
597 SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
598 FROM t7 LIMIT 1;
599 }
600 } {null null null null}
601
602 # Test that when creating a table using CREATE TABLE AS, column types are
603 # assigned correctly for (SELECT ...) and 'x AS y' expressions.
604 do_test table-12.1 {
605 ifcapable subquery {
606 execsql {
607 CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
608 }
609 } else {
610 execsql {
611 CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
612 }
613 }
614 } {}
615 do_test table-12.2 {
616 execsql {
617 SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
618 }
619 } {{CREATE TABLE t8(b NUM,h,i INT,j)}}
620
621 #--------------------------------------------------------------------
622 # Test cases table-13.*
623 #
624 # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
625 # and CURRENT_TIMESTAMP.
626 #
627 do_test table-13.1 {
628 execsql {
629 CREATE TABLE tablet8(
630 a integer primary key,
631 tm text DEFAULT CURRENT_TIME,
632 dt text DEFAULT CURRENT_DATE,
633 dttm text DEFAULT CURRENT_TIMESTAMP
634 );
635 SELECT * FROM tablet8;
636 }
637 } {}
638 set i 0
639 unset -nocomplain date time seconds
640 foreach {date time seconds} {
641 1976-07-04 12:00:00 205329600
642 1994-04-16 14:00:00 766504800
643 2000-01-01 00:00:00 946684800
644 2003-12-31 12:34:56 1072874096
645 } {
646 incr i
647 set sqlite_current_time $seconds
648 do_test table-13.2.$i {
649 execsql "
650 INSERT INTO tablet8(a) VALUES($i);
651 SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
652 "
653 } [list $time $date [list $date $time]]
654 }
655 set sqlite_current_time 0
656
657 #--------------------------------------------------------------------
658 # Test cases table-14.*
659 #
660 # Test that a table cannot be created or dropped while other virtual
661 # machines are active. This is required because otherwise when in
662 # auto-vacuum mode the btree-layer may need to move the root-pages of
663 # a table for which there is an open cursor.
664 #
665 # 2007-05-02: A open btree cursor no longer blocks CREATE TABLE.
666 # But DROP TABLE is still prohibited because we do not want to
667 # delete a table out from under a running query.
668 #
669
670 # db eval {
671 # pragma vdbe_trace = 0;
672 # }
673 # Try to create a table from within a callback:
674 unset -nocomplain result
675 do_test table-14.1 {
676 set rc [
677 catch {
678 db eval {SELECT * FROM tablet8 LIMIT 1} {} {
679 db eval {CREATE TABLE t9(a, b, c)}
680 }
681 } msg
682 ]
683 set result [list $rc $msg]
684 } {0 {}}
685
686 # Try to drop a table from within a callback:
687 do_test table-14.2 {
688 set rc [
689 catch {
690 db eval {SELECT * FROM tablet8 LIMIT 1} {} {
691 db eval {DROP TABLE t9;}
692 }
693 } msg
694 ]
695 set result [list $rc $msg]
696 } {1 {database table is locked}}
697
698 ifcapable attach {
699 # Now attach a database and ensure that a table can be created in the
700 # attached database whilst in a callback from a query on the main database.
701 do_test table-14.3 {
702 forcedelete test2.db
703 forcedelete test2.db-journal
704 execsql {
705 ATTACH 'test2.db' as aux;
706 }
707 db eval {SELECT * FROM tablet8 LIMIT 1} {} {
708 db eval {CREATE TABLE aux.t1(a, b, c)}
709 }
710 } {}
711
712 # On the other hand, it should be impossible to drop a table when any VMs
713 # are active. This is because VerifyCookie instructions may have already
714 # been executed, and btree root-pages may not move after this (which a
715 # delete table might do).
716 do_test table-14.4 {
717 set rc [
718 catch {
719 db eval {SELECT * FROM tablet8 LIMIT 1} {} {
720 db eval {DROP TABLE aux.t1;}
721 }
722 } msg
723 ]
724 set result [list $rc $msg]
725 } {1 {database table is locked}}
726 }
727
728 # Create and drop 2000 tables. This is to check that the balance_shallow()
729 # routine works correctly on the sqlite_master table. At one point it
730 # contained a bug that would prevent the right-child pointer of the
731 # child page from being copied to the root page.
732 #
733 do_test table-15.1 {
734 execsql {BEGIN}
735 for {set i 0} {$i<2000} {incr i} {
736 execsql "CREATE TABLE tbl$i (a, b, c)"
737 }
738 execsql {COMMIT}
739 } {}
740 do_test table-15.2 {
741 execsql {BEGIN}
742 for {set i 0} {$i<2000} {incr i} {
743 execsql "DROP TABLE tbl$i"
744 }
745 execsql {COMMIT}
746 } {}
747
748 # Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05)
749 # The following SQL script segfaults while running the INSERT statement:
750 #
751 # CREATE TABLE t1(x DEFAULT(max(1)));
752 # INSERT INTO t1(rowid) VALUES(1);
753 #
754 # The problem appears to be the use of an aggregate function as part of
755 # the default value for a column. This problem has been in the code since
756 # at least 2006-01-01 and probably before that. This problem was detected
757 # and reported on the [email protected] mailing list by Zsbán Ambrus.
758 #
759 do_execsql_test table-16.1 {
760 CREATE TABLE t16(x DEFAULT(max(1)));
761 INSERT INTO t16(x) VALUES(123);
762 SELECT rowid, x FROM t16;
763 } {1 123}
764 do_catchsql_test table-16.2 {
765 INSERT INTO t16(rowid) VALUES(4);
766 } {1 {unknown function: max()}}
767 do_execsql_test table-16.3 {
768 DROP TABLE t16;
769 CREATE TABLE t16(x DEFAULT(abs(1)));
770 INSERT INTO t16(rowid) VALUES(4);
771 SELECT rowid, x FROM t16;
772 } {4 1}
773 do_catchsql_test table-16.4 {
774 DROP TABLE t16;
775 CREATE TABLE t16(x DEFAULT(avg(1)));
776 INSERT INTO t16(rowid) VALUES(123);
777 SELECT rowid, x FROM t16;
778 } {1 {unknown function: avg()}}
779 do_catchsql_test table-16.5 {
780 DROP TABLE t16;
781 CREATE TABLE t16(x DEFAULT(count()));
782 INSERT INTO t16(rowid) VALUES(123);
783 SELECT rowid, x FROM t16;
784 } {1 {unknown function: count()}}
785 do_catchsql_test table-16.6 {
786 DROP TABLE t16;
787 CREATE TABLE t16(x DEFAULT(string_agg('x',',')));
788 INSERT INTO t16(rowid) VALUES(123);
789 SELECT rowid, x FROM t16;
790 } {1 {unknown function: string_agg()}}
791 do_catchsql_test table-16.7 {
792 INSERT INTO t16 DEFAULT VALUES;
793 } {1 {unknown function: string_agg()}}
794
795 # Ticket [https://sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c63]
796 # describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT statement.
797 # the following test verifies that the problem has been fixed.
798 #
799 do_execsql_test table-17.1 {
800 DROP TABLE IF EXISTS t1;
801 CREATE TABLE t1(a TEXT);
802 INSERT INTO t1(a) VALUES(1),(2);
803 DROP TABLE IF EXISTS t2;
804 CREATE TABLE t2(x TEXT, y TEXT);
805 INSERT INTO t2(x,y) VALUES(3,4);
806 DROP TABLE IF EXISTS t3;
807 CREATE TABLE t3 AS
808 SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x;
809 SELECT p, q, '|' FROM t3 ORDER BY p;
810 } {1 1 | 2 2 |}
811
812 # 2015-06-16
813 # Ticket [https://sqlite.org/src/tktview/873cae2b6e25b1991ce5e9b782f9cd0409b96063]
814 # Make sure a CREATE TABLE AS statement correctly rolls back partial changes to the
815 # sqlite_master table when the SELECT on the right-hand side aborts.
816 #
817 do_catchsql_test table-18.1 {
818 DROP TABLE IF EXISTS t1;
819 BEGIN;
820 CREATE TABLE t1 AS SELECT zeroblob(2e20);
821 } {1 {string or blob too big}}
822 do_execsql_test table-18.2 {
823 COMMIT;
824 PRAGMA integrity_check;
825 } {ok}
826
827 # 2015-09-09
828 # Ticket [https://sqlite.org/src/info/acd12990885d9276]
829 # "CREATE TABLE ... AS SELECT ... FROM sqlite_master" fails because the row
830 # in the sqlite_master table for the next table is initially populated
831 # with a NULL instead of a record created by OP_Record.
832 #
833 do_execsql_test table-19.1 {
834 CREATE TABLE t19 AS SELECT * FROM sqlite_master;
835 SELECT name FROM t19 ORDER BY name;
836 } {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird}
837
838
839
840 finish_test