000001 # 2010 September 25
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file implements tests to verify that the "testable statements" in
000013 # the lang_createtable.html document are correct.
000014 #
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018
000019 set ::testprefix e_createtable
000020
000021 # Test organization:
000022 #
000023 # e_createtable-0.*: Test that the syntax diagrams are correct.
000024 #
000025 # e_createtable-1.*: Test statements related to table and database names,
000026 # the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
000027 #
000028 # e_createtable-2.*: Test "CREATE TABLE AS" statements.
000029 #
000030
000031 proc do_createtable_tests {nm args} {
000032 uplevel do_select_tests [list e_createtable-$nm] $args
000033 }
000034
000035
000036 #-------------------------------------------------------------------------
000037 # This command returns a serialized tcl array mapping from the name of
000038 # each attached database to a list of tables in that database. For example,
000039 # if the database schema is created with:
000040 #
000041 # CREATE TABLE t1(x);
000042 # CREATE TEMP TABLE t2(x);
000043 # CREATE TEMP TABLE t3(x);
000044 #
000045 # Then this command returns "main t1 temp {t2 t3}".
000046 #
000047 proc table_list {} {
000048 set res [list]
000049 db eval { pragma database_list } a {
000050 set dbname $a(name)
000051 set master $a(name).sqlite_master
000052 if {$dbname == "temp"} { set master sqlite_temp_master }
000053 lappend res $dbname [
000054 db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
000055 ]
000056 }
000057 set res
000058 }
000059
000060
000061 do_createtable_tests 0.1.1 -repair {
000062 drop_all_tables
000063 } {
000064 1 "CREATE TABLE t1(c1 one)" {}
000065 2 "CREATE TABLE t1(c1 one two)" {}
000066 3 "CREATE TABLE t1(c1 one two three)" {}
000067 4 "CREATE TABLE t1(c1 one two three four)" {}
000068 5 "CREATE TABLE t1(c1 one two three four(14))" {}
000069 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {}
000070 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {}
000071 8 "CREATE TABLE t1(c1 var(1.0e10))" {}
000072 }
000073 do_createtable_tests 0.1.2 -error {
000074 near "%s": syntax error
000075 } {
000076 1 "CREATE TABLE t1(c1 one(number))" {number}
000077 }
000078
000079
000080 # syntax diagram column-constraint
000081 #
000082 do_createtable_tests 0.2.1 -repair {
000083 drop_all_tables
000084 execsql { CREATE TABLE t2(x PRIMARY KEY) }
000085 } {
000086 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {}
000087 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {}
000088 1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {}
000089 1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {}
000090
000091 2.1 "CREATE TABLE t1(c1 text NOT NULL)" {}
000092 2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {}
000093 2.3 "CREATE TABLE t1(c1 text NULL)" {}
000094 2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {}
000095
000096 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {}
000097 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {}
000098
000099 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {}
000100 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {}
000101
000102 5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {}
000103 5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {}
000104 5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {}
000105 5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {}
000106 5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {}
000107 5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {}
000108
000109 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {}
000110 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {}
000111
000112 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {}
000113 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {}
000114
000115 8.1 {
000116 CREATE TABLE t1(c1
000117 PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
000118 );
000119 } {}
000120 8.2 {
000121 CREATE TABLE t1(c1
000122 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY
000123 );
000124 } {}
000125 }
000126
000127 # -- syntax diagram table-constraint
000128 #
000129 do_createtable_tests 0.3.1 -repair {
000130 drop_all_tables
000131 execsql { CREATE TABLE t2(x PRIMARY KEY) }
000132 } {
000133 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {}
000134 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {}
000135 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {}
000136
000137 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {}
000138 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {}
000139 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {}
000140
000141 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {}
000142
000143 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {}
000144 }
000145
000146 # -- syntax diagram column-def
000147 #
000148 do_createtable_tests 0.4.1 -repair {
000149 drop_all_tables
000150 } {
000151 1 {CREATE TABLE t1(
000152 col1,
000153 col2 TEXT,
000154 col3 INTEGER UNIQUE,
000155 col4 VARCHAR(10, 10) PRIMARY KEY,
000156 "name with spaces" REFERENCES t1
000157 );
000158 } {}
000159 }
000160
000161 # -- syntax diagram create-table-stmt
000162 #
000163 do_createtable_tests 0.5.1 -repair {
000164 drop_all_tables
000165 execsql { CREATE TABLE t2(a, b, c) }
000166 } {
000167 1 "CREATE TABLE t1(a, b, c)" {}
000168 2 "CREATE TEMP TABLE t1(a, b, c)" {}
000169 3 "CREATE TEMPORARY TABLE t1(a, b, c)" {}
000170 4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {}
000171 5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {}
000172 6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {}
000173
000174 7 "CREATE TABLE main.t1(a, b, c)" {}
000175 8 "CREATE TEMP TABLE temp.t1(a, b, c)" {}
000176 9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {}
000177 10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {}
000178 11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {}
000179 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {}
000180
000181 13 "CREATE TABLE t1 AS SELECT * FROM t2" {}
000182 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {}
000183 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {}
000184 }
000185
000186 #
000187 # 1: Explicit parent-key columns.
000188 # 2: Implicit child-key columns.
000189 #
000190 # 1: MATCH FULL
000191 # 2: MATCH PARTIAL
000192 # 3: MATCH SIMPLE
000193 # 4: MATCH STICK
000194 # 5:
000195 #
000196 # 1: ON DELETE SET NULL
000197 # 2: ON DELETE SET DEFAULT
000198 # 3: ON DELETE CASCADE
000199 # 4: ON DELETE RESTRICT
000200 # 5: ON DELETE NO ACTION
000201 # 6:
000202 #
000203 # 1: ON UPDATE SET NULL
000204 # 2: ON UPDATE SET DEFAULT
000205 # 3: ON UPDATE CASCADE
000206 # 4: ON UPDATE RESTRICT
000207 # 5: ON UPDATE NO ACTION
000208 # 6:
000209 #
000210 # 1: NOT DEFERRABLE INITIALLY DEFERRED
000211 # 2: NOT DEFERRABLE INITIALLY IMMEDIATE
000212 # 3: NOT DEFERRABLE
000213 # 4: DEFERRABLE INITIALLY DEFERRED
000214 # 5: DEFERRABLE INITIALLY IMMEDIATE
000215 # 6: DEFERRABLE
000216 # 7:
000217 #
000218 do_createtable_tests 0.6.1 -repair {
000219 drop_all_tables
000220 execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
000221 execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
000222 } {
000223 11146 { CREATE TABLE t1(a
000224 REFERENCES t2(x) MATCH FULL
000225 ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
000226 )} {}
000227 11412 { CREATE TABLE t1(a
000228 REFERENCES t2(x)
000229 ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL
000230 NOT DEFERRABLE INITIALLY IMMEDIATE
000231 )} {}
000232 12135 { CREATE TABLE t1(a
000233 REFERENCES t2(x) MATCH PARTIAL
000234 ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
000235 )} {}
000236 12427 { CREATE TABLE t1(a
000237 REFERENCES t2(x) MATCH PARTIAL
000238 ON DELETE RESTRICT ON UPDATE SET DEFAULT
000239 )} {}
000240 12446 { CREATE TABLE t1(a
000241 REFERENCES t2(x) MATCH PARTIAL
000242 ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
000243 )} {}
000244 12522 { CREATE TABLE t1(a
000245 REFERENCES t2(x) MATCH PARTIAL
000246 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
000247 )} {}
000248 13133 { CREATE TABLE t1(a
000249 REFERENCES t2(x) MATCH SIMPLE
000250 ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
000251 )} {}
000252 13216 { CREATE TABLE t1(a
000253 REFERENCES t2(x) MATCH SIMPLE
000254 ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
000255 )} {}
000256 13263 { CREATE TABLE t1(a
000257 REFERENCES t2(x) MATCH SIMPLE
000258 ON DELETE SET DEFAULT NOT DEFERRABLE
000259 )} {}
000260 13421 { CREATE TABLE t1(a
000261 REFERENCES t2(x) MATCH SIMPLE
000262 ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
000263 )} {}
000264 13432 { CREATE TABLE t1(a
000265 REFERENCES t2(x) MATCH SIMPLE
000266 ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
000267 )} {}
000268 13523 { CREATE TABLE t1(a
000269 REFERENCES t2(x) MATCH SIMPLE
000270 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
000271 )} {}
000272 14336 { CREATE TABLE t1(a
000273 REFERENCES t2(x) MATCH STICK
000274 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
000275 )} {}
000276 14611 { CREATE TABLE t1(a
000277 REFERENCES t2(x) MATCH STICK
000278 ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
000279 )} {}
000280 15155 { CREATE TABLE t1(a
000281 REFERENCES t2(x)
000282 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
000283 )} {}
000284 15453 { CREATE TABLE t1(a
000285 REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
000286 )} {}
000287 15661 { CREATE TABLE t1(a
000288 REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
000289 )} {}
000290 21115 { CREATE TABLE t1(a
000291 REFERENCES t2 MATCH FULL
000292 ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
000293 )} {}
000294 21123 { CREATE TABLE t1(a
000295 REFERENCES t2 MATCH FULL
000296 ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
000297 )} {}
000298 21217 { CREATE TABLE t1(a
000299 REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL
000300 )} {}
000301 21362 { CREATE TABLE t1(a
000302 REFERENCES t2 MATCH FULL
000303 ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
000304 )} {}
000305 22143 { CREATE TABLE t1(a
000306 REFERENCES t2 MATCH PARTIAL
000307 ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
000308 )} {}
000309 22156 { CREATE TABLE t1(a
000310 REFERENCES t2 MATCH PARTIAL
000311 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
000312 )} {}
000313 22327 { CREATE TABLE t1(a
000314 REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT
000315 )} {}
000316 22663 { CREATE TABLE t1(a
000317 REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
000318 )} {}
000319 23236 { CREATE TABLE t1(a
000320 REFERENCES t2 MATCH SIMPLE
000321 ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
000322 )} {}
000323 24155 { CREATE TABLE t1(a
000324 REFERENCES t2 MATCH STICK
000325 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
000326 )} {}
000327 24522 { CREATE TABLE t1(a
000328 REFERENCES t2 MATCH STICK
000329 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
000330 )} {}
000331 24625 { CREATE TABLE t1(a
000332 REFERENCES t2 MATCH STICK
000333 ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
000334 )} {}
000335 25454 { CREATE TABLE t1(a
000336 REFERENCES t2
000337 ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
000338 )} {}
000339 }
000340
000341 #-------------------------------------------------------------------------
000342 # Test cases e_createtable-1.* - test statements related to table and
000343 # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS
000344 # clause.
000345 #
000346 drop_all_tables
000347 forcedelete test.db2 test.db3
000348
000349 do_execsql_test e_createtable-1.0 {
000350 ATTACH 'test.db2' AS auxa;
000351 ATTACH 'test.db3' AS auxb;
000352 } {}
000353
000354 # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are
000355 # reserved for internal use. It is an error to attempt to create a table
000356 # with a name that starts with "sqlite_".
000357 #
000358 do_createtable_tests 1.1.1 -error {
000359 object name reserved for internal use: %s
000360 } {
000361 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc
000362 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld
000363 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__
000364 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_
000365 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL
000366 }
000367 do_createtable_tests 1.1.2 {
000368 1 "CREATE TABLE sqlit_abc(a, b, c)" {}
000369 2 "CREATE TABLE temp.sqlitehelloworld(x)" {}
000370 3 {CREATE TABLE auxa."sqlite"(x, y)} {}
000371 4 {CREATE TABLE auxb."sqlite-"(z)} {}
000372 5 {CREATE TABLE "SQLITE-TBL"(z)} {}
000373 }
000374
000375
000376 # EVIDENCE-OF: R-18448-33677 If a schema-name is specified, it must be
000377 # either "main", "temp", or the name of an attached database.
000378 #
000379 # EVIDENCE-OF: R-39822-07822 In this case the new table is created in
000380 # the named database.
000381 #
000382 # Test cases 1.2.* test the first of the two requirements above. The
000383 # second is verified by cases 1.3.*.
000384 #
000385 do_createtable_tests 1.2.1 -error {
000386 unknown database %s
000387 } {
000388 1 "CREATE TABLE george.t1(a, b)" george
000389 2 "CREATE TABLE _.t1(a, b)" _
000390 }
000391 do_createtable_tests 1.2.2 {
000392 1 "CREATE TABLE main.abc(a, b, c)" {}
000393 2 "CREATE TABLE temp.helloworld(x)" {}
000394 3 {CREATE TABLE auxa."t 1"(x, y)} {}
000395 4 {CREATE TABLE auxb.xyz(z)} {}
000396 }
000397 drop_all_tables
000398 if {[permutation]!="maindbname"} {
000399 do_createtable_tests 1.3 -tclquery {
000400 unset -nocomplain X
000401 array set X [table_list]
000402 list $X(main) $X(temp) $X(auxa) $X(auxb)
000403 } {
000404 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}}
000405 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}}
000406 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}}
000407 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl}
000408 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}}
000409 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}}
000410 }
000411 }
000412
000413 # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs
000414 # between the "CREATE" and "TABLE" then the new table is created in the
000415 # temp database.
000416 #
000417 drop_all_tables
000418 if {[permutation]!="maindbname"} {
000419 do_createtable_tests 1.4 -tclquery {
000420 unset -nocomplain X
000421 array set X [table_list]
000422 list $X(main) $X(temp) $X(auxa) $X(auxb)
000423 } {
000424 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}}
000425 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}
000426 }
000427 }
000428
000429 # EVIDENCE-OF: R-23976-43329 It is an error to specify both a
000430 # schema-name and the TEMP or TEMPORARY keyword, unless the schema-name
000431 # is "temp".
000432 #
000433 drop_all_tables
000434 do_createtable_tests 1.5.1 -error {
000435 temporary table name must be unqualified
000436 } {
000437 1 "CREATE TEMP TABLE main.t1(a, b)" {}
000438 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {}
000439 3 "CREATE TEMP TABLE auxb.t3(a, b)" {}
000440 4 "CREATE TEMPORARY TABLE main.xxx(x)" {}
000441 }
000442 drop_all_tables
000443 if {[permutation]!="maindbname"} {
000444 do_createtable_tests 1.5.2 -tclquery {
000445 unset -nocomplain X
000446 array set X [table_list]
000447 list $X(main) $X(temp) $X(auxa) $X(auxb)
000448 } {
000449 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}}
000450 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}}
000451 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}}
000452 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}}
000453 }
000454 }
000455
000456 # EVIDENCE-OF: R-31997-24564 If no schema name is specified and the TEMP
000457 # keyword is not present then the table is created in the main database.
000458 #
000459 drop_all_tables
000460 if {[permutation]!="maindbname"} {
000461 do_createtable_tests 1.6 -tclquery {
000462 unset -nocomplain X
000463 array set X [table_list]
000464 list $X(main) $X(temp) $X(auxa) $X(auxb)
000465 } {
000466 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}}
000467 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}}
000468 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}}
000469 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}}
000470 }
000471 }
000472
000473 drop_all_tables
000474 do_execsql_test e_createtable-1.7.0 {
000475 CREATE TABLE t1(x, y);
000476 CREATE INDEX i1 ON t1(x);
000477 CREATE VIEW v1 AS SELECT * FROM t1;
000478
000479 CREATE TABLE auxa.tbl1(x, y);
000480 CREATE INDEX auxa.idx1 ON tbl1(x);
000481 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
000482 } {}
000483
000484 # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create
000485 # a new table in a database that already contains a table, index or view
000486 # of the same name.
000487 #
000488 # Test cases 1.7.1.* verify that creating a table in a database with a
000489 # table/index/view of the same name does fail. 1.7.2.* tests that creating
000490 # a table with the same name as a table/index/view in a different database
000491 # is Ok.
000492 #
000493 do_createtable_tests 1.7.1 -error { %s } {
000494 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}}
000495 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}}
000496 3 "CREATE TABLE v1(a, b)" {{view v1 already exists}}
000497 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}}
000498 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}}
000499 6 "CREATE TABLE auxa.view1(a, b)" {{view view1 already exists}}
000500 }
000501 do_createtable_tests 1.7.2 {
000502 1 "CREATE TABLE auxa.t1(a, b)" {}
000503 2 "CREATE TABLE auxa.i1(a, b)" {}
000504 3 "CREATE TABLE auxa.v1(a, b)" {}
000505 4 "CREATE TABLE tbl1(a, b)" {}
000506 5 "CREATE TABLE idx1(a, b)" {}
000507 6 "CREATE TABLE view1(a, b)" {}
000508 }
000509
000510 # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is
000511 # specified as part of the CREATE TABLE statement and a table or view of
000512 # the same name already exists, the CREATE TABLE command simply has no
000513 # effect (and no error message is returned).
000514 #
000515 drop_all_tables
000516 do_execsql_test e_createtable-1.8.0 {
000517 CREATE TABLE t1(x, y);
000518 CREATE INDEX i1 ON t1(x);
000519 CREATE VIEW v1 AS SELECT * FROM t1;
000520 CREATE TABLE auxa.tbl1(x, y);
000521 CREATE INDEX auxa.idx1 ON tbl1(x);
000522 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
000523 } {}
000524 do_createtable_tests 1.8 {
000525 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {}
000526 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {}
000527 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {}
000528 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {}
000529 }
000530
000531 # EVIDENCE-OF: R-16465-40078 An error is still returned if the table
000532 # cannot be created because of an existing index, even if the "IF NOT
000533 # EXISTS" clause is specified.
000534 #
000535 do_createtable_tests 1.9 -error { %s } {
000536 1 "CREATE TABLE IF NOT EXISTS i1(a, b)"
000537 {{there is already an index named i1}}
000538 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"
000539 {{there is already an index named idx1}}
000540 }
000541
000542 # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that
000543 # has the same name as an existing trigger.
000544 #
000545 drop_all_tables
000546 do_execsql_test e_createtable-1.10.0 {
000547 CREATE TABLE t1(x, y);
000548 CREATE TABLE auxb.t2(x, y);
000549
000550 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
000551 SELECT 1;
000552 END;
000553 CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
000554 SELECT 1;
000555 END;
000556 } {}
000557 do_createtable_tests 1.10 {
000558 1 "CREATE TABLE tr1(a, b)" {}
000559 2 "CREATE TABLE tr2(a, b)" {}
000560 3 "CREATE TABLE auxb.tr1(a, b)" {}
000561 4 "CREATE TABLE auxb.tr2(a, b)" {}
000562 }
000563
000564 # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE
000565 # statement.
000566 #
000567 drop_all_tables
000568 do_execsql_test e_createtable-1.11.0 {
000569 CREATE TABLE t1(a, b);
000570 CREATE TABLE t2(a, b);
000571 CREATE TABLE auxa.t3(a, b);
000572 CREATE TABLE auxa.t4(a, b);
000573 } {}
000574
000575 do_execsql_test e_createtable-1.11.1.1 {
000576 SELECT * FROM t1;
000577 SELECT * FROM t2;
000578 SELECT * FROM t3;
000579 SELECT * FROM t4;
000580 } {}
000581 do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {}
000582 do_catchsql_test e_createtable-1.11.1.3 {
000583 SELECT * FROM t1
000584 } {1 {no such table: t1}}
000585 do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {}
000586 do_catchsql_test e_createtable-1.11.1.5 {
000587 SELECT * FROM t3
000588 } {1 {no such table: t3}}
000589
000590 do_execsql_test e_createtable-1.11.2.1 {
000591 SELECT name FROM sqlite_master;
000592 SELECT name FROM auxa.sqlite_master;
000593 } {t2 t4}
000594 do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {}
000595 do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {}
000596 do_execsql_test e_createtable-1.11.2.4 {
000597 SELECT name FROM sqlite_master;
000598 SELECT name FROM auxa.sqlite_master;
000599 } {}
000600
000601 #-------------------------------------------------------------------------
000602 # Test cases e_createtable-2.* - test statements related to the CREATE
000603 # TABLE AS ... SELECT statement.
000604 #
000605
000606 # Three Tcl commands:
000607 #
000608 # select_column_names SQL
000609 # The argument must be a SELECT statement. Return a list of the names
000610 # of the columns of the result-set that would be returned by executing
000611 # the SELECT.
000612 #
000613 # table_column_names TBL
000614 # The argument must be a table name. Return a list of column names, from
000615 # left to right, for the table.
000616 #
000617 # table_column_decltypes TBL
000618 # The argument must be a table name. Return a list of column declared
000619 # types, from left to right, for the table.
000620 #
000621 proc sci {select cmd} {
000622 set res [list]
000623 set STMT [sqlite3_prepare_v2 db $select -1 dummy]
000624 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
000625 lappend res [$cmd $STMT $i]
000626 }
000627 sqlite3_finalize $STMT
000628 set res
000629 }
000630 proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
000631 proc select_column_names {sql} { sci $sql sqlite3_column_name }
000632 proc table_column_names {tbl} { tci $tbl sqlite3_column_name }
000633 proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }
000634
000635 # Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
000636 #
000637 drop_all_tables
000638 do_execsql_test e_createtable-2.0 {
000639 CREATE TABLE t1(a, b, c);
000640 CREATE TABLE t2(d, e, f);
000641 CREATE TABLE t3(g BIGINT, h VARCHAR(10));
000642 CREATE TABLE t4(i BLOB, j ANYOLDATA);
000643 CREATE TABLE t5(k FLOAT, l INTEGER);
000644 CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
000645 CREATE TABLE t7(x INTEGER PRIMARY KEY);
000646 CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
000647 CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
000648 } {}
000649
000650 # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
000651 # the rows returned by the SELECT statement. The name of each column is
000652 # the same as the name of the corresponding column in the result set of
000653 # the SELECT statement.
000654 #
000655 do_createtable_tests 2.1 -tclquery {
000656 table_column_names x1
000657 } -repair {
000658 catchsql { DROP TABLE x1 }
000659 } {
000660 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c}
000661 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a}
000662 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f}
000663 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)}
000664 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
000665 }
000666
000667 # EVIDENCE-OF: R-55407-45319 The declared type of each column is
000668 # determined by the expression affinity of the corresponding expression
000669 # in the result set of the SELECT statement, as follows: Expression
000670 # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
000671 # REAL "REAL" BLOB (a.k.a "NONE") "" (empty string)
000672 #
000673 do_createtable_tests 2.2 -tclquery {
000674 table_column_decltypes x1
000675 } -repair {
000676 catchsql { DROP TABLE x1 }
000677 } {
000678 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""}
000679 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT}
000680 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM}
000681 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT}
000682 }
000683
000684 # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
000685 # no PRIMARY KEY and no constraints of any kind. The default value of
000686 # each column is NULL. The default collation sequence for each column of
000687 # the new table is BINARY.
000688 #
000689 # The following tests create tables based on SELECT statements that read
000690 # from tables that have primary keys, constraints and explicit default
000691 # collation sequences. None of this is transfered to the definition of
000692 # the new table as stored in the sqlite_master table.
000693 #
000694 # Tests 2.3.2.* show that the default value of each column is NULL.
000695 #
000696 do_createtable_tests 2.3.1 -query {
000697 SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
000698 } {
000699 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
000700 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
000701 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
000702 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
000703 }
000704 do_execsql_test e_createtable-2.3.2.1 {
000705 INSERT INTO x1 DEFAULT VALUES;
000706 INSERT INTO x2 DEFAULT VALUES;
000707 INSERT INTO x3 DEFAULT VALUES;
000708 INSERT INTO x4 DEFAULT VALUES;
000709 } {}
000710 db nullvalue null
000711 do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
000712 do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
000713 do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
000714 do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
000715 db nullvalue {}
000716
000717 drop_all_tables
000718 do_execsql_test e_createtable-2.4.0 {
000719 CREATE TABLE t1(x, y);
000720 INSERT INTO t1 VALUES('i', 'one');
000721 INSERT INTO t1 VALUES('ii', 'two');
000722 INSERT INTO t1 VALUES('iii', 'three');
000723 } {}
000724
000725 # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
000726 # initially populated with the rows of data returned by the SELECT
000727 # statement.
000728 #
000729 # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
000730 # rowid values, starting with 1, in the order that they are returned by
000731 # the SELECT statement.
000732 #
000733 # Each test case below is specified as the name of a table to create
000734 # using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
000735 # creating it. The table is created.
000736 #
000737 # Test cases 2.4.*.1 check that after it has been created, the data in the
000738 # table is the same as the data returned by the SELECT statement executed as
000739 # a standalone command, verifying the first testable statement above.
000740 #
000741 # Test cases 2.4.*.2 check that the rowids were allocated contiguously
000742 # as required by the second testable statement above. That the rowids
000743 # from the contiguous block were allocated to rows in the order rows are
000744 # returned by the SELECT statement is verified by 2.4.*.1.
000745 #
000746 # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
000747 # creates and populates a database table based on the results of a
000748 # SELECT statement.
000749 #
000750 # The above is also considered to be tested by the following. It is
000751 # clear that tables are being created and populated by the command in
000752 # question.
000753 #
000754 foreach {tn tbl select} {
000755 1 x1 "SELECT * FROM t1"
000756 2 x2 "SELECT * FROM t1 ORDER BY x DESC"
000757 3 x3 "SELECT * FROM t1 ORDER BY x ASC"
000758 } {
000759 # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
000760 execsql [subst {CREATE TABLE $tbl AS $select}]
000761
000762 # Check that the rows inserted into the table, sorted in ascending rowid
000763 # order, match those returned by executing the SELECT statement as a
000764 # standalone command.
000765 do_execsql_test e_createtable-2.4.$tn.1 [subst {
000766 SELECT * FROM $tbl ORDER BY rowid;
000767 }] [execsql $select]
000768
000769 # Check that the rowids in the new table are a contiguous block starting
000770 # with rowid 1. Note that this will fail if SELECT statement $select
000771 # returns 0 rows (as max(rowid) will be NULL).
000772 do_execsql_test e_createtable-2.4.$tn.2 [subst {
000773 SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
000774 }] {1 1}
000775 }
000776
000777 #--------------------------------------------------------------------------
000778 # Test cases for column defintions in CREATE TABLE statements that do not
000779 # use a SELECT statement. Not including data constraints. In other words,
000780 # tests for the specification of:
000781 #
000782 # * declared types,
000783 # * default values, and
000784 # * default collation sequences.
000785 #
000786
000787 # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not
000788 # restrict the type of data that may be inserted into a column based on
000789 # the columns declared type.
000790 #
000791 # Test this by creating a few tables with varied declared types, then
000792 # inserting various different types of values into them.
000793 #
000794 drop_all_tables
000795 do_execsql_test e_createtable-3.1.0 {
000796 CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
000797 CREATE TABLE t2(a DATETIME, b STRING, c REAL);
000798 CREATE TABLE t3(o, t);
000799 } {}
000800
000801 # value type -> declared column type
000802 # ----------------------------------
000803 # integer -> VARCHAR(10)
000804 # string -> INTEGER
000805 # blob -> DOUBLE
000806 #
000807 do_execsql_test e_createtable-3.1.1 {
000808 INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
000809 SELECT * FROM t1;
000810 } {14 {quite a lengthy string} UVU}
000811
000812 # string -> DATETIME
000813 # integer -> STRING
000814 # time -> REAL
000815 #
000816 do_execsql_test e_createtable-3.1.2 {
000817 INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
000818 SELECT * FROM t2;
000819 } {{not a datetime} 13 12:41:59}
000820
000821 # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to
000822 # determine the affinity of the column only.
000823 #
000824 # Affinities are tested in more detail elsewhere (see document
000825 # datatype3.html). Here, just test that affinity transformations
000826 # consistent with the expected affinity of each column (based on
000827 # the declared type) appear to take place.
000828 #
000829 # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL
000830 # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL
000831 # Affinities of t3 (test cases 3.2.3.*): NONE, NONE
000832 #
000833 do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}
000834
000835 do_createtable_tests 3.2.1 -query {
000836 SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;
000837 } {
000838 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0}
000839 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0}
000840 }
000841 do_createtable_tests 3.2.2 -query {
000842 SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;
000843 } {
000844 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0}
000845 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0}
000846 }
000847 do_createtable_tests 3.2.3 -query {
000848 SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;
000849 } {
000850 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'}
000851 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0}
000852 }
000853
000854 # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause
000855 # attached to a column definition, then the default value of the column
000856 # is NULL.
000857 #
000858 # None of the columns in table t1 have an explicit DEFAULT clause.
000859 # So testing that the default value of all columns in table t1 is
000860 # NULL serves to verify the above.
000861 #
000862 do_createtable_tests 3.2.3 -query {
000863 SELECT quote(x), quote(y), quote(z) FROM t1
000864 } -repair {
000865 execsql { DELETE FROM t1 }
000866 } {
000867 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL}
000868 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'}
000869 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL}
000870 }
000871
000872 # EVIDENCE-OF: R-07343-35026 An explicit DEFAULT clause may specify that
000873 # the default value is NULL, a string constant, a blob constant, a
000874 # signed-number, or any constant expression enclosed in parentheses. A
000875 # default value may also be one of the special case-independent keywords
000876 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
000877 #
000878 do_execsql_test e_createtable-3.3.1 {
000879 CREATE TABLE t4(
000880 a DEFAULT NULL,
000881 b DEFAULT 'string constant',
000882 c DEFAULT X'424C4F42',
000883 d DEFAULT 1,
000884 e DEFAULT -1,
000885 f DEFAULT 3.14,
000886 g DEFAULT -3.14,
000887 h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
000888 i DEFAULT CURRENT_TIME,
000889 j DEFAULT CURRENT_DATE,
000890 k DEFAULT CURRENT_TIMESTAMP
000891 );
000892 } {}
000893
000894 # EVIDENCE-OF: R-33440-07331 For the purposes of the DEFAULT clause, an
000895 # expression is considered constant if it contains no sub-queries,
000896 # column or table references, bound parameters, or string literals
000897 # enclosed in double-quotes instead of single-quotes.
000898 #
000899 do_createtable_tests 3.4.1 -error {
000900 default value of column [x] is not constant
000901 } {
000902 1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {}
000903 2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {}
000904 3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {}
000905 4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {}
000906 5 {CREATE TABLE t5(x DEFAULT ( x!=?1 ))} {}
000907 }
000908 do_createtable_tests 3.4.2 -repair {
000909 catchsql { DROP TABLE t5 }
000910 } {
000911 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {}
000912 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {}
000913 }
000914
000915 # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table
000916 # by an INSERT statement that does not provide explicit values for all
000917 # table columns the values stored in the new row are determined by their
000918 # default values
000919 #
000920 # Verify this with some assert statements for which all, some and no
000921 # columns lack explicit values.
000922 #
000923 set sqlite_current_time 1000000000
000924 do_createtable_tests 3.5 -query {
000925 SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f),
000926 quote(g), quote(h), quote(i), quote(j), quote(k)
000927 FROM t4 ORDER BY rowid DESC LIMIT 1;
000928 } {
000929 1 "INSERT INTO t4 DEFAULT VALUES" {
000930 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14
000931 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
000932 }
000933
000934 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
000935 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
000936 }
000937
000938 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
000939 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
000940 }
000941
000942 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)" {
000943 1 2 3 4 5 6 7 8 9 10 11
000944 }
000945 }
000946
000947 # EVIDENCE-OF: R-12572-62501 If the default value of the column is a
000948 # constant NULL, text, blob or signed-number value, then that value is
000949 # used directly in the new row.
000950 #
000951 do_execsql_test e_createtable-3.6.1 {
000952 CREATE TABLE t5(
000953 a DEFAULT NULL,
000954 b DEFAULT 'text value',
000955 c DEFAULT X'424C4F42',
000956 d DEFAULT -45678.6,
000957 e DEFAULT 394507
000958 );
000959 } {}
000960 do_execsql_test e_createtable-3.6.2 {
000961 INSERT INTO t5 DEFAULT VALUES;
000962 SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
000963 } {NULL {'text value'} X'424C4F42' -45678.6 394507}
000964
000965 # EVIDENCE-OF: R-60616-50251 If the default value of a column is an
000966 # expression in parentheses, then the expression is evaluated once for
000967 # each row inserted and the results used in the new row.
000968 #
000969 # Test case 3.6.4 demonstrates that the expression is evaluated
000970 # separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
000971 # command.
000972 #
000973 set ::nextint 0
000974 proc nextint {} { incr ::nextint }
000975 db func nextint nextint
000976
000977 do_execsql_test e_createtable-3.7.1 {
000978 CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
000979 } {}
000980 do_execsql_test e_createtable-3.7.2 {
000981 INSERT INTO t6 DEFAULT VALUES;
000982 SELECT quote(a), quote(b) FROM t6;
000983 } {1 2}
000984 do_execsql_test e_createtable-3.7.3 {
000985 INSERT INTO t6(a) VALUES('X');
000986 SELECT quote(a), quote(b) FROM t6;
000987 } {1 2 'X' 3}
000988 do_execsql_test e_createtable-3.7.4 {
000989 INSERT INTO t6(a) SELECT a FROM t6;
000990 SELECT quote(a), quote(b) FROM t6;
000991 } {1 2 'X' 3 1 4 'X' 5}
000992
000993 # EVIDENCE-OF: R-15363-55230 If the default value of a column is
000994 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used
000995 # in the new row is a text representation of the current UTC date and/or
000996 # time.
000997 #
000998 # This is difficult to test literally without knowing what time the
000999 # user will run the tests. Instead, we test that the three cases
001000 # above set the value to the current date and/or time according to
001001 # the xCurrentTime() method of the VFS. Which is usually the same
001002 # as UTC. In this case, however, we instrument it to always return
001003 # a time equivalent to "2001-09-09 01:46:40 UTC".
001004 #
001005 set sqlite_current_time 1000000000
001006 do_execsql_test e_createtable-3.8.1 {
001007 CREATE TABLE t7(
001008 a DEFAULT CURRENT_TIME,
001009 b DEFAULT CURRENT_DATE,
001010 c DEFAULT CURRENT_TIMESTAMP
001011 );
001012 } {}
001013 do_execsql_test e_createtable-3.8.2 {
001014 INSERT INTO t7 DEFAULT VALUES;
001015 SELECT quote(a), quote(b), quote(c) FROM t7;
001016 } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}
001017
001018
001019 # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value
001020 # is "HH:MM:SS".
001021 #
001022 # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
001023 #
001024 # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is
001025 # "YYYY-MM-DD HH:MM:SS".
001026 #
001027 # The three above are demonstrated by tests 1, 2 and 3 below.
001028 # Respectively.
001029 #
001030 do_createtable_tests 3.8.3 -query {
001031 SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;
001032 } {
001033 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}
001034 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}
001035 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}
001036 }
001037
001038 # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a
001039 # collating sequence to use as the default collation sequence for the
001040 # column.
001041 #
001042 # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the
001043 # default collation sequence is BINARY.
001044 #
001045 do_execsql_test e_createtable-3-9.1 {
001046 CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
001047 INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc');
001048 INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc ');
001049 INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC ');
001050 INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC');
001051 } {}
001052 do_createtable_tests 3.9 {
001053 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }}
001054 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }}
001055 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }}
001056 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }}
001057 }
001058
001059 # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
001060 # by the SQLITE_MAX_COLUMN compile-time parameter.
001061 #
001062 proc columns {n} {
001063 set res [list]
001064 for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
001065 join $res ", "
001066 }
001067 do_execsql_test e_createtable-3.10.1 [subst {
001068 CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
001069 }] {}
001070 do_catchsql_test e_createtable-3.10.2 [subst {
001071 CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
001072 }] {1 {too many columns on t10}}
001073
001074 # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
001075 # runtime using the sqlite3_limit() C/C++ interface.
001076 #
001077 # A 30,000 byte blob consumes 30,003 bytes of record space. A record
001078 # that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
001079 # 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
001080 # at runtime, are based on this calculation.
001081 #
001082 sqlite3_limit db SQLITE_LIMIT_COLUMN 500
001083 do_execsql_test e_createtable-3.11.1 [subst {
001084 CREATE TABLE t10([columns 500]);
001085 }] {}
001086 do_catchsql_test e_createtable-3.11.2 [subst {
001087 CREATE TABLE t11([columns 501]);
001088 }] {1 {too many columns on t11}}
001089
001090 # Check that it is not possible to raise the column limit above its
001091 # default compile time value.
001092 #
001093 sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
001094 do_catchsql_test e_createtable-3.11.3 [subst {
001095 CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
001096 }] {1 {too many columns on t11}}
001097
001098 sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
001099 do_execsql_test e_createtable-3.11.4 {
001100 CREATE TABLE t12(a, b, c);
001101 INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
001102 } {}
001103 do_catchsql_test e_createtable-3.11.5 {
001104 INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
001105 } {1 {string or blob too big}}
001106
001107 #-------------------------------------------------------------------------
001108 # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT
001109 # NULL and CHECK constraints).
001110 #
001111
001112 # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
001113 # PRIMARY KEY.
001114 #
001115 # EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY
001116 # KEY clause appears in a CREATE TABLE statement.
001117 #
001118 # To test the two above, show that zero primary keys is Ok, one primary
001119 # key is Ok, and two or more primary keys is an error.
001120 #
001121 drop_all_tables
001122 do_createtable_tests 4.1.1 {
001123 1 "CREATE TABLE t1(a, b, c)" {}
001124 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {}
001125 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {}
001126 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {}
001127 }
001128 do_createtable_tests 4.1.2 -error {
001129 table "t5" has more than one primary key
001130 } {
001131 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {}
001132 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {}
001133 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {}
001134 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
001135 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {}
001136 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {}
001137 }
001138
001139 # EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary
001140 # tables but is required for WITHOUT ROWID tables.
001141 #
001142 do_catchsql_test 4.1.3 {
001143 CREATE TABLE t6(a, b); --ok
001144 } {0 {}}
001145 do_catchsql_test 4.1.4 {
001146 CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY
001147 } {1 {PRIMARY KEY missing on table t7}}
001148
001149
001150 proc table_pk {tbl} {
001151 set pk [list]
001152 db eval "pragma table_info($tbl)" a {
001153 if {$a(pk)} { lappend pk $a(name) }
001154 }
001155 set pk
001156 }
001157
001158 # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
001159 # column definition, then the primary key for the table consists of that
001160 # single column.
001161 #
001162 # The above is tested by 4.2.1.*
001163 #
001164 # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
001165 # a table-constraint, then the primary key of the table consists of the
001166 # list of columns specified as part of the PRIMARY KEY clause.
001167 #
001168 # The above is tested by 4.2.2.*
001169 #
001170 do_createtable_tests 4.2 -repair {
001171 catchsql { DROP TABLE t5 }
001172 } -tclquery {
001173 table_pk t5
001174 } {
001175 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}
001176 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a}
001177
001178 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a}
001179 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c}
001180 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}
001181 }
001182
001183 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
001184 # have a unique combination of values in its primary key columns.
001185 #
001186 # EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts
001187 # to modify the table content so that two or more rows have identical
001188 # primary key values, that is a constraint violation.
001189 #
001190 drop_all_tables
001191 do_execsql_test 4.3.0 {
001192 CREATE TABLE t1(x PRIMARY KEY, y);
001193 INSERT INTO t1 VALUES(0, 'zero');
001194 INSERT INTO t1 VALUES(45.5, 'one');
001195 INSERT INTO t1 VALUES('brambles', 'two');
001196 INSERT INTO t1 VALUES(X'ABCDEF', 'three');
001197
001198 CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
001199 INSERT INTO t2 VALUES(0, 'zero');
001200 INSERT INTO t2 VALUES(45.5, 'one');
001201 INSERT INTO t2 VALUES('brambles', 'two');
001202 INSERT INTO t2 VALUES(X'ABCDEF', 'three');
001203 } {}
001204
001205 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} {
001206 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"}
001207 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"}
001208 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"}
001209 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"}
001210 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"}
001211 }
001212 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} {
001213 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"}
001214 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"}
001215 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"}
001216 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"}
001217 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"}
001218 }
001219 do_createtable_tests 4.3.2 {
001220 1 "INSERT INTO t1 VALUES(-1, 0)" {}
001221 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {}
001222 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {}
001223 4 "INSERT INTO t1 VALUES('bramble', 'abc')" {}
001224 5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {}
001225
001226 6 "INSERT INTO t2 VALUES(0, 0)" {}
001227 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {}
001228 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {}
001229 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {}
001230 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {}
001231 }
001232 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} {
001233 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"}
001234 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"}
001235 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"}
001236 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"}
001237 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"}
001238 }
001239 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} {
001240 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"}
001241 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"
001242 {"columns x, y are"}
001243 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
001244 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'"
001245 {"columns x, y are"}
001246 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'"
001247 {"columns x, y are"}
001248 }
001249
001250
001251 # EVIDENCE-OF: R-52572-02078 For the purposes of determining the
001252 # uniqueness of primary key values, NULL values are considered distinct
001253 # from all other values, including other NULLs.
001254 #
001255 do_createtable_tests 4.4 {
001256 1 "INSERT INTO t1 VALUES(NULL, 0)" {}
001257 2 "INSERT INTO t1 VALUES(NULL, 0)" {}
001258 3 "INSERT INTO t1 VALUES(NULL, 0)" {}
001259
001260 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {}
001261 5 "INSERT INTO t2 VALUES(NULL, 'one')" {}
001262 6 "INSERT INTO t2 VALUES(NULL, 'two')" {}
001263 7 "INSERT INTO t2 VALUES(NULL, 'three')" {}
001264
001265 8 "INSERT INTO t2 VALUES(0, NULL)" {}
001266 9 "INSERT INTO t2 VALUES(45.5, NULL)" {}
001267 10 "INSERT INTO t2 VALUES(0.0, NULL)" {}
001268 11 "INSERT INTO t2 VALUES('brambles', NULL)" {}
001269 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {}
001270
001271 13 "INSERT INTO t2 VALUES(NULL, NULL)" {}
001272 14 "INSERT INTO t2 VALUES(NULL, NULL)" {}
001273 }
001274
001275 # EVIDENCE-OF: R-40010-16873 Unless the column is an INTEGER PRIMARY KEY
001276 # or the table is a WITHOUT ROWID table or a STRICT table or the column
001277 # is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY
001278 # column.
001279 #
001280 # If the column is an integer primary key, attempting to insert a NULL
001281 # into the column triggers the auto-increment behavior. Attempting
001282 # to use UPDATE to set an ipk column to a NULL value is an error.
001283 #
001284 do_createtable_tests 4.5.1 {
001285 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3
001286 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6
001287 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7
001288 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2
001289 }
001290 do_execsql_test 4.5.2 {
001291 CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
001292 INSERT INTO t3 VALUES(1, NULL, 2);
001293 INSERT INTO t3 VALUES('x', NULL, 'y');
001294 SELECT u FROM t3;
001295 } {1 2}
001296 do_catchsql_test 4.5.3 {
001297 INSERT INTO t3 VALUES(2, 5, 3);
001298 UPDATE t3 SET u = NULL WHERE s = 2;
001299 } {1 {datatype mismatch}}
001300 do_catchsql_test 4.5.4 {
001301 CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID;
001302 INSERT INTO t4 VALUES(1, NULL, 2);
001303 } {1 {NOT NULL constraint failed: t4.u}}
001304 do_catchsql_test 4.5.5 {
001305 CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v);
001306 INSERT INTO t5 VALUES(1, NULL, 2);
001307 } {1 {NOT NULL constraint failed: t5.u}}
001308 do_catchsql_test 4.5.6 {
001309 CREATE TABLE t6(s INT, u INT PRIMARY KEY, v INT) STRICT;
001310 INSERT INTO t6 VALUES(1, NULL, 2);
001311 } {1 {NOT NULL constraint failed: t6.u}}
001312 do_catchsql_test 4.5.7 {
001313 CREATE TABLE t7(s INT, u INT PRIMARY KEY NOT NULL, v INT) STRICT;
001314 INSERT INTO t7 VALUES(1, NULL, 2);
001315 } {1 {NOT NULL constraint failed: t7.u}}
001316
001317 # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
001318 # KEY constraint, except that a single table may have any number of
001319 # UNIQUE constraints.
001320 #
001321 drop_all_tables
001322 do_createtable_tests 4.6 {
001323 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {}
001324 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {}
001325 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
001326 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {}
001327 }
001328
001329 # EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table,
001330 # each row must contain a unique combination of values in the columns
001331 # identified by the UNIQUE constraint.
001332 #
001333 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
001334 # have a unique combination of values in its primary key columns.
001335 #
001336 do_execsql_test 4.7.0 {
001337 INSERT INTO t1 VALUES(1, 2);
001338 INSERT INTO t1 VALUES(4.3, 5.5);
001339 INSERT INTO t1 VALUES('reveal', 'variableness');
001340 INSERT INTO t1 VALUES(X'123456', X'654321');
001341
001342 INSERT INTO t4 VALUES('xyx', 1, 1);
001343 INSERT INTO t4 VALUES('xyx', 2, 1);
001344 INSERT INTO t4 VALUES('uvw', 1, 1);
001345 }
001346 do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} {
001347 1 "INSERT INTO t1 VALUES(1, 'one')" {{t1.a}}
001348 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{t1.a}}
001349 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{t1.a}}
001350 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{t1.a}}
001351
001352 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{t1.a}}
001353 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{t1.a}}
001354 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{t1.a}}
001355 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{t1.a}}
001356
001357 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{t4.a, t4.b, t4.c}}
001358 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{t4.a, t4.b, t4.c}}
001359 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{t4.a, t4.b, t4.c}}
001360
001361 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{t4.a, t4.b, t4.c}}
001362 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{t4.a, t4.b, t4.c}}
001363 14 "UPDATE t4 SET a=0, b=0, c=0" {{t4.a, t4.b, t4.c}}
001364 }
001365
001366 # EVIDENCE-OF: R-00404-17670 For the purposes of UNIQUE constraints,
001367 # NULL values are considered distinct from all other values, including
001368 # other NULLs.
001369 #
001370 do_createtable_tests 4.8 {
001371 1 "INSERT INTO t1 VALUES(NULL, NULL)" {}
001372 2 "INSERT INTO t1 VALUES(NULL, NULL)" {}
001373 3 "UPDATE t1 SET a = NULL" {}
001374 4 "UPDATE t1 SET b = NULL" {}
001375
001376 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}
001377 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}
001378 7 "UPDATE t4 SET a = NULL" {}
001379 8 "UPDATE t4 SET b = NULL" {}
001380 9 "UPDATE t4 SET c = NULL" {}
001381 }
001382
001383 # EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY
001384 # constraints are implemented by creating a unique index in the
001385 # database.
001386 do_createtable_tests 4.9 -repair drop_all_tables -query {
001387 SELECT count(*) FROM sqlite_master WHERE type='index'
001388 } {
001389 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1
001390 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0
001391 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1
001392 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2
001393 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2
001394 }
001395
001396 # Obsolete: R-02252-33116 Such an index is used like any other index
001397 # in the database to optimize queries.
001398 #
001399 do_execsql_test 4.10.0 {
001400 CREATE TABLE t1(a, b PRIMARY KEY);
001401 CREATE TABLE t2(a, b, c, UNIQUE(b, c));
001402 }
001403 do_createtable_tests 4.10 {
001404 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"
001405 {/*SEARCH t1 USING INDEX sqlite_autoindex_t1_1 (b=?)*/}
001406
001407 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
001408 {/*SCAN t2 USING INDEX sqlite_autoindex_t2_1*/}
001409
001410 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
001411 {/*SEARCH t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)*/}
001412 }
001413
001414 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
001415 # column definition or specified as a table constraint. In practice it
001416 # makes no difference.
001417 #
001418 # All the tests that deal with CHECK constraints below (4.11.* and
001419 # 4.12.*) are run once for a table with the check constraint attached
001420 # to a column definition, and once with a table where the check
001421 # condition is specified as a table constraint.
001422 #
001423 # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
001424 # table or an existing row is updated, the expression associated with
001425 # each CHECK constraint is evaluated and cast to a NUMERIC value in the
001426 # same way as a CAST expression. If the result is zero (integer value 0
001427 # or real value 0.0), then a constraint violation has occurred.
001428 #
001429 drop_all_tables
001430 do_execsql_test 4.11 {
001431 CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
001432 CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
001433 INSERT INTO x1 VALUES('x', 'xx');
001434 INSERT INTO x1 VALUES('y', 'yy');
001435 INSERT INTO t1 SELECT * FROM x1;
001436
001437 CREATE TABLE x2(a CHECK( a||b ), b);
001438 CREATE TABLE t2(a, b, CHECK( a||b ));
001439 INSERT INTO x2 VALUES(1, 'xx');
001440 INSERT INTO x2 VALUES(1, 'yy');
001441 INSERT INTO t2 SELECT * FROM x2;
001442 }
001443
001444 do_createtable_tests 4.11 -error {CHECK constraint failed: %s} {
001445 1a "INSERT INTO x1 VALUES('one', 0)" {b>0}
001446 1b "INSERT INTO t1 VALUES('one', -4.0)" {b>0}
001447
001448 2a "INSERT INTO x2 VALUES('abc', 1)" {a||b}
001449 2b "INSERT INTO t2 VALUES('abc', 1)" {a||b}
001450
001451 3a "INSERT INTO x2 VALUES(0, 'abc')" {a||b}
001452 3b "INSERT INTO t2 VALUES(0, 'abc')" {a||b}
001453
001454 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {b>0}
001455 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {b>0}
001456
001457 4a "UPDATE x2 SET a='' WHERE rowid=1" {a||b}
001458 4b "UPDATE t2 SET a='' WHERE rowid=1" {a||b}
001459 }
001460
001461 # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
001462 # or any other non-zero value, it is not a constraint violation.
001463 #
001464 do_createtable_tests 4.12 {
001465 1a "INSERT INTO x1 VALUES('one', NULL)" {}
001466 1b "INSERT INTO t1 VALUES('one', NULL)" {}
001467
001468 2a "INSERT INTO x1 VALUES('one', 2)" {}
001469 2b "INSERT INTO t1 VALUES('one', 2)" {}
001470
001471 3a "INSERT INTO x2 VALUES(1, 'abc')" {}
001472 3b "INSERT INTO t2 VALUES(1, 'abc')" {}
001473 }
001474
001475 # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached
001476 # to a column definition, not specified as a table constraint.
001477 #
001478 drop_all_tables
001479 do_createtable_tests 4.13.1 {
001480 1 "CREATE TABLE t1(a NOT NULL, b)" {}
001481 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {}
001482 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {}
001483 }
001484 do_createtable_tests 4.13.2 -error {
001485 near "NOT": syntax error
001486 } {
001487 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {}
001488 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {}
001489 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {}
001490 }
001491
001492 # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the
001493 # associated column may not contain a NULL value. Attempting to set the
001494 # column value to NULL when inserting a new row or updating an existing
001495 # one causes a constraint violation.
001496 #
001497 # These tests use the tables created by 4.13.
001498 #
001499 do_execsql_test 4.14.0 {
001500 INSERT INTO t1 VALUES('x', 'y');
001501 INSERT INTO t1 VALUES('z', NULL);
001502
001503 INSERT INTO t2 VALUES('x', 'y');
001504 INSERT INTO t2 VALUES('z', NULL);
001505
001506 INSERT INTO t3 VALUES('x', 'y', 'z');
001507 INSERT INTO t3 VALUES(1, 2, 3);
001508 }
001509 do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} {
001510 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a}
001511 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a}
001512 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c}
001513 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b}
001514 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a}
001515 }
001516
001517 # EVIDENCE-OF: R-34093-09213 PRIMARY KEY, UNIQUE and NOT NULL
001518 # constraints may be explicitly assigned another default conflict
001519 # resolution algorithm by including a conflict-clause in their
001520 # definitions.
001521 #
001522 # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE
001523 #
001524 # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL
001525 # and UNIQUE constraints, respectively.
001526 #
001527 drop_all_tables
001528 do_execsql_test 4.15.0 {
001529 CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
001530 CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
001531 CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
001532 CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
001533 CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
001534 CREATE TABLE t1_xx(a PRIMARY KEY, b);
001535
001536 INSERT INTO t1_ab VALUES(1, 'one');
001537 INSERT INTO t1_ab VALUES(2, 'two');
001538 INSERT INTO t1_ro SELECT * FROM t1_ab;
001539 INSERT INTO t1_ig SELECT * FROM t1_ab;
001540 INSERT INTO t1_fa SELECT * FROM t1_ab;
001541 INSERT INTO t1_re SELECT * FROM t1_ab;
001542 INSERT INTO t1_xx SELECT * FROM t1_ab;
001543
001544 CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
001545 CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
001546 CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
001547 CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
001548 CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
001549 CREATE TABLE t2_xx(a, b NOT NULL);
001550
001551 INSERT INTO t2_ab VALUES(1, 'one');
001552 INSERT INTO t2_ab VALUES(2, 'two');
001553 INSERT INTO t2_ro SELECT * FROM t2_ab;
001554 INSERT INTO t2_ig SELECT * FROM t2_ab;
001555 INSERT INTO t2_fa SELECT * FROM t2_ab;
001556 INSERT INTO t2_re SELECT * FROM t2_ab;
001557 INSERT INTO t2_xx SELECT * FROM t2_ab;
001558
001559 CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
001560 CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
001561 CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
001562 CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
001563 CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
001564 CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
001565
001566 INSERT INTO t3_ab VALUES(1, 'one');
001567 INSERT INTO t3_ab VALUES(2, 'two');
001568 INSERT INTO t3_ro SELECT * FROM t3_ab;
001569 INSERT INTO t3_ig SELECT * FROM t3_ab;
001570 INSERT INTO t3_fa SELECT * FROM t3_ab;
001571 INSERT INTO t3_re SELECT * FROM t3_ab;
001572 INSERT INTO t3_xx SELECT * FROM t3_ab;
001573 }
001574
001575 foreach {tn tbl res ac data} {
001576 1 t1_ab {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three}
001577 2 t1_ro {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two}
001578 3 t1_fa {1 {UNIQUE constraint failed: t1_fa.a}} 0 {1 one 2 two 3 three 4 string}
001579 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
001580 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
001581 6 t1_xx {1 {UNIQUE constraint failed: t1_xx.a}} 0 {1 one 2 two 3 three}
001582 } {
001583 catchsql COMMIT
001584 do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
001585
001586 do_catchsql_test 4.15.$tn.2 "
001587 INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
001588 " $res
001589
001590 do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
001591 do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
001592 }
001593 foreach {tn tbl res ac data} {
001594 1 t2_ab {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three}
001595 2 t2_ro {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two}
001596 3 t2_fa {1 {NOT NULL constraint failed: t2_fa.b}} 0 {1 one 2 two 3 three 4 xx}
001597 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
001598 5 t2_re {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three}
001599 6 t2_xx {1 {NOT NULL constraint failed: t2_xx.b}} 0 {1 one 2 two 3 three}
001600 } {
001601 catchsql COMMIT
001602 do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
001603
001604 do_catchsql_test 4.16.$tn.2 "
001605 INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
001606 " $res
001607
001608 do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
001609 do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
001610 }
001611 foreach {tn tbl res ac data} {
001612 1 t3_ab {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}}
001613 0 {1 one 2 two 3 three}
001614 2 t3_ro {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}}
001615 1 {1 one 2 two}
001616 3 t3_fa {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}}
001617 0 {1 one 2 two 3 three 4 three}
001618 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
001619 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
001620 6 t3_xx {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}}
001621 0 {1 one 2 two 3 three}
001622 } {
001623 catchsql COMMIT
001624 do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
001625
001626 do_catchsql_test 4.17.$tn.2 "
001627 INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
001628 " $res
001629
001630 do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
001631 do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data
001632 }
001633 catchsql COMMIT
001634
001635 # EVIDENCE-OF: R-17539-59899 Or, if a constraint definition does not
001636 # include a conflict-clause, the default conflict resolution algorithm
001637 # is ABORT.
001638 #
001639 # The first half of the above is tested along with explicit ON
001640 # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx
001641 # and t3_xx). The following just tests that the default conflict
001642 # handling for CHECK constraints is ABORT.
001643 #
001644 do_execsql_test 4.18.1 {
001645 CREATE TABLE t4(a, b CHECK (b!=10));
001646 INSERT INTO t4 VALUES(1, 2);
001647 INSERT INTO t4 VALUES(3, 4);
001648 }
001649 do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
001650 do_catchsql_test 4.18.3 {
001651 INSERT INTO t4 SELECT a+4, b+4 FROM t4
001652 } {1 {CHECK constraint failed: b!=10}}
001653 do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
001654 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
001655
001656 # EVIDENCE-OF: R-19114-56113 Different constraints within the same table
001657 # may have different default conflict resolution algorithms.
001658 #
001659 do_execsql_test 4.19.0 {
001660 CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
001661 }
001662 do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
001663 do_execsql_test 4.19.2 { SELECT * FROM t5 } {}
001664 do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
001665 {1 {NOT NULL constraint failed: t5.b}}
001666 do_execsql_test 4.19.4 { SELECT * FROM t5 } {}
001667
001668 #------------------------------------------------------------------------
001669 # Tests for INTEGER PRIMARY KEY and rowid related statements.
001670 #
001671
001672 # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
001673 # of the special case-independent names "rowid", "oid", or "_rowid_" in
001674 # place of a column name.
001675 #
001676 # EVIDENCE-OF: R-06726-07466 A column name can be any of the names
001677 # defined in the CREATE TABLE statement or one of the following special
001678 # identifiers: "ROWID", "OID", or "_ROWID_".
001679 #
001680 drop_all_tables
001681 do_execsql_test 5.1.0 {
001682 CREATE TABLE t1(x, y);
001683 INSERT INTO t1 VALUES('one', 'first');
001684 INSERT INTO t1 VALUES('two', 'second');
001685 INSERT INTO t1 VALUES('three', 'third');
001686 }
001687 do_createtable_tests 5.1 {
001688 1 "SELECT rowid FROM t1" {1 2 3}
001689 2 "SELECT oid FROM t1" {1 2 3}
001690 3 "SELECT _rowid_ FROM t1" {1 2 3}
001691 4 "SELECT ROWID FROM t1" {1 2 3}
001692 5 "SELECT OID FROM t1" {1 2 3}
001693 6 "SELECT _ROWID_ FROM t1" {1 2 3}
001694 7 "SELECT RoWiD FROM t1" {1 2 3}
001695 8 "SELECT OiD FROM t1" {1 2 3}
001696 9 "SELECT _RoWiD_ FROM t1" {1 2 3}
001697 }
001698
001699 # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
001700 # named "rowid", "oid" or "_rowid_", then that name always refers the
001701 # explicitly declared column and cannot be used to retrieve the integer
001702 # rowid value.
001703 #
001704 # EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the
001705 # row key if the CREATE TABLE statement does not define a real column
001706 # with the same name.
001707 #
001708 do_execsql_test 5.2.0 {
001709 CREATE TABLE t2(oid, b);
001710 CREATE TABLE t3(a, _rowid_);
001711 CREATE TABLE t4(a, b, rowid);
001712
001713 INSERT INTO t2 VALUES('one', 'two');
001714 INSERT INTO t2 VALUES('three', 'four');
001715
001716 INSERT INTO t3 VALUES('five', 'six');
001717 INSERT INTO t3 VALUES('seven', 'eight');
001718
001719 INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
001720 INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
001721 }
001722 do_createtable_tests 5.2 {
001723 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2}
001724 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight}
001725 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2}
001726 }
001727
001728
001729 # Argument $tbl is the name of a table in the database. Argument $col is
001730 # the name of one of the tables columns. Return 1 if $col is an alias for
001731 # the rowid, or 0 otherwise.
001732 #
001733 proc is_integer_primary_key {tbl col} {
001734 lindex [db eval [subst {
001735 DELETE FROM $tbl;
001736 INSERT INTO $tbl ($col) VALUES(0);
001737 SELECT (rowid==$col) FROM $tbl;
001738 DELETE FROM $tbl;
001739 }]] 0
001740 }
001741
001742 # EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid
001743 # table has a primary key that consists of a single column and the
001744 # declared type of that column is "INTEGER" in any mixture of upper and
001745 # lower case, then the column becomes an alias for the rowid.
001746 #
001747 # EVIDENCE-OF: R-45951-08347 if the declaration of a column with
001748 # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
001749 # not become an alias for the rowid and is not classified as an integer
001750 # primary key.
001751 #
001752 do_createtable_tests 5.3 -tclquery {
001753 is_integer_primary_key t5 pk
001754 } -repair {
001755 catchsql { DROP TABLE t5 }
001756 } {
001757 1 "CREATE TABLE t5(pk integer primary key)" 1
001758 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1
001759 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1
001760 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0
001761 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0
001762 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0
001763 7 "CREATE TABLE t5(pk int primary key, v integer)" 0
001764 8 "CREATE TABLE t5(pk inTEger primary key)" 1
001765 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1
001766 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1
001767 }
001768
001769 # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
001770 # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
001771 # key column to behave as an ordinary table column with integer affinity
001772 # and a unique index, not as an alias for the rowid.
001773 #
001774 do_execsql_test 5.4.1 {
001775 CREATE TABLE t6(pk INT primary key);
001776 CREATE TABLE t7(pk BIGINT primary key);
001777 CREATE TABLE t8(pk SHORT INTEGER primary key);
001778 CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
001779 }
001780 do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
001781 do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
001782 do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
001783 do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
001784
001785 do_execsql_test 5.4.3 {
001786 INSERT INTO t6 VALUES('2.0');
001787 INSERT INTO t7 VALUES('2.0');
001788 INSERT INTO t8 VALUES('2.0');
001789 INSERT INTO t9 VALUES('2.0');
001790 SELECT typeof(pk), pk FROM t6;
001791 SELECT typeof(pk), pk FROM t7;
001792 SELECT typeof(pk), pk FROM t8;
001793 SELECT typeof(pk), pk FROM t9;
001794 } {integer 2 integer 2 integer 2 integer 2}
001795
001796 do_catchsql_test 5.4.4.1 {
001797 INSERT INTO t6 VALUES(2)
001798 } {1 {UNIQUE constraint failed: t6.pk}}
001799 do_catchsql_test 5.4.4.2 {
001800 INSERT INTO t7 VALUES(2)
001801 } {1 {UNIQUE constraint failed: t7.pk}}
001802 do_catchsql_test 5.4.4.3 {
001803 INSERT INTO t8 VALUES(2)
001804 } {1 {UNIQUE constraint failed: t8.pk}}
001805 do_catchsql_test 5.4.4.4 {
001806 INSERT INTO t9 VALUES(2)
001807 } {1 {UNIQUE constraint failed: t9.pk}}
001808
001809 # EVIDENCE-OF: R-56094-57830 the following three table declarations all
001810 # cause the column "x" to be an alias for the rowid (an integer primary
001811 # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
001812 # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
001813 # z, PRIMARY KEY(x DESC));
001814 #
001815 # EVIDENCE-OF: R-20149-25884 the following declaration does not result
001816 # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
001817 # KEY DESC, y, z);
001818 #
001819 do_createtable_tests 5 -tclquery {
001820 is_integer_primary_key t x
001821 } -repair {
001822 catchsql { DROP TABLE t }
001823 } {
001824 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1
001825 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1
001826 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
001827 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0
001828 }
001829
001830 # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
001831 # UPDATE statement in the same way as any other column value can, either
001832 # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
001833 # using an alias created by an integer primary key.
001834 #
001835 do_execsql_test 5.7.0 {
001836 CREATE TABLE t10(a, b);
001837 INSERT INTO t10 VALUES('ten', 10);
001838
001839 CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
001840 INSERT INTO t11 VALUES('ten', 10);
001841 }
001842 do_createtable_tests 5.7.1 -query {
001843 SELECT rowid, _rowid_, oid FROM t10;
001844 } {
001845 1 "UPDATE t10 SET rowid = 5" {5 5 5}
001846 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6}
001847 3 "UPDATE t10 SET oid = 7" {7 7 7}
001848 }
001849 do_createtable_tests 5.7.2 -query {
001850 SELECT rowid, _rowid_, oid, b FROM t11;
001851 } {
001852 1 "UPDATE t11 SET rowid = 5" {5 5 5 5}
001853 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
001854 3 "UPDATE t11 SET oid = 7" {7 7 7 7}
001855 4 "UPDATE t11 SET b = 8" {8 8 8 8}
001856 }
001857
001858 # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
001859 # a value to use as the rowid for each row inserted.
001860 #
001861 do_createtable_tests 5.8.1 -query {
001862 SELECT rowid, _rowid_, oid FROM t10;
001863 } -repair {
001864 execsql { DELETE FROM t10 }
001865 } {
001866 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15}
001867 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16}
001868 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17}
001869 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3}
001870 }
001871 do_createtable_tests 5.8.2 -query {
001872 SELECT rowid, _rowid_, oid, b FROM t11;
001873 } -repair {
001874 execsql { DELETE FROM t11 }
001875 } {
001876 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15}
001877 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16}
001878 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17}
001879 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2}
001880 }
001881
001882 # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
001883 # primary key or rowid column must contain integer values. Integer
001884 # primary key or rowid columns are not able to hold floating point
001885 # values, strings, BLOBs, or NULLs.
001886 #
001887 # This is considered by the tests for the following 3 statements,
001888 # which show that:
001889 #
001890 # 1. Attempts to UPDATE a rowid column to a non-integer value fail,
001891 # 2. Attempts to INSERT a real, string or blob value into a rowid
001892 # column fail, and
001893 # 3. Attempting to INSERT a NULL value into a rowid column causes the
001894 # system to automatically select an integer value to use.
001895 #
001896
001897
001898 # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
001899 # integer primary key or rowid column to a NULL or blob value, or to a
001900 # string or real value that cannot be losslessly converted to an
001901 # integer, a "datatype mismatch" error occurs and the statement is
001902 # aborted.
001903 #
001904 drop_all_tables
001905 do_execsql_test 5.9.0 {
001906 CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
001907 INSERT INTO t12 VALUES(5, 'five');
001908 }
001909 do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
001910 1 "UPDATE t12 SET x = 4" {integer 4}
001911 2 "UPDATE t12 SET x = 10.0" {integer 10}
001912 3 "UPDATE t12 SET x = '12.0'" {integer 12}
001913 4 "UPDATE t12 SET x = '-15.0'" {integer -15}
001914 }
001915 do_createtable_tests 5.9.2 -error {
001916 datatype mismatch
001917 } {
001918 1 "UPDATE t12 SET x = 4.1" {}
001919 2 "UPDATE t12 SET x = 'hello'" {}
001920 3 "UPDATE t12 SET x = NULL" {}
001921 4 "UPDATE t12 SET x = X'ABCD'" {}
001922 5 "UPDATE t12 SET x = X'3900'" {}
001923 6 "UPDATE t12 SET x = X'39'" {}
001924 }
001925
001926 # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
001927 # blob value, or a string or real value that cannot be losslessly
001928 # converted to an integer into an integer primary key or rowid column, a
001929 # "datatype mismatch" error occurs and the statement is aborted.
001930 #
001931 do_execsql_test 5.10.0 { DELETE FROM t12 }
001932 do_createtable_tests 5.10.1 -error {
001933 datatype mismatch
001934 } {
001935 1 "INSERT INTO t12(x) VALUES(4.1)" {}
001936 2 "INSERT INTO t12(x) VALUES('hello')" {}
001937 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {}
001938 4 "INSERT INTO t12(x) VALUES(X'3900')" {}
001939 5 "INSERT INTO t12(x) VALUES(X'39')" {}
001940 }
001941 do_createtable_tests 5.10.2 -query {
001942 SELECT typeof(x), x FROM t12
001943 } -repair {
001944 execsql { DELETE FROM t12 }
001945 } {
001946 1 "INSERT INTO t12(x) VALUES(4)" {integer 4}
001947 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10}
001948 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12}
001949 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000}
001950 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
001951 }
001952
001953 # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
001954 # NULL value into a rowid or integer primary key column, the system
001955 # chooses an integer value to use as the rowid automatically.
001956 #
001957 do_execsql_test 5.11.0 { DELETE FROM t12 }
001958 do_createtable_tests 5.11 -query {
001959 SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
001960 } {
001961 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1}
001962 2 "INSERT INTO t12(y) VALUES(5)" {integer 2}
001963 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3}
001964 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12"
001965 {integer 4 integer 5 integer 6}
001966 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
001967 {integer 7 integer 8 integer 9}
001968 }
001969
001970 finish_test