| blob | 2921d86c6f3e81eeb721b2dbdbbeddd7926a8e45 |
1 # 2010 September 25
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 # This file implements tests to verify that the "testable statements" in
13 # the lang_createtable.html document are correct.
14 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 set ::testprefix e_createtable
21 # Test organization:
22 #
23 # e_createtable-0.*: Test that the syntax diagrams are correct.
24 #
25 # e_createtable-1.*: Test statements related to table and database names,
26 # the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
27 #
28 # e_createtable-2.*: Test "CREATE TABLE AS" statements.
29 #
31 proc do_createtable_tests {nm args} {
32 uplevel do_select_tests [list e_createtable-$nm] $args
33 }
36 #-------------------------------------------------------------------------
37 # This command returns a serialized tcl array mapping from the name of
38 # each attached database to a list of tables in that database. For example,
39 # if the database schema is created with:
40 #
41 # CREATE TABLE t1(x);
42 # CREATE TEMP TABLE t2(x);
43 # CREATE TEMP TABLE t3(x);
44 #
45 # Then this command returns "main t1 temp {t2 t3}".
46 #
47 proc table_list {} {
48 set res [list]
49 db eval { pragma database_list } a {
50 set dbname $a(name)
51 set master $a(name).sqlite_master
52 if {$dbname == "temp"} { set master sqlite_temp_master }
53 lappend res $dbname [
54 db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
55 ]
56 }
57 set res
58 }
61 do_createtable_tests 0.1.1 -repair {
62 drop_all_tables
63 } {
64 1 "CREATE TABLE t1(c1 one)" {}
65 2 "CREATE TABLE t1(c1 one two)" {}
66 3 "CREATE TABLE t1(c1 one two three)" {}
67 4 "CREATE TABLE t1(c1 one two three four)" {}
68 5 "CREATE TABLE t1(c1 one two three four(14))" {}
69 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {}
70 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {}
71 8 "CREATE TABLE t1(c1 var(1.0e10))" {}
72 }
73 do_createtable_tests 0.1.2 -error {
74 near "%s": syntax error
75 } {
76 1 "CREATE TABLE t1(c1 one(number))" {number}
77 }
80 # syntax diagram column-constraint
81 #
82 do_createtable_tests 0.2.1 -repair {
83 drop_all_tables
84 execsql { CREATE TABLE t2(x PRIMARY KEY) }
85 } {
86 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {}
87 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {}
88 1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {}
89 1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {}
91 2.1 "CREATE TABLE t1(c1 text NOT NULL)" {}
92 2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {}
93 2.3 "CREATE TABLE t1(c1 text NULL)" {}
94 2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {}
96 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {}
97 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {}
99 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {}
100 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {}
102 5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {}
103 5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {}
104 5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {}
105 5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {}
106 5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {}
107 5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {}
109 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {}
110 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {}
112 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {}
113 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {}
115 8.1 {
116 CREATE TABLE t1(c1
117 PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
118 );
119 } {}
120 8.2 {
121 CREATE TABLE t1(c1
122 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY
123 );
124 } {}
125 }
127 # -- syntax diagram table-constraint
128 #
129 do_createtable_tests 0.3.1 -repair {
130 drop_all_tables
131 execsql { CREATE TABLE t2(x PRIMARY KEY) }
132 } {
133 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {}
134 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {}
135 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {}
137 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {}
138 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {}
139 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {}
141 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {}
143 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {}
144 }
146 # -- syntax diagram column-def
147 #
148 do_createtable_tests 0.4.1 -repair {
149 drop_all_tables
150 } {
151 1 {CREATE TABLE t1(
152 col1,
153 col2 TEXT,
154 col3 INTEGER UNIQUE,
155 col4 VARCHAR(10, 10) PRIMARY KEY,
156 "name with spaces" REFERENCES t1
157 );
158 } {}
159 }
161 # -- syntax diagram create-table-stmt
162 #
163 do_createtable_tests 0.5.1 -repair {
164 drop_all_tables
165 execsql { CREATE TABLE t2(a, b, c) }
166 } {
167 1 "CREATE TABLE t1(a, b, c)" {}
168 2 "CREATE TEMP TABLE t1(a, b, c)" {}
169 3 "CREATE TEMPORARY TABLE t1(a, b, c)" {}
170 4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {}
171 5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {}
172 6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {}
174 7 "CREATE TABLE main.t1(a, b, c)" {}
175 8 "CREATE TEMP TABLE temp.t1(a, b, c)" {}
176 9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {}
177 10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {}
178 11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {}
179 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {}
181 13 "CREATE TABLE t1 AS SELECT * FROM t2" {}
182 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {}
183 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {}
184 }
186 #
187 # 1: Explicit parent-key columns.
188 # 2: Implicit child-key columns.
189 #
190 # 1: MATCH FULL
191 # 2: MATCH PARTIAL
192 # 3: MATCH SIMPLE
193 # 4: MATCH STICK
194 # 5:
195 #
196 # 1: ON DELETE SET NULL
197 # 2: ON DELETE SET DEFAULT
198 # 3: ON DELETE CASCADE
199 # 4: ON DELETE RESTRICT
200 # 5: ON DELETE NO ACTION
201 # 6:
202 #
203 # 1: ON UPDATE SET NULL
204 # 2: ON UPDATE SET DEFAULT
205 # 3: ON UPDATE CASCADE
206 # 4: ON UPDATE RESTRICT
207 # 5: ON UPDATE NO ACTION
208 # 6:
209 #
210 # 1: NOT DEFERRABLE INITIALLY DEFERRED
211 # 2: NOT DEFERRABLE INITIALLY IMMEDIATE
212 # 3: NOT DEFERRABLE
213 # 4: DEFERRABLE INITIALLY DEFERRED
214 # 5: DEFERRABLE INITIALLY IMMEDIATE
215 # 6: DEFERRABLE
216 # 7:
217 #
218 do_createtable_tests 0.6.1 -repair {
219 drop_all_tables
220 execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
221 execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
222 } {
223 11146 { CREATE TABLE t1(a
224 REFERENCES t2(x) MATCH FULL
225 ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
226 )} {}
227 11412 { CREATE TABLE t1(a
228 REFERENCES t2(x)
229 ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL
230 NOT DEFERRABLE INITIALLY IMMEDIATE
231 )} {}
232 12135 { CREATE TABLE t1(a
233 REFERENCES t2(x) MATCH PARTIAL
234 ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
235 )} {}
236 12427 { CREATE TABLE t1(a
237 REFERENCES t2(x) MATCH PARTIAL
238 ON DELETE RESTRICT ON UPDATE SET DEFAULT
239 )} {}
240 12446 { CREATE TABLE t1(a
241 REFERENCES t2(x) MATCH PARTIAL
242 ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
243 )} {}
244 12522 { CREATE TABLE t1(a
245 REFERENCES t2(x) MATCH PARTIAL
246 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
247 )} {}
248 13133 { CREATE TABLE t1(a
249 REFERENCES t2(x) MATCH SIMPLE
250 ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
251 )} {}
252 13216 { CREATE TABLE t1(a
253 REFERENCES t2(x) MATCH SIMPLE
254 ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
255 )} {}
256 13263 { CREATE TABLE t1(a
257 REFERENCES t2(x) MATCH SIMPLE
258 ON DELETE SET DEFAULT NOT DEFERRABLE
259 )} {}
260 13421 { CREATE TABLE t1(a
261 REFERENCES t2(x) MATCH SIMPLE
262 ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
263 )} {}
264 13432 { CREATE TABLE t1(a
265 REFERENCES t2(x) MATCH SIMPLE
266 ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
267 )} {}
268 13523 { CREATE TABLE t1(a
269 REFERENCES t2(x) MATCH SIMPLE
270 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
271 )} {}
272 14336 { CREATE TABLE t1(a
273 REFERENCES t2(x) MATCH STICK
274 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
275 )} {}
276 14611 { CREATE TABLE t1(a
277 REFERENCES t2(x) MATCH STICK
278 ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
279 )} {}
280 15155 { CREATE TABLE t1(a
281 REFERENCES t2(x)
282 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
283 )} {}
284 15453 { CREATE TABLE t1(a
285 REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
286 )} {}
287 15661 { CREATE TABLE t1(a
288 REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
289 )} {}
290 21115 { CREATE TABLE t1(a
291 REFERENCES t2 MATCH FULL
292 ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
293 )} {}
294 21123 { CREATE TABLE t1(a
295 REFERENCES t2 MATCH FULL
296 ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
297 )} {}
298 21217 { CREATE TABLE t1(a
299 REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL
300 )} {}
301 21362 { CREATE TABLE t1(a
302 REFERENCES t2 MATCH FULL
303 ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
304 )} {}
305 22143 { CREATE TABLE t1(a
306 REFERENCES t2 MATCH PARTIAL
307 ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
308 )} {}
309 22156 { CREATE TABLE t1(a
310 REFERENCES t2 MATCH PARTIAL
311 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
312 )} {}
313 22327 { CREATE TABLE t1(a
314 REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT
315 )} {}
316 22663 { CREATE TABLE t1(a
317 REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
318 )} {}
319 23236 { CREATE TABLE t1(a
320 REFERENCES t2 MATCH SIMPLE
321 ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
322 )} {}
323 24155 { CREATE TABLE t1(a
324 REFERENCES t2 MATCH STICK
325 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
326 )} {}
327 24522 { CREATE TABLE t1(a
328 REFERENCES t2 MATCH STICK
329 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
330 )} {}
331 24625 { CREATE TABLE t1(a
332 REFERENCES t2 MATCH STICK
333 ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
334 )} {}
335 25454 { CREATE TABLE t1(a
336 REFERENCES t2
337 ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
338 )} {}
339 }
341 #-------------------------------------------------------------------------
342 # Test cases e_createtable-1.* - test statements related to table and
343 # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS
344 # clause.
345 #
346 drop_all_tables
347 forcedelete test.db2 test.db3
349 do_execsql_test e_createtable-1.0 {
350 ATTACH 'test.db2' AS auxa;
351 ATTACH 'test.db3' AS auxb;
352 } {}
354 # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are
355 # reserved for internal use. It is an error to attempt to create a table
356 # with a name that starts with "sqlite_".
357 #
358 do_createtable_tests 1.1.1 -error {
359 object name reserved for internal use: %s
360 } {
361 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc
362 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld
363 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__
364 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_
365 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL
366 }
367 do_createtable_tests 1.1.2 {
368 1 "CREATE TABLE sqlit_abc(a, b, c)" {}
369 2 "CREATE TABLE temp.sqlitehelloworld(x)" {}
370 3 {CREATE TABLE auxa."sqlite"(x, y)} {}
371 4 {CREATE TABLE auxb."sqlite-"(z)} {}
372 5 {CREATE TABLE "SQLITE-TBL"(z)} {}
373 }
376 # EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it
377 # must be either "main", "temp", or the name of an attached database.
378 #
379 # EVIDENCE-OF: R-39822-07822 In this case the new table is created in
380 # the named database.
381 #
382 # Test cases 1.2.* test the first of the two requirements above. The
383 # second is verified by cases 1.3.*.
384 #
385 do_createtable_tests 1.2.1 -error {
386 unknown database %s
387 } {
388 1 "CREATE TABLE george.t1(a, b)" george
389 2 "CREATE TABLE _.t1(a, b)" _
390 }
391 do_createtable_tests 1.2.2 {
392 1 "CREATE TABLE main.abc(a, b, c)" {}
393 2 "CREATE TABLE temp.helloworld(x)" {}
394 3 {CREATE TABLE auxa."t 1"(x, y)} {}
395 4 {CREATE TABLE auxb.xyz(z)} {}
396 }
397 drop_all_tables
398 do_createtable_tests 1.3 -tclquery {
399 unset -nocomplain X
400 array set X [table_list]
401 list $X(main) $X(temp) $X(auxa) $X(auxb)
402 } {
403 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}}
404 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}}
405 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}}
406 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl}
407 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}}
408 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}}
409 }
411 # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs
412 # between the "CREATE" and "TABLE" then the new table is created in the
413 # temp database.
414 #
415 drop_all_tables
416 do_createtable_tests 1.4 -tclquery {
417 unset -nocomplain X
418 array set X [table_list]
419 list $X(main) $X(temp) $X(auxa) $X(auxb)
420 } {
421 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}}
422 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}
423 }
425 # EVIDENCE-OF: R-49439-47561 It is an error to specify both a
426 # <database-name> and the TEMP or TEMPORARY keyword, unless the
427 # <database-name> is "temp".
428 #
429 drop_all_tables
430 do_createtable_tests 1.5.1 -error {
431 temporary table name must be unqualified
432 } {
433 1 "CREATE TEMP TABLE main.t1(a, b)" {}
434 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {}
435 3 "CREATE TEMP TABLE auxb.t3(a, b)" {}
436 4 "CREATE TEMPORARY TABLE main.xxx(x)" {}
437 }
438 drop_all_tables
439 do_createtable_tests 1.5.2 -tclquery {
440 unset -nocomplain X
441 array set X [table_list]
442 list $X(main) $X(temp) $X(auxa) $X(auxb)
443 } {
444 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}}
445 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}}
446 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}}
447 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}}
448 }
450 # EVIDENCE-OF: R-00917-09393 If no database name is specified and the
451 # TEMP keyword is not present then the table is created in the main
452 # database.
453 #
454 drop_all_tables
455 do_createtable_tests 1.6 -tclquery {
456 unset -nocomplain X
457 array set X [table_list]
458 list $X(main) $X(temp) $X(auxa) $X(auxb)
459 } {
460 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}}
461 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}}
462 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}}
463 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}}
464 }
466 drop_all_tables
467 do_execsql_test e_createtable-1.7.0 {
468 CREATE TABLE t1(x, y);
469 CREATE INDEX i1 ON t1(x);
470 CREATE VIEW v1 AS SELECT * FROM t1;
472 CREATE TABLE auxa.tbl1(x, y);
473 CREATE INDEX auxa.idx1 ON tbl1(x);
474 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
475 } {}
477 # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create
478 # a new table in a database that already contains a table, index or view
479 # of the same name.
480 #
481 # Test cases 1.7.1.* verify that creating a table in a database with a
482 # table/index/view of the same name does fail. 1.7.2.* tests that creating
483 # a table with the same name as a table/index/view in a different database
484 # is Ok.
485 #
486 do_createtable_tests 1.7.1 -error { %s } {
487 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}}
488 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}}
489 3 "CREATE TABLE v1(a, b)" {{table v1 already exists}}
490 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}}
491 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}}
492 6 "CREATE TABLE auxa.view1(a, b)" {{table view1 already exists}}
493 }
494 do_createtable_tests 1.7.2 {
495 1 "CREATE TABLE auxa.t1(a, b)" {}
496 2 "CREATE TABLE auxa.i1(a, b)" {}
497 3 "CREATE TABLE auxa.v1(a, b)" {}
498 4 "CREATE TABLE tbl1(a, b)" {}
499 5 "CREATE TABLE idx1(a, b)" {}
500 6 "CREATE TABLE view1(a, b)" {}
501 }
503 # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is
504 # specified as part of the CREATE TABLE statement and a table or view of
505 # the same name already exists, the CREATE TABLE command simply has no
506 # effect (and no error message is returned).
507 #
508 drop_all_tables
509 do_execsql_test e_createtable-1.8.0 {
510 CREATE TABLE t1(x, y);
511 CREATE INDEX i1 ON t1(x);
512 CREATE VIEW v1 AS SELECT * FROM t1;
513 CREATE TABLE auxa.tbl1(x, y);
514 CREATE INDEX auxa.idx1 ON tbl1(x);
515 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
516 } {}
517 do_createtable_tests 1.8 {
518 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {}
519 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {}
520 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {}
521 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {}
522 }
524 # EVIDENCE-OF: R-16465-40078 An error is still returned if the table
525 # cannot be created because of an existing index, even if the "IF NOT
526 # EXISTS" clause is specified.
527 #
528 do_createtable_tests 1.9 -error { %s } {
529 1 "CREATE TABLE IF NOT EXISTS i1(a, b)"
530 {{there is already an index named i1}}
531 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"
532 {{there is already an index named idx1}}
533 }
535 # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that
536 # has the same name as an existing trigger.
537 #
538 drop_all_tables
539 do_execsql_test e_createtable-1.10.0 {
540 CREATE TABLE t1(x, y);
541 CREATE TABLE auxb.t2(x, y);
543 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
544 SELECT 1;
545 END;
546 CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
547 SELECT 1;
548 END;
549 } {}
550 do_createtable_tests 1.10 {
551 1 "CREATE TABLE tr1(a, b)" {}
552 2 "CREATE TABLE tr2(a, b)" {}
553 3 "CREATE TABLE auxb.tr1(a, b)" {}
554 4 "CREATE TABLE auxb.tr2(a, b)" {}
555 }
557 # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE
558 # statement.
559 #
560 drop_all_tables
561 do_execsql_test e_createtable-1.11.0 {
562 CREATE TABLE t1(a, b);
563 CREATE TABLE t2(a, b);
564 CREATE TABLE auxa.t3(a, b);
565 CREATE TABLE auxa.t4(a, b);
566 } {}
568 do_execsql_test e_createtable-1.11.1.1 {
569 SELECT * FROM t1;
570 SELECT * FROM t2;
571 SELECT * FROM t3;
572 SELECT * FROM t4;
573 } {}
574 do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {}
575 do_catchsql_test e_createtable-1.11.1.3 {
576 SELECT * FROM t1
577 } {1 {no such table: t1}}
578 do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {}
579 do_catchsql_test e_createtable-1.11.1.5 {
580 SELECT * FROM t3
581 } {1 {no such table: t3}}
583 do_execsql_test e_createtable-1.11.2.1 {
584 SELECT name FROM sqlite_master;
585 SELECT name FROM auxa.sqlite_master;
586 } {t2 t4}
587 do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {}
588 do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {}
589 do_execsql_test e_createtable-1.11.2.4 {
590 SELECT name FROM sqlite_master;
591 SELECT name FROM auxa.sqlite_master;
592 } {}
594 #-------------------------------------------------------------------------
595 # Test cases e_createtable-2.* - test statements related to the CREATE
596 # TABLE AS ... SELECT statement.
597 #
599 # Three Tcl commands:
600 #
601 # select_column_names SQL
602 # The argument must be a SELECT statement. Return a list of the names
603 # of the columns of the result-set that would be returned by executing
604 # the SELECT.
605 #
606 # table_column_names TBL
607 # The argument must be a table name. Return a list of column names, from
608 # left to right, for the table.
609 #
610 # table_column_decltypes TBL
611 # The argument must be a table name. Return a list of column declared
612 # types, from left to right, for the table.
613 #
614 proc sci {select cmd} {
615 set res [list]
616 set STMT [sqlite3_prepare_v2 db $select -1 dummy]
617 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
618 lappend res [$cmd $STMT $i]
619 }
620 sqlite3_finalize $STMT
621 set res
622 }
623 proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
624 proc select_column_names {sql} { sci $sql sqlite3_column_name }
625 proc table_column_names {tbl} { tci $tbl sqlite3_column_name }
626 proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }
628 # Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
629 #
630 drop_all_tables
631 do_execsql_test e_createtable-2.0 {
632 CREATE TABLE t1(a, b, c);
633 CREATE TABLE t2(d, e, f);
634 CREATE TABLE t3(g BIGINT, h VARCHAR(10));
635 CREATE TABLE t4(i BLOB, j ANYOLDATA);
636 CREATE TABLE t5(k FLOAT, l INTEGER);
637 CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
638 CREATE TABLE t7(x INTEGER PRIMARY KEY);
639 CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
640 CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
641 } {}
643 # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
644 # the rows returned by the SELECT statement. The name of each column is
645 # the same as the name of the corresponding column in the result set of
646 # the SELECT statement.
647 #
648 do_createtable_tests 2.1 -tclquery {
649 table_column_names x1
650 } -repair {
651 catchsql { DROP TABLE x1 }
652 } {
653 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c}
654 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a}
655 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f}
656 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)}
657 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
658 }
660 # EVIDENCE-OF: R-37111-22855 The declared type of each column is
661 # determined by the expression affinity of the corresponding expression
662 # in the result set of the SELECT statement, as follows: Expression
663 # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
664 # REAL "REAL" NONE "" (empty string)
665 #
666 do_createtable_tests 2.2 -tclquery {
667 table_column_decltypes x1
668 } -repair {
669 catchsql { DROP TABLE x1 }
670 } {
671 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""}
672 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT}
673 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM}
674 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT}
675 }
677 # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
678 # no PRIMARY KEY and no constraints of any kind. The default value of
679 # each column is NULL. The default collation sequence for each column of
680 # the new table is BINARY.
681 #
682 # The following tests create tables based on SELECT statements that read
683 # from tables that have primary keys, constraints and explicit default
684 # collation sequences. None of this is transfered to the definition of
685 # the new table as stored in the sqlite_master table.
686 #
687 # Tests 2.3.2.* show that the default value of each column is NULL.
688 #
689 do_createtable_tests 2.3.1 -query {
690 SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
691 } {
692 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
693 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
694 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
695 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
696 }
697 do_execsql_test e_createtable-2.3.2.1 {
698 INSERT INTO x1 DEFAULT VALUES;
699 INSERT INTO x2 DEFAULT VALUES;
700 INSERT INTO x3 DEFAULT VALUES;
701 INSERT INTO x4 DEFAULT VALUES;
702 } {}
703 db nullvalue null
704 do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
705 do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
706 do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
707 do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
708 db nullvalue {}
710 drop_all_tables
711 do_execsql_test e_createtable-2.4.0 {
712 CREATE TABLE t1(x, y);
713 INSERT INTO t1 VALUES('i', 'one');
714 INSERT INTO t1 VALUES('ii', 'two');
715 INSERT INTO t1 VALUES('iii', 'three');
716 } {}
718 # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
719 # initially populated with the rows of data returned by the SELECT
720 # statement.
721 #
722 # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
723 # rowid values, starting with 1, in the order that they are returned by
724 # the SELECT statement.
725 #
726 # Each test case below is specified as the name of a table to create
727 # using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
728 # creating it. The table is created.
729 #
730 # Test cases 2.4.*.1 check that after it has been created, the data in the
731 # table is the same as the data returned by the SELECT statement executed as
732 # a standalone command, verifying the first testable statement above.
733 #
734 # Test cases 2.4.*.2 check that the rowids were allocated contiguously
735 # as required by the second testable statement above. That the rowids
736 # from the contiguous block were allocated to rows in the order rows are
737 # returned by the SELECT statement is verified by 2.4.*.1.
738 #
739 # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
740 # creates and populates a database table based on the results of a
741 # SELECT statement.
742 #
743 # The above is also considered to be tested by the following. It is
744 # clear that tables are being created and populated by the command in
745 # question.
746 #
747 foreach {tn tbl select} {
748 1 x1 "SELECT * FROM t1"
749 2 x2 "SELECT * FROM t1 ORDER BY x DESC"
750 3 x3 "SELECT * FROM t1 ORDER BY x ASC"
751 } {
752 # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
753 execsql [subst {CREATE TABLE $tbl AS $select}]
755 # Check that the rows inserted into the table, sorted in ascending rowid
756 # order, match those returned by executing the SELECT statement as a
757 # standalone command.
758 do_execsql_test e_createtable-2.4.$tn.1 [subst {
759 SELECT * FROM $tbl ORDER BY rowid;
760 }] [execsql $select]
762 # Check that the rowids in the new table are a contiguous block starting
763 # with rowid 1. Note that this will fail if SELECT statement $select
764 # returns 0 rows (as max(rowid) will be NULL).
765 do_execsql_test e_createtable-2.4.$tn.2 [subst {
766 SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
767 }] {1 1}
768 }
770 #--------------------------------------------------------------------------
771 # Test cases for column defintions in CREATE TABLE statements that do not
772 # use a SELECT statement. Not including data constraints. In other words,
773 # tests for the specification of:
774 #
775 # * declared types,
776 # * default values, and
777 # * default collation sequences.
778 #
780 # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not
781 # restrict the type of data that may be inserted into a column based on
782 # the columns declared type.
783 #
784 # Test this by creating a few tables with varied declared types, then
785 # inserting various different types of values into them.
786 #
787 drop_all_tables
788 do_execsql_test e_createtable-3.1.0 {
789 CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
790 CREATE TABLE t2(a DATETIME, b STRING, c REAL);
791 CREATE TABLE t3(o, t);
792 } {}
794 # value type -> declared column type
795 # ----------------------------------
796 # integer -> VARCHAR(10)
797 # string -> INTEGER
798 # blob -> DOUBLE
799 #
800 do_execsql_test e_createtable-3.1.1 {
801 INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
802 SELECT * FROM t1;
803 } {14 {quite a lengthy string} UVU}
805 # string -> DATETIME
806 # integer -> STRING
807 # time -> REAL
808 #
809 do_execsql_test e_createtable-3.1.2 {
810 INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
811 SELECT * FROM t2;
812 } {{not a datetime} 13 12:41:59}
814 # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to
815 # determine the affinity of the column only.
816 #
817 # Affinities are tested in more detail elsewhere (see document
818 # datatype3.html). Here, just test that affinity transformations
819 # consistent with the expected affinity of each column (based on
820 # the declared type) appear to take place.
821 #
822 # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL
823 # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL
824 # Affinities of t3 (test cases 3.2.3.*): NONE, NONE
825 #
826 do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}
828 do_createtable_tests 3.2.1 -query {
829 SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;
830 } {
831 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0}
832 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0}
833 }
834 do_createtable_tests 3.2.2 -query {
835 SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;
836 } {
837 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0}
838 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0}
839 }
840 do_createtable_tests 3.2.3 -query {
841 SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;
842 } {
843 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'}
844 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0}
845 }
847 # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause
848 # attached to a column definition, then the default value of the column
849 # is NULL.
850 #
851 # None of the columns in table t1 have an explicit DEFAULT clause.
852 # So testing that the default value of all columns in table t1 is
853 # NULL serves to verify the above.
854 #
855 do_createtable_tests 3.2.3 -query {
856 SELECT quote(x), quote(y), quote(z) FROM t1
857 } -repair {
858 execsql { DELETE FROM t1 }
859 } {
860 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL}
861 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'}
862 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL}
863 }
865 # EVIDENCE-OF: R-07343-35026 An explicit DEFAULT clause may specify that
866 # the default value is NULL, a string constant, a blob constant, a
867 # signed-number, or any constant expression enclosed in parentheses. A
868 # default value may also be one of the special case-independent keywords
869 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
870 #
871 do_execsql_test e_createtable-3.3.1 {
872 CREATE TABLE t4(
873 a DEFAULT NULL,
874 b DEFAULT 'string constant',
875 c DEFAULT X'424C4F42',
876 d DEFAULT 1,
877 e DEFAULT -1,
878 f DEFAULT 3.14,
879 g DEFAULT -3.14,
880 h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
881 i DEFAULT CURRENT_TIME,
882 j DEFAULT CURRENT_DATE,
883 k DEFAULT CURRENT_TIMESTAMP
884 );
885 } {}
887 # EVIDENCE-OF: R-18415-27776 For the purposes of the DEFAULT clause, an
888 # expression is considered constant if it does contains no sub-queries,
889 # column or table references, bound parameters, or string literals
890 # enclosed in double-quotes instead of single-quotes.
891 #
892 do_createtable_tests 3.4.1 -error {
893 default value of column [x] is not constant
894 } {
895 1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {}
896 2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {}
897 3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {}
898 4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {}
899 5 {CREATE TABLE t5(x DEFAULT ( x!=?1 ))} {}
900 }
901 do_createtable_tests 3.4.2 -repair {
902 catchsql { DROP TABLE t5 }
903 } {
904 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {}
905 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {}
906 }
908 # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table
909 # by an INSERT statement that does not provide explicit values for all
910 # table columns the values stored in the new row are determined by their
911 # default values
912 #
913 # Verify this with some assert statements for which all, some and no
914 # columns lack explicit values.
915 #
916 set sqlite_current_time 1000000000
917 do_createtable_tests 3.5 -query {
918 SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f),
919 quote(g), quote(h), quote(i), quote(j), quote(k)
920 FROM t4 ORDER BY rowid DESC LIMIT 1;
921 } {
922 1 "INSERT INTO t4 DEFAULT VALUES" {
923 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14
924 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
925 }
927 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
928 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
929 }
931 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
932 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
933 }
935 4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {
936 1 2 3 4 5 6 7 8 9 10 11
937 }
938 }
940 # EVIDENCE-OF: R-12572-62501 If the default value of the column is a
941 # constant NULL, text, blob or signed-number value, then that value is
942 # used directly in the new row.
943 #
944 do_execsql_test e_createtable-3.6.1 {
945 CREATE TABLE t5(
946 a DEFAULT NULL,
947 b DEFAULT 'text value',
948 c DEFAULT X'424C4F42',
949 d DEFAULT -45678.6,
950 e DEFAULT 394507
951 );
952 } {}
953 do_execsql_test e_createtable-3.6.2 {
954 INSERT INTO t5 DEFAULT VALUES;
955 SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
956 } {NULL {'text value'} X'424C4F42' -45678.6 394507}
958 # EVIDENCE-OF: R-60616-50251 If the default value of a column is an
959 # expression in parentheses, then the expression is evaluated once for
960 # each row inserted and the results used in the new row.
961 #
962 # Test case 3.6.4 demonstrates that the expression is evaluated
963 # separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
964 # command.
965 #
966 set ::nextint 0
967 proc nextint {} { incr ::nextint }
968 db func nextint nextint
970 do_execsql_test e_createtable-3.7.1 {
971 CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
972 } {}
973 do_execsql_test e_createtable-3.7.2 {
974 INSERT INTO t6 DEFAULT VALUES;
975 SELECT quote(a), quote(b) FROM t6;
976 } {1 2}
977 do_execsql_test e_createtable-3.7.3 {
978 INSERT INTO t6(a) VALUES('X');
979 SELECT quote(a), quote(b) FROM t6;
980 } {1 2 'X' 3}
981 do_execsql_test e_createtable-3.7.4 {
982 INSERT INTO t6(a) SELECT a FROM t6;
983 SELECT quote(a), quote(b) FROM t6;
984 } {1 2 'X' 3 1 4 'X' 5}
986 # EVIDENCE-OF: R-15363-55230 If the default value of a column is
987 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used
988 # in the new row is a text representation of the current UTC date and/or
989 # time.
990 #
991 # This is difficult to test literally without knowing what time the
992 # user will run the tests. Instead, we test that the three cases
993 # above set the value to the current date and/or time according to
994 # the xCurrentTime() method of the VFS. Which is usually the same
995 # as UTC. In this case, however, we instrument it to always return
996 # a time equivalent to "2001-09-09 01:46:40 UTC".
997 #
998 set sqlite_current_time 1000000000
999 do_execsql_test e_createtable-3.8.1 {
1000 CREATE TABLE t7(
1001 a DEFAULT CURRENT_TIME,
1002 b DEFAULT CURRENT_DATE,
1003 c DEFAULT CURRENT_TIMESTAMP
1004 );
1005 } {}
1006 do_execsql_test e_createtable-3.8.2 {
1007 INSERT INTO t7 DEFAULT VALUES;
1008 SELECT quote(a), quote(b), quote(c) FROM t7;
1009 } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}
1012 # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value
1013 # is "HH:MM:SS".
1014 #
1015 # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
1016 #
1017 # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is
1018 # "YYYY-MM-DD HH:MM:SS".
1019 #
1020 # The three above are demonstrated by tests 1, 2 and 3 below.
1021 # Respectively.
1022 #
1023 do_createtable_tests 3.8.3 -query {
1024 SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;
1025 } {
1026 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}
1027 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}
1028 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}
1029 }
1031 # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a
1032 # collating sequence to use as the default collation sequence for the
1033 # column.
1034 #
1035 # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the
1036 # default collation sequence is BINARY.
1037 #
1038 do_execsql_test e_createtable-3-9.1 {
1039 CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
1040 INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc');
1041 INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc ');
1042 INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC ');
1043 INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC');
1044 } {}
1045 do_createtable_tests 3.9 {
1046 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }}
1047 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }}
1048 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }}
1049 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }}
1050 }
1052 # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
1053 # by the SQLITE_MAX_COLUMN compile-time parameter.
1054 #
1055 proc columns {n} {
1056 set res [list]
1057 for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
1058 join $res ", "
1059 }
1060 do_execsql_test e_createtable-3.10.1 [subst {
1061 CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
1062 }] {}
1063 do_catchsql_test e_createtable-3.10.2 [subst {
1064 CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
1065 }] {1 {too many columns on t10}}
1067 # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
1068 # runtime using the sqlite3_limit() C/C++ interface.
1069 #
1070 # A 30,000 byte blob consumes 30,003 bytes of record space. A record
1071 # that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
1072 # 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
1073 # at runtime, are based on this calculation.
1074 #
1075 sqlite3_limit db SQLITE_LIMIT_COLUMN 500
1076 do_execsql_test e_createtable-3.11.1 [subst {
1077 CREATE TABLE t10([columns 500]);
1078 }] {}
1079 do_catchsql_test e_createtable-3.11.2 [subst {
1080 CREATE TABLE t11([columns 501]);
1081 }] {1 {too many columns on t11}}
1083 # Check that it is not possible to raise the column limit above its
1084 # default compile time value.
1085 #
1086 sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
1087 do_catchsql_test e_createtable-3.11.3 [subst {
1088 CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
1089 }] {1 {too many columns on t11}}
1091 sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
1092 do_execsql_test e_createtable-3.11.4 {
1093 CREATE TABLE t12(a, b, c);
1094 INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
1095 } {}
1096 do_catchsql_test e_createtable-3.11.5 {
1097 INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
1098 } {1 {string or blob too big}}
1100 #-------------------------------------------------------------------------
1101 # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT
1102 # NULL and CHECK constraints).
1103 #
1105 # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
1106 # PRIMARY KEY.
1107 #
1108 # EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY
1109 # KEY clause appears in a CREATE TABLE statement.
1110 #
1111 # To test the two above, show that zero primary keys is Ok, one primary
1112 # key is Ok, and two or more primary keys is an error.
1113 #
1114 drop_all_tables
1115 do_createtable_tests 4.1.1 {
1116 1 "CREATE TABLE t1(a, b, c)" {}
1117 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {}
1118 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {}
1119 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {}
1120 }
1121 do_createtable_tests 4.1.2 -error {
1122 table "t5" has more than one primary key
1123 } {
1124 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {}
1125 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {}
1126 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {}
1127 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
1128 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {}
1129 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {}
1130 }
1132 # EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary
1133 # tables but is required for WITHOUT ROWID tables.
1134 #
1135 do_catchsql_test 4.1.3 {
1136 CREATE TABLE t6(a, b); --ok
1137 } {0 {}}
1138 do_catchsql_test 4.1.4 {
1139 CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY
1140 } {1 {PRIMARY KEY missing on table t7}}
1143 proc table_pk {tbl} {
1144 set pk [list]
1145 db eval "pragma table_info($tbl)" a {
1146 if {$a(pk)} { lappend pk $a(name) }
1147 }
1148 set pk
1149 }
1151 # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
1152 # column definition, then the primary key for the table consists of that
1153 # single column.
1154 #
1155 # The above is tested by 4.2.1.*
1156 #
1157 # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
1158 # a table-constraint, then the primary key of the table consists of the
1159 # list of columns specified as part of the PRIMARY KEY clause.
1160 #
1161 # The above is tested by 4.2.2.*
1162 #
1163 do_createtable_tests 4.2 -repair {
1164 catchsql { DROP TABLE t5 }
1165 } -tclquery {
1166 table_pk t5
1167 } {
1168 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}
1169 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a}
1171 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a}
1172 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c}
1173 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}
1174 }
1176 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
1177 # have a unique combination of values in its primary key columns.
1178 #
1179 # EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts
1180 # to modify the table content so that two or more rows have identical
1181 # primary key values, that is a constraint violation.
1182 #
1183 drop_all_tables
1184 do_execsql_test 4.3.0 {
1185 CREATE TABLE t1(x PRIMARY KEY, y);
1186 INSERT INTO t1 VALUES(0, 'zero');
1187 INSERT INTO t1 VALUES(45.5, 'one');
1188 INSERT INTO t1 VALUES('brambles', 'two');
1189 INSERT INTO t1 VALUES(X'ABCDEF', 'three');
1191 CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
1192 INSERT INTO t2 VALUES(0, 'zero');
1193 INSERT INTO t2 VALUES(45.5, 'one');
1194 INSERT INTO t2 VALUES('brambles', 'two');
1195 INSERT INTO t2 VALUES(X'ABCDEF', 'three');
1196 } {}
1198 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} {
1199 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"}
1200 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"}
1201 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"}
1202 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"}
1203 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"}
1204 }
1205 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} {
1206 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"}
1207 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"}
1208 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"}
1209 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"}
1210 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"}
1211 }
1212 do_createtable_tests 4.3.2 {
1213 1 "INSERT INTO t1 VALUES(-1, 0)" {}
1214 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {}
1215 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {}
1216 4 "INSERT INTO t1 VALUES('bramble', 'abc')" {}
1217 5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {}
1219 6 "INSERT INTO t2 VALUES(0, 0)" {}
1220 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {}
1221 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {}
1222 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {}
1223 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {}
1224 }
1225 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} {
1226 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"}
1227 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"}
1228 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"}
1229 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"}
1230 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"}
1231 }
1232 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} {
1233 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"}
1234 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"
1235 {"columns x, y are"}
1236 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
1237 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'"
1238 {"columns x, y are"}
1239 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'"
1240 {"columns x, y are"}
1241 }
1244 # EVIDENCE-OF: R-52572-02078 For the purposes of determining the
1245 # uniqueness of primary key values, NULL values are considered distinct
1246 # from all other values, including other NULLs.
1247 #
1248 do_createtable_tests 4.4 {
1249 1 "INSERT INTO t1 VALUES(NULL, 0)" {}
1250 2 "INSERT INTO t1 VALUES(NULL, 0)" {}
1251 3 "INSERT INTO t1 VALUES(NULL, 0)" {}
1253 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {}
1254 5 "INSERT INTO t2 VALUES(NULL, 'one')" {}
1255 6 "INSERT INTO t2 VALUES(NULL, 'two')" {}
1256 7 "INSERT INTO t2 VALUES(NULL, 'three')" {}
1258 8 "INSERT INTO t2 VALUES(0, NULL)" {}
1259 9 "INSERT INTO t2 VALUES(45.5, NULL)" {}
1260 10 "INSERT INTO t2 VALUES(0.0, NULL)" {}
1261 11 "INSERT INTO t2 VALUES('brambles', NULL)" {}
1262 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {}
1264 13 "INSERT INTO t2 VALUES(NULL, NULL)" {}
1265 14 "INSERT INTO t2 VALUES(NULL, NULL)" {}
1266 }
1268 # EVIDENCE-OF: R-35113-43214 Unless the column is an INTEGER PRIMARY KEY
1269 # or the table is a WITHOUT ROWID table or the column is declared NOT
1270 # NULL, SQLite allows NULL values in a PRIMARY KEY column.
1271 #
1272 # If the column is an integer primary key, attempting to insert a NULL
1273 # into the column triggers the auto-increment behavior. Attempting
1274 # to use UPDATE to set an ipk column to a NULL value is an error.
1275 #
1276 do_createtable_tests 4.5.1 {
1277 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3
1278 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6
1279 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7
1280 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2
1281 }
1282 do_execsql_test 4.5.2 {
1283 CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
1284 INSERT INTO t3 VALUES(1, NULL, 2);
1285 INSERT INTO t3 VALUES('x', NULL, 'y');
1286 SELECT u FROM t3;
1287 } {1 2}
1288 do_catchsql_test 4.5.3 {
1289 INSERT INTO t3 VALUES(2, 5, 3);
1290 UPDATE t3 SET u = NULL WHERE s = 2;
1291 } {1 {datatype mismatch}}
1292 do_catchsql_test 4.5.4 {
1293 CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID;
1294 INSERT INTO t4 VALUES(1, NULL, 2);
1295 } {1 {NOT NULL constraint failed: t4.u}}
1296 do_catchsql_test 4.5.5 {
1297 CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v);
1298 INSERT INTO t5 VALUES(1, NULL, 2);
1299 } {1 {NOT NULL constraint failed: t5.u}}
1301 # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
1302 # KEY constraint, except that a single table may have any number of
1303 # UNIQUE constraints.
1304 #
1305 drop_all_tables
1306 do_createtable_tests 4.6 {
1307 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {}
1308 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {}
1309 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
1310 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {}
1311 }
1313 # EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table,
1314 # each row must contain a unique combination of values in the columns
1315 # identified by the UNIQUE constraint.
1316 #
1317 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
1318 # have a unique combination of values in its primary key columns.
1319 #
1320 do_execsql_test 4.7.0 {
1321 INSERT INTO t1 VALUES(1, 2);
1322 INSERT INTO t1 VALUES(4.3, 5.5);
1323 INSERT INTO t1 VALUES('reveal', 'variableness');
1324 INSERT INTO t1 VALUES(X'123456', X'654321');
1326 INSERT INTO t4 VALUES('xyx', 1, 1);
1327 INSERT INTO t4 VALUES('xyx', 2, 1);
1328 INSERT INTO t4 VALUES('uvw', 1, 1);
1329 }
1330 do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} {
1331 1 "INSERT INTO t1 VALUES(1, 'one')" {{t1.a}}
1332 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{t1.a}}
1333 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{t1.a}}
1334 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{t1.a}}
1336 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{t1.a}}
1337 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{t1.a}}
1338 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{t1.a}}
1339 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{t1.a}}
1341 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{t4.a, t4.b, t4.c}}
1342 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{t4.a, t4.b, t4.c}}
1343 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{t4.a, t4.b, t4.c}}
1345 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{t4.a, t4.b, t4.c}}
1346 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{t4.a, t4.b, t4.c}}
1347 14 "UPDATE t4 SET a=0, b=0, c=0" {{t4.a, t4.b, t4.c}}
1348 }
1350 # EVIDENCE-OF: R-00404-17670 For the purposes of UNIQUE constraints,
1351 # NULL values are considered distinct from all other values, including
1352 # other NULLs.
1353 #
1354 do_createtable_tests 4.8 {
1355 1 "INSERT INTO t1 VALUES(NULL, NULL)" {}
1356 2 "INSERT INTO t1 VALUES(NULL, NULL)" {}
1357 3 "UPDATE t1 SET a = NULL" {}
1358 4 "UPDATE t1 SET b = NULL" {}
1360 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}
1361 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}
1362 7 "UPDATE t4 SET a = NULL" {}
1363 8 "UPDATE t4 SET b = NULL" {}
1364 9 "UPDATE t4 SET c = NULL" {}
1365 }
1367 # EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY
1368 # constraints are implemented by creating a unique index in the
1369 # database.
1370 do_createtable_tests 4.9 -repair drop_all_tables -query {
1371 SELECT count(*) FROM sqlite_master WHERE type='index'
1372 } {
1373 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1
1374 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0
1375 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1
1376 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2
1377 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2
1378 }
1380 # Obsolete: R-02252-33116 Such an index is used like any other index
1381 # in the database to optimize queries.
1382 #
1383 do_execsql_test 4.10.0 {
1384 CREATE TABLE t1(a, b PRIMARY KEY);
1385 CREATE TABLE t2(a, b, c, UNIQUE(b, c));
1386 }
1387 do_createtable_tests 4.10 {
1388 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"
1389 {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)}}
1391 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
1392 {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1}}
1394 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
1395 {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}}
1396 }
1398 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
1399 # column definition or specified as a table constraint. In practice it
1400 # makes no difference.
1401 #
1402 # All the tests that deal with CHECK constraints below (4.11.* and
1403 # 4.12.*) are run once for a table with the check constraint attached
1404 # to a column definition, and once with a table where the check
1405 # condition is specified as a table constraint.
1406 #
1407 # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
1408 # table or an existing row is updated, the expression associated with
1409 # each CHECK constraint is evaluated and cast to a NUMERIC value in the
1410 # same way as a CAST expression. If the result is zero (integer value 0
1411 # or real value 0.0), then a constraint violation has occurred.
1412 #
1413 drop_all_tables
1414 do_execsql_test 4.11 {
1415 CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
1416 CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
1417 INSERT INTO x1 VALUES('x', 'xx');
1418 INSERT INTO x1 VALUES('y', 'yy');
1419 INSERT INTO t1 SELECT * FROM x1;
1421 CREATE TABLE x2(a CHECK( a||b ), b);
1422 CREATE TABLE t2(a, b, CHECK( a||b ));
1423 INSERT INTO x2 VALUES(1, 'xx');
1424 INSERT INTO x2 VALUES(1, 'yy');
1425 INSERT INTO t2 SELECT * FROM x2;
1426 }
1428 do_createtable_tests 4.11 -error {CHECK constraint failed: %s} {
1429 1a "INSERT INTO x1 VALUES('one', 0)" {x1}
1430 1b "INSERT INTO t1 VALUES('one', -4.0)" {t1}
1432 2a "INSERT INTO x2 VALUES('abc', 1)" {x2}
1433 2b "INSERT INTO t2 VALUES('abc', 1)" {t2}
1435 3a "INSERT INTO x2 VALUES(0, 'abc')" {x2}
1436 3b "INSERT INTO t2 VALUES(0, 'abc')" {t2}
1438 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {t1}
1439 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {x1}
1441 4a "UPDATE x2 SET a='' WHERE rowid=1" {x2}
1442 4b "UPDATE t2 SET a='' WHERE rowid=1" {t2}
1443 }
1445 # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
1446 # or any other non-zero value, it is not a constraint violation.
1447 #
1448 do_createtable_tests 4.12 {
1449 1a "INSERT INTO x1 VALUES('one', NULL)" {}
1450 1b "INSERT INTO t1 VALUES('one', NULL)" {}
1452 2a "INSERT INTO x1 VALUES('one', 2)" {}
1453 2b "INSERT INTO t1 VALUES('one', 2)" {}
1455 3a "INSERT INTO x2 VALUES(1, 'abc')" {}
1456 3b "INSERT INTO t2 VALUES(1, 'abc')" {}
1457 }
1459 # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached
1460 # to a column definition, not specified as a table constraint.
1461 #
1462 drop_all_tables
1463 do_createtable_tests 4.13.1 {
1464 1 "CREATE TABLE t1(a NOT NULL, b)" {}
1465 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {}
1466 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {}
1467 }
1468 do_createtable_tests 4.13.2 -error {
1469 near "NOT": syntax error
1470 } {
1471 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {}
1472 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {}
1473 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {}
1474 }
1476 # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the
1477 # associated column may not contain a NULL value. Attempting to set the
1478 # column value to NULL when inserting a new row or updating an existing
1479 # one causes a constraint violation.
1480 #
1481 # These tests use the tables created by 4.13.
1482 #
1483 do_execsql_test 4.14.0 {
1484 INSERT INTO t1 VALUES('x', 'y');
1485 INSERT INTO t1 VALUES('z', NULL);
1487 INSERT INTO t2 VALUES('x', 'y');
1488 INSERT INTO t2 VALUES('z', NULL);
1490 INSERT INTO t3 VALUES('x', 'y', 'z');
1491 INSERT INTO t3 VALUES(1, 2, 3);
1492 }
1493 do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} {
1494 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a}
1495 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a}
1496 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c}
1497 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b}
1498 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a}
1499 }
1501 # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL
1502 # constraints may be explicitly assigned a default conflict resolution
1503 # algorithm by including a conflict-clause in their definitions.
1504 #
1505 # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE
1506 #
1507 # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL
1508 # and UNIQUE constraints, respectively.
1509 #
1510 drop_all_tables
1511 do_execsql_test 4.15.0 {
1512 CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
1513 CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
1514 CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
1515 CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
1516 CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
1517 CREATE TABLE t1_xx(a PRIMARY KEY, b);
1519 INSERT INTO t1_ab VALUES(1, 'one');
1520 INSERT INTO t1_ab VALUES(2, 'two');
1521 INSERT INTO t1_ro SELECT * FROM t1_ab;
1522 INSERT INTO t1_ig SELECT * FROM t1_ab;
1523 INSERT INTO t1_fa SELECT * FROM t1_ab;
1524 INSERT INTO t1_re SELECT * FROM t1_ab;
1525 INSERT INTO t1_xx SELECT * FROM t1_ab;
1527 CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
1528 CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
1529 CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
1530 CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
1531 CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
1532 CREATE TABLE t2_xx(a, b NOT NULL);
1534 INSERT INTO t2_ab VALUES(1, 'one');
1535 INSERT INTO t2_ab VALUES(2, 'two');
1536 INSERT INTO t2_ro SELECT * FROM t2_ab;
1537 INSERT INTO t2_ig SELECT * FROM t2_ab;
1538 INSERT INTO t2_fa SELECT * FROM t2_ab;
1539 INSERT INTO t2_re SELECT * FROM t2_ab;
1540 INSERT INTO t2_xx SELECT * FROM t2_ab;
1542 CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
1543 CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
1544 CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
1545 CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
1546 CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
1547 CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
1549 INSERT INTO t3_ab VALUES(1, 'one');
1550 INSERT INTO t3_ab VALUES(2, 'two');
1551 INSERT INTO t3_ro SELECT * FROM t3_ab;
1552 INSERT INTO t3_ig SELECT * FROM t3_ab;
1553 INSERT INTO t3_fa SELECT * FROM t3_ab;
1554 INSERT INTO t3_re SELECT * FROM t3_ab;
1555 INSERT INTO t3_xx SELECT * FROM t3_ab;
1556 }
1558 foreach {tn tbl res ac data} {
1559 1 t1_ab {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three}
1560 2 t1_ro {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two}
1561 3 t1_fa {1 {UNIQUE constraint failed: t1_fa.a}} 0 {1 one 2 two 3 three 4 string}
1562 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
1563 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
1564 6 t1_xx {1 {UNIQUE constraint failed: t1_xx.a}} 0 {1 one 2 two 3 three}
1565 } {
1566 catchsql COMMIT
1567 do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1569 do_catchsql_test 4.15.$tn.2 "
1570 INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
1571 " $res
1573 do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
1574 do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
1575 }
1576 foreach {tn tbl res ac data} {
1577 1 t2_ab {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three}
1578 2 t2_ro {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two}
1579 3 t2_fa {1 {NOT NULL constraint failed: t2_fa.b}} 0 {1 one 2 two 3 three 4 xx}
1580 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
1581 5 t2_re {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three}
1582 6 t2_xx {1 {NOT NULL constraint failed: t2_xx.b}} 0 {1 one 2 two 3 three}
1583 } {
1584 catchsql COMMIT
1585 do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1587 do_catchsql_test 4.16.$tn.2 "
1588 INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
1589 " $res
1591 do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
1592 do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
1593 }
1594 foreach {tn tbl res ac data} {
1595 1 t3_ab {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}}
1596 0 {1 one 2 two 3 three}
1597 2 t3_ro {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}}
1598 1 {1 one 2 two}
1599 3 t3_fa {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}}
1600 0 {1 one 2 two 3 three 4 three}
1601 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
1602 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
1603 6 t3_xx {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}}
1604 0 {1 one 2 two 3 three}
1605 } {
1606 catchsql COMMIT
1607 do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1609 do_catchsql_test 4.17.$tn.2 "
1610 INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
1611 " $res
1613 do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
1614 do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data
1615 }
1616 catchsql COMMIT
1618 # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
1619 # include a conflict-clause or it is a CHECK constraint, the default
1620 # conflict resolution algorithm is ABORT.
1621 #
1622 # The first half of the above is tested along with explicit ON
1623 # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx
1624 # and t3_xx). The following just tests that the default conflict
1625 # handling for CHECK constraints is ABORT.
1626 #
1627 do_execsql_test 4.18.1 {
1628 CREATE TABLE t4(a, b CHECK (b!=10));
1629 INSERT INTO t4 VALUES(1, 2);
1630 INSERT INTO t4 VALUES(3, 4);
1631 }
1632 do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
1633 do_catchsql_test 4.18.3 {
1634 INSERT INTO t4 SELECT a+4, b+4 FROM t4
1635 } {1 {CHECK constraint failed: t4}}
1636 do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
1637 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
1639 # EVIDENCE-OF: R-19114-56113 Different constraints within the same table
1640 # may have different default conflict resolution algorithms.
1641 #
1642 do_execsql_test 4.19.0 {
1643 CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
1644 }
1645 do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
1646 do_execsql_test 4.19.2 { SELECT * FROM t5 } {}
1647 do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
1648 {1 {NOT NULL constraint failed: t5.b}}
1649 do_execsql_test 4.19.4 { SELECT * FROM t5 } {}
1651 #------------------------------------------------------------------------
1652 # Tests for INTEGER PRIMARY KEY and rowid related statements.
1653 #
1655 # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
1656 # of the special case-independent names "rowid", "oid", or "_rowid_" in
1657 # place of a column name.
1658 #
1659 # EVIDENCE-OF: R-06726-07466 A column name can be any of the names
1660 # defined in the CREATE TABLE statement or one of the following special
1661 # identifiers: "ROWID", "OID", or "_ROWID_".
1662 #
1663 drop_all_tables
1664 do_execsql_test 5.1.0 {
1665 CREATE TABLE t1(x, y);
1666 INSERT INTO t1 VALUES('one', 'first');
1667 INSERT INTO t1 VALUES('two', 'second');
1668 INSERT INTO t1 VALUES('three', 'third');
1669 }
1670 do_createtable_tests 5.1 {
1671 1 "SELECT rowid FROM t1" {1 2 3}
1672 2 "SELECT oid FROM t1" {1 2 3}
1673 3 "SELECT _rowid_ FROM t1" {1 2 3}
1674 4 "SELECT ROWID FROM t1" {1 2 3}
1675 5 "SELECT OID FROM t1" {1 2 3}
1676 6 "SELECT _ROWID_ FROM t1" {1 2 3}
1677 7 "SELECT RoWiD FROM t1" {1 2 3}
1678 8 "SELECT OiD FROM t1" {1 2 3}
1679 9 "SELECT _RoWiD_ FROM t1" {1 2 3}
1680 }
1682 # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
1683 # named "rowid", "oid" or "_rowid_", then that name always refers the
1684 # explicitly declared column and cannot be used to retrieve the integer
1685 # rowid value.
1686 #
1687 # EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the
1688 # row key if the CREATE TABLE statement does not define a real column
1689 # with the same name.
1690 #
1691 do_execsql_test 5.2.0 {
1692 CREATE TABLE t2(oid, b);
1693 CREATE TABLE t3(a, _rowid_);
1694 CREATE TABLE t4(a, b, rowid);
1696 INSERT INTO t2 VALUES('one', 'two');
1697 INSERT INTO t2 VALUES('three', 'four');
1699 INSERT INTO t3 VALUES('five', 'six');
1700 INSERT INTO t3 VALUES('seven', 'eight');
1702 INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
1703 INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
1704 }
1705 do_createtable_tests 5.2 {
1706 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2}
1707 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight}
1708 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2}
1709 }
1712 # Argument $tbl is the name of a table in the database. Argument $col is
1713 # the name of one of the tables columns. Return 1 if $col is an alias for
1714 # the rowid, or 0 otherwise.
1715 #
1716 proc is_integer_primary_key {tbl col} {
1717 lindex [db eval [subst {
1718 DELETE FROM $tbl;
1719 INSERT INTO $tbl ($col) VALUES(0);
1720 SELECT (rowid==$col) FROM $tbl;
1721 DELETE FROM $tbl;
1722 }]] 0
1723 }
1725 # EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid
1726 # table has a primary key that consists of a single column and the
1727 # declared type of that column is "INTEGER" in any mixture of upper and
1728 # lower case, then the column becomes an alias for the rowid.
1729 #
1730 # EVIDENCE-OF: R-45951-08347 if the declaration of a column with
1731 # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
1732 # not become an alias for the rowid and is not classified as an integer
1733 # primary key.
1734 #
1735 do_createtable_tests 5.3 -tclquery {
1736 is_integer_primary_key t5 pk
1737 } -repair {
1738 catchsql { DROP TABLE t5 }
1739 } {
1740 1 "CREATE TABLE t5(pk integer primary key)" 1
1741 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1
1742 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1
1743 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0
1744 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0
1745 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0
1746 7 "CREATE TABLE t5(pk int primary key, v integer)" 0
1747 8 "CREATE TABLE t5(pk inTEger primary key)" 1
1748 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1
1749 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1
1750 }
1752 # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
1753 # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
1754 # key column to behave as an ordinary table column with integer affinity
1755 # and a unique index, not as an alias for the rowid.
1756 #
1757 do_execsql_test 5.4.1 {
1758 CREATE TABLE t6(pk INT primary key);
1759 CREATE TABLE t7(pk BIGINT primary key);
1760 CREATE TABLE t8(pk SHORT INTEGER primary key);
1761 CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
1762 }
1763 do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
1764 do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
1765 do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
1766 do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
1768 do_execsql_test 5.4.3 {
1769 INSERT INTO t6 VALUES('2.0');
1770 INSERT INTO t7 VALUES('2.0');
1771 INSERT INTO t8 VALUES('2.0');
1772 INSERT INTO t9 VALUES('2.0');
1773 SELECT typeof(pk), pk FROM t6;
1774 SELECT typeof(pk), pk FROM t7;
1775 SELECT typeof(pk), pk FROM t8;
1776 SELECT typeof(pk), pk FROM t9;
1777 } {integer 2 integer 2 integer 2 integer 2}
1779 do_catchsql_test 5.4.4.1 {
1780 INSERT INTO t6 VALUES(2)
1781 } {1 {UNIQUE constraint failed: t6.pk}}
1782 do_catchsql_test 5.4.4.2 {
1783 INSERT INTO t7 VALUES(2)
1784 } {1 {UNIQUE constraint failed: t7.pk}}
1785 do_catchsql_test 5.4.4.3 {
1786 INSERT INTO t8 VALUES(2)
1787 } {1 {UNIQUE constraint failed: t8.pk}}
1788 do_catchsql_test 5.4.4.4 {
1789 INSERT INTO t9 VALUES(2)
1790 } {1 {UNIQUE constraint failed: t9.pk}}
1792 # EVIDENCE-OF: R-56094-57830 the following three table declarations all
1793 # cause the column "x" to be an alias for the rowid (an integer primary
1794 # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
1795 # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
1796 # z, PRIMARY KEY(x DESC));
1797 #
1798 # EVIDENCE-OF: R-20149-25884 the following declaration does not result
1799 # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
1800 # KEY DESC, y, z);
1801 #
1802 do_createtable_tests 5 -tclquery {
1803 is_integer_primary_key t x
1804 } -repair {
1805 catchsql { DROP TABLE t }
1806 } {
1807 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1
1808 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1
1809 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
1810 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0
1811 }
1813 # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
1814 # UPDATE statement in the same way as any other column value can, either
1815 # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
1816 # using an alias created by an integer primary key.
1817 #
1818 do_execsql_test 5.7.0 {
1819 CREATE TABLE t10(a, b);
1820 INSERT INTO t10 VALUES('ten', 10);
1822 CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
1823 INSERT INTO t11 VALUES('ten', 10);
1824 }
1825 do_createtable_tests 5.7.1 -query {
1826 SELECT rowid, _rowid_, oid FROM t10;
1827 } {
1828 1 "UPDATE t10 SET rowid = 5" {5 5 5}
1829 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6}
1830 3 "UPDATE t10 SET oid = 7" {7 7 7}
1831 }
1832 do_createtable_tests 5.7.2 -query {
1833 SELECT rowid, _rowid_, oid, b FROM t11;
1834 } {
1835 1 "UPDATE t11 SET rowid = 5" {5 5 5 5}
1836 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
1837 3 "UPDATE t11 SET oid = 7" {7 7 7 7}
1838 4 "UPDATE t11 SET b = 8" {8 8 8 8}
1839 }
1841 # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
1842 # a value to use as the rowid for each row inserted.
1843 #
1844 do_createtable_tests 5.8.1 -query {
1845 SELECT rowid, _rowid_, oid FROM t10;
1846 } -repair {
1847 execsql { DELETE FROM t10 }
1848 } {
1849 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15}
1850 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16}
1851 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17}
1852 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3}
1853 }
1854 do_createtable_tests 5.8.2 -query {
1855 SELECT rowid, _rowid_, oid, b FROM t11;
1856 } -repair {
1857 execsql { DELETE FROM t11 }
1858 } {
1859 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15}
1860 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16}
1861 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17}
1862 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2}
1863 }
1865 # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
1866 # primary key or rowid column must contain integer values. Integer
1867 # primary key or rowid columns are not able to hold floating point
1868 # values, strings, BLOBs, or NULLs.
1869 #
1870 # This is considered by the tests for the following 3 statements,
1871 # which show that:
1872 #
1873 # 1. Attempts to UPDATE a rowid column to a non-integer value fail,
1874 # 2. Attempts to INSERT a real, string or blob value into a rowid
1875 # column fail, and
1876 # 3. Attempting to INSERT a NULL value into a rowid column causes the
1877 # system to automatically select an integer value to use.
1878 #
1881 # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
1882 # integer primary key or rowid column to a NULL or blob value, or to a
1883 # string or real value that cannot be losslessly converted to an
1884 # integer, a "datatype mismatch" error occurs and the statement is
1885 # aborted.
1886 #
1887 drop_all_tables
1888 do_execsql_test 5.9.0 {
1889 CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
1890 INSERT INTO t12 VALUES(5, 'five');
1891 }
1892 do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
1893 1 "UPDATE t12 SET x = 4" {integer 4}
1894 2 "UPDATE t12 SET x = 10.0" {integer 10}
1895 3 "UPDATE t12 SET x = '12.0'" {integer 12}
1896 4 "UPDATE t12 SET x = '-15.0'" {integer -15}
1897 }
1898 do_createtable_tests 5.9.2 -error {
1899 datatype mismatch
1900 } {
1901 1 "UPDATE t12 SET x = 4.1" {}
1902 2 "UPDATE t12 SET x = 'hello'" {}
1903 3 "UPDATE t12 SET x = NULL" {}
1904 4 "UPDATE t12 SET x = X'ABCD'" {}
1905 5 "UPDATE t12 SET x = X'3900'" {}
1906 6 "UPDATE t12 SET x = X'39'" {}
1907 }
1909 # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
1910 # blob value, or a string or real value that cannot be losslessly
1911 # converted to an integer into an integer primary key or rowid column, a
1912 # "datatype mismatch" error occurs and the statement is aborted.
1913 #
1914 do_execsql_test 5.10.0 { DELETE FROM t12 }
1915 do_createtable_tests 5.10.1 -error {
1916 datatype mismatch
1917 } {
1918 1 "INSERT INTO t12(x) VALUES(4.1)" {}
1919 2 "INSERT INTO t12(x) VALUES('hello')" {}
1920 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {}
1921 4 "INSERT INTO t12(x) VALUES(X'3900')" {}
1922 5 "INSERT INTO t12(x) VALUES(X'39')" {}
1923 }
1924 do_createtable_tests 5.10.2 -query {
1925 SELECT typeof(x), x FROM t12
1926 } -repair {
1927 execsql { DELETE FROM t12 }
1928 } {
1929 1 "INSERT INTO t12(x) VALUES(4)" {integer 4}
1930 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10}
1931 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12}
1932 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000}
1933 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
1934 }
1936 # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
1937 # NULL value into a rowid or integer primary key column, the system
1938 # chooses an integer value to use as the rowid automatically.
1939 #
1940 do_execsql_test 5.11.0 { DELETE FROM t12 }
1941 do_createtable_tests 5.11 -query {
1942 SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
1943 } {
1944 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1}
1945 2 "INSERT INTO t12(y) VALUES(5)" {integer 2}
1946 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3}
1947 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12"
1948 {integer 4 integer 5 integer 6}
1949 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
1950 {integer 7 integer 8 integer 9}
1951 }
1953 finish_test