gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/e_createtable.test (about) 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 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 set ::testprefix e_createtable 20 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 # 30 31 proc do_createtable_tests {nm args} { 32 uplevel do_select_tests [list e_createtable-$nm] $args 33 } 34 35 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 } 59 60 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 } 78 79 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)" {} 90 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)" {} 95 96 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {} 97 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {} 98 99 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {} 100 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {} 101 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))" {} 108 109 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {} 110 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {} 111 112 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {} 113 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {} 114 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 } 126 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)" {} 136 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)" {} 140 141 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {} 142 143 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {} 144 } 145 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 } 160 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)" {} 173 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)" {} 180 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 } 185 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 } 340 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 348 349 do_execsql_test e_createtable-1.0 { 350 ATTACH 'test.db2' AS auxa; 351 ATTACH 'test.db3' AS auxb; 352 } {} 353 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 } 374 375 376 # EVIDENCE-OF: R-18448-33677 If a schema-name is specified, it must be 377 # 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 if {[permutation]!="maindbname"} { 399 do_createtable_tests 1.3 -tclquery { 400 unset -nocomplain X 401 array set X [table_list] 402 list $X(main) $X(temp) $X(auxa) $X(auxb) 403 } { 404 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}} 405 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}} 406 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}} 407 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl} 408 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}} 409 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}} 410 } 411 } 412 413 # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs 414 # between the "CREATE" and "TABLE" then the new table is created in the 415 # temp database. 416 # 417 drop_all_tables 418 if {[permutation]!="maindbname"} { 419 do_createtable_tests 1.4 -tclquery { 420 unset -nocomplain X 421 array set X [table_list] 422 list $X(main) $X(temp) $X(auxa) $X(auxb) 423 } { 424 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}} 425 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}} 426 } 427 } 428 429 # EVIDENCE-OF: R-23976-43329 It is an error to specify both a 430 # schema-name and the TEMP or TEMPORARY keyword, unless the schema-name 431 # is "temp". 432 # 433 drop_all_tables 434 do_createtable_tests 1.5.1 -error { 435 temporary table name must be unqualified 436 } { 437 1 "CREATE TEMP TABLE main.t1(a, b)" {} 438 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {} 439 3 "CREATE TEMP TABLE auxb.t3(a, b)" {} 440 4 "CREATE TEMPORARY TABLE main.xxx(x)" {} 441 } 442 drop_all_tables 443 if {[permutation]!="maindbname"} { 444 do_createtable_tests 1.5.2 -tclquery { 445 unset -nocomplain X 446 array set X [table_list] 447 list $X(main) $X(temp) $X(auxa) $X(auxb) 448 } { 449 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}} 450 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}} 451 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}} 452 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}} 453 } 454 } 455 456 # EVIDENCE-OF: R-31997-24564 If no schema name is specified and the TEMP 457 # keyword is not present then the table is created in the main database. 458 # 459 drop_all_tables 460 if {[permutation]!="maindbname"} { 461 do_createtable_tests 1.6 -tclquery { 462 unset -nocomplain X 463 array set X [table_list] 464 list $X(main) $X(temp) $X(auxa) $X(auxb) 465 } { 466 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}} 467 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}} 468 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}} 469 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}} 470 } 471 } 472 473 drop_all_tables 474 do_execsql_test e_createtable-1.7.0 { 475 CREATE TABLE t1(x, y); 476 CREATE INDEX i1 ON t1(x); 477 CREATE VIEW v1 AS SELECT * FROM t1; 478 479 CREATE TABLE auxa.tbl1(x, y); 480 CREATE INDEX auxa.idx1 ON tbl1(x); 481 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1; 482 } {} 483 484 # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create 485 # a new table in a database that already contains a table, index or view 486 # of the same name. 487 # 488 # Test cases 1.7.1.* verify that creating a table in a database with a 489 # table/index/view of the same name does fail. 1.7.2.* tests that creating 490 # a table with the same name as a table/index/view in a different database 491 # is Ok. 492 # 493 do_createtable_tests 1.7.1 -error { %s } { 494 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}} 495 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}} 496 3 "CREATE TABLE v1(a, b)" {{view v1 already exists}} 497 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}} 498 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}} 499 6 "CREATE TABLE auxa.view1(a, b)" {{view view1 already exists}} 500 } 501 do_createtable_tests 1.7.2 { 502 1 "CREATE TABLE auxa.t1(a, b)" {} 503 2 "CREATE TABLE auxa.i1(a, b)" {} 504 3 "CREATE TABLE auxa.v1(a, b)" {} 505 4 "CREATE TABLE tbl1(a, b)" {} 506 5 "CREATE TABLE idx1(a, b)" {} 507 6 "CREATE TABLE view1(a, b)" {} 508 } 509 510 # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is 511 # specified as part of the CREATE TABLE statement and a table or view of 512 # the same name already exists, the CREATE TABLE command simply has no 513 # effect (and no error message is returned). 514 # 515 drop_all_tables 516 do_execsql_test e_createtable-1.8.0 { 517 CREATE TABLE t1(x, y); 518 CREATE INDEX i1 ON t1(x); 519 CREATE VIEW v1 AS SELECT * FROM t1; 520 CREATE TABLE auxa.tbl1(x, y); 521 CREATE INDEX auxa.idx1 ON tbl1(x); 522 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1; 523 } {} 524 do_createtable_tests 1.8 { 525 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {} 526 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {} 527 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {} 528 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {} 529 } 530 531 # EVIDENCE-OF: R-16465-40078 An error is still returned if the table 532 # cannot be created because of an existing index, even if the "IF NOT 533 # EXISTS" clause is specified. 534 # 535 do_createtable_tests 1.9 -error { %s } { 536 1 "CREATE TABLE IF NOT EXISTS i1(a, b)" 537 {{there is already an index named i1}} 538 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)" 539 {{there is already an index named idx1}} 540 } 541 542 # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that 543 # has the same name as an existing trigger. 544 # 545 drop_all_tables 546 do_execsql_test e_createtable-1.10.0 { 547 CREATE TABLE t1(x, y); 548 CREATE TABLE auxb.t2(x, y); 549 550 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 551 SELECT 1; 552 END; 553 CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN 554 SELECT 1; 555 END; 556 } {} 557 do_createtable_tests 1.10 { 558 1 "CREATE TABLE tr1(a, b)" {} 559 2 "CREATE TABLE tr2(a, b)" {} 560 3 "CREATE TABLE auxb.tr1(a, b)" {} 561 4 "CREATE TABLE auxb.tr2(a, b)" {} 562 } 563 564 # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE 565 # statement. 566 # 567 drop_all_tables 568 do_execsql_test e_createtable-1.11.0 { 569 CREATE TABLE t1(a, b); 570 CREATE TABLE t2(a, b); 571 CREATE TABLE auxa.t3(a, b); 572 CREATE TABLE auxa.t4(a, b); 573 } {} 574 575 do_execsql_test e_createtable-1.11.1.1 { 576 SELECT * FROM t1; 577 SELECT * FROM t2; 578 SELECT * FROM t3; 579 SELECT * FROM t4; 580 } {} 581 do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {} 582 do_catchsql_test e_createtable-1.11.1.3 { 583 SELECT * FROM t1 584 } {1 {no such table: t1}} 585 do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {} 586 do_catchsql_test e_createtable-1.11.1.5 { 587 SELECT * FROM t3 588 } {1 {no such table: t3}} 589 590 do_execsql_test e_createtable-1.11.2.1 { 591 SELECT name FROM sqlite_master; 592 SELECT name FROM auxa.sqlite_master; 593 } {t2 t4} 594 do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {} 595 do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {} 596 do_execsql_test e_createtable-1.11.2.4 { 597 SELECT name FROM sqlite_master; 598 SELECT name FROM auxa.sqlite_master; 599 } {} 600 601 #------------------------------------------------------------------------- 602 # Test cases e_createtable-2.* - test statements related to the CREATE 603 # TABLE AS ... SELECT statement. 604 # 605 606 # Three Tcl commands: 607 # 608 # select_column_names SQL 609 # The argument must be a SELECT statement. Return a list of the names 610 # of the columns of the result-set that would be returned by executing 611 # the SELECT. 612 # 613 # table_column_names TBL 614 # The argument must be a table name. Return a list of column names, from 615 # left to right, for the table. 616 # 617 # table_column_decltypes TBL 618 # The argument must be a table name. Return a list of column declared 619 # types, from left to right, for the table. 620 # 621 proc sci {select cmd} { 622 set res [list] 623 set STMT [sqlite3_prepare_v2 db $select -1 dummy] 624 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { 625 lappend res [$cmd $STMT $i] 626 } 627 sqlite3_finalize $STMT 628 set res 629 } 630 proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd } 631 proc select_column_names {sql} { sci $sql sqlite3_column_name } 632 proc table_column_names {tbl} { tci $tbl sqlite3_column_name } 633 proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype } 634 635 # Create a database schema. This schema is used by tests 2.1.* through 2.3.*. 636 # 637 drop_all_tables 638 do_execsql_test e_createtable-2.0 { 639 CREATE TABLE t1(a, b, c); 640 CREATE TABLE t2(d, e, f); 641 CREATE TABLE t3(g BIGINT, h VARCHAR(10)); 642 CREATE TABLE t4(i BLOB, j ANYOLDATA); 643 CREATE TABLE t5(k FLOAT, l INTEGER); 644 CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n)); 645 CREATE TABLE t7(x INTEGER PRIMARY KEY); 646 CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc'); 647 CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE); 648 } {} 649 650 # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as 651 # the rows returned by the SELECT statement. The name of each column is 652 # the same as the name of the corresponding column in the result set of 653 # the SELECT statement. 654 # 655 do_createtable_tests 2.1 -tclquery { 656 table_column_names x1 657 } -repair { 658 catchsql { DROP TABLE x1 } 659 } { 660 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c} 661 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a} 662 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f} 663 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)} 664 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)} 665 } 666 667 # EVIDENCE-OF: R-55407-45319 The declared type of each column is 668 # determined by the expression affinity of the corresponding expression 669 # in the result set of the SELECT statement, as follows: Expression 670 # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT" 671 # REAL "REAL" BLOB (a.k.a "NONE") "" (empty string) 672 # 673 do_createtable_tests 2.2 -tclquery { 674 table_column_decltypes x1 675 } -repair { 676 catchsql { DROP TABLE x1 } 677 } { 678 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""} 679 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT} 680 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM} 681 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT} 682 } 683 684 # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has 685 # no PRIMARY KEY and no constraints of any kind. The default value of 686 # each column is NULL. The default collation sequence for each column of 687 # the new table is BINARY. 688 # 689 # The following tests create tables based on SELECT statements that read 690 # from tables that have primary keys, constraints and explicit default 691 # collation sequences. None of this is transfered to the definition of 692 # the new table as stored in the sqlite_master table. 693 # 694 # Tests 2.3.2.* show that the default value of each column is NULL. 695 # 696 do_createtable_tests 2.3.1 -query { 697 SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1 698 } { 699 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}} 700 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}} 701 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}} 702 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}} 703 } 704 do_execsql_test e_createtable-2.3.2.1 { 705 INSERT INTO x1 DEFAULT VALUES; 706 INSERT INTO x2 DEFAULT VALUES; 707 INSERT INTO x3 DEFAULT VALUES; 708 INSERT INTO x4 DEFAULT VALUES; 709 } {} 710 db nullvalue null 711 do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null} 712 do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null} 713 do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null} 714 do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null} 715 db nullvalue {} 716 717 drop_all_tables 718 do_execsql_test e_createtable-2.4.0 { 719 CREATE TABLE t1(x, y); 720 INSERT INTO t1 VALUES('i', 'one'); 721 INSERT INTO t1 VALUES('ii', 'two'); 722 INSERT INTO t1 VALUES('iii', 'three'); 723 } {} 724 725 # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are 726 # initially populated with the rows of data returned by the SELECT 727 # statement. 728 # 729 # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending 730 # rowid values, starting with 1, in the order that they are returned by 731 # the SELECT statement. 732 # 733 # Each test case below is specified as the name of a table to create 734 # using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in 735 # creating it. The table is created. 736 # 737 # Test cases 2.4.*.1 check that after it has been created, the data in the 738 # table is the same as the data returned by the SELECT statement executed as 739 # a standalone command, verifying the first testable statement above. 740 # 741 # Test cases 2.4.*.2 check that the rowids were allocated contiguously 742 # as required by the second testable statement above. That the rowids 743 # from the contiguous block were allocated to rows in the order rows are 744 # returned by the SELECT statement is verified by 2.4.*.1. 745 # 746 # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement 747 # creates and populates a database table based on the results of a 748 # SELECT statement. 749 # 750 # The above is also considered to be tested by the following. It is 751 # clear that tables are being created and populated by the command in 752 # question. 753 # 754 foreach {tn tbl select} { 755 1 x1 "SELECT * FROM t1" 756 2 x2 "SELECT * FROM t1 ORDER BY x DESC" 757 3 x3 "SELECT * FROM t1 ORDER BY x ASC" 758 } { 759 # Create the table using a "CREATE TABLE ... AS SELECT ..." command. 760 execsql [subst {CREATE TABLE $tbl AS $select}] 761 762 # Check that the rows inserted into the table, sorted in ascending rowid 763 # order, match those returned by executing the SELECT statement as a 764 # standalone command. 765 do_execsql_test e_createtable-2.4.$tn.1 [subst { 766 SELECT * FROM $tbl ORDER BY rowid; 767 }] [execsql $select] 768 769 # Check that the rowids in the new table are a contiguous block starting 770 # with rowid 1. Note that this will fail if SELECT statement $select 771 # returns 0 rows (as max(rowid) will be NULL). 772 do_execsql_test e_createtable-2.4.$tn.2 [subst { 773 SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl 774 }] {1 1} 775 } 776 777 #-------------------------------------------------------------------------- 778 # Test cases for column defintions in CREATE TABLE statements that do not 779 # use a SELECT statement. Not including data constraints. In other words, 780 # tests for the specification of: 781 # 782 # * declared types, 783 # * default values, and 784 # * default collation sequences. 785 # 786 787 # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not 788 # restrict the type of data that may be inserted into a column based on 789 # the columns declared type. 790 # 791 # Test this by creating a few tables with varied declared types, then 792 # inserting various different types of values into them. 793 # 794 drop_all_tables 795 do_execsql_test e_createtable-3.1.0 { 796 CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE); 797 CREATE TABLE t2(a DATETIME, b STRING, c REAL); 798 CREATE TABLE t3(o, t); 799 } {} 800 801 # value type -> declared column type 802 # ---------------------------------- 803 # integer -> VARCHAR(10) 804 # string -> INTEGER 805 # blob -> DOUBLE 806 # 807 do_execsql_test e_createtable-3.1.1 { 808 INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655'); 809 SELECT * FROM t1; 810 } {14 {quite a lengthy string} UVU} 811 812 # string -> DATETIME 813 # integer -> STRING 814 # time -> REAL 815 # 816 do_execsql_test e_createtable-3.1.2 { 817 INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59'); 818 SELECT * FROM t2; 819 } {{not a datetime} 13 12:41:59} 820 821 # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to 822 # determine the affinity of the column only. 823 # 824 # Affinities are tested in more detail elsewhere (see document 825 # datatype3.html). Here, just test that affinity transformations 826 # consistent with the expected affinity of each column (based on 827 # the declared type) appear to take place. 828 # 829 # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL 830 # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL 831 # Affinities of t3 (test cases 3.2.3.*): NONE, NONE 832 # 833 do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {} 834 835 do_createtable_tests 3.2.1 -query { 836 SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1; 837 } { 838 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0} 839 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0} 840 } 841 do_createtable_tests 3.2.2 -query { 842 SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1; 843 } { 844 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0} 845 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0} 846 } 847 do_createtable_tests 3.2.3 -query { 848 SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1; 849 } { 850 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'} 851 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0} 852 } 853 854 # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause 855 # attached to a column definition, then the default value of the column 856 # is NULL. 857 # 858 # None of the columns in table t1 have an explicit DEFAULT clause. 859 # So testing that the default value of all columns in table t1 is 860 # NULL serves to verify the above. 861 # 862 do_createtable_tests 3.2.3 -query { 863 SELECT quote(x), quote(y), quote(z) FROM t1 864 } -repair { 865 execsql { DELETE FROM t1 } 866 } { 867 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL} 868 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'} 869 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL} 870 } 871 872 # EVIDENCE-OF: R-07343-35026 An explicit DEFAULT clause may specify that 873 # the default value is NULL, a string constant, a blob constant, a 874 # signed-number, or any constant expression enclosed in parentheses. A 875 # default value may also be one of the special case-independent keywords 876 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. 877 # 878 do_execsql_test e_createtable-3.3.1 { 879 CREATE TABLE t4( 880 a DEFAULT NULL, 881 b DEFAULT 'string constant', 882 c DEFAULT X'424C4F42', 883 d DEFAULT 1, 884 e DEFAULT -1, 885 f DEFAULT 3.14, 886 g DEFAULT -3.14, 887 h DEFAULT ( substr('abcd', 0, 2) || 'cd' ), 888 i DEFAULT CURRENT_TIME, 889 j DEFAULT CURRENT_DATE, 890 k DEFAULT CURRENT_TIMESTAMP 891 ); 892 } {} 893 894 # EVIDENCE-OF: R-33440-07331 For the purposes of the DEFAULT clause, an 895 # expression is considered constant if it contains no sub-queries, 896 # column or table references, bound parameters, or string literals 897 # enclosed in double-quotes instead of single-quotes. 898 # 899 do_createtable_tests 3.4.1 -error { 900 default value of column [x] is not constant 901 } { 902 1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {} 903 2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {} 904 3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {} 905 4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {} 906 5 {CREATE TABLE t5(x DEFAULT ( x!=?1 ))} {} 907 } 908 do_createtable_tests 3.4.2 -repair { 909 catchsql { DROP TABLE t5 } 910 } { 911 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {} 912 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {} 913 } 914 915 # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table 916 # by an INSERT statement that does not provide explicit values for all 917 # table columns the values stored in the new row are determined by their 918 # default values 919 # 920 # Verify this with some assert statements for which all, some and no 921 # columns lack explicit values. 922 # 923 set sqlite_current_time 1000000000 924 do_createtable_tests 3.5 -query { 925 SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), 926 quote(g), quote(h), quote(i), quote(j), quote(k) 927 FROM t4 ORDER BY rowid DESC LIMIT 1; 928 } { 929 1 "INSERT INTO t4 DEFAULT VALUES" { 930 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 931 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} 932 } 933 934 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" { 935 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} 936 } 937 938 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" { 939 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1 940 } 941 942 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)" { 943 1 2 3 4 5 6 7 8 9 10 11 944 } 945 } 946 947 # EVIDENCE-OF: R-12572-62501 If the default value of the column is a 948 # constant NULL, text, blob or signed-number value, then that value is 949 # used directly in the new row. 950 # 951 do_execsql_test e_createtable-3.6.1 { 952 CREATE TABLE t5( 953 a DEFAULT NULL, 954 b DEFAULT 'text value', 955 c DEFAULT X'424C4F42', 956 d DEFAULT -45678.6, 957 e DEFAULT 394507 958 ); 959 } {} 960 do_execsql_test e_createtable-3.6.2 { 961 INSERT INTO t5 DEFAULT VALUES; 962 SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5; 963 } {NULL {'text value'} X'424C4F42' -45678.6 394507} 964 965 # EVIDENCE-OF: R-60616-50251 If the default value of a column is an 966 # expression in parentheses, then the expression is evaluated once for 967 # each row inserted and the results used in the new row. 968 # 969 # Test case 3.6.4 demonstrates that the expression is evaluated 970 # separately for each row if the INSERT is an "INSERT INTO ... SELECT ..." 971 # command. 972 # 973 set ::nextint 0 974 proc nextint {} { incr ::nextint } 975 db func nextint nextint 976 977 do_execsql_test e_createtable-3.7.1 { 978 CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() )); 979 } {} 980 do_execsql_test e_createtable-3.7.2 { 981 INSERT INTO t6 DEFAULT VALUES; 982 SELECT quote(a), quote(b) FROM t6; 983 } {1 2} 984 do_execsql_test e_createtable-3.7.3 { 985 INSERT INTO t6(a) VALUES('X'); 986 SELECT quote(a), quote(b) FROM t6; 987 } {1 2 'X' 3} 988 do_execsql_test e_createtable-3.7.4 { 989 INSERT INTO t6(a) SELECT a FROM t6; 990 SELECT quote(a), quote(b) FROM t6; 991 } {1 2 'X' 3 1 4 'X' 5} 992 993 # EVIDENCE-OF: R-15363-55230 If the default value of a column is 994 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used 995 # in the new row is a text representation of the current UTC date and/or 996 # time. 997 # 998 # This is difficult to test literally without knowing what time the 999 # user will run the tests. Instead, we test that the three cases 1000 # above set the value to the current date and/or time according to 1001 # the xCurrentTime() method of the VFS. Which is usually the same 1002 # as UTC. In this case, however, we instrument it to always return 1003 # a time equivalent to "2001-09-09 01:46:40 UTC". 1004 # 1005 set sqlite_current_time 1000000000 1006 do_execsql_test e_createtable-3.8.1 { 1007 CREATE TABLE t7( 1008 a DEFAULT CURRENT_TIME, 1009 b DEFAULT CURRENT_DATE, 1010 c DEFAULT CURRENT_TIMESTAMP 1011 ); 1012 } {} 1013 do_execsql_test e_createtable-3.8.2 { 1014 INSERT INTO t7 DEFAULT VALUES; 1015 SELECT quote(a), quote(b), quote(c) FROM t7; 1016 } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}} 1017 1018 1019 # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value 1020 # is "HH:MM:SS". 1021 # 1022 # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD". 1023 # 1024 # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is 1025 # "YYYY-MM-DD HH:MM:SS". 1026 # 1027 # The three above are demonstrated by tests 1, 2 and 3 below. 1028 # Respectively. 1029 # 1030 do_createtable_tests 3.8.3 -query { 1031 SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1; 1032 } { 1033 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y} 1034 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x} 1035 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}} 1036 } 1037 1038 # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a 1039 # collating sequence to use as the default collation sequence for the 1040 # column. 1041 # 1042 # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the 1043 # default collation sequence is BINARY. 1044 # 1045 do_execsql_test e_createtable-3-9.1 { 1046 CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d); 1047 INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc'); 1048 INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc '); 1049 INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC '); 1050 INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC'); 1051 } {} 1052 do_createtable_tests 3.9 { 1053 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }} 1054 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }} 1055 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }} 1056 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }} 1057 } 1058 1059 # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited 1060 # by the SQLITE_MAX_COLUMN compile-time parameter. 1061 # 1062 proc columns {n} { 1063 set res [list] 1064 for {set i 0} {$i < $n} {incr i} { lappend res "c$i" } 1065 join $res ", " 1066 } 1067 do_execsql_test e_createtable-3.10.1 [subst { 1068 CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]); 1069 }] {} 1070 do_catchsql_test e_createtable-3.10.2 [subst { 1071 CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]); 1072 }] {1 {too many columns on t10}} 1073 1074 # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at 1075 # runtime using the sqlite3_limit() C/C++ interface. 1076 # 1077 # A 30,000 byte blob consumes 30,003 bytes of record space. A record 1078 # that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests 1079 # 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered 1080 # at runtime, are based on this calculation. 1081 # 1082 sqlite3_limit db SQLITE_LIMIT_COLUMN 500 1083 do_execsql_test e_createtable-3.11.1 [subst { 1084 CREATE TABLE t10([columns 500]); 1085 }] {} 1086 do_catchsql_test e_createtable-3.11.2 [subst { 1087 CREATE TABLE t11([columns 501]); 1088 }] {1 {too many columns on t11}} 1089 1090 # Check that it is not possible to raise the column limit above its 1091 # default compile time value. 1092 # 1093 sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2] 1094 do_catchsql_test e_createtable-3.11.3 [subst { 1095 CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]); 1096 }] {1 {too many columns on t11}} 1097 1098 sqlite3_limit db SQLITE_LIMIT_LENGTH 90010 1099 do_execsql_test e_createtable-3.11.4 { 1100 CREATE TABLE t12(a, b, c); 1101 INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000)); 1102 } {} 1103 do_catchsql_test e_createtable-3.11.5 { 1104 INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000)); 1105 } {1 {string or blob too big}} 1106 1107 #------------------------------------------------------------------------- 1108 # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT 1109 # NULL and CHECK constraints). 1110 # 1111 1112 # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one 1113 # PRIMARY KEY. 1114 # 1115 # EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY 1116 # KEY clause appears in a CREATE TABLE statement. 1117 # 1118 # To test the two above, show that zero primary keys is Ok, one primary 1119 # key is Ok, and two or more primary keys is an error. 1120 # 1121 drop_all_tables 1122 do_createtable_tests 4.1.1 { 1123 1 "CREATE TABLE t1(a, b, c)" {} 1124 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {} 1125 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {} 1126 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {} 1127 } 1128 do_createtable_tests 4.1.2 -error { 1129 table "t5" has more than one primary key 1130 } { 1131 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {} 1132 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {} 1133 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {} 1134 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {} 1135 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {} 1136 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {} 1137 } 1138 1139 # EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary 1140 # tables but is required for WITHOUT ROWID tables. 1141 # 1142 do_catchsql_test 4.1.3 { 1143 CREATE TABLE t6(a, b); --ok 1144 } {0 {}} 1145 do_catchsql_test 4.1.4 { 1146 CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY 1147 } {1 {PRIMARY KEY missing on table t7}} 1148 1149 1150 proc table_pk {tbl} { 1151 set pk [list] 1152 db eval "pragma table_info($tbl)" a { 1153 if {$a(pk)} { lappend pk $a(name) } 1154 } 1155 set pk 1156 } 1157 1158 # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a 1159 # column definition, then the primary key for the table consists of that 1160 # single column. 1161 # 1162 # The above is tested by 4.2.1.* 1163 # 1164 # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as 1165 # a table-constraint, then the primary key of the table consists of the 1166 # list of columns specified as part of the PRIMARY KEY clause. 1167 # 1168 # The above is tested by 4.2.2.* 1169 # 1170 do_createtable_tests 4.2 -repair { 1171 catchsql { DROP TABLE t5 } 1172 } -tclquery { 1173 table_pk t5 1174 } { 1175 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} 1176 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a} 1177 1178 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a} 1179 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c} 1180 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} 1181 } 1182 1183 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must 1184 # have a unique combination of values in its primary key columns. 1185 # 1186 # EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts 1187 # to modify the table content so that two or more rows have identical 1188 # primary key values, that is a constraint violation. 1189 # 1190 drop_all_tables 1191 do_execsql_test 4.3.0 { 1192 CREATE TABLE t1(x PRIMARY KEY, y); 1193 INSERT INTO t1 VALUES(0, 'zero'); 1194 INSERT INTO t1 VALUES(45.5, 'one'); 1195 INSERT INTO t1 VALUES('brambles', 'two'); 1196 INSERT INTO t1 VALUES(X'ABCDEF', 'three'); 1197 1198 CREATE TABLE t2(x, y, PRIMARY KEY(x, y)); 1199 INSERT INTO t2 VALUES(0, 'zero'); 1200 INSERT INTO t2 VALUES(45.5, 'one'); 1201 INSERT INTO t2 VALUES('brambles', 'two'); 1202 INSERT INTO t2 VALUES(X'ABCDEF', 'three'); 1203 } {} 1204 1205 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} { 1206 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"} 1207 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"} 1208 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"} 1209 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"} 1210 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"} 1211 } 1212 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} { 1213 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"} 1214 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"} 1215 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"} 1216 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"} 1217 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"} 1218 } 1219 do_createtable_tests 4.3.2 { 1220 1 "INSERT INTO t1 VALUES(-1, 0)" {} 1221 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {} 1222 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {} 1223 4 "INSERT INTO t1 VALUES('bramble', 'abc')" {} 1224 5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {} 1225 1226 6 "INSERT INTO t2 VALUES(0, 0)" {} 1227 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {} 1228 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {} 1229 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {} 1230 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {} 1231 } 1232 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} { 1233 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"} 1234 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"} 1235 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"} 1236 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"} 1237 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"} 1238 } 1239 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} { 1240 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"} 1241 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'" 1242 {"columns x, y are"} 1243 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"} 1244 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 1245 {"columns x, y are"} 1246 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'" 1247 {"columns x, y are"} 1248 } 1249 1250 1251 # EVIDENCE-OF: R-52572-02078 For the purposes of determining the 1252 # uniqueness of primary key values, NULL values are considered distinct 1253 # from all other values, including other NULLs. 1254 # 1255 do_createtable_tests 4.4 { 1256 1 "INSERT INTO t1 VALUES(NULL, 0)" {} 1257 2 "INSERT INTO t1 VALUES(NULL, 0)" {} 1258 3 "INSERT INTO t1 VALUES(NULL, 0)" {} 1259 1260 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {} 1261 5 "INSERT INTO t2 VALUES(NULL, 'one')" {} 1262 6 "INSERT INTO t2 VALUES(NULL, 'two')" {} 1263 7 "INSERT INTO t2 VALUES(NULL, 'three')" {} 1264 1265 8 "INSERT INTO t2 VALUES(0, NULL)" {} 1266 9 "INSERT INTO t2 VALUES(45.5, NULL)" {} 1267 10 "INSERT INTO t2 VALUES(0.0, NULL)" {} 1268 11 "INSERT INTO t2 VALUES('brambles', NULL)" {} 1269 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {} 1270 1271 13 "INSERT INTO t2 VALUES(NULL, NULL)" {} 1272 14 "INSERT INTO t2 VALUES(NULL, NULL)" {} 1273 } 1274 1275 # EVIDENCE-OF: R-40010-16873 Unless the column is an INTEGER PRIMARY KEY 1276 # or the table is a WITHOUT ROWID table or a STRICT table or the column 1277 # is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY 1278 # column. 1279 # 1280 # If the column is an integer primary key, attempting to insert a NULL 1281 # into the column triggers the auto-increment behavior. Attempting 1282 # to use UPDATE to set an ipk column to a NULL value is an error. 1283 # 1284 do_createtable_tests 4.5.1 { 1285 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3 1286 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6 1287 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7 1288 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2 1289 } 1290 do_execsql_test 4.5.2 { 1291 CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v); 1292 INSERT INTO t3 VALUES(1, NULL, 2); 1293 INSERT INTO t3 VALUES('x', NULL, 'y'); 1294 SELECT u FROM t3; 1295 } {1 2} 1296 do_catchsql_test 4.5.3 { 1297 INSERT INTO t3 VALUES(2, 5, 3); 1298 UPDATE t3 SET u = NULL WHERE s = 2; 1299 } {1 {datatype mismatch}} 1300 do_catchsql_test 4.5.4 { 1301 CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID; 1302 INSERT INTO t4 VALUES(1, NULL, 2); 1303 } {1 {NOT NULL constraint failed: t4.u}} 1304 do_catchsql_test 4.5.5 { 1305 CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v); 1306 INSERT INTO t5 VALUES(1, NULL, 2); 1307 } {1 {NOT NULL constraint failed: t5.u}} 1308 do_catchsql_test 4.5.6 { 1309 CREATE TABLE t6(s INT, u INT PRIMARY KEY, v INT) STRICT; 1310 INSERT INTO t6 VALUES(1, NULL, 2); 1311 } {1 {NOT NULL constraint failed: t6.u}} 1312 do_catchsql_test 4.5.7 { 1313 CREATE TABLE t7(s INT, u INT PRIMARY KEY NOT NULL, v INT) STRICT; 1314 INSERT INTO t7 VALUES(1, NULL, 2); 1315 } {1 {NOT NULL constraint failed: t7.u}} 1316 1317 # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY 1318 # KEY constraint, except that a single table may have any number of 1319 # UNIQUE constraints. 1320 # 1321 drop_all_tables 1322 do_createtable_tests 4.6 { 1323 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {} 1324 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {} 1325 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {} 1326 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {} 1327 } 1328 1329 # EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table, 1330 # each row must contain a unique combination of values in the columns 1331 # identified by the UNIQUE constraint. 1332 # 1333 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must 1334 # have a unique combination of values in its primary key columns. 1335 # 1336 do_execsql_test 4.7.0 { 1337 INSERT INTO t1 VALUES(1, 2); 1338 INSERT INTO t1 VALUES(4.3, 5.5); 1339 INSERT INTO t1 VALUES('reveal', 'variableness'); 1340 INSERT INTO t1 VALUES(X'123456', X'654321'); 1341 1342 INSERT INTO t4 VALUES('xyx', 1, 1); 1343 INSERT INTO t4 VALUES('xyx', 2, 1); 1344 INSERT INTO t4 VALUES('uvw', 1, 1); 1345 } 1346 do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} { 1347 1 "INSERT INTO t1 VALUES(1, 'one')" {{t1.a}} 1348 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{t1.a}} 1349 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{t1.a}} 1350 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{t1.a}} 1351 1352 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{t1.a}} 1353 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{t1.a}} 1354 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{t1.a}} 1355 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{t1.a}} 1356 1357 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{t4.a, t4.b, t4.c}} 1358 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{t4.a, t4.b, t4.c}} 1359 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{t4.a, t4.b, t4.c}} 1360 1361 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{t4.a, t4.b, t4.c}} 1362 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{t4.a, t4.b, t4.c}} 1363 14 "UPDATE t4 SET a=0, b=0, c=0" {{t4.a, t4.b, t4.c}} 1364 } 1365 1366 # EVIDENCE-OF: R-00404-17670 For the purposes of UNIQUE constraints, 1367 # NULL values are considered distinct from all other values, including 1368 # other NULLs. 1369 # 1370 do_createtable_tests 4.8 { 1371 1 "INSERT INTO t1 VALUES(NULL, NULL)" {} 1372 2 "INSERT INTO t1 VALUES(NULL, NULL)" {} 1373 3 "UPDATE t1 SET a = NULL" {} 1374 4 "UPDATE t1 SET b = NULL" {} 1375 1376 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} 1377 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} 1378 7 "UPDATE t4 SET a = NULL" {} 1379 8 "UPDATE t4 SET b = NULL" {} 1380 9 "UPDATE t4 SET c = NULL" {} 1381 } 1382 1383 # EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY 1384 # constraints are implemented by creating a unique index in the 1385 # database. 1386 do_createtable_tests 4.9 -repair drop_all_tables -query { 1387 SELECT count(*) FROM sqlite_master WHERE type='index' 1388 } { 1389 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1 1390 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0 1391 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1 1392 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2 1393 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2 1394 } 1395 1396 # Obsolete: R-02252-33116 Such an index is used like any other index 1397 # in the database to optimize queries. 1398 # 1399 do_execsql_test 4.10.0 { 1400 CREATE TABLE t1(a, b PRIMARY KEY); 1401 CREATE TABLE t2(a, b, c, UNIQUE(b, c)); 1402 } 1403 do_createtable_tests 4.10 { 1404 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 1405 {/*SEARCH t1 USING INDEX sqlite_autoindex_t1_1 (b=?)*/} 1406 1407 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" 1408 {/*SCAN t2 USING INDEX sqlite_autoindex_t2_1*/} 1409 1410 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" 1411 {/*SEARCH t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)*/} 1412 } 1413 1414 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a 1415 # column definition or specified as a table constraint. In practice it 1416 # makes no difference. 1417 # 1418 # All the tests that deal with CHECK constraints below (4.11.* and 1419 # 4.12.*) are run once for a table with the check constraint attached 1420 # to a column definition, and once with a table where the check 1421 # condition is specified as a table constraint. 1422 # 1423 # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the 1424 # table or an existing row is updated, the expression associated with 1425 # each CHECK constraint is evaluated and cast to a NUMERIC value in the 1426 # same way as a CAST expression. If the result is zero (integer value 0 1427 # or real value 0.0), then a constraint violation has occurred. 1428 # 1429 drop_all_tables 1430 do_execsql_test 4.11 { 1431 CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 )); 1432 CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 )); 1433 INSERT INTO x1 VALUES('x', 'xx'); 1434 INSERT INTO x1 VALUES('y', 'yy'); 1435 INSERT INTO t1 SELECT * FROM x1; 1436 1437 CREATE TABLE x2(a CHECK( a||b ), b); 1438 CREATE TABLE t2(a, b, CHECK( a||b )); 1439 INSERT INTO x2 VALUES(1, 'xx'); 1440 INSERT INTO x2 VALUES(1, 'yy'); 1441 INSERT INTO t2 SELECT * FROM x2; 1442 } 1443 1444 do_createtable_tests 4.11 -error {CHECK constraint failed: %s} { 1445 1a "INSERT INTO x1 VALUES('one', 0)" {b>0} 1446 1b "INSERT INTO t1 VALUES('one', -4.0)" {b>0} 1447 1448 2a "INSERT INTO x2 VALUES('abc', 1)" {a||b} 1449 2b "INSERT INTO t2 VALUES('abc', 1)" {a||b} 1450 1451 3a "INSERT INTO x2 VALUES(0, 'abc')" {a||b} 1452 3b "INSERT INTO t2 VALUES(0, 'abc')" {a||b} 1453 1454 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {b>0} 1455 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {b>0} 1456 1457 4a "UPDATE x2 SET a='' WHERE rowid=1" {a||b} 1458 4b "UPDATE t2 SET a='' WHERE rowid=1" {a||b} 1459 } 1460 1461 # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL, 1462 # or any other non-zero value, it is not a constraint violation. 1463 # 1464 do_createtable_tests 4.12 { 1465 1a "INSERT INTO x1 VALUES('one', NULL)" {} 1466 1b "INSERT INTO t1 VALUES('one', NULL)" {} 1467 1468 2a "INSERT INTO x1 VALUES('one', 2)" {} 1469 2b "INSERT INTO t1 VALUES('one', 2)" {} 1470 1471 3a "INSERT INTO x2 VALUES(1, 'abc')" {} 1472 3b "INSERT INTO t2 VALUES(1, 'abc')" {} 1473 } 1474 1475 # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached 1476 # to a column definition, not specified as a table constraint. 1477 # 1478 drop_all_tables 1479 do_createtable_tests 4.13.1 { 1480 1 "CREATE TABLE t1(a NOT NULL, b)" {} 1481 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {} 1482 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {} 1483 } 1484 do_createtable_tests 4.13.2 -error { 1485 near "NOT": syntax error 1486 } { 1487 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {} 1488 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {} 1489 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {} 1490 } 1491 1492 # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the 1493 # associated column may not contain a NULL value. Attempting to set the 1494 # column value to NULL when inserting a new row or updating an existing 1495 # one causes a constraint violation. 1496 # 1497 # These tests use the tables created by 4.13. 1498 # 1499 do_execsql_test 4.14.0 { 1500 INSERT INTO t1 VALUES('x', 'y'); 1501 INSERT INTO t1 VALUES('z', NULL); 1502 1503 INSERT INTO t2 VALUES('x', 'y'); 1504 INSERT INTO t2 VALUES('z', NULL); 1505 1506 INSERT INTO t3 VALUES('x', 'y', 'z'); 1507 INSERT INTO t3 VALUES(1, 2, 3); 1508 } 1509 do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} { 1510 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a} 1511 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a} 1512 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c} 1513 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b} 1514 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a} 1515 } 1516 1517 # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL 1518 # constraints may be explicitly assigned a default conflict resolution 1519 # algorithm by including a conflict-clause in their definitions. 1520 # 1521 # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE 1522 # 1523 # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL 1524 # and UNIQUE constraints, respectively. 1525 # 1526 drop_all_tables 1527 do_execsql_test 4.15.0 { 1528 CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b); 1529 CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b); 1530 CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b); 1531 CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b); 1532 CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b); 1533 CREATE TABLE t1_xx(a PRIMARY KEY, b); 1534 1535 INSERT INTO t1_ab VALUES(1, 'one'); 1536 INSERT INTO t1_ab VALUES(2, 'two'); 1537 INSERT INTO t1_ro SELECT * FROM t1_ab; 1538 INSERT INTO t1_ig SELECT * FROM t1_ab; 1539 INSERT INTO t1_fa SELECT * FROM t1_ab; 1540 INSERT INTO t1_re SELECT * FROM t1_ab; 1541 INSERT INTO t1_xx SELECT * FROM t1_ab; 1542 1543 CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT); 1544 CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK); 1545 CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE); 1546 CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL); 1547 CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE); 1548 CREATE TABLE t2_xx(a, b NOT NULL); 1549 1550 INSERT INTO t2_ab VALUES(1, 'one'); 1551 INSERT INTO t2_ab VALUES(2, 'two'); 1552 INSERT INTO t2_ro SELECT * FROM t2_ab; 1553 INSERT INTO t2_ig SELECT * FROM t2_ab; 1554 INSERT INTO t2_fa SELECT * FROM t2_ab; 1555 INSERT INTO t2_re SELECT * FROM t2_ab; 1556 INSERT INTO t2_xx SELECT * FROM t2_ab; 1557 1558 CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT); 1559 CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK); 1560 CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE); 1561 CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL); 1562 CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE); 1563 CREATE TABLE t3_xx(a, b, UNIQUE(a, b)); 1564 1565 INSERT INTO t3_ab VALUES(1, 'one'); 1566 INSERT INTO t3_ab VALUES(2, 'two'); 1567 INSERT INTO t3_ro SELECT * FROM t3_ab; 1568 INSERT INTO t3_ig SELECT * FROM t3_ab; 1569 INSERT INTO t3_fa SELECT * FROM t3_ab; 1570 INSERT INTO t3_re SELECT * FROM t3_ab; 1571 INSERT INTO t3_xx SELECT * FROM t3_ab; 1572 } 1573 1574 foreach {tn tbl res ac data} { 1575 1 t1_ab {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three} 1576 2 t1_ro {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two} 1577 3 t1_fa {1 {UNIQUE constraint failed: t1_fa.a}} 0 {1 one 2 two 3 three 4 string} 1578 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string} 1579 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string} 1580 6 t1_xx {1 {UNIQUE constraint failed: t1_xx.a}} 0 {1 one 2 two 3 three} 1581 } { 1582 catchsql COMMIT 1583 do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" 1584 1585 do_catchsql_test 4.15.$tn.2 " 1586 INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl; 1587 " $res 1588 1589 do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac 1590 do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data 1591 } 1592 foreach {tn tbl res ac data} { 1593 1 t2_ab {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three} 1594 2 t2_ro {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two} 1595 3 t2_fa {1 {NOT NULL constraint failed: t2_fa.b}} 0 {1 one 2 two 3 three 4 xx} 1596 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx} 1597 5 t2_re {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three} 1598 6 t2_xx {1 {NOT NULL constraint failed: t2_xx.b}} 0 {1 one 2 two 3 three} 1599 } { 1600 catchsql COMMIT 1601 do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" 1602 1603 do_catchsql_test 4.16.$tn.2 " 1604 INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl 1605 " $res 1606 1607 do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac 1608 do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data 1609 } 1610 foreach {tn tbl res ac data} { 1611 1 t3_ab {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}} 1612 0 {1 one 2 two 3 three} 1613 2 t3_ro {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}} 1614 1 {1 one 2 two} 1615 3 t3_fa {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}} 1616 0 {1 one 2 two 3 three 4 three} 1617 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three} 1618 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three} 1619 6 t3_xx {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}} 1620 0 {1 one 2 two 3 three} 1621 } { 1622 catchsql COMMIT 1623 do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" 1624 1625 do_catchsql_test 4.17.$tn.2 " 1626 INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl 1627 " $res 1628 1629 do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac 1630 do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data 1631 } 1632 catchsql COMMIT 1633 1634 # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not 1635 # include a conflict-clause or it is a CHECK constraint, the default 1636 # conflict resolution algorithm is ABORT. 1637 # 1638 # The first half of the above is tested along with explicit ON 1639 # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx 1640 # and t3_xx). The following just tests that the default conflict 1641 # handling for CHECK constraints is ABORT. 1642 # 1643 do_execsql_test 4.18.1 { 1644 CREATE TABLE t4(a, b CHECK (b!=10)); 1645 INSERT INTO t4 VALUES(1, 2); 1646 INSERT INTO t4 VALUES(3, 4); 1647 } 1648 do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) } 1649 do_catchsql_test 4.18.3 { 1650 INSERT INTO t4 SELECT a+4, b+4 FROM t4 1651 } {1 {CHECK constraint failed: b!=10}} 1652 do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0 1653 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6} 1654 1655 # EVIDENCE-OF: R-19114-56113 Different constraints within the same table 1656 # may have different default conflict resolution algorithms. 1657 # 1658 do_execsql_test 4.19.0 { 1659 CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT); 1660 } 1661 do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}} 1662 do_execsql_test 4.19.2 { SELECT * FROM t5 } {} 1663 do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \ 1664 {1 {NOT NULL constraint failed: t5.b}} 1665 do_execsql_test 4.19.4 { SELECT * FROM t5 } {} 1666 1667 #------------------------------------------------------------------------ 1668 # Tests for INTEGER PRIMARY KEY and rowid related statements. 1669 # 1670 1671 # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one 1672 # of the special case-independent names "rowid", "oid", or "_rowid_" in 1673 # place of a column name. 1674 # 1675 # EVIDENCE-OF: R-06726-07466 A column name can be any of the names 1676 # defined in the CREATE TABLE statement or one of the following special 1677 # identifiers: "ROWID", "OID", or "_ROWID_". 1678 # 1679 drop_all_tables 1680 do_execsql_test 5.1.0 { 1681 CREATE TABLE t1(x, y); 1682 INSERT INTO t1 VALUES('one', 'first'); 1683 INSERT INTO t1 VALUES('two', 'second'); 1684 INSERT INTO t1 VALUES('three', 'third'); 1685 } 1686 do_createtable_tests 5.1 { 1687 1 "SELECT rowid FROM t1" {1 2 3} 1688 2 "SELECT oid FROM t1" {1 2 3} 1689 3 "SELECT _rowid_ FROM t1" {1 2 3} 1690 4 "SELECT ROWID FROM t1" {1 2 3} 1691 5 "SELECT OID FROM t1" {1 2 3} 1692 6 "SELECT _ROWID_ FROM t1" {1 2 3} 1693 7 "SELECT RoWiD FROM t1" {1 2 3} 1694 8 "SELECT OiD FROM t1" {1 2 3} 1695 9 "SELECT _RoWiD_ FROM t1" {1 2 3} 1696 } 1697 1698 # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column 1699 # named "rowid", "oid" or "_rowid_", then that name always refers the 1700 # explicitly declared column and cannot be used to retrieve the integer 1701 # rowid value. 1702 # 1703 # EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the 1704 # row key if the CREATE TABLE statement does not define a real column 1705 # with the same name. 1706 # 1707 do_execsql_test 5.2.0 { 1708 CREATE TABLE t2(oid, b); 1709 CREATE TABLE t3(a, _rowid_); 1710 CREATE TABLE t4(a, b, rowid); 1711 1712 INSERT INTO t2 VALUES('one', 'two'); 1713 INSERT INTO t2 VALUES('three', 'four'); 1714 1715 INSERT INTO t3 VALUES('five', 'six'); 1716 INSERT INTO t3 VALUES('seven', 'eight'); 1717 1718 INSERT INTO t4 VALUES('nine', 'ten', 'eleven'); 1719 INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen'); 1720 } 1721 do_createtable_tests 5.2 { 1722 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2} 1723 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight} 1724 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2} 1725 } 1726 1727 1728 # Argument $tbl is the name of a table in the database. Argument $col is 1729 # the name of one of the tables columns. Return 1 if $col is an alias for 1730 # the rowid, or 0 otherwise. 1731 # 1732 proc is_integer_primary_key {tbl col} { 1733 lindex [db eval [subst { 1734 DELETE FROM $tbl; 1735 INSERT INTO $tbl ($col) VALUES(0); 1736 SELECT (rowid==$col) FROM $tbl; 1737 DELETE FROM $tbl; 1738 }]] 0 1739 } 1740 1741 # EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid 1742 # table has a primary key that consists of a single column and the 1743 # declared type of that column is "INTEGER" in any mixture of upper and 1744 # lower case, then the column becomes an alias for the rowid. 1745 # 1746 # EVIDENCE-OF: R-45951-08347 if the declaration of a column with 1747 # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does 1748 # not become an alias for the rowid and is not classified as an integer 1749 # primary key. 1750 # 1751 do_createtable_tests 5.3 -tclquery { 1752 is_integer_primary_key t5 pk 1753 } -repair { 1754 catchsql { DROP TABLE t5 } 1755 } { 1756 1 "CREATE TABLE t5(pk integer primary key)" 1 1757 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1 1758 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1 1759 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0 1760 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0 1761 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0 1762 7 "CREATE TABLE t5(pk int primary key, v integer)" 0 1763 8 "CREATE TABLE t5(pk inTEger primary key)" 1 1764 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1 1765 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1 1766 } 1767 1768 # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or 1769 # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary 1770 # key column to behave as an ordinary table column with integer affinity 1771 # and a unique index, not as an alias for the rowid. 1772 # 1773 do_execsql_test 5.4.1 { 1774 CREATE TABLE t6(pk INT primary key); 1775 CREATE TABLE t7(pk BIGINT primary key); 1776 CREATE TABLE t8(pk SHORT INTEGER primary key); 1777 CREATE TABLE t9(pk UNSIGNED INTEGER primary key); 1778 } 1779 do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0 1780 do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0 1781 do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0 1782 do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0 1783 1784 do_execsql_test 5.4.3 { 1785 INSERT INTO t6 VALUES('2.0'); 1786 INSERT INTO t7 VALUES('2.0'); 1787 INSERT INTO t8 VALUES('2.0'); 1788 INSERT INTO t9 VALUES('2.0'); 1789 SELECT typeof(pk), pk FROM t6; 1790 SELECT typeof(pk), pk FROM t7; 1791 SELECT typeof(pk), pk FROM t8; 1792 SELECT typeof(pk), pk FROM t9; 1793 } {integer 2 integer 2 integer 2 integer 2} 1794 1795 do_catchsql_test 5.4.4.1 { 1796 INSERT INTO t6 VALUES(2) 1797 } {1 {UNIQUE constraint failed: t6.pk}} 1798 do_catchsql_test 5.4.4.2 { 1799 INSERT INTO t7 VALUES(2) 1800 } {1 {UNIQUE constraint failed: t7.pk}} 1801 do_catchsql_test 5.4.4.3 { 1802 INSERT INTO t8 VALUES(2) 1803 } {1 {UNIQUE constraint failed: t8.pk}} 1804 do_catchsql_test 5.4.4.4 { 1805 INSERT INTO t9 VALUES(2) 1806 } {1 {UNIQUE constraint failed: t9.pk}} 1807 1808 # EVIDENCE-OF: R-56094-57830 the following three table declarations all 1809 # cause the column "x" to be an alias for the rowid (an integer primary 1810 # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE 1811 # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y, 1812 # z, PRIMARY KEY(x DESC)); 1813 # 1814 # EVIDENCE-OF: R-20149-25884 the following declaration does not result 1815 # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY 1816 # KEY DESC, y, z); 1817 # 1818 do_createtable_tests 5 -tclquery { 1819 is_integer_primary_key t x 1820 } -repair { 1821 catchsql { DROP TABLE t } 1822 } { 1823 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1 1824 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1 1825 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1 1826 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0 1827 } 1828 1829 # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an 1830 # UPDATE statement in the same way as any other column value can, either 1831 # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by 1832 # using an alias created by an integer primary key. 1833 # 1834 do_execsql_test 5.7.0 { 1835 CREATE TABLE t10(a, b); 1836 INSERT INTO t10 VALUES('ten', 10); 1837 1838 CREATE TABLE t11(a, b INTEGER PRIMARY KEY); 1839 INSERT INTO t11 VALUES('ten', 10); 1840 } 1841 do_createtable_tests 5.7.1 -query { 1842 SELECT rowid, _rowid_, oid FROM t10; 1843 } { 1844 1 "UPDATE t10 SET rowid = 5" {5 5 5} 1845 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6} 1846 3 "UPDATE t10 SET oid = 7" {7 7 7} 1847 } 1848 do_createtable_tests 5.7.2 -query { 1849 SELECT rowid, _rowid_, oid, b FROM t11; 1850 } { 1851 1 "UPDATE t11 SET rowid = 5" {5 5 5 5} 1852 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6} 1853 3 "UPDATE t11 SET oid = 7" {7 7 7 7} 1854 4 "UPDATE t11 SET b = 8" {8 8 8 8} 1855 } 1856 1857 # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide 1858 # a value to use as the rowid for each row inserted. 1859 # 1860 do_createtable_tests 5.8.1 -query { 1861 SELECT rowid, _rowid_, oid FROM t10; 1862 } -repair { 1863 execsql { DELETE FROM t10 } 1864 } { 1865 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15} 1866 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16} 1867 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17} 1868 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3} 1869 } 1870 do_createtable_tests 5.8.2 -query { 1871 SELECT rowid, _rowid_, oid, b FROM t11; 1872 } -repair { 1873 execsql { DELETE FROM t11 } 1874 } { 1875 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15} 1876 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16} 1877 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17} 1878 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2} 1879 } 1880 1881 # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer 1882 # primary key or rowid column must contain integer values. Integer 1883 # primary key or rowid columns are not able to hold floating point 1884 # values, strings, BLOBs, or NULLs. 1885 # 1886 # This is considered by the tests for the following 3 statements, 1887 # which show that: 1888 # 1889 # 1. Attempts to UPDATE a rowid column to a non-integer value fail, 1890 # 2. Attempts to INSERT a real, string or blob value into a rowid 1891 # column fail, and 1892 # 3. Attempting to INSERT a NULL value into a rowid column causes the 1893 # system to automatically select an integer value to use. 1894 # 1895 1896 1897 # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an 1898 # integer primary key or rowid column to a NULL or blob value, or to a 1899 # string or real value that cannot be losslessly converted to an 1900 # integer, a "datatype mismatch" error occurs and the statement is 1901 # aborted. 1902 # 1903 drop_all_tables 1904 do_execsql_test 5.9.0 { 1905 CREATE TABLE t12(x INTEGER PRIMARY KEY, y); 1906 INSERT INTO t12 VALUES(5, 'five'); 1907 } 1908 do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } { 1909 1 "UPDATE t12 SET x = 4" {integer 4} 1910 2 "UPDATE t12 SET x = 10.0" {integer 10} 1911 3 "UPDATE t12 SET x = '12.0'" {integer 12} 1912 4 "UPDATE t12 SET x = '-15.0'" {integer -15} 1913 } 1914 do_createtable_tests 5.9.2 -error { 1915 datatype mismatch 1916 } { 1917 1 "UPDATE t12 SET x = 4.1" {} 1918 2 "UPDATE t12 SET x = 'hello'" {} 1919 3 "UPDATE t12 SET x = NULL" {} 1920 4 "UPDATE t12 SET x = X'ABCD'" {} 1921 5 "UPDATE t12 SET x = X'3900'" {} 1922 6 "UPDATE t12 SET x = X'39'" {} 1923 } 1924 1925 # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a 1926 # blob value, or a string or real value that cannot be losslessly 1927 # converted to an integer into an integer primary key or rowid column, a 1928 # "datatype mismatch" error occurs and the statement is aborted. 1929 # 1930 do_execsql_test 5.10.0 { DELETE FROM t12 } 1931 do_createtable_tests 5.10.1 -error { 1932 datatype mismatch 1933 } { 1934 1 "INSERT INTO t12(x) VALUES(4.1)" {} 1935 2 "INSERT INTO t12(x) VALUES('hello')" {} 1936 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {} 1937 4 "INSERT INTO t12(x) VALUES(X'3900')" {} 1938 5 "INSERT INTO t12(x) VALUES(X'39')" {} 1939 } 1940 do_createtable_tests 5.10.2 -query { 1941 SELECT typeof(x), x FROM t12 1942 } -repair { 1943 execsql { DELETE FROM t12 } 1944 } { 1945 1 "INSERT INTO t12(x) VALUES(4)" {integer 4} 1946 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10} 1947 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12} 1948 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000} 1949 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14} 1950 } 1951 1952 # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a 1953 # NULL value into a rowid or integer primary key column, the system 1954 # chooses an integer value to use as the rowid automatically. 1955 # 1956 do_execsql_test 5.11.0 { DELETE FROM t12 } 1957 do_createtable_tests 5.11 -query { 1958 SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12) 1959 } { 1960 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1} 1961 2 "INSERT INTO t12(y) VALUES(5)" {integer 2} 1962 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3} 1963 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" 1964 {integer 4 integer 5 integer 6} 1965 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3" 1966 {integer 7 integer 8 integer 9} 1967 } 1968 1969 finish_test